Oracle第7章存儲(chǔ)過程操作

第7章存儲(chǔ)過程操作主編：劉志成?本章學(xué)習(xí)導(dǎo)航本章學(xué)習(xí)導(dǎo)航本章學(xué)習(xí)要點(diǎn)（1）存儲(chǔ)過程概述。（2）OEM創(chuàng)建、調(diào)用、查看、修改和刪除存儲(chǔ)過程。（3）PL/SQL創(chuàng)建、調(diào)用、查看、修改和刪除存儲(chǔ)過程。（4）創(chuàng)建、調(diào)用和刪除函數(shù)。（5）定義包頭、包體。（6）包中定義函數(shù)和存儲(chǔ)過程。建議課時(shí)：12課時(shí)課堂案例1——使用PL/SQL編程常量和變量--常量常量是指在程序運(yùn)行期間其值不能改變的量。

【例1-1】定義常量PI（3.14159）。--設(shè)置打開控制臺(tái)輸出SETSERVEROUTPUTON--聲明一個(gè)變量或常量DECLAREPICONSTANTNUMBER(6,5):=3.14159;BEGIN--輸出指定變量或常量的值

DBMS_OUTPUT.PUT_LINE('PI='||PI);END;

其中，PUT_LINE過程中使用的“||”用于連接輸出內(nèi)容的兩部分

課堂案例1——使用PL/SQL編程常量和變量--變量變量是指由程序讀取或賦值的存儲(chǔ)單元，用于臨時(shí)存儲(chǔ)數(shù)據(jù)，變量中的數(shù)據(jù)可以隨著程序的運(yùn)行而發(fā)生變化。每個(gè)變量都必須有一個(gè)特定的數(shù)據(jù)類型，可以是系統(tǒng)數(shù)據(jù)類型，也可以是自定義數(shù)據(jù)類型。

【例1-2】編寫計(jì)算圓面積的PL/SQL塊。SETSERVEROUTPUTONDECLAREPICONSTANTNUMBER(6,5):=3.14159;--聲明兩個(gè)變量并賦初值

v_radiuFLOAT:=2;v_areaFLOAT;BEGINv_area:=PI*v_radiu*v_radiu;DBMS_OUTPUT.PUT_LINE('Area='||v_area);END;課堂案例1——使用PL/SQL編程條件結(jié)構(gòu)-IF結(jié)構(gòu)【例1-3】使用簡單IF結(jié)構(gòu)判斷一個(gè)整數(shù)的奇偶性。SETSERVEROUTPUTONDECLAREv_numberINTEGER:=518;BEGIN

IFMOD(v_number,2)=0THENDBMS_OUTPUT.PUT_LINE(v_number||'是一個(gè)偶數(shù)');

ELSEDBMS_OUTPUT.PUT_LINE(v_number||'是一個(gè)奇數(shù)');ENDIF;END;課堂案例1——使用PL/SQL編程條件結(jié)構(gòu)-IF結(jié)構(gòu)【例1-4】使用復(fù)雜IF結(jié)構(gòu)輸出3個(gè)整數(shù)之中的最大者。課堂案例1——使用PL/SQL編程條件結(jié)構(gòu)-CASE結(jié)構(gòu)【例1-5】使用單一選擇符進(jìn)行等值比較的CASE結(jié)構(gòu)將百分制成績轉(zhuǎn)換為5分制成績。課堂案例1——使用PL/SQL編程條件結(jié)構(gòu)-CASE結(jié)構(gòu)【例1-6】使用多種條件進(jìn)行非等值比較的CASE結(jié)構(gòu)將百分制成績轉(zhuǎn)換為5分制成績。課堂案例1——使用PL/SQL編程循環(huán)結(jié)構(gòu)-While循環(huán)【例1-7】使用WHILE循環(huán)求1到100的所有正整數(shù)之和。SETSERVEROUTPUTONDECLARE iINTEGER:=1; sINTEGER:=0;BEGIN

WHILEi<=100LOOP s:=s+i; i:=i+1;

ENDLOOP; DBMS_OUTPUT.PUT_LINE('1+2+...+100='||s);END;課堂案例1——使用PL/SQL編程循環(huán)結(jié)構(gòu)-Loop循環(huán)【例1-8】使用LOOP循環(huán)求1到100的所有正整數(shù)之和。SETSERVEROUTPUTONDECLARE iINTEGER:=1; sINTEGER:=0;BEGIN

LOOP s:=s+i; i:=i+1; EXITWHENi>100;

ENDLOOP; DBMS_OUTPUT.PUT_LINE('1+2+...+100='||s);END;課堂案例1——使用PL/SQL編程循環(huán)結(jié)構(gòu)-For循環(huán)使用FOR循環(huán)不需要顯式聲明循環(huán)控制變量的類型，而由PL/SQL隱式提供。默認(rèn)情況下，循環(huán)控制變量從下限值開始，每次循環(huán)結(jié)束后自動(dòng)增加1，直至超過上限值為止；若指定REVERSE參數(shù)，則循環(huán)控制變量從上限值開始，每次循環(huán)結(jié)束后自動(dòng)減1，直至低于下限值為止。

課堂案例1——使用PL/SQL編程循環(huán)結(jié)構(gòu)-For循環(huán)【例1-9】使用不帶REVERSE參數(shù)的FOR循環(huán)求1到100的所有正整數(shù)之和?！纠?-10】使用帶REVERSE參數(shù)的FOR循環(huán)求1到100的所有正整數(shù)之和。課堂案例1——使用PL/SQL編程循環(huán)結(jié)構(gòu)-跳轉(zhuǎn)語句【例1-11】借助于GOTO跳轉(zhuǎn)語句輸出10以內(nèi)第一個(gè)能同時(shí)被2和3整除的正整數(shù)。SETSERVEROUTPUTONDECLAREnumINTEGER:=1;BEGINWHILEnum<=10LOOPIFMOD(num,2)=0ANDMOD(num,3)=0THEN

