程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle存儲過程及REFCURSOR的使用

oracle存儲過程及REFCURSOR的使用

編輯：Oracle教程

oracle 存儲過程及REF CURSOR的使用

摘要：主要記錄了oracle的stored procedure相關知識、從定義到使用。從最簡單的例子入手、逐漸深入。同時後面結合了ref cursor的使用。順便補充了在java中如何使用。

一：簡介

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('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();
      }  
  }
}
*/