Java程序员的日常——存储过程知识普及

存储过程是保存可以接受或返回用户提供参数的SQL语句集合。在日常的使用中,经常会遇到复杂的业务逻辑和对数据库的操作,使用存储过程可以进行封装。可以在数据库中定义子程序,然后把子程序存储在数据库服务器,之后通过名称调用。

特点

1 提高性能

存储过程是预先编译过,进行优化后,存储在SQL的内存中,使用的时候不需要重新编译,提高工作效率。

2 减少网络流量

存储过程的代码直接存储在数据库中,用户通过名称进行调用,减小网络流量,加快执行速度。如:百万以上的数据查询,存储过程分页要比其他方式的分页快得多

3 提高安全性

存储过程可以减少SQL注入攻击,提高系统的安全性。执行的过程也受到用户的身份权限控制,因此没有数据操作权限的用户只能在权限控制下间接的存储数据。

4 事务处理机制

在同时进行主从表以及夺标的数据维护和有效性验证时,存储过程比较方便,可以有效地利用SQL的事务处理机制。

5 分离设计编码与使用

使用存储过程,可以实现存储过程设计和编码工作分开进行,只要存储过程名、参数、及返回信息告诉编码人员即可。

6 (缺点)不易移植和修改

使用存储过程封装业务逻辑将限制应用程序的可移植性;另外,如果更改参数或者返回的数据以及类型,需要修改相关的代码,比较繁琐。

语法结构

完整的过程结构如下:

create to replace procedure 过程名 as 
声明语句段;
begin
执行语句段;
exception
异常处理语句段;
end;

举例子:

-- 学生表student
create table student(
    sno number(6),
    sname varchar2(25),
    pno number(6) primary key
);

-- 存储过程
create or replace procedure stu_proc as 
p_name varchar2(25);
begin
    select sname into p_name from student where sno=1;
    dbms_output.put_line(p_name);
end;

-- 调用存储过程
call stu_proc();

关于参数的类型,存储过程大致提供下面几种

无参数的存储过程

定义

create or replace procedure stu_proc as 
pname varchar2(25);
begin
    select sname into pname from student where sno=1;
    dbms_output.put_line(pname);
end;

使用方法为:call stu_proc();

仅有输入参数的过程

create or replace procedure stu_proc1(pno in student.sno%type) as 
pname varchar2(25);
begin
    select sname into pname from student where sno=pno;
    dbms_output.put_line(pname);
end;

使用方法为:call stu_proc1('001')

仅有输出参数的过程

create or replace procedure stu_proc2(pname out student.sname%type) as 
begin
    select sname into pname from student where sno=1;
    dbms_output.put_line(pname);
end;

这种存储过程不能直接用call调用,需要在oracle函数调用。使用方法为:call stu_proc2(name)

有输入、输出的存储过程

create or replace procedure stu_proc3(pname out student.sname%type,pname out student.sname%type) as 
begin
    select sname into pname from student where sno=pno;
    dbms_output.put_line(pname);
end;

使用方法为:call stu_proc3(name,'001')

存储过程的异常处理

为了提高存储过程的健壮性,避免运行错误,建立存储过程时,应该包含异常处理的部分。异常包括预定义异常、非预定义异常和自定义异常。

  • 预定义异常:PL\SQL提供的系统异常
  • 费预定义异常:用于处理与预定义异常无关的Oracle错误
  • 自定义异常:处理Oracle错误之外的一些异常

使用方法:

create or replace procedure stu_proc6(pno in student.sno%type,pname out student.sname%type)
    is 
    begin
        select sname into pname from student where sno=pno;
    EXCEPTION
        when NO_DATA_FOUND then
        RAISE_APPLICATION_ERROR
            (-20011,'ERROR:不存在!');
end;

常用的异常处理:

命名的系统异常

产生原因

ACCESS_INTO_NULL

定义对象

CASE_NOT_FOUND

CASE中未包含相应的WHEN,并且没有设置集合元素的初始化

COLLECTION_IS_NULL

集合元素未初始化

CURSER_ALREADY_OPEN

游标已经打开

DUP_VAL_ON_INDEX

唯一索引对应的列上有重复的值

INVALID_CURSOR

在不合法的游标上进行操作

INVALID_NUMBER

内嵌的SQL语句不能讲字符穿换成数字

NO_DATA_FOUND

使用select into 未返回行,或者应用索引表未初始化的

TOO_MANY_ROWS

执行select into,结果集超过一行

ZERO_DIVIDE

除数为0

SUBSCRIPT_BEYOND_COUNT

