Oracle Database development and design specifications

Document directory

• 1.1 Conventions
• 1.2 table name
• 1.3 stored procedures
• 1.4 View
• 1.5 Index
• Sequence 1.6
• 1.7 primary key
• 1.8 foreign key
• 2.1 provisions
• 2.2 field specifications
• 3.1 Integration
• 3.2 Query
• 3.4 Delete
• 3.5 modify

 

1 naming principles 1.1 Conventions

Ü refers to naming conventions for databases and database objects, such as tables, fields, indexes, sequences, and stored procedures;

Ü Use meaningful English words for naming. Do not use abbreviations, which are composed of multiple words and separated by hyphens (-).

Ü avoid using reserved Oracle words such as level and keywords such as type (see reserved Oracle words and keywords );

Ü names of related columns of tables should be the same as possible;

Ü except for the database name, the name must be 1-8 characters long and the remaining 1-30 characters long. The database link name must not exceed 30 characters;

Ü the name can only contain English letters, numbers, and underscores;

 

1.2 table name

The rules are as follows:

The naming rule is xxx_yyy_tablename. Xxx indicates the sub-system name, which consists of three letters. YYY indicates the sub-module name in the sub-system (which may not exist). tablename indicates the table meaning.

 

Tablename rules are as follows:

Ü use English words or phrases as table names. Do not use Chinese pinyin

Ü use nouns and noun phrases as table names

Ü do not use the plural

 

Correct name, for example:

Sys_user

Biz_order

 

1.3 stored procedures

The rules are as follows:

The naming rule is xxx_yyy_storedprocedurename. Xxx indicates the name of the sub-system, which consists of three letters. YYY indicates the name of the sub-module in the sub-system (which may not exist). storedprocedurename indicates the meaning of the stored procedure.

 

The storedprocedurename rules are as follows:

Ü it is named by a verb or verb phrase with an object

Ü follow the Pascal naming rules.

Ü exercise caution when using abbreviations

Ü try not to overlap with keywords

Ü do not use any name prefix (for example, U, B)

Ü storedprocedurename does not contain underscores

Ü when the operation depends on the condition, the by + condition is generally used at the end.

 

The stored procedure is named correctly, for example:

Sys_insertuser

Sys_searchuserbyuserid

Sys_deleteuserbyuserid

 

1.4 View

The rules are as follows:

Ü the view name is xxx_yyy _Viewname_v. Xxx indicates the sub-system name, which consists of three letters. YYY indicates the sub-module name in the sub-system (which may not exist). _ V indicates the view suffix, and viewname indicates the meaning of the view.

 

The viewname rules are as follows:

Ü use nouns and noun phrases,

Ü do not use the plural

Ü use Pascal naming rules

Ü exercise caution when using abbreviations

Ü try not to overlap with keywords

Ü do not use any name prefix (for example, U, B)

Ü underline in viewname

 

View name, for example:

Sys_userview_v

Biz_userorderview_v

 

1.5 Index

The rules are as follows:

Table_name_column_name_idx.

The column name composed of multiple words. It takes the first few letters and the last word to form column_name.

For example:

Index on member_id in the tst_sample table: tst_sample_mid_idx

Index on sys_news table title: sys_news_titile_idx;

 

Sequence 1.6

The rules are as follows:

Table_name_seq;

For example:

Sequence of the tst_sample table: tst_sample_seq

 

1.7 primary key

Primary Key name: table_name_pk;

For example:

Primary Key of the tst_sample table: tst_sample_pk

 

1.8 foreign key

Foreign key name: table_name_column_name_fk;

The column name composed of multiple words. It takes the first few letters and the last word to form column_name.

 

For example:

The foreign key of the user_id field in the tst_sample table: tst_sample_uid_fk

Foreign key of the type_id field in the tst_sample table: tst_sample_tid_fk

 

2. Design Specifications 2.1 specifications

1. comply with data design specifications 3nf

· Each value in the table can be expressed only once.

· Each row in the table should be uniquely identified (with a unique key ).

· The table should not store non-key information dependent on other keys.

 

2. the entity table must contain four fields: ID, code, created by, Creation Time, and modified), modification time, and delete status ). Its meaning is as follows:

Ü ID is the unique identifier in the system

Ü the code is the customer's code for this entity. This field depends on whether it needs to be retained.

