PLSQL編程基礎(chǔ)課件

第7章PL/SQL編程基礎(chǔ)前面所學(xué)習(xí)的SQL是操作關(guān)系性數(shù)據(jù)庫(kù)的一種通用語(yǔ)言，但是SQL本身是一種非過(guò)程化的語(yǔ)言，即不用指明執(zhí)行的具體方法和途徑，而是簡(jiǎn)單地調(diào)用相應(yīng)語(yǔ)句直接取得結(jié)果即可。所以SQL本身并不適合在復(fù)雜的業(yè)務(wù)流程下使用，為了解決這一問題，Oracle提供了PL/SQL編程，這是一種過(guò)程化編程語(yǔ)言，與JAVA語(yǔ)言一樣關(guān)注于處理細(xì)節(jié)，可以實(shí)現(xiàn)較為復(fù)雜的業(yè)務(wù)邏輯。PLSQL編程基礎(chǔ)7.1PL/SQL簡(jiǎn)介PL/SQL語(yǔ)法結(jié)構(gòu)DECLARE--聲明部分，例如：定義變量、常量、游標(biāo)BEGIN--程序編寫、SQL語(yǔ)句EXECEPTION--處理異常END;/聲明部分（DECLARE）：包含變量定義、用戶定義的PL/SQL類型、游標(biāo)、引用的函數(shù)或過(guò)程。執(zhí)行部分（BEGIN）：包含變量賦值、對(duì)象初始化、條件結(jié)構(gòu)、迭代結(jié)構(gòu)、嵌套的PL/SQL塊，或是對(duì)局部或者存儲(chǔ)PL/SQL命名塊的調(diào)用。異常部分（exception）：包含錯(cuò)誤處理語(yǔ)句，該語(yǔ)句可以像執(zhí)行部分一樣使用所有項(xiàng)。結(jié)束部分（END）:程序執(zhí)行到END表示結(jié)束，分號(hào)用戶結(jié)束塊，正斜杠（/）執(zhí)行塊程序PLSQL編程基礎(chǔ)既沒有變量，也沒有流程控制（分支、循環(huán)）。PL/SQL與SQL區(qū)別：SQLPL/SQLPL/SQL是結(jié)構(gòu)化和過(guò)程化的結(jié)合體。每條SQL語(yǔ)句逐一發(fā)送給數(shù)據(jù)庫(kù)。一次性將多條SQL語(yǔ)言一起發(fā)送給數(shù)據(jù)庫(kù)7.1PL/SQL簡(jiǎn)介PLSQL編程基礎(chǔ)最簡(jiǎn)單的PL/SQL塊可以不做任何事情，但是在PL/SQL編程過(guò)程中要求，執(zhí)行塊至少要有一條語(yǔ)句，即使這條語(yǔ)句只是編寫一個(gè)NULL也行。范例：編寫不做任何工作的PL/SQL塊。BEGINNULL;END;/范例：編寫一個(gè)簡(jiǎn)單的PL/SQL程序DECLAREv_numnumber;BEGINv_num:=30;DBMS_output.put_line(‘v_num變量的內(nèi)容是：’||v_num);END;7.1PL/SQL簡(jiǎn)介PLSQL編程基礎(chǔ)范例：編寫PL/SQL塊，輸入一個(gè)雇員編號(hào)，而后取得指定的雇員姓名。Declarev_enonumber;v_enamevarchar2(10);BeginV_eno:=&empno;--由鍵盤輸入雇員編號(hào)Selectenameintov_enamefromempwhereempno=v_eno;DBMS_OUTPUT.put_line('編號(hào)為:'||v_eno||'雇員的名字為：'||v_ename);End;7.1PL/SQL簡(jiǎn)介PLSQL編程基礎(chǔ)7.2

變量的聲明與賦值變量聲明并使用變量：變量名稱可以有字母、數(shù)字、_、$、#等組成。所有變量名稱要求以字母開頭，不能是Oracle中的保留字變量的長(zhǎng)度最多只能為30個(gè)字符變量不區(qū)分大小寫PLSQL編程基礎(chǔ)7.2

