PLSQL基礎(chǔ)教程必看

1、PL/SQL程序設(shè)計22 TOC o 1-5 h z HYPERLINK l bookmark0第一章PL/SQL程序設(shè)計簡介4 HYPERLINK l bookmark21.2SQL與PL/SQL4 HYPERLINK l bookmark41.2.1什么是PL/SQL?4 HYPERLINK l bookmark61.2.1PL/SQL的好處4 HYPERLINK l bookmark81.2.2PL/SQL可用的SQL語句5 HYPERLINK l bookmark101.3運行PL/SQL程序5 HYPERLINK l bookmark12第二章PL/SQL塊結(jié)構(gòu)和組成元素6 HYPE

2、RLINK l bookmark142.1PL/SQL塊6 HYPERLINK l bookmark242.2PL/SQL結(jié)構(gòu)6 HYPERLINK l bookmark262.3標(biāo)識符6 HYPERLINK l bookmark282.4PL/SQL變量類型7 HYPERLINK l bookmark322.4.1變量類型7 HYPERLINK l bookmark302.4.2復(fù)合類型9 HYPERLINK l bookmark382.4.3使用ROWTYPE11 HYPERLINK l bookmark402.4.4LOB類型*11 HYPERLINK l bookmark502.4.5

3、Bind變量11 HYPERLINK l bookmark522.4.6INDEXBYTABLES122.4.7數(shù)據(jù)類型的轉(zhuǎn)換*132.5運算符和表達(dá)式（數(shù)據(jù)定義）132.5.1關(guān)系運算符132.5.2一般運算符13 HYPERLINK l bookmark542.5.3邏輯運算符14 HYPERLINK l bookmark562.6變量賦值142.6.1字符及數(shù)字運算特點14 HYPERLINK l bookmark582.6.2BOOLEAN賦值14 HYPERLINK l bookmark602.6.3數(shù)據(jù)庫賦值142.6.4可轉(zhuǎn)換的類型賦值152.7變量作用范圉及可見性152.8注釋

4、16 HYPERLINK l bookmark702.9簡單例子172.9.1簡單數(shù)擁插入例子172.9.2簡單數(shù)據(jù)刪除例子17 HYPERLINK l bookmark74第三章PL/SQL流稈控制語句18 HYPERLINK l bookmark763.1條件語句18 HYPERLINK l bookmark783.2CASE表達(dá)式19 HYPERLINK l bookmark803.3循環(huán)19 HYPERLINK l bookmark903.3標(biāo)號和GOTO21 HYPERLINK l bookmark943.4NULL語句21 HYPERLINK l bookmark96第四章游標(biāo)的使

5、用23 HYPERLINK l bookmark984.1游標(biāo)概念234.1.1處理顯式游標(biāo)234.1.2處理隱式游標(biāo)264.1.3游標(biāo)修改和刪除操作27 HYPERLINK l bookmark102第五章異常錯課處理29 HYPERLINK l bookmark1045.1異常處理概念295.1.1預(yù)定義的異常處理295.1.2非預(yù)定義的異常處理3()5.1.3用戶自定義的異常處理315.1.4用戶定義的異常處理32 HYPERLINK l bookmark1065.2異常錯謀傳播335.2.1在執(zhí)行部分引發(fā)異常錯誤335.2.2在聲明部分引發(fā)異常錯謀34 HYPERLINK l book

6、mark1085.3異常錯謀處理編程34 HYPERLINK l bookmark1105.4在PL/SQL中使用SQLCODE,SQLERRM35 HYPERLINK l bookmark114第六章存儲函數(shù)和過稈366.1弓丨言36 HYPERLINK l bookmark1166.2創(chuàng)建函數(shù)36 HYPERLINK l bookmark1226.3存儲過程396.3.1創(chuàng)建過程396.3.2調(diào)用存儲過程406.3.3開發(fā)存儲過程步驟426.3.4與過程相關(guān)數(shù)據(jù)字典43 HYPERLINK l bookmark128第七章包的創(chuàng)建和應(yīng)用447.1引言44 HYPERLINK l bookm

7、ark1307.2包的定義447.3包的開發(fā)步驟457.4包定義的說明457.5子程序重載487.6刪除過程、函數(shù)和包507.7包的管理50 HYPERLINK l bookmark132第八章觸發(fā)器518.1觸發(fā)器類型51 HYPERLINK l bookmark1348.1.1DML觸發(fā)器518.1.2替代觸發(fā)器518.1.3系統(tǒng)觸發(fā)器518.2創(chuàng)建觸發(fā)器518.2.1觸發(fā)器觸發(fā)次序538.2.2創(chuàng)建DML觸發(fā)器53 HYPERLINK l bookmark1368.2.3創(chuàng)建替代(Instead_of)觸發(fā)器548.2.3創(chuàng)建系統(tǒng)爭件觸發(fā)器548.2.4系統(tǒng)觸發(fā)器爭件屬性558.2.5使

8、用觸發(fā)器謂詞568.2.6重新編譯觸發(fā)器56 HYPERLINK l bookmark1388.3刪除和使能觸發(fā)器568.4觸發(fā)器和數(shù)據(jù)字典578.5數(shù)據(jù)庫觸發(fā)器的應(yīng)用舉例5722 #第一章PL/SQL程序設(shè)計簡介PL/SQL是一種高級數(shù)據(jù)庫程序設(shè)計語言，該語言專門用于在各種環(huán)境下對ORACLE數(shù)據(jù)庫進(jìn)行訪問。由于該語言集成于數(shù)據(jù)庫服務(wù)器中，所以PL/SQL代碼可以對數(shù)據(jù)進(jìn)行快速高效的處理。除此Z外，可以在ORACLE數(shù)據(jù)庫的某些客戶端工具中，使用PL/SQL語言也是該語言的一個特點。本章的主要內(nèi)容是討論引入PL/SQL語言的必要性和該語言的主要特點，以及了解PL/SQL語言的重耍性和數(shù)據(jù)庫版

