Oracle存储过程、触发器

一、存储过程

存储过程是一种命名的PL/SQL程序块，保存在数据库中，因为是已经编译好的代码，所以在被调用时，其执行效率非常高。

存储过程的参数种类：

1、in 输入参数（默认）

2、out 输出参数

3、in out 输入输出参数

存储过程的创建和执行

1、创建存储过程

创建存储过程的语句是CREATE PROCEDURE，语法格式：

create [or replace] procedure pro_name [(parameter1[,parameter2]...)] is|as
begin
     plsql_sentences;
[exception]
     [dowith_sentences;]
end [pro_name];

eg：创建一个存储过程dept_proc并向dept表中插入一条记录（无参存储过程）

create or replace procedure dept_proc is
var_name dept.dname%type;
begin
    var_name:='董事局';
    insert into dept values(95,var_name,'中国');
    commit;
end;
/

创建结果：

结果显示已将创建成功，接下来进行查询，看一下到底是否插入记录了，

select * from dept;

查询结果：

结果显示并没有插入该记录，原因因为我们并没有执行这个存储过程，接下来会介绍存储过程的执行

2、调用存储过程

调用存储过程一般使用EXECUTE语句，但在 PL/SQL块中可以直接使用存储过程的名称来调用。其语法格式如下：

[exec | execute] procedure_name

其中不论使用exec调用还是使用execute调用都是可以的

eg：调用刚才创建的存储过程，并查询

exec dept_proc;

调用结果：

接下来进行查询：select * from dept;

查询结果：

这次很显然插入记录成功

练习：

1、使用存储过程查询指定编号的员工的信息（使用in参数）

create or replace procedure empno_proc(var_empno in int) is
type emp_record is record(
    var_ename emp.ename%type,
    var_job   emp.job%type
);
empinfo emp_record;
begin
    select ename,job into empinfo from emp where empno=var_empno;
    dbms_output.put_line(var_empno||'工号的员工姓名是：'||empinfo.var_ename||',职务是：'||empinfo.var_job);
end;
/

创建结果：

在调用存储过程之前，要先写set serveroutput on进行系统输出，调用存储过程时，必须给存储过程一个参数

exec empno_proc(7369);

查询结果：

2、使用out参数根据员工编号查询其姓名

create or replace procedure empno_proc(var_empno in int,var_ename out varchar2) is
begin
     select ename into var_ename from emp where empno=var_empno;
end;
/

在PL/SQL中调用存储过程：

declare 
     var_good emp.ename%type;
begin
     empno_proc(7369,var_good);
     dbms_output.put_line('查询到的姓名为：'||var_good);
end;
/

查询结果：

3、使用in out类型参数根据员工号查询工资

create or replace procedure empno_proc(var_info in out number) is
begin
     select sal into var_info from emp where empno=var_info;
end;
/

在PL/SQL中调用改存储过程：

declare
    var_empno_info number:=7369;
begin
    empno_proc(var_empno_info);
    dbms_output.put_line('该工号的员工工资为：'||var_empno_info);
end;
/ 

查询结果：

存储过程的修改

      修改存储过程和修改视图一样，虽然也有ALTER PROCEDURE语句，但是它是用于重新编译或验证现有过程。如果要修改过程定义，仍然使用CREATE OR REPLACE PROCEDURE命令，语法格式一样。其实，修改已有过程本质上就是使用CREATE OR REPLACE PROCEDURE重新创建一个新的过程，保持和原来名称一致。

存储过程的删除

当以个存储过程不在需要时，要将此存储过程从内存中删除，以释放相应的内存空间，可以使用下面的语句：

DROP PORCEDURE count_name;

eg：删除存储过程dept_proc

drop procedure dept_proc;

删除结果：

二、触发器

利用SQL语句创建触发器

语法：

create [or replace] trigger tri_name [before | after | instead of] 
tri_event on table_name | view_name | user_name db_name [for each row [when tri_condition]]
begin
     plsql_sentences;
end tri_name;

1、创建语句级触发器

      语句级触发器是针对一条DML语句而引起的触发器执行。在语句级触发器中，不用使用for each row子句，即无论数据操作影响多少行，触发器都只会执行一次。

eg1：使用触发器监听对dept表的操作，并记录到日志表中

建立日志表：

create table dept_log(
     opertime date,
     operdesc varchar2(50)
);

创建触发器：

create or replace trigger dept_trigger
    after insert or update or delete on dept for each row
begin
    if inserting then
         insert into dept_log values(sysdate,'执行了插入操作');
    elsif updating then
         insert into dept_log values(sysdate,'执行了更新操作');
    elsif deleting then
         insert into dept_log values(sysdate,'执行了删除操作');
    end if;
end;
/

创建结果：

插入数据：

insert into dept values(88,'ALICE','JA');

insert into dept values(89,'ALICE','CH');

insert into dept values(90,'ALICE','AM');

更新操作：

update dept set loc='JC' where deptno=88;

删除操作：

delete from dept where deptno=90;

查询dept_log中结果：

每执行一条语句，就会执行一次触发器

eg2：在不指定主键名称的情况下，实现student表主键自增效果

创建学生表：

create table student(
     stuid int primary key,
     stuname varchar2(20),
     score number
);

创建序列：

create sequence my_seq
start with 1
increment by 1;

创建过滤器：

create or replace trigger stu_trigger
before insert on student for each row
begin
    select my_seq.nextval into :new.stuid from dual;
end;
/

创建结果：

插入数据：

insert into student(stuname,score) values('张三'，89.5);

insert into student(stuname,score) values('李四'，77);

insert into student(stuname,score) values('王五'，99);

插入数据之后查询学生表：

此时，学生的stuid是自增的

启用和禁用触发器

Oracle提供了ALTER TRIGGER语句用于启用和禁用触发器，语法格式：

ALTER TRIGGER [schema.]trigger_name DISABLE | ENABLE;

触发器的删除

当一个触发器不在使用时，要从内存中删除它。语法：

删除：DROP TRIGGER tri_name;

当一个触发器已经过时，想重新定义时，不必先删除在创建，同样只需在CREATE语句后面加上OR REPLACE关键字即可。如：

重新定义：CREATE OR REPLACE TRIGGER tri_name;