oracle database to build the unity of the ORACLE database table naming and numbering

2011-01-20  来源:本站原创  分类:Database  人气:117 

1, write the purpose of

A uniform naming and coding standards, naming and coding style to make the database standardization, in order to facilitate reading, understanding and inheritance.

2 Scope

This specification applies to all company-wide database to ORACLE as a background application and project development.

3, object naming

3.1 Database and SID

Database name is defined as the system name + module name

★ global database name and SID name requires the same routine

★ because SID name can only contain letters and numbers, so the global database name and SID name can not contain "_" characters such as

3.2 Table-related

3.2.1 Table space

★ user-specific data for the table space to user + _ + data name, such as Aud user-specific data table space named Aud_data

★ index table for the user's private space to user + _ + idx named

★ user-oriented user-specific temporary table space named name + _ + tmp

★ user-oriented private rollback table space to user + _ + rbs named

★ table space for the application to the application name + _data / application name + _idx / application name + _tmp / application name + _rbs named

★ LOB data segment of its dedicated data table space tablespace + _ + lobs named above example data table space Aud_data, then the LOB segment table space named Aud_data_lobs

3.2.2 Table space file

Tablespace file name to a table space + digit serial number (serial number starting from 01), with such as Aud_data01

Table 3.2.3

Table names should follow the following principles:

★ generally used in the table "system name + t_ + module name + _ + semantic name" constitutes a form

★ If the database contains only a single module, can be named "System Name + t_ + semantic name" constitutes a form

★ semantic module name or names are the first of its Pinyin character named semantic names are used in the pinyin lowercase, and without a separator between characters;

★ table alias name rule: take the semantic name of the first three characters plus the last character. If a conflict exists, the appropriate increase in character (such as taking semantic name of the first four characters plus the last character, etc.)

★ temporary table using "system name + t_tmp_ + semantic name" constitutes a form

★ table name such as

dft_gy_cbap: System name (electricity df) + t_ + module name (high-pressure gy) + _ + semantic name (meter reading arrangement cbap) dft_cbbj: System name (electricity df) + t_ + semantic name (meter mark cbbj ) dft_tmp_hj: System name (electricity df) + tmp + semantic name (total hj) (here as a temporary table) ★ associated table table named Re_ A_ Table B, Re is the abbreviation for Relative, tables A and B are using the semantic name or abbreviation.

3.2.4 attributes (columns or fields)

Attribute names follow the following principles:

★ column a meaningful name for the actual meaning of the pinyin of the first character, and character without any separator between

★ Do not increase before the property name as a prefix table names, etc.

★ attributes without any type of identification as a suffix

★ Do not use "ID" as the column name

★ associated field name to "cd + _ + semantic association table name (or initials) + _ + field name" to

3.2.5 Primary Key

★ any table primary key must be defined

★ table primary key named: "pk + _ + table name (or initials) _ + + primary key identification" such as "pk_YHXX_IDKH" etc.

3.2.6 Foreign Key

Table foreign key named: "fk + _ + table name (or initials) + _ the main table name (or initials) + _ + primary key identification" such as "fk_YHLX_YHXX_SFZH" etc.

3.2.7 CHECK constraints

CHECK constraint named: "chk + _ + CHECK constraint column names (or initials)"

3.2.8 UNIQUE constraint

UNIQUE constraint named: "unq + _ + UNIQUE constraint column names (or initials)"

3.2.9 Index

Index name: "Table name (or initials) + _ + listing + _idx". Property consisting of more than one word column name to take out a few words before the end of the first word character plus the first characters as yd_kh table khid the index: yd_kh_khid_idx

3.2.10 trigger

★ AFTER-type triggers

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

★ BEFORE-type trigger

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

★ INSTEAD OF trigger type

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

★ types of flip-flop

i, u, d, respectively, insert, update and delete row-level trigger, added _row logo, statement-level triggers do not rise, as yddftr_CSH_i_row

3.2.11 Cluster

