32orcale存儲過程jdbc與大操作等-plsql

第一 PL/SQL程序設(shè)計(jì)簡PL/SQL是一種高級數(shù)據(jù)庫程序設(shè)計(jì)語言，該語言專門用于在各種環(huán)境下對ORACLE數(shù)據(jù)庫過函包 SQL PL/SQL是ProcedureLanguage&StructuredQueryLanguage的縮寫。ORACLE的SQLANSI(AmericannationalStandardsInstitute)和ISO92(InternationalStandardsOrganization)標(biāo)準(zhǔn)的產(chǎn)品。PL/SQL是對SQL語言過程語言的擴(kuò)展ORACLE6以后，ORACLERDBMSPL/SQL。它現(xiàn)在已經(jīng)成為一種PL/SQL另外還增加了用于支持工具（ORACLEForms）的句法，如：PL/SQL內(nèi)容。 PL/SQL 有利于客戶/對于客戶服務(wù)器環(huán)境來說，真正的瓶頸是網(wǎng)絡(luò)上。無論網(wǎng)絡(luò)多快，只要客戶端與服務(wù)L將這種具有大量數(shù)據(jù)處理的應(yīng)用放在服務(wù)器端來執(zhí)行。自然就省去了數(shù)據(jù)在網(wǎng)上的傳輸時間。 PL/SQLPL/SQLPL/SQL。對于客戶端來說，PL/SQL可PL/SQL程序運(yùn)行。 PL/SQLSQL成PL/SQLSQL語句有：提示：在PL/SQL中只能用SQL語句中的DML部分，不能用DDLPL/SQL中使用DDL(如 等)的話，只能以動態(tài)的方式來使用ORACLE的PL/SQL組件在對PL/SQL程序進(jìn)行解釋時，同時對在其所使用的PL/SQLSQL*PLUSPL/SQLPL/SQLORACLE其它開發(fā)工具也可以調(diào)用PL/SQL編寫的過函數(shù)，如PowerBuilder等都可以PL/SQL過程。 PL/SQLPL/SQL程序的運(yùn)行是通過ORACLE中的一個引擎來進(jìn)行的。這個引擎可能在ORACLEORACLEPL/SQL中的過SQL語句發(fā)送給數(shù)據(jù)庫服務(wù)器來執(zhí)行。再將結(jié)果返回給執(zhí)行端。第二 PL/SQL塊結(jié)構(gòu)和組成元 PL/SQLPL/SQL程序由三個塊組成，即部分、執(zhí)行部分、異常處理部分PL/SQL/*部分:在此PL/SQL用到的變量,類型及游標(biāo)，以及局部的過函數(shù) 執(zhí)行部分 過程及SQL語句,即程序的主要部分/*執(zhí)行異常部分:錯誤處理PL/SQL：子程序在數(shù)據(jù)庫中的過程、函數(shù)及包等。當(dāng)在數(shù)據(jù)庫上建立好后可以在其它： PL/SQL子塊可以位于PL/SQL中的 標(biāo)識符名過30字符SQL提示 例如：下面的例子將會刪除所有的，而不是KING的記錄Enamevarchar2(20):=’KING’;DELETEFROMempWHEREename=ename;PL/SQL中有特別的講究，建議在系統(tǒng)的設(shè)計(jì)階段就要求所有編程人員共同遵守一定的要求，使得整個系統(tǒng)的文檔在規(guī)范上達(dá)到要求。下面是建議名方法：表SQL*Plus PL/SQLPL/SQL中的變量類型的合法使用列表： 類子 ORACLE限帶符號整數(shù),為整數(shù)計(jì)算小數(shù)NUMBER的子類型整數(shù)NUMBERSmall整數(shù)NUMBER的子類型與NUMBER等價與NUMBER等價整數(shù),integer小32,767字1日至公元471212日 ORACLE在PL/SQL中除了提供象前面介紹的各種類型外,還提供一種稱為復(fù)合類型§2.4.2.1記錄類型是把邏輯相關(guān)的數(shù)據(jù)作為一個單元起來，稱作PL/SQLRECORD的TYPErecord_typeISField1[NOT[:=exp1Field2[NOT[:=exp2.. ..Fieldn[NOT[:=expn])1或提示 過程的功能類似 中System.out.println()直接將輸出結(jié)果送到標(biāo)準(zhǔn)輸出中2)在使用上述過程之前必須將SQL*PLUS的環(huán)境參數(shù)SERVEROUTPUT設(shè)置為ON,否則將看不到輸出結(jié)果: setserveroutputon可以用SELECT語句對記錄變量進(jìn)行賦值,只要保證記錄字段與查詢結(jié)果列表中的字§2.4.2.2使用所的數(shù)據(jù)庫列的數(shù)據(jù)類型可以不必知道所的數(shù)據(jù)庫列的數(shù)據(jù)類型可以實(shí)時改變。2：§2.4.3使用PL/SQL提供%ROWTYPE操作符,返回一個記錄類型,其數(shù)據(jù)類型和數(shù)據(jù)庫表的數(shù)所的數(shù)據(jù)庫中列的個數(shù)和數(shù)據(jù)類型可以實(shí)時改變。3：§2.4.4PL/SQL表(嵌套表PL/SQL程序可使用嵌套表類型創(chuàng)建具有一個或多個列和無限行的變量,這很像數(shù)據(jù)庫中的表.嵌套表類型的一般語法如下:TYPEtype_nameISTABLE{datatype|{variable|table.column}%type|ReturnTRUEifthenthelementinaPL/SQLtableReturnsthenumberofelementsthataPL/SQLtablecurrentlyReturnthefirstandlast(smallestandlastest)indexnumbersinatable.ReturnsNULLifthePL/SQLtableisReturnstheindexnumberthatprecedesindexninaPL/SQLReturnstheindexnumberthatsucceedsindexninaPL/SQLTRIMremovesoneelementfromtheendofaPL/SQLTRIM(n)removesnelementfromtheendofaPL/SQLDELETEremovesallelementsfromaPL/SQLtable.DELETE(n)removesthenthelementsfromaPL/SQLtable.DELETE(m,n)removesallelementsintherangemtonfroma說明:1)在使用嵌套表之前必須先使用該集合的構(gòu)造器初始化它.PL/SQL自動提供一個帶有2)嵌套表可以有任意數(shù)量的行.表的大小在必要時可動態(tài)地增加或減少:extend(x)方法添加x個空元素到集合末尾; trim(x)方法為去掉集合末尾的x個元素. 運(yùn)算符和表達(dá)式(數(shù)據(jù)定義 =<>,!=,~=,<> +-*/ IS 取返,ISNOTNULLNOT :=expressionvariablePL/SQL變量expressionPL/SQL表達(dá)式 空值加數(shù)字仍是空值：NULL+<數(shù)字空值加（連接）字符，結(jié)果為字符：NULL||字符串>=<字符串 BOOLEAN布爾值只有TRUE,FALSENULL emp_nameemp.ename%TYPE; SELECTename,NVL(sal,0)+NVL(comm,0)INTOemp_name,wagesFROMempWHEREempno=emp_id; 提示：不能將SELECT CHAR轉(zhuǎn)換為TO_NUMBER :=TO_NUMBER(‘100.0’)+NUMBERTO_CHARmTO_CHAR(‘123.45||元TO_DATEv_date:=使用 當(dāng)一個變量超出了作用范圍，PL/SQL引擎就釋放用來存放該變量的空間（因?yàn)樗?使‘-‘(減號)加注PL/SQL– NUMBER(12,2);--使用 /*文件名： 提示：被解釋存放在數(shù)據(jù)庫中的PL/SQL程序，一般系統(tǒng)自動將程序頭部的注釋去掉。只 11：/*本例子僅是一個簡單的插入，不是實(shí)際應(yīng)用。*/ VARCHAR2(20):=‘Bill’; NUMBER(7,2):=1234.56; NUMBER(2):=10; NUMBER(4):=8888;INSERTINTOemp(empno,ename,JOB,sal,deptno,hiredate)VALUES(v_empno,v_ename,‘Manager’,v_sal,v_deptno,TO_DATE(’1954.06.09’,’yyyy.mm.dd’)); 12：/*本例子僅是一個簡單的刪除例子，不是實(shí)際應(yīng)用。*/ number(4):=8888;DELETEFROMempWHEREempno=v_empno;第三章PL/SQL流程控制語PL/SQL的流程控制語句,包括如下三類控制語句IF循環(huán)語句LOOP語句EXIT順序語句GOTO語句NULL IF<布爾表達(dá)式PL/SQLSQLENDIF<布爾表達(dá)式>THENPL/SQLSQL語句ENDIF<布爾表達(dá)式>THENPL/SQLSQL語句ELSIF<其它布爾表達(dá)式ELSIF<其它布爾表達(dá)式END提示ELSIF不能寫成v_empnoemp.empno%TYPE;V_salaryemp.sal%TYPE;mentSELECTsalINTOv_salaryFROMempWHEREIFv_salary<1500ment:=‘FairlyELSIFv_salary<3000ment:=‘AlittleEND

