Oracle第7章存储过程操作

资源描述

《Oracle第7章存储过程操作》由会员分享，可在线阅读，更多相关《Oracle第7章存储过程操作（66页珍藏版）》请在金锄头文库上搜索。

1、第第7 7章章存储过程操作存储过程操作主编：O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程本章学习导航本章学习导航本章学习导航本章学习导航本章学习导航本章学习导航 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程本章学习要点本章学习要点本章学习要点本章学习要点（1）存储过程概述。）存储过程概述。（2）OEM创建、调用、查看、修改和删除创建、调用、查看、修改和删除存储存储过程过程。（3）PL/SQL创建、调用、查看、修改和删除创建、调用、查看、修改和删除存储过程存储过程。（4）创建、调用和删除）创建、调用和删除函数函数

2、。（5）定义包头、包体定义包头、包体。（6）包包中定义函数和存储过程。中定义函数和存储过程。建议课时：建议课时：12课时课时O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常量和变量常量和变量-常量常量l常量是指在程序运行期间其值不能改变的量。常量是指在程序运行期间其值不能改变的量。【例例1-1】定义常量定义常量PI（3.14159）。）。-设置打开控制台输出SET SERVEROUTPUT ON-声明一个变量或常量DECLARE PI CONSTANT NUMBER(6,5) := 3.1

3、4159;BEGIN-输出指定变量或常量的值 DBMS_OUTPUT.PUT_LINE(PI = | PI);END; 其中，PUT_LINE过程中使用的过程中使用的“|”用于连接输出内容的两部分用于连接输出内容的两部分 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常量和变量常量和变量-变量变量l变量是指由程序读取或赋值的存储单元，用于临时存储数据，变量中变量是指由程序读取或赋值的存储单元，用于临时存储数据，变量中的数据可以随着程序的运行而发生变化。每个变量都必须有一个特定的的数据可以随着程

4、序的运行而发生变化。每个变量都必须有一个特定的数据类型，可以是系统数据类型，也可以是自定义数据类型。数据类型，可以是系统数据类型，也可以是自定义数据类型。【例例1-2】编写计算圆面积的编写计算圆面积的PL/SQL块。块。SET SERVEROUTPUT ONDECLARE PI CONSTANT NUMBER(6,5) := 3.14159;-声明两个变量并赋初值 v_radiu FLOAT := 2; v_area FLOAT;BEGIN v_area := PI * v_radiu * v_radiu; DBMS_OUTPUT.PUT_LINE(Area = | v_area);END;

5、O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程条件结构条件结构-IF-IF结构结构【例例1-3】使用简单使用简单IF结构判断一个整数的奇偶性。结构判断一个整数的奇偶性。SET SERVEROUTPUT ONDECLARE v_number INTEGER := 518;BEGIN IF MOD(v_number, 2)=0 THEN DBMS_OUTPUT.PUT_LINE(v_number | 是一个偶数); ELSE DBMS_OUTPUT.PUT_LINE(v_number | 是一个奇

6、数); END IF;END;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程条件结构条件结构-IF-IF结构结构【例例1-4】使用复使用复杂杂IF结构输出结构输出3个个整数之中的最大者。整数之中的最大者。O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程条件结构条件结构-CASE-CASE结构结构【例例1-5】使用单一选择使用单一选择符进行等值比较的符进行等值比较的CASE结构将百分制成绩转换为结

7、构将百分制成绩转换为5分制成绩。分制成绩。 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程条件结构条件结构-CASE-CASE结构结构【例例1-6】使用多种条件使用多种条件进行非等值比较的进行非等值比较的CASE结构将百分制成绩转换为结构将百分制成绩转换为5分制成绩分制成绩。 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程循环结构循环结构-While-While循环循环【例例1-7】使用使用

8、WHILE循环求循环求1到到100的所有正整数之和。的所有正整数之和。SET SERVEROUTPUT ONDECLARE i INTEGER:=1; s INTEGER:=0; BEGIN WHILE i100; END LOOP; DBMS_OUTPUT.PUT_LINE(1+2+.+100= | s);END;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程循环结构循环结构-For-For循环循环使用FOR循环不需要显式声明循环控制变量的类型，而由PL/SQL隐式提供。默认情况下，循环控制