9、木問題。還要介紹一些貫穿全書的更詳細(xì)的高級概念，并在木章的最示就我們在木書案例中使用的數(shù)據(jù)庫表的若干約定做一說明。本章主要重點：PL/SQL概述PL/SQL塊結(jié)構(gòu)PL/SQL流稈運算符和表達(dá)式游標(biāo)異常處理數(shù)據(jù)庫存儲過程和函數(shù)包觸發(fā)器1.2SQL與PL/SQL1.2.1什么是PL/SQL?PL/SQL是ProcedureLanguage&StructuredQueryLanguage的縮寫。ORACLE的SQL是支持ANSI(AmericannationalStandardsInstitute)和ISO92(InternationalStandardsOrganization)標(biāo)準(zhǔn)的產(chǎn)品。PL/

10、SQL是對SQL語言存儲過程語言的擴(kuò)展。從ORACLE6以后，ORACLE的RDBMS附帶了PL/SQL。它現(xiàn)在已經(jīng)成為一種過程處理語言，簡稱PL/SQLo目前的PL/SQL包括兩部分，一部分是數(shù)據(jù)庫引擎部分；另一部分是可嵌入到許多產(chǎn)品(如C語言，JAVA語言等)工具中的獨立引擎?？梢詫⑦@兩部分稱為：數(shù)據(jù)庫PL/SQL和工具PL/SQL0兩者的編程非常相似。都具有編程結(jié)構(gòu)、語法和邏笹機(jī)制。工具PL/SQL另外還增加了用于支持工具(如0RACLEForms)的句法，如：在窗體上設(shè)置按鈕等。本章主要介紹數(shù)據(jù)庫PL/SQL內(nèi)容。1.2.1PL/SQL的好處有利于客戶/服務(wù)器環(huán)境應(yīng)用的運行對于客戶/服

11、務(wù)器環(huán)境來說，真正的瓶頸是網(wǎng)絡(luò)上。無論網(wǎng)絡(luò)多快，只要客戶端與服務(wù)器進(jìn)行大量的數(shù)據(jù)交換。應(yīng)用運行的效率白然就回受到影響。如果使用PL/SQL進(jìn)行編程,將這種具有大最數(shù)據(jù)處理的應(yīng)用放在服務(wù)器端來執(zhí)行。白然就省去了數(shù)據(jù)在網(wǎng)上的傳輸時間。1-2.1.2適合于客戶環(huán)境PL/SQL由于分為數(shù)據(jù)庫PL/SQL部分和工具PL/SQL。對于客戶端來說，PL/SQL可以嵌套到相應(yīng)的T具中，客戶端程序可以執(zhí)行本地包含PL/SQL部分，也可以向服務(wù)發(fā)SQL命令或激活服務(wù)器端的PL/SQL程序運行。1.2.2PL/SQL可用的SQL語句PL/SQL是ORACLE系統(tǒng)的核心語言，現(xiàn)在ORACLE的許多部件都是由PL/SQ

12、L寫成。在PL/SQL中可以使用的SQL語句有：INSERT,UPDATE,DELETE,SELECTINTO,COMMIT,ROLLBACK,SAVEPOINT。提示：在PL/SQL中只能用SQL語句中的DML部分，不能用DDL部分，如果要在PL/SQL中使用DDL（如CREATEtable等）的話，只能以動態(tài)的方式來使用。ORACLE的PL/SQL組件在對PL/SQL稈序進(jìn)行解釋時，同時對在其所使用的表名、列名及數(shù)據(jù)類型進(jìn)行檢查。PL/SQL可以在SQL*PLUS中使用。PL/SQL可以在高級語言中使用。PL/SQL可以在0RACLE的開發(fā)工具中使用。其它開發(fā)工具也可以調(diào)用PL/SQL編寫

13、的過稈和函數(shù)，如PowerBuilder等都可以調(diào)用服務(wù)器端的PL/SQL過稈。1.3運行PL/SQL程序PL/SQL程序的運行是通過ORACLE中的一個引擎來進(jìn)行的。這個引擎可能在ORACLE的服務(wù)器端，也可能在ORACLE應(yīng)用開發(fā)的客戶端。引擎執(zhí)行PL/SQL中的過程性語句，然后將SQL語句發(fā)送給數(shù)據(jù)庫服務(wù)器來執(zhí)行。再將結(jié)果返I叫給執(zhí)行端。22 #第二章PL/SQL塊結(jié)構(gòu)和組成元素2.1PL/SQL塊PL/SQL程序由三個塊紐.成，即聲明部分、執(zhí)行部分、異常處理部分。PL/SQL塊的結(jié)構(gòu)如下：DECLARE/*聲明部分：在此聲明PL/SQL用到的變最,類型及游標(biāo)，以及局部的存儲過程和函數(shù)*

14、/BEGIN/*執(zhí)行部分：過程及SQL語句，即稈序的主要部分*/EXCEPTION/*執(zhí)行異常部分：錯誤處理*/END;其中執(zhí)行部分是必須的。PL/SQL塊可以分為三類：無名塊：動態(tài)構(gòu)造，以能執(zhí)行一次。了稈序：存儲在數(shù)據(jù)庫中的存儲過稈、函數(shù)及包等。當(dāng)在數(shù)據(jù)庫上建立好麻可以在其它稈序中調(diào)用它們。觸發(fā)器：當(dāng)數(shù)據(jù)庫發(fā)生操作時，會觸發(fā)一些事件，從而白動執(zhí)行相應(yīng)的程序。2.2PL/SQL結(jié)構(gòu)PL/SQL塊中可以包含了塊；子塊可以位于PL/SQL中的任何部分;子塊也即PL/SQL中的一條命令；2.3標(biāo)識符PL/SQL程序設(shè)計中的標(biāo)識符定義與SQL的標(biāo)識符定義的要求相同。要求和限制有：標(biāo)識符名不能超過30字

