oracle sqlplus transaction,WHENEVER SQLERROR EXIT ROLLBACK

the action will be the same

• run sqls in sqlplus
• write sqls in a file(eg. 1.sql) and run @1.sql in sqlplus
• write sqls in different files(eg. a.sql,b.sql) and @a.sqls;@b.sqls; in a file(eg. 2.sql) and run @2.sql in sqlplus

1.just like below,dont need set autocommit or us begin, dcsal+1 will be rollback because of WHENEVER SQLERROR EXIT ROLLBACK,and dcsal+2 will not be executed because of WHENEVER SQLERROR EXIT ;

WHENEVER SQLERROR EXIT ROLLBACK;
WHENEVER OSERROR EXIT;

update Table2 set dcsal=dcsal+1;
update axxxxx set ff=6;
update Table2  set dcsal=dcsal+2;

*2.raise warning? at aa ,but dcsal+1 and dcsal+2 will be commit,beacuse WHENEVER SQLERROR EXIT ROLLBACK only work on sql error;

WHENEVER SQLERROR EXIT ROLLBACK;
WHENEVER OSERROR EXIT;

update Table2 set dcsal=dcsal+1;
aa;
update Table2 set dcsal=dcsal+2;

*3,dcsal+1 will be rollback,table will be created;

CREATE文などのデータ定義言語(DDL)文は、明示的にコミットしなくても暗黙的にコミットされるとのこと。
その他、SQL*PlusをEXITかQUITコマンドで終了した場合にも暗黙コミットされるとのこと。

<参考> https://www.shift-the-oracle.com/transaction/commit.html
http://oraclemasterdb.seesaa.net/article/96566762.html

DDL例 　
・CREATE文 (CREATE TABLE,CREATE SEQUENCE,CREATE SYNONYM) 　
・ALTER文 (ALTER TABLE,ALTER USER) 　
・DROP文 (DROP TABLE,DROP SEQUENCE,DROP VIEW)
・TRUNCATE文 　
・RENAME文

WHENEVER SQLERROR EXIT ROLLBACK;
WHENEVER OSERROR EXIT;

@create_bk_Table1.sql;
update Table2 set dcsal=dcsal+1;
update axxxxx set ff=6;

*4,create table error because the table exists,dcsal+1 will be commited;

--WHENEVER SQLERROR EXIT ROLLBACK;
WHENEVER OSERROR EXIT;

@create_bk_Table1.sql;
update Table2 set dcsal=dcsal+1;
update axxxxx set ff=6;

should add “;” or “/” after every sql sentence,and there is only one “/” if use “/”

if use “/” ,should used at next row. e.g.:

select * from table1;
or
select * from table1
/

• if there is “/” in 1.sql,dont add “/” after @1.sql,else ths sql will be execute 2 times
• if there is no “/” in 1.sql,add “/” after @1.sql,else ths sql will not be execute and does not raise error(can not use “:” here)

if 1.sql does not exsit,sqlplus will return 111. WHENEVER OSERROR EXIT will return 0 when file is not found,It is better to return a value that is not 0,

sqlplus -s $username/$password@$host:$port/$dbname <<EOF
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT 111
@1.sql
EOF