第6章PL/SQL系統(tǒng)內(nèi)置函數(shù)系統(tǒng)內(nèi)置函數(shù)1．?dāng)?shù)學(xué)運算函數(shù)數(shù)學(xué)運算函數(shù)可對Oracle系統(tǒng)提供的數(shù)值數(shù)據(jù)進行數(shù)學(xué)運算并返回運算結(jié)果。常用的數(shù)學(xué)運算函數(shù)如表6.7所示。函

數(shù)名說

明Abs(數(shù)值)返回參數(shù)數(shù)值的絕對值，結(jié)果恒為正Ceil(數(shù)值)返回大于或等于參數(shù)數(shù)值最接近的整數(shù)Cos(數(shù)值)返回參數(shù)數(shù)值的余弦值Floor(數(shù)值)返回等于或小于參數(shù)的最大整數(shù)Mod(被除數(shù),除數(shù))返回兩數(shù)相除的余數(shù)。如果除數(shù)等于0，則返回被除數(shù)Power(數(shù)值,n)返回指定數(shù)值的n次冪Round(數(shù)值,n)結(jié)果近似到數(shù)值小數(shù)點右側(cè)的n位Sign(數(shù)值)返回值指出參數(shù)值是正還是負。若參數(shù)大于0則返回1；若小于0則返回-1；若等于0則返回0Sqrt(數(shù)值)返回參數(shù)數(shù)值的平方根Trunc(數(shù)值,n)返回舍入到指定的n位的參數(shù)數(shù)值。如果n為正，就截取到小數(shù)右側(cè)的該數(shù)值處；如果n為負，就截取到小數(shù)點左側(cè)的該數(shù)值處；如果沒有指定n就假定為0，截取到小數(shù)點處系統(tǒng)內(nèi)置函數(shù)（1）ABS函數(shù)語法格式為：ABS(數(shù)值)功能：返回給定數(shù)值的絕對值，參數(shù)為數(shù)值型表達式?！纠?.20】

顯示ABS函數(shù)對兩個不同數(shù)值的效果。SELECTABS(-0.8)FROMdual;SELECTABS(0.8)FROMdual;得出的結(jié)果都為0.8。說明：Oracle11g數(shù)據(jù)庫中的dual表是虛擬的一個表，它有一行一列，所有者為sys用戶，可供數(shù)據(jù)庫中的所有用戶使用。系統(tǒng)內(nèi)置函數(shù)（2）ROUND函數(shù)語法格式為：ROUND(數(shù)值,n)功能：求一個數(shù)值的近似值，結(jié)果近似到小數(shù)點右側(cè)的n位?！纠?.21】

求幾個數(shù)值的近似值。SELECTROUND(3.678,2)FROMdual;結(jié)果為：3.68。SELECTROUND(3.3243,3)FROMdual;結(jié)果為：3.324。系統(tǒng)內(nèi)置函數(shù)2．字符串函數(shù)字符串函數(shù)用于對字符串進行處理。一些常用的字符串函數(shù)如表。函

數(shù)

名返回值說明Length(值)返回字符串、數(shù)字或表達式的長度Lower(字符串)把給定字符串中的字符變成小寫Upper(字符串)把給定字符串中的字符變成大寫Lpad(字符串,長度[,填充字符串])在字符串左側(cè)，使用指定的填充字符串進行填充直到指定的長度，若未指定填充字符串，則默認為空格Rpad(字符串,長度[,填充字符串])在字符串右側(cè)，使用指定的填充字符串進行填充直到指定的長度，若未指定填充字符串，則默認為空格Ltrim(字符串,[,匹配字符串])從字符串左側(cè)刪除匹配字符串中出現(xiàn)的任何字符，直到匹配字符串中沒有字符為止Rtrim(字符串,[,匹配字符串])從字符串右側(cè)刪除匹配字符串中出現(xiàn)的任何字符，直到匹配字符串中沒有字符為止字符串1‖字符串2合并兩個字符串Initcap(字符串)將每個字符串的首字母大寫Instr(源字符串,目標(biāo)字符串[,起始位置[,匹配次數(shù)]])判斷目標(biāo)字符串是否存在于源字符串中，并根據(jù)匹配次數(shù)顯示目標(biāo)字符串的位置，返回數(shù)值Replace(源字符串,目標(biāo)字符串,替代字符串)在源字符串中查找目標(biāo)字符串，并用替代字符串來替換所有的目標(biāo)字符串Soundex(字符串)查找與字符串發(fā)音相似的單詞，該單詞的首字母要與字符串的首字母相同Subs(字符串,開始位置[,刪除字符的個數(shù)])在字符串中刪除從指定位置開始的指定個數(shù)字符。若未指定個數(shù)，則刪除從開始位置的所有字符系統(tǒng)內(nèi)置函數(shù)（1）LENTH函數(shù)語法格式為：LENGTH(值)功能：返回參數(shù)值的長度，返回值為整數(shù)。參數(shù)值可以是字符串、數(shù)字或者表達式。（2）LOWER函數(shù)語法格式為：LOWER(字符串)功能：將給定字符串的字符變?yōu)樾??！纠?.22】

轉(zhuǎn)換字符的大小寫。SELECTLOWER('hello')FROMdual;SELECTLOWER('Hello')FROMdual;SELECTLOWER('HELLO')FROMdual;結(jié)果都為：hello。系統(tǒng)內(nèi)置函數(shù)（3）REPLACE函數(shù)語法格式為：Replace(源字符串,目標(biāo)字符串,替代字符串)功能：把源字符串中目標(biāo)字符串用替代字符串代替?！纠?.23】

字符替換。SELECTREPLACE('Helloworld','world','baby')FROMdual;結(jié)果為：Hellobaby。系統(tǒng)內(nèi)置函數(shù)3．統(tǒng)計函數(shù)Oracle11g數(shù)據(jù)庫提供了豐富的統(tǒng)計函數(shù)用于處理數(shù)值型數(shù)據(jù)，如表6.9所示列出的常用統(tǒng)計函數(shù)。函

數(shù)

名返回值說明Avg([distinct]列名)求列名中所有值的平均值，若使用DISTINCT選項，則只能使用不同的非空數(shù)值Count([distinct]值表達式)統(tǒng)計選擇行的數(shù)目，并忽略參數(shù)值中的空值。若使用distinct選項，則只統(tǒng)計不同的非空數(shù)值。參數(shù)值可以是字段名，也可以是表達式Max(value)從選定的value中選取數(shù)值/字符的最大值，忽略空值Min(value)從選定的value中選取數(shù)值/字符的最小值，忽略空值Stddev(value)返回所選擇的value的標(biāo)準偏差Sum(value)返回value的和。value可以是字段名，也可以是表達式Variance([distinct]value)返回所選行的所有數(shù)值的方差，忽略value的空值系統(tǒng)內(nèi)置函數(shù)（1）AVG函數(shù)語法格式為：AVG([DISTINCT]列名)功能：求所有數(shù)值型列中所有值的平均值，若使用DISTINCT關(guān)鍵字，則只能使用不能非空的數(shù)值?！纠?.24】

求“計算機基礎(chǔ)”課的平均成績。SELECTAVG(成績) FROMcjb WHERE課程號='101';執(zhí)行結(jié)果為：78.65。系統(tǒng)內(nèi)置函數(shù)（2）COUNT函數(shù)語法格式為：COUNT([DISTINCT]值)功能：統(tǒng)計選擇行的數(shù)目，并忽略參數(shù)值中的空值。若使用DISTINCT選項，則只統(tǒng)計不同的非空數(shù)值。參數(shù)值可以是字段名，也可以是表達式。【例6.25】

