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('${存储过程的名称}');