Database Object Storage Process easily enters database series 8

Stored Procedure concept a stored procedure is a set of pre-compiled SQL statements and optional control flow statements. It is stored in a name and processed as a unit. Stored procedures are stored in the database and can be executed independently or by an application through a single call, and allow users to declare variables, conditional execution, and other powerful programming functions. Once a stored procedure is created

Stored Procedure concept a stored procedure is a set of pre-compiled SQL statements and optional control flow statements. It is stored in a name and processed as a unit. Stored procedures are stored in the database and can be executed independently or by an application through a single call, and allow users to declare variables, conditional execution, and other powerful programming functions. Once a stored procedure is created

Stored Procedure Concept

A stored procedure is a pre-compiled set of SQL statements and optional control flow statements. It is stored in a name and processed as a unit. Stored procedures are stored in the database and can be executed independently or by an application through a single call, and allow users to declare variables, conditional execution, and other powerful programming functions. Once a stored procedure is created, it is compiled on the server and can be executed multiple times as needed, effectively improving the execution efficiency.

Storage Process Classification: System stored procedures and user stored procedures.

The system stored procedures are stored in the master database and prefixed with sp. It can be called directly in other databases without specifying the Database Name

Advantages of Stored Procedures

You can execute a series of SQL statements in a single stored procedure.

You can reference other stored procedures from your stored procedures, which simplifies a series of complex statements.

Differences from functions

The essence of execution is the same. There are many restrictions on functions. The details are as follows;

1. Return Value: A function can return only one value or table object, while a stored procedure can return parameters, return one or more result sets, and return values.

2. Call: A function can be called as a part of a query statement. Therefore, it can be embedded in SQL and called in select;

Because a function can return a table object, it can be located behind the FROM keyword in the query statement;

The stored procedure is executed as an independent part.

3. Data Source; Functions cannot use temporary tables, but only table variables. Stored Procedures have fewer restrictions.

Implement Functions: The functions implemented by stored procedures must be more complex, while the functions implemented by functions are more targeted.

It mainly describes the operations and calls of user stored procedures.

Manage Stored Procedure operations

1. Create a stored procedure: Create one and create a group.

Procedure: 1. Determine whether a stored procedure exists. If yes, delete the stored procedure first.

2. Create a stored procedure.

Syntax(1) create a stored procedure

If exists (SQL statement)

Drop procedure proname

Go

'Start to create

Create procedure proname [parameter list (parameter name, data type, default value) [output]

[With recompile | encryption]

As

(2) create a set of stored procedures. Let's use an example!

A set of stored procedures are created using ";" and numbers.

During execution, you can execute one of them separately, specifying the name, semicolon, and number of the stored procedure. Example: execute group_sp; 3

However, you cannot delete a stored procedure separately. to delete a stored procedure, you can only delete it together.

2. Modify the stored procedure: Change "create" in the definition to "alter.

3. Delete the stored procedure: drop procedure proname

4. view the stored procedure: sp_helptext <存储过程名> View the Stored Procedure text,

Sp_depends <对象名> View the drinking objects of stored procedures.

Sp_help displays all relevant information.

5. Rename the stored procedure: sp_rename' <原名> ',' <新名> '

Call Stored Procedure

Call, parameter input, and return value.

Call: execute the statement.

Parameter input; pass in by bit: Pass in the Declaration Order;

Input by reference: input by parameter name. parameters can be written in an unordered order.

Return Value: return and output

There is no difference between the two: just declare different. Return appears after the stored procedure is defined. Return @ variable name

While output is written after the variable when the stored procedure definition is created: @ variable name output

Note:1. Add @ before parameter variables @.

2. During the call, if the stored procedure is the first statement of batch processing, you can directly execute the stored procedure without using the execute keyword.

3. When the value is passed, @ variable name = default or do not write. Use the default value for definition.

4. You cannot mix the two data transfer methods.

A trigger is a special stored procedure. A trigger is triggered by an event, and a stored procedure is executed independently or called by a program.