9、变量从下限值开始，每次循环结束后自动增加1，直至超过上限值为止；若指定REVERSE参数，则循环控制变量从上限值开始，每次循环结束后自动减1，直至低于下限值为止。 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程循环结构循环结构-For-For循环循环【例1-9】使用不带REVERSE参数的FOR循环求1到100的所有正整数之和。【例1-10】使用带REVERSE参数的FOR循环求1到100的所有正整数之和。O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂

10、案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程循环结构循环结构- -跳转语句跳转语句【例1-11】借助于GOTO跳转语句输出10以内第一个能同时被2和3整除的正整数。SET SERVEROUTPUT ONDECLARE num INTEGER:=1; BEGIN WHILE num=10 LOOP IF MOD(num, 2)=0 AND MOD(num, 3)=0 THEN GOTO display; END IF; num := num + 1; END LOOP; DBMS_OUTPUT.PUT_LINE(num);END;O Or ra ac cl le e数数据据库库管

11、管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常用系统函数常用系统函数- -数学函数数学函数O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常用系统函数常用系统函数- -数学函数数学函数【例例1-12】测试常用数学函数的用法。测试常用数学函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(-8的绝对值为的绝对值为 | ABS(-8); DBMS_OUTPUT.PUT_LINE(8的的3次幂为次

13、D;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常用系统函数常用系统函数- -字符串函数字符串函数O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常用系统函数常用系统函数- -字符串函数字符串函数【例例1-13】测试常用字符串函数的用法。测试常用字符串函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(a的的ACSCII值为值为 | ASCI

14、I(a); DBMS_OUTPUT.PUT_LINE(ACSCII值值97对应的字符为对应的字符为 | CHR(97); DBMS_OUTPUT.PUT_LINE(字符串字符串Hunan Railway的长度的长度为为 | LENGTH(Hunan Railway); DBMS_OUTPUT.PUT_LINE(将字符串将字符串Hunan Railway全部全部转换为大写形式为转换为大写形式为 | UPPER(Hunan Railway); DBMS_OUTPUT.PUT_LINE(将字符串将字符串Hunan Railway全部全部转换为小写形式为转换为小写形式为 | LOWER(Hunan R

15、ailway);END;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常用系统函数常用系统函数- -日期函数日期函数O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常用系统函数常用系统函数- -日期函数日期函数【例例1-14】测试常用日期函数的用法。测试常用日期函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(当前日期时间为 | SYSDAT

16、E); DBMS_OUTPUT.PUT_LINE(当前月份的最后一天的日期为 | LAST_DAY(SYSDATE); DBMS_OUTPUT.PUT_LINE(字符串对应日期 | TO_DATE(2007-5-24,YYYY-MM-DD); DBMS_OUTPUT.PUT_LINE(两个日期相差的月份 | MONTHS_BETWEEN(14-4月 -99,SYSDATE);END;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常用系统函数常用系统函数- -转换函数转换函数O Or ra ac

17、 cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程常用系统函数常用系统函数- -转换函数转换函数【例例1-15】测试常用转换函数的用法。测试常用转换函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(转换为数据库字符集的转换为数据库字符集的ASCII字符串为字符串为 | ASCIISTR(湖南铁道湖南铁道); DBMS_OUTPUT.PUT_LINE(将当前日期转换为字符串类型数据为将当前日期转换为字符串类型数据为 | CAST(SYSDATE AS VARCHAR

18、2); DBMS_OUTPUT.PUT_LINE(将字符串转换为将字符串转换为ROWID数据类型为数据类型为 | CHARTOROWID(AAAAFdl/#$); DBMS_OUTPUT.PUT_LINE(字符集转换字符集转换 | CONVERT(湖南铁道湖南铁道,US7ASCII,WE8ISO8859P1);END;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程 %TYPE%TYPE变量变量Oracle 9i以后的版本提供了以后的版本提供了%TYPE定义方法。这样当数据表的字定义方法。这样当数

19、据表的字段类型修改后，段类型修改后，PL/SQL程序中相应变量的类型也自动修改。程序中相应变量的类型也自动修改。【例例1-16】使用使用%TYPE获取查询的商品的基本信息。获取查询的商品的基本信息。SET SERVEROUTPUT ONDECLARE v_gId Goods.g_ID%TYPE; v_gName Goods.g_Name%TYPE; v_gPrice Goods.g_Price%TYPE; v_gNumber Goods.g_Number%TYPE;BEGIN SELECT g_ID,g_Name,g_Price,g_Number INTO v_gId,v_gName, v_g

20、Price, v_gNumber FROM SCOTT.Goods WHERE g_ID=010003; dbms_output.put_line(v_gId | / | v_gName |/ | v_gPrice | /| v_gNumber );END; O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程 %ROWTYPE%ROWTYPE变量变量与与%TYPE类型类似，也可以在不确定查询列的类型的情况下，使类型类似，也可以在不确定查询列的类型的情况下，使用用%ROWTYPE类型的变量存储查询的一

21、行数据类型的变量存储查询的一行数据。【例例1-17】使用使用%ROWTYPE获取查询的商品基本信息。获取查询的商品基本信息。SET SERVEROUTPUT ONDECLARE v_GoodRecord Goods%ROWTYPE;BEGIN SELECT * INTO v_GoodRecord FROM SCOTT.Goods WHERE g_ID=010003; dbms_output.put_line(v_GoodRecord.g_ID );dbms_output.put_line(v_GoodRecord.g_Name );dbms_output.put_line(v_GoodRec

22、ord.g_Price );dbms_output.put_line(v_GoodRecord.g_Number );END;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程异常处理异常处理为了提高应用程序的健壮性，开发人员必须考虑程序可能出现的各为了提高应用程序的健壮性，开发人员必须考虑程序可能出现的各种错误，并进行相应的处理。在种错误，并进行相应的处理。在Oracle中，为了处理中，为了处理PL/SQL应用程应用程序的各种错误，序的各种错误，Oracle提供了提供了三种类型三种类型的异常。的

23、异常。（1）预定义异常：用于处理常见的）预定义异常：用于处理常见的Oracle错误错误（2）非预定义异常：用于处理预定义异常所不能处理的）非预定义异常：用于处理预定义异常所不能处理的Oracle错错误误（3）自定义异常：用于处理于）自定义异常：用于处理于Oracle错误无关的其他情况错误无关的其他情况O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程异常处理异常处理系统预定义异常系统预定义异常O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1

24、使用使用PL/SQLPL/SQL编程编程异常处理异常处理系统预定义异常系统预定义异常【例例1-18】对对Goods表中的插入的重复商品号进行异常处理（使表中的插入的重复商品号进行异常处理（使用预定义异常）。用预定义异常）。SET SERVEROUTPUT ONBEGIN INSERT INTO SCOTT.Goods VALUES(010001,诺基亚6700 Slide,01,1500,0.9,20,to_date(2009-06-01,yyyy-mm-dd),pImage/010001.gif,热点,彩屏,1600万色,TFT,240320像素,2.2英寸);EXCEPTIONWHEN

25、DUP_VAL_ON_INDEX THENdbms_output.put_line(捕获到DUP_VAL_ON_INDEX异常);dbms_output.put_line(重复的商品编号);END;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程异常处理异常处理非预定义异常非预定义异常使用非预定义异常需要包括以下三个步骤：使用非预定义异常需要包括以下三个步骤：（1）在定义部分定义异常名；）在定义部分定义异常名；（2）在异常和）在异常和Oracle错误之间建立关联（需要使用伪过程错误之间建立关联（

26、需要使用伪过程EXCEPTION_INIT）；）；（3）在异常处理部分捕捉并处理异常。）在异常处理部分捕捉并处理异常。【例例1-19】删除商品类别表，并处理删除商品类别表，并处理ORA-2292错误（使用非预定义异常）错误（使用非预定义异常）。SET SERVEROUTPUT ONDECLARE e_FK EXCEPTION;-1、定义部分 PRAGMA EXCEPTION_INIT(e_FK,-2292); -2、建立关联关系BEGIN DELETE SCOTT.TypesWHERE t_Name=通信商品;EXCEPTION WHEN e_FK THEN -3、捕捉处理 DBMS_OUTP

27、UT.PUT_LINE(该类别已被使用);END;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例1 1使用使用PL/SQLPL/SQL编程编程异常处理异常处理自定义异常自定义异常使用自定义异常时，需要包括以下三个步骤：使用自定义异常时，需要包括以下三个步骤：（1）需要在定义部分（）需要在定义部分（DECLARE）定义异常；）定义异常；（2）再执行部分（）再执行部分（BEGIN）触发异常（使用）触发异常（使用RAISE语句）；语句）；（3）在异常处理部分（）在异常处理部分（EXCEPTION）捕捉并处理异常。）捕捉并处理异常。 O Or r

28、a ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程7.2 7.2 存储过程概述存储过程概述视图特点视图特点在在Oracle中，可以在数据库中定义子程序，在子程序中将一些固定的操中，可以在数据库中定义子程序，在子程序中将一些固定的操作集中起来，由作集中起来，由Oracle数据库服务器完成，以完成某个特定的功能。这数据库服务器完成，以完成某个特定的功能。这种子程序称为存储过程（种子程序称为存储过程（Proce-Dure）。使用存储过程具有如下的优点：）。使用存储过程具有如下的优点：（1）存储过程在服务器端运行，）存储过程在服务器端运行，执行速度快执行速度快；（2）存储过程执

29、行一次后驻留在）存储过程执行一次后驻留在Oracle数据库服务器的数据库服务器的高速高速Cache中，中，以后再次执行存储过程时，只需从高速以后再次执行存储过程时，只需从高速Cache中调用已经编译好的代码中调用已经编译好的代码即可，从而提高了系统性能；即可，从而提高了系统性能；（3）存储过程确保了）存储过程确保了数据库的安全数据库的安全。使用存储过程，可以在禁止用户直。使用存储过程，可以在禁止用户直接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的存储过程。存储过程。（4）自动完成自动完成需要预先执行的任务。存储过程可

30、以设置为系统启动时自需要预先执行的任务。存储过程可以设置为系统启动时自动执行，而不必在系统启动后再进行手动操作，从而方便了用户的使用，动执行，而不必在系统启动后再进行手动操作，从而方便了用户的使用，可以自动完成一些需要预先执行的任务。可以自动完成一些需要预先执行的任务。 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例2 2使用使用OEMOEM管理存储过程管理存储过程掌握在OEM中创建存储过程、执行存储过程的方法。案例学习目标案例学习目标 OEM中创建存储过程、执行存储过程。案例知识要点案例知识要点 O Or ra ac cl le e

31、数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例2 2使用使用OEMOEM管理存储过程管理存储过程案例完成步骤案例完成步骤（1）启动）启动OEM后，依次选择后，依次选择“方案方案”、“程序程序”下的下的“过程过程”，进入，进入“过程过程”页面页面（2）单击单击“创建创建”按钮，进入按钮，进入“创建过程创建过程”对话框，输对话框，输入新建存储过程的名称入新建存储过程的名称up_NameByID，编写存储过程的，编写存储过程的内容内容教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例2 2使用使用OE

32、MOEM管理存储过程管理存储过程案例完成步骤案例完成步骤- -创建存储过程创建存储过程（1）启动）启动OEM后，依次选择后，依次选择“方案方案”、“程序程序”下的下的“过程过程”，进入，进入“过程过程”页面页面（2）单击单击“创建创建”按钮，进入按钮，进入“创建过程创建过程”对话框，输对话框，输入新建存储过程的名称入新建存储过程的名称up_NameByID，编写存储过程的，编写存储过程的内容内容教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例2 2使用使用OEMOEM管理存储过程管理存储过程案例完成步骤案例完成

33、步骤- -查看存储过程查看存储过程在在OEM中，进入指定方案的中，进入指定方案的“过程过程”页面，在过程列表页面，在过程列表中选择要查看的过程，单击中选择要查看的过程，单击“查看查看”按钮，进入按钮，进入“查看过查看过程程”页面页面教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例2 2使用使用OEMOEM管理存储过程管理存储过程案例完成步骤案例完成步骤- -修改存储过程修改存储过程（1）在）在OEM中，进入指定方案的中，进入指定方案的“过程过程”页面，在过程页面，在过程列表中选择要查看的过程，单击列表中选择要查看

34、的过程，单击“编辑编辑”按钮，进入按钮，进入“编编辑过程辑过程”页面页面教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例2 2使用使用OEMOEM管理存储过程管理存储过程案例完成步骤案例完成步骤- -删除存储过程删除存储过程（1）在）在OEM中，进入指定方案的中，进入指定方案的“过程过程”页面，在过程页面，在过程列表中选择要查看的过程，单击列表中选择要查看的过程，单击“删除删除”按钮，进入按钮，进入“确确认删除认删除”页面页面（2）单击“是”按钮，删除过程up_NameByID 教教师师演演示示讲讲解解O Or

35、ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理存储过程学习使用PL/SQL语句创建存储过程、修改存储过程、编译存储过程和执行存储过程的方法。案例学习目标案例学习目标使用CREATE OR REPLACE PROCEDURE创建和修改存储过程、使用ALTER PROCEDURE编译存储过程、执行存储过程的几种方法。案例知识要点案例知识要点 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理

36、存储过程案例完成步骤案例完成步骤 1创建存储过程创建存储过程使用使用PL/SQL创建存储过程的基本语法格式为：创建存储过程的基本语法格式为：CREATE OR REPLACE PROCEDURE 用户方用户方案案. ( 参数参数1 参数模式参数模式数据类型数据类型， ) IS | AS参数参数1 数据类型数据类型，BEGINPL/SQL语句语句END 存储过程名存储过程名;2执行存储过程执行存储过程使用使用PL/SQL执行存储过程的基本语法格式为：执行存储过程的基本语法格式为：DECLARE参数参数1 数据类型数据类型， BEGIN EXECUTE 用户方案用户方案. ( 参数参数1 ，

37、) ;END;教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤【例例3-1】创建简单存储过程，显示当前的系统时间。创建简单存储过程，显示当前的系统时间。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE SCOTT.up_CurrentTimeASBEGINDBMS_OUTPUT.PUT_LINE(SYSDATE);END up_CurrentTime;（2）执行存储过程）执行存储过程BEGIN

38、SCOTT. up_CurrentTime ();END;教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤【例例3-2】通过存储过程添加用户记录。通过存储过程添加用户记录。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_InsertUser ASBEGIN INSERT INTO SCOTT.Users VALUES(88,存储过程存储过程,普通普通,storeproc);EXCEPTI

39、ON WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE(重复的用编号重复的用编号); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(发生其他错误发生其他错误);END up_InsertUser;（2）执行存储过程）执行存储过程EXEC up_InsertUser;（3）查询）查询Users表表存储过程执行后，使用下列语句查看存储过程执行后，使用下列语句查看User表的记录情况。表的记录情况。SELECT * FROM USERS;教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应

40、用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤【例例3-3】创建存储过程，根据商品类别编号统计该类型所有商创建存储过程，根据商品类别编号统计该类型所有商品的总数量（带品的总数量（带IN参数）。参数）。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_CountByTid(tid in VARCHAR2)AS total NUMBER; BEGIN SELECT COUNT(*) INTO total FROM SCOTT.GOODS WHERE t_ID=tid; DBMS

41、_OUTPUT.PUT_LINE(total); END;（2）执行存储过程）执行存储过程 BEGIN -up_CountByTid(01); up_CountByTid(tid=01); END;up_CountByTid(01)：位置表示法传递参数：位置表示法传递参数up_CountByTid(tid=01)：名称表示法传递参数：名称表示法传递参数教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤【例例3-4】创建存储过程，根据商

42、品类别编号统计该类型创建存储过程，根据商品类别编号统计该类型所有商品的总数量，缺省情况下统计类别编号为所有商品的总数量，缺省情况下统计类别编号为“02”的商品的总数量（带缺省值的的商品的总数量（带缺省值的IN参数）。参数）。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_CountByTid(tid in VARCHAR2 DEFAULT 02)AS total NUMBER; BEGIN SELECT COUNT(*) INTO total FROM SCOTT.GOODS WHERE t_ID=tid; DBMS_OUTPUT.PUT_LINE

43、(total); END;教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤【例例3-5】创建存储过程，根据商品的编号获得商品的名创建存储过程，根据商品的编号获得商品的名称和类别编号（带称和类别编号（带IN和和OUT参数）。参数）。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_GetByID(gid in VARCHAR2,gname out GOODS.g_Name%TYPE,tid

44、out GOODS.t_ID%TYPE)ASBEGIN SELECT g_Name,t_ID INTO gname,tid FROM SCOTT.Goods WHERE g_ID=gid;EXCEPTION WHEN NO_DATA_FOUND THEN gname:=null; tid:=null;END up_GetByID;教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤（2）执行存储过程）执行存储过程-调用带输出参数的存储过

45、程调用带输出参数的存储过程variable v_name varchar2(50);variable v_id varchar2(2);exec up_GetByID(020001,:v_name,:v_id);print v_name; print v_id;教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤【例例3-5】编写存储过程实现两个数交换，并在比较两个编写存储过程实现两个数交换，并在比较两个数的大小中调用该存储过程（带数的

46、大小中调用该存储过程（带INOUT参数）参数）教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤查看存储过程查看存储过程【例例3-6】查看用户方案查看用户方案SCOTT的存储过程的存储过程up_GetByID的信息。的信息。DESC SCOTT.up_GetByID;教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例3 3使用使用PL/SQLPL/SQL管理存

47、储过程管理存储过程案例完成步骤案例完成步骤删除存储过程删除存储过程【例例3-7】删除用户方案删除用户方案SCOTT的存储过程的存储过程up_CurrentTime。DROP PROCEDURE SCOTT. up_CurrentTime;教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例4 4管理函数管理函数学习在SQL Developer和PL/SQL中创建函数、调用函数、删除函数的方法。案例学习目标案例学习目标 SQL Developer中创建函数、SQL Developer删除函数、PL/SQL创建函数、PL/

48、SQL调用函数、PL/SQL删除函数。案例知识要点案例知识要点 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例4 4管理函数管理函数案例完成步骤案例完成步骤创建函数创建函数函函数数（Function）与与存存储储过过程程类类似似，也也是是组组成成一一个个子子程程序序的的一一组组PL/SQL语语句句。函函数数接接受受0个个或或多多个个输输入入参参数数，仅仅返返回回一一个个值值，返回值的数据类型在创建函数时定义。返回值的数据类型在创建函数时定义。1使用使用SQL Developer创建函数创建函数（1）在在SQL Developer中中

49、右右击击Functions选选项项，从从快快捷捷菜菜单单中中选择选择“New Function”项，将开始创建函数项，将开始创建函数教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例4 4管理函数管理函数案例完成步骤案例完成步骤创建函数创建函数（2）在在打打开开的的“Create PL/SQL Function”对对话话框框内内，指指定定用用户户方方案案为为 SCOTT，设设置置函函数数名名称称为为fn_CountTypes，创建一个统计商品种类的函数，创建一个统计商品种类的函数教教师师

50、演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例4 4管理函数管理函数案例完成步骤案例完成步骤创建函数创建函数（3）单单击击“确确定定”按按钮钮，开开始始编编辑辑函函数数的的定定义义，以以实实现现统计商品种类的函数。统计商品种类的函数。教教师师演演示示讲讲解解 CREATE OR REPLACE FUNCTION fn_CountTypes RETURN NUMBER ASCOUNTER NUMBER;BEGIN SELECTCOUNT (t_ID) INTO COUNTER FROM SCOTT.GOODS; RET

51、URN COUNTER;END fn_CountTypes;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例4 4管理函数管理函数案例完成步骤案例完成步骤执行函数执行函数（4）展展开开Functions选选项项，右右击击函函数数fn_CountTypes，从快捷菜单中选择从快捷菜单中选择“Run”项，打开运行函数对话框项，打开运行函数对话框。教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例4 4管理函数管理函数案例完成步骤案例完成步骤PL/SQLPL/SQ

52、L创建函数创建函数【例例4-1】在用户方案在用户方案SCOTT中创建函数中创建函数fn_TOTALVALUE，它，它根据客户编号查询该客户的订单总金额。根据客户编号查询该客户的订单总金额。CREATE OR REPLACE FUNCTION SCOTT.fn_TOTALVALUE(cid SCOTT.ORDERS.c_ID%TYPE)RETURN NUMBERAS T_VALUENUMBER;BEGIN SELECT SUM(d_Price *d_Number) INTO T_VALUE FROM SCOTT.ORDERDETAILS OD JOIN SCOTT.ORDERS O ON OD

53、.o_ID = O.o_ID WHERE c_ID = cid; RETURN T_VALUE;END fn_TOTALVALUE;教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例4 4管理函数管理函数案例完成步骤案例完成步骤调用函数调用函数【例例4-2】调用用户方案调用用户方案SCOTT中的函数中的函数DECLARE cid SCOTT.ORDERS.c_ID%TYPE; BEGIN cid:=C0001; DBMS_OUTPUT.PUT_LINE(cid | : | SCOTT.fn_TOTALVALUE(c

54、id); END;教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例4 4管理函数管理函数案例完成步骤案例完成步骤删除函数删除函数 1使用使用SQL Developer删除函数删除函数（1）在）在SQL Developer中右击中右击Functions选项，从快捷选项，从快捷菜单中选择菜单中选择“Drop”项，将会删除选定的函数项，将会删除选定的函数教教师师演演示示讲讲解解 2使用使用PL/SQL命令删除函数命令删除函数【例例4-3】删除用户方案删除用户方案SCOTT中的函数中的函数fn_CountTypes。DR

55、OP FUNCTION fn_CountTypes;O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例5 5应用包应用包学习学习SQL Developer和和PL/SQL定义包头、定义包体和使用包的方法定义包头、定义包体和使用包的方法。案例学习目标案例学习目标 SQL Developer定义包头、定义包头、SQL Developer定义包体、定义包体、PL/SQL定义包定义包头、头、PL/SQL定义包体和使用包定义包体和使用包。案例知识要点案例知识要点 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂

56、案例课堂案例5 5应用包应用包包概述包概述 l包（包（Package）可将一些有联系的对象放置在其内部，构）可将一些有联系的对象放置在其内部，构成一个成一个逻辑分组逻辑分组，这些对象包括存储过程、函数、游标、，这些对象包括存储过程、函数、游标、自定义的类型（例如自定义的类型（例如PL/SQL表和记录）和变量等。表和记录）和变量等。l实际上，包相当于一个命名的声明部分，任何能在块定义实际上，包相当于一个命名的声明部分，任何能在块定义部分出现的对象都可以在包中出现，用户可以从其他部分出现的对象都可以在包中出现，用户可以从其他PL/SQL块中对包进行引用。块中对包进行引用。l包拥有两个独立的部分：包

57、拥有两个独立的部分：包头包头和和包体包体，它们都存储在数据，它们都存储在数据字典中。定义一个包，要分别定义包头和包体。字典中。定义一个包，要分别定义包头和包体。l包与存储过程和函数的一个显著区别是包仅能存储在非本包与存储过程和函数的一个显著区别是包仅能存储在非本地的数据库中。可以将存储过程和函数定义在包中，包被地的数据库中。可以将存储过程和函数定义在包中，包被保存在高速缓存中，这样体现了模块化编程的特点，使得保存在高速缓存中，这样体现了模块化编程的特点，使得应用系统的开发更为灵活，运行效率更高。应用系统的开发更为灵活，运行效率更高。l存储过程和函数被加入到包中时，存储过程和函数的声明存储过程和

58、函数被加入到包中时，存储过程和函数的声明放在包头部分，而执行代码则放在包体部分。放在包头部分，而执行代码则放在包体部分。教教师师演演示示讲讲解解O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例5 5应用包应用包案例完成步骤案例完成步骤SQL DeveloperSQL Developer定义包头定义包头（1）使用）使用SQL Developer定义包头定义包头（a）在SQL Developer中右击Packages选项，从快捷菜单中选择“New Package”项（b）在打开的“Create PL/SQL Package”对话框中，输入包

59、的名称（如pkg_Goods）（c）单击“确定”按钮，开始编写包头定义部分教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例5 5应用包应用包案例完成步骤案例完成步骤PL/SQLPL/SQL定义包头定义包头【例5-1】创建包pkg_DisplayGoods，其包括一个存储过程和一个函数，其中存储过程实现根据商品编号查询商品名称、类别名称和商品价格的功能，函数实现根据商品类别编号返回该类别商品的总库存量的功能。教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程

60、程课堂案例课堂案例5 5应用包应用包案例完成步骤案例完成步骤SQL DeveloperSQL Developer定义包体定义包体（1）使用SQL Developer定义包体（a）在SQL Developer的Package选项中右击已经定义包头的pkg_Goods选项，从快捷菜单中选择“Create Body”项（b）开始编写包体定义部分教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例5 5应用包应用包案例完成步骤案例完成步骤PL/SQLPL/SQL定义包体定义包体【例5-2】实现包pkg_DisplayGo

61、ods中存储过程和函数的功能。教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例5 5应用包应用包案例完成步骤案例完成步骤使用包使用包 1包的初始化包的初始化当第一次调用打包子程序时，该包将进行初始化，即将该当第一次调用打包子程序时，该包将进行初始化，即将该包从辅存中读入内存，并启动调用的子程序的编译代码。包从辅存中读入内存，并启动调用的子程序的编译代码。2引用包中对象引用包中对象在包中定义的任何对象既可以在包内使用，也可以在包外在包中定义的任何对象既可以在包内使用，也可以在包外使用。在外部引用包中对象时，可以通过使

62、用包名作为前使用。在外部引用包中对象时，可以通过使用包名作为前缀对其进行引用缀对其进行引用教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例5 5应用包应用包案例完成步骤案例完成步骤使用包使用包【例例5-3】调用包调用包pkg_DisplayGoods中存储过程和函数，查询商品编中存储过程和函数，查询商品编号为号为“200708011430”的商品信息和统计商品类别编号为的商品信息和统计商品类别编号为“01”的商品的商品总数量。总数量。教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理

63、理与与应应用用实实例例教教程程课堂案例课堂案例5 5应用包应用包OracleOracle内置包内置包 Oracle 11g提供了很多具有特定功能的内置包提供了很多具有特定功能的内置包教教师师演演示示讲讲解解 O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程编写编写PL/SQL语句块，使用语句块，使用IF语句求出三个数中最大的数语句求出三个数中最大的数任务任务1 1编写编写PL/SQL语句块，使用语句块，使用LOOP和和FOR-IN-LOOP-END LOOP循环计算循环计算1+3+5+99的值的值任务任务2 2创建存储过程创建存储过程up_Borrow

64、，要求该存储过程返回未还图书的借阅信息，包，要求该存储过程返回未还图书的借阅信息，包括借书人、借书日期、图书名称和图书作者括借书人、借书日期、图书名称和图书作者任务任务3 3O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程执行“【任务3】”所创建的存储过程up_Borrow，查询所有未还图书的详细信息。任务任务4 4创建存储过程up_ Borrow ByID，要求该存储过程能够根据输入的读者号返回该读者的所有借阅信息，包括借书日期、还书日期、图书名称和图书作者。任务任务5 5执行“【任务5】”所创建的存储过程up_ Borrow ByID，查询读者号为“0016584”的借阅信息。任务任务6 6

展开阅读全文

Oracle第7章存储过程操作

最新文档