第五章：存储过程和触发器

CREATE [OR REPLACE] PROCEDURE <过程名>  /*定义过程名*/
[ (<参数名> <参数类型> <数据类型> [ DEFAULT <默认值>] [, …n])]
/*定义参数类型及属性*/
{ IS | AS }
[<变量声明>]/*变量声明部分*/
BEGIN
<过程体>/*PL/SQL过程体*/
END [<过程名>][;]

相关参数说明如下：

（1）过程名：存储过程名称要符合标识符规则，并且在所属方案中必须是唯一的。关键字OR REPLACE表示在创建存储过程时，如果已存在同名的过程，则重新创建。

（2）参数名：存储过程的参数名也要符合标识符规则，创建过程时，可以声明一个或多个参数，执行过程时应提供相对应的参数。存储过程的参数模式和函数参数一样，也有三种模式，分别为IN、OUT和IN OUT。

（3）DEFAULT：指定过程中IN参数的默认值，默认值必须是常量。

（4）过程体：其中包含PL/SQL语句块。

在存储过程的定义体中，不能使用下列对象创建语句：
CREATE VIEW
CREATE DEFAULT
CREATE RULE 
CREATE PROCEDURE
CREATE TRIGGER

【例1】创建一个简单的存储过程，输出hello world。

CREATE PROCEDURE proc
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world');
END;

【例2】创建存储过程，计算指定学生的总学分。

CREATE OR REPLACE PROCEDURE totalcredit
 ( xh IN varchar2)
AS
xf number;
BEGIN
SELECT 总学分
INTO xf
FROM XSB
WHERE 学号=xh AND rownum=1;
DBMS_OUTPUT.PUT_LINE(xf);
END;

注意：在存储过程体中，不能使用SELECT语句直接查询，否则会出现编译错误。

【3】计算机某专业总学分大于50分的人数，该存储过程使用了一个输入（IN）参数和一个（OUT）参数。

2．以界面方式创建存储过程

如果要通过界面方式定义上面的存储过程count_grade，步骤如下。

（1）启动SQL Developer，选择myorcl连接的“过程”节点，右击鼠标，选择“新建过程”菜单项进入“创建 PL/SQL 过程”对话框，如图所示。

（2）在“名称”文本框中输入存储过程的名称，单击按钮添加一个参数，在“参数”选项页的“Name”栏中输入各参数名称，在“Type”栏中选择参数的类型，在“Mode”栏中选择参数的模式，在“Default Value”栏中输入参数默认值（如果有的话）。

（3）单击“确定”按钮，在出现的“COUNT_GRADE”过程的编辑框中编写过程语句块，如图7.2所示，单击“编译以进行调试”按钮完成过程的创建。

（二）存储过程的调用

调用存储过程一般使用EXEC语句，语法格式为：

[ { EXEC | EXECUTE } ]  <过程名> 
[ ( [<参数名> =>] <实参> | @<实参变量> [,…n]) ] [;]

说明：EXEC是EXECUTE的缩写，<参数名>为CREATE PROCUDURE中定义的参数名称。在传递参数的实参时，如果指定了变量名，该变量则用于保存OUT参数返回的值；如果省略“<参数名>=>”，则后面的实参顺序要与定义时参数的顺序一致。

【例4】调用【例1】中的存储过程proc。

EXEC proc;

//或者
BEGIN
proc;
END;

【例5】从XSCJ数据库的XSB表中查询某人的总学分，根据总学分写评语。

CREATE OR REPLACE PROCEDURE update_info
 ( xh in char )
AS
xf number;
BEGIN
SELECT 总学分 INTO xf
FROM XSB
WHERE 学号=xh AND ROWNUM=1;
IF xf>50 THEN
UPDATE XSB SET 备注= '三好学生' WHERE 学号=xh;
END IF;
IF xf<42 THEN
UPDATE XSB SET 备注= '学分未修满' WHERE 学号=xh;
END IF;
END;

执行存储过程update_info：

EXEC update_info(xh=>'151242');

【例6】统计XSB表中男女同学的人数。

CREATE OR REPLACE PROCEDURE count_number
( sex IN char, num OUT number )
AS
BEGIN
IF sex= '男' THEN
SELECT COUNT(性别) INTO num
FROM XSB
WHERE 性别= '男';
ELSE
SELECT COUNT(性别) INTO num
FROM XSB
WHERE 性别= '女';
END IF;
END;