求XSB表的學(xué)生總數(shù)。SELECTCOUNT(*) FROMxsb;執(zhí)行結(jié)果為：24。系統(tǒng)內(nèi)置函數(shù)4．日期函數(shù)Oracle11g數(shù)據(jù)庫提供了豐富的日期函數(shù)用來處理日期型數(shù)據(jù)，如表所示的常用日期函數(shù)。函

數(shù)

名返回值說明Add_months(日期值,月份數(shù))把一些月份加到日期上，并返回結(jié)果Last_day(日期值)返回指定日期所在月份的最后一天Months_between(日期值1,日期值)返回日期值1減去日期值2得到的月數(shù)New_time(當(dāng)前日期,當(dāng)前時區(qū),指定時區(qū))根據(jù)當(dāng)前日期和當(dāng)前時區(qū)，返回在指定時區(qū)中的日期。其中，當(dāng)前時區(qū)和指定時區(qū)的值為時區(qū)的3個字母縮寫Next_day(日期值,'day')給出指定日期后的day所在的日期；day是全拼的星期名稱Round(日期值,'format')把日期值四舍五入到由format指定的格式To_char(日期值,'format')將日期型數(shù)據(jù)轉(zhuǎn)換成以format指定形式的字符型數(shù)據(jù)To_date(字符串,'format')將字符串轉(zhuǎn)換成以format指定形式的日期型數(shù)據(jù)返回Trunc(日期值,'format')把任何日期的時間設(shè)置為00:00:00系統(tǒng)內(nèi)置函數(shù)（1）LAST_DAY函數(shù)語法格式為：LAST_DAY(日期值)功能：求指定日期所在月份的最后一天?！纠?.26】

查詢本月的最后一天。SELECTLAST_DAY(SYSDATE)FROMdual;其中，SYSDATE也是日期函數(shù)，可返回當(dāng)前系統(tǒng)的日期。系統(tǒng)內(nèi)置函數(shù)（2）MONTHS_BETWEEN函數(shù)語法格式為：MONTHS_BETWEEN(日期值1,日期值2)功能：返回日期值1減去日期值2得到的月數(shù)。如果日期值1比日期值2要早，則函數(shù)將返回一個負數(shù)?！纠?.27】

求兩個日期間相隔的月數(shù)。SELECTMONTHS_BETWEEN('2016-01-25','2016-03-25')FROMdual;執(zhí)行結(jié)果如圖。第6章PL/SQL用戶定義函數(shù)用戶定義函數(shù)用戶定義函數(shù)是存儲在數(shù)據(jù)庫中的代碼塊，可以把值返回到調(diào)用程序。調(diào)用時如同系統(tǒng)函數(shù)一樣，如max(value)函數(shù)，其中value為參數(shù)。函數(shù)的參數(shù)有如下3種模式。（1）IN模式：表示該參數(shù)是輸入給函數(shù)的參數(shù)。（2）OUT模式：表示該參數(shù)在函數(shù)中被賦值，并可以傳給函數(shù)調(diào)用程序。（3）INOUT模式：表示該參數(shù)既可以傳值也可以被賦值。01創(chuàng)建函數(shù)

1．以界面方式創(chuàng)建函數(shù)2．以命令方式創(chuàng)建函數(shù)創(chuàng)建函數(shù)

1．以界面方式創(chuàng)建函數(shù)右擊myorcl連接的“函數(shù)”節(jié)點，選擇“新建函數(shù)”選項，彈出“創(chuàng)建PL/SQL函數(shù)”對話框。在“名稱”欄中輸入函數(shù)的名稱，在“參數(shù)”選項頁的第一行選擇返回值的類型，單擊按鈕增加一個參數(shù)，設(shè)置參數(shù)名稱、類型和模式，設(shè)置完成后單擊“確定”按鈕。在打開的主界面“COUNT_NUM”窗口中完成函數(shù)的編寫工作，完成后單擊“編譯以進行調(diào)試”按鈕完成函數(shù)的創(chuàng)建，整個過程的操作步驟如圖。創(chuàng)建函數(shù)

2．以命令方式創(chuàng)建函數(shù)在Oracle11g數(shù)據(jù)庫中，創(chuàng)建用戶定義函數(shù)使用CREATEFUNCTION語句，其語法格式為：CREATE[ORREPLACE]FUNCTION函數(shù)名 /*函數(shù)名稱*/(

參數(shù)名1,參數(shù)類型數(shù)據(jù)類型, /*參數(shù)定義部分*/

參數(shù)名2,參數(shù)類型數(shù)據(jù)類型,

參數(shù)名3,參數(shù)類型數(shù)據(jù)類型, …) RETURN返回值類型 /*定義返回值類型*/ {IS|AS} [聲明變量] BEGIN

函數(shù)體; /*函數(shù)體部分*/ [RETURN(返回表達式);] /*返回語句*/ END[函數(shù)名];創(chuàng)建函數(shù)

相關(guān)參數(shù)說明如下。（1）函數(shù)名：用戶定義函數(shù)的名稱必須符合標(biāo)識符的規(guī)則，對其所有者來說，該名稱在數(shù)據(jù)庫中是唯一的。（2）參數(shù)類型：參數(shù)類型可以是IN模式、OUT模式或INOUT模式，默認為IN模式。（3）數(shù)據(jù)類型：這里定義參數(shù)的數(shù)據(jù)類型時不需要指定數(shù)據(jù)類型的長度。（4）RETURN選項：在函數(shù)參數(shù)定義部分后面的RETURN選項中，可以指定函數(shù)返回值的數(shù)據(jù)類型。（5）函數(shù)體：函數(shù)體部分由PL/SQL的語句構(gòu)成，是實現(xiàn)函數(shù)功能的主要部分。（6）RETURN語句：在函數(shù)體最后使用一條RETURN語句，將返回表達式的值返回給函數(shù)調(diào)用程序。創(chuàng)建函數(shù)

下面給出一個函數(shù)，并說明函數(shù)的3種參數(shù)的合法性。CREATEORREPLACEFUNCTION函數(shù)名稱( in_pmtINchar, out_pmtOUTchar, in_out_pmtINOUTchar) RETURNchar AS return_charchar; BEGIN

函數(shù)語句序列 RETURN(return_char); END[函數(shù)名稱];創(chuàng)建函數(shù)

函數(shù)語句序列及其可能出現(xiàn)的情況如下。①in_pmt:='hello';該語句是錯誤的，因為IN類型參數(shù)只能作為形參來傳遞值，不能在函數(shù)體中賦值。②return_char:=in_pmt;該語句語法正確。因為IN類型參數(shù)本身就是用來傳遞值的，而return_char則是作為返回值變量，通過IN類型參數(shù)in_pmt給return_char賦值。③out_pmt:='hello';該語句正確。因為out_pmt作為OUT類型參數(shù)，在函數(shù)體內(nèi)被賦值是允許的。④return_char:=out_pmt;該語句不正確。因為OUT類型參數(shù)不能傳遞值。⑤in_out_pmt:='world';該語句正確。因為INOUT類型參數(shù)可以在函數(shù)體中被賦值。⑥r(nóng)eturn_char:=in_out_pmt;該語句正確。因為INOUT類型參數(shù)既能傳遞值，也可以賦值。創(chuàng)建函數(shù)

【例6.28】

計算某門課程全體學(xué)生的平均成績。CREATEORREPLACEFUNCTIONaverage(cnumINchar) RETURNnumberAS avgernumber; /*定義返回值變量*/BEGIN SELECTAVG(成績)INTOavger FROMCJB WHERE課程號=cnum GROUPBY課程號; RETURN(avger);END;創(chuàng)建函數(shù)

