目錄

• 一、概述
• 二、存儲過程詳解
• 1、創建過程語法：
• 2、創建存儲過程
• 3、調用存儲過程
• 4、C# 調用：
• 三、存儲過程返回記錄集SYS_REFCURSOR
• 1、返回單行語法
• 2、返回多行語法
• 四、維護存儲過程
• 1、刪除過程
• 2、顯示過程代碼
• 3、查看過程狀態
• 4、重新編譯過程
• 五. 過程與函數比較
• 1、相同點：
• 2、不同點：
• 六、 與過程相關數據字典

一、概述

過程和函數統稱為PL/SQL子程序，他們是被命名的PL/SQL塊，均存儲于數據庫中。

并通過輸入、輸出和輸入輸出參數與其調用者交換信息。唯一區別是函數總向調用者返回數據。

二、存儲過程詳解

1、創建過程語法：

CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
    [ ( parameter_declaration [, parameter_declaration ]... ) ]
    [ invoker_rights_clause ]
    { IS | AS }
    { [ declare_section ] body | call_spec | EXTERNAL} ;

說明：

• procedure_name：過程名稱。
• parameter_declaration：參數聲明，格式如下：

parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
  | { OUT | IN OUT } [ NOCOPY ] datatype

• IN：輸入參數。
• OUT：輸出參數。
• IN OUT：輸入輸出參數。
• invoker_rights_clause：這個過程使用誰的權限運行，格式：

AUTHID { CURRENT_USER | DEFINER }

• declare_section：聲明部分。
• body：過程塊主體，執行部分

2、創建存儲過程

帶有輸入、輸出參數的過程

CREATE OR REPLACE PROCEDURE proc_demo
(
    dept_no NUMBER DEFAULT 10,
    sal_sum OUT NUMBER,
    emp_count OUT NUMBER
  )
IS
BEGIN
    SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
  FROM employees WHERE department_id = dept_no;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE("溫馨提示:你需要的數據不存在!");
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||"---"||SQLERRM);
END proc_demo;

3、調用存儲過程

調用方式： 
1)、當在SQL*PLUS中調用過程時，需要使用CALL或者EXECUTE命令，而在PL/SQL塊中過程可以直接引用。

-- 調用刪除員工的過程
EXEC remove_emp(1);
  
-- 調用插入員工的過程
EXECUTE insert_emp(1, "tommy", "lin", 2);

2)、在PL/SQL語句塊中直接調用。

DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
BEGIN
  Proc_demo(30, v_sum, v_num);
     DBMS_OUTPUT.PUT_LINE("溫馨提示:30號部門工資總和："||v_sum||",人數："||v_num);
  Proc_demo(sal_sum => v_sum, emp_count => v_num);
     DBMS_OUTPUT.PUT_LINE("溫馨提示:10號部門工資總和："||v_sum||",人數："||v_num);
END;

4、C# 調用：