在调用过程count_number时，需要先定义OUT类型参数，如下：

DECLARE 
girl_num number;
BEGIN
count_number('女', girl_num);
DBMS_OUTPUT.PUT_LINE(girl_num);
END;

（三）存储过程的修改

修改存储过程和修改视图一样，虽然也有ALTER PROCEDURE语句，但它是用于重新编译或验证现有过程的。如果要修改过程定义，仍然使用CREATE OR REPLACE PROCEDURE命令，语法格式一样。其实，修改已有过程本质就是使用CREATE OR REPLEACE PROCEDURE重新创建一个新的过程，只要保持名字与原来的过程相同即可。使用界面方式也可很方便地修改存储过程定义。在SQL Developer中，在“过程”节点下选择要修改的存储过程，右击鼠标，选择“编辑”菜单项，在打开的存储过程编辑窗口中修改定义后单击“编译以进行调试”按钮即可。

当某个过程不再需要时，应将其删除，以释放它占用的内存资源。删除过程的语法格式为：

DROP PROCEDURE [<用户方案名>.] <过程名>;

【例7】删除XSCJ数据库中的count_number存储过程。

DROP PROCEDURE count_number;

也可以使用界面方式删除存储过程，具体操作如图所示，请读者自行尝试。

二、触发器

触发器是被指定关联到一个表的数据对象，它不需要调用，当对一个表的特别事件出现时，它就会被激活。触发器的代码也是由SQL语句组成的，因此用在存储过程中的语句也可以用在触发器的定义中。触发器是一类特殊的存储过程，与表的关系密切，用于保护表中的数据。当有操作影响到触发器保护的数据时，触发器将自动执行。

（一）以命令方式创建触发器

1．创建DML触发器

创建DML触发器语法格式为：

CREATE [OR REPLACE] TRIGGER [<用户方案名>.] <触发器名>
{ BEFORE∣AFTER∣INSTEAD OF }/*定义触发动作*/
{ DELETE | INSERT | UPDATE [ OF <列名>[,…n] ]}/*定义触发器种类*/
[OR { DELETE | INSERT | UPDATE [ OF <列名>[,…n] ]}]
ON  {<表名>∣<视图名>}              /*在指定表或视图中建立触发器*/
[ FOR EACH ROW [ WHEN(<条件表达式>) ] ]
<PL/SQL语句块>

【例8】创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将变量str的值设为“TRIGGER IS WORKING”并显示。

创建表table1：

CREATE TABLE table1(a number);

创建INSERT触发器table1_insert：

CREATE OR REPLACE TRIGGER table1_insert
AFTER INSERT ON table1
DECLARE 
str char(100) :='TRIGGER IS WORKING';
BEGIN
DBMS_OUTPUT.PUT_LINE(str);
END;

向table1中插入一行数据：

INSERT INTO table1 VALUES(10);

【例9】在XSCJ数据库中增加一个日志表XSB_HIS，表结构和XSB表相同，用来存放从XSB表中删除的记录。创建一个触发器，当XSB表被删除一行时，把删除的记录写到XSB_HIS表中。

CREATE OR REPLACE TRIGGER del_xs
BEFORE DELETE ON XSB FOR EACH ROW
BEGIN
INSERT INTO XSB_HIS (学号, 姓名, 性别, 出生时间, 专业, 总学分, 备注)
VALUES(:OLD.学号,:OLD.姓名, :OLD.性别, :OLD.出生时间, :OLD.专业, :OLD.总学分, :OLD.备注);
END;

OLD修饰访问操作完成前列的值。触发器建立后向XSB表中插入一行数据，之后查看XSB_HIS表中并没有添加了该行数据，这是因为触发器中的DML语句并没有使用提交语句提交，但触发器中不能使用COMMIT语句，所以需要定义自治事务来提交

【例10】利用触发器在数据库XSCJ的XSB表执行插入、更新和删除三种操作后给出相应提示。

