Database --- Storage Process Summary

xiaoxiao2021-03-30  222

definition:

Working with common or complex work, pre-use SQL statements and stores with a specified name, then the database is called to provide the same service as the defined stored procedure, simply call the execute, Automatically complete the command.

Speaking here, some may ask: This is said that the stored procedure is a bunch of SQL statements?

Why do Microsoft add this technology?

So what is the difference between the stored procedure with a general SQL statement?

Advantages of stored procedures:

1. The stored procedure is only compiled when it is created, and each execution stored procedure does not need to be recompiled again, and the general SQL statement is compiled once, so it can improve the database execution speed by using the stored procedure.

2. When performing complex operations for the database (such as Update, Insert, Query, Delete to multiple tables), this complex operation stored procedure can be encapsulated together with the transaction processing provided by the database.

3. The stored procedure can be reused to reduce the workload of database developers.

4. High security, you can set only one user with the right to use the stored procedure

Type of stored procedures:

1. System stored procedure: starting with SP_, used to perform system settings. Get information. Related management work,

If sp_help is the information obtained by obtaining a specified object

2. The extended stored procedure starts with XP_, used to call the function provided by the operating system

EXEC MASTER..XP_CMDSHELL 'PING 10.8.16.1'

3. User-defined stored procedures, this is the stored procedure we finishes

Common format

Create Procedure Procedue_name

[@Parameter Data_Type] [OUTPUT]

[with] {recompile | Encryption}

AS

SQL_STATEMENT

Explanation:

Output: Indicates that this parameter is available back

with {recompile | Encryption}

Recompile: Re-compiled once every time this memory is executed

Encryption: The content of the stored procedure created will be encrypted

Such as:

The content of the table book is as follows

Number title price

001 C Language Getting Started with $ 30

002 PowerBuilder Report Development $ 52

Example 1: Stored procedure for the contents of the query table BOOK

Create Proc Query_book

AS

SELECT * AWOK

Go

EXEC Query_book

Example 2: Add a record to the table book and query the total amount of all books in this table

Create Proc Insert_book

@ param1 char (10), @ param2 varchar (20), @ param3 money, @ param4 Money Output

WITH ENCRYPTION --------- Encryption

AS

Insert Book (No., Title, Price) VALUES (@ param1, @ param2, @ param3)

SELECT @ param4 = sum (price) from book

Go

Executive example:

Declare @total_price Money

EXEC INSERT_BOOK '003', 'Delphi Control Development Guide', $ 100, @ Total_price

Print 'total amount is' convert (varchar, @ Total_price)

Go

3 types of transfers in the stored procedure:

Returning an integer with Return

2. Retrieve parameters in OUTPUT format

3.Recordset

The difference between passing value:

Output and return can receive variables in batches, and RecordSet is transmitted back to the client of the execution batch 3: There are two tables as Product, Order, the table content is as follows:

PRODUCT

Product Number Product Name Customer Order

001 Pen 30

002 Brush 50

003 pencil 100

ORDER

Product number customer name customer deposit

001 Nanshan District $ 30

002 Luohu District $ 50

003 Baoan District $ 4

Implement the numbered connection condition, connect the two tables into a temporary table, which is only numbered. Product name. Customer name. Deposit. Total amount,

Total amount = deposit * Order, temporary table is placed in the stored procedure

code show as below:

Create Proc Temp_sale

AS

SELECT A. Product Number, A. Product Name, B. Customer Name, B. Customer Deposit, A. Customer Order * b. Customer Deposit AS Total Amount

INTO #Temptable from Product a inner Join ORDER B on a. Product Number = B. Product Number

IF @@ error = 0

Print 'good'

Else

& n bsp; Print 'Fail'

Go

Introduction to the stored procedure, first introduce what is the stored procedure stored procedure to use the program provided by the TRANACT-SQL language provided by SQL Server. The TRANACT-SQL language is SQL Server provides a language designed to design a database application, which is the primary programming interface between the application and the SQL Server database. It is better than the Informix-4GL language that Pro-SQL and Informix in the Oracle database system. Such languages ​​provide the following functions, allowing users to design programs that meet the reference requirements: 1), variables 2), ANSI-compatible SQL commands (such as SELECT, UPDATE ....) 3), general flow control command (if ... Else ..., while ....) 4), internal functions

Second, the writing of the stored procedure

Create Procedure [owner.] Store process name [; program number] [(parameter # 1, ... parameter # 1024)] [with {recompile | encryption | Recompile, Encryption}] [for replication] AS line

The stored procedure name cannot exceed 128 words. Set up to 1024 parameters during each store (SQL Server 7.0 or higher), the method of use of the parameters is as follows:

@ 参数 Name Data Type [Varying] [= Normal] [OUTPUT]

There is a "@" symbol before each parameter name. The parameters of each stored procedure are only internal to the program, and the type of data supported by other SQL Server is available in addition to Image. [= Obredity] It is equivalent to setting the default value of a field when we set up a database, here is the default value for this parameter. [OUTPUT] is used to specify that the parameter is both input and output values, that is, when the stored procedure is called, if the specified parameter value is the parameters we need to enter, it also needs to be output in the results. If the item must be OUTPUT, if only the output parameter is used, you can use Cursor, and when using this parameter, you must specify the two statements of Varying and Output.

Examples: CREATE PROCEDURE order_tot_amt @o_id int, @ p_tot int output AS SELECT @p_tot = ordered = o_id example FROM orderdetails WHERE @ sum (Unitprice * Quantity) Description: This example is to establish a simple stored procedure order_tot_amt, the storage process based on the user The set order ID number (@o_id), calculated the total amount of the order sales in the order of order [Unitprice *), this amount is output from the parameter output by @P_tot to call this save. Process procedure

Third, perform a stored procedure in SQL Server

In the Query Analyzer of SQL Server, enter the following code: declare @tot_amt int execute order_tot_amt 1, @ Tot_amt Output SELECT @tot_amt

The above code is the stored procedure for executing the order_tot_amt to calculate the order sales amount of 1, we define @tot_amt as the output parameters, used to undertake the results we want

转载请注明原文地址:https://www.9cbs.com/read-130577.html

New Post(0)