變量的聲明與賦值聲明變量語(yǔ)法：變量名稱[CONSTANT]類型[notNULL][:=value]使用%TYPE聲明變量類型：在編寫PL/SQL程序時(shí)，如果希望某一個(gè)變量與指定數(shù)據(jù)表中某一列的類型一樣，則可以采用“變量定義”表名稱.表名稱%TYPE的格式PLSQL編程基礎(chǔ)7.2

變量的聲明與賦值Declarev_enoemp.empno%type;v_enameemp.ename%type;BeginDBMS_output.put_line('請(qǐng)輸入雇員編號(hào)：');v_eno:=&empno;selectenameintov_enamefromempwhereempno=v_eno;DBMS_output.put_line('編號(hào)為：'||v_eno||'雇員的名字為：'||v_ename);end;PLSQL編程基礎(chǔ)7.2

變量的聲明與賦值使用%ROWTYPE聲明變量類型PL/SQL提供了一種%ROWTYPE，使用此標(biāo)記可以定義表中一行記錄的類型。當(dāng)用戶使用了“select……into……”將表中的一行記錄設(shè)置為ROWTYPE類型的變量中時(shí)，就可以利用“rowtype變量.表字段”的方式取得表中每行對(duì)應(yīng)的列數(shù)據(jù)。PLSQL編程基礎(chǔ)7.2

變量的聲明與賦值Declarev_deptrowdept%rowtype;----裝載一行dept記錄

Beginselect*intov_deptrowfromdeptwheredeptno=10;DBMS_output.put_line('部門編號(hào)：'||v_deptrow.deptno||'名稱：'||v_deptrow.dname||'位置：'||v_deptrow.loc);End;PLSQL編程基礎(chǔ)7.3

標(biāo)量類型數(shù)值型標(biāo)量類型字符型日期型大對(duì)象布爾PLSQL編程基礎(chǔ)7.3

標(biāo)量類型No.分類數(shù)據(jù)類型描述1數(shù)值型Number(數(shù)據(jù)總長(zhǎng)度[,小數(shù)位長(zhǎng)度])Number是一種表示數(shù)字的數(shù)據(jù)類型?？陕暶鞅４鏀?shù)據(jù)類型的整數(shù)位和小數(shù)位的精度，在數(shù)據(jù)庫(kù)中是以十進(jìn)制格式存儲(chǔ)，在計(jì)算時(shí)，系統(tǒng)將其變成二進(jìn)制數(shù)據(jù)運(yùn)算，占32字節(jié)。BINARY_INTEGER不存儲(chǔ)在數(shù)據(jù)庫(kù)中，只能在PL/SQL中使用的帶符號(hào)整數(shù)，范圍為-231-231，如果發(fā)生溢出，則自動(dòng)變?yōu)閚umber型數(shù)據(jù)。23PLS_INTEFER有符號(hào)的整數(shù)，范圍為-231-231，可以直接進(jìn)行數(shù)學(xué)運(yùn)算，溢出則觸發(fā)異常，與Number相比，其占空間小，性能好4BINARY_FLOAT單精度32位浮點(diǎn)類型，占5個(gè)字節(jié)5BINARY_DOUBLE雙精度浮點(diǎn)數(shù)類型，占9個(gè)字節(jié)PLSQL編程基礎(chǔ)7.3

標(biāo)量類型No.分類數(shù)據(jù)類型描述12字符型RAW保存固定長(zhǎng)度的二進(jìn)制數(shù)據(jù)，最多可以存放2000個(gè)字節(jié)的數(shù)據(jù)LONGRAW存儲(chǔ)二進(jìn)制數(shù)據(jù)（圖片、音樂等），最多可以存儲(chǔ)2G大小數(shù)據(jù)1314ROWID數(shù)據(jù)表中每行記錄的唯一物理地址標(biāo)示，只支持物理行ID，不支持邏輯行ID15UROWID支持物理行ID和邏輯行IDPLSQL編程基礎(chǔ)7.3

