Detailed description of Oracle Database usage and naming rules

Ekw719

Websites already established or to be established by the company, such as the Chinese site (China), the international site (Alibaba), and the Chinese site (China.

OLPS

Online Process
System: the online processing system, that is, the database connected to the website that our members can browse, is what we call the frontend.

BOPs

Back office process system: the background processing system, that is, the publishing system of our website.

BzO

Business Object (?), The company's website's low-level Java object is mainly responsible for dealing with databases.

I. Naming Conventions

1. Naming Conventions for databases and database objects such as table, sequence, procedure, and column;
2. Name meaningful English words. Except for some commonly used words (see the list), you must avoid using abbreviations. Words are separated by hyphens;
3. Except for the database name with a length of 1-8 characters, the remaining names must be 1-30 characters and the database link name must not exceed 30 characters;
4. The name can only contain English letters, numbers, and underscores. 'ekw719 '>
5. Avoid using reserved Oracle words such as level and keywords such as type (see the list );
6. The names of related columns should be the same as possible;
7.
Database Name: the online database name is "OLPS" + 2-4 characters indicating the site (the same as below), and the background database name is "BOPS" + 2-4 characters indicating the site. Test
The name of the trial database is "OLPS | BOPS" + "test", and that of the development database is "OLPS | BOPS" + "test ".
To distinguish different sites.

8. Index name: table_name + column_name + index_type (1
Byte) + idx, each part is separated by an underscore. 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 sample table:
Unique index: news_titile_uidx;
9. Sequence name: seq _ + table_name;

Ii. Notes

1. This description is mainly used for PL/SQL programs and other SQL files, and can be used as a reference;
2. There are three annotations accepted by sqlplus:
-Here is a comment.
/* Here is the comment */
Rem here is the comment
3. Start annotation, similar to the start annotation in javak, which mainly lists the file name, write date, copyright description, program functions and modification records:
Rem
Rem $ header: filename, version, created date, auther
Rem
Rem Copyright
Rem
Rem Function
Rem function explanation
Rem
Rem notes
Rem
Rem modified (yy/MM/DD)
Rem who when-for what, recently goes first
4. Block comments, such as table comments and procedure comments, are the same as Java:
/*
* This table is for trustpass
* Mainly store the information
* Of trustpass members
*/
5. Single Row comments, such as column comments:
Login_id varchar2 (32) not null, -- member ID

Iii. indent

A low-level statement after a high-level statement is generally Indented by four spaces:
Declare
V_memberid varchar2 (32 ),
Begin
Select admin_member_id into v_memberid
From Company
Where id = 10;
Dbms_output.put_line (v_memberid );
End;
　　
Indentation of different parts of the same statement.
Statement, usually two spaces. If there is a close relationship with a part of the previous sentence, it is reduced to its alignment:

Begin
For v_tmprec in
(Select login_id,
Gmt_created, -- Here indented as column abve
Satus
From Member -- sub statement
Where site = 'China'
And Country = 'cn ')
Loop
NULL;
End loop;
End;

4. Disconnected rows

• A row cannot exceed 80 characters
• Different words in the same statement
• Space after comma
• Space before other separators

Select offer_name
| ','
| Offer_count as offer_category,
ID
From category
Where super_category_id_1 = 0;

V. Case sensitivity

The keyword size of Oracle, table name, column name, and other lower case.

6. Selecting column types

• Replace the Boolean value with Char (1;
• Varchar2 should be used as much as possible to replace the char type;
• Varchar (2) can contain up to 4000 characters;
• Date is accurate to microseconds, not days; 'ekw719'>
• Use clob instead of long, And blob instead of long raw;
• Oracle has only one data type, and the length must be specified;

VII. Primary Key Selection

Select columns that are meaningful, not long, and can uniquely identify record rows as primary keys. If this column is not available, sequence is used as the primary key.

8. Column Length Selection

Select the column length as needed. There is a corresponding web page, and the length of the corresponding column on the page is consistent.

In addition to database implementation, verify data at the presentation layer as much as possible.

9. SQL statement conventions

1. Avoid using SQL statements in cycles whenever possible.
2. Avoid applying functions to columns in the WHERE clause:
Select *
From service_promotion
Where to_char (gmt_modified, 'yyyy-mm-dd ')
= '2017-09-01 ';
Instead, use:
Select *
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 ');
　　
3. Avoid using the automatic type conversion function of the database:
Select * from category
Where id = '000000'; -- ID's type is number
4. Avoid invalid connections:
Select count (*)
From offer a, count_by_email B
Where a. Email (+) = B. Email;
5. Alias used for join:
Select a. *, B. offer_count (*)
From offer a, count_by_email B
Where a. Email (+) = B. Email;
6. Get the meta information of the table:
Select *
From table_name
Where rowid is null (or rownum = 1 or PK =
Impossible_value );
Consider the following:
Select *
From table_name
Where 1 = 0;

10. Conventions on the Alibaba table

If Alibaba BzO is used, the table must have at least the following fields:
Site varchar2 (32)
Gmt_create date
Gmt_modified date

If it is inherited from idbizobject, there is also a number-type column, usually the primary key, and corresponds to a sequencecf; if it is inherited from
Stridbizobject inheritance, there is a varchar2 column.

11. Column value conventions

1. Columns with fixed value lists, such as status, action, and site, whose values are in lower case;
2. Select numbers as much as possible to represent the values in the fixed value list. The corresponding columns are defined as number type;
3. Use char (1) to indicate the upper case of the Boolean value: "Y", "n ".

12. Definition of duplicate table attributes

Duplicate attribute Columns cannot be selected;
　　

For repeated attribute columns that can be selected multiple times, id_varray should be used instead of the query columns that do not need to be used as the query columns. If the number of repeated columns is large, another table should be created, A column value with a few times
For the second time, the number type should be used, plus bit operation.

XIII. data modification conventions

If data in the production environment is found to be incorrect and needs to be corrected, the data update form should be submitted on RA terminal.
The modification was completed 17 hours ago on the current day.

　　
The structure changes and data updates of database update programs are similar.
　　

If you need to prepare data or update the structure for submitting the sqtt test, write the data to the test request. Then, the sqtt Comrade mail (indicating the link to the test request form) modifies the database through the database operator.
After a new form is added to the Intranet team, the test request is submitted separately.

Iv. Database Design Process

The DBA should attend the new project design stage meeting. Encoding can be started only after the schema is determined.
　　
Abbreviation list:
Payment pymt
My trade activity MTA
　　
Special Word List:
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