PL/SQL之高级篇
原文地址：http://www.cnblogs.com/sin90lzc/archive/2012/08/30/2661117.html
參考文獻：《Oracle完全學習手冊》
1.概述
本文主要介紹PL/SQL中的有名程序塊：存儲過程、函數、包頭/包體及觸發器的使用。而這些的基礎是PL/SQL無名塊的編寫，這在PL/SQL之基礎篇?中已經講述過了。
這四種程序塊到底是什么東西呢？
存儲過程可以認為它是一個無返回值的函數（排除參數返回）；
函數則是一個帶返回值的函數，但函數只能用于表達式中，不能像存儲過程那樣直接調用；
包頭/包體與Java中的接口/實現類比較的話，包頭就好比接口，而包體就好比實現類。
觸發器則可以認為是一個事件處理函數，它能在某個事件發生時自動地執行。
2.存儲過程
2.1存儲過程的創建及修改
語法：
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] datatype [{(:= | DEFAULT ) defaultvalue}] [, ...])] {IS | AS} BEGIN procedure_body END procedure_name;語法解析：
IN:輸入參數；
OUT:輸出參數；
IN OUT：即可輸入也可輸出的參數；
datatype:參數的數據類型，此處不能帶精度。
:=|DEFAULT：用于設置參數的默認值。
?
Example:
--注意，在聲明參數類型時，不能帶精度，如VARCHAR2(20)后面的(20) CREATE OR REPLACE PROCEDURE test_procedure(name IN VARCHAR2 default 'Tim',age IN NUMBER :=20,sex IN OUT VARCHAR2,realname OUT VARCHAR2) ISrn VARCHAR2(10) default 'Tim Leung'; BEGINIF name='Tim' THENrealname:=rn;END IF;IF sex='1' THENsex:='男';ELSEsex:='女';END IF;DBMS_OUTPUT.PUT_LINE(name || ' age:'||age||' sex:'||sex||' realname:'||realname); END test_procedure;?
2.2 過程的調用
declare result_sex varchar(3):='1';result_rn varchar(10) default null; begintest_procedure('Tim',sex => result_sex,age => 20,realname => result_rn);--可以使用符號=>來傳參DBMS_OUTPUT.PUT_LINE('sex:'||result_sex);DBMS_OUTPUT.PUT_LINE('realname:'||result_rn); end;/*運行結果: Tim age:20 sex:男 realname:Tim Leung sex:男 realname:Tim Leung */?
2.3 刪除過程
DROP PROCEDURE test_procedure;?
3.函數
過程用來完成一項任務，可能不返回值，也可能返回多個值，過程的調用是一條PL/SQL語句；函數包含RETURN子句，用來進行數據操作，并返回一個單獨的函數值，函數的調用只能在一個表達式中。
3.1創建及修改函數
語法：
CREATE [OR REPLACE] FUNCTION function_name [(parameter1 {IN | OUT | IN OUT} datatype] [,...])] RETURN datatype { IS | AS } BEGIN function_body END function_name;?
Example:
CREATE OR REPLACE FUNCTION test_function(name IN VARCHAR2 default 'Tim',age IN NUMBER :=20,sex IN OUT VARCHAR2,realname OUT VARCHAR2) RETURN VARCHAR2 ISrn VARCHAR2(10) default 'Tim Leung'; BEGINIF name='Tim' THENrealname:=rn;END IF;IF sex='1' THENsex:='男';ELSEsex:='女';END IF;RETURN (name || ' age:'||age||' sex:'||sex||' realname:'||realname); END test_function;?
3.2調用函數
declare result_sex varchar(3):='1';result_rn varchar(10) default null; begin--可以使用符號=>來傳參DBMS_OUTPUT.PUT_LINE(test_function('Tim',sex => result_sex,age => 20,realname => result_rn)); end;/*運行結果: Tim age:20 sex:男 realname:Tim Leung */?
?
4.包頭/包體
4.1包頭創建及修改
語法:
CREATE [OR REPLACE] PACKAGE package_name { IS | AS } package_specification --存儲過程，函數，變量聲明部分 END package_name;?
Example:
CREATE OR REPLACE PACKAGE test_package IS v_name VARCHAR2(20);--聲明公有變量 PROCEDURE getName(id NUMBER);--聲明過程getName PROCEDURE getName(p_name VARCHAR2);--聲明重載過程getName FUNCTION getAge RETURN NUMBER;--聲明函數getAge END test_package;?
4.2包體的創建及修改
語法：
--包體名必須與包頭名一致！ CREATE [OR REPLACE] PACKAGE BODY package_name { IS | AS}package_implement --存儲過程，函數實現部分BEGINpackage_init --包初始化部分，在包被第一次調用時執行END; --注意！！！包體不能有END;子句?
Example:
CREATE OR REPLACE PACKAGE BODY test_package IS --包體名與包頭名要一致/*創建私有過程output，以供其他過程或函數調用，必須在其他過程或函數調用前創建*/PROCEDURE output(output_str VARCHAR2) ISBEGINDBMS_OUTPUT.PUT_LINE(output_str);END output;/*創建公有過程getName*/PROCEDURE getName(id NUMBER) ISBEGINoutput('do getName('||id||')');END getName;/*重載過程getName，重載的意思即有相同的過程名或函數名，相同的返回類型，不同的參數列表！*/PROCEDURE getNAME(p_name VARCHAR2) ISBEGINoutput('do getNAME('||p_name|| ')!');END getNAME;/*創建函數getAge*/FUNCTION getAge RETURN NUMBER ISBEGINRETURN 20;END getAge;/*包的初始化塊，在包被第一次調用時執行。初始化塊只能在包體的末尾編寫*/BEGINv_name:='Tim';END;--注意！！包體不能有END;子句?
4.3 包的調用
declare beginDBMS_OUTPUT.PUT_LINE(test_package.v_name);test_package.getName(10);test_package.getName('Tim');DBMS_OUTPUT.PUT_LINE('getAge:' || test_package.getAge); end;/*運行結果: Tim do getName(10) do getNAME(Tim)! getAge:20 */?
5.觸發器
5.1觸發器的分類
觸發器主要有DML觸發器、替代觸發器、系統觸發器及DDL觸發器幾種類型。
- DML觸發器
DML觸發器可以處理INSERT、UPDATE和DELETE事件。DML觸發器可以在語句級或行級操作上被觸發，語句級觸發器對于每一個SQL語句只觸發一次，行級觸發器對SQL語句受影響的表中的每一行都觸發一次 - 替代觸發器
替代觸發器主要針對處理視圖的DML操作事件（INSERT、UPDATE和DELETE）。替代觸發器只能在行級操作上被觸發，不能在語句級上被觸發。 - 系統觸發器
分為數據庫級(Database)和模式級(Schema)兩種。數據庫級觸發器的觸發事件對于所有用戶都有效，模式級觸發器僅被指定模式的用戶觸發。系統觸發器支持的觸發事件有：LOGON、LOGOFF、SERVERERROR、STARTUP和SHUTDOWN。 - DDL觸發器
即由DDL語句（CREATE、ALTER或DROP等）觸發的觸發器。
5.2創建及修改觸發器
語法：
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} trigger_event ON event_target [FOR EACH ROW] [WHEN condition] BEGINtrigger_body END trigger_name;?
語法解析：
INSTEAD OF:僅用于替代觸發器，即event_target必須為視圖，而且必須帶FOR EACH ROW子句
trigger_event：觸發的事件。可以是DML事件(INSERT、UPDATE、DELETE、INSERT OR UPDATE、UPDATE OF column_name)，系統事件(STARTUP、SHUTDOWN等)。
event_target：即指trigger_event事件是發生在哪個對象上的。如DML事件，那么event_target應該是表名或視圖名，如果是系統事件，那么event_target就應該是DATABASE或SCHEMA
FOR EACH ROW:如果帶此子句，即表示該觸發器是行級觸發器，如果省略，則是語句級觸發器。
WHEN condition?：用于限制行級觸發器，只有滿足condition條件，才會觸發觸發器。
Example:
CREATE OR REPLACE TRIGGER test_trigger AFTER INSERT OR UPDATE OF sal OR DELETE ON emp FOR EACH ROW BEGINCASEWHEN INSERTING THEN --當事件為INSERT時，INSERTING為TRUE/*:NEW關鍵字可以獲取新記錄的數據，它只能用于行級觸發器*/DBMS_OUTPUT.PUT_LINE('INSERT INTO EMP VALUES('||:NEW.empno||','||:NEW.ename||','||:NEW.job||','||:NEW.mgr||','||:NEW.hiredate||','||:NEW.sal||','||:NEW.comm||','||:NEW.deptno||')');WHEN UPDATING THEN --當事件為UPDATE時，UPDATING為TRUEDBMS_OUTPUT.PUT_LINE('UPDATING emp which empno='||:OLD.empno||'to sal:'||:NEW.sal);--:OLD關鍵字可以獲取舊記錄的數據，它只能用于行級觸發器WHEN DELETING THEN --當事件為DELETE時，DELETING為TRUEDBMS_OUTPUT.PUT_LINE('DELETING emp which empno='||:OLD.empno);--:OLD關鍵字可以獲取舊記錄的數據，它只能用于行級觸發器ELSENULL;END CASE; EXCEPTIONWHEN DUP_VAL_ON_INDEX THENNULL;WHEN OTHERS THENRAISE_APPLICATION_ERROR(SQLCODE,SQLERRM); END test_trigger;?
執行DML操作觸發上面的觸發器，效果如下：
5.3修改觸發器的狀態
觸發器有ENABLED和DISABLED兩種狀態。
修改某個觸發器的狀態可以執行下面的SQL:
ALTER TRIGGER trigger_name ENABLE | DISABLE;?
使某個表上的所有觸發器有效或無效:
ALTER TABLE table_name {ENABLE | DISABLE} ALL TRIGGERS;?
5.4刪除觸發器
DROP TRIGGER trigger_name;轉載于:https://www.cnblogs.com/m-xy/p/3276838.html
總結
以上是生活随笔為你收集整理的PL/SQL之高级篇的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Cocos2D 添加 UIView
- 下一篇： 最短路打印路径