《oracle数据库技术》课件第8章

8.1管理存储过程

8.2管理存储函数

8.3管理触发器

8.4小结

习题与思考题

实践8PL/SQL高级编程第8章PL/SQL高级应用8.1管理存储过程存储过程是PL/SQL语言的重要特征，是指为了完成某种特定功能而编写的命名的PL/SQL程序块，它为创建和存储高度结构化的、可重用的模块代码提供了一种手段。它存储在数据库中，属于数据库的一部分。利用存储过程不仅可以使程序代码简洁、规范，提高代码重用性的同时还能极大地改善操作性能，提高程序的执行效率。例如，在前台程序中想多次统计某医保卡的消费金额及等级，只需要将第7章中例7.1的PL/SQL块命名，修改为存储过程并存储在数据库中，这样在前台程序中可以随时通过调用这个存储过程方便地完成相关信息的统计。8.1.1创建存储过程

在Oracle数据库中创建存储过程的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“过程”，出现管理存储过程界面，如图8-1所示。

图8-1中对象类型显示为“过程”，单击“创建”按钮，出现创建存储过程界面，如图8-2所示。

图8-2界面定义了存储过程的名称、该存储过程所属方案及源代码。其中，存储过程的源代码包含了存储过程的参数说明和过程体的定义。图8-1管理存储过程界面图8-2创建存储过程界面

1)参数说明

存储过程中既可以没有参数，也可以有参数，还可以有多个参数。如果有，那么参数说明部分要在最前面，用括号括起来。如果有多个参数，那么参数之间用“，”分隔，其定义格式如下：

<参数名>IN|OUT|INOUT<数据类型>[<默认值>]

其中，参数的类型有3种：IN、OUT和INOUT。若没有为参数指定类型，则默认是IN类型。

● IN表示输入参数，用于从外部(调用环境)向过程内传递值，在过程内部不能给IN参数赋值，它是只读的。调用过程时，可以用常量、变量、表达式给这种参数传递值，这种参数也可以有默认值。

● OUT表示输出参数，用于从过程内返回值给过程的调用者。在过程内部不能使用OUT参数，只能给它赋值，且必须赋值。调用过程时，要使用变量代替这种参数，不能使用常量或表达式。

● INOUT表示输入输出参数，是前两者的结合，既可以从外部(调用环境)向过程内传递值，又可以将改变后的值从过程内返回给过程的调用者。同OUT参数一样，调用过程时，要使用变量代替这种参数，不能使用常量或表达式，这种参数也可以有默认值。

●参数的数据类型无须指明宽度，由调用环境决定。例如，定义一个字符型的输入参数s1，则s1INCHAR即可。

2)过程体的定义

与第7章的无名PL/SQL块结构基本相似，也包含了声明部分、执行部分和异常处理部分，只不过将无名PL/SQL块中的DECLARE关键字换成了AS。

图8-2中定义存储过程名为“consume_pro”，存储过程所属的方案为“ygbx_user”，PL/SQL源代码实现了显示“consume”表某医保卡的消费金额及等级，与例7.1一致。

所有信息设置完毕后，单击“显示SQL”按钮，即可显示自动形成的创建存储过程的CREATEPROCEDURE语句，此语句即为命令行方式创建存储过程的命令，单击“创建”按钮即可完成新存储过程的创建。

注意：只有编译通过的存储过程才产生编译代码，并存储到数据库数据字典中，才能被调用执行。

2．命令行方式

命令行方式创建存储过程的方法是在SQL*Plus或iSQL*Plus中使用CREATEPROCEDURE命令创建存储过程，创建存储过程的语法如下：

CREATEPROCEDURE[<方案名>.]<存储过程名>

[(<参数1>IN|OUT|INOUT<数据类型>,

<参数2>IN|OUT|INOUT<数据类型>,…)]

{IS|AS}

[说明部分]

BEGIN

语句序列；

[EXCEPTION异常处理]

END；其中：

● PROCEDURE为创建存储过程关键字。

●关键字IS和AS含义一样，两者选择其一。

●其他各项的含义同企业管理器方式。

【例8.1】利用命令行方式创建存储过程“staff1_pro”，通过员工的编号查看某员工的姓名及性别。(改写第7章例7.8为存储过程)

CREATEPROCEDUREstaff1_pro

(c1INCHAR)

AS

TYPEstaff_record_typeISRECORD

(v_snostaff.sno%TYPE,

v_snamestaff.sname%TYPE,

v_ssexstaff.ssex%TYPE,

v_sbirthdaystaff.sbirthday%TYPE,

v_saddressstaff.saddress%TYPE,

v_stelstaff.stel%TYPE,

v_cnoo%TYPE,

v_bnostaff.bno%TYPE

);

v1_staffstaff_record_type;

BEGIN

SELECT*INTOv1_staffFROMstaffWHEREsno=c1;

DBMS_OUTPUT.PUT_LINE(‘该员工的姓名为：’||v1_staff.v_sname||‘性别为：’||v1_staff.v_ssex);

END;

本例中定义了传入参数“c1”用于向过程体传入某员工的编号，“v1_staff”为过程体内的变量，为记录类型变量，用于临时存储某员工对应的相关信息。

命令执行后，在数据库中创建了一个命名为“staff1_pro”的存储过程，该过程信息存储在Oracle数据字典中。

