第07章OraclePLSQL語言基礎(chǔ)-控制語句-過程-觸發(fā)器課件

數(shù)據(jù)庫操作與管理語言O(shè)racleSQL

OraclePL/SQL語言基礎(chǔ)/過程/觸發(fā)器數(shù)據(jù)庫操作與管理語言O(shè)racleSQL

Oracle本章目標PL/SQL語言基本結(jié)構(gòu)PL/SQL變量和常量的聲明PL/SQL程序的執(zhí)行部分了解PL/SQL常用函數(shù)存儲過程觸發(fā)器自定義函數(shù)游標本章目標PL/SQL語言基本結(jié)構(gòu)PL/SQL簡介PL/SQL(ProceduralLanguage/SQL，過程語言/SQL)它是結(jié)合Oracle過程語言和結(jié)構(gòu)化查詢語言的一種擴展語言PL/SQL支持多種數(shù)據(jù)類型，可以使用條件語句和循環(huán)語句等控制結(jié)構(gòu)PL/SQL可用于創(chuàng)建存儲過程、觸發(fā)器和程序包，也可以用來處理業(yè)務(wù)規(guī)則、數(shù)據(jù)庫事件或給SQL命令的執(zhí)行添加程序邏輯PL/SQL簡介PL/SQL(ProceduralLangPL/SQL的優(yōu)點支持SQL支持面向?qū)ο缶幊?OOP)更好的性能可移植性與SQL集成安全性PL/SQL的優(yōu)點支持SQLPL/SQL的基本結(jié)構(gòu)-1PL/SQL語言是程序化程序設(shè)計語言。塊(Block)是PL/SQL程序中最基本的結(jié)構(gòu)，所有PL/SQL程序都是由塊組成。PL/SQL的塊由變量聲明、程序代碼和異常處理代碼3部分組成：DECLARE標記聲明部分變量的聲明，必須要在begin前面聲明一些變量、常量、用戶定義的數(shù)據(jù)類型及游標namevarchar(30);--聲明時不設(shè)置值namevarchar(30):=‘Jack’;--聲明帶有默認值%type;--直接引用一個表的數(shù)據(jù)類型BEGIN標記主程序體部分開始主程序體，在這里可以加入各種合法語句EXCEPTION標記異常處理部分開始異常處理程序，當程序中出現(xiàn)錯誤時執(zhí)行這一部分END標記主程序體結(jié)束部分PL/SQL的基本結(jié)構(gòu)-1PL/SQL語言是程序化程序設(shè)計語PL/SQL基本結(jié)構(gòu)-2declare

說明部分（變量說明，光標申明，例外說明〕begin語句序列（DML語句〕…exception例外處理語句End;/PL/SQL基本結(jié)構(gòu)-2declarePL/SQL字符集PL/SQL語言有效字符包括以下三類所有大寫和小寫英文字母0~9的阿拉伯數(shù)字操作符，包括(、)、+、-、*、/、<、>、!、=、@、%等PL/SQL標識符標識符的最大長度為30個字符，不區(qū)分大小寫，但建議在標識符中適當使用大小寫，以增加程序的可讀性。PL/SQL字符集PL/SQL語言有效字符包括以下三類PL/SQL的運算符PL/SQL語言的運算符算術(shù)運算符加(+)、減(-)、乘(*)、除(/)、乘方(**)和連接(||)關(guān)系運算符=、<>(或!=)、<、>、>=、<=、BETWEEN...AND...、IN、LIKE、ISNULL邏輯運算符邏輯與(AND)、邏輯或(OR)、邏輯非(NOT)PL/SQL的運算符PL/SQL語言的運算符PL/SQL常量和變量在PL/SQL程序運行時，需要定義一些變量來存放一些數(shù)據(jù)。常量和變量在使用前必須聲明，可以使用DECLARE對變量進行聲明，語法如下：DECLARE <變量名><變量類型>:=默認值; ….在DECLARE塊中可以同時聲明多個常量和變量。聲明普通常量或變量是需要說明以下信息：常量或變量的名稱常量或變量的數(shù)據(jù)類型PL/SQL常量和變量在PL/SQL程序運行時，需要定義一些變量說明說明變量（char,varchar2,date,number,boolean,long)記錄變量分量的引用：emp_rec.ename:='ADAMS';說明變量名、數(shù)據(jù)類型和長度后用分號結(jié)束說明語句。引用型變量，即my_name的類型與emp表中ename列的類型一樣記錄型變量集變量說明說明變量（char,varchar2,dat聲明常量聲明常量的基本格式如下：<常量名>constant<數(shù)據(jù)類型>:=<值>;:=為賦值語句關(guān)鍵字constant表示聲明的是常量。常量一旦定義，在以后的使用中其值不再改變。一些固定的大小為了防止有人改變，最好定義成常量。例如Pass_ScoreconstantINTEGER:=60;聲明常量聲明常量的基本格式如下：聲明變量聲明變量的基本格式如下：<變量名><數(shù)據(jù)類型>[(寬度):=<初始值>];變量聲明是沒有關(guān)鍵字，但要指定數(shù)據(jù)類型，寬度和初始值可以定義也可以不定義。例如AddressVARCHAR2(30):=‘地址未知’;PL/SQL對一個未初始化的變量，將被默認賦值為NULL例如AddressVARCHAR2(30);聲明變量聲明變量的基本格式如下：PLSQL案例-1：--打開輸出setseveroutputon-聲明一個變量并輸出declare namevarchar(10):=‘HelloWorld';begindbms_output.put_line(name);end;PLSQL案例-1：--打開輸出案例-2：例如SETSERVEROUTPUTON;DECLARE

