oracle子程序和程序包課件

1、第七章子程序和程序包第七章子程序和程序包回顧游標(biāo)用于處理查詢(xún)結(jié)果集中的數(shù)據(jù)游標(biāo)類(lèi)型有：隱式游標(biāo)、顯式游標(biāo)和 REF 游標(biāo)隱式游標(biāo)由 PL/SQL 自動(dòng)定義、打開(kāi)和關(guān)閉顯式游標(biāo)用于處理返回多行的查詢(xún)顯式游標(biāo)可以刪除和更新活動(dòng)集中的行要處理結(jié)果集中所有記錄時(shí)，可使用循環(huán)游標(biāo)在聲明 REF 游標(biāo)時(shí)，不需要將 SELECT 語(yǔ)句與 其關(guān)聯(lián)回顧游標(biāo)用于處理查詢(xún)結(jié)果集中的數(shù)據(jù)目標(biāo)了解和使用子程序 了解和使用程序包目標(biāo)了解和使用子程序 子程序 2-1命名的 PL/SQL 塊，編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中。子程序的各個(gè)部分：聲明部分可執(zhí)行部分異常處理部分(可選)子程序的分類(lèi)：過(guò)程 執(zhí)行某些操作函數(shù) 執(zhí)行操作并返回值子

2、程序 2-1命名的 PL/SQL 塊，編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中子程序 2-2子程序的優(yōu)點(diǎn)：模塊化將程序分解為邏輯模塊可重用性可以被任意數(shù)目的程序調(diào)用可維護(hù)性簡(jiǎn)化維護(hù)操作安全性通過(guò)設(shè)置權(quán)限，使數(shù)據(jù)更安全子程序 2-2子程序的優(yōu)點(diǎn)：過(guò)程 8-1過(guò)程是用于完成特定任務(wù)的子程序 例如：前往售票廳詢(xún)問(wèn)關(guān)于車(chē)票的信息 排隊(duì)等候 在柜臺(tái)購(gòu)買(mǎi)車(chē)票 過(guò)程 8-1過(guò)程是用于完成特定任務(wù)的子程序 前往售票廳詢(xún)問(wèn)關(guān)過(guò)程 8-2創(chuàng)建過(guò)程的語(yǔ)法：CREATE OR REPLACE PROCEDURE ()IS|AS BEGIN EXCEPTION END;創(chuàng)建過(guò)程，可指定運(yùn)行過(guò)程需傳遞的參數(shù)處理異常 包括在過(guò)程中要執(zhí)行的語(yǔ)句

3、 過(guò)程 8-2創(chuàng)建過(guò)程的語(yǔ)法：創(chuàng)建過(guò)程，可指定運(yùn)行過(guò)程需傳遞的過(guò)程 8-3create or replace procedure test4(v_id varchar2)is v_ename emp.ename%type; v_sal emp.sal%type;begin select ename, sal into v_ename,v_sal from emp where empno=v_id; dbms_output.put_line(v_ename| |v_sal); end;過(guò)程 8-3create or replace proced過(guò)程 8-4執(zhí)行過(guò)程的語(yǔ)法： EXECUTE pro

4、cedure_name(parameters_list);SQL SET SERVEROUTPUT ONSQL EXEC proc_stu(007);過(guò)程 8-4執(zhí)行過(guò)程的語(yǔ)法：SQL SET SERVERO過(guò)程 8-5過(guò)程參數(shù)的三種模式：IN用于接受調(diào)用程序的值默認(rèn)的參數(shù)模式OUT用于向調(diào)用程序返回值 IN OUT用于接受調(diào)用程序的值，并向調(diào)用程序返回更新的值過(guò)程 8-5過(guò)程參數(shù)的三種模式：過(guò)程 8-6create or replace procedure test5( x in number, y in number, z out number)as begin z:=x+y;end;過(guò)程

5、 8-6create or replace proced過(guò)程 8-7create or replace procedure test6( a in out number, b in out number)is begin dbms_output.put_line(a); dbms_output.put_line(b); a:=100; b:=100;end;SQL SET SERVEROUT ONSQL declare num1 number := 100; num2 number := 500;begin p_swap(num1,num2); dbms_output.put_line(num

6、1=|num1); dbms_output.put_line(num2=|num2);end;/過(guò)程 8-7create or replace proced過(guò)程 8-8將過(guò)程的執(zhí)行權(quán)限授予其他用戶(hù)：刪除過(guò)程：SQL GRANT EXECUTE ON proc_stu TO SCTOO;SQL GRANT EXECUTE ON proc_swap TO PUBLIC;SQL DROP PROCEDURE proc_swap;過(guò)程 8-8將過(guò)程的執(zhí)行權(quán)限授予其他用戶(hù)：SQL GRAN函數(shù) 4-1函數(shù)是可以返回值的命名的 PL/SQL 子程序。 創(chuàng)建函數(shù)的語(yǔ)法： CREATE OR REPLACE

7、FUNCTION (param1,param2)RETURN IS|AS local declarationsBEGIN Executable Statements; RETURN result;EXCEPTION Exception handlers;END;函數(shù) 4-1函數(shù)是可以返回值的命名的 PL/SQL 子程序。函數(shù) 4-2定義函數(shù)的限制：函數(shù)只能接受 IN 參數(shù)，而不能接受 IN OUT 或 OUT 參數(shù)形參不能是 PL/SQL 類(lèi)型函數(shù)的返回類(lèi)型也必須是數(shù)據(jù)庫(kù)類(lèi)型訪(fǎng)問(wèn)函數(shù)的兩種方式：使用 PL/SQL 塊使用 SQL 語(yǔ)句函數(shù) 4-2定義函數(shù)的限制：函數(shù) 4-3創(chuàng)建函數(shù)：查看函數(shù)：

