Oracle存储过程和触发器基本操作解析

合集下载

oracle 触发器工作原理

oracle触发器工作原理Oracle数据库中的触发器是一种存储过程，它在特定的数据库操作（如INSERT、UPDATE或DELETE）发生时自动执行。

触发器可以用于实现数据一致性、审计、业务规则验证和复杂的数据处理逻辑。

以下是Oracle触发器工作原理的基本概述：1.定义与激活：在Oracle中，通过使用CREATE TRIGGER语句创建触发器，指定其名称、触发时机（BEFORE或AFTER）、触发事件（INSERT、UPDATE、DELETE或COMMIT等）以及作用的对象（表或视图）。

2.触发时机：BEFORE触发器会在实际操作之前执行，此时可以查看并修改将要插入、更新或删除的数据。

AFTER触发器则在实际操作完成之后执行，此时只能查看已经更改后的结果。

3.触发上下文：对于INSERT操作，触发器可以通过:NEW伪记录访问被插入的新行数据。

对于UPDATE操作，触发器同时可以获得:OLD和:NEW伪记录，分别代表更新前的老数据和更新后的新数据。

对于DELETE操作，触发器可以通过:OLD伪记录访问即将被删除的行数据。

4.执行逻辑：触发器内的PL/SQL代码会根据触发条件进行执行，可以包含任何合法的PL/SQL命令，包括对其他表的操作、控制流语句、异常处理等。

5.事务处理：触发器是事务的一部分，所以它们遵循ACID属性，并且其行为受当前事务的影响。

例如，如果事务回滚，则触发器所做的所有变更也会随之回滚。

6.实例应用：举例来说，一个AFTER INSERT触发器可能用来记录新插入数据到审计表中；而一个BEFORE UPDATE触发器可能用于检查更新的数据是否满足某些业务规则，如果不满足则阻止更新操作。

总之，Oracle触发器是数据库系统内嵌的一种自动化机制，它在特定数据库事件发生时自动执行预定义的逻辑，为确保数据完整性和业务规则得以强制执行提供了强大的支持。

Oracle触发器trigger详解

Oracle触发器trigger详解触发器相关概念及语法，供⼤家参考，具体内容如下概述本篇博⽂中主要探讨以下内容：什么是触发器触发器的应⽤场景触发器的语法触发器的类型案例数据：触发器的概念和第⼀个触发器数据库触发器是⼀个与表相关联的，存储的PL/SQL 语句。

每当⼀个特定的数据操作语句（insert update delete）在指定的表上发出时，Oracle⾃动执⾏触发器中定义的语句序列。

举个简单的例⼦：当员⼯表中新增⼀条记录后，⾃动打印“成功插⼊新员⼯”create or replace trigger insertStaffHintafter insert on xgj_testfor each rowdeclare-- local variables herebegindbms_output.put_line('新增员⼯成功');end insertStaffHint;触发器的应⽤场景复杂的安全性检查数据的确认数据库审计数据的备份和审计触发器的语法CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER }{INSERT | DELETE | UPDATE [OF column [, column …]]}[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]ON [schema.]table_name | [schema.]view_name[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}][FOR EACH ROW ][WHEN condition]PL/SQL_BLOCK | CALL procedure_name;其中：BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发⽅式，前触发是在执⾏触发事件之前触发当前所创建的触发器，后触发是在执⾏触发事件之后触发当前所创建的触发器。

Oracle触发器操作

AS
SELECT * FROM Users
WHERE u_Name='amy';
教
（2）创建行级触发器
师
CREATE OR REPLACE TRIGGER tr_DeleteUser2 AFTER DELETE
2020/10/15 Or acle 数据库管理与应用实例教程
课堂案例2—使用PL/SQL管理触发器
案例完成步骤-创建触发器
添加标【例2-4】通过临时表将Users表中删除的记录进行临时保存（行级触发器）。
（1）创建行级触发器的测试表 CREATE TABLE userdel
题文字
建议课时：8课时
2020/10/15 Or acle 数据库管理与应用实例教程
9.1 触发器概述
触发器简介触发器是一种特殊的存储过程，它与数据表紧密联系，用于保护表中的数据，当一个定义了特定类型触发器的基表执行插入、修改或删除表中数据的操作时，将自动触发触发器中定义的操作，以实现数据的一致性和完整性。触发器拥有比数据库本身标准的功能更精细和更复杂的数据控制能力。触发器具有以下的作用：（1）在安全性方面，触发器可以基于数据库的值使用户具有操作数据库的某种权利。（2）在审计方面，触发器可以跟踪用户对数据库的操作。（3）实现复杂的数据完整性规则。（4）实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。（5）同步实时地复制表中的数据。（6）自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果商品的数量低于5，则立即给管理人员发送库存报警信息。
教
WHO VARCHAR2(30),
师