Pass_ScoreconstantINTEGER:=60;AddressVARCHAR2(30):='北京海淀區(qū)';BEGIN DBMS_OUTPUT.PUT_LINE(Pass_Score); DBMS_OUTPUT.PUT_LINE(Address);END;使用SETSERVEROUTPUTON命令設(shè)置環(huán)境變量SERVEROUTPUT為打開狀態(tài)，從而使PL/SQL程序能夠在SQL*Plus中輸出結(jié)果使用函數(shù)DBMS_OUTPUT.PUT_LINE()可以輸出參數(shù)的值案例-2：例如PL/SQL程序的執(zhí)行部分PL/SQL程序的執(zhí)行部分包括賦值語句流程控制語句SQL語句游標語句PL/SQL程序的執(zhí)行部分PL/SQL程序的執(zhí)行部分包括使用賦值語句可以在聲明變量時或處理變量時設(shè)置初始值。也可以在程序的執(zhí)行部分對變量進行賦值。SETServerOutPutON;DECLARE TrainNameVARCHAR2(30);BEGIN TrainName:=‘OracleAdministration’;--賦值一個新值 Dbms_output.put_line(TrainName);END;運行結(jié)果如下圖所示使用賦值語句可以在聲明變量時或處理變量時設(shè)置初始值。也可以在接收用戶的輸入賦值：在declare之前，可以通過acceptsomeVarprompt‘提示信息’;要求用戶輸入在后面的代碼中，可以通過地址引用&someVal接收這值--接收用戶的輸入setserveroutputon--要求用戶輸入一個串,如果不輸入后面也可以直接引用，只是一個提示而已

acceptageprompt'plzenteranumber:'; declare nnnumber; begin

nn:=&age; dbms_output.put_line('你輸入的信息是'||nn); end;接收用戶的輸入賦值：在declare之前，可以通過從查詢中結(jié)果中賦值：用into關(guān)鍵字可以將查詢結(jié)果的值，設(shè)置給變量：setserveroutputondeclareidvarchar(30);nmvarchar(30);beginselectid,nameintoid,nmfrompersonwhereid='P001';dbms_output.put_line('編號'||id||'名稱'||nm);--以下是異常處理-–可選exceptionwhenNO_DATA_FOUNDthendbms_output.put_line('沒有你要查詢的數(shù)據(jù)');whenothersthendbms_output.put_line('其他錯誤');end;從查詢中結(jié)果中賦值：用into關(guān)鍵字可以將查詢結(jié)果的值，設(shè)置流程控制語句流程控制語句是所有過程性程序語言的關(guān)鍵PL/SQL的主要控制語句如下：if...thenelsif…thenendif;判斷if正確則執(zhí)行then，否則執(zhí)行else(elsif為嵌套判斷)注意elsif,里面少一下e.Casevarwhen…thenwhen…thenend有邏輯的從數(shù)值中做出選擇Loopexitendloop循環(huán)控制，用判斷語句執(zhí)行exitLoopexitwhen…endloop同上，當when為真時執(zhí)行exitwhile..loopendloop當while為真時循環(huán)for...in...loopendloop已知循環(huán)次數(shù)的循環(huán)流程控制語句流程控制語句是所有過程性程序語言的關(guān)鍵條件語句IF-1IF語句是根據(jù)條件表達式的值決定執(zhí)行相應(yīng)的程序段。語法結(jié)構(gòu)如下：IF<條件表達式>THEN<執(zhí)行語句>...<執(zhí)行語句n>[ELSIF<條件表達式>THEN<執(zhí)行語句>...<執(zhí)行語句n>...ELSE<執(zhí)行語句>]ENDIF;其中ELSIF子句是可選項。注意是ELSIF而不是ELSEIF條件語句IF-1IF語句是根據(jù)條件表達式的值決定執(zhí)行相應(yīng)的程條件語句IF-2程序中說明一個整型變量Number，使用IF語句判斷Number變量是正數(shù)、負數(shù)或0。SETServerOutPutON;DECLARENumberINTEGER:=-10;Begin

IFNumber<0THENdbms_output.put_line('負數(shù)');

ELSIFNumber>0THENdbms_output.put_line('正數(shù)');

ELSEdbms_output.put_line('0');

ENDIF;End;執(zhí)行效果如右圖所示：條件語句IF-2程序中說明一個整型變量Number，使用IF分支語句CASE-1分支語句是對指定的變量進行判斷，從指定的列表中選擇滿足條件的行，并把該行的值作為CASE語句的結(jié)果返回。CASE語句的語法結(jié)構(gòu)如下：CASE<變量>WHEN<表達式1>THEN值1WHEN<表達式2>THEN值2......WHEN<表達式n>THEN值nELSE值n+1END;分支語句CASE-1分支語句是對指定的變量進行判斷，從指定的分支語句CASE-2聲明一個整型變量varDAY和一個字符型變量Result。使用CASE語句判斷varDAY是星期幾。如果變量varDAY在1~7之間，則能夠顯示相應(yīng)的星期信息，否則返回提示信息“數(shù)據(jù)越界”；SETServerOutPutON;DECLAREvarDAYINTEGER:=3;ResultVARCHAR2(20);BEGINResult:=CasevarDAYWHEN1THEN'星期一'WHEN2THEN'星期二'WHEN3THEN'星期三'WHEN4THEN'星期四'WHEN5THEN'星期五'WHEN6THEN'星期六'WHEN7THEN'星期日'ELSE'數(shù)據(jù)越界'END;dbms_output.put_line(Result);END;分支語句CASE-2聲明一個整型變量varDAY和一個字符型案例-1：使用簡單的casewhenelseend語句：declareiint:=0;begini:=(casewhen1=1then111else222end);dbms_output.put_line(i);end;案例-1：使用簡單的casewhenelseend語句在查詢時使用簡單的Case：在查詢中使用case語句：SQL>select(caseidwhen2then'222'else'33'end)fromt6;SQL>--上面的示例等于SQL>select(casewhenid=2then'2222'else'333'end)fromt6;在查詢時使用簡單的Case：在查詢中使用case語句：SQL循環(huán)語句LOOP..EXIT..END-1此語句的功能是重復(fù)執(zhí)行循環(huán)體中的程序塊，直到執(zhí)行EXIT語句，則退出循環(huán)。LOOP...EXIT...END語句的語法結(jié)構(gòu)如下LOOP<程序塊1>IF<條件表達式>THENEXITENDIF<程序塊2>ENDLOOP;循環(huán)語句LOOP..EXIT..END-1此語句的功能是重復(fù)LOOP..EXIT..ENDLOOP-2計算1~4累加SETServerOutPutON;DECLAREvarNumINTEGER:=1;varSumINTEGER:=0;BEGIN

LOOPvarSum:=varSum+varNum;dbms_output.put_line(varNum);IFvarNum=4THEN

EXIT;ENDIF;dbms_output.put_line('+');varNum:=varNum+1;