8、var name varchar2;exec :name:=funl(函數(shù)名字)從 SQL 語(yǔ)句調(diào)用函數(shù)：CREATE OR REPLACE FUNCTION func_hello RETURN VARCHAR2ISBEGIN RETURN 朋友，您好;END;/SQL SELECT func_hello FROM DUAL;函數(shù) 4-3創(chuàng)建函數(shù)：CREATE OR REPLACE F函數(shù) 4-4create or replace function get_sal(v_ename in varchar2)return numberis v_sal number;begin select sal

9、 into v_sal from emp where upper(ename)=upper(v_ename); return v_sal;exception when no_data_found then raise_application_error(-20000,員工不存在);end;函數(shù) 4-4create or replace functi過(guò)程和函數(shù)的比較過(guò) 程函 數(shù)作為 PL/SQL 語(yǔ)句執(zhí)行作為表達(dá)式的一部分調(diào)用在規(guī)格說(shuō)明中不包含 RETURN 子句必須在規(guī)格說(shuō)明中包含 RETURN 子句不返回任何值必須返回單個(gè)值可以包含 RETURN 語(yǔ)句，但是與函數(shù)不同，它不能用于返回值必須包

10、含至少一條 RETURN 語(yǔ)句過(guò)程和函數(shù)的比較過(guò) 程函 數(shù)作為 PL/SQL 語(yǔ)句執(zhí)行作程序包程序包是對(duì)相關(guān)過(guò)程、函數(shù)、變量、游標(biāo)和異常等對(duì)象的封裝程序包由規(guī)范和主體兩部分組成聲明程序包中公共對(duì)象。包括類(lèi)型、變量、常量、異常、游標(biāo)規(guī)范和子程序規(guī)范等聲明程序包私有對(duì)象和實(shí)現(xiàn)在包規(guī)范中聲明的子程序和游標(biāo)程序包規(guī)范主體程序包程序包是對(duì)相關(guān)過(guò)程、函數(shù)、變量、游標(biāo)和異常等對(duì)象的封裝創(chuàng)建程序包 2-1程序包規(guī)范CREATE OR REPLACE PACKAGE package_name IS|ASPublic item declarationsSubprogram specificationEND pac

11、kage_name;程序包主體CREATE OR REPLACE PACKAGE BODY package_name IS|ASPrivate item declarationsSubprogram bodiesBEGINInitializationEND package_name;創(chuàng)建程序包 2-1程序包規(guī)范程序包主體創(chuàng)建程序包 2-2create or replace package pack_stu As v_globalid char(8); procedure proc_stu(v_id varchar2); function func_stu(v_id varchar2) retu

12、rn varchar2;end pack_stu;/create or replace package body pack_stu Asprocedure proc_stu(v_id varchar2)Is v_name t_student.f_name%type; v_dept t_student.f_department%type; v_class t_student.f_class%type;begin v_globalid := v_id; select f_name,f_department,f_class into v_name,v_dept,v_class from t_stud

13、ent where f_id=v_id; .end proc_stu;function func_stu(v_id varchar2)return varchar2 As .end func_stu;end pack_stu;/創(chuàng)建程序包 2-2create or replace pac程序包的優(yōu)點(diǎn)模塊化更輕松的應(yīng)用程序設(shè)計(jì)信息隱藏新增功能性能更佳程序包的優(yōu)點(diǎn)模塊化程序包中的游標(biāo) 2-1游標(biāo)的定義分為游標(biāo)規(guī)范和游標(biāo)主體兩部分在包規(guī)范中聲明游標(biāo)規(guī)范時(shí)必須使用 RETURN 子句指定游標(biāo)的返回類(lèi)型RETURN子句指定的數(shù)據(jù)類(lèi)型可以是：用 %ROWTYPE 屬性引用表定義的記錄類(lèi)型程序員定義的記錄類(lèi)

14、型程序包中的游標(biāo) 2-1游標(biāo)的定義分為游標(biāo)規(guī)范和游標(biāo)主體兩部分程序包中的游標(biāo) 2-2SQL CREATE OR REPLACE PACKAGE pack_cur As Cursor cur_stu(stuclass number) return t_student%rowtype; procedure proc_stu(stuclass number);end;/SQL CREATE OR REPLACE PACKAGE BODY pack_cur ASCursor cur_stu(stuclass number) return t_student%rowtype Isselect * fro

15、m t_student where f_class = stuclass;procedure proc_stu(stuclass number)As rec_stu t_student%rowtype;begin Open cur_stu(stuclass); loop Fetch cur_stu Into rec_stu; Exit when cur_stu%NotFound; dbms_output.put_line(學(xué)生姓名：|rec_stu.f_name); end loop;end proc_stu;end;/程序包中的游標(biāo) 2-2SQL CREATE OR REPL有關(guān)子程序和程序包的信息USER_OBJECTS 視圖包含用戶(hù)創(chuàng)建的子程序和程序包的信息USER_SOURCE 視圖存儲(chǔ)子程序和程序包的源代碼SELECT object_name, object_typeFROM USER_OBJECTSWHERE object_type IN (PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY);SELECT line, text FROM USER_SOURCEWH