Oracle 存储过程，触发器，事务，锁

1.1存储过程

　　存储过程是一种命名的PL/SQL程序块，他可以有参数，也可以有若干个输入、输出参数。甚至可以有多个即做输入又做输出的参数，但他都没有返回值。存储过程被保存在数据库中，他不可以被SQL语句直接执行调用。通过EXECUTE命令或在PL/SQL命令中调用，因为存储过程是已经编译好的代码块，所以被调用或引用时，执行效率很高。

1.1.1 存储过程的创建和执行

　　用户的储存过程只能定义在当前数据库中，用户创建的储存过程归登录数据库的用户拥有。单DBA可以把许可授权给其他用户。在存储过程的定义体中不可以使用下列对象：

　　CREATE VIEW　　CREATE DEFAULT　　CREATE RULE　　CREATE PROCEDURE　　CREATE TRIGGER

1.1.2创建储存过程

create [or replace] procedure pro_name [(parameter1[,parameter2]...)]  is|as
begin
    plsql_sentences;
[exception]
    [dowith_sentences;]
end [pro_name];

1.pro_name:存储过程的名称，如果数据库中已经存在此名称。则可以指定"or replace" 关键字，这样心得储存过程将覆盖原来的存储过程。
2.parameter:存储过程的参数，若是输入参数则在后面添加IN关键字，输出则添加OUT关键字，IN或OUT后面是参数的数据类型，但不能指定该类型的长度。
3.plsql_sentences:PL/SQL语句。
4.dowith_sentences：异常处理语句。

※：parameter是存储过程定义的参数，而不是存储过程定义的内部变量，内部变量要在"is|as"关键字后面定义，并使用分号结束

create or replace procedure pro_insertdept is 
begin
    insert into dept values(77,'市场拓展部','JILIN');    --插入数据记录
    commit;            --提交数据
    dbms_output.put_line("插入记录成功");    --提示插入记录成功
end pro_insertdep;        

execute pro_insertdept;    --execute命令执行pro_insertdep存储过程

--修改可以使用ALTER PROCEDURE语句，也可以用CREATE OR REPLACE重新创建一个新的过程

drop procedure pro_insertdep;    --删除存储过程pro_insertdep

 1.2触发器

　　触发器可以被看做是一种特殊的存储过程，他定义了一些与数据库相关事件（insert update create）等事件，通常用来管理复杂的管理约束，或监控对表的修改，或通知其他程序。可以实现对数据审计的效果。

1.2.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;

1.before|after|instead of:表示"出发时机"的关键字。before表示执行DML操作之前触发，便于防止某些错误操作发生而便于回滚或实现某些业务规则；after表示在DML等操作之后发生。方便与记录该操作或某些时候处理信息；
instead of触发器替代触发器。
2.for each row ：指定触发器为行级触发器，当DML语句对每一行数据都操作时会引起触发器的运行。如果未指定该条件。则表示创建语句触发器无论数据影响多少行，触发器只执行一次。
3. tri_event：出发事件比如常用INSERT UPDATE DELETE DROP ALTER

触发器的分类：

1.语句触发器　　2.替换触发器　　3.系统事件触发器

SQL>create table dept_log(
    operate_tag varchar2(20),
    operate_time date
);
create or replace trigger tri_dept
    before insert of update of delete on dept    --创建触发器、当DEPT表执行操作类型
declare
    var_tag varchar2(20);    --声明变量
begin
    if inserting then    --当触发事件INSERT时
        var_tag:="插入";
    elseif updating then     --当触发事件UPDATE时
        var_tag:="更新";
    elseif deleting then    --当触发事件DELETE时
        var_tag:="删除";
    end if;
    insert into dept_log values(var_tag,sysdate);
end tri_dept;
/

if updating(dname) then 　　--当修改dept表中dname列
　　do something about update danme
end if;

view_dept_emp视图insert事件触发器
create or replace trigger tri_insert_view
    instead of insert
    on view_emp_dept    --创建一个关于视图的替换触发器
    for each row    --行级视图
declare
    row_dept dept%rowtype;
begin   
    select * into row_dept where deptno =:new.deptno;    --检索指定部门编号
    if sql%notfound then    --未检测到该部门编号
        insert into dept(deptno,dname) values(:new.deptno,:new.dname)    --向dept表插入数据
    end if;
    insert into emp(empno,ename,deptno,job,hiredate) values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate);
end tri_insert_view;
/

系统事件触发器
创建before drop触发器，在用户删除对象之前将记录插入信息表dropped_object中
create or replace trigger obj_tri
    before drop on system.schema
begin
    insert into dropped_objec values(ora_dict_obj_name,ora_dict_obj_type,SYSDATE);
end;
删除system模式下对象时，dropped_objec表中插入信息
drop trigger my_trigger;　　--删除触发器

1.3事务

1.3.1事务特性

　　事务特性：称为ACID（原子性，一致性，隔离性，持久性）。

　　事务状态：活动状态，部分提交状态，失败状态，提交状态，终止状态

1.3.2事务处理

　　1.执行commit语句提交事务。

　　2.指定rollback语句撤销事务。

　　3.执行一条数据定义语句，比如CREATE、DROP或者REVOKE等操作命令，如果执行成功，会自动COMMIT命令。否则执行ROLLBACK.

　　4.执行一个数据控制命令，比如GRANT,REVOKE等控制命令，Oracle自动COMMIT命令；

　　5.正常断开数据库，Oracle结束一个事务时，自动COMMIT命令要么执行ROLLBACK。

1.3.3设置事务提交

　　显示提交：输入commit语句

　　自动提交: 输入"set autocommit on"命令

1.3.4设置回滚点

　　建立保存点：savepoint sp01;

　　回滚保存点：rollback sp01;

1.4锁机制

　　共享锁：通过数据存取的高并行性实现，获得一个共享锁，那么用户可以共享相同的资源。许多事务可以获得相同资源的共享锁。列如多个用户可以在相同的事件读取相同的数据。

　　独占锁：防止共同改变的相同资源。列如一个事务获得某一资源的一个独占锁。那么直到该锁被解锁。其他事务才能修改资源。

　　死锁：当多个用户等待一个被锁住资源时，就会发生死锁现象。