Interpretation of unified naming and coding specifications for ORACLE databases

1. Writing Purpose

Unified naming and coding specifications are used to standardize database naming and encoding styles for reading, understanding, and inheritance.

2. Applicability

This specification applies to the development of all application systems and projects that use ORACLE as the background database within the company.

3. object naming rules

3.1 database and SID

Database Name is defined as system name + Module name

★The global database name and routine SID Name must be consistent

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

3.2 Table-related

3.2.1 tablespace

★The user-oriented data table space is named after the user name + _ + data. For example, the Aud user-specific data table space can be named Aud_data.

★The user-oriented dedicated index tablespace is named by user name + _ + idx

★The user-oriented dedicated temporary tablespace is named with the username + _ + tmp

★The user-oriented dedicated rollback segment tablespace is named by username + _ + rbs

★The application-oriented tablespace is named by the Application name + _ data/Application name + _ idx/Application name + _ tmp/Application name + _ rbs.

★The dedicated tablespace of the LOB segment is named after the data table space + _ + lobs. In the preceding example, if the data table space is Aud_data, The tablespace of the LOB segment can be named Aud_data_lobs.

3.2.2 tablespace files

The name of a tablespace file is composed of the table space name and two digit numbers (the sequence number starts from 01), such as Aud_data01.

3.2.3 Table

Table naming should follow the following principles:

★Generally, the table is in the format of "system name + t _ + Module name + _ + Table name ".

★If the database contains only one module, the name can be in the format of "system name + t _ + Table name ".

★The module name or table name are both named after the first character of the Chinese alphabet. The Chinese alphabet in the table name is in lower case, and no Delimiter is added between the characters;

★Table alias naming rules: Take the first three characters of the table name and add the last character. If a conflict exists, add appropriate characters (for example, add the first 4 characters of the table name and the last character)

★The temporary table is in the format of "system name + t_tmp _ + Table name ".

★The table name is as follows:

Dft_gy_cbap: system name (power bill df) + t _ + Module name (High Pressure gy) + _ + Table Name (cbap for meter reading)
Dft_cbbj: system name (electricity bill df) + t _ + Table Name (meter reading Mark cbbj)
Dft_tmp_hj: system name (electricity bill df) + tmp + Table Name (total hj) (temporary table here)
 

 

★The associated table is named "Re _ Table A _ table B". "Re" is the abbreviation of "Relative". Table A and table B both use their table meanings or abbreviations.

3.2.4 attributes (columns or fields)

Attribute naming follows the following principles:

★Use a meaningful column name, which is the first character of the actual meaning of Chinese pinyin, and no Delimiter is added between the characters.

★Do not add table names or other prefixes before attribute names

★Attribute without any type identifier as the suffix

★Do not use "ID" as the column name

★The name of the joined field is "cd + _ + Table Name (or abbreviation) + _ + field name ".

3.2.5 primary key

★A primary key must be defined for any table.

★The primary key of a table is named "pk + _ + Table Name (or abbreviation) + _ + primary key ID", for example, "pk_YHXX_IDKH ".

3.2.6 Foreign keys

The foreign key name is "fk + _ + Table Name (or abbreviation) + _ main table name (or abbreviation) + _ + primary key ID", for example, "fk_YHLX_YHXX_SFZH ".

3.2.7 CHECK Constraints

The CHECK constraint is named "Name of the chk + _ + CHECK constraint column (or abbreviation )"

3.2.8 UNIQUE constraints

The UNIQUE constraint is named "unq + _ + UNIQUE constraint column name (or abbreviation )"

3.2.9 Index

The index name is "Table Name (or abbreviation) + _ + column name + _ idx ". The names of attribute columns composed of multiple words are the first few words and then the last word is added to form the index: yd_kh_khid_idx on the khid table of yd_kh.

3.2.10 trigger

★AFTER trigger

System name + tr _ + <Table Name> _ + + [_ row]

★BEFORE trigger

System name + tr _ + <Table Name> _ + bef _ + [_ row]

★Instead of trigger

System name + ti _ + <Table Name> + _ + [_ row]

★Among various types of triggers

I, u, and d indicate the insert, update, and delete row-level triggers, respectively. The _ row identifier is added after the action, and the statement-level triggers are not added, such as yddftr_CSH_ I _row.

3.2.11 Clusters

The cluster is named after the tables (or table aliases) to be stored in the cluster and the combination of tables plus And, that is, the table "A + and + Table B ...", For example, the cluster storing the GR (worker) and GRJN (worker skill) tables is named GRAndGRJN.

3.3 View

View naming is prefixed with system name v _ + Module name. Other naming rules are similar to table naming rules.

Sequence 3.4

Sequential names are composed of seq _ + meanings.

3.5 Synonyms

The name of the synonym is the same as that of the basic object. However, you must remove the user prefix or the suffix that contains the remote database link.

