oracle 事务处理

最新推荐文章于 2024-10-17 18:58:27 发布

crc153246

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量194

点赞数

文章标签：数据库运维数据结构与算法

oracle 事务处理
数据库事务处理:
事务开始: 当第一个DML语句一个事务开始(数据操纵语言:insert、delete、select、update

                  增、删、查、改)
        事务结束: 当一个commit和rollback语句被发布
                  当一个DDL语句执行,(数据定义语言 create、alter、drop、truncate)
                  当一个DCL语句执行,(数据控制语言 grant、revoke(授权、回收)、set role)
                  用户退出
                  机器失效或者崩溃
         当一个事务结束后,下一个可执行的SQL语句自动开始下一个事务,
         一个DDL语句或者一个DCL语句自动提交,并且因此一个事务隐式结束
         commit和rollback语句的优点:
                 确保数据的一致性
                 在数据永久改变之前进行预览
                 分组逻辑相关的操作
         控制事务:
                 commit 结束当前事务,使得所有未决的数据永久改变
                 savepoint name 在当前事务中标记保存点
                 rollback 结束当前事务,丢弃所有未决的数据改变
                 rollback to savepoint name 回滚当前事务到指定的保存点,从而丢弃保存点创建后的

任何改变.如果忽略 to savepoint子句,rollback语句回

滚整个事务.由于保存点是逻辑的,因此,没有办法列出已

                                            经创建的保存点
         可以用savepoint语句在当前事务中创建一个标记
                 用rollback to savepoint 语句回退到哪个标记
                 例如:
                 update .........
                 savepoint update_done;
                 insert..........
                 rollback to update_done;
                 回退到保存点,可以用savepoint语句创建一个标记,它把事务分为较小的部分
                 rollback to savepoint语句丢弃未决的改变到该标记.如果你用与前面的保存点相同的

                 名字创建了另一个保存点,哪个早一点时间创建的保存点就被删除了.
                 select * from testtable t;
                 update testtable set content ='11pp1' where id = 1;
                 commit;
                 savepoint kk;
                 insert into testtable values(12,'kingking',sysdate);
                 rollback to kk;
                 commit;
         隐式事务处理
                 在正常的情况下,一个自动提交发生:
                 ----DDL语句被发送
                 ----DCL语句被发送
                 ----正常退出,没有明确地发送commit或rollback语句
                 在正常的情况下,一个自动回滚发生:
                 ----系统发生异常,或者非正常退出
         commit或rollback之前的数据状态
                事务中所在的每一个数据改变在事务被提交之前都是临时的,
                在commit或rollback语句发布之前数据的状态:
                  1.数据操纵操作首先影响数据库缓冲区,因此,数据以前的状态可以被恢复.
                  2.当前用户可以查询表观察到数据操纵操作的结果.
                  3.其他用户不能观察到当前所做的数据操纵操作的结果.oracle服务器用读一致性
                    来确定每个用户看到的数据和上次提交时相同.
                  4.其他用户不能改变受影响的行中的数据.
        在commit之后数据的状态
                 数据在数据库中被永久地改变.
                 数据以前的状态永久地丢失.
                 所有用户都可以观察到事务的结果.
                 受影响的行的锁定被释放:其他用户可以操纵那么行.
                 所有保存点给擦除.
        rollback后的数据状态
                 数据的改变被撤消.
                 数据的以前的状态被恢复.
                 受影响行的锁定被释放.
        语句级rollback
                 如果一个语句的执行错误被发现,一个事务的一部分可以用隐式的回退丢弃.如果一个单
                 个的DML语句在一个事务的执行期间失败,它的影响被一个语句级的回退撤消, 但在事务
                 中的以前已经由DML语句完成的改变不能丢弃,它们可以由用户显示地提交或回滚.
                 oracle在任何DDL(create,建表)语句之前和之后发布一个隐式的提交.所以即使你的DDL
                 语句执行不成功,你也不能回退前面的语句,因此服务器已经发布提交命令.
                    执行commit或rollback语句来明确地结束事务.
                 oracle服务器在数据上实现锁以防止对数据库中数据的并发操作, 当一个DML语句成功执

                 行时,数据库上的隐式锁被获得,默认情况下,oracle服务器在尽可能的最低级别锁定数据
                 执行带for update子句的lock table语句或select语句可以手动获得数据库上的锁.
        读一致性
                数据库用户用两种方法访问数据库:
                    读操作(select 语句)
                    写操作(插入,更新,删除语句)
                你需要读一致性,所以有下面事发生:
                    数据库读者和写者被确保对数据观察的一致性.
                    读者不能观察正在变化过程中的数据.
                    写者被确保对数据库的改变以一致的方式进行.
                    一个写者所做的改变不破坏另一个写者所做的改变或与其冲突.
                读一致性的目的是确保每个用户看到的数据和他最后一次提交,并且在一个DML操作操作开始之前的数据一样.
                读一致性是一种自动的执行,该操作在回退段保持一个数据库的局部的拷贝.对数据库进行
                一个插入,更新或者删除时,oracle服务器在数据改变之前获得相关数据的拷贝,并且将这

