PLSQL.ppt

1、PLSQL,主要內(nèi)容,PL/SQL的組成 條件控制 循環(huán)控制 異常處理 記錄類型,什么是PL/SQL?,問題： 標準化的SQL是針對數(shù)據(jù)庫進行操作的語言，每次只能執(zhí)行一條語句，語句以英文的分號“；”為 結(jié)束標識； 數(shù)據(jù)庫在進行后臺數(shù)據(jù)管理的同時，需要一定的根據(jù)業(yè)務邏輯的需求， 進行較為復雜的管理，但較為復雜的管理都需要變成語言進行實現(xiàn)，結(jié)構(gòu)化變成語言對數(shù)據(jù)庫的支持能力較弱。 PL/SQL不是一個獨立的產(chǎn)品。PL/SQL程序塊只能在SQL PLUS、SQL PLUS Worksheet等工具支持下以解釋型方式執(zhí)行，不能編譯成可執(zhí)行文件，脫離支撐環(huán)境執(zhí)行。 Oracle中PL/SQL的例子：C:o

2、racleora90plsqldemo,PL/SQL簡介,PL/SQL(Procedural Language/SQL) 是對SQL的擴展 支持多種數(shù)據(jù)類型，如大對象和集合類型，可使用條件和循環(huán)等控制結(jié)構(gòu) 可用于創(chuàng)建存儲過程、觸發(fā)器和程序包，給SQL語句的執(zhí)行添加程序邏輯 支持Oracle的大型對象和集合；,PL/SQL 的體系結(jié)構(gòu),PL/SQL 引擎駐留在 Oracle 服務器中 該引擎接受 PL/SQL 塊并對其進行編譯執(zhí)行,將PL/SQL 塊發(fā)送給 Oracle 服務器,用戶,執(zhí)行過程語句,引擎將 SQL 語句發(fā)送給SQL 語句執(zhí)行器,執(zhí)行 SQL 語句,將結(jié)果發(fā)送給用戶,PL/SQL的

3、組成,PL/SQL塊的組成 PL/SQL語言以塊為單位，塊中可以嵌套子塊。 一個基本的PL/SQL塊由3部分組成：定義部分（DECLARE），可執(zhí)行部分（BEGIN），異常處理部分（EXCEPTION）。,定義部分,執(zhí)行部分,例外處理部分,PL/SQL塊,HELLO PL/SQL,DECLARE X VARCHAR2(20) ; BEGIN -注釋 X:=HELLO PL/SQL ; DBMS_OUTPUT.PUT_LINE(X) ; END;,屏幕上沒有顯示結(jié)果時應打開SERVEROUT開關(guān) SET SERVEROUT ON SIZE 10000,賦值賦號為：=。 格式： ：= ；,每個標識

4、符必須以字母開頭，而且不分大小寫。如果定義的標識符不能為空，則必須加上關(guān)鍵字NOT NULL,并賦初值。 X VARCHAR2(20) NOT NULL :=HELLO PL/SQL;,PL/SQL的組成,PL/SQL塊的定義部分： 與C語言類似，PL/SQL中使用的變量、常量、游標和異常處理的名字都必須先定義后使用。并且必須定義在以DECLARE關(guān)鍵字開頭的定義部分。 PL/SQL塊的可執(zhí)行部分： 該部分是PL/SQL塊的主體，包含該塊的可執(zhí)行語句。該部分定義了塊的功能，是必須的。 由關(guān)鍵字BEGIN開始，EXCEPTION或END結(jié)束。 PL/SQL塊的異常處理部分： 該部分包含該塊的異常

5、處理程序（錯誤處理程序）。當該塊程序體中的某個語句出現(xiàn)異常（檢測到一個錯誤）時，他將程序控制轉(zhuǎn)到異常部分的相應的異常處理程序中進行進一步的處理。該部分由關(guān)鍵字EXCEPTION開始，END關(guān)鍵字結(jié)束。,數(shù)據(jù)類型,內(nèi)置數(shù)據(jù)類型 標量 復合 引用 LOB,標量類型,標量 容納單個值 沒有內(nèi)部組成 分為四個類別 NUMBER CHARACTER DATE BOOLEAN,Number類型,用于存儲和操縱數(shù)字數(shù)據(jù) Number類型是： BINARY_INTEGER NUMBER 為了和其他數(shù)據(jù)庫兼容，Oracle支持以下子類型 在內(nèi)部這些子類型都轉(zhuǎn)換為NUMBER類型 子類型是DEC、DECIMAL、

6、DOUBLE PRECISION、FLOAT、INTEGER、INT、NUMERIC、REAL、SMALLINT PLS_INTEGER,Character類型,Character類型 CHAR VARCHAR VARCHAR2 RAW LONG、LONG RAW ROWID、UROWID 區(qū)域字符類型 NCHAR NVARCHAR2,BOOLEAN,不能把數(shù)據(jù)庫中的字段定義為BOOLEAN 只能用于PLSQL中進行邏輯判斷,其他數(shù)據(jù)類型,組合類型 RECORD VARRAY NESTED TABLE 引用類型 REF CURSOR REF操作符 LOB類型 BLOB CLOB NCLOB B