ENDLOOP;dbms_output.put_line('=');dbms_output.put_line(varSum);END;LOOP..EXIT..ENDLOOP-2計算1~4累加LOOP...EXITWHEN...ENDLOOP-1此循環(huán)語句的功能是重復(fù)執(zhí)行循環(huán)體中的程序塊，直到滿足EXITWHEN后面的判斷語句，則退出循環(huán)。LOOP...EXITWHEN...END語句的語法結(jié)構(gòu)如下：LOOP<程序塊1>EXITWHEN<條件表達式><程序塊2>ENDLOOP;LOOP...EXITWHEN...ENDLOOP-1此LOOP...EXITWHEN...ENDLOOP-2重新實現(xiàn)1~4累加SETServerOutPutON;DECLAREvarNumINTEGER:=1;varSumINTEGER:=0;BEGIN

LOOPvarSum:=varSum+varNum;dbms_output.put_line(varNum);

EXITWHENvarNum=4;dbms_output.put_line('+');varNum:=varNum+1;ENDLOOP;dbms_output.put_line('=');dbms_output.put_line(varSum);END;LOOP...EXITWHEN...ENDLOOP-2重WHILE..LOOP..ENDLOOP-1此語句的功能是當WHILE后面的語句條件成立時，重復(fù)執(zhí)行循環(huán)體中的程序塊。WHILE...LOOP...ENDLOOP語句語法結(jié)構(gòu)如下：WHILE<條件表達式>LOOP<程序塊>ENDLOOP;WHILE..LOOP..ENDLOOP-1此語句的功能是WHILE..LOOP..ENDLOOP-2再次實現(xiàn)1~4累加SETServerOutPutON;DECLAREvarNumINTEGER:=1;varSumINTEGER:=0;BEGINWHILEvarNum<=4LOOPvarSum:=varSum+varNum;dbms_output.put_line(varNum);IFvarNum<4THENdbms_output.put_line('+');ENDIF;varNum:=varNum+1;

ENDLOOP;dbms_output.put_line('=');dbms_output.put_line(varSum);END;WHILE..LOOP..ENDLOOP-2再次實現(xiàn)1~4FOR..IN..LOOP..ENDLOOP-1此語句定義一個循環(huán)變量，并指定循環(huán)變量的初始值和終止值。每循環(huán)一次循環(huán)變量自動加1.FOR...IN...LOOP...ENDLOOP語句的語法如下FOR<循環(huán)變量>IN<初始值>..<終止值>LOOP<程序塊>ENDLOOP;FOR..IN..LOOP..ENDLOOP-1此語句定義FOR..IN..LOOP..ENDLOOP-2再次實現(xiàn)1~4累加SETServerOutPutON;DECLAREvarNumINTEGER:=1;varSumINTEGER:=0;BEGINFORvarNumIN1..4LOOPvarSum:=varSum+varNum;dbms_output.put_line(varNum);IFvarNum<4THENdbms_output.put_line('+');ENDIF;ENDLOOP;dbms_output.put_line('=');dbms_output.put_line(varSum);END;FOR..IN..LOOP..ENDLOOP-2再次實現(xiàn)1FOR..IN..LOOP–3:--forinloop使用變量setserveroutputondeclareiinteger:=1;jinteger:=10;xinteger:=0;beginforxini..jloop//i和j都是變量dbms_output.put_line(x);endloop;end;FOR..IN..LOOP–3:--forinloo異常處理PL/SQL程序在運行過程中，可能會出現(xiàn)錯誤或異?，F(xiàn)象例如：無法建立到Oracle的連接或用0做除數(shù)。好的程序應(yīng)該對可能發(fā)生的異常情況進行處理，異常處理代碼在EXCEPTION塊中實現(xiàn)可以使用WHEN語句來定義異常。WHEN語句的使用方法如下：EXCEPTIONWHEN<異常情況名>THEN<異常處理代碼>WHEN<異常情況名>THEN<異常處理代碼>...WHENOTHERSTHEN<異常處理代碼>異常處理PL/SQL程序在運行過程中，可能會出現(xiàn)錯誤或異?，F(xiàn)預(yù)定義異常種類異常說明ACCESS_INTO_NULL在未初始化對象時出現(xiàn)CASE_NOT_FOUNF在CASE語句中的選項與用戶輸入的數(shù)據(jù)不匹配時出現(xiàn)COLLECTION_IS_NULL在給尚未初始化的表或數(shù)組賦值時出現(xiàn)CURSOR_ALREADY_OPEN用戶試圖重新打開已經(jīng)打開的游標時出現(xiàn)。在重新打開游標前必須先將其關(guān)閉DUP_VAL_ON_INDEX用戶試圖將重復(fù)的值存儲在使用唯一索引的數(shù)據(jù)庫列中時出現(xiàn)INVALID_CURSOR在執(zhí)行非法游標運算(如fetch一個尚未打開的游標)時出現(xiàn)INVALID_NUMBER將字符串轉(zhuǎn)換為數(shù)字時出現(xiàn)LOGIN_DENIED輸入的用戶名或密碼無效時出現(xiàn)NO_DATA_FOUND在表中不存在請求的行時出現(xiàn)SOTRAGE_ERROR在內(nèi)存損壞或PL/SQL耗盡內(nèi)存時出現(xiàn)TOO_MANY_ROWS在執(zhí)行SELECTINTO語句后返回多行時出現(xiàn)VALUE_ERROR在產(chǎn)生大小限制錯誤時出現(xiàn)。如，變量中的列值超出變量的大小ZERO_DIVIDE以零做除數(shù)時出現(xiàn)預(yù)定義異常種類異常說明ACCESS_INTO_NULL在未初示例1：向一個NUMBER類型的變量賦值字符串時，導(dǎo)致異常的發(fā)生SETServerOutPutON;DECLAREvarNumNUMBER;BEGINvarNum:='abc';EXCEPTION