OracleCommand cmd = new OracleCommand("prroc_demo", myOracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_companycode", OracleType.Char); 
cmd.Parameters["v_companycode"].Value = "aa";
cmd.Parameters.Add("v_returnvalue", OracleType.Float).Direction = ParameterDirection.Output;
cmd.ExecuteNoQuery();
string eval = cmd.Parameters["v_returnvalue"].Value.ToString();

三、存儲過程返回記錄集SYS_REFCURSOR

cursor與REF cursor大致有以下幾點區別：

• 靜態游標不能返回到客戶端，只有PL/SQL才能利用它。ref游標則可以，是從Oracle的存儲過站返回結果集的方式。
• PL/SQL靜態游標可以是全局的，而ref游標只能在定義它的過程中使用，但ref游標可以從子例程傳遞到子例程，而普通游標則不能。
• 靜態光標比ref游標效率要高。
• sys_refcursor在oracle9i以后系統定義的一個refcursor，主要用于在過程中返回結果集。

1、返回單行語法

create or replace procedure proc_query_rent (
  param_region varchar2,  --定義區
  param_room number,  --定義室
  param_hall number,  --定義廳
  param_rentMin number,  --定義租金上限
  param_rentMax number,  --定義租金下限
  param_resultSet OUT SYS_REFCURSOR --定義out參數返回結果集
) 
as 
begin 
open param_resultSet for select  * from tb_rent
 where region like case when param_region IS null then "%" else param_region end
  AND room like case when param_room IS null then "%" else to_char(param_room) end
  AND hall like case when param_hall IS null then "%" else to_char(param_hall) end
  AND rent between case when param_rentMin IS null then 0 else param_rentMin end
  AND case when param_rentMax IS null then 99999999 else param_rentMax end;
end;

調用：

declare 
  v_rent_rows SYS_REFCURSOR;
  v_rent_row tb_rent % rowType;
begin 
   proc_query_rent("山區", null, null, 1200, null, v_rent_rows);
   Dbms_output.put_line("所在區 室 廳 租金");
   loop 
      fetch v_rent_rows into v_rent_row;//單行
      exit when v_rent_rows % NOTFOUND;
      Dbms_output.put_line(v_rent_row.region || "  " || v_rent_row.room || "  " || v_rent_row.hall || "  " || v_rent_row.rent);
   end loop;
   close v_rent_rows;
end;

2、返回多行語法

存儲過程：

create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type,  out_curEmp out SYS_REFCURSOR) as 
begin 
 open out_curEmp for 
  SELECT * FROM emp WHERE deptno = in_deptNo ; 
EXCEPTION 
 WHEN OTHERS THEN 
 RAISE_APPLICATION_ERROR(-20101, 
  "Error in getEmpByDept" || SQLCODE ); 
end getEmpByDept;

調用（執行存儲過程）：

declare 
  cur_emp sys_refcursor;
  type emp emp_type is table of yemp%rowtype;
  vemps emp_type;
begin
   sp_getEmp(line=>"A5",curemp=>cur_emp);
   fetch cur_emp bulk collect into vemps;
   for i in v_emps.first..v_emps.last loop
      dbms_output.putline(v_emps(i).empid);
   end loop;
   close cur_emp;
end;

C# 調用：

OracleCommand cmd = new OracleCommand("prroc_demo", myOracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_companycode", OracleType.Char).Value = "aa";
cmd.Parameters.Add("curEmp", OracleType.Cursor).Direction = ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];

四、維護存儲過程

1、刪除過程

可以使用DROP PROCEDURE命令對不需要的過程進行刪除

DROP PROCEDURE logexecution;

2、顯示過程代碼

select text from user_source where name="存儲過程名(大寫)" and type="PROCEDURE";

3、查看過程狀態

select  object_type ,object_name ,status from user_objects where  object_name  = "procedure";

4、重新編譯過程

alter procedure pro_backup compile;

五. 過程與函數比較

1、相同點：

• 都使用IN模式的參數傳入數據、OUT模式的參數返回數據。
• 輸入參數都可以接受默認值，都可以傳值或傳引導。
• 調用時的實際參數都可以使用位置表示法、名稱表示法或組合方法。
• 都有聲明部分、執行部分和異常處理部分。
• 其管理過程都有創建、編譯、授權、刪除、顯示依賴關系等。

2、不同點：

• 過程：作為PL/SQL語句執行；函數：作為表達式的一部分執行
• 過程：在規范中不包含RETURN子句；函數：必須在規范中包含RETURN子句
• 過程：不返回任何值；函數：必須返回單個值
• 過程：可以RETURN語句，但是與函數不同，它不能用于返回值；函數：必須包含至少一條RETURN語句

六、 與過程相關數據字典

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,

ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

相關的權限:

CREATE ANY PROCEDURE

DROP ANY PROCEDURE

SQL*PLUS 中，可以用DESCRIBE 命令查看過程的名字及其參數表。

DESC[RIBE] Procedure_name;

到此這篇關于Oracle在PL/SQL中使用存儲過程的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持。