《第7章 存储过程和触发器.ppt》由会员分享，可在线阅读，更多相关《第7章 存储过程和触发器.ppt（38页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第第7章章 存储过程和触发器存储过程和触发器01 存储过程存储过程02 触发器触发器03 事务事务04 锁锁1 存储过程存储过程主要内容 存储过程的创建和执行存储过程的创建和执行 存储过程的修改存储过程的修改0102 存储过程的删除存储过程的删除03存储过程的创建和执行1 1创建存储过程创建存储过程 创建存储过程的语句是创建存储过程的语句是CREATE PROCEDURECREATE PROCEDURE，语法格式：，语法格式：create or replace procedure pro_name(parameter1,parameter2)is|asbegin plsql_sentences
2、;exception dowith _ sentences;end pro_name;SQL create procedure pro_insertDept is begin insert into dept values(77,市场拓展部,JILIN);-插入数据记录 commit;-提交数据 dbms_output.put_line(插入新记录成功！);-提示插入记录成功 end pro_insertDept;/创建一个存储过程pro_insertDept，该存储过程实现向dept表中插入一条记录。试一试试一试试一试试一试存储过程的创建和执行存储过程的创建和执行2 2调用存储过程调用存储过
3、程 调用存储过程一般使用EXECUTEEXECUTE语句语句，但在PL/SQL块中可以直接使用存储过程的名称直接使用存储过程的名称来调用。语法格式：exec|execute procedure_nameSQL execute pro_insertDept;使用execute命令执行pro_insertDept存储过程试一试试一试试一试试一试存储过程的修改 修改存储过程和修改视图一样，虽然也有ALTER PROCEDURE语句，但是它是用于重新编译或验证现有过程的。如果要修改过程定义，仍然使用CREATE OR REPLACE PROCEDURECREATE OR REPLACE PROCEDU
4、RE命令命令，语法格式一样。其实，修改已有过程本质就是使用CREATE OR REPLEACE PROCEDURE重新创建一个新的过程，保持名字和原来的相同。存储过程的删除 当一个过程不再需要时，要将此过程从内存中删除，以释放相应的内存空间，可以使用下面的语句：DROP PROCEDURE count_num;2 触发器触发器主要内容 利用利用SQLSQL语句创建触发器语句创建触发器01 利用界面方式创建触发器利用界面方式创建触发器02 启用和禁用触发器启用和禁用触发器03 触发器的删除触发器的删除04利用SQL语句创建触发器create or replace trigger tri_name
5、 before|after|instead of tri_event on table_name|view_name|user_name|db_name for each row when tri_conditionbeginplsql_sentences;end tri_name;创建触发器的语法：利用SQL语句创建触发器触发器分类触发器分类语句级触发器替换触发器系统事件触发器1 1创建语句级触发器创建语句级触发器 语句级触发器，顾名思义，就是针对一条语句级触发器，顾名思义，就是针对一条DMLDML语句而引起的触发器执行。在语句级触发语句而引起的触发器执行。在语句级触发器中，不使用器中，不使
6、用for each rowfor each row子句，也就是说无论数据操作影响多少行，触发器都只会执行子句，也就是说无论数据操作影响多少行，触发器都只会执行一次。一次。利用SQL语句创建触发器创建一个触发器tri_dept，该触发器在insert、update和delete事件下都可以被触发，并且操作的数据对象是dept表。然后要求在触发器执行时输出对dept表所做的具体操作。试一试试一试试一试试一试2 2创建替换触发器创建替换触发器 替换触发器替换触发器即即instead ofinstead of触发器，它的触发器，它的“触发时机触发时机”关键字是关键字是instead ofinstead
7、 of，而不，而不是是beforebefore或或afterafter。与其它类型触发器不同是，。与其它类型触发器不同是，替换触发器定义在视图替换触发器定义在视图上的，而不是定义在上的，而不是定义在表上。由于视图是由多个基表连接组成的逻辑结构，所以一般不允许用户进行表上。由于视图是由多个基表连接组成的逻辑结构，所以一般不允许用户进行DMLDML操作（如操作（如insertinsert、updateupdate、deletedelete等操作），这样当用户为视图编写等操作），这样当用户为视图编写“替换触发器替换触发器”后，用户对视后，用户对视图的图的DMLDML操作实际上就变成了执行触发器中的操
8、作实际上就变成了执行触发器中的PL/SQLPL/SQL语句块，这样就可以通过在语句块，这样就可以通过在“替换触发替换触发器器”中编写适当的代码对构成视图的各个基表进行操作。中编写适当的代码对构成视图的各个基表进行操作。利用SQL语句创建触发器创建一个关于view_emp_dept视图的替换触发器，在该触发器的主体中实现向emp表和dept表中插入两行相互关联的数据。试一试试一试试一试试一试3 3系统事件触发器系统事件触发器 从从Oracle 8iOracle 8i开始，开始，OracleOracle提供的系统触发器可以在提供的系统触发器可以在DDLDDL或数据库系统上被触发。或数据库系统上被触
9、发。DDLDDL指的是数据定义语句，如指的是数据定义语句，如CREATECREATE、ALTERALTER和和DROPDROP等。而数据库系统事件包括数据库服务器的等。而数据库系统事件包括数据库服务器的启动（启动（STARTUPSTARTUP）或关闭（）或关闭（SHUTDOWNSHUTDOWN），数据库服务器出错（），数据库服务器出错（SERVERERRORSERVERERROR）等。）等。语法格式：语法格式：CREATE OR REPLACE TRIGGER scache.trigger_name BEFORE|AFTER ddl_event_list|databse_event_list
10、ON DATABASE|schema.SCHEMA when_clause tigger_body利用SQL语句创建触发器利用界面方式创建触发器1 1利用利用OEMOEM创建触发器创建触发器 2 2利用利用SQL DeveloperSQL Developer创建触发器创建触发器 OracleOracle提供了提供了ALTER TRIGGERALTER TRIGGER语句用于启用和禁用触发器，语法格式：语句用于启用和禁用触发器，语法格式：ALTER TRIGGER schema.trigger_name DISABLE|ENABLE;启用和禁用触发器当一个触发器不再使用时，要从内存中删除它。语法
11、：当一个触发器不再使用时，要从内存中删除它。语法：删除：删除：DROP TRIGGER my_trigger;当一个触发器已经过时，想重新定义时，不必先删除再创建，同样只需在当一个触发器已经过时，想重新定义时，不必先删除再创建，同样只需在CREATECREATE语句后面语句后面加上加上OR REPLACEOR REPLACE关键字即可。如：关键字即可。如：重新定义：重新定义：CREATE OR REPLACE TRIGGER my_trigger;触发器的删除3 事务事务主要内容 事务的概念事务的概念 事务处理事务处理 自治事务自治事务010203事务的概念1 1事务的特性事务的特性（1 1）
12、原子性（）原子性（AtomicAtomic）（2 2）一致性（）一致性（ConDemoltentConDemoltent）（3 3）隔离性（）隔离性（IsolatedIsolated）（4 4）持久性（）持久性（DurabilityDurability）2 2事务的状态事务的状态事务的概念事务处理1 1提交事务（提交事务（commitcommit语句）语句）提交事务是指把对数据库进行的全部操作持久性地保存到数据库中，这种操作通常使用提交事务是指把对数据库进行的全部操作持久性地保存到数据库中，这种操作通常使用COMMITCOMMIT语句来完成。语句来完成。事务的提交方式包括如下事务的提交方式包括
13、如下3 3种：种：显式提交显式提交：使用：使用commitcommit命令使当前事务生效。命令使当前事务生效。自动提交自动提交：在：在SQL*PlusSQL*Plus里执行里执行“set autocommit on;set autocommit on;”命令。命令。隐式提交隐式提交：除了显式提交之外的提交，如发出：除了显式提交之外的提交，如发出DDLDDL命令、程序中止和关闭数据库等。命令、程序中止和关闭数据库等。2 2回滚事务（回滚事务（rollbackrollback语句）语句）回退事务是指撤销对数据库进行的全部操作，Oracle利用回退段来存储修改前的数据，通过重做日志来记录对数据所做的
14、修改。如果要回退整个事务，Oracle系统内部将会执行如下操作过程。（1）首先使用回退段中的数据撤销对数据库所做的修改。（2）然后Oracle后台服务进程释放掉事务所使用的系统资源。（3）最后显示通知，告诉用户事务回退成功。事务处理3 3设置回退点设置回退点回退点又称为保存点，即指在含有较多SQL语句的事务中间设定的回滚标记，其作用类似于调试程序的中断点。利用保存点可以将事务划分成若干小部分，这样就不必回滚整个事务，可以回滚到指定的保存点，有更大的灵活性。回滚到指定保存点将完成如下主要工作。回滚保存点之后的部分事务。删除在该保存点之后建立的全部保存点，该保存点保留，以便多次回避。解除保存点之后
15、表的封锁或行的封锁。事务处理事务处理使用保存点（savepoint）来回滚记录。试一试试一试试一试试一试4 锁锁主要内容 锁机制和死锁锁机制和死锁 锁的类型锁的类型 表锁和事务锁表锁和事务锁010203锁机制和死锁 在Oracle中，提供了两种锁机制。（1）共享锁共享锁（share lock）。共享锁通过数据存取的高并行性来实现。如果获得了一个共享锁，那么用户就可以共享相同的资源。许多事务可以获得相同资源上的共享锁。例如，多个用户可以在相同的时间读取相同的数据。（2）独占锁独占锁（exclusive lock）。独占锁防止共同改变相同的资源。假如一个事务获得了某一资源上的一个专用锁，那么直到该
16、锁被解锁，其它的事务才能修改该资源，但允许对资源进行共享。例如，假如一个表被锁定在独占模式下，它并组织其它用户从同一个表得到数据。所有的锁在事务期间被保持，事务中的SQL语句所做的修改只有在事务提交时才能对其他事务可用。Oracle在事务提交和回滚事务时，释放事务所使用的锁。1 1锁机制锁机制 当两个或者多个用户等待其中一个被锁住的资源时，就有可能发生死锁现象。对于死锁，Oracle自动进行定期搜索，通过回滚死锁中包含的其中一个语句来解决死锁问题，也就是释放其中一个冲突锁，同时返回一个消息给对应的事务。用户在设计应用程序时，要遵循一定的锁规则，尽力避免死锁现象的发生。2 2死锁死锁锁机制和死锁
17、锁的类型 DML锁 DDL锁 内部锁表锁和事务锁 为了使事务能够保护表中的DML存取以及防止表中产生冲突的DDL操作，Oracle获得表锁（TM）。例如，假如某个事务在一张表上持有一个表锁，那么它会阻止任何其他事务获取该表中用于删除或改变该表的一个专用DDL锁。下表列出了不同的模式。当执行特定的语句时，由RDBMS获得这些模式的表锁。通过V$lock动态表可以查看锁的相关信息。模式列的值分别为2、3或6。数值2表示一个行共享锁（RS）；数值3表示一个行独占锁（RX）；数值6表示一个独占锁（X）。语句语句类型类型模式模式INSERTTM行独占（3）（RX）UPDATETM行独占（3）（RX）DE
18、LETETM行独占（3）（RS）SELECT FOR UPDATETM行共享（2）（RS）LOCK TABLETM独占（6）（X）当一个事务发出如下表所列出的语句时，将获得事务锁（TX）。事务锁总是在行级上火的。事务锁独占地锁住该行，并阻止其他事务修改行，知道持有该锁的事务回滚或提交数据为止。表锁和事务锁语句语句类型类型模式模式INSERTTX独占（6）（X）UPDATETX独占（6）（X）DELETETX独占（6）（X）SELECT FOR UPDATETX独占（6）（X）小结小结 通过本章的学习，能够对通过本章的学习，能够对PL/SQLPL/SQL的编程有进一步的了解，过程和触发器都是的编
19、程有进一步的了解，过程和触发器都是命名的命名的PL/SQLPL/SQL块，这些程序块可以被保存在块，这些程序块可以被保存在OracleOracle数据库中，以便用户随时调用数据库中，以便用户随时调用和维护。另外也对数据库控制有了一个深入的了解。在事务中了解如何提交事务，和维护。另外也对数据库控制有了一个深入的了解。在事务中了解如何提交事务，回滚事务，还有如何设置回滚点。了解加锁的原因和一些方法。回滚事务，还有如何设置回滚点。了解加锁的原因和一些方法。上机指导将3个不同功能的替代触发器变为一个替代触发器。除了可以根据不同的DML分开编写替代触发器之外，也可以将所有功能集中在一个替代触发器中进行编
20、写，此时依然可以使用触发器提供的3个谓词INSERTING、UPDATINGH和DELETING进行操作的判断。CREATE OR REPLACE TRIGGER view_triggerINSTEAD OF INSERT OR UPDATE OR DELETE ON v_myviewFOR EACH ROWDECLAREv_empCountNUMBER;v_deptCountNUMBER;BEGINIF INSERTING THEN-判断要增加的雇员是否存在SELECT COUNT(empno)INTO v_empCount FROM emp WHERE empno=:new.empno;-
21、判断要增加的部门是否存在SELECT COUNT(deptno)INTO v_deptCount FROM dept WHERE deptno=:new.deptno;IF v_deptCount=0 THEN-部门不存在INSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);上机指导END IF;IF v_empCount=0 THENINSERT INTO emp(empno,ename,job,sal,deptno)VALUES(:new.empno,:new.ename,:new.job,:ne
22、w.sal,:new.deptno);END IF;ELSIF UPDATING THENUPDATE emp SET ename=:new.empno,job=:new.job,sal=:new.sal WHERE empno=:new.empno;UPDATE dept SET dname=:new.dname,loc=:new.loc WHERE deptno=:new.deptno;ELSIF DELETING THENDELETE FROM emp WHERE empno=:old.empno;SELECT COUNT(empno)INTO v_empCount FROM emp WHERE deptno=:old.deptno;IF v_empCount=0 THEN-此部门没有雇员DELETE FROM dept WHERE deptno=:old.deptno;END IF;ELSENULL;END IF;END;/