存储过程操作理论课程50150.pptx

《存储过程操作理论课程50150.pptx》由会员分享，可在线阅读，更多相关《存储过程操作理论课程50150.pptx（67页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

2、储存储过程过程。（3）PL/SQL创建、调用、查看、修改和删除创建、调用、查看、修改和删除存储过程存储过程。（4）创建、调用和删除）创建、调用和删除函数函数。（5）定义包头、包体定义包头、包体。（6）包包中定义函数和存储过程。中定义函数和存储过程。建议课时：建议课时：12课时课时2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 常量和变量常量和变量-常量常量l常量是指在程序运行期间其值不能改变的量。常量是指在程序运行期间其值不能改变的量。【例例1-1】定义常量定义常量PI（3.14

3、159）。）。-设置打开控制台输出SET SERVEROUTPUT ON-声明一个变量或常量DECLARE PI CONSTANT NUMBER(6,5):=3.14159;BEGIN-输出指定变量或常量的值 DBMS_OUTPUT.PUT_LINE(PI=|PI);END;其中，PUT_LINE过程中使用的过程中使用的“|”用于连接输出内容的两部分用于连接输出内容的两部分 2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用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;BE

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

6、UTPUT.PUT_LINE(v_number|是一个偶数);ELSE DBMS_OUTPUT.PUT_LINE(v_number|是一个奇数);END IF;END;2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 条件结构条件结构-IF-IF结构结构【例例1-4】使用复使用复杂杂IF结构输出结构输出3个个整数之中的最大者。整数之中的最大者。2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQ

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

8、 cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/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;2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教

9、程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 循环结构循环结构-For-For循环循环使用FOR循环不需要显式声明循环控制变量的类型，而由PL/SQL隐式提供。默认情况下，循环控制变量从下限值开始，每次循环结束后自动增加1，直至超过上限值为止；若指定REVERSE参数，则循环控制变量从上限值开始，每次循环结束后自动减1，直至低于下限值为止。2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 循环结构循环结构-For-For循环循环【例1-9】使用不带REVERSE

10、参数的FOR循环求1到100的所有正整数之和。【例1-10】使用带REVERSE参数的FOR循环求1到100的所有正整数之和。2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用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)=

