存储过程操作理论课程

1、存储过程操作理论课程本章学习导航本章学习导航本章学习导航本章学习导航 本章学习要点本章学习要点（1）存储过程概述。）存储过程概述。（2）OEM创建、调用、查看、修改和删除存储创建、调用、查看、修改和删除存储过程。过程。（3）PL/SQL创建、调用、查看、修改和删除创建、调用、查看、修改和删除存储过程。存储过程。（4）创建、调用和删除函数。）创建、调用和删除函数。（5）定义包头、包体。）定义包头、包体。（6）包中定义函数和存储过程。）包中定义函数和存储过程。 建议课时：建议课时：12课时课时课堂案例1使用PL/SQL编程 常量和变量常量和变量-常量常量l常量是指在程序运行期间其值不能改变的量。常

2、量是指在程序运行期间其值不能改变的量。 【例例1-1】定义常量定义常量PI（3.14159）。）。-设置打开控制台输出SET SERVEROUTPUT ON-声明一个变量或常量DECLARE PI CONSTANT NUMBER(6,5) := 3.14159;BEGIN-输出指定变量或常量的值 DBMS_OUTPUT.PUT_LINE(PI = | PI);END; 其中，PUT_LINE过程中使用的过程中使用的“|”用于连接输出内容的两部分用于连接输出内容的两部分 课堂案例1使用PL/SQL编程 常量和变量常量和变量-变量变量l变量是指由程序读取或赋值的存储单元，用于临时存储数据，变量中变

3、量是指由程序读取或赋值的存储单元，用于临时存储数据，变量中的数据可以随着程序的运行而发生变化。每个变量都必须有一个特定的的数据可以随着程序的运行而发生变化。每个变量都必须有一个特定的数据类型，可以是系统数据类型，也可以是自定义数据类型。数据类型，可以是系统数据类型，也可以是自定义数据类型。 【例例1-2】编写计算圆面积的编写计算圆面积的PL/SQL块。块。SET SERVEROUTPUT ONDECLARE PI CONSTANT NUMBER(6,5) := 3.14159;-声明两个变量并赋初值 v_radiu FLOAT := 2; v_area FLOAT;BEGIN v_area :

4、= PI * v_radiu * v_radiu; DBMS_OUTPUT.PUT_LINE(Area = | v_area);END;课堂案例1使用PL/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_num

5、ber | 是一个奇数); END IF;END;课堂案例1使用PL/SQL编程 条件结构条件结构-IF-IF结构结构【例例1-4】使用复使用复杂杂IF结构输出结构输出3个个整数之中的最大者。整数之中的最大者。课堂案例1使用PL/SQL编程 条件结构条件结构-CASE-CASE结构结构【例例1-5】使用单一选择使用单一选择符进行等值比较的符进行等值比较的CASE结构将百分制成绩转换为结构将百分制成绩转换为5分制成绩。分制成绩。 课堂案例1使用PL/SQL编程 条件结构条件结构-CASE-CASE结构结构【例例1-6】使用多种条件使用多种条件进行非等值比较的进行非等值比较的CASE结构将百分制成

6、绩转换为结构将百分制成绩转换为5分制成绩分制成绩 。 课堂案例1使用PL/SQL编程 循环结构循环结构-While-While循环循环【例例1-7】使用使用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;表达式循环体FALSETRUE课堂案例1使用PL/SQL编程 循环结构循环结构-For-For循环循环使用FOR循环不需要显式

7、声明循环控制变量的类型，而由PL/SQL隐式提供。默认情况下，循环控制变量从下限值开始，每次循环结束后自动增加1，直至超过上限值为止；若指定REVERSE参数，则循环控制变量从上限值开始，每次循环结束后自动减1，直至低于下限值为止。 循环控制变量=下限值循环控制变量=上限值循环体循环控制变量减1TRUEFALSE 课堂案例1使用PL/SQL编程 循环结构循环结构-For-For循环循环【例1-9】使用不带REVERSE参数的FOR循环求1到100的所有正整数之和。【例1-10】使用带REVERSE参数的FOR循环求1到100的所有正整数之和。课堂案例1使用PL/SQL编程 循环结构循环结构-

8、-跳转语句跳转语句【例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;课堂案例1使用PL/SQL编程 常用系统函数常用系统函数- -数学函数数学函数课堂案例1使用PL/SQL编程 常用系统函数常用系统

