SQL Stored Procedure Learning example

What is a stored procedure?

Defined:

To use the usual or very complex work, which is written in a SQL statement and stored with a specified name, you can automatically complete the command by simply calling execute when you want the database to provide the same functionality as the defined stored procedure.

Here, someone might ask: so the stored procedure is just a bunch of SQL statements?

Why do Microsoft companies add this technology?

So what's the difference between a stored procedure and a normal SQL statement?

Advantages of stored procedures:

1. Stored procedures are compiled only when created, and each time a stored procedure is executed without recompiling, and the general SQL statements are compiled every time, so using stored procedures can improve database execution speed.

2. When complex operations are performed on the database (for example, when multiple tables are update,insert,query,delete), this complex operation can be encapsulated in a stored procedure and used in conjunction with transaction processing provided by the database.

3. Stored procedures can be reused to reduce the workload of database developers

4. High security, can be set only a certain user to have the right to use the specified stored procedures

Types of stored procedures:

1. System stored procedures: to sp_ the beginning of the system for the various settings. Get information. Relevant management work, such as sp_help, is to obtain information about the specified object

2. Extended stored procedures begin with XP_ to invoke the functionality provided by the operating system

EXEC master.. xp_cmdshell ' Ping 10.8.16.1 '

3. User-defined stored procedures, which we refer to as stored procedures

Common format

Create procedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
ql_statement

Explain:

Output: Indicates that this parameter is a can be returned

with {recompile|encryption}

Recompile: means recompiling every time this stored procedure is executed

Encryption: The contents of the stored procedure created are encrypted

Such as:

The contents of the table book are as follows

编号 书名 价格
001 C语言入门 $30
002 PowerBuilder报表开发 $52

Instance 1: Stored procedures for querying the contents of a 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 ---------加密
as
insert book(编号,书名，价格） Values(@param1,@param2,@param3)
select @param4=sum(价格) from book
go

Example of execution:

declare @total_price money
exec insert_book '003','Delphi 控件开发指南',$100,@total_price
print '总金额为'+convert(varchar,@total_price)
go

3 Kinds of return values for stored procedures:

1. Returns an integer with return

2. Returns parameters in output format

3.Recordset

The difference between return values:

Both output and return can be received in a batch program and the recordset is returned to the client of the execution batch

Example 3: With two tables for product,order, the table reads as follows:

Product
产品编号 产品名称 客户订数
001 钢笔 30
002 毛笔 50
003 铅笔 100
Order
产品编号 客户名 客户订金
001 南山区 $30
002 罗湖区 $50
003 宝安区 $4

Implement a numbered join condition that joins two tables into a temporary table containing only numbers. Product name. Customer Name. Deposit. Total amount,

Total Amount = deposit * number, temporary table placed in stored procedure

The code is as follows:

Create proc temp_sale
as
select a.产品编号,a.产品名称,b.客户名,b.客户订金,a.客户订数* b.客户订金 as总金额
into #temptable from Product a inner join Order b on a.产品编号=b.产品编号
if @@error=0
print 'Good'
else
print 'Fail'
go