【例6.29】

創(chuàng)建一個統(tǒng)計數(shù)據(jù)庫中不同性別人數(shù)的函數(shù)。CREATEORREPLACEFUNCTIONcount_num(in_sexINchar) RETURNnumberAS out_numnumber; /*定義返回值變量*/BEGIN IFin_sex='男'THEN SELECTCOUNT(性別)INTOout_num FROMxsb WHERE性別='男'; ELSE SELECTCOUNT(性別)INTOout_num FROMxsb WHERE性別='女'; ENDIF; RETURN(out_num); /*返回語句*/ENDcount_num; /*函數(shù)結(jié)束*/02調(diào)用函數(shù)調(diào)用函數(shù)無論是命令行還是程序語句，都可以通過名稱直接在表達式中調(diào)用函數(shù)，語法格式為：

變量名:=函數(shù)名[(實參1,實參2,…)]【例6.30】

用函數(shù)count_num統(tǒng)計XSB表中有多少女學(xué)生。DECLARE girl_numnumber;BEGIN girl_num:=count_num('女'); DBMS_OUTPUT.PUT_LINE(TO_CHAR(girl_num));END;輸出結(jié)果為：8。03刪除函數(shù)

刪除函數(shù)

用DROPFUNCTION語句可以刪除用戶定義的函數(shù)，語法格式為：DROPFUNCTION[用戶方案名.]函數(shù)名例如，要把函數(shù)count_num刪除，只需執(zhí)行如下語句：DROPFUNCTIONcount_num;第6章PL/SQL游

標(biāo)01顯式游標(biāo)1．聲明游標(biāo)2．打開游標(biāo)3．讀取數(shù)據(jù)4．關(guān)閉游標(biāo)5．注意事項顯式游標(biāo)1．聲明游標(biāo)顯式游標(biāo)是作為聲明段的一部分進行定義的，定義方法如下：DECLARECURSOR游標(biāo)名 IS SELECT語句其中，游標(biāo)名是與某個查詢結(jié)果集聯(lián)系的符號名，要遵循Oracle系統(tǒng)變量定義的規(guī)則。SELECT語句可查詢產(chǎn)生與所聲明游標(biāo)相關(guān)聯(lián)的結(jié)果集。例如：DECLARECURSORXS_CUR IS SELECT學(xué)號,姓名,總學(xué)分 FROMxsb WHERE專業(yè)='計算機';顯式游標(biāo)2．打開游標(biāo)聲明后，要使用游標(biāo)就必須先打開它。使用OPEN語句打開游標(biāo)，其格式為：

OPEN游標(biāo)名打開游標(biāo)后，可以使用系統(tǒng)變量%ROWCOUNT返回最近一次提取到數(shù)據(jù)行的序列號。在打開游標(biāo)之后，且提取數(shù)據(jù)之前可訪問%ROWCOUNT值，并返回0?！纠?.31】

定義游標(biāo)XS_CUR，然后打開游標(biāo)，輸出當(dāng)前行的序列號。DECLARECURSORxs_cur IS SELECT學(xué)號,姓名,總學(xué)分 FROMxsb; BEGIN OPENxs_cur; DBMS_OUTPUT.PUT_LINE(xs_cur%ROWCOUNT); END;輸出結(jié)果為：0。顯式游標(biāo)3．讀取數(shù)據(jù)游標(biāo)打開后，就可以使用FETCH語句從中讀取數(shù)據(jù)。FETCH語句的格式為：FETCH游標(biāo)名[INTO變量名,…n]其中，INTO子句將讀取的數(shù)據(jù)存放到指定的變量中?！纠?.32】

將計算機專業(yè)每個學(xué)生的“學(xué)號”與“總學(xué)分”相加后的值輸出。DECLARE v_xhchar(6); v_zxfnumber(2); CURSORxs_cur IS SELECT學(xué)號,總學(xué)分 FROMxsb WHERE專業(yè)='計算機'; BEGIN OPENxs_cur; FETCHxs_curINTOv_xh,v_zxf; WHILExs_cur%FOUND LOOP DBMS_OUTPUT.PUT_LINE(v_xh+v_zxf); FETCHxs_curINTOv_xh,v_zxf; ENDLOOP; CLOSEXS_CUR; END;輸出結(jié)果如圖。顯式游標(biāo)4．關(guān)閉游標(biāo)游標(biāo)使用完以后要及時關(guān)閉。關(guān)閉游標(biāo)可使用CLOSE語句，其格式為：CLOSE游標(biāo)名;如關(guān)閉上例中的游標(biāo)：CLOSEXS_CUR;關(guān)閉游標(biāo)即關(guān)閉SELECT操作，釋放所占的內(nèi)存區(qū)。顯式游標(biāo)5．注意事項（1）用%FOUND或%NOTFOUND檢驗游標(biāo)操作成功與否。%FOUND屬性表示當(dāng)前游標(biāo)是否指向有效的一行，如果游標(biāo)能按照其選擇條件從數(shù)據(jù)庫中成功查詢出一行數(shù)據(jù)，則返回TRUE表示成功，否則返回FALSE表示失敗。（2）循環(huán)執(zhí)行游標(biāo)進行取數(shù)操作時，將最近一次提取到數(shù)據(jù)行的序列號保存在系統(tǒng)變量%ROWCOUNT中。（3）用FETCH語句取游標(biāo)數(shù)據(jù)到一個或多個變量中，目標(biāo)變量的數(shù)目和類型必須與游標(biāo)SELECT表中表列的數(shù)目、數(shù)據(jù)類型相一致，例如：DECLARECURSORmycur IS SELECT課程號, /*課程號是字符型*/

成績 /*成績是數(shù)字型*/ FROMcjb WHERE課程號='101'; v_kchchar(3); /*v_kch存儲課程號，為字符型*/ v_cjnumber(2); /*v_cj存儲成績，為數(shù)字型*/ BEGIN OPENmycur; FETCHmycurINTOv_kch,v_cj; /*目標(biāo)變量的數(shù)目和類型都與SELECT表列相匹配*/ END;顯式游標(biāo)（4）如果試圖打開一個已打開的游標(biāo)或關(guān)閉一個已關(guān)閉的游標(biāo)，都將會出現(xiàn)錯誤。因此用戶在打開或關(guān)閉游標(biāo)前，若不清楚其狀態(tài)，應(yīng)該用%ISOPEN進行檢查。根據(jù)其返回值（TRUE或FALSE）采取相應(yīng)的動作，例如：IFmycur%ISOPENTHEN FETCHmycurINTOv_kch,v_cj; /*游標(biāo)已打開，可以操作*/ELSE OPENmycur; /*游標(biāo)沒有打開，先打開游標(biāo)*/ENDIF;02隱式游標(biāo)隱式游標(biāo)下面一段程序是在存儲過程（詳見第7章）定義中使用的隱式游標(biāo)：CREATEORREPLACEPROCEDUREcx_xm( in_xhINchar,out_xmOUTvarchar2)ASBEGIN SELECT姓名INTOout_xm /*隱式游標(biāo)必須用INTO*/ FROMxsb WHERE學(xué)號=in_xhANDrownum=1;

