Oracle Naming conventions

1. Purpose of preparation

Standardize database naming and coding styles with uniform naming and coding specifications for readability, comprehension, and inheritance.

2. Scope of application

This specification applies to all company-wide applications and project development using Oracle as a background database.

3. Object Naming specification

3.1 Databases and SIDs

Database name defined as System name + module name

★ Global database name and routine SID name requirements consistent

★ Because SID names can contain only characters and numbers, the global database name and SID name cannot contain characters such as "_".

3.2 Table Related

3.2.1 Table Space

★ User-specific data table space named +_+data with user name, such as AUD user-specific data table space can be named Aud_data

★ User-oriented dedicated index table spaces named +_+idx with user name

★ Dedicated temporary table space for users named +_+tmp with user name

★ Dedicated rollback segment table space for users named +_+rbs with user name

★ App-oriented tablespace to apply name +_data/app name +_idx/app name +_tmp/app name +_rbs name

The ★LOB segment Data private tablespace is named after its data table space +_+lobs, as in the previous example, where the data table space is Aud_data, the LOB segment table space can be named Aud_data_lobs

3.2.2 Table Space Files

Tablespace file naming consists of table space name + two-digit ordinal number (ordinal starting from 01), such as AUD_DATA01, etc.

3.2.3 Table

Table naming follows these guidelines:

★ General Table using "System name +t_+ module name +_+" form name "format

★ If the database contains only a single module, the name can be used "system name +t_+ name" format form

★ The module name or the name of the table is named after the first character of the Hanyu Pinyin, the alphabetical name is lowercase, and the characters are not divided;

★ Table alias naming rules: Take the first 3 characters of the table name multibyte the last character. If there is a conflict, increase the character appropriately (such as the first 4 characters of the multibyte name and the last character, etc.)

★ Temporary table with "system name +t_tmp_+ name" format

★ The name of the table as

DFT_GY_CBAP: System name (electricity charge DF) +t_+ module name (High voltage GY) +_+ table name (meter reading arrangement cbap)

DFT_CBBJ: System name (electricity charge DF) +t_+ table name (meter reading Mark CBBJ)

DFT_TMP_HJ: System name (electricity charge DF) +tmp+ table name (Total HJ) (temporary table here)

★ Association table named re_ Table A_ table B,re is the abbreviation of relative, both table A and table B use their literal name or abbreviation form.

3.2.4 Property (column or field)

Attribute naming follows these guidelines:

★ Use meaningful column names as the first character of the actual meaning of the Hanyu Pinyin, and do not add any delimiters between characters

★ Do not prefix the attribute name with the table name, etc.

★ attribute does not add any type identification as suffix

★ Do not use "ID" as the column name

★ The associated field is named with the cd+_+ name (or abbreviation) +_+ field name of the associated table

3.2.5 PRIMARY Key

★ Any table must have a primary key defined

★ Table PRIMARY Key named: "pk+_+ table name (or abbreviation) +_+ primary key identification" such as "Pk_yhxx_idkh" and so on

3.2.6 FOREIGN Key

The table foreign key is named: "fk+_+ table name (or abbreviation) +_ primary table name (or abbreviation) +_+ primary key identification" such as "Fk_yhlx_yhxx_sfzh", etc.

3.2.7 CHECK Constraint

The CHECK constraint is named: "Column name (or abbreviation) for the Chk+_+check constraint"

3.2.8 UNIQUE Constraint

UNIQUE constraint named: "Unq+_+unique constraint column name (or abbreviation)"

3.2.9 Index

The index is named: "Table name (or abbreviation) +_+ column name +_idx". One of the multiple-word attribute column names takes the first few words and then the first character of the last word to make up the index:yd_kh_khid_idx on the Yd_kh table Khid

3.2.10 Trigger

★after Type Trigger

System name +tr_+< table name >_+ +[_row]

★before Type Trigger

System name +tr_+< table name >_+bef_+[_row]

★instead type of Trigger

System name +ti_+< table name >+_++[_row]

★ Various types of triggers in

The I,U,D represents insert, update, and delete row-level triggers, followed by _row identification, and statement-level triggers that are not added, such as Yddftr_csh_i_row

3.2.11 Cluster

The cluster is named after each table (or table alias) in the cluster and the composition of the table plus and, that is, the table "a+and+ Table B ...", such as the cluster that stores the GR (worker) and GRJN (worker skills) tables named Grandgrjn

