程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle學習筆記十二 子程序(存儲過程、自定函數)和程序包,oracle學習筆記

Oracle學習筆記十二 子程序(存儲過程、自定函數)和程序包,oracle學習筆記

編輯:Oracle教程

Oracle學習筆記十二 子程序(存儲過程、自定函數)和程序包,oracle學習筆記


子程序

子程序:命名的 PL/SQL 塊,編譯並存儲在數據庫中。   子程序的各個部分: 1.聲明部分 2.可執行部分 3.異常處理部分(可選) 子程序的分類: 1.過程 - 執行某些操作 2.函數 - 執行操作並返回值

 

子程序的優點:

  模塊化     將程序分解為邏輯模塊   可重用性     可以被任意數目的程序調用   可維護性     簡化維護操作   安全性     通過設置權限,使數據更安全

 存儲過程

過程是用於完成特定任務的子程序。 例如:

 

 一個購票過程可以分為很多個子過程,分別完成。

創建存儲過程

創建過程的語法:

CREATE [OR REPLACE] PROCEDURE
   <procedure name> [(<parameter list>)]
IS|AS
   <local variable declaration>
BEGIN
   <executable statements>
[EXCEPTION
   <exception handlers>]
END;

 

CREATE OR REPLACE PROCEDURE  find_emp
  (emp_no NUMBER)
AS
  empname VARCHAR2(20);
BEGIN
  SELECT ename INTO empname
  FROM EMP WHERE empno = emp_no;
  DBMS_OUTPUT.PUT_LINE('雇員姓名是 '|| empname);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('雇員編號未找到');
END find_emp;

 

 

我們還可以:

--查詢用戶所定義的存儲過程
select distinct name from user_source where type = 'PROCEDURE';

--查看存儲過程定義的源碼內容(PL/SQL語句)
select text from user_source where name = 'P_TEST';

--如果定義有錯誤,查看錯誤原因
Show error procedure 存儲過程名

--刪除存儲過程
Drop procedure 存儲過程名;

 

 

過程參數的三種模式:

IN 1.用於接受調用程序的值 2.默認的參數模式 OUT 1.用於向調用程序返回值 IN OUT 1.用於接受調用程序的值,並向調用程序返回更新的值  

--統計滿足指定工資數的員工的數量:帶輸入參數in的存儲過程 

Create or replace procedure p_total_sal(var_sal in int) is 
    Var_count int;
Begin
    Select count(*) into var_count from emp where sal > var_sal;
    Dbms_output.put_line(' 符合要求的員工總數為: ' || var_count);
Exception
    When others then
        Dbms_output.put_line('未知錯誤');
End;

 

--定義一個存儲過程返回指定部門的員工總數:帶返回值out的存儲過程

Create or replace procedure p_get_emp(var_deptno int, var_total out int) as 
  Var_n int;
Begin
    Select count(*) into var_n from emp where deptno = var_deptno;
    Var_total := var_n;  --總數由參數返回
End;

 

--使用
Declare
  Var_s int;
Begin
  p_get_emp(10, var_s);
  Dbms_output.put_line('返回的值為' || var_s);
End;

 

--定義一個存儲過程,通過該存儲過程能返回一個結果集(游標)。

Create or replace procedure p_get_datas(mycur out sys_refcursor) is 
Begin
    Open mycur for select * from emp where deptno = 10;
End;

 

 

--調用:
Declare
    Var_cur sys_refcursor;  --接收參數
    Row emp%rowtype;
Begin
    p_get_datas(var_cur);
    --無需再次打開,因為在存儲過程中已經打開過了
    Loop
        Fetch var_cur into row;
        Exit when var_cur%notfound;
            Dbms_output.put_line(row.ename || ' ' || row.job);
    End loop;
End;

--輸入輸出參數

--根據員工編號返回他的工資的存儲過程
Create or replace procedure p_get_sal(var_n in out int) is
Begin
    Select sal into var_n from emp where empno = var_n);
End;

 

--調用:
Declare
    Var_s int;
Begin
    Var_s := &n;
    p_get_sal(var_s);
    Dbms_output.put_line('他的工資為:' || var_s);
End;

 

 

 存儲過程的使用

 

--存儲過程的使用

1 命令方式:execute 存儲過程名;

2 在PL/SQL中:直接使用 存儲過程名 即可

 

調用存儲過程時傳遞參數的方式。
    1、按照位置方式傳遞。

Swap(num1,num2);
    2、按名稱方式傳遞。 

swap(p2=>num2,p1=>num1);  

(p1,p2是定義存儲過程時參數名字)

 

將過程的執行權限授予其他用戶:

GRANT EXECUTE ON find_emp TO MARTIN;
GRANT EXECUTE ON swap TO PUBLIC;(所有數據庫用戶)

 

 