WHENVALUE_ERRORTHENdbms_output.put_line('VALUE_ERROR');END;示例1：向一個NUMBER類型的變量賦值字符串時，導(dǎo)致異常的示例2：使用SQLCODE,SQLERRM輸出錯誤信息：setserveroutputon;declarejinteger:=0;beginj:='Jack';--如果出錯，直接去異常處執(zhí)行，以下行不會輸出dbms_output.put_line('Valueissetted');exceptionwhenothersthen--在異常中默認使用sqlerrm輸出信息dbms_output.put_line(SQLCODE||'Othererrors'||sqlerrm);end;示例2：使用SQLCODE,SQLERRM輸出錯誤信息：用戶自定義異常：當與一個異常錯誤相關(guān)的錯誤出現(xiàn)時，就會隱含觸發(fā)該異常錯誤。用戶定義的異常錯誤是通過顯式使用RAISE語句來觸發(fā)。當引發(fā)一個異常錯誤時，控制就轉(zhuǎn)向到EXCEPTION塊異常錯誤部分，執(zhí)行錯誤處理代碼。對于這類異常情況的處理，步驟如下：在PL/SQL塊的定義部分定義異常情況：<異常名稱>exception;RAISE<異常名稱>；在PL/SQL塊的異常情況處理部分對異常情況做出相應(yīng)的處理。用戶自定義異常：當與一個異常錯誤相關(guān)的錯誤出現(xiàn)時，就會隱含觸自定義異常示例：setserveroutputon;declare

myExceptionEXCEPTION;--定義異常beginupdatepersonsetid='900'whereid='901';ifSQL%NOTFOUNDthen

RAISEmyException;endif;EXCEPTIONwhenmyExceptionthendbms_output.put_line(SQLCODE||'Error'||SQLERRM);end;自定義異常示例：setserveroutputon;RAISE_APPLICATION_ERROR過程:RAISE_APPLICATION_ERROR過程可以重新定義異常錯誤消息，它為應(yīng)用程序提供了一種與ORACLE交互的方法。語法如下：RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);這里的error_number是從–20,000到–20,999之間的參數(shù)。error_message是相應(yīng)的提示信息(<2048字節(jié)).keep_errors為可選，如果keep_errors=TRUE,則新錯誤將被添加到已經(jīng)引發(fā)的錯誤列表中。如果keep_errors=FALSE(缺省),則新錯誤將替換當前的錯誤列表。RAISE_APPLICATION_ERROR過程:RAIRAISE_APPLICATION_ERRROR示例：RAISE_APPLICATION_ERRROR示例：setserveroutputon;declaremyExceptionEXCEPTION;beginupdatepersonsetid='900'whereid='901';ifSQL%NOTFOUNDthen--調(diào)用系統(tǒng)的異常處理，拋出異常

RAISE_APPLICATION_ERROR(-20001,'cannotfoundanyrow!');endif;EXCEPTIONwhenmyExceptionthendbms_output.put_line('usererrors');whenothersthendbms_output.put_line('Others'||SQLCODE||''||SQLERRM);end;RAISE_APPLICATION_ERRROR示例：RAI用異常在PLSQL塊中管理事務(wù)setserveroutputon;begininsertintopersonvalues('P007','Tom1');insertintopersonvalues('P006','Tom4');commit;--提交exceptionwhenothersthenrollback;--回滾dbms_output.put_line(SQLCODE||SQLERRM);end;用異常在PLSQL塊中管理事務(wù)setserveroutpu小結(jié)PL/SQL語言基本結(jié)構(gòu)變量和常量的聲明流程控制語句小結(jié)PL/SQL語言基本結(jié)構(gòu)存儲過程\函數(shù)\觸發(fā)器\游標存儲過程\函數(shù)\觸發(fā)器\游標存儲過程存儲過程存儲過程指存儲在數(shù)據(jù)庫中供所有用戶程序調(diào)用的子程序叫存儲過程。創(chuàng)建存儲過程用CREATEPROCEDURE命令建立存儲過程和存儲函數(shù)語法： create[orreplace]PROCEDURE過程名(參數(shù)列表) ASPLSQL子程序體存儲過程中可以接收的參數(shù)類型： in類型為輸入類型的參數(shù) out類型為輸出類型的參數(shù)。存儲過程指存儲在數(shù)據(jù)庫中供所有用戶程序調(diào)用的子程序叫存儲過程存儲過程調(diào)用setserveroutputonbeginraisesalary(7369);end;/setserveroutputonexecraisesalary(7369);方法一：方法二：存儲過程調(diào)用setserveroutputonsets不接收參數(shù)的過程createorreplaceprocedureP1isbegindbms_output.put_line('Currentdateis:'||to_char(sysdate,'yyyy-mm-dd'));end;--調(diào)用方法1setserveroutputon;execP1();--調(diào)用方法2setserveroutputon;beginp1();end;不接收參數(shù)的過程createorreplaceproc接收輸入類型的參數(shù)：--聲明接收參數(shù)的只聲明類型，不聲明大小createorreplaceprocedurepro2(p_idinvarchar2,p_nameinvarchar2)asbegininsertintopersonvalues(p_id,p_name);end;--調(diào)用setserveroutputon;execpro2('P100','Marray');接收輸入類型的參數(shù)：--聲明接收參數(shù)的只聲明類型，不聲明大小接收輸出類型的參數(shù)：--select*fromall_objectswhereowner='HR';createorreplaceprocedurepro4(pidinvarchar,pnminvarchar,rcountoutnumber)asrcnumber:=0;begin--先寫入數(shù)據(jù)insertintopersonvalues(pid,pnm);commit;selectcount(1)intorcfromperson;rcount:=rc;end;--測試調(diào)用setserveroutputon;declarercnumber:=0;begin--第三個參數(shù)為返回值的參數(shù)pro4('P210','Jack',rc);dbms_output.put_line(rc);end;接收輸出類型的參數(shù)：--select*fromall_在過程中使用游標createorreplaceprocedurepro5ascursorc1isselect*fromperson;v_pperson%rowType;beginopenc1;loopfetchc1intov_p;exitwhenc1%notfound;dbms_output.put_line(v_p.id||v_);endloop;ifc1%isopenthenclosec1;endif;end;--調(diào)用setserveroutputon;execpro5;在過程中使用游標createorreplaceproc函數(shù)函數(shù)存儲函數(shù)函數(shù)（Function）為一命名的存儲程序，可帶參數(shù)，并返回一計算值。函數(shù)和過程的結(jié)構(gòu)類似，但必須有一個RETURN子句，用于返回函數(shù)值。函數(shù)說明要指定函數(shù)名、結(jié)果值的類型，以及參數(shù)類型等。建立存儲函數(shù)的語法：CREATE[ORREPLACE]FUNCTION函數(shù)名(參數(shù)列表)RETURN函數(shù)值類型ASPLSQL子程序體；存儲函數(shù)函數(shù)（Function）為一命名的存儲程序，可帶參數(shù)函數(shù)示例：createorreplacefunctionf1returnvarchar2asbeginreturn'hello';end;--調(diào)用selectf1()fromdual;函數(shù)示例：createorreplacefunctio函數(shù)的調(diào)用declare v_salnumber;begin v_sal:=queryEmpSalary(7934); dbms_output.put_line('salaryis:'||v_sal);end;或是直接使用select調(diào)用

