PL/SQL程序設計主要內容：PL/SQL語言的基本特征、PL/SQL程序的結構、PL/SQL語言的控制結構、游標(cursor)編程、錯誤處理、存儲過程和函數、創建包(Package)、觸發器(Trigger)。PL/SQL語言的基本特征什么是PL/SQL?PL/SQL是ProcedureLanguage&StructuredQueryLanguage的縮寫。SQLSERVER：Transaction-SQL。PL/SQL可分為兩種：數據庫PL/SQL和工具PL/SQL。兩者的編程非常相似。都具有編程結構、語法和邏輯機制。主要介紹數據庫PL/SQL內容。工具PL/SQL用于Oracle的客戶端開發工具。工具PL/SQL另外還增加了用于支持工具（如ORACLEForms）的句法，如：在窗體上設置按鈕等。Oracle的客戶端開發工具OracleiDS(DeveloperStudio)Release2(10.2)包括的軟件: Oracle10gJDeveloper-JAVA開發工具Oracle10gFormsDeveloper-窗體開發 Oracle10gDesigner-數據庫工具 Oracle10gSoftwareConfigurationManager-軟件配置 Oracle10gReportsDeveloper-報表開發 Oracle10gDiscovererAdministrator Oracle10gWarehouseBuilder-數據倉庫Oracle10gBusinessIntelligenceBeans-商業智能2.PL/SQL語言的特征支持SQL語言、支持面向對象編程、可移植、與Oracle集成。PL/SQL程序不區分大小寫。字符串數據區分大小寫。3.PL/SQL可用的SQL語句在PL/SQL中可以使用的SQL語句有：INSERT，UPDATE，DELETE，SELECTINTO，COMMIT，ROLLBACK，SAVEPOINT。重點：在PL/SQL中不能有SELECT語句，只能用SELECTINTO語句。提示：在PL/SQL中只能用SQL語句中的DML部分，不能用DDL部分，如果要在PL/SQL中使用DDL(如CREATETABLE等)的話，只能以動態的方式來使用。PL/SQL程序的結構PL/SQL程序的塊結構PL/SQL程序由三個部分組成，即聲明部分、執行部分、異常處理部分。PL/SQL塊的結構如下：[DECLARE/*聲明部分:在此聲明PL/SQL用到的變量,類型及游標，以及局部的存儲過程和函數*/]BEGIN /*執行部分:過程及SQL語句,即程序的主要部分*/[EXCEPTION /*執行異常部分:錯誤處理*/]END;其中執行部分是必須的。一個PL/SQL程序實例：DECLARE QtyonhandNUMBER(5);BEGIN SELECTquantityINTOqtyonhandFROMinventoryWHEREproduct=’TENNISRACKET’;IFqtyonhand>OTHENUPDATEinventorySETquantity=quantity-1WHEREproduct=’TENNISRACKET’;INSERTINTOpurchaserecordVALUES(’Tennisracketpurchased’,SYSDATE);ELSEINSERTINTOpurchaserecordVALUES(’Outoftennisrackets’,SYSDATE);ENDIF;COMMIT;END;演示一個例子。PL/SQL塊可以分為兩類：未命名塊：動態構造，只能執行一次。命名塊：存儲在數據庫中的存儲過程、函數、觸發器以及包等。標識符PL/SQL程序設計中的標識符定義與SQL的標識符定義的要求相同。要求和限制有：標識符名不能超過30字符；第一個字符必須為字母；可包含字母，數字，$，_，和#；不分大小寫；不能包含空格、&、-、/；不能是SQL保留字。提示:一般不要把變量名聲明與表中字段名完全一樣,如果這樣可能得到不正確的結果.例如：下面的例子將會刪除所有的紀錄，而不是KING的記錄；DECLAREenamevarchar2(20):=’KING’;BEGIN DELETEFROMempWHEREename=ename;END;變量命名在PL/SQL中有特別的講究，建議在系統的設計階段就要求所有編程人員共同遵守一定的要求，使得整個系統的文檔在規范上達到要求。下面是建議的命名方法：標識符命名規則例子程序變量V_nameV_name程序常量C_NameC_company_name游標變量Name_cursorEmp_cursor異常標識E_nameE_too_many表類型Name_table_typeEmp_table_type表Name_tableEmp_table記錄類型Name_recordEmp_recordSQL*Plus替代變量P_nameP_sal綁定變量G_nameG_year_salPL/SQL變量類型PL/SQL數據類型包括：標量類型：number,character,Boolean和date/time。復合類型：table、varray、record、object。引用類型：Ref(對象)LOB類型:CLOB、BLOB、NCLOB、BFILE。PL/SQL數據類型比表的字段的數據類型更細。標量數據類型在ORACLE9i中可以使用的標量數據類型有：類型子類說明范圍ORACLE限制CHARCharacterStringRowidNchar定長字符串民族語言字符集032767可選,確省=12000VARCHAR2Varchar,StringNVARCHAR2可變字符串民族語言字符集03276740004000BINARY_INTEGERNATURALNATURALNPOSITIVEPOSITIVENSIGNTYPE帶符號整數,為整數計算優化性能NUMBER(p,s)DecDecimalDoubleprecisionNumericfloatRealIntegerIntSmallint小數,NUMBER的子類型高精度實數與NUMBER等價與NUMBER等價整數,NUMBER的子類型整數,NUMBER的子類型整數,比integer小LONG變長字符串0->214748364732,767字節DATE日期型公元前4712年1月1日至公元后4712年12月31日BOOLEAN布爾型TRUE,FALSE,NULL不使用ROWID存放數據庫行號UROWID通用行標識符，字符類型插入一條記錄并顯示；setserveroutputonDECLARERow_idROWID;infoVARCHAR2(40);BEGIN INSERTINTOdeptVALUES(90,‘SERVICE’,‘BEIJING’) RETURNINGrowid,dname||’:’||to_char(deptno)||’:’||loc INTOrow_id,info; DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id); DBMS_OUTPUT.PUT_LINE(info);END;例2.修改一條記錄并顯示DECLARERow_idROWID;infoVARCHAR2(40);BEGIN UPDATEdeptSETdeptno=80WHEREDNAME=‘SERVICE’ RETURNINGrowid,dname||’:’||to_char(deptno)||’:’||loc INTOrow_id,info; DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id); DBMS_OUTPUT.PUT_LINE(info);END;例3.刪除一條記錄并顯示DECLARERow_idROWID;infoVARCHAR2(40);BEGIN DELETEdeptWHEREDNAME=‘SERVICE’ RETURNINGrowid,dname||’:’||to_char(deptno)||’:’||loc INTOrow_id,info; DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id); DBMS_OUTPUT.PUT_LINE(info);END;記錄類型例1：DECLARETYPEtest_recISRECORD(CodeVARCHAR2(10),Book_NameVARCHAR2(30)NOTNULL:=’abook’);V_booktest_rec;BEGINV_book.code:=’123’;V_book.Book_name:=’C++Programming’;DBMS_OUTPUT.PUT_LINE(v_book.code||v_book.book_name);END;例2：DECLARETYPEDeptRecISRECORD(Dept_numNUMBER(2),dept_nameCHAR(14),LocationCHAR(13));dept_infoDeptRec;BEGINSELECTdeptno,dname,locINTOdept_infoFROMdeptWHEREdeptno=20;END;記錄不能作為數據庫的字段。使用%TYPE例1：SETSERVEROUTPUTONDECLARE--用%TYPE類型定義與表相配的字段TYPEt_RecordISRECORD(T_noemp.empno%TYPE,T_nameemp.ename%TYPE,T_salemp.sal%TYPE);--聲明接收數據的變量Rect_record;BEGINSELECTempno,ename,salINTORecFROMempWHEREempno=7788;DBMS_OUTPUT.PUT_LINE(Rec.t_no||'---'||Rec.t_name||'--'||Rec.t_sal);END;例2：AcceptnoPrompt“pleaseinputemployeenumber:”DECLAREv_empnoemp.empno%TYPE:=&no;Typet_recordisrecord(t_nameemp.ename%TYPE,t_salemp.sal%TYPE,t_dateemp.hiredate%TYPE);Rect_record;BEGINSELECTename,sal,hiredateINTORecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE(Rec.t_name||'---'||Rec.t_sal||'--'||Rec.t_date);END;使用%ROWTYPE例1：DECLAREv_empnoemp.empno%TYPE:=&no;recemp%ROWTYPE;BEGINSELECT*INTOrecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工資:'||rec.sal||'工作時間:'||rec.hiredate);END;LOB類型*ORACLE提供了LOB(LargeOBject)類型，用于存儲大的數據對象的類型。ORACLE目前主要支持BFILE,BLOB,CLOB及NCLOB類型。BFILE(Movie)存放大的二進制數據對象，這些數據文件不放在數據庫里，而是放在操作系統的某個目錄里，數據庫的表里只存放文件的目錄。BLOB(Photo)存儲大的二進制數據類型。變量存儲大的二進制對象的位置。大二進制對象的大小<=4GB。CLOB(Book)存儲大的字符數據類型。每個變量存儲大字符對象的位置，該位置指到大字符數據塊。大字符對象的大小<=4GB。NCLOB存儲大的NCHAR字符數據類型。每個變量存儲大字符對象的位置，該位置指到大字符數據塊。大字符對象的大小<=4GB。BIND變量綁定變量是在主機環境中定義的變量。在PL/SQL程序中可以使用綁定變量作為他們將要使用的其它變量。為了在PL/SQL環境中聲明綁定變量，使用命令VARIABLE。例如：VARIABLEreturn_codeNUMBERVARIABLEreturn_msgVARCHAR2(20)可以通過SQL*Plus命令中的PRINT顯示綁定變量的值。例如：PRINTreturn_codePRINTreturn_msg例1：VARIABLEresultNUMBERBEGINSELECT(sal*12)+nvl(comm,0)INTO:resultFROMempWHEREempno=7788;END;/PRINTresult數組DECLARETYPECalendarISVARRAY(366)OFDATE;---最大大小。CalCalendar;例子1：CREATETYPEProjectASOBJECT(project_noNUMBER(2),TitleVARCHAR2(35),costNUMBER(7,2));CREATETYPEProjectListASVARRAY(50)DECLAREAccounting_projectsProjectList;BEGINAccounting_projects:=ProjectList(Project(1,’DesignNewExpenseReport’,3250),Project(2,’OutsourcePayroll’,12350),Project(3,’AuditAccountsPayable’,1425));END;對象、數組、表類型用構造函數初始化。向表中添加值：CREATETABLEdepartment(dept_idNUMBER(2),NameVARCHAR2(15),budgetNUMBER(11,2),ProjectsProjectList);INSERTINTOdepartmentVALUES(60,’Security’,750400,ProjectList(Project(1,‘IssueNewEmployeeBadges’,13500),Project(2,’FindMissingComputerchips’,2750),Project(3,’InspectEmergencyExits’,1900)));表類型：方法描述EXISTS(n)ReturnTRUEifthenthelementinaPL/SQLtableexists;COUNTReturnsthenumberofelementsthataPL/SQLtablecurrentlycontains;FIRSTLASTReturnthefirstandlast(smallestandlastest)indexnumbersinaPL/SQLtable.ReturnsNULLifthePL/SQLtableisempty.PRIOR(n)ReturnstheindexnumberthatprecedesindexninaPL/SQLtable;NEXT(N)ReturnstheindexnumberthatsucceedsindexninaPL/SQLtable;TRIMTRIMremovesoneelementfromtheendofaPL/SQLtable.TRIM(n)removesnelementfromtheendofaPL/SQLtable.DELETEDELETEremovesallelementsfromaPL/SQLtable.DELETE(n)removesthenthelementsfromaPL/SQLtable.DELETE(m,n)removesallelementsintherangemtonfromaPL/SQLtable.例1：DECLARE TYPEdept_table_typeISTABLEOF dept%ROWTYPEINDEXBYBINARY_INTEGER; my_dname_tabledept_table_type; v_countnumber(2):=4;inumber(2);BEGIN FORiIN1..v_countLOOP SELECT*INTOmy_dname_table(i)FROMdeptWHEREdeptno=i*10; ENDLOOP; FORiINmy_dname_table.FIRST..my_dname_table.LASTLOOP DBMS_OUTPUT.PUT_LINE(‘Departmentnumber:‘||my_dname_table(i).deptno); DBMS_OUTPUT.PUT_LINE(‘Departmentname:‘||my_dname_table(i).dname); ENDLOOP;END;數據類型的轉換隱式轉換(自動)和明確轉換。DECLARE Start_timeCHAR(5); Finish_timeCHAR(5); Elapsed_timeNUMBER(5);BEGIN SELECTTO_CHAR(SYSDATE,’SSSSS’)INTOstart_timeFROMsys.dual;--/*執行某些操作*/ SELECTTO_CHAR（SYSDATE，‘SSSSS’）INTOfinish_timeFROMsys.dual; elapsed_time:=finish_time-start_time; INSERTINTOresultsVALUES(elapsed_time,…)；END;關系運算符運算符意義=等于<>,!=,~=,^=不等于<小于>大于<=小于或等于>=大于或等于一般運算符運算符意義+加號-減號*乘號/除號:=賦值號=>參數賦值號..范圍運算符||字符連接符邏輯運算符運算符意義ISNULL是空值

