Shulou(Shulou.com)05/31 Report--

This article is about how to do when there is an error in the execution of multiple SQL statements in the middle of Oracle Foundation. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

Environmental preparation

Creating docker-style demo environments using Oracle Lite

Normal execution of multiline statements

For the student information table of the two fields created in the previous article, three pieces of data are normally added, as follows:

# sqlplus system/liumiao123@XE 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;> EOFSQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 21 12:08:35 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit ProductionSQL> Name Null? Type ----------------------------------------- -------- ---------------------------- STUID NOT NULL NUMBER(4) STUNAME VARCHAR2(50)SQL> no rows selectedSQL> 1 row created.SQL> 1 row created.SQL> 1 row created.SQL> Commit complete.SQL> STUID STUNAME---------- -------------------------------------------------- 1001 liumiaocn 1002 liumiao 1003 michaelSQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production#

Default actions for errors in the middle of a multiline statement

Question:

Three-line insert statement. If the middle line is wrong, will the third line be inserted by default?

We deliberately duplicate the primary key of the second insert statement, and then confirm whether the third data will be inserted.

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

The result is very clear that it will continue to be executed, and what controls it in oracle?

WHENEVER SQLERROR

The answer is simple, controlled in oracle by WHENEVER SQLERROR.

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

WHENEVER SQLERROR EXIT

Add this line setting, that is, it will be pushed out immediately when it fails, and then we will confirm:

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

WHENEVER SQLERROR CONTINUE

Use CONTINUE to behave in the same way as in the default mode, and continue with errors

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

Similar mechanisms in Mysql

In the question of whether the use of source in mysql provides a related similar mechanism, Oracle finally introduced this feature in mysql. For details, please refer to:

https://bugs.mysql.com/bug.php? id=73177

Therefore, at present, this is only an enhanced function of sqlplus, not a standard, different databases need to confirm whether the corresponding function exists.

Thank you for reading! About "Oracle basic multiple sql execution in the middle of the statement error how to do" This article is shared here, I hope the above content can have some help for everyone, so that we can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.