Storage process entry and improvement
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 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 (Number, Title, Price) VALUES (@ param1, @ param2, @ param3) Select @ param4 = sum (Price) from book Go
Example: Declare @total_price Money Exec Insert_book '003', 'Delphi Control Development Guide', $ 100, @ Total_Price Print 'The total amount of' Convert (varchar, @ Total_Price) GO stored procedure 3 types of passages: 1. Returning an integer with RETURN 2. Retrof the parameter 3.RecordSet Retrieval Value in Output Format: Output and Return can receive variables in batches, and RecordSet is sent back to the client of the execution batch. 3: There are two tables as Product, Order, the table contents are 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 003 Luohu District $ 50 003 Baoan District $ 4 Please implement the number as a connection condition, connect two tables into a temporary table, which is only numbered. Product name. Customer name. Deposit. Total amount, total amount = deposit * Order, temporary During storage
The code is as follows: 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 print 'Fail' Go
