Summary of Common Errors in oracle Code

Many friends who use other databases often make some habitual mistakes when they go to Oracle database. This paper summarizes some common Oracle code errors for everyone to query and compare.

1. The insertion statement is written as:

insert into t1 values(...)

Should read:

insert into t1(...) values(...)

2.

to_char(sysdate,'YYYYMMDDHHMMSS')

It should read:

to_char(sysdate,'YYYYMMDDHH24MISS')

3.

select count(*) into v_count from t1 where c1=?;

no_data_found error will never be triggered, and v_count=0 should be judged

4. Return value problem:

Reasonable treatment:
At the beginning of the process result: = false;
At the end of the process result: = true;
There is no need to assign a value to result in the middle.

Unreasonable treatment:
At the beginning of the process result: = true;
result: = false assignment is required for every nonconformity judgment in the intermediate code.
There is no need to assign a value to result at the end of the procedure

5.

select c1,c2 in from t1 where c3 = v_c3;
if v_c1 = 0 or v_c2 = 0 then

When the code is not found, an error will be reported, and 0 will not be returned
Should be judged with when no_data_found

6.

'... where c1 = ' || v_c1;

The c1 field is character type and should be enclosed in single quotation marks, otherwise the c1 index is not used, and oracle is internally converted to '... where c1 =' chr (39) to_char (v_c1) chr (39);
Replace with the following:

where c1 = '||chr(39) ||v_c1||chr(39);

7. If you only judge whether there is data, you should add and rownum < 2 faster

select count(*)
into v_count
from t1
where c1 = v_c1;

Conditions to be added: and rownum < 2

8.

WHERE EXISTS (SELECT *
FROM t1

Should read:

insert into t1(...) values(...)

0

9.

insert into t1(...) values(...)

1

Don't all use 20000 error numbers
The available error numbers for RAISE_APPLICATION_ERROR range from-20000 to-20999