些数据写到一个回退段(undo segment).
所有读数据者,除了发布修改命令的用户,看到的数据还是改变之前的状态;他们看到数据

是回退段数据快照.在改变被提交到数据库之前,只有正在修改数的用户能看见数据库的改

变:除了他之外的任何人看到都是回退段的快照,这样就确保数据的读者读到一致的数据,

                而不是当前正在被修改的数据.
                当一个DML语句被提交的时,对数据库所做的改变对任何执行select语句的人成为可见的.
                在回退段的文件中被旧数据占用的空间被释放以重新使用.
          锁定:
                锁是防止访问相同资源的事务之间的破坏性交互机制.即可以是用户对象(如表或行),也可

                以是对用户不可见的系统对象(例如共享数据结构和数据字典).
          oracle数据库怎么锁定数据
                oracle锁被自动执行,并且不要求用户干预.对于SQL语句隐式锁是必须的，依赖被请求的

                动作.隐式锁定除了select外,对所有的SQL都发生.
                     用户也可以手动锁定数据,这叫显示锁定.
         隐式锁定:
                2种锁的模式:
                     独占锁:不允许其他用户访问
                     共享锁:允许其他用户访问
                高级数据并发操作:
                     DML:表共享,行独占
                     查询:不需要锁
                     DDL:保护对象定义
                当执行数据操纵语言(DML,insert)操作时,oracle服务器通过DML所防止数据被同时操纵.
                共享锁是在表级在DML操作期间自动获得的.用这个模式,几个事务可以在相同的资源上获得共享锁.
                对于用DML语句修改的每一行,独占锁被自动获得.独占锁在本事务被提交或回滚之前防止行被其他事物修改.该锁确保无其他用户能够在相同的时间修改相同的行,并且覆盖另一
                个还没有提交的改变.
                当你修改数据库对象时,DDL锁发生.
oracle使用 merge 更新或插入数据（总结）
总结下。使用merge比传统的先判断再选择插入或更新快很多。
1）主要功能
提供有条件地更新和插入数据到数据库表中
如果该行存在，执行一个UPDATE操作，如果是一个新行，执行INSERT操作
— 避免了分开更新
— 提高性能并易于使用
— 在数据仓库应用中十分有用

2）MERGE语句的语法如下：

MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]

{ table | view | subquery } [t_alias] ON ( condition )

WHEN MATCHED THEN merge_update_clause

WHEN NOT MATCHED THEN merge_insert_clause;

