培訓(xùn)plsqloracle在基礎(chǔ)上進(jìn)行擴(kuò)展而成一種過程語言

Oracle_PLSQL培訓(xùn)Oracle_PLSQL培訓(xùn)漢得信息技術(shù)有限公HANDEnterpriseSolutionsCompany1漢得公技術(shù)有限公司版權(quán)所PLSQL是Oracle公司在SQL基礎(chǔ)上進(jìn)行擴(kuò)展而成的一種過程語言。PLSQL提供了典型的高級語言特性，包括封裝，例外處理機(jī)制，信息隱藏，面向?qū)ο蟮?；并把最新的編程思想帶到了?shù)據(jù)庫服務(wù)器和工具集中。與C#相比PLSQL是Oracle公司在SQL基礎(chǔ)上進(jìn)行擴(kuò)展而成的一種過程語言。PLSQL提供了典型的高級語言特性，包括封裝，例外處理機(jī)制，信息隱藏，面向?qū)ο蟮?；并把最新的編程思想帶到了?shù)據(jù)庫服務(wù)器和工具集中。與C#相比，PLSQL的優(yōu)勢是：SQL語言可以直接寫到PLSQL的“塊”中或者是PLSQL的過程ava那樣先創(chuàng)建Statemet對象來執(zhí)行SQ;言，即：使用SQL來處理數(shù)據(jù)，使用控制結(jié)構(gòu)來處理業(yè)務(wù)邏輯。這使得PLSQL成為很強(qiáng)大的事務(wù)處理PL/SQLSQLstatementOraclePLSQL在數(shù)據(jù)庫服務(wù)器（在存儲過程、函數(shù)、數(shù)據(jù)庫觸發(fā)器，Package包中使用）和Oracle開（包含使用PLSQL寫的過程和函數(shù)，擴(kuò)展名為PL的文件）;SQ數(shù)據(jù)類型也可以在PLSQL中使用，結(jié)合SQPLSQ和Oracle的數(shù)據(jù)庫字典。PLSQ消除了存取數(shù)據(jù)庫的便利性與過程語言之間的障礙。漢得公司版權(quán)所有PLSQL概PLSQL的另一個顯著好處在于它可以通過減少來回交PLSQL的另一個顯著好處在于它可以通過減少來回交互減輕網(wǎng)絡(luò)流量壓力、節(jié)省時間OtherENDIF;3漢得公版權(quán)PLSQL概PLSQL是一種類PASCAL語言，每一段程序都是由組成的使用分號PLSQL是一種類PASCAL語言，每一段程序都是由組成的使用分號作為一句或者PLSQL語句的結(jié)束；塊結(jié)構(gòu)關(guān)鍵字后面跟分號；END后面需帶分號；你可以把一句SQL語句寫在一行上，但一般不建議這么做，因為代碼不夠漂亮WHENexception_name4漢得公司版權(quán)所有DECLAREVariables,cursors,user-definedBEGINSQLPL/SQLEXCEPTIONActionstoperformwhenerrorsEND;PLSQL的塊概PLSQL的塊包括三種：匿名塊、存儲過程、函數(shù)PLSQL的塊包括三種：匿名塊、存儲過程、函數(shù)5漢得公版權(quán)FUNCTIONnameRETURNPLSQL的塊概PLSQL的變量類型1、系統(tǒng)內(nèi)置的常規(guī)簡單變量類PLSQL的變量類型1、系統(tǒng)內(nèi)置的常規(guī)簡單變量類型2、用戶自定義復(fù)雜變量類型比如記錄類型3、引用類型：保存了一個指針值4、大對象類型LOB）：保存了一個指向大對象的地址SQLPLUS變PLSQL本身沒有輸入輸出功能，如果要像命令行運(yùn)行C程序那樣可以接收輸入值，那你必須依賴執(zhí)行環(huán)值傳給PLSQL塊，比iSQLPlus執(zhí)行環(huán)境或者PLSQLDeveloper的CommandWindow執(zhí)行環(huán)境中，有一種substitution變量可以用來接收輸入值；而另一種Host變量可以把運(yùn)行時的值傳出到執(zhí)行環(huán)境中。6漢得公版權(quán)PLSQL變PLSQL的變量類型舉例日期類25-JAN-布爾類BLOB類“FourscoreandPLSQL的變量類型舉例日期類25-JAN-布爾類BLOB類“Fourscoreandsevenyearsourfathersbroughtforthupon thiscontinent,anewnation,conceivedinLIBERTY,andtothepropositionthatallmenarecreatedequal.”LONG類數(shù)值類字符串類BFILE類7漢得公司版權(quán)所有PLSQL變PLSQL的變量聲明語法舉例說明1、變PLSQL的變量聲明語法舉例說明1、變量命名建議遵循通用規(guī)則，比如表示一個變量，c_name表示一個常量2、一般建議每一行聲明一個變量，這樣程序的可讀性比較好3、如果聲明了變量，但未進(jìn)行初始化，則在沒有賦值之前該變量的值為變量進(jìn)行初始化賦值一個好的編程習(xí)慣是對所有聲8漢得公司版權(quán)所有 NUMBER(2)NOTNULL:= VARCHAR2(13):='Atlanta'; CONSTANTNUMBER:=1400;identifier[CONSTANT]datatype[NOTNULL][:=|DEFAULTexpr];PLSQL變說明4、在同一個塊中，說明4、在同一個塊中，避免命名與數(shù)據(jù)庫表中的字段名相同的變量9漢得公司版權(quán)所有employee_idNUMBER(6); PLSQL變常規(guī)類型的變量聲明舉例PLSQL特有的常規(guī)類型的變量聲明舉例PLSQL特有的%TYPE屬性來聲明與XX類型一致的變量類型語法舉例漢得公司版權(quán)所有 v_balance%TYPE:=10; BINARY_INTEGER:= NUMBER(9,2):= DATE:=SYSDATE+7; CONSTANTNUMBER(3,2):=8.25; BOOLEANNOTNULL:=TRUE;PLSQL變可綁定變量也稱為HostVariable,非PLSQL變量可綁定變量是一種可綁定變量也稱為HostVariable,非PLSQL變量可綁定變量是一種在縮主環(huán)境中定義的變量，所謂縮主環(huán)境一般指示SQLPLUS執(zhí)行環(huán)境或者PLSQL 的 Window執(zhí)行環(huán)境；可綁定變量可用于在運(yùn)行時把值傳遞給創(chuàng)建語法VARIABLEreturn_codeVARIABLE大家注意，在標(biāo)準(zhǔn)的PLSQLS執(zhí)行環(huán)境中有效，可使用T語句輸出變量內(nèi)容。在PLSQL中使用這種變量時，前面加以示區(qū)分漢得公司版權(quán)所有 employee_id=178;/PRINTPLSQL變DBMS_OUTPUT.PUT_LINE()介紹在接下來的實驗中，經(jīng)常需要DBMS_OUTPUT.PUT_LINE()介紹在接下來的實驗中，經(jīng)常需要在調(diào)試程序時輸出中間變量的值，我們可使用內(nèi)置的Package中的函數(shù)上述例子中，我們使用 輸出變量v_sal的值解釋&p_annual_sal在PlsqlDeveloper的SQLwindow執(zhí)行環(huán)境中，可用于提示用戶輸入一個具體注意：在SQLPLUS中執(zhí)行DBMS_OUTPUT.PUT_LINE前，必須先執(zhí)行SETSERVEROUTPUTON，而在PLSQLDeveloper的SQLWindow中則不需要這句話漢得公司版權(quán)所有v_salNUMBER(9,2):=&p_annual_sal;v_sal:=DBMS_OUTPUT.PUT_LINE('Themonthlysalaryis'PLSQL變PLSQL中的注釋語句1、多行注釋類似于javaPLSQL中的注釋語句1、多行注釋類似于javaC使和2、單行注釋是在語句后面使–舉例漢得公版權(quán)v_salNUMBER(9,2);/*Computetheannualsalarybasedonthemonthlysalaryinputfromtheuser*/v_sal:=:g_monthly_sal* --ThisistheendofthePLSQL變SQL函數(shù)在PLSQL的過程語句中的使用SQL函數(shù)在PLSQL的過程語句中的使用哪些可以用？哪些不可以用大多數(shù)SQL函數(shù)都可以PLSQL的過程語句中使用，比如函數(shù)等但有些函數(shù)在PLSQL的過程語句中是不能使用的，比如Decode函數(shù)、分組函數(shù)（AVG,MIN,MAX,COUNT,SUM,STDDEV,andVARIANCE）等漢得公司版權(quán)所有PLSQL變塊嵌套和變量范圍PLSQL的塊是可以塊嵌套和變量范圍PLSQL的塊是可以嵌套的，變量的作用范圍與其他語言類漢得公司版權(quán)所有xBINARY_INTEGER;yNUMBER;y:=ScopeofScopeofPLSQL變假設(shè)我假設(shè)我們在塊嵌套的程序中，里層和外層有相同的變量聲明，而里層的程序要訪問外層的同名變答案是：使用塊限定詞，請看例子這個例子中 是同名變量，限定outer.birthdate這種格式 表示外層，里層要訪問外層的bithdate時使?jié)h得公司版權(quán)所有TO_DATE('03-AUG-'DD-MON-PLSQL變課堂練習(xí)12告訴我如下答案課堂練習(xí)12告訴我如下答案ThevalueofThevalueofThevalueofThevalueofThevalueofThevalueV_MESSAGEatposition1.V_TOTAL_COMPatposition2.V_COMMatposition1.outer.V_COMMatposition1.V_COMMatposition2.atposition漢得公司版權(quán)所有 NUMBER(7,2):= NUMBER(7,2):=v_sal*v_messageVARCHAR2(255):='eligibleforcommission'; NUMBER(7,2):= NUMBER(7,2):= NUMBER(7,2):=v_sal+v_message:='CLERKnot'||v_message;outer.v_comm:=v_sal*0.30;v_message:=PLSQL變PLSQL代碼美化使用Developer中功能PLSQL代碼美化使用Developer中功能操作：選中欲美化的代碼，點上面的Beautifier”按鈕即可，注意有時代碼有語法錯誤的時候該功 無效，所以最好先編譯通過再美化 漢得公司版權(quán)所有PLSQL變語句用于把從數(shù)據(jù)庫查詢出內(nèi)容存入變SETSERVEROUTPUT語句用于把從數(shù)據(jù)庫查詢出內(nèi)容存入變SETSERVEROUTPUTNUMBERNOTNULL:=SELECT--group=('Thesumsalaryis'果Where條件控制不好，導(dǎo)致多行查/漢得公司版權(quán)所有 hire_date,salary employee_id=/PLSQL中的SQL語INSERT、UPDATE、DELETE、MERGE語句：在INSERT、UPDATE、DELETE、MERGE語句：在PLSQL中執(zhí)行這些SQL語句和直接執(zhí)行這些語句差不多，漢得公版權(quán) employees.salary%TYPE:=800;UPDATE salary=salary+v_sal_increaseWHEREjob_id='ST_CLERK';/INSERTINTO(employee_id,first_name,last_name,email,hire_date,job_id,salary)(employees_seq.NEXTVAL,'Ruth','Cores','RCORES',sysdate,'AD_ASST',4000);/PLSQL中的SQL語漢得公司版權(quán)所有v_empnoemployees.employee_id%TYPE漢得公司版權(quán)所有v_empnoemployees.employee_id%TYPE:=MERGEINTOcopy_empcUSINGemployeeseON(e.employee_id=v_empno)WHENMATCHEDTHENUPDATE =e.first_name, =e.last_name, =..WHENNOTMATCHEDINSERTVALUES(e.employee_id,e.first_name,.. employees.department_id%TYPE:=10;DELETE department_id=v_deptno;/PLSQL中的SQL語和其他語言一樣，控制主要包和其他語言一樣，控制主要包括判斷和循環(huán)判斷語句的語法與其他語言類似需要注意的是對NULL的判斷處理漢得公司版權(quán)所有CASEWHENexpression1THENresult1WHENexpression2THENWHENexpressionNTHENresultN[ELSEresultN+1;]IFcondition[ELSIFconditionENDIF;PLSQL中的控制語需要注意的是對NULL的判斷處理：一般人容易犯錯誤或者不容易記需要注意的是對NULL的判斷處理：一般人容易犯錯誤或者不容易記漢得公司版權(quán)所有TRUEFALSE TRUEFALSETRUETRUETRUEFALSETRUEFALSENULL NULLPLSQL中的控制語循環(huán)語句的語法與其他語言類似循環(huán)語句的語法與其他語言類似：有基本循環(huán)、For循環(huán)、Wihle循環(huán)三漢得公司版權(quán)所有FORcounterINlower_bound..upper_bound..ENDWHILEcondition Condition evaluatedat beginning.. eachEND..EXIT[WHENcondition];ENDLOOP;PLSQL中的控制語舉例請把它改寫成標(biāo)準(zhǔn)舉例請把它改寫成標(biāo)準(zhǔn)循環(huán)、While循漢得公司版權(quán)所有 locations.country_id%TYPE:='CA'; locations.city%TYPE:='Montreal';SELECTMAX(location_id)INTOFROMWHEREcountry_id=v_country_id;FORiIN1..3LOOPINSERTINTOlocations(location_id,city,country_id)VALUES((v_location_id+i),v_city,v_country_id);/PLSQL中的控制語嵌套循環(huán)和Label嵌套循環(huán)和Label一般用不著，只有在使用goto語句，或者內(nèi)部循環(huán)需要訪問外部的同名變量的時候才需要，而一般這種做法也是不被提倡的。漢得公司版權(quán)所有v_counter:=v_counter+1;EXITWHENv_counter>10;EXITOuter_loopWHENtotal_done=--LeavebothEXITWHENinner_done=--LeaveinnerloopENDLOOPENDLOOPOuter_loop;PLSQL中的控制語概述：PLSQL中常用的自定義類型就兩種記錄類型概述：PLSQL中常用的自定義類型就兩種記錄類型、PLSQL內(nèi)存表類型（根據(jù)表中的數(shù)據(jù)字段的簡單和復(fù)雜度又可分別實現(xiàn)類似于簡單數(shù)組和記錄數(shù)組的功能記錄類型的定義語法這里的的具體格式可以是舉例漢得公版權(quán)所有TYPEemp_record_typeISRECORD field_name{field_type||table.column%TYPE|[[NOTNULL]{:=|DEFAULT}TYPEtype_nameIS(field_declaration[, PLSQL中的復(fù)雜自定義數(shù)據(jù)類%ROWTYPE屬性：在PLSQL中%ROWTYPE表示某張表的%ROWTYPE屬性：在PLSQL中%ROWTYPE表示某張表的記錄類型或者是用戶指定以的記錄類型，使用此*….的語句使用舉例漢得公司版權(quán)所有 SELECT*INTOemp_recFROMemployeesWHEREemployee_id=INSERTINTOretired_emps(empno,ename,job,mgr,leavedate,sal,comm,VALUES(emp_rec.employee_id,emp_rec.last_name,emp_rec.job_id,emp_rec.manager_id,emp_rec.hire_date,SYSDATE,emp_rec.salary,emp_mission_pct,emp_rec.department_id);/PLSQL中的復(fù)雜自定義數(shù)據(jù)類PLSQL內(nèi)存表即IndexByTable這種結(jié)構(gòu)類似于PLSQL內(nèi)存表即IndexByTable這種結(jié)構(gòu)類似于數(shù)組，使用主鍵提供類似于數(shù)組那樣的素訪問這種類型必須包括兩部分：1、使類型構(gòu)成的索引主鍵2、另外一個簡單類或者用戶自定義類型的字段作為具體的數(shù)組元素 這種類型可以自動增長，所以也類似于可變長數(shù)組語法舉例：這是一個簡單數(shù)漢得公司版權(quán)所有TYPEename_table_typeISTABLEINDEXBYename_tableTYPEtype_nameISTABLE{column_type||table.column%TYPE}[NOT|[INDEXBY PLSQL中的復(fù)雜自定義數(shù)據(jù)類PLSQL內(nèi)存表應(yīng)用舉例下面定PLSQL內(nèi)存表應(yīng)用舉例下面定義的兩個內(nèi)存表中的元素都是簡單數(shù)據(jù)類型，所以相當(dāng)于定義了兩個簡單數(shù)組備注：對PLSQ這種數(shù)據(jù)類型的值在-21474836472147483647范圍內(nèi)，所以下表也可以在這個范圍內(nèi)。漢得公司版權(quán)所有TYPEename_table_typeISTABLEOFINDEXBYBINARY_INTEGER;TYPEhiredate_table_typeISTABLEOFDATEINDEXBYBINARY_INTEGER; :='CAMERON';hiredate_table(8):=SYSDATE+7;IFename_table.EXISTS(1)THENINSERTINTO.../PLSQL中的復(fù)雜自定義數(shù)據(jù)類PLSQL內(nèi)存表應(yīng)用舉例下面PLSQL內(nèi)存表應(yīng)用舉例下面定義的兩個內(nèi)存表中的元素記錄類型，所以相當(dāng)于定義了真正的內(nèi)存表漢得公司版權(quán)所有TYPEemp_table_typeistableofemployees%ROWTYPEINDEXBYBINARY_INTEGER;my_emp_tableemp_table_type; FORiIN100..v_countSELECT*INTOmy_emp_table(i)FROMemployeesWHEREemployee_id=i;ENDFORiINmy_emp_table.FIRST..my_emp_table.LASTENDLOOP;PLSQL中的復(fù)雜自定義數(shù)據(jù)類游標(biāo)概論：游標(biāo)是一個私有的游標(biāo)概論：游標(biāo)是一個私有的SQL工作區(qū)域，Oracle數(shù)據(jù)庫中有兩種游標(biāo)，分別是隱式游標(biāo)和顯式游標(biāo)隱式游標(biāo)不易被用戶和程序員察覺和意識到，實際上OracleSQL語句；隱式游標(biāo)的幾個有用屬性舉例漢得公司版權(quán)所有VARIABLErows_deletedVARCHAR2(30)v_employee_idemployees.employee_id%TYPE:=176;DELETEFROM employee_id=:rows_deleted:=(SQL%ROWCOUNT'row/PRINT 受最近的SQL語句影響的行 最近的SQL語句是否影響了一行以上的數(shù) 最近的SQL語句是否未影響任何數(shù) 對于隱式游標(biāo)而言永遠(yuǎn)為PLSQL中的游顯式游標(biāo)：對于返回多行結(jié)果的SQL語句的返回結(jié)顯式游標(biāo)：對于返回多行結(jié)果的SQL語句的返回結(jié)果，可使用顯式游標(biāo)獨立的處理器中每一行的數(shù)據(jù)Active 顯式游標(biāo)的相關(guān)函數(shù)可以做到1、一行一行的處理返回的數(shù)據(jù)2、保持當(dāng)前處理行的一個跟蹤，像一個指針一樣指示當(dāng)前的處理的記錄3、允許程序員在PLSQL塊中人為的控制游標(biāo)的開啟、關(guān)閉、上下移動漢得公司版權(quán)所有 KochharDeHaan Patel PLSQL中的游在程序中對顯式游標(biāo)控制的一般過程?????在程序中對顯式游標(biāo)控制的一般過程?????聲明游打開游測試行的關(guān)閉游?漢得公司版權(quán)所有PLSQL中的游舉例CURSORemp_cursoremployee_id,聲明游打開游OPENFETCHemp_cursorINTO提舉例CURSORemp_cursoremployee_id,聲明游打開游OPENFETCHemp_cursorINTO提取當(dāng)前行到變EXITWHENemp_cursor%ROWCOUNT>10測試行存||''||ENDCLOSE關(guān)閉游END漢得公司版權(quán)所有PLSQL中的游如果你覺得像前面那個例子那樣對一個游標(biāo)的遍歷很麻煩的話，可以考慮使用ForFor循環(huán)省去了游標(biāo)的聲明、打開、提取、測試、關(guān)閉等語句，對程序員來說很方便，語法如下：舉例也可以如果你覺得像前面那個例子那樣對一個游標(biāo)的遍歷很麻煩的話，可以考慮使用ForFor循環(huán)省去了游標(biāo)的聲明、打開、提取、測試、關(guān)閉等語句，對程序員來說很方便，語法如下：舉例也可以漢得公司版權(quán)所有FORemp_recordIN(SELECTlast_name, employees)--implicitopenandimplicitfetchoccurIFemp_record.department_id=80THENENDLOOP;--implicitcloseoccursFORrecord_nameINcursor_name..ENDPLSQL中的游舉例思考：在什么情況下舉例思考：在什么情況下適用于使用舉例，在哪些情況下適用于舉例？漢得公司版權(quán)所有CURSORemp_cursorSELECTlast_name,department_id FORemp_recordINemp_cursor--implicitopenandimplicitfetchoccurIFemp_record.department_id=80THENENDLOOP;--implicitcloseoccurs/PLSQL中的游游標(biāo)能否帶有參數(shù)？答案是肯游標(biāo)能否帶有參數(shù)？答案是肯定的舉例漢得公司版權(quán)所有CURSOR(p_deptnoNUMBER,p_jobVARCHAR2)ISSELECTemployee_id,last_name WHEREdepartment_id=p_deptno job_id=p_job;OPENemp_cursor(80,..CLOSEOPENemp_cursor(60,..CURSOR[(parameter_namedatatype,PLSQL中的游FORET語句：有的時候我們打開一個游標(biāo)是為了更新或者刪除一些記錄，這種情況下我們希望rFORET語句：有的時候我們打開一個游標(biāo)是為了更新或者刪除一些記錄，這種情況下我們希望re舉例漢得公司版權(quán)所有CURSORemp_cursorSELECTemployee_id,last_name,department_name WHEREemployees.department_id=ANDemployees.department_id=80FORUPDATEOFsalaryNOWAIT; FORUPDATE[OFWHERECURRENTOFcursor：我們經(jīng)常WHERECURRENTOFcursor：我們經(jīng)常要逐條處理游標(biāo)中的每一條記錄，在循環(huán)體內(nèi)做Update或者時需要有Where指向游標(biāo)的當(dāng)前記錄有沒有簡單一點的的Where條件寫法呢答案是肯定的，就是。。漢得公司版權(quán)所有CURSORsal_cursorSELECTe.department_id,employee_id,last_name,salary employeese,departmentsdWHEREd.department_id=e.department_id d.department_id=60FORUPDATEOFsalaryNOWAIT;FORemp_recordINsal_cursorIFemp_record.salary<5000THENUPDATEemployeesSETsalary=emp_record.salary*1.10WHERECURRENTOFsal_cursor;ENDIF;ENDLOOP;/PLSQL中的游PLSQL中的例外一般有兩種1、 內(nèi)部錯誤拋出的例外：這又分為預(yù)定義例外（PLSQL中的例外一般有兩種1、 內(nèi)部錯誤拋出的例外：這又分為預(yù)定義例外（有錯誤號+常量定義）和非預(yù)定義例（僅有錯誤號，無常量定義2、程序員顯式的拋出的例PLSQ外層（外層BOCK或者外層調(diào)用者函數(shù)）例外捕例外傳例外被拋例外被拋例外被捕-結(jié)例外未被例外被傳遞到調(diào)用者漢得公司版權(quán)所有PLSQL中的例外處PLSQL中的例外處理的一般語PLSQL中的例外處理的一般語法漢得公司版權(quán)所有WHENexception1[ORexception2...]..[WHENexception3[ORexception4...]..[WHENOTHERS..PLSQL中的例外處處理預(yù)定義的例外：有些常見例外處理預(yù)定義的例外：有些常見例外NO_DATA_FOUND和TOO_MANY_ROWS是最常見的例外，大多數(shù)Block中都建議對這兩種例外有處理整的預(yù)定義例外的列表，請參考PL/SQLUser’sGuideandReference,“ErrorHandling.”（百度搜索，下載點很多）都已經(jīng)預(yù)定義好了，使用時無需預(yù)先聲明，比如...WHENWHENWHENOTHERSTHEN這兩例外總要考OTHERS總在最漢得公司版權(quán)所有PLSQL中的例外處OTHERS的處理Others表明我們程序員未能預(yù)計到這種錯誤，所OTHERS的處理Others表明我們程序員未能預(yù)計到這種錯誤，所以全部歸入到 里面去了，單發(fā)生這種況是，我們還是希望了解當(dāng)時發(fā)生的Oracle錯誤號和相關(guān)描述信息，怎樣才能取到呢和錯誤號和錯誤描提供了兩個內(nèi)置函分別用來返回漢得公司版權(quán)所有 WHENOTHERSTHENv_error_code:=INSERTINTOVALUES(v_error_code,SQLCODESQLERRMPLSQL中的例外處處理非預(yù)定義的Oracle錯誤：此類錯誤屬于Oracle聲明部例外處理部聲處理例使用PRAGMA把錯誤號和例外名稱聯(lián)系處理非預(yù)定義的Oracle錯誤：此類錯誤屬于Oracle聲明部例外處理部聲處理例使用PRAGMA把錯誤號和例外名稱聯(lián)系起給例外命處理拋出的漢得公司版權(quán)所有建立關(guān)PLSQL中的例外處舉例123Oracle內(nèi)部錯誤號很多，想了解全部的Ora舉例123Oracle內(nèi)部錯誤號很多，想了解全部的Ora錯誤號，請參考：http://www.ora-漢得公司版權(quán)所有DEFINEp_deptno=10DELETEFROMWHEREdepartment_id=&p_deptno; DBMS_OUTPUT.PUT_LINE('Cannotremovedept'||TO_CHAR(&p_deptno)||'.Employeesexist.e_emps_remainingPRAGMA(e_emps_remaining,-PLSQL中的例外處這種錯誤一般是程序員根據(jù)具體的業(yè)務(wù)邏輯定義的應(yīng)用類錯誤，需要先聲明后使用給例外命名使用RAISE語句顯式的拋例外處理拋出的例外漢得公司版權(quán)所有例外處理部處理例程序執(zhí)行部拋出例聲明部聲PLSQL中的例外處舉123漢得公司版權(quán)所有 department_name=&p_department_desc舉123漢得公司版權(quán)所有 department_name=&p_department_desc department_id=&p_department_number;IFSQL%NOTFOUNDTHENENDIF; DBMS_OUTPUT.PUT_LINE('NosuchdepartmentPLSQL中的例外處函數(shù)：對于用戶自定義的業(yè)務(wù)錯誤函數(shù)：對于用戶自定義的業(yè)務(wù)錯誤，如果覺得先定義再使用很麻煩，那也可以簡單的使用raise_application_error()來簡化處理。它可以無需預(yù)先定義錯誤，而在需要拋出錯誤的舉例：執(zhí)行部例外處理部分漢得公司版權(quán)所有WHENNO_DATA_FOUNDRAISE_APPLICATION_ERROR(-'ManagerisnotavalidDELETEFROMWHEREmanager_id=v_mgr;IFSQL%NOTFOUNDTHENRAISE_APPLICATION_ERROR(-'Thisisnotavalidmanager');ENDIF;PLSQL中的例外處例外傳遞：當(dāng)前塊中不處理，傳遞到外層..例外傳遞：當(dāng)前塊中不處理，傳遞到外層..PRAGMAEXCEPTION_INIT-FORc_recordINSELECTUPDATE子塊可以直接把例外處理掉，也可以不處理，把它扔到外層IFENDIF;e_integrityTHENe_no_rowsTHEN漢得公司版權(quán)所有PLSQL中的例外處語法舉例漢得公司版權(quán)所有CREATE語法舉例漢得公司版權(quán)所有CREATEORREPLACEPROCEDUREraise_salary(p_idINemployees.employee_id%TYPE)UPDATE salary=salary*1.10WHEREemployee_id=p_id;END/CREATE[ORREPLACE]PROCEDURE[(parameter1[mode1]datatype1,parameter2[mode2]datatype2,...)]PL/SQLPLSQL中的存儲過程和函使用開發(fā)存儲過程File->New-使用開發(fā)存儲過程File->New-Window-漢得公司版權(quán)所有PLSQL中的存儲過程和函PLSQL存儲過程的參數(shù)模式INparameterINOUTIN默認(rèn)模必須顯式必須顯式用PLSQL存儲過程的參數(shù)模式INparameterINOUTIN默認(rèn)模必須顯式必須顯式用以把值傳給過必須是個必須是個變量可以賦予默認(rèn)不能賦予默認(rèn)不能賦予默認(rèn)漢得公版權(quán)所有PLSQL中的存儲過程和函舉例漢得公司版權(quán)所有CREATEOR舉例漢得公司版權(quán)所有CREATEORREPLACEPROCEDUREformat_phone(p_phone_noINOUTVARCHAR2)p_phone_no:='('||SUBSTR(p_phone_no,1,3)')'||SUBSTR(p_phone_no,4,3)||'-'||SUBSTR(p_phone_no,7);END/CREATEORREPLACEPROCEDURE OUTemployees.last_name%TYPE,p_salaryOUTemployees.salary%TYPE, OUT last_name,salary,commission_pct p_name,p_salary,p_comm employee_id=p_id;ENDquery_emp;/PLSQL中的存儲過程和函參數(shù)傳遞方(按順序傳使用=>符號傳)或使用默認(rèn)按順序傳參數(shù)傳遞方(按順序傳使用=>符號傳)或使用默認(rèn)按順序傳add_dept(p_loc=>/1200)SELECTdepartment_id,FROM漢得公司版權(quán)所有add_dept('TRAINING',add_dept(p_loc=>2400,p_nameCREATEORREPLACEPROCEDURE(p_nameIN INdepartments.location_id%TYPEDEFAULTINSERTINTOdepartments(department_id,department_name,VALUES(departments_seq.NEXTVAL,p_name,p_loc);ENDadd_dept;/PLSQL中的存儲過程和函過程調(diào)用的例外處理被調(diào)用過調(diào)過程調(diào)用的例外處理被調(diào)用過調(diào)用過拋出例例外未被處漢得公司版權(quán)所有PROC1...ENDPROC2...ENDPLSQL中的存儲過程和函刪除儲存過程漢得刪除儲存過程漢得公司版權(quán)所有DROPPROCEDUREPLSQL中的存儲過程和函PLSQL存儲函數(shù)CREATEORINPLSQL存儲函數(shù)CREATEORINemployee_id=END/漢得公司版權(quán)所有CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter1[mode1]datatype1,..PL/SQLPLSQL中的存儲過程和函使用開發(fā)函數(shù)漢使用開發(fā)函數(shù)漢得公司版權(quán)所有PLSQL中的存儲過程和函哪些SQL語句中可以使用用戶自定哪些SQL語句中可以使用用戶自定義的函數(shù)Select語Where條件和Having子CONNECTBY,STARTWITH,INSERT的Values子UPDATE的Set子BY和BY子舉例漢得公司版權(quán)所有SELECTemployee_id,tax(salary) WHEREtax(salary)>(SELECTMAX(tax(salary))FROMemployeesWHEREdepartment_id=30)ORDERBYtax(salary)DESC;PLSQL中的存儲過程和函想要在SQL語句中可以使用用戶自定義的函數(shù)想要在SQL語句中可以使用用戶自定義的函數(shù)，那么這樣的用戶定義函數(shù)有哪些限制答有如下限制必須是個函數(shù)（不能是過程-只能用IN模式的參數(shù)（不能有OUT,INOUT模式的參數(shù)QPLSQL中特有的參數(shù)（比如記錄、PLSQL函數(shù)返回的數(shù)據(jù)類型也必須是有效的數(shù)據(jù)類型，而不能是PLSQL特有的數(shù)據(jù)類型在SQ中使用的函數(shù)，其函數(shù)體內(nèi)部不能有DM語句。在UPDATE/DELET在SQ（比如Commit，a漢得公司版權(quán)所有PLSQL中的存儲過程和函反面教材舉例漢得公司版權(quán)所有UPDATE反面教材舉例漢得公司版權(quán)所有UPDATEemployeesSETsalary=dml_call_sql(2000)WHEREemployee_id=170;CREATEORREPLACEFUNCTIONdml_call_sql(p_salNUMBER)RETURNNUMBERISINSERTINTOemployees(employee_id,last_name,hire_date,job_id,salary)VALUES(1,'employee1','emp1@',RETURN(p_sal+100);/PLSQL中的存儲過程和函刪除儲存函數(shù)漢得刪除儲存函數(shù)漢得公司版權(quán)所有DROPFUNCTIONPLSQL中的存儲過程和函函數(shù)過程對數(shù)據(jù)訪問的權(quán)限概念：定義者權(quán)和調(diào)用者權(quán)PERSON給Scott賦予Select權(quán)函數(shù)過程對數(shù)據(jù)訪問的權(quán)限概念：定義者權(quán)和調(diào)用者權(quán)PERSON給Scott賦予Select權(quán)限Scott給Green賦予函數(shù)執(zhí)行權(quán)限漢得公版權(quán)有權(quán)限問題嗎GRANT GRANT PLSQL中的存儲過程和函定義者權(quán)：函數(shù)執(zhí)行時，對表定義者權(quán)：函數(shù)執(zhí)行時，對表的訪問默認(rèn)使用定義者權(quán)限那么什么情況會使用調(diào)用者權(quán)限呢？這需要在寫函數(shù)的時候有特殊語句標(biāo)識大家想一下在這種情況下：Green執(zhí)行函數(shù)有沒有問題漢得公司版權(quán)所有CREATEPROCEDURE(p_idINemployees.employee_id%TYPE,p_nameOUTemployees.last_name%TYPE,p_salaryOUTemployees.salary%TYPE,p_commOUTmission_pct%TYPE)AUTHIDCURRENT_USERSELECTlast_name,salary,INTOp_name,p_salary,p_commFROMemployeesENDquery_emp;/PLSQL中的存儲過程和函課堂測試Ora1用戶創(chuàng)建測試表--(t_idnumber,t_name課堂測試Ora1用戶創(chuàng)建測試表--(t_idnumber,t_nameOra2用戶創(chuàng)建函數(shù)--CREATEorreplacefunctionreturnvarchar2v_namevarchar2(100);INTOv_nameFROMora1.testora_1WHEREt_id=p_id;returnv_name;returnNULL;END賦予系統(tǒng)權(quán)限--GRANTcreate,create,create,create,create,create,create,altersessionaltertableprimarykey(t_id);grantselectontestora_1to測試1：在Ora3用戶下執(zhí)行ora2.query_fun_test()函數(shù)；selectquery_fun_test(1)fromCURRENT_USER，然后再重復(fù)“測試grantexecuteonquery_fun_testto漢得公司版權(quán)所有PLSQL中的存儲過程和函編譯PLSQL程序：使用Developer集成編譯PLSQL程序：使用Developer集成環(huán)境編譯：方便注意在SQLWindow中執(zhí)行函數(shù)或過程的定義，即使有編譯錯誤，也不會顯示；只有在ProgramWindow中執(zhí)行函數(shù)或過程的定義的時候才會立即顯示編譯錯誤；漢得公司版權(quán)所有PLSQL中的存儲過程和函Package概念：按照業(yè)務(wù)邏Package概念：按照業(yè)務(wù)邏輯、把相關(guān)的Func,Procedure組織到一起，形成一個函數(shù)或者過程集合，這就是漢得公司版權(quán)所有PLSQL中的包Package組成：Package由包說明Specification)和包體body)兩部分構(gòu)成包說部分相當(dāng)于C語言里面的.H文件，包體部分相當(dāng)C語言里面針對.H實現(xiàn)的C文件公有變公有過私有變Procedure私Package組成：Package由包說明Specification)和包體body)兩部分構(gòu)成包說部分相當(dāng)于C語言里面的.H文件，包體部分相當(dāng)C語言里面針對.H實現(xiàn)的C文件公有變公有過私有變Procedure私有過公有過局部變漢得公版權(quán)PLSQL中的包Package常用Package好處1、模塊化：一Package常用Package好處1、模塊化：一般把有相關(guān)性的函數(shù)和過程放到一個Package中23、信息隱藏：包體中函數(shù)可以部分出現(xiàn)在包說明中，只有出現(xiàn)在包說明中的函數(shù)和過程才是該P(yáng)ackae的公有函數(shù)和過程，可以被其他包中的函數(shù)調(diào)用，否則對其他包中的函數(shù)是不可見的，未在包說明部分出現(xiàn)的函數(shù)和過程相當(dāng)于私有的。Package中其他函數(shù)被調(diào)用時，就直接從內(nèi)存讀取了，可以減少磁盤IO,從而提高性能。這個特性也提醒我們不要去搞巨無霸的把你用到的任函數(shù)都寫到一個Package中，這會導(dǎo)致嚴(yán)重的內(nèi)存浪費(fèi)5、重載：一個中可以定義同名、不同參數(shù)的函數(shù)或過程漢得公司版權(quán)所有PLSQL中的包Package中的向前聲明特性：在PackaePackage中的向前聲明特性：在Packaebody中，一個函數(shù)中調(diào)用另一個函數(shù)（也在該P(yáng)ackae中），則另一個函數(shù)必須在前面先定義；如果你非要調(diào)用在程序代碼中后定義的函數(shù)，可把這個函數(shù)設(shè)置成公有函數(shù)，在包說明部分說明；漢得公司版權(quán)所有CREATEORREPLACEPACKAGEBODYforward_packPROCEDUREaward_bonus(...)calc_rating(...); PROCEDUREcalc_rating(...)END/PLSQL中的包Package中的初始化過程代碼Package中可以Package中的初始化過程代碼Package中可以寫一段初始化過程代碼，這段代碼只是一個Session中加載時被執(zhí)行一次，一般用于一些復(fù)變量的初始化（比如某個公有變量的初始化值是需要通過一段負(fù)責(zé)的SQ來獲取的）；如果我們沒有這樣的初始化NUL;就可以了。漢得公司版權(quán)所有CREATEORREPLACEPACKAGEBODYtaxes...--declareallprivate...--definepublic/privateprocedures/functions rate_name='TAX';ENDtaxes;/CREATEORREPLACEPACKAGEtaxes ...--declareallpublicprocedures/functionsENDtaxes;/PLSQL中的包Package中的變量的持久狀態(tài)Package中的變量的持久狀態(tài)漢得公司版權(quán)所有CREATEORREPLACEPACKAGEBODYcomm_packageFUNCTIONvalidate_comm(p_commINNUMBER)RETURNBOOLEANISv_max_comm commission是否低于數(shù)據(jù)庫表中的最大ommission，是則返回TRUEENDvalidate_comm;PROCEDUREreset_comm(p_comm INNUMBER)ISBEGIN validate_comm判斷是否超出最大值，若是RAISE_APPLICATION_ERROR;否則設(shè)置g_commp_commENDreset_comm;END/CREATEORREPLACEPACKAGEcomm_packageISg_commNUMBER:=10; --初始化為10PROCEDUREreset_comm(p_comm END/PLSQL中的包時數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶思時數(shù)據(jù)庫用戶數(shù)據(jù)庫用戶思考：Package中的公共變量，不同的Session是否會相互影響漢得公司版權(quán)所有INSERTINTOemployees(last_name,commission_pct)VALUES('Madonna',0.8);--max_comm=0.8>--g_comm=--max_comm=0.4>--g_comm=Selectcomm_package.g_commfromdual; --g_comm=?--max_comm=0.4<0.6PLSQL中的包動態(tài)SQL：不是在Time寫的而是可以在運(yùn)行時動態(tài)SQL：不是在Time寫的而是可以在運(yùn)行時臨時拼接起來的SQL語句動態(tài)SQL可以使用語句來執(zhí)行內(nèi)置來執(zhí)行，也可以使用執(zhí)行例子漢得公司版權(quán)所有CREATEORREPLACEPROCEDUREdelete_all_rows(p_tab_nameINVARCHAR2,p_rows_delOUTNUMBER) cursor_name:=DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(cursor_name,'DELETEFROM'||p_tab_name,DBMS_SQL.NATIVEp_rows_del:=DBMS_SQL.EXECUTE(cursor_name);/內(nèi)置PLSQL工具執(zhí)行例子動態(tài)SQL實際應(yīng)用例子執(zhí)行例子動態(tài)SQL實際應(yīng)用例子：POS基礎(chǔ)數(shù)據(jù)同步并發(fā)程缺點漢得公司版權(quán)所有CREATEPROCEDUREdel_rows(p_table_nameINVARCHAR2, OUTEXECUTEIMMEDIATE'deletefrom'||p_table_name;p_rows_deld:=SQL%ROWCOUNT;/內(nèi)置PLSQL工具程序中執(zhí)行DDL：如果想在程序中執(zhí)程序中執(zhí)行DDL：如果想在程序中執(zhí)行DDL,可使用內(nèi)置包比如在程序中執(zhí)行編譯命令比如在程序中執(zhí)行數(shù)據(jù)收集命令漢得公司版權(quán)所有內(nèi)置PLSQL工具Oracle數(shù)據(jù)庫JOB：定義可以定期執(zhí)行某個程序應(yīng)用場景：比如每隔一周對某些表進(jìn)行數(shù)據(jù)收集，以確保Oracle數(shù)據(jù)庫JOB：定義可以定期執(zhí)行某個程序應(yīng)用場景：比如每隔一周對某些表進(jìn)行數(shù)據(jù)收集，以確保CBO正確，又比如在消息處理機(jī)制中，每隔5分鐘對消息隊列進(jìn)行掃描處理等。Oracle提供內(nèi)置DBMS_JOB，可完成JOB的定義、提交、更改、停止、移除例子：提交一個每隔1天執(zhí)行一次例子：更改JOB的執(zhí)行頻率為：每4小時執(zhí)行一DBMS_JOB.CHANGE(1,NULL,TRUNC(SYSDATE+1)+6/24,漢得公司版權(quán)所有DBMS_JOB.SUBMITjob=>jobnowhat=>next_date=>TRUNC(SYSDATE+1),interval=>'TRUNC(SYSDATE+1)'dbms_output.put_line(‘job_no=‘||jobno)內(nèi)置PLSQL工具如何找到自己提交的JOB如何找到自己提交的JOB號漢得公司版權(quán)所有 FROM內(nèi)置PLSQL工具PLSQL中讀寫外部文件提供內(nèi)置包UTL_FILE來讀寫外PLSQL中讀寫外部文件提供內(nèi)置包UTL_FILE來讀寫外部文件，其一般處理過程為還有更多需要處理我們來看個例子漢得公司版權(quán)所有把行寫入文關(guān)閉文本文打開文本文從文件讀取內(nèi)置PLSQL工具應(yīng)用舉例漢得公司應(yīng)用舉例漢得公司版權(quán)所有CREATEORREPLACEPROCEDURE(p_filedirINVARCHAR2,p_filenameINVARCHAR2)CURSORemp_infoISSELECTlast_name,salary,department_idFROMemployeesORDERBYv_newdeptnoemployees.department_id%TYPE;v_olddeptnoemployees.department_id%TYPE:=0;v_filehandle:=UTL_FILE.FOPEN(p_filedir,p_filename,'w');UTL_FILE.PUTF(v_filehandle,'SALARYREPORT:GENERATEDON%s\n',UTL_FILE.NEW_LINEFORv_emp_recINemp_infoLOOPv_newdeptno:=v_emp_rec.department_id;內(nèi)置PLSQL工具應(yīng)用舉例（續(xù)漢得應(yīng)用舉例（續(xù)漢得公司版權(quán)所有IFv_newdeptno<>v_olddeptnoUTL_FILE.PUTF(v_filehandle,'DEPARTMENT:ENDUTL_FILE.PUTF(v_filehandle,'EMPLOYEE:%searns:%s\n',v_emp_rec.last_name,v_emp_rec.salary);v_olddeptno:=v_newdeptno;ENDLOOP;UTL_FILE.PUT_LINE(v_filehandle,'***ENDOFREPORT***');UTL_FILE.FCLOSE(v_filehandle);WHENUTL_FILE.INVALID_FILEHANDLETHENRAISE_APPLICATION_ERROR(-20001,'InvalidFile.');WHENUTL_FILE.WRITE_ERRORRAISE_APPLICATION_ERROR(-20002,'UnabletowriteEND/內(nèi)置PLSQL工具LOB概念回顧：大對象類型用于存儲非結(jié)構(gòu)LOB概念回顧：大對象類型用于存儲非結(jié)構(gòu)化的大數(shù)據(jù)，比如大文本、圖片、電影、音樂“Fourscoreandsevenyearsagoourfathersbroughtforthuponthiscontinent,anewnation,conceivedinLIBERTY,anddedicatedtothepropositionthatallmenarecreatedOracle數(shù)據(jù)庫里面的LOB有四種類型1、：字符大對象，存儲在數(shù)據(jù)庫內(nèi)部2、NCLOB：多字節(jié)字符大對象，存儲在數(shù)據(jù)庫內(nèi)部3、BLOB：二進(jìn)制大對象，存儲在數(shù)據(jù)庫內(nèi)部4、BFILE：二進(jìn)制文件，存儲在數(shù)據(jù)庫外部漢得公司版權(quán)所有圖文電PLSQL中大對象的操LONGLOB的區(qū)Long和Long是9i以前的版本使用的大對象類LONGLOB的區(qū)Long和Long是9i以前的版本使用的大對象類型；在9i以后都建議使9i也提供了一系列函數(shù)用以從LONG升級到OUT-of-的理解：LOB可以是對象類型的屬性，而LONG不行漢得公司版權(quán)所有LONGandLONGSingleLONGcolumnperMultipleLOBcolumnsperUpto2Upto4Datastoredin-lineorout-of-SequentialaccesstoRandomaccesstoPLSQL中大對象的操LOB數(shù)據(jù)的存儲方式：LOBValueLocator兩LOB數(shù)據(jù)的存儲方式：LOBValueLocator兩部分，在我們的數(shù)據(jù)庫表上的LOB字段，肯定會存儲LOB的Locator;至于LOB的value,則要看其內(nèi)外部類型和大小決定存儲位置，默認(rèn)情況下，內(nèi)部LOB小于LOBLOB表上的LOB內(nèi)部LOB的一般操作步驟為1、在表中添加LOB類型的2、在程序中聲明和初始化LOB的SELECTFORUPDATE鎖定目標(biāo)行，準(zhǔn)備更新行上的LOB列（LOB的3、使5、Commit提交更改；這樣的PLSQL包，也可以使用OCI,JDBC等漢得公司版權(quán)所有PLSQL中大對象的操外部Bfile的操作Bfile是數(shù)據(jù)庫外部外部Bfile的操作Bfile是數(shù)據(jù)庫外部文件，在數(shù)據(jù)庫表上這種類型的字段實際只是存儲一個電Bfilee是數(shù)據(jù)庫外部文件，是只讀的，所以不參與事務(wù)操作；用戶必須先創(chuàng)建文件并放到特定的目錄下，給予進(jìn)程以目錄和文件的讀取權(quán)限；在中刪除Bfile這樣的LOB數(shù)據(jù)的時候，它并沒有實際的去刪除對應(yīng)的操作系統(tǒng)上的文件，實際文的刪除是DBA,系統(tǒng)管理員的工作，Bfile的大小限制取決于操作系統(tǒng)，不受Oracle限制漢得公司版權(quán)所有PLSQL中大對象的操OracleDirectory：為了便于控制Bfile存儲的安全性，Oracle數(shù)據(jù)庫引入了OracleOracleDirectory：為了便于控制Bfile存儲的安全性，Oracle數(shù)據(jù)庫引入了OracleDirectory的在Oracle內(nèi)部創(chuàng)建Directory默認(rèn)的所有者是sys,并有DBA(或者是另一個擁ANYDIRECTORY權(quán)限的用戶)創(chuàng)建對象可以像表那樣給其他用戶賦權(quán)使用Bfile的一般步驟3、在Oracle數(shù)據(jù)庫中創(chuàng)建Directory對4、授權(quán)讀權(quán)限給特定的數(shù)據(jù)庫用5、往表中插入數(shù)據(jù)時使6、在程序中聲明和初始化LOB的7、Select指定行上列到8、使DBMS_LOB或者通OCI讀取Bfile使用Locator作為文件的一個引用漢得公司版權(quán)所有CREATEDIRECTORYdir_nameASos_path;ALTERTABLEemployeesADDemp_videoPLSQL中大對象的操使用Bfile的一般步驟使用Bfile的一般步驟3、在Oracle數(shù)據(jù)庫中創(chuàng)建Directory對4、授權(quán)讀權(quán)限給特定的數(shù)據(jù)庫用5、往表中插入數(shù)據(jù)時使6、在程序中聲明和初始化LOB的7、Select指定行上Bfile列到8、使DBMS_LOB或者通OCI讀取Bfile使用Locator作為文件的一個引用漢得公司版權(quán)所有UPDATESETemp_video=WHEREemployee_id=FUNCTIONBFILENAME(directory_aliasINfilenameINRETURNGRANTREADONDIRECTORYdir_nameTOCREATEDIRECTORYdir_nameASos_path;ALTERTABLEemployeesADDemp_videoPLSQL中大對象的操舉例漢得公司版權(quán)所有CREATE舉例漢得公司版權(quán)所有CREATEORREPLACEPROCEDURE(p_file_locINVARCHAR2) v_filenameVARCHAR2(16);CURSORemp_cursorISSELECTfirst_nameFROMemployeesWHEREdepartment_id=60FORUPDATE;FORemp_recordINemp_cursorv_filename:=emp_record.first_name||'.bmp';v_file:=BFILENAME(p_file_loc,v_filename);UPDATEemployeesSETemp_video=v_fileWHERECURRENTOFemp_cursor;||'SIZE:'||DBMS_LOB.GETLENGTH(v_file));ENDEND/PLSQL中大對象的操使測試文件是否存在使測試文件是否存在漢得公司版權(quán)所有CREATEORREPLACEPROCEDUREload_emp_bfile(p_file_locINVARCHAR2) v_file_existsBOOLEAN;CURSORemp_cursorISFORemp_recordINemp_cursorv_filename:=emp_record.first_name||'.bmp';v_file:=BFILENAME(p_file_loc,v_filename);v_file_exists:=(DBMS_LOB.FILEEXISTS(v_file)=1);IFv_file_existsTHENDBMS_LOB.FILEOPEN(v_file);...PLSQL中大對象的操DBMS_LOB主要函數(shù)介紹1、更改LOB的值A(chǔ)PPENDCOPYDBMS_LOB主要函數(shù)介紹1、更改LOB的值A(chǔ)PPENDCOPYERASETRIMWRITE2、讀取、檢查LOB的值：GETLENGTH,INSTR,READ,3、Bfile專用：FILECLOSE,最常用的和Write介紹漢得公司版權(quán)所有PROCEDUREWRITElobdstINOUTBLOB|CLOB,amountINOUTBINARY_INTEGER,offsetININTEGER:=bufferINRAW|VARCHAR2)--RAWforPROCEDUREREADlobsrcINBFILE|BLOB|CLOB,amountINOUTBINARY_INTEGER,offsetININTEGER,bufferOUTRAW|VARCHAR2)PLSQL中大對象的操數(shù)據(jù)庫表中LOB列的增刪改（下面的例子中數(shù)據(jù)庫表中LOB列的增刪改（下面的例子中resume是CLOB類型，Picture是BLOB類型新增更新注意EMPTY_CLOB(),EMPTY_BLOB()跟NULL是不同的概念，ISNULL對這兩種情況返回漢得公司版權(quán)所有UPDATESETresume='DateofBirth:1June1956'WHEREemployee_id=170;UPDATESETresume='DateofBirth:8February1951',picture=EMPTY_BLOB()WHEREemployee_id=INSERTINTOemployees(employee_id,first_name，last_name,email,hire_date,job_id,salary,resume,picture)VALUES(405,'Marvin','Ellis','MELLIS',SYSDATE,'AD_ASST',4000,EMPTY_CLOB(),NULL);PLSQL中大對象的操漢得公司版權(quán)所有l(wèi)oblocCLOB; --servesas漢得公司版權(quán)所有l(wèi)oblocCLOB; --servesastheLOBlocator VARCHAR2(32767):='Resigned:5August2000';amountNUMBER; --amounttobewrittenoffsetINTEGER; --wheretostartwritingSELECTresumeINTOlobloc WHEREemployee_id=405FORUPDATE;offset:=Damount:= :='Resigned:30September2000';SELECTresumeINTOlobloc WHEREemployee_id=170FORUPDATE;amount:=length(text);DBMS_LOB.WRITEAPPEND(lobloc,amount,DBMS_LOB.WRITE(lobloc,amount,offset,textBMS_LOB.GETLENGTH(lobloc)+PLSQL中大對象的操查詢LOB內(nèi)容截取常用函數(shù)漢得查詢LOB內(nèi)容截取常用函數(shù)漢得公司版權(quán)所有SELECTDBMS_LOB.SUBSTR(resume,5,18),DBMS_LOB.INSTR(resume,'=') WHEREemployee_idIN(170,DBMS_LOB.SUBSTR(lob_column,no_of_chars,starting)DBMS_LOB.INSTR(lob_column,pattern)SELECTemployee_id,last_name,resume--CLOBFROMemployeesWHEREemployee_idIN(405,PLSQL中大對象的操刪除當(dāng)我們刪除一行的時候，該刪除當(dāng)我們刪除一行的時候，該行上的內(nèi)部LOB對象也被刪除或語句，是LOB列漢得公司版權(quán)所有UPDATESETresume=EMPTY_CLOB()WHEREemployee_id=170;FROMWHEREemployee_id=PLSQL中大對象的操數(shù)據(jù)庫觸發(fā)器Trigger的概念對數(shù)據(jù)庫對數(shù)據(jù)庫觸發(fā)器Trigger的概念對數(shù)據(jù)庫對象的操作可引發(fā)很多事件，比如beforeInsert,beforeupdate等等，但這些事件產(chǎn)生的時候我實際應(yīng)用場景舉例：（從EBS的發(fā)運(yùn)確認(rèn)產(chǎn)生的生成POS收貨確認(rèn)單發(fā)運(yùn)確TRANSACTIONID到表注意：除非迫不得已，盡量避免使用Trigger,因為這會導(dǎo)致維護(hù)困難漢得公司版權(quán)所有按規(guī)則挑錄入銷售訂數(shù)據(jù)庫觸發(fā)舉例INSERT.…漢得公舉例INSERT.…漢得公司版權(quán)所有select*fromall_triggerswheretable_name數(shù)據(jù)庫觸發(fā)創(chuàng)建Trigger：Trigger的定義語創(chuàng)建Trigger：Trigger的定義語句里面涉及到如下關(guān)鍵因素時機(jī)：Before或者AfterInstead事件：InsertUpdate對象：表名（或視圖名類型：Row或者Statement級；重點注意:Row級：SQL語句影響到的每一行都會引發(fā)Statement級：一句SQL語句引發(fā)一次，不管它影響多少行（甚至0行漢得公司版權(quán)所有數(shù)據(jù)庫觸發(fā)Trigger的觸發(fā)順序DML語Triggering動…BEFORErowtriggerAFTERrowtriggerTrigger的觸發(fā)順序DML語Triggering動…BEFORErowtriggerAFTERrowtriggerDML語BEFOREstatementTriggering動BEFORErowAFTERrowBEFORErowAFTERrowAFTERstatement漢得公司版權(quán)所有UPDATESETsalary=salary*1.1WHEREdepartment_id=INSERTINTOdepartmentsdepartment_name,location_id)VALUES(400,'CONSULTING',2400);數(shù)據(jù)庫觸發(fā)創(chuàng)建Statement級別語法舉例：單一事件創(chuàng)建Statement級別語法舉例：單一事件測試：把數(shù)據(jù)庫服務(wù)器日期改成星期天，然后在Insert數(shù)據(jù)，看有什么效果漢得公司版權(quán)所有INSERTINTOemployees(employee_id,last_name,first_name,email,hire_date,VALUES(300,'Smith','Rob','RSMITH',SYSDATE,'IT_PROG',4500,60);CREATEORREPLACETRIGGERsecure_empBEFOREINSERTONemployeesIF(TO_CHAR(SYSDATE,'DY')IN('SAT','SUN'))OR(TO_CHAR(SYSDATE,'HH24:MI')NOTBETWEEN'08:00'AND'18:00')THENRAISE_APPLICATION_ERROR(-20500,'YoumayinsertintoEMPLOYEEStableonlyENDIF;/CREATE[ORREPLACE]TRIGGERtrigger_nameevent1[ORevent2OR數(shù)據(jù)庫觸發(fā)舉例：多事件Trigger，在Body舉例：多事件Trigger，在Body中判斷具體事漢得公司版權(quán)所有CREATEORREPLACETRIGGERBEFOREINSERTORUPDATEORDELETEONemployeesIF(TO_CHAR(SYSDATE,'DY')IN('SAT','SUN'))OR(TO_CHAR(SYSDATE,'HH24')NOTBETWEEN'08'AND'18') DELETINGRAISE_APPLICATION_ERROR(-20502,'Youmaydelete tableonlyduringbusinessELSIFINSERTINGRAISE_APPLICATION_ERROR(-20500,'YoumayinsertintoEMPLOYEEStableonlyduringbusinesshours.'); UPDATING('SALARY')THENRAISE_APPLICATION_ERROR(-20503,'YoumayupdateSALARYonlyduringbusinessRAISE_APPLICATION_ERROR(-20504,'YoumayEMPLOYEEStableonlyduringnormalENDIF;數(shù)據(jù)庫觸發(fā)創(chuàng)建Row級別語法舉例漢創(chuàng)建Row級別語法舉例漢得公司版權(quán)所有CREATEORREPLACETRIGGERBEFOREINSERTORUPDATEOFsalaryONemployeesFOREACHROWIFNOT(:NEW.job_idIN('AD_PRES','AD_VP'))AND:NEW.salary>15000ENDIF;/CREATE[ORREPLACE]TRIGGERtrigger_nameevent1[ORevent2ORON[REFERENCINGOLDASold|NEWASnew]FOREACHROW數(shù)據(jù)庫觸發(fā)使用NEW修飾詞和漢得使用NEW修飾詞和漢得公司版權(quán)所有CREATEORREPLACETRIGGERaudit_emp_valuesAFTERDELETEORINSERTORUPDATEONemployeesFOREACHROWINSERTINTOaudit_emp_table(user_name,timestamp,id,old_last_name,new_last_name,old_title,new_