7、FILE,常見數(shù)據(jù)類型,條件控制之IF語句,IF 條件 THEN ELSE END IF;,執(zhí)行流程,IF 條件1為真 THEN ELSIF 條件2為真 THEN ELSE END IF;,plsql_02.txt,條件控制之IF語句,CASE 條件 WHEN 條件1為真 THEN WHEN 條件2為真 THEN ELSE END CASE;,plsql_03.txt,循環(huán)控制之LOOP,直到型循環(huán) 語法格式： LOOP EXIT WHEN ； END LOOP; 執(zhí)行過程： 先執(zhí)行循環(huán)體，然后判斷，如果條件為真，則結(jié)束循環(huán)，否則繼續(xù)循環(huán)。 說明： 直到型循環(huán)的循環(huán)體至少執(zhí)行1次。,求1到10

8、0之和,VARIABLE SUM NUMBER ; DECLARE I NUMBER NOT NULL :=0 ; BEGIN :SUM:=0 ; LOOP :SUM:=:SUM+I ; EXIT WHEN I=100 ; I:=I+1; END LOOP ; DBMS_OUTPUT.PUT_LINE(SUM IS |:SUM) ; END;,全局變量的引用時，必須加:,plsql_04.txt,循環(huán)控制之WHILE,當型循環(huán) 語法格式： WHILE LOOP END LOOP; 執(zhí)行過程： 先判斷，如果條件為真，則執(zhí)行循環(huán)體，繼續(xù)循環(huán)，否則結(jié)束循環(huán)。 說明： 當型循環(huán)的循環(huán)體可能一次也不執(zhí)行

9、。,求1到100之和,DECLARE I NUMBER :=100 ; SUMM NUMBER := 0 ; BEGIN WHILE I0 LOOP SUMM:=SUMM+I; I:=I-1; END LOOP; DBMS_OUTPUT.PUT_LINE(SUMM); END;,plsql_05.txt,循環(huán)控制之FOR,FOR循環(huán) 語法格式： FOR IN REVERSE LOOP END LOOP; 說明： 循環(huán)變量是控制循環(huán)的變量，它不需要顯式的在變量定義部分進行定義。系統(tǒng)隱含地將它看成一個整型變量。 系統(tǒng)默認時，計數(shù)器從下界往上界遞增計數(shù)，如果使用REVERSE關(guān)鍵字，則表示計數(shù)器從下

10、界到上界遞減計數(shù)。 循環(huán)變量只能在循環(huán)體中使用，不能在循環(huán)體外使用。,求1到100之和,DECLARE SUMM NUMBER := 0 ; BEGIN FOR I IN REVERSE 1.100 LOOP SUMM:=SUMM+I; DBMS_OUTPUT.PUT_lINE(I is |I); END LOOP; DBMS_OUTPUT.PUT_lINE(SUMM); END;,plsql_06.txt,求出100到150之間所有的素數(shù),家庭作業(yè),求斐波拉契數(shù)列的前n項之和；,標記和goto語句結(jié)合,DECLARE SUMM NUMBER := 0 ; I NUMBER :=0 ; BEG

11、IN SUMM:=SUMM+I; I:=I+3; IF I100 THEN GOTO REPEAT1; END IF ; DBMS_OUTPUT.PUT_lINE(SUMM); END;,PL/SQL中的異常處理,PL/SQL中的異常處理 預定義異常 對于Oracle預定義的異常，當預定義的情況發(fā)生時，系統(tǒng)將自動觸發(fā)。 用戶自定義的異常 需要程序員自己定義代碼，對異常情況進行處理。 異常處理的一般格式： DECLARE ; BEGIN ; EXCEPTION WHEN 異常情況1 OR 異常情況2 THEN ; WHEN異常情況3 OR 異常情況4 THEN ； WHEN OTHERS THE

12、N ； END;,異常舉例,DECLARE TMP_NAME VARCHAR(10); BEGIN SELECT ENAME INTO TMP_NAME FROM EMP; DBMS_OUTPUT.PUT_LINE(TMP_NAME) ; END;,ERROR 位于第 1 行: ORA-01422: 實際返回的行數(shù)超出請求的行數(shù) ORA-06512: 在line 4,SQL語句的使用 在可執(zhí)行部分，可以使用SQL語句，但是不是所有的SQL語句都可以使用。可以使用的主要有：SELECT,INSERT,UPDATE,DELETE,COMMIT,ROLLBACK等數(shù)據(jù)查詢、數(shù)據(jù)操縱或事務控制命令，不

13、能使用CREATE,ALTER,DROP,GRANT,REVOKE等數(shù)據(jù)定義和數(shù)據(jù)控制命令。 說明 在PL/SQL中，SELECT語句必須與INTO子句相配合，在INTO子句后面跟需要賦值的變量. 在使用SELECT INTO時，結(jié)果只能有一條，如果返回了多條數(shù)據(jù)或沒有數(shù)據(jù)，則將產(chǎn)生錯誤。（對于多條記錄的遍歷，可以使用游標）,plsql_exception_01.txt,預定義的異常處理,預定義異常的處理,DECLARE TMP_NAME VARCHAR(10); BEGIN SELECT ENAME INTO TMP_NAME FROM EMP; DBMS_OUTPUT.PUT_LINE(T

14、MP_NAME) ; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(返回了太多行) ; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(出現(xiàn)了其他異常) ; END;,plsql_exception_02.txt,用戶自定義的異常處理,說明 用戶自定義異常必須在定義部分進行聲明。 當異常發(fā)生時，系統(tǒng)不能自動觸發(fā)，需要用戶使用RAISE語句。 示例 DECLARE OUT_OF_STOCK EXCEPTION; NUMBER_ON_HAND NUMBER; BEGIN IF NUMBER_ON_HAND