注意：在定义参数时，如果参数与数据库表中字段相对应，那么其类型必须与字段类型一致。

【例8.2】利用OUT参数重做例8.1，创建存储过程“staff2_pro”，注意与“staff1_pro”的差别。

CREATEPROCEDUREstaff2_pro

(c1INCHAR,

v1_staffOUTstaff%ROWTYPE)

AS

BEGIN

SELECT*INTOv1_staffFROMstaffWHEREsno=c1;

END;

本例中利用“staff%ROWTYPE”定义记录类型变量“v1_staff”为传出参数，若传出参数是标量类型，例如NUMBER等，则不能给出数据的长度；另外，通过“v1_staff”可以直接将某员工的相关信息带出此存储过程，所以可以不需要在过程体内输出“v1_staff”中各分量的值。

命令执行后，在数据库中创建了一个命名为“staff2_pro”的存储过程，该过程信息存储在Oracle数据字典中。

注意：IN参数在过程内是只读的，只能出现在等号的右边。OUT参数在过程内值是可以改变的，而且必须在过程内被赋值，赋值的方式有两种，一种是用赋值语句，一种是通过SELECT…INTO语句。8.1.2调用存储过程

在创建的存储过程通过编译之后，就可以从SQL*Plus或iSQL*Plus环境中调用它，也可以从某一个具体应用中调用它，调用时必须传递相应的参数，要求实际参数与形式参数保持次序、类型及个数一致。调用的语法格式如下：

<过程名>(<实际参数1>,<实际参数2>,…);

【例8.3】调用存储过程“staff1_pro”和“staff2_pro”，查看存储过程的调用方法。

SETSERVEROUTPUTON

DECLARE

v1_staffstaff%ROWTYPE;

BEGIN

staff1_pro(‘00007’);

staff2_pro(‘00007’,v1_staff);

DBMS_OUTPUT.PUT_LINE(‘调用staff2_pro得到的员工姓名为：’||v1_staff.sname||‘性别为：’||v1_staff.ssex);

END;执行结果为本例中“staff1_pro”和“staff2_pro”是编译通过的存储过程，两者实现的功能相同。执行存储过程“staff1_pro”时，为其传入参数赋值“00007”，输出此员工的姓名及性别信息；执行存储过程“staff2_pro”时，同样为其传入参数赋值“00007”，同时增加传出参数“v1_staff”，其数据类型必须与存储过程中声明的传出参数一致，以此从存储过程中带出此员工的相关信息。8.1.3查看存储过程

Oracle数据库查看存储过程的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“过程”，出现管理存储过程界面，在搜索处输入要查看的存储过程的方案，单击“开始”按钮，该方案下的所有存储过程就会出现在结果列表中。选中要查看的存储过程，单击“视图”按钮即出现查看存储过程界面。

2．命令行方式

存储过程创建成功后，存储过程的信息存储在数据字典DBA_SOURCE中。命令行方式查看存储过程的方法是在SQL*Plus或iSQL*Plus中使用DESC、SELECT命令来查看存储在DBA_SOURCE中的存储过程信息。

【例8.4】用DESC命令查看数据字典DBA_SOURCE的结构。

DESCDBA_SOURCE;执行结果为从本例中可以看出，DBA_SOURCE系统表中存储着存储过程所属的方案名(OWNER)、存储过程名(NAME)、类型(TYPE)、存储过程体(TEXT)等信息。

【例8.5】利用DBA_SOURCE数据字典查看存储过程“staff1_pro”的相关信息。

SELECT*FROMDBA_SOURCEWHERENAME='STAFF1_PRO';执行结果为注意：通过存储过程名来查看存储过程信息时，一定要使用大写的名称，否则查不到结果。8.1.4修改存储过程

Oracle数据库修改存储过程的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“过程”，出现管理存储过程界面，选中要修改的存储过程，单击“编辑”按钮，即可出现修改存储过程界面。修改存储过程的基本操作同创建存储过程，单击“显示SQL”按钮，即可显示自动形成的修改存储过程的CREATEORREPLACEPROCEDURE语句，即为命令行方式修改存储过程的命令。

2．命令行方式

命令行方式修改存储过程的方法是在SQL*Plus或iSQL*Plus中，在创建存储过程命令中增加ORREPLACE选项来修改存储过程。修改存储过程的语法格式如下：

CREATEORREPLACEPROCEDURE[<方案名>.]<存储过程名>

[(<参数1>IN|OUT|INOUT<数据类型>,

<参数2>IN|OUT|INOUT<数据类型>,…)]

{IS|AS}

[说明部分]

BEGIN

语句序列；

[EXCEPTION异常处理]

END；其中，各参数的含义同创建存储过程。

注意：存储过程创建完成后，只允许修改存储过程体及参数。

【例8.6】修改存储过程“staff2_pro”，在过程体内也加入输出语句。

CREATEORREPLACEPROCEDUREstaff2_pro

(c1INCHAR,

v1_staffOUTstaff%ROWTYPE)

AS

BEGIN

SELECT*INTOv1_staffFROMstaffWHEREsno=c1;

DBMS_OUTPUT.PUT_LINE(‘该员工的姓名为：’||v1_staff.sname||‘性别为：’||v1_staff.ssex);

END;

