If exists function similar to MSSQL in ORACLE

The if exists function similar to MSSQL in ORACLE does not have if exists (...) in Oracle (...) currently, there are many solutions. Here, we first analyze the three commonly used methods. We recommend that you use the last one. The first one is the most commonly used, and determine whether the value of count (*) is zero, the following SQL code 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 some modifications have been made and changed to select count (*) into v_cnt from T_VIP where col = 1 and rownum = 1 seems to have solved the performance problem, but the analysis execution plan is the same and is not recommended. The second type is the so-called attack-based programming. Instead of making a pre-judgment, the system uses the default judgment directly and uses exception to capture exceptions. For example, I do not judge whether the table has any records that meet the conditions, by default, the SQL code declare v_1 number will be processed in the exception if not. begin select vip_level into v_1 from T_VIP where 1 = 0; exception when no_data_found then dbms_output.put_line ('unrecorded '); end; this method is much better in performance than the first one, but it cannot adapt to all situations first, for example, the first piece of code cannot be transformed. Second, this type of code seems to be abnormal, rather than running normally, resulting in confusion and is not recommended. The third method is to use the original Oracle Exists syntax. The following SQL code 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 ('non-records'); end if; end; overwrite dual on the outside of the statement, although the original oracle exists syntax 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 of MSSQL and is recommended. You can encapsulate the judgment into a FUNCTION for ease of use. The Code is as follows: SQL code 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 ('unrecorded'); * 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 determine the commonly used insert statements, such as the following code: SQL code if not exists (select * from table1 where id = 1) insert into table1 values (1, 'A'); can be rewritten to SQL code 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 SQL 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; the SQL code 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 prefer to write count (*) as the count (column name). The latter is not recommended because the column name requires additional operations, query the table to locate the column information. In addition, there is no difference between count (1) and count (*). We recommend that you use count (*) for clarity.