Dbms_output.put_line(out_xm);ENDcx_xm;使用隱式游標(biāo)要注意以下幾點。（1）每一個隱式游標(biāo)必須有一個INTO。（2）與顯式游標(biāo)一樣，接收數(shù)據(jù)目標(biāo)變量的數(shù)目、數(shù)據(jù)類型要與SELECT列表一致。（3）隱式游標(biāo)一次僅能返回一行數(shù)據(jù)，使用時必須檢查異常，常見的異常有“no_data_found”和“too_many_rows”。（4）為確保隱式游標(biāo)僅返回一行數(shù)據(jù)，可用ROWNUM=1來限定，表示返回第一行數(shù)據(jù)。03游標(biāo)FOR循環(huán)

游標(biāo)FOR循環(huán)

FOR循環(huán)和游標(biāo)的結(jié)合使游標(biāo)的使用更簡明：用戶不需要打開游標(biāo)、取數(shù)據(jù)、測試數(shù)據(jù)的存在（用%FOUND或%notfound），以及關(guān)閉游標(biāo)這些重復(fù)且煩瑣的操作。當(dāng)游標(biāo)被調(diào)用時，用SELECT語句中的同樣一些元素創(chuàng)建一條記錄，對于游標(biāo)檢索出的每一行繼續(xù)執(zhí)行循環(huán)內(nèi)的全部代碼，當(dāng)發(fā)現(xiàn)沒有數(shù)據(jù)時，游標(biāo)自動關(guān)閉。游標(biāo)FOR循環(huán)的語法格式如下：FOR變量名IN游標(biāo)名[(參數(shù)1[,參數(shù)2]…)]LOOP

語句段ENDLOOP;其中，游標(biāo)名必須是已經(jīng)聲明的游標(biāo)名稱，后面括號中是應(yīng)用程序傳遞給游標(biāo)的參數(shù)，F(xiàn)OR關(guān)鍵字后面的變量名是FOR循環(huán)隱含聲明的記錄變量，其結(jié)構(gòu)與游標(biāo)查詢語句返回的結(jié)果集結(jié)構(gòu)相同。游標(biāo)FOR循環(huán)

【例6.33】

從CJB表中選出優(yōu)秀（大于90分）的成績記錄另存入一張表。先在XSCJ數(shù)據(jù)庫中創(chuàng)建tempCj表（結(jié)構(gòu)與CJB相同），然后編寫PL/SQL代碼如下：DECLARE v_xhchar(6); v_kchchar(3); v_cjnumber(4,2);CURSORkc_curIS SELECT學(xué)號,課程號,成績 FROMcjb;BEGIN OPENkc_cur; FETCHkc_curINTOv_xh,v_kch,v_cj; WHILEkc_cur%FOUNDLOOP IFv_cj>90THEN INSERTINTOtempCjVALUES(v_xh,v_kch,v_cj); ENDIF; FETCHkc_curINTOv_xh,v_kch,v_cj; ENDLOOP; CLOSEkc_cur;END;游標(biāo)FOR循環(huán)

上面的例子用游標(biāo)的FOR循環(huán)重寫如下：DECLARE v_xhchar(6); v_kchchar(3); v_cjnumber(4,2);CURSORkc_curIS SELECT學(xué)號,課程號,成績 FROMcjb;BEGIN FORkc_cur_recINkc_curLOOP v_xh:=kc_cur_rec.學(xué)號; v_kch:=kc_cur_rec.課程號; v_cj:=kc_cur_rec.成績; IFv_cj>90THEN INSERTINTOtempCjVALUES(v_xh,v_kch,v_cj); ENDIF; ENDLOOP;END;執(zhí)行結(jié)果如圖。04游標(biāo)變量1．定義REFCURSOR類型2．聲明游標(biāo)變量3．控制游標(biāo)變量游標(biāo)變量1．定義REFCURSOR類型游標(biāo)變量就像C語言的指針一樣，在PL/SQL中，指針具有REFX數(shù)據(jù)類型（REF是REFERENCE縮寫，X表示類對象），因此游標(biāo)變量就具有REFCURSOR類型。創(chuàng)建游標(biāo)變量首先要定義REFCURSOR類型，語法格式為：TYPEREFCURSOR類型名 IS REFCURSOR[RETURN返回類型];其中，返回類型表示一個記錄或者數(shù)據(jù)庫表的一行。例如，下面定義一個REFCURSOR類型游標(biāo)：DECLARETYPExs_cur IS REFCURSORRETURNXSB%ROWTYPE;游標(biāo)變量2．聲明游標(biāo)變量一旦定義了REFCURSOR類型，就可以在PL/SQL塊或子程序中聲明游標(biāo)變量，例如：DECLARE TYPExs_curISREFCURSORRETURNXSB%ROWTYPE; xscurxs_cur; /*聲明游標(biāo)變量*/在RETURN子句中可定義用戶自定義的RECORD類型。例如：DECLARE TYPEkc_cjISRECORD( kchnumber(4), kcmvarchar2(10), cjnumber(4,2)); TYPEkc_cjcurISREFCURSORRETURNkc_cj;此外，還可以聲明游標(biāo)變量作為函數(shù)和過程的參數(shù)。例如：DECLARE TYPExs_curISREFCURSORRETURNXSB%ROWTYPE; PRCEDUREopen_xs(xscurINOUTxs_cur)IS…游標(biāo)變量3．控制游標(biāo)變量在使用游標(biāo)變量時，要遵循如下步驟：打開→提取行數(shù)據(jù)→關(guān)閉。首先，使用OPEN打開游標(biāo)變量；然后使用FETCH從結(jié)果集中提取行，當(dāng)所有的行都處理完畢時，再使用CLOSE關(guān)閉游標(biāo)變量。OPEN語句與多行查詢的游標(biāo)相關(guān)聯(lián)，它執(zhí)行查詢并標(biāo)識結(jié)果集。語法格式為：OPEN{弱游標(biāo)變量名∣:強游標(biāo)變量名} FOR SELECT語句例如，要打開游標(biāo)變量xscur，使用語句如下：IFNOTxscur%ISOPENTHEN OPENxscurFORSELECT*FROMXSB;ENDIF;游標(biāo)變量在使用過程中，其他的OPEN語句可以為不同查詢打開相同的游標(biāo)變量。因此，在重新打開之前，建議不要關(guān)閉該游標(biāo)變量。游標(biāo)變量還可以作為參數(shù)傳遞給存儲過程。例如：CREATEPACKAGExs_dataAS … TYPExs_curISREFCURSORRETURNxs%ROWTYPE; PROCEDUREopen_xs(xscurINOUTxs_cur);END;CREATEPACKAGEBODYxs_dataAS … PROCEDUREopen_xs(xscurINOUTxs_cur)IS BEGIN OPENxscurFORSELECT*FROMXSB; END;END;第6章PL/SQL包01包的創(chuàng)建

1．以界面方式創(chuàng)建包2．以命令方式創(chuàng)建包包的創(chuàng)建

1．以界面方式創(chuàng)建包右擊myorcl連接的“程序包”節(jié)點，選擇“新建程序包”選項，彈出“創(chuàng)建PL/SQL程序包”對話框。在“名稱”欄中輸入包的名稱TEST_PACKAGE，單擊“確定”按鈕。在打開的主界面“TEST_PACKGE”窗口中完成此包代碼的編寫工作，完成后單擊“編譯以進行調(diào)試”按鈕完成包的創(chuàng)建。整個過程的操作步驟如圖。包的創(chuàng)建

