PL/SQL存储过程

在本章中，我们将讨论PL/SQL中的存储过程。 子程序是执行特定任务的程序单元/模块。 这些子程序组合起来形成更大的程序。这种做法被称为“模块化设计”。 子程序可以被称为调用程序的另一个子程序或程序调用。

可以在以下几个地方中创建一个子程序 –

  • 在模式(schema)级别中

  • 一个程序包中

  • 在PL/SQL块中

在模式(schema)级别中，子程序是一个独立的子程序。它是使用CREATE PROCEDURE或CREATE FUNCTION语句创建的。它存储在数据库中，可以使用DROP PROCEDURE或DROP FUNCTION语句进行删除。

在包中创建的子程序是打包的子程序。它存储在数据库中，只有当使用DROP PACKAGE语句删除程序包时，才能将其删除。我们将在“PL/SQL程序包”一章中讨论程序包的应用。

PL/SQL子程序被命名为可以使用一组参数调用的PL/SQL块。 PL/SQL提供两种子程序 –

  • 函数 – 这些子程序返回单个值; 主要用于计算和返回值。

  • 存储过程(程序) – 这些子程序不直接返回值; 主要用于执行动作。

本章将主要介绍PL/SQL中的存储过程。在下一章介绍讨论PL/SQL函数。

PL/SQL子程序的部分

每个PL/SQL子程序都有一个名称，也可能有一个参数列表。 像匿名PL/SQL块一样，命名块也将具有以下三个部分

部分

描述

声明部分

这是一个可选的部分。但是，子程序的声明部分不以DECLARE关键字开头。 它包含类型，游标，常量，变量，异常和嵌套子程序的声明。这些项是本子程序，当子程序完成执行时，它们将不复存在。

可执行部分

这是一个强制性部分(必须有)，并包含执行指定操作的语句。

异常处理

这是一个可选的部分。它包含处理运行时错误的代码。

创建存储过程

可使用CREATE OR REPLACE PROCEDURE语句来创建一个存储过程。 CREATE OR REPLACE PROCEDURE语句的简化语法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} [局部变量声明]BEGIN  END procedure_name;

其中

  • procedure-name是要创建的存储过程的名称。

  • [OR REPLACE]选项允许修改现有的过程。

  • 可选参数列表包含参数的名称，模式和类型。IN表示将从外部传递的值，OUT表示将用于返回过程外的值的参数。

  • procedure-body包含可执行部分。

  • 使用AS关键字而不是IS关键字来创建存储过程。

例子

以下示例演示如何创建一个简单的存储过程，执行时它只显示字符串“Hello World！”在屏幕上。

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE greetings AS BEGINdbms_output.put_line('Hello World!'); END; -- 执行存储过程exec greetings;-- 或者EXECUTE greetings;-- 或者call greetings();

执行独立程序

独立的存储程序可以通过两种方式调用 –

  • 使用EXECUTE关键字

  • 从PL/SQL块调用过程的名称

可以使用EXECUTE关键字调用名为“greetings”的存储过程如下

EXECUTE greetings;

上述调用将显示结果为

Hello World!PL/SQL 过程已成功完成。

该过程也可以从另一个PL/SQL块调用，例如

BEGINgreetings; END; /

执行上面示例代码，得到以下结果

Hello World!PL/SQL 过程已成功完成。

删除独立存储过程

使用DROP PROCEDURE语句删除独立存储过程。删除程序的语法是

DROP PROCEDURE procedure-name;

PL/SQL子程序中的参数模式

下表列出了PL/SQL子程序中的参数模式 –

参数模式

描述

IN

IN参数允许将值传递给子程序。它是一个只读参数。在子程序中，IN参数的作用如常数，它不能被赋值。可以将常量，文字，初始化的变量或表达式作为IN参数传递。也可以将其初始化为默认值; 然而，在这种情况下，从子程序调用中省略它。 它是参数传递的默认模式。参数通过引用传递。

OUT

OUT参数返回一个值给调用程序。在子程序中，OUT参数像变量一样。 可以更改其值并在分配该值后引用该值。实际参数必须是可变的，并且通过值传递。

IN OUT