11、0 THEN GOTO display;END IF;num:=num+1;END LOOP;DBMS_OUTPUT.PUT_LINE(num);END;2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 常用系统函数常用系统函数-数学函数数学函数2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 常用系统函数常用系统函数-数学函数数学函数【例例1-12】测试常用数学函数的

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(e=|EXP(1);DBMS_OUTPUT.PUT_LINE(大于或等于大于或等于-32

13、.5的最小整数为的最小整数为|CEIL(-32.5);DBMS_OUTPUT.PUT_LINE(小于或等于小于或等于-32.5的最大整数为的最大整数为|FLOOR(-32.5);END;2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 常用系统函数常用系统函数-字符串函数字符串函数2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 常用系统函数常用系统函数-字符串函数字符

14、串函数【例例1-13】测试常用字符串函数的用法。测试常用字符串函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_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(Hu

15、nan Railway);DBMS_OUTPUT.PUT_LINE(将字符串将字符串Hunan Railway全部全部转换为小写形式为转换为小写形式为|LOWER(Hunan Railway);END;2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 常用系统函数常用系统函数-日期函数日期函数2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 常用系统函数常用系统函数-日

16、期函数日期函数【例例1-14】测试常用日期函数的用法。测试常用日期函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(当前日期时间为|SYSDATE);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;2023/5/22O Or ra a

17、c cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 常用系统函数常用系统函数-转换函数转换函数2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 常用系统函数常用系统函数-转换函数转换函数【例例1-15】测试常用转换函数的用法。测试常用转换函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(转换为数据库字符集的转换为数据库字符集的ASCII字符串为字符串为|

18、ASCIISTR(湖南铁道湖南铁道);DBMS_OUTPUT.PUT_LINE(将当前日期转换为字符串类型数据为将当前日期转换为字符串类型数据为|CAST(SYSDATE AS VARCHAR2);DBMS_OUTPUT.PUT_LINE(将字符串转换为将字符串转换为ROWID数据类型为数据类型为|CHARTOROWID(AAAAFdl/#$);DBMS_OUTPUT.PUT_LINE(字符集转换字符集转换|CONVERT(湖南铁道湖南铁道,US7ASCII,WE8ISO8859P1);END;2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程

19、程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程%TYPE%TYPE变量变量Oracle 9i以后的版本提供了以后的版本提供了%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

20、_gNumber Goods.g_Number%TYPE;BEGIN SELECT g_ID,g_Name,g_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;2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程%ROWTYPE%R

21、OWTYPE变量变量与与%TYPE类型类似，也可以在不确定查询列的类型的情况下，使类型类似，也可以在不确定查询列的类型的情况下，使用用%ROWTYPE类型的变量存储查询的一行数据类型的变量存储查询的一行数据。【例例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_GoodRecor

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

23、理。在种错误，并进行相应的处理。在Oracle中，为了处理中，为了处理PL/SQL应用程应用程序的各种错误，序的各种错误，Oracle提供了提供了三种类型三种类型的异常。的异常。（1）预定义异常：用于处理常见的）预定义异常：用于处理常见的Oracle错误错误（2）非预定义异常：用于处理预定义异常所不能处理的）非预定义异常：用于处理预定义异常所不能处理的Oracle错错误误（3）自定义异常：用于处理于）自定义异常：用于处理于Oracle错误无关的其他情况错误无关的其他情况2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用

24、使用PL/SQLPL/SQL编程编程 异常处理异常处理系统预定义异常系统预定义异常2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 异常处理异常处理系统预定义异常系统预定义异常【例例1-18】对对Goods表中的插入的重复商品号进行异常处理（使表中的插入的重复商品号进行异常处理（使用预定义异常）。用预定义异常）。SET SERVEROUTPUT ONBEGIN INSERT INTO SCOTT.Goods VALUES(010001,诺基亚6700 Slide,01,1500,0

25、.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;2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 异常处理异常处理非预定义异常非预定

26、义异常使用非预定义异常需要包括以下三个步骤：使用非预定义异常需要包括以下三个步骤：（1）在定义部分定义异常名；）在定义部分定义异常名；（2）在异常和）在异常和Oracle错误之间建立关联（需要使用伪过程错误之间建立关联（需要使用伪过程EXCEPTION_INIT）；）；（3）在异常处理部分捕捉并处理异常。）在异常处理部分捕捉并处理异常。【例例1-19】删除商品类别表，并处理删除商品类别表，并处理ORA-2292错误（使用非预定义异常）错误（使用非预定义异常）。SET SERVEROUTPUT ONDECLARE e_FK EXCEPTION;-1、定义部分 PRAGMA EXCEPTION_I

27、NIT(e_FK,-2292);-2、建立关联关系BEGIN DELETE SCOTT.TypesWHERE t_Name=通信商品;EXCEPTION WHEN e_FK THEN-3、捕捉处理 DBMS_OUTPUT.PUT_LINE(该类别已被使用);END;2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例11使用使用PL/SQLPL/SQL编程编程 异常处理异常处理自定义异常自定义异常使用自定义异常时，需要包括以下三个步骤：使用自定义异常时，需要包括以下三个步骤：（1）需要在定义部分（）需要在定义部分（DECLARE

28、）定义异常；）定义异常；（2）再执行部分（）再执行部分（BEGIN）触发异常（使用）触发异常（使用RAISE语句）；语句）；（3）在异常处理部分（）在异常处理部分（EXCEPTION）捕捉并处理异常。）捕捉并处理异常。2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程7.2 7.2 存储过程概述存储过程概述 视图特点视图特点在在Oracle中，可以在数据库中定义子程序，在子程序中将一些固定的操中，可以在数据库中定义子程序，在子程序中将一些固定的操作集中起来，由作集中起来，由Oracle数据库服务器完成，以完成某个特定的功能。这数据库服务器完成

29、，以完成某个特定的功能。这种子程序称为存储过程（种子程序称为存储过程（Proce-Dure）。使用存储过程具有如下的优点：）。使用存储过程具有如下的优点：（1）存储过程在服务器端运行，）存储过程在服务器端运行，执行速度快执行速度快；（2）存储过程执行一次后驻留在）存储过程执行一次后驻留在Oracle数据库服务器的数据库服务器的高速高速Cache中，中，以后再次执行存储过程时，只需从高速以后再次执行存储过程时，只需从高速Cache中调用已经编译好的代码中调用已经编译好的代码即可，从而提高了系统性能；即可，从而提高了系统性能；（3）存储过程确保了）存储过程确保了数据库的安全数据库的安全。使用存储过

30、程，可以在禁止用户直。使用存储过程，可以在禁止用户直接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的存储过程。存储过程。（4）自动完成自动完成需要预先执行的任务。存储过程可以设置为系统启动时自需要预先执行的任务。存储过程可以设置为系统启动时自动执行，而不必在系统启动后再进行手动操作，从而方便了用户的使用，动执行，而不必在系统启动后再进行手动操作，从而方便了用户的使用，可以自动完成一些需要预先执行的任务。可以自动完成一些需要预先执行的任务。2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应

31、用用实实例例教教程程课堂案例课堂案例22使用使用OEMOEM管理存储过程管理存储过程 掌握在OEM中创建存储过程、执行存储过程的方法。案例学习目标案例学习目标 OEM中创建存储过程、执行存储过程。案例知识要点案例知识要点 2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例22使用使用OEMOEM管理存储过程管理存储过程案例完成步骤案例完成步骤 添加添加标标题题文字文字（1）启动）启动OEM后，依次选择后，依次选择“方案方案”、“程序程序”下的下的“过程过程”，进入，进入“过程过程”页面页面（2）单击单击“创建创建”按钮，进入按

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

33、入按钮，进入“创建过程创建过程”对话框，输对话框，输入新建存储过程的名称入新建存储过程的名称up_NameByID，编写存储过程的，编写存储过程的内容内容 添加添加标标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例22使用使用OEMOEM管理存储过程管理存储过程案例完成步骤案例完成步骤-查看存储过程查看存储过程 添加添加标标题题文字文字在在OEM中，进入指定方案的中，进入指定方案的“过程过程”页面，在过程列表页面，在过程列表中选择要查看的过程，单击中选择要查看的过程，单击“查看查看”按钮

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

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

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

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

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

39、2）执行存储过程）执行存储过程BEGIN SCOTT.up_CurrentTime();END;添加添加标标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例33使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤 添加添加标标题题文字文字【例例3-2】通过存储过程添加用户记录。通过存储过程添加用户记录。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_InsertUser ASBEGIN INSERT INTO SCO

40、TT.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）查询）查询Users表表存储过程执行后，使用下列语句查看存储过程执行后，使用下列语句查看User表的记录情况。表的记录情况。SELECT*FROM US

41、ERS;添加添加标标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例33使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤 添加添加标标题题文字文字【例例3-3】创建存储过程，根据商品类别编号统计该类型所有商创建存储过程，根据商品类别编号统计该类型所有商品的总数量（带品的总数量（带IN参数）。参数）。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_CountByTid(tid in VARCHAR2)AS tota

42、l 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)：名称表示法传递参数：名称表示法传递参数添加添加标标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le

43、 e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例33使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤 添加添加标标题题文字文字【例例3-4】创建存储过程，根据商品类别编号统计该类型创建存储过程，根据商品类别编号统计该类型所有商品的总数量，缺省情况下统计类别编号为所有商品的总数量，缺省情况下统计类别编号为“02”的商品的总数量（带缺省值的的商品的总数量（带缺省值的IN参数）。参数）。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_CountByTid(tid in VARCHAR2 DEFAULT

44、02)AS total NUMBER;BEGIN SELECT COUNT(*)INTO total FROM SCOTT.GOODS WHERE t_ID=tid;DBMS_OUTPUT.PUT_LINE(total);END;添加添加标标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例33使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤 添加添加标标题题文字文字【例例3-5】创建存储过程，根据商品的编号获得商品的名创建存储过程，根据商品的编号获得商品的名

45、称和类别编号（带称和类别编号（带IN和和OUT参数）。参数）。（1）定义存储过程）定义存储过程CREATE OR REPLACE PROCEDURE up_GetByID(gid in VARCHAR2,gname out GOODS.g_Name%TYPE,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;添

46、加添加标标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例33使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤 添加添加标标题题文字文字（2）执行存储过程）执行存储过程-调用带输出参数的存储过程调用带输出参数的存储过程variable v_name varchar2(50);variable v_id varchar2(2);exec up_GetByID(020001,:v_name,:v_id);print v_name;print v_id;添加添加标

47、标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例33使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤 添加添加标标题题文字文字【例例3-5】编写存储过程实现两个数交换，并在比较两个编写存储过程实现两个数交换，并在比较两个数的大小中调用该存储过程（带数的大小中调用该存储过程（带INOUT参数）参数）添加添加标标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂

48、案例33使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤查看存储过程查看存储过程 添加添加标标题题文字文字【例例3-6】查看用户方案查看用户方案SCOTT的存储过程的存储过程up_GetByID的信息。的信息。DESC SCOTT.up_GetByID;添加添加标标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例33使用使用PL/SQLPL/SQL管理存储过程管理存储过程案例完成步骤案例完成步骤删除存储过程删除存储过程 添加添加标标题题文字文字【例例3-7】

49、删除用户方案删除用户方案SCOTT的存储过程的存储过程up_CurrentTime。DROP PROCEDURE SCOTT.up_CurrentTime;添加添加标标题题文字文字教教师师演演示示讲讲解解2023/5/22O Or ra ac cl le e数数据据库库管管理理与与应应用用实实例例教教程程课堂案例课堂案例44管理函数管理函数学习在SQL Developer和PL/SQL中创建函数、调用函数、删除函数的方法。案例学习目标案例学习目标 SQL Developer中创建函数、SQL Developer删除函数、PL/SQL创建函数、PL/SQL调用函数、PL/SQL删除函数。案例知识

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