Oracle PL/SQL coding Specification

I. Object encoding Specification

 

Table T _/TBL _ or no prefix.

View V _/V

Sequence seq _

Cluster c _

Trigger trigger Trg _

Stored Procedure procedure SP _/P _/proc _

Function FN _/F _

Materialized View materialized view MV _

Package or package & package body PKG _

Type & type body typ _

Primary key pk _

Foreign key foreign key FK _

Unique index UK _

Normal index idx _

Bitmap index BK _

The synonym mainly depends on the module or mode to which the table belongs.

Database connection without special requirements

 

Ii. variable naming

 

Input variable I _/I

Output variable O _/o

Input/output variable IO _/IO

Common variable V _/V

Global variable GV _/gv

Constant capital

Cursor cur _

User-Defined type _

Save point SPT _

 

Iii. SQL statement Structure

1. Avoid using dialects. Decode can be replaced by the case when statement, which improves encoding.

(+) = Join again with right Outer Join

(-) = Left join with left Outer Join

2. for complex SQL statements (especially multi-layer nesting, clause, or related queries), you should consider whether it is caused by improper operations. For complex SQL statements, consider usingProgramIn principle, one sentence is followed for only one thing.

Static SQL> dynamic SQL

Variable binding SQL> dynamic SQL (this is recommended in the OLTP system)

SQL> PL/SQL processes, except for extreme SQL statements.

SQL> cursor traversal.

Oracle functions> custom functions.

The Oracle analysis function is used to replace the association of a unified table for multiple times.

3. Be sure to handle exceptions again

Exception

When other then

Rollback to savepoint spt_xxx;

V_err_num: = sqlcode;

V_err_msg = substr (sqlerrm, 1,100 );

Insert/update;

Commit;

End;

 

Iv. deployment sequence

1. Create Database roles and user scripts

2. Create Database tablespace and data file scripts.

3. Create a data type script to customize the data type.

4. Create a business table script. Table 4 is the basis for other dependencies.

5. Create a temporary table script, which may be used in the script.

6. Create a View Script.

7. Create a primary foreign key script.

8. Create an index script.

9. Create a trigger script.

10. Create a function and a stored procedure script.

11. Create an initialization data script.

12. Create a job script.

 

Scripts should be stored in different directories according to their functions, and a total script for running all scripts should be created under the corresponding directory.

Address: http://blog.csdn.net/zzuiezhangqihui/article/details/6273023