《oracle存储过程、函数和程序包精编版》由会员分享，可在线阅读，更多相关《oracle存储过程、函数和程序包精编版（50页珍藏版）》请在金锄头文库上搜索。
1、专题七 过程、函数和程序包,回顾,游标就是指向上下文区的句柄或指针。 游标有两种类型：显式游标、隐式游标。 四个游标属性 ：SQLFOUND、SQLNOTFOUND、SQLROWCOUNT、SQLISOPEN 显式游标的使用步骤 ：个 记录变量和%ROWTYPE 带参数的游标 游标FOR循环（循环游标） 游标中的更新和删除 REF游标,教学目标,掌握过程的用法 掌握函数的用法 理解过程与函数的相同点和不同点 理解程序包的概念并能熟练应用,工作任务,用无参过程实现“Hello World！”程序 用带输入参数的过程向表中插入记录 用带输出参数的过程查询表中的记录数 使用带输入输出参数的过程查询记
2、录是否存在 使用函数查询部门信息 使用程序包封装过程和函数,相关实践知识,从开始菜单中打开SQL*Plus工具，以SCOTT用户的身份登录到数据库,不带参数的过程2-1,输入以下代码，创建一个最简单的过程 功能: 显示”Hello World!”,不带参数的过程2-2,执行过程 EXECUTE sp_helloWorld; 执行结果: 删除过程 DROP PROCEDURE sp_helloWorld;,带输入参数的过程3-1,实现的功能：向表dept中插入一条记录 创建带输入参数的过程，代码为：,带输入参数的过程3-2,CREATE OR REPLACE PROCEDURE sp_dept_
3、insert (i_deptno NUMBER, i_dname VARCHAR2, i_loc VARCHAR2) AS BEGIN INSERT INTO dept VALUES(i_deptno,i_dname,i_loc); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(添加失败!原因为:|SQLERRM); ROLLBACK; END sp_dept_insert;,带输入参数的过程3-3,分别输入两组数据来执行过程 ，结果如下：,带输出参数的过程3-1,实现功能：通过输出参数count来得到dept表中的记录数 创建
4、带输出参数的过程,CREATE OR REPLACE PROCEDURE sp_getcount (o_count OUT NUMBER) AS BEGIN SELECT COUNT(*) INTO o_count FROM dept; END sp_getcount;,带输出参数的过程3-2,编写一段匿名的PL/SQL块来执行过程,DECLARE cnt NUMBER; BEGIN sp_getcount(cnt); DBMS_OUTPUT.PUT_LINE(dept表中的记录数为: |cnt); END;,带输出参数的过程3-3,输出结果：,带IN OUT参数的过程2-1,实现功能:查询某
5、个部门名称在表dept中是否已经存在 创建带IN OUT参数的过程，代码为：,CREATE OR REPLACE PROCEDURE sp_dept_dname_exist (io_value IN OUT VARCHAR2) IS l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM dept WHERE dname=io_value; IF(l_count0) THEN io_value:=存在; ELSE io_value:=不存在; END IF; END sp_dept_dname_exist;,带IN OUT参数的过程2-2
6、,执行过程： 输出结果： 部门名称ACCOUNTING存在!,DECLARE l_iotest varchar2(20):=ACCOUNTING; BEGIN sp_dept_dname_exist(l_iotest); DBMS_OUTPUT.PUT_LINE(部门名称ACCOUNTING|l_iotest|!); END;,函数4-1,实现功能： 按部门编号查询出表dept中的部门名称 创建一个函数，代码为：,函数4-2,CREATE OR REPLACE FUNCTION f_dept_getname_byno (i_deptno NUMBER) RETURN VARCHAR2 AS l
7、_dname VARCHAR2(14); BEGIN SELECT dname INTO l_dname FROM dept WHERE deptno=i_deptno; RETURN l_dname; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 错误!该编号的部门不存在!; END f_dept_getname_byno;,函数4-3,函数的调用及其输出结果 ：,函数4-4,删除函数 DROP FUNCTION f_dept_getname_byno,过程与函数小结2-1,共同点：两者的实质都是已命名的PL/SQL程序块，即子程序，它们是子程序的两种类型
8、，存储在数据库中，可以从任何数据库客户端和前台应用程序中调用它们。,过程与函数小结2-2,不同点：,程序包5-1,利用程序包封装过程sp_dept_insert和函数f_dept_getname_byno 程序包规范部分的代码：,CREATE OR REPLACE PACKAGE pkg_dept AS PROCEDURE sp_dept_insert (i_deptno NUMBER,i_dname VARCHAR2,i_loc VARCHAR2); FUNCTION f_dept_getname_byno(i_deptno NUMBER) RETURN VARCHAR2; END pkg_
9、dept;,程序包5-2,程序包主体部分的代码,CREATE OR REPLACE PACKAGE BODY pkg_dept AS -过程sp_dept_insert PROCEDURE sp_dept_insert (i_deptno NUMBER,i_dname VARCHAR2,i_loc VARCHAR2) AS BEGIN INSERT INTO dept VALUES(i_deptno,i_dname,i_loc); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(添加失败!原因为:|SQLERRM); ROLLBA
10、CK; END sp_dept_insert;,程序包5-3,-函数f_num_range FUNCTION f_dept_getname_byno(i_deptno NUMBER) RETURN VARCHAR2 AS l_dname VARCHAR2(14); BEGIN SELECT dname INTO l_dname FROM dept WHERE deptno=i_deptno; RETURN l_dname; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 错误!该编号的部门不存在!; END f_dept_getname_byno; END p
11、kg_dept;,程序包5-4,执行程序包中的过程和函数：,程序包5-5,删除程序包： 只删除程序包主体： DROP PACKAGE BODY pkg_dept; 删除整个程序包（规范+主体）： DROP PACKAGE pkg_dept;,小结：程序包的优点,使用程序包的优点： 信息隐藏 模块化 对多态的支持 性能更佳,过程返回结果集2-1,在Oracle中的过程不能象SQL SERVER那样直接返回结果集，而必须借助于REF游标 程序包规范中的代码:,CREATE OR REPLACE PACKAGE pkg_dept AS TYPE deptcursor IS REF CURSOR; P
12、ROCEDURE sp_dept_getall( dept_cur OUT deptcursor); END pkg_dept;,过程返回结果集2-2,程序包主体中的代码: 执行过程:,CREATE OR REPLACE PACKAGE BODY pkg_dept AS PROCEDURE sp_dept_getall(dept_cur OUT deptcursor) IS BEGIN OPEN dept_cur FOR SELECT * FROM dept; END sp_dept_getall; END pkg_dept;,VARIABLE test_cur REFCURSOR; EXEC
13、UTE pkg_dept.sp_dept_getall(:test_cur); PRINT test_cur;,OEM中管理过程、函数、程序包,请老师用浏览器打开OEM，演示在OEM中管理过程、函数、程序包,总结,过程和函数都是命名的PL/SQL程序块，合称子程序 过程有三种参数模式：IN、OUT和IN OUT 过程用户执行特定的任务，函数用于执行任务并返回值 过程与函数的相同点和不同点 程序包是对过程、函数、变量、常量、游标、异常及PL/SQL数据类型等的封装 程序包由两部分组成，即包规范和包主体 使用程序包的优点,过程的定义,定义： 过程是一组为了完成特定功能的、符合数据库程序脚本规范的程
14、序，经编译后存储在数据库中，然后由一个应用程序或其他的PL/SQL程序调用。从根本上讲，过程就是命名的PL/SQL程序块。,创建过程的语法,CREATE OR REPLACE PROCEDURE procedure_name (parameter_list) AS|IS local_declarations BEGIN executable_statements EXCEPTION exception_handlers END procedure_name;,执行过程,在SQL提示符下 ： EXECUTE procedure_name(parameters_list) 在其它的过程、函数或匿名
15、的PL/SQL块中的可执行语句部分执行过程，直接写过程的名称（如有参数需带上）即可,删除过程,删除过程的语法: DROP PROCEDURE procedure_name;,过程的参数模式,Oracle 中过程的参数模式有三种：IN、OUT和IN OUT ，即输入、输出和输入输出 定义参数的语法： parameter_name IN|OUT|IN OUT DATATYPE :=|DEFAULT expression,IN模式参数,IN模式是输入模式，可以传递输入参数 IN模式是默认模式，如果未指定参数的模式，则该参数是IN模式的 可以在参数列表中为IN参数赋予一个默认值,OUT模式参数,OUT
16、模式是输出模式，可以传递输出参数 OUT模式的参数，则必须明确指定 ，即OUT必须注明 在返回到调用环境之前，应该先给OUT模式的参数赋值 不能为OUT模式的参数赋默认值,IN OUT模式参数,IN OUT模式是一种比较特殊的模式，它兼有IN模式和OUT模式的特点 IN OUT模式的参数，则必须明确指定 不能为IN OUT模式的参数赋默认值,函数的定义,定义 函数的实质也是数据库中已命名的PL/SQL程序块。它的主要特性是函数能且只能返回一个值。,创建函数的语法,CREATE OR REPLACE FUNCTION function_name (parameter_list) RETURN DATATYPE AS|IS local_declarations BEGIN executable_statements EXCEPTION exception_handlers END function_name;,函
