Oracle-if exists Function

Oracle does not have the if exists (...) syntax. Currently, there are many solutions. Here we first analyze the three commonly used methods. We recommend that you use the last one.

The first type is the most commonly used. Determine whether the value of Count (*) is zero, as shown below:
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 records ');
End if;
End;
First of all, this writing method is quite strange. You only need to know whether there are records in the table, but count the number of records in the whole table.
This method is acceptable for small tables. Once there are many records in the table, the performance problem is very serious.
So someone made some modifications and changed them to select count (*) into v_cnt from t_vip where Col = 1 and rownum = 1.
It seems to have solved the performance problem, but the analysis execution plan is actually the same and is not recommended.

The second type is the so-called attack-type programming. Instead of making a pre-judgment, it directly uses the default judgment and then uses exception to capture exceptions.
For example, if I do not judge whether the table has any records that meet the conditions, it will by default. If not, it will be processed in the exception.
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 records ');
End;
In terms of performance, this method is much better than the first one.
But first it cannot adapt to all situations, such as the first code, it cannot be modified.
Second, this type of code seems to have encountered exceptions rather than normal operations, resulting in confusion and is not recommended.

The third method is to use the original Oracle exists syntax, as shown below:
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 records ');
End if;
End;
Add a dual layer to the statement to use the original Oracle exists syntax.
Although it looks similar to the first one, the analysis execution plan can know that the performance is much better than the above two. It is the closest to if exists in MSSQL and is recommended.

You can encapsulate the judgment into a function for ease of use. The Code is as follows:

Create or replace function exists2 (in_ SQL in varchar2)
Return number
Is
/*************************************** *******************
* Example
* Begin
* If exists2 ('select * from dual where 1 = 1') = 1 then
* Dbms_output.put_line ('recorded ');
* Else
* Dbms_output.put_line ('no records ');
* 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 way to write commonly used insert statements, such as the following code:
If not exists (select * From Table1 where id = 1)
Insert into Table1 values (1, 'A ');
It can be rewritten
Insert
When (not exists (select * From Table1 where id = 1) then
Into Table1
Select 1 as ID, 'A' as data from dual;

-

For example, 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;
It can be rewritten
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 not matched then
Insert values (SRC. ID, SRC. data );

-

Some people like to write count (*) as the Count (column name). The latter one is not recommended, because the column name requires additional operations to query the system table to locate the column information.

In addition, there is no difference between count (1) and count (*). We recommend that you use count (*).