Oracle數據庫子程序和程序包課件

子程序和程序包回顧q游標用于處理查詢結果集中的數據q游標類型有：隱式游標、顯式游標和

REF游標q隱式游標由

PL/SQL

自動定義、打開和關閉q顯式游標用于處理返回多行的查詢q顯式游標可以刪除和更新活動集中的行q要處理結果集中所有記錄時，可使用循環(huán)游標q在聲明

REF游標時，不需要將

SELECT

語句與其關聯(lián)2目標q創(chuàng)建和使用子程序q創(chuàng)建和使用程序包3子程序

2-1q命名的

PL/SQL

塊，編譯并存儲在數據庫中。q子程序的各個部分：q聲明部分q可執(zhí)行部分q異常處理部分(可選)q子程序的分類：q過程

－

執(zhí)行某些操作q函數

－

執(zhí)行操作并返回值4子程序

2-2子程序的優(yōu)點：q模塊化q將程序分解為邏輯模塊q可重用性q可以被任意數目的程序調用q可維護性q簡化維護操作q安全性q通過設置權限，使數據更安全5過程

8-1q過程是用于完成特定任務的子程序q例如：在柜臺購買車票前往售票廳排隊等候詢問關于車票的信息6過程

8-2創(chuàng)建過程的語法：CREATE[ORREPLACE]PROCEDURE<procedurename>[(<parameterlist>)]IS|AS<localvariabledeclaration>創(chuàng)建過程，可指定運行過程需傳遞的參數BEGIN<executablestatements>包括在過程中要執(zhí)行的語句[EXCEPTION<exceptionhandlers>]END;7過程

8-3CREATEORREPLACEPROCEDUREfind_emp(emp_noNUMBER)ASempnameVARCHAR2(20);BEGINSELECTenameINTOempnameFROMEMPWHEREempno=emp_no;DBMS_OUTPUT.PUT_LINE('雇員姓名是

'||empname);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('雇員編號未找到');ENDfind_emp;/8過程

8-4過程參數的三種模式：qINq用于接受調用程序的值q默認的參數模式qOUTq用于向調用程序返回值qINOUTq用于接受調用程序的值，并向調用程序返回更新的值9過程

8-5執(zhí)行過程的語法：EXECUTE

procedure_name(parameters_list);SQL>CREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code||'項目的說明為'||v_itemdesc);END;/SQL>SETSERVEROUTPUTONSQL>EXECUTEitemdesc('i201');10過程

8-6SQL>CREATEORREPLACEPROCEDUREDECLAREtest(value1IN

VARCHAR2,value1VARCHAR2(5):='i202';value2OUTNUMBER)ISvalue2NUMBER;BEGINidentity

NUMBER;test(value1,value2);BEGINDBMS_OUTPUT.PUT_LINE('value2

的值為'SELECTITEMRATEINTOidentity||TO_CHAR(value2));FROMitemFileWH

EREitemcode=value1;/IFidentity<200THENvalue2:=100;ENDIF;END;11過程

8-7SCQRLE>ATSEETOSRERREVPELRAOCUETPORNOC

E

D

U

R

ESsQwLa>pDp

E

1

C

I

NLAORUET

NUMBER,p2INOUTNUMBER)ISnum1

NUMBER:=100;v

n

_

u

t

emm2pNNUUMMBBEERR;

=200;BEGINvs_wteamp(pnu:=mp11,;

n

u

m

2

)

;pD1B:M=