IN OUT参数将初始值传递给子程序，并将更新的值返回给调用者。 它可以分配一个值，该值可以被读取。对应于IN OUT形式参数的实际参数必须是变量，而不是常量或表达式。正式参数必须分配一个值。实际参数(实参)通过值传递。

IN和OUT模式 – 示例1

假设以下存储过程需要求出两个值中的最小值。这里，存储过程两个输入的数字使用IN模式，并使用OUT模式参数返回最小值。

CREATE OR REPLACE PROCEDURE findMin(x IN number, y IN number, z OUT number) IS BEGINIF x < y THEN z:= x;ELSE z:= y;END IF; END; DECLAREa number;b number;c number;BEGINa:= 12;b:= 35;findMin(a, b, c);dbms_output.put_line('两个数：12, 35中的最小值是 : ' || c); END;

当上述代码在SQL提示符下执行时，它会产生以下结果

两个数：12, 35中的最小值是 : 12

IN和OUT模式 – 示例2

此过程计算传递值的值的平方。此示例显示了如何使用相同的参数来接受值，然后返回另一个结果。

CREATE OR REPLACE PROCEDURE squareNum(x IN OUT number) IS BEGIN x := x * x; END; DECLAREa number; BEGINa:= 11;squareNum(a);dbms_output.put_line(' Square of (23): ' || a); END; /

存储过程案例

create or replace procedure myDemo01asbegindbms_output.put_line('hello word, my name is stored procedure');end;call myDemo01();--call 存储过程名可完成调用，注意括号不能少
create or replace procedure myDemo02(name in varchar,age in int)asbegindbms_output.put_line('name='||name||', age='||age);end;call myDemo02('张三',22);
create or replace procedure myDemo04(name out varchar,age in int)asbegindbms_output.put_line('age='||age);select 'ex_sunqi' into name from dual;end;declare name varchar(10); age int := 25;begin myDemo04(name,age); dbms_output.put_line('name='||name);end;
create or replace procedure myDemo03asage int;beginage:=1/0;dbms_output.put_line(age);--异常exception when others thendbms_output.put_line('error');end;call myDemo03();
create or replace procedure myDemo5asn_count number := 0;beginwhile n_count < 5 loopdbms_output.put_line(n_count);n_count := n_count + 1;end loop;end; beginmyDemo5;end;
create or replace procedure myDemo06asbegin FOR USE in (select * from T_USER_INFO) loop if (USE.id<3) thendbms_output.put_line(USE.USER_NAME);end if;end loop;end;CALL myDemo06();
create or replace procedure mydemo07(ID in int, addTime in date,userName in varchar2, password in varchar2,logTime in date)asbegin insert into t_user VALUES(ID,addTime,userName,password,logTime);commit; --提交end;beginmydemo07(t_user_id_seq.nextVal,sysdate,'张三XXX','666666',sysdate);end;
CREATE SEQUENCE t_user_seq_idINCREMENT BY 1START WITH 150;create or replace procedure insert_user(paddTime in timestamp,puserName in varchar2 ,pPassword in varchar2,pSex in int)as begininsert into t_user(id,addTime,userName,password,sex) values(t_user_seq_id.nextval,paddTime,puserName,pPassword,pSex);end;declare addTime TIMESTAMP := sysdate; userName varchar2(20) := '管理员'; password varchar2(20) := '123456'; sex int := 1;beginfor pid in 1..100 loopinsert_user(addTime,userName,password,sex);end loop;end;call insert_user(sysdate,'张三','123456',0);

L/SQL函数

在本章中，我们将讨论和学习PL/SQL中的函数。函数与过程(也叫程序)相同，只不过函数有返回一个值，而过程没有返回值。 因此，上一章中所有有关存储过程的内容也适用于函数。

创建函数

使用CREATE FUNCTION语句创建独立函数。CREATE OR REPLACE PROCEDURE语句的简化语法如下：

CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] type [, ...])] RETURN return_datatype {IS | AS} [局部变量]BEGIN END [function_name];