还是看例子就知道怎么回事：
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id=e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name=e.first_name,
c.last_name=e.last_name,
c.department_id=e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id,e.first_name,e.last_name,
e.email,e.phone_number,e.hire_date,e.job_id,
e.salary,e.commission_pct,e.manager_id,
e.departmetn_id);
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id=e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name=e.first_name,
c.last_name=e.last_name,
c.department_id=e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id,e.first_name,e.last_name,
e.email,e.phone_number,e.hire_date,e.job_id,
e.salary,e.commission_pct,e.manager_id,
e.departmetn_id);

3）使用merge的注意事项：
创建测试表：
CREATE TABLE MM (ID NUMBER, NAME VARCHAR2(20));
CREATE TABLE MN (ID NUMBER, NAME VARCHAR2(20));
插入数据
INSERT INTO MM VALUES (1, 'A');
INSERT INTO MN VALUES (1, 'B');
执行：
MERGE INTO MN A
USING MM B
ON(A.ID=B.ID)
WHEN MATCHED THEN
UPDATE SET A.ID = B.ID
WHEN NOT MATCHED THEN
INSERT VALUES(B.ID, B.NAME);
ON(A.ID=B.ID)
报错，原因是on子句的使用的字段不能够用于update，即Oracle不允许更新用于连接的列
修改：
MERGE INTO MN A
USING MM B
ON(A.ID=B.ID)
WHEN MATCHED THEN
UPDATE SET A.NAME = B.NAME
WHEN NOT MATCHED THEN
INSERT VALUES(B.ID, B.NAME);
ON(A.ID=B.ID)

再插入：INSERT INTO MM VALUES (1, 'C');
再执行：
MERGE INTO MN A
USING MM B
ON(A.ID=B.ID)
WHEN MATCHED THEN
UPDATE SET A.NAME = B.NAME
WHEN NOT MATCHED THEN
INSERT VALUES(B.ID, B.NAME);
ON(A.ID=B.ID)
报错，原因无法在源表中获得一组稳定的行

4）更新同一张表的数据。需要注意下细节，因为可能涉及到using的数据集为null,所以要使用count()函数。
MERGE INTO mn a
USING (select count(*) co from mn where mn.ID=4) b
ON (b.co<>0)--这里使用了count和<>，注意下，想下为什么！
WHEN MATCHED THEN
UPDATE
SET a.NAME = 'E'
where a.ID=4
WHEN NOT MATCHED THEN
INSERT
VALUES (4, 'E');

oracle中merge使用注意2007年06月07日星期四 14:11把数据从一个表复制到另一个表，插入新数据或替换掉老数据是每一个ORACLE DBA都会经常碰到的问题。在ORACLE9i以前的年代，我们要先查找是否存在老数据，如果有用UPDATE替换，否则用INSERT语句插入，其间少不了还有一些标记变量等等，繁琐的很。现在ORACLE9i专为这种情况提供了MERGE语句，使这一工作变得异常轻松，MERGE语句的语法如下：

MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] { table | view | subquery } [t_alias] ON ( condition ) WHEN MATCHED THEN merge_update_clause WHEN NOT MATCHED THEN merge_insert_clause;

例如：

MERGE INTO tdest d
USING tsrc s
ON (s.srckey = d.destkey)
WHEN MATCHED THEN
UPDATE SET d.destdata = d.destdata + s.srcdata
WHEN NOT MATCHED THEN
INSERT (destkey, destdata) VALUES (srckey, srcdata)

一条语句代替了原来的一段复杂语句，但是使用MERGE也是有要注意的地方的，看下面的例子： CREATE TABLE tdest(destkey KEYTYE,destdata DATATYPE);

CREATE TABLE tsrc(srckey KEYTYE,srcdata DATATYPE); --insert some rows into tsrc here.

MERGE INTO tdest d
USING tsrc s
ON (d.destkey = s.srckey)
WHEN MATCHED THEN
UPDATE SET d.destkey = s.srckey
WHEN NOT MATCHED THEN
INSERT (d.destkey, d.destdata) VALUES (s.srckey, s.srcdata);

运行以上代码,你可能会惊讶得得到如下结果：

