Oracle第7章存储过程操作

第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;BEGIN

IFMOD(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的所有正整数之和。SETSERVEROUTPUTONDECLARE iINTEGER:=1; sINTEGER:=0;BEGIN

WHILEi<=100LOOP s:=s+i; i:=i+1;

ENDLOOP; DBMS_OUTPUT.PUT_LINE('1+2+...+100='||s);END;课堂案例1——使用PL/SQL编程循环结构-Loop循环【例1-8】使用LOOP循环求1到100的所有正整数之和。SETSERVEROUTPUTONDECLARE iINTEGER:=1; sINTEGER:=0;BEGIN

LOOP s:=s+i; i:=i+1; EXITWHENi>100;

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;BEGINWHILEnum<=10LOOPIFMOD(num,2)=0ANDMOD(num,3)=0THEN

GOTOdisplay;ENDIF;num:=num+1;ENDLOOP;<<display>> DBMS_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获取查询的商品的基本信息。SETSERVEROUTPUTONDECLARE

v_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获取查询的商品基本信息。SETSERVEROUTPUTONDECLARE

v_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,240×320像素,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语句创建存储过程、修改存储过程、编译存储过程和执行存储过程的方法。案例学习目标使用CREATE[ORREPLACE]PROCEDURE创建和修改存储过程、使用ALTERPROCEDURE编译存储过程、执行存储过程的几种方法。案例知识要点课堂案例3—使用PL/SQL管理存储过程案例完成步骤添加标题文字1．创建存储过程使用PL/SQL创建存储过程的基本语法格式为：

CREATE[ORREPLACE]PROCEDURE[用户方案.]<存储过程名> [(参数1参数模式数据类型[，…])] IS|AS [参数1数据类型[，…]] BEGIN PL/SQL语句

END[存储过程名];2．执行存储过程使用PL/SQL执行存储过程的基本语法格式为：

[DECLARE

参数1数据类型[，…]] BEGIN [EXECUTE][用户方案.]<存储过程名>[(参数1[，…])]; END;添加标题文字教师演示讲解课堂案例3—使用PL/SQL管理存储过程案例完成步骤添加标题文字【例3-1】创建简单存储过程，显示当前的系统时间。（1）定义存储过程

CREATEORREPLACEPROCEDURESCOTT.up_CurrentTime AS BEGIN DBMS_OUTPUT.PUT_LINE(SYSDATE); ENDup_CurrentTime;（2）执行存储过程

BEGIN

SCOTT.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(tidinVARCHAR2DEFAULT'02')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;BEGIN SELECT COUNT(t_ID)INTOCOUNTER FROM SCOTT.GOODS; RETURN COUNTER;ENDfn_CountTypes;课堂案例4—管理函数

案例完成步骤—执行函数添加标题文字（4）展开Functions选项，右击函数fn_CountTypes，从快捷菜单中选择“Run”项，打开运行函数对话框

。添加标题文字教师演示讲解

课堂案例4—管理函数

案例完成步骤—PL/SQL创建函数添加标题文字【例4-1】在用户方案SCOTT中创建函数fn_TOTALVALUE，它根据客户编号查询该客户的订单总金额。CREATEORREPLACEFUNCTIONSCOTT.fn_TOTALVALUE (cidSCOTT.ORDERS.c_ID%TYPE) RETURN NUMBER AS T_VALUE NUMBER; BEGIN SELECT SUM(d_Price*d_Number)INTOT_VALUE FROMSCOTT.ORDERDETAILSOD JOINSCOTT.ORDERSO ONOD.o_ID=O.o_ID WHEREc_ID=cid; RETURN T_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选项，从快捷菜单中选择“NewPackage”项（b）在打开的“CreatePL/SQLPackage”对话框中，输入包的名称（如pkg_Goods）（c）单击“确定”按钮，开始编写包头定义部分添加标题文字教师演示讲解

课堂案例5—应用包案例完成步骤—PL/SQL定义包头添加标题文字【例5-1】创建包pkg_DisplayGoods，其包括一个存储过程和一个函数，其中存储过程实现根据商品编号查询商品名称、类别名称和商品价格的功能，函数实现根据商品类别编号返回该类别商品的总库存量的功能。添加标题文字教师演示讲解

课堂案例5—应用包案例完成步骤—SQLDeveloper定义包体添加标题文字（1）使用SQLDeveloper定义包体（a）在SQLDeveloper的Package选项中右击已经定义包头的pkg_Goods选项，从快捷菜单中选择“CreateBody”项（b）开始编写包体定义部分添加标题文字教师演示讲解

课堂案例5—应用包案例完成步骤—PL/SQL定义包体添加标题文字【例5-2】实现包pkg_DisplayGoods中存储过程和函数的功能。添加标题文字教师演示讲解

课堂案例5—应用包案例完成步骤—使用包添加标题文字1．包的初始化当第一次调用打包子程序时，该包将进行初始化，即将该包从辅存中读入内存，并启动调用的子程序的编译代码。2．引用包中对象在包中定义的任何对象既可以在包内使用，也可以在包外使用。在外部引用包中对象时，可以通过使用包名作为前缀对其进行引用添加标题文字教师演示讲解

课堂案例5—应用包案例完成步骤—使用包添加标题文字【例5-3】调用包pkg_DisplayGoods中存储过程和函数，查询商品编号为“200708011430”的商品信息和统计商品类别编号为“01”的商品总数量。添加标题文字教师演示讲解

课堂案例5—应用包Oracle内置包添加标题文字Oracle11g提供了很多具有特定功能的内置包

添加标题文字教师演示讲解

编写PL/SQL语句块，使用IF语句求出三个数中最大的数

任务1编写PL/SQL语句块，使用LOOP和FOR-IN-LOOP-ENDLOOP循环计算1+3+5+…+99的值

任务2创建存储过程up_Borrow，要求该存储过程返回未还图书的借阅信息，包括借书人、借书日期、图书名称和图书作者

任务3执行“【任务3】”所创建的存储过程up_Borrow，查询所有未还图书的详细信息。任务4创建存储过程up_BorrowByID，要求该存储过程能够根据输入的读者号返回该读者的所有借阅信息，包括借书日期、还书日期、图书名称和图书作者。任务5执行“【任务5】”所创建的存储过程up_BorrowByID，查询读者号为“0016584”的借阅信息。任务6演讲完毕，谢谢观看！附录资料：不需要的可以自行删除​oracle数据库培训内容*基本概念*Oracle的体系结构SQL*Plus的使用及常用命令*SQL*NET的配置和使用数据库的备份(exp/imp)其它常见问题​一、基本概念什么是数据库：是存储在一起的相关数据的集合。这些数据无有害或不必要的冗余，为多种应用服务。数据的存储独立于使用它的应用程序。对数据库插入新数据、修改和检索原有数据均能用一种公用的和可控的方法进行。数据库能做什么：数据库的基本功能就是存储数据，而且能够为用户提供便捷、快速的查找、修改、添加和删除数据等功能。什么是SQL语言：是运用于数据库的语言。它除了具有数据库查询、插入、删除、修改等功能外，可以独立使用，也可以嵌入其它主语言中使用。它的语法类似于英语。​什么是oracle：oracle是以高级结构化查询语言(SQL)为基础的大型关系数据库，通俗地讲它是用方便逻辑管理的语言操纵大量有规律数据的集合。是目前最流行的客户/服务器(CLIENT/SERVER)体系结构的数据库之一。​二、Oracle的体系结构(1)物理结构

ORACLE数据库在物理上是存储于硬盘的各种文件。它是活动的，可扩充的，随着数据的添加和应用程序的增大而变化。datafilesredologfilescontrolfilesparameterfile

数据文件日志文件控制文件参数文件

datafilesredologfilescontrolfileparameterfiles*.dbf*.logInitoraid.ora*.ctl​(2)session(连接)

Oracle是多用户、多任务、可分布式管理的数据库，同

时可有许多个用户对数据库操作。oracleuseruserusersession……​

ORACLE数据库在逻辑上是由许多表空间构成。主要分为系统表空间和非系统表空间。非系统表空间内存储着各项应用的数据、索引、程序等相关信息。我们准备上马一个较大的ORACLE应用系统时，应该创建它所独占的表空间，同时定义物理文件的存放路径和所占硬盘的大小。Oracle数据存储单位a、block数据块：2k最小的I-O单位，伴随database产生而产生，不可变b、extent一组连续的数据块：是用户所能分配存储的最小单位c、segment段：有共同结构的一个或几个区域(extent)d、tablespace表空间：一组物理数据的逻辑组合，(象逻辑间数据仓库)e、file文件：属于某个表空间的物理文件f、database数据库：一组表空间所构成的逻辑的可共享的数据。(3)存储结构​表空间tablespacesystemRBSnosystem把system表空间单独放在一个硬盘上做成一组镜像toolsuserstempapplicationdataapplicationindex分开存放减少争用​segment段的分类data数据段存储对象object(table,view,index,sequence...)index索引段temporary临时段用做(join,groupby,orderbysorting操作)rollback回滚段用于记录修改前后信息bootstrap启动段存储数据字典系统信息不能读写，放在系统表空间内，约占40几个block​datasegment中可能有十种不同类型的存储对象(1)table(2)view实为一个select语句(3)index(一个表不多于3个)(4)cluster(簇)(5)sequence序列(6)synonyms用于定义某个远程数据库同义词，实现分式数据库管理透明(7)snapsot快照(8)storedprocedure(function)(9)package程序包(10)dbtrigger数据触发器，处理updata,delete,insert中可能出现的问题​常用的ORACLE的数据库字段类型字段类型中文说明限制条件其它说明CHAR固定长度字符串最大长度2000bytesVARCHAR2可变长度的字符串最大长度4000bytes可做索引的最大长度749DATE日期（日-月-年）LONG超长字符串最大长度2G（231-1）足够存储大部头著作RAW固定长度的二进制数据最大长度2000bytes可存放多媒体图象声音等LONGRAW可变长度的二进制数据最大长度2G同上NUMBER(P,S)数字类型P为整数位，S为小数位INTEGER整数类型小的整数FLOAT浮点数类型NUMBER(38)，双精度REAL实数类型NUMBER(63)，精度更高……​三、SQL*Plus的使用及常用命令进入sql*plus；数据查询操作（select)；改变数据操作(insert、update、delete)；其它常用sql和sql*plus命令(spool、create、drop、desc、connect、grant……)。​数据查询操作简单查询select列名1，列名2，列名3……from表名where条件orderby列名1,列名2……；​逻辑运算符>>=<<=<>或!=其它(not)inis(not)betweenandandor单值函数nvl----空值替换函数abs---绝对值函数mod---求模函数power---求幂函数sqrt-----平方根函数exp，ln，log----对数函数sin，cos等----三角函数​唯一性查询关键字：distinctselectdistinct列名from表名where条件；​模糊查询select列名1，列名2，列名3……from表名where列名1like‘_%mm’orderby列名1,列名2……；关键字：(not)like通配符：%_​联接查询select表名1.列名1，表名2.列名2，列名3……from表名1，表名2……where条件orderby表名1.列名1,表名2.列名2……；​子查询单值子查询