GOTOdisplay;ENDIF;num:=num+1;ENDLOOP;<<display>> DBMS_OUTPUT.PUT_LINE(num);END;課堂案例1——使用PL/SQL編程常用系統(tǒng)函數(shù)-數(shù)學(xué)函數(shù)課堂案例1——使用PL/SQL編程常用系統(tǒng)函數(shù)-數(shù)學(xué)函數(shù)【例1-12】測(cè)試常用數(shù)學(xué)函數(shù)的用法。SETSERVEROUTPUTONBEGINDBMS_OUTPUT.PUT_LINE('-8的絕對(duì)值為'||ABS(-8));DBMS_OUTPUT.PUT_LINE('8的3次冪為'||POWER(8,3));DBMS_OUTPUT.PUT_LINE('8的平方根為'||SQRT(8));DBMS_OUTPUT.PUT_LINE('3.14159四舍五入到小數(shù)點(diǎn)后3位為'||ROUND(3.14159,3));DBMS_OUTPUT.PUT_LINE('e='||EXP(1));DBMS_OUTPUT.PUT_LINE('大于或等于-32.5的最小整數(shù)為'||CEIL(-32.5));DBMS_OUTPUT.PUT_LINE('小于或等于-32.5的最大整數(shù)為'||FLOOR(-32.5));END;課堂案例1——使用PL/SQL編程常用系統(tǒng)函數(shù)-字符串函數(shù)課堂案例1——使用PL/SQL編程常用系統(tǒng)函數(shù)-字符串函數(shù)【例1-13】測(cè)試常用字符串函數(shù)的用法。SETSERVEROUTPUTONBEGINDBMS_OUTPUT.PUT_LINE('a的ACSCII值為'||ASCII('a'));DBMS_OUTPUT.PUT_LINE('ACSCII值97對(duì)應(yīng)的字符為'||CHR(97));DBMS_OUTPUT.PUT_LINE('字符串"HunanRailway"的長度為'||LENGTH('HunanRailway'));DBMS_OUTPUT.PUT_LINE('將字符串"HunanRailway"全部轉(zhuǎn)換為大寫形式為'||UPPER('HunanRailway'));DBMS_OUTPUT.PUT_LINE('將字符串"HunanRailway"全部轉(zhuǎn)換為小寫形式為'||LOWER('HunanRailway'));END;課堂案例1——使用PL/SQL編程常用系統(tǒng)函數(shù)-日期函數(shù)課堂案例1——使用PL/SQL編程常用系統(tǒng)函數(shù)-日期函數(shù)【例1-14】測(cè)試常用日期函數(shù)的用法。SETSERVEROUTPUTONBEGINDBMS_OUTPUT.PUT_LINE('當(dāng)前日期時(shí)間為'||SYSDATE);DBMS_OUTPUT.PUT_LINE('當(dāng)前月份的最后一天的日期為'||LAST_DAY(SYSDATE));DBMS_OUTPUT.PUT_LINE('字符串對(duì)應(yīng)日期'||TO_DATE('2007-5-24','YYYY-MM-DD'));DBMS_OUTPUT.PUT_LINE('兩個(gè)日期相差的月份'||MONTHS_BETWEEN('14-4月-99',SYSDATE));END;課堂案例1——使用PL/SQL編程常用系統(tǒng)函數(shù)-轉(zhuǎn)換函數(shù)課堂案例1——使用PL/SQL編程常用系統(tǒng)函數(shù)-轉(zhuǎn)換函數(shù)【例1-15】測(cè)試常用轉(zhuǎn)換函數(shù)的用法。SETSERVEROUTPUTONBEGINDBMS_OUTPUT.PUT_LINE('轉(zhuǎn)換為數(shù)據(jù)庫字符集的ASCII字符串為'||ASCIISTR('湖南鐵道'));DBMS_OUTPUT.PUT_LINE('將當(dāng)前日期轉(zhuǎn)換為字符串類型數(shù)據(jù)為'||CAST(SYSDATEASVARCHAR2));DBMS_OUTPUT.PUT_LINE('將字符串轉(zhuǎn)換為ROWID數(shù)據(jù)類型為'||CHARTOROWID('AAAAFdl/#$'));DBMS_OUTPUT.PUT_LINE('字符集轉(zhuǎn)換'||CONVERT('湖南鐵道','US7ASCII','WE8ISO8859P1'));END;課堂案例1——使用PL/SQL編程%TYPE變量Oracle9i以后的版本提供了%TYPE定義方法。這樣當(dāng)數(shù)據(jù)表的字段類型修改后，PL/SQL程序中相應(yīng)變量的類型也自動(dòng)修改?！纠?-16】使用%TYPE獲取查詢的商品的基本信息。SETSERVEROUTPUTONDECLARE

v_gIdGoods.g_ID%TYPE;v_gNameGoods.g_Name%TYPE;v_gPriceGoods.g_Price%TYPE;v_gNumberGoods.g_Number%TYPE;BEGINSELECTg_ID,g_Name,g_Price,g_NumberINTOv_gId,v_gName,v_gPrice,v_gNumberFROMSCOTT.GoodsWHEREg_ID='010003';dbms_output.put_line(v_gId||'/'||v_gName||'/'||v_gPrice||'/'||v_gNumber);END;

課堂案例1——使用PL/SQL編程%ROWTYPE變量與%TYPE類型類似，也可以在不確定查詢列的類型的情況下，使用%ROWTYPE類型的變量存儲(chǔ)查詢的一行數(shù)據(jù)

?！纠?-17】使用%ROWTYPE獲取查詢的商品基本信息。SETSERVEROUTPUTONDECLARE