15、符；第一個字符必須為字母；不分大小寫；不能用（減號）；不能是SQL保留字。提示：一戲不要把變量名聲明與表中字段名完全一樣,如果這樣可能得到不正確的結(jié)果.例如：下面的例子將會刪除所有的紀(jì)錄，而不是KING的記錄;DECLAREEnamevarchar2(20)hKING;BEGINDELETEFROMempWHEREename=ename;END;變量命名在PL/SQL中有特別的講究,建議在系統(tǒng)的設(shè)計階段就要求所有編程人員共同遵導(dǎo)一定的要求，使得整個系統(tǒng)的文檔在規(guī)范上達(dá)到要求。下面是建議的命名方法：標(biāo)識符命名規(guī)則例子程序變最VnameVname程序常量CNameCcompanyname游標(biāo)變量N

16、amecursorEmpcursor異常標(biāo)識EnameEjoomany表類型NametabletypeEmprecordtype表NametableEmp記錄類型NamerecordEmprecordSQL*Plus替代變量PnamePsal綁定變量Gr)ameGyearsal2.4PL/SQL變量類型在前面的介紹中，有系統(tǒng)的數(shù)據(jù)類型，也可以自定義數(shù)據(jù)類型。下表是0RACLE類型和PL/SQL中的變量類型的合法使用列表：2.4.1變量類型在ORACLE8i中可以使川的變最類型有:類型子類說明范mORACLE限制CHARCharacterStringRowidNchar定長字符串民族語言字符集0

17、932767可選，確省=12000VARCHAR2Varchar,StringNVARCHAR2可變字符串民族語言字符集0T3276740004000BINARYINTEGER帶符號幣數(shù),為整數(shù)計算優(yōu)化性能NUMBER(p,s)DecDoubleprecisionIntegerIntNumericRealSmallint小數(shù)，NUMBER的子類型高精度實數(shù)整數(shù)，NUMBER的子類型整數(shù)，NUMBER的子類型與NUMBER等價與NUMBER等價整數(shù)，比integer小LONG變長字符串0-214748364732,767字節(jié)DATE日期型公元前4712年1月1日至公元后4712年12月31日BO

18、OLEAN布爾型TRUE,FALSE,NULL不使用ROWID存放數(shù)據(jù)庫行號UROWID通用行標(biāo)識符，字符類型例1插入一條記錄并顯示;DECLARERowjdUROWID;infoVARCHAR2(40);BEGININSERTINTOdeptVALUES(90,SERVICE；BEIJING)RETURNINGrowid,dnamellf:JHto_char(deptno)H，:flllocINTOrowjd,info;DBMS_OUTPUT.PUT_LINECROWID:5|rowJd);DBMS_OUTPUT.PUT_LINE(info);END;其中:RETURNING子句用于檢索IN

19、SERT語句中所影響的數(shù)據(jù)行數(shù),當(dāng)INSERT語句使用VALUES了句插入數(shù)據(jù)時，RETURNING字句還可將列表達(dá)式、ROWID和REF值返冋到輸出變量中。在使用RETURNING了句是應(yīng)注意以下幾點限制：1不能并行DML語句和遠(yuǎn)程對彖一起使用；不能檢索LONG類型信息；當(dāng)通過視圖向基表中插入數(shù)據(jù)時，只能與單基表視圖一起使用。例2修改一條記錄并顯示DECLARERowjdUROWID;infoVARCHAR2(40);BEGINUPDATEdeptSETdeptno=80WHEREDNAME=SERVICE*RETURNINGrowid,dname:|to_char(deptno)|:|lo

20、cINTOrowjd,info;DBMS_OUTPUT.PUT_LINECROWID:5|rowJd);DBMS_OUTPUT.PUT_LINE(info);END;其中：RETURNING子旬用于檢索被修改行信息：當(dāng)UPDATE語旬修改單行數(shù)據(jù)時，RETURNING了句可以檢索被修改行的ROWID和REF值，以及行中被修改列的列表達(dá)式,并可將他們存儲到PL/SQL變量或復(fù)合變量中；當(dāng)UPDATE語句修改多行數(shù)據(jù)時，RETURNING子句可以將被修改行的ROWID和REF值，以及列表達(dá)式值返冋到復(fù)合變量數(shù)組中。在UPDATE中使用RETURNING子句的限制與INSERT語句中對RETURNI

21、NG了句的限制相同。例3.刪除一條記錄并顯示DECLARERowjdUROWID;infoVARCHAR2(40);BEGINDELETEdeptWHEREDNAME=SERVICE,RETURNINGrowid,dname|r：,|to_char(deptno)|,:5|locINTOrowjd,info;DBMS_OUTPUT.PUT_LINECROWID:|row_id);DBMS_OUTPUT.PUT_LINE(info);END;其中：RETURNING了句用于檢索被修改行信息：當(dāng)UPDATE語句修改單行數(shù)據(jù)時，RETURNING了句可以檢索被修改行的ROWID和REF值，以及行中被