元素下表超过嵌套表或VARRAY的最大值

SUBSCRIPT_OUTSIDE_LIMIT

使用嵌套类或VARRAY时,将下表指定为负数

VALUE_ERROR

赋值时,变量长度不足以容纳实际数据

LOGIN_DENIED

PL\SQL应用连接到oracle时,提供了 不正确的用户名密码

NOT_LOGGED_ON

PL\SQL应用程序在没有连接oracle数据的情况下访问数据

PROGRAM_ERROR

PL\SQL内部问题,可能需要重装数据字典

ROWTYPE_MISMATCH

主游标变量与PLSQL游标变量的返回类型不兼容

SELF_IS_NULL

使用对象类型时,在null对象上调用对象方法

STORAGE_ERROR

运行PL\SQL时,超出内存空间

SYS_INVALIDE_ID

无效的ROWID字符串

TIMEOUT_ON_RESOURCE

Oracle在等待资源连接超时

存储过程与函数的区别

在定义上

定义的名称这个就不说了,一个是FUNCTION,一个是PROCEDURE;

  • 存储过程的参数列表有输入参数、输出参数、输入输出参数
  • 函数的参数只有输入参数,最后会加上一个return返回值。

在返回值上

  • 存储过程的返回值,可以有多个
  • 函数的返回值只有一个

调用方式上

  • 存储过程的调用方式有:exec、execute、语句块调用
  • 函数的调用方式有:可以在函数块中、也可以直接在sql中使用,比如:
create or replace function add_three_numbers
(
    a NUMBER:=0,b NUMBER:=0,c NUMBER:=0
)
return number is
begin
return a+b+c;
end;

select add_three_numbers(1,2,3) from dual;

事务处理

  • 1 事务用于确保数据的一致性,要么全部确认,要不全部取消。
  • 2 档执行事务操作的时候,Oracle会作用在表上加锁,防止其他的用户改变表。同时也会在被作用的行上加行锁,以防止其他事务在相应行上执行DML操作
  • 3 执行事务提交或者事务回滚时,Oracle会确认事务变化或回滚事务、结束事务、删除保存点、释放锁

参考

1 Oracle存储过程

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏osc同步分享

mybatis 的一些常用功能

1. association 查询结果的一对一关联: <resultMap id="blogResult" type="Blog"> <id propert...

2868
来自专栏MasiMaro 的技术博文

SQL语句执行与结果集的获取

title: SQL语句执行与结果集的获取 tags: [OLEDB, 数据库编程, VC++, 数据库] date: 2018-01-28 09:22:1...

1792
来自专栏云数据库

MySQL5.7 JSON实现简介

本文主要介绍在MySQL 5.7.7开始引入的非结构化数据类型JSON的特性以及具体的实现方式(包括存储方式)。首先介绍为什么要引入JSON的原生数据类型的支持...

2504
来自专栏帘卷西风的专栏

关于mysql自增id的获取和重置

转载请注明出处:帘卷西风的专栏(http://blog.csdn.net/ljxfblog)

3462
来自专栏JetpropelledSnake

SQL学习笔记之MySQL中真假“utf8” 问题

最近我遇到了一个 bug,我试着通过 Rails 在以“utf8”编码的 MariaDB 中保存一个 UTF-8 字符串,然后出现了一个离奇的错误:

802
来自专栏黑泽君的专栏

day31_Hibernate复习_03(补刀)

701
来自专栏腾讯云数据库团队的专栏

MySQL 5.7 JSON 实现简介

本文主要介绍了MySQL在5.7.7之后引入的原生JSON支持的特性,说明了引入JSON类型的好处,并结合具体的示例介绍了MySQL在JSON类型上对外的接口以...

5.3K1
来自专栏chenssy

【死磕Java并发】—–Java内存模型之从JMM角度分析DCL

DCL,即Double Check Lock,中卫双重检查锁定。其实DCL很多人在单例模式中用过,LZ面试人的时候也要他们写过,但是有很多人都会写错。他们为什么...

40911
来自专栏大学生计算机视觉学习DeepLearning

c++ 网络编程(七)TCP/IP LINUX下 socket编程 基于套接字的标准I/O函数使用 与 fopen,feof,fgets,fputs函数用法

原文链接:https://www.cnblogs.com/DOMLX/p/9614820.html

2104
来自专栏木子昭的博客

<导图>Mysql入门基础语法及示例

数据库操作 查看所有数据库 show database; 创建数据库 语法 create database 数据库名 charset=utf8; 示例 ...

3009

扫码关注云+社区

领取腾讯云代金券