v_GoodRecordGoods%ROWTYPE;BEGINSELECT*INTOv_GoodRecordFROMSCOTT.GoodsWHEREg_ID='010003';dbms_output.put_line(v_GoodRecord.g_ID);dbms_output.put_line(v_GoodRecord.g_Name);dbms_output.put_line(v_GoodRecord.g_Price);dbms_output.put_line(v_GoodRecord.g_Number);END;課堂案例1——使用PL/SQL編程異常處理為了提高應(yīng)用程序的健壯性，開發(fā)人員必須考慮程序可能出現(xiàn)的各種錯(cuò)誤，并進(jìn)行相應(yīng)的處理。在Oracle中，為了處理PL/SQL應(yīng)用程序的各種錯(cuò)誤，Oracle提供了三種類型的異常。（1）預(yù)定義異常：用于處理常見的Oracle錯(cuò)誤（2）非預(yù)定義異常：用于處理預(yù)定義異常所不能處理的Oracle錯(cuò)誤（3）自定義異常：用于處理于Oracle錯(cuò)誤無關(guān)的其他情況課堂案例1——使用PL/SQL編程異常處理—系統(tǒng)預(yù)定義異常課堂案例1——使用PL/SQL編程異常處理—系統(tǒng)預(yù)定義異?！纠?-18】對(duì)Goods表中的插入的重復(fù)商品號(hào)進(jìn)行異常處理（使用預(yù)定義異常）。SETSERVEROUTPUTONBEGININSERTINTOSCOTT.GoodsVALUES('010001','諾基亞6700Slide','01',1500,0.9,20,to_date('2009-06-01','yyyy-mm-dd'),'pImage/010001.gif','熱點(diǎn)','彩屏,1600萬色,TFT,240×320像素,2.2英寸');EXCEPTIONWHENDUP_VAL_ON_INDEXTHENdbms_output.put_line('捕獲到DUP_VAL_ON_INDEX異常');dbms_output.put_line('重復(fù)的商品編號(hào)');END;課堂案例1——使用PL/SQL編程異常處理—非預(yù)定義異常使用非預(yù)定義異常需要包括以下三個(gè)步驟：（1）在定義部分定義異常名；（2）在異常和Oracle錯(cuò)誤之間建立關(guān)聯(lián)（需要使用偽過程EXCEPTION_INIT）；（3）在異常處理部分捕捉并處理異常?！纠?-19】刪除商品類別表，并處理ORA-2292錯(cuò)誤（使用非預(yù)定義異常）。SETSERVEROUTPUTONDECLAREe_FKEXCEPTION;--1、定義部分

PRAGMAEXCEPTION_INIT(e_FK,-2292);--2、建立關(guān)聯(lián)關(guān)系BEGINDELETESCOTT.TypesWHEREt_Name='通信商品';EXCEPTIONWHENe_FKTHEN--3、捕捉處理

DBMS_OUTPUT.PUT_LINE('該類別已被使用');END;課堂案例1——使用PL/SQL編程異常處理—自定義異常使用自定義異常時(shí)，需要包括以下三個(gè)步驟：（1）需要在定義部分（DECLARE）定義異常；（2）再執(zhí)行部分（BEGIN）觸發(fā)異常（使用RAISE語句）；（3）在異常處理部分（EXCEPTION）捕捉并處理異常。

7.2存儲(chǔ)過程概述視圖特點(diǎn)在Oracle中，可以在數(shù)據(jù)庫中定義子程序，在子程序中將一些固定的操作集中起來，由Oracle數(shù)據(jù)庫服務(wù)器完成，以完成某個(gè)特定的功能。這種子程序稱為存儲(chǔ)過程（Proce-Dure）。使用存儲(chǔ)過程具有如下的優(yōu)點(diǎn)：（1）存儲(chǔ)過程在服務(wù)器端運(yùn)行，執(zhí)行速度快；（2）存儲(chǔ)過程執(zhí)行一次后駐留在Oracle數(shù)據(jù)庫服務(wù)器的高速Cache中，以后再次執(zhí)行存儲(chǔ)過程時(shí)，只需從高速Cache中調(diào)用已經(jīng)編譯好的代碼即可，從而提高了系統(tǒng)性能；（3）存儲(chǔ)過程確保了數(shù)據(jù)庫的安全。使用存儲(chǔ)過程，可以在禁止用戶直接訪問應(yīng)用程序中的某些數(shù)據(jù)表的情況下，授權(quán)執(zhí)行訪問這些數(shù)據(jù)表的存儲(chǔ)過程。（4）自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)。存儲(chǔ)過程可以設(shè)置為系統(tǒng)啟動(dòng)時(shí)自動(dòng)執(zhí)行，而不必在系統(tǒng)啟動(dòng)后再進(jìn)行手動(dòng)操作，從而方便了用戶的使用，可以自動(dòng)完成一些需要預(yù)先執(zhí)行的任務(wù)。課堂案例2—使用OEM管理存儲(chǔ)過程

掌握在OEM中創(chuàng)建存儲(chǔ)過程、執(zhí)行存儲(chǔ)過程的方法。案例學(xué)習(xí)目標(biāo)OEM中創(chuàng)建存儲(chǔ)過程、執(zhí)行存儲(chǔ)過程。案例知識(shí)要點(diǎn)課堂案例2—使用OEM管理存儲(chǔ)過程案例完成步驟添加標(biāo)題文字（1）啟動(dòng)OEM后，依次選擇“方案”、“程序”下的“過程”，進(jìn)入“過程”頁面

（2）單擊“創(chuàng)建”按鈕，進(jìn)入“創(chuàng)建過程”對(duì)話框，輸入新建存儲(chǔ)過程的名稱up_NameByID，編寫存儲(chǔ)過程的內(nèi)容

添加標(biāo)題文字教師演示講解課堂案例2—使用OEM管理存儲(chǔ)過程案例完成步驟-創(chuàng)建存儲(chǔ)過程添加標(biāo)題文字（1）啟動(dòng)OEM后，依次選擇“方案”、“程序”下的“過程”，進(jìn)入“過程”頁面

（2）單擊“創(chuàng)建”按鈕，進(jìn)入“創(chuàng)建過程”對(duì)話框，輸入新建存儲(chǔ)過程的名稱up_NameByID，編寫存儲(chǔ)過程的內(nèi)容

添加標(biāo)題文字教師演示講解課堂案例2—使用OEM管理存儲(chǔ)過程案例完成步驟-查看存儲(chǔ)過程添加標(biāo)題文字在OEM中，進(jìn)入指定方案的“過程”頁面，在過程列表中選擇要查看的過程，單擊“查看”按鈕，進(jìn)入“查看過程”頁面