Cluster to cluster to store all tables (or table aliases) and table between Canada and the composition of the name, it means that "A + And + Form B ...", such as storage GR (workers) and GRJN (workers' skills) of the cluster table named To GRAndGRJN


Named to the system view name v_ + name of the module, other naming conventions and the naming of a similar table

3.4 Sequence

Sequences were named to the meaning of the composition seq_ +

3.5 Synonyms

Name synonymous with the name of the same underlying object, but to remove the user prefix or suffix with a remote database link

3.6 Storage Object related

3.6.1 Stored Procedures

Stored procedure named by the "system name + sp + _ + stored procedure identifier (abbreviated)" to identify the composition of the stored procedure to the actual meaning of the first characters of the pinyin form, and separate the various components with an underscore. Such as increasing the agent's account of the stored procedure as "sfsp_ZJDLSZH".

3.6.2 Functions

Function named by the "system name + f + _ + function identifier" component

3.6.3 Package

Package named by the "system name + pkg + _ + package identity" form

3.6.4 Function text variable named using the following format:

★ parameter variable names using "i (o or io) + _ + name" in the form, i or o the table prefix input or output parameters

★ process variable name used "l + _ + name" in the form

★ global package variable named using "g + _ + name" in the form

★ cursor variable named a "name + _ + cur" in the form

★ constant variable names use "c + _ + name" in the form

★ variable names use lower case, if the form is the phrase, each word separated by underscores

★ variable used to store the table columns or rows of data values, use the% TYPE,% ROWTYPE to declare variables means that the type and variable declarations in the table to keep pace with the change table

3.7 User and Role

★ user named by the "system name + _ + user + _ + noun (or abbreviation) or noun phrase (or acronym)" form

★ role named by the "system name + _ + role + _ + noun (or abbreviation) or noun phrase (or acronym)" form

3.8 Database Link

database link named by the "remote server name + _ + database name + _ + link" component

★ If the remote server name and database name, and the style "_ + database name" section omitted

3.9 Naming Other Considerations

★ named shall not exceed 30 characters.

★ Do not leave the object name space between characters

★ careful to retain the word, to ensure that your name is not reserved words and the database system used to access methods conflict or 4 coding standard

4.1 General comments

4.1.1 comments as succinct as possible, a detailed and comprehensive

4.1.2 should be created with each database object COMMENT ON comments to explain the function and purpose of the object; build tables, add some data COMMENT ON column also notes, in order to illustrate this column and / or The meaning of the column values. Such as: XX table has CZZT column attribute NUMBER (10, 0) can add comments as follows COMMENT ON COMMENT ON COLUMN XX.CZZT IS '0 = normal, 1 = waiting, 2 = out 3 = log '

4.1.3 Note the syntax contains two cases: single-line comments, multi-line comments

Single-line comment: Note first two hyphens (--), generally variable, conditional clause can be used to such comments.

Multi-line comments: symbols / * and * / the contents of the note content. For a complete operation of the proposed use of such comments.

4.2 Function text comments

4.2.1 In each block, and procedures (stored procedures, functions, packages, triggers, views, etc.) at the beginning to put a comment /********************** *********************************************************** *********************************************************** * name: - name of the function * function: - Function * input: - Input parameters * output: - output parameters * author: - Author * CreateDate: - Created * UpdateDate: - function to change the information ( including the author, time, change content, etc.) ***************************************** ********* ***********************/ CREATE [OR REPLACE] PROCEDURE dfsp_xxx ... 4.2.2 the meaning of the parameters should be passed be spelled out. Determine if the range should also be described. Values ​​have specific meaning of the variables (such as boolean type variable), should be given the meaning of each value.

4.2.3 In the next to each variable declaration to add comments. Say

Out what the variables to be used as

Typically, simple to use single-line comment on the line, such as l_sfzh CHAR (11) - ID number

4.2.4 In the main part of each block before adding comments

In the block before each major part of the increase in notes, excuse - Group statement purposes, it is best to explain the purpose of paragraph statement and algorithms, and to get results, but do not describe the details too much

4.2.5 In the beginning of the process block and notes also can increase the database to access other information

4.3 Common SQL statements written specifications

4.3.1 CREATE statement 4.3.2 SELECT statement query written using the following principles (which can maximize the reuse of shared pool of SQL statements, improve application performance):

★ SELECT statement will be divided into five parts:

(1) beginning from the SELECT, followed by a display query results list;

(2) from the FROM the beginning, followed by one or more to get involved in the data table;

(3) by the WHERE beginning, followed by one or more of the conditions to determine the required value;

(4) GROUP BY from the beginning, followed by one or more table column names, through these columns to summarize the results of the query;

(5) ORDER BY from the beginning, followed by one or more table column names, through these columns to sort the query results.

★ branches each part is written to each line of the first row of the SELECT keyword and the end of the first alignment, such as ★ keywords in uppercase, column and table names in lowercase

★ statement embedded commas, the comma plus one space, when the comma is the last character, put it in our

★ When the same part of the statement to continue to the next line, arranged in the following format: ★ AND the statement in the WHERE and some formatting, writing, similar to the layout ★ When the statement appears in parentheses, the parentheses without spaces on either side of

★ operator in the SQL statement used when operating on both sides should all leave a space, such as 4.3.3 INSERT statement 4.3.4 UPDATE statement 4.3.5 DELETE statement 4.4 conditional execution (IF) preparation of specifications

IF ... ELSE conditional execution of statements written in the following format (1) IF ... THEN and ELSE (or ELSIF) and ELSE ... THEN and END IF can contain between one or more PL / SQL

Statement, without added BEGIN and END

(2) IF ... ELSE ... ENDIF statement can be nested

(3) Note the wording ELSIF

Written specification 4.5 loop

4.5.1 Simple loop

4.5.2 FOR loop

FOR variable IN [Variable range] 4.5.3 WHILE loop

WHILE <conditional expression> 4.6 function text (stored procedures, functions, and packages, etc.)

★ For stored procedures, functions, and other exception handling block should have a part in the abnormal part of the last to be set OTHERS exception modality processors to improve the program's self-test capability in the following format: ★ For subroutines, triggers, packages other with the names of the blocks, to identify the end use, such as CREATE OR REPLACE PROCEDURE XXXsp_XXX IS ... BEGIN ... END XXXsp_XXX; / * process name XXXsp_XXX here is optional, write specifications, and the block corresponding to the beginning of CREATE * /