第九章:记录&触发器&存储过程



--使用记录
--因为记录没有自己的值，因此不能测试记录是否为null、
--是否相等或者不相等。以下语句是非法的：
IF course_rec  IS NULL  THEN ……
IF course_rec1 = course_rec2 THEN ……

--用户定义的记录
--创建用户定义记录的通用语法如下所示(方括号中的内容是可选的): 
type  type_name  is  record 
(field_name1  datatype1 [not  null] [:= 默认值] , 
field_name2  datatype2  [not  null] [:= 默认值]  , 
……
field_nameN  datatypeN  [not  null] [:= 默认值]) ;

record_name  type_name;

--了解：
--记录的兼容性
--用户定义记录的约束及默认值
--封装记录的使用

--包含记录的集合。重点

declare
  --声明游标
  cursor name_cur is
    select first_name,last_name
      from student
      where rownum<=4;

  --声明索引表集合类型.其中放基于游标的记录。
  --注意：name_cur%rowtype就是记录类型的名字
  type name_type is tableof name_cur%rowtype
    index by pls_integer;

  --声明集合变量
  name_tab name_type;

  v_index pls_integer := 0;
begin
  --遍历游标，将拿到4条的记录放到集合中
  for name_rec in name_cur loop
    v_index := v_index + 1;
    name_tab(v_index) := name_rec;  
  end loop;

  --从集合中取出记录并打印
  for i in 1..name_tab.count loop
      dbms_output.put_line(name_tab(i).first_name);
      dbms_output.put_line(name_tab(i).last_name);
  end loop;
end;

--创建使用触发器
--数据库触发器是存储在数据库中的命名PL/SQL语句块，
--当触发事件发生时它们会隐含地执行。

--触发事件可以是如下任何一种：
--DML语句(如INSERT、UPDATE或者DELETE)。
--DDL语句(如CREATE或者ALTER)。
--系统事件，如数据库启动或者关闭
--用户事件，如登录和注销。

--创建触发器的通用语法如下所示（中括号中的保留字是可选的）：
CREATE [OR REPLACE]TRIGGER  trigger_name
{BEFORE|AFTER}  Triggering_event   ON  table_name
[FOR EACH ROW]
[FOLLOWS  another_trigger]
[ENABLE/DISABLE]
[WHEN condition]
DECLARE
     declaration  statements
BEGIN
     executable  statements
EXCEPTION
     exception-handling  statements
END;

--写触发器的限制：
--1）不要在触发器中使用commit、rollback等事务控制语句。
--因为触发器执行的操作是和客户端在同一个事务中执行的，
--事务的结束应该由客户端来控制。这个规则的一个例外是：
--在触发器中使用自治事务

--2）不要在触发器中使用long类型的变量


--before触发器。
--是指触发器的执行时间是在dml操作执行之前。

--示例1：这个触发器针对STUDENT表的INSERT语句之前执行，
--填充STUDENT_ID、CREATED_DATE、MODIFIED_DATE、
--CREATED_BY和MODIFIED_BY等列

create or replace trigger student_bi
  before insert on student  
  for each row
begin
  --直接给新行的5个列赋值
  :new.student_id := student_id_seq.nextval;
  :new.created_by :=user;
  :new.created_date := sysdate;
  :new.modified_by :=user;
  :new.modified_date := sysdate;

  dbms_output.put_line('触发器执行完毕');
end student_bi;

--客户端测试语句：
INSERT INTO student(first_name, last_name, zip, registration_date)VALUES ('John','Smith', '00914', SYSDATE);

--观察某些列值会被触发器提供的值覆盖掉 
INSERT INTO student(student_id, first_name, 
last_name, zip,registration_date, created_by,
  created_date, modified_by,modified_date) 
  VALUES (800,'John', 'Smith','00914', SYSDATE, 
   '张三', SYSDATE, '张三', SYSDATE);


--after触发器：在dml语句执行完后再执行
--示例2：建立针对INSTRUCTOR表的UPDATE或者
--DELETE操作执行之后触发的触发器。该触发器
--在statistics表中记录用户对INSTRUCTOR表进行的最后修改
--信息

create table statistics(
  TABLE_NAME      VARCHAR2(30),
  TRANSACTION_NAME    VARCHAR2(10),
  TRANSACTION_USER   VARCHAR2(30),
  TRANSACTION_DATE  DATE
);

create or replace trigger instructor_aud
  after update ordelete on instructor  

declare
  --
  v_type varchar2(10);
begin
  --确定用户的操作
  if updating then
    v_type := 'UPDATE';  
  elsif deleting then
    v_type := 'DELETE';
  end if;

  --更新统计表
  update statistics 
    set transaction_user = USER, 
        transaction_date = SYSDATE
    WHERE table_name = 'INSTRUCTOR'
      AND transaction_name = v_type; 

  --判断更新是否成功，如果没有，则插入一行
  insert into statistics 
    values('INSTRUCTOR', v_type,USER, SYSDATE);    

  dbms_output.put_line('记录完毕');     
end instructor_aud;


--客户端测试
update instructor
  set first_name='zs'
  where instructor_id=107;

delete from instructor
  where instructor_id=110;  

--可看到两条记录
select * from statistics;

--客户端回滚
rollback;

--看到触发器所做的工作也被撤销了
select * from statistics;


--问题：当客户端撤销事务时，如何保留触发器所做的
--工作而不被同时撤销掉？
--在触发器中使用自治事务

--自治事务：
--是由其它事务(通常被称为主事务)发起的独立事务。
--也就是说，自治事务也许会执行多个DML语句，
--并且提交或者回滚操作，而不会提交或者回滚主事务
--执行的DML语句

--修改上面的触发器，使用自治事务
create or replace trigger instructor_aud
  after update ordelete on instructor  

declare
  --
  v_type varchar2(10);
  --声明使用自治事务
  pragma autonomous_transaction;
begin
  --确定用户的操作
  if updating then
    v_type := 'UPDATE';  
  elsif deleting then
    v_type := 'DELETE';
  end if;

  --更新统计表
  update statistics 
    set transaction_user = USER, 
        transaction_date = SYSDATE
    WHERE table_name = 'INSTRUCTOR'
      AND transaction_name = v_type; 

  --判断更新是否成功，如果没有，则插入一行
  if sql%notfound then
    insert into statistics 
     values('INSTRUCTOR', v_type,USER, SYSDATE);    
  end if;

  dbms_output.put_line('记录完毕'); 

  --提交自治事务
  commit;    
end instructor_aud;


--客户端测试
update instructor
  set first_name='zs'
  where instructor_id=107;

delete from instructor
  where instructor_id=110;  

--可看到两条记录
select * from statistics;

--客户端回滚
rollback;

--看到触发器所做的工作仍然保留
select * from statistics;


--示例4：理解触发器
CREATE TRIGGER student_au 
AFTER UPDATE ON STUDENT 
FOR EACH ROW 
WHEN (NVL(NEW.ZIP,'') <> OLD.ZIP) 
         Trigger Body.. .
--在WHEN子句中，使用伪记录:OLD可以访问当前被处
--理的数据行。要注意，当在WHEN子句的条件中使用时，
-- :NEW和:OLD都不再使用冒号作为前缀

--对于update操作，即可以使用:new,也可以使用:old
UPDATE student SET zip = '01247' WHERE zip = '02189'; 
--ZIP列的值01247是个新值，并且触发器使用:NEW.ZIP来引
--用它。02189是ZIP列的先前值，使用:OLD.ZIP来引用

--对于INSERT语句而言， :OLD是未定义的；
--对于DELETE语句而言， :NEW是未定义的。
--当触发事件是INSERT或者DELETE时，如果在触发器中分别
--使用:OLD或者:NEW，PL/SQL编译器并不会产生语法错误，
--在这种情况下， :OLD或者:NEW伪记录的字段值会被
--设置为NULL

--简单的说，:new引用的是将要写入表中的数据，
--而:old引用的是表中已有的数据


--行触发器
--行触发器指的是触发器被触发的次数等同于触发语句所影响的行数。当子句FOREACH ROW出现在CREATETRIGGER语句中，该触发器就是行触发器。例如：
CREATE OR REPLACE TRIGGER course_au
AFTER UPDATE ON course
FOR EACH ROW
……
--该触发器是行触发器。如果某UPDATE语句导致COURSE表
--中20条记录被修改，则该触发器会执行20次


--语句触发器
--对于语句触发器而言，每执行一次触发语句，该触发器就会执行一次，也就是说，不管触发语句影响多少数据行，该触发器只会执行一次。为创建语句触发器，应该忽略FOREACH ROW子句。例如：
CREATE OR REPLACE TRIGGER enrollment_ad
AFTER UPDATE ON enrollment
……
--当对ENROLLMENT表执行一个DELETE语句时，该触发器就
--会执行一次。不管该DELETE语句从ENROLLMENT表删除1行
--或者多行数据，该触发器都只会触发一次


--问题：写触发器时如何确定是写成行级的还是语句的？
--如果在触发器中没有使用到:new或者:old，就写成语句级的。


--如果希望限制只能在上班时间访问某个表，
--就应该使用语句触发器
--示例8：限制客户只能在上班时间访问instructor表
create or replace trigger instructor_buid
before update orinsert or delete
on instructor
declare
  v_day varchar2(9);
begin
  --得到服务器当前日期所代表的那一天的名字
  v_day := rtrim(to_char(sysdate,'DAY'));

  --判断是周六日吗？
  if v_day in ('SATURDAY','SUNDAY')then
    raise_application_error(-20000,'周六日应该休息');
  end if;  
end;

--记住：触发器抛异常就可以阻止客户端事务的完成。


--触发器的其它内容：
--1、变异表问题：
--dml操作正在处理的表叫做变异表。对于触发器而言，
--就是在其上定义触发器的表叫变异表。
--变异表问题专门针对行级触发器：对于行级触发器，不允许
--在触发器体中访问变异表。

create trigger student_au
after update on student
for each row
begin
  --不能修改student表
  update student
    set first_name='zs'
    where student_id=102;
end student_au;

--客户端测试
update student
    set first_name='ls'
    where student_id=101;

--抛出异常：
---ORA-04091:table STUDENT.STUDENT is mutating, trigger/function maynot see it
--ORA-06512:at "STUDENT.STUDENT_AU", line 2
--ORA-04088: error during executionof trigger 'STUDENT.STUDENT_AU'

--2、11g新增的复合触发器


--处理动态sql语句
--动态SQL： 
--SQL语句在程序被编译时是未知的（例如，包含变量），
--涉及的数据库对象（例如，表）可以是运行时才创建的

--为处理动态 SQL语句，需要使用EXECUTE IMMEDIAT
--或者OPENFOR语句

--EXECUTEIMMEDIATE被用于单行SELECT语句、
--所有的DML和DDL语句。

--OPENFOR语句用于多行SELECT语句以及引用游标

--EXECUTEIMMEDIATE 语句 
EXECUTE  IMMEDIATE  dynamic_SQL_string
[INTO defined_variable1, defined_variable2, ...]
[USING [IN |OUT | IN OUT] bind_argument1, bind_argument2,...]
[{RETURNING | RETURN} INTO bind_argument1, bind_argument2, ...]

--intio子句：专门接收select语句返回行（只有一行）的
--各个列值
--using子句：专门用来给绑定参数传值
--returninginto子句：专门接收dml语句中returning into
--子句返回的列值


--示例1：
DECLARE 
    sql_stmt  VARCHAR2(100); 
    plsql_block  VARCHAR2(300); 
    v_zip  VARCHAR2(5) :='11106'; 
    v_total_students  NUMBER; 
    v_new_zip  VARCHAR2(5); 
    v_student_id  NUMBER :=151; 
begin
  --1/创建my_student表
  --拼凑动态sql语句
  sql_stmt := 'create table my_student '||
              ' as select * from student where zip='||v_zip;

  --执行动态sql
  execute immediate sql_stmt;


  --2、查询my_student表中的学生人数并打印
  execute immediate 'select count(*) from my_student'
    into v_total_students;

  dbms_output.put_line('学生人数是：'||v_total_students);


  --3、更新my_student表的记录，并得到更新后的列值
  sql_stmt := 'update my_student set zip=11105 '||
              'where student_id=:1 returning zip into :2';

  execute immediate sql_stmt 
    using v_student_id
    returning into v_new_zip;

  dbms_output.put_line('新的邮编值：'||v_new_zip);  
end;

--当使用  EXECUTE  IMMEDIATE语句时，
--要避免常见的ORA错误
--示例2：不正确的例子
--ORA-01027: bind variablesnot allowed for data definition operations
--ORA-06512:at line 12
DECLARE 
    sql_stmt  VARCHAR2(100);   
    v_zip  VARCHAR2(5) :='11106';    
begin
  --1/创建my_student表
  --拼凑动态sql语句
  sql_stmt := 'create table my_student1 '||
              ' as select * from student where zip=:zip';

  --执行动态sql
  execute immediate sql_stmt
    using v_zip;
end;

--结论：动态ddl语句中不允许使用绑定变量。但是
--使用普通变量没问题


--ORA-00903: invalidtable name
--ORA-06512:at line 11
DECLARE 
    sql_stmt  VARCHAR2(100);   
    v_zip  VARCHAR2(5) :='11106'; 
    v_total_students  NUMBER;    
begin  
  --表名使用绑定变量
  sql_stmt := 'select count(*) from :my_table';

  --执行动态sql
  execute immediate sql_stmt
    into v_total_students
    using 'my_student';
end;

--结论：不能把表的名称作为绑定参数传递给动态SQL语句。
--为了在运行时提供表的名称，需要使用字符串连接的写法：
EXECUTE IMMEDIATE 'SELECT  COUNT(*)  FROM'||my_table
INTO  v_total_students;


--动态SQL语句的结尾不应该有分号(;) 
--ORA-00911: invalidcharacter
--ORA-06512:at line 11
DECLARE 
    sql_stmt  VARCHAR2(100);   
    v_zip  VARCHAR2(5) :='11106'; 
    v_total_students  NUMBER;    
begin  
  --表名使用绑定变量
  sql_stmt := 'select count(*) from my_student;';

  --执行动态sql
  execute immediate sql_stmt
    into v_total_students;    
end;

--如何给绑定变量传null值？
--示例5：
--ORA-06550: line8, column12:
--PLS-00457: expressions haveto be of SQL types
--ORA-06550: line7, column5:
--PL/SQL:Statement ignored
DECLARE 
    sql_stmt VARCHAR2(100); 
BEGIN 
    sql_stmt := 'UPDATE course'|| 
              ' SET prerequisite = :some_value'; 
    EXECUTE IMMEDIATE sql_stmt 
    USING  NULL; 
end;

--可见，不能使用字面量null来给绑定变量传递null值。
--变通方法是：声明一个未初始化的变量，再将该变量
--传递给绑定变量。
DECLARE 
    sql_stmt VARCHAR2(100); 
    v_null varchar2(1);
BEGIN 
    sql_stmt := 'UPDATE course'|| 
              ' SET prerequisite = :some_value'; 
    EXECUTE IMMEDIATE sql_stmt 
    USING  v_null; 
end;


--为了处理返回多行结果集的动态select语句，
--需要使用open  for语句以游标的形式进行处理

--语法：
--OPEN  cursor_variable  FOR  dynamic_SQL_string 
--[USING  bind_argument1 ,  bind_argument2 ,  ...]

--注意：cursor_variable不是使用cursor...is..定义的
--游标，而是一个refcursor类型的变量。称为游标变量。

--所以，为了使用open  for语句，首先应该定义一个游标变量。

--输入11236测试
declare
  --声明refcursor类型
  type student_cur_type is refcursor;

  --声明游标变量
  student_cur student_cur_type;

  v_zip  VARCHAR2(5) :='&sv_zip'; 
  v_first_name  VARCHAR2(25); 
  v_last_name  VARCHAR2(25);   
