oracle存储过程及REFCURSOR的使用

一：简介

1、存储过程定义：

存储过程（Stored Procedure ）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

存储过程是由流控制的和SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。在ORACLE 中，若干个有联系的过程可以组合在一起构成程序包。

2、优点：

a） 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

b） 当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务管理结合一起使用。

c）存储过程可以重复使用,可减少数据库开发人员的工作量。

d）安全性高,可设定只有某用户才具有对指定存储过程的使用权。

简单说，你在你的机器上写了个存储过程，这个存储过程像那些表里的数据一样被放在遥远的数据库服务器当中，但是它又是可执行的代码，其他能连到数据库服务器的用户，可以调用你写的存储过程
它的作用是隐藏细节，就是说，你写的存储过程代码可能很复杂，但是其他人调用它却很简单，不用具体知道它是如何做的，且一次能完成多个指令

3、分类：

oracle有系统存储过程和自定义存储过程2种存储过程。

系统存储过程就是由oracle预先提供的一组完成特定功能的存储过程,安装完oracle就有了。

自定义存储过程就是存在oracle数据库里由一组plsql语句组成的自定义过程(procedure)。它可以供其它oracle自定义存储过程、自定义函数和job调用或者由客户端程序调用。

二：基本使用方法及示例

1、基本结构：

CREATE OR REPLACE PROCEDURE 存储过程名字
（参数1 IN NUMBER,参数2 IN NUMBER） 
AS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END 存储过程名字 

2、 无参形式的procedure：

--无参procedure
create or replace procedure pro_no_param
is
begin
  dbms_output.put_line(&＃39;the procedure without params&＃39;);  
end pro_no_param;

--调用 
--one: 无参的procedure名字后面必须要()；
call pro_no_param();

--two:procedure名称后面可以没有();
begin
  pro_no_param();
end; 

3、 参数类型为IN的procedure：

--有参procedure  只有IN类型
create or replace procedure pro_in_param(
       v_1 in number,
       v_2 in varchar2,
       v_3 in date
)
is
begin
  dbms_output.put_line(&＃39;v1: &＃39; || v_1 || &＃39; v2: &＃39; || v_2 || &＃39; v2: &＃39;|| (to_char(v_3, &＃39;yyyy-mm-dd&＃39;)));
end pro_in_param;


begin
  pro_in_param(1, &＃39;chy&＃39;, sysdate);
end;

4、 参数类型为OUT的procedure：

--有参procedure  只有OUT类型
create or replace procedure pro_out_param(
       v1 out number,
       v2 out char
)
is
begin
  v1 := 2;
  v2 := &＃39;andyChen&＃39;;
end pro_out_param;


--记得声明用于存放procedure的out值的变量
--语句结束了一定记得结尾的 —— ;
declare
  v_1 number;
  v_2 varchar2(200);
begin
  pro_out_param(v_1, v_2);
  dbms_output.put_line(&＃39;v1: &＃39; || v_1 || &＃39; v2: &＃39; || v_2);
end; 

5、 参数类型同时为IN和OUT的procedure：

--同时为INOUT参数的procedure
--用同一变量接收传入的值然后将这个变量当作输出的值赋给执行时声明的变量
create or replace procedure pro_in_out_param(
       in_out_param in out varchar2
)
is
begin
      in_out_param := &＃39;in_out_param and &＃39; || in_out_param;
end pro_in_out_param; 

declare
   in_out_param varchar2(222) := &＃39;detail param&＃39;;
begin
   pro_in_out_param(in_out_param);
   dbms_output.put_line(in_out_param);
end;

三：实例

CREATE TABLE user_info
(
 id   VARCHAR2(4) not null primary key,
 name VARCHAR2(15),
 pwd  VARCHAR2(15),
 address VARCHAR2(30)
);

--创建一个添加用户的stored_procedure;
create or replace procedure pro_addUser(
       n_id user_info.id%type,
       n_name user_info.name%type,
       n_pwd     user_info.pwd%TYPE,
       n_address user_info.address%TYPE
)
as
begin
  --插入数据
  insert into user_info(id,name,pwd,address)
  values(n_id, n_name, n_pwd, n_address);
end pro_addUser;

--调用、有变量需要声明的时候才有declare、没有就直接begin
begin
    pro_addUser(&＃39;1&＃39;, &＃39;chy&＃39;, &＃39;admin&＃39;, &＃39;nanjin&＃39;);
    if SQL%found then
      dbms_output.put_line(&＃39;add successed&＃39;);
    end if;
