oracle存储过程、函数和程序包.pptx
( 4.5 )《oracle存储过程、函数和程序包.pptx》由会员分享，可在线阅读，更多相关《oracle存储过程、函数和程序包.pptx（74页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。
1、 Hands-On实训教程系列实训教程系列专题七专题七 过程、函数和程序包过程、函数和程序包 Hands-On实训教程系列实训教程系列回顾回顾l游标就是指向上下文区的句柄或指针。游标就是指向上下文区的句柄或指针。l游标有两种类型：显式游标、隐式游标。游标有两种类型：显式游标、隐式游标。l四个游标属性四个游标属性：SQLFOUND、SQLNOTFOUND、SQLROWCOUNT、SQLISOPENl显式游标的使用步骤显式游标的使用步骤：个：个l记录变量和记录变量和%ROWTYPE l带参数的游标带参数的游标l游标游标FOR循环（循环游标）循环（循环游标）l游标中的更新和删除游标中的更新和删除lR
2、EF游标游标 Hands-On实训教程系列实训教程系列教学目标l掌握过程的用法掌握过程的用法l掌握函数的用法掌握函数的用法l理解过程与函数的相同点和不同点理解过程与函数的相同点和不同点l理解程序包的概念并能熟练应用理解程序包的概念并能熟练应用 Hands-On实训教程系列实训教程系列工作任务 1.用无参过程实现用无参过程实现“Hello World！”程序程序2.用带输入参数的过程向表中插入记录用带输入参数的过程向表中插入记录3.用带输出参数的过程查询表中的记录数用带输出参数的过程查询表中的记录数4.使用带输入输出参数的过程查询记录是否存使用带输入输出参数的过程查询记录是否存在在5.使用函数查
3、询部门信息使用函数查询部门信息6.使用程序包封装过程和函数使用程序包封装过程和函数 Hands-On实训教程系列实训教程系列相关实践知识相关实践知识l从开始菜单中打开从开始菜单中打开SQL*Plus工具，以工具，以SCOTT用户的身份登录到数据库用户的身份登录到数据库 Hands-On实训教程系列实训教程系列不带参数的过程不带参数的过程2-1l输入以下代码，创建一个最简单的输入以下代码，创建一个最简单的过程过程 l功能功能:显示显示”Hello World!”CREATE OR REPLACE PROCEDURE sp_helloWorld ASBEGINDBMS_OUTPUT.PUT_LIN
4、E(Hello World!);END sp_helloWorld;Hands-On实训教程系列实训教程系列不带参数的过程不带参数的过程2-2l执行过程执行过程l EXECUTE sp_helloWorld;l执行结果执行结果:l删除过程删除过程lDROP PROCEDURE sp_helloWorld;Hands-On实训教程系列实训教程系列带输入参数的过程带输入参数的过程3-1l实现的功能：向表实现的功能：向表dept中插入一条记录中插入一条记录l创建带创建带输入参数输入参数的过程，代码为：的过程，代码为：Hands-On实训教程系列实训教程系列带输入参数的过程带输入参数的过程3-2CRE
5、ATE 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_LINE(添加失败添加失败!原因为原因为:|SQLERRM);ROLLBACK;END sp_dept_insert;Hands-On实训教程系列实训教程系列带输入参数的过程带输入参数的过程3-3l分别输入两组数
6、据来执行过程分别输入两组数据来执行过程，结果如下：，结果如下：Hands-On实训教程系列实训教程系列带输出参数的过程带输出参数的过程3-1l实现功能：通过输出参数实现功能：通过输出参数count来得到来得到dept表表中的记录数中的记录数l创建带创建带输出参数输出参数的过程的过程CREATE OR REPLACE PROCEDURE sp_getcount(o_count OUT NUMBER)ASBEGINSELECT COUNT(*)INTO o_count FROM dept;END sp_getcount;Hands-On实训教程系列实训教程系列带输出参数的过程带输出参数的过程3-2
7、l编写一段匿名的编写一段匿名的PL/SQL块来执行过程块来执行过程 DECLAREcnt NUMBER;BEGINsp_getcount(cnt);DBMS_OUTPUT.PUT_LINE(dept表中的记录数为表中的记录数为:|cnt);END;Hands-On实训教程系列实训教程系列带输出参数的过程带输出参数的过程3-3l输出结果：输出结果：Hands-On实训教程系列实训教程系列带带IN OUT参数的过程参数的过程2-1l实现功能实现功能:查询某个部门名称在表查询某个部门名称在表dept中是否中是否已经存在已经存在 l创建带创建带IN OUT参数参数的过程，代码为：的过程，代码为：CRE
8、ATE 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;Hands-On实训教程系列实训教程系列带带IN OUT参数的过程参数的过程2-2l执行过程：执行过程：l输出结果：输出结果
9、：l部门名称部门名称ACCOUNTING存在存在!DECLARE l_iotest varchar2(20):=ACCOUNTING;BEGIN sp_dept_dname_exist(l_iotest);DBMS_OUTPUT.PUT_LINE(部门名称部门名称ACCOUNTING|l_iotest|!);END;Hands-On实训教程系列实训教程系列函数函数4-1l实现功能：实现功能：按部门编号查询出表按部门编号查询出表dept中的部门名称中的部门名称l创建一个创建一个函数函数，代码为：，代码为：Hands-On实训教程系列实训教程系列函数函数4-2lCREATE OR REPLACE
10、FUNCTION f_dept_getname_bynol(i_deptno NUMBER)lRETURN VARCHAR2lASll_dname VARCHAR2(14);lBEGINl SELECT dname INTO l_dname FROM dept WHERE deptno=i_deptno;l RETURN l_dname;lEXCEPTIONl WHEN NO_DATA_FOUND THENl RETURN 错误错误!该编号的部门不存在该编号的部门不存在!;lEND f_dept_getname_byno;Hands-On实训教程系列实训教程系列函数函数4-3l函数的调用函数的
11、调用及其输出结果及其输出结果：Hands-On实训教程系列实训教程系列函数函数4-4l删除函数删除函数 DROP FUNCTION f_dept_getname_byno Hands-On实训教程系列实训教程系列过程与函数小结过程与函数小结2-1l共同点：两者的实质都是已共同点：两者的实质都是已命名的命名的PL/SQL程程序块序块，即，即子程序子程序，它们是子程序的两种类型，它们是子程序的两种类型，存储在数据库中，可以从任何数据库客户端和存储在数据库中，可以从任何数据库客户端和前台应用程序中调用它们。前台应用程序中调用它们。Hands-On实训教程系列实训教程系列过程与函数小结过程与函数小结2
12、-2l不同点：不同点：过过程程函数函数参数模式可以是IN、OUT或IN OUT参数模式只能是IN模式在语法规范中不包含RETURN子句在语法规范中必须包含RETURN子句在可执行语句部分可以有RETURN语句，但其后不能加任何表达式在可执行语句部分至少应该包含一条RETURN expression语句可以用EXECUTE语句来执行不能用EXECUTE语句来执行 Hands-On实训教程系列实训教程系列程序包程序包5-1l利用利用程序包程序包封装过程封装过程sp_dept_insert和函数和函数f_dept_getname_bynol程序包规范部分的代码：程序包规范部分的代码：CREATE O
13、R REPLACE PACKAGE pkg_deptASPROCEDURE sp_dept_insert(i_deptno NUMBER,i_dname VARCHAR2,i_loc VARCHAR2);FUNCTION f_dept_getname_byno(i_deptno NUMBER)RETURN VARCHAR2;END pkg_dept;Hands-On实训教程系列实训教程系列程序包程序包5-2l程序包主体部分的代码程序包主体部分的代码 CREATE OR REPLACE PACKAGE BODY pkg_dept AS -过程sp_dept_insert PROCEDURE sp
14、_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);ROLLBACK;END sp_dept_insert;Hands-On实训教程系列实训教程系列程序包程序包5-3 -函数f_num_range FUNCTION f_dept_getname_byno(i_dep
15、tno 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 pkg_dept;Hands-On实训教程系列实训教程系列程序包程序包5-4l执行程序包中的过程和函数执行程序包中的过程和函数：Hands-On实训教程系列实训教程系列程序包
16、程序包5-5l删除程序包删除程序包：l只删除程序包主体：只删除程序包主体：DROP PACKAGE BODY pkg_dept;l删除整个程序包（规范删除整个程序包（规范+主体）：主体）：DROP PACKAGE pkg_dept;Hands-On实训教程系列实训教程系列小结：程序包的优点小结：程序包的优点l使用程序包的优点：使用程序包的优点：信息隐藏信息隐藏 模块化模块化 对多态的支持对多态的支持 性能更佳性能更佳 Hands-On实训教程系列实训教程系列过程返回结果集过程返回结果集2-1l在在Oracle中的过程不能象中的过程不能象SQL SERVER那样那样直接返回结果集，而必须借助于直
17、接返回结果集，而必须借助于REF游标游标 l程序包规范中的代码程序包规范中的代码:lCREATE OR REPLACE PACKAGE pkg_deptlASl TYPE deptcursor IS REF CURSOR;l PROCEDURE sp_dept_getall(dept_cur OUT deptcursor);lEND pkg_dept;Hands-On实训教程系列实训教程系列过程返回结果集过程返回结果集2-2l程序包主体中的代码程序包主体中的代码:l执行过程执行过程:CREATE OR REPLACE PACKAGE BODY pkg_deptAS PROCEDURE sp_d
18、ept_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;Hands-On实训教程系列实训教程系列OEM中管理过程、函数、程序包中管理过程、函数、程序包l请老师用浏览器打开请老师用浏览器打开OEM，演示在，演示在OEM中中管管理过程、函数、程序包理过程、函数、
19、程序包 Hands-On实训教程系列实训教程系列总结总结l过程和函数都是命名的过程和函数都是命名的PL/SQL程序块，合称子程序程序块，合称子程序l过程有三种参数模式：过程有三种参数模式：IN、OUT和和IN OUTl过过程程用用户户执执行行特特定定的的任任务务，函函数数用用于于执执行行任任务务并并返返回回值值l过程与函数的相同点和不同点过程与函数的相同点和不同点l程程序序包包是是对对过过程程、函函数数、变变量量、常常量量、游游标标、异异常常及及PL/SQL数据类型等的封装数据类型等的封装 l程序包由两部分组成，即包规范和包主体程序包由两部分组成，即包规范和包主体l使用程序包的优点使用程序包的
20、优点 Hands-On实训教程系列实训教程系列过程的定义过程的定义l定义：定义：l过程是一组为了过程是一组为了完成特定功能完成特定功能的、符合数据库的、符合数据库程序脚本规范的程序，经程序脚本规范的程序，经编译后编译后存储在数据库存储在数据库中，然后由一个应用程序或其他的中，然后由一个应用程序或其他的PL/SQL程程序调用。从根本上讲，过程就是序调用。从根本上讲，过程就是命名的命名的PL/SQL程序块程序块。Hands-On实训教程系列实训教程系列创建过程的语法创建过程的语法CREATE OR REPLACE PROCEDURE procedure_name(parameter_list)AS
21、|ISlocal_declarationsBEGINexecutable_statementsEXCEPTIONexception_handlersEND procedure_name;Hands-On实训教程系列实训教程系列执行过程执行过程l在在SQL提示符下提示符下：EXECUTE procedure_name(parameters_list)l在其它的过程、函数或匿名的在其它的过程、函数或匿名的PL/SQL块中的块中的可执行语句部分执行过程，直接写过程的名称可执行语句部分执行过程，直接写过程的名称（如有参数需带上）即可（如有参数需带上）即可 Hands-On实训教程系列实训教程系列删除过
22、程删除过程l删除过程的语法删除过程的语法:DROP PROCEDURE procedure_name;Hands-On实训教程系列实训教程系列过程的参数模式过程的参数模式lOracle 中过程的参数模式有三种：中过程的参数模式有三种：IN、OUT和和IN OUT，即输入、输出和输入输出，即输入、输出和输入输出l定义参数的语法：定义参数的语法：parameter_name IN|OUT|IN OUT DATATYPE:=|DEFAULT expression Hands-On实训教程系列实训教程系列IN模式参数模式参数lIN模式是输入模式，可以传递输入参数模式是输入模式，可以传递输入参数lIN模
23、式是默认模式，如果未指定参数的模式，模式是默认模式，如果未指定参数的模式，则该参数是则该参数是IN模式的模式的 l可以在参数列表中为可以在参数列表中为IN参数赋予一个默认值参数赋予一个默认值 Hands-On实训教程系列实训教程系列OUT模式参数模式参数lOUT模式是输出模式，可以传递输出参数模式是输出模式，可以传递输出参数 lOUT模式的参数，则必须明确指定模式的参数，则必须明确指定，即，即OUT必须注明必须注明l在返回到调用环境之前，应该先给在返回到调用环境之前，应该先给OUT模式的模式的参数赋值参数赋值 l不能为不能为OUT模式的参数赋默认值模式的参数赋默认值 Hands-On实训教程系
24、列实训教程系列IN OUT模式参数模式参数lIN OUT模式是一种比较特殊的模式，它兼有模式是一种比较特殊的模式，它兼有IN模式和模式和OUT模式的特点模式的特点 lIN OUT模式的参数，则必须明确指定模式的参数，则必须明确指定 l不能为不能为IN OUT模式的参数赋默认值模式的参数赋默认值 Hands-On实训教程系列实训教程系列函数的定义函数的定义l定义定义 函数的实质也是数据库中已函数的实质也是数据库中已命名的命名的PL/SQL程程序块序块。它的主要特性是函数。它的主要特性是函数能且只能返回一个能且只能返回一个值值。Hands-On实训教程系列实训教程系列创建函数的语法创建函数的语法C
25、REATE OR REPLACE FUNCTION function_name(parameter_list)RETURN DATATYPEAS|ISlocal_declarationsBEGINexecutable_statementsEXCEPTIONexception_handlersEND function_name;Hands-On实训教程系列实训教程系列函数定义的要点函数定义的要点l创建函数时通过创建函数时通过RETURN子句来定义函数的返子句来定义函数的返回类型回类型 l在函数体的任何地方用户都可以通过在函数体的任何地方用户都可以通过RETURN expression语句从函数返
- 配套讲稿：
如PPT文件的首页显示word图标，表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制：
部分文档作品中含有的国旗、国徽等图片，仅作为作品整体效果示例展示，禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词：
- oracle 存储 过程 函数 程序包
淘文阁 - 分享文档赚钱的网站所有资源均是用户自行上传分享，仅供网友学习交流，未经上传用户书面授权，请勿作他用。
限制150内