oracle子程序和程序包课件.ppt

1、第七章子程序和程序包回顾回顾游标用于处理查询结果集中的数据游标类型有：隐式游标、显式游标和 REF 游标隐式游标由 PL/SQL 自动定义、打开和关闭显式游标用于处理返回多行的查询显式游标可以删除和更新活动集中的行要处理结果集中所有记录时，可使用循环游标在声明 REF 游标时，不需要将 SELECT 语句与 其关联目标目标了解和使用子程序 了解和使用程序包子程序子程序 2-1命名的 PL/SQL 块，编译并存储在数据库中。子程序的各个部分：声明部分可执行部分异常处理部分(可选)子程序的分类：过程 执行某些操作函数 执行操作并返回值子程序子程序 2-2子程序的优点：模块化将程序分解为逻辑模块可重

2、用性可以被任意数目的程序调用可维护性简化维护操作安全性通过设置权限，使数据更安全过程过程 8-1过程是用于完成特定任务的子程序 例如：前往售票厅询问关于车票的信息 排队等候 在柜台购买车票 过程过程 8-2创建过程的语法：CREATE OR REPLACE PROCEDURE ()IS|AS BEGIN EXCEPTION END;创建过程，可指定运行过程需传递的参数处理异常 包括在过程中要执行的语句 过程过程 8-3create or replace procedure test4(v_id varchar2)is v_ename emp.ename%type;v_sal emp.sal%t

3、ype;begin select ename,sal into v_ename,v_sal from emp where empno=v_id;dbms_output.put_line(v_ename|v_sal);end;过程过程 8-4执行过程的语法：EXECUTE procedure_name(parameters_list);SQL SET SERVEROUTPUT ONSQL EXEC proc_stu(007);过程过程 8-5过程参数的三种模式：IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值 IN OUT用于接受调用程序的值，并向调用程序返回更新的值过程过程 8

4、-6create or replace procedure test5(x in number,y in number,z out number)as begin z:=x+y;end;过程过程 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 numb

5、er:=500;begin p_swap(num1,num2);dbms_output.put_line(num1=|num1);dbms_output.put_line(num2=|num2);end;/过程过程 8-8将过程的执行权限授予其他用户：删除过程：SQL GRANT EXECUTE ON proc_stu TO SCTOO;SQL GRANT EXECUTE ON proc_swap TO PUBLIC;SQL DROP PROCEDURE proc_swap;函数函数 4-1函数是可以返回值的命名的 PL/SQL 子程序。创建函数的语法：CREATE OR REPLACE FU

6、NCTION (param1,param2)RETURN IS|AS local declarationsBEGIN Executable Statements;RETURN result;EXCEPTION Exception handlers;END;函数函数 4-2q定义函数的限制：q函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数q形参不能是 PL/SQL 类型q函数的返回类型也必须是数据库类型q访问函数的两种方式：q使用 PL/SQL 块q使用 SQL 语句函数函数 4-3l创建函数：查看函数：var name varchar2;exec:name:=funl(函

7、数名字)l从 SQL 语句调用函数：CREATE OR REPLACE FUNCTION func_hello RETURN VARCHAR2ISBEGIN RETURN 朋友，您好;END;/SQL SELECT func_hello FROM DUAL;函数函数 4-4create or replace function get_sal(v_ename in varchar2)return numberis v_sal number;begin select sal into v_sal from emp where upper(ename)=upper(v_ename);return v

8、_sal;exception when no_data_found then raise_application_error(-20000,员工不存在);end;过程和函数的比较过程和函数的比较过过 程程函函 数数作为 PL/SQL 语句执行作为表达式的一部分调用在规格说明中不包含 RETURN 子句必须在规格说明中包含 RETURN 子句不返回任何值必须返回单个值可以包含 RETURN 语句，但是与函数不同，它不能用于返回值必须包含至少一条 RETURN 语句程序包程序包程序包是对相关过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常

9、量、异常、游标规范和子程序规范等声明程序包私有对象和实现在包规范中声明的子程序和游标程序包规范主体创建程序包创建程序包 2-1程序包规范CREATE OR REPLACE PACKAGE package_name IS|ASPublic item declarationsSubprogram specificationEND package_name;程序包主体CREATE OR REPLACE PACKAGE BODY package_name IS|ASPrivate item declarationsSubprogram bodiesBEGINInitializationEND pack

10、age_name;创建程序包创建程序包 2-2create or replace package pack_stu As v_globalid char(8);procedure proc_stu(v_id varchar2);function func_stu(v_id varchar2)return 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.

11、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_student where f_id=v_id;.end proc_stu;function func_stu(v_id varchar2)return varchar2 As .end func_stu;end pack_stu;/程序包的优点程序包的优点模块化更轻松的应用程序设计信息隐藏新增功能性能更佳程序包中的

12、游标程序包中的游标 2-1游标的定义分为游标规范和游标主体两部分在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型RETURN子句指定的数据类型可以是：用%ROWTYPE 属性引用表定义的记录类型程序员定义的记录类型程序包中的游标程序包中的游标 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 PAC

13、KAGE BODY pack_cur ASCursor cur_stu(stuclass number)return t_student%rowtype Isselect*from 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(学生姓

14、名：学生姓名：|rec_stu.f_name);end loop;end proc_stu;end;/有关子程序和程序包的信息有关子程序和程序包的信息USER_OBJECTS 视图包含用户创建的子程序和程序包的信息USER_SOURCE 视图存储子程序和程序包的源代码SELECT object_name,object_typeFROM USER_OBJECTSWHERE object_type IN(PROCEDURE,FUNCTION,PACKAGE,PACKAGE BODY);SELECT line,text FROM USER_SOURCEWHERE NAME=PACK_CUR;总结总结子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用有两种类型的PL/SQL子程序，即过程和函数过程用户执行特定的任务，函数用于执行任务并返回值程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装程序包由两部分组成，即包规范和包主体使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