標(biāo)量類型No.分類數(shù)據(jù)類型描述16日期型DATADATA是一個(gè)7字節(jié)的列，可以保存時(shí)期和時(shí)間，不包含毫秒TIMESTAMPDATA子類型，包含日期和時(shí)間，時(shí)間部分包含毫秒，有TIMESTAMPWITHTIMEZONE和TIMESTAMPWITHLOCALTIMEZONE1718INTERVALDATA的子類型，用于管理時(shí)間間隔，有INTERVALDAY

TOSECOND和INTERVALYEARTOMONTH兩種子類型PLSQL編程基礎(chǔ)7.3

標(biāo)量類型No.分類數(shù)據(jù)類型描述19大對(duì)象CLOBCLOB數(shù)據(jù)類型代表字符型大對(duì)象，最大存儲(chǔ)4GB的字符串?dāng)?shù)據(jù)NCLOB存儲(chǔ)UNICODE編碼的大文本數(shù)據(jù)，最多可以存儲(chǔ)4GB的非結(jié)構(gòu)化的二進(jìn)制數(shù)據(jù)2021BLOBBLOB數(shù)據(jù)類型列可以包含最大4GB的非結(jié)構(gòu)化二進(jìn)制數(shù)據(jù)22BFILE包含存儲(chǔ)在外部文件系統(tǒng)上文件的索引，最大不超過(guò)4GB23布爾BOOLEAN布爾類型，可以設(shè)置的內(nèi)容為TRUE、FALSE、NULLPLSQL編程基礎(chǔ)7.4

程序結(jié)構(gòu)PL/SQL程序與其他編程語(yǔ)言一樣，也擁有自己的3中程序結(jié)構(gòu)，即順序結(jié)構(gòu)、分支結(jié)構(gòu)、循環(huán)結(jié)構(gòu)。開始語(yǔ)句1語(yǔ)句2語(yǔ)句n結(jié)束順序結(jié)構(gòu)判斷條件語(yǔ)句2語(yǔ)句n結(jié)束語(yǔ)句1分支結(jié)構(gòu)語(yǔ)句1語(yǔ)句2判斷條件循環(huán)結(jié)構(gòu)PLSQL編程基礎(chǔ)7.4.1

分支結(jié)構(gòu)IF語(yǔ)句If語(yǔ)句IF……else語(yǔ)句IF……elseif……else語(yǔ)句If判斷條件Then

滿足條件執(zhí)行語(yǔ)句；Endif;If判斷條件then

滿足條件執(zhí)行語(yǔ)句；Else

不滿足條件執(zhí)行語(yǔ)句；EndIF;If判斷條件1

then

滿足條件1執(zhí)行語(yǔ)句；Elseif判斷條件2then……Else

所有條件不滿足執(zhí)行語(yǔ)句；ENDif;PLSQL編程基礎(chǔ)7.4.1

分支結(jié)構(gòu)范例：查詢emp表的工資。輸入員工編號(hào)，根據(jù)編號(hào)查詢工資。如果工資高于3000元，則顯示高工資；如果工資大于2000元?jiǎng)t顯示中等工資；如果小于2000元，則顯示低工資。PLSQL編程基礎(chǔ)DECLARE v_empSal emp.sal%TYPE; --定義變量與emp.sal字段類型相同

v_empName emp.ename%TYPE; --定義變量與emp.ename字段類型相同

v_eno emp.empno%TYPE; --定義變量與emp.empno字段類型相同BEGIN v_eno:=&inputEmpno; --用戶輸入要查找的雇員編號(hào)

--根據(jù)輸入的雇員編號(hào)查找雇員姓名及工資

SELECTename,salINTOv_empName,v_empSalFROMempWHEREempno=v_eno; IFv_empSal>3000THEN --判斷

DBMS_OUTPUT.put_line(v_empName||'的工資屬于高工資！'); ELSIFv_empSal>2000THEN --判斷

