关于Oracle和PLSQL的学习记录11

------事务处理和并发控制------
1.事务是Oracle中进行数据库操作的基本单位

2.事务开始
事务是由一个或多个SQL语句序列结合在一起所形成的一个逻辑处理单元
Oracle中的开始事务不需要通过类似begin等语句的显式表示，而是从执行第一条对表进行操作的DML语句开始

3.提交事务
在Oracle中，提交事务有两种方式，其表现形式分别如下：
commit 该语句是Oracle PLSQL显式提交事务的语句，执行commit语句后之前的DML语句组成的事务结束，数据写入对象中
DDL/DCL语句：如果在DML语句后执行了DDL(数据定义语句，如create\drop等)或DCL(数据控制语句，如grant\revoke等)语句，事务被提交

事务提交时事务结束的正常表现之一，还有多种原因可能造成事务结束，例如数据库连接断开、服务器宕机等

4.回滚事务
即当事务还没有提交时数据库服务器异常关闭了，那么事务中的所有操作都会被撤销
当用户要撤销事务中对数据的操作时，也可以选择手动回滚事务使数据表回到事务开始前的状态
update stu set sage = sage + 1;
delete from stu where sno = '100002';
rollback;
事务回滚仅仅是在逻辑上撤销事务操作，用户在数据库级所做的一些操作不会回到事务开始前的状态

5.设置保存点
上例中将事务开始回滚前的2个操作全部撤销了，这种操作称为回滚全部事务
在具体数据库操作中，事务中包含的操作往往很多，当用户需要回滚事务时，只需要撤销事务中的部分操作而非所有操作，此时就需要为事务设置保存点
update stu set sage = sage - 1;
savepoint updatepoin;
delete from stu where sno = '10001';
在Oracle中，savepoint时设置保存点命令，通常与rollback一起使用
即使设置了保存点，也并不代表保存点之前的操作不需要commit提交了，只有提交事务才能对数据表造成影响

6.回滚部分事务
创建保存点后，用户可以继续执行其他sql语句，如果用户需要撤销事务的部分操作，就可以通过回滚至保存点来实现
uodate stu set sage = sage + 1;
savapoint updatepoin;
delete from stu where sno = '10001';
rollback to updatepoin;
保存点之前对stu表做的操作不会被撤销
虽然没有撤销，但这些操作要写入数据表中还是必须通过commit提交事务

7.DDL语句对事务的影响
事实上，Oracle的事务从第一条SQL语句开始，碰到第一条DDL语句则结束事务
因此，可以认为DDL语句能够提交事务
insert into stu values('120009','里斯','男',20,'12艺术设计',to_date('21-08-1989','DD-MM-YYYY'));
alter table stu disable all triggers;
rollback;
上述当执行rollback试图撤销insert时是无法撤销的

在Oracle PLSQL中的DDL语句包含create、alter、drop、rename和truncate等5中语句

8.DCL语句提交事务
delete from stu where sno = '12008';
grant connect to test;
rollback;
同样，无法撤销delete语句操作

在Oracle PLSQL中的DCL语句包含grant授权语句和revoke收回授权两种语句

9.断开数据库连接对事务的影响
用户正常断开与数据库的连接时，Oracle会自动提交前面未被提交的事务，所有对数据表的操作都生效

10.异常断开数据库连接回滚事务
当用户异常断开数据库连接的时候，事务不会自动提交，而是被回滚

总的来说，Oracle数据库的事务是由一个或多个DML语句组成，事务中可以包含一个DDL/DCL语句；Oracle可以自动地标识一个事务，事务是以第一个可执行地SQL语句开始，当下列事件之一发生时结束：
用户执行了commit语句，事务被显式提交
用户执行了rollback语句，事务被回滚
用户执行了DDL语句，事务被自动提交
用户执行了DCL语句，事务被自动提交
用户正常退出，事务被自动提交
用户非正常退出，事务被自动回滚
系统崩溃，包括硬件或软件故障，事务被自动回滚

11.避免脏读
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库，这时，另外一个事务也在访问这个数据，然后使用了这个数据
例如：
里斯的年龄为20，事务A中把他的年龄改为21，但是事务A尚未提交；同时，事务B正在读取里斯的年龄，读取到里斯的年龄为21；随后事务A发生异常，而回滚了事务，里斯的年龄又回滚回了20；最后事务B读取到的里斯年龄为21的数据即为脏数据，事务做了一次脏读

事实上，Oracle目前不支持脏读，对于未提交的数据只能取到原始数据，没有提交的更新不能出现在用户的查询结果中
Oracle在进行并发控制时，为了保证数据的一致性和完整性不支持脏读，但也降低了部分并发性能

12.避免不可重复读
不可重复读，是指在一个事务内，多次读同一数据；然后在这个事务还没有结束的时候，另外一个事务也访问该同一数据；那么在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的，这是不允许的
例如：
里斯的年龄为20，事务A第一次读取时该值为20；随后，事务B对里斯的年龄进行了修改，将其值改为21并提交；最后，事务A再一次读取到里斯的年龄，此时的值就变为21了；事务A两次对里斯年龄的读取不同，构成了不可重复读

