【教学课件】第四讲-PLSQL应用

第四讲PL/SQL应用本章学习目标本章将讲述PL/SQL的过程、函数、触发器及包的概念，以及如何设计和调试应用。2021/8/171第四讲PL/SQL应用本章学习目标2021/8/1714.1过程4.2函数4.3包4.4PL/SQL程序的开发与调试4.5触发器2021/8/1724.1过程2021/8/1724.1过程4.1.1过程的创建4.1.2过程的调用4.1.3过程的释放2021/8/1734.1过程4.1.1过程的创建2021/8/1734.1.1过程的创建[CREATE[ORREPLACE]]PROCEDURE<过程名>[(<参数1>，[方式l]<数据类型1>，

[<参数2>，[方式2]<数据类型2>]……)]IS|AS局部声明BEGINPL/SQL过程体END<过程名>2021/8/1744.1.1过程的创建[CREATE[ORREPLACE]CREATE表示在数据库中生成独立的存储过程；CREATEORREPLACE表示修改数据库中已有的存储过程。局部声明不需用DECLARE传参方式

（1）In表示输入给过程的参数。

（2）Out表示参数在过程中将被赋值，可以传给过程体的外部。

（3）inout表示该类参数既可以向过程体传值，也可以在过程体中赋值，以便向过程体外传值。2021/8/175CREATE表示在数据库中生成独立的存储过程；CREAT实例CREATEPROCEDUREraise_salary(emp_idINTEGER,amountREAL)IScurrent_salaryREAL;salary_missingEXCEPTION;BEGINSELECTsalINTOcurrent_salaryFROMempWHEREempno=emp_id;IFcurrent_salaryISNULLTHENRAISEsalary_missing;ELSEUPDATEempSETsal=sal+amountWHEREempno=emp_id;ENDIF;EXCEPTIONWHENNO_DATA_FOUNDTHENINSERTINTOemp_auditVALUES(emp_id,‘Nosuchemployee’);WHENsalary_missingTHENINSERTINTOemp_auditVALUES(emp_id,‘salaryisnull’);ENDraise_salary2021/8/176实例CREATEPROCEDUREraise_salar4.1.2过程的调用用EXECUTE命令

SQL>EXECUTEproc_name(par1,par2…);在PL/SQL程序块内部调用DECLAREpar1,par2;BEGIN

proc_name(par1,par2…);

END;

在调用前要声明变量par1，par2

2021/8/1774.1.2过程的调用用EXECUTE命令2021/8/174.1.3过程的释放当某个存储过程不再需要时，应将其从内存中删除，以释放它占用的内存资源。释放过程的语句格式如下：SQL>DROPPROCEDUREproc_name;2021/8/1784.1.3过程的释放当某个存储过程不再需要时，应将4.2函数4.2.1函数的创建4.2.2函数的调用4.2.3函数的释放2021/8/1794.2函数4.2.1函数的创建2021/8/1794.2.1函数的创建[CREATE[ORREPLACE]]FUNCTIONname[{parameter[，parameter，...])]RETURNdatatypesIS[localdeclarations]BEGINexecutestatements[EXCEPTIONexceptionhandlers]END[name]2021/8/17104.2.1函数的创建[CREATE[ORREPLACE]实例CREATEORREPLACEFUNCTIONHire_Day(noemp.empno%TYPE)RETURNNUMBERASvhiredateemp.hiredate%TYPE;vdayNUMBER;BEGINSELECThiredateINTOvhiredateFROMempWHEREempno=no;vday:=CEIL(SYSDATE-vhiredate);RETURNvday;END;2021/8/1711实例2021/8/17114.2.2函数的调用

无论在命令行还是在程序语句中，函数都可以通过函数名称直接在表达式中调用。例如：将函数Hire_Day(7056)的返回值赋予变量v_days。SQL>EXECUTEv_days:=Hire_Day(7056)2021/8/17124.2.2函数的调用2021/8/17124.2.3函数的释放当函数不再使用时，要用DROP命令将其从内存中删除，例如：SQL>DROPFUNCTIONHire_Day;2021/8/17134.2.3函数的释放当函数不再使用时，要用DRO4.3包4.3.1包的定义和生成4.3.2包的访问4.3.3为什么使用包？2021/8/17144.3包4.3.1包的定义和生成2021/8/17144.3.1包的定义和生成包是一种模式对象，它将PL/SQL的类型、函数、子程序等逻辑上组织在一起。一个包中可能包含如下的元素：过程（ｐｒｏｃｅｄｕｒｅ）函数（Ｆｕｎｃｔｉｏｎ）变量（Ｖａｒｉａｂｌｅ）游标（Ｃｕｒｓｏｒ）常量（Ｃｏｎｓｔａｎｔ）意外情况（Ｅｘｃｅｐｔｉｏｎ）2021/8/17154.3.1包的定义和生成包是一种模式对象，它将PL/SQL包由说明部分和包体两部分组成。在包的说明部分说明的元素（过程、函数等）是公共元素。只在包体中说明的元素是私有元素

ApplicationDatabasePackagespecificationbody2021/8/1716包由说明部分和包体两部分组成。ApplicationData包的说明包的说明部分包含公共的声明，这些声明对应用而言是可见的。

CREATE[ORREPLACE]PACKAGE＜包名>IS|AS变量、常量及数据类型定义；游标定义；函数、过程定义和参数列表及返回类型；END＜包名＞；2021/8/1717包的说明包的说明部分包含公共的声明，这些声明对应用而言是可见注意：在包的声明部分，游标由以下方式声明

CURSORcursor_name[(parameter[,parameter]…)]RETURNreturn_type声明部分的定义仅对应用的数据库模式有效，在应用范围内可见；对包而言是全局定义，可在包的任意部分使用。2021/8/1718注意：2021/8/1718包体CREATE[ORREPLACE]PACKAGEBODY＜包名＞IS|AS游标、函数、过程的具体定义；END＜包名＞；2021/8/1719包体CREATE[ORREPLACE]2021/8/17CREATEPACKAGEemp_actionAS/*Declareexternallyvisibletypes,cursor,exception.*/TYPEEmpRecTypISRECORD(emp_idINT,salaryREAL);TYPEDeptRecTypISRECORD(dept_idINT,locationVARCHAR2);CURSORdesc_salaryRETURNEmpRecTyp;invalid_salaryEXCEPTION;

实例2021/8/1720CREATEPACKAGEemp_actionAS实例/*Declareexternallycallablesubprogram.*/Procedurehire_employee(empidINT,enameVARCHAR2,

jobVARCHAR2,mgrREAL,salREAL,commREAL,deptnoREAL);PROCEDUREfire_employee(emp_idINT);PROCEDUREraise_salary(emp_idINT,gradeINT,amountREAL);FUNCTIONnth_highest_salary(nINT)RETURNEmpRecTypENDemp_actions;2021/8/1721/*DeclareexternallycallablCREATEPACKAGEBODYemp_actionsAS-----visibleonlyinthispackageCURSORdesc_salaryRETURNEmpRecTypISSELECTempno,salFROMempORDERBYsalDESC;PROCEDUREhire_employee(empidINT,enameVARCHAR2,jobVARCHAR2,mgrREAL,salREAL,commREAL,deptnoREAL)ISBEGININSERTINTOempVALUES(empid,ename,job,mgr,SYSDATE,sal,comm,deptno);ENDhire_employee;2021/8/1722CREATEPACKAGEBODYemp_actionPROCEDUREfire_employee(emp_idINT)ISBEGINDELETEFROMempWHEREempno=emp_id;ENDfire_employee;/*Definelocalfunction,availableonlyinsidepackage.*/FUNCTIONsal_ok(rankINT,salaryREAL)RETURNBOOLEANISmin_salREAL;max_salREAL;BEGINSELECTlosal,hisalINTOmin_sal,max_salFROMsalgradeWHEREgrade=rank;RETURN(salary>=min_sal)AND(salary<=max_sal);ENDsal_ok;2021/8/1723PROCEDUREfire_employee(emp_iPROCEDUREraise_salary(emp_id,gradeINT,amountREAL)ISsalaryREAL;BEGINSELECTsalINTOsalaryFROMempWHEREempno=emp_id;IFsal_ok(grade,salary+amount)THENUPDATEempSETsal=sal+amountWHEREempno=emp_id;ELSERAISEinvalid_salary;ENDIF;ENDraise_salary;2021/8/1724PROCEDUREraise_salary(emp_idFUNCTIONnth_highest_salary(nINT)RETURNEmpRecTypISemp_recEmpRecTyp;BEGINOPENdesc_salary;FORjIN1..nLOOPFETCHdesc_salaryINTOemp_rec;ENDLOOP;CLOSEdesc_salary;RETURNemp_rec;ENDnth_highest_salary;ENDemp_actions;2021/8/1725FUNCTIONnth_highest_salary(n4.3.2包的访问我们可以在数据库触发器、存储过程、3GL应用程序、Oracle工具中访问包中的类型、子程序，使用如下形式：

<包名>.<类型名><包名>.<子程序名>在SQL*PLUS上访问一个过程时，

SQL>CALLemp_actions.hire_employee(‘TATE’,‘CLERK’,…);2021/8/17264.3.2包的访问我们可以在数据库触发器、存储过程、3GL4.3.3为什么使用包？模块化包封装了逻辑上相关的类、子程序，使包之间的界面简单、清晰。便于开发应用在设计应用的初始阶段，你只需要包的声明部分，访问该包的程序即可编译通过。便于信息隐藏包可以隐藏实现的细节，简化了维护和加强改善性能你第一次调用包中的子程序时，整个包被载入内存。你改变包的实现后，调用程序无需重新编译。2021/8/17274.3.3为什么使用包？模块化2021/8/17274.4PL/SQL程序的开发与调试4.4.1程序开发4.4.2常用的系统包4.4.3调试2021/8/17284.4PL/SQL程序的开发与调试4.4.1程序开发204.4.1程序开发任务命令创建一个新的包的说明信息CREATEPACKAGE创建一个新的包体CREATEPACKAGEBODY修改一个已有的包的说明信息CREATEORREPLACEPACKAGE修改一个已有的包体CREATEORREPLACEPACKAGEBODY删除包的说明和包体DROPPACKAGE删除包体DROPPACKAGEBODY2021/8/17294.4.1程序开发任务命令创建一个新的包的说明信息CRE开发步骤１）将包的声明放入一个文本文件中２）将包体放入另一个文本文件中３）在ＳＱＬ*ｐｌｕｓ上将包的源代码编译成编译代码（Ｐ－ｃｏｄｅ），并将这两种代码存入数据库中。４）可以在ＯＲＡＣＬＥ环境，或者从一个独立的函数或过程调用一个包内的公共元素。2021/8/1730开发步骤１）将包的声明放入一个文本文件中2021/8/1734.4.2常用的系统包系统包 功能ＤＢＭＳ_ＯＵＴＰＵＴ 从一个存储过程中输出信息ＤＢＭＳ_ＤＤＬ编译过程、函数和包用ＡＮＡＬＹＺＥ获取其操作的统计信息ＤＢＭＳ_ＳＥＳＳＩＯＮ改变用户的对话为用户赋给一个角色重新初始化一个包的状态ＤＢＭＳ_ＴＲＡＮＳＡＣＴＩＯＮ 控制逻辑事务改善短的、非分布式事务的操作性能ＤＢＭＳ_ＬＯＣＫ 进行复杂的锁机制管理ＤＢＭＳ_ＡＬＥＲＴ 标识数据库中发生的某个事件ＤＢＭＳ_ＰＩＰＥ 在不同对话间传递信息2021/8/17314.4.2常用的系统包系统包 功能ＤＢＭＳ_ＯＵＴＰＵDBMS_OUTPUT过程的使用描述

分类 ＤＢＭＳ_ＯＵＴＰＵＴ过程描述输出操作 ＰＵＴ向输出缓冲区的当前行追加一些过程中的文本ＮＥＷ_ＬＩＮＥ在缓出区的尾部置一行结束标志ＰＵＴ_ＬＩＮＥ=PUT+NEW_LINE输入操作 ＧＥＴ_ＬＩＮＥ把输出缓冲区的当前行数据传给过程ＧＥＴＬＩＮＥＳ 从输出缓冲区中获取多行数据传给过程2021/8/1732DBMS_OUTPUT过程的使用描述分类 ＤＢＭＳ_ＯＵＴ4.4.3调试1）查询数据字典：列出已有过程和函数2）准备一个可执行的过程：编写脚本文件3）运行过程：执行编译和测试4）调试过程：使用DBMS_OUTPUT在SQL*PLUS中激活DBMS_OUTPUT过程SETserveroutputON…dbms_output.putline(‘valueofsalary:’||to_char(salary))2021/8/17334.4.3调试1）查询数据字典：列出已有过程和函数2021任务 包独立过程文档记录在数据字典视图USER_SOURCE中 在数据字典视图USER_SOURCE检查编译错误 在数据字典视图USER_ERRORS中在数据字典视图USER_ERRORS中开发方法利用ＳＱＬ*Ｐｌｕｓ批文件利用ＳＱＬ*Ｐlｕｓ批文件开发权限 需要CREATEPROCEDURE系统权限需要CREATEPROCEDURE系统权限使用权限需要对包有ＥＸＥＣＵＴＥ实体特权需要对过程有ＥＸＥＣＵＴＥ实体特权2021/8/1734任务 包独立过程文档记录在数据字典视图USER_SOURC文档信息

从数据字典视图ＵＳＥＲ_ＳＯＵＲＣＥ中可得到源代码文档在ＳＱＬ*ＰＬｕｓ中用ＤＥＳＣＲＩＢＥ命令可以查看过程的名字及其参数表DESCRIBEprocedure_name列名 列的描述ＮＡＭＥ实体名ＴＹＰＥ实体类型ＬＩＮＥ源代码文本的行号ＴＥＸＴ相应行的文本2021/8/1735文档信息从数据字典视图ＵＳＥＲ_ＳＯＵＲＣＥ中可得到源代码编译错误信息在ＳＱＬ*Ｐｌｕｓ中用ＳＨＯＷＥＲＲＯＲＳ命令可以获得过程或函数的译错误信息编译出错信息可通过查询数据字典视图ＵＳＥＲ_ＥＲＲＯＲＳ获得

列名列的描述ＮＡＭＥ实体名ＴＹＰＥ实体类型ＬＩＮＥ出现错误的行号ＰＯＳＩＴＩＯＮ行中出错的具体位置ＴＥＸＴ错误信息2021/8/1736编译错误信息在ＳＱＬ*Ｐｌｕｓ中用ＳＨＯＷＥＲＲＯＲＳ命4.5触发器4.5.1触发器的定义4.5.2触发器与一般存储过程的区别4.5.3触发器的建立、修改和撤消4.5.4触发器的调试2021/8/17374.5触发器4.5.1触发器的定义2021/8/17374.5.1触发器的定义

制作一个数据库触发器，目的是在某个表上执行特定的数据维护操作时隐含地执行一个ＰＬ／ＳＱＬ块

2021/8/17384.5.1触发器的定义制作一个数据库触发器，目的4.5.2触发器与一般存储过程的区别数据库触发器存储过程当某类数据操纵发生时隐式地调用 从一个应用或过程中显式地调用在触发器体内禁止使用COMMIT,ROLLBACK等事务控制语句在过程体内可以使用所有PL/SQL块中能使用的SQL语句2021/8/17394.5.2触发器与一般存储过程的区别数据库触发器存储过程当4.5.3触发器的建立、修改和撤消使用CREATETRIGGER语句创建或修改触发器

CREATE[ORREPLACE]TRIGGER<触发器名>{BEFORE|AFTER}{<触发事件>[OR...]}ON<表名>[FOR[EACH]{ROW|STATEMENT}][WHEN(<条件>)]<PL/SQL块>|<调用过程语句

>2021/8/17404.5.3触发器的建立、修改和撤消使用CREATETRI

在写触发器的源代码前，需要先确定以下内容内容 描述可能值触发时间与触发事件的时间次序BEFOREAFTER触发事件 触发触发器的操作事件类型INSERT

UPDATEDELETE触发器类型 触发子体被执行的次数STATEMENTROW触发限制引发触发器必须满足的条件布尔表达式触发器体该触发器将要执行的动作ＰＬ／ＳＱＬ块2021/8/1741在写触发器的源代码前，需要先确定以下内容内容 描述触发器作为过程，是PL/SQL块，当触发语句发出、触发限制计算为真时该过程被执行删除触发器的语句格式为：

DROPTRIGGER<触发器名>ON<表名>;2021/8/1742触发器作为过程，是PL/SQL块，当触发语句发出、触发限制计实例编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。CREATEORREPLACETRIGGERdel_emp_dept