DBMS_OUTPUT.put_line(v_empName||'的工資屬于中等工資！'); ELSE DBMS_OUTPUT.put_line(v_empName||'的工資屬于低工資！'); ENDIF;END;7.4.1

分支結(jié)構(gòu)PLSQL編程基礎(chǔ)范例：用戶輸入一個(gè)雇員編號(hào)，根據(jù)它所在的部門給上漲工資，規(guī)則：·10部門上漲10%，20上漲20%，30上漲30%；·

但是要求最高不能超過(guò)5000，超過(guò)5000就停留在5000。7.4.1

分支結(jié)構(gòu)PLSQL編程基礎(chǔ)7.4.1

分支結(jié)構(gòu)DECLARE v_empSal emp.sal%TYPE; --定義變量與emp.sal字段類型相同 v_dno emp.deptno%TYPE; --定義變量與emp.deptno字段類型相同 v_eno emp.empno%TYPE; --定義變量與emp.empno字段類型相同BEGIN v_eno:=&inputEmpno; --用戶輸入要查找的雇員編號(hào) SELECTdeptno,salINTOv_dno,v_empSalFROMempWHEREempno=v_eno; IFv_dno=10THEN IFv_empSal*1.1>5000THEN UPDATEempSETsal=5000WHEREempno=v_eno; ELSE UPDATEempSETsal=sal*1.1WHEREempno=v_eno; ENDIF; ELSIFv_dno=20THEN IFv_empSal*1.2>5000THEN UPDATEempSETsal=5000WHEREempno=v_eno; ELSE UPDATEempSETsal=sal*1.2WHEREempno=v_eno; ENDIF; ELSIFv_dno=30THEN IFv_empSal*1.3>5000THEN UPDATEempSETsal=5000WHEREempno=v_eno; ELSE UPDATEempSETsal=sal*1.3WHEREempno=v_eno; ENDIF; ELSE null; ENDIF;END;PLSQL編程基礎(chǔ)7.4.1

分支結(jié)構(gòu)CASE語(yǔ)句Case語(yǔ)句是一種多條件的判斷語(yǔ)句，其功能與IF……elseif……else類似Case語(yǔ)法：Case[變量]when[值|表達(dá)式]THEN執(zhí)行語(yǔ)句塊；when[值|表達(dá)式]THEN執(zhí)行語(yǔ)句塊；Else條件都不滿足時(shí)執(zhí)行語(yǔ)塊；EndcasePLSQL編程基礎(chǔ)7.4.1

分支結(jié)構(gòu)范例：使用CASE進(jìn)行多條件判斷DECLARE v_salary emp.sal%TYPE; v_eno emp.empno%TYPE;BEGIN v_eno:=&inputEmpno; SELECTsalINTOv_salaryFROMempWHEREempno=v_eno; CASE WHENv_salary>=3000THEN DBMS_OUTPUT.put_line('雇員：'||v_eno||'的收入為高工資。'); WHENv_salary>=2000ANDv_salary<3000THEN DBMS_OUTPUT.put_line('雇員：'||v_eno||'的收入為中等工資。'); ELSE DBMS_OUTPUT.put_line('雇員：'||v_eno||'的收入為低工資。'); ENDCASE;END;/PLSQL編程基礎(chǔ)7.4.1

分支結(jié)構(gòu)范例：輸入雇員編號(hào)，根據(jù)雇員的職位進(jìn)行工資提升，提升要求如下

·如果職位是辦事員（CLERK），工資增長(zhǎng)5%；

·如果職位是銷售人員（SALESMAN），工資增長(zhǎng)8%；

·如果職位為經(jīng)理（MANAGER），工資增長(zhǎng)10%；

·如果職位為分析員（ANALYST），工資增長(zhǎng)20%；

·如果職位為總裁（PRESIDENT），工資不增長(zhǎng)。PLSQL編程基礎(chǔ)7.4.2