其中

  • function-name是指定要创建的函数的名称。

  • [OR REPLACE]选项指示是否允许修改现有的函数。

  • 可选参数列表包含参数的名称，模式和类型。 IN表示将从外部传递的值，OUT表示将用于返回过程外的值的参数。

  • 函数必须包含一个返回(RETURN)语句。

  • RETURN子句指定要从函数返回的数据类型。

  • function-body包含可执行部分。

  • 使用AS关键字代替IS关键字，用来创建独立的函数。

示例

以下示例说明如何创建和调用独立函数。此函数返回客户表(CUSTOMERS)中的总数。

我们将使用在PL/SQL变量章节中创建的CUSTOMERS表 –

CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );-- 数据INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 );

基于上面表和数据记录，创建一个函数：totalCustomers 来计算总客户数量。参考以下代码

CREATE OR REPLACE FUNCTION totalCustomers RETURN number IStotal number(2) := 0; BEGINSELECT count(*) into totalFROM customers;RETURN total; END; /begin dbms_output.put_line(totalCustomers);end;

调用函数

在创建一个函数时，我们给出一个定义函数的语句以及实现的功能。 要使用一个函数，必须调用该函数来执行定义的任务。当程序调用一个函数时，程序控制被传递给被调用的函数。

被调用的函数执行定义的任务，当执行返回语句或达到最后一个结束语句时，它将程序控制返回到主程序。

如果调用一个函数，只需要传递所需的参数和函数名，如果函数返回一个值，那么可以存储返回的值。 以下程序演示如何从匿名块调用函数totalCustomers

SET SERVEROUTPUT ON SIZE 99999;DECLAREc number(2); BEGINc := totalCustomers();dbms_output.put_line('当前客户的总数为: ' || c); END; /

示例

以下示例演示声明，定义和调用一个简单的PL/SQL函数，该函数计算并返回两个值中的最大值。

SET SERVEROUTPUT ON SIZE 99999;CREATE OR REPLACE FUNCTION findMax(x IN number, y IN number)RETURN number IS z number; BEGINIF x > y THEN z:= x;ELSE Z:= y;END IF; RETURN z; END;DECLAREa number;b number;c number; BEGINa:= 23;b:= 45; c := findMax(a, b);dbms_output.put_line('两个数：23,45 之中的最大值是: ' || c); END; /

执行上面示例代码，得到以下结果

两个数：23,45 之中的最大值是: 4
CREATE OR REPLACE FUNCTION count_userRETURN intistotalUser int := 0;beginselect count(id) into totalUser from t_user;return totalUser;end;declareret int := 0;beginret := count_user();dbms_output.put_line(ret);end;-- 函数的返回值：1、return，2、可以用outcreate or replace function findUserNameByIdFunction(pid in int)return varchar2ispUserName varchar2(20) := null;beginselect userName into pUserName from t_user where id = pid;return pUserName;end;declareuserName varchar2(20) := null;beginuserName := findUserNameByIdFunction(1);dbms_output.put_line(userName);end;

PL/SQL递归函数

我们在前面已经看到程序或子程序可能会调用另一个子程序。当子程序调用自身时，它被称为递归调用，该过程称为递归。

为了更好地说明递归这个概念，让我们来看看计算一个给定数字的阶乘示例。 数字n的因子被定义为

n! = n*(n-1)!= n*(n-1)*(n-2)! ...= n*(n-1)*(n-2)*(n-3)... 1

以下过程是通过递归调用本身来计算给定数字的阶乘

SET SERVEROUTPUT ON SIZE 99999;CREATE OR REPLACEFUNCTION fact(x number) RETURN numberISf number; BEGINIF x=0 THEN f := 1;ELSE f := x * fact(x-1);END IF; RETURN f; END;DECLAREnum number;factorial number;BEGINnum:= 10;factorial := fact(num);dbms_output.put_line(' 数字 '|| num || ' 的阶乘积是： ' || factorial); END; /

当上述代码在SQL提示符下执行时，它会产生以下结果

数字 10 的阶乘积是： 3628800PL/SQL 过程已成功完成。

PL/SQL游标

在本章中，我们将讨论和学习PL/SQL中的游标。 Oracle创建一个称为上下文区域的内存区域，用于处理SQL语句，它包含处理该语句所需的所有信息; 例如，处理的行数等。