Ü the creator is the creator of the database record.

Ü creation time refers to the Creation Time of the database record. The default value is sysdate.

U. The modifier refers to the database record modifier.

Ü modification time refers to the modification time of the database record. The default value is sysdate.

Ü the delete flag indicates whether the record has been deleted (0: not deleted; 1: deleted). Only this flag is set if data is not deleted.

The naming rules for these five fields are as follows:

Ü entity name + field name, such as user_id, user_code, created_by, creation_time, modified_by, modification_time, and delete_status.

U The following table lists the types and lengths of these five fields.

N no. Integer

N code varchar2 (30)

N created by integer

N creation time date

N modifier integer

N modification time date

N Delete flag char (1)

 

2.2 field specifications

1. A row of records must be unique in the table and the table must have a primary key.
2. Number is used for enumeration, And the meanings of different values of enumeration types must be described.
3. The field ending with ID is composed of only numbers, and the field ending with code is composed of only letters or numbers
4. Char (1) indicates that the Boolean values are capitalized: "Y" and "N ".
5. Varchar2 should be used as much as possible to replace the char type;
6. Varchar (2) can contain up to 4000 characters;
7. Date is accurate to seconds, not days;
8. Use clob instead of long, And blob instead of long raw;
9. Specify the length when using the number data type. For example, number (5, 2) indicates that the integer part has a maximum of three digits and the decimal part has two digits;

 

3 usage specification 3.1 Integration

1. If you need to create an index during development, you need to submit a written change request to describe the definition of the required index (name, Field List, sequence, index type) and the reason for the establishment. The database administrator maintains the index and changes the submitted request.
2. The initial data (including the code table and configuration table) of each database table must be submitted to the database administrator.
3. Do not use triggers.
4. Changes that involve database multi-table data (insert/delete/update) must be controlled using database transactions and must have a complete transaction start and commit/rollback mechanism. You cannot use range transactions.
5. Avoid Union operations as much as possible. If you need to use the union operation, contact the database administrator for the impact of the union operation.
6. Use the PL/SQL develper code beautification tool to beautify SQL statements and stored procedures.
7. The keyword size of Oracle, table name, column name, and other lower case.

 

3.2 Query

1. In table queries, do not use * as the list of queried fields. Which fields must be explicitly stated?
2. In table queries, the where condition must exist unless the table is a non-incrementing table.
3. In a Table query, a maximum of 1000 records can be returned at a time, or the record content cannot exceed 1 MB.
4. In table queries, primary key columns and index columns are preferentially used for order by sorting.
5. For multi-table join queries, the where condition is used first for table join, and the associated fields must be indexed.
6. Avoid applying functions to columns in the WHERE clause:

Error:
Select service_id, SERVICE_NAME
From service_promotion
Where to_char (gmt_modified, 'yyyy-mm-dd ')
= '2017-09-01 ';
Correct:
Select service_id, SERVICE_NAME
From service_promotion
Where gmt_modified
> = To_date ('2017-9-01 ', 'yyyy-mm-dd ')
And gmt_modified
<To_date ('2014-9-02 ', 'yyyy-mm-dd ');

1. Avoid using the automatic type conversion function of the database:

Error:
Select category_id, category_name from category
Where category_id = '20140901'; -- ID's type is number

 

Correct:
Select category_id, category_name from category
Where category_id = 123; -- ID's type is number

 

3.4 Delete

1. When deleting a record, you must have a unique where condition.
2. When a Master/Slave table exists, you must first Delete the slave table record.

 

3.5 modify

1. When modifying a record, you must have a unique where condition.

 

4 Oracle Reserved Words and keywords

Access decimal initial on Start
Add not insert online successful
All default integer option Synonym
Alter Delete Intersect or sysdate
And DESC into order table
Any distinct is pctfree then
As drop level prior
ASC else like privileges trigger
Audit exclusive lock public uid
Between exists long raw Union
By file maxextents rename unique
From float minus resource update
Char for mlslabel revoke user
Check share mode row validate
Cluster grant modify rowid values
Column group noaudit rownum varchar
Comment having nocompress rows varchar2
Compress identified Nowait select View
Connect immediate null session whenever
Create in number set where
Current increment of size
Date index offline smallint

Char varhcar varchar2 number date long
Clob blob bfile
Integer decimal
Sum count grouping average
Type