循環(huán)結(jié)構(gòu)在PL/SQL程序中，循環(huán)結(jié)構(gòu)一共定義了兩種，及LOOP循環(huán)和for循環(huán)。其中，LOOP主要使用在不確定循環(huán)次數(shù)的操作中，而FOR使用在明確知道循環(huán)次數(shù)的操作中。LOOP循環(huán)While……LOOPLOOP

循環(huán)執(zhí)行的語(yǔ)句塊；exitwhen循環(huán)結(jié)束條件；

循環(huán)結(jié)束條件修改；ENDLOOPWHILE（循環(huán)結(jié)束條件）LOOP

循環(huán)執(zhí)行的語(yǔ)句塊；

循環(huán)結(jié)束條件修改；ENDLOOPPLSQL編程基礎(chǔ)7.4.2

循環(huán)結(jié)構(gòu)For循環(huán)語(yǔ)法FOR循環(huán)索引IN[REVERSE]循環(huán)區(qū)域下限…循環(huán)區(qū)域上限LOOP循環(huán)執(zhí)行的語(yǔ)句塊；ENDLOOP;PLSQL編程基礎(chǔ)7.4.2

循環(huán)結(jié)構(gòu)范例：使用LOOP循環(huán)DECLARE v_iNUMBER:=1; --定義一個(gè)變量，用于循環(huán)BEGIN LOOP DBMS_OUTPUT.put_line('v_i='||v_i); EXITWHENv_i>=3; v_i:=v_i+1; ENDLOOP;END;PLSQL編程基礎(chǔ)7.4.2

循環(huán)結(jié)構(gòu)范例：使用WHILE…LOOP循環(huán)DECLARE v_iNUMBER:=1; --定義一個(gè)變量，用于循環(huán)BEGIN WHILE(v_i<=3)LOOP DBMS_OUTPUT.put_line('v_i='||v_i); v_i:=v_i+1; ENDLOOP;END;PLSQL編程基礎(chǔ)7.4.2

循環(huán)結(jié)構(gòu)范例：使用FOR循環(huán)DECLARE v_iNUMBER:=1; --定義一個(gè)變量，用于循環(huán)BEGIN FORv_iIN1..3LOOP DBMS_OUTPUT.put_line('v_i='||v_i); ENDLOOP;END;PLSQL編程基礎(chǔ)7.4.2

循環(huán)結(jié)構(gòu)循環(huán)控制在正常循環(huán)操作中，如果需要結(jié)束循環(huán)或者退出當(dāng)前循環(huán)，可以使用EXIT與CONTINUE語(yǔ)句來(lái)完成。范例：使用EXIT結(jié)束循環(huán)操作DECLARE v_iNUMBER:=1; --定義一個(gè)變量，用于循環(huán)BEGIN FORv_iIN1..10LOOP IFv_i=3THEN --當(dāng)v_i變量增長(zhǎng)到3時(shí)結(jié)束循環(huán) EXIT; ENDIF; DBMS_OUTPUT.put_line('v_i='||v_i); ENDLOOP;END;PLSQL編程基礎(chǔ)7.4.2

循環(huán)結(jié)構(gòu)循環(huán)控制范例：使用CONTINUE控制循環(huán)操作DECLARE v_iNUMBER:=1; --定義一個(gè)變量，用于循環(huán)BEGIN FORv_iIN1..10LOOP IFMOD(v_i,2)=0THEN --為偶數(shù)的時(shí)候不執(zhí)行后續(xù)方法體 CONTINUE; ENDIF; DBMS_OUTPUT.put_line('v_i='||v_i); ENDLOOP;END;PLSQL編程基礎(chǔ)7.5

內(nèi)部程序塊對(duì)每個(gè)PL/SQL程序塊，其基本的組成部分就是DECLARE、BEGIN、END，如果用戶有需要，也可以在一個(gè)程序中定義多個(gè)子程序模塊，語(yǔ)法如下：DECLARE----聲明部分，例如定義變量、常量、游標(biāo)BEGIN--程序編寫、SQL語(yǔ)句；DECLARE--子程序編寫、SQL語(yǔ)句EXCEPTION--子程序處理異常END；EXCEPTION--異常處理END;