15、1 THEN RAISE OUT_OF_STOCK; END IF; EXCEPTION WHEN OUT_OF_STOCK THEN -HANDLE THE ERROR END;,用戶自定義的異常處理,DECLARE TMP_NAME VARCHAR(10); MY_EXCEPTION EXCEPTION ; BEGIN SELECT ENAME INTO TMP_NAME FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.PUT_LINE(TMP_NAME) ; IF TMP_NAME LYF THEN RAISE MY_EXCEPTION ; END IF ;

16、 EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(返回了太多行) ; WHEN MY_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE(該用戶不是lyf) ; END;,plsql_exception_03.txt,變量的聲明,變量的聲明 聲明一個變量，使它的類型與某個變量或數(shù)據(jù)庫基本表中某個列的數(shù)據(jù)類型一致。（不知道該變量或列的數(shù)據(jù)類型）可以使用 %TYPE；,DECLARE MY_NAME VARCHAR(10); TMP_NAME MY_NAME%TYPE; -TMP_NAME EMP.ENAME%TYP

17、E; BEGIN SELECT ENAME INTO TMP_NAME FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.PUT_LINE(TMP_NAME) ; END;,plsql_var_01.txt,記錄類型,記錄是由幾個相關(guān)值構(gòu)成的復合變量，常用于select的返回值。使用記錄可以把一行數(shù)據(jù)看成是一個單元來處理。 記錄類型定義的一般格式： TYPE IS RECORD ( NOT NULLDEFAULT|:= , )； 說明 標識符 是定義的記錄類型名; 定義記錄型變量， my_record_var recordtypename ; 記錄類型變量的屬性引用

18、方法是.引用。,字段1（數(shù)據(jù)類型）,字段2（數(shù)據(jù)類型）,字段3（數(shù)據(jù)類型）,記錄類型,DECLARE TYPE MY_RECORD IS RECORD( NO NUMBER(4), NAME VARCHAR(10) ); MY_VAR MY_RECORD; BEGIN SELECT EMPNO,ENAME INTO MY_VAR FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.PUT_LINE(編號：|MY_VAR.NO| 姓名：|MY_VAR.NAME) ; END;,plsql_var_02.txt,記錄類型,DECLARE MY_VAR EMP%ROWTYP

19、E; BEGIN SELECT * INTO MY_VAR FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.PUT_LINE(編號：|MY_VAR.EMPNO| 姓名：|MY_VAR.ENAME) ; END;,plsql_var_03.txt,記錄注意事項,RECORD存儲單行多列結(jié)構(gòu)的數(shù)據(jù)，必須首先定義該RECORD類型，然后再定義一個屬于該類型的變量。 一個PL/SQL RECORD數(shù)據(jù)類型與一個數(shù)據(jù)庫表中的行不一樣； 一個PL/SQL RECORD在結(jié)構(gòu)上與第三代語言中的記錄非常相似。 一個PL/SQL RECORD必須包含一個或多個字段：這些字段的數(shù)據(jù)類

20、型可以是標量類型、RECORD類型或PL/SQL表類型 一個PL/SQL RECORD允許用戶將這些字段的集合看成一個邏輯單元； 一般在PL/SQL塊中從表中取出一行進行處理時用PL/SQL RECORD。,主要內(nèi)容,游標的設(shè)計與開發(fā) 存儲過程 包 觸發(fā)器,游標的定義,游標的定義 游標（cursor）是Oracle系統(tǒng)在內(nèi)存中開辟的一個工作區(qū)，在其中存放SELECT語句返回的查詢結(jié)果。 說明 使用游標時，select語句查詢的結(jié)果可以是單條記錄，多條記錄，也可以是零條記錄。 游標工作區(qū)中，存在著一個指針（POINTER）,在初始狀態(tài)它指向查詢結(jié)果的首記錄。 要訪問查詢結(jié)果的所有記錄，可以通過F

21、ETCH語句，進行指針的移動來實現(xiàn)。 使用游標進行操作，包括定義游標、打開游標、提取數(shù)據(jù)以及關(guān)閉游標幾步。,打開游標.,從游標中獲取一行.,繼續(xù)獲取直到為空.,關(guān)閉游標.,控制顯式游標,游標的使用,定義游標，PL/SQL塊中，游標的定義應該放在定義部分 語法格式：CURSOR IS ; 打開游標 語法格式：OPEN ； 說明：打開游標，實際上是執(zhí)行游標定義時對應的SELECT語句，將查詢結(jié)果檢索到工作區(qū)中。 提取數(shù)據(jù) 語法格式：FETCH INTO 變量1,變量2, 說明 在使用FETCH語句之前必須先打開游標，這樣才能保證工作區(qū)中有數(shù)據(jù)。 對游標第一次使用FETCH語句時，游標指針指向第一條

22、記錄，因此操作的對象是第一條記錄，使用后，游標指針指向下一條記錄。 游標指針只能向下移動，不能回退。如果想查完第二條記錄后又回到第一條記錄，則必須關(guān)閉游標，然后重新打開游標。 INTO子句中的變量個數(shù)、順序、數(shù)據(jù)類型必須與工作區(qū)中每行記錄的字段數(shù)、順序以及數(shù)據(jù)類型一一對應。 關(guān)閉游標 語法格式：CLOSE ； 說明：關(guān)閉游標的作用在于，使游標所對應的內(nèi)存工作區(qū)變?yōu)闊o效，并釋放與游標相關(guān)的系統(tǒng)資源。,游標的屬性,游標的屬性 %ISOPEN 該屬性是布爾型。如果游標已經(jīng)打開，返回TRUE,否則為FALSE。 %FOUND 布爾型，如果最近一次使用FETCH語句，有返回結(jié)果則為TRUE,否則為FAL