3.3 views

The view name is prefixed with the system name v_+ module name, and other naming rules and tables are named similar

3.4 Sequence

Sequence naming consists of a seq_+ meaning name

3.5 synonyms

Synonym naming is consistent with the name of the underlying object, but to remove its user prefix or the suffix that contains the remote database link

3.6 Storage Object Correlation

3.6.1 Stored Procedures

Stored procedure naming consists of "system name +sp+_+ stored procedure identification (abbreviation)" The stored procedure identifies the first character of the Hanyu Pinyin in its actual meaning, and divides the components with an underscore. The stored procedure for adding an agent's account is "Sfsp_zjdlszh".

3.6.2 function

Function naming consists of "system name +f+_+ function Identifier"

3.6.3 Bag

Package naming consists of "System name +pkg+_+ package Identity"

The variables in the 3.6.4 function text are named in the following format:

★ Parameter variable naming takes "I (o or IO) +_+ name" form, prefix i or O table input or output parameter

★ Process variable naming takes the form of "l+_+ name"

★ Global Package variable naming takes the form of "g+_+ name"

★ Cursor variable naming takes the form of "name +_+cur"

★ Constant-type variable naming takes the form of "c+_+ name"

★ Variable names are lowercase, and if they are in the form of a phrase, separate each word with an underscore

★ variables are used to store column or row data values in a table, declare variables using%type,%rowtype, and keep the type of the variable declaration in sync with the table, changing as the table changes

3.7 Users and Roles

★ User name consists of "system name +_+user+_+ noun (or abbreviation) or noun phrase (or abbreviation)"

★ Role naming consists of "system name +_+role+_+ noun (or abbreviation) or noun phrase (or abbreviation)"

3.8 Database Links

★ Database link naming consists of "remote server name +_+ database name +_+link"

★ If the remote server name and database name are consistent, the "_+ Database name" section is omitted

3.9 Other considerations in naming

★ Name must not exceed 30 characters.

★ Do not leave spaces between the characters of the object name

★ Be careful to keep the words, to ensure that you do not have a name and reserved words, database system or common access methods conflict

4. Code specification

4.1 General Comments

4.1.1 Annotations as concise, detailed and comprehensive as possible

4.1.2 Each database object is created with a comment on comment to illustrate the function and purpose of the object; When you build a table, you also add a comment on comment to some data columns to illustrate the meaning of the column and/or column values. such as: XX table has the Czzt column attribute is number (10, 0) can add comment on comments as follows comment on COLUMN XX. Czzt is ' 0 = normal, 1 = wait, 2 = timeout, 3 = Logout '

4.1.3 Annotation syntax consists of two cases: single-line comment, multiline comment

Single-line Comment: There are two hyphens (--) before the comment, which can be used for variables and conditional clauses.

Multiline Comment: The content between the symbol/* and/* is the content of the comment. This class of comments is recommended for a complete operation.

4.2 Function Text Comment

4.2.1 Place comments at the beginning of each block and procedure (stored procedures, functions, packages, triggers, views, etc.)

/************************************************************************

*name : --函数名*function : --函数功能*input : --输入参数*output : --输出参数*author : --作者*CreateDate : --创建时间*UpdateDate : --函数更改信息（包括作者、时间、更改内容等）

*************************************************************************/

CREATE [OR REPLACE] PROCEDURE dfsp_xxx

...

4.2.2 The meaning of the incoming parameter should be described. If the range of values is determined, it should also be explained. Variables that have a specific meaning, such as a Boolean variable, should be given the meaning of each value.

4.2.3 Add a comment next to each variable declaration. Describes what the variable is intended to be used as

Usually, simply use a single-line comment, for example L_sfzh CHAR (11)--ID number

4.2.4 add comments before each major part of the block

Add comments before each major part of the block, explaining the purpose of the group statement, preferably explaining the purpose of the statement and the algorithm and the results to be obtained, but do not describe too much of its details

4.2.5 You can also add information such as the database you want to access in the comments at the beginning of the block and procedure

4.3 Writing specifications for common SQL statements

4.3.1 Create statement

CREATE TABLE dft_dksz(YHBS VARCHAR2(20) NOT NULL,ZHGX DATE,DKKHD VARCHAR2(24),CONSTRAINT pk_dksz_yhbs PRIMARY KEY (YHBS))

Recommendation: http://www.cnblogs.com/roucheng/p/3506033.html

Oracle Naming conventions