Oracle第7章存储过程操作

第7章存储过程操作主编：刘志成Oracle数据库管理与应用实例教程本章学习导航本章学习导航Oracle数据库管理与应用实例教程本章学习要点（1）存储过程概述。（2）OEM创建、调用、查看、修改和删除存储过程。（3）PL/SQL创建、调用、查看、修改和删除存储过程。（4）创建、调用和删除函数。（5）定义包头、包体。（6）包中定义函数和存储过程。建议课时：12课时Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常量和变量--常量常量是指在程序运行期间其值不能改变的量。【例1-1】定义常量PI（3.14159）。--设置打开控制台输出SETSERVEROUTPUTON--声明一个变量或常量DECLAREPICONSTANTNUMBER(6,5):=3.14159;BEGIN--输出指定变量或常量的值DBMS_OUTPUT.PUT_LINE('PI='||PI);END;其中，PUT_LINE过程中使用的“||”用于连接输出内容的两部分Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常量和变量--变量变量是指由程序读取或赋值的存储单元，用于临时存储数据，变量中的数据可以随着程序的运行而发生变化。每个变量都必须有一个特定的数据类型，可以是系统数据类型，也可以是自定义数据类型。【例1-2】编写计算圆面积的PL/SQL块。SETSERVEROUTPUTONDECLAREPICONSTANTNUMBER(6,5):=3.14159;--声明两个变量并赋初值v_radiuFLOAT:=2;v_areaFLOAT;BEGINv_area:=PI*v_radiu*v_radiu;DBMS_OUTPUT.PUT_LINE('Area='||v_area);END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程条件结构-IF结构【例1-3】使用简单IF结构判断一个整数的奇偶性。SETSERVEROUTPUTONDECLAREv_numberINTEGER:=518;BEGINIFMOD(v_number,2)=0THENDBMS_OUTPUT.PUT_LINE(v_number||'是一个偶数');ELSEDBMS_OUTPUT.PUT_LINE(v_number||'是一个奇数');ENDIF;END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程条件结构-IF结构【例1-4】使用复杂IF结构输出3个整数之中的最大者。Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程条件结构-CASE结构【例1-5】使用单一选择符进行等值比较的CASE结构将百分制成绩转换为5分制成绩。Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程条件结构-CASE结构【例1-6】使用多种条件进行非等值比较的CASE结构将百分制成绩转换为5分制成绩。Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程循环结构-While循环【例1-7】使用WHILE循环求1到100的所有正整数之和。SETSERVEROUTPUTONDECLAREiINTEGER:=1;sINTEGER:=0;BEGINWHILEi=100LOOPs:=s+i;i:=i+1;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1+2+...+100='||s);END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程循环结构-Loop循环【例1-8】使用LOOP循环求1到100的所有正整数之和。SETSERVEROUTPUTONDECLAREiINTEGER:=1;sINTEGER:=0;BEGINLOOPs:=s+i;i:=i+1;EXITWHENi100;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1+2+...+100='||s);END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程循环结构-For循环使用FOR循环不需要显式声明循环控制变量的类型，而由PL/SQL隐式提供。默认情况下，循环控制变量从下限值开始，每次循环结束后自动增加1，直至超过上限值为止；若指定REVERSE参数，则循环控制变量从上限值开始，每次循环结束后自动减1，直至低于下限值为止。Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程循环结构-For循环【例1-9】使用不带REVERSE参数的FOR循环求1到100的所有正整数之和。【例1-10】使用带REVERSE参数的FOR循环求1到100的所有正整数之和。Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程循环结构-跳转语句【例1-11】借助于GOTO跳转语句输出10以内第一个能同时被2和3整除的正整数。SETSERVEROUTPUTONDECLAREnumINTEGER:=1;BEGINWHILEnum=10LOOPIFMOD(num,2)=0ANDMOD(num,3)=0THENGOTOdisplay;ENDIF;num:=num+1;ENDLOOP;displayDBMS_OUTPUT.PUT_LINE(num);END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常用系统函数-数学函数Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常用系统函数-数学函数【例1-12】测试常用数学函数的用法。SETSERVEROUTPUTONBEGINDBMS_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.5的最小整数为'||CEIL(-32.5));DBMS_OUTPUT.PUT_LINE('小于或等于-32.5的最大整数为'||FLOOR(-32.5));END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常用系统函数-字符串函数Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常用系统函数-字符串函数【例1-13】测试常用字符串函数的用法。SETSERVEROUTPUTONBEGINDBMS_OUTPUT.PUT_LINE('a的ACSCII值为'||ASCII('a'));DBMS_OUTPUT.PUT_LINE('ACSCII值97对应的字符为'||CHR(97));DBMS_OUTPUT.PUT_LINE('字符串HunanRailway的长度为'||LENGTH('HunanRailway'));DBMS_OUTPUT.PUT_LINE('将字符串HunanRailway全部转换为大写形式为'||UPPER('HunanRailway'));DBMS_OUTPUT.PUT_LINE('将字符串HunanRailway全部转换为小写形式为'||LOWER('HunanRailway'));END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常用系统函数-日期函数Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常用系统函数-日期函数【例1-14】测试常用日期函数的用法。SETSERVEROUTPUTONBEGINDBMS_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;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常用系统函数-转换函数Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程常用系统函数-转换函数【例1-15】测试常用转换函数的用法。SETSERVEROUTPUTONBEGINDBMS_OUTPUT.PUT_LINE('转换为数据库字符集的ASCII字符串为'||ASCIISTR('湖南铁道'));DBMS_OUTPUT.PUT_LINE('将当前日期转换为字符串类型数据为'||CAST(SYSDATEASVARCHAR2));DBMS_OUTPUT.PUT_LINE('将字符串转换为ROWID数据类型为'||CHARTOROWID('AAAAFdl/#$'));DBMS_OUTPUT.PUT_LINE('字符集转换'||CONVERT('湖南铁道','US7ASCII','WE8ISO8859P1'));END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程%TYPE变量Oracle9i以后的版本提供了%TYPE定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。【例1-16】使用%TYPE获取查询的商品的基本信息。SETSERVEROUTPUTONDECLAREv_gIdGoods.g_ID%TYPE;v_gNameGoods.g_Name%TYPE;v_gPriceGoods.g_Price%TYPE;v_gNumberGoods.g_Number%TYPE;BEGINSELECTg_ID,g_Name,g_Price,g_NumberINTOv_gId,v_gName,v_gPrice,v_gNumberFROMSCOTT.GoodsWHEREg_ID='010003';dbms_output.put_line(v_gId||'/'||v_gName||'/'||v_gPrice||'/'||v_gNumber);END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程%ROWTYPE变量与%TYPE类型类似，也可以在不确定查询列的类型的情况下，使用%ROWTYPE类型的变量存储查询的一行数据。【例1-17】使用%ROWTYPE获取查询的商品基本信息。SETSERVEROUTPUTONDECLAREv_GoodRecordGoods%ROWTYPE;BEGINSELECT*INTOv_GoodRecordFROMSCOTT.GoodsWHEREg_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);END;Oracle数据库管理与应用实例教程课堂案例1——使用PL/SQL编程异常处理为了提高应用程序的健壮性，开发人员必须考虑程序可能出现的各种错误，并进行相应的处理。在Oracle中，为了处理PL/SQL应用程序的各种错误，Oracle提供了三种类型的异常。（1）预定义异常：用于处理常见的Oracle错误（2）非预定义异常：用于处理预定义异常所不能处理的Oracle错误（3）自定义异常：用于处理于Oracle错误无关的其他情况Oracle数据库管理与应用实例教程课