触发器与存储过程

触发器与存储过程触发器（Trigger）是数据库中的一种特殊对象，它与一些特定的数据库事件相关联，并且当这个事件发生时，触发器可以在自动执行的过程中被触发。

而存储过程（Stored Procedure）则是一段预先编译好的SQL 语句的集合，它可以被保存在数据库服务器端，通过调用来执行。

触发器和存储过程都是数据库中的重要组件，它们都可以用于实现数据的自动化处理和一些复杂的业务逻辑。

但是它们在功能和使用方法上有一些不同之处。

首先，触发器的触发条件是事先设置好的，当该条件满足时才会被触发执行，而存储过程是主动调用执行的。

触发器通常与数据库中的表相关联，并且在表上的插入、更新或删除等事件发生时触发。

存储过程可以在任何时候被调用执行，无论是否有其他数据库事件发生。

其次，触发器通常用于实现数据的自动化处理，比如在插入新纪录时通过触发器自动计算一些字段的值，或者在删除记录时触发器做一些相关操作。

而存储过程则更倾向于实现业务逻辑的封装和复用，比如在一个库存管理系统中，可以使用存储过程来实现添加商品、修改商品信息、删除商品等操作。

此外，触发器由数据库引擎直接管理，它是与数据库表密切相关的一种对象，所以当表被删除或者修改时，相关联的触发器也会相应地被删除或修改。

而存储过程则是作为独立于表的对象存在，当数据库表被删除或修改时，存储过程不受影响。

在使用上，触发器和存储过程都可以用于实现一些相同的功能，但触发器更适合于在特定的数据库事件发生时执行自动化的操作，而存储过程更适合于实现复杂的业务逻辑和一些需要主动调用的场景。

总之，触发器和存储过程都是数据库中的重要组件，它们可以用于实现一些自动化的处理和复杂的业务逻辑。

它们在功能和使用方法上有一些不同之处，需要根据具体的需求来选择和使用。

触发器和存储过程

应用1 应用2 应用N
DBMS
OS
DB
由于要设计自己的多任务处理机制和调度算法，使得N+1方案的DBMS设计在整体上比2N方案要复杂。
Server要处理所有用户的申请，如果调度策略不当或
不能使数据库进程获得较高的优先权，这种方案将导致瓶颈。另外，N+1方案中要用到操作系统级的消息机制以实现多进程向单进程以及单进程向多进程的数据传
针对强制关联关系，建立触发器。
注意引用完整性
数据录入、修改
数据查询、报表
View
存储过程
T_student
T_teacher
T_Course
T_grade
触发器
三、 DBMS的基本功能和系统结构
1、DBMS的基本功能
◆ 数据库定义
对数据库的结构进行描述，包括外模式、模式、内模式的定义；数据库完整性的定义；安全保密定义 (如用户口令、级别、存取权限)；存取路径 (如索引)
2、设计有效索引注意：如果一项查询中，需访问表中多于20%的行，则使用表扫描比使用索引效率高。使用索引的指导方针：
● 适量使用索引 ● 不要索引较小的表（比如只有几百行） ● 尽可能使用较少的索引键列
● 尽可能使用覆盖查询（即查询内容包含于索引列
中，则只有索引被访问，表本身被“绕”过）
数据库设计中需要深入考虑的问题
的结果。
存储过程潜在的缺点如下：
1、难以保持负荷平衡。
存储过程提出了一种在服务器上集中处理的模型，
用集中处理代替事务的分布处理。这种方法，除非服务器的性能进行了优化，符合存储过程运行的需求，否则它会降低服务器的性能。 2、增加了管理要求。
存储过程是一个共享的资源。管理员必须确保使