2．以命令方式創(chuàng)建包用SQL命令創(chuàng)建包需要分別創(chuàng)建包頭和包體兩部分。（1）創(chuàng)建包頭語法格式如下：CREATE[ORREPLACE]PACKAGE[用戶方案名.]包名 /*包頭名稱*/ IS∣ASPL/SQL程序序列 /*定義過程、函數(shù)等*/在定義包頭時，要遵循以下規(guī)則。①包元素的位置可以任意安排。然而，在聲明部分，對象必須在引用前進行聲明。②包頭可以不對任何類型的元素進行說明。例如，包頭可以只帶過程和函數(shù)說明語句，而不聲明任何異常和類型。③對過程和函數(shù)的任何聲明都只是對子程序及其參數(shù)（如果有的話）進行描述，而不帶任何代碼的說明，實現(xiàn)代碼只能在包體中。包的創(chuàng)建

（2）創(chuàng)建包體語法格式如下：CREATE[ORREPLACE]PACKAGEBODY[用戶方案名.]包名 IS∣ASPL/SQL程序序列說明：包體中的PL/SQL程序序列部分可以是游標(biāo)、函數(shù)、過程的具體定義。包體是一個獨立于包頭的數(shù)據(jù)字典對象。包體只能在包頭完成編譯后才能進行編譯。（3）刪除包如果只是刪除包體，則使用命令如下：DROPPACKAGEBODY包名;如果要同時刪除包頭和包體，則使用命令如下：DROPPACKAGE包名;包的創(chuàng)建

【例6.34】

定義一個包頭，為后面的示例做準備。其中，代碼段如下：TYPEtab_02ISRECORD( itnum_1varchar2(1), itnum_2varchar2(1));【例6.35】

應(yīng)用前面統(tǒng)計全體學(xué)生平均成績的函數(shù)創(chuàng)建包TEST_PACKAGE。（1）包頭部分CREATEORREPLACEPACKAGEtest_package IS FUNCTIONaverage(cnumINchar) RETURNNUMBER;END;包的創(chuàng)建

（2）包體部分CREATEORREPLACEPACKAGEBODYtest_package IS FUNCTIONaverage(cnumINchar) RETURNNUMBER AS avgernumber; /*定義返回值變量*/ BEGIN SELECTAVG(成績)INTOavger FROMcjb WHERE課程號=cnum GROUPBY課程號; RETURN(avger); ENDaverage; END;該包體部分包括了實現(xiàn)包頭過程中前向說明的代碼。如果在包頭中沒有前向說明的對象（如異常），則可以在包體中直接引用。包的創(chuàng)建

包頭中聲明的任何對象都在其作用域中，并且可在其外部使用包名作為前綴對其進行引用。例如，可以在下面的PL/SQL塊中調(diào)用對象TEST_PACKAGE.average，其代碼如下：DECLARE numnumber;BEGIN num:=TEST_PACKAGE.average('101');