3.6 storage objects

3.6.1 Stored Procedure

The name of a stored procedure consists of the system name + sp + _ + Stored Procedure identifier (abbreviation). The Stored Procedure identifier must start with the first character of the Chinese pinyin alphabet, separate the components with underscores. For example, the storage process for adding an agent account is "sfsp_ZJDLSZH ".

3.6.2 Functions

The function name is composed of "system name + f + _ + Function Identifier ".

3.6.3 package

The package name is composed of "system name + pkg + _ + package ID ".

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

★Parameter variables are named in the form of "I (o or io) + _ + name", prefix I or o table input or output parameters

★Process variables are named in the form of "l + _ + name"

★The global package variable name is in the form of "g + _ + name ".

★The name of the cursor variable is in the form of "name + _ + cur ".

★Constant variables are named in the form of "c ++ name ".

★The variable name is in lowercase. If the variable name is in the phrase format, it is separated by underscores (_).

★When a variable is used to store column or row data values in a table, the variable is declared using % TYPE and % ROWTYPE, so that the declared TYPE of the variable is synchronized with that in the table and changes as the table changes.

3.7 users and Roles

★The user name is composed of "system name + _ + user + _ + noun (or abbreviation) or noun phrase (or abbreviation )".

★The role name is composed of "system name + _ + role + _ + noun (or abbreviation) or noun phrase (or abbreviation )".

3.8 Database Link

★The Database link name is composed of "remote server name + _ + database name + _ + link ".

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

3.9 Other considerations

★The name must not exceed 30 characters.

★Do not leave spaces between characters in the object name

★Be careful when retaining words. Make sure that your name does not conflict with the reserved words, database systems, or common access methods.

4. coding specifications

4.1 General Comments

4.1.1 The comments should be as concise, detailed, and comprehensive as possible

4.1.2 comment on must be added to each database object to describe the functions and usage of the object. comment on must be added to some data columns during table creation, to describe the meaning of the column and/or column values. For example, the czzt column attribute in Table XX is NUMBER (10, 0). You can add comment on column xx as follows. czzt is 0 = normal, 1 = waiting, 2 = timeout, 3 = logout

4.1.3 The comment syntax contains two situations: single-line comment and multi-line comment.

Single line comment: there are two hyphens (--) before the comment. This type of comment can be used for variables and condition clauses.

Multi-line comment: the content between the symbol/* and */is the comment content. We recommend that you use this annotation for a complete operation.

4.2 function text comment

4.2.1 place comments at the beginning of each block and process (stored procedure, function, package, trigger, view, etc.)

/*************************************** *********************************
* Name: -- function name
* Function: -- function
* Input: -- input parameters
* Output: -- output parameters
* Author: -- author
* CreateDate: -- Creation Time
* UpdateDate: -- function change information (including author, time, and change content)
**************************************** *********************************/
CREATE [or replace] PROCEDURE dfsp_xxx
...
 

 

4.2.2 The meanings of input parameters should be described. If the value range is determined, it should also be described. Variables with specific meanings (such as boolean variables) should give the meaning of each value.

4.2.3 add comments next to each variable declaration. Description of the variable to be used

Generally, just use a single line comment, for example, l_sfzh CHAR (11) -- ID card number

4.2.4 add comments before each major part of a block

Add a comment before each major part of the block to explain the purpose of the group statement. It is best to describe the purpose of the statement and algorithm and the result to be obtained, but do not describe the details too much.

4.2.5 you can add information such as the database to be accessed in the annotations at the beginning of the block and process.

4.3 coding rules for common SQL statements

4.3.1 CREATE statement

Create table dft_dksz (
Yharvard VARCHAR2 (20) not null,
Zhgx date,
DKKHD VARCHAR2 (24 ),
CONSTRAINT pk_dksz_yharvard primary key (yharvard)
)
 

 

4.3.2 SELECT statement

Query statements are written in the following principles (SQL statements in the shared pool can be reused to improve application performance ):

★Divide the SELECT statement into five parts:

(1) It starts with SELECT, followed by a list displaying the query results;

(2) tables that start with "FROM", followed by one or more tables involved in obtaining data;

(3) conditions starting with WHERE followed by one or more conditions for determining the required values;

(4) columns starting with group by followed BY one or more table columns are used to summarize the query results;

(5) The query results are sorted BY one or more table column names starting with order.

★Write each part of the branch, and align the first keyword of each line with the SELECT tail of the first line, as shown in

SELECT col1, col2, col3
FROM table1
WHERE col1> col2
Group by col1, col2
Order by col1;
 

 

★The keywords are in upper case, and the column names and table names are in lower case.

★When a comma is embedded in a statement, add a space after the comma. When the comma is the last character, place it in the row.

★When the same part of the statement is to be extended to the next line, it is arranged in the following format:

SELECT col1, col2, col3, col4, col5, col6,