begin
  --打开游标变量，使其和某个动态select查询的结果集关联
  open student_cur for 'select first_name,last_name '||
    ' from student where zip=:1' using v_zip;

  --游标检索
  loop
    fetch student_cur into v_first_name,v_last_name;
    exit when student_cur%notfound;

    --处理拿到的行
    dbms_output.put_line(v_first_name||' '||v_last_name);
  end loop;

  --关闭游标变量
  close student_cur;
end;


--存储过程
--过程是可以执行一个或者多个动作的命名模块，
--它不需要返回任何值
--创建过程的语法如下所示: 
CREATE OR REPLACE PROCEDUREname 
           [(parameter [, parameter , ...])] 
      { IS | AS }
           [local declarations] 
       BEGIN 
           executable statements 
       [EXCEPTION 
           exception handlers] 
       END [name]; 

--创建一个过程，对于有8个以上学生学习的课程，将其费用
--打95折
create or replace procedure discount 
is
  --声明游标，保存需要打折的课程编号及名称
  cursor c_group_discount is
    select distinct c.course_no,c.description
      from enrollment e,section s,coursec
      where e.section_id = s.section_id
        and s.course_no = c.course_no
        group by c.course_no,c.description,s.section_id
        having count(*)>=8;  
begin
  --遍历游标，取出每个课程编号，进行打折
  for r_discount in c_group_discount loop
    update course
      set cost = cost*0.95
      where course_no=r_discount.course_no;

    dbms_output.put_line('95折给了课程：'||
      r_discount.course_no||' '||r_discount.description);  

  end loop;

  rollback;
end discount;


--如何调用存储过程？
--1、直接在plsql块中以名字来调用
begin
  discount;
end;

--2、直接在sqlpls中使用execute命令调用
execute discount;

--3、在Java程序中使用jdbc调用


--存储过程和事务：
--因为过程主要是提供给客户端调用的，客户端是事务的
--发起者，事务的结束应该由客户端来决定。所以，
--在过程中一般不使用commit或者rollback。


--从数据字典查询有关过程的信息
select object_name,object_type,status
  from user_objects
  where object_name='DISCOUNT';


--显示discount过程的源代码
select text
  from user_source
  where name='DISCOUNT';


--关于过程的参数：
--有3中模式：
--in模式：输入参数，用来给过程传值，值可以是字面量或者
--已赋值的变量

--out模式：输出参数，过程负责给这些参数赋值。过程执行完后，
--调用者就可以从这些参数中拿到值。输出参数必须是变量。

--inout模式：输入输出参数。必须是变量。一方面用来给
--过程传递一个值，另一方面过程也将修改该参数的值。


--过程传参的语法：
--1）位置表示法
--2）命名表示法

create or replace procedure find_sname(
  i_student_id in number,
  o_first_name out varchar2,
  o_last_name out varchar2
)
is  
begin
  select first_name,last_name
    into o_first_name,o_last_name
    from student
    where student_id=i_student_id;
exception
  when no_data_found then
    DBMS_OUTPUT.PUT_LINE('Error in finding student_id: '||i_student_id);
end find_sname;

--测试
declare
  v_first_name varchar2(25);
  v_last_name varchar2(25);
begin
  --位置表示法传参
  find_sname(145,v_first_name,v_last_name);  
  dbms_output.put_line(v_first_name||v_last_name);

  --命名表示法传参
  find_sname(i_student_id =>145,
             o_first_name => v_first_name,
             o_last_name => v_last_name);  
  dbms_output.put_line(v_first_name||v_last_name);

end;



--使用输入输出参数
create or replace procedure test_io(
  io_greeting in out varchar2
)
is  
begin
  --首先使用输入的值
  dbms_output.put_line('你给我的是：'||io_greeting);

  --给参数重新赋值
  io_greeting := '我给你的是：bye bye';
end test_io;


--测试
declare
  v_greeting varchar2(20) :='你好';
begin
  test_io(io_greeting => v_greeting);
  dbms_output.put_line(v_greeting);
end;