BETWEENAND介于兩者之間IN在一列值中間

AND邏輯與OR邏輯或NOT取返,如ISNOTNULL,NOTIN注意：在PL/SQL中變量默認初始化值為NULL。空值加數字仍是空值：NULL+<數字>=NULL空值加（連接）字符，結果為字符：NULL||<字符串>=<字符串>變量的作用范圍和可見性例1：DECLAREEmesschar(80);BEGINDECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=’president’; DBMS_OUTPUT.PUT_LINE(V1);EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘Morethanonepresident’);END;DECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=’manager’;EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘Morethanonemanager’);END;EXCEPTIONWhenothersTHENEmess:=substr(SQLERRM,1,80);DBMS_OUTPUT.PUT_LINE(Emess);END;在PL/SQL里，可以使用兩種符號來寫注釋，即：使用雙‘-‘(減號)加注釋。使用/**/來加一行或多行注釋。簡單數據插入例子例1：/*本例子僅是一個簡單的插入，不是實際應用。*/DECLAREv_enameVARCHAR2(20):=‘Bill’;v_salNUMBER(7,2):=1234.56;v_deptnoNUMBER(2):=10;v_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.dd’COMMIT;END;簡單數據刪除例子例2：/*本例子僅是一個簡單的刪除例子，不是實際應用。*/DECLAREv_empnonumber(4):=8888;BEGINDELETEFROMempWHEREempno=v_empno;COMMIT;END;用戶定義子類型語法：SUBTYPE子類型名IS基本類型名：合法：DECLARESUBTYPEEmpDateISDATE;--基于DATE類型edEmpDate;CURSORclISSELECT*FROMdept;SUBTYPEDeptFileIScl%ROWTYPE;--基于游標記錄類型Dept_recDeptFile;以下定義是不合法的：DECLARESUBTYPEAccumulatorISNUMBER(7,2);SUBTYPEWordISVARCHAR2(15);可采用如下方法：DECLARETempVARCHAR2(15);SUBTYPEWordIStemp%TYPE;注意名稱沖突例子1：DECLAREvalid_idBOOLEAN;valid_idVARCHAR2(5);FUNCTIONbonus(valid_idININTEGER)RETURNREALIS…BEGIN……END；BEGIN….END;例子2：PROCEDUREcalc_bonus(empNUMBER,bonusOUTREAL)ISavg_salREAL;BEGINSELECTAVG(sal)INTOavg_salFROMempWHERE…;END;例子3：FUNCTIONbonus(deptnoINNUMBER,…)RETURNREALISjobCHAR(10);BEGINSELECT…WHEREdeptno=bonus.deptnoANDjob=bonus.job;END;動態SQL語句例1：CREATEPROCEDUREdrop_table(table_nameINVARCHAR2)AScidINTEGER;BEGINcid:=DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(cid,’DROPTABLE‘||table_name,dbms_sql.v7);--‘DROPTABLEEMP’DBMS_SQL.CLOSE_CURSOR(cid);EXCEPTIONWHENOTHERSTHENDBMS_SQL.CLOSE_CURSOR(cid);END;參數不能進行限制！PL/SQL面向對象編程CREATETYPEBank_AccountASOBJECT(acct_number_INTEGER(5),balanceREAL,statusVARCHAR2(10),MEMBERPROCEDUREopen(amountINREAL),MEMBERPROCEDUREverify_acct(numININTEGER),MEMBERPROCEDUREclose(numININTEGER,amountOUTREAL),MEMBERPROCEDUREdeposit(numININTEGER,amountINREAL),MEMBERPROCEDUREwithdraw(numININTEGER,amountINREAL),MEMBERFUNCTIONcurr_bal(SELFINOUTBank_Account,numININTEGER)RETURNREAL);CREATETYPEBODYBank_AccountASMEMBERPROCEDUREdeposit(numININTEGER,amountINREAL)ISBEGINIFNOTamount>0THENRAISE_APPLICATION_ERROR(-20104,‘badamount’);ENDIF;Balance:=balance+amount;ENDdeposit;MEMBERPROCEDUREwithdraw(numININTEGER,amountINREAL)ISBEGINIFamount<=balanceTHENBalance:=balance-amount;ELSERAISE_APPLICATION_ERROR(-20107,‘insufficientfunds’);ENDIF;ENDwithdraw;END;PL/SQL語言的控制結構條件語句例1:DECLAREv_empnoemp.empno%TYPE:=&empno;V_salaryemp.sal%TYPE;V_commentVARCHAR2(35);BEGINSELECTsalINTOv_salaryFROMempWHEREempno=v_empno;IFv_salary<1500THENV_comment:=‘Fairlyless’;ELSIFv_salary<3000THENV_comment:=‘Alittlemore’;ELSEV_comment:=‘Lotsofsalary’;ENDIF;DBMS_OUTPUT.PUT_LINE(V_comment);END;Case表達式例2:DECLARE V_gradechar(1):=UPPER(‘&p_grade’); V_appraisalVARCHAR2(20);BEGIN V_appraisal:= CASEv_grade WHEN‘A’THEN‘Excellent’ WHEN‘B’THEN‘VeryGood’ WHEN‘C’THEN‘Good’ ELSE‘Nosuchgrade’ END; DBMS_OUTPUT.PUT_LINE(‘Grade:‘||v_grade||’Appraisal:‘||v_appraisal);END;循環語句簡單循環LOOP要執行的語句;EXITWHEN<條件表達式>/*條件滿足，退出循環語句*/ENDLOOP;例1：DECLAREiNUMBER(2):=0;BEGINLOOPi:=i+1;DBMS_OUTPUT.PUT_LINE('i的當前值為:'||i);EXITWHENi=10;ENDLOOP;END;WHILE循環WHILE<條件表達式>LOOP要執行的語句;ENDLOOP;例1.DECLAREiNUMBER:=1;BEGINWHILEi<=10LOOPDBMS_OUTPUT.PUT_LINE('i的當前值為:'||i); i:=i+1;ENDLOOP;END;FOR循環FOR循環計數器IN[REVERSE]下限..上限LOOP要執行的語句;ENDLOOP;每循環一次，循環變量自動加1；使用關鍵字REVERSE，循環變量自動減1。可以使用EXIT退出循環。例1.DECLAREinumber(2);BEGINFORiIN1..10LOOPDBMS_OUTPUT.PUT_LINE('i的當前值為:'||i);ENDLOOP;END;例2.CREATETABLEtemp_table(num_colNUMBER);DECLAREV_counterNUMBER:=10;BEGININSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterIN20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;INSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterINREVERSE25..20LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;COMMIT;END;DROPTABLEtemp_table;標號和GOTO語句例1:DECLAREV_counterNUMBER:=1;BEGINLOOPDBMS_OUTPUT.PUT_LINE('V_counter的當前值為:'||V_counter);V_counter:=v_counter+1;IFv_counter>10THENGOTOl_ENDofLOOP;ENDIF;ENDLOOP;<<l_ENDofLOOP>>DBMS_OUTPUT.PUT_LINE('V_counter的當前值為:'||V_counter);END;null語句在PL/SQL程序中，可以用null語句來說明“不用做任何事情”的意思，相當于一個占位符，可以使某些語句變得有意義，提高程序的可讀性。如：DECLARE...BEGIN…IFv_numISNULLTHENGOTOprint1;ENDIF;…<<print1>>NULL;--不需要處理任何數據。END;游標(cursor)編程基本概念定義：SELECT。物理：內存。指針：最開始指向第一條記錄。作用：逐條。分類：Server端。用戶數少。PL/SQL。Client端。用戶數多。CursorC1C2C3顯式游標處理需四個PL/SQL步驟:定義游標：就是定義一個游標名，以及與其相對應的SELECT語句。格式：CURSORcursor_name[(parameter[,parameter]…)]ISselect_statement; 在指定數據類型時，不能使用長度約束。如NUMBER(4)、CHAR(10)等都是錯誤的。打開游標：就是執行游標所對應的SELECT語句，將其查詢結果放入工作區，并且指針指向工作區的首部，標識游標結果集合。如果游標查詢語句中帶有FORUPDATE選項，OPEN語句還將鎖定數據庫表中游標結果集合對應的數據行。格式：OPENcursor_name[([parameter=>]value[,[parameter=>]value]…)];在向游標傳遞參數時，可以使用與函數參數相同的傳值方法，即位置表示法和名稱表示法。PL/SQL程序不能用OPEN語句重復打開一個游標。提取游標數據：就是檢索結果集合中的數據行，放入指定的輸出變量中。格式：FETCHcursor_nameINTO{variable_list|record_variable};對該記錄進行處理；繼續處理，直到活動集合中沒有記錄；關閉游標：當提取和處理完游標結果集合數據后，應及時關閉游標，以釋放該游標所占用的系統資源，并使該游標的工作區變成無效，不能再使用FETCH語句取其中數據。關閉后的游標可以使用OPEN語句重新打開。格式：CLOSEcursor_name;注：定義的游標不能有INTO子句。例1.查詢前10名員工的信息。SETSERVEROUTPUTONDECLARECURSORc_cursorISSELECTename,salFROMempWHERErownum<11;v_enameemp.ename%TYPE;v_salemp.sal%TYPE;BEGINOPENc_cursor;FETCHc_cursorINTOv_ename,v_sal;WHILEc_cursor%FOUNDLOOPDBMS_OUTPUT.PUT_LINE(v_ename||’---‘||to_char(v_sal));FETCHc_cursorINTOv_ename,v_sal;ENDLOOP;CLOSEc_cursor;END;例2.游標參數的傳遞方法。SETSERVEROUTPUTONDECLARE DeptRecdept%ROWTYPE; Dept_namedept.dname%TYPE; Dept_locdept.loc%TYPE; CURSORc1ISSELECTdname,locFROMdeptWHEREdeptno<=30; CURSORc2(dept_noNUMBERDEFAULT10)IS SELECTdname,locFROMdeptWHEREdeptno<=dept_no; CURSORc3(dept_noNUMBERDEFAULT10)IS SELECT*FROMdeptWHEREdeptno<=dept_no;BEGIN OPENc1; LOOP FETCHc1INTOdept_name,dept_loc; EXITWHENc1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(dept_name||’---‘||dept_loc); ENDLOOP; CLOSEc1; OPENc2; LOOP FETCHc2INTOdept_name,dept_loc; EXITWHENc2%NOTFOUND; DBMS_OUTPUT.PUT_LINE(dept_name||’---‘||dept_loc); ENDLOOP; CLOSEc2; OPENc3(20); LOOP FETCHc3INTOdeptrec; EXITWHENc3%NOTFOUND; DBMS_OUTPUT.PUT_LINE(deptrec.deptno||’---‘||deptrec.dname||’---‘||deptrec.loc); ENDLOOP; CLOSEc3;END;2.游標屬性%FOUND布爾型屬性，當最近一次讀記錄時成功返回,則值為TRUE；%NOTFOUND布爾型屬性，與%FOUND相反；%ISOPEN布爾型屬性，當游標已打開時返回TRUE；%ROWCOUNT數字型屬性，返回已從游標中讀取的記錄數。例1：給工資低于1200的員工增加工資50。DECLAREv_empnoemp.empno%TYPE;v_salemp.sal%TYPE;CURSORc_cursorISSELECTempno,salFROMemp;BEGINOPENc_cursor;LOOPFETCHc_cursorINTOv_empno,v_sal;EXITWHENc_cursor%NOTFOUND;IFv_sal<=1200THENUPDATEempSETsal=sal+50WHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'工資已更新!');ENDIF;DBMS_OUTPUT.PUT_LINE('記錄數:'||c_cursor%ROWCOUNT);ENDLOOP;CLOSEc_cursor;END;用FOR循環處理游標（建議游標編程采用這種方式。）例1：DECLARECURSORc_salISSELECTempno,ename,salFROMemp;BEGIN--隱含打開游標FORv_salINc_salL--隱含執行一個FETCH語句 DBMS_OUTPUT.PUT_LINE(to_char(v_sal.empno)||’---‘||v_sal.ename||’---‘||to_char(v_sal.sal));--隱含監測c_sal%NOTFOUNDENDLOOP;--隱含關閉游標END;注：不要在程序中對游標進行人工操作；不要在程序中定義用于控制FOR循環的記錄。例2：當所聲明的游標帶有參數時，通過游標FOR循環語句為游標傳遞參數。DECLARE CURSORc_cursor(dept_noNUMBERDEFAULT10)IS SELECTdname,locFROMdeptWHEREdeptno<=dept_no;BEGIN DBMS_OUTPUT.PUT_LINE(‘dept_no參數值為30：’); FORc1_recINc_cursor(30)LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.dname||’---‘||c1_rec.loc); ENDLOOP; DBMS_OUTPUT.PUT_LINE(CHR(10)||’使用默認的dept_no參數值10：’); FORc1_recINc_cursorLOOP DBMS_OUTPUT.PUT_LINE(c1_rec.dname||’---‘||c1_rec.loc); ENDLOOP;END;例3：隱含游標。BEGIN FORc1_recIN(SELECTdname,locFROMdept)LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.dname||’---‘||c1_rec.loc); ENDLOOP;END;隱式游標例1:刪除EMP表中某部門的所有員工，如果該部門中已沒有員工，則在DEPT表中刪除該部門。DECLAREV_deptnoemp.deptno%TYPE:=&p_deptno;BEGIN DELETEFROMempWHEREdeptno=v_deptno; IFSQL%NOTFOUNDTHEN DELETEFROMdeptWHEREdeptno=v_deptno; ENDIF;END;通過游標修改數據例1：從EMP表中查詢某部門的員工情況，將其工資最低定為1500；DECLAREV_deptnoemp.deptno%TYPE:=&p_deptno; CURSORemp_cursorISSELECTempno,salFROMempWHEREdeptno=v_deptnoFORUPDATENOWAIT;BEGIN FORemp_recordINemp_cursorLOOPIFemp_record.sal<1500THEN UPDATEempSETsal=1500WHERECURRENTOFemp_cursor;ENDIF; ENDLOOP; COMMIT;END;電信計費程序：call_ticket表Call_noCalled_noCall_time62341234234560625612340202345100623812340012345200623412340222345100623812343456200Call_fee表Call_noCall_fee623412341.5625612341.2623812345.2計費規則：以分鐘為單位。本地：0.30國內：0.60國際：1.00FORphone_recINphone_recLOOPFORticket_recINticket_curLOOPLOOP;LOOP;錯誤處理基本概念PL/SQL中有兩種類型的錯誤：預定義(Predefined)錯誤ORACLE預定義的異常情況大約有24個。對這種異常情況的處理，無需在程序中定義，由ORACLE自動將其引發。用戶定義(User_define)錯誤程序執行過程中，出現編程人員認為的非正常情況。對這種異常情況的處理，需要用戶在程序中定義，然后顯式地在程序中將其引發。錯誤號異常錯誤信息名稱說明ORA-0001Dup_val_on_index試圖破壞一個唯一性限制ORA-0051Timeout_on_resource在等待資源時發生超時ORA-0061Transaction_backed_out由于發生死鎖事務被撤消ORA-1001Invalid_CURSOR試圖使用一個無效的游標ORA-1012Not_logged_on沒有連接到ORACLEORA-1017Login_denied無效的用戶名/口令ORA-1403No_data_foundSELECTINTO沒有找到數據ORA-1422Too_many_rowsSELECTINTO返回多行ORA-1476Zero-divide試圖被零除ORA-1722Invalid-NUMBER轉換一個數字失敗ORA-6500Storage-error內存不夠引發的內部錯誤ORA-6501Program-error內部錯誤ORA-6502Value-error轉換或截斷錯誤ORA-6504Rowtype-mismatch縮主游標變量與PL/SQL變量有不兼容行類型ORA-6511CURSOR-already-OPEN試圖打開一個已存在的游標ORA-6530Access-INTO-null試圖為null對象的屬性賦值ORA-6531Collection-is-null試圖將Exists以外的集合(collection)方法應用于一個nullpl/sql表上或varray上ORA-6532Subscript-outside-limit對嵌套或varray索引得引用超出聲明范圍以外ORA-6533Subscript-beyond-count對嵌套或varray索引得引用大于集合中元素的個數.例1：更新指定員工工資，如工資小于1500，則加100；DECLAREv_empnoemp.empno%TYPE:=&empno;v_salemp.sal%TYPE;BEGINSELECTsalINTOv_salFROMempWHEREempno=v_empno;IFv_sal<=1500THENUPDATEempSETsal=sal+100WHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已更新!');ELSEDBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已經超過規定值!');ENDIF;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('數據庫中沒有編碼為'||v_empno||'的員工');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('程序運行錯誤!請使用游標');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);END;例2：更新指定員工工資，增加100；DECLAREv_empnoemp.empno%TYPE:=&empno;no_resultEXCEPTION;BEGINUPDATEempSETsal=sal+100WHEREempno=v_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('你的數據更新語句失敗了!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);END;調用DBMS_STANDARD(ORACLE提供的包)包所定義的RAISE_APPLICATION_ERROR過程，可以重新定義異常錯誤消息，它為應用程序提供了一種與ORACLE交互的方法。RAISE_APPLICATION_ERROR的語法如下：RAISE_APPLICATION_ERROR(error_number,error_message);這里的error_number是從–20,000到–20,999之間的參數，error_message是相應的提示信息(<2048字節)。例4：創建一個函數get_salary,該函數檢索指定部門的工資總和，其中定義了-20991和-20992號錯誤，分別處理參數為空和非法部門代碼兩種錯誤：CREATETABLEerrlog( ErrcodeNUMBER, ErrtextCHAR(40));CREATEORREPLACEFUNCTIONget_salary(p_deptnoNUMBER) RETURNNUMBERAS V_salNUMBER;BEGIN IFp_deptnoISNULLTHEN RAISE_APPLICATION_ERROR(-20991,’部門代碼為空’); ELSIFp_deptno<0THEN RAISE_APPLICATION_ERROR(-20992,’無效的部門代碼’); ELSE SELECTSUM(sal)INTOv_salFROMEMPWHEREdeptno=p_deptno; RETURNV_sal; ENDIF;END;在PL/SQL中使用SQLCODE,SQLERRM例1.查詢ORACLE錯誤代碼；BEGININSERTINTOemp(empno,ename,hiredate,deptno) VALUES(2222,‘Jerry’,SYSDATE,20);DBMS_OUTPUT.PUT_LINE('插入數據記錄成功!');INSERTINTOemp(empno,ename,hiredate,deptno) VALUES(2222,‘Jerry’,SYSDATE,20);DBMS_OUTPUT.PUT_LINE('插入數據記錄成功!');EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);END;存儲過程和函數存儲過程例1．刪除指定員工記錄；CREATEORREPLACEPROCEDUREDelEmp(v_empnoINemp.empno%TYPE)ASNo_resultEXCEPTION;BEGINDELETEFROMempWHEREempno=v_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'的員工已被除名!');EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('你需要的數據不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);ENDDelEmp;例11：查詢指定員工記錄；CREATEORREPLACEPROCEDUREQueryEmp(v_empnoINemp.empno%TYPE, v_enameOUTemp.ename%TYPE, v_salOUTemp.sal%TYPE)ASBEGIN SELECTename,salINTOv_ename,v_salFROMempWHEREempno=v_empno; DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'的員工已經查到!');EXCEPTION WHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的數據不存在!'); WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);ENDQueryEmp;調用存儲過程:SETSERVEROUTPUTONDECLAREv1emp.ename%TYPE;v2emp.sal%TYPE;BEGINQueryEmp(7788,v1,v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工資:'||v2);QueryEmp(7902,v1,v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工資:'||v2);QueryEmp(8899,v1,v2);DBMS_OUTPUT.PUT_LINE('姓名:'||v1);DBMS_OUTPUT.PUT_LINE('工資:'||v2);END;例12．計算指定部門的工資總和，并統計其中的職工數量。CREATEORREPLACEPROCEDUREproc_demo(Dept_noNUMBERDEFAULT10, Sal_sumOUTNUMBER, Emp_countOUTNUMBER)ISBEGIN SELECTSUM(sal),COUNT(*)INTOsal_sum,emp_countFROMempWHEREdeptno=dept_no;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的數據不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);ENDproc_demo;調用存儲過程:DECLAREV_numNUMBER;V_sumNUMBER(8,2);BEGIN Proc_demo(30,v_sum,v_num);DBMS_OUTPUT.PUT_LINE('30號部門工資總和：'||v_sum||’，人數：’||v_num); Proc_demo(sal_sum=>v_sum,emp_count=>v_num);DBMS_OUTPUT.PUT_LINE('10號部門工資總和：'||v_sum||’，人數：’||v_num);END;在PL/SQL程序中還可以在塊內建立本地函數和過程，這些函數和過程不存儲在數據庫中，但可以在創建它們的PL/SQL程序中被重復調用。本地函數和過程在PL/SQL塊的聲明部分定義，它們的語法格式與存儲函數和過程相同，但不能使用CREATEORREPLACE關鍵字。例13：建立本地過程，用于計算指定部門的工資總和，并統計其中的職工數量；DECLAREV_numNUMBER;V_sumNUMBER(8,2);PROCEDUREproc_demo (Dept_noNUMBERDEFAULT10, Sal_sumOUTNUMBER, Emp_countOUTNUMBER)ISBEGIN SELECTSUM(sal),COUNT(*)INTOsal_sum,emp_countFROMempWHEREdeptno=dept_no;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的數據不存在!');WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);ENDproc_demo;BEGIN Proc_demo(30,v_sum,v_num);DBMS_OUTPUT.PUT_LINE('30號部門工資總和：'||v_sum||’，人數：’||v_num); Proc_demo(sal_sum=>v_sum,emp_count=>v_num);DBMS_OUTPUT.PUT_LINE('10號部門工資總和：'||v_sum||’，人數：’||v_num);END;函數例1.獲取某部門的工資總和：CREATEORREPLACEFUNCTIONget_salary( Dept_noNUMBER, Emp_countOUTNUMBER) RETURNNUMBERIS V_sumNUMBER;BEGIN SELECTSUM(sal),count(*)INTOV_sum,emp_count FROMempWHEREdeptno=dept_no; RETURNv_sum;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的數據不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);ENDget_salary;函數的調用例1：計算某部門的工資總和：DECLARE V_numNUMBER; V_sumNUMBER;BEGIN V_sum:=get_salary(30,v_num); DBMS_OUTPUT.PUT_LINE(’30號部門工資總和：’||v_sum||’，人數：’||v_num);END;例2：計算某部門的工資總和：DECLARE V_numNUMBER; V_sumNUMBER;BEGIN V_sum:=get_salary(emp_count=>v_num,dept_no=>30); DBMS_OUTPUT.PUT_LINE(’30號部門工資總和：’||v_sum||’，人數：’||v_num);END;在SQL*PLUS中調用存儲過程、函數、包SQL>SETSERVETOUTPUTONSQL>VARIABLEnumNUMBERSQL>EXECUTE:num:=1;SQL>EXECUTEcalc_payroll(:num)SQL>PRINTnum創建包(Package)包聲明：CREATEPACKAGEemp_actionsASTYPEEmpRecTypISRECORD(emp_idINTEGER,salaryREAL);CURSORdesc_salaryRETURNEmpRecTyp;PROCEDUREhire_employee(EnameVARCHAR2,jobVARCHAR2,mgrNUMBER,SalNUMBER,commNUMBER,deptnoNUMBER);ENDemp_actions;包主體：CREATEPACKAGEBODYemp_actionsASCURSORdesc_salaryRETURNEmpRecTypIS SELECTempno,salFROMempORDERBYsalDESC;PROCEDUREhire_employee(enameVARCHAR2,jobVARCHAR2,mgrNUMBER,SalNUMBER,commNUMBER,deptnoNUMBER)ISBEGININSERTINTOempVALUES(empno_seq.NEXTVAL,ename,job,Mgr,SYSDATE,sal,comm,deptno);COMMIT；ENDhire_employee:ENDemp_actions;SQL>EXECUTEemp_actions.hire_employees();觸發器(Trigger)1.基本概念(DML觸發器)與表關聯。自動執行的存儲過程。不能直接調用。三種類型：INSERT、DELETE、UPDATE。FOREACHROW觸發器STATEMENT觸發器。BEFORE和AFTER觸發器。是一個事務。一個表可有多個同類型的觸發器。觸發器不應該返回結果集。觸發器的限制CREATETRIGGER語句文本的字符長度不能超過32KB；只能有SELECT…INTO…結構，不能有SELECT語句。觸發器中不能使用數據庫事務控制語句COMMIT;ROLLBACK,SAVEPOINT語句；由觸發器所調用的過程或函數也不能使用數據庫事務控制語句；觸發器中不能使用LONG,LONGRAW類型；觸發器內可以參照LOB類型列的列值，但不能通過:NEW修改LOB列中的數據；觸發器在數據庫編程中的作用：實現表和表之間的級聯變化RK表prodnameQtyCK表prodnameQtyKC表prodnameQtyNokia0Moto0例子1：用觸發器實現審計功能。CREATETRIGGERaudit_triggerBEFOREINSERTORDELETEORUPDATEONclassified_tableFOREACHROWBEGINIFINSERTINGTHENINSERTINTOaudit_tableVALUES(USER||’isinserting’||’newkey:’||:new.key);ELSIFDELETINGTHENINSERTINTOaudit_tableVALUES(USER||’isdeleting’||’oldkey:’||:old.key);ELSIFUPDATING(’FORMULA’)THENINSERTINTOaudit_tableVALUES(USER||’isupdating’||’oldformula:’||:old.formula||’newformula:’||:new.formula);INSERTINTOaudit_tableVALUES(USER||’isupdating’||’oldkey:’||:old.key||’newkey:’||:new.key);ENDIF;END;:old表：系統臨時表。只能在觸發器中使用。結構跟定義觸發器的表的結構一樣。剛刪除的數據。:new表：系統臨時表。只能在觸發器中使用。結構跟定義觸發器的表的結構一樣。剛添加的數據。在UPDATE觸發器中：:new表中存放更新后的數據，:old表中存放更新前的數據。例子2：CREATETRIGGERscott.salary_checkBEFOREINSERTORUPDATEOFsal,jobONscott.empFOREACHROWWHEN(new.job<>’PRESIDENT’)DECLAREMinsalNUMBER;BEGIN…END；2.替代(INSTEADOF)觸發器只能對視圖和對象視圖建立INSTEADOF觸發器，而不能對表、方案和數據庫建立INSTEADOF觸發器。例2:CREATEORREPLA