dbms_output.put_line(TO_CHAR(num));END;02包的初始化包的初始化當(dāng)?shù)谝淮握{(diào)用包子程序時，該包將進行初始化。也就是說，將該包從硬盤中讀入內(nèi)存，并啟動調(diào)用的子程序編譯代碼。這時系統(tǒng)為該包中定義的所有變量分配內(nèi)存單元。每個會話都有其打包變量的副本，以確保執(zhí)行同一包子程序的兩個對話使用不同的內(nèi)存單元。初始化代碼要在包第一次初始化時運行。為了實現(xiàn)這種功能，可以在包體中的所有對象之后加入一個初始化部分，語法格式為：CRETEORREPLACEPACKAGEBODY包名 IS∣AS … BEGIN

初始化代碼; END;03重

載重

載【例6.36】

把一個學(xué)生加入temp表中，通過重載實現(xiàn)兩種不同的添加方式：①只使用學(xué)生的學(xué)號，其他信息字段為空值；②使用學(xué)號和性別兩個字段添加學(xué)生信息。CREATEORREPLACEPACKAGETempPackage AS PROCEDUREAddStudent(v_xhINtemp.學(xué)號%TYPE); PROCEDUREAddStudent(v_xhINtemp.學(xué)號%TYPE,v_xbINtemp.性別%TYPE);END;CREATEORREPLACEPACKAGEBODYTempPackage AS PROCEDUREAddStudent(v_xhINtemp.學(xué)號%TYPE) IS BEGIN INSERTINTOtemp(學(xué)號) VALUES(v_xh); ENDAddStudent; PROCEDUREAddStudent ( v_xhINtemp.學(xué)號%TYPE, v_xbINtemp.性別%TYPE ) IS BEGIN INSERTINTOtemp(學(xué)號,性別) VALUES(v_xh,v_xb); ENDAddStudent;END;重

載增加學(xué)生信息，執(zhí)行語句如下：BEGINTempPackage.AddStudent('150010');TempPackage.AddStudent('150011','女');END;打開數(shù)據(jù)庫的temp表，可看到如圖所示的執(zhí)行結(jié)果。重

載同樣的操作可以通過不同類型的參數(shù)實現(xiàn)，可見重載是非常有用的技術(shù)。但是，重載又受到下列限制。（1）如果兩個子程序的參數(shù)僅在名稱和模式上不同，則這兩個子程序不能重載。例如，下面的兩個存儲過程就不能重載：PROCEDUREoverloadMe(p_theparameterINnumber);PROCEDUREoverloadMe(p_theparameterOUTnumber);（2）不能僅根據(jù)兩個子程序不同的返回類型對其進行重載。如下面兩個函數(shù)就不能重載：FUNCTIONoverloadMeTooRETURNDATE;FUNCTIONoverloadMeTooRETURNNUMBER;（3）重載子程序的參數(shù)類族（typefamily）必須不同。如由于CHAR和VARCHAR2屬于同一類族，故下面的兩個存儲過程也不能重載：PROCEDUREOverloadChar(p_theparameterINCHAR);PROCEDUREOverloadVarchar(p_theparameterINVARCHAR2);04Oracle11g數(shù)據(jù)庫的內(nèi)置包Oracle11g數(shù)據(jù)庫的內(nèi)置包Oracle11g數(shù)據(jù)庫提供了若干具有特殊功能的內(nèi)置包，簡述如下。（1）（2）（3）（4）（5）（6）DBMS_ALERT包：用于數(shù)據(jù)庫報警，允許會話間通信。DBMS_JOB包：用于任務(wù)調(diào)度服務(wù)。DBMS_LOB包：用于大型對象操作。DBMS_PIPE包：用于數(shù)據(jù)庫管道，允許會話間通信。DBMS_SQL包：用于執(zhí)行動態(tài)SQL。UTL_FILE包：用于文本文件的輸入與輸出。第6章PL/SQL集合01聯(lián)合數(shù)組1．給元素賦值2．輸出元素聯(lián)合數(shù)組聯(lián)合數(shù)組是具有Oracle11g數(shù)據(jù)庫的數(shù)據(jù)類型或用戶自定義的記錄/對象類型的一維體，類似于C語言中的二維數(shù)組。定義聯(lián)合數(shù)組的語法格式為：TYPE聯(lián)合數(shù)組名 IS TABLEOF數(shù)據(jù)類型INDEXBYBINARY_INTEGER;下面的代碼定義了一個聯(lián)合數(shù)組類型：TYPExs_name IS TABLEOFxsb.姓名%TYPE INDEXBYBINARY_INTEGER; /*聲明類型*/ v_namexs_name; /*聲明變量*/在聲明了類型和變量后，就可以使用聯(lián)合數(shù)組表中的單個元素，其語句為：v_name(index)其中index是指表中第幾個元素，其數(shù)據(jù)類型屬于BINARY_INTEGER類型。聯(lián)合數(shù)組1．給元素賦值可以使用以下語句給表中的元素賦值：BEGIN v_name(1):='韓許'; v_name(2):='陳俊';END;在聯(lián)合數(shù)組中下面的元素賦值也是合法的：BEGIN v_name(1):='韓許'; v_name(-2):='陳俊'; v_name(5):='朱珠';END;聯(lián)合數(shù)組的元素個數(shù)只受BINARY_INTEGER類型的限制，即index的范圍為-2147483647～+2147483647。因此只要在此范圍內(nèi)給元素賦值都是合法的。聯(lián)合數(shù)組2．輸出元素需要注意的是，在調(diào)用每個聯(lián)合數(shù)組的元素之前，都必須先給該元素賦值。【例6.37】

創(chuàng)建聯(lián)合數(shù)組并輸出其元素。DECLARE TYPEstudytab ISTABLEOFVARCHAR2(20)INDEXBYBINARY_INTEGER; v_studytabstudytab;BEGIN FORv_countIN1..5LOOP v_studytab(v_count):=v_count*10; ENDLOOP; FORv_countIN1..5LOOP DBMS_OUTPUT.PUT_LINE(v_studytab(v_count)); ENDLOOP;END;輸出結(jié)果如圖。聯(lián)合數(shù)組如果將第二個FOR循環(huán)中的循環(huán)范圍改為1..6，由于v_studytab(6)元素沒有賦值，因此系統(tǒng)會出現(xiàn)錯誤信息，如圖所示。02嵌

套

表1．嵌套表初始化2．元素有序性嵌

套

表嵌套表的聲明和聯(lián)合數(shù)組的聲明十分類似，語法格式為：TYPE嵌套表名 IS TABLEOF數(shù)據(jù)類型[NOTNULL]1．嵌套表初始化嵌套表的初始化與聯(lián)合數(shù)組的初始化完全不同。在聲明了類型之后，再聲明一個聯(lián)合數(shù)組變量類型，如果沒有給該表賦值，那么該表就是一個空的聯(lián)合數(shù)組。【例6.38】

嵌套表的初始化。DECLARE TYPEstudytab ISTABLEOFVARCHAR(20); v_studytabstudytab:=studytab('Tom','Jack','Rose');BEGIN FORv_countIN1..3LOOP DBMS_OUTPUT.PUT_LINE(v_studytab(v_count)); ENDLOOP;END;以上是嵌套表正確初始化的過程，系統(tǒng)輸出結(jié)果如圖。嵌

套

表2．元素有序性嵌套表與聯(lián)合數(shù)組十分相似，只是嵌套表在結(jié)構(gòu)上是有序的，而聯(lián)合數(shù)組是無序的。如果給一個嵌套表賦值，表元素的index將會從1開始依次遞增?！纠?.39】

嵌套表元素的有序性演示。DECLARE TYPEnumtab ISTABLEOFNUMBER(4); v_numnumtab:=numtab(1,3,4,5,7,9,11);BEGIN FORv_countIN1..7LOOP DBMS_OUTPUT.PUT_LINE('v_num('||v_count||')='||v_num(v_count)); ENDLOOP;END;輸出結(jié)果如圖。03可變數(shù)組可變數(shù)組定義可變數(shù)組的語法格式為：TYPE可變數(shù)組名 IS {VARRAY∣VARYINGARRAY}(元素個數(shù)最大值) OF數(shù)組元素類型[NOTNULL]說明：可變數(shù)組的“可變”是指當(dāng)定義了數(shù)組的最大上限時，數(shù)組元素的個數(shù)可以在這個最大上限內(nèi)變化，但是不得超過最大上限。下面是一個合法的可變數(shù)組聲明：DECLARE TYPEdates ISVARRAY(7)OFVARCHAR2(10); TYPEmonths ISVARRAY(12)OFVARCHAR2(10);可變數(shù)組【例6.40】

可變數(shù)組的初始化演示。DECLARE TYPEdates ISVARRAY(7)OFVARCHAR2(10); v_datesdates:=dates('Monday','Tuesday','Wesdnesday');BEGIN DBMS_OUTPUT.PUT_LINE(v_dates(1)); DBMS_OUTPUT.PUT_LINE(v_dates(2)); DBMS_OUTPUT.PUT_LINE(v_dates(3));END;輸出結(jié)果如圖。04集合的屬性和方法1．COUNT屬性2．DELETE方法3．EXISTS方法4．EXTEND方法5．LIMIT屬性6．FIRST/LAST屬性7．NEXT/PRIOR方法8．TRIM方法集合的屬性和方法1．COUNT屬性COUNT屬性用來返回集合中的數(shù)組元素個數(shù)?！纠?.41】

統(tǒng)計3種集合類型的元素個數(shù)。DECLARE TYPEnameISTABLEOFVARCHAR2(20)INDEXBYBINARY_INTEGER; TYPEpwdISTABLEOFVARCHAR2(20); TYPEdatesISVARRAY(7)OFVARCHAR2(20); v_namename; v_pwdpwd:=pwd('10000','12345','22','yes','no'); v_datesdates:=dates('Monday','Sunday');BEGIN v_name(1):='Tom'; v_name(-1):='Jack'; v_name(4):='Rose'; DBMS_OUTPUT.PUT_LINE('Theindex_bycountis:'||v_name.count); DBMS_OUTPUT.PUT_LINE('Thenestedcountis:'||v_pwd.count); DBMS_OUTPUT.PUT_LINE('Thevarraycountis:'||v_dates.count);END;輸出結(jié)果如圖。集合的屬性和方法2．DELETE方法DELETE方法用來刪除集合中的一個或多個元素。需要注意的是，由于DELETE方法執(zhí)行刪除操作的大小固定，故對于可變數(shù)組來說就沒有DELETE方法。DELETE方法有以下3種形式。（1）DELETE：不帶參數(shù)的DELETE方法，即將整個集合刪除。（2）DELETE(x)：將集合表中第x個位置的元素刪除。（3）DELETE(x,y)：將集合表中從第x個位置到第y個位置之間的所有元素刪除。集合的屬性和方法【例6.42】

使用DELETE方法的演示。DECLARE TYPEpwdISTABLEOFVARCHAR2(20); v_pwdpwd:=pwd('10000','12345','22','yes','no');BEGIN DBMS_OUTPUT.PUT_LINE('Theoriginaltablecountis:'); DBMS_OUTPUT.PUT_LINE(v_pwd.count); v_pwd.delete(4); DBMS_OUTPUT.PUT_LINE('Afterdeleteaelement,tablecountis:'); DBMS_OUTPUT.PUT_LINE(v_pwd.count); v_pwd.delete(6,8); DBMS_OUTPUT.PUT_LINE('Afterdeletesomeelement,tablecountis:'); DBMS_OUTPUT.PUT_LINE(v_pwd.count);END;輸出結(jié)果如圖。集合的屬性和方法3．EXISTS方法EXISTS方法是用來判斷集合中元素是否存在的，語法格式為：EXISTS(x)即判斷位于x處的元素是否存在，如果存在則返回TRUE；如果x大于集合的最大范圍，則返回FALSE。4．EXTEND方法EXTEND方法用來將元素添加到集合的末端，具體形式有以下幾種。（1）（2）（3）EXTEND：不帶參數(shù)的EXTEND是將一個NULL元素添加到集合的末端。EXTEND(x)：將x個NULL元素添加到集合的末端。EXTEND(x,y)：將x個位于y的元素添加到集合的末端。集合的屬性和方法【例6.43】

使用EXTEND方法。DECLARE TYPEpwdISTABLEOFVARCHAR2(20); v_pwdpwd:=pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left','Football'); v_countnumber;BEGIN v_count:=v_pwd.LAST; DBMS_OUTPUT.PUT_LINE('v_pwd('||v_count||'):'||v_pwd(v_count)); v_pwd.EXTEND(2,2); /*向集合末端添加兩個“12345”元素*/ v_count:=v_pwd.LAST; DBMS_OUTPUT.PUT_LINE('v_pwd('||v_count||'):'||v_pwd(v_count)); v_pwd.EXTEND(2); /*向集合末端添加兩個NULL元素*/ v_count:=v_pwd.LAST; v_pwd(v_count):='Basketball'; /*為末尾的元素賦值*/ DBMS_OUTPUT.PUT_LINE('v_pwd('||v_count||'):'||v_pwd(v_count));END;輸出結(jié)果如圖。集合的屬性和方法5．LIMIT屬性LIMIT屬性用于返回集合中的最大元素個數(shù)。由于嵌套表沒有上限，所以當(dāng)嵌套表使用LIMIT屬性時，總是返回NULL?！纠?.44】

使用LIMIT屬性。DECLARE TYPEpwdISTABLEOFVARCHAR2(20); v_pwdpwd:=pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left','Football'); TYPEnameISVARRAY(20)OFVARCHAR2(20); v_namename:=name('10000','12345','22','yes','no','OK','All','Hello','Right','Left','Football');BEGIN DBMS_OUTPUT.PUT_LINE('Thenestedtablelimitis:'||v_pwd.LIMIT); DBMS_OUTPUT.PUT_LINE('Thevarraytablelimitis:'||v_name.LIMIT);END;輸出結(jié)果如圖。集合的屬性和方法6．FIRST/LAST屬性FIRST屬性用來返回集合第一個元素的序列號，LAST屬性則是返回集合最后一個元素的序列號。7．NEXT/PRIOR方法使用這兩個方法時，后面都會跟一個參數(shù)。其中，NEXT(x)方法返回位置為x處元素后面的那個元素；PRIOR(X)方法返回x處元素前面的那個元素。【例6.45】

通常NEXT/PRIOR方法與FIRST/LAST屬性一起使用，共同進行循環(huán)處理。DECLARE TYPEpwdISTABLEOFVARCHAR2(20); v_pwdpwd:=pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left','Football'); v_countinteger;BEGIN v_count:=v_pwd.FIRST; WHILEv_count<=v_pwd.LASTLOOP DBMS_OUTPUT.PUT_LINE(v_pwd(v_count)); /*循環(huán)輸出pwd的內(nèi)容*/ v_count:=v_pwd.NEXT(v_count); ENDLOOP;END;輸出結(jié)果如圖。集合的屬性和方法8．TRIM方法TRIM方法用來刪除集合末端的元素，其具體形式如下。（1）TRIM方法：從集合的末端刪除一個元素。（2）TRIM(x)方法：從集合的末端刪除x個元素，其中x要小于集合的COUNT值。第7章存儲過程和觸發(fā)器存儲過程存儲過程它存放在數(shù)據(jù)字典中，可以在不同用戶和應(yīng)用程序之間共享，并可實現(xiàn)程序的優(yōu)化和重用。存儲過程的優(yōu)點如下。（1）存儲過程在服務(wù)器端運行，且執(zhí)行速度快。（2）存儲過程執(zhí)行一次后，代碼就駐留在高速緩存中，以后再操作時，只需從高速緩存中調(diào)用已編譯代碼執(zhí)行即可，從而提高了系統(tǒng)性能。（3）確保數(shù)據(jù)庫的安全。可以不授權(quán)用戶直接訪問應(yīng)用程序中的一些表，而是授權(quán)用戶執(zhí)行訪問這些表的存儲過程。非表的授權(quán)用戶除非通過存儲過程，否則就不能訪問這些表。（4）自動完成需要預(yù)先執(zhí)行的任務(wù)。存儲過程可以在系統(tǒng)啟動時自動執(zhí)行，而不必在系統(tǒng)啟動后再進行手工操作，從而大大方便了用戶的使用，可以自動完成一些需要預(yù)先執(zhí)行的任務(wù)。01存儲過程的創(chuàng)建

1．以命令方式創(chuàng)建存儲過程2．以界面方式創(chuàng)建存儲過程存儲過程的創(chuàng)建

1．以命令方式創(chuàng)建存儲過程創(chuàng)建存儲過程使用CREATEPROCEDURE語句，語法格式為：CREATE[ORREPLACE]PROCEDURE過程名 /*定義過程名*/ [(參數(shù)名參數(shù)類型數(shù)據(jù)類型[DEFAULT默認值][,…n])] /*定義參數(shù)類型及屬性*/{IS|AS} [變量聲明] /*變量聲明部分*/ BEGIN

過程體 /*PL/SQL過程體*/ END[過程名][;]相關(guān)參數(shù)說明如下。（1）過程名：存儲過程名稱要符合標(biāo)識符規(guī)則，并且在所屬方案中必須是唯一的。（2）參數(shù)名：存儲過程的參數(shù)名也要符合標(biāo)識符規(guī)則，創(chuàng)建過程時，可以聲明一個或多個參數(shù)，執(zhí)行過程時應(yīng)提供相對應(yīng)的參數(shù)。（3）DEFAULT：指定過程中IN參數(shù)的默認值，且默認值必須是常量。（4）過程體：表示包含PL/SQL語句塊。在存儲過程的定義體中，不能使用下列對象創(chuàng)建語句：CREATEVIEWCREATEDEFAULTCREATERULECREATEPROCEDURECREATETRIGGER存儲過程的創(chuàng)建

【例7.1】

創(chuàng)建一個簡單的存儲過程，輸出helloworld。CREATEPROCEDUREprocASBEGIN DBMS_OUTPUT.PUT_LINE('helloworld');END;【例7.2】

創(chuàng)建存儲過程，計算指定學(xué)生的總學(xué)分。CREATEORREPLACEPROCEDUREtotalcredit (xhINvarchar2)AS xfnumber;BEGIN SELECT總學(xué)分

INTOxf FROMxsb WHERE學(xué)號=xhANDrownum=1; DBMS_OUTPUT.PUT_LINE(xf);END;存儲過程的創(chuàng)建

【例7.3】

計算某專業(yè)總學(xué)分大于50分的人數(shù)，該存儲過程使用了一個輸入（IN）參數(shù)和一個輸出（OUT）參數(shù)。CREATEORREPLACEPROCEDUREcount_grade (zyINchar,person_numOUTnumber)ASBEGIN SELECTCOUNT(學(xué)號) INTOperson_num FROMxsb WHERE專業(yè)=zyAND總學(xué)分>50;END;存儲過程的創(chuàng)建

2．以界面方式創(chuàng)建存儲過程如果要通過界面方式定義上面的存儲過程count_grade，其步驟如下。（1）啟動SQLDeveloper，選擇myorcl連接的“過程”節(jié)點，右擊選擇“新建過程”選項進入“創(chuàng)建PL/SQL過程”對話框，如圖。存儲過程的創(chuàng)建

（2）在“名稱”文本框中輸入存儲過程的名稱，單擊按鈕添加一個參數(shù)，在“參數(shù)”選項頁的“Name”欄中輸入各參數(shù)名稱，在“Type”欄中選擇參數(shù)的類型，在“Mode”欄中選擇參數(shù)的模式，在“DefaultValue”欄中輸入?yún)?shù)默認值（如果有的話）。（3）單擊“確定”按鈕，在出現(xiàn)的“COUNT_GRADE”過程的編輯框中編寫過程語句塊，如圖7.2所示，單擊“編譯以進行調(diào)試”按鈕完成過程的創(chuàng)建。02存儲過程的調(diào)用存儲過程的調(diào)用調(diào)用存儲過程一般使用EXEC語句，語法格式為：[{EXEC|EXECUTE}]過程名 [([參數(shù)名=]實參|@實參變量[,…n])][;]說明：EXEC是EXECUTE的縮寫，參數(shù)名為CREATEPROCUDURE定義的參數(shù)名稱?！纠?.4】

