Oracle第7章存储过程操作.ppt

第7章存储过程操作,主编：刘志成,本章学习导航,本章学习导航,本章学习要点,（1）存储过程概述。（2）OEM创建、调用、查看、修改和删除存储过程。（3）PL/SQL创建、调用、查看、修改和删除存储过程。（4）创建、调用和删除函数。（5）定义包头、包体。（6）包中定义函数和存储过程。建议课时：12课时,课堂案例1使用PL/SQL编程,常量和变量-常量,常量是指在程序运行期间其值不能改变的量。【例1-1】定义常量PI（3.14159）。-设置打开控制台输出SETSERVEROUTPUTON-声明一个变量或常量DECLAREPICONSTANTNUMBER(6,5):=3.14159;BEGIN-输出指定变量或常量的值DBMS_OUTPUT.PUT_LINE(PI=|PI);END;其中，PUT_LINE过程中使用的“|”用于连接输出内容的两部分,课堂案例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;,课堂案例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;,课堂案例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的所有正整数之和。SETSERVEROUTPUTONDECLAREiINTEGER:=1;sINTEGER:=0;BEGINWHILEi100;ENDLOOP;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】使用不带REVERSE参数的FOR循环求1到100的所有正整数之和。【例1-10】使用带REVERSE参数的FOR循环求1到100的所有正整数之和。,课堂案例1使用PL/SQL编程,循环结构-跳转语句,【例1-11】借助于GOTO跳转语句输出10以内第一个能同时被2和3整除的正整数。SETSERVEROUTPUTONDECLAREnumINTEGER:=1;BEGINWHILEnumDBMS_OUTPUT.PUT_LINE(num);END;,课堂案例1使用PL/SQL编程,常用系统函数-数学函数,课堂案例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;,课堂案例1使用PL/SQL编程,常用系统函数-字符串函数,课堂案例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;,课堂案例1使用PL/SQL编程,常用系统函数-日期函数,课堂案例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;,课堂案例1使用PL/SQL编程,常用系统函数-转换函数,课堂案例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;,课堂案例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;,课堂案例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;,课堂案例1使用PL/SQL编程,异常处理,为了提高应用程序的健壮性，开发人员必须考虑程序可能出现的各种错误，并进行相应的处理。在Oracle中，为了处理PL/SQL应用程序的各种错误，Oracle提供了三种类型的异常。（1）预定义异常：用于处理常见的Oracle错误（2）非预定义异常：用于处理预定义异常所不能处理的Oracle错误（3）自定义异常：用于处理于Oracle错误无关的其他情况,课堂案例1使用PL/SQL编程,异常处理系统预定义异常,课堂案例1使用PL/SQL编程,异常处理系统预定义异常,【例1-18】对Goods表中的插入的重复商品号进行异常处理（使用预定义异常）。SETSERVEROUTPUTONBEGININSERTINTOSCOTT.GoodsVALUES(010001,诺基亚6700Slide,01,1500,0.9,20,to_date(2009-06-01,yyyy-mm-dd),pImage/010001.gif,热点,彩屏,1600万色,TFT,240320像素,2.2英寸);EXCEPTIONWHENDUP_VAL_ON_INDEXTHENdbms_output.put_line(捕获到DUP_VAL_ON_INDEX异常);dbms_output.put_line(重复的商品编号);END;,课堂案例1使用PL/SQL编程,异常处理非预定义异常,使用非预定义异常需要包括以下三个步骤：（1）在定义部分定义异常名；（2）在异常和Oracle错误之间建立关联（需要使用伪过程EXCEPTION_INIT）；（3）在异常处理部分捕捉并处理异常。,【例1-19】删除商品类别表，并处理ORA-2292错误（使用非预定义异常）。SETSERVEROUTPUTONDECLAREe_FKEXCEPTION;-1、定义部分PRAGMAEXCEPTION_INIT(e_FK,-2292);-2、建立关联关系BEGINDELETESCOTT.TypesWHEREt_Name=通信商品;EXCEPTIONWHENe_FKTHEN-3、捕捉处理DBMS_OUTPUT.PUT_LINE(该类别已被使用);END;,课堂案例1使用PL/SQL编程,异常处理自定义异常,使用自定义异常时，需要包括以下三个步骤：（1）需要在定义部分（DECLARE）定义异常；（2）再执行部分（BEGIN）触发异常（使用RAISE语句）；（3）在异常处理部分（EXCEPTION）捕捉并处理异常。,7.2存储过程概述,视图特点,在Oracle中，可以在数据库中定义子程序，在子程序中将一些固定的操作集中起来，由Oracle数据库服务器完成，以完成某个特定的功能。这种子程序称为存储过程（Proce-Dure）。使用存储过程具有如下的优点：（1）存储过程在服务器端运行，执行速度快；（2）存储过程执行一次后驻留在Oracle数据库服务器的高速Cache中，以后再次执行存储过程时，只需从高速Cache中调用已经编译好的代码即可，从而提高了系统性能；（3）存储过程确保了数据库的安全。使用存储过程，可以在禁止用户直接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的存储过程。（4）自动完成需要预先执行的任务。存储过程可以设置为系统启动时自动执行，而不必在系统启动后再进行手动操作，从而方便了用户的使用，可以自动完成一些需要预先执行的任务。,课堂案例2使用OEM管理存储过程,掌握在OEM中创建存储过程、执行存储过程的方法。,案例学习目标,OEM中创建存储过程、执行存储过程。,案例知识要点,课堂案例2使用OEM管理存储过程,案例完成步骤,添加标题文字,（1）启动OEM后，依次选择“方案”、“程序”下的“过程”，进入“过程”页面（2）单击“创建”按钮，进入“创建过程”对话框，输入新建存储过程的名称up_NameByID，编写存储过程的内容,添加标题文字,教师演示讲解,课堂案例2使用OEM管理存储过程,案例完成步骤-创建存储过程,添加标题文字,（1）启动OEM后，依次选择“方案”、“程序”下的“过程”，进入“过程”页面（2）单击“创建”按钮，进入“创建过程”对话框，输入新建存储过程的名称up_NameByID，编写存储过程的内容,添加标题文字,教师演示讲解,课堂案例2使用OEM管理存储过程,案例完成步骤-查看存储过程,添加标题文字,在OEM中，进入指定方案的“过程”页面，在过程列表中选择要查看的过程，单击“查看”按钮，进入“查看过程”页面,添加标题文字,教师演示讲解,课堂案例2使用OEM管理存储过程,案例完成步骤-修改存储过程,添加标题文字,（1）在OEM中，进入指定方案的“过程”页面，在过程列表中选择要查看的过程，单击“编辑”按钮，进入“编辑过程”页面,添加标题文字,教师演示讲解,课堂案例2使用OEM管理存储过程,案例完成步骤-删除存储过程,添加标题文字,（1）在OEM中，进入指定方案的“过程”页面，在过程列表中选择要查看的过程，单击“删除”按钮，进入“确认删除”页面（2）单击“是”按钮，删除过程up_NameByID,添加标题文字,教师演示讲解,课堂案例3使用PL/SQL管理存储过程,学习使用PL/SQL语句创建存储过程、修改存储过程、编译存储过程和执行存储过程的方法。,案例学习目标,使用CREATEORREPLACEPROCEDURE创建和修改存储过程、使用ALTERPROCEDURE编译存储过程、执行存储过程的几种方法。,案例知识要点,课堂案例3使用PL/SQL管理存储过程,案例完成步骤,添加标题文字,1创建存储过程使用PL/SQL创建存储过程的基本语法格式为：CREATEORREPLACEPROCEDURE用户方案.(参数1参数模式数据类型，)IS|AS参数1数据类型，BEGINPL/SQL语句END存储过程名;2执行存储过程使用PL/SQL执行存储过程的基本语法格式为：DECLARE参数1数据类型，BEGINEXECUTE用户方案.(参数1，);END;,添加标题文字,教师演示讲解,课堂案例3使用PL/SQL管理存储过程,案例完成步骤,添加标题文字,【例3-1】创建简单存储过程，显示当前的系统时间。（1）定义存储过程CREATEORREPLACEPROCEDURESCOTT.up_CurrentTimeASBEGINDBMS_OUTPUT.PUT_LINE(SYSDATE);ENDup_CurrentTime;（2）执行存储过程BEGINSCOTT.up_CurrentTime();END;,添加标题文字,教师演示讲解,课堂案例3使用PL/SQL管理存储过程,案例完成步骤,添加标题文字,【例3-2】通过存储过程添加用户记录。（1）定义存储过程CREATEORREPLACEPROCEDUREup_InsertUserASBEGININSERTINTOSCOTT.UsersVALUES(88,存储过程,普通,storeproc);EXCEPTIONWHENDUP_VAL_ON_INDEXTHENDBMS_OUTPUT.PUT_LINE(重复的用编号);WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(发生其他错误);ENDup_InsertUser;（2）执行存储过程EXECup_InsertUser;（3）查询Users表存储过程执行后，使用下列语句查看User表的记录情况。SELECT*FROMUSERS;,添加标题文字,教师演示讲解,课堂案例3使用PL/SQL管理存储过程,案例完成步骤,添加标题文字,【例3-3】创建存储过程，根据商品类别编号统计该类型所有商品的总数量（带IN参数）。（1）定义存储过程CREATEORREPLACEPROCEDUREup_CountByTid(tidinVARCHAR2)AStotalNUMBER;BEGINSELECTCOUNT(*)INTOtotalFROMSCOTT.GOODSWHEREt_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】创建存储过程，根据商品类别编号统计该类型所有商品的总数量，缺省情况下统计类别编号为“02”的商品的总数量（带缺省值的IN参数）。（1）定义存储过程CREATEORREPLACEPROCEDUREup_CountByTid(tidinVARCHAR2DEFAULT02)AStotalNUMBER;BEGINSELECTCOUNT(*)INTOtotalFROMSCOTT.GOODSWHEREt_ID=tid;DBMS_OUTPUT.PUT_LINE(total);END;,添加标题文字,教师演示讲解,课堂案例3使用PL/SQL管理存储过程,案例完成步骤,添加标题文字,【例3-5】创建存储过程，根据商品的编号获得商品的名称和类别编号（带IN和OUT参数）。（1）定义存储过程CREATEORREPLACEPROCEDUREup_GetByID(gidinVARCHAR2,gnameoutGOODS.g_Name%TYPE,tidoutGOODS.t_ID%TYPE)ASBEGINSELECTg_Name,t_IDINTOgname,tidFROMSCOTT.GoodsWHEREg_ID=gid;EXCEPTIONWHENNO_DATA_FOUNDTHENgname:=null;tid:=null;ENDup_GetByID;,添加标题文字,教师演示讲解,课堂案例3使用PL/SQL管理存储过程,案例完成步骤,添加标题文字,（2）执行存储过程-调用带输出参数的存储过程variablev_namevarchar2(50);variablev_idvarchar2(2);execup_GetByID(020001,:v_name,:v_id);printv_name;printv_id;,添加标题文字,教师演示讲解,课堂案例3使用PL/SQL管理存储过程,案例完成步骤,添加标题文字,【例3-5】编写存储过程实现两个数交换，并在比较两个数的大小中调用该存储过程（带INOUT参数）,添加标题文字,教师演示讲解,课堂案例3使用PL/SQL管理存储过程,案例完成步骤查看存储过程,添加标题文字,【例3-6】查看用户方案SCOTT的存储过程up_GetByID的信息。DESCSCOTT.up_GetByID;,添加标题文字,教师演示讲解,课堂案例3使用PL/SQL管理存储过程,案例完成步骤删除存储过程,添加标题文字,【例3-7】删除用户方案SCOTT的存储过程up_CurrentTime。DROPPROCEDURESCOTT.up_CurrentTime;,添加标题文字,教师演示讲解,课堂案例4管理函数,学习在SQLDeveloper和PL/SQL中创建函数、调用函数、删除函数的方法。,案例学习目标,SQLDeveloper中创建函数、SQLDeveloper删除函数、PL/SQL创建函数、PL/SQL调用函数、PL/SQL删除函数。,案例知识要点,课堂案例4管理函数,案例完成步骤创建函数,添加标题文字,函数（Function）与存储过程类似，也是组成一个子程序的一组PL/SQL语句。函数接受0个或多个输入参数，仅返回一个值，返回值的数据类型在创建函数时定义。1使用SQLDeveloper创建函数（1）在SQLDeveloper中右击Functions选项，从快捷菜单中选择“NewFunction”项，将开始创建函数,添加标题文字,教师演示讲解,课堂案例4管理函数,案例完成步骤创建函数,添加标题文字,（2）在打开的“CreatePL/SQLFunction”对话框内，指定用户方案为SCOTT，设置函数名称为fn_CountTypes，创建一个统计商品种类的函数,添加标题文字,教师演示讲解,课堂案例4管理函数,案例完成步骤创建函数,添加标题文字,（3）单击“确定”按钮，开始编辑函数的定义，以实现统计商品种类的函数。,添加标题文字,教师演示讲解,CREATEORREPLACEFUNCTIONfn_CountTypesRETURNNUMBERASCOUNTERNUMBER;BEGINSELECTCOUNT(t_ID)INTOCOUNTERFROMSCOTT.GOODS;RETURNCOUNTER;ENDfn_CountTypes;,课堂案例4管理函数,案例完成步骤执行函数,添加标题文字,（4）展开Functions选项，右击函数fn_CountTypes，从快捷菜单中选择“Run”项，打开运行函数对话框。,添加标题文字,教师演示讲解,课堂案例4管理函数,案例完成步骤PL/SQL创建函数,添加标题文字,【例4-1】在用户方案SCOTT中创建函数fn_TOTALVALUE，它根据客户编号查询该客户的订单总金额。CREATEORREPLACEFUNCTIONSCOTT.fn_TOTALVALUE(cidSCOTT.ORDERS.c_ID%TYPE)RETURNNUMBERAST_VALUENUMBER;BEGINSELECTSUM(d_Price*d_Number)INTOT_VALUEFROMSCOTT.ORDERDETAILSODJOINSCOTT.ORDERSOONOD.o_ID=O.o_IDWHEREc_ID=cid;RETURNT_VALUE;ENDfn_TOTALVALUE;,添加标题文字,教师演示讲解,课堂案例4管理函数,案例完成步骤调用函数,添加标题文字,【例4-2】调用用户方案SCOTT中的函数DECLAREcidSCOTT.ORDERS.c_ID%TYPE;BEGINcid:=C0001;DBMS_OUTPUT.PUT_LINE(cid|:|SCOTT.fn_TOTALVALUE(cid);END;,添加标题文字,教师演示讲解,课堂案例4管理函数,案例完成步骤删除函数,添加标题文字,1使用SQLDeveloper删除函数（1）在SQLDeveloper中右击Functions选项，从快捷菜单中选择“Drop”项，将会删除选定的函数,添加标题文字,教师演示讲解,2使用PL/SQL命令删除函数【例4-3】删除用户方案SCOTT中的函数fn_CountTypes。DROPFUNCTIONfn_CountTypes;,课堂案例5应用包,学习SQLDeveloper和PL/SQL定义包头、定义包体和使用包的方法。,案例学习目标,SQLDeveloper定义包头、SQLDeveloper定义包体、PL/SQL定义包头、PL/SQL定义包体和使用包。,案例知识要点,课堂案例5应用包,包概述,添加标题文字,包（Package）可将一些有联系的对象放置在其内部，构成一个逻辑分组，这些对象包括存储过程、函数、游标、自定义的类型（例如PL/SQL表和记录）和变量等。实际上，包相当于一个命名的声明部分，任何能在块定义部分出现的对象都可以在包中出现，用户可以从其他PL/SQL块中对包进行引用。包拥有两个独立的部分：包头和包体，它们都存储在数据字典中。定义一个包，要分别定义包头和包体。包与存储过程和函数的一个显著区别是包仅能存储在非本地的数据库中。可以将存储过程和函数定义在包中，包被保存在高速缓存中，这样体现了模块化编程的特点，使得应用系统的开发更为灵活，运行效率更高。存储过程和函数被加入到包中时，存储过程和函数的声明放在包头部分，而执行代码则放在包体部分。,添加标题文字,教师演示讲解,课堂案例5应用包,案例完成步骤SQLDeveloper定义包头,添加标题文字,（1）使用SQLDeveloper定义包头（a）在SQLDeveloper中右击Packages选项