第二部分 存储过程、触发器、包体、物化视图2.1存储过程Ø 存储过程功能说明存储过程是一种命名的PL/SQL程序块，它可以接受零个或多个参数作为输入、输出，或者既作输入又作输出的参数。过程被存储在数据库中，并且存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUTE命令或PL/SQL程序块内部调用。由于存储过程是已经编译好的代码，所以在调用的时候不必再次进行编译，从而提高了程序的运行效率。Ø 存储的基本结构主要包括三部分：声明、执行部分、异常语法：create procedure procedure_name[(parameter[,parameter,...])]—传参数 is[local declarations]—声明部分begin –执行部分 execute statements[exception—异常处理部分end [procedure _name]2.2触发器触发器是关系数据库系统提供的一项技术，触发器类似过程和函数，它们都包括声明部分，执行逻辑处理部分和异常处理部分，并且都被存储在数据库中。Ø 触发器分类：1） 语句级触发器如果在创建触发器时未使用FOR EACH ROW子句，则创建的触发器为语句级触发器。语句级触发器在被触发后只执行一次，而不管这一操作会影响到数据库中多少行记录。举例：创建触发器不允许在休息日操作员工信息CREATE OR REPLACE TRIGGER TR_SEC_EMP BEFORE INSERT OR UPDATE OR DELETE ON EMPBEGIN IFTO_CHAR(SYSDATE, 'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN RAISE_APPLICATION_ERROR(-20000, '不能在休息日修改雇员信息'); ENDIF;END; 2） 行级触发器在创建触发器时，如果使用了FOR EACH ROW选项，则创建的该触发器为行级触发器。对于行级触发器而言，当一个DML语句操作影响到数据库中的多行数据时，行级触发器会针对于每一行执行一次。举例1：创建对emp的薪资只允许上调的操作CREATE OR REPLACE TRIGGER TR_EMP_SAL BEFORE UPDATE OF SAL ON EMP FOREACH ROW BEGIN IF:NEW.SAL < :OLD.SAL THEN RAISE_APPLICATION_ERROR(-20000, '工资只涨不降'); ENDIF; END;[color=teal][font="]/*[color=teal]实例：[color=teal][color=teal][font="]update emp set sal=6400 whereempno=7369;[color=teal][font="]*/举例2：利用行触发器实现级联更新。在修改了主表regions中的region_id之后（AFTER），级联的、自动的更新子表countries表中原来在该地区的国家的region_id。环境搭建：--创建表create table regions (region_idnumber(5),name varchar2(100));create table countries（region_idnumber(5)，country_name varchar2(100));--添加表的约束条件alter table regions add constraintpk_region_id primary key(region_id);alter table countries add constraintpk_region_id2 primary key(region_id);alter table regions add constraint REGIONS_FK_REGION_ID foreign key (region_id) references countries (region_id);--创建触发器CREATE OR REPLACE TRIGGER tr_reg_couAFTER update OF region_idON regionsFOR EACH ROWBEGIN DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id ||'、新的region_id值是'||:new.region_id); UPDATE countries SET region_id = :new.region_id WHERE region_id = :old.region_id; END;--测试UPDATE regions T SET T.REGION_ID=60 WHERET.REGION_ID=10;[color=teal][font="] 3） INSTEAD OF 触发器只能对视图建立[font=Helvetica, sans-serif]instead of触发器，不能对表、模式和数据库建立[font=Helvetica, sans-serif]instead of触发器。举例：[font="] [font=Helvetica, sans-serif]INSTEAD_OF 用于对视图的[font=Helvetica, sans-serif]DML触发，由于视图有可能是由多个表进行联结[font=Helvetica, sans-serif](join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况[font=Helvetica, sans-serif]:[font=Helvetica, sans-serif]CREATE OR REPLACE VIEW emp_view AS [font=Helvetica, sans-serif]SELECT deptno, count(*) total_employeer, sum(sal) total_salary [font=Helvetica, sans-serif]FROM emp GROUP BY deptno;在此视图中直接删除是非法：[font=Helvetica, sans-serif]SQL>DELETE FROM emp_view WHERE deptno=10;DELETE FROM emp_view WHERE deptno=10[font=Helvetica, sans-serif]ERROR 位于第[font=Helvetica, sans-serif] 1 行[font=Helvetica, sans-serif]:ORA-01732: 此视图的数据操纵操作非法但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：CREATE OR REPLACE TRIGGER emp_view_delete INSTEAD OF DELETE ON emp_view FOR EACH ROWBEGIN DELETE FROM emp WHERE deptno= :old.deptno; END emp_view_delete; 测试：DELETE FROM emp_view WHERE deptno=10; DROP TRIGGER emp_view_delete; DROP VIEW emp_view; 4） 基于系统和用户事件触发器用户事件触发器是建立在模式级的操作上的触发器。激活该类型触发器的用户事件包括：CREATE、ALTER、DROP、ANALYZE、ASSOCIATE STATISTICS、DISASSOCIATE 、STATISTICS、COMMENT、GRANT、REVOKE、RENAME、TRUNCATE、LOGOFF、SUSPEND和LOGON 下面给出系统触发器的种类和事件出现的时机（前或后）：
[td=123] 事件 [td=123]允许的时机 [td=356]说明
[td=123]STARTUP [td=123][align=right]AFTER [td=356][align=right]启动数据库实例之后触发
[td=123]SHUTDOWN [td=123][align=right]BEFORE [td=356][align=right]关闭数据库实例之前触发（非正常关闭不触发）
[td=123]SERVERERROR [td=123][align=right]AFTER [td=356][align=right]数据库服务器发生错误之后触发
[td=123]LOGON [td=123][align=right]AFTER [td=356][align=right]成功登录连接到数据库后触发
[td=123]LOGOFF [td=123][align=right]BEFORE [td=356][align=right]开始断开数据库连接之前触发
[td=123]CREATE [td=123][align=right]BEFORE，AFTER [td=356][align=right]在执行CREATE语句创建数据库对象之前、之后触发
[td=123]DROP [td=123][align=right]BEFORE，AFTER [td=356][align=right]在执行DROP语句删除数据库对象之前、之后触发
[td=123]ALTER [td=123][align=right]BEFORE，AFTER [td=356][align=right]在执行ALTER语句更新数据库对象之前、之后触发
[td=123]DDL [td=123][align=right]BEFORE，AFTER [td=356][align=right]在执行大多数DDL语句之前、之后触发
[td=123]GRANT [td=123][align=right]BEFORE，AFTER [td=356][align=right]执行GRANT语句授予权限之前、之后触发
[td=123]REVOKE [td=123][align=right]BEFORE，AFTER [td=356][align=right]执行REVOKE语句收权限之前、之后触犯发
[td=123]RENAME [td=123][align=right]BEFORE，AFTER [td=356][align=right]执行RENAME语句更改数据库对象名称之前、之后触犯发
[td=123]AUDIT / NOAUDIT [td=123][align=right]AUDIT / NOAUDIT [td=356][align=right]AUDIT / NOAUDIT
举例：CREATE TABLE log_event (user_name VARCHAR2(10), address VARCHAR2(20), logon_date timestamp, logoff_date timestamp; --创建登录触发器
CREATE OR REPLACE TRIGGER tr_logonAFTER LOGON ON DATABASEBEGIN INSERT INTO log_event (user_name, address, logon_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp); END tr_logon; --创建退出触发器
CREATE OR REPLACE TRIGGER tr_logoffBEFORE LOGOFF ON DATABASEBEGIN INSERT INTO log_event (user_name, address, logoff_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp); END tr_logoff; 小结：FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。2.3包体程序包其实就是被组合在一起的相关对象的集合，当程序包中任何函数或存储过程被调用时，程序包就被加载入到内存中，这样程序包中的任何函数或存储过程的子程序访问速度将大大加快。例如，在PL/SQL程序中，为了输出运行结果，在程序的代码中使用了DBMS_OUTPT.PUT_LINE语句。事实上，这是调用程序包DBMS_OUTPUT中的PUT_LINE过程。DBMS_OUTPUT程序包的主要功能就是在PL/SQL程序中的输入和输出。[font=Verdana, sans-serif] 创建程序包规范的语法形式如下：create [or replace] package package_name is [public_variable_declarations…] [public_type_declarations…] [public_exception_declarations…] [public_cursor_declarations…] [function_declarations…] [procedure_specifications…]end [package_name] 实例： (1)创建一个包package，声明该包有一个过程、有一个函数
create package sp_package isprocedure update_sal(name varchar2,newsalnumber);function annual_income(name varchar2) returnnumber;end sp_package;[i]包的规范只包含过程和函数的声明，但是没有过程和函数实现代码，包体用于实现包规范的过程和函数(2)建立包体create package body命令create package body sp_package isprocedure update_sal(name varchar2,newsalnumber) isbeginupdate emp set sal=newsal where ename=name;end update_sal; function annual_income(name varchar2) returnnumber is annual_salary number; beginselect sal*12+nvl(comm,0) into annual_salaryfrom emp where ename= name; return annual_salary; end annual_income;end sp_package;如何调用包的过程或是函数
包名+函数名或是过程名exec sp_package.update_sal('SCOTT',2000);2.4物化视图ORACLE物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。 当数据库中表的数据量比较大，而又需要对这些大表进行关联查询，不管语句如何调整，查询的速度总是不尽人意，使用物化视图，速度可以提高n倍。1、物化视图的类型：ON DEMAND、ON COMMIT--刷新方式 on commit 视图数据基于基表提交时更新 on demand 根据自己需要刷新数据（可以根据刷新包手动刷新） 物化视图的数据怎么随着基表而更新？ 1、 创建定时刷新的物化视图(指定物化视图每天刷新一次)： SQL>create materialized view mv_name refresh force on demand start with sysdatenext sysdate+1;上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间（比如每天晚上10：00定时刷新一次）： SQL>create materialized view mv_name refresh force on demand start with sysdatenext to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyyhh24:mi:ss'); 2、 dbms_mview安排自动刷新时间(手动刷新) SQL>executedbms_mview.refresh(‘EMP_BY_DISTRICT’); －－刷新指定的物化视图SQL>executedbms_mview.refresh_defresh_dependent(‘EMPLOYEE’); ――刷新利用了该表的所有物化视图SQL>executedbms_mview.refresh_all_mviews; ――刷新该模式中，自上次刷新以来，未得到刷新的所有物化视图。 3、ON COMMIT物化视图 ON COMMIT物化视图的创建，和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的，所以ON COMMIT物化视图，需要再增加个参数即可。 需要注意的是，无法在定义时仅指定ON COMMIT，还得附带个参数才行。 创建ON COMMIT物化视图：creatematerialized view mv_name refresh force on commit as select * fromtable_name ; 备注：实际创建过程中，基表需要有主键约束，否则会报错（ORA-12014 ）优点： 1、物化视图的最大的优势是可以提高性能：Oracle的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。 2、 物化视图有很多方面和索引很相似 3、通过预先计算好答案存储起来，可以大大地减少机器的负载 A、更少的物理读--扫描更少的数据 B、更少的写--不用经常排序和聚集 C、减少CPU的消耗--不用对数据进行聚集计算和函数调用 D、显著加快响应时间，在使用物化视图查询数据时(与主表相反)，将会很快的返回查询结果 缺点： 1、物化视图用于只读或者“精读”环境下工作最好 ，不用于联机事务处理系统(OLTP)环境，　在事实表等更新时会导致物化视图行锁，从而影响系统并发性。 2、物化视图有出现无法快速刷新，导致查询数据不准确的现象 3、Rowid物化视图（创建的物化视图通常情况下有主键，rowid,和子查询视图）只有一个单一的主表，不能包括下面任何一项: A，Distinct 或者聚合函数. B，Group by，子查询，连接和SET操作 4、物化视图会增加对磁盘资源的需求，即需要永久分配的硬盘空间给物化视图来存储数据 [font="] 5、物化视图的工作原理受一些可能的约束