Let you know software development in advance (29): Layout of database script files

Part 1 Database SQL language

Database script file Layout

 

The Code layout of database script files has the following significance:

(1) demonstrates a good logic structure of the Code, improving the accuracy, continuity, readability and maintainability of the Code.

(2) It is conducive to improving product development quality and efficiency, and reducing development costs.

(3) for developers, developing good script writing habits will help improve their individual database script programming level, and thus improve the script programming efficiency.

It can be seen that the unified and good script code layout and style not only affect the subjective aesthetics or form of the individual, but also affect the product quality and involve the improvement of the Script Programming capability.

 

1. Script layout sequence Overview

In actual software development projects, you can easily read the code by standardizing the script layout sequence, facilitating subsequent maintenance and test modification. You can layout the content of a script file in either of the following ways:

Method 1:

File Header

Initialization

User and access permission Establishment

Create a data table

Create a stored procedure

Create a database task

End

 

Method 2:

File Header

Initialization

Create a data table

Create a stored procedure

Create a database task

User and access permission Establishment

End

 

Note:

(1) A common script file contains seven parts in method 1 and method 2. To facilitate differentiation and reading, each part must be separated by a comment block (the comment operator uses "--").

(2) "user and access permission establishment" can be placed in the third part, or in the sixth part.

(3) The annotation description must be used before each part. Relevant content must be written in the corresponding part. If the content is blank, the annotation description of this part must also be kept.

 

2. Script layout example

Method 1: The script code example based on Sybase database is as follows:

--*************************************** ******************************

-- Copyright (C) 2014, Zhou Zhaoxiong.

-- Database version: Sybase ASE Enterprise 15.0

-- Content Abstract: script file layout example

-- Author: Zhou Zhaoxiong

-- Completion date: 20140616

-- Modify record 1:

-- Modification date:

-- Version:

-- Modifier:

-- Modified content:

--*************************************** *******************************/

--*************************************** ***********

-- Initialization

--*************************************** ***********

Use master

Go

Use xxx -- database to be used

Go

Checkpoint

Go

 

Dump tran xxx with no_log -- log Truncation

Go

 

--*************************************** *********************

-- User and right creation user and permission creation

--*************************************** *********************

Exec sp_addalias xxx, dbo

Go

 

--*************************************** ********************************

-- Create a table creation data table

--*************************************** ********************************

-- Example Table tb_example

If exists (select 1 from sysobjects where id = object_id ('tb _ example '))

Begin

Drop table tb_example

End

Go

 

Create table tb_example

(

Name varchar (30) not null, -- name

Age int not null -- age

)

Go

 

-- Create an index

Create index idx_tb_example1 on tb_example (name)

Go

 

--*************************************** *********************

-- Procedure creation: Create a stored procedure

--*************************************** *********************

-- Information Query Stored Procedure pr_selectinfo

-- Input parameter: @ v_name name, @ v_age age

-- Output parameter: None

If exists (select 1 from sysobjects where id = object_id ('pr _ selectinfo '))

Begin

Drop procedure pr_selectinfo

End

Go

 

Create procedure pr_selectinfo

@ V_name varchar (30), -- name

@ V_age int -- Age

As

Begin

......

End

Go

 

Print 'create procedure pr_selectinfo OK'

Go

 

--*************************************** ***********

-- Task creation: Create a database task

--*************************************** ***********

......

 

--*************************************** ***********

-- Finalization ends

--*************************************** ***********

......

 

In actual software projects, there are strict regulations on the layout of script files. developers need to follow the programming specifications to write script code. This not only facilitates reading and modifying the code, but also facilitates technical exchange and sharing by the team.

 

 

(My microblogging: http://weibo.com/zhouzxi? Topnav = 1 & wvr = 5, No.: 245924426, welcome !)