oracle 触发器case 写法

Oracle触发器是一种特殊类型的存储过程，它可以在特定的数据库操作发生时自动执行。

在实际应用中，我们经常会遇到需要使用Oracle触发器来实现一些复杂的业务逻辑。

在使用Oracle触发器时，我们需要根据具体的业务需求来设计触发器的逻辑，而针对不同的场景，需要使用不同的触发器写法。

本文将针对Oracle触发器的不同应用场景，介绍其对应的写法和注意事项。

一、触发器简介1.1 触发器概述Oracle触发器是一种特殊的数据库对象，它会在特定的数据库操作（如INSERT、UPDATE、DELETE）发生时自动触发执行。

触发器可以在数据库级别定义，对于特定的表和事件进行相应的响应操作。

在实际应用中，触发器常用于实现数据完整性约束、数据审计、实时计算等功能。

1.2 触发器类型在Oracle数据库中，触发器主要分为行级触发器和语句级触发器两种类型。

行级触发器会在每一行数据被操作时触发执行，而语句级触发器则会在整个SQL语句执行完成时触发执行。

在设计触发器时，需要根据具体的业务需求来选择合适的触发器类型。

二、Oracle触发器的应用场景及写法2.1 实现数据完整性约束在数据库中，我们经常需要实现一些数据完整性约束，如主键约束、外键约束等。

而触发器可以作为实现这些约束的一种手段。

我们可以使用触发器在插入或更新数据时，对数据进行校验，确保符合特定的约束条件。

对于主键约束，我们可以使用BEFORE INSERT触发器来实现，在数据插入前对主键进行校验，防止重复插入数据。

示例代码如下：```sqlCREATE OR REPLACE TRIGGER trg_check_pkBEFORE INSERT ON table_nameFOR EACH ROWDECLAREv_count NUMBER;BEGINSELECT COUNT(*) INTO v_countFROM table_nameWHERE primary_key = :new.primary_key;IF v_count > 0 THENR本人SE_APPLICATION_ERROR(-0, 'Primary key already exists');END IF;END;/```对于外键约束，我们可以使用AFTER INSERT、AFTER UPDATE触发器来实现，在数据插入或更新后对外键进行校验，确保引用的数据存在。

Oracle中触发器如何调用存储过程

Oracle中触发器如何调⽤存储过程1--创建测试表B_TEST_TABLE，稍后会在本表上增加触发器，在插⼊本表的同时复制⼀份数据到B_TEST_TABLE2中2create table B_TEST_TABLE3(4 c1 VARCHAR2(200),5 d2 DATE6);78--创建B_TEST_TABLE2表，在插⼊B_TEST_TABLE数据时，复制⼀份数据到本表中9create table B_TEST_TABLE210(11 c1 VARCHAR2(200),12 d2 DATE13);1415--创建存储过程，往B_TEST_TABLE2中插⼊⼀条数据16create or replace procedure TestPro(C1 VARCHAR2) is17begin18INSERT INTO B_TEST_TABLE2 VALUES (C1, SYSDATE);19--因为此存储过程是触发器中调⽤的，所以不能增加提交命令20--COMMIT;21end TestPro;22--下⾯这个斜杠不能去掉，否则会导致触发器和存储过程或其他脚本编译在同⼀个对象中，每个单独的对象都应⽤此符号分开(建表等操作除外) 23/24--创建触发器，before insert on b_test_table为在插⼊b_test_table数据之前触发，可以改成after insert on b_test_table25--或者before update on b_test_table等选项26create or replace trigger TEST_TABLE_TRIGGER_INST_BF27 before insert on b_test_table28for each row29begin30--调⽤存储过程:new代表的是新的记录对象，可以直接取值或修改该记录的每⼀列，:new.c1是取新纪录的c1列值，:new.d2是取新纪录的d2列值31 TestPro(:new.c1);32--如果不想⽤存储过程也可以直接这样写33--INSERT INTO B_TEST_TABLE2 VALUES (:new.c1, :new.d2);34end TEST_TABLE_TRIGGER_INST_BF;35/36--插⼊测试数据37insert into B_TEST_TABLE values ('1', sysdate);38commit;3940--查询测试表数据41select*from B_TEST_TABLE;42select*from B_TEST_TABLE2;。