PLSQL編程基礎(chǔ)7.5

內(nèi)部程序塊范例：定義內(nèi)部程序塊DECLARE v_x NUMBER:=30; --此為全局變量BEGIN DECLARE v_xVARCHAR2(40):='MLDNJAVA';--此為局部變量，--只能在內(nèi)部程序塊中使用 v_y NUMBER:=20; BEGIN DBMS_OUTPUT.put_line('內(nèi)部程序塊輸出：v_x='||v_x); DBMS_OUTPUT.put_line('內(nèi)部程序塊輸出：v_y='||v_y); END; DBMS_OUTPUT.put_line('外部程序塊輸出：v_x='||v_x);END;PLSQL編程基礎(chǔ)7.6

異常處理程序開發(fā)中會(huì)由于設(shè)計(jì)錯(cuò)誤、編碼錯(cuò)誤、硬件故障或其他原因奇異程序運(yùn)行出錯(cuò)，PL/SQL中的異常處理機(jī)制，使得在出現(xiàn)錯(cuò)誤的時(shí)候程序仍能執(zhí)行。異常類型編譯型異常：程序語(yǔ)法出現(xiàn)錯(cuò)誤導(dǎo)致的異常運(yùn)行型異常：程序語(yǔ)法沒有語(yǔ)法錯(cuò)誤，但因?yàn)槌绦蜻\(yùn)算或者返回結(jié)果而出現(xiàn)錯(cuò)誤PLSQL編程基礎(chǔ)7.6

異常處理異常處理語(yǔ)法：WHEN異常類型|用戶定義異常|異常代碼|OTHERSTHEN異常處理；在一個(gè)EXCEPTION語(yǔ)句塊中可以同時(shí)編寫多個(gè)WHEN，用于判斷不同的異常類型。異常可以是系統(tǒng)定義的，也可以用戶自定義的異常類型。PLSQL編程基礎(chǔ)7.6

異常處理常見預(yù)定義異常NO.異常名稱異常代碼觸發(fā)條件1ACCESS_INTO_NULLORA-06530試圖訪問未初始化的對(duì)象屬性是觸發(fā)2CASE_NOT_FOUNDORA-06592Case語(yǔ)句格式有誤，沒有分支語(yǔ)句觸發(fā)3INVALID_CURSORORA-01001在游標(biāo)操作中指針出現(xiàn)異常（未打開或已關(guān)閉）4SUBSCRIPT_OUTSIDE_LIMFITORA-06532當(dāng)訪問嵌套表或可變數(shù)組時(shí)使用了非法索引值觸發(fā)5TIMEOUT_ON_RESOUrCEORA-00051訪問鎖定資源時(shí)間過(guò)長(zhǎng)時(shí)觸發(fā)6VALUE_ERRORORA-06502試圖將一個(gè)變流量?jī)?nèi)容賦值給另一個(gè)不能容納變量?jī)?nèi)容時(shí)觸發(fā)7ZERO_DIVIDEORA-01476當(dāng)使用出發(fā)計(jì)算被除數(shù)為0時(shí)觸發(fā)PLSQL編程基礎(chǔ)7.6

異常處理范例：處理SQL異?！也坏綌?shù)據(jù)DECLARE v_eno emp.empno%TYPE; v_ename emp.ename%TYPE;BEGIN v_eno:=&empno; --由鍵盤輸入雇員編號(hào) SELECTenameINTOv_enameFROMempWHEREempno=v_eno; DBMS_OUTPUT.put_line('編號(hào)為：'||v_eno||'雇員的名字為：'||v_ename);EXCEPTION WHENno_data_foundTHEN DBMS_OUTPUT.put_line('沒有這個(gè)雇員！');END;PLSQL編程基礎(chǔ)7.6

異常處理用戶自定義異常：范例：使用用戶定義異常DECLARE v_data NUMBER; v_myexp EXCEPTION