begin dbms_output.put_line('salaryis:'||queryEmpSalary(7934));end;函數(shù)的調(diào)用declarebegin過程和函數(shù)中的in和out一般來講，過程和函數(shù)的區(qū)別在于函數(shù)可以有一個返回值；而過程沒有返回值。In類型的參數(shù)，只可以接收值，不能再給in類型的參數(shù)設(shè)置新的值。但過程和函數(shù)都可以通過out指定一個或多個輸出參數(shù)。我們可以利用out參數(shù)，在過程和函數(shù)中實現(xiàn)返回多個。過程和函數(shù)中的in和out一般來講，過程和函數(shù)的區(qū)別在于函數(shù)什么時候用存儲過程/存儲函數(shù)？原則：如果只有一個返回值，用存儲函數(shù)；否則，就用存儲過程。什么時候用存儲過程/存儲函數(shù)？原則：函數(shù)示例2：用函數(shù)去掉字段中多個重復(fù)的空格：createorreplacefunctionmtrim(strvarchar2)returnvarchar2asv_strvarchar2(500):='';beginselectregexp_replace(str,'(\s)\1','')intov_strfromdual;returnv_str;end;--測試selectmtrim('HA')fromdual;函數(shù)示例2：用函數(shù)去掉字段中多個重復(fù)的空格：觸發(fā)器/Trigger觸發(fā)器/Trigger觸發(fā)器數(shù)據(jù)庫觸發(fā)器是一個與表相關(guān)聯(lián)的、存儲的PL/SQL程序。每當一個特定的數(shù)據(jù)操作語句(Insert,update,delete)在指定的表上發(fā)出時，Oracle自動地執(zhí)行觸發(fā)器中定義的語句序列。觸發(fā)器的類型語句級觸發(fā)器在指定的操作語句操作之前或之后執(zhí)行一次，不管這條語句影響了多少行。行級觸發(fā)器（FOREACHROW）觸發(fā)語句作用的每一條記錄都被觸發(fā)。在行級觸發(fā)器中使用old和new偽記錄變量,識別值的狀態(tài)。觸發(fā)器數(shù)據(jù)庫觸發(fā)器是一個與表相關(guān)聯(lián)的、存儲的PL/SQL程序創(chuàng)建觸發(fā)器CREATE[orREPLACE]TRIGGER觸發(fā)器名{BEFORE|AFTER}{DELETE|INSERT|UPDATE[OF列名]}ON表名[FOREACHROW[WHEN(條件)]]PLSQL塊創(chuàng)建觸發(fā)器CREATE[orREPLACE]T觸發(fā)語句與偽記錄變量的值觸發(fā)語句:old:newInsert將要插入的數(shù)據(jù)Update更新以前該行的值更新后的值delete刪除以前該行的值觸發(fā)語句與偽記錄變量的值觸發(fā)語句:old:newInsert示例：限制寫入以J開始的串：--書寫一個最基本的beforetriggercreateorreplacetriggert1beforeinsertonpersonforeachrowbegindbms_output.put_line('Hello'||:NEW.id||:NEW.name);

if:NEW.namelike'J%'thenRAISE_APPLICATION_ERROR(-20000,'限制寫入以J開始的串');--在觸發(fā)器中，不能寫commit或是rollbackendif;end;setserveroutputon;insertintopersonvalues('X790','Mack');示例：限制寫入以J開始的串：--書寫一個最基本的beforeForeachrow的含義：添加foreachrow后，觸發(fā)器，將變?yōu)樾屑売|發(fā)器：如：createorreplacetriggertrigger1beforeupdateonpersonforeachrowdeclarebegindbms_output.put_line('Hello');end;如果person表中有四行，則會顯示4個hello.如果沒有foreachrow則只會顯示一個hello.只有在行級的觸發(fā)器中，才可以使用:new,:old。Foreachrow的含義：添加foreachrow示例2：限制修改的年齡大于以前的年齡createorreplacetriggert2beforeupdateont6foreachrowbeginif:NEW.age<:OLD.agethenRAISE_APPLICATION_ERROR(-20000,'年齡不能越來越小');endif;end;--寫入小值就是出錯updatet6setage=143;select*fromt6;示例2：限制修改的年齡大于以前的年齡示例：同時使用insert,update,delete類型的觸發(fā)器：--在同一個表上，使用insert,update,delete觸發(fā)器createorreplacetriggertrigger1beforeinsertorupdateordeleteonpersondeclarebegindbms_output.put_line('hello');end;--以下都會引發(fā)上面的觸發(fā)器setserveroutputon;insertintopersonvalues(1,'Jack');setserveroutputon;updatepersonsetname='Tom';setserveroutputon;deletefromperson;示例：同時使用insert,update,delete類型的示例：判斷是何種類型的操作：--同時聲明insert,update,delete判斷是何種操作createorreplacetriggertrigger1beforeinsertorupdateordeleteonpersonforeachrowdeclarebeginifINSERTINGthendbms_output.put_line('這是插入數(shù)據(jù)');elsifDELETINGthendbms_output.put_line('這是刪除數(shù)據(jù)');elsedbms_output.put_line('這是修改數(shù)據(jù)');endif;end;示例：判斷是何種類型的操作：示例：觸發(fā)器_根據(jù)條件決定是否執(zhí)行plsql塊createorreplacetriggertrigger1beforeinsertonpersonforeachrowwhen(NEW.id=8)--這兒是這樣引用newdeclarebegin--這兒前面必須要添加:new才可以引用dbms_output.put_line('這真是的是等于'||:new.id||:);end;示例：觸發(fā)器_根據(jù)條件決定是否執(zhí)行plsql塊示例：作用在某個列上的createorreplacetriggertrigger1--聲明將這個觸發(fā)器作用到某個列上beforeupdateofnameonpersonforeachrowDeclareBegindbms_output.put_line('之前的值'||:||'現(xiàn)在的值:'||:);end;示例：作用在某個列上的示例：在觸發(fā)器調(diào)用過程：createorreplacetriggertg1beforeinsertonpersonforeachrowdeclarev_agenumber:=0;begindbms_output.put_line('Trigger..');

