Oracle学习笔记(第四章) 在PLSQL中更改数据和管理事务

 

这是第四章的学习笔记，学习完第三章的程序控制之后，开始要学习数据更改、集合遍历和事务管理了……

 

编程时使用的工具是PLSQL Developer 7.1.4

每次执行一个SQL DML语句(insert、update或delete)以及返回单选select into时，
PL/SQL都会声明和管理一个隐式游标
隐式游标的相关属性(不能用dbms_output输出)：
sql%found:记录成功获取返回True，否则返回False
sql%notfound:记录获取失败返回True，否则返回False
sql%rowcount:返回已经从游标中获取的记录数
sql%isopen:判断游标是否打开，语句执行完游标就会关闭，所以一般总是返回False
如果会话中还没有隐式游标被执行，那么所有的隐式游标属性都返回NULL，如果有游标
执行了，那么不管SQL语句是在块还是子程序中执行的，属性的值总是指向最近执行的SQL语句

 

Sql代码

1. declare   
2.   n_empno employee.empno%type;  
3.   v_name employee.name %type;  
4.   n_salary employee.salary%type;  
5.   type a_name is  varray(100)  of  varchar2(20);  
6.   type a_salary is  varray(100)  of  number(10,2);  
7.   n_names a_name := a_name('李明' , '赵林' , '晓月' , '钱勇' );  
8.   n_salaries a_salary;  
9. begin   
10.   -- 判断Update语句是否执行成功并返回影响的行数   
11.   update  employee  set  salary=4000  where  empno=2;  
12.   if sql%found then   
13.      dbms_output.put_line('Update语句执行成功！' );  
14.      dbms_output.put_line('Update语句影响的行数是：'  || sql%rowcount);  
15.   else   
16.      dbms_output.put_line('Update语句执行失败！' );  
17.      dbms_output.put_line('Update语句影响的行数是：'  || sql%rowcount);  
18.   end  if;  
19.   -- 可以在insert、update或delete这三条语句之后加上Returning子句，将这三条语句执行之后的信息返回给变量   
20.   n_empno := &输入要加薪员工的编号：;  
21.   select   name   into  v_name  from  employee  where  empno = n_empno;  
22.   update  employee  set  salary = salary + 500  where  empno = n_empno returning salary  into  n_salary;  
23.   if sql%found then   
24.      dbms_output.put_line(v_name || '加薪500元，他加薪后的工资是：'  || n_salary);  
25.   else   
26.      dbms_output.put_line(v_name || '加薪失败！' );  
27.   end  if;  

declare
  n_empno employee.empno%type;
  v_name employee.name%type;
  n_salary employee.salary%type;
  type a_name is varray(100) of varchar2(20);
  type a_salary is varray(100) of number(10,2);
  n_names a_name := a_name('李明','赵林','晓月','钱勇');
  n_salaries a_salary;
begin
  -- 判断Update语句是否执行成功并返回影响的行数
  update employee set salary=4000 where empno=2;
  if sql%found then
     dbms_output.put_line('Update语句执行成功！');
     dbms_output.put_line('Update语句影响的行数是：' || sql%rowcount);
  else
     dbms_output.put_line('Update语句执行失败！');
     dbms_output.put_line('Update语句影响的行数是：' || sql%rowcount);
  end if;
  -- 可以在insert、update或delete这三条语句之后加上Returning子句，将这三条语句执行之后的信息返回给变量
  n_empno := &输入要加薪员工的编号：;
  select name into v_name from employee where empno = n_empno;
  update employee set salary = salary + 500 where empno = n_empno returning salary into n_salary;
  if sql%found then
     dbms_output.put_line(v_name || '加薪500元，他加薪后的工资是：' || n_salary);
  else
     dbms_output.put_line(v_name || '加薪失败！');
  end if;