22、修改列的列表達(dá)式,并可將他們存儲到PL/SQL變量或復(fù)合變量中；當(dāng)UPDATE語句修改多行數(shù)據(jù)時，RETURNING子句可以將被修改行的ROWID和REF值，以及列表達(dá)式值返冋到復(fù)合變量數(shù)組中。在UPDATE中使用RETURNING子句的限制與INSERT語句中對RETURNING了句的限制相同。2.4.2復(fù)合類型ORACLE在PL/SQL中除了提供象前血介紹的各種類型外，還提供一種稱為復(fù)合類型的類型記錄和表.記錄類型記錄類型是把邏輯相關(guān)的數(shù)據(jù)作為一個單元存儲起來，它必須包括至少一個標(biāo)量型或RECORD數(shù)據(jù)類型的成員，稱作PL/SQLRECORD的域(FIELD),其作用是存放互不相同但邏輯相

23、關(guān)的信息。定義記錄類型語法如下：TYPErecordjypeISRECORD(FieldltypelNOTNULL:=exp1,Field2type2NOTNULL:=exp2,FieldntypenNOTNULL:=expn);例4: #1111 #DECLARETYPEtest_recISRECORD(CodeVARCHAR2(10),NameVARCHAR2(30)NOTNULL:=!abook5);V_booktest_rec;BEGINV_book.code:=123;V_:=C+Programming5;DBMS_OUTPUT.PUT_LINE(v_book.code|v_);EN

24、D;可以用SELECT語句對記錄變量進(jìn)行賦值，只要保證記錄字段與杳詢結(jié)果列表中的字段相配即可。ffl%TYPE定義一個變量，其數(shù)據(jù)類型與已經(jīng)定義的某個數(shù)據(jù)變量的類型相同，或者與數(shù)據(jù)庫表的某個列的數(shù)據(jù)類型相同，這時可以使用%TYPEo使用%TYPE特性的優(yōu)點在于：所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型可以不必知道；所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型可以實時改變。例5：DECLARE-用%TYPE類型定義與表相配的字段TYPEt_RecordISRECORD(T_noemp.empno%TYPE,T_nameemp.ename%TYPE,T_salemp.sal%TYPE);-聲明接收數(shù)據(jù)的變量v_empt_Reco

25、rd;BEGINSELECTempno,ename,salINTOv_empFROMempWHEREempno=7788;DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_emp.t_no川v_emp.t_name|TO_CHAR(v_emp.t_sal);END;例6：DECLAREv_empnoemp.empno%TYPE:=&empno;Typer_recordisrecord(v_nameemp.ename%TYPE,v_salemp.sal%TYPE,v_dateemp.hiredate%TYPE);Recr_record; 1111 #BEGINSELECTename,

26、sal,hiredateINTORecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE(Rec.v_name|,|Rec.v_sal|,-,|Rec.v_date);END;2.4.3使用ROWTYPEPL/SQL提供%ROWTYPE操作符，返冋一個記錄類型，其數(shù)據(jù)類型和數(shù)據(jù)庫表的數(shù)據(jù)結(jié)構(gòu)相一致。使用%ROWTYPE特性的優(yōu)點在于：所引用的數(shù)據(jù)庫中列的個數(shù)和數(shù)據(jù)類型可以不必知道；所引用的數(shù)據(jù)庫中列的個數(shù)和數(shù)據(jù)類型可以實時改變。例7：DECLAREv_empnoemp.empno%TYPE:=&empno;recemp%ROWTYPE;BEGINSEL

27、ECT*INTOrecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINEf姓名：1|rec.ename工資:|rec.sal工作時間:*|rec.hiredate);END;2.4.4LOB類型*ORACLE提供了LOB(LargeOBject)類型，用于存儲大的數(shù)據(jù)對象的類型。ORACLE目前主要支持BFILE,BLOB,CLOB及NCLOB類型。BFILE(Movie)存放大的二進(jìn)制數(shù)據(jù)對象，這些數(shù)據(jù)文件不放在數(shù)據(jù)庫里，而是放在操作系統(tǒng)的某個目錄里，數(shù)據(jù)庫的表里只存放文件的目錄。BLOB(Photo)存儲大的二進(jìn)制數(shù)據(jù)類型。變量存儲大的二進(jìn)制對象的

28、位置。大二進(jìn)制對象的大小v=4GBCLOB(Book)存儲大的字符數(shù)據(jù)類型。每個變量存儲大字符對象的位置，該位置指到大字符數(shù)據(jù)塊。大字符對象的大小=4GB3NCLOB存儲大的NCHAR字符數(shù)據(jù)類型。每個變量存儲大字符對彖的位置，該位置指到大字符數(shù)據(jù)塊。大字符對象的大小=4GB02.4.5Bind變量綁定變量是在主機(jī)環(huán)境中定義的變量。在PL/SQL稈序中可以使用綁定變量作為他們將要使用的其它變最。為了在PL/SQL壞境中聲明綁定變最，使用命令VARIABLEo例如： -13-13- -12-VARIABLEreturn_codeNUMBERVARIABLEreturn_msgVARCHAR2(2

29、0)可以通過SQPlus命令中的PRINT顯示綁定變量的值。例如:PRINTreturn_codePRINTreturn_msg例7：VARIABLEresultNUMBERBEGINSELECT(sari2)+nvl(comm,0)INTO:resultFROMempWHEREempno=7788;END;PRINTresult2.4.6INDEXBYTABLES包括兩個基木成分：.數(shù)據(jù)處理類型為BINARYJNTEGER主鍵;標(biāo)量或記錄數(shù)據(jù)類型的列.TYPEtype_nameISTABLEOFcolumnJype|variable%TYPE|table.column%TYPENOTNULL