P1(v_age);--直接調(diào)用過程即可dbms_output.put_line('ageis:'||v_age);end;示例：在觸發(fā)器調(diào)用過程：觸發(fā)器小總結(jié)觸發(fā)器可用于數(shù)據(jù)確認

實施復(fù)雜的安全性檢查做審計，跟蹤表上所做的數(shù)據(jù)操作等數(shù)據(jù)的備份和同步查詢觸發(fā)器、過程及函數(shù)Select*fromuser_triggers;Select*fromuser_source;觸發(fā)器小總結(jié)觸發(fā)器可用于游標游標游標：游標是SQL的一個內(nèi)存工作區(qū)，由系統(tǒng)或用戶以變量的形式定義。游標的作用就是用于臨時存儲從數(shù)據(jù)庫中提取的數(shù)據(jù)塊。游標分為三種類型：隱式Cursor顯式CursorRefCursor（動態(tài)Cursor）。游標：游標是SQL的一個內(nèi)存工作區(qū)，由系統(tǒng)或用戶以變量的形式隱式游標：對于Select…INTO…語句，一次只能從數(shù)據(jù)庫中獲取到一條數(shù)據(jù)，對于這種類型的DMLSql語句，就是隱式Cursor。例如：Select/Update/Insert/Delete操作。作用：可以通過隱式Cusor的屬性來了解操作的狀態(tài)和結(jié)果，從而達到流程的控制。隱式Cursor是系統(tǒng)自動打開和關(guān)閉Cursor。Cursor的屬性包含：SQL%ROWCOUNT整型代表DML語句成功執(zhí)行的數(shù)據(jù)行數(shù)。SQL%FOUND布爾型值為TRUE代表插入、刪除、更新或單行查詢操作成功。SQL%NOTFOUND布爾型與SQL%FOUND屬性返回值相反。SQL%ISOPEN布爾型DML執(zhí)行過程中為真，結(jié)束后為假。隱式游標：對于Select…INTO…語句，一次只能從數(shù)據(jù)隱式游標示例1：用隱式的游標判斷是否修改數(shù)據(jù)成功：setserveroutputon;beginupdatepersonsetid='P003'whereid='P010';ifSQL%FOUNDthendbms_output.put_line('updatesuccessfull');commit;elsedbms_output.put_line('updatefaulied');endif;end;隱式游標示例1：用隱式的游標判斷是否修改數(shù)據(jù)成功：隱式游標示例2：判斷影響的行數(shù)：setserveroutputon;declarerowCountinteger;beginupdatepersonsetname='Jack';rowCount:=SQL%ROWCOUNT;--輸出本次共影響幾行dbms_output.put_line('Effectedrowsis:'||rowCount);end;隱式游標示例2：判斷影響的行數(shù)：顯式Cursor：對于從數(shù)據(jù)庫中提取多行數(shù)據(jù)，就需要使用顯式Cursor。顯式Cursor的屬性包含：%ROWCOUNT整型獲得FETCH語句返回的數(shù)據(jù)行數(shù)。%FOUND布爾型最近的FETCH語句返回一行數(shù)據(jù)則為真，否則為假。%NOTFOUND布爾型與%FOUND屬性返回值相反。%ISOPEN布爾型游標已經(jīng)打開時值為真，否則為假。對于顯式游標的運用分為四個步驟：定義游標---Cursor[CursorName]IS;必須要使用is.打開游標---Open[CursorName];操作數(shù)據(jù)---Fetch[Cursorname]intosomeVar;判斷游標是否獲取到行值exitwhenc1%notfound;關(guān)閉游標---Close[CursorName],這個Step絕對不可以遺漏。顯式Cursor：對于從數(shù)據(jù)庫中提取多行數(shù)據(jù)，就需要使用顯顯式Cursor用法-1：遍歷數(shù)據(jù)：setserveroutputon;declarersperson%rowType;cursorc1isselect*fromperson;beginopenc1;loop

fetchc1intors;--每一次將值寫到rs記錄集中exitwhenc1%notfound;--判斷是否獲取到了數(shù)據(jù)dbms_output.put_line('行號：'||c1%rowcount||'編號'||rs.id||'姓名'||);endloop;ifc1%isopenthen–關(guān)閉游標closec1;endif;end;顯式Cursor用法-1：遍歷數(shù)據(jù)：顯式Cursor用法-2setserveroutputon;declarecursorc1isselectid,namefromperson;--聲明兩個變量v_idperson.id%type;v_%type;beginopenc1;loop

--分別對應(yīng)寫入兩個值,注意與select語句的對應(yīng)關(guān)系fetchc1intov_id,v_name;exitwhenc1%notfound;DBMS_OUTPUT.put_line('Nameis:'||v_name);endloop;ifc1%isopenthenclosec1;endif;end;顯式Cursor用法-2setserveroutputo顯式Cursor用法-3:setserveroutputon;declarecursorc1isselect*fromperson;begin

--使用for..in直接打開的游標,直接使用rs變量即可forrsinc1loopdbms_output.put_line(rs.id||''||);endloop;ifc1%isopenthenclosec1;endif;end;顯式Cursor用法-3:setserveroutput綜合示例：12執(zhí)行select語句并把查詢的結(jié)果寫c13取一條記錄處理4判斷是否取到數(shù)據(jù)按員工的工種長工資,總裁1000元，經(jīng)理長800元其，他人員長400元綜合示例：12執(zhí)行select語句并把查詢的結(jié)果寫c13取一RefCursor（動態(tài)游標）：與隱式Cursor,顯式Cursor的區(qū)別：RefCursor是可以通過在運行期間傳遞參數(shù)來獲取數(shù)據(jù)結(jié)果集。而另外兩種Cursor，是靜態(tài)的，在編譯期間就決定數(shù)據(jù)結(jié)果集。Refcursor的使用:Type[Cursortypename]isrefcursor;Define動態(tài)的Sql語句Opencursor操作數(shù)據(jù)---Fetch[Cursorname]CloseCursorRefCursor（動態(tài)游標）：與隱式Cursor,顯式動態(tài)游標示例：declaretypecursor_typeisrefcursor;myCursorcursor_type;v_sqlvarchar2(500);v_personperson%rowType;beginv_sql:='select*frompersonwherenamelike''%a%''';