Oracle存储过程和触发器基本操作

南华大学计算机科学与技术学院实验报告（2012~2013 学年度第二学期）课程名称Oracle高级数据库开发设计实验名称存储过程与触发器基本操作姓名学号专业班级地点教师前提表脚本：create table S_RZ0122 (Sno varchar2(11) primary key,Sname varchar2(20) not null,Ssex varchar(2) not null ,Sage number(2) not null,Sdept varchar(20) not null)create table C_RZ0122(Cno varchar2(20) primary key,Cname varchar2(20) not null ,Ccredit number(2) not null)create table SC_RZ0122(Sno varchar2(11) not null,Cno varchar2(20) not null,Score number(3) ,primary key(Sno ,Cno),foreign key(Sno) references S_RZ0122(Sno),foreign key(Cno) references C_RZ0122(Cno))INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811101,'李勇','男',21,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811102,'刘晨','男',20,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811103,'王敏','女',20,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0811104,'张小红','女',19,'计算机系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0821101,'张立','男',20,'信息管理系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0821102,'吴宾','女',19,'信息管理系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0821103,'张海','男',20,'信息管理系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0831101,'钱小平','女',21,'通信工程系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0831102,'王大力','男',20,'通信工程系')INSERT INTO S_RZ0122 (Sno ,Sname,Ssex,Sage ,Sdept) V ALUES(0831103,'张珊珊','女',19,'通信工程系')---增加课程信息脚本：---insert allINSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C001','高等数学',4) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C002','大学英语',3) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C003','大学英语',3) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C004','计算机文化学',2) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C005','VB',2)INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C006','数据库基础',4) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C007','数据结构',4) INSERT INTO C_RZ0122 (Cno ,Cname ,Ccredit ) V ALUES ('C008','计算机网络',4) select * from C_RZ0122;---增加关联学生和课程信息脚本：---INSERT ALLINSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C001',96)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C002',80)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C003',84)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811101,'C005',62)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811102,'C001',92)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811102,'C002',90)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0811102,'C004',84)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C001',76)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C004',85)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C005',73)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821102,'C007',0)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821103,'C001',50)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0821103,'C004',80)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831101,'C001',50)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831101,'C004',80)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831102,'C007',0)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831103,'C004',78)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831103,'C005',65)INSERT INTO SC_RZ0122(Sno,Cno,Score) V ALUES (0831103,'C007',0)select Sno,Cno,Score from SC_RZ0122;一．实验题目存储过程与触发器基本操作二．实验要求掌握对存储过程和触发器的创建、修改等基本操作。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

三．实验步骤及过程根据实验 3创建的数据库及表，完成以下内容：(1)在JXGL数据库中创建一存储过程并运行，从 S 表中查询所有女生的信息。

创建存储过程prWoman执行存储过程prWoman(2)在 JXGL 数据库中创建一存储过程并运行，从 S 表中根据学号查询并返回该学生的姓名和年龄。

创建存储过程执行存储过程(3) 在JXGL数据库中创建一触发器，保证 S 表中学生的年龄在8-45 岁之间。

触发器说明：（触发器是特定事件出现的时候，自动执行的代码块。

类似于存储过程，触发器与存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的。

）首次创建出错：错误在下面，因为SYS用户所属的表中不能创建触发器，所以先复制了一份表到SYSTEM用户中，再创建触发器测试：(4) 在 JXGL 数据库中创建一触发器，当有学生选了某门课时，不能从 C 表中删除同样的，先复制C课程表再复制学生选课表：创建触发器：测试：成功该课程。

四．实验总结整个实验做下来困难重重啊！因为这些平时使用的不是很多，所以用起来很生疏，特别是触发器的使用的更少，这还是从头开始学了。

Oracle也挺烦的，这么麻烦。

以下是遇见的部分错误与感受，重要的都记下了，方便以后查看。