Storage process entry and improvement [transfer]

xiaoxiao2021-03-06  79

What is a stored procedure?

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 complicated databases (such as Update, INSERT, Query, DELETE for multiple tables), this complex * can be used to store the procedure to be used with 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 begins with XP_, used to call * as a system provided by the system

EXECMASTER..XP_CMDSHELL'PING10.8.16.1 '

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

Common format

CREATEPROCEDUREPROCEDUE_NAME

[@ParameterData_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

001c language entry $ 30

002PowerBuilder Report $ 52

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

CreateProcQuery_book

AS

SELECT * FROOK

Go

EXECQUERY_BOOK

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

CREATEPROCINSERT_BOOK

@ param1char (10), @ param2varchar (20), @ param3money, @ param4moneyOutput

Withenncryption --------- Encryption

AS

INSERTBOOK (Number, Title, Price) VALUES (@ param1, @ param2, @ param3) SELECT @ param4 = sum (Price) fromBookgo

Executive example: declare @ total_pricemoneyexecinsert_book'003 ',' Delphi Control Development Guide ', $ 100, @ Total_PricePrint' total amount is' Convert (varchar, @ Total_Price) GO stored procedure 3 types of passage values: 1. Retro Integer 2. Retrof the parameter 3.RecordSet Retrieval Value in Output Format: Output and Return can receive variables in the batch program, and RecordSet is transferred to the client that executes the batch.

Example 3: Two tables as Product, Order, the table contents are as follows: Product product number Product Name Customer Order 001 Pen 30002 Brush 50003 Pencil 100ORDER Product Number Customer Name Customer Deposit 001 Nanshan District $ 30002 Luohu District $ 50003 Baoan District $ 4 Implementing the number as a connection condition, connecting 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 As follows: createProctemp_saleasselecta. Product Number, a. Product Name, B. Customer Name, B. Customer Deposit, A. Customer Order * b. Customer Deposit AS Total Into # TemptableFromProductainnerJoinorderbona. Product Number = b. Product No. IF @@ error = 0Print'Good'ElsePrint'Fail'go

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

New Post(0)