9、函数- -数学函数数学函数【例例1-12】测试常用数学函数的用法。测试常用数学函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(-8的绝对值为的绝对值为 | ABS(-8); DBMS_OUTPUT.PUT_LINE(8的的3次幂为次幂为 | POWER(8, 3); DBMS_OUTPUT.PUT_LINE(8的平方根为的平方根为 | SQRT(8); DBMS_OUTPUT.PUT_LINE(3.14159四舍五入到小数点后四舍五入到小数点后3位为位为 | ROUND(3.14159, 3); DBMS_OUTPUT.PUT_LINE(

10、e = | EXP(1); DBMS_OUTPUT.PUT_LINE(大于或等于大于或等于-32.5的最小整数为的最小整数为 | CEIL(-32.5); DBMS_OUTPUT.PUT_LINE(小于或等于小于或等于-32.5的最大整数为的最大整数为 | FLOOR(-32.5);END;课堂案例1使用PL/SQL编程 常用系统函数常用系统函数- -字符串函数字符串函数课堂案例1使用PL/SQL编程 常用系统函数常用系统函数- -字符串函数字符串函数【例例1-13】测试常用字符串函数的用法。测试常用字符串函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.P

11、UT_LINE(a的的ACSCII值为值为 | ASCII(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全部全部转换为小

12、写形式为转换为小写形式为 | LOWER(Hunan Railway);END;课堂案例1使用PL/SQL编程 常用系统函数常用系统函数- -日期函数日期函数课堂案例1使用PL/SQL编程 常用系统函数常用系统函数- -日期函数日期函数【例例1-14】测试常用日期函数的用法。测试常用日期函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(当前日期时间为 | SYSDATE); DBMS_OUTPUT.PUT_LINE(当前月份的最后一天的日期为 | LAST_DAY(SYSDATE); DBMS_OUTPUT.PUT_LINE(字符串对应日期

13、 | TO_DATE(2007-5-24,YYYY-MM-DD); DBMS_OUTPUT.PUT_LINE(两个日期相差的月份 | MONTHS_BETWEEN(14-4月 -99,SYSDATE);END;课堂案例1使用PL/SQL编程 常用系统函数常用系统函数- -转换函数转换函数课堂案例1使用PL/SQL编程 常用系统函数常用系统函数- -转换函数转换函数【例例1-15】测试常用转换函数的用法。测试常用转换函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(转换为数据库字符集的转换为数据库字符集的ASCII字符串为字符串为 | ASC

14、IISTR(湖南铁道湖南铁道); DBMS_OUTPUT.PUT_LINE(将当前日期转换为字符串类型数据为将当前日期转换为字符串类型数据为 | CAST(SYSDATE AS VARCHAR2); DBMS_OUTPUT.PUT_LINE(将字符串转换为将字符串转换为ROWID数据类型为数据类型为 | CHARTOROWID(AAAAFdl/#$); DBMS_OUTPUT.PUT_LINE(字符集转换字符集转换 | CONVERT(湖南铁道湖南铁道,US7ASCII,WE8ISO8859P1);END;课堂案例1使用PL/SQL编程 %TYPE%TYPE变量变量Oracle 9i以后的版本

15、提供了以后的版本提供了%TYPE定义方法。这样当数据表的字定义方法。这样当数据表的字段类型修改后，段类型修改后，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_

16、Price,g_Number INTO v_gId,v_gName, v_gPrice, v_gNumber FROM SCOTT.Goods WHERE g_ID=010003; dbms_output.put_line(v_gId | / | v_gName |/ | v_gPrice | /| v_gNumber );END; 课堂案例1使用PL/SQL编程 %ROWTYPE%ROWTYPE变量变量与与%TYPE类型类似，也可以在不确定查询列的类型的情况下，使类型类似，也可以在不确定查询列的类型的情况下，使用用%ROWTYPE类型的变量存储查询的一行数据类型的变量存储查询的一行数据 。【

17、例例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_GoodRecord.g_Price );dbms_o

18、utput.put_line(v_GoodRecord.g_Number );END;课堂案例1使用PL/SQL编程 异常处理异常处理为了提高应用程序的健壮性，开发人员必须考虑程序可能出现的为了提高应用程序的健壮性，开发人员必须考虑程序可能出现的各种错误，并进行相应的处理。在各种错误，并进行相应的处理。在Oracle中，为了处理中，为了处理PL/SQL应用应用程序的各种错误，程序的各种错误，Oracle提供了三种类型的异常。提供了三种类型的异常。（1）预定义异常：用于处理常见的）预定义异常：用于处理常见的Oracle错误错误（2）非预定义异常：用于处理预定义异常所不能处理的）非预定义异常：用于

19、处理预定义异常所不能处理的Oracle错错误误（3）自定义异常：用于处理于）自定义异常：用于处理于Oracle错误无关的其他情况错误无关的其他情况课堂案例1使用PL/SQL编程 异常处理异常处理系统预定义异常系统预定义异常课堂案例1使用PL/SQL编程 异常处理异常处理系统预定义异常系统预定义异常【例例1-18】对对Goods表中的插入的重复商品号进行异常处理（使表中的插入的重复商品号进行异常处理（使用预定义异常）。用预定义异常）。SET SERVEROUTPUT ONBEGIN INSERT INTO SCOTT.Goods VALUES(010001,诺基亚6700 Slide,01,15

20、00,0.9,20,to_date(2009-06-01,yyyy-mm-dd),pImage/010001.gif,热点,彩屏,1600万色,TFT,240320像素,2.2英寸);EXCEPTIONWHEN DUP_VAL_ON_INDEX THENdbms_output.put_line(捕获到DUP_VAL_ON_INDEX异常);dbms_output.put_line(重复的商品编号);END;课堂案例1使用PL/SQL编程 异常处理异常处理非预定义异常非预定义异常使用非预定义异常需要包括以下三个步骤：使用非预定义异常需要包括以下三个步骤：（1）在定义部分定义异常名；）在定义部分定

21、义异常名；（2）在异常和）在异常和Oracle错误之间建立关联（需要使用伪过程错误之间建立关联（需要使用伪过程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_Nam

22、e=通信商品;EXCEPTION WHEN e_FK THEN -3、捕捉处理 DBMS_OUTPUT.PUT_LINE(该类别已被使用);END;课堂案例1使用PL/SQL编程 异常处理异常处理自定义异常自定义异常使用自定义异常时，需要包括以下三个步骤：使用自定义异常时，需要包括以下三个步骤：（1）需要在定义部分（）需要在定义部分（DECLARE）定义异常；）定义异常；（2）再执行部分（）再执行部分（BEGIN）触发异常（使用）触发异常（使用RAISE语句）；语句）；（3）在异常处理部分（）在异常处理部分（EXCEPTION）捕捉并处理异常。）捕捉并处理异常。 7.2 存储过程概述 视图特点

23、视图特点在在Oracle中，可以在数据库中定义子程序，在子程序中将一些固定的操中，可以在数据库中定义子程序，在子程序中将一些固定的操作集中起来，由作集中起来，由Oracle数据库服务器完成，以完成某个特定的功能。这数据库服务器完成，以完成某个特定的功能。这种子程序称为存储过程（种子程序称为存储过程（Proce-Dure）。使用存储过程具有如下的优点：）。使用存储过程具有如下的优点：（1）存储过程在服务器端运行，执行速度快；）存储过程在服务器端运行，执行速度快；（2）存储过程执行一次后驻留在）存储过程执行一次后驻留在Oracle数据库服务器的高速数据库服务器的高速Cache中，中，以后再次执行存

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

25、了用户的使自动执行，而不必在系统启动后再进行手动操作，从而方便了用户的使用，可以自动完成一些需要预先执行的任务。用，可以自动完成一些需要预先执行的任务。 课堂案例2使用OEM管理存储过程 掌握在OEM中创建存储过程、执行存储过程的方法 。案例学习目标案例学习目标 OEM中创建存储过程、执行存储过程 。案例知识要点案例知识要点 课堂案例2使用OEM管理存储过程案例完成步骤案例完成步骤 添加标添加标题文字题文字（1）启动）启动OEM后，依次选择后，依次选择“方案方案”、“程序程序”下的下的“过程过程”，进入，进入“过程过程”页面页面 （2） 单击单击“创建创建”按钮，进入按钮，进入“创建过程创建过

26、程”对话框，输对话框，输入新建存储过程的名称入新建存储过程的名称up_NameByID，编写存储过程的，编写存储过程的内容内容 添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例2使用OEM管理存储过程案例完成步骤案例完成步骤- -创建存储过程创建存储过程 添加标添加标题文字题文字（1）启动）启动OEM后，依次选择后，依次选择“方案方案”、“程序程序”下的下的“过程过程”，进入，进入“过程过程”页面页面 （2） 单击单击“创建创建”按钮，进入按钮，进入“创建过程创建过程”对话框，输对话框，输入新建存储过程的名称入新建存储过程的名称up_NameByID，编写存储过程的，编写存储过程的内容

27、内容 添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例2使用OEM管理存储过程案例完成步骤案例完成步骤- -查看存储过程查看存储过程 添加标添加标题文字题文字在在OEM中，进入指定方案的中，进入指定方案的“过程过程”页面，在过程列表页面，在过程列表中选择要查看的过程，单击中选择要查看的过程，单击“查看查看”按钮，进入按钮，进入“查看查看过程过程”页面页面 添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例2使用OEM管理存储过程案例完成步骤案例完成步骤- -修改存储过程修改存储过程 添加标添加标题文字题文字（1）在）在OEM中，进入指定方案的中，进入指定方案的“过程过程”页面，在

28、过程页面，在过程列表中选择要查看的过程，单击列表中选择要查看的过程，单击“编辑编辑”按钮，进入按钮，进入“编辑过程编辑过程”页面页面 添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例2使用OEM管理存储过程案例完成步骤案例完成步骤- -删除存储过程删除存储过程 添加标添加标题文字题文字（1）在）在OEM中，进入指定方案的中，进入指定方案的“过程过程”页面，在过程页面，在过程列表中选择要查看的过程，单击列表中选择要查看的过程，单击“删除删除”按钮，进入按钮，进入“确认删除确认删除”页面页面 （2）单击“是”按钮，删除过程up_NameByID 添加标添加标题文字题文字教教师师演演示示讲讲

29、解解课堂案例3使用PL/SQL管理存储过程 学习使用PL/SQL语句创建存储过程、修改存储过程、编译存储过程和执行存储过程的方法 。案例学习目标案例学习目标 使用CREATE OR REPLACE PROCEDURE创建和修改存储过程、使用ALTER PROCEDURE编译存储过程、执行存储过程的几种方法 。案例知识要点案例知识要点 课堂案例3使用PL/SQL管理存储过程案例完成步骤案例完成步骤 添加标添加标题文字题文字1创建存储过程创建存储过程使用使用PL/SQL创建存储过程的基本语法格式为：创建存储过程的基本语法格式为：CREATE OR REPLACE PROCEDURE 用户方用户方案

30、案. ( 参数参数1 参数模式参数模式 数据类型数据类型 ， ) IS | AS参数参数1 数据类型数据类型，BEGINPL/SQL语句语句END 存储过程名存储过程名;2执行存储过程执行存储过程使用使用PL/SQL执行存储过程的基本语法格式为：执行存储过程的基本语法格式为：DECLARE参数参数1 数据类型数据类型， BEGIN EXECUTE 用户方案用户方案. ( 参数参数1 ， ) ;END;添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例3使用PL/SQL管理存储过程案例完成步骤案例完成步骤 添加标添加标题文字题文字【例例3-1】创建简单存储过程，显示当前的系统时间。创建简单

31、存储过程，显示当前的系统时间。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE SCOTT.up_CurrentTimeASBEGINDBMS_OUTPUT.PUT_LINE(SYSDATE);END up_CurrentTime;（2）执行存储过程）执行存储过程BEGIN SCOTT. up_CurrentTime ();END;添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例3使用PL/SQL管理存储过程案例完成步骤案例完成步骤 添加标添加标题文字题文字【例例3-2】通过存储过程添加用户记录。通过存储过程添加用户记录。（1）定义存储过程）定义存

32、储过程CREATE OR REPLACE PROCEDURE up_InsertUser ASBEGIN INSERT INTO SCOTT.Users VALUES(88,存储过程存储过程,普通普通,storeproc);EXCEPTION 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）查询）查询Us

33、ers表表存储过程执行后，使用下列语句查看存储过程执行后，使用下列语句查看User表的记录情况。表的记录情况。SELECT * FROM USERS;添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例3使用PL/SQL管理存储过程案例完成步骤案例完成步骤 添加标添加标题文字题文字【例例3-3】创建存储过程，根据商品类别编号统计该类型所有商创建存储过程，根据商品类别编号统计该类型所有商品的总数量（带品的总数量（带IN参数）。参数）。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_CountByTid(tid in VARCHAR2)AS tot

34、al NUMBER; BEGIN SELECT COUNT(*) INTO total FROM SCOTT.GOODS WHERE t_ID=tid; DBMS_OUTPUT.PUT_LINE(total); END;（2）执行存储过程）执行存储过程 BEGIN -up_CountByTid(01); up_CountByTid(tid=01); END;up_CountByTid(01)：位置表示法传递参数：位置表示法传递参数up_CountByTid(tid=01)：名称表示法传递参数：名称表示法传递参数添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例3使用PL/SQL管理存储过

35、程案例完成步骤案例完成步骤 添加标添加标题文字题文字【例例3-4】创建存储过程，根据商品类别编号统计该类型创建存储过程，根据商品类别编号统计该类型所有商品的总数量，缺省情况下统计类别编号为所有商品的总数量，缺省情况下统计类别编号为“02”的商品的总数量（带缺省值的的商品的总数量（带缺省值的IN参数）。参数）。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_CountByTid(tid in VARCHAR2 DEFAULT 02)AS total NUMBER; BEGIN SELECT COUNT(*) INTO total FROM SCOTT

36、.GOODS WHERE t_ID=tid; DBMS_OUTPUT.PUT_LINE(total); END;添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例3使用PL/SQL管理存储过程案例完成步骤案例完成步骤 添加标添加标题文字题文字【例例3-5】创建存储过程，根据商品的编号获得商品的名创建存储过程，根据商品的编号获得商品的名称和类别编号（带称和类别编号（带IN和和OUT参数）。参数）。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_GetByID(gid in VARCHAR2,gname out GOODS.g_Name%TYPE

37、,tid 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;添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例3使用PL/SQL管理存储过程案例完成步骤案例完成步骤 添加标添加标题文字题文字（2）执行存储过程）执行存储过程-调用带输出参数的存储过程调用带输出参数的存储过程variable v_name varc

38、har2(50);variable v_id varchar2(2);exec up_GetByID(020001,:v_name,:v_id);print v_name; print v_id;添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例3使用PL/SQL管理存储过程案例完成步骤案例完成步骤 添加标添加标题文字题文字【例例3-5】编写存储过程实现两个数交换，并在比较两个编写存储过程实现两个数交换，并在比较两个数的大小中调用该存储过程（带数的大小中调用该存储过程（带INOUT参数）参数） 添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例3使用PL/SQL管理存储过程案例完成

39、步骤案例完成步骤查看存储过程查看存储过程 添加标添加标题文字题文字【例例3-6】查看用户方案查看用户方案SCOTT的存储过程的存储过程up_GetByID的信息。的信息。DESC SCOTT.up_GetByID;添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例3使用PL/SQL管理存储过程案例完成步骤案例完成步骤删除存储过程删除存储过程 添加标添加标题文字题文字【例例3-7】删除用户方案删除用户方案SCOTT的存储过程的存储过程up_CurrentTime。DROP PROCEDURE SCOTT. up_CurrentTime;添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案

40、例4管理函数学习在SQL Developer和PL/SQL中创建函数、调用函数、删除函数的方法 。案例学习目标案例学习目标 SQL Developer中创建函数、SQL Developer删除函数、PL/SQL创建函数、PL/SQL调用函数、PL/SQL删除函数 。案例知识要点案例知识要点 课堂案例4管理函数 案例完成步骤案例完成步骤创建函数创建函数 添加标添加标题文字题文字函数（函数（Function）与存储过程类似，也是组成一个子程序的一）与存储过程类似，也是组成一个子程序的一组组PL/SQL语句。函数接受语句。函数接受0个或多个输入参数，仅返回一个值，个或多个输入参数，仅返回一个值，返回

41、值的数据类型在创建函数时定义。返回值的数据类型在创建函数时定义。1使用使用SQL Developer创建函数创建函数 （1）在）在SQL Developer中右击中右击Functions选项，从快捷菜单中选项，从快捷菜单中选择选择“New Function”项，将开始创建函数项，将开始创建函数 添加标添加标题文字题文字教教师师演演示示讲讲解解课堂案例4管理函数 案例完成步骤案例完成步骤创建函数创建函数 添加标添加标题文字题文字（2）在打开的）在打开的“Create PL/SQL Function”对话框内，对话框内，指 定 用 户 方 案 为指 定 用 户 方 案 为 S C O T T ，

42、设 置 函 数 名 称 为， 设 置 函 数 名 称 为fn_CountTypes，创建一个统计商品种类的函数，创建一个统计商品种类的函数 添加标添加标题文字题文字教教师师演演示示讲讲解解 课堂案例4管理函数 案例完成步骤案例完成步骤创建函数创建函数 添加标添加标题文字题文字（3）单击）单击“确定确定”按钮，开始编辑函数的定义，以实现按钮，开始编辑函数的定义，以实现统计商品种类的函数。统计商品种类的函数。 添加标添加标题文字题文字教教师师演演示示讲讲解解 CREATE OR REPLACE FUNCTION fn_CountTypes RETURN NUMBER ASCOUNTER NUMBE

43、R;BEGIN SELECTCOUNT (t_ID) INTO COUNTER FROM SCOTT.GOODS; RETURN COUNTER;END fn_CountTypes;课堂案例4管理函数 案例完成步骤案例完成步骤执行函数执行函数 添加标添加标题文字题文字（4）展开）展开Functions选项，右击函数选项，右击函数fn_CountTypes，从快捷菜单中选择从快捷菜单中选择“Run”项，打开运行函数对话框项，打开运行函数对话框 。 添加标添加标题文字题文字教教师师演演示示讲讲解解 课堂案例4管理函数 案例完成步骤案例完成步骤PL/SQLPL/SQL创建函数创建函数 添加标添加标题

44、文字题文字【例例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.o_ID

45、 = O.o_ID WHERE c_ID = cid; RETURN T_VALUE;END fn_TOTALVALUE;添加标添加标题文字题文字教教师师演演示示讲讲解解 课堂案例4管理函数 案例完成步骤案例完成步骤调用函数调用函数 添加标添加标题文字题文字【例例4-2】调用用户方案调用用户方案SCOTT中的函数中的函数DECLARE cid SCOTT.ORDERS.c_ID%TYPE; BEGIN cid:=C0001; DBMS_OUTPUT.PUT_LINE(cid | : | SCOTT.fn_TOTALVALUE(cid); END;添加标添加标题文字题文字教教师师演演示示讲讲解解

46、 课堂案例4管理函数 案例完成步骤案例完成步骤删除函数删除函数 添加标添加标题文字题文字1使用使用SQL Developer删除函数删除函数（1）在）在SQL Developer中右击中右击Functions选项，从快捷选项，从快捷菜单中选择菜单中选择“Drop”项，将会删除选定的函数项，将会删除选定的函数 添加标添加标题文字题文字教教师师演演示示讲讲解解 2使用使用PL/SQL命令删除函数命令删除函数【例例4-3】删除用户方案删除用户方案SCOTT中的函数中的函数fn_CountTypes。DROP FUNCTION fn_CountTypes;课堂案例5应用包学习学习SQL Develop

47、er和和PL/SQL定义包头、定义包体和使用包的方法定义包头、定义包体和使用包的方法 。案例学习目标案例学习目标 SQL Developer定义包头、定义包头、SQL Developer定义包体、定义包体、PL/SQL定义包定义包头、头、PL/SQL定义包体和使用包定义包体和使用包 。案例知识要点案例知识要点 课堂案例5应用包包概述包概述 添加标添加标题文字题文字l包（包（Package）可将一些有联系的对象放置在其内部，构）可将一些有联系的对象放置在其内部，构成一个逻辑分组，这些对象包括存储过程、函数、游标、成一个逻辑分组，这些对象包括存储过程、函数、游标、自定义的类型（例如自定义的类型（例

48、如PL/SQL表和记录）和变量等。表和记录）和变量等。l实际上，包相当于一个命名的声明部分，任何能在块定义实际上，包相当于一个命名的声明部分，任何能在块定义部分出现的对象都可以在包中出现，用户可以从其他部分出现的对象都可以在包中出现，用户可以从其他PL/SQL块中对包进行引用。块中对包进行引用。l包拥有两个独立的部分：包头和包体，它们都存储在数据包拥有两个独立的部分：包头和包体，它们都存储在数据字典中。定义一个包，要分别定义包头和包体。字典中。定义一个包，要分别定义包头和包体。l包与存储过程和函数的一个显著区别是包仅能存储在非本包与存储过程和函数的一个显著区别是包仅能存储在非本地的数据库中。可以将存储过程和函数定义在包中，包被地的数据库中。可以将存储过程和函数定义在包中，包被保存在高速缓存中，这样体现了模块化编程的特点，使得保存在高速缓存中，这样体现了模块化编程的特点，使得应用系统的开发更为灵活，运行效率更高。应用系统的开发更为灵活，运行效率更高。l存储过程和函数被加入到包中时，存储过程和函数的声明存储过程和函数被加入到包中时，存储过程和函数的声明放在包头部分，而执行代码则放在包体部分。放在包头部分