23、SE; %NOTFOUND 布爾型，如果最近一次使用FETCH語句,沒有返回結(jié)果則為TRUE,否則為FALSE; %ROWCOUNT 數(shù)值型，描述的是到目前為止實際從游標工作區(qū)抽取的記錄數(shù)。 游標屬性只能在PL/SQL塊中使用，不能在SQL命令中使用。,游標的使用,DECLARE CURSOR CUR IS SELECT * FROM EMP; MY_EMP EMP%ROWTYPE; BEGIN OPEN CUR; FETCH CUR INTO MY_EMP; WHILE CUR%FOUND LOOP DBMS_OUTPUT.PUT_LINE(編號：|MY_EMP.EMPNO| 姓名：|MY_

24、EMP.ENAME) ; FETCH CUR INTO MY_EMP; END LOOP; CLOSE CUR; END;,plsql_cur_01.txt,游標練習,修改表emp中各個雇員的工資，若雇員屬于10號部門，則增加$100，若雇員屬于20號部門，則增加$200；若雇員屬于30號部門，則增加$300。,plsql_cur_02.txt,FOR循環(huán)中游標的使用,語法格式 FOR IN LOOP END LOOP; 說明 系統(tǒng)自動打開游標，不用顯式地使用OPEN語句打開； 系統(tǒng)隱含地定義了一個數(shù)據(jù)類型為%ROWTYPE的變量，并以此作為循環(huán)的計算器。 系統(tǒng)重復地自動從游標工作區(qū)中提取數(shù)據(jù)

25、并放入計數(shù)器變量中。 當游標工作區(qū)中所有的記錄都被提取完畢或循環(huán)中斷時，系統(tǒng)自動地關(guān)閉游標。,FOR循環(huán)中游標的使用,使用FOR循環(huán)修改表emp中各個雇員的工資，若雇員屬于10號部門，則增加$100，若雇員屬于20號部門，則增加$200；若雇員屬于30號部門，則增加$300。,plsql_cur_03.txt,直接使用變量的方式傳遞參數(shù),DECLARE SAL_TMP EMP.SAL%TYPE; CURSOR CUR IS SELECT * FROM EMP WHERE SALSAL_TMP; SELECT * FROM EMP WHERE SAL,plsql_cur_04.txt,使用形參方

26、式傳遞參數(shù)給游標,游標定義語法格式： CURSOR 游標名（ ，） IS ； 說明 打開帶參數(shù)的游標時，參數(shù)個數(shù)和數(shù)據(jù)類型必須與其定義時保持一致。,使用形參方式傳遞參數(shù)給游標,DECLARE CURSOR CUR(V_DEPTNO EMP.DEPTNO%TYPE) IS SELECT * FROM EMP WHERE DEPTNO=V_DEPTNO; MY_EMP EMP%ROWTYPE; BEGIN OPEN CUR(10); FETCH CUR INTO MY_EMP; WHILE CUR%FOUND LOOP DBMS_OUTPUT.PUT_LINE(MY_EMP.ENAME|,|MY_

27、EMP.SAL); FETCH CUR INTO MY_EMP; END LOOP; CLOSE CUR; END;,plsql_cur_05.txt,練習,使用帶形參的游標把部門為20的員工每人的工資加上100元；,plsql_cur_06.txt,FOR UPDATE 子句,語法： SELECT. FROM. FOR UPDATE OF column_referenceNOWAIT; 說明 在事務執(zhí)行期間可以顯式鎖定以拒絕訪問。 在更新或刪除行時要鎖定該行。 使用游標更新或刪除當前行。 首先要在游標中使用 FOR UPDATE 子句鎖定行。 使用 WHERE CURRENT OF 游標從顯

28、式游標中引用當前行,FOR UPDATE 子句,DECLARE CURSOR CUR IS SELECT * FROM EMP FOR UPDATE; MY_EMP EMP%ROWTYPE; INC NUMBER :=0 ; BEGIN OPEN CUR; FETCH CUR INTO MY_EMP; WHILE CUR%FOUND LOOP IF MY_EMP.DEPTNO = 10 THEN INC :=10; ELSIF MY_EMP.DEPTNO = 20 THEN INC :=20; ELSIF MY_EMP.DEPTNO = 30 THEN INC :=30; ELSE INC :

29、=0; END IF; UPDATE EMP SET SAL=SAL+INC WHERE CURRENT OF CUR; FETCH CUR INTO MY_EMP; INC :=0; END LOOP; CLOSE CUR; END;,plsql_cur_07.txt,隱式游標,Oracle在內(nèi)部自動聲明、創(chuàng)建 用于處理DML語句時使用 返回單行的查詢,隱式游標,BEGIN INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES (8892,TT,99); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT:|SQL%ROWCOUNT); IF SQL