添加標(biāo)題文字教師演示講解課堂案例2—使用OEM管理存儲(chǔ)過程案例完成步驟-修改存儲(chǔ)過程添加標(biāo)題文字（1）在OEM中，進(jìn)入指定方案的“過程”頁面，在過程列表中選擇要查看的過程，單擊“編輯”按鈕，進(jìn)入“編輯過程”頁面

添加標(biāo)題文字教師演示講解課堂案例2—使用OEM管理存儲(chǔ)過程案例完成步驟-刪除存儲(chǔ)過程添加標(biāo)題文字（1）在OEM中，進(jìn)入指定方案的“過程”頁面，在過程列表中選擇要查看的過程，單擊“刪除”按鈕，進(jìn)入“確認(rèn)刪除”頁面

（2）單擊“是”按鈕，刪除過程up_NameByID添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程

學(xué)習(xí)使用PL/SQL語句創(chuàng)建存儲(chǔ)過程、修改存儲(chǔ)過程、編譯存儲(chǔ)過程和執(zhí)行存儲(chǔ)過程的方法。案例學(xué)習(xí)目標(biāo)使用CREATE[ORREPLACE]PROCEDURE創(chuàng)建和修改存儲(chǔ)過程、使用ALTERPROCEDURE編譯存儲(chǔ)過程、執(zhí)行存儲(chǔ)過程的幾種方法。案例知識(shí)要點(diǎn)課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟添加標(biāo)題文字1．創(chuàng)建存儲(chǔ)過程使用PL/SQL創(chuàng)建存儲(chǔ)過程的基本語法格式為：

CREATE[ORREPLACE]PROCEDURE[用戶方案.]<存儲(chǔ)過程名> [(參數(shù)1參數(shù)模式數(shù)據(jù)類型[，…])] IS|AS [參數(shù)1數(shù)據(jù)類型[，…]] BEGIN PL/SQL語句

END[存儲(chǔ)過程名];2．執(zhí)行存儲(chǔ)過程使用PL/SQL執(zhí)行存儲(chǔ)過程的基本語法格式為：

[DECLARE

參數(shù)1數(shù)據(jù)類型[，…]] BEGIN [EXECUTE][用戶方案.]<存儲(chǔ)過程名>[(參數(shù)1[，…])]; END;添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟添加標(biāo)題文字【例3-1】創(chuàng)建簡單存儲(chǔ)過程，顯示當(dāng)前的系統(tǒng)時(shí)間。（1）定義存儲(chǔ)過程

CREATEORREPLACEPROCEDURESCOTT.up_CurrentTime AS BEGIN DBMS_OUTPUT.PUT_LINE(SYSDATE); ENDup_CurrentTime;（2）執(zhí)行存儲(chǔ)過程

BEGIN

SCOTT.up_CurrentTime(); END;添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟添加標(biāo)題文字【例3-2】通過存儲(chǔ)過程添加用戶記錄。（1）定義存儲(chǔ)過程CREATEORREPLACEPROCEDUREup_InsertUserASBEGININSERTINTOSCOTT.UsersVALUES('88','存儲(chǔ)過程','普通','storeproc');EXCEPTIONWHENDUP_VAL_ON_INDEXTHENDBMS_OUTPUT.PUT_LINE('重復(fù)的用編號(hào)');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('發(fā)生其他錯(cuò)誤');ENDup_InsertUser;（2）執(zhí)行存儲(chǔ)過程EXECup_InsertUser;（3）查詢Users表存儲(chǔ)過程執(zhí)行后，使用下列語句查看User表的記錄情況。SELECT*FROMUSERS;添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟添加標(biāo)題文字【例3-3】創(chuàng)建存儲(chǔ)過程，根據(jù)商品類別編號(hào)統(tǒng)計(jì)該類型所有商品的總數(shù)量（帶IN參數(shù)）。（1）定義存儲(chǔ)過程CREATEORREPLACEPROCEDUREup_CountByTid(tidinVARCHAR2)AStotalNUMBER;BEGINSELECTCOUNT(*)INTOtotalFROMSCOTT.GOODSWHEREt_ID=tid;DBMS_OUTPUT.PUT_LINE(total);END;（2）執(zhí)行存儲(chǔ)過程

BEGIN--up_CountByTid('01');up_CountByTid(tid=>'01');END;up_CountByTid('01')：位置表示法傳遞參數(shù)up_CountByTid(tid=>'01')：名稱表示法傳遞參數(shù)添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟添加標(biāo)題文字【例3-4】創(chuàng)建存儲(chǔ)過程，根據(jù)商品類別編號(hào)統(tǒng)計(jì)該類型所有商品的總數(shù)量，缺省情況下統(tǒng)計(jì)類別編號(hào)為“02”的商品的總數(shù)量（帶缺省值的IN參數(shù)）。（1）定義存儲(chǔ)過程CREATEORREPLACEPROCEDUREup_CountByTid(tidinVARCHAR2DEFAULT'02')AStotalNUMBER;BEGINSELECTCOUNT(*)INTOtotalFROMSCOTT.GOODSWHEREt_ID=tid;DBMS_OUTPUT.PUT_LINE(total);END;添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟添加標(biāo)題文字【例3-5】創(chuàng)建存儲(chǔ)過程，根據(jù)商品的編號(hào)獲得商品的名稱和類別編號(hào)（帶IN和OUT參數(shù)）。（1）定義存儲(chǔ)過程CREATEORREPLACEPROCEDUREup_GetByID(gidinVARCHAR2,gnameoutGOODS.g_Name%TYPE,tidoutGOODS.t_ID%TYPE)ASBEGINSELECTg_Name,t_IDINTOgname,tidFROMSCOTT.GoodsWHEREg_ID=gid;EXCEPTIONWHENNO_DATA_FOUNDTHENgname:=null;tid:=null;ENDup_GetByID;添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟添加標(biāo)題文字（2）執(zhí)行存儲(chǔ)過程--調(diào)用帶輸出參數(shù)的存儲(chǔ)過程variablev_namevarchar2(50);variablev_idvarchar2(2);execup_GetByID('020001',:v_name,:v_id);printv_name;printv_id;添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟添加標(biāo)題文字【例3-5】編寫存儲(chǔ)過程實(shí)現(xiàn)兩個(gè)數(shù)交換，并在比較兩個(gè)數(shù)的大小中調(diào)用該存儲(chǔ)過程（帶INOUT參數(shù)）