在本例中过程体内增加了输出某员工姓名及性别的信息语句后，存储过程“staff2_pro”与“staff1_pro”在执行结果上就没有任何的差别了，但“staff1_pro”中“v1_staff”的各个分量值只能应用在过程体中，而“staff2_pro”中“v1_staff”的各个分量值可带出过程体。8.1.5删除存储过程

Oracle数据库删除存储过程的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“过程”，出现管理存储过程界面，选中要删除的存储过程，单击“删除”按钮，在出现的提示信息中选择“是”即可删除存储过程。

2．命令行方式

命令行方式删除存储过程的方法是在SQL*Plus或iSQL*Plus中使用DROPPROCEDURE命令删除存储过程，删除存储过程命令的一般格式如下：

DROPPROCEDURE[<方案名>.]<存储过程名>;

【例8.7】命令行方式删除存储过程“consume_pro”。

DROPPROCEDUREconsume_pro;

命令执行后，数据库中不再有存储过程“consume_pro”。8.2管理存储函数在Oracle数据库中，已经有很多内置的函数可以直接调用，如SUBSTR、SQRT等，另外，用户还可以根据需要在Oracle数据库中创建自己的函数，这些函数就是存储函数。存储函数与存储过程非常相似，都是Oracle数据库中命名的PL/SQL程序块。与存储过程的主要区别就是存储函数必须有一个返回值，而存储过程没有返回值。8.2.1创建存储函数

在Oracle数据库中创建PL/SQL存储函数的方法有企业管理器方式和命令行方式。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“函数”，出现管理存储函数界面，如图8-3所示。

图8-3中对象类型显示为“函数”，单击“创建”按钮，出现创建存储函数界面，如图8-4所示。图8-3管理存储函数界面图8-4创建存储函数界面

图8-4定义了PL/SQL存储函数的名称、所属方案和源代码。其中，存储函数的源代码包含了存储函数的参数说明、返回值类型说明和函数体的定义。

1)参数说明

存储函数中的形式参数也有IN、OUT和INOUT3种类型，用法同存储过程，但一般情况下只用IN类型。

2)返回值类型说明

由于存储函数必须有返回值，因此需要在源代码中定义返回值的数据类型。定义的语法格式如下：

RETURN<返回值类型>

3)函数体的定义

与过程体的定义类似，所不同的是，在函数体中必须有返回语句，可以返回一个值、一个变量或一个表达式，其语法格式如下：

RETURN<返回值/变量名/表达式>；

所有信息设置完毕后，单击“显示SQL”按钮，即可显示自动形成的创建存储函数的CREATEFUNCTION语句，此语句即为命令行方式创建存储函数的命令，单击“创建”按钮即可完成新存储函数的创建。

图8-4中定义存储函数名为“consume_fun”，存储函数所属的方案为“ygbx_user”，PL/SQL源代码实现了返回“consume”表某医保卡的消费金额及等级。

注意：同存储过程一样，只有编译通过的存储函数才产生编译代码，并只有存储到数据库数据字典中，才能被调用执行。

2．命令行方式

命令行方式创建存储函数的方法是在SQL*Plus或iSQL*Plus中使用CREATEFUNCTION命令创建存储函数，创建存储函数的语法如下：

CREATEFUNCTION[<方案名>.]<存储函数名>

[(<参数1>IN|OUT|INOUT<数据类型>,

<参数2>IN|OUT|INOUT<数据类型>,…)]

RETURN<返回值类型>

{IS|AS}

[说明部分]

BEGIN

语句序列；

RETURN<返回值/变量名/表达式>；

[EXCEPTION异常处理]

END；

由存储函数的创建语法可以看出，它与存储过程的创建语法很相似，主要有以下区别：

①创建存储过程的关键字是PROCEDURE，而创建存储函数的关键字是FUNCTION。

②由于函数有返回值，因此在参数定义完成后，增加了一个RETURN关键字，其后指明函数返回值的数据类型。

③执行部分必须至少有一个RETURN语句，用于把返回值返回给调用者，返回值的数据类型必须和RETURN关键字后面的数据类型相同。

注意：在存储函数执行到RETURN语句时，程序的执行流程会返回到调用环境中。在存储函数中可以存在多个RETURN语句，但是只有一个RETURN能被执行到。

【例8.8】利用命令行方式创建存储函数“staff1_fun”，通过员工的编号查看某员工的姓名及性别。

CREATEFUNCTIONstaff1_fun

(c1INCHAR)

RETURNstaff%ROWTYPE

AS

v1_staffstaff%ROWTYPE;

BEGIN

SELECT*INTOv1_staffFROMstaffWHEREsno=c1;

RETURNv1_staff;

END;

本例中创建的存储函数“staff1_fun”与存储过程“staff1_pro”很相似，同样用于查看某员工的姓名及性别，只不过在存储函数体中增加了RETURN语句，将得到的某员工的相关信息以记录类型变量“v1_staff”作为返回值返回给调用者。

注意：在定义参数时，如果参数与数据库表中字段相对应，则其类型必须与字段类型一致。8.2.2调用存储函数

同存储过程一样，一旦创建的存储函数通过编译之后，就可以从SQL*Plus或iSQL*Plus环境中调用它，也可以从某一个具体应用中调用它，调用时必须传递相应的参数，要求实际参数与形式参数保持次序、类型及个数一致。调用的语法格式如下：

<变量名>:=<函数名>(<实际参数1>,<实际参数2>,…);

【例8.9】调用存储函数“staff1_fun”，并输出某员工的姓名及性别。

