Oracle數(shù)據(jù)庫知識-PLSQL 培訓膠片V1.0課件

1、Oracle基礎(chǔ)知識PLSQLCC&CRM新員工報表培訓PL/SQL簡介PL/SQL概述PL/SQL是Oracle對SQL規(guī)范的擴展，是一種塊結(jié)構(gòu)語言，包括一整套的數(shù)據(jù)類型、條件結(jié)構(gòu)、循環(huán)結(jié)構(gòu)和異常處理結(jié)構(gòu)。其優(yōu)點大致如下： PL/SQL是一種高性能的基于事務(wù)處理的語言，支持所有數(shù)據(jù)處理命令，支持所有SQL數(shù)據(jù)類型和所有SQL函數(shù)，同時支持所有ORACLE對象類型。 PL/SQL塊可以被命名和存儲在ORACLE服務(wù)器中，同時也能被其他的PL/SQL程序或SQL命令調(diào)用，具有很好的可重用性。 可以將功能劃分為不同的邏輯代碼塊編程的模塊化為應用程序開發(fā)環(huán)境提供了更大的靈活性。 對于SQL，ORAC

2、LE必須在同一時間處理每一條SQL語句，每一個獨立的調(diào)用都必須被Oracle服務(wù)器處理，占用大量的服務(wù)器時間，并導致網(wǎng)絡(luò)擁擠。而PL/SQL是以整個語句塊發(fā)給服務(wù)器，這就降低了網(wǎng)絡(luò)擁擠。PL/SQL塊結(jié)構(gòu)PL/SQL是一種塊結(jié)構(gòu)的語言，組成PL/SQL程序的單元是邏輯塊，一個PL/SQL 程序包含了一個或多個邏輯塊，每個塊都可以劃分為三個部分：DECLARE變量說明部分；包括定義的變量和其它的對象（如常量和指針），游標 也在這里定義；可選。BEGIN執(zhí)行語句部分；PL/SQL塊中的指令部分，所有的可執(zhí)行語句都放在這 里，其他的PL/SQL塊也可以放在這一部分；必選。EXCEPTION異常處理部

3、分；包含如何處理指定的錯誤并按用戶的定義進行處理；可 選。END;PL/SQL塊類型 PL/SQL塊主要有兩種類型，即命名塊和匿名塊： 匿名塊：以DECLARE或BEGIN開始，每次使用時都要進行編譯，除此之外，該類塊不在數(shù)據(jù)庫中存儲并且不能直接從其他的PL/SQL塊中調(diào)用。 命名塊沒有匿名塊的限制，獨立編譯并存儲在數(shù)據(jù)庫中，任何與數(shù)據(jù)庫相連接的應用程序都可以訪問這些存儲的PL/SQL程序塊，如過程，函數(shù)，包和觸發(fā)器都屬于命名塊CREATE OR REPLACE PROCEDURE name (parameter,parameter,.) IS local declarationsBEGIN

4、execute statementsEXCEPTION exception handlersEND name; 過程（PROCEDURE）PL/SQL程序PL/SQL過程、函數(shù)的參數(shù)形式模式形式說明IN 當過程被調(diào)用時，實參的值將傳入該過程。在該過程內(nèi)部，形參類似P L / S Q L使用的常數(shù)，即該值具有只讀屬性不能對其修改。當該過程結(jié)束時，控制將返回到調(diào)用環(huán)境，這時，對應的實參沒有改變。OUT 當過程被調(diào)用時，實參具有的任何值將被忽略不計。在該過程內(nèi)部，形參的作用類似沒有初始化的P L / S Q L變量，其值為空（ N U L L）。該變量具有讀寫屬性。當該過程結(jié)束時，控制將返回調(diào)用環(huán)境

5、，形參的內(nèi)容將賦予對應的實參。IN OUT 該模式是模式IN 和O U T的組合。當調(diào)用過程時，實參的值將被傳遞到該過程中。在該過程內(nèi)部，形參相當于初始化的變量，并具有讀寫屬性。當該過程結(jié)束時，控制將返回到調(diào)用環(huán)境中，形參的內(nèi)容將賦予實參形參可以有三種模式， IN，OUT或IN OUT。默認為IN。CREATE OR REPLACE TRIGGER schema. Trigger_name BEFORE | AFTER DELETE | INSERT | UPDATE OF column, column. ON schema.table REFERENCING OLD AS old NEW A

6、S new | NEW AS new OLD AS old FOR EACH ROWDECLARE local declarationsBEGIN execute statementsEXCEPTION exception handlersEND; 觸發(fā)器（TRIGGER）PL/SQL程序 觸發(fā)器的應用場景： 維護在表創(chuàng)建階段通過聲明限制無法實現(xiàn)的復雜完整性限制。 通過記錄修改內(nèi)容和修改者來審計表中的信息。 在表內(nèi)容發(fā)生變更時，自動通知其他程序采取相應的處理。 盡量在業(yè)務(wù)設(shè)計邏輯上用存儲過程來代替觸發(fā)器。PL/SQL程序CREATE OR REPLACE PACKAGE package_nam

7、e AS PROCEDURE procedure1 (global_variable1 datatype, .); PROCEDURE procedure2 (global_variable1 datatype, .);END package_name;CREATE OR REPLACE PACKAGE BODY package_name AS PROCEDURE procedure1 (global_variable1 datatype, .) IS BEGIN . END procedure1; PROCEDURE procedure2 (global_variable1 datatype

8、, .) IS BEGIN . END procedure2;END package_name; 包（PACKAGE）： 包是由存儲在一起的相關(guān)對象組成的PL/SQL結(jié)構(gòu)。包有兩個獨立的部分，即說明部分和包體，這兩部分獨立地存儲在數(shù)據(jù)字典中。PL/SQL程序PL/SQL常用數(shù)據(jù)類型名稱類型說明NUMBER數(shù)字型能存放整數(shù)值和實數(shù)值，并且可以定義精度和取值范圍BINARY_INTEGER數(shù)字型可存儲帶符號整數(shù)，為整數(shù)計算優(yōu)化性能DEC數(shù)字型NUMBER的子類型，小數(shù)DOUBLE PRECISION數(shù)字型NUMBER的子類型，高精度實數(shù)INTEGER數(shù)字型NUMBER的子類型，整數(shù)INT數(shù)字型NU

9、MBER的子類型，整數(shù)NUMERIC數(shù)字型NUMBER的子類型，與NUMBER等價REAL數(shù)字型NUMBER的子類型，與NUMBER等價SMALLINT數(shù)字型NUMBER的子類型，取值范圍比INTEGER小VARCHAR2字符型存放可變長字符串，有最大長度CHAR字符型定長字符串LONG字符型變長字符串，最大長度可達32,767DATE日期型以數(shù)據(jù)庫相同的格式存放日期值BOOLEAN布爾型TRUE OR FALSEROWIDROWID存放數(shù)據(jù)庫的行號DECLARE部分DECLARE CURSOR Oper_cur IS -聲明游標 SELECT AgentID, Name FROM icd.t

10、AgentInfo WHERE AgentID 2001; -變量之間以“;”分隔 v_FaxNo AutoFaxLog.FaxNo%TYPE;-v_FaxNo與字段FaxNo數(shù)據(jù)類型相同 v_AutoFax t_SceAutoFax%ROWTYPE; -v_AutoFax與表得行數(shù)據(jù)類型相同 v_Date DATE := SYSDATE; -日期型常量 v_ErrCode NUMBER(8); v_ErrMsg VARCHAR2(200);條件語句IF (a b) AND (a c) THENg := a; -每一個賦值語句都以“;”結(jié)束ELSIF (a b) AND (a g THENg

11、:= b + c;END IF; -”END IF”也以“;”結(jié)束END IF;LOOP循環(huán)X := 100;LOOPX := X + 10;IF X 1000 THENEXIT;END IF;END LOOP;Y := X;X := 100;LOOP X := X + 10; EXIT WHEN X 1000;END LOOP;Y := X;WHILE LOOPX := 100;WHILE X = 1000 LOOP X := X + 10;END LOOP;Y = X; FOR LOOPX:=100;FOR v_Counter IN 1.10 LOOP X := X + 10;END LO

12、OP;Y := X;PL/SQL異常處理PL/SQL異常處理DECLAREX NUMBER;BEGINX:= yyyy;-Error HereEXCEPTION WHEN VALUE_ERROR THEN /*Oracle預定義的異常*/ DBMS_OUTPUT.PUT_LINE(EXCEPTION HANDED); WHEN OTHERS THEN /*OTHERS異常處理器必須排在最后，它處理所有沒有明確列出的異常*/ DBMS_OUTPUT.PUT_LINE(未知異常); END; ORACLE預定義的異常異常名稱ORACLE錯誤代碼說明CURSOR_ALREADY_OPENORA-65

13、11試圖打開一個已打開的光標DUP_VAL_ON_INDEXORA-0001試圖破壞一個唯一性限制INVALID_CURSORORA-1001試圖使用一個無效的光標INVALID_NUMBERORA-1722試圖對非數(shù)字值進行數(shù)字操作LOGIN_DENIEDORA-1017無效的用戶名或者口令NO_DATA_FOUNDORA-1403查詢未找到數(shù)據(jù)NOT_LOGGED_ONORA-1012還未連接就試圖數(shù)據(jù)庫操作PROGRAM_ERRORORA-6501內(nèi)部錯誤ROWTYPE_MISMATCHORA-6504主變量和光標的類型不兼容STORAGE_ERRORORA-6500內(nèi)部錯誤TIMEOU

14、T_ON_RESOURCEORA-0051發(fā)生超時TOO_MANY_ROWSORA-1422SELECT INTD命令返回的多行TRANSACTION_BACKED_OUTORA-006由于死鎖提交被退回VALUE_ERRORORA-6502轉(zhuǎn)換或者裁剪錯誤ZERO_DIVIDEORA-1476試圖被零除PL/SQL捕獲異常DECLARESALARY_CODE VARCHAR2(1); v_ErrCode NUMBER; v_ErrMsg VARCHAR2(200);BEGINSALARY_CODE := XX;EXCEPTION WHEN OTHERS THEN v_ErrCode := S

15、QLCODE; v_ErrMsg := SUBSTR(SQLERRM,1,200); DBMS_OUTPUT.PUT_LINE(v_ErrMsg);END; 使用ORACLE游標DECLAREln_dno NUMBER;lvs_dname VARCHAR2(40);BEGINSELECT DEPT_NO,DEPT_NAMEINTO ln_dno,lvs_dnameFROM deptWHERE DEPT_NO=1;DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)|.|lvs_dname);EXCEPTION WHEN NO_DATA_FOUND THENDBMS_OUTP

16、UT.PUT_LINE(NO DATA_FOUND); WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE(TOO_MANY_ROWS);END; 隱式游標 隱式游標用PL/SQL中單條記錄的查詢，須與INTO子句一起使用，查詢的返回值被賦予INTO子句中的變量，變量的聲明是在DELCARE中。 當查詢開始時隱式游標打開，查詢結(jié)束時隱式游標自動關(guān)閉。數(shù)據(jù)類型必須一致返回多行或零數(shù)據(jù)時均會走異常定義顯式游標 當查詢返回結(jié)果超過一行時，就需要一個顯式游標，此時用戶不能使用SELECT INTO語句。 顯式游標在PL/SQL塊的聲明部分聲明，在執(zhí)行部分或異常處理部

17、分打開，取數(shù)據(jù)，關(guān)閉。DECLAREVNAME VARCHAR2(40);VNAME ALL_VIEWS.VIEW_NAME%TYPE; CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWSWHERE ROWNUM = 10 ORDER BY VIEW_NAME;BEGINEND; 使用%TYPE屬性游標屬性屬性含量%FOUND布爾型屬性，當最近一次該記錄時成功返回，則值為TRUE%NOTFOUND布爾型屬性，它的值總與%FOUND屬性的值相反%ISOPEN布爾型屬性，當光標是打開時返回TRUE%ROWCOUNT數(shù)字型屬性，返回已從光標中讀取的記錄數(shù)顯式游標

18、打開、取數(shù)、關(guān)閉 使用游標中的值之前應該首先打開游標。 從游標得到一行數(shù)據(jù)使用FETCH命令。每一次提取數(shù)據(jù)后，游標都指向結(jié)果集的下一行。 不需要使用游標時切記關(guān)閉游標以釋放內(nèi)存。 DECLAREBEGINOPEN C1;FETCH C1 INTO VNAME;WHILE C1%FOUND LOOPDBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)| |VNAME; FETCH C1 INTO VNAME;END LOOP; CLOSE C1;END; 在游標中使用FOR循環(huán)DECLARECURSOR C1 IS SELECT VIEW_NAME FROM AL

19、L_VIEWS WHERE ROWNUM = 10 ORDER BY VIEW_NAME;BEGINFOR I IN C1 LOOPDBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);END LOOP; CLOSE C1;EXCEPTION WHEN OTHERS THEN IF C1%ISOPEN THEN CLOSE C1; END IF;END; 異常處理時如何關(guān)閉游標在游標中使用LOOP循環(huán)DECLARE CURSORc_empIS SELECTename, salary FROMemp; R_empc_emp%ROWTYPE;R_empEMP%ROWTYPE; BEGIN OPENc_emp; LOOP FETCHc_empINTOr_emp; EXITWHENc_emp%NOTFOUND; DBMS_OUT.PUT.PUT_LINE(SalaryofEmployee|r_emp.ename| is|r_emp.salary); ENDLOOP; CLOSEc_emp; END; 記錄變量的使用游標中的刪除和更新DELCARE CURSORc1IS SELECTempno,salary FROMemp WHEREcommISNULL FOR