添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟—查看存儲(chǔ)過程添加標(biāo)題文字【例3-6】查看用戶方案SCOTT的存儲(chǔ)過程up_GetByID的信息。

DESCSCOTT.up_GetByID;添加標(biāo)題文字教師演示講解課堂案例3—使用PL/SQL管理存儲(chǔ)過程案例完成步驟—?jiǎng)h除存儲(chǔ)過程添加標(biāo)題文字【例3-7】刪除用戶方案SCOTT的存儲(chǔ)過程up_CurrentTime。

DROPPROCEDURESCOTT.up_CurrentTime;添加標(biāo)題文字教師演示講解課堂案例4—管理函數(shù)學(xué)習(xí)在SQLDeveloper和PL/SQL中創(chuàng)建函數(shù)、調(diào)用函數(shù)、刪除函數(shù)的方法。案例學(xué)習(xí)目標(biāo)SQLDeveloper中創(chuàng)建函數(shù)、SQLDeveloper刪除函數(shù)、PL/SQL創(chuàng)建函數(shù)、PL/SQL調(diào)用函數(shù)、PL/SQL刪除函數(shù)。案例知識(shí)要點(diǎn)課堂案例4—管理函數(shù)

案例完成步驟—?jiǎng)?chuàng)建函數(shù)添加標(biāo)題文字函數(shù)（Function）與存儲(chǔ)過程類似，也是組成一個(gè)子程序的一組PL/SQL語句。函數(shù)接受0個(gè)或多個(gè)輸入?yún)?shù)，僅返回一個(gè)值，返回值的數(shù)據(jù)類型在創(chuàng)建函數(shù)時(shí)定義。1．使用SQLDeveloper創(chuàng)建函數(shù)（1）在SQLDeveloper中右擊Functions選項(xiàng)，從快捷菜單中選擇“NewFunction”項(xiàng)，將開始創(chuàng)建函數(shù)添加標(biāo)題文字教師演示講解課堂案例4—管理函數(shù)

案例完成步驟—?jiǎng)?chuàng)建函數(shù)添加標(biāo)題文字（2）在打開的“CreatePL/SQLFunction”對(duì)話框內(nèi)，指定用戶方案為SCOTT，設(shè)置函數(shù)名稱為fn_CountTypes，創(chuàng)建一個(gè)統(tǒng)計(jì)商品種類的函數(shù)

添加標(biāo)題文字教師演示講解

課堂案例4—管理函數(shù)

案例完成步驟—?jiǎng)?chuàng)建函數(shù)添加標(biāo)題文字（3）單擊“確定”按鈕，開始編輯函數(shù)的定義，以實(shí)現(xiàn)統(tǒng)計(jì)商品種類的函數(shù)。添加標(biāo)題文字教師演示講解

CREATEORREPLACEFUNCTIONfn_CountTypesRETURNNUMBERASCOUNTERNUMBER;BEGIN SELECT COUNT(t_ID)INTOCOUNTER FROM SCOTT.GOODS; RETURN COUNTER;ENDfn_CountTypes;課堂案例4—管理函數(shù)

案例完成步驟—執(zhí)行函數(shù)添加標(biāo)題文字（4）展開Functions選項(xiàng)，右擊函數(shù)fn_CountTypes，從快捷菜單中選擇“Run”項(xiàng)，打開運(yùn)行函數(shù)對(duì)話框

。添加標(biāo)題文字教師演示講解

課堂案例4—管理函數(shù)

案例完成步驟—PL/SQL創(chuàng)建函數(shù)添加標(biāo)題文字【例4-1】在用戶方案SCOTT中創(chuàng)建函數(shù)fn_TOTALVALUE，它根據(jù)客戶編號(hào)查詢?cè)摽蛻舻挠唵慰偨痤~。CREATEORREPLACEFUNCTIONSCOTT.fn_TOTALVALUE (cidSCOTT.ORDERS.c_ID%TYPE) RETURN NUMBER AS T_VALUE NUMBER; BEGIN SELECT SUM(d_Price*d_Number)INTOT_VALUE FROMSCOTT.ORDERDETAILSOD JOINSCOTT.ORDERSO ONOD.o_ID=O.o_ID WHEREc_ID=cid; RETURN T_VALUE; ENDfn_TOTALVALUE;添加標(biāo)題文字教師演示講解

課堂案例4—管理函數(shù)

案例完成步驟—調(diào)用函數(shù)添加標(biāo)題文字【例4-2】調(diào)用用戶方案SCOTT中的函數(shù)

DECLAREcidSCOTT.ORDERS.c_ID%TYPE;BEGINcid:='C0001';DBMS_OUTPUT.PUT_LINE(cid||':'||SCOTT.fn_TOTALVALUE(cid));END;添加標(biāo)題文字教師演示講解

課堂案例4—管理函數(shù)

案例完成步驟—?jiǎng)h除函數(shù)添加標(biāo)題文字1．使用SQLDeveloper刪除函數(shù)（1）在SQLDeveloper中右擊Functions選項(xiàng)，從快捷菜單中選擇“Drop”項(xiàng)，將會(huì)刪除選定的函數(shù)

添加標(biāo)題文字教師演示講解