30、%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE(SQL%NOTFOUND 為TRUE); ELSE DBMS_OUTPUT.PUT_LINE(SQL%NOTFOUND 為FALSE); END IF; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(SQL%FOUND 為TRUE); ELSE DBMS_OUTPUT.PUT_LINE(SQL%FOUND 為FALSE); END IF; END;,plsql_cur_08.txt,隱式游標,EMP和DEPT表具有參照關(guān)系，所以在刪除EMP表中某部門員工時，如果發(fā)現(xiàn)該部門中已沒有員工，則可以

31、在dept表中刪除該部門。,DECLARE V_DEPTNO EMP.DEPTNO%TYPE; BEGIN V_DEPTNO := 30 ; DELETE FROM EMP WHERE DEPTNO = V_DEPTNO ; IF SQL%NOTFOUND THEN DELETE FROM DEPT WHERE DEPTNO=V_DEPTNO ; END IF ; END;,EMP和DEPT表具有參照關(guān)系，所以在刪除EMP表中某位員工時進行檢查，如果發(fā)現(xiàn)該員工所在的部門中已沒有員工，則可以在dept表中刪除該部門。,DECLARE V_EMPNO EMP.EMPNO%TYPE ; CURSOR

32、 CUR(V_NO EMP.DEPTNO%TYPE) IS SELECT * FROM EMP WHERE DEPTNO=V_NO; V_EMP EMP%ROWTYPE ; BEGIN -按給定編號獲得員工的部門編號 V_EMPNO :=,顯示游標與隱式游標的比較,動態(tài)游標,REF游標 在運行時才與sql語句關(guān)聯(lián)，從而可以將一個游標和多個不同的sql語句關(guān)聯(lián)；經(jīng)常用在向客戶端程序返回變量的存儲過程中用到； 可以為REF游標設(shè)置游標變量 定義游標變量類型的語法: Type is ref cursor ; 游標變量 一種引用類型 可以在運行時指向不同的存儲位置 Close語句關(guān)閉游標并釋放用于查詢

33、的資源 定義游標變量的語法： ;,SET SERVEROUT ON SIZE 10000 DECLARE TYPE REFCURTYPE IS REF CURSOR;- TYPE REFCURTYPE IS REF CURSOR RETURN EMP%ROWTYPE;(強型) REFCUR REFCURTYPE; FLAG INT:=0; EMPROW EMP%ROWTYPE; DEPTROW DEPT%ROWTYPE; BEGIN FLAG:=,plsql_cur_09.txt,游標變量的類型,具有約束的游標變量 具有返回類型的游標變量 也稱為“強游標” 無約束的游標變量：無約束游標變量沒有

34、RETURN子句，當后來打開一個無約束游標變量時，它們可以為任何查詢而打開。 沒有返回類型的游標變量 也稱為“弱游標” 注意：具有約束的游標變量，它們被聲明為特定的返回類型。當隨后打開該變量時，則必須為查詢而打開，其中此查詢的選擇列表匹配游標的返回類型。否則，將出現(xiàn)預定義的ROWTYPE_MISMATCH異常。,過程和函數(shù),PL/SQL塊主要有兩種類型，即命名塊和匿名塊。 匿名塊每次提交時都被編譯，而且，匿名塊不在數(shù)據(jù)庫中存儲并且不能直接被其他的PL/SQL塊調(diào)用。 過程和函數(shù)在命名的PL/SQL塊，被存儲在數(shù)據(jù)庫中，并且可以被其他PL/SQL塊使用。命名塊包括存儲過程、包和觸發(fā)器等。,創(chuàng)建過

35、程,過程用于執(zhí)行特定的操作。可以把經(jīng)常需要執(zhí)行的特定的操作寫成過程。創(chuàng)建過程的語法格式為：,CREATE OR REPLACE PROCEDURE procedure_name (arg 1 IN| OUT | IN OUT arg_type1, arg n IN| OUT | IN OUT arg_type1 ) IS | AS 聲明部分 BEGIN 執(zhí)行部分 EXCEPTION 異常處理部分 END procedure_name;,查看錯誤1:show errors procedure plsql_pro_01 查看錯誤2:通過數(shù)據(jù)字典：Select * from user_errors;

36、,參數(shù)模式：IN：輸入 。OUT：輸出 IN OUT即可輸入又可輸出，接收輸入值并返回,是一個可選的關(guān)鍵字。如果過程已經(jīng)存在，該關(guān)鍵字將重新創(chuàng)建過程，這樣就不必刪除和重新創(chuàng)建過程。,創(chuàng)建過程,CREATE OR REPLACE PROCEDURE PLSQL_PRO_01(EID IN NUMBER) IS NAME VARCHAR(10); BEGIN SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=EID; DBMS_OUTPUT.PUT_LINE(NAME); END PLSQL_PRO_01;,plsql_pro_01.txt,執(zhí)行1：在其他過程

37、中調(diào)用 BEGIN PLSQL_PRO_01(7369); END;,plsql_pro_exe_01.txt,執(zhí)行2：使用EXECUTE EXECUTE PLSQL_PRO_01(7369);,存儲過程,查看存儲過程 數(shù)據(jù)字典：select * from user_source; SELECT TEXT FROM USER_SOURCE WHERE NAME=PRO_1 ; 刪除存儲過程： Drop procedure 過程名； 把存儲過程的執(zhí)行權(quán)限賦給其他用戶 SQL GRANT EXECUTE ON PRO_1 TO TEST;,存儲過程,CREATE OR REPLACE PROCED

