There is no if exists (...) in Oracle The workaround

Source: Internet
Author: User There are many solutions to the syntax of Oracle that do not have an if exists (...), here we first analyze the three commonly used, the last one is recommended

The first is most commonly used to determine whether the value of count (*) is zero, as follows
V_CNT number;
Select COUNT (*) into v_cnt from T_vip where col=1;
If v_cnt = 0 Then
Dbms_output.put_line (' no record ');
End If;
First of all, this kind of writing makes people feel very strange, clearly only need to know that the table has no record, but to count the number of records in the list.
This is acceptable for small tables, and when the table is logged a lot, the performance problem is very serious.
So someone made some changes and changed it to select COUNT (*) into v_cnt from T_VIP where col=1 and Rownum=1
It seems to solve the performance problem, but the analysis execution plan can know that it is actually the same and is not recommended for use.

The second is the so-called offensive programming, not pre-judgment, but directly by default, and then use exception to catch the exception
For example, I don't know if there is a record in the table that satisfies the condition, default it has, if not in the exception to handle
V_1 number;
Select Vip_level into V_1 from T_VIP where 1=0;
When No_data_found Then
Dbms_output.put_line (' no record ');
This approach is much better than the first in terms of performance.
But first it has no way of adapting to everything, like the first piece of code, and it's not going to change.
Second, this code seems to make people feel as if something had happened, rather than running normally, causing confusion and not recommending it.

The third is the use of Oracle's original Exists syntax, as follows
V_CNT number;
Select COUNT (*)
Into v_cnt
From dual
where exists (SELECT * from T_VIP where col=1);
If v_cnt = 0 Then
Dbms_output.put_line (' no record ');
End If;
Use Oracle's original exists syntax by nesting a layer of dual on the outside of the statement
Although it looks similar to the first one, the analysis execution plan knows that performance is much better than both of the above, and is best approached with the if exists of MSSQL, recommended for use.

The judgment can be encapsulated into a function for ease of use, the code is as follows

RETURN number
* Use Example
* If EXISTS2 (' select * from dual where 1=1 ') =1 Then
* Dbms_output.put_line (' recorded ');
* Else
* Dbms_output.put_line (' no record ');
* End If;
* END;
V_sql VARCHAR2 (4000);
V_CNT number (1);
V_sql: = ' SELECT COUNT (*) from DUAL WHERE EXISTS (' | | In_sql | | ‘)‘;
EXECUTE IMMEDIATE v_sql into v_cnt;


There is a simpler notation for common insert judgments, such as the following code
If not EXISTS (SELECT * from table1 where id=1)
INSERT INTO table1 values (1, ' a ');
Can be changed to write
When (No exists (SELECT * from table1 where id=1) and then
into table1
Select 1 as ID, ' a ' as data from dual;


Another example is the following code
If not EXISTS (SELECT * from table1 where id=2)
INSERT INTO Table1 values (2, ' B ')
Update table1 set data= ' B ' where id=2;
Can be changed to write
Merge into table1 his
Select 2 as ID, ' B ' as data from dual
) src
On (
When matched then
Update set where
When isn't matched then
Insert values (,;


Incidentally, some people like to write count (*) as Count (column name), not the latter, because the column name is required for additional operations, to query the system table to locate column information

In addition, count (1) and COUNT (*) do not differ, it is recommended to use COUNT (*) for visual clarity

There is no if exists (...) in Oracle The workaround

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.