30、|table%ROWTYPEINDEXBYBINARYJNTEGER;方法描述EXISTS(n)ReturnTRUEifthenthelementinaPL/SQLtableexists;COUNTReturnsthenumberofelementsthataPL/SQLtablecurrentlycontains;FIRSTReturnthefirstandlast(smallestandlastest)indexnumbersinaLASTPL/SQLtable.ReturnsNULLifthePL/SQLtableisempty.PRIOR(n)Returnstheindexnumber

31、thatprecedesindexninaPL/SQLtable;NEXT(N)ReturnstheindexnumberthatsucceedsindexninaPL/SQLtable;TRIMTRIMremovesoneelementfromtheendofaPL/SQLtable.TRIM(n)removesnelementfromtheendofaPL/SQLtable.DELETEDELETEremovesallelementsfromaPL/SQLtableDELETE(n)removesthenthelementsfromaPL/SQLtableDELETE(m,n)remove

32、sallelementsintherangemtonfromaPL/SQLtable.例&DECLARETYPEdept_table_typeISTABLEOFdept%ROWTYPEINDEXBYBINARYJNTEGER;mvdnametabledepttabletype;vcountnumber(2):=4;BEGINFORintIN1.v_countLOOPSELECT*INTOmy_dname_table(int)FROMdeptWHEREdeptno=int*10;ENDLOOP;FORintINmy_dname_table.FIRST.my_dname_table.LASTLOO

33、PDBMS_OUTPUT.PUT_LINE(Departmentnumber:*|my_dname_table(int).deptno);DBMS_OUTPUT.PUT_LINE(Departmentname:|my_dname_table(int).dname);ENDLOOP;END;2.4.7數(shù)據(jù)類型的轉(zhuǎn)換*隱式類型轉(zhuǎn)換BININTCHARDATELONGNUMBERPLSJNTUROWIDVARCHAR2BINNTCHARDATELONGNUMBERRAWUROWIDVARCHAR22.5運算符和表達(dá)式（數(shù)據(jù)定義）2.5.1關(guān)系運算符運算符意義=等于,!=,-=,A=不等于大于=大于或

34、等于2.5.2一般運算符運算符意義+加號-減號*乘號/除號_賦值號=關(guān)系號1515 #-12-范圍運算符II字符連接符2.5.3邏輯運算符運算符意義ISNULL是空值BETWEEN介于兩考之間IN在一列值中間AND邏輯與OR邏輯或NOT取返，如ISNOTNULL,NOTIN2.6變量賦值在PL/SQL編程中，變量賦值是一個值得注意的地方，它的語法如下：variable:=expression;variable是一個PL/SQL變量,expression是一個PL/SQL表達(dá)式.2.6.1字符及數(shù)字運算特點空值加數(shù)字仍是空值：NULL+v數(shù)字=NULL空值加（連接）字符，結(jié)果為字符：NULL|v

35、字符串=v字符串2.6.2BOOLEAN賦值布爾值只有TRUE,FALSE及NULL三個值。如：DECLAREdoneBOOLEAN;/*thefollowingstatementsarelegal:*/BEGINdone:=FALSE;WHILENOTdoneLOOPNull;ENDLOOP;END;2.6.3數(shù)據(jù)庫賦值數(shù)據(jù)庫賦值是通過SELECT語句來完成的，每次執(zhí)行SELECT語句就賦值一次，一般要求被賦值的變量與SELECT中的列名要一一對應(yīng)。如：例9：DECLAREempjdemp.empno%TYPE:=7788;emp_nameemp.ename%TYPE;wagesemp.sa

36、l%TYPE;BEGINSELECTename,NVL(sal,O)+NVL(comm,0)INTOemp_name,wagesFROMempWHEREempno=empjd;Dbms_output.putjine(emp_name|to_char(wages);END;提示：不能將SELECT語句中的列賦值給布爾變量。2.6.4可轉(zhuǎn)換的類型賦值CHAR轉(zhuǎn)換為NUMBER：使用TO_NUMBER函數(shù)來完成字符到數(shù)字的轉(zhuǎn)換，如：v_total:=TO_NUMBER(100.0)+sal;NUMBER轉(zhuǎn)換為CHAR：使用TO_CHAR函數(shù)可以實現(xiàn)數(shù)字到字符的轉(zhuǎn)換，如：v_comm:=TO_CHAR

37、f123.455)|元；字符轉(zhuǎn)換為日期：使用TO_DATE函數(shù)可以實現(xiàn)字符到II期的轉(zhuǎn)換，如：v_date:=TO_DATE(,2001.07.03,；yyyy.mm.dd,);日期轉(zhuǎn)換為字符使用TO_CHAR函數(shù)可以實現(xiàn)口期到字符的轉(zhuǎn)換，如：v_to_day:=TO_CHAR(SYSDATE,yyyy.mm.ddhh24:mi:ss);2.7變量作用范圍及可見性在PL/SQL編程中，如果在變量的定義上沒有做到統(tǒng)一的話，可能會隱藏一些危險的錯誤，這樣的原因主要是變量的作用范圍所致。與其它高級語言類似，PL/SQL的變最作用范圍特點是：變量的作用范圍是在你所引用的程序單元（塊、了程序、包）內(nèi)。即

38、從聲明變量開始到該塊的結(jié)束。一個變量（標(biāo)識）只能在你所引用的塊內(nèi)是可見的。當(dāng)一個變最超出了作用范圍，PL/SQL引擎就釋放用來存放該變量的空間（因為它可能不用了）。在了塊中重新定義該變量麻，它的作用僅在該塊內(nèi)。1717 #- -6 例10：DECLAREEmesschar(80);BEGINDECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=presidenf;DBMS_OUTPUT.PUT_LINE(V1);EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINEfMore

39、thanonepresident5);END;DECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=manager5;EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(Morethanonemanager5);END;EXCEPTIONWhenothersTHENEmess:=substr(SQLERRM,1,80);DBMS_OUTPUT.PUT_LINE(emess);END;在PL/SQL里，可以使用兩種符號來寫注釋，即：使用雙(減號)加注釋PL/SQL允許用-來

40、寫注釋，它的作用范圍是只能在一行有效。如：V_SalNUMBER(12,2);-T資變量。使用/*/來加一行或多行注釋，如：/I/*文件名：statistcs_sal.sql*/it*/提示：被解釋存放在數(shù)據(jù)庫中的PL/SQL程序，一般系統(tǒng)自動將程序頭部的注釋去掉。只有在PROCEDUREZ后的注釋才被保留；另外程序中的空行也自動被去掉。2.9簡單例子2.9.1簡單數(shù)據(jù)插入例子例11：/*木例了僅是一個簡單的插入，不是實際應(yīng)用。*/DECLAREv_enameVARCHAR2(20):=Bill;v_salNUMBER(7,2):=1234.56;v_deptnoNUMBER(2):=10;v

41、_empnoNUMBER(4):=8888;BEGININSERTINTOemp(empno,ename,JOB,sal,deptno,hiredate)VALUES(v_empno,v_ename,*Manager,v_sal,v_deptno,TO_DATE(1954.06.09,yyyy.mm.dcT);COMMIT;END;2.9.2簡單數(shù)據(jù)刪除例子例12：/*木例了僅是一個簡單的刪除例了，不是實際應(yīng)用。*/DECLAREv_empnonumber(4):=8888;BEGINDELETEFROMempWHEREempno=v_empno;COMMIT;END; 21例6.-20- |

42、8 -第三章PL/SQL流程控制語句介紹PL/SQL的流程控制語句，包括如下三類:控制語句：IF語旬循環(huán)語句：LOOP語句，EXIT語句順序語句:GOTO語句,NULL語句3.1條件語句IFv布爾表達(dá)式THENPL/SQL和SQL語句ENDIF;IFv布爾表達(dá)式THENPL/SQL和SQL語句ELSE其它語句ENDIF;IFv布爾表達(dá)式THENPL/SQL和SQL語句ELSIF其它布爾表達(dá)式THEN其它語句ELSIF其它布爾表達(dá)式THEN其它語句ELSE其它語句ENDIF;提示：ELSIF不能寫成ELSEIF例1：DECLAREv_empnoemp.empno%TYPE:=&empno;V_s

43、alaryemp.sal%TYPE;V_commentVARCHAR2(35);BEGINSELECTsalINTOv_salaryFROMempWHEREempno=v_empno;IFv_salary1500THENV_comment:=Tairlyless1;ELSIFv_salary3000THENV_comment:=*Alittlemore：ELSEV_comment:=*Lotsofsalary5;ENDIF;DBMS_OUTPUT.PUT_LINE(V_comment);END;3.2CASE表達(dá)式CASEselectorWHENexpressionlTHENresultlWH

44、ENexpression2THENresult2WHENexpressionNTHENresultNELSEresultN+1END;例2:DECLAREV_gradechar(1):=UPPER。&p_grade)；V_appraisalVARCHAR2(20);BEGINV_appraisal:=CASEv_gradeWHEN*A!THENExcellenfWHEN*B5THEN(VeryGood*WHENCTHENGoodELSE*Nosuchgrade5END;DBMS_OUTPUT.PUT_LINEfGrade:,|v_grade|rAppraisal:*|v_appraisal);

45、END;3.3循環(huán)1-簡單循環(huán)LOOP要執(zhí)行的語句;廣條件滿足，退出循環(huán)語彳/EXITWHENv條件語句ENDLOOP;例3.DECLAREintNUMBER(2):=0;BEGINLOOPint:=int+1;DBMS_OUTPUT.PUT_LINE(int的當(dāng)前值為fllint);EXITWHENint=10;ENDLOOP;END;WHILE循環(huán)WHILEv布爾表達(dá)式LOOP要執(zhí)行的語句；ENDLOOP;例4DECLARExNUMBER;BEGINx:=1;WHILEx括起來的標(biāo)識符*/例7:DECLAREV_counterNUMBER:=1;BEGINLOOPDBMS_OUTPUT.P

46、UT_LINECV_counter的當(dāng)前值為:*|V_counter);Vcounter:=vcounter+1;IFv_counter10THENGOTOLENDofLOOP;ENDIF;ENDLOOP;l_ENDofLOOPDBMS_OUTPUT.PUT_LINECV_counter的當(dāng)前值為:*|V_counter);END;3.4NULL語句在PL/SQL程序中，可以用null語句來說明“不用做任何事情”的意思，相當(dāng)于一個占位符，可以使某些語句變得有意義，提高程序的可讀性。如：2222- #- -|8 #-DECLAREBEGINIFv_numISNULLTHENGOTOprintl;

47、ENDIF;print1NULL;-不需要處理任何數(shù)據(jù)。END;- - -|8 #-第四章游標(biāo)的使用在PL/SQL稈序中，對于處理多行記錄的事務(wù)經(jīng)常使用游標(biāo)來實現(xiàn)。4.1游標(biāo)概念為了處理SQL語他ORACLE必須分配一片叫上下文(contextarea)的區(qū)域來處理所必需的信息，其中包括要處理的行的數(shù)目，一個指向語句被分析以麻的表示形式的指針以及查詢的活動集(activeset)。游標(biāo)是一個指向上下文的句柄(handle)或指針。通過游標(biāo)，PL/SQL可以控制上下文區(qū)和處理語句時上下文區(qū)會發(fā)生些什么事情。對于不同的SQL語仙游標(biāo)的使用情況不同:SQL語句游標(biāo)非查詢語句隱式的結(jié)果是單行的杏詢語句