2．使用PL/SQL命令刪除函數(shù)【例4-3】刪除用戶方案SCOTT中的函數(shù)fn_CountTypes。DROPFUNCTIONfn_CountTypes;課堂案例5—應(yīng)用包學(xué)習(xí)SQLDeveloper和PL/SQL定義包頭、定義包體和使用包的方法。案例學(xué)習(xí)目標(biāo)SQLDeveloper定義包頭、SQLDeveloper定義包體、PL/SQL定義包頭、PL/SQL定義包體和使用包。案例知識(shí)要點(diǎn)課堂案例5—應(yīng)用包包概述添加標(biāo)題文字包（Package）可將一些有聯(lián)系的對(duì)象放置在其內(nèi)部，構(gòu)成一個(gè)邏輯分組，這些對(duì)象包括存儲(chǔ)過程、函數(shù)、游標(biāo)、自定義的類型（例如PL/SQL表和記錄）和變量等。實(shí)際上，包相當(dāng)于一個(gè)命名的聲明部分，任何能在塊定義部分出現(xiàn)的對(duì)象都可以在包中出現(xiàn)，用戶可以從其他PL/SQL塊中對(duì)包進(jìn)行引用。包擁有兩個(gè)獨(dú)立的部分：包頭和包體，它們都存儲(chǔ)在數(shù)據(jù)字典中。定義一個(gè)包，要分別定義包頭和包體。包與存儲(chǔ)過程和函數(shù)的一個(gè)顯著區(qū)別是包僅能存儲(chǔ)在非本地的數(shù)據(jù)庫中?？梢詫⒋鎯?chǔ)過程和函數(shù)定義在包中，包被保存在高速緩存中，這樣體現(xiàn)了模塊化編程的特點(diǎn)，使得應(yīng)用系統(tǒng)的開發(fā)更為靈活，運(yùn)行效率更高。存儲(chǔ)過程和函數(shù)被加入到包中時(shí)，存儲(chǔ)過程和函數(shù)的聲明放在包頭部分，而執(zhí)行代碼則放在包體部分。添加標(biāo)題文字教師演示講解課堂案例5—應(yīng)用包案例完成步驟—SQLDeveloper定義包頭添加標(biāo)題文字（1）使用SQLDeveloper定義包頭

（a）在SQLDeveloper中右擊Packages選項(xiàng)，從快捷菜單中選擇“NewPackage”項(xiàng)（b）在打開的“CreatePL/SQLPackage”對(duì)話框中，輸入包的名稱（如pkg_Goods）（c）單擊“確定”按鈕，開始編寫包頭定義部分添加標(biāo)題文字教師演示講解

課堂案例5—應(yīng)用包案例完成步驟—PL/SQL定義包頭添加標(biāo)題文字【例5-1】創(chuàng)建包pkg_DisplayGoods，其包括一個(gè)存儲(chǔ)過程和一個(gè)函數(shù)，其中存儲(chǔ)過程實(shí)現(xiàn)根據(jù)商品編號(hào)查詢商品名稱、類別名稱和商品價(jià)格的功能，函數(shù)實(shí)現(xiàn)根據(jù)商品類別編號(hào)返回該類別商品的總庫存量的功能。添加標(biāo)題文字教師演示講解

課堂案例5—應(yīng)用包案例完成步驟—SQLDeveloper定義包體添加標(biāo)題文字（1）使用SQLDeveloper定義包體（a）在SQLDeveloper的Package選項(xiàng)中右擊已經(jīng)定義包頭的pkg_Goods選項(xiàng)，從快捷菜單中選擇“CreateBody”項(xiàng)（b）開始編寫包體定義部分添加標(biāo)題文字教師演示講解

課堂案例5—應(yīng)用包案例完成步驟—PL/SQL定義包體添加標(biāo)題文字【例5-2】實(shí)現(xiàn)包pkg_DisplayGoods中存儲(chǔ)過程和函數(shù)的功能。添加標(biāo)題文字教師演示講解

課堂案例5—應(yīng)用包案例完成步驟—使用包添加標(biāo)題文字1．包的初始化當(dāng)?shù)谝淮握{(diào)用打包子程序時(shí)，該包將進(jìn)行初始化，即將該包從輔存中讀入內(nèi)存，并啟動(dòng)調(diào)用的子程序的編譯代碼。2．引用包中對(duì)象在包中定義的任何對(duì)象既可以在包內(nèi)使用，也可以在包外使用。在外部引用包中對(duì)象時(shí)，可以通過使用包名作為前綴對(duì)其進(jìn)行引用添加標(biāo)題文字教師演示講解

課堂案例5—應(yīng)用包案例完成步驟—使用包添加標(biāo)題文字【例5-3】調(diào)用包pkg_DisplayGoods中存儲(chǔ)過程和函數(shù)，查詢商品編號(hào)為“200708011430”的商品信息和統(tǒng)計(jì)商品類別編號(hào)為“01”的商品總數(shù)量。添加標(biāo)題文字教師演示講解

課堂案例5—應(yīng)用包Oracle內(nèi)置包添加標(biāo)題文字Oracle11g提供了很多具有特定功能的內(nèi)置包

添加標(biāo)題文字教師演示講解

編寫PL/SQL語句塊，使用IF語句求出三個(gè)數(shù)中最大的數(shù)

任務(wù)1編寫PL/SQL語句塊，使用LOOP和FOR-IN-LOOP-ENDLOOP循環(huán)計(jì)算1+3+5+…+99的值

任務(wù)2創(chuàng)建存儲(chǔ)過程up_Borrow，要求該存儲(chǔ)過程返回未還圖書的借閱信息，包括借書人、借書日期、圖書名稱和圖書作者

任務(wù)3執(zhí)行“【任務(wù)3】”所創(chuàng)建的存儲(chǔ)過程up_Borrow，查詢所有未還圖書的詳細(xì)信息。任務(wù)4創(chuàng)建存儲(chǔ)過程up_BorrowByID，要求該存儲(chǔ)過程能夠根據(jù)輸入的讀者號(hào)返回該讀者的所有借閱信息，包括借書日期、還書日期、圖書名稱和圖書作者。任務(wù)5執(zhí)行“【任務(wù)5】”所創(chuàng)建的存儲(chǔ)過程up_BorrowByID，查詢讀者號(hào)為“0016584”的借閱信息。任務(wù)6演講完畢，謝謝觀看！附錄資料：不需要的可以自行刪除?oracle數(shù)據(jù)庫培訓(xùn)內(nèi)容*基本概念*Oracle的體系結(jié)構(gòu)SQL*Plus的使用及常用命令*SQL*NET的配置和使用數(shù)據(jù)庫的備份(exp/imp)其它常見問題?一、基本概念什么是數(shù)據(jù)庫：是存儲(chǔ)在一起的相關(guān)數(shù)據(jù)的集合。這些數(shù)據(jù)無有害或不必要的冗余，為多種應(yīng)用服務(wù)。數(shù)據(jù)的存儲(chǔ)獨(dú)立于使用它的應(yīng)用程序。對(duì)數(shù)據(jù)庫插入新數(shù)據(jù)、修改和檢索原有數(shù)據(jù)均能用一種公用的和可控的方法進(jìn)行。數(shù)據(jù)庫能做什么：數(shù)據(jù)庫的基本功能就是存儲(chǔ)數(shù)據(jù)，而且能夠?yàn)橛脩籼峁┍憬?、快速的查找、修改、添加和刪除數(shù)據(jù)等功能。什么是SQL語言：是運(yùn)用于數(shù)據(jù)庫的語言。它除了具有數(shù)據(jù)庫查詢、插入、刪除、修改等功能外，可以獨(dú)立使用，也可以嵌入其它主語言中使用。它的語法類似于英語。?什么是oracle：oracle是以高級(jí)結(jié)構(gòu)化查詢語言(SQL)為基礎(chǔ)的大型關(guān)系數(shù)據(jù)庫，通俗地講它是用方便邏輯管理的語言操縱大量有規(guī)律數(shù)據(jù)的集合。是目前最流行的客戶/服務(wù)器(CLIENT/SERVER)體系結(jié)構(gòu)的數(shù)據(jù)庫之一。?二、Oracle的體系結(jié)構(gòu)(1)物理結(jié)構(gòu)

