Oracle SQL*Plus 退出Commit or Rollback设置

原创

布衣530

发布于 2025-01-10 10:01:53

13300

代码可运行

文章被收录于专栏：Oracle DBAOracle DBA

运行总次数：0

代码可运行

SET AUTOCOMMIT{ON | OFF | IMMEDIATE | n}

官方文档介绍

译

控制Oracle数据库在执行SQL或PL/SQL命令后何时向数据库提交挂起的更改。
ON：在Oracle数据库执行每个成功的INSERT、UPDATE、DELETE或PL/SQL块后，向数据库提交处理的更改。
OFF：禁止自动提交，因此您必须手动提交更改（例如，使用SQL命令commit）。
IMM[EDIATE] | n：在Oracle数据库执行n个成功的SQL INSERT、UPDATE或DELETE命令或PL/SQL块后，IMMEDIATE函数以与ON.n相同的方式向数据库提交挂起的更改。n不能小于零或大于200000000。在成功完成n个INSERT、UPDATE或DELETE命令或PL/SQL块、提交、回滚或SET AUTOCOMMIT命令后，语句计数器重置为零。
当SQL*Plus退出时，SET AUTOCOMMIT不会改变提交行为。默认情况下，任何未提交的数据都会被提交。

测试-1：默认情况下(SET AUTOCOMMIT OFF)，任何未提交的数据都会被提交

TWO@phytest1:74> show auto
autocommit OFF
TWO@phytest1:74> select * From t1;
no rows selected
TWO@phytest1:74> insert into t1 values(1,userenv('sid'),'xx');
1 row created.
TWO@phytest1:74> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db ~]$ sqlplus two/two
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 17:09:56 2024
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TWO@phytest1:74> select * from t1;
        ID SID                  NAME
---------- -------------------- --------------------------------------------------
         1 74                   xx

测试-2：SET AUTOCOMMIT ON/IMM (自动提交开)

TWO@phytest1:74> SET AUTOCOMMIT ON
TWO@phytest1:74> select * from t1;
no rows selected
TWO@phytest1:74> insert into t1 values(1,userenv('sid'),'xx');
1 row created.
Commit complete.
TWO@phytest1:74> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db ~]$ sqlplus two/two
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 17:11:25 2024
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TWO@phytest1:74> select * From t1;

        ID SID                  NAME
---------- -------------------- --------------------------------------------------
         1 74                   xx

测试-3：SET AUTOCOMMIT N

N : DML语句的个数

SET EXITCOMMIT {ON | OFF}

官方文档介绍

译

指定默认EXIT行为是COMMIT还是ROLLBACK。
默认设置为ON，这意味着在退出时提交工作，无论您是否期望提交。将EXITCOMMIT设置为OFF，以便在退出时回滚工作。

显示了与SET命令（AUTOCOMMIT和EXITCOMMIT）和exit命令组合相关的退出操作

测试：

AUTOCOMMIT= OFF (默认)
EXITCOMMIT= ON (默认)
EXIT = ROLLBACK

TWO@phytest1:74> show auto
autocommit OFF
TWO@phytest1:74> show exitc
exitcommit ON
TWO@phytest1:74> select * from t1;
no rows selected
TWO@phytest1:74> insert into t1 values(1,userenv('sid'),'xx');
1 row created.
TWO@phytest1:74> rollback;
Rollback complete.
TWO@phytest1:74> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db ~]$ sqlplus two/two
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 17:50:19 2024
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TWO@phytest1:74> select * from t1;
no rows selected

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库