What is a stored procedure?
definition:
Write commonly used or very complex tasks in advance with SQL statements and store them with a specified name. Then when you want to ask the database to provide services with the same functions as the defined stored procedures in the future, you only need to call execute. Autocomplete commands.
At this point, someone may ask: So a stored procedure is just a bunch of SQL statements?
Why does Microsoft want to add this technology?
So what is the difference between stored procedures and ordinary SQL statements?
Advantages of stored procedures:
1. Stored procedures are only compiled when they are created. There is no need to recompile each time the stored procedure is executed in the future. Generally, SQL statements are compiled once every time they are executed, so using stored procedures can improve the execution speed of the database.
2. When performing complex operations on the database (such as Update, Insert, Query, Delete on multiple tables), this complex operation can be encapsulated in a stored procedure and used in conjunction with the transaction processing provided by the database.
3. Stored procedures can be reused, which can reduce the workload of database developers
4. High security, you can set that only a certain user has the right to use the specified stored process
Types of stored procedures:
1. System stored procedures: starting with sp_, used to set up the system, obtain information, and related management work, such as sp_help, which is used to obtain relevant information about the specified object.
2. The extended stored procedure starts with XP_ and is used to call functions provided by the operating system.
exec master..xp_cmdshell 'ping 10.8.16.1'
3. User-defined stored procedures, this is what we refer to as stored procedures
Common formats
Create procedure procedure_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
ql_statement
explain:
output: indicates that this parameter can be returned
with {recompile|encryption}
recompile: means that this stored procedure will be recompiled every time it is executed.
encryption: The content of the created stored procedure will be encrypted
like:
The contents of the table book are as follows
Numbered book title price
001 Introduction to C language$30
002 PowerBuilder report development$52
Example 1: Stored procedure to query the contents of table Book
create proc query_book
as
select * from book
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, book title, price) Values(@param1,@param2,@param3)
select @param4=sum(price) from book
go
Execution example:
declare @total_price money
exec insert_book '003','Delphi Control Development Guide',$100,@total_price
print 'The total amount is'+convert(varchar,@total_price)
go
Three types of return values from stored procedures:
1. Return the integer with Return
2. Return parameters in output format
3.Recordset
The difference between the returned values:
Both output and return can be received as variables in the batch program, and the recordset is passed back to the client that executes the batch.
Example 3: There are two tables, Product and Order, with the following table contents:
Product
Product number Product name Customer order quantity
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
Please use number as the connection condition to connect the two tables into a temporary table. This table only contains number. Product name. Customer name. Deposit. Total amount.
Total amount = deposit * order quantity, the temporary table is placed in the storage process
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 quantity* 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'