38、URE PLSQL_PRO_02(EID IN NUMBER,NAME OUT VARCHAR2) AS BEGIN SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=EID; END PLSQL_PRO_02;,plsql_pro_02.txt,DECLARE TMP VARCHAR2(10); BEGIN PLSQL_PRO_02(7369,TMP); DBMS_OUTPUT.PUT_LINE(TMP); END;,plsql_pro_exe_02.txt,存儲函數(shù),CREATE OR REPLACE FUNCTION function_name (p

39、arameter1 IN|OUT|IN OUT datatype :=|DEFAULT expression , parameter2 IN|OUT|IN OUT datatype :=|DEFAULT expression ) RETURN returntype IS|AS declarations BEGIN code EXCEPTION exception_handlers END,存儲函數(shù),CREATE OR REPLACE FUNCTION PLSQL_FUNC_01(EID IN NUMBER) RETURN VARCHAR2 AS NAME VARCHAR(10); BEGIN

40、SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=EID; RETURN NAME; END PLSQL_FUNC_01;,plsql_func_01.txt,執(zhí)行1：在其他PLSQL代碼中調(diào)用 執(zhí)行2：在sql語句中使用： SELECT PLSQL_FUNC_01(7369) FROM DUAL;,過程與函數(shù)的區(qū)別,區(qū)別之一：參數(shù)形式及返回值不同 函數(shù)有零個或多個參數(shù)，并且只有一個返回值，函數(shù)值的返回是靠RETURN子句返回的。 過程有零個或多個參數(shù)，并且不返回值，其返回值是靠OUT參數(shù)帶出來的。過程可以由零個或多個OUT參數(shù)返回結(jié)果。 區(qū)別之二：調(diào)用形

41、式不同。 過程可以作為單獨可執(zhí)行語句一樣被調(diào)用， 如：過程名（實際參數(shù)1，實際參數(shù)2，），語句可以在PL/SQL塊中單獨出現(xiàn)。 函數(shù)可以在任何表達式能夠出現(xiàn)的地方被調(diào)用。 如：變量名：=函數(shù)名（實際參數(shù)1，實際參數(shù)2，）,函數(shù)和過程中的異常處理,CREATE OR REPLACE PROCEDURE FIRE_EMP (NO EMP.EMPNO%TYPE) AS INVALID_EMP EXCEPTION ; BEGIN DELETE FROM EMP WHERE EMPNO=NO ; IF SQL%NOTFOUND THEN RAISE INVALID_EMP ; END IF ; COMM

42、IT ; EXCEPTION WHEN INVALID_EMP THEN ROLLBACK ; INSERT INTO TEST_LOG_FILE VALUES (ERR_SEQ.NEXTVAL,SYSDATE,EMP,DELETE,NOT FOUND EMPNO |NO| IN EMP!) ; END FIRE_EMP;,PLSQL_PRO_EXCEPTION.SQL,過程和函數(shù)的優(yōu)點,（1）提高數(shù)據(jù)的安全性和完整性 利用安全性的權(quán)限來控制那些沒有足夠權(quán)限的用戶對數(shù)據(jù)庫的間接訪問； 通過把相關(guān)聯(lián)的表的操作集中到一起，來保證針對這些相關(guān)表執(zhí)行一致的操作或任何操作都不做； （2）改善操作性能 多

43、個用戶使用同一個SQL語句時，只做依次語法分析。 只在編譯時進行語法分析，運行時不再重做，直接調(diào)用編譯編碼。 （3）節(jié)省存儲空間 多個不同應用，有同一個存儲代碼 維護性高 （4）模塊化,自主事務處理,自主事務處理 主事務啟動自主事務處理； 然后主事務被暫停； 自主事務處理sql操作； 然后中止自主事務處理； 恢復主事務處理； pragma AUTONOMOUS_TRANSACTION用于標記子程序；,事務互相影響例,CREATE OR REPLACE PROCEDURE PLSQL_TRANSPRO_01 IS -pragma AUTONOMOUS_TRANSACTION; BEGIN INS

44、ERT INTO EMP(EMPNO,ENAME,SAL) VALUES (8888,TEST,1111); ROLLBACK; END PLSQL_TRANSPRO_01;,PLSQL_TRANSPRO_01.txt,CREATE OR REPLACE PROCEDURE PLSQL_TRANSPRO_02 IS BEGIN UPDATE EMP SET COMM=9999; PLSQL_TRANSPRO_01; END ;,PLSQL_TRANSPRO_02.txt,程序包,程序包(package) 用于將邏輯相關(guān)的PL/SQL塊或元素(變量、常量、自定義數(shù)據(jù)類型、過程、函數(shù)、游標等)組織

45、在一起，作為一個完整的單元被存儲在數(shù)據(jù)庫中，以名稱來標識。 它具有面向?qū)ο蟮某绦蛟O(shè)計語言的特點，是對這些PL/SQL塊或元素的封裝。程序包類似于Java語言中的類，其中的變量相當于類中的成員變量，過程和函數(shù)相當于類中的方法。,包的優(yōu)點,規(guī)范化應用程序的開發(fā) 方便對存儲過程和函數(shù)的組織和管理 將相關(guān)的過程和函數(shù)組織在一起 在一個用戶的環(huán)境中解決命名沖突 在不改變包的說明定義時可以改變包體的定義。 限制過程的依賴性。 方便對存儲過程和函數(shù)的安全性管理 整個包的訪問權(quán)限只需要一次性授權(quán) 區(qū)分公共過程和私有過程。公共過程在包外可以被調(diào)用，私有過程在包外不能被調(diào)用。 為用戶會話提供狀態(tài)確認信息 在各種環(huán)

