第九章:plsql编程基础(记录&触发器&存储过程)



- -使用记录
- -因为记录没有自己的值，因此不能测试记录是否为 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 table of 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 or delete 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 or delete 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引用的是表中已有的数据


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


- -语句触发器
- -对于语句触发器而言，每执行一次触发语句，该触发器就会执行一次，也就是说，不管触发语句影响多少数据行，该触发器只会执行一次。为创建语句触发器，应该忽略 FOR EACH 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 or insert 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 may not see it
- -ORA - 06512: at "STUDENT.STUDENT_AU", line 2
- -ORA - 04088: error during execution of trigger 'STUDENT.STUDENT_AU'

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


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

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

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

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

- - EXECUTE IMMEDIATE 语句 
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子句：专门用来给绑定参数传值
- -returning into子句：专门接收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 variables not 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: invalid table 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: invalid character
- -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: line 8, column 12:
- -PLS - 00457: expressions have to be of SQL types
- -ORA - 06550: line 7, column 5:
- -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..定义的
- -游标，而是一个 ref cursor类型的变量。称为游标变量。

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

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

   - -声明游标变量
  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 PROCEDURE name 
           [( 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,course c
       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模式：输出参数，过程负责给这些参数赋值。过程执行完后，
- -调用者就可以从这些参数中拿到值。输出参数必须是变量。

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


- -过程传参的语法：
- - 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;