調(diào)用【例7.1】中的存儲過程proc。EXECproc;或BEGIN proc;END;輸出結(jié)果均為：“helloworld”。存儲過程的調(diào)用【例7.5】

從XSCJ數(shù)據(jù)庫的XSB表中查詢某人的總學(xué)分，并根據(jù)總學(xué)分寫評語。CREATEORREPLACEPROCEDUREupdate_info(xhinchar)AS xfnumber;BEGIN SELECT總學(xué)分INTOxf FROMxsb WHERE學(xué)號=xhANDROWNUM=1; IFxf>50THEN UPDATExsbSET備注='三好學(xué)生'WHERE學(xué)號=xh; ENDIF; IFxf<42THEN UPDATExsbSET備注='學(xué)分未修滿'WHERE學(xué)號=xh; ENDIF;END;執(zhí)行存儲過程update_info：EXECupdate_info(xh=>'151242');執(zhí)行結(jié)果如圖。存儲過程的調(diào)用【例7.6】

統(tǒng)計XSB表中男女學(xué)生的人數(shù)。CREATEORREPLACEPROCEDUREcount_number (sexINchar,numOUTnumber)ASBEGIN IFsex='男'THEN SELECTCOUNT(性別)INTOnum FROMxsb WHERE性別='男'; ELSE SELECTCOUNT(性別)INTOnum FROMxsb WHERE性別='女'; ENDIF;END;在調(diào)用過程count_number時，需要先定義OUT類型參數(shù)，其代碼如下：DECLARE girl_numnumber;BEGIN count_number('女',girl_num); DBMS_OUTPUT.PUT_LINE(girl_num);END;輸出結(jié)果為：8。03存儲過程的修改存儲過程的修改修改存儲過程和修改視圖一樣，雖然也有ALTERPROCEDURE語句，但它是用于重新編譯或驗證現(xiàn)有過程的。如果要修改過程定義，仍然使用CREATEORREPLACEPROCEDURE命令，語法格式一樣。其實，修改已有過程的本質(zhì)就是使用CREATEORREPLEACEPROCEDURE命令重新創(chuàng)建一個新的過程，只要保持名字與原來的過程相同即可。04存儲過程的刪除存儲過程的刪除當(dāng)某個過程不再需要時，應(yīng)將其刪除，以釋放它占用的內(nèi)存資源。刪除過程的語法格式為：DROPPROCEDURE[用戶方案名.]過程名;【例7.7】