函數

函數是可以返回值的命名的 PL/SQL 子程序。 創建函數的語法:
CREATE [OR REPLACE] FUNCTION
  <function name> [(param1,param2)]
RETURN <datatype>  IS|AS
  [local declarations]
BEGIN
  Executable Statements;
  RETURN result;
  EXCEPTION
    Exception handlers;
END;

 

定義函數的限制:   函數只能接受 IN 參數,而不能接受 IN OUT 或 OUT 參數   形參不能是 PL/SQL 類型   函數的返回類型也必須是數據庫類型 訪問函數的兩種方式:   使用 PL/SQL 塊   使用 SQL 語句  

創建函數

CREATE OR REPLACE FUNCTION fun_hello
  RETURN  VARCHAR2
IS
BEGIN
  RETURN '朋友,您好';
END;
從 SQL 語句調用函數:
SELECT fun_hello FROM DUAL;

 

 

CREATE OR REPLACE FUNCTION 
  item_price_range (price NUMBER) 
RETURN VARCHAR2 AS
  min_price NUMBER;
  max_price NUMBER;
BEGIN
  SELECT MAX(ITEMRATE), MIN(ITEMRATE)
  INTO max_price, min_price
  FROM itemfile;
  IF price >= min_price AND price <= max_price 
  THEN
    RETURN '輸入的單價介於最低價與最高價之間';
  ELSE
    RETURN '超出范圍';
  END IF;
END;

 

DECLARE
  P NUMBER := 300;
  MSG VARCHAR2(200);
BEGIN
  MSG := item_price_range(300);
  DBMS_OUTPUT.PUT_LINE(MSG);
END;

 

 

過程和函數的比較

過 程

函  數

作為 PL/SQL 語句執行

作為表達式的一部分調用

在規格說明中不包含  RETURN 子句

必須在規格說明中包含 RETURN 子句

不返回任何值

必須返回單個值

可以包含 RETURN 語句,但是與函數不同,它不能用於返回值

必須包含至少一條 RETURN
語句

 

 程序包

程序包是對相關過程、函數、變量、游標和異常等對象的封裝,程序包由規范和主體兩部分組成。

 

 

程序包規范

CREATE [OR REPLACE]
  PACKAGE 
  package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];

程序包主體

CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];

 

 

CREATE OR REPLACE PACKAGE pack_me
IS
  PROCEDURE order_proc (orno VARCHAR2);
  FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2;
END pack_me;

 

 

CREATE OR REPLACE PACKAGE BODY pack_me AS
  PROCEDURE order_proc (orno VARCHAR2) IS
    stat CHAR(1);
  BEGIN
    SELECT ostatus INTO stat FROM order_master
    WHERE orderno = orno;
    ……
  END order_proc;
  FUNCTION order_fun(ornos VARCHAR2)
  RETURN VARCHAR2
  IS
    icode   VARCHAR2(5);
    ocode   VARCHAR2(5);
  BEGIN
  ……
  END order_fun;
END pack_me;

 

程序包的優點

  • 模塊化
  • 更輕松的應用程序設計
  • 信息隱藏
  • 新增功能(過程可以重載,可以定義公用變量或游標)
  • 性能更佳

程序包中的游標

游標的定義分為游標規范和游標主體兩部分。在包規范中聲明游標規范時必須使用 RETURN 子句指定游標的返回類型。   RETURN子句指定的數據類型可以是: 1.用 %ROWTYPE 屬性引用表定義的記錄類型 2.程序員定義的記錄類型  
CREATE OR REPLACE PACKAGE cur_pack IS
  CURSOR ord_cur(vcode VARCHAR2)
    RETURN order_master%ROWTYPE;
  PROCEDURE ord_pro(vcode VARCHAR2);
END cur_pack;

 

 

CREATE OR REPLACE PACKAGE BODY cur_pack AS
 CURSOR ord_cur(vcode VARCHAR2)
 RETURN order_master%ROWTYPE IS 
 SELECT * FROM order_master WHERE VENCODE=vcode;
 PROCEDURE ord_pro(vcode VARCHAR2) IS
   or_rec order_master%ROWTYPE;
 BEGIN
  OPEN ord_cur(vcode); 
  LOOP
    FETCH ord_cur INTO or_rec;
    EXIT WHEN ord_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LIne('返回的值為' || or_rec.orderno);
  END LOOP;
 END ord_pro;
END cur_pack;

 

 

USER_OBJECTS 視圖包含用戶創建的子程序和程序包的信息
SELECT object_name, object_type
  FROM USER_OBJECTS
  WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');

 

 

USER_SOURCE 視圖存儲子程序和程序包的源代碼
SELECT line, text FROM USER_SOURCE WHERE NAME='TEST';

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved