福建省電力公司oracle培訓(xùn)教材PLSQL語言篇課件

ORACLE入門

－PL/SQL語言篇技術(shù)支持部

本課程學(xué)習內(nèi)容PL/SQL簡介PL/SQL數(shù)據(jù)類型(ORACLE的數(shù)據(jù)類型)ORACL內(nèi)置的SQL函數(shù)PL/SQL中使用SQLPL/SQL中游標的使用動態(tài)PL/SQLPL/SQL的異常處理PL/SQL簡介

PL/SQL(ProceduralLanguage/SQL)即模塊化的程序設(shè)計語言，用于從各種環(huán)境中訪問ORACLE數(shù)據(jù)庫。它具備了許多SQL中所沒有的過程化屬性方面的特點。主要包括：變量和類型控制結(jié)構(gòu)（條件語句、循環(huán)語句…)過程、函數(shù)游標異常處理PL/SQL的程序結(jié)構(gòu)

PL/SQL的基本單位是“塊”(Block)。所有的PL/SQL程序都是由一個或多個PL/SQL塊構(gòu)成的，這些塊可以相互進行嵌套。通常一個塊完成程序的一個單元的工作。一個基本的塊由三個部分組成：定義部分定義變量、常量、游標、異常處理可執(zhí)行部分包括對數(shù)據(jù)庫進行操作的SQL語句，以及對塊中的語句進行組織、控制的PL/SQL語句。異常處理（Exception)部分可執(zhí)行部分中的語句，在執(zhí)行過程中出錯或出現(xiàn)非正?，F(xiàn)象時，所做的響應(yīng)處理DECLAREBEGINEXCEPTIONENDPL/SQL塊結(jié)構(gòu)PL/SQL數(shù)據(jù)類型PL/SQL數(shù)據(jù)類型常用的數(shù)據(jù)類型CHAR：存放固定長度的字符串VARCHAR2：存放可變長度的字符串NUMBER：存放0、正負數(shù)、浮點數(shù)DATE：存放時間數(shù)據(jù)（包括日期和時間）LONG：存放變長字符串。一般用來存儲大文本RAWLONG存放多媒體數(shù)據(jù),如聲音、圖片例如：創(chuàng)建一雇員表CREATETABLEemp(empnonumber(4),enamevarchar2(10),hiredatedate,salnumber(7,2),deptnonumber(2));ORACL內(nèi)置的SQL函數(shù)Concat(s1,s2)返回串接上s2之后的s1.該函數(shù)與||運算符作用相同。實例：selectconcat(‘a(chǎn)bc’,’def’)fromdual;返回結(jié)果：‘a(chǎn)bcdef’select‘a(chǎn)bc’||’def’fromdual;返回結(jié)果：‘a(chǎn)bcdef’Length(s)以字節(jié)為單位返回字符串s的長度。ORACL內(nèi)置的SQL函數(shù)數(shù)值函數(shù)Ceil(n)返回大于或等于n的整數(shù)Selectceil(18.6),ceil(-18.6)fromdual;Floor(n)返回小于或等于n的整數(shù)Selectfloor(18.6),floor(-18.6)fromdual;Mod(x,y)返回x除以y得余數(shù)，若y為0，則返回x。Selectmod(23,5),mod(4,1.3)fromdual;返回結(jié)果：1.1,0.1Round(x,[,y])返回舍入到小數(shù)點右邊y為的x值。Selectround(1.56),round(1.56,1),round(123.4,-1)fromdual;返回結(jié)果：1.1,0.1,120ORACL內(nèi)置的SQL函數(shù)日期函數(shù)Sysdate返回當前的日期和時間Add_months(D,x)Last_day(D)返回日期D的月份的最后一天的日期Months_Between(D1,D2)返回在D1和D2之間月的數(shù)目。Trunc(D[,format])返回結(jié)尾由format指定的單位的日期。示例：Selecttrunc(sysdate,’year’)fromdual;返回今年的第一天Selecttrunc(sysdate,’mm’)fromdual;返回本月的第一天Selecttrunc(sysdate,’D’)fromdual;返回本周的第一天ORACL內(nèi)置的SQL函數(shù)其它函數(shù)Nvl(a,b)空值替換函數(shù)，若a為空，則替換成b。示例：Selectename,sal,sal+nvl(comm,0)fromdual;DECODE(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,缺省值)該函數(shù)的含義如下：

IF條件=值1THEN

RETURN(翻譯值1)

ELSIF條件=值2THEN

RETURN(翻譯值2)

......

ELSIF條件=值nTHEN

RETURN(翻譯值n)

ELSE

RETURN(缺省值)

ENDIF

PL/SQL的注釋注釋增強了可閱讀性，使得程序更易于理解。單行注釋--comment多行注釋/*comment*/注意：此注釋不能作用在SQL語言上。示例：DECLAREv_deptnonumber(2);--與雇員表中部門代碼字段交互的變量v_salnumber(7,2);--與雇員表中工資字段交互的變量BEGIN/*thisisatest!*/selectdeptno,salintov_deptno,v_salfromempwhereempno=7788;END;PL/SQL塊的定義部分

在PL/SQL塊中引用的所有標識符，都必須在定義部分中明確定義。定義常量格式：〈標識符〉CONSTANT〈數(shù)據(jù)類型〉：=〈表達式〉]例：定義一常量PI，值為3.14。PICONSTANTNUMBER(3,2):=3.14;定義標量型變量標量型數(shù)據(jù)類型，是指數(shù)據(jù)類型為個體型。格式：<標識符><數(shù)據(jù)類型>[NOTNULL][:=|DEFAULT<表達式>]例：定義一寬度為10個字符的字符串變量X。DECLAREXCHAR(5)；yCHAR(5):=‘ORACLE’;ZCHAR(5)default‘oracle’;PL/SQL塊的定義部分另一種定義標量型變量的方法——%TYPE

定義一個變量,其數(shù)據(jù)類型與已知變量的數(shù)據(jù)類型相同，或者與數(shù)據(jù)庫表的某個列的數(shù)據(jù)類型相同。%TYPE的優(yōu)點在于：所引用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以不必知道。所引用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以實時改變。格式：<標識符><已知變量或表列>[NOTNULL][:=|DEFAULT<表達式>]<變量名><基表名>.<列名>%TYPE例：定義一個變量，其數(shù)據(jù)類型基于另一個變量DECLAREV_1NUMBER(7,2);V_11V1%TYPE:=12345.6;例：定義一個變量，其數(shù)據(jù)類型基于數(shù)據(jù)庫中表的列DECLAREv_enameEMP.ENAME%TYPE;V_SALEMP.SAL%TYPE;PL/SQL塊的定義部分另一種定義組合型變量的方法——%ROWTYPE

定義一個變量,其數(shù)據(jù)類型與數(shù)據(jù)庫表的數(shù)據(jù)結(jié)構(gòu)相同。%ROWTYPE的優(yōu)點在于：所引用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以不必知道。所引用的數(shù)據(jù)庫表中的數(shù)據(jù)類型可以實時改變。簡易格式：<變量名><基表名>%ROWTYPE例：DECLAREv_empemp%rowtype;BEGINSELECT*INTOv_empFROMempWHEREempno=7788;DBMS_OUTPUT.PUT_LINE(v_emp.empno);DBMS_OUTPUT.PUT_LINE(v_emp.ename);DBMS_OUTPUT.PUT_LINE(v_emp.job);DBMS_OUTPUT.PUT_LINE(v_emp.sal);END;變量的引用和賦值標量變量賦值格式：<變量>:=<表達式>；例：V_NAME:=‘JOAN’;v_demptno:=10;組合型變量賦值格式：〈變量.域名〉〈（主鍵值）〉：=〈表達式〉；例：v_emp.sal:=8888;v_m:=8888;PL/SQL中使用SQL－SELECT語句SELECT語句：將數(shù)據(jù)從數(shù)據(jù)庫中檢索出來并放入PL/SQL變量中。格式：SELECT<表列>INTO<變量>FROM<表>例：查詢某個雇員的姓名及工資。DECLAREv_empnoemp.empno%type:=7788;v_enameemp.ename%type;v_salemp.sal%type;BEGINselectename,salintov_ename,v_salfromempwhereempno=v_empno;DBMS_OUTPUT.PUT_LINE(v_empno||v_ename||v_sal);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(‘sorry:nodatafound!’);END;/PL/SQL中的SELECT語句中必須包含INTO子句，而且對應(yīng)的個數(shù)要相同，位置要一一對應(yīng)。查詢結(jié)果只返回一條記錄，否則會產(chǎn)生異常情況。（1）查詢結(jié)果多于一條記錄異常變量：TOO_MANY_ROWS（2）查詢結(jié)果沒有返回記錄異常變量：NO_DATA_FOUNDPL/SQL中使用SQL在PL/SQL中，對數(shù)據(jù)庫進行插入(INSERT)、刪除(DELETE)、修改（UPDATE）語句，其語法形式與SQL中的是完全一樣的。例：在EMP表中刪去某個雇員。BEGINDELETEempWHEREempno=7788;COMMIT;END;PL/SQL的執(zhí)行部分——流程控制語句流程控制語句主要有三種：條件控制循環(huán)控制跳轉(zhuǎn)控制流程控制語句——循環(huán)控制在PL/SQL中循環(huán)控制的有以下四種：簡單循環(huán)FOR循環(huán)WHERE循環(huán)用于游標的FOR循環(huán)循環(huán)控制——簡單循環(huán)語法格式：LOOP〈語句1〉；〈語句2〉；…EXITWHEN〈條件〉；ENDLOOP；例：把數(shù)值1到50順序插入表中。V_counter:=1;LOOPINSERTINTOtemp_tableVALUES(v_counter);EXITWHENv_counter>50；V_count:=v_count+1;ENDLOOP；跳轉(zhuǎn)控制語句語法格式：<<標號>>……GOTO<<標號>>;

在進行PL/SQL編程時,盡量避免或不用GOTO語句,因為這種無條件的跳轉(zhuǎn)語句打破了程序的邏輯性,有悖于自頂向下的編程風格.PL/SQL游標的使用游標(CURSOR)的功能,是ORALCE系統(tǒng)為了將所有查詢結(jié)果返回給用戶程序而提供的。一個游標,實際上是在內(nèi)存中開辟一個工作區(qū),它對應(yīng)一條SELECT語句。當打開游標時，就是執(zhí)行游標所對應(yīng)的SELECT語句，并將其查詢結(jié)果放入工作區(qū)，并且指針指向工作區(qū)的首部。通過光標上的操作可以把這些記錄檢索到客戶端的應(yīng)用程序。CURSOR內(nèi)存區(qū)POINTERSELECT…INTO…:只能查詢數(shù)據(jù)庫的單條記錄，并把記錄的數(shù)據(jù)賦給變量。游標——定義和操縱游標步驟：定義游標打開游標從游標中取值關(guān)閉游標定義游標定義游標，就是定義一個游標名，以及與其相對應(yīng)的SELECT語句。語法格式：CURSOR〈游標名〉IS〈SELECT子句〉；示例：定義一個包含所有雇員記錄的游標。cursorcur_empisselect*fromemp;打開游標打開游標，就是執(zhí)行游標所對應(yīng)的SELECT語句，將其查詢結(jié)果放入工作區(qū)，并且指針指向工作區(qū)的首部。語法格式：OPEN〈游標名〉；從游標中取值取值工作是將游標工作區(qū)中的數(shù)據(jù)取出一行，放入指定的輸出變量中。語法格式：FETCH〈游標名〉INTO〈變量1〉,〈變量2〉…；示例：fetchcur_empintov_empno,v_ename,v_sal,v_comm,v_deptno關(guān)閉游標釋放與該游標相關(guān)的資源。語法格式：CLOSE<游標名>;示例：closecur_emp;游標的屬性從游標工作區(qū)中逐一地取數(shù)據(jù)，可以在循環(huán)中完成。但循環(huán)的開始以及結(jié)束，需以游標屬性為依據(jù)。游標屬性有：%ISOPEN：判斷游標是否被打開%NOTFOUND：判斷何時中斷循環(huán)%FOUND：與%NOTFOUND相反%ROWCOUNT：實際從游標工作區(qū)抽取的記錄數(shù)示例：Opencur_emp;Loopfetchcur_empintov_empno,v_ename,v_sal,v_deptno;exitwhencur_emp%NOTFOUND;Endloop;游標——用于游標的FOR循環(huán)游標的FOR循環(huán)，是一種簡單的游標操作方法，系統(tǒng)隱式地進行游標的打開、提取數(shù)據(jù)、循環(huán)、關(guān)閉。格式：

FOR〈記錄變量〉IN〈游標名〉LOOP〈語句…>;

ENDLOOP;<記錄變量>:由系統(tǒng)隱含定義的記錄名示例：Declarecursorcur_empisselect*fromemp;Beginforv_empincur_emploop

DBMS_OUTPUT.PUT_LINE(v_emp.ename);

DBMS_OUTPUT.PUT_LINE(v_emp.sal);endloop;End;一個完整的示例例：建立一存儲過程，根據(jù)職務(wù)修改工資CREATEORREPLACEPROCEDUREp_update_salASCURSORcur_empISSELECT*FROMemp;v_empcur_emp%ROWTYPE;BEGINOPENcur_emp;LOOPFETCHcur_empINTOv_emp;EXITWHENcur_emp%NOTFOUND;IFv_emp.job=‘MANAGER’THENv_emp.sal:=v_emp.sal*1.3;ELSIFv_emp.job=‘SALESMAN’THENv_emp.sal:=v_emp.sal*1.2;ELSEv_emp.sal:=v_emp.sal*1.1;ENDIF；UPDATEempSETsal=v_emp.salWHEREempno=v_emp.empno;ENDLOOP;CLOSEcur_emp;COMMIT;END;一個完整的示例(用FOR循環(huán))CREATEPROCEDUREp_update_salASCURSORcur_empISSELECT*FROMemp;BEGINFORv_empINcur_empLOOPIFv_emp.job=‘MANAGER’THENv_emp.sal:=v_emp.sal*1.3;ELSIFv_emp.job=‘SALESMAN’THENv_emp.sal:=v_emp.sal*1.2;ELSEv_sal:=v_sal*1.1;ENDIF；UPDATEempSETsal=v_emp.salWHEREempno=v_emp.empno;ENDLOOP;COMMIT;END;示例DECLARECURSORc1isSELECTename,empno,salFROMempORDERBYsalDESC;--startwithhighestpaidemployeemy_enameCHAR(10);my_empnoNUMBER(4);my_salNUMBER(7,2);BEGINOPENc1;FORiIN1..5LOOPFETCHc1INTOmy_ename,my_empno,my_sal;EXITWHENc1%NOTFOUND;INSERTINTOtempVALUES(my_sal,my_empno,my_ename);COMMIT;ENDLOOP;CLOSEc1;END;異常處理

PL/SQL中，將程序執(zhí)行過程中的一個警告或錯誤稱為一個異常(EXCEPTION)。異常情況的種類有三種：預(yù)定義的ORACLE錯誤ORACLE預(yù)定一的異常情況大約有24個。對這種異常情況的處理，無須在程序中定義，由ORACLE自動將其引發(fā)。非預(yù)定義的ORACLE錯誤即其他標準的ORACLE錯誤。對這種異常情況的處理，需在定義部分定義，然后由ORACLE自動將其引發(fā)。用戶定義的錯誤程序執(zhí)行過程中，出現(xiàn)編程人員認為非正常的。對這種異常情況的處理，需在定義部分定義，然后顯式由地將其引發(fā)。異常處理語法格式：EXCEPTIONWHEN<異常情況1>THEN〈語句〉；[WHEN<異常情況2〉THEN〈語句〉；…[WHENOTHERSTHEN〈語句〉；]OTHERS：指沒有列在異常處理部分中的其他異常情況。DECLAREBEGINEXCEPTIONENDPL/SQL塊執(zhí)行過程異常發(fā)生異常處理異常處理預(yù)定義的ORACLE錯誤預(yù)定義的異常名稱錯誤號說明CURSOR_ALREADY_OPENORA-6511試圖打開一個已打開的光標LOGIN_DENIEDORA-1017無效的用戶名或者口令NO_DATA_FOUNDORA-1403查詢未找到數(shù)據(jù)NOT_LOGGED_ONORA-1012還未連接就試圖數(shù)據(jù)庫操作DUP_VAL_ON_INDEXORA-0001試圖破壞一個唯一性限制TIMEOUT_ON_RESOURCEORA-0051發(fā)生超時TRANSACTION_BACKED_OUTORA-006由于死鎖提交被退回TOO_MANY_ROWSORA-1422SELECTINTD命令返回的多行異常處理預(yù)定義異常示例：BEGINinsertintoemp(empno,ename)values(7788,'testuser');EXCEPTIONWHENDUP_VAL_ON_INDEXTHENDBMS_OUTPUT.PUT_LINE('錯誤：破壞了唯一性的原則!');

WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('錯誤：未知!');END;對預(yù)定義異常情況的處理，無須在程序中定義，由ORACLE自動將其引發(fā)。異常處理非預(yù)定義的ORACLE異常處理

對于這