CREATE TRIGGER cue_xs
AFTER INSERT OR UPDATE OR DELETE ON XSB FOR EACH ROW
DECLARE
Infor char(10);
BEGIN
IF INSERTING THEN/*INSERT语句激活了触发器*/
Infor:= '插入';
ELSIF UPDATING THEN/*UPDATE语句激活了触发器*/
Infor:= '更新';
ELSIF DELETING THEN/*DELETE语句激活了触发器*/
Infor:= '删除';
END IF;
DBMS_OUTPUT.PUT_LINE(Infor);
END;

说明：程序中使用条件谓词IF通过谓词INSERTING、UPDATING和DELETING分别判断是否是INSERT、UPDATE和DELETE激活了触发器。另外，在UPDATE触发器中使用UPDATING(列名)的形式来判断特定列是否被更新。

2．创建替代触发器

创建替代触发器使用INSTEAD OF关键字，一般用于对视图的DML触发。由于视图有可能由多个表进行关联而成，因而并非所有的关联都是可更新的。INSTEAD OF触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句。例如，若在一个多表视图上定义了INSTEAD OF INSERT触发器，视图各列的值可能允许为空也可能不允许。若视图某列的值不允许为空，则INSERT语句必须为该列提供相应的值。

【例7.11】在XSCJ数据库中创建视图stu_view，包含学生学号、专业、课程号、成绩。该视图依赖于表XSB和CJB，是不可更新视图。可以在视图上创建INSTEAD OF触发器，当向视图中插入数据时分别向表XSB和CJB插入数据，从而实现向视图插入数据的功能。

首先创建视图：

CREATE VIEW stu_view
AS 
SELECT XSB.学号, 专业, 课程号, 成绩
FROM XSB, CJB
WHERE XSB.学号=CJB.学号

创建INSTEAD OF触发器：

CREATE TRIGGER InsteadTrig
INSTEAD OF INSERT ON stu_view FOR EACH ROW
DECLARE 
xm char(8);
xb char(2);
cssj date;
BEGIN
xm:='徐鹤';
xb:= '男';
cssj:= '1997-07-28';
INSERT INTO XSB(学号, 姓名, 性别, 出生时间, 专业) 
VALUES(:NEW.学号,xm, xb, cssj, :NEW.专业);
INSERT INTO CJB VALUES(:NEW.学号, :NEW.课程号, :NEW.成绩);
END;

向视图插入一行数据：

INSERT INTO stu_view VALUES('151116', '计算机', '101', 85 );

查看数据是否插入：

SELECT * FROM stu_view WHERE 学号= '151116';

执行结果如图所示。

查看与视图关联的XSB表的情况：

SELECT * FROM XSB WHERE 学号= '151116';

执行结果如图所示。

3．创建系统触发器

系统触发器可以在DDL或数据库系统事件上被触发。DDL指的是数据定义语句，如CREATE、ALTER和DROP等。而数据库系统事件包括数据库服务器的启动（STARTUP）、关闭（SHUTDOWN）、出错（SERVERERROR）等。创建系统触发器的语法格式为：

CREATE OR REPLACE TRIGGER [<用户方案名>.] <触发器名>
{ BEFORE︱AFTER }
{ <DDL事件>︱<数据库事件> }
ON { DATABASE︱[用户方案名.] SCHEMA }
<触发器的PL/SQL语句块>

【例12】创建一个用户事件触发器，记录用户SYSTEM所删除的所有对象。首先以用户SYSTEM身份连接数据库，创建一个存储用户信息的表：

CREATE TABLE dropped_objects
(
object_name varchar2(30),
object_type varchar(20),
dropped_date date
);

创建BEFORE DROP触发器，在用户删除对象之前记录到信息表dropped_objects中。

CREATE OR REPLACE TRIGGER dropped_obj_trigger
BEFORE DROP ON SYSTEM.SCHEMA
BEGIN
INSERT INTO dropped_objects
VALUES(ora_dict_obj_name, ora_dict_obj_type, SYSDATE);
END;

现在删除SYSTEM模式下的一些对象，并查询表dropped_objects：

DROP TABLE table1;
DROP TABLE table2;
SELECT * FROM dropped_objects;

以界面方式创建触发器

触发器也可以利用SQL Developer的界面方式创建。

（1）选择myorcl连接的“触发器”节点，右击鼠标，选择“新建触发器”菜单项，进入“创建触发器”窗口，如图所示。

