第十讲 子程序和程序包

10第十讲子程序和程序包西南林业大学计算机与信息科学系——鲁　宁教学目标•子程序–过程–函数•程序包–说明–主体西南林业大学计算机与信息科学系——鲁 宁子程序•命名的PL/SQL 块•子程序的各个部分–声明部分–可执行部分–异常部分（可选）•子程序退出时，声明部分的对象是本地的并且终止其存在•可以使用支持PL/SQL的任何Oracle 工具进行定义•可以在PL/SQL程序块、过程、函数和程序包中声明西南林业大学计算机与信息科学系——鲁 宁子程序的优点•模块化–将程序分解为多个逻辑模块•可重用性–子程序在被执行之后，可以在任意数目的应用程序中使用•可维护性–子程序简化了维护•具有可扩展性西南林业大学计算机与信息科学系——鲁 宁子程序的类型•过程–用于执行某项操作•函数–用于执行某项操作并返回值西南林业大学计算机与信息科学系——鲁 宁存储过程•执行特定操作的子程序•存储于数据库中并可由任意匿名块调用•能够接受参数•在参数声明中的数据类型区分符应为无限制的西南林业大学计算机与信息科学系——鲁 宁存储过程的优点•增强了数据安全性•提高了数据库性能•节省内存•提高了开发的工作效率•完整性西南林业大学计算机与信息科学系——鲁 宁存储过程•具有两个部分–说明–以关键字PROCEDURE 开始，以过程名或参数列表结束–主体–以关键字IS 开始，以关键字END 结束，后面可以跟可选过程名称西南林业大学计算机与信息科学系——鲁 宁存储过程语法•语法CREATE [OR REPLACE] PROCEDURE <过程名> [(参数1, …参数N) ] IS|AS[局部声明]BEGIN可执行语句；EXCEPTION[例外处理程序]；END [<过程名>];•参数说明变量名[IN|OUT|IN OUT] 数据类型[{:= | DEFAULT} 值] 西南林业大学计算机与信息科学系——鲁 宁西南林业大学计算机与信息科学系——鲁 宁存储过程示例CREATE PROCEDURE CREATE PROCEDURE branch_sum(p_brnch branch_sum(p_brnch branch_sum(p_brnchbranch.branch_code%TYPE branch.branch_code%TYPE) IS) IS declare variables;BEGIN可执行语句；EXCEPTIONWHEN NO_DATA_FOUND THEN 语句 ;END END branch_sum branch_sum branch_sum;;西南林业大学计算机与信息科学系——鲁 宁调用存储过程•可以作为 PL/SQL 语句调用过程–示例branch_sum(branch_sum(‘‘NYK NYK’’);•可以从 SQL SQL**Plus 中调用单独的过程 •语法EXECUTE EXECUTE Procedure_name Procedure_name Procedure_name ((参数列表);•示例SQL> EXECUTE SQL> EXECUTE branch_sum(branch_sum(branch_sum(‘‘NYK NYK’’);存储过程•创建过程时，Oracle 自动执行下列步骤–编译过程–存储所编译的代码–在数据库中存储过程•PL/SQL 编译程序用于编译代码•如果发生错误，也可创建过程，但是无效•使用SHOW ERRORS 命令或下列命令查看编译错误 SELECT ** FROM USER_ERRORS;SELECT•Oracle 将编译过程装入SGA 中•其他用户也可以执行存储在SGA 中的相同过程西南林业大学计算机与信息科学系——鲁 宁存储过程•Oracle 分三个步骤执行过程–验证用户访问–如果为非法用户，则拒绝访问–验证过程有效性–如果为非法过程，则不执行–执行过程•可以通过下列方式检查过程的有效性SELECT OBJECT_NAME, OBJECT_TYPE, STATUSFROM USER_OBJECTSPROCEDURE’’;WHERE OBJECT_TYPE =WHERE OBJECT_TYPE = ‘‘PROCEDURE西南林业大学计算机与信息科学系——鲁 宁过程和函数的参数模式•IN：输入，接受值，默认模式–参数可以是一个常数、数据量、初始化变量或表达式•OUT：输出，将值返回给子程序的调用程序–参数应为一个变量；不能为一个常量或一个表达式•IN OUT：输入输出，接受值并返回已更新的值–参数应为一个变量；不能为一个常量或一个表达式–被赋值，它的值也可以对另一个变量赋值•在函数中只能使用IN模式西南林业大学计算机与信息科学系——鲁 宁西南林业大学计算机与信息科学系——鲁 宁In 参数示例Create Or Replace ProcedureProcInsertClassInfo(StrClassID ProcInsertClassInfo(StrClassID Varchar2,Varchar2, StrClassName StrClassName Varchar2) ISVarchar2) IS BeginInsert Into Insert Into ClassInfo(ClassID,ClassName ClassInfo(ClassID,ClassName ClassInfo(ClassID,ClassName)) Values(StrClassID,StrClassName Values(StrClassID,StrClassName);); end;•-- exec procinsertclassinfo('20060704','计本06');西南林业大学计算机与信息科学系——鲁 宁添加或更新记录•create or replace procedure •Procinsert_ClassInfo(StrCID Procinsert_ClassInfo(StrCID Varchar2,StrCName Varchar2) Varchar2,StrCName Varchar2)•is •RecCount RecCount int int;;•begin • Select count( Select count(**) into ) into RecCount RecCount RecCount From From From ClassInfo ClassInfo ClassInfo WhereWhere ClassID ClassID==StrCID StrCID;;• if if reccount reccount reccount>0 then>0 then • update update classinfo classinfo classinfo set set set classname classname classname==StrCName StrCName Where Where Where ClassID ClassID ClassID==StrCID StrCID;;• else• Insert into Insert into ClassInfo(ClassID,ClassName ClassInfo(ClassID,ClassName ClassInfo(ClassID,ClassName))• Values(StrCID,StrCName Values(StrCID,StrCName Values(StrCID,StrCName););• end if;• end;•/西南林业大学计算机与信息科学系——鲁 宁Out 参数示例Create Or Replace Procedure Create Or Replace Procedure ProcGetStudScore(StrStudNo ProcGetStudScore(StrStudNo ProcGetStudScore(StrStudNovarchar2,AvgScore out number )isBeginSelect Select Avg(StudScore Avg(StudScore Avg(StudScore) Into ) Into ) Into AvgScoreAvgScore From From StudScoreInfoStudScoreInfo Where Where StudNo StudNo StudNo==StrStudNo StrStudNo;; End;•--调用存储过程declareAvgScore AvgScore AvgScore number;number;beginProcGetStudScore('20010505001',AvgScore ); DBMS_OUTPUT.PUT_LINE(AvgScore DBMS_OUTPUT.PUT_LINE(AvgScore DBMS_OUTPUT.PUT_LINE(AvgScore););end;西南林业大学计算机与信息科学系——鲁 宁IN Out 参数示例Create Or Replace Procedure Create Or Replace Procedure ProcGetStudScore(StrStudNo ProcGetStudScore(StrStudNo ProcGetStudScore(StrStudNovarchar2,AvgScore in out number )isBeginDBMS_OUTPUT.PUT_LINE(AvgScore+5);Select Select Avg(StudScore Avg(StudScore Avg(StudScore) Into ) Into ) Into AvgScoreAvgScore From From StudScoreInfoStudScoreInfo Where Where StudNo StudNo StudNo==StrStudNo StrStudNo;; End;•--调用存储过程declareAvgScore AvgScore AvgScore number:=20;number:=20;BeginProcGetStudScore('20010505001',AvgScore ); DBMS_OUTPUT.PUT_LINE(AvgScore DBMS_OUTPUT.PUT_LINE(AvgScore DBMS_OUTPUT.PUT_LINE(AvgScore););end;西南林业大学计算机与信息科学系——鲁 宁Out 参数示例•create or replace procedure •Procinsert_ClassInfo(StrCID Procinsert_ClassInfo(StrCID Varchar2,StrCName Varchar2,StrMSG out varchar2) Varchar2,StrCName Varchar2,StrMSG out varchar2)•is •RecCount RecCount int int;;•begin •Select count( Select count(**) into ) into RecCount RecCount RecCount From From From ClassInfo ClassInfo ClassInfo Where Where Where ClassID ClassID ClassID==StrCID StrCID;;•if if reccount reccount reccount>0 then >0 then •update update classinfo classinfo classinfo set set set classname classname classname==StrCName StrCName Where Where Where ClassID ClassID ClassID==StrCID StrCID;;•strmsg strmsg strmsg:=':='更新成功';•else •Insert into Insert into ClassInfo(ClassID,ClassName ClassInfo(ClassID,ClassName ClassInfo(ClassID,ClassName))•Values(StrCID,StrCName Values(StrCID,StrCName Values(StrCID,StrCName););•strmsg strmsg strmsg:=':='添加成功';•end if;•end;西南林业大学计算机与信息科学系——鲁 宁调用存储过程• declare declare StrMsg StrMsg StrMsg varchar2(30); varchar2(30);• begin• procinsert_classinfo('20070704','计算机科学与技术07',strmsg);• dbms_output.put_line(strmsg dbms_output.put_line(strmsg dbms_output.put_line(strmsg););• end;西南林业大学计算机与信息科学系——鲁 宁删除存储过程•语法DROP PROCEDURE <过程名>;•示例DROP PROCEDURE DROP PROCEDURE branch_sum branch_sum branch_sum;;函数•返回值的子程序•具有一个RETURN 子句•存储于数据库中并可由任意匿名程序块调用•仅接受IN 参数•在参数声明中的数据类型区分符应为无限制的•形参不能是PL/SQL 类型西南林业大学计算机与信息科学系——鲁 宁函数的两个部分组成•说明–以关键字FUNCTION 开始，以RETURN 子句结束•主体–以关键字IS 开始，以关键字END 结束，后面可以跟可选函数名称西南林业大学计算机与信息科学系——鲁 宁西南林业大学计算机与信息科学系——鲁 宁函数语法•语法CREATE [OR REPLACE] FUNCTION <函数名> [(参数1, 1, …… 参数N) ] RETURN N) ] RETURN datatype datatype datatype IS IS [局部声明]BEGIN 可执行语句 ;[EXCEPTION][例外处理程序]END [<函数名>];•参数说明变量名 [IN] [IN] 数据类型 [{:= | DEFAULT}[{:= | DEFAULT} 值西南林业大学计算机与信息科学系——鲁 宁函数示例CREATE FUNCTION CREATE FUNCTION day_fn(mday day_fn(mday day_fn(mday NUMBER) NUMBER) RETURN CHAR IS disp_day disp_day CHAR(15); CHAR(15);BEGIN 可执行语句 ;RETURN RETURN disp_day disp_day disp_day;;EXCEPTION 语句 ;END END day_fn day_fn day_fn;;西南林业大学计算机与信息科学系——鲁 宁求N ！函数Create Or Replace Function Create Or Replace Function GetJC(N GetJC(N GetJC(N int int)) RETURN Number IS k Number:=1; Begin for i in 1..N loop k:=k k:=k**i; end loop; return K; end;西南林业大学计算机与信息科学系——鲁 宁调用函数•使用 PL/SQL 语句–示例begindbms_output.put_line(getjc(5)); end;•使用 SQL 语句–语法 SELECT SELECT function_name function_name function_name((参数) FROM DUAL;–示例Select GETJC(5) from dual;西南林业大学计算机与信息科学系——鲁 宁学生成绩统计——学生答案信息表（StudAnswer)20020316003AD ACDStudNo Custor_AnsStand_Ans20020316003A AB 20020316003ABCD ABC 20020316003B B 20010404002D AD 20010404002C C 20010404002C A 20010404002ACD ACD 20010404002B B西南林业大学计算机与信息科学系——鲁 宁学生成绩信息表（StudScoreInfo)史波梅20020505009StudScoreStudName StudNo 吴边20020505008王平20020505007包云鹏20020505006胡靖萱20020505005张璐20020505004段练20020505003沈映璧20020505002陈莉20020316003李泽伟20010404002西南林业大学计算机与信息科学系——鲁 宁评分标准：错选一个全错412标准总分24ABCD BD02BC BCD 12BD B 百分制：4*100/12=3303ABD ABC 11A A 得分标准分标准答案学生答案西南林业大学计算机与信息科学系——鲁 宁学生统分函数Create Or Replace Function GetItemScore(Stand_Ansvarchar2,Custor_Ans varchar2)return int isLenCustor int:=length(Custor_Ans);beginif LenCustor>length(stand_ans) if LenCustor>length(stand_ans) or or or Custor_Ans Custor_Ans Custor_Ans IS NULL thenIS NULL then return 0;end if;for i in 1..LenCustorloopif instr(stand_ans,substr(custor_ans,i,1))=0 thenreturn 0;end if;end loop;return LenCustor;end;西南林业大学计算机与信息科学系——鲁 宁使用SQL 语句调用函数•Select Select GetItemScore('ABC','AC GetItemScore('ABC','AC GetItemScore('ABC','AC') From Dual;') From Dual;•Select Select GetItemScore('ABC','ACD GetItemScore('ABC','ACD GetItemScore('ABC','ACD') From Dual;') From Dual;•Select Select GetItemScore('AB','ABC GetItemScore('AB','ABC GetItemScore('AB','ABC') From Dual;') From Dual;--统计各题目学生正确个数•select select studno,stand_ans,custor_ans studno,stand_ans,custor_ans studno,stand_ans,custor_ans, , getitemscore(stand_ans,custor_ans getitemscore(stand_ans,custor_ans getitemscore(stand_ans,custor_ans)) from from testanswer testanswer testanswer;;西南林业大学计算机与信息科学系——鲁 宁统计学生所有题正确数和标准分•select select studno studno studno,, Sum(getitemscore(stand_ans,custor_ans Sum(getitemscore(stand_ans,custor_ans Sum(getitemscore(stand_ans,custor_ans)),)), Sum(Length(Stand_Ans Sum(Length(Stand_Ans Sum(Length(Stand_Ans)) As )) As )) As StandScoreStandScore •from from testanswertestanswer •Group by Group by StudNo StudNo StudNo;;西南林业大学计算机与信息科学系——鲁 宁计算学生得分（百分制）•select select studno studno studno,, Sum(getitemscore(stand_ans,custor_ans Sum(getitemscore(stand_ans,custor_ans))))))**100/Sum(Length(Stand_Ans)) As Sum(Length(Stand_Ans)) As StudScoreStudScore •from from testanswertestanswer •Group by Group by StudNo StudNo StudNo;;西南林业大学计算机与信息科学系——鲁 宁建立统计视图•Create Or Replace View Create Or Replace View ViewStudTotalScore ViewStudTotalScore •as•select select studno studno studno,, round (Sum(getitemscore(stand_ans,custor_ans (Sum(getitemscore(stand_ans,custor_ans))))*100/Sum(Length(Stand_Ans)),2) As 100/Sum(Length(Stand_Ans)),2) As StudScoreStudScore •from from testanswertestanswer •Group by Group by StudNo StudNo StudNo;;西南林业大学计算机与信息科学系——鲁 宁使用视图更新学生成绩表• Update Update StudScore StudScore StudScore SS Set Set S.StudScore S.StudScore S.StudScore== (select (select V.StudScore V.StudScore V.StudScoreFrom From ViewStudTotalScore ViewStudTotalScore ViewStudTotalScore VV Where Where V.StudNo V.StudNo V.StudNo==S.StudNo S.StudNo))• Where Exists(Select (Select StudNo StudNo StudNo From From From ViewStudTotalScore ViewStudTotalScore ViewStudTotalScore VV Where Where V.StudNo V.StudNo V.StudNo==S.StudNo S.StudNo))•--查看统计结果•Select Select ** From From StudScore StudScore StudScore;;西南林业大学计算机与信息科学系——鲁 宁使用Merge 合并记录•Merge Into Merge Into StudScore StudScore StudScore SS • USING USING ViewStudTotalScore ViewStudTotalScore ViewStudTotalScore V V • On ( On (S.StudNo S.StudNo S.StudNo==V.StudNo V.StudNo))•When Matched Then• Update Set• S.StudScore S.StudScore S.StudScore==V.StudScore V.StudScore;;西南林业大学计算机与信息科学系——鲁 宁删除函数•语法DROP FUNCTION <函数名>;•示例DROP FUNCTION DROP FUNCTION day_fn day_fn day_fn;;RETURN 语句•立即完成子程序的执行，并将控制返回给调用者•内置过程–不可以包含表达式–在实现过程的正常结束之前将控制返回给调用者•内置函数–必须包含表达式，并在执行时判定西南林业大学计算机与信息科学系——鲁 宁调试存储子程序•可以使用DBMS_OUTPUT 提供的程序包调试存储子程序•可以使用PUT 和 PUT_LINEPUT_LINE 语句将变量和表达式的值输出到显示器•还可以使用SHOW ERRORS 命令查看编译错误SHOW ERRORS [PROCEDURE | FUNCTION |PACKAGE] <名称>;西南林业大学计算机与信息科学系——鲁 宁西南林业大学计算机与信息科学系——鲁 宁过程与函数作为表达式的一部分调用作为 PL/SQL 语句执行必须在规格说明中包含 RETURN 子句在规格说明中不包含RETURN 子句必须返回单个值可以返回任何值必须包含至少一条RETURN 语句可以包含 RETURN 语句，但是与函数不同，它不能用于返回值函 数过 程程序包•相关对象的封装•程序包的两部分组成–程序包规格说明–声明子程序–程序包主体–定义子程序•将逻辑相关的PL/SQL 类型、对象和子程序进行分组的数据库对象•不能对它们进行调用，也不能对其传送参数或嵌套西南林业大学计算机与信息科学系——鲁 宁程序包的优点•模块化•更轻松的应用程序设计•信息隐藏•新增功能性•性能更佳•重载西南林业大学计算机与信息科学系——鲁 宁重载•程序包中的多个子程序可以具有相同的名称•它们的形参是不同的•只能位于打包的子程序中•限制–如果子程序的参数仅名称或模式不同，则不能重载–不能基于其返回类型重载子程序西南林业大学计算机与信息科学系——鲁 宁程序包说明•是一个与应用程序的接口•声明的范围对于数据库构架而言是局部的，对程序包而言是全局的•使用CREATE PACKAGE 命令进行创建•包含公用对象和类型•声明可用的类型、常量、变量、异常、游标和子程序•可以在没有程序包主体的情况下存在西南林业大学计算机与信息科学系——鲁 宁程序包说明•是使用CREATE PACKAGE 命令创建的CREATE [OR REPLACE] PACKAGE> AS<packagenamepackagename> AS-- 公共类型和对象声明-- 子程序说明END [<程序包名称>];西南林业大学计算机与信息科学系——鲁 宁西南林业大学计算机与信息科学系——鲁 宁程序包说明示例CREATE PACKAGE airlines ASTYPE TYPE flight_day_type flight_day_type flight_day_type is RECORDis RECORD (flightno flightno flight_sch.flightno%TYPE flight_sch.flightno%TYPE,, flight_day1 NUMBER(1));CURSOR CURSOR flight_cur flight_cur flight_cur RETURN RETURN RETURN flight_day_type flight_day_type flight_day_type;;disp_day disp_day CHAR(15);CHAR(15);FUNCTION FUNCTION day_fn(mday day_fn(mday day_fn(mday NUMBER) RETURN CHAR;NUMBER) RETURN CHAR;PROCEDURE PROCEDURE branch_sum branch_sum branch_sum ( ( (p_brnch p_brnch p_brnchbranch.branch_code%TYPE branch.branch_code%TYPE););END airlines;程序包主体•实现程序包说明•完全定义游标和子程序•将实现细节和私有声明从应用程序隐含•可以将其设想为“黑箱”•可被替换增强或在不更改接口的情况下被替换•可以在不重新编译调用程序的情况下对其进行更改西南林业大学计算机与信息科学系——鲁 宁程序包主体•声明范围对于程序包主体是局部的•首次引用程序包时，运行一次程序包的初始化部分•使用CREATE PACKAGE BODY 命令进行创建•包含子程序和游标的定义•包含私有声明的类型和对象•不能在没有程序包规格说明的情况下存在西南林业大学计算机与信息科学系——鲁 宁程序包主体（续）•使用 CREATE PACKAGE BODYCREATE PACKAGE BODY 命令生成CREATE [OR REPLACE] PACKAGE BODY <程序包名称> AS-- 私有类型和对象声明-- 子程序主体[BEGIN-- 初始化语句]END [<程序包名称>];西南林业大学计算机与信息科学系——鲁 宁。