ment:=‘Lotsof CASECASEWHENexpression1THENresult1WHENexpression2THENWHENexpressionNTHENresultN[ELSEresultN+1]V_gradechar(1);V_appraisal:=CASEWHEN‘A’THEN‘Excellent’WHEN‘B’THEN‘VeryGood’WHEN‘C’THEN‘Good’ELSE‘Nosuchgrade’ Appraisal:‘||v_appraisal); EXITWHEN<條件語句 END例intNUMBER(2):=0;int:=int+DBMS_OUTPUT.PUT_LINE('int的當(dāng)前值為:'||int);EXITWHENint=10;ENDLOOP;WHILEWHILE<布爾表達(dá)式ENDxNUMBER:=1;WHILEx<=10LOOPx:=x+1;ENDLOOP;FORINREVERSE]..END1REVERSE1INREVERSE后面的數(shù)字必須是從小到大的順序，而且必須是整數(shù)，不能是變量或表達(dá)式。EXIT退出循環(huán)。FOR in1..10DBMS_OUTPUT.PUT_LINE('int的當(dāng)前值為||int);ENDLOOP;例V_counterNUMBER:=10;INSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterIN20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;INSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterINREVERSE20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;END .. .. >>括起來的標(biāo)識符V_counterNUMBER:=1;V_counter:=v_counter+1;IFv_counter>10GOTOENDENDEND; NULLPL/SQLnull語句來說明“不用做任何事情”...…IFv_numISNULLGOTOprint1;ENDIF;… --第四章游標(biāo)的PL/SQL§4.1SQL語句，ORACLE必須分配一片叫上下文contextarea的區(qū)域來處理及查詢的活動集(activeset)。游標(biāo)是一個指向上下文的句柄(handle)或指針。通過游標(biāo)，PL/SQL可以控制上下文SQL§4.1.1PL/SQL步驟定義游標(biāo)：就是定義一個游標(biāo)名，以及與其相對應(yīng)的SELECT語句。CURSORcursor_name[(parameter[,parameter]…)]ISparameter_name[IN]datatype[{:=|DEFAULT}在指定數(shù)據(jù)類型時，不能使用長度約束NUMBER(4)、CHAR(10)打開游標(biāo)：SELECT語句，將其查詢結(jié)果放入工作區(qū)，并且指項(xiàng)，OPEN語句還將鎖定數(shù)據(jù)庫表中游標(biāo)結(jié)果集合對應(yīng)的數(shù)據(jù)行。OPENcursor_name[([parameter=>]value[,[parameter=>]法。PL/SQLOPEN語句重復(fù)打開一個游標(biāo)。FETCHcursor_nameINTO{variable_list|record_variableFETCH語句取其中數(shù)據(jù)。OPEN語句重新打開。CLOSEINTO子句。1.10名員工的信息。2.或 3：3000的員工工資調(diào)為3000。PL/SQL語言提供了FOROPEN、FETCH、CLOSEFORindex_variableINcursor_name[value[,value]…]--ENDindex_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)FOR循環(huán)語句中的索引變量來這些列數(shù)據(jù)。FOR例5：當(dāng)所的游標(biāo)帶有參數(shù)時，通過游標(biāo)FOR循環(huán)語句為游標(biāo)傳遞參數(shù)或6：PL/SQL還允許在游標(biāo)FOR§4.1.2、其工作區(qū)，這些由系統(tǒng)隱含創(chuàng)建的游標(biāo)稱為隱式游標(biāo)，隱式游標(biāo)的名字為SQL，這是由ORACLEORACLE作。在隱式游標(biāo)的工作區(qū)中，所存放的數(shù)據(jù)是與用戶自定義的顯示游標(biāo)無關(guān)的處理的SQL語句所包含的數(shù)據(jù)。、 布爾型屬性,與%FOUND相反；SQL 數(shù)字型屬性,返回已從游標(biāo)中得記錄數(shù)SQLISOPEN 布爾型屬性,取值總是FALSESQL命令執(zhí)行完畢立即關(guān)閉隱式游標(biāo)。7:更新指定員工信息，如果該員工沒有找到，則打印”查無此人”信息?！?.1.3NO_DATA_FOUND%NOTFOUNDSELECTINTO當(dāng)一個顯式游標(biāo)的WHERE子句未找到時觸發(fā)UPDATEDELETE語句的WHERESQL%NOTFOUND；在提取循環(huán)中要用%NOTFOUND%FOUND§4.1.4為了對正在處理(查詢)的行不被另外的用戶改動，ORACLEFORUPDATESELECT...FROM…FORUPDATE[OFcolumn[,column]…]SELECTFORUPDATE操作一直等OPEN立即返回并給出：ORA-0054：resourcebusyandacquirewithnowait如果使用FORUPDATE游標(biāo)，則可在DELETE和UPDATE語句中使用WHERECURRENTOFcursor_name子句，修改或刪除游標(biāo)結(jié)果集合當(dāng)前行對應(yīng)的數(shù)據(jù)第五 異常錯誤處提供異常情況(EXCEPTION)和異常處理(EXCEPTIONHANDLER)來實(shí)現(xiàn)錯誤處理?！?.1異常情況處理(CTI)是用來處理正常執(zhí)行過程中未預(yù)料的事件程序塊的異常處理預(yù)定義的錯誤和自定義錯誤由于L/L程序塊一旦產(chǎn)生異常而沒有如何處理程序就會自動終止整個程序運(yùn)行.PredefinedORACLE24由ORACLE自動將其。PredefinedORACLE錯誤。對這種異常情況的處理，需要用戶在程序中定義，然后由ORACLE自動將其。用戶定義(User_define)PL/SQL程序體的后半部,結(jié)構(gòu)為WHENfirst_exceptionTHEN <codetohandlefirstexception>WHENsecond_exceptionTHEN <codetohandlesecondexception>WHENOTHERSTHEN <codetohandleothersexception>異常處理可以按任意次序排列,OTHERS必須放在最后§5.1.1ORACLESELECTINTOSELECTINTO內(nèi)存不夠的內(nèi)部錯PL/SQLnull試圖將Exists以外的集合(collection)nullpl/sql表上或varraySubscript--對嵌套或varray索引得大于集合中元素個數(shù)對這種異常情況的處理只需在PL/SQL塊的異常處理部分直接相應(yīng)的異常情況例1：更新指定員工工資，如工資小于300，則加100；對NO_DATA_FOUND異常,TOO_MANY_ROWS進(jìn)行處理.§5.1.2ORACLEPL/SQL塊的定義部分定義異常情況 ORACLE>,<PL/SQL塊的異常情況處理部分對異常情況做出相應(yīng)的處理。2：刪除指定部門的記錄信息，以確保該部門沒有員工?！?.1.3是通過顯式使用RAISE語句來觸發(fā)當(dāng)一個異常錯誤時控制就轉(zhuǎn)向到EXCEPTIONPL/SQL塊的定義部分定義異常情況 RAISE異常情況PL/SQL塊的異常情況處理部分對異常情況做出相應(yīng)的處理: PL/SQLSQLCODESQLCODESQLERRM返回錯誤信息如 ?SQLERRM=’no_data_found ?SQLERRM=’normal,successfual5.ORACLE ORACLE錯誤信息FORerr_numIN-100..0LOOPerr_msg:=SQLERRM(err_num);INSERTINTOerrorsVALUES(err_num,err_msg);ENDLOOP;6.ORACLEINSERTINTOemp(empno,ename,hiredate,deptno)VALUES(2222,‘Jerry’,SYSDATE,20);INSERTINTOemp(empno,ename,hiredate,VALUES(2222,‘Jerry’,SYSDATE,20);WHENOTHERSTHEN

第六 函數(shù)和過1．ORACLE提供可以把PL/SQL程序在數(shù)據(jù)庫中，并可以在任何地方來運(yùn)行它。這樣就叫過程或函數(shù)。過函數(shù)統(tǒng)稱為PL/SQL子程序，他們是被命名 CREATE[ORREPLACE]FUNCTIONfunction_name[(argment[{IN|INOUT}]Type,argment[{IN|OUT|INOUT}]Type][AUTHIDDEFINER|CURRENT_USERRETURN{IS|AS說明ORREPLACE為可選.有了它,可以或者創(chuàng)建一個新函數(shù)或者替換相同名字的函數(shù),函數(shù)名后面是一個可選的參數(shù)列表,IN,OUTINOUT標(biāo)記.參數(shù)之間用逗號隔開.IN參數(shù)標(biāo)記表示傳遞給函數(shù)的值在該函數(shù)執(zhí)行中不改變;OUT標(biāo)記表示一個值在函數(shù)中進(jìn)行計(jì)算并通過該參數(shù)傳遞給調(diào)用語句;INOUT標(biāo)記表示傳遞給函數(shù)的值可以變化并傳遞給調(diào)用語句.若省略標(biāo)記,則參數(shù)隱含為IN。因?yàn)楹瘮?shù)需要返回一個值,所以RETURN包含返回結(jié)果的數(shù)據(jù)類型. 例 函數(shù)時所定義的參數(shù)稱為形式參數(shù)，應(yīng)用程序調(diào)用時為函數(shù)傳遞的參數(shù)稱為實(shí)際參數(shù)。argument=>parameter在CREATEORREPLACEFUNCTION語句中函數(shù)參數(shù)時可以使用DEFAULT關(guān) 過 在ORACLESERVER上建立過程,可以被多個應(yīng)用程序調(diào)用,可以向過程傳遞參數(shù),也可以向過程傳回參數(shù).創(chuàng)建過程語法CREATE[ORREPLACE]PROCEDUREProcedure_name[(argment[{IN|INOUT}]Type,argment[{IN|OUT|INOUT}]Type][AUTHIDDEFINER|CURRENT_USER{IS|AS 調(diào)用過ORACLE使用EXECUTE語句來實(shí)現(xiàn)對過程的調(diào)用 調(diào)用方法調(diào)用方法 在創(chuàng)建過程時,可使用AUTHIDCURRENT_USER或AUTHIDDEFINER選項(xiàng)Oracle使用的權(quán)限AUTHIDCURRENT_USER選項(xiàng)創(chuàng)建一個過程,Oracle用調(diào)用該過程的用戶權(quán)限執(zhí)行該過程.為了成功執(zhí)行該過程,調(diào)用者必須具有該過程體中的所有數(shù)據(jù)庫對象所必須的權(quán)限如果用默認(rèn)的AUTHIDDEFINER選項(xiàng)創(chuàng)建過程,則Oracle使用過程所有者的執(zhí)行該過程.為了成功執(zhí)行該過程,過程的所有者必須具有該存儲過程體中的所有數(shù)據(jù)庫對象所必須的權(quán)限.想要簡化應(yīng)用程序用戶的特權(quán)管理,在創(chuàng)建過程時,一般選擇AUTHIDDEFINER選項(xiàng)–--這樣就不必給需要調(diào)用的此過程的所有用戶了. 開發(fā)過程步 ， 在SQLPLUS或用調(diào)試工具將過程程序進(jìn)行解釋在SQLPLUS或用調(diào)試工具將過程程序進(jìn)行解釋；SQL>STARTGETORACLE SQLPLUS下來調(diào)式主要用的方法是：SHOWERROR命令來提示源碼的錯誤位置使用user_errors數(shù)據(jù)字典來查看各過程的錯誤位置 執(zhí)行權(quán)給相關(guān)的用戶或角如果調(diào)式正確的過程沒有進(jìn)行那就只有建立者本人才可以運(yùn)行所以作為應(yīng)用系統(tǒng)的一部分的過程也必須進(jìn)行才能達(dá)到要求在SQL*PLUS下可以用GRANTEXECUTEONdbms_jobTOPUBLICWITHGRANT 相關(guān)的權(quán)限CREATEANYPROCEDUREDROPANYPROCEDURESQL*PLUSDESCRIBE命令查看過程的名字及其參數(shù)表DESCRIBE 刪除過函DROPPROCEDURE可以使用DROPFUNCTIONDROPFUNCTION第七章包的創(chuàng)建和應(yīng) PL/SQL程序設(shè)計(jì)元素的封裝。C++和允許的程序范圍不同，即它們的作用域不同。公用元素不僅可以被包中的函數(shù)、過程所調(diào)用，也可以被包外的PL/SQL程序，而私有元素只能被包內(nèi)的函數(shù)和過程序所。I/O操作，從而使程序執(zhí)行效率得到提高。一個包由兩個分開的部分組成（ACKAGEBODY分所的游標(biāo)和子程序，在包主體中還可以包的私有元素。user_source,all_source,dba_source. CREATE[ORREPLACE]PACKAGEpackage_name[AUTHID{CURRENT_USER|DEFINER}]{IS|[公有游標(biāo)[公有游標(biāo)]…][公有變量、常量[公有變量、常量]…][公有子程序[公有子程序]…]END其中：AUTHIDCURRENT_USER和AUTHIDDEFINER選項(xiàng)說明應(yīng)用程序在調(diào)用函數(shù)時所創(chuàng)建包主體CREATE[ORREPLACE]PACKAGEBODY{IS|[私有變量、常量[私有變量、常量[私有子程序和定義[私有子程序和定義]…]PL/SQLEND 1:demo_pack,DeptRec、兩個函數(shù)和一個過程。CREATEORREPLACEPACKAGEdemo_packDeptRecdept%ROWTYPE;FUNCTIONadd_dept(dept_noNUMBER,dept_nameVARCHAR2,locationVARCHAR2)RETURNNUMBER;FUNCTIONremove_dept(dept_noNUMBER)RETURNNUMBER;PROCEDUREquery_dept(dept_noINNUMBER);ENDdemo_pack;CREATEORREPLACEPACKAGEBODYdemo_packFUNCTION(dept_noNUMBER,dept_nameVARCHAR2,locationVARCHAR2)RETURNNUMBER/*-1是唯一約束條件的錯誤代碼*/INSERTINTOdeptVALUES(dept_no,dept_name,location);IFSQL%FOUNDTHENRETURN1;ENDIF;WHENempno_remainingTHENRETURN0;WHENOTHERSTHENRETURN-1;ENDFUNCTIONremove_dept(dept_noNUMBER)RETURNNUMBERDELETEFROMdeptWHEREdeptno=dept_no;IFSQL%FOUNDTHENRETURN1;RETURN0;ENDIF;WHENOTHERSTHENRETURN-1;ENDPROCEDUREquery_dept(dept_noINNUMBER)SELECT*INTODeptRecFROMdeptWHEREdeptno=dept_no;WHENTOO_MANY_ROWSTHENWHENOTHERSTHEN ENDEND的記錄變量DeptRec顯示所查詢到的數(shù)據(jù)庫信息：VarNUMBER;Var:=demo_pack.add_dept(90,’Administration’,‘Beijing’);IFvar=-1THEN ELSIFvar=0 var:=demo_pack.remove_dept(90);IFvar=-1THEN ELSIFvar=0ENDIF;END2:CREATEORREPLACEPACKAGEemp_packageTYPEemp_table_typeISTABLEOFemp%ROWTYPEINDEXBYBINARY_INTEGER;PROCEDUREread_emp_table(p_emp_tableOUTemp_table_type);ENDemp_package;CREATEORREPLACEPACKAGEBODYemp_packagePROCEDUREread_emp_table(p_emp_tableOUTemp_table_type)IBINARY_INTEGER:=0;FORemp_recordIN(SELECT*FROMemp)LOOPP_emp_table(i):=emp_record;I:=I+1;ENDLOOP;ENDread_emp_table;ENDemp_package;E_tableemp_package.emp_table_type;FORIINe_table.FIRST..e_table.LASTLOOP ENDLOOP;3:emp_mgmt：CREATESEQUENCEempseqINCREMENTBY1ORDERNOCYCLE;CREATESEQUENCEdeptseqSTARTWITH50INCREMENTBY10ORDERCREATEORREPLACEPACKAGEemp_mgmtFUNCTIONhire(enameVARCHAR2,jobVARCHAR2,mgrNUMBER,salNUMBER,commNUMBER,deptnoNUMBER)RETURNFUNCTIONcreate_dept(dnameVARCHAR2,locVARCHAR2)RETURNNUMBER;PROCEDUREremove_emp(empnoNUMBER);PROCEDUREincrease_sal(empnoNUMBER,sal_incrNUMBER); m(empnoNUMBER,comm_incrNUMBER);ENDCREATEORREPLACEPACKAGEBODYemp_mgmttot_empsNUMBER;tot_deptsNUMBER;no_salmFUNCTIONhire(enameVARCHAR2,jobVARCHAR2,mgrNUMBER,salNUMBER,commNUMBER,deptnoNUMBER)RETURNNUMBERnew_empnoNUMBER(4);SELECTempseq.NEXTVALINTOnew_empnoFROMdual;INSERTINTOempVALUES(new_empno,ename,job,mgr,sysdate,sal,comm,deptno);WHENOTHERSTHENENDFUNCTIONcreate_dept(dnameVARCHAR2,locVARCHAR2)RETURNNUMBERISnew_deptnoNUMBER(4);SELECTdeptseq.NEXTVALINTOnew_deptnoFROMdual;INSERTINTOdeptVALUES(new_deptno,dname,loc);WHENOTHERSTHENENDPROCEDUREremove_emp(empnoNUMBER)ISNo_resultEXCEPTION;DELETEFROMempWHEREemp.empno=remove_emp.empno;IFSQL%NOTFOUNDTHENRAISEENDWHENno_resultWHENOTHERSTHENENDPROCEDUREremove_dept(deptnoNUMBER)ISNo_resultEXCEPTION;/*-2292是一致性約束的錯誤代碼*/DELETEFROMdeptWHEREdept.deptno=remove_dept.deptno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;WHENno_resultWHENe_deptno_remainingTHENWHENOTHERSTHENENDPROCEDUREincrease_sal(empnoNUMBER,sal_incrNUMBER)IScurr_salNUMBER(7,2);SELECTsalINTOcurr_salFROMempWHEREemp.empno=increase_sal.empno;IFcurr_salISNULLTHENRAISEno_sal;UPDATEempSETsal=sal+increase_sal.sal_incrWHEREemp.empno=increase_sal.empno;ENDWHENno_salTHENWHENOTHERSTHENEND m(empnoNUMBER,comm_incrNUMBER)ISmNUMBER(7,2);SELECTcomm FROMempWHEREemp. mISNULLTHEN UPDATEemp WHERE END mTHENWHENOTHERSTHEN ENDTYPEDeptCurTypeISREFCURSORRETURNdept%ROWTYPE強(qiáng)類型定義TYPECurTypeISREFCURSOR;--弱類型定義PROCEDUREOpenDeptVar(CvINOUTDeptCurType,ChoiceINTEGERDEFAULT0,Dept_noNUMBERDEFAULT50,PROCEDUREOpenDeptvar(CvINOUTDeptCurType,Dept_noNUMBERDEFAULT50,IFchoice=1OPENcvFORSELECT*FROMdeptWHEREdeptno<=dept_no;ELSIFchoice=2THENOPENcvFORSELECT*FROMdeptWHEREdnameLIKEdept_name;OPENcvFORSELECT*FROMdept;ENDIF;ENDOpenDeptvar;ENDCurVarPack;CREATEORREPLACEPROCEDURECvINOUTCurVarPack.CurType,TabCHAR)--由于CurVarPack.CurTypeIFtab=‘D’THENOPENcvFORSELECT*FROMdept;OPENcvFORSELECT*FROMemp;ENDIF;ENDDeptRecDept%ROWTYPE;EmpRecEmp%ROWTYPE;Cv1Curvarpack.deptcurtype;Cv2Curvarpack.curtype;Curvarpack.OpenDeptVar(cv1,1,30);FETCHcv1INTODeptRec;WHILEcv1%FOUNDLOOPFETCHcv1INTODeptRec;ENDLOOP;CLOSECurVarPack.OpenDeptvar(cv2,2,dept_name=>‘A%’);FETCHcv2INTODeptRec;WHILEcv2%FOUNDFETCHcv2INTODeptRec;ENDOpenCurtype(cv2,‘D’);FETCHcv2INTODeptRec;WHILEcv2%FOUNDLOOPFETCHcv2INTOdeptrec;ENDOpenCurtype(cv2,‘E’);FETCHcv2INTOEmpRec;WHILEcv2%FOUNDLOOPFETCHcv2INTOemprec;ENDLOOP;CLOSE PL/SQL允許對包內(nèi)子程序和本地子程序進(jìn)行重載。所謂重載時指兩個或多個子程序有相CREATEORREPLACEPACKAGEdemo_pack1DeptRecdept%ROWTYPE;V_sqlcodeNUMBER;FUNCTIONquery_dept(dept_noINNUMBER)RETURNINTEGER;FUNCTIONquery_dept(dept_noINVARCHAR2)RETURNINTEGER;ENDCREATEORREPLACEPACKAGEBODYFUNCTIONcheck_dept(dept_noNUMBER)RETURNINTEGERFlagSELECTCOUNT(*)INTOflagFROMdeptWHEREdeptno=dept_no;IFflag>0THENRETURN1;RETURN0;ENDIF;ENDFUNCTIONcheck_dept(dept_noVARCHAR2)RETURNINTEGERFlagSELECTCOUNT(*)INTOflagFROMdeptWHEREdeptno=dept_no;IFflag>0THENRETURN1;RETURN0;ENDIF;ENDFUNCTIONquery_dept(dept_noINNUMBER)RETURNINTEGERIFcheck_dept(dept_no)=1SELECT*INTODeptRecFROMdeptWHEREdeptno=dept_no;RETURN1;RETURN0;ENDIF;ENDFUNCTIONquery_dept(dept_noINVARCHAR2)RETURNINTEGERIFcheck_dept(dept_no)=1SELECT*INTODeptRecFROMdeptWHEREdeptno=dept_no;RETURN1;RETURN0;ENDIF;ENDquery_dept;ENDdemo_pack1; DROPPACKAGEDROPPACKAGE[BODY][user.]package_name;DROPPACKAGEdemo_pack;DROPPACKAGEdemo_pack1;DROPPACKAGEemp_mgmt;DROPPACKAGEemp_package; 第八章觸發(fā)函數(shù)，都有，執(zhí)行和異常處理過程的PL/SQL塊。 ，觸發(fā)器在數(shù)據(jù)庫里以獨(dú)立的對象它與過程不同的是過程通過其它程序（firingORACLEINSERT、UPDATEDELETE操作或?qū)σ晥D進(jìn)行類似的操作。ORACLEORACLE，如數(shù)據(jù)庫的啟動與關(guān)，§8.1.1DMLORACLEDML語句進(jìn)行觸發(fā)，可以在DML操作前或操作后進(jìn)行觸發(fā)，并且可§8.1.2§8.1.3觸發(fā)器組成觸發(fā)時間TRIGGER是在觸發(fā)事件發(fā)生之前（BEFORE）還是之后(AFTER)觸TRIGGER的操作順序。觸發(fā)器本身TRIGGER例如：PL/SQL塊。 創(chuàng)建觸發(fā)器的一般語法是CREATE[ORREPLACE]TRIGGER{BEFORE|AFTER{INSERT|DELETE|UPDATE[OFcolumn[,columnON[schema.]table_name[FOREACHROW][WHENcondition]BEFORE和AFTER觸發(fā)器的觸發(fā)時序分別為前觸發(fā)和后觸發(fā)方式，前觸發(fā)是在FOREACHROW行觸發(fā)器和語句觸發(fā)器的區(qū)別表現(xiàn)在：DML語句操做影響數(shù)據(jù)庫中的多行數(shù)據(jù)時，對于其中的每個數(shù)據(jù)行，件，當(dāng)它符合約束條件時，激活一次觸發(fā)器。FOREACHROW選項(xiàng)時，BEFOREAFTER觸發(fā)器為語句觸發(fā)器INSTEADOF觸發(fā)器則為行觸發(fā)器。WHEN子句說明觸發(fā)約束條件。Condition為一個邏輯表達(dá)時，其中必須包含相關(guān)名PL/SQL函數(shù)。WHEN子句指定的觸發(fā)約束條件只BEFOREAFTERINSTEADOF行觸發(fā)器和其它類型的當(dāng)一個基表被修改(INSERT,UPDATE,DELETE)時要執(zhí)行的過程，執(zhí)行時根據(jù)其12種類型的觸發(fā)器，它們是:BEFOREINSERTBEFOREINSERTFOREACHROWAFTERINSERTAFTERINSERTFOREACHBEFOREBEFOREUPDATEFOREACHROWAFTERUPDATEAFTERUPDATEFOREACHBEFOREBEFOREDELETEFOREACHROWAFTERDELETEAFTERDELETEFOREACH§8.2.1BEFORE語句級觸發(fā)器BEFORE行級DMLAFTER行級AFTER語句級§8.2.2DMLSELECTSELECT…INTO…結(jié)構(gòu)，或者為定義游標(biāo)所使SELECT語句。COMMITROLLBACKSVAEPOINT問題當(dāng)觸發(fā)器被觸發(fā)時要使用入更新或刪除的記錄中的列值有時要使用操作前、實(shí)現(xiàn) 修飾符操作完成后列的 修飾符操作完成前列的1:建立一個觸發(fā)器,emp表被刪除一條記錄時，把被刪除記錄寫到職工表刪§