存储过程是保存可以接受或返回用户提供参数的SQL语句集合。在日常的使用中,经常会遇到复杂的业务逻辑和对数据库的操作,使用存储过程可以进行封装。可以在数据库中定义子程序,然后把子程序存储在数据库服务器,之后通过名称调用。
存储过程是预先编译过,进行优化后,存储在SQL的内存中,使用的时候不需要重新编译,提高工作效率。
存储过程的代码直接存储在数据库中,用户通过名称进行调用,减小网络流量,加快执行速度。如:百万以上的数据查询,存储过程分页要比其他方式的分页快得多
存储过程可以减少SQL注入攻击,提高系统的安全性。执行的过程也受到用户的身份权限控制,因此没有数据操作权限的用户只能在权限控制下间接的存储数据。
在同时进行主从表以及夺标的数据维护和有效性验证时,存储过程比较方便,可以有效地利用SQL的事务处理机制。
使用存储过程,可以实现存储过程设计和编码工作分开进行,只要存储过程名、参数、及返回信息告诉编码人员即可。
使用存储过程封装业务逻辑将限制应用程序的可移植性;另外,如果更改参数或者返回的数据以及类型,需要修改相关的代码,比较繁琐。
完整的过程结构如下:
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')
为了提高存储过程的健壮性,避免运行错误,建立存储过程时,应该包含异常处理的部分。异常包括预定义异常、非预定义异常和自定义异常。
使用方法:
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;
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;