第9章 存储过程、函数和触发器.ppt

第9章 存储过程、函数和触发器 第9章 存储过程、函数和触发器 Oracle数据库中存储过程和函数是由多条语句组成的可以整体执行的语句块。这些语句块可以进行显式命名，并被其他应用调用，它们是数据库常用对象。另外一种数据库常用对象是触发器，它也是程序块，不同的是触发器在用户创建了之后，会在触发条件成立时自动执行代码块，无需人为控制调用。 9.1 存储过程 存储过程是编译好且存储在数据库服务器中的程序代码，有自己的名字和参数，在需要的时候可以被调用执行。 存储过程有以下优点： ★实现了模块化编程，一个存储过程可以被多个用户共享和重用。 ★存储过程具有对数据库立即访问的功能。 ★可以提高数据库执行效率，加快程序的运行速度。 ★可以减少网络通信量。 ★隐藏了数据，提高数据库的安全性。 9.1 存储过程 9.1.1 创建存储过程 下面是存储过程的创建语法： CREATE [ OR REPLACE ] PROCEDURE [SCHEMA.] procedure_name [ parameter_name [ [IN] datatype [{:= | default} expression]| { OUT|IN OUT } datatype ] [, …] { IS | AS } BEGIN body END [ procedure_name ]; 9.1 存储过程 1. 使用SQL语句创建和查看存储过程 可以在SQL*Plus 中使用CREATE命令创建存储过程，下面是一个简单的例子。 【例9.1】创建存储过程Resetsal，将SCOTT.emp表中所有人的工资增加500. CREATE OR REPLACE PROCEDURE Resetsal AS BEGIN UPDATE scott.emp SET sal =sal + 500 ; END; / 9.1 存储过程 例9.2在视图USER_SOURCE中查看刚刚创建的存储过程的脚本。 SELECT text FROM USER_SOURCE WHERE NAME=RESETSAL AND TYPE=PROCEDURE‘ / 9.1 存储过程 9.1.2 调用存储过程 不同环境下，存储过程的执行方式不同。可以使用EXECUTE命令执行存储过程，但必须具有对该存储过程的EXECUTE权限。语法如下： EXECUTE procedure_name (parameter_list) 执行存储过程： EXECUTE RESETSAL; 9.1 存储过程 9.1.3 带参数的存储过程 存储过程中可以带参数，参数分为输入参数、输出参数和输入输出参数三种，可以是常量、变量、表达式等。 1．输入参数 输入参数对于存储过程是只读的，它由调用者传递给存储过程之后，在存储过程执行过程中，可以使用输入参数，但无法改变该参数的值。 9.1 存储过程 9.1.3 带参数的存储过程 【例9.3】改写例9.1中创建的存储过程，传入一个参数in_sal，将SCOTT.emp表中所有人的工资增加到原来的in_sal倍. CREATE OR REPLACE PROCEDURE Resetsal2(in_sal IN number) AS BEGIN UPDATE SCOTT.emp SET sal =sal * in_sal ; COMMIT; END; / 9.1 存储过程 2．输出参数 【例9.5】创建存储过程，使用户可以输入相应的雇员编号，得到scott.emp表中该雇员的工作类型。 CREATE PROCEDURE Getjob(eno in number, jobtype out varchar2) AS BEGIN SELECT job INTO jobtype FROM scott.emp WHERE empno=eno; COMMIT; END; / 9.1 存储过程 执行存储过程： Set serveroutput on; DECLARE jobkind varchar2(10)； BEGIN Getjob(7900,jobkind); dbms_output.put_line(jobkind); END； / 9.1 存储过程 3．输入输出参数 IN OUT参数及可以作为输入参数，也可以作为输出参数。一般在需要对参数的值进行处理再输出处理结果的情况下使用IN OUT参数。 9.1 存储过程 【例9.6】使用IN OUT参数交换两个变量的值。 CREATE