SETSERVEROUTPUTON

DECLARE

v1_staffstaff%ROWTYPE;

BEGIN

v1_staff:=staff1_fun(‘00007’);

DBMS_OUTPUT.PUT_LINE(‘该员工的姓名为：’||v1_staff.sname||‘性别为：’||v1_staff.ssex);

END;执行结果为本例在执行存储函数“staff1_fun”时，为其传入参数赋值“00007”，此存储函数的调用返回记录类型变量存储了此员工的相关信息。注意：调用存储过程的语句可以作为单独的可执行语句在PL/SQL块中单独出现，而存储函数可以在任何表达式能够出现的地方被调用，不能作为可执行语句单独出现在PL/SQL块中。8.2.3查看存储函数

Oracle数据库查看存储函数的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“函数”，出现管理存储函数界面，在搜索处输入要查看的存储函数的方案，单击“开始”按钮，该方案下的所有存储函数就会出现在结果列表中。选中要查看的存储函数，单击“视图”按钮即出现查看存储函数界面。

2．命令行方式

命令行方式查看存储函数的方法与存储过程一样，存储过程和存储函数共用数据字典DBA_SOURCE，存储函数创建成功后，存储函数的信息同样存储在数据字典DBA_SOURCE中，通过表中的TYPE字段的值是“PROCEDURE”还是“FUNCTION”来区分存储的是存储过程还是存储函数的相关信息。

【例8.10】利用DBA_SOURCE数据字典查看存储函数“staff1_fun”的相关信息。

SELECT*FROMDBA_SOURCEWHERENAME='STAFF1_FUN';执行结果为从本例可以看出，通过存储函数名来查看存储函数信息时，同存储过程一样，也一定要使用大写的名称，否则查不到结果。8.2.4修改存储函数

Oracle数据库修改存储函数的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“函数”，出现管理存储函数界面，选中要修改的存储函数，单击“编辑”按钮，即可出现修改存储函数界面。修改存储函数的基本操作同创建存储函数，单击“显示SQL”按钮，即可显示自动形成的修改存储函数的CREATEORREPLACEFUNCTION语句，此语句即为命令行方式修改存储函数的命令。

2．命令行方式

命令行方式修改存储函数的方法是在SQL*Plus或iSQL*Plus中，在创建存储函数命令中增加ORREPLACE选项来修改存储函数。修改存储函数的语法格式如下：

CREATEORREPLACEFUNCTION[<方案名>.]<存储函数名>

[(<参数1>IN|OUT|INOUT<数据类型>,

<参数2>IN|OUT|INOUT<数据类型>,…)]

RETURN<返回值类型>

{IS|AS}

[说明部分]

BEGIN

语句序列；

RETURN<返回值/变量名/表达式>；

[EXCEPTION异常处理]

END；

其中，各参数的含义同创建存储函数。

注意：存储函数创建完成后，只允许修改参数、返回值类型及存储函数体。

【例8.11】修改存储函数“staff1_fun”，实现查询出生日期在某一时间段内的员工人数。

CREATEORREPLACEFUNCTIONstaff1_fun

(date1INDATE,

date2INDATE)

RETURNNUMBER

AS

sum1NUMBER;

BEGIN

SELECTcount(*)INTOsum1FROMstaffWHEREsbirthday>=date1andsbirthday<=date2;

RETURNsum1;

END;

本例中设置了两个IN类型的参数“date1”和“date2”，限定查询时间段的起始时间和终止时间。8.2.5删除存储函数

Oracle数据库删除存储函数的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“函数”，出现管理存储函数界面，选中要删除的存储函数，单击“删除”按钮，在出现的提示信息中选择“是”即可删除存储函数。

2．命令行方式

命令行方式删除存储函数的方法是在SQL*Plus或iSQL*Plus中使用DROPFUNCTION命令删除存储函数，删除存储函数命令的一般格式如下：

DROPFUNCTION[<方案名>.]<存储函数名>;

【例8.12】命令行方式删除存储函数“consume_fun”。

DROPFUNCTIONconsume_fun;

命令执行后，数据库中不再有存储函数“consume_fun”了。8.3管 理 触 发 器触发器是存储在数据库中由特定事件触发的一种特殊类型的存储过程。它与普通的存储过程不同的是，触发器不能在程序中显示地调用执行，只有当某一触发事件发生时，Oracle隐式地(自动地)调用执行该触发器，并且触发器不能接受任何参数。触发器的应用主要在安全性、数据跟踪、数据完整性和数据复制等方面。一个触发器由触发依据、触发事件、触发时间、触发器类型和触发器主体5部分组成。在编写触发器主体(源代码)之前，必须先确定好其触发依据、触发时间和触发器类型。触发器的组成如表8-1所示。8.3.1创建触发器

在Oracle数据库中创建触发器的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“触发器”，出现管理触发器界面，如图8-5所示。

图8-5中对象类型显示为“触发器”，单击“创建”按钮，出现创建触发器“一般信息”页，如图8-6所示。图8-5管理触发器界面图8-6触发器“一般信息”页图8-6用于定义触发器的一般属性，包括触发器的名称、触发器所属方案、若存在是否替换、是否启动和触发器主体。其中，“替换”和“启用”均为复选项，前者选中后，表示如果触发器已存在则将被覆盖，后者选中后，表示触发器一旦创建完成就立即启用。