多值子查询select列名1，列名2，列名3……from表名1where列名1=（select列名1from表名2where条件）orderby列名1,列名2……；select列名1，列名2，列名3……from表名1where列名1in（select列名1from表名2where条件）orderby列名1,列名2……；​组值函数avg---平均值函数count---非空值数目max---最大值函数min---最小值函数sum---求和函数distinct---唯一性函数分组查询select列名1，列名2，sum(列名3)……from表名where条件groupby列名1,列名2……；关键字：groupby​改变数据操作Insert插入操作insertinto表名1（列名1，列名2……）values(值1，值2……）；注意：⑴值表中的数据用逗号间隔；⑵字符型和日期型要用单引号括起来；⑶缺省的日期格式为dd-man-yy。insertinto表名1usingselect列名1，列名2……from表名2where条件；​Update修改操作可修改单个字段、修改多行、修改单个列。update表名set列名=值或表达式where条件；​Delete删除deletefrom表名where条件；注意⑴不能只删除行的一部分⑵where子句决定哪一行将被删除。如里省略了where子句，则所有的行都将被删除。​其它常用sql命令Createtable---创建表；例子Droptable---删除表，sql同时自动删除该表上的索引、特权，但并不删除相关的视图或同义词；Grant---授权Connect-----连上Oracle,做最基本操作resource----具有程序开发最基本的权限dba----------数据库管理员所有权限​@-------运行指定的命令文件，作用等价于start命令；/---------运行sql缓冲区的命令，与run命令等价；Connect---退出当前账号，向指定的用户帐号登录；Desc-----显示指定表的结构；Exit-----中止sql*plus，返回操作系统；Spool---将屏幕显示内容输出到一个文件上；Spooloff--输出文件结束；其它常用sql*plus命令​四、SQL*NET的配置和使用1配置网络协议TCP/IP

2测试网络是否连通3在Windows95上选择“开始”按钮，选定“程序”中的“OracleforWindows95”下的“SQL*NetEasyConfiguration”，在“SQL*NetEasyConfiguration”窗口中选择“AddDatabaseAlias”，在“ChooseDatabaseAlias”对话框中“DatabaseAlias”项中键入数据库别名，按“OK”按钮确认。

在“ChooseTCP/IPHostNameandDatabase”对话框中的TCP/IPHostname项中键入SERVER的IP地址，在“DatabaseInstance”项中键入Oracle的实例名。在“ConfirmAddingDatabaseAlias”对话框中可以看到你新增加的数据库别名。按“YES”按钮，按“OK”，退出“SQL*NetEasyConfiguration”。​五、数据库备份(exp/imp)EXPORT将数据库中数据备份成一个二进制系统文件称为“导出转储文件”(EXPORTDUMPFILE)，并将重新生成数据库写入文件中。它有三种模式：a.用户模式：导出用户所有对象以及对象中的数据；b.表模式：导出用户所有表或者指定的表；c.整个数据库：导出数据库中所有对象。IMPORT导入：导入的过程是导出的逆过程，这个命令先读取导出来的导出转储二进制文件，并运行文件，恢复对象用户和数据。​数据库导出过程exp80用户名/密码Enterarrayfetchbuffersize:4096>exportfile:expdat.dmp>(1)Entiredatabase(2)Users(3)Table:(2)Users>Exportgrants:yes>Exporttabledata:yes>