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

- 日理万妓 2022-04-11 09:08 456阅读 0赞

*  [博客园][Link 1]
 *  [首页][Link 2]
 *  [新随笔][Link 3]
 *  [联系][Link 4]
 *  [管理][Link 5]
 *  [订阅][Link 6][![订阅][xml.gif]][Link 6]

随笔- 75  文章- 0  评论- 0

# [Oracle 存储过程，触发器，事务，锁][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创建储存过程 ###

![复制代码][copycode.gif]

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：异常处理语句。

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

##  1.2触发器 ##

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

### 1.2.1创建触发器 ###

![复制代码][copycode.gif]

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.系统事件触发器

![复制代码][copycode.gif]

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;

![复制代码][copycode.gif]

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;
    /

![复制代码][copycode.gif]

系统事件触发器
    创建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;　　--删除触发器

![复制代码][copycode.gif]

## 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锁机制 ##

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

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

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

分类: [Oracle][Oracle 1]

标签: [Oracle][Oracle 2]

好文要顶 关注我 收藏该文 ![icon_weibo_24.png][] ![wechat.png][]

[![20170205201551.png][]][20170205201551.png 1]

[珈子][20170205201551.png 1]  
[关注 - 2][- 2]  
[粉丝 - 3][- 3]

\+加关注

[« ][Link 7]上一篇：[HTTPS和HTTP的区别][Link 7]  
[» ][Link 8]下一篇：[问题总结][Link 8]