创建触发器“事件”页用于定义触发器的触发事件及触发时间等信息，如图8-7所示。图8-7触发器“事件”页

“触发器依据”下拉列表框中有“表”、“视图”、“方案”和“数据库”4项，如果选择“表”(默认值)，则表示要创建的触发器是数据表的触发器，这里主要介绍按表触发的设置，其他触发依据类似。

“表”是触发器依据的表，可以直接填写，也可以通过单击表后的图标，在表选择页中搜索得到。

“触发触发器”是定义触发器触发的时间，确定在触发事件执行之前还是之后触发触发器，此项为单选项，必须二者选一，默认为“之前”。

“事件”是指定触发事件。对于触发依据表而言，触发事件包括插入、删除、更新，此项为复选项。插入表示向表中插入新数据行时会触发触发器，删除表示从表中删除数据行时会触发触发器，更新列表示对表中某列数据进行更新时会触发触发器，选中此选项后“更新列”表格会列出此触发依据表的所有列。如果想在更新某些列时触发触发器，则选中这些列即可，即设置是基于哪些列进行触发，相当于在触发器定义语法中选择UPDATEOF选项。一个触发器可以包含多个触发事件，在触发器的主体可以使用谓词来判断是哪个触发事件触发了触发器。这些谓词包括INSERTING、UPDATING和DELETING，分别与插入、更新和删除事件相对应，这些谓词的值是布尔型的，由系统根据触发事件来决定其值是TRUE还是FALSE。

创建触发器“高级”页用于定义触发器的类型，确定触发器是语句级触发还是行级触发，此页只用于表或视图触发器，如图8-8所示。

“逐行触发”复选项表明该触发器被指定为行级触发器，使其在受到触发事件影响的每一行数据上均被触发。如果未选中，表明该触发器被指定为语句级触发器，使其在受到触发事件影响的所有数据行上只被触发一次。图8-8触发器“高级”页“引用”列表只有“逐行触发”选项选中后，此项才有效。具体的引用分为“旧值”和“新值”。“旧值”指数据操作之前的原始值，标识符为“:OLD”；“新值”指数据操作之后的新值，标识符为“:NEW”。

“条件”同样是只有“逐行触发”选项选中后，此项才有效，用于指定行触发器触发的条件。

在创建数据库触发器时，经常会引用被插入和被删除列的值，或者是被更新记录的更新前和更新后的值，这就需要有一种方法来获得这两个值，标识符“:OLD”和“:NEW”可以达到这个目的。针对行触发器的“:OLD”和“:NEW”的意义如表8-2所示。注意：“:OLD”和“:NEW”只用在行级触发器，不用在语句级触发器。在WHEN子句中使用时，前面不要加“:”。

图8-6～图8-8定义了语句级触发器名为“staffupdate_tri”，触发器所属的方案为“ygbx_user”，触发器触发的依据为“staff”表，当更新“sname”字段值后触发触发器，显示“数据更新成功！”。

所有信息设置完毕后，单击“显示SQL”按钮，即可显示自动形成的创建触发器的CREATETRIGGER语句，此语句即为命令行方式创建触发器的命令，单击“创建”按钮即可完成新触发器的创建。

注意：只有编译通过的触发器才能被触发执行。

2．命令行方式

命令行方式创建触发器的方法是在SQL*Plus或iSQL*Plus中使用CREATETRIGGER命令创建触发器，创建触发器的语法如下：

CREATETRIGGER[<方案名>.]<触发器名>

BEFORE|AFTER

INSERT|UPDATE[OF<字段列表>]|DELETE[ORINSERT|UPDATE[OF<字段列表>]|DELETE…]ON<表名>

[FOREACHROW[WHEN<触发条件>]]

<触发体>；其中：

● TRIGGER：创建触发器关键字。

● BEFORE|AFTER：指定触发器触发时间，是在触发事件发生之前触发还是触发事件发生之后触发。

● INSERT|UPDATE[OF<字段列表>]|DELETE：指定触发事件，插入事件、更新事件(OF<字段列表>表示在某些列被更新时才触发触发器)还是删除事件。以上三个语句可以通过OR进行组合，表示一个触发器可以包含多个触发事件。

● ON<表名>：表示触发事件依据的表。

● FOREACHROW：表示该触发器是行级触发器，默认是语句级触发器。

● WHEN<触发条件>：指定触发器约束条件，只有条件成立时，才会触发触发器，只适用在行级触发器中。

●触发体：完整的PL/SQL块，定义触发器触发时要完成的功能。

【例8.13】创建语句级触发器“consume_tri”，当删除“comsume”表中的数据行之前触发触发器，并输出提示信息。

CREATETRIGGERconsume_tri

BEFOREDELETEONconsume

BEGIN

DBMS_OUTPUT.PUT_LINE(‘语句级触发器正在执行删除数据行操作！’);

END;

本例在创建触发器时没有FOREACHROW选项，所以触发器“consume_tri”是语句级触发器。

【例8.14】创建行级触发器“consume_row_tri”，当删除“comsume”表中的数据行之前触发触发器，并输出提示信息。

CREATETRIGGERconsume_row_tri

BEFOREDELETEONconsume

FOREACHROW

BEGIN

DBMS_OUTPUT.PUT_LINE(‘行级触发器正在执行删除数据行操作！’);

END;