ORA-00904: invalid column name Cause: The column name entered is either missing or invalid. Action: Enter a valid column name.

你一开始会觉得很蹊跷，于是到处找自己哪里写错了，其实你没有写错，呵呵，是ORACLE错了。原来，其中关键是那个UPDATE语句，不能更新在ON语句中引用得那些列，这可以说是一个限制，也可以说是一个BUG，因为毕竟ORACLE的正式DOCUMENT里并没有提有这一限制。metalink里你可以找[BUG:2124282] 对这一问题的描述，可见ORACLE确实把它当成了一个BUG对待，或许，后续的版本就没有这一限制了，但是目前，如果你想从ORACLE工程师提供MERGE功能中获益的话，还是要记住这个小BUG的。

///
解析：轻松掌握Oracle中事务管理的概念

发布时间：2007.09.26 05:14 来源：赛迪网作者：limeinan

在Oracle中一个事务是由一个可执行的SQL语句开始，一个可执行SQL语句产生对实例的调用。在事务开始时，被赋给一个可用回滚段，记录该事务的回滚项。一个事务以下列任何一个出现而结束。

◆当COMMIT或ROLLBACK（没有SAVEPOINT子句）语句发出。

◆一个DDL语句被执行。在DDL语句执行前、后都隐式地提交。

◆用户撤消对Oracle的连接（当前事务提交）。

◆用户进程异常中止（当前事务回滚）。

(1)提交事务

提交一事务，即将在事务中由SQL语句所执行的改变永久化。在提交前，Oracle已有下列情况：

◆在SGA的回滚段缓冲区已生成回滚段记录，回滚信息包含有所修改值的老值。

◆在SGA的日志缓冲区已生成日志项。这些改变在事务提交前可进入磁盘。

◆对SGA的数据库缓冲区已作修改，这些修改在事务真正提交之前可进入磁盘。

在事务提交之后，有下列情况：

◆对于与回滚段相关的内部事务表记录提交事务，并赋给一个相应的唯一系统修改号（SCN），记录在表中。

◆在SGA的日志缓冲区中日志项由LGWR进程写入到在线日志文件，这是构成提交事务的原子事务。

◆在行上和表上的封锁被释放。

◆该事务标志为完成。

注意：对于提交事务的数据修改不必由DBWR后台进程立即写入数据文件，可继续存储在SGA的数据库缓冲区中，在最有效时将其写入数据文件。

(2)回滚事务

回滚事务的含义是撤消未提交事务中的SQL语句所作的对数据修改。Oracle允许撤消未提交的整个事务，也允许撤消部分。

在回滚整个事务（没有引用保留点）时，有下列情况：

◆在事务中所有SQL语句作的全部修改，利用相应的回滚段被撤消。

◆所有数据的事务封锁被释放。

◆事务结束。

◆当事务回滚到一保留点（具有SAVEPOINT）时，有下列情况：

◆仅在该保留点之后执行的语句被撤消。

◆该指定的保留点仍然被保留，该保留点之后所建立的保留点被删除。

◆自该保留点之后所获取的全部表封锁和行封锁被释放，但指定的保留点以前所获取的全部数据封锁继续保持。

◆该事务仍可继续。

(3)保留点

保留点（savepoint）是在一事务范围内的中间标志，经常用于将一个长的事务划分为小的部分。保留点可标志长事务中的任何点，允许可回滚该点之后的工作。在应用程序中经常使用保留点；例如一过程包含几个函数，在每个函数前可建立一个保留点，如果函数失败，很容易返回到每一个函数开始的情况。在回滚到一个保留点之后，该保持点之后所获得的数据封锁被释放。

（责任编辑：卢兆林）

这个问题第1个回答：
触发器执行完成后自动提交。
你可以加一个条件如果错误，让他ROLLBACK TRANSACTION
--------------------------------------------------------------------------------
这个问题第2个回答：
都可以使用
--------------------------------------------------------------------------------
这个问题第3个回答：
完全可以