ORACLE數(shù)據(jù)庫在物理上是存儲(chǔ)于硬盤的各種文件。它是活動(dòng)的，可擴(kuò)充的，隨著數(shù)據(jù)的添加和應(yīng)用程序的增大而變化。datafilesredologfilescontrolfilesparameterfile

數(shù)據(jù)文件日志文件控制文件參數(shù)文件

datafilesredologfilescontrolfileparameterfiles*.dbf*.logInitoraid.ora*.ctl?(2)session(連接)

Oracle是多用戶、多任務(wù)、可分布式管理的數(shù)據(jù)庫，同

時(shí)可有許多個(gè)用戶對(duì)數(shù)據(jù)庫操作。oracleuseruserusersession……?

ORACLE數(shù)據(jù)庫在邏輯上是由許多表空間構(gòu)成。主要分為系統(tǒng)表空間和非系統(tǒng)表空間。非系統(tǒng)表空間內(nèi)存儲(chǔ)著各項(xiàng)應(yīng)用的數(shù)據(jù)、索引、程序等相關(guān)信息。我們準(zhǔn)備上馬一個(gè)較大的ORACLE應(yīng)用系統(tǒng)時(shí)，應(yīng)該創(chuàng)建它所獨(dú)占的表空間，同時(shí)定義物理文件的存放路徑和所占硬盤的大小。Oracle數(shù)據(jù)存儲(chǔ)單位a、block數(shù)據(jù)塊：2k最小的I-O單位，伴隨database產(chǎn)生而產(chǎn)生，不可變b、extent一組連續(xù)的數(shù)據(jù)塊：是用戶所能分配存儲(chǔ)的最小單位c、segment段：有共同結(jié)構(gòu)的一個(gè)或幾個(gè)區(qū)域(extent)d、tablespace表空間：一組物理數(shù)據(jù)的邏輯組合，(象邏輯間數(shù)據(jù)倉庫)e、file文件：屬于某個(gè)表空間的物理文件f、database數(shù)據(jù)庫：一組表空間所構(gòu)成的邏輯的可共享的數(shù)據(jù)。(3)存儲(chǔ)結(jié)構(gòu)?表空間tablespacesystemRBSnosystem把system表空間單獨(dú)放在一個(gè)硬盤上做成一組鏡像toolsuserstempapplicationdataapplicationindex分開存放減少爭用?segment段的分類data數(shù)據(jù)段存儲(chǔ)對(duì)象object(table,view,index,sequence...)index索引段temporary臨時(shí)段用做(join,groupby,orderbysorting操作)rollback回滾段用于記錄修改前后信息bootstrap啟動(dòng)段存儲(chǔ)數(shù)據(jù)字典系統(tǒng)信息不能讀寫，放在系統(tǒng)表空間內(nèi)，約占40幾個(gè)block?datasegment中可能有十種不同類型的存儲(chǔ)對(duì)象(1)table(2)view實(shí)為一個(gè)select語句(3)index(一個(gè)表不多于3個(gè))(4)cluster(簇)(5)sequence序列(6)synonyms用于定義某個(gè)遠(yuǎn)程數(shù)據(jù)庫同義詞，實(shí)現(xiàn)分式數(shù)據(jù)庫管理透明(7)snapsot快照(8)storedprocedure(function)(9)package程序包(10)dbtrigger數(shù)據(jù)觸發(fā)器，處理updata,delete,insert中可能出現(xiàn)的問題?常用的ORACLE的數(shù)據(jù)庫字段類型字段類型中文說明限制條件其它說明CHAR固定長度字符串最大長度2000bytesVARCHAR2可變長度的字符串最大長度4000bytes可做索引的最大長度749DATE日期（日-月-年）LONG超長字符串最大長度2G（231-1）足夠存儲(chǔ)大部頭著作RAW固定長度的二進(jìn)制數(shù)據(jù)最大長度2000bytes可存放多媒體圖象聲音等LONGRAW可變長度的二進(jìn)制數(shù)據(jù)最大長度2G同上NUMBER(P,S)數(shù)字類型P為整數(shù)位，S為小數(shù)位INTEGER整數(shù)類型小的整數(shù)FLOAT浮點(diǎn)數(shù)類型NUMBER(38)，雙精度REAL實(shí)數(shù)類型NUMBER(63)，精度更高……?三、SQL*Plus的使用及常用命令進(jìn)入sql*plus；數(shù)據(jù)查詢操作（select)；改變數(shù)據(jù)操作(insert、update、delete)；其它常用sql和sql*plus命令(spool、create、drop、desc、connect、grant……)。?數(shù)據(jù)查詢操作簡單查詢select列名1，列名2，列名3……from表名where條件orderby列名1,列名2……；?邏輯運(yùn)算符>>=<<=<>或!=其它(not)inis(not)betweenandandor單值函數(shù)nvl----空值替換函數(shù)abs---絕對(duì)值函數(shù)mod---求模函數(shù)power---求冪函數(shù)sqrt-----平方根函數(shù)exp，ln，log----對(duì)數(shù)函數(shù)sin，cos等----三角函數(shù)?唯一性查詢關(guān)鍵字：distinctselectdistinct列名from表名where條件；?模糊查詢select列名1，列名2，列名3……from表名where列名1like‘_%mm’orderby列名1,列名2……；關(guān)鍵字：(not)like通配符：%_?聯(lián)接查詢select表名1.列名1，表名2.列名2，列名3……from表名1，表名2……where條件orderby表名1.列名1,表名2.列名2……；?子查詢單值子查詢

