Oracle Code Common Errors

1. Insert into T1 values (...)

Should be: INSERT INTO T1 (...) values (...)

2.to_char (sysdate, ' YYYYMMDDHHMMSS ')

To

To_char (sysdate, ' Yyyymmddhh24miss ')

3. Select COUNT (*) into the v_count from T1 where c1=?;

Never triggers No_data_found error, should judge V_count=0

4. Return value problem

A reasonable approach:

At the beginning of the process result:=false;

At the end of the process result:=true;

There is no need to assign values to result in the middle.

Unreasonable approach:

At the beginning of the process result:=true;

The intermediate code needs to be result:=false when it is judged not to be compliant.

No value is assigned 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

The error will not be returned if it is not found. 0

Should use when No_data_found judge

6. Where C1 = ' | | V_C1; C1 field is character type, to add single quotes, otherwise C1 index, Oracle internally converted to ' ... where C1 = ' | | Chr (39) | | To_char (V_C1) | | Chr (39);

Instead: where C1 = ' | | Chr (39) | | v_c1| | Chr (39);

7. If you only determine 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

7. WHERE EXISTS (SELECT *

from T1

should be:

WHERE EXISTS (SELECT ' x '

from T1

8. Raise_application_error ( -20000, infomsg); Don't use 20000 error numbers.

The range of available error numbers for Raise_application_error is-20000 to-20999