48、隱式的或顯示的結(jié)果是多行的杏詢語旬顯示的4.1.1處理顯式游標(biāo)顯式游標(biāo)處理顯式游標(biāo)處理需四個PL/SQL步驟：定義游標(biāo)：就是定義一個游標(biāo)名，以及與其相對應(yīng)的SELECT語句。格式：CURSORcursor_name(paraineter,parameter.)ISselect_statement;游標(biāo)參數(shù)只能為輸入?yún)?shù)，其格式為：parameternameINdatatype:=|DEFAULTexpression在指定數(shù)據(jù)類型時，不能使用長度約朿。女UNUMBER(4)、CHAR(10)等都是錯謀的。打開游標(biāo)：就是執(zhí)行游標(biāo)所對應(yīng)的SELECT語旬，將其杏詢結(jié)果放入T作區(qū)，并且指針指向T作區(qū)的

49、首部，標(biāo)識游標(biāo)結(jié)果集合。如果游標(biāo)杏詢語句中帶有FORUPDATE選項，OPEN語句還將鎖定數(shù)據(jù)庫表中游標(biāo)結(jié)果集合對應(yīng)的數(shù)據(jù)行。格式：OPENcursor_name(parameter=value,parameter=value.);在向游標(biāo)傳遞參數(shù)時，可以使川與函數(shù)參數(shù)相同的傳值方法，即位置表示法和名稱表示法。PL/SQL程序不能用OPEN語旬重復(fù)打開一個游標(biāo)。提取游標(biāo)數(shù)據(jù)：就是檢索結(jié)果集合中的數(shù)據(jù)行，放入指定的輸出變量中。格式：FETCHcursor_nameINTOvariablejist|record_variable;對該記錄進(jìn)行處理；繼續(xù)處理，宜到活動集合中沒有記錄；關(guān)閉游標(biāo)：當(dāng)提取

50、和處理完游標(biāo)結(jié)果集合數(shù)據(jù)示，丿應(yīng)及時關(guān)閉游標(biāo)，以帑放該游標(biāo)所占一用的系統(tǒng)資源，并使該游標(biāo)的T作區(qū)變成無效，不能再使用FETCH語句取其中數(shù)據(jù)。關(guān)閉后的游標(biāo)可以使用OPEN語句重新打開。格式：CLOSEcursor_name;24注：定義的游標(biāo)不能有INTO了句。例1游標(biāo)參數(shù)的傳遞方法。DECLAREDeptRecdept%ROWTYPE;Dept_namedept.dname%TYPE;Deptjocdept.loc%TYPE;CURSORc1ISSELECTdname,locFROMdeptWHEREdeptno=30;CURSORc2(dept_noNUMBERDEFAULT10)ISSE

51、LECTdname,locFROMdeptWHEREdeptno=dept_no;CURSORc3(dept_noNUMBERDEFAULT10)ISSELECT*FROMdeptWHEREdeptno20);LOOPFETCHc3INTOdeptrec;EXITWHENc3%NOTFOUND;DBMS_OUTPUT.PUT_LINE(deptrec.deptno|r*|deptrec.dnameTldeptrec.loc);ENDLOOP;CLOSEc3;END;2游標(biāo)屬性%FOUND布爾型屬性，當(dāng)最近一次讀記錄時成功返I叫，則值為TRUE；%NOTFOUND布爾型屬性，與%FOUND相反；

52、%ISOPEN布爾型屬性,當(dāng)游標(biāo)已打開時返回TRUE； -25-25-|8 #-%ROWCOUNT數(shù)字型屬性，返冋已從游標(biāo)中讀取的記錄數(shù)。例2：給工資低于1200的員工增加工資50。DECLAREv_empnoemp.empno%TYPE;v_salemp.sal%TYPE;CURSORcISSELECTempno,salFROMemp;BEGINOPENc;LOOPFETCHcINTOvempno,vsal;EXITWHENC%NOTFOUND;IFv_sal=1200THENUPDATEempSETsal=sal+50WHEREempno=v_empno;DBMS_OUTPUT.PUT_L

53、INE（編碼為,|v_empno|,T資已更新!）；ENDIF;DBMS_OUTPUT.PUT_LINEC記錄數(shù):|C%ROWCOUNT）;ENDLOOP;CLOSEc;END;3.游標(biāo)的FOR循環(huán)PL/SQL語言提供了游標(biāo)FOR循環(huán)語句，白動執(zhí)行游標(biāo)的OPEN、FETCH.CLOSE語句和循環(huán)語句的功能；當(dāng)進(jìn)入循環(huán)時，游標(biāo)FOR循環(huán)語句自動打開游標(biāo)，并提取第一行游標(biāo)數(shù)據(jù)，當(dāng)程序處理完當(dāng)前所提取的數(shù)據(jù)而進(jìn)入下一次循環(huán)時，游標(biāo)FOR循環(huán)語句自動提取下一行數(shù)據(jù)供稈序處理，當(dāng)提取完結(jié)果集合中的所有數(shù)據(jù)行后結(jié)束循環(huán)，并白動關(guān)閉游標(biāo)。格式：FORindex_variableINcursor_nameva