如果DML语句改变了多行，可以用returning ... bulk collect into val_collection 获得改变多行的信息
Forall 语句后面必须且只能接一个SQL DML语句，它没有Loop和end结束语句
Forall语句只适用于执行批量的insert、update或delete的SQL DML语句
Forall语句中使用的I变量不能直接用在SQL语句的Where条件中，只能把I变量放在集合中使用
而bulk collect子句用于取得批量数据，该子句只能用于Select、Fetch
和insert、update或delete的SQL DML语句的Returning子句中
用于forall 语句的隐式游标的相关属性：
除了支持SQL DML语句的隐式游标属性外还包括其特有的属性：
sql%bulk_rowcount:返回一个集合，来告诉我们通过Forall执行的每个相关SQL语句处理的行数，当%bulk_rowcount(i)为零时，
%found和%notfound分别为false和true
sql%bulk_exception:返回Forall中每DML操作可能抛出的异常信息

Sql代码

1. -- 遍历集合的方法一   
2. forall i in  n_names. first ..n_names. last   
3.    update  employee  set  salary = salary + 500  where   name  = n_names(i) returning salary bulk collect  into  n_salaries;  
4.    dbms_output.put_line('第1个元素更新的行数为：'  || sql%bulk_rowcount(1));  
5. -- 遍历集合的方法二   
6. for  i  in  1..n_salaries. count  loop  
7.   dbms_output.put_line(n_salaries(i));  
8. end  loop;  

  -- 遍历集合的方法一
  forall i in n_names.first..n_names.last
     update employee set salary = salary + 500 where name = n_names(i) returning salary bulk collect into n_salaries;
     dbms_output.put_line('第1个元素更新的行数为：' || sql%bulk_rowcount(1));
  -- 遍历集合的方法二
  for i in 1..n_salaries.count loop
    dbms_output.put_line(n_salaries(i));
  end loop;

事务管理：
管理事务的语句：
Commit：提交改变并释放锁定资源
Rollback：回滚所有的改变并释放锁定资源
SavePoint：设置一个保存点
Rollback to SavePoint：回滚到指定的保存点并释放保存点范围内的锁定资源
Set Transaction：建立隔离级别或分配当前的事务到一个指定的回滚段
设置只读或可读写事务的语法是：
set transaction [read only | read write]
设置一个事务的级别：
set transaction isolation level <isolation level name>
注意：set transaction 必须出现在一个事务的第一条且只能出现一次
set transaction 仅影响当前事务
Lock Table：允许我们使用指定的模式锁住整个数据库表，它将覆盖默认的行级锁

Sql代码

1.    -- 事务开始   
2.   insert   into  Employee  values (EmpSeq.Nextval, '江忠' , '一厂厂长' ,0, '2006-10-21' ,4200, '生产' ,5);  
3.   -- 设置保存点   
4.   savepoint one;  
5.   insert   into  Employee  values (EmpSeq.Nextval, '赵林' , '后勤主任' ,0, '2008-01-05' ,3500, '后勤' ,2);  
6.   insert   into  Employee  values (EmpSeq.Nextval, '晓月' , '财务经理' ,1, '2007-11-16' ,6800, '财务' ,3);  
7.   insert   into  Employee  values (EmpSeq.Nextval, '钱勇' , '销售经理' ,1, '2009-01-09' ,5300, '销售' ,4);  
8.   -- 回滚到指定的保存点   
9.   rollback   to  savepoint one;   
10.   -- 回滚所有的改变   
11.   rollback ;    
12.   -- 提交时可以附加注释信息(当执行DDL、DCL语句和退出SQL*Plus时会自动提交事务)   
13.   commit  comment  '修改员工信息' ;  
14.   -- 所有事务结束   
15.   /*  
16.     四种事务隔离级别：  
17.     未提交读、提交读、可重复读、可串行读   
18.    */  
19. exception  
20.    when  NO_DATA_FOUND  then  dbms_output.put_line( '查询不到员工编号为'  || n_empno ||  '的员工！' );  
21.    when  TOO_MANY_ROWS  then  dbms_output.put_line( '数据完整性错误，员工编号'  || n_empno ||  '重复！' );  
22.    when  OTHERS  then  dbms_output.put_line( 'PL/SQL执行错误！'  || sqlerrm);  
23. end ;  
24. /