openmyCursorforv_sql;loopfetchmyCursorintov_person;exitwhenmycursor%notfound;dbms_output.put_line(v_person.id||v_);endloop;exception--異常處理塊，確保關(guān)閉游標whenothersthenclosemyCursor;ifmyCursor%isopenthenclosemyCursor;endif;end;動態(tài)游標示例：declare帶參數(shù)的游標：語法：聲明參數(shù)，不可以聲明大小cursorc2(jobcvarchar2)isselectename,salfromempwherejob=jobc;執(zhí)行語句:Openc2(‘clerk’);帶參數(shù)的游標：語法：聲明參數(shù)，不可以聲明大小執(zhí)行語句:帶參數(shù)的游標示例：setserveroutputon;declarecursorc1(p_idvarchar2)isselect*frompersonwhereid=p_id;begin

--注意下面的參數(shù)，或是使用openc1('P001');forrsinc1('P002')loopdbms_output.put_line(rs.id||);endloop;end;帶參數(shù)的游標示例：setserveroutputon;游標綜合示例：用游標刪除用戶指定的時間創(chuàng)建的表：游標綜合示例：用游標刪除用戶指定的時間創(chuàng)建的表：Pl/sql塊斷點調(diào)試：斷點調(diào)試需要用戶具備以下兩個權(quán)限：debugconnectsessiondebuganyprocedure(或其他對象)Pl/sql塊斷點調(diào)試：斷點調(diào)試需要用戶具備以下兩個權(quán)限：總結(jié)：過程觸發(fā)器函數(shù)游標總結(jié)：過程第07章OraclePLSQL語言基礎(chǔ)_控制語句_過程_觸發(fā)器課件數(shù)據(jù)庫操作與管理語言O(shè)racleSQL

OraclePL/SQL語言基礎(chǔ)/過程/觸發(fā)器數(shù)據(jù)庫操作與管理語言O(shè)racleSQL

Oracle本章目標PL/SQL語言基本結(jié)構(gòu)PL/SQL變量和常量的聲明PL/SQL程序的執(zhí)行部分了解PL/SQL常用函數(shù)存儲過程觸發(fā)器自定義函數(shù)游標本章目標PL/SQL語言基本結(jié)構(gòu)PL/SQL簡介PL/SQL(ProceduralLanguage/SQL，過程語言/SQL)它是結(jié)合Oracle過程語言和結(jié)構(gòu)化查詢語言的一種擴展語言PL/SQL支持多種數(shù)據(jù)類型，可以使用條件語句和循環(huán)語句等控制結(jié)構(gòu)PL/SQL可用于創(chuàng)建存儲過程、觸發(fā)器和程序包，也可以用來處理業(yè)務(wù)規(guī)則、數(shù)據(jù)庫事件或給SQL命令的執(zhí)行添加程序邏輯PL/SQL簡介PL/SQL(ProceduralLangPL/SQL的優(yōu)點支持SQL支持面向?qū)ο缶幊?OOP)更好的性能可移植性與SQL集成安全性PL/SQL的優(yōu)點支持SQLPL/SQL的基本結(jié)構(gòu)-1PL/SQL語言是程序化程序設(shè)計語言。塊(Block)是PL/SQL程序中最基本的結(jié)構(gòu)，所有PL/SQL程序都是由塊組成。PL/SQL的塊由變量聲明、程序代碼和異常處理代碼3部分組成：DECLARE標記聲明部分變量的聲明，必須要在begin前面聲明一些變量、常量、用戶定義的數(shù)據(jù)類型及游標namevarchar(30);--聲明時不設(shè)置值namevarchar(30):=‘Jack’;--聲明帶有默認值%type;--直接引用一個表的數(shù)據(jù)類型BEGIN標記主程序體部分開始主程序體，在這里可以加入各種合法語句EXCEPTION標記異常處理部分開始異常處理程序，當程序中出現(xiàn)錯誤時執(zhí)行這一部分END標記主程序體結(jié)束部分PL/SQL的基本結(jié)構(gòu)-1PL/SQL語言是程序化程序設(shè)計語PL/SQL基本結(jié)構(gòu)-2declare

說明部分（變量說明，光標申明，例外說明〕begin語句序列（DML語句〕…exception例外處理語句End;/PL/SQL基本結(jié)構(gòu)-2declarePL/SQL字符集PL/SQL語言有效字符包括以下三類所有大寫和小寫英文字母0~9的阿拉伯數(shù)字操作符，包括(、)、+、-、*、/、<、>、!、=、@、%等PL/SQL標識符標識符的最大長度為30個字符，不區(qū)分大小寫，但建議在標識符中適當使用大小寫，以增加程序的可讀性。PL/SQL字符集PL/SQL語言有效字符包括以下三類PL/SQL的運算符PL/SQL語言的運算符算術(shù)運算符加(+)、減(-)、乘(*)、除(/)、乘方(**)和連接(||)關(guān)系運算符=、<>(或!=)、<、>、>=、<=、BETWEEN...AND...、IN、LIKE、ISNULL邏輯運算符邏輯與(AND)、邏輯或(OR)、邏輯非(NOT)PL/SQL的運算符PL/SQL語言的運算符PL/SQL常量和變量在PL/SQL程序運行時，需要定義一些變量來存放一些數(shù)據(jù)。常量和變量在使用前必須聲明，可以使用DECLARE對變量進行聲明，語法如下：DECLARE <變量名><變量類型>:=默認值; ….在DECLARE塊中可以同時聲明多個常量和變量。聲明普通常量或變量是需要說明以下信息：常量或變量的名稱常量或變量的數(shù)據(jù)類型PL/SQL常量和變量在PL/SQL程序運行時，需要定義一些變量說明說明變量（char,varchar2,date,number,boolean,long)記錄變量分量的引用：emp_rec.ename:='ADAMS';說明變量名、數(shù)據(jù)類型和長度后用分號結(jié)束說明語句。引用型變量，即my_name的類型與emp表中ename列的類型一樣記錄型變量集變量說明說明變量（char,varchar2,dat聲明常量聲明常量的基本格式如下：<常量名>constant<數(shù)據(jù)類型>:=<值>;:=為賦值語句關(guān)鍵字constant表示聲明的是常量。常量一旦定義，在以后的使用中其值不再改變。一些固定的大小為了防止有人改變，最好定義成常量。例如Pass_ScoreconstantINTEGER:=60;聲明常量聲明常量的基本格式如下：聲明變量聲明變量的基本格式如下：<變量名><數(shù)據(jù)類型>[(寬度):=<初始值>];變量聲明是沒有關(guān)鍵字，但要指定數(shù)據(jù)類型，寬度和初始值可以定義也可以不定義。例如AddressVARCHAR2(30):=‘地址未知’;PL/SQL對一個未初始化的變量，將被默認賦值為NULL例如AddressVARCHAR2(30);聲明變量聲明變量的基本格式如下：PLSQL案例-1：--打開輸出setseveroutputon-聲明一個變量并輸出declare namevarchar(10):=‘HelloWorld';begindbms_output.put_line(name);end;PLSQL案例-1：--打開輸出案例-2：例如SETSERVEROUTPUTON;DECLARE

