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

http://blog.csdn.net/hollboy/article/details/7550171 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
Declare
V_CNT number;
Begin
Select COUNT (*) into v_cnt from T_vip where col=1;
If v_cnt = 0 Then
Dbms_output.put_line (' no record ');
End If;
End
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
Declare
V_1 number;
Begin
Select Vip_level into V_1 from T_VIP where 1=0;
exception
When No_data_found Then
Dbms_output.put_line (' no record ');
End
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
Declare
V_CNT number;
Begin
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;
End
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

CREATE OR REPLACE FUNCTION EXISTS2 (in_sql in VARCHAR2)
RETURN number
Is
/**********************************************************
* Use Example
* BEGIN
* 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);
BEGIN
V_sql: = ' SELECT COUNT (*) from DUAL WHERE EXISTS (' | | In_sql | | ‘)‘;
EXECUTE IMMEDIATE v_sql into v_cnt;
RETURN (V_CNT);
END;

-

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
Insert
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 ')
Else
Update table1 set data= ' B ' where id=2;
Can be changed to write
Merge into table1 his
Using
(
Select 2 as ID, ' B ' as data from dual
) src
On (his.id=src.id)
When matched then
Update set His.data=src.data where id=src.id
When isn't matched then
Insert values (src.id,src.data);

-

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