Database Review ⑨

Database Review ⑨

June 19, 2016

12:45

Main Database Programming

1. Introduction of Transact-SQL

Transact-SQL is the language used by the application to interact with SQL Serverdbms.

Transact-SQL = high-level programming language

can apply Transact-SQL to database programming to realize data processing function

Variables in the 2.transact-sql

Local variables:

A local variable is a user-defined variable whose scope is limited to the inside of the program.

It is commonly used in programs to store data that is queried from a table, or as a staging variable during program execution.

Local variables must begin with @ and must be described with the DECLARE command before they can be used. The following forms are described:

DECLARE @ variable name variable type [, @ variable name variable type ...]

In Transact-SQL, it is not possible to assign values to variables using the variable = variable value as in a normal programming language, and you must use the Select or SET command to set the value of the variable. Its syntax is as follows:

SELECT @ local variable = variable value

SET @ local variable = variable value

Global variables:

A global variable is a variable used internally by a SQL Server system and is scoped not to a program, but to any program that can be called at any time.

Global variables are not defined by the user's program, they are defined at the server level, and can only be used with pre-defined and predefined global variables.

You must start with "@@" when referencing global variables.

The name of the local variable cannot be the same as the name of the global variable, otherwise an error occurs in the app

The comment character:

1. The ANSI standard annotation "--" is used for single-line comments.

2. The same program annotation symbol as the C language, "/*......*/",/* for the beginning of the comment text, */For the end of the comment text, in the program to identify the multiline text as a comment

3. Process Control Commands

BEGIN ... END has the following syntax:

BEGIN < command line or program block block > END

BEGIN ... End is used to set a program block that will be at BEGIN ... All programs in END are treated as one unit execution.

BEGIN ... END is often in conditional statements such as if ... ELSE) is used in the.

At the begin ... END can be nested in another BEGIN ... END to define another program block.

IF ... ELSE its syntax is as follows:

IF < conditional expressions >

< command line or block blocks >

[ELSE [conditional-expression]

< command-line or block block;]

Description

< conditional expressions > can be a combination of various expressions, but the value of an expression must be a logical value of TRUE or false.

The ELSE clause is optional, and the simplest if statement does not have an ELSE clause part.

IF ... else is used to judge when a certain condition is set up to execute a certain procedure, and another procedure is executed when the condition is not established.

If you do not use a block, if or else can only execute one command.

IF ELSE can be nested and can be nested up to 32 levels in Transact-SQL.

Case

Format 1:

Case < op >

When < expressions >

Then < expressions >

...

When < expressions >

Then < expressions >

[ELSE < op;]

END

The execution of this statement is:

Compares the value of an expression after a case to the value of an expression in each when clause.

If they are equal, returns the value of the then expression, then jumps out of the case statement, otherwise returns the value of an expression in the Else clause.

The ELSE clause is an optional option. When the ELSE clause is not included in the case statement, if all comparisons fail, the scenario statement returns NULL.

Format 2:

Case

When < conditional expressions > then < expression >

...

When < conditional expressions > then < expression >

[ELSE < op;]

END

The execution of this statement is:

The value of the expression after the first Test

If its value is true, returns the value of the expression after then, otherwise tests the value of an expression in the next when clause

If the value of the expression after all the When clause is false, the value of the expression after the else is returned

If there is no else clause in the case statement, the scenario expression returns NULL.

While ... CONTINUE ... Break

Its syntax is as follows:

While < conditional expressions >

BEGIN

< command line or program block >

[Break]

[CONTINUE]

[Command line or program block]

END

The while command executes the command line or block repeatedly when the condition is set.

The CONTINUE command allows the program to skip the statement after the CONTINUE command, returning to the first line of the while loop and continuing with the next loop.

The break command lets the program jump out of the loop completely, ending execution of the while command.

While statements can also be nested.

WAITFOR

Its syntax is as follows:

WAITFOR {Delay < ' time ' > | Times < ' time ' >

| Errorexit | ProcessExit | Mirrorexit}

The WAITFOR command is used to temporarily stop the execution of the program until the waiting time has been set or the time has been set to continue.

Where ' time ' must be a datetime (HH:MM:SS) type of data, but cannot include a date.

The meanings of each keyword are as follows:

1. Delay: Used to set the waiting time for up to 24 hours

2. Time: Used to set the point at which the wait is ended

3. Errorexit: Until a non-normal interrupt is handled

4. ProcessExit: Until normal or non-normal interrupts are handled

5. Mirrorexi: Until the mirror device fails

Goto

The syntax is as follows:

GOTO identifier

The GOTO command is used to change the process of executing the program, so that the program jumps to the specified program line labeled with the identifier and continues execution.

The identifier for a jump target can be a combination of numbers and characters. But must end with ":".

On the goto command line, the identifier does not have to be followed by ":"

4. Database commands

1. BACKUP

The backup command is used to back up database content or its transaction log to storage media (floppy disk, hard disk, tape, and so on).

2. CHECKPOINT

The CHECKPOINT command is used to force a changed data page or log page in the currently working database from the data buffer to the hard disk.

3. Dbcc

The DBCC (database Base consistency Checker DB consistency checker) command is used to verify database integrity, find errors, analyze system usage, and so on.

The DBCC command must be preceded by a sub-command before the system knows what to do.

For example, the DBCC checkalloc command checks the allocation and usage of all data pages in the current database.

5. EXECUTE

The Execute command is used to execute the stored procedure.

6. KILL

The KILL command is used to terminate the execution of a procedure.

7. PRINT

The PRINT command returns a user-defined message to the client that displays a string, local variable, or global variable.

If the value of the variable is not a string, it must first be converted to a string using the data type conversion function, convert ().

8. RAISERROR

The RAISERROR command is used to return the user-specified information while the SQL Server system returns an error message.

5. Common functions

6. Cursor Programming

The SQL statement takes the relation as the manipulating object and the operation result;

For example, the rowset returned by the SELECT statement includes all rows that satisfy the conditions in the WHERE clause of the statement;

Applications sometimes need to traverse relational tables to handle each row of data separately. The SQL language cannot implement such operational requirements;

An application needs a mechanism to process one or a portion of rows at a time. Cursors are an extension of the result set of a select query that provides this mechanism.

Cursor Action:

Cursors provide a flexible means of manipulating the data retrieved from a table.

A cursor extracts only one record at a time from the result set of a multi-data record.

Cursors are always related to a SELECT statement.

A cursor consists of a result set and a cursor position that points to a particular record in the result sets.

You must declare a cursor that points to the result set when you use it.

Cursor Features:

Cursors allow an application to manipulate each row in a row result set returned by a query statement, providing the ability to delete and update data in a table based on the cursor position.

The main functions of cursors include:

Locate the specified row in the result set;

Retrieves one or more rows from the current position of the result set;

Data modifications can be made to the rows of the current position in the result set;

Can display data changes made by other users to the database data in the result set;

Provides a T-SQL statement of the access result set used in stored procedures and triggers.

7. Stored Procedures

Database Review ⑨