select * from stu where sname = '里斯' for update;
update stu set sage = 21 where sname = '里斯';
第一个执行的时候加上for update表示为该语句查询到的记录加上锁，其他事务不能再对该记录进行update操作，因此执行第二个操作update的时候被卡住，呈现假死状态

从程序员的角度看，Oracle提供的锁机制分为乐观锁和悲观锁；Oracle的悲观锁分成两种方式，从SQL语句的区别来看，就是for update和for update nowait两种形式

Oracle用的是行级锁，只是对想要锁定的数据才进行锁定，其余的数据不相干，所以在对Oracle表中并发插数据的时候，基本上不会有任何影响

13.判断数据是否被锁
select * from stu where sname = '里斯' for update;
select * from stu where sname = '里斯' for update nowait;
通过带nowait参数的select语句检测当前操作的数据是否被锁住，当发现数据被别的session锁定的时候，就会迅速的返回ora-00054错误，不让用户对该数据进行update等操作，避免了假死的可能

14.锁定数据表
Oracle表级锁，对整个数据表进行锁定，不允许其他事务对该表进行任何操作
lock table stu in exclusive mode;

使用Oracle提供的lock table命令将一个基本表stu锁定了，表被锁定之后，其他事务不能再对该表进行DML操作，否则该事务将会出现假死状态，但对锁定表执行select等操作是可以的
参数in exclusive mode表示以独占(排他)模式锁定表，即该表之恶能由该事务来访问和操作，不允许其他事务对其进行操作

15.查看被锁定的数据对象
Oracle提供了试图V$LOCKED_OBJECT用于监控锁的运行状态
select oracle_username,object_id,session_id from v$locked_object;
select object_name,object_type from dba_objects where object_id = 55332;
第二个查询中的条件是第一个查询查出来的结果

16.为被锁定表解锁
查询被锁定对象的进程号：
select sid,serial#,program from v$session where sid = 148;
使用kill结束对应的进程：
alter system kill session '148,1009';
需要重复执行，直到视图v$locked_object中被锁定stu表所对应的session_id全部被结束为止

事实上，只要不是异常关闭数据库连接，锁定的数据对象会在重新连接后自动解锁

17.创建程序包
程序包是一组相关过程、函数、变量、常量和游标等PLSQL程序设计元素的组合，作为一个完整的单元存储在数据库中，用名称来标识包
create or replace package stu_pkg
is
  stu_rec stu%rowtype;
  procedure delete_stu(sno in number);
  procedure query_stu(sno in number);
end stu_pkg;

create or replace package body stu_pkg
is
procedure delete_stu(sno in number)
is
begin
  delete from stu where sno = sno;
  if sql%found then
    dbms_output.put_line('数据库中没有学号为'||sno||'的学生');
  else
    dbms_output.put_line('删除成功');
  end if;
exception
  when others then
    dbms_output.put_line('操作异常');
end delete_stu;
procedure query_stu(sno in number)
is
begin
  select * into stu_rec from stu where sno = sno;
exception
  when no_data_found then
    dbms_output.put_line('数据库没有学号为'||sno||'的学生');
  when too_many_rows then
    dbms_output.put_line('程序运行错误，请使用游标进行操作');
  when others then
    dbms_output.put_line(sqlcode || '----' || sqlerrm);
end query_stu;
begin
  null;
end stu_pkg;
包创建完成后，可以通过对数据字典all_source的查询操作查看其名称和类型

为了实现信息的隐藏，建议不要将所有组件都放在包说明处声明，只应该把公共组件放在包声明部分；此外，在包主体定义共有程序时，他们必须与包定义中所声明子程序的格式完全一致

18.使用程序包
set serveroutput on;
begin
  stu_pkg.query_stu('12001');
  dbms_output.put_line(stu_pkg.stu_rec.sno||'----'||stu_pkg.stu_rec.sname||'----'||stu_pkg.stu_rec.sage||'----'||stu.pkg.stu_rec.sdept);
end;
包体只能在包说明被创建或编译后才能进行创建或编译，在胞体中实现的过程、函数、游标的名称必须与包说明中的过程、函数、游标一致

19.查看程序包
select text from all_source where name = upper('stu_pkg');

20.删除程序包
可以选择只删除包体或者删除整个包；如果只删除包体，那么包定义仍然存储在数据库中，供用户定义其中的过程和函数；如果删除整个包，则该包所有的信息都从数据库中移除了
drop package body stu_pkg;
drop package stu_pkg;

在创建程序包时，头部和包体是分别创建的，并且头部必须在包体之前创建，而删除包时不需要遵循该规则，可以单独删除包体，也可以删除整个包，但不能只删除包定义，否则包体将变成无用的数据