游标是指向此上下文区域的指针。PL/SQL通过游标控制上下文区域，游标保存SQL语句返回的行(一个或多个)。 游标所在的行集称为活动集。

可以命名一个游标，以便在程序中引用它来获取和处理SQL语句返回的行，一次处理一个(行)。PL/SQL中有两种类型的游标 –

  • 隐式游标

  • 显式游标

隐式游标

隐式游标用来将DML语句执行结果接收到。

当执行SQL语句时，如果语句没有显式游标，则Oracle会自动创建隐式游标。程序员无法控制隐式游标及其信息。

每当发出DML语句(INSERT，UPDATE和DELETE)时，隐式游标与此语句相关联。 对于INSERT操作，游标保存需要插入的数据。对于UPDATE和DELETE操作，游标标识将受到影响的行。

在PL/SQL中，可以将最近的隐式游标引用为SQL游标，它始终具有%FOUND，%ISOPEN，%NOTFOUND和%ROWCOUNT等属性。 SQL游标具有额外的属性%BULK_ROWCOUNT和%BULK_EXCEPTIONS，旨在与FORALL语句一起使用。下表提供了游标中最常用属性的描述

属性

描述

%FOUND

如果INSERT，UPDATE或DELETE语句影响一行或多行，或老兄SELECT INTO语句返回一行或多行，则返回TRUE，否则返回FALSE。

%NOTFOUND

与%FOUND的逻辑相反。 如果INSERT，UPDATE或DELETE语句没有影响任何行，或SELECT INTO语句未返回任何行，则返回TRUE。 否则返回FALSE。

%ISOPEN

由于Oracle在执行关联的SQL语句后会自动关闭SQL游标，因此总是为隐式游标返回FALSE。

%ROWCOUNT

返回受INSERT，UPDATE或DELETE语句，或者受SELECT INTO语句影响的行数。

任何SQL游标属性将被访问为sql％attribute_name，如下例所示。

示例

这里将使用在前几章中创建和使用的CUSTOMERS表，表结构和数据参考：

CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );-- 插入数据INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 );

以下程序将表中每个客户的工资增加500，并使用SQL%ROWCOUNT属性来确定受影响的行数

DECLARE total_rows number(2); BEGINUPDATE customersSET salary = salary + 500;IF sql%notfound THEN dbms_output.put_line('没有找到客户信息~');ELSIF sql%found THEN total_rows := sql%rowcount;dbms_output.put_line('一共有：' || total_rows || ' 个客户的工资被更新！ ');END IF;END;

如果查询了客户表中的记录，会发现工资已更新(增加了500)

SQL> select id,name,salary from customers;ID NAME SALARY---------- -------------------- ---------- 1 Ramesh 2500 2 Khilan 2000 3 kaushik2500 4 Chaitali 7000 5 Hardik 9000 6 Komal5000

隐式游标案例

beginupdate customers set salary = salary + 1000;if sql%found thendbms_output.put_line('受影响的行数:' || sql%rowcount);elsedbms_output.put_line('受影响的行数为0!');end if;end;
begindelete from customers;if sql%notfound thendbms_output.put_line('受影响的行数为0!');elsedbms_output.put_line('受影响的行数:' || sql%rowcount);end if;end;
declarec_count int;beginselect count(1) into c_count from customers;dbms_output.put_line('受影响的行数为 ' || sql%rowcount);dbms_output.put_line('查询出来的记录数 ' ||c_count);end;

显式游标

显式游标是用于获得对上下文区域的更多控制的程序员定义的游标。应在PL/SQL块的声明部分中定义一个显式游标。它是在一个返回多行的SELECT语句中创建的。

创建显式游标的语法是

CURSOR cursor_name IS select_statement;

使用显式游标包括以下步骤 –

  • 声明游标初始化内存

  • 打开游标分配内存

  • 从游标获取数据

  • 关闭游标以释放分配的内存

声明游标

声明游标使用名称和相关的SELECT语句来定义游标。 例如 –

CURSOR c_customers ISSELECT id, name, address FROM customers;

打开游标

打开游标将为游标分配内存，并使其准备好将SQL语句返回的行记录数据提取到其中。例如，打开上面定义的游标，如下所示：

OPEN c_customers;

