《应用程序结构》PPT课件.ppt

1,Oracle 数据库系统第12章 应用程序结构,2,本章要点,理解子程序（过程、函数）及包的概念 掌握如何创建、执行和删除过程、函数及包的方法 灵活使用过程、函数及包书写PL/SQL程序 掌握创建和测试DML触发器、instead-of数据库触发器以及系统触发器 了解数据库触发器的使用时机和限制,3,子程序,子程序： PL/SQL的过程和函数统称为子程序 匿名块： 以DECLARE或BEGIN开始，每次提交都被编译。匿名块不在数据库中存储并且不能直接从其他PL/SQL块中调用。 命名块：除匿名块之外的其他块。包括过程，函数，包和触发器。可以在数据库中存储并在适当的时候运行。,4,子程序的优点,具有可扩展性 可以自定义 PL/SQL 语言以满足应用程序的需要 提高可复用性和可维护性 子程序只要有效，就完全可以用于任何数目的应用程序中 简化了维护/优化过程，因为如果定义更改只有子程序受到影响,5,创建子程序,创建过程（语法如下）,CREATE OR REPLACE PROCEDURE procedure_name (argumentIN|OUT|IN OUTtype, . argument IN| OUT| IN OUT type) IS| AS procedure_body,6,创建过程,过程的结构应具有下面所示的特征：,CREATE OR REPLACE PROCEDURE procedure_name parameter_list AS /*Declarative section is here*/ BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END procedure_name;,7,创建过程,CREATE OR REPLACE PROCEDURE search_emp (p_empno IN NUMBER,p_ename OUT VARCHAR2(20), p_deptno OUT NUMBER) IS v_name VARCHAR2(20); v_deptno NUMBER; BEGIN SELECT ENAME,DEPTNO INTO v_name, v_deptno FROM EMP WHERE EMPNO= p_empno; p_ename := v_name ; p_deptno := v _deptno ; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(EMPNO:| p_empno|NO EXIST ); END search_emp;,8,创建函数,函数与过程的比较： 两者都带有参数，参数可以具有模式。 两者都是PL/SQL语句块的不同形式，都有声明段、可执行段以及异常段。 两者都可以存储在数据库中或在语句块中声明 两者不同的是，过程调用本身是一个PL/SQL语句，而函数调用是作为表达式的一部分被调用的。,9,函数的语法,非常类似于创建过程的语法。其定义如下：,CREATE OR REPLACE FUNCTION function_name ( argumentIN | OUT | IN OUT type, . argumentIN | OUT | IN OUT type) RETURN return_typeIS | AS function_body,10,RETURN语句,RETURN语句用来把控制返回到调用环境中。,语法如下： RETURN expression;,当该语句执行时，如果表达式的类型与定义不符，该表达式将被转为函数定义子句RETURN中指定的类型，同时，控制将立即返回到调用环境。,11,删除过程和函数,删除操作是将过程或函数从数据字典中删除。,删除过程的语法如下,删除函数的语法如下：,DROP PROCEDURE procedure_name;,DROP FUNCTION function_name ;,12,子程序参数,1、参数模式 2、在形参和实参之间传递数值 3、对形参的约束 4、子程序内部引发的异常 5、按引用和按值传递参数 6、使用NOCOPY参数 7、不带参数的子程序 8、定位符和命名符 9、参数缺省值,13,1、参数模式,过程和函数的参数可以有不同的模式，并可以按值或按引用进行传递。,14,DECLARE - Variables describing the new employee v_NewEmpNo emp.empno%TYPE:=7968; v_NewName emp.ename%TYPE:=Cynthia; v_NewJob emp.job%TYPE:=Manager; v_Mgr emp.mgr%TYPE:=7839; v_HireDate emp.hiredate%TYPE:=20-SEP-03; v_Sal emp.sal%TYPE:=2010; v_Comm m%TYPE:=NULL; v_DeptNo emp.deptno%TYPE:=40; BEGIN - Add Cynthia to the database. HireNewEmployee(v_EmpNo,v_EName,v_Job,v_Mgr, v_HireDate,v_Sal,v_Comm,v_DeptNo); END;,举例,15,参数模式,16,2、在形参和实参之间传递数值,文字或常数作为实参 因为复制功能的使用，对应于参数IN OUT或OUT的实参必须是变量，而不能是常数或表达式。也就是说，程序必须提供返回的变量的存储位置。 编译检查 PL/SQL编译器在创建过程时将对合法的赋值进行检查。 从OUT参数读取数据,17,3、对形参的约束,调用过程时，实参的值将被传入该过程，这些实参在该过程内部以引用的方式使用形参。 同时，作为参数传递机制一部分，对变量的约束也传递给该过程。 在过程的声明中，强制指定参数CHAR和VARCHAR2的长度，以及指定NUMBER参数的精度或小数点后位数都是非法的，这是因为这些约束可以从实参中获得。,18,错误声明： CREATE OR REPLACE PROCEDURE ParameterLength( p_Parameter1 IN OUT VARCHAR2(10), p_Parameter2 IN OUT NUMBER(3,1) AS BEGIN p_Parameter1:=abcdefghijklm; p_Parameter2:=12.3; END ParameterLength;,举例,正确声明： CREATE OR REPLACE PROCEDURE ParameterLength( p_Parameter1 IN OUT VARCHAR2, p_Parameter2 IN OUT NUMBER) AS BEGIN p_Parameter1:=abcdefghijklmno; p_Parameter2:=12.3; END ParameterLength;,19,4、子程序内部引发的异常,如果错误发生在子程序的内部，就会引发异常。 该异常既可以是由用户定义的，也可以是程序中预定义的。 如果引发异常的过程中没有处理该错误的异常处理程序（或该异常发生在该异常处理程序的内部），根据异常的传播规则，控制将立即转出该过程返回其调用环境。然而，在这种情况下，OUT和IN OUT形参的值并没有返回到实参。这些实参仍将被设置为调用前的值。,20,5、按引用和按值传递参数,子程序参数可以按即按引用或按值传递。 按引用传递：一个指向实参的指针将被传递到对应的形参。 按值传递：实参的值将被赋予对应的形参。,21,6、使用NOCOPY参数,语法如下：,parameter_name mode NOCOPY datatype,parameter_name是参数名 mode是参数的模式（IN，OU，IN OUT） datatype是参数的数据类型 如果使用了NOCOPY，则PL/SQL编译器将按引用传递参数，而不按值传递 由于NOCOPY是一个编译选项，而非指令，所以该选项不总是被采用。,22,使用NOCOPY时的异常语义,当参数通过引用传递时，任何对实参的修改也将引起对其对应形参的修改，这是因为该实参和形参同时位于相同的存储单元的缘故。换句话说，如果过程退出时没有处理异常而形参已被修改的话，则该形参对应的实参的原始值也将被修改。,23,使用NOCOPY的限制,在某些情况下，NOCOPY将被编译器忽略，这时的参数仍将按值传递。在这种情况下，编译器不会报告编译错误。由于NOCOPY是一个提示项（Hint）,编译器可以决定是否执行该项。,24,使用NOCOPY的优点,NOCOPY的主要优点是可以提高程序的效率。当我们传递大型PL/SQL表时，其优越性特别显著。 按值传递IN OUT模式的参数所使用的时间远远大于按引用传递IN和IN OUT NOCOPY参数所使用的时间。,25,7、不带参数的子程序,如果过程没有参数的话，就不需要在该过程调用声明中或在其过程调用中使用括弧。函数也具有类似的情况。 函数与过程类似 。,26,8、定位符和命名符,实参都通过位置与对应的形参相关联，参数间的这种对应法称为定位符（Positional Notation）。 命名符中，对于每一个参数，都包含了形参和实参。这就允许我们在需要时重新安排参数的顺序。 定位符和命名符也可以在同一个调用中混合使用。但是，该类调用的第一个参数必须是通过位置指定，而其余的参数可以根据名称指定。,27,定位符与命名符的对比,28,9、参数缺省值,过程或函数的形参可以具有缺省值。如果一个参数有缺省值的话，该参数就可以不从调用环境中传递。如果传递了一个值，则实参的值将取代缺省值。,parameter_name mode parameter_type:=|DEFAULT initial_value,29,过程与函数的比较,相同点： 通过设置OUT参数，过程和函数都可以返回一个以上的值。 过程和函数都可以具有声明段、可执行段和异常处理段。 过程和函数都可以接受缺省值。 都可以使用位置或名称对应法调用过程和函数。 过程和函数都可以接受参数NOCOPY（仅Oracle8i及更高版本支持）。,30,过程与函数的比较,区别： 如果返回值在一个以上时，用过程为好。 如果只有一个返回值，使用函数就可以满足要求。 函数还可以从SQL语句中调用。,31,过程、函数练习,1、创建一个过程，向DEPT插入一个新单位，通过两个变量提供单位编号和单位名称 2、创建一个过程，修改DEPT中的某一单位的单位名称，过程应包括异常处理。 3、创建一函数，以确认指定的DEPTNO是否存在，返回BOOLEAN型值 4、创建一个过程，新增一员工，调用上题函数判断DEPTNO的存在，如果不存在发出警告，员工各数据项均有默认值。,32,包,包是由存储在一起的相关对象组成的PL/SQL结构。 包有两个独立的部分：说明和包体，这两部分独立地存储在数据字典中。 包只能被存储，而且不可能是局部的。,33,包的说明,语法：CREATE OR REPLACE PACKAGE package_nameIS|AS type_definition| procedure_specification| function_specification| variable_declaration| exception_declaration| cursor_declaration| pragma_declaration END package_name;,34,包的说明,包的说明：也叫做包头，包含了有关包的内容的信息。该部分中不包括任何子程序代码。,35,包体,包体是一个独立于包头的数据字典对象。 包体只能在包头完成编译后才能进行编译。 包体中包含包头中预先声明的子程序的代码。 除此之外，包体还可以包括对包体是全局的其他声明，但这些附加说明对于说明部分是不可见的。 包体是可选的。如果包头中不包含任何过程或函数的话（只有变量声明，游标，类型等），那么包体就不必存在。 包头中的任何预先声明不能出现在包体中。包头和包体中的过程和函数的说明必须一致，其中包括子程序名和其参数名，以及参数的模式。,36,包和作用域,包头中声明的任何对象都是在其作用域中，并且可在其外部使用包名作为前缀对其进行引用。 在包体内，包头中的对象可以不带包名进行直接引用。 在包体中可以包含私有声明。这些定义的类型和数据项可以且只能在包体内部使用。,37,重载封装子程序,在包的内部，过程和函数可以被重载。也就是说，可以有一个以上具有相同名称但参数不同的过程或函数。 如果两个子程序的参数仅在名称和模式上不同，则这两个过程不能重载。 不能只根据两个过程的不同返回类型对其进行重载。 重载函数的参数的类型系列（type family）必须不同，也就是说，不能对同一个类型系列的过程进行重载。,38,包的初始化,定义： 当第一次调用封装子程序时，或者对封装变量或类型进行引用时，就对包进行了实例化。 在大多数情况下，在会话内首次对一个包进行实例化操作时，要运行初始化代码。,39,小结,命名块是已经命名并保存的PL/SQL语句块，程序中可以重复使用它们。存储子程序是保存在数据库中的命名块。 PL/SQL有两种类型子程序：过程和函数。子程序是一种可以执行操作和接收参数的命名块。 子程序的结构是标准的PL/SQL结构，包括声明段、可执行段和异常处理段。 过程是一个PL/SQL语句，而函数是表达式的一部分。 函数使用一些参数，并且必须返回一个值。在声明段包括了一个RETURN语句，指出返回值的数据类型，在可执行段至少包括一个RETURN语句，但只会执行其中一个。过程中的RETURN语句是不同的，它只是控制执行语句的流程。 形参指的是子程序声明段中的参数。实参是用来在调用子程序时向子程序传递值的变量。形参具有3种模式：IN、OUT和IN OUT。,40,小结,子程序参数可以按两种方式传递参数值：按引用或按值传递。当参数是按引用传递时，一个指向实参的指针将被传递到对应的形参。当参数是按值传递时，实参的值将被赋予对应的形参。 将值传递到参数可以使用两种方法：占位符和命名符。 包是由存储在一起的相关对象组成的PL/SQL结构。包有两个独立的部分：说明和包体，这两部分独立地存储在数据字典中。 在包的说明中声明的对象是全局的，可以在包外引用它们。在包体声明的所有元素都是局部的，只能在包体中引用它们，而不能在包外引用。 重载是包的一种特性，它允许创建多个具有相同名称的子程序。这允许同一个子程序可以接受不同类型的参数。重载子程序的参数必须在参数数量、数据类型系列或者顺序上有所不同。,41,局部子程序,局部子程序:是一个在PL/SQL语句块的声明段中声明的子程序。 子函数只在其声明的语句块中可见，其作用域从声明点开始到该语句块结束为止。其他语句块不能调用该函数，因为该函数对其他语句块来说是不可见的。,42,局部子程序（续）,局部子程序也可以声明为存储子程序声明段的一部分 局部子程序的位置：任何局部子程序都必须在声明段的最后进行声明 预先声明 ：局部PL/SQL子程序的名称是标识符，必须在被引用前声明。 局部子程序也可以重载,43,存储子程序和局部子程序的比较,44,存储子程序和包的注意事项,优势： 可以由多个数据库用户共享 隐含影响： 存储子程序间的依赖性 包状态的处理方法 运行存储子程序 包所需要的特权,45,子程序的依赖性,依赖：数据字典记录了存储过程或函数所引用的所有Oracle对象。该过程或函数就依赖于这些存储的对象。 自动重编译:如果一个依赖对象失效，PL/SQL引擎将在该对象再次被调用时对其重新进行编译。 包的依赖性：包体的变化不会导致修改包头。因此，其他依赖于该包头的对象也不需要进行重新编译。如果该包头有变化，则会使包体自动失效，这是因为该包体依赖于包头 。 如何确定无效 :通过数据字典在不断地跟踪对象间依赖关,46,包运行时状态,当第一次实例化一个包时，将从磁盘中读入该包的伪代码并将其放入系统全局工作区SGA的共享池中。 包的运行时状态，即打包的变量和游标，则存放在用户全局区（UGA）的会话存储区中。这就保证了每个会话都将有其运行时状态的副本。 可串行复用包和非串行复用包：PL/SQL 2.3版及更高版本允许程序员对包做可串行复用标志。,47,包运行时状态,48,EXECUTE特权,为了能够对表进行访问，必须使用SELECT，INSERT，UPDATE和DELETE对象特权。GRANT语句把这些特权赋予数据库用户或角色。对于存储子程序和包来说，相关的特权是EXECUTE。,49,50,51,存储子程序和角色,子程序使用显式地（而不是通过角色）授权其所有者的特权而执行。 为了使通过角色授权的特权作用在存储子程序和触发器内被使用，每次运行过程时，必须对该特权进行检查。,52,调用者的权限与定义者的权限,Oracle8i提供了不同的外部引用解决方案。在调用者的权限子程序中，外部引用是通过调用者而不是所有者的特权设置而执行的。调用者的权限程序是通过使用AUTHID子句而创建，该语句只适用于独立子程序、包说明和对象类型说明。 在包内部或对象类型中的独立子程序必须都是调用者子程序或都是定义者子程序，而不能混合。,53,AUTHID的语法如下： CREATE OR REPLACE FUNCTION function_name parameter_list RETURN return_type AUTHID CURRENT_USER | DEFINER IS | AS function_body; CREATE OR REPLACE PROCEDURE procedure_name parameter_list AUTHID CURRENT_USER | DEFINER IS | AS function_body; CREATE OR REPLACE PACKAGE package_spec_name AUTHID CURRENT_USER | DEFINER IS | AS package_spec; CREATE OR REPLACE TYPE type_name AUTHID CURRENT_USER | DEFINER IS | AS OBJECT type_spec;,54,默认参数,从过程化语句中调用一个函数时，如果该函数有形参的话，可以使用其缺省值。然而，如果从SQL语句调用函数时，必须指定所有参数。 必须使用定位符（Positinal Notation）,而不能使用命名符（Name Notation）。,55,包的辅助功能,共享池锁定 包体长度的限制 优化参数,56,共享池锁定,DBMS_SHARED_POOL包允许程序员把一个对象锁定在共享池中。 当该对象被锁定后，除非由程序申请对其清除，否则无论共享池有多满，也不管是否有程序访问该对象，该对象将常驻在共享池中。 这种处理方法有利于提高程序的运行效率，因为从系统的磁盘重新装入对象要进行大量读写操作。锁定对象还有助于最大限度地减少共享池的碎片。,57,包体长度的限制,编译器对包体长度的限制如下 ： Diana树中的节点数。在Oracle8i之前的版本中，Diana节点的最大数目是32K，Oracle8i以上版本将包体和类型体的限制扩充到了64兆字节的容量。 编译器生成的临时中间变量的容量最多21K字节。 入口点的数量。一个包体最多可以有32K个入口点，入口点可以是过程或函数。 字符串的数量。PL/SQL对字符串的限制单位是232。,58,触发器,触发器类似于函数和过程，它们都是具有声明段、可执行段和异常处理段的命名PL/SQL语句块。像包一样，触发器必须在数据库中存储为独立的对象。 主要用于： 维护那些通过创建表时的声明约束无法实现的复杂的完整性约束。 通过记录已进行的改变以及是谁进行了该项改变来检查一个表中的信息。 当一个表发生改变时，自动向其他程序发送需要采取行动的信号。 在一个发布-预订环境中发布有关各种事件的信息。,59,触发器的类型,触发器主要有三种类型： DML INSTEAD-OF触发器 系统触发器。,60,DML触发器,DML触发器可以由DML语句激发 由DML语句的类型决定DML触发器的类型。 DML触发器可以在DML语句操作之前或之后激发。 DML触发器也可以在行或语句操作上激发 。,61,INSTEAD-OF触发器,INSTEAD-OF 触发器是行级别的,执行时，激发它的DML语句不执行。 只能定义在视图上。 Oracle8及更高版本才提供。,62,系统触发器,系统触发器在发生诸如数据库启动或关闭等系统事件时激发，而不是在执行DML语句时激发。 系统触发器也可以在DDL操作时，如表的创建时被激发。,63,创建触发器,CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER | INSTEAD OF triggering_event referencing_clause WHEN trigger_condition FOR EACH ROW trigger_body;,64,创建DML触发器,65,DML触发器例子,Create or replace trigger modi_emp before insert or update or delete on emp Begin if (to_char(sysdate,HH24)not between 08and 17) or (to_char(sysdate,dy,nls_date_language=american) in (sat，sun) then raise_application_error(-20000,费工作时间不能修改职工信息)； end if; End;,66,DML触发器激发顺序,如果存在语句之前的触发器，先执行该触发器。 对于受语句影响每一行： 如果存在行之前的触发器，执行该触发器。 执行该语句本身。 如果存在行之后的触发器，执行该触发器。 如果存在语句之后的触发器，运行该触发器。,67,行级别触发器的相关标识符,触发语句处理每一行时，行级别触发器都激发一次。 在触发器内部，可以通过相关标识符:old和:new访问正在处理中的行的数据。 标识符:old和:new也被称为伪记录。,68,: old和:new相关标识符,注意:old标识符对INSERT语句未定义，而:new标识符对DELETE语句未定义。PL/SQL编译器不会对在INSERT语句中使用的:old和在DELETE语句中使用的:new标识符报错，编译的结果将使这两者的字段值为NULL。,69,触发器例子,Create or replace trigger score_change after update of score on grade For each row Begin insert into table_score_change values(:old.sno,:o,:old.score,:new.score,sysdate); End;,70,WHEN子句,WHEN子句只适用于行级别触发器。 如果使用该子句，触发器体将只对满足由WHEN子句指定条件的行而执行。,语法：WHEN trigger_condition,71,触发器谓词,72,创建instead-of触发器,instead-of触发器用于以下两种情况： 允许修改一个本来无法修改的视图。 修改视图中嵌套表列的列。,73,可更改的与不可更改的视图,可更改视图：可以发出DML命令的视图。 一般来说，视图如果不包括下列命令中的任何一项，它就是一个可更改视图 。 1、集合操作符（UNION、UNION ALL、MINUS） 2、聚集函数（SUM、AVG等） 3、GROUP BY、CONNECT BY或START WITH子句 4、DISTINCT操作符 5、连接,74,instead-of触发器,如果一个视图是不可更改的，则可以在其上编写一个instead-of触发器来执行正确的操作，从而使该视图可更改。 如果需要进行其他处理的话，也可以在可更改视图上编写instead-of触发器。,75,创建系统触发器,系统触发器可以在两种不同种类的事件（即DDL或数据库）上激发。 DDL事件包括CREATE、ALTER或DROP语句。 而数据库事件包括服务器的启动或关闭，用户的登录或退出，以及服务器错误。,语法： CREATE OR REPLACE TRIGGER schema. trigger_name BEFORE | AFTER ddl_event_list| database_event_list ON DATABASE | schema.SCHEMA when_clause trigger_body;,76,系统DLL和数据库事件,77,其他触发器问题,触发器名称的命名空间（Name-space） 使用触发器的各种限制和不同种类的触发器体。 与触发器有关的权限问题,78,触发器名称,命名空间：是一组合法的可供对象作为名字使用的标识符。过程、包和表都共享同一个命名空间，在一个数据库模式范围内，同一命名空间内的所有的对象必须具有唯一的名称。 触发器隶属于一个独立的命名空间。也就是说，触发器可以有与表和过程相同的名称。然而，在一个模式范围内，给定的名称只能用于一个触发器。,79,对触发器的限制,触发器不能发出任何事务控制语句，如COMMIT、ROLLBACK、SAVEPOINT或SET TRANSACTION。 由触发器体调用的任何过程或函数都不能发出任何事务控制语句（除非在Oracle8i及更高版本中把它们声明为自治的）。 触发器体不能声明任何LONG或LONG RAW变量。而且，:new和:old也不能指向为之定义的表的LONG或LONG RAW类型的列。 在Oracle8及更高版本中，触发器体中的代码可以引用和使用LOB（大型对象）列，但不能修改该列的值。这个限制也适用于对象列。,80,触发器权限,81,本章小结,数据库触发器是根据一个事件执行的PL/SQL语句块。数据库触发器有三种类型：DML触发器、instead-of触发器和系统触发器。 CREATE TRIGGER语句包括执行时间选项（之前或之后），并且可以标识为行级别和语句级别。 行级别触发器对于DML事件影响的每一行激发。语句级别触发器只对DML事件触发一次，而不管DML影响了多少行。 CREATE TRIGGER语句包括一个可选的WHEN子句，它可以检查一个条件，决定是否应该执行触发器。 相关性标识符:OLD和:NEW允许引用DML操作的原始行值和新行值。 触发器谓词INSERTING、UPDATING和DELETING允许在一个触发器中对于不同事件执行不同的PL/SQL代码。,82,本章小结,Instead-of触发器基于一个视图，并允许通过视图执行DML操作。 系统触发器是在DDL语句和系统事件上触发的。 如果DML语句触发了一个触发器，并且这个触发器尝试引用该DML事件所处理的那个表，则将引发一个错误。 ALTER TRIGGER语句允许编译器禁用或启用触发器。 想要从系统中删除一个触发器，可以使用DROP TRIGGER语句。 USER_TRIGGERS数据字典视图包含了所有触发器声明信息以及源代码。,