存储过程操作理论课程

1、第7章 存储过程操作 主编：刘志成本章学习导航本章学习导航 本章学习要点（1）存储过程概述。（2）OEM创建、调用、查看、修改和删除存储过程。（3）PL/SQL创建、调用、查看、修改和删除存储过程。（4）创建、调用和删除函数。（5）定义包头、包体。（6）包中定义函数和存储过程。 建议课时：12课时课堂案例1使用PL/SQL编程 常量和变量-常量常量是指在程序运行期间其值不能改变的量。 【例1-1】定义常量PI（3.14159）。-设置打开控制台输出SET SERVEROUTPUT ON-声明一个变量或常量DECLARE PI CONSTANT NUMBER(6,5) := 3.14159;BE

2、GIN-输出指定变量或常量的值 DBMS_OUTPUT.PUT_LINE(PI = | PI);END; 其中，PUT_LINE过程中使用的“|”用于连接输出内容的两部分 课堂案例1使用PL/SQL编程 常量和变量-变量变量是指由程序读取或赋值的存储单元，用于临时存储数据，变量中的数据可以随着程序的运行而发生变化。每个变量都必须有一个特定的数据类型，可以是系统数据类型，也可以是自定义数据类型。 【例1-2】编写计算圆面积的PL/SQL块。SET SERVEROUTPUT ONDECLARE PI CONSTANT NUMBER(6,5) := 3.14159;-声明两个变量并赋初值 v_rad

3、iu FLOAT := 2; v_area FLOAT;BEGIN v_area := PI * v_radiu * v_radiu; DBMS_OUTPUT.PUT_LINE(Area = | v_area);END;课堂案例1使用PL/SQL编程 条件结构-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

4、.PUT_LINE(v_number | 是一个奇数); END IF;END;课堂案例1使用PL/SQL编程 条件结构-IF结构【例1-4】使用复杂IF结构输出3个整数之中的最大者。课堂案例1使用PL/SQL编程 条件结构-CASE结构【例1-5】使用单一选择符进行等值比较的CASE结构将百分制成绩转换为5分制成绩。 课堂案例1使用PL/SQL编程 条件结构-CASE结构【例1-6】使用多种条件进行非等值比较的CASE结构将百分制成绩转换为5分制成绩 。 课堂案例1使用PL/SQL编程 循环结构-While循环【例1-7】使用WHILE循环求1到100的所有正整数之和。SET SERVERO

5、UTPUT ONDECLARE i INTEGER:=1; s INTEGER:=0; BEGIN WHILE i100; END LOOP; DBMS_OUTPUT.PUT_LINE(1+2+.+100= | s);END;课堂案例1使用PL/SQL编程 循环结构-For循环使用FOR循环不需要显式声明循环控制变量的类型，而由PL/SQL隐式提供。默认情况下，循环控制变量从下限值开始，每次循环结束后自动增加1，直至超过上限值为止；若指定REVERSE参数，则循环控制变量从上限值开始，每次循环结束后自动减1，直至低于下限值为止。 课堂案例1使用PL/SQL编程 循环结构-For循环【例1-9】

6、使用不带REVERSE参数的FOR循环求1到100的所有正整数之和。【例1-10】使用带REVERSE参数的FOR循环求1到100的所有正整数之和。课堂案例1使用PL/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_

7、OUTPUT.PUT_LINE(num);END;课堂案例1使用PL/SQL编程 常用系统函数-数学函数课堂案例1使用PL/SQL编程 常用系统函数-数学函数【例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.1415