获取游标

获取游标一次仅访问一行。 例如，从上面打开的游标中获取行，如下所示代码：

FETCH c_customers INTO c_id, c_name, c_addr;

关闭游标

关闭游标意味着释放分配的内存。例如，关闭上面打开的游标，如下所示：

CLOSE c_customers;

示例

以下是一个完整的例子来说明显式游标的概念。

DECLAREc_id customers.id%type;c_name customers.name%type;c_addr customers.address%type;CURSOR c_customers is SELECT id, name, address FROM customers; BEGINOPEN c_customers;LOOPFETCH c_customers into c_id, c_name, c_addr; EXIT WHEN c_customers%notfound; dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);END LOOP;CLOSE c_customers; END;

游标案例

-- 测试基础数据create table stu_info (id number(3),name varchar2(30),sexvarchar2(2));insert into stu_info(id, name, sex) values (1, '小游子', '女');insert into stu_info(id, name, sex) values (2, '小优子', '男');commit;
declare-- 1 声明游标cursor cur_stu_info is select * from stu_info;v_stu_info cur_stu_info%rowtype; --rowtype用于接收行数据begin-- 2 开启游标open cur_stu_info;-- 3 获取数据（一次获取一行）fetch cur_stu_info into v_stu_info;dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name);-- 4 关闭游标close cur_stu_info;end;
declarecursor cur_user is select id,addTime,userName,password from t_user;id t_user.id%type;addTime t_user.addTime%type;userName t_user.userName%type;password t_user.password%type;beginopen cur_user;loopfetch cur_user into id,addTime,userName,password;exit when cur_user%NOTFOUND ;dbms_output.put_line('id = '||id||'addTime = '||to_char(addTime,'yyyy-mm-dd hh24:mi:ss')||' userName = '||userName||' password = '||password);end loop;close cur_user;end;
declare p_id int := 1;--查询条件--下面是接收返回结果c_id t_user.id%type;c_addTime t_user.addTime%type;c_userName t_user.userName%type;--前面_userName和表t_user的字段userName类型一致c_password t_user.password%type;c_sex t_user.sex%type;CURSOR user_rs is select id,addTime,userName,password,sex from t_user where id = p_id;beginopen user_rs;loopfetch user_rs into c_id, c_addTime,c_userName,c_password,c_sex;exit when user_rs%notfound;dbms_output.put_line(c_id);dbms_output.put_line(c_userName);dbms_output.put_line(c_addTime);dbms_output.put_line(to_char(c_addTime,'yyyy-mm-dd hh24:mi:ss'));dbms_output.put_line(c_userName);dbms_output.put_line(c_password);dbms_output.put_line(c_sex);end loop;close user_rs;end;

PL/SQL记录

在本章中，我们将讨论和学习PL/SQL中的记录。 记录是可以容纳不同种类的数据项的数据结构。 记录由不同的字段组成，类似于数据库表的一行。

例如，想要在图书馆中跟踪记录图书信息。可能希望跟踪每本书的以下属性，例如标题，作者，主题，图书ID。 包含每个这些项目的字段的记录允许将图书视为逻辑单元，并允许以更好的方式组织和表示其信息。

PL/SQL可以处理以下类型的记录 –

  • 基于表的记录

  • 基于游标的记录

  • 用户定义的记录

基于表的记录

%ROWTYPE属性使程序员能够创建基于表和基于游标的记录。

以下示例说明了基于表的记录的概念。这里将使用前面章节中创建和使用的customers表，表结构和数据如下

CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );-- 插入示例数据INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 );

使用表记录示例代码

DECLAREcustomer_rec customers%rowtype;BEGINSELECT * into customer_recFROM customersWHERE id = 5; dbms_output.put_line('客户ID: ' || customer_rec.id);dbms_output.put_line('客户姓名: ' || customer_rec.name);dbms_output.put_line('客户地址: ' || customer_rec.address);dbms_output.put_line('客户薪资: ' || customer_rec.salary); END;

基于游标的记录

以下示例说明了基于游标的记录的概念，下面将使用在前面创建和使用的CUSTOMERS表，参考示例代码如下