本例在创建触发器时有FOREACHROW选项，所以触发器“consume_row_tri”是行级触发器。

触发器创建成功后，当执行下面的DELETE语句时，触发器“consume_tri”和“consume_row_tri”被触发。

DELETEFROMconsumeWHEREcno='2199990004800017';

假设上面语句的执行删除了3条记录，其执行结果为：

行级触发器正在执行删除数据行操作！

行级触发器正在执行删除数据行操作！

行级触发器正在执行删除数据行操作！

语句级触发器正在执行删除数据行操作！

从结果中可以看出，行级触发器和语句级触发器的执行过程有明显的区别。行级触发器对满足触发条件的数据表的每行数据操作均执行一次触发器，而语句级触发器对满足触发条件的数据表的所有数据操作只触发一次。另外，依据同一对象(这里是表“consume”)可以创建多个触发器。不管数据库中有多少个触发器，只要有触发器的触发条件满足时，该触发器就会自动执行。

【例8.15】包含多个触发事件的触发器示例。

CREATETRIGGERcs_row_tri

AFTERINSERTORUPDATEORDELETEONconsume

FOREACHROW

BEGIN

IFINSERTINGTHEN

DBMS_OUTPUT.PUT_LINE(‘正在向consume表插入数据！’);

ENDIF;

IFUPDATINGTHEN

DBMS_OUTPUT.PUT_LINE(‘正在更新consume表中的数据！’);

ENDIF;

IFDELETINGTHEN

DBMS_OUTPUT.PUT_LINE(‘正在删除consume表中的数据！’);

ENDIF;

END;

从本例中可以看出，包含多个触发事件的触发器在触发体中使用谓词“INSERTING”、“UPDATING”、“DELETING”将触发执行语句分开。如果触发器被触发，就会根据不同的触发事件自动转入不同的分支执行。当执行“INSERT”操作时，触发器会转入“INSERTING”分支执行；当执行“UPDATE”操作时，触发器会转入“UPDATING”分支执行；当执行“DELETE”操作时，触发器会转入“DELETING”分支执行。

【例8.16】创建一个行级触发器“insuranceinsert_row_tri”，当向医保卡中存入金额时，医保卡中存款余额随之增加相应的金额。

CREATEORREPLACETRIGGERinsuranceinsert_row_tri

AFTERINSERTONinsurance

FOREACHROW

BEGIN

UPDATEcardSETcmoney=cmoney+:NEW.imoneyWHEREcno=:NEW.cno;

DBMS_OUTPUT.PUT_LINE(‘当“insurance”表增加一笔存款金额时，“card”表中相应医保卡上的金额随之增加等量的金额！’);

END;

当执行下面的SQL语句时，触发器“insuranceinsert_row_tri”被触发。

INSERTINTOinsurance(idate,cno,imoney,bno)VALUES(‘8-8月-2000’,‘219800010100011’,100,‘B19800101’);

从本例中可以看出，创建了行级触发器“insuranceinsert_row_tri”用于维护“insurance”和“card”两表中数据的平衡，也就是级联更新“card”表中“cmoney”的值，当“insurance”表中“imoney”的值改变时，“card”表中“imoney”的值也随之改变。例子中利用“:NEW.imoney”指定“insurance”表中新插入的“imoney”值为100，利用“cno=:NEW.cno”指定“insurane”表中新增加的金额对应的是“card”表中的哪个医保卡。

注意：触发器的一个重要作用就是增强参照完整性约束。参照完整性约束有两层含义：一层是级联删除，另一层是级联更新。对于级联删除在定义外键约束时指定ONDELETTECASCADE关键字即可实现，当然也可以通过触发器和存储过程完成。但想实现级联更新就必须借助于触发器和存储过程，两者的区别在于是否能接收参数。由于触发器是由数据库自动触发执行的，因此触发器不能接收参数。如果想完成级联更新的同时又想接收参数，那么只能利用存储过程来实现。

【例8.17】创建带有WHEN子句的行级触发器“when_row_tri”，当修改“card”表中医保卡余额后，如果医保卡余额小于等于0时触发，将给出提示信息“医保卡余额不足，无法进行消费”。

CREATETRIGGERwhen_row_tri

AFTERUPDATEONcard

FOREACHROWWHEN(NEW.cmoney<=0)

BEGIN

DBMS_OUTPUT.PUT_LINE(‘医保卡余额不足，无法进行消费！’);

END;

本例中，WHEN子句表示当修改记录的“cmoney”字段时，若该字段的值小于等于0，则触发器“when_row_tri”执行。由于是在WHEN子句中使用NEW，因此其前面不要加“:”。8.3.2查看触发器

Oracle数据库查看触发器的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“触发器”，出现管理触发器界面，在搜索处输入要查看的触发器的方案，单击“开始”按钮，该方案下的所有触发器就会出现在结果列表中。选中要查看的触发器，单击“视图”按钮即出现查看触发器界面。

2．命令行方式

命令行方式查看触发器的方法是在SQL*Plus或iSQL*Plus中使用DESC、SELECT命令来查看触发器。一旦触发器被创建，其相关信息就被存储在DBA_TRIGGERS数据字

典中。

【例8.18】用DESC命令查看数据字典DBA_TRIGGERS的结构。

DESCDBA_TRIGGERS;执行结果为