多值子查詢select列名1，列名2，列名3……from表名1where列名1=（select列名1from表名2where條件）orderby列名1,列名2……；select列名1，列名2，列名3……from表名1where列名1in（select列名1from表名2where條件）orderby列名1,列名2……；?組值函數(shù)avg---平均值函數(shù)count---非空值數(shù)目max---最大值函數(shù)min---最小值函數(shù)sum---求和函數(shù)distinct---唯一性函數(shù)分組查詢select列名1，列名2，sum(列名3)……from表名where條件groupby列名1,列名2……；關(guān)鍵字：groupby?改變數(shù)據(jù)操作Insert插入操作insertinto表名1（列名1，列名2……）values(值1，值2……）；注意：⑴值表中的數(shù)據(jù)用逗號(hào)間隔；⑵字符型和日期型要用單引號(hào)括起來；⑶缺省的日期格式為dd-man-yy。insertinto表名1usingselect列名1，列名2……from表名2where條件；?Update修改操作可修改單個(gè)字段、修改多行、修改單個(gè)列。update表名set列名=值或表達(dá)式where條件；?Delete刪除deletefrom表名where條件；注意⑴不能只刪除行的一部分⑵where子句決定哪一行將被刪除。如里省略了where子句，則所有的行都將被刪除。?其它常用sql命令Createtable---創(chuàng)建表；例子Droptable---刪除表，sql同時(shí)自動(dòng)刪除該表上的索引、特權(quán)，但并不刪除相關(guān)的視圖或同義詞；Grant---授權(quán)Connect-----連上Oracle,做最基本操作resource----具有程序開發(fā)最基本的權(quán)限dba----------數(shù)據(jù)庫管理員所有權(quán)限?@-------運(yùn)行指定的命令文件，作用等價(jià)于start命令；/---------運(yùn)行sql緩沖區(qū)的命令，與run命令等價(jià)；Connect---退出當(dāng)前賬號(hào)，向指定的用戶帳號(hào)登錄；Desc-----顯示指定表的結(jié)構(gòu)；Exit-----中止sql*plus，返回操作系統(tǒng)；Spool---將屏幕顯示內(nèi)容輸出到一個(gè)文件上；Spooloff--輸出文件結(jié)束；其它常用sql*plus命令?四、SQL*NET的配置和使用1配置網(wǎng)絡(luò)協(xié)議TCP/IP

2測(cè)試網(wǎng)絡(luò)是否連通3在Windows95上選擇“開始”按鈕，選定“程序”中的“OracleforWindows95”下的“SQL*NetEasyConfiguration”，在“SQL*NetEasyConfiguration”窗口中選擇“AddDatabaseAlias”，在“ChooseDatabaseAlias”對(duì)話框中“DatabaseAlias”項(xiàng)中鍵入數(shù)據(jù)庫別名，按“OK”按鈕確認(rèn)。

在“ChooseTCP/IPHostNameandDatabase”對(duì)話框中的TCP/IPHostname項(xiàng)中鍵入SERVER的IP地址，在“DatabaseInstance”項(xiàng)中鍵入Oracle的實(shí)例名。在“ConfirmAddingDatabaseAlias”對(duì)話框中可以看到你新增加的數(shù)據(jù)庫別名。按“YES”按鈕，按“OK”，退出“SQL*NetEasyConfiguration”。?五、數(shù)據(jù)庫備份(exp/imp)EXPORT將數(shù)據(jù)庫中數(shù)據(jù)備份成一個(gè)二進(jìn)制系統(tǒng)文件稱為“導(dǎo)出轉(zhuǎn)儲(chǔ)文件”(EXPORTDUMPFILE)，并將重新生成數(shù)據(jù)庫寫入文件中。它有三種模式：a.用戶模式：導(dǎo)出用戶所有對(duì)象以及對(duì)象中的數(shù)據(jù)；b.表模式：導(dǎo)出用戶所有表或者指定的表；c.整個(gè)數(shù)據(jù)庫：導(dǎo)出數(shù)據(jù)庫中所有對(duì)象。IMPORT導(dǎo)入：導(dǎo)入的過程是導(dǎo)出的逆過程，這個(gè)命令先讀取導(dǎo)出來的導(dǎo)出轉(zhuǎn)儲(chǔ)二進(jìn)制文件，并運(yùn)行文件，恢復(fù)對(duì)象用戶和數(shù)據(jù)。?數(shù)據(jù)庫導(dǎo)出過程exp80用戶名/密碼Enterarrayfetchbuffersize:4096>exportfile:expdat.dmp>(1)Entiredatabase(2)Users(3)Table:(2)Users>Exportgrants:yes>Exporttabledata:yes>