DECLARECURSOR customer_cur isSELECT id, name,address FROM customers;customer_rec customer_cur%rowtype; BEGINOPEN customer_cur;LOOP FETCH customer_cur into customer_rec; EXIT WHEN customer_cur%notfound; DBMS_OUTPUT.put_line(customer_rec.id || ' : ' || customer_rec.name);END LOOP; END;

PL/SQL触发器

在本章中，我们将讨论和学习PL/SQL中的触发器。 触发器是存储的程序，在发生某些事件时会自动执行或触发。事实上，触发器是为了响应以下任何事件而被执行的 –

  • 数据库操作(DML)语句(DELETE，INSERT或UPDATE)

  • 数据库定义(DDL)语句(CREATE，ALTER或DROP)。

  • 数据库操作(SERVERERROR，LOGON，LOGOFF，STARTUP或SHUTDOWN)。

可以在事件关联的表，视图，模式或数据库上定义触发器。

使用触发器的好处

触发器可以用于以下目的 –

  • 自动生成一些派生列值

  • 强化参照完整性

  • 事件记录和存储表访问信息

  • 审计

  • 表的同步复制

  • 实施安全授权

  • 防止无效的事务

创建触发器

创建触发器的语法是

CREATE [OR REPLACE ] TRIGGER trigger_name{BEFORE | AFTER | INSTEAD OF }{INSERT [OR] | UPDATE [OR] | DELETE}[OF col_name]ON table_name[REFERENCING OLD AS o NEW AS n][FOR EACH ROW]WHEN (condition) DECLAREDeclaration-statements BEGIN Executable-statements EXCEPTIONException-handling-statements END;

其中，

  • CREATE [OR REPLACE] TRIGGER trigger_name – 使用trigger_name创建或替换现有的触发器。

  • {BEFORE | AFTER | INSTEAD OF} – 指定何时执行触发器。INSTEAD OF子句用于在视图上创建触发器。

  • {INSERT [OR] | UPDATE [OR] | DELETE} – 这指定了DML操作。

  • [OF col_name] − 这指定了将要更新的列名称。

  • [ON table_name] – 这指定了与触发器关联的表的名称。

  • [REFERENCING OLD AS o NEW AS n] – 这允许各种DML语句(如INSERT，UPDATE和DELETE)引用新值和旧值。

  • [FOR EACH ROW] – 这指定了一个行级别的触发器，即触发器将被执行的每一行受到影响。否则触发器将在执行SQL语句时执行一次，这称为表级触发器。

  • WHEN(condition) – 这为触发器触发的行提供了一个条件。该子句仅对行级触发器有效。

删除触发器

DROP TRIGGER

示例

首先，将使用前面章节中创建和使用的CUSTOMERS表，表的定义和数据如下 –

CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );-- 插入示例数据INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 );

下面的程序为customers表创建一个行级触发器，该触发器将触发在customers表上执行的INSERT，UPDATE或DELETE操作。这个触发器将显示旧值和新值之间的工资差异

CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLAREsal_diff number; BEGINsal_diff := :NEW.salary- :OLD.salary;dbms_output.put_line('Old salary: ' || :OLD.salary);dbms_output.put_line('New salary: ' || :NEW.salary);dbms_output.put_line('Salary difference: ' || sal_diff); END;

这里需要考虑以下几点 –

  • OLD和NEW引用不可用于表级触发器，而是可以将它们用于记录级触发器。

  • 如果要在同一个触发器中查询表，则应该使用AFTER关键字，因为触发器只能在应用初始更改并且表返回一致状态后才能查询表或进行更改。

  • 上面的触发器是这样：在表上执行任何DELETE或INSERT或UPDATE操作之前触发，但是可以在一个或多个操作上编写触发器，例如BEFORE DELETE，当表中的一条记录被删除时，自动触发。

触发一个触发器

现在，在customers表上执行一些DML操作。这里以执行一个INSERT语句作为示例，它将在表中创建一个新记录

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Hinew', 23, 'Oracle', 9500.00 );

当在CUSTOMERS表中创建一条记录时，上面的创建触发器display_salary_changes将被触发，并且将显示以下结果

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)VALUES (7, 'Hinew', 23, 'Oracle', 9500.00);