Oracle的存储过程
Oracle存储过程简介
什么是存储过程
在oracle中,存储过程是为了完成特定功能的SQL语句集,编译后存储在数据库中,用户通过指定存储过程名字并给出参数(如果该存储过程带有参数)来调用存储过程。
存储过程有什么优点
- 效率高:存储过程编译一次后,就会存到数据库,每次调用时都直接执行,而如果是执行普通sql语句,每次都要编译后再执行,效率显然更低一些。
- 复用性高:存储过程往往是针对一个特定的功能编写的,当再需要完成这个特定的功能时,可以再次调用该存储过程。
- 减少网络传输:存储过程编译好会放在数据库,我们在远程调用时,不会传输大量的字符串类型的sql语句;另外因为一个存储过程中可以执行一系列的操作,程序中如果要执行复杂的sql操作,不需要多次连接数据库执行多条sql。
- 安全性高:完成某个特定功能的存储过程一般只有特定的用户可以使用,具有使用身份限制,更安全。
存储过程的语法格式
基本结构
Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常(可写可不写,要增强脚本的容错性和调试的方便性那就写上异常处理)
无参存储过程
无参存储过程是指存储过程没有输入、输出参数,如下就是一个简单的无参存储过程:
CREATE OR REPLACE PROCEDURE pro_01
AS --这里用AS或者IS是一样的
v_id NUMBER := 100; --学生id
v_name VARCHAR2; --学生名称
v_age NUMBER; --学生年龄
BEGIN
SELECT student_name,student_age INTO v_name,v_age
FROM student where student_id=v_id;
--输出学生信息
dbms_output.put_line('姓名:'||v_name||',年龄:'||v_age);
--异常处理
WHEN OTHERS THEN
dbms_output.put_line('OTHER_EXCEPTION:' || SQLERRM);
END;
有参存储过程
我们像调用Java中的方法一样,给存储过程定义输入、输出参数,比如对于上面的这个例子,学生id可以通过参数传入:
CREATE OR REPLACE PROCEDURE pro_02(
i_id IN NUMBER;
o_resultcode OUT VARCHAR2,
o_msg OUT VARCHAR2 --最后一个参数不需要以‘,’结尾,参考insert语句最后一个字段后也没有‘,’
)
AS
v_name VARCHAR2;
v_age NUMBER;
BEGIN
SELECT student_name,student_age INTO v_name,v_age
FROM student where student_id=v_id;
--输出学生信息
dbms_output.put_line('姓名:'||v_name||',年龄:'||v_age);
o_resultcode := 0;
o_msg := 'SUCCESS';
--异常处理
WHEN OTHERS THEN
dbms_output.put_line('OTHER_EXCEPTION:' || SQLERRM);
o_resultcode := -1;
o_msg := 'OTHER_EXCEPTION:' || SQLERRM;
END;
存储过程参数的几种类型:
- in 表示传入的参数,是参数的默认模式,例如i_id IN NUMBER和i_id NUMBER语义是相同的。
- out 表示返回的参数,该参数的值可以传递回调用它的过程。
- in out 表示此参数可以向该存储过程中传递值,也可以将某个值传出去。
扩展
在存储过程中既可以使用各种循环判断,也可以有事务的控制,比如我们需要写一个存储过程来统计不同月份各个城市的订单量。
CREATE OR REPLACE PROCEDURE pro_03(
i_month IN NUMBER;
o_resultcode OUT VARCHAR2,
o_msg OUT VARCHAR2
)
AS
v_count NUMBER := 0;
cursor cursor_stat is
select month, citycode, count(*) ordercount from order group by month, citycode;
BEGIN
delete from order_stat where month = i_month;
for stat_item in cursor_stat loop
insert into order_stat(month, citycode, ordercount)
values(stat_item.month, stat_item.citycode, stat_item.ordercount);
v_count := v_count + 1;
if mod(v_count, 200) = 0 then --每200条数据commit一次
commit;
end loop;
commit;
o_resultcode := 0;
o_msg := 'SUCCESS';
--异常处理
WHEN OTHERS THEN
dbms_output.put_line('OTHER_EXCEPTION:' || SQLERRM);
o_resultcode := -1;
o_msg := 'OTHER_EXCEPTION:' || SQLERRM;
--rollback; 这里也可以进行回滚
END;
存储过程也可以返回结果集。
CREATE OR REPLACE PROCEDURE pro_04(
cur_data OUT SYS_REFCURSOR --这里使用 cur_data OUT TYPES.CURSORTYPE 也是可以的
)
AS
BEGIN
open cur_data for
select id, name, age from student;
END;
其它
查询存储过程的编译错误
存储过程如果问题,在编译时时会报错的,编译界面也可以看到异常信息。另外编译的异常信息也会记录到数据库表,可通过如下sql查询。
select * from SYS.USER_ERRORS where NAME = upper('${存储过程的名称}');