从本例中可以看出，DBA_TRIGGERS数据字典中存储着触发器所属的方案名(OWNER)、触发器名(TRIGGER_NAME)、触发器类型(TRIGGER_TYPE)、触发器事件(TRIGGERING_EVENT)、触发器主体(TRIGGER_BODY)等信息。

【例8.19】利用DBA_TRIGGERS系统表查看触发器“staffupdate_tri”的相关信息。

SELECTTRIGGER_NAME,TRIGGER_TYPE,

TRIGGERING_EVENT,TABLE_NAME,TRIGGER_BODY

FROMDBA_TRIGGERSWHERETRIGGER_NAME=‘STAFFUPDATE_TRI’;

执行结果为

从本例可以看出，通过触发器名来查看触发器信息时，触发器名也一定要使用大写的名称，否则查不到结果。8.3.3修改触发器

Oracle数据库修改触发器的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“触发器”，出现管理触发器界面，选中要修改的触发器，单击“编辑”按钮，即可出现修改触发器界面。修改触发器的基本操作同创建存储过程，单击“显示SQL”按钮，即可显示自动形成的修改触发器的CREATEORREPLACETRIGGER语句，此语句即为命令行方式修改触发器的命令。

2．命令行方式

命令行方式修改触发器的方法是在SQL*Plus或iSQL*Plus中，在创建触发器命令中增加ORREPLACE选项来修改触发器。修改触发器的语法格式如下：

CREATEORREPLACETRIGGER[<方案名>.]<触发器名>

BEFORE|AFTERINSERT|UPDATE[OF<字段列表>]|DELETE[ORINSERT|UPDATE[OF<字段列表>]|DELETE…]ON<表名>

[FOREACHROW[WHEN<触发条件>]]

<触发体>；

其中，各参数的含义同创建触发器。

注意：触发器创建完成后，触发器所属的方案、触发器的名称、触发器依据不能被修改。

【例8.20】修改触发器“staffupdate_tri”，使其变为行级触发器。

CREATEORREPLACETRIGGERstaffupdate_tri

AFTERDELETEONstaff

FOREACHROW

BEGIN

DBMS_OUTPUT.PUT_LINE(‘sname字段值更新成功！’);

END;

本例中，增加了FOREACHROW选项，使触发器“staffupdate_tri”由原来的语句级触发器变成了行级触发器。8.3.4删除触发器

Oracle数据库删除触发器的方法有企业管理器方式和命令行方式。

1．企业管理器方式

在企业管理器中选择“管理”\“方案”\“触发器”，出现管理触发器界面，选中要删除的触发器，单击“删除”按钮，在出现的提示信息中选择“是”即可删除触发器。

2．命令行方式

命令行方式删除触发器的方法是在SQL*Plus或iSQL*Plus中使用DROPTRIGGER命令删除触发器，删除触发器命令的一般格式如下：

DROPTRIGGER[<方案名>.]<触发器名>;

【例8.21】命令行方式删除触发器“cs_row_tri”。

DROPTRIGGERcs_row_tri;

命令执行后，数据库中不再有触发器“cs_row_tri”。8.3.5禁用/启用触发器

在许多情况下触发器也比较“烦人”，特别是在数据库调试的过程中，如果某个触发依据(例如某个表)上创建了多个触发器，当进行多种操作时，可能会频繁触发触发器而引起过多“已知”的提示，并且可能使系统效率急剧下降。因此，这时需要屏蔽某些触发器，这就需要对触发器禁用。

禁用了触发器后，如果又要使用这个触发器，就必须启动它。与服务器一样，触发器有两种状态：禁用状态和启动状态。这两种状态是可以相互转换的。

Oracle数据库禁用/启动触发器的方法有企业管理器方式和命令行方式两种。

1．企业管理器方式

在触发器修改的过程中，可以对触发器进行禁用或启动。例如，想将触发器“consume_tri”禁用，通过企业管理器方式进入到“consume_tri”触发器修改界面，如图8-9所示。

图8-9中有一个“启用”复选框，选中表示启用，否则表示禁用，只要改变该复选框状态，并单击“应用”按钮即可完成触发器的禁用或启动。图8-9触发器修改界面

2．命令行方式

命令行方式禁用/启动触发器的方法是在SQL*Plus或iSQL*Plus中使用带DISABLE |ENABLE选项的ALTERTRIGGER语句禁用/启动触发器，禁用/启动触发器命令的一般格式如下：

ALTERTRIGGER触发器名DISABLE|ENABLE；

或

ALTERTABLE表名DISABLE|ENABLEALLTRIGGER；其中，第一条语句是对单个触发器禁用/启动，第二条语句是对基于“表名”对应的表的所有触发器禁用/启动。例如，想将刚刚被禁用的触发器“consume_tri”重新启用，可输入如下命令：

ALTERTRIGGERconsume_triENABLE；8.4小结本章对方案的高级对象作了介绍，重点介绍了存储过程、存储函数和触发器的概念、作用及基本操作。通过本章的学习，读者应该了解：

(1)存储过程、存储函数是对第7章内容的扩充，它们可以在Oracle的客户端与服务器端的任何工具及与Oracle数据库连接的任何前台应用程序中通过存储过程或存储函数名调用，在应用程序中通过为它们赋予不同的参数值来多次调用同一存储过程或存储函数，以实现程序的规范化和简单化，提高程序的使用效率。存储过程与存储函数非常相似，但存储过程是为了完成某种特定功能，存储函数的最终任务是返回一个值。