46、境和過程中均引用標識符（即包內(nèi)的公共變量） 在用戶整個會話中保留標識符的狀態(tài)（即在整個會話中公共變量的值一直保留，在一個新的會話中公共變量的值又被初始化） 改善性能 包在首次被調(diào)用時。作為一個整體全部調(diào)入內(nèi)存，不必一個過程一個過程調(diào)入內(nèi)存， 減少多次調(diào)入時的磁盤I/O次數(shù)。,包的組成 有兩個部分,包的說明 （也叫做包頭）,包 體,包頭包含了有關(guān)包內(nèi)容的信息。 該部分中不包括包的代碼部分。,包體是一個獨立于包頭的數(shù)據(jù)字典對象。包體只能在包頭完成編譯后才能進行編譯。包體中帶有實現(xiàn)包頭中描述的前向子程序的代碼段。,在包的說明部分說明的元素（過程、函數(shù)等）是公共元素，只是在包體中說明的元素是私有的。公

47、共元素可以在包的外面單獨調(diào)用，但私有元素只能在包體內(nèi)定義別的過程函數(shù)時被調(diào)用，不能在包外單獨調(diào)用。局部變量是包體內(nèi)定義過程函數(shù)時定義的變量，該局部變量只能在該過程函數(shù)內(nèi)使用，不能在包體內(nèi)別的過程函數(shù)內(nèi)使用。,包的說明,創(chuàng)建包頭的語法： CREATE OR REPLACE PACKAGE package_name IS | AS 公有數(shù)據(jù)類型定義 公有變量聲明 公有常量聲明 公有異常錯誤聲明 公有游標聲明 公有函數(shù)聲明 公有過程聲明END package_name;,包頭示例,數(shù)據(jù)包說明示例 CREATE PACKAGE airlines AS TYPE flight_day_type is R