8、9, 3); DBMS_OUTPUT.PUT_LINE(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.PUT_LINE(a的ACSCII值为 | ASCII(a); DBM

9、S_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 Railway);END;课堂案例1使用PL/SQL编程 常用系统函数-日期函数课堂案例1使用PL/SQ

10、L编程 常用系统函数-日期函数【例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;课堂案例1使用PL/S

11、QL编程 常用系统函数-转换函数课堂案例1使用PL/SQL编程 常用系统函数-转换函数【例1-15】测试常用转换函数的用法。SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(转换为数据库字符集的ASCII字符串为 | ASCIISTR(湖南铁道); DBMS_OUTPUT.PUT_LINE(将当前日期转换为字符串类型数据为 | CAST(SYSDATE AS VARCHAR2); DBMS_OUTPUT.PUT_LINE(将字符串转换为ROWID数据类型为 | CHARTOROWID(AAAAFdl/#$); DBMS_OUTPUT.PUT_LINE(

12、字符集转换 | CONVERT(湖南铁道,US7ASCII,WE8ISO8859P1);END;课堂案例1使用PL/SQL编程 %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_gNumber Goods.g_Number%TYPE;BE

13、GIN 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; 课堂案例1使用PL/SQL编程 %ROWTYPE变量与%TYPE类型类似，也可以在不确定查询列的类型的情况下，使用%ROWTYPE类型的变量存储查询的一行数据 。【例1-17】使用%ROWTYPE获取查询的商品基本

14、信息。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_output.put_line(v_GoodRecord.g_Number );E

15、ND;课堂案例1使用PL/SQL编程 异常处理为了提高应用程序的健壮性，开发人员必须考虑程序可能出现的各种错误，并进行相应的处理。在Oracle中，为了处理PL/SQL应用程序的各种错误，Oracle提供了三种类型的异常。（1）预定义异常：用于处理常见的Oracle错误（2）非预定义异常：用于处理预定义异常所不能处理的Oracle错误（3）自定义异常：用于处理于Oracle错误无关的其他情况课堂案例1使用PL/SQL编程 异常处理系统预定义异常课堂案例1使用PL/SQL编程 异常处理系统预定义异常【例1-18】对Goods表中的插入的重复商品号进行异常处理（使用预定义异常）。SET SERVE

16、ROUTPUT 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 DUP_VAL_ON_INDEX THENdbms_output.put_line(捕获到DUP_VAL_ON_INDEX异常);dbms_output.put_line(重复的商品编号);END;课堂案例1使用PL/SQL编程 异常处理非预

17、定义异常使用非预定义异常需要包括以下三个步骤：（1）在定义部分定义异常名；（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_Name=通信商品;EXCEPTION WHEN e_FK THEN

18、-3、捕捉处理 DBMS_OUTPUT.PUT_LINE(该类别已被使用);END;课堂案例1使用PL/SQL编程 异常处理自定义异常使用自定义异常时，需要包括以下三个步骤：（1）需要在定义部分（DECLARE）定义异常；（2）再执行部分（BEGIN）触发异常（使用RAISE语句）；（3）在异常处理部分（EXCEPTION）捕捉并处理异常。 7.2 存储过程概述 视图特点在Oracle中，可以在数据库中定义子程序，在子程序中将一些固定的操作集中起来，由Oracle数据库服务器完成，以完成某个特定的功能。这种子程序称为存储过程（Proce-Dure）。使用存储过程具有如下的优点：（1）存储过程在

19、服务器端运行，执行速度快；（2）存储过程执行一次后驻留在Oracle数据库服务器的高速Cache中，以后再次执行存储过程时，只需从高速Cache中调用已经编译好的代码即可，从而提高了系统性能；（3）存储过程确保了数据库的安全。使用存储过程，可以在禁止用户直接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的存储过程。（4）自动完成需要预先执行的任务。存储过程可以设置为系统启动时自动执行，而不必在系统启动后再进行手动操作，从而方便了用户的使用，可以自动完成一些需要预先执行的任务。 课堂案例2使用OEM管理存储过程 掌握在OEM中创建存储过程、执行存储过程的方法 。案例学习目标 OEM中

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

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

22、确认删除”页面 （2）单击“是”按钮，删除过程up_NameByID 添加标题文字教师演示讲解课堂案例3使用PL/SQL管理存储过程 学习使用PL/SQL语句创建存储过程、修改存储过程、编译存储过程和执行存储过程的方法 。案例学习目标 使用CREATE OR REPLACE PROCEDURE创建和修改存储过程、使用ALTER PROCEDURE编译存储过程、执行存储过程的几种方法 。案例知识要点 课堂案例3使用PL/SQL管理存储过程案例完成步骤 添加标题文字1创建存储过程使用PL/SQL创建存储过程的基本语法格式为：CREATE OR REPLACE PROCEDURE 用户方案. ( 参

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

24、;END up_CurrentTime;（2）执行存储过程BEGIN SCOTT. up_CurrentTime ();END;添加标题文字教师演示讲解课堂案例3使用PL/SQL管理存储过程案例完成步骤 添加标题文字【例3-2】通过存储过程添加用户记录。（1）定义存储过程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(重复的

25、用编号); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(发生其他错误);END up_InsertUser;（2）执行存储过程EXEC up_InsertUser;（3）查询Users表存储过程执行后，使用下列语句查看User表的记录情况。SELECT * FROM USERS;添加标题文字教师演示讲解课堂案例3使用PL/SQL管理存储过程案例完成步骤 添加标题文字【例3-3】创建存储过程，根据商品类别编号统计该类型所有商品的总数量（带IN参数）。（1）定义存储过程CREATE OR REPLACE PROCEDURE up_CountByTid(tid in

26、VARCHAR2)AS total 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管理存储过程案例完成步骤 添加标题文字【例3-4】创建存储

27、过程，根据商品类别编号统计该类型所有商品的总数量，缺省情况下统计类别编号为“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(total); END;添加标题文字教师演示讲解课堂案例3使用PL/SQL管理存储过程案例完成步骤 添加标题文字【例

28、3-5】创建存储过程，根据商品的编号获得商品的名称和类别编号（带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_GetB

29、yID;添加标题文字教师演示讲解课堂案例3使用PL/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;添加标题文字教师演示讲解课堂案例3使用PL/SQL管理存储过程案例完成步骤 添加标题文字【例3-5】编写存储过程实现两个数交换，并在比较两个数的大小中调用该存储过程（带INOUT参数） 添加标题文字教师演示讲解课堂案例3使用

30、PL/SQL管理存储过程案例完成步骤查看存储过程 添加标题文字【例3-6】查看用户方案SCOTT的存储过程up_GetByID的信息。DESC SCOTT.up_GetByID;添加标题文字教师演示讲解课堂案例3使用PL/SQL管理存储过程案例完成步骤删除存储过程 添加标题文字【例3-7】删除用户方案SCOTT的存储过程up_CurrentTime。DROP PROCEDURE SCOTT. up_CurrentTime;添加标题文字教师演示讲解课堂案例4管理函数学习在SQL Developer和PL/SQL中创建函数、调用函数、删除函数的方法 。案例学习目标 SQL Developer中创建

31、函数、SQL Developer删除函数、PL/SQL创建函数、PL/SQL调用函数、PL/SQL删除函数 。案例知识要点 课堂案例4管理函数 案例完成步骤创建函数 添加标题文字函数（Function）与存储过程类似，也是组成一个子程序的一组PL/SQL语句。函数接受0个或多个输入参数，仅返回一个值，返回值的数据类型在创建函数时定义。1使用SQL Developer创建函数 （1）在SQL Developer中右击Functions选项，从快捷菜单中选择“New Function”项，将开始创建函数 添加标题文字教师演示讲解课堂案例4管理函数 案例完成步骤创建函数 添加标题文字（2）在打开的“

32、Create PL/SQL Function”对话框内，指定用户方案为SCOTT，设置函数名称为fn_CountTypes，创建一个统计商品种类的函数 添加标题文字教师演示讲解 课堂案例4管理函数 案例完成步骤创建函数 添加标题文字（3）单击“确定”按钮，开始编辑函数的定义，以实现统计商品种类的函数。 添加标题文字教师演示讲解 CREATE OR REPLACE FUNCTION fn_CountTypes RETURN NUMBER ASCOUNTER NUMBER;BEGIN SELECTCOUNT (t_ID) INTO COUNTER FROM SCOTT.GOODS; RETURN

33、COUNTER;END fn_CountTypes;课堂案例4管理函数 案例完成步骤执行函数 添加标题文字（4）展开Functions选项，右击函数fn_CountTypes，从快捷菜单中选择“Run”项，打开运行函数对话框 。 添加标题文字教师演示讲解 课堂案例4管理函数 案例完成步骤PL/SQL创建函数 添加标题文字【例4-1】在用户方案SCOTT中创建函数fn_TOTALVALUE，它根据客户编号查询该客户的订单总金额。CREATE OR REPLACE FUNCTION SCOTT.fn_TOTALVALUE(cid SCOTT.ORDERS.c_ID%TYPE)RETURNNUMBE

34、RAS T_VALUENUMBER;BEGIN SELECTSUM(d_Price *d_Number) INTO T_VALUE FROM SCOTT.ORDERDETAILS OD JOIN SCOTT.ORDERS O ON OD.o_ID = O.o_ID WHERE c_ID = cid; RETURNT_VALUE;END fn_TOTALVALUE;添加标题文字教师演示讲解 课堂案例4管理函数 案例完成步骤调用函数 添加标题文字【例4-2】调用用户方案SCOTT中的函数DECLARE cid SCOTT.ORDERS.c_ID%TYPE; BEGIN cid:=C0001; DB

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

36、包头、定义包体和使用包的方法 。案例学习目标 SQL Developer定义包头、SQL Developer定义包体、PL/SQL定义包头、PL/SQL定义包体和使用包 。案例知识要点 课堂案例5应用包包概述 添加标题文字包（Package）可将一些有联系的对象放置在其内部，构成一个逻辑分组，这些对象包括存储过程、函数、游标、自定义的类型（例如PL/SQL表和记录）和变量等。实际上，包相当于一个命名的声明部分，任何能在块定义部分出现的对象都可以在包中出现，用户可以从其他PL/SQL块中对包进行引用。包拥有两个独立的部分：包头和包体，它们都存储在数据字典中。定义一个包，要分别定义包头和包体。包与存储过程和函数的一个显著区别是包仅能存储在非本地的数据库中。可以将存储过程和函数定义在包中，包被保存在高速缓存中，这样体现了模块化编程的特点，使得应用系统的开发更为灵活，运行效率更高。存储过程和函数被加入到包中时，存储过程和函数的声明放在包头部分，而执行代码则放在包体部分。添加标题文字教师演示讲解课堂案例5应用包案例完成步骤SQL Developer定义包头 添加标题文字（1）使