【Oracle】存储过程和触发器
一、存储过程1、存储过程的创建
以命令方式创建存储过程
创建存储过程使用CREATE PROCEDURE语句，语法格式为：
CREATE PROCEDURE <过程名> /*界说过程名*/
[ (<参数名> <参数范例> <数据范例> [ DEFAULT <默认值>] [, …n])] /*界说参数范例及属性*/
{ IS | AS }
[<变量声明>] /*变量声明部门*/
BEGIN
<过程体> /*PL/SQL过程体*/
END [<过程名>][;]
在视图(VIEW)中只能用AS不能用IS;
在游标(CURSOR)中只能用IS不能用AS。
【例1】创建一个简单的存储过程，输出hello world。
https://i-blog.csdnimg.cn/blog_migrate/2f437a5acbdc7f1af5c543a1415eb2e2.png
【例2】创建存储过程，盘算指定学生的总学分。
https://i-blog.csdnimg.cn/blog_migrate/c8fb6612c6ddc5a46e512cb2f46e8348.png
rownum=1：查询第一行的记载。
【例3】
盘算某专业总学分大于50的人数，该存储过程使用了一个输入（IN）参数和一个输出（OUT）参数。
https://i-blog.csdnimg.cn/blog_migrate/25ea71dea8a74572f57d0a5b8d7d4d9c.png
2、存储过程的调用
调用存储过程一般使用EXEC语句，语法格式为：
[ { EXEC | EXECUTE } ] <过程名>
[ ( [<参数名> =>] <实参> | @<实参变量> [,…n]) ] [;]
在PL/SQL块中也可以直接使用过程名来调用存储过程。
【例1】调用上面创建的存储过程proc。
https://i-blog.csdnimg.cn/blog_migrate/ed70e3ba6c6c7908dfaf8f7dfa999793.pnghttps://i-blog.csdnimg.cn/blog_migrate/7120be4bc4c6cac19ba9085160ee9371.png
【例2】 统计XSB表中男女同砚的人数。
在调用过程count_number时，需要先界说OUT范例参数
https://i-blog.csdnimg.cn/blog_migrate/4ca323eb3524b33ecec0b6f14c74c4a4.png
3、存储过程的修改
修改已有过程本质就是使用CREATE OR REPLACE PROCEDURE重新创建一个新的过程，只要保持名字与原来的过程相同即可。
4、存储过程的删除
删除过程的语法格式为：
DROP PROCEDURE [<用户方案名>.] <过程名>;
https://i-blog.csdnimg.cn/blog_migrate/7be2fa701e94203253b1aa6a3f1a6f99.png
二、触发器
触发器是一种特殊的数据库对象，它可以在特定的变乱发生时自动执行一些操作，可以用于实现复杂的数据约束、数据验证、数据审计等功能。
（1）DML触发器。当数据库中发生数据操纵语言（DML）变乱时将调用DML触发器。一般情况下，DML变乱包括对表或视图的INSERT语句、UPDATE语句和DELETE语句，因此DML触发器又可分为三种范例：INSERT、UPDATE和DELETE触发器。
（2）替代触发器。由于在Oracle中不能直接对由两个以上的表创建的视图进行操作，所以给出了替代触发器。它是Oracle专门为进行视图操作的一种处理方式。
（3）系统触发器。系统触发器也是由相应的变乱触发，但它的激活一般基于对数据库系统所进行的操作，如数据界说语句（DDL）、启动或关闭数据库、毗连与断开、服务器错误等系统变乱。
1、创建触发器
（1）创建DML触发器
创建触发器都使用CREATE RIGGER语句。
语法格式为：
CREATE TRIGGER [<用户方案名>.] <触发器名>
{ BEFORE∣AFTER∣INSTEAD OF } /*界说触发动作*/
{ DELETE | INSERT | UPDATE [ OF <列名>[,…n] ]} /*界说触发器种类*/
]}]
ON {<表名>∣<视图名>} /*在指定表或视图中创建触发器*/
[ FOR EACH ROW [ WHEN(<条件表达式>) ] ]
<PL/SQL语句块>
说明：
（1）触发器名：触发器与过程名和包的名字不一样，它有单独的名字空间，因此触发器名可以和表名或过程名同名，但在同一个方案中的触发器名不能相同。
（2）BEFORE：触发器在指定操作执行前触发，如BEFORE INSERT表示在向表中插入数据前激活触发器。
（3）AFTER：触发器在指定操作都乐成执行后触发，如AFTER INSERT表示向表中插入数据时激活触发器。不能在视图上界说AFTER触发器。
（4）INSTEAD OF：指定创建替代触发器，触发器指定的变乱不执行，而执行触发器本身的操作。
（5）DELETE，INSERT，UPDATE：指定一个或多个触发变乱，多个触发变乱之间用OR毗连。
（6）OF：指定在某列上应用UPDATE触发器，如果为多个列，则需要使用逗号分隔。
（7）FOR EACH ROW：在触发器界说中，如果未使用FOR EACH ROW子句则表示触发器为语句级触发器，触发器在激活后只执行一次，而不管这一操作将影响多少行。
【例1】创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将变量str的值设为“TRIGGER IS WORKING”并表现。
创建表table1：
CREATE TABLE table1(a number);
创建INSERT触发器table1_insert：
https://i-blog.csdnimg.cn/blog_migrate/2436be1c6f380ea96040b35b992225e8.png
【例2】在数据库中增长一个日记表XSB_HIS，表结构和XSB表相同，用来存放从XSB表中删除的记载。创建一个触发器，当XSB表被删除一行，把删除的记载写到XSB_HIS表中。
https://i-blog.csdnimg.cn/blog_migrate/30def8d43e2be2bb966a1dbeb7e9e95b.png
OLD表示要被更改的原来的数据行
【例3】 利用触发器在数据库的XSB表中执行插入、更新和删除三种操作后给出相应提示。
https://i-blog.csdnimg.cn/blog_migrate/1cba25e85ec5741ee299f36d7b07c88b.png
（3）创建替代触发器
【例】在数据库中创建视图stu_view，包含学生学号、专业、课程号、成绩。该视图依靠于表XSB和CJB，是不可更新视图。可以在视图上创建INSTEAD OF触发器，当向视图中插入数据时分别向表XSB和CJB插入数据，从而实现向视图插入数据的功能。
起首创建视图：
CREATE VIEW stu_view
AS
SELECT XSB.学号,专业,课程号,成绩
FROM XSB, CJB
WHERE XSB.学号=CJB.学号
创建INSTEAD OF触发器：
https://i-blog.csdnimg.cn/blog_migrate/6f13f3a94ea41c5ee8505ee2bfea6fd7.png
向视图插入一行数据：
INSERT INTO stu_view VALUES('151116', '盘算机', '101', 85 );
检察数据是否插入：
SELECT * FROM stu_view WHERE 学号= '151116';
检察与视图关联的XSB表的情况：
SELECT * FROM XSB WHERE 学号= '151116';
2、启用和禁用触发器
Oracle提供了ALTER TRIGGER语句用于启用和禁用触发器，语法格式为：
ALTER TRIGGER [<用户方案名>.]<触发器名>
DISABLE | ENABLE;
其中，DISABLE表示禁用触发器，ENABLE表示启用触发器。例如，要禁用触发器del_xs，使用如下语句：
ALTER TRIGGER del_xs DISABLE;
如果要启用或禁用一个表中的所有触发器，还可以使用如下的语法：
ALTER TABLE <表名>
{ DISABLE | ENABLE }
ALL TRIGGERS;
3、触发器的删除
删除触发器使用DROP TRIGGER语句，语法格式为：
DROP TRIGGER [<用户方案名>.] <触发器名>
【例】删除触发器del_xs。
https://i-blog.csdnimg.cn/blog_migrate/221e128cbcc9a3a7bcb46fa74bb29a2a.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]