(2)触发器也是为了完成某种特定功能而编写的PL/SQL命名块，以独立的对象存储在数据库中由特定事件触发的存储过程。触发器与存储过程的不同之处在于，触发器由数据库系统在满足触发条件时自动运行，而无需编程调用它；触发器不能接收参数。习题与思考题

1.试述存储过程中3种类型参数的区别。

2.试述存储过程与存储函数的区别。

3.数据库触发器有哪几个组成部分？

4.试述触发器与存储过程的区别。

5.哪个系统表存储了存储过程、存储函数和触发器的信息？

6.简述存储过程、存储函数和触发器的作用。实践8PL/SQL高级编程实践目的

(1)掌握存储过程、存储函数、触发器高级数据库对象的基本作用。

(2)掌握存储过程、存储函数、触发器的建立、修改、查看、删除操作。实践要求

(1)记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。

(2)记录利用企业管理器管理存储过程、存储函数、触发器的方法。

(3)记录利用SQL*Plus和iSQL*Plus管理存储过程、存储函数、触发器的命令。实践内容下列任务中涉及的数据表是第2章中给出的表。

1．创建存储过程

(1)利用企业管理器将实践7.3中的(3)～(5)题创建成存储过程，存储过程名自己设定，注意比较未命名的PL/SQL与命名的PL/SQL的差别。

(2)利用SQL*Plus或iSQL*Plus创建存储过程“sex_pro”，通过传入参数传入性别(男、女)，显示员工表“staff”中不同性别的员工人数，并执行该存储过程。

(3)利用SQL*Plus或iSQL*Plus创建存储过程“num_pro”，通过传入参数传入3个数，完成3个数的从小到大排序，通过3个传出参数保存排序后的3个数，并执行该存储过程，显示排序结果。

2．查看存储过程

(1)利用企业管理器查看员工医疗保险数据库中的所有存储过程。

(2)利用SQL*Plus或iSQL*Plus从DBA_SOURCE数据字典中查看员工医疗保险数据库中的所有存储过程。

3．修改存储过程

(1)利用企业管理器修改存储过程“num_pro”，完成3个数的从大到小的排序，并执行该存储过程，显示排序结果。

(2)利用SQL*Plus或iSQL*Plus修改存储过程“sex_pro”，通过传入参数传入性别(男、女)，通过传出参数得到员工表“staff”中不同性别的员工人数，并执行该存储过程，显示员工表“staff”中不同性别的员工人数。

4．删除存储过程

(1)利用企业管理器删除存储过程“sex_pro”。

(2)利用SQL*Plus或iSQL*Plus删除存储过程“num_pro”。

5．创建存储函数

(1)利用企业管理器将实践7.3中的(1)、(2)题创建成存储函数，存储函数名自己设定，注意比较未命名的PL/SQL与命名的PL/SQL的差别。

(2)利用SQL*Plus或iSQL*Plus创建存储函数“card_fun”，通过传入参数传入医保类型(企业、事业或是灵活就业)，返回医保卡表“card”中不同医保类型的医保卡数量，并执行该存储函数。

(3)利用SQL*Plus或iSQL*Plus创建存储函数“staff_fun”，通过传入参数传入员工的编号，根据传入的员工编号，检查该员工是否存在。如果存在，则返回TRUE，否则返回FALSE，并执行该存储函数。

(4)利用SQL*Plus或iSQL*Plus创建存储函数“sex_fun”，利用传入参数传入性别(男、女)，返回员工表“staff”中不同性别的员工人数，并执行该存储函数，注意比较与存储过程“sex_pro”的差别。

6．查看存储函数

(1)利用企业管理器查看员工医疗保险数据库中的所有存储函数。

(2)利用SQL*Plus或iSQL*Plus从DBA_SOURCE数据字典中查看员工医疗保险数据库中的所有存储函数。

7．修改存储函数

(1)利用企业管理器修改存储函数“staff_fun”，增加一个传出参数，当检查传入的员工编号存在时，传出参数存储该员工的姓名信息，否则存储“该员工不存在”信息，并执行该存储函数。

(2)利用SQL*Plus或iSQL*Plus修改存储函数“card_fun”，增加通过传出参数显示不同医保类型的医保卡上的总余额，并执行该存储函数。

8．删除存储函数

(1)利用企业管理器删除存储函数“staff_fun”。

(2)利用SQL*Plus或iSQL*Plus删除存储函数“card_fun”。

9．创建触发器

(1)利用企业管理器创建行级触发器“insurance_row_tri”，当删除医保表“insurance”中某医保卡号的记录时触发，提示“行级触发器删除数据成功！”。删除某记录，并查看

结果。

(2)利用企业管理器创建语句级触发器“insurance_tri”，当删除医保表“insurance”中某医保卡号的记录时触发，提示“语句级触发器删除数据成功！”。删除某记录，并查看结果，注意比较与行级触发器“insurance_row_tri”的不同。

(3)利用SQL*Plus或iSQL*Plus创建行级触发器“update_row_tri”，当医保卡表“card”的某一“cno”值更改时，消费表“consume”中对应的“cno”值也跟着进行相应的更改。更改“card”表的某一“cno”值，查看“consume”表中对应的“cno”值是否发生变化。

(4)利用SQL*Plus或iSQL*Plus创建语句级触发器“delete_tri”，当删除员工表“st