（2）在“名称”栏中输入触发器名称，在“触发器”选项卡中的“触发器类型”下拉列表中选择触发依据，有“Table”“View”“SCHEMA”和“Database”等选项。例如，如果是在表中创建触发器，则这里就选择“TABLE”。可以在“表名”栏中选择触发器所在的表，选中“早于”或“晚于”选项对应BEFORE和AFTER关键字，勾选“插入”、“删除”和“更新”复选框对应触发事件，完成后单击“确定”按钮。

（3）在出现的触发器代码编辑框中编写触发器定义中的PL/SQL语句，完成后单击工具栏的“编译以进行调试”按钮完成触发器的创建，如图所示。

（二）触发器的删除

1．以命令方式删除触发器

删除触发器使用DROP TRIGGER语句，语法格式为：

DROP TRIGGER [<用户方案名>.] <触发器名>

【例13】删除触发器del_xs。

DROP TRIGGER del_xs;

2．以界面方式删除触发器

在“触发器”节点中选择要删除的触发器，右击鼠标，选择“删除触发器”菜单项，在弹出的“删除触发器”对话框中单击“应用”按钮即可。操作如图所示。

最后，感谢大家的观看

原文地址：https://blog.csdn.net/2302_76674204/article/details/143721241

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：已解决：spark代码中sqlContext.createDataframe空指针异常
下一篇：C# VS的常用功能(一) 视图篇

@Autowired 和 @Resource思考（注入redisTemplate时发现一些奇怪的现象）
@Autowired 和 @Resource思考（注入redisTemplate时发现一些奇怪的现象）
阅读更多2024-11-16
25浙江省考-28天学行测-Day5 & Day6-判断推理（中）
1、另有他因2、因果倒置3、否定此因4、根本原因5、三圈质疑1、排除他因2、四圈支持1、无论据有结论2、有论据有结论3、质疑论证过程4、严谨逻辑关系5、比例类论证1、解释说明2、增加论据3、断点
阅读更多2024-11-16
Rust 入门指南（零）：安装及 Cargo 管理器
系列第 0 篇，简单介绍了 Rust 的基本安装和配置流程，以及如何通过 Cargo 创建和管理项目。
阅读更多2024-11-16
Centos使用人大金仓ksql
Centos使用人大金仓ksql
阅读更多2024-11-16
多模态大模型简介
多模态大模型是机器学习领域的一个新兴趋势，它结合了文本、图像、音频等多种数据模态，以实现更全面和深入的信息理解和处理。这种模型能够处理跨模态任务，如图像标注、视觉问答、文本到图像的生成等，是人工智能领
阅读更多2024-11-16
javaScript交互补充2（动画函数封装）
如果多个元素都使用这个动画函数，每次都要var 声明定时器，我们可以给不同元素使用不同的定时器（自己用自己的定时器）核心原理：利用js是一门动态语言，可以很方便的给当前对象添加属性。缓动动画就是让元素
阅读更多2024-11-16
【鸿蒙开发】第十五章 H5与端侧交互、Cookies以及Web调试
Web组件支持在应用拦截到页面请求后自定义响应请求能力。开发者通过接口来实现自定义资源请求响应。自定义请求能力可以用于开发者自定义Web页面响应、自定义文件资源响应等场景。Web网页上发起资源加载请求
阅读更多2024-11-16
javaScript交互补充（元素的三大系列）
使用client系列的相关属性来获取元素可视区的相关信息，可以动态的得到该元素的边框大小，元素大小等。返回自身包括padding，内容区宽度，不含边框，返回数值不带单位。返回自身包括padding，内
阅读更多2024-11-16
django 过滤器的执行
默认情况下，会执行，特别是如果在中配置了它，或者没有禁用它。禁用过滤器：可以通过在APIView中设置来禁用过滤器，避免执行。手动调用过滤器：如果需要，你可以在视图中手动调用来过滤查询集。自定义过滤器
阅读更多2024-11-16
在Ubuntu 24.04 LTS上安装飞桨PaddleX
首先，请运行“ubuntu-drivers devices”命令列出当前可用的硬件设备，确保Ubuntu 24.04.1 LTS已发现安装在桌面计算机中的英伟达显卡，如下图所示。是基于飞桨框架构建的一
阅读更多2024-11-16