oracle 存储过程及REF CURSOR的使用

本文详细介绍了Oracle数据库中PL/SQL存储过程的基本使用方法,包括无参数存储过程、不同参数类型的存储过程(IN、OUT、INOUT)、自定义游标以及REFCURSOR的使用,并提供了具体的应用实例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

基本使用方法及示例

1、基本结构:

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

2、无参形式的procedure:

    --无参procedure  
    create or replace procedure pro_no_param  
    is  
    begin  
      dbms_output.put_line('the procedure without params');    
    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('v1: ' || v_1 || ' v2: ' || v_2 || ' v2: '|| (to_char(v_3, 'yyyy-mm-dd')));  
end pro_in_param;  
  
  
begin  
  pro_in_param(1, 'chy', 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 := 'andyChen';  
    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('v1: ' || v_1 || ' v2: ' || 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 := 'in_out_param and ' || in_out_param;  
end pro_in_out_param;   
  
declare  
   in_out_param varchar2(222) := 'detail param';  
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('1', 'chy', 'admin', 'nanjin');  
        if SQL%found then  
          dbms_output.put_line('add successed');  
        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 := '1';  
        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('name: ' || v_name || ' pwd: ' || 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)||'*'||to_char(i)||'='||to_char(i*j)||'   ');  
               end if;  
             end loop;  
             DBMS_output.put_line('');  
           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('职工姓名为:'||n_row.ename||'    工作为:'||n_job);  
             end loop;  
           else  
               DBMS_output.put_line('未查到符合条件的记录!');    
           end if;  
    end  pro_getName;        
      
    -- 调用  
    declare  
       v_sal   emp.sal%type := 2000;  
       v_job   emp.job%type :='MANAGER';  
       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('empno: ' || r_emp.empno || ' ename: ' || r_emp.ename || ' job: ' || 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< columnCount;j++){  
                temp = rs.getObject(j+1);  
             }  
          }  
      } finally {  
          if (!rs==null){  
            rs.close();  
          }  
          if (!stmt==null){  
            stmt.close();  
          }  
          if (!conn==null){  
            conn.close();  
          }    
      }  
    }  
    */    

 

转载于:https://www.cnblogs.com/xwb583312435/p/9054844.html

### 使用 OUT SYS_REFCURSOR 参数传递游标 在 Oracle PL/SQL 中,`SYS_REFCURSOR` 是一种用于表示数据库查询结果集的数据类型。通过 `OUT` 参数可以将这种类型的游标从存储过程或函数返回给调用者。 #### 创建带有 OUT SYS_REFCURSOR存储过程 定义一个简单的存储过程来演示如何声明并打开 `SYS_REFCURSOR` 类型的输出参数: ```plsql CREATE OR REPLACE PROCEDURE get_employee_data ( p_deptno IN NUMBER, emp_cursor OUT SYS_REFCURSOR ) IS BEGIN OPEN emp_cursor FOR SELECT ename, job, hiredate FROM emp WHERE deptno = p_deptno; END; / ``` 此代码创建了一个名为 `get_employee_data` 的存储过程,它接受部门编号作为输入,并通过 `emp_cursor` 返回该部门员工的信息[^1]。 #### 调用带 OUT SYS_REFCURSOR 参数的过程 为了展示如何接收来自上述存储过程的结果,在客户端应用程序中可以通过绑定变量的方式处理这个游标对象。下面是一个匿名PL/SQL块的例子,展示了如何调用上面定义好的存储过程并将获取到的数据打印出来: ```plsql DECLARE v_emp_cur SYS_REFCURSOR; v_ename VARCHAR2(50); v_job VARCHAR2(50); v_hiredate DATE; BEGIN -- 调用存储过程 get_employee_data(p_deptno => 10, emp_cursor => v_emp_cur); LOOP FETCH v_emp_cur INTO v_ename, v_job, v_hiredate; EXIT WHEN v_emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Name: ' || v_ename || ', Job: ' || v_job || ', Hire Date:'|| TO_CHAR(v_hiredate,'DD-MON-YYYY')); END LOOP; CLOSE v_emp_cur; END; / ``` 这段脚本首先声明了一个 `SYS_REFCURSOR` 变量 `v_emp_cur` 来保存由 `get_employee_data` 过程传回的游标数据;接着在一个循环里逐行取出记录直到没有更多行为止;最后关闭游标完成操作。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值