[https://www.cnblogs.com/Jonecmnn/p/6416137.html][Oracle]

*  [博客园][Link 1]
 *  [首页][Link 9]
 *  [新随笔][Link 3]
 *  [联系][Link 10]
 *  [管理][Link 5]
 *  [订阅][Link 11][![订阅][xml.gif]][Link 11]

随笔- 59  文章- 0  评论- 7

# [SQL 存储过程 触发器 事务][SQL _ _] #

**一、存储过程**

存储过程：就像函数一样的  
会保存在：数据库中--》可编程性 --》 存储过程  
  
**创建存储过程：**  
create **proc** JiaFa --存储关键字proc  
  
@a int,               --需要的参数  
@b int

as  
                         --存储过程的内容  
declare @c int;  
set @c = @a + @b;  
return @c;  
go                     --执行完毕后全部选中，执行创建

**执行存储过程：**关键字exec  
exec JiaFa 3,5;              --无返回值时

declare @f int;               --有返回值时  
exec @f = JiaFa 3,5;       --存储名后面填参数  
print @f;

**例：根据用户传入的参数查询汽车表符合该条件的汽车数量**  
create proc ChaXun       --创建存储过程  
@n varchar(20)  
as  
declare @num int  
select @num = count(\*) from car where name like '%'+@n+'%'  
return @num  
go

declare @m int              --执行存储过程  
exec @m = ChaXun '奥迪'  
print @m

**二、触发器**

**是一个特殊的存储过程    **通过**增删改**的动作来触发执行，**没有参数，没有返回值**

满足条件时执行，否则不执行

create **trigger** Insert \_Student            --命名规范  关键字：tigger  注意下划线

**Insert/delete/update\_表名   在\[表名\]表 添加/删除/修改 数据是执行**

**on** student                                        --针对于哪一个表    on

**for /****instead of**   insert                      --针对于哪一个动作来触发   for     同js： onclick = "show()"

**as**

触发执行的代码段 触发过程  
**go**

**※ for的意思是在动作执行之后触发**

**※ instead of delete 的意思是删除之前引发，可以理解为替代，写了这个之后，****写的执行代码就没有用了，就被触发器的代码覆盖了**

**例：**

create trigger Delete\_Info

on info  
instead of delete  
as  
declare @c varchar(20)  
select @c = code from deleted  
  
delete from work where infocode=@c  
delete from family where infocode=@c  
delete from info where code=@c  
go

**触发器常用的为级联删除**

**例：**  
create trigger delete\_student  
on student  
instead of delete  
as  
\--如果要删除student表数据，那么需要级联删除  
declare @sno varchar(20);  
set @sno = sno from deleted      **--deleted固定格式，为删除执行所能删除的数据，并没有执行删除，而是把他们显示出来，在这获得要删除的数据的sno，**

**　　　　　　　　　　　　　　　　   然后先删除其他表中此sno的数据**  
delete from score where sno = @sno;  
delete from student where sno = @sno;  
go

**三、事务**

**保障流程的完整执行 （两条多条sql语句要么同时成功，要么同时失败）**

例：就像银行取钱，先在你账上扣钱，然后存入别人的账上，但是从你账上扣完钱了，突然网断了，对方没有收到钱，那么此时你的钱也没了，别人的钱也没加上，为了防止此类情况的出现，事务。

**begin tran**             　　**--在流程开始的位置**

sql语句

**if @@ERROR>0           --判断是否有错误**  
**begin**  
　　**rollback tran**          **--回滚事务，到begin tran的位置，就当没发生过**  
**end**  
**else**  
**begin**  
　　**commit tran  **         **--提交事务，都没问题，那么就一把进行提交**  
**end**

**例：****购物车实例**

begin tran                                       --开启事务  
declare @tran\_error int;                   --存储错误  
set @tran\_error = 0;                       --默认没有错误

update Fruit set Numbers = Numbers-1 where Ids='k002'  
set @tran\_error = @tran\_error + @@ERROR;  
update Login set Account=Account-1 where UserName='wangwu'  
set @tran\_error = @tran\_error + @@ERROR;  
insert into Orders values('d002','wangwu','2016-8-7')  
set @tran\_error = @tran\_error + @@ERROR;  
insert into OrderDetails values('d002','k002',10)  
set @tran\_error = @tran\_error + @@ERROR;

if @tran\_error>0  
begin  
rollback tran                                   --回滚事务，到begin tran的位置，就当没发生过  
end  
else  
begin  
commit tran                                   --提交事务，都没问题，那么就一把进行提交  
end

好文要顶 关注我 收藏该文 ![icon_weibo_24.png][] ![wechat.png][]

[![20160820161109.png][]][20160820161109.png 1]

[无毒不羁][20160820161109.png 1]  
[关注 - 16][- 16]  
[粉丝 - 18][- 18]

\+加关注

[« ][Link 12]上一篇：[SQL 常用函数][Link 12]  
[» ][Link 13]下一篇：[面向对象 基础][Link 13]

[https://www.cnblogs.com/ShenG1/p/5763284.html][SQL _ _]

[Link 1]: https://www.cnblogs.com/
[Link 2]: https://www.cnblogs.com/Jonecmnn/
[Link 3]: https://i.cnblogs.com/EditPosts.aspx?opt=1
[Link 4]: https://msg.cnblogs.com/send/%E7%8F%88%E5%AD%90
[Link 5]: https://i.cnblogs.com/
[Link 6]: /images/20220411/f696b4341acd4464b880b110019380a0.png
[xml.gif]: https://www.cnblogs.com/images/xml.gif
[Oracle]: https://www.cnblogs.com/Jonecmnn/p/6416137.html
[copycode.gif]: /images/20220411/2bf6379c136a4670b6971b0214343195.png
[Oracle 1]: https://www.cnblogs.com/Jonecmnn/category/942621.html
[Oracle 2]: https://www.cnblogs.com/Jonecmnn/tag/Oracle/
[icon_weibo_24.png]: https://common.cnblogs.com/images/icon_weibo_24.png
[wechat.png]: /images/20220411/6e90a66a79c84c10ba96b2fb419912c2.png
[20170205201551.png]: /images/20220411/424e59a1ee6a4db4a002df9c490375ea.png
[20170205201551.png 1]: http://home.cnblogs.com/u/Jonecmnn/
[- 2]: http://home.cnblogs.com/u/Jonecmnn/followees
[- 3]: http://home.cnblogs.com/u/Jonecmnn/followers
[Link 7]: https://www.cnblogs.com/Jonecmnn/p/6403858.html
[Link 8]: https://www.cnblogs.com/Jonecmnn/p/6416498.html
[Link 9]: https://www.cnblogs.com/ShenG1/
[Link 10]: https://msg.cnblogs.com/send/%E6%97%A0%E6%AF%92%E4%B8%8D%E7%BE%81
[Link 11]: /images/20220411/7d0bcd15c72747798e43d59e26caac1f.png
[SQL _ _]: https://www.cnblogs.com/ShenG1/p/5763284.html
[20160820161109.png]: /images/20220411/dc05d95c200c446fa2402b9439dbf663.png
[20160820161109.png 1]: http://home.cnblogs.com/u/ShenG1/
[- 16]: http://home.cnblogs.com/u/ShenG1/followees
[- 18]: http://home.cnblogs.com/u/ShenG1/followers
[Link 12]: https://www.cnblogs.com/ShenG1/p/5763239.html
[Link 13]: https://www.cnblogs.com/ShenG1/p/5768355.html