刪除XSCJ數(shù)據(jù)庫中的count_number存儲過程。

DROPPROCEDUREcount_number;也可以使用界面方式刪除存儲過程，具體操作如圖。第7章存儲過程和觸發(fā)器觸

發(fā)

器01以命令方式創(chuàng)建觸發(fā)器1．創(chuàng)建DML觸發(fā)器2．創(chuàng)建替代觸發(fā)器3．創(chuàng)建系統(tǒng)觸發(fā)器以命令方式創(chuàng)建觸發(fā)器1．創(chuàng)建DML觸發(fā)器語法格式為：CREATE[ORREPLACE]TRIGGER[用戶方案名.]觸發(fā)器名 {BEFORE∣AFTER∣INSTEADOF} /*定義觸發(fā)動作*/ {DELETE|INSERT|UPDATE[OF列名[,…n]]} /*定義觸發(fā)器種類*/ [OR{DELETE|INSERT|UPDATE[OF列名[,…n]]}] ON{表名∣視圖名} /*在指定表或視圖中建立觸發(fā)器*/ [FOREACHROW[WHEN(條件表達式)]] PL/SQL語句塊相關(guān)參數(shù)說明如下。（1）觸發(fā)器名：觸發(fā)器與過程名和包的名字不一樣，它有單獨的名字空間，因此觸發(fā)器名可以和表名或過程名同名，但在同一個方案中的觸發(fā)器名不能相同。（2）BEFORE：觸發(fā)器在指定操作執(zhí)行前觸發(fā)，如BEFOREINSERT表示在向表中插入數(shù)據(jù)前激活觸發(fā)器。（3）AFTER：觸發(fā)器在指定操作都成功執(zhí)行后觸發(fā)，如AFTERINSERT表示向表中插入數(shù)據(jù)時激活觸發(fā)器。不能在視圖上定義AFTER觸發(fā)器。（4）INSTEADOF：指定創(chuàng)建替代觸發(fā)器，觸發(fā)器指定的事件不執(zhí)行，而執(zhí)行觸發(fā)器本身的操作。（5）DELETE|INSERT|UPDATE：指定一個或多個觸發(fā)事件，多個觸發(fā)事件之間用OR連接。（6）OF：指定在某列上應(yīng)用UPDATE觸發(fā)器，如果為多個列，則需要使用逗號分隔。（7）FOREACHROW：在觸發(fā)器定義中，如果未使用FOREACHROW子句則表示觸發(fā)器為語句級觸發(fā)器，觸發(fā)器在激活后只執(zhí)行一次，而不管這個操作將影響多少行。以命令方式創(chuàng)建觸發(fā)器有關(guān)DML觸發(fā)器，還有以下幾點說明。（1）創(chuàng)建觸發(fā)器的限制。創(chuàng)建觸發(fā)器有以下限制。①代碼大小。觸發(fā)器代碼大小必須小于32KB。②觸發(fā)器中有效語句可以包括DML語句，但不能包括DDL語句。③LONG、LONGRAW和LOB的限制如下。

不能插入數(shù)據(jù)到LONG或LONGRAW中。

來自LONG或LONGRAW的數(shù)據(jù)可以轉(zhuǎn)換成字符型（如char、varchar2），但是不能超過32KB。

使用LONG或LONGRAW不能聲明變量。

在LONG或LONGRAW列中不能使用:NEW和:OLD。

在LOB中的:NEW變量不能被修改。④

引用包變量的限制。（2）觸發(fā)器觸發(fā)次序。Oracle系統(tǒng)對事件的觸發(fā)是按照一定次序執(zhí)行的。①

執(zhí)行BEFORE語句級觸發(fā)器。②

對于受語句影響的每一行，執(zhí)行順序為：BEFORE行級觸發(fā)器→DML語句→AFTER行級觸發(fā)器。③

執(zhí)行AFTER語句級觸發(fā)器。以命令方式創(chuàng)建觸發(fā)器【例7.8】

創(chuàng)建一個表table1，其中只有一列a。在表上創(chuàng)建一個觸發(fā)器，每次插入操作時，將變量str的值設(shè)為“TRIGGERISWORKING”并顯示。創(chuàng)建表table1：CREATETABLEtable1(anumber);創(chuàng)建INSERT觸發(fā)器table1_insert：CREATEORREPLACETRIGGERtable1_insert AFTERINSERTONtable1DECLARE strchar(100):='TRIGGERISWORKING';BEGIN DBMS_OUTPUT.PUT_LINE(str);END;向table1中插入一行數(shù)據(jù)：INSERTINTOtable1VALUES(10);輸出結(jié)果如圖。以命令方式創(chuàng)建觸發(fā)器【例7.9】

在XSCJ數(shù)據(jù)庫中增加一個日志表XSB_HIS，表結(jié)構(gòu)和XSB表相同，用來存放從XSB表中刪