Let you know ahead of time Software development (31): Begin and end in database scripts

2nd part Database SQL language

Begin and end in database scripts

In database scripting,begin and end are a pair of strange words. Without them, some code looks confused, and the structure of the code is instantly clear when you add them.

Indeed, begin and end, as the start and end flags of code statements, make the script logical and easy to read.

begin and end are mainly used in the following places:

1.in statements such as if,else, else if, while, etc.

if,else,else if, while and other statements to occupy a row, the execution statement must not be immediately followed, regardless of the number of execution statements have to add a statement block flag Begin... End.

begin and end in a script file should be exclusive to one row and be in the same starting column, aligned to the left of the statement referencing them. begin... The code block within end uses indentation, and the general indentation is 4 spaces.

Positive example:

if (@varible1 < @varible2)

Begin

[ EXECUTE statement ]

End

Counter Example 1:

if (@varible1 < @varible2)

[ EXECUTE statement 1]

[ EXECUTE Statement 2]

In counter example 1 , there is a lack of begin and end, so the "attribution problem" of "EXECUTE statement 1" and "EXECUTE statement 2" is confusing, and it is easy to mistake the logic of the code.

Counter Example 2:

if (@varible1 < @varible2)

Begin

[ EXECUTE statement ]

End

In counter example 2 ,begin and end are not in the same starting column, nor are they left-aligned with the statement that references them. In this way, the code looks messy.

Counter Example 3:

if (@varible1 < @varible2) begin

[ EXECUTE statement ]

End

In counter example 3 , thebegin and if statements are in the same line of code, which is also not canonical.

2. When creating stored procedures ( functions, triggers, etc. )

When you create a stored procedure ( function, trigger, and so on ) , you must begin with begin , regardless of the number of rows in the execution statement content portion of the stored procedure ( function, trigger, and so on ) . end, and no stored procedure ( function, trigger, etc. ) name after end .

Example 1 ( Creating a stored procedure based on a Sybase database ):

if exists (select 1 from sysobjects where name = ' Pr_example ')

Begin

drop procedure Pr_example

End

Go

CREATE PROCEDURE Pr_example

@name varchar (in),-- name

@age INT-- Age

As

declare @begintime varchar,-- start time

@endtime varchar-- End time

Begin

[ EXECUTE statement section ]

End

Go

print ' CREATE PROCEDURE pr_example OK '

Go

Example 2 ( Create a stored procedure based on an Oracle database ):

Create or replace procedure Pr_example

(

V_name in Varchar2,-- name

v_age out INT-- Age

)

As

begintime varchar2 (20); -- start time

endtime VARCHAR2 (20); -- End time

Begin

[ EXECUTE statement section ]

End

/

Prompt ' CREATE procedure pr_example OK ';

In addition, when you create stored procedures ( functions, triggers, and so on ) , each parameter must be on a single line, and no wrapping or multiple arguments are allowed. The comment for the parameter is either on the same line as the parameter, or on a separate line, and does not allow wrapping on the parameter line. The following code is not canonical:

Example 3 ( Create a stored procedure based on an Oracle database ):

Create or replace procedure Pr_example

(

V_name

in Varchar2,-- name

V_age out int

-- Age

)

As

begintime varchar2 (20); -- start time

-- end time

Endtime

VARCHAR2 (20);

Begin

[ EXECUTE statement section ]

End

/

Prompt ' CREATE procedure pr_example OK ';

In the actual software project, the proper use of begin and endcan make the logic of the code clear and readable. This is conducive to the improvement of efficiency.

(I Weibo: Http://weibo.com/zhouzxi?topnav=1&wvr=5, No.: 245924426, welcome attention!) )