end;

--根据id查询用户名和密码
create or replace procedure pro_getUserInfo(
       n_id       user_info.id%type,
       n_name out user_info.name%type,
       n_pwd  out user_info.pwd%type
)
as
begin
  select user_info.name, user_info.pwd into n_name, n_pwd 
  from user_info
  where user_info.id=n_id;
end pro_getUserInfo;


--调用
declare
    v_id    user_info.id%type := &＃39;1&＃39;;
    v_name  user_info.name%type;
    v_pwd   user_info.pwd%type;
begin
    pro_getUserInfo(v_id, v_name, v_pwd);
    dbms_output.put_line(&＃39;name: &＃39; || v_name || &＃39; pwd: &＃39; || v_pwd);
end;

-- 打印九九乘法表
create or replace procedure pro_multiplication_table
is
       i  integer;
       j  integer;
begin
       for i in 1..9 loop
         for j in 1..9 loop
           if i>=j then
             DBMS_output.put(To_Char(j)||&＃39;*&＃39;||to_char(i)||&＃39;=&＃39;||to_char(i*j)||&＃39;　　 &＃39;);
           end if;
         end loop;
         DBMS_output.put_line(&＃39;&＃39;);
       end loop;
end  pro_multiplication_table;      

--调用
call pro_multiplication_table();

--使用自定义游标、根据工作and薪水查询员工姓名
create or replace procedure pro_getName(
       n_sal        emp.sal%type,
       n_ename  out emp.ename%type,
       n_job in out emp.job%type
)
is
       n_count number;
       cursor cur is select ename from emp where emp.sal > n_sal and emp.job=n_job;
       n_row  cur%rowtype;
begin
       select count(*) into n_count from emp where emp.sal > n_sal and emp.job=n_job;
       if n_count > 1 then
         for n_row in cur loop
           DBMS_output.put_line(&＃39;职工姓名为:&＃39;||n_row.ename||&＃39;    工作为:&＃39;||n_job);
         end loop;
       else
           DBMS_output.put_line(&＃39;未查到符合条件的记录！&＃39;);  
       end if;
end  pro_getName;      

-- 调用
declare
   v_sal   emp.sal%type := 2000;
   v_job   emp.job%type :=&＃39;MANAGER&＃39;;
   v_ename emp.ename%type;
begin
   pro_getName(v_sal, v_ename, v_job);
end;   

--ref cursor的使用
--创建存放弱引用和强引用的cursor的包
create or replace package refcursor_pkg 
as
type weak_ref_cursor is ref cursor;
type strong_ref_cursor is ref cursor return emp%rowtype;
end refcursor_pkg;

--将弱引用的cursor作为结果返回
create or replace procedure test(
       p_deptno in number,
       p_cursor out refcursor_pkg.weak_ref_cursor
)
is begin
       open p_cursor for select * from emp where deptno=p_deptno;
end test;

/**或者不用包直接使用下面这种定义
create or replace procedure test_1( 
       p_deptno IN number,
       p_cursor OUT SYS_REFCURSOR
)
is
begin
  open p_cursor FOR select *from emp where  deptno = p_deptno;
end test_1;
*/

declare
    v_deptno number := 20;
    v_cursor refcursor_pkg.weak_ref_cursor;
    r_emp emp%rowtype;
begin
    test(v_deptno, v_cursor);
    loop
      fetch v_cursor into r_emp;
      exit when v_cursor%notfound;
      dbms_output.put_line(&＃39;empno: &＃39; || r_emp.empno || &＃39; ename: &＃39; || r_emp.ename || &＃39; job: &＃39; || r_emp.job);
    end loop;
    close v_cursor;
end;    
/**

//java中使用ref cursor

public void method() throws SQLException{
  Connection cOnn= getConnection();
  CallableStatement cstmt = null;
  ResultSet rs = null;
  int deptno = 10;
  Object temp;
  try{
      cstmt = conn.prepareCall("begin  test(?,?); end;");
      cstmt.setInt(1, deptno);
      cstmt.registerOutParameter(2, OracleTypes.CURSOR); 
      cstmt.execute();
      rs = (ResultSet) cstmt.getObject(2);
      ResultSetMetaData rsm = rs.getMetaData();
      int columnCount = rsm.getColumnCount();
      while (rs.next()){
         for (int j=0;j