48、ECORD (flightno flight_sch.flightno%TYPE, flight_day1 NUMBER(1); CURSOR flight_cur RETURN flight_day_type; disp_day CHAR(15); FUNCTION day_fn(mday NUMBER) RETURN CHAR; PROCEDURE branch_sum (p_brnch branch.branch_code%TYPE); END airlines;,包體,創(chuàng)建包體的語法格式： CREATE OR REPLACE PACKAGE BODY package_name IS |

49、 AS 私有數(shù)據(jù)類型定義 私有變量聲明 私有常量聲明 私有異常錯誤聲明 私有函數(shù)聲明和定義 私有過程聲明和定義 公有游標聲明 公有函數(shù)聲明 公有過程聲明 BEGIN 執(zhí)行部分（初始化部分） END package_name;,數(shù)據(jù)包主體,實現(xiàn)數(shù)據(jù)包說明 完全定義游標和子程序 將實現(xiàn)細節(jié)和私有聲明從應用程序隱含 可以將其設(shè)想為“黑箱” 可被替換增強或在不更改接口的情況下被替換 可以在不重新編譯調(diào)用程序的情況下對其進行更改,聲明范圍對于數(shù)據(jù)包主體是局部的 除了在數(shù)據(jù)包主體內(nèi)將不能訪問到聲明的類型和對象 只在首次引用數(shù)據(jù)包時，運行一次數(shù)據(jù)包的初始化部分 使用 CREATE PACKAGE BODY

50、命令生成 CREATE OR REPLACE PACKAGE BODY AS - 私有類型和對象聲明 - 子程序主體 BEGIN - 初始化語句 END ;,CREATE PACKAGE BODY airlines AS CURSOR flight_cur RETURN flight_day_type IS SELECT flightno, reoute_code, flight_day1, flight_day2 FROM flight_sch; FUNCTION day_fn(mday NUMBER) RETURN CHAR IS BEGIN 語句 ; END day_fn; . . EN

51、D airlines;,包中可以包含的元素的性質(zhì),包的實例,Package子目錄 建表：STUDENT.TXT,SUBJECT.TXT 包頭：STUDENTPACKAGE.txt 包體：STUDENTPACKAGEBODY.txt 包的調(diào)用：調(diào)用包.txt 查看用戶包的源文件：user_source;,刪除包,當不在需要某個程序包時，可以將其刪除。如果只刪除包體，可以使用命令 DROP PACKAGE BODY package_name; 如果要同時刪除包說明，可以使用 DROP PACKAGE package_name;,觸發(fā)器,數(shù)據(jù)庫觸發(fā)器是一個與特定表相連的存儲過程。當應用程序用一條滿足

52、觸發(fā)器條件的SQL DML語句指向該表時，Oracle將自動執(zhí)行該觸發(fā)器以執(zhí)行任務。 觸發(fā)器是在事件發(fā)生時隱式地運行的，不能接收參數(shù)，不能被調(diào)用。 運行觸發(fā)器的方式叫做激發(fā)（firing）觸發(fā)器，觸發(fā)事件可以是對數(shù)據(jù)庫表的DML（INSERT、UPDATE或DELETE）操作或某種視圖的操作( View )。,觸發(fā)事件 （如INSERT、UPDATE、DELETE等）,觸發(fā)器腳本,觸發(fā)時機 BEFORE （事件）,觸發(fā)對象表,觸發(fā)器事件（如INSERT、UPDATE、DELETE等）,觸發(fā)器腳本,觸發(fā)時機 AFTER （事件）,觸發(fā)對象表,創(chuàng)建觸發(fā)器,CREATE OR REPLACE TRI

53、GGER trigger BEFORE|AFTER DELETE|INSERT|UPDATE OF column ,column ON table FOR EACH ROW WHEN condition BEGIN pl/sql block. END trigger,觸發(fā)器例,CREATE OR REPLACE TRIGGER TG_INSERT BEFORE INSERT ON STUDENT BEGIN DBMS_OUTPUT.PUT_LINE(插入前觸發(fā)了觸發(fā)器); END TG_INSERT;,PLSQL_TRI_01.txt,觸發(fā)器的組成,觸發(fā)器的組成 觸發(fā)事件 可選的觸發(fā)器約束條件

54、 觸發(fā)器動作 可以創(chuàng)建對應于以下語句觸發(fā)的觸發(fā)器 DML語句（insert、update、delete） DDL語句（create、alter、drop） 數(shù)據(jù)庫操作（logon、logoff、startup、servererror、shutdowndeng） 觸發(fā)器類型 DML觸發(fā)器 系統(tǒng)觸發(fā)器 替代觸發(fā)器（instead of）,數(shù)據(jù)庫級別 啟動 關(guān)閉 連接 注銷 系統(tǒng)出錯,模式（方案）級別 對象的創(chuàng)建 對象的修改 對象的刪除,表或行級別 INSERT DELETE UPDATE,DML on view INSTEAD OF other tables,可以觸發(fā)觸發(fā)器的事件包括,表級別DML

55、的事件-操作對象：表 INSERT UPDATE DELETE 模式級別的DDL 對象事件-操作對象：模式 CREATE ALTER DROP 數(shù)據(jù)庫級別的事件-操作對象：數(shù)據(jù)庫 啟動 STARTUP 關(guān)閉 SHUTDOWN 連接 LOGON 注銷 LOGOUT 系統(tǒng)出錯 SERVERERROR,各種觸發(fā)事件,類型一：DML觸發(fā)器,DML觸發(fā)器的組成與類型 在編寫觸發(fā)器源代碼之前，必須先確定其觸發(fā)時間、觸發(fā)事件及觸發(fā)器的類型。,DML觸發(fā)器,行級觸發(fā)器,語句級觸發(fā)器,DML觸發(fā)器 觸發(fā)事件,表更新,表插入,表刪除,DML觸發(fā)器 觸發(fā)時間（時機）,BEFORE,AFTER,觸發(fā)器例(DML觸發(fā)器

56、),CREATE OR REPLACE TRIGGER TG_UPDATE AFTER UPDATE ON STUDENT -FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(更新時觸發(fā)了觸發(fā)器1); END TG_UPDATE;,PLSQL_TRI_02.txt,表級：又稱語句級，每條語句執(zhí)行之前或之后執(zhí)行觸發(fā)器一次； 行級（for each row）：表中每行被影響一條就觸發(fā)一次；,兩個特殊的行級變量,:NEW（insert的值）； :OLD（表中原有的值，被刪除、更新的值）；,CREATE OR REPLACE TRIGGER TG_UPDATE_NEWOL

57、D BEFORE UPDATE ON STUDENT FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(-NEW-); DBMS_OUTPUT.PUT_LINE(NEW STUID:|:NEW.STUID); DBMS_OUTPUT.PUT_LINE(NEW STUNAME:|:NEW.STUNAME); DBMS_OUTPUT.PUT_LINE(NEW SEX:|:NEW.SEX); DBMS_OUTPUT.PUT_LINE(-OLD-); DBMS_OUTPUT.PUT_LINE(OLD STUID:|:OLD.STUID); DBMS_OUTPUT.PUT_

58、LINE(OLD STUNAME:|:OLD.STUNAME); DBMS_OUTPUT.PUT_LINE(OLD SEX:|:OLD.SEX); END TG_UPDATE;,PLSQL_TRI_03.txt,在BEFORE類型行級觸發(fā)器和AFTER類型行級觸發(fā)器中使用這些標識符。 在語句級觸發(fā)器中不要使用這些觸發(fā)器。 在PL/SQL語句或SQL語句中，這些標識符前加上冒號（:）來引用它們。 在行級觸發(fā)器的WHEN條件中使用該標識符時，前面不要加冒號（:）. 在BEFORE觸發(fā)器中修改 “:new”，不能修改“:old”。在AFTER觸發(fā)器中不能修改“:new”。因為，觸發(fā)時機AFTER后，

59、 :new的值已經(jīng)被插入到表里， 因此:new不能修改,使用“:old”和“:new”應注意的,用觸發(fā)器增強參照完整性約束。當DEPT表的DEPTNO發(fā)生變化時，EMP表的相關(guān)行也跟著進行適當?shù)男薷摹?CREATE OR REPLACE TRIGGER cascade_update AFTER UPDATE OR DELETE ON dept FOR EACH ROW BEGIN UPDATE emp SET emp.deptno=:new.deptno WHERE emp.deptno=:old.deptno; END;,Goods目錄,當一個觸發(fā)器中的觸發(fā)事件中既有刪除、更新又有插入時，如何判斷觸發(fā)器是因哪個事件而動作？,用觸發(fā)器謂詞（INSERTING、UPDATING、DELETING）,DML觸發(fā)器是INSERT、UPDATE、DELETE觸發(fā)器。在這種觸發(fā)器的內(nèi)部，有三個布爾函數(shù)可以用來決定要進行哪個操作。這些謂