Oracle--存储过程、触发器

一、存储过程

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

存储过程的参数种类：
   1. in 输入参数(默认)
   2. out 输出参数

   3.  in out 输入输出参数

1、创建存储过程

create [or replace] procedure pro_name [(parameter1[,parameter2]…)] is|as
begin
  plsql_sentences;
[exception]
  [dowith _ sentences;]
end [pro_name];

2、调用存储过程

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

[exec|execute] procedure_name

3、存储过程的删除

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

DROP PROCEDURE count_num;

1.使用存储过程插入dept表一条记录(无参存储过程)。
  create or replace procedure dept_proc is
    var_dname dept.dname%type;
  begin
    var_dname:='总裁部';
    insert into dept values(95,var_dname,'中国');
    commit;
  end;
  /v
  
2.使用存储过程查询指定编号的员工信息(使用了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;
  /

3.根据员工编号查询其姓名(使用了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;
  /

4.使用in out类型参数根据工号查询工资。
  create or replace procedure emp_proc(var_info in out number) is
  begin
    select sal into var_info from emp where empno=var_info;
  end;
  /

  在PL/SQL中调用该存储过程：
  declare
    var_emp_info number:=7369;
  begin
    emp_proc(var_emp_info);
    dbms_output.put_line('该工号的员工工资是：'||var_emp_info);
  end;
  /

二、触发器

1、创建触发器

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;

加上for each row 为行级语句，即为SQL语句影响多少行，触发多少次；

如果不加for each row为语句级，即为有几个SQL语句触发几次。

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

删除：DROP TRIGGER my_trigger;

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

重新定义：CREATE OR REPLACE TRIGGER my_trigger;

1.使用触发器监听对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,'执行了insert操作');
     elsif updating then
        insert into dept_log values(sysdate,'执行了update操作');
     elsif deleting then
        insert into dept_log values(sysdate,'执行了delete操作');
     end if;
  end;
  /

三、游标

1、显示游标

    显示游标是由用户声明和操作的一种游标，通常用于操作查询结果集（即由SELECT语句返回的查询结果），使用它处理数据的步骤包括：声明游标、打开游标、读取游标和关闭游标4个步骤。其中读取游标可能是个反复操作的步骤，因为游标每次只能读取一行数据，所以对于多条记录，需要反复读取，直到游标读取不到数据为止，其操作过程如图所示。

(1)声明游标语法

declare cursor cur_name

       is 

       select_ sentence;

(2)打开游标语法

(3)读取游标语法

(4)关闭游标语法

2、隐式游标

        在执行一个SQL语句时，Oracle会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。隐式游标主要是处理数据操纵语句（如，UPDATE、DELETE语句）的执行结果，当然特殊情况下，也可以处理SELECT语句的查询结果。由于隐式游标也有属性，当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称——SQL。