CREATE TRIGGER dbo.tr_person
ON [dbo].[person]
FOR UPDATE
AS
begin tran
declare @p varchar(10),@e varchar(1)
select @p=person_no,@e=enable from deleted
create table tb(person_no varchar(10),enable varchar(1))
insert into tb(person_no,enable)
values(@p,@e)
if @@error <>0 goto err

commit tran
return

err:
rollback

--------------------------------------------------------------------------------
这个问题第4个回答：
你建立一个,用代码难一下,就知道了

--------------------------------------------------------------------------------
这个问题第5个回答：
少一个字了

你建立一个表....
--------------------------------------------------------------------------------
这个问题第6个回答：

需要注意的是：

    执行触发器时，将开始隐式事务。
    如果触发器执行完后 @@TRANCOUNT 为 0，则会出现错误 3609 并终止批处理。
    由于这个原因，建议避免在触发器内部使用 ROLLBACK TRANSACTION（它将把 @@TRANCOUNT 重置为 0）以及 COMMIT TRANSACTION（它将把 @@TRANCOUNT 减少为 0）。
    在回滚后发出 BEGIN TRANSACTION 语句可以阻止错误的发生，但这样可能会导致应用程序逻辑出现问题。

参考：
    了解在触发器中发出 BEGIN TRANSACTION 语句实际上是开始了一个嵌套事务这一点很重要。
    在这种情况下，执行 COMMIT TRANSACTION 语句将只应用到嵌套事务。
    因为回滚嵌套事务时将忽略嵌套的 BEGIN TRANSACTION 语句，所以触发器中执行的 ROLLBACK TRANSACTION 将回滚过去该触发器本身发出的所有 BEGIN TRANSACTION 语句。ROLLBACK 回滚到最外部事务并将 @@TRANCOUNT 设置为 0。

在触发器使用 ROLLBACK TRANSACTION 时，请注意下列行为：
（1）当前事务中该时间点之前所做的所有数据修改都将回滚，包括触发器所做的修改。
（2）触发器继续执行 ROLLBACK 语句之后的所有语句。如果这些语句中的任意语句修改数据，则不回滚这些修改。
（3）触发器中的 ROLLBACK 关闭并释放所有在包含激发触发器的语句的批处理中声明和打开的游标。这包括在激发触发器的批处理所调用的存储过程中声明和打开的游标。在激发触发器的批处理之前的批处理中声明的游标只关闭。但是，STATIC 或 INSENSITIVE 游标在下列条件下不会关闭：
（3-A）CURSOR_CLOSE_ON_COMMIT 设置为 OFF。
（3-B）静态游标是同步游标或者完全填充的异步游标。

可以不使用 ROLLBACK TRANSACTION，而使用 SAVE TRANSACTION 语句在触发器中执行部分回滚。

--------------------------------------------------------------------------------
这个问题第7个回答：
可以

SQL code

CREATE TRIGGER Barn_IU

ON dbo.Barn

FOR INSERT

begin

begin tran

DECLARE @find integer

DECLARE @rowcount integer

set @rowcount=(select count(*) from inserted)

if @rowcount=1

begin

set @find=(select count(*) from Barn where ProBarcode=(select cast(ProBarcode as varchar(50)) as ProBarcode from inserted) AND rtrim(pihao)=(select cast(pihao as varchar(50)) as pihao from inserted))

print @find

if @find>1

begin

delete Barn where id in (select id from inserted)

UPDATE Barn SET Sum_Num = Sum_Num + (select Sum_Num from inserted) WHERE ProBarcode=(select cast(ProBarcode as varchar(50)) as ProBarcode from inserted) AND rtrim(pihao)=(select cast(pihao as varchar(50)) as pihao from inserted)

end

commit tran

end

else

commit tran

end

return

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7600171/viewspace-545609/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7600171/viewspace-545609/