《oracle基础教程7PPT课件》由会员分享，可在线阅读，更多相关《oracle基础教程7PPT课件（50页珍藏版）》请在金锄头文库上搜索。
1、专题七专题七 过程、函数和程序包过程、函数和程序包 1回顾回顾游标就是指向上下文区的句柄或指针。游标就是指向上下文区的句柄或指针。游标有两种类型：显式游标、隐式游标。游标有两种类型：显式游标、隐式游标。四个游标属性四个游标属性 ：SQLFOUND、SQLNOTFOUND、SQLROWCOUNT、SQLISOPEN显式游标的使用步骤显式游标的使用步骤 ：个：个记录变量和记录变量和%ROWTYPE 带参数的游标带参数的游标游标游标FOR循环（循环游标）循环（循环游标）游标中的更新和删除游标中的更新和删除REF游标游标2教学目标掌握过程的用法掌握过程的用法掌握函数的用法掌握函数的用法理解过程与函数的
2、相同点和不同点理解过程与函数的相同点和不同点理解程序包的概念并能熟练应用理解程序包的概念并能熟练应用3工作任务 1.用无参过程实现用无参过程实现“Hello World！”程序程序2.用带输入参数的过程向表中插入记录用带输入参数的过程向表中插入记录3.用带输出参数的过程查询表中的记录数用带输出参数的过程查询表中的记录数4.使用带输入输出参数的过程查询记录是否存在使用带输入输出参数的过程查询记录是否存在5.使用函数查询部门信息使用函数查询部门信息6.使用程序包封装过程和函数使用程序包封装过程和函数4相关实践知识相关实践知识从开始菜单中打开从开始菜单中打开SQL*Plus工具，以工具，以SCOTT
3、用户的身份登用户的身份登录到数据库录到数据库 5不带参数的过程不带参数的过程2-1输入以下代码，创建一个最简单的输入以下代码，创建一个最简单的过程过程 功能功能: 显示显示”Hello World!”CREATE OR REPLACE PROCEDURE sp_helloWorld ASBEGINDBMS_OUTPUT.PUT_LINE(Hello World!);END sp_helloWorld; 6不带参数的过程不带参数的过程2-2执行过程执行过程 EXECUTE sp_helloWorld;执行结果执行结果:删除过程删除过程DROP PROCEDURE sp_helloWorld;7带
4、输入参数的过程带输入参数的过程3-1实现的功能：向表实现的功能：向表dept中插入一条记录中插入一条记录创建带创建带输入参数输入参数的过程，代码为：的过程，代码为：8带输入参数的过程带输入参数的过程3-2CREATE OR REPLACE PROCEDURE sp_dept_insert(i_deptno NUMBER,i_dname VARCHAR2,i_loc VARCHAR2)ASBEGIN INSERT INTO dept VALUES(i_deptno,i_dname,i_loc); COMMIT;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_
5、LINE(添加失败添加失败!原因为原因为:|SQLERRM); ROLLBACK;END sp_dept_insert; 9带输入参数的过程带输入参数的过程3-3分别输入两组数据来执行过程分别输入两组数据来执行过程 ，结果如下：，结果如下：10带输出参数的过程带输出参数的过程3-1实现功能：通过输出参数实现功能：通过输出参数count来得到来得到dept表中的记录数表中的记录数创建带创建带输出参数输出参数的过程的过程CREATE OR REPLACE PROCEDURE sp_getcount(o_count OUT NUMBER)ASBEGINSELECT COUNT(*) INTO o_c
6、ount FROM dept;END sp_getcount; 11带输出参数的过程带输出参数的过程3-2编写一段匿名的编写一段匿名的PL/SQL块来执行过程块来执行过程 DECLAREcnt NUMBER;BEGINsp_getcount(cnt);DBMS_OUTPUT.PUT_LINE(dept表中的记录数为表中的记录数为: |cnt);END; 12带输出参数的过程带输出参数的过程3-3输出结果：输出结果：13带带IN OUT参数的过程参数的过程2-1实现功能实现功能:查询某个部门名称在表查询某个部门名称在表dept中是否已经存在中是否已经存在 创建带创建带IN OUT参数参数的过程，
7、代码为：的过程，代码为：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; 14带带IN OUT参数的过程参数的过程2-2执行过程：执行过程：输出结果：输出结
8、果：部门名称部门名称ACCOUNTING存在存在! DECLARE l_iotest varchar2(20):=ACCOUNTING;BEGIN sp_dept_dname_exist(l_iotest); DBMS_OUTPUT.PUT_LINE(部门名称部门名称ACCOUNTING|l_iotest|!);END; 15函数函数4-1实现功能：实现功能： 按部门编号查询出表按部门编号查询出表dept中的部门名称中的部门名称创建一个创建一个函数函数，代码为：，代码为：16函数函数4-2CREATE OR REPLACE FUNCTION f_dept_getname_byno(i_dept
9、no NUMBER)RETURN VARCHAR2ASl_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; 17函数函数4-3函数的调用函数的调用及其输出结果及其输出结果 ：18函数函数4-4删除函数删除函数 DROP FUNCTION f_dept_getname_
10、byno; 19过程与函数小结过程与函数小结2-1共同点：两者的实质都是已共同点：两者的实质都是已命名的命名的PL/SQL程序块程序块，即，即子程子程序序，它们是子程序的两种类型，存储在数据库中，可以从，它们是子程序的两种类型，存储在数据库中，可以从任何数据库客户端和前台应用程序中调用它们。任何数据库客户端和前台应用程序中调用它们。 20过程与函数小结过程与函数小结2-2不同点：不同点： 过程过程函数函数参数模式可以是IN、OUT或IN OUT参数模式只能是IN模式在语法规范中不包含RETURN子句在语法规范中必须包含RETURN子句在可执行语句部分可以有RETURN语句，但其后不能加任何表达
11、式在可执行语句部分至少应该包含一条RETURN expression语句可以用EXECUTE语句来执行不能用EXECUTE语句来执行21程序包程序包5-1利用利用程序包程序包封装过程封装过程sp_dept_insert和函数和函数f_dept_getname_byno程序包规范部分的代码：程序包规范部分的代码：CREATE OR REPLACE PACKAGE pkg_deptASPROCEDURE sp_dept_insert(i_deptno NUMBER,i_dname VARCHAR2,i_loc VARCHAR2);FUNCTION f_dept_getname_byno(i_dep
12、tno NUMBER) RETURN VARCHAR2;END pkg_dept; 22程序包程序包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 TH
13、EN DBMS_OUTPUT.PUT_LINE(添加失败!原因为:|SQLERRM); ROLLBACK; END sp_dept_insert;23程序包程序包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 THE
14、N RETURN 错误!该编号的部门不存在!; END f_dept_getname_byno;END pkg_dept; 24程序包程序包5-4执行程序包中的过程和函数执行程序包中的过程和函数：25程序包程序包5-5删除程序包删除程序包：只删除程序包主体：只删除程序包主体： DROP PACKAGE BODY pkg_dept;删除整个程序包（规范删除整个程序包（规范+主体）：主体）： DROP PACKAGE pkg_dept;26小结：程序包的优点小结：程序包的优点使用程序包的优点：使用程序包的优点：信息隐藏信息隐藏 模块化模块化 对多态的支持对多态的支持 性能更佳性能更佳27过程返回结
15、果集过程返回结果集2-1在在Oracle中的过程不能象中的过程不能象SQL SERVER那样直接返回结果那样直接返回结果集，而必须借助于集，而必须借助于REF游标游标 程序包规范中的代码程序包规范中的代码:lCREATE OR REPLACE PACKAGE pkg_deptlASl TYPE deptcursor IS REF CURSOR;l PROCEDURE sp_dept_getall( dept_cur OUT deptcursor);lEND pkg_dept; 28过程返回结果集过程返回结果集2-2程序包主体中的代码程序包主体中的代码:执行过程执行过程:CREATE OR RE
16、PLACE PACKAGE BODY pkg_deptAS PROCEDURE sp_dept_getall(dept_cur OUT deptcursor) IS BEGINOPEN dept_cur FOR SELECT * FROM dept; END sp_dept_getall;END pkg_dept; lVARIABLE test_cur REFCURSOR;lEXECUTE pkg_dept.sp_dept_getall(:test_cur); lPRINT test_cur; 29查看查看查看过程、函数的信息查看过程、函数的信息SELECT object_name,proce
17、dure_name FROM USER_PROCEDURES;查看过程、函数的源代码查看过程、函数的源代码SELECT line,text FROM USER_SOURCE WHERE name=;30OEM中管理过程、函数、程序包中管理过程、函数、程序包请老师用浏览器打开请老师用浏览器打开OEM，演示在，演示在OEM中中管理过程、函数、管理过程、函数、程序包程序包31总结总结过程和函数都是命名的过程和函数都是命名的PL/SQL程序块，合称子程序程序块，合称子程序过程有三种参数模式：过程有三种参数模式：IN、OUT和和IN OUT过程用户执行特定的任务，函数用于执行任务并返回值过程用户执行特定
18、的任务，函数用于执行任务并返回值过程与函数的相同点和不同点过程与函数的相同点和不同点程程序序包包是是对对过过程程、函函数数、变变量量、常常量量、游游标标、异异常常及及PL/SQL数数据据类类型型等的封装等的封装 程序包由两部分组成，即包规范和包主体程序包由两部分组成，即包规范和包主体使用程序包的优点使用程序包的优点32过程的定义过程的定义定义：定义：过程是一组为了过程是一组为了完成特定功能完成特定功能的、符合数据库程序脚本规的、符合数据库程序脚本规范的程序，经范的程序，经编译后编译后存储在数据库中，然后由一个应用程存储在数据库中，然后由一个应用程序或其他的序或其他的PL/SQL程序调用。从根本
19、上讲，过程就是程序调用。从根本上讲，过程就是命名命名的的PL/SQL程序块程序块。 33创建过程的语法创建过程的语法CREATE OR REPLACE PROCEDURE procedure_name(parameter_list)AS|ISlocal_declarationsBEGINexecutable_statementsEXCEPTIONexception_handlersEND procedure_name; 34执行过程执行过程在在SQL提示符下提示符下 ： EXECUTE procedure_name(parameters_list) 在其它的过程、函数或匿名的在其它的过程、函数
20、或匿名的PL/SQL块中的可执行语句部块中的可执行语句部分执行过程，直接写过程的名称（如有参数需带上）即可分执行过程，直接写过程的名称（如有参数需带上）即可 35删除过程删除过程删除过程的语法删除过程的语法: DROP PROCEDURE procedure_name; 36过程的参数模式过程的参数模式Oracle 中过程的参数模式有三种：中过程的参数模式有三种：IN、OUT和和IN OUT ，即，即输入、输出和输入输出输入、输出和输入输出定义参数的语法：定义参数的语法： parameter_name IN|OUT|IN OUT DATATYPE :=|DEFAULT expression 3
21、7IN模式参数模式参数IN模式是输入模式，可以传递输入参数模式是输入模式，可以传递输入参数IN模式是默认模式，如果未指定参数的模式，则该参数是模式是默认模式，如果未指定参数的模式，则该参数是IN模式的模式的 可以在参数列表中为可以在参数列表中为IN参数赋予一个默认值参数赋予一个默认值 38OUT模式参数模式参数OUT模式是输出模式，可以传递输出参数模式是输出模式，可以传递输出参数 OUT模式的参数，则必须明确指定模式的参数，则必须明确指定 ，即，即OUT必须注明必须注明在返回到调用环境之前，应该先给在返回到调用环境之前，应该先给OUT模式的参数赋值模式的参数赋值 不能为不能为OUT模式的参数赋
22、默认值模式的参数赋默认值39IN OUT模式参数模式参数IN OUT模式是一种比较特殊的模式，它兼有模式是一种比较特殊的模式，它兼有IN模式和模式和OUT模式的特点模式的特点 IN OUT模式的参数，则必须明确指定模式的参数，则必须明确指定 不能为不能为IN OUT模式的参数赋默认值模式的参数赋默认值 40函数的定义函数的定义定义定义 函数的实质也是数据库中已函数的实质也是数据库中已命名的命名的PL/SQL程序块程序块。它的主。它的主要特性是函数要特性是函数能且只能返回一个值能且只能返回一个值。41创建函数的语法创建函数的语法CREATE OR REPLACE FUNCTION functio
23、n_name(parameter_list)RETURN DATATYPEAS|ISlocal_declarationsBEGINexecutable_statementsEXCEPTIONexception_handlersEND function_name; 42函数定义的要点函数定义的要点创建函数时通过创建函数时通过RETURN子句来定义函数的返回类型子句来定义函数的返回类型 在函数体的任何地方用户都可以通过在函数体的任何地方用户都可以通过RETURN expression语句从函数返回，但表达式的类型一定要与语句从函数返回，但表达式的类型一定要与RETURN子句中定义的数据类型一致。子
24、句中定义的数据类型一致。函数的参数模式只能是函数的参数模式只能是IN模式的，而不能是模式的，而不能是OUT或或IN OUT模式的。模式的。43调用函数调用函数在在SQL提示符下：提示符下： SELECT function_name from dual; 在其它的过程、函数或匿名的在其它的过程、函数或匿名的PL/SQL块中的可以用一个与块中的可以用一个与函数的返回类型相同的变量来接收该函数函数的返回类型相同的变量来接收该函数 不能用不能用EXECUTE语句来调用函数语句来调用函数44删除函数删除函数删除函数的语法：删除函数的语法： DROP FUNCTION function_name;45程序
25、包程序包程序包是对过程、函数、变量、常量、游标、异常及程序包是对过程、函数、变量、常量、游标、异常及PL/SQL数据类型等的封装，是一种数据库对象。数据类型等的封装，是一种数据库对象。它由两部分构成：它由两部分构成：程序包规范程序包规范程序包主体程序包主体46创建程序包规范的语法创建程序包规范的语法创建程序包规范的语法创建程序包规范的语法 CREATE OR REPLACE PACKAGE package_name IS|AS public type and item declarations subprogram specifications END package_name; 47创建程序
26、包主体的语法创建程序包主体的语法创建程序包主体的语法创建程序包主体的语法CREATE OR REPLACE PACKAGE BODY package_name IS|AS initialization_statements subprogram bodiesEND package_name; 48程序包元素的引用程序包元素的引用公有项：在程序包规范中声明的元素，可以从其它的应用公有项：在程序包规范中声明的元素，可以从其它的应用程序或程序或PL/SQL块用块用点分表示法点分表示法引用引用私有项：在程序包主体中定义的元素，并没有在程序包规私有项：在程序包主体中定义的元素，并没有在程序包规范中出现过，只能在程序包主体内被使用，不能在程序包范中出现过，只能在程序包主体内被使用，不能在程序包之外被引用之外被引用点分表示法，即点分表示法，即“程序包名称程序包名称”+“.”+“元素名称元素名称” 49只删除程序包主体的语法：只删除程序包主体的语法： DROP PACKAGE BODY package_name；程序包主体和程序包规范全部删除的语法：程序包主体和程序包规范全部删除的语法： DROP PACKAGE package_name; 程序包的删除程序包的删除50