Pass_ScoreconstantINTEGER:=60;AddressVARCHAR2(30):='北京海淀區(qū)';BEGIN DBMS_OUTPUT.PUT_LINE(Pass_Score); DBMS_OUTPUT.PUT_LINE(Address);END;使用SETSERVEROUTPUTON命令設(shè)置環(huán)境變量SERVEROUTPUT為打開狀態(tài)，從而使PL/SQL程序能夠在SQL*Plus中輸出結(jié)果使用函數(shù)DBMS_OUTPUT.PUT_LINE()可以輸出參數(shù)的值案例-2：例如PL/SQL程序的執(zhí)行部分PL/SQL程序的執(zhí)行部分包括賦值語句流程控制語句SQL語句游標語句PL/SQL程序的執(zhí)行部分PL/SQL程序的執(zhí)行部分包括使用賦值語句可以在聲明變量時或處理變量時設(shè)置初始值。也可以在程序的執(zhí)行部分對變量進行賦值。SETServerOutPutON;DECLARE TrainNameVARCHAR2(30);BEGIN TrainName:=‘OracleAdministration’;--賦值一個新值 Dbms_output.put_line(TrainName);END;運行結(jié)果如下圖所示使用賦值語句可以在聲明變量時或處理變量時設(shè)置初始值。也可以在接收用戶的輸入賦值：在declare之前，可以通過acceptsomeVarprompt‘提示信息’;要求用戶輸入在后面的代碼中，可以通過地址引用&someVal接收這值--接收用戶的輸入setserveroutputon--要求用戶輸入一個串,如果不輸入后面也可以直接引用，只是一個提示而已

acceptageprompt'plzenteranumber:'; declare nnnumber; begin

nn:=&age; dbms_output.put_line('你輸入的信息是'||nn); end;接收用戶的輸入賦值：在declare之前，可以通過從查詢中結(jié)果中賦值：用into關(guān)鍵字可以將查詢結(jié)果的值，設(shè)置給變量：setserveroutputondeclareidvarchar(30);nmvarchar(30);beginselectid,nameintoid,nmfrompersonwhereid='P001';dbms_output.put_line('編號'||id||'名稱'||nm);--以下是異常處理-–可選exceptionwhenNO_DATA_FOUNDthendbms_output.put_line('沒有你要查詢的數(shù)據(jù)');whenothersthendbms_output.put_line('其他錯誤');end;從查詢中結(jié)果中賦值：用into關(guān)鍵字可以將查詢結(jié)果的值，設(shè)置流程控制語句流程控制語句是所有過程性程序語言的關(guān)鍵PL/SQL的主要控制語句如下：if...thenelsif…thenendif;判斷if正確則執(zhí)行then，否則執(zhí)行else(elsif為嵌套判斷)注意elsif,里面少一下e.Casevarwhen…thenwhen…thenend有邏輯的從數(shù)值中做出選擇Loopexitendloop循環(huán)控制，用判斷語句執(zhí)行exitLoopexitwhen…endloop同上，當when為真時執(zhí)行exitwhile..loopendloop當while為真時循環(huán)for...in...loopendloop已知循環(huán)次數(shù)的循環(huán)流程控制語句流程控制語句是所有過程性程序語言的關(guān)鍵條件語句IF-1IF語句是根據(jù)條件表達式的值決定執(zhí)行相應(yīng)的程序段。語法結(jié)構(gòu)如下：IF<條件表達式>THEN<執(zhí)行語句>...<執(zhí)行語句n>[ELSIF<條件表達式>THEN<執(zhí)行語句>...<執(zhí)行語句n>...ELSE<執(zhí)行語句>]ENDIF;其中ELSIF子句是可選項。注意是ELSIF而不是ELSEIF條件語句IF-1IF語句是根據(jù)條件表達式的值決定執(zhí)行相應(yīng)的程條件語句IF-2程序中說明一個整型變量Number，使用IF語句判斷Number變量是正數(shù)、負數(shù)或0。SETServerOutPutON;DECLARENumberINTEGER:=-10;Begin

IFNumber<0THENdbms_output.put_line('負數(shù)');

ELSIFNumber>0THENdbms_output.put_line('正數(shù)');

ELSEdbms_output.put_line('0');

ENDIF;End;執(zhí)行效果如右圖所示：條件語句IF-2程序中說明一個整型變量Number，使用IF分支語句CASE-1分支語句是對指定的變量進行判斷，從指定的列表中選擇滿足條件的行，并把該行的值作為CASE語句的結(jié)果返回。CASE語句的語法結(jié)構(gòu)如下：CASE<變量>WHEN<表達式1>THEN值1WHEN<表達式2>THEN值2......WHEN<表達式n>THEN值nELSE值n+1END;分支語句CASE-1分支語句是對指定的變量進行判斷，從指定的分支語句CASE-2聲明一個整型變量varDAY和一個字符型變量Result。使用CASE語句判斷varDAY是星期幾。如果變量varDAY在1~7之間，則能夠顯示相應(yīng)的星期信息，否則返回提示信息“數(shù)據(jù)越界”；SETServerOutPutON;DECLAREvarDAYINTEGER:=3;ResultVARCHAR2(20);BEGINResult:=CasevarDAYWHEN1THEN'星期一'WHEN2THEN'星期二'WHEN3THEN'星期三'WHEN4THEN'星期四'WHEN5THEN'星期五'WHEN6THEN'星期六'WHEN7THEN'星期日'ELSE'數(shù)據(jù)越界'END;dbms_output.put_line(Result);END;分支語句CASE-2聲明一個整型變量varDAY和一個字符型案例-1：使用簡單的casewhenelseend語句：declareiint:=0;begini:=(casewhen