亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

Oracle基礎(chǔ)多條sql執(zhí)行在中間的語句出現(xiàn)錯(cuò)誤時(shí)的控制方式

 更新時(shí)間:2018年12月27日 08:58:13   作者:liumiaocn  
今天小編就為大家分享一篇關(guān)于Oracle基礎(chǔ)多條sql執(zhí)行在中間的語句出現(xiàn)錯(cuò)誤時(shí)的控制方式,小編覺得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來看看吧

多條sql執(zhí)行時(shí)如果在中間的語句出現(xiàn)錯(cuò)誤,后續(xù)會(huì)不會(huì)直接執(zhí)行,如何進(jìn)行設(shè)定,以及其他數(shù)據(jù)庫諸如Mysql是如何對(duì)應(yīng)的,這篇文章將會(huì)進(jìn)行簡(jiǎn)單的整理和說明。

環(huán)境準(zhǔn)備

使用Oracle的精簡(jiǎn)版創(chuàng)建docker方式的demo環(huán)境,詳細(xì)可參看:

多行語句的正常執(zhí)行

對(duì)上篇文章創(chuàng)建的兩個(gè)字段的學(xué)生信息表,正常添加三條數(shù)據(jù),詳細(xì)如下:

# sqlplus system/liumiao123@XE <<EOF
> desc student
> select * from student;
> insert into student values (1001, 'liumiaocn');
> insert into student values (1002, 'liumiao');
> insert into student values (1003, 'michael');
> commit;
> select * from student;
> EOF
SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 12:08:35 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> Name    Null?  Type
 ----------------------------------------- -------- ----------------------------
 STUID    NOT NULL NUMBER(4)
 STUNAME     VARCHAR2(50)
SQL> 
no rows selected
SQL> 
1 row created.
SQL> 
1 row created.
SQL> 
1 row created.
SQL> 
Commit complete.
SQL> 
   STUID STUNAME
---------- --------------------------------------------------
   1001 liumiaocn
   1002 liumiao
   1003 michael
SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
#

多行語句中間出錯(cuò)時(shí)的缺省動(dòng)作

問題:

三行insert語句,如果中間的一行出錯(cuò),缺省的狀況下第三行會(huì)不會(huì)被插入進(jìn)去?

我們將第二條insert語句的主鍵故意設(shè)定重復(fù),然后進(jìn)行確認(rèn)第三條數(shù)據(jù)是否會(huì)進(jìn)行插入即可。

# sqlplus system/liumiao123@XE <<EOF
desc student
delete from student;
select * from student;
insert into student values (1001, 'liumiaocn');
insert into student values (1001, 'liumiao');
insert into student values (1003, 'michael');
select * from student;
commit;> > > > >    
> EOF
SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 12:15:16 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> Name    Null?  Type
 ----------------------------------------- -------- ----------------------------
 STUID    NOT NULL NUMBER(4)
 STUNAME     VARCHAR2(50)
SQL> 
2 rows deleted.
SQL> 
no rows selected
SQL> 
1 row created.
SQL> insert into student values (1001, 'liumiao')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C007024) violated
SQL> 
1 row created.
SQL> 
   STUID STUNAME
---------- --------------------------------------------------
   1001 liumiaocn
   1003 michael
SQL> SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
# 

結(jié)果非常清晰地表明是會(huì)繼續(xù)執(zhí)行的,在oracle中通過什么來對(duì)其進(jìn)行控制呢?

WHENEVER SQLERROR

答案很簡(jiǎn)單,在oracle中通過WHENEVER SQLERROR來進(jìn)行控制。

WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}

WHENEVER SQLERROR EXIT

添加此行設(shè)定,即會(huì)在失敗的時(shí)候立即推出,接下來我們進(jìn)行確認(rèn):

# sqlplus system/liumiao123@XE <<EOF
WHENEVER SQLERROR EXIT
desc student
delete from student;
select * from student;
insert into student values (1001, 'liumiaocn');
insert into student values (1001, 'liumiao');
insert into student values (1003, 'michael');
select * from student;
commit;> > > > > > > > > 
> EOF
SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 12:27:15 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> SQL> Name    Null?  Type
 ----------------------------------------- -------- ----------------------------
 STUID    NOT NULL NUMBER(4)
 STUNAME     VARCHAR2(50)
SQL> 
2 rows deleted.
SQL> 
no rows selected
SQL> 
1 row created.
SQL> insert into student values (1001, 'liumiao')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C007024) violated
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
# 

WHENEVER SQLERROR CONTINUE

使用CONTINUE則和缺省方式下的行為一致,出錯(cuò)仍然繼續(xù)執(zhí)行

# sqlplus system/liumiao123@XE <<EOF
WHENEVER SQLERROR CONTINUE
desc student
delete from student;
select * from student;
insert into student values (1001, 'liumiaocn');
insert into student values (1001, 'liumiao');
insert into student values (1003, 'michael');
select * from student;
commit;> > > > > > > > > 
> EOF
SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 12:31:54 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> SQL> Name    Null?  Type
 ----------------------------------------- -------- ----------------------------
 STUID    NOT NULL NUMBER(4)
 STUNAME     VARCHAR2(50)
SQL> 
1 row deleted.
SQL> 
no rows selected
SQL> 
1 row created.
SQL> insert into student values (1001, 'liumiao')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C007024) violated
SQL> 
1 row created.
SQL> 
   STUID STUNAME
---------- --------------------------------------------------
   1001 liumiaocn
   1003 michael
SQL> 
Commit complete.
SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
#

Mysql中類似的機(jī)制

mysql中使用source是否提供相關(guān)的類似機(jī)制的問題中,最終引入了Oracle此項(xiàng)功能在mysql中引入的建議,詳細(xì)請(qǐng)參看:

所以目前這只是一個(gè)sqlplus端的強(qiáng)化功能,并非標(biāo)準(zhǔn),不同數(shù)據(jù)庫需要確認(rèn)相應(yīng)的功能是否存在。

小結(jié)

Oracle中使用WHENEVER SQLERROR進(jìn)行出錯(cuò)控制是否繼續(xù),本文給出的例子非常簡(jiǎn)單,詳細(xì)功能的使用可根據(jù)文中列出的Usage進(jìn)行自行驗(yàn)證和探索。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,謝謝大家對(duì)腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請(qǐng)查看下面相關(guān)鏈接

相關(guān)文章

最新評(píng)論