Sp2_;OUTPUT.PUT_LINE('num1

='||num1);pD2B:M=

Sv__tOemUTp;PUT.PUT_LINE('num2

='||num2);END;/12過程

8-8q將過程的執(zhí)行權限授予其他用戶：SQL>GRANTEXECUTEONfind_empTOMARTIN;SQL>GRANTEXECUTEONswapTOPUBLIC;q刪除過程：SQL>DROPPROCEDUREfind_emp;13函數

4-1q函數是可以返回值的命名的

PL/SQL

子程序。q創(chuàng)建函數的語法：CREATE[ORREPLACE]FUNCTION<functionname>[(param1,param2)]RETURN<datatype>

IS|AS[localdeclarations]BEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;14函數

4-2q定義函數的限制：q函數只能接受

IN參數，而不能接受

INOUT

或

OUT參數q形參不能是

PL/SQL

類型q函數的返回類型也必須是數據庫類型q訪問函數的兩種方式：q使用

PL/SQL

塊q使用

SQL語句15函數

4-3q創(chuàng)建函數：CREATEORREPLACEFUNCTIONfun_helloRETURN

VARCHAR2ISBEGINRETURN'朋友，您好';END;/q從

SQL

語句調用函數：SQL>SELECTfun_helloFROMDUAL;16函數

4-4CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin_priceNUMBER;DECLAREmax_priceNUMBER;PNUMBER:=300;BEGINMSGVARCHAR2(200);SELECTMAX(ITEMRATE),MIN(ITEMRATE)INT

Omax_price,min_priceMSG:=item_price_range(300);FROMitemfile;DBMS_OUTPUT.PUT_LINE(MSG);IFprice>=min_priceANDprice<=max_priceEND;/THENRETURN'輸入的單價介于最低價與最高價之間';ELSERETURN'超出范圍';ENDIF;END;/17過程和函數的比較過

程函

數作為

PL/SQL

語句執(zhí)行作為表達式的一部分調用在規(guī)格說明中不包含

RETURN子句

必須在規(guī)格說明中包含RETURN子句不返回任何值可以包含

RETURN語句，但是與函

必須包含至少一條

RETURN數不同，它不能用于返回值語句18自主事務處理

2-1q自主事務處理q主事務處理啟動獨立事務處理q然后主事務處理被暫停q自主事務處理子程序內的

SQL操作q然后終止自主事務處理q恢復主事務處理qPRAGMAAUTONOMOUS_TRANSACTION用于標記子程序為自主事務處理19自主事務處理

2-2q自主事務處理的特征：q與主事務處理的狀態(tài)無關q提交或回滾操作不影響主事務處理q自主事務處理的結果對其他事務是可見的q能夠啟動其他自主事務處理20程序包q程序包是對相關過程、函數、變量、游標和異常等對象的封裝q程序包由規(guī)范和主體兩部分組成聲明程序包中公共對象。包括類型、變量、常量、異常、游標規(guī)范和子程序規(guī)范等21創(chuàng)建程序包

2-1程序包規(guī)范程序包主體CREATE

[OR

REPLACE]PACKAGECREATE

[OR

REPLACE]PACKAGE

BODYpackage_name

IS|AS[Public

item

declarations][Subprogram

specification]END

[package_name];package_name

IS|AS[Private

item

declarations][Subprogram

bodies][BEGINEND

[package_name];22創(chuàng)建程序包

2-2CREATEORREPLACEPACKAGEpack_meCREATEORREPLACEPACKAGEBODYpack_meASISPROCEDUREorder_proc(ornoVARCHAR2)ISPBROFUstatCHAR(1);ECEDGUREoIrNder_proc(ornoVARCHAR2);NCTIONorder_fun(ornosVARCHAR2)RETURNVARCHAR2;ENDpack_me;SELECTostatusINTOstatFROMorder_master/WHEREorderno=orno;……ENDorder_proc;FUNCTIONorder_fun(ornosVARCHAR2)RETURNVARCHAR2ISicode

VARCHAR2(5);ocode

VARCHAR2(5);BEGIN……ENDorder_fun;ENDpack_me;23/程序包的優(yōu)點q模塊化q更輕松的應用程序設計q信息隱藏q新增功能q性能更佳24程序包中的游標

2-1q游標的定義分為游標規(guī)范和游標主體兩部分q在包規(guī)范中聲明游標規(guī)范時必須使用

RETURN子句指定游標的返回類型qRETURN子句指定的數據類型可以是：q用

%ROWTYPE

屬性引用表定義的記錄類型q程序員定義的記錄類型25程序包中的游標

2-2SQL>

CREATE

OR

REPLACE

PACKAGE

BODY

cur_pack

ASCURSOR

ord_cur(vcode

VARCHAR2)SQL>

CREATE

OR

REPLACE

PACKAGE

cur_pack

ISRETURN

order_master%ROWTYPE

ISCURSOR

ord_cur(vcode

VARCHAR2)SELECT

*

FROM

order_master

WHERE

VENCODE=vcode;RETURN

order_master%ROWTYPE;PROCEDURE

ord_pro(vcode

VARCHAR2)

ISPROCEDURE

ord_pro(vcode

VARCHAR2);or_rec

order_master%ROWTYPE;EBNEDGIcNur_pack;/

OPEN

ord_cur(vcode);LOOPFETCH

ord_cur

INTO

or_rec;EXIT

WHEN

ord_cur%NOTFOUND;DBMS_O