32orcale存儲(chǔ)過(guò)程jdbc與大操作等-plsql

第一 PL/SQL程序設(shè)計(jì)簡(jiǎn)PL/SQL是一種高級(jí)數(shù)據(jù)庫(kù)程序設(shè)計(jì)語(yǔ)言，該語(yǔ)言專(zhuān)門(mén)用于在各種環(huán)境下對(duì)ORACLE數(shù)據(jù)庫(kù)過(guò)函包 SQL PL/SQL是ProcedureLanguage&StructuredQueryLanguage的縮寫(xiě)。ORACLE的SQLANSI(AmericannationalStandardsInstitute)和ISO92(InternationalStandardsOrganization)標(biāo)準(zhǔn)的產(chǎn)品。PL/SQL是對(duì)SQL語(yǔ)言過(guò)程語(yǔ)言的擴(kuò)展ORACLE6以后，ORACLERDBMSPL/SQL。它現(xiàn)在已經(jīng)成為一種PL/SQL另外還增加了用于支持工具（ORACLEForms）的句法，如：PL/SQL內(nèi)容。 PL/SQL 有利于客戶(hù)/對(duì)于客戶(hù)服務(wù)器環(huán)境來(lái)說(shuō)，真正的瓶頸是網(wǎng)絡(luò)上。無(wú)論網(wǎng)絡(luò)多快，只要客戶(hù)端與服務(wù)L將這種具有大量數(shù)據(jù)處理的應(yīng)用放在服務(wù)器端來(lái)執(zhí)行。自然就省去了數(shù)據(jù)在網(wǎng)上的傳輸時(shí)間。 PL/SQLPL/SQLPL/SQL。對(duì)于客戶(hù)端來(lái)說(shuō)，PL/SQL可PL/SQL程序運(yùn)行。 PL/SQLSQL成PL/SQLSQL語(yǔ)句有：提示：在PL/SQL中只能用SQL語(yǔ)句中的DML部分，不能用DDLPL/SQL中使用DDL(如 等)的話(huà)，只能以動(dòng)態(tài)的方式來(lái)使用ORACLE的PL/SQL組件在對(duì)PL/SQL程序進(jìn)行解釋時(shí)，同時(shí)對(duì)在其所使用的PL/SQLSQL*PLUSPL/SQLPL/SQLORACLE其它開(kāi)發(fā)工具也可以調(diào)用PL/SQL編寫(xiě)的過(guò)函數(shù)，如PowerBuilder等都可以PL/SQL過(guò)程。 PL/SQLPL/SQL程序的運(yùn)行是通過(guò)ORACLE中的一個(gè)引擎來(lái)進(jìn)行的。這個(gè)引擎可能在ORACLEORACLEPL/SQL中的過(guò)SQL語(yǔ)句發(fā)送給數(shù)據(jù)庫(kù)服務(wù)器來(lái)執(zhí)行。再將結(jié)果返回給執(zhí)行端。第二 PL/SQL塊結(jié)構(gòu)和組成元 PL/SQLPL/SQL程序由三個(gè)塊組成，即部分、執(zhí)行部分、異常處理部分PL/SQL/*部分:在此PL/SQL用到的變量,類(lèi)型及游標(biāo)，以及局部的過(guò)函數(shù) 執(zhí)行部分 過(guò)程及SQL語(yǔ)句,即程序的主要部分/*執(zhí)行異常部分:錯(cuò)誤處理PL/SQL：子程序在數(shù)據(jù)庫(kù)中的過(guò)程、函數(shù)及包等。當(dāng)在數(shù)據(jù)庫(kù)上建立好后可以在其它： PL/SQL子塊可以位于PL/SQL中的 標(biāo)識(shí)符名過(guò)30字符SQL提示 例如：下面的例子將會(huì)刪除所有的，而不是KING的記錄Enamevarchar2(20):=’KING’;DELETEFROMempWHEREename=ename;PL/SQL中有特別的講究，建議在系統(tǒng)的設(shè)計(jì)階段就要求所有編程人員共同遵守一定的要求，使得整個(gè)系統(tǒng)的文檔在規(guī)范上達(dá)到要求。下面是建議名方法：表SQL*Plus PL/SQLPL/SQL中的變量類(lèi)型的合法使用列表： 類(lèi)子 ORACLE限帶符號(hào)整數(shù),為整數(shù)計(jì)算小數(shù)NUMBER的子類(lèi)型整數(shù)NUMBERSmall整數(shù)NUMBER的子類(lèi)型與NUMBER等價(jià)與NUMBER等價(jià)整數(shù),integer小32,767字1日至公元471212日 ORACLE在PL/SQL中除了提供象前面介紹的各種類(lèi)型外,還提供一種稱(chēng)為復(fù)合類(lèi)型§2.4.2.1記錄類(lèi)型是把邏輯相關(guān)的數(shù)據(jù)作為一個(gè)單元起來(lái)，稱(chēng)作PL/SQLRECORD的TYPErecord_typeISField1[NOT[:=exp1Field2[NOT[:=exp2.. ..Fieldn[NOT[:=expn])1或提示 過(guò)程的功能類(lèi)似 中System.out.println()直接將輸出結(jié)果送到標(biāo)準(zhǔn)輸出中2)在使用上述過(guò)程之前必須將SQL*PLUS的環(huán)境參數(shù)SERVEROUTPUT設(shè)置為ON,否則將看不到輸出結(jié)果: setserveroutputon可以用SELECT語(yǔ)句對(duì)記錄變量進(jìn)行賦值,只要保證記錄字段與查詢(xún)結(jié)果列表中的字§2.4.2.2使用所的數(shù)據(jù)庫(kù)列的數(shù)據(jù)類(lèi)型可以不必知道所的數(shù)據(jù)庫(kù)列的數(shù)據(jù)類(lèi)型可以實(shí)時(shí)改變。2：§2.4.3使用PL/SQL提供%ROWTYPE操作符,返回一個(gè)記錄類(lèi)型,其數(shù)據(jù)類(lèi)型和數(shù)據(jù)庫(kù)表的數(shù)所的數(shù)據(jù)庫(kù)中列的個(gè)數(shù)和數(shù)據(jù)類(lèi)型可以實(shí)時(shí)改變。3：§2.4.4PL/SQL表(嵌套表PL/SQL程序可使用嵌套表類(lèi)型創(chuàng)建具有一個(gè)或多個(gè)列和無(wú)限行的變量,這很像數(shù)據(jù)庫(kù)中的表.嵌套表類(lèi)型的一般語(yǔ)法如下: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說(shuō)明:1)在使用嵌套表之前必須先使用該集合的構(gòu)造器初始化它.PL/SQL自動(dòng)提供一個(gè)帶有2)嵌套表可以有任意數(shù)量的行.表的大小在必要時(shí)可動(dòng)態(tài)地增加或減少:extend(x)方法添加x個(gè)空元素到集合末尾; trim(x)方法為去掉集合末尾的x個(gè)元素. 運(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)換為T(mén)O_NUMBER :=TO_NUMBER(‘100.0’)+NUMBERTO_CHARmTO_CHAR(‘123.45||元TO_DATEv_date:=使用 當(dāng)一個(gè)變量超出了作用范圍，PL/SQL引擎就釋放用來(lái)存放該變量的空間（因?yàn)樗?使‘-‘(減號(hào))加注PL/SQL– NUMBER(12,2);--使用 /*文件名： 提示：被解釋存放在數(shù)據(jù)庫(kù)中的PL/SQL程序，一般系統(tǒng)自動(dòng)將程序頭部的注釋去掉。只 11：/*本例子僅是一個(gè)簡(jiǎn)單的插入，不是實(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：/*本例子僅是一個(gè)簡(jiǎn)單的刪除例子，不是實(shí)際應(yīng)用。*/ number(4):=8888;DELETEFROMempWHEREempno=v_empno;第三章PL/SQL流程控制語(yǔ)PL/SQL的流程控制語(yǔ)句,包括如下三類(lèi)控制語(yǔ)句IF循環(huán)語(yǔ)句LOOP語(yǔ)句EXIT順序語(yǔ)句GOTO語(yǔ)句N(xiāo)ULL IF<布爾表達(dá)式PL/SQLSQLENDIF<布爾表達(dá)式>THENPL/SQLSQL語(yǔ)句ENDIF<布爾表達(dá)式>THENPL/SQLSQL語(yǔ)句ELSIF<其它布爾表達(dá)式ELSIF<其它布爾表達(dá)式END提示ELSIF不能寫(xiě)成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<條件語(yǔ)句 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 .. .. >>括起來(lái)的標(biāo)識(shí)符V_counterNUMBER:=1;V_counter:=v_counter+1;IFv_counter>10GOTOENDENDEND; NULLPL/SQLnull語(yǔ)句來(lái)說(shuō)明“不用做任何事情”...…IFv_numISNULLGOTOprint1;ENDIF;… --第四章游標(biāo)的PL/SQL§4.1SQL語(yǔ)句，ORACLE必須分配一片叫上下文contextarea的區(qū)域來(lái)處理及查詢(xún)的活動(dòng)集(activeset)。游標(biāo)是一個(gè)指向上下文的句柄(handle)或指針。通過(guò)游標(biāo)，PL/SQL可以控制上下文SQL§4.1.1PL/SQL步驟定義游標(biāo)：就是定義一個(gè)游標(biāo)名，以及與其相對(duì)應(yīng)的SELECT語(yǔ)句。CURSORcursor_name[(parameter[,parameter]…)]ISparameter_name[IN]datatype[{:=|DEFAULT}在指定數(shù)據(jù)類(lèi)型時(shí)，不能使用長(zhǎng)度約束NUMBER(4)、CHAR(10)打開(kāi)游標(biāo)：SELECT語(yǔ)句，將其查詢(xún)結(jié)果放入工作區(qū)，并且指項(xiàng)，OPEN語(yǔ)句還將鎖定數(shù)據(jù)庫(kù)表中游標(biāo)結(jié)果集合對(duì)應(yīng)的數(shù)據(jù)行。OPENcursor_name[([parameter=>]value[,[parameter=>]法。PL/SQLOPEN語(yǔ)句重復(fù)打開(kāi)一個(gè)游標(biāo)。FETCHcursor_nameINTO{variable_list|record_variableFETCH語(yǔ)句取其中數(shù)據(jù)。OPEN語(yǔ)句重新打開(kāi)。CLOSEINTO子句。1.10名員工的信息。2.或 3：3000的員工工資調(diào)為3000。PL/SQL語(yǔ)言提供了FOROPEN、FETCH、CLOSEFORindex_variableINcursor_name[value[,value]…]--ENDindex_variable為游標(biāo)FOR循環(huán)語(yǔ)句隱含的索引變量，該變量為記錄變量，其結(jié)構(gòu)與游標(biāo)查詢(xún)語(yǔ)句返回的結(jié)構(gòu)集合的結(jié)構(gòu)相同。在程序中可以通過(guò)該索引記錄變量元素來(lái)所提取的游標(biāo)數(shù)據(jù)，index_variable中各元素的名稱(chēng)與游標(biāo)查詢(xún)語(yǔ)句選擇列表中所別名后才能通過(guò)游標(biāo)FOR循環(huán)語(yǔ)句中的索引變量來(lái)這些列數(shù)據(jù)。FOR例5：當(dāng)所的游標(biāo)帶有參數(shù)時(shí)，通過(guò)游標(biāo)FOR循環(huán)語(yǔ)句為游標(biāo)傳遞參數(shù)或6：PL/SQL還允許在游標(biāo)FOR§4.1.2、其工作區(qū)，這些由系統(tǒng)隱含創(chuàng)建的游標(biāo)稱(chēng)為隱式游標(biāo)，隱式游標(biāo)的名字為SQL，這是由ORACLEORACLE作。在隱式游標(biāo)的工作區(qū)中，所存放的數(shù)據(jù)是與用戶(hù)自定義的顯示游標(biāo)無(wú)關(guān)的處理的SQL語(yǔ)句所包含的數(shù)據(jù)。、 布爾型屬性,與%FOUND相反；SQL 數(shù)字型屬性,返回已從游標(biāo)中得記錄數(shù)SQLISOPEN 布爾型屬性,取值總是FALSESQL命令執(zhí)行完畢立即關(guān)閉隱式游標(biāo)。7:更新指定員工信息，如果該員工沒(méi)有找到，則打印”查無(wú)此人”信息?！?.1.3NO_DATA_FOUND%NOTFOUNDSELECTINTO當(dāng)一個(gè)顯式游標(biāo)的WHERE子句未找到時(shí)觸發(fā)UPDATEDELETE語(yǔ)句的WHERESQL%NOTFOUND；在提取循環(huán)中要用%NOTFOUND%FOUND§4.1.4為了對(duì)正在處理(查詢(xún))的行不被另外的用戶(hù)改動(dòng)，ORACLEFORUPDATESELECT...FROM…FORUPDATE[OFcolumn[,column]…]SELECTFORUPDATE操作一直等OPEN立即返回并給出：ORA-0054：resourcebusyandacquirewithnowait如果使用FORUPDATE游標(biāo)，則可在DELETE和UPDATE語(yǔ)句中使用WHERECURRENTOFcursor_name子句，修改或刪除游標(biāo)結(jié)果集合當(dāng)前行對(duì)應(yīng)的數(shù)據(jù)第五 異常錯(cuò)誤處提供異常情況(EXCEPTION)和異常處理(EXCEPTIONHANDLER)來(lái)實(shí)現(xiàn)錯(cuò)誤處理?！?.1異常情況處理(CTI)是用來(lái)處理正常執(zhí)行過(guò)程中未預(yù)料的事件程序塊的異常處理預(yù)定義的錯(cuò)誤和自定義錯(cuò)誤由于L/L程序塊一旦產(chǎn)生異常而沒(méi)有如何處理程序就會(huì)自動(dòng)終止整個(gè)程序運(yùn)行.PredefinedORACLE24由ORACLE自動(dòng)將其。PredefinedORACLE錯(cuò)誤。對(duì)這種異常情況的處理，需要用戶(hù)在程序中定義，然后由ORACLE自動(dòng)將其。用戶(hù)定義(User_define)PL/SQL程序體的后半部,結(jié)構(gòu)為WHENfirst_exceptionTHEN <codetohandlefirstexception>WHENsecond_exceptionTHEN <codetohandlesecondexception>WHENOTHERSTHEN <codetohandleothersexception>異常處理可以按任意次序排列,OTHERS必須放在最后§5.1.1ORACLESELECTINTOSELECTINTO內(nèi)存不夠的內(nèi)部錯(cuò)PL/SQLnull試圖將Exists以外的集合(collection)nullpl/sql表上或varraySubscript--對(duì)嵌套或varray索引得大于集合中元素個(gè)數(shù)對(duì)這種異常情況的處理只需在PL/SQL塊的異常處理部分直接相應(yīng)的異常情況例1：更新指定員工工資，如工資小于300，則加100；對(duì)NO_DATA_FOUND異常,TOO_MANY_ROWS進(jìn)行處理.§5.1.2ORACLEPL/SQL塊的定義部分定義異常情況 ORACLE>,<PL/SQL塊的異常情況處理部分對(duì)異常情況做出相應(yīng)的處理。2：刪除指定部門(mén)的記錄信息，以確保該部門(mén)沒(méi)有員工?！?.1.3是通過(guò)顯式使用RAISE語(yǔ)句來(lái)觸發(fā)當(dāng)一個(gè)異常錯(cuò)誤時(shí)控制就轉(zhuǎn)向到EXCEPTIONPL/SQL塊的定義部分定義異常情況 RAISE異常情況PL/SQL塊的異常情況處理部分對(duì)異常情況做出相應(yīng)的處理: PL/SQLSQLCODESQLCODESQLERRM返回錯(cuò)誤信息如 ?SQLERRM=’no_data_found ?SQLERRM=’normal,successfual5.ORACLE ORACLE錯(cuò)誤信息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ù)和過(guò)1．ORACLE提供可以把PL/SQL程序在數(shù)據(jù)庫(kù)中，并可以在任何地方來(lái)運(yùn)行它。這樣就叫過(guò)程或函數(shù)。過(guò)函數(shù)統(tǒng)稱(chēng)為PL/SQL子程序，他們是被命名 CREATE[ORREPLACE]FUNCTIONfunction_name[(argment[{IN|INOUT}]Type,argment[{IN|OUT|INOUT}]Type][AUTHIDDEFINER|CURRENT_USERRETURN{IS|AS說(shuō)明ORREPLACE為可選.有了它,可以或者創(chuàng)建一個(gè)新函數(shù)或者替換相同名字的函數(shù),函數(shù)名后面是一個(gè)可選的參數(shù)列表,IN,OUTINOUT標(biāo)記.參數(shù)之間用逗號(hào)隔開(kāi).IN參數(shù)標(biāo)記表示傳遞給函數(shù)的值在該函數(shù)執(zhí)行中不改變;OUT標(biāo)記表示一個(gè)值在函數(shù)中進(jìn)行計(jì)算并通過(guò)該參數(shù)傳遞給調(diào)用語(yǔ)句;INOUT標(biāo)記表示傳遞給函數(shù)的值可以變化并傳遞給調(diào)用語(yǔ)句.若省略標(biāo)記,則參數(shù)隱含為IN。因?yàn)楹瘮?shù)需要返回一個(gè)值,所以RETURN包含返回結(jié)果的數(shù)據(jù)類(lèi)型. 例 函數(shù)時(shí)所定義的參數(shù)稱(chēng)為形式參數(shù)，應(yīng)用程序調(diào)用時(shí)為函數(shù)傳遞的參數(shù)稱(chēng)為實(shí)際參數(shù)。argument=>parameter在CREATEORREPLACEFUNCTION語(yǔ)句中函數(shù)參數(shù)時(shí)可以使用DEFAULT關(guān) 過(guò) 在ORACLESERVER上建立過(guò)程,可以被多個(gè)應(yīng)用程序調(diào)用,可以向過(guò)程傳遞參數(shù),也可以向過(guò)程傳回參數(shù).創(chuàng)建過(guò)程語(yǔ)法CREATE[ORREPLACE]PROCEDUREProcedure_name[(argment[{IN|INOUT}]Type,argment[{IN|OUT|INOUT}]Type][AUTHIDDEFINER|CURRENT_USER{IS|AS 調(diào)用過(guò)ORACLE使用EXECUTE語(yǔ)句來(lái)實(shí)現(xiàn)對(duì)過(guò)程的調(diào)用 調(diào)用方法調(diào)用方法 在創(chuàng)建過(guò)程時(shí),可使用AUTHIDCURRENT_USER或AUTHIDDEFINER選項(xiàng)Oracle使用的權(quán)限AUTHIDCURRENT_USER選項(xiàng)創(chuàng)建一個(gè)過(guò)程,Oracle用調(diào)用該過(guò)程的用戶(hù)權(quán)限執(zhí)行該過(guò)程.為了成功執(zhí)行該過(guò)程,調(diào)用者必須具有該過(guò)程體中的所有數(shù)據(jù)庫(kù)對(duì)象所必須的權(quán)限如果用默認(rèn)的AUTHIDDEFINER選項(xiàng)創(chuàng)建過(guò)程,則Oracle使用過(guò)程所有者的執(zhí)行該過(guò)程.為了成功執(zhí)行該過(guò)程,過(guò)程的所有者必須具有該存儲(chǔ)過(guò)程體中的所有數(shù)據(jù)庫(kù)對(duì)象所必須的權(quán)限.想要簡(jiǎn)化應(yīng)用程序用戶(hù)的特權(quán)管理,在創(chuàng)建過(guò)程時(shí),一般選擇AUTHIDDEFINER選項(xiàng)–--這樣就不必給需要調(diào)用的此過(guò)程的所有用戶(hù)了. 開(kāi)發(fā)過(guò)程步 ， 在SQLPLUS或用調(diào)試工具將過(guò)程程序進(jìn)行解釋在SQLPLUS或用調(diào)試工具將過(guò)程程序進(jìn)行解釋?zhuān)籗QL>STARTGETORACLE SQLPLUS下來(lái)調(diào)式主要用的方法是：SHOWERROR命令來(lái)提示源碼的錯(cuò)誤位置使用user_errors數(shù)據(jù)字典來(lái)查看各過(guò)程的錯(cuò)誤位置 執(zhí)行權(quán)給相關(guān)的用戶(hù)或角如果調(diào)式正確的過(guò)程沒(méi)有進(jìn)行那就只有建立者本人才可以運(yùn)行所以作為應(yīng)用系統(tǒng)的一部分的過(guò)程也必須進(jìn)行才能達(dá)到要求在SQL*PLUS下可以用GRANTEXECUTEONdbms_jobTOPUBLICWITHGRANT 相關(guān)的權(quán)限CREATEANYPROCEDUREDROPANYPROCEDURESQL*PLUSDESCRIBE命令查看過(guò)程的名字及其參數(shù)表DESCRIBE 刪除過(guò)函DROPPROCEDURE可以使用DROPFUNCTIONDROPFUNCTION第七章包的創(chuàng)建和應(yīng) PL/SQL程序設(shè)計(jì)元素的封裝。C++和允許的程序范圍不同，即它們的作用域不同。公用元素不僅可以被包中的函數(shù)、過(guò)程所調(diào)用，也可以被包外的PL/SQL程序，而私有元素只能被包內(nèi)的函數(shù)和過(guò)程序所。I/O操作，從而使程序執(zhí)行效率得到提高。一個(gè)包由兩個(gè)分開(kāi)的部分組成（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)說(shuō)明應(yīng)用程序在調(diào)用函數(shù)時(shí)所創(chuàng)建包主體CREATE[ORREPLACE]PACKAGEBODY{IS|[私有變量、常量[私有變量、常量[私有子程序和定義[私有子程序和定義]…]PL/SQLEND 1:demo_pack,DeptRec、兩個(gè)函數(shù)和一個(gè)過(guò)程。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是唯一約束條件的錯(cuò)誤代碼*/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顯示所查詢(xún)到的數(shù)據(jù)庫(kù)信息：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是一致性約束的錯(cuò)誤代碼*/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)類(lèi)型定義TYPECurTypeISREFCURSOR;--弱類(lèi)型定義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允許對(duì)包內(nèi)子程序和本地子程序進(jìn)行重載。所謂重載時(shí)指兩個(gè)或多個(gè)子程序有相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í)行和異常處理過(guò)程的PL/SQL塊。 ，觸發(fā)器在數(shù)據(jù)庫(kù)里以獨(dú)立的對(duì)象它與過(guò)程不同的是過(guò)程通過(guò)其它程序（firingORACLEINSERT、UPDATEDELETE操作或?qū)σ晥D進(jìn)行類(lèi)似的操作。ORACLEORACLE，如數(shù)據(jù)庫(kù)的啟動(dòng)與關(guān)，§8.1.1DMLORACLEDML語(yǔ)句進(jìn)行觸發(fā)，可以在DML操作前或操作后進(jìn)行觸發(fā)，并且可§8.1.2§8.1.3觸發(fā)器組成觸發(fā)時(shí)間TRIGGER是在觸發(fā)事件發(fā)生之前（BEFORE）還是之后(AFTER)觸TRIGGER的操作順序。觸發(fā)器本身TRIGGER例如：PL/SQL塊。 創(chuàng)建觸發(fā)器的一般語(yǔ)法是CREATE[ORREPLACE]TRIGGER{BEFORE|AFTER{INSERT|DELETE|UPDATE[OFcolumn[,columnON[schema.]table_name[FOREACHROW][WHENcondition]BEFORE和AFTER觸發(fā)器的觸發(fā)時(shí)序分別為前觸發(fā)和后觸發(fā)方式，前觸發(fā)是在FOREACHROW行觸發(fā)器和語(yǔ)句觸發(fā)器的區(qū)別表現(xiàn)在：DML語(yǔ)句操做影響數(shù)據(jù)庫(kù)中的多行數(shù)據(jù)時(shí)，對(duì)于其中的每個(gè)數(shù)據(jù)行，件，當(dāng)它符合約束條件時(shí)，激活一次觸發(fā)器。FOREACHROW選項(xiàng)時(shí)，BEFOREAFTER觸發(fā)器為語(yǔ)句觸發(fā)器INSTEADOF觸發(fā)器則為行觸發(fā)器。WHEN子句說(shuō)明觸發(fā)約束條件。Condition為一個(gè)邏輯表達(dá)時(shí)，其中必須包含相關(guān)名PL/SQL函數(shù)。WHEN子句指定的觸發(fā)約束條件只BEFOREAFTERINSTEADOF行觸發(fā)器和其它類(lèi)型的當(dāng)一個(gè)基表被修改(INSERT,UPDATE,DELETE)時(shí)要執(zhí)行的過(guò)程，執(zhí)行時(shí)根據(jù)其12種類(lèi)型的觸發(fā)器，它們是:BEFOREINSERTBEFOREINSERTFOREACHROWAFTERINSERTAFTERINSERTFOREACHBEFOREBEFOREUPDATEFOREACHROWAFTERUPDATEAFTERUPDATEFOREACHBEFOREBEFOREDELETEFOREACHROWAFTERDELETEAFTERDELETEFOREACH§8.2.1BEFORE語(yǔ)句級(jí)觸發(fā)器BEFORE行級(jí)DMLAFTER行級(jí)AFTER語(yǔ)句級(jí)§8.2.2DMLSELECTSELECT…INTO…結(jié)構(gòu)，或者為定義游標(biāo)所使SELECT語(yǔ)句。COMMITROLLBACKSVAEPOINT問(wèn)題當(dāng)觸發(fā)器被觸發(fā)時(shí)要使用入更新或刪除的記錄中的列值有時(shí)要使用操作前、實(shí)現(xiàn) 修飾符操作完成后列的 修飾符操作完成前列的1:建立一個(gè)觸發(fā)器,emp表被刪除一條記錄時(shí)，把被刪除記錄寫(xiě)到職工表刪§