54、lue,value.LOOP-游標(biāo)數(shù)據(jù)處理代碼ENDLOOP;其中：index_variable為游標(biāo)FOR循環(huán)語句隱含聲明的索引變量，該變量為記錄變量，其結(jié)構(gòu)與游標(biāo)杏詢語句返冋的結(jié)構(gòu)集合的結(jié)構(gòu)相同。在程序中可以通過引川該索引記錄變最元素來讀取所提取的游標(biāo)數(shù)據(jù)，index_variable中備元素的名稱與游標(biāo)杳詢語句選擇列表中所制定的列名相同。如果在游標(biāo)查詢語句的選擇列表中存在計算列，則必須為這些計算列指定別名后才能通過游標(biāo)FOR循環(huán)語句中的索引變量來訪問這些列數(shù)據(jù)。注：不要在程序中對游標(biāo)進(jìn)行人工操作；不要在程序中定義用于控制FOR循環(huán)的記錄。例3：DECLARECURSORc_salISSE

55、LECTempno,ename,salFROMemp;BEGIN-隱含打開游標(biāo)FORvsalINc_salLOOP-隱含執(zhí)行一個FETCH語句DBMS_OUTPUT.PUT_LINE(to_char(v_sal.empno)|r*|v_sal.ename|T|to_char(v_sal.sal);隱含監(jiān)測c_sal%NOTFOUNDENDLOOP;-隱含關(guān)閉游標(biāo)END;例4：當(dāng)所聲明的游標(biāo)帶有參數(shù)時，通過游標(biāo)FOR循環(huán)語句為游標(biāo)傳遞參數(shù)。DECLARECURSORc1(dept_noNUMBERDEFAULT10)ISSELECTdname,locFROMdeptWHEREdeptno=dep

56、t_no;BEGINDBMS_OUTPUT.PUT_LINECdept_no參數(shù)值為30：);FORc1_recINc1(30)LOOPDBMS_OUTPUT.PUT_LINE(c1_rec.dname|rqicl.rec.loc);ENDLOOP;DBMS_OUTPUT.PUT_LINE(CHR(10)使用默認(rèn)的dept_no參數(shù)值10：);FORc1_recINc1LOOPDBMS_OUTPUT.PUT_LINE(c1_rec.dname|rqicl.rec.loc);ENDLOOP;END;例5：PL/SQL還允許在游標(biāo)FOR循環(huán)語句中使用了查詢來實現(xiàn)游標(biāo)的功能。BEGINFORc1_r

57、ecIN(SELECTdname,locFROMdept)LOOPDBMS_OUTPUT.PUT_LINE(c1ec.dname|卜T|c1_rec.loc);ENDLOOP;END;4.1.2處理隱式游標(biāo)顯式游標(biāo)主要是用于對杏詢語旬的處理，尤其是在杏詢結(jié)果為多條記錄的情況下；而對于菲杏詢語句，如修改、刪除操作，則由ORACLE系統(tǒng)自動地為這些操作設(shè)置游標(biāo)并創(chuàng)建其工作區(qū)，這些由系統(tǒng)隱含創(chuàng)建的游標(biāo)稱為隱式游標(biāo)，隱式游標(biāo)的名字為SQL,這是由ORACLE系統(tǒng)定義的。對于隱式游標(biāo)的操作,如定義、打開、取值及關(guān)閉操作，都由ORACLE系統(tǒng)白動地完成，無需用戶進(jìn)行處理。用戶只能通過隱式游標(biāo)的相關(guān)屬性，來

58、完成相應(yīng)的操作。在隱式游標(biāo)的工作區(qū)中，所存放的數(shù)據(jù)是與用戶自定義的顯示游標(biāo)無關(guān)的、最新處理的一條SQL語句所包含的數(shù)據(jù)。格式調(diào)用為：SQL%注：INSERT,UPDATE,DELETE,SELECT語句中不必明確定義游標(biāo)。隱式游標(biāo)屬性SQL%FOUND布爾型屬性，當(dāng)最近一次讀記錄時成功返回，貝II值為true；SQL%NOTFOUND布爾型屬性，與%found相反；SQL%ROWCOUNT數(shù)字型屬性，返回已從游標(biāo)中讀取得記錄數(shù)；SQL%ISOPEN布爾型屬性，取值總是FALSE.SQL命令執(zhí)行完畢立即關(guān)閉隱式游標(biāo)。例6:刪除EMP表中某部門的所有員工,如果該部門中已沒有員工,則在DEPT表中刪

59、除該部門。DECLAREV_deptnoemp.deptno%TYPE:=&p_deptno;BEGINDELETEFROMempWHEREdeptno=v_deptno;IFSQL%NOTFOUNDTHENDELETEFROMdeptWHEREdeptno=v_deptno;ENDIF;END;4.1.3游標(biāo)修改和刪除操作游標(biāo)修改和刪除操作是指在游標(biāo)定位下，修改或刪除表中指定的數(shù)據(jù)行。這時，要求游標(biāo)查詢語句中必須使用FORUPDATE選項，以便在打開游標(biāo)時鎖定游標(biāo)結(jié)果集合在表中對應(yīng)數(shù)據(jù)行的所有列和部分列。為了對正在處理（杏詢）的行不被另外的用戶改動，ORACLE提供一個FORUPDATE了句

60、來對所選擇的行進(jìn)行鎖住。該需求迫使0RACLE鎖定游標(biāo)結(jié)果集合的行，可以防止其他事務(wù)處理更新或刪除相同的行，真到您的事務(wù)處理提交或冋退為止。語法：SELECTFROM.FORUPDATEOFcolumn,column.NOWAIT如果另一個會話已對活動集中的行加了鎖，那么SELECTFORUPDATE操作一直等待到其它的會話特放這些鎖示才繼續(xù)自己的操作，對于這種情況，當(dāng)加上NOWAIT了句時，如果這些行真的被另一個會話鎖定，則OPEN立即返冋并給出：ORA-0054:resourcebusyandacquirewithnowaitspecified如果使用FORUPDATE聲明游標(biāo),則可在DE