sqlserver自訂函數與預存程序的區別 執行個體詳解

標籤：style   http   io   使用   ar   資料   sp   html   on   

分享下sql server自訂函數與預存程序的區別，一起來學習下。 

一、自訂函數：
　　1. 可以返回表變數
　　2. 限制頗多，包括
　　　　不能使用output參數；
　　　　不能用暫存資料表；
　　　　函數內部的操作不能影響到外部環境；
　　　　不能通過select返回結果集；
　　　　不能update，delete，資料庫表；
　　3. 必須return 一個標量值或表變數
　　自訂函數一般用在複用度高，功能簡單單一，爭對性強的地方。
二、預存程序
　　1. 不能返回表變數
　　2. 限制少，可以執行對資料庫表的操作，可以返回資料集
　　3. 可以return一個標量值，也可以省略return
　　　預存程序一般用在實現複雜的功能，資料操縱方面。

=========================================================================
SqlServer預存程序--執行個體
執行個體1：只返回單一記錄集的預存程序。
　　表銀行存款表（bankMoney）的內容如下

Id
userID
Sex
Money
001
Zhangsan
男
30
002
Wangwu
男
50
003
Zhangsan
男
40

要求1：查詢表bankMoney的內容的預存程序
create procedure sp_query_bankMoney
as
select * from bankMoney
go
exec sp_query_bankMoney
注* 在使用過程中只需要把T-Sql中的SQL語句替換為預存程序名，就可以了很方便吧！
執行個體2（向預存程序中傳遞參數）：
加入一筆記錄到表bankMoney，並查詢此表中userID= Zhangsan的所有存款的總金額。
Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
with encryption ---------加密
as --- www.jbxue.com
insert into bankMoney (id,userID,sex,Money)
Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID=‘Zhangsan‘
go
在SQL Server查詢分析器中執行該預存程序的方法是：
declare @total_price int
exec insert_bank ‘004‘,‘Zhangsan‘,‘男‘,100,@total_price output
print ‘總餘額為‘+convert(varchar,@total_price)
go
在這裡再囉嗦一下預存程序的3種傳回值（方便正在看這個例子的朋友不用再去查看文法內容）:
1.以Return傳回整數
2.以output格式傳回參數
3.Recordset
傳回值的區別:
output和return都可在批次程式中用變數接收,而recordset則傳回到執行批次的用戶端中。
執行個體3：使用帶有複雜 SELECT 語句的簡單過程
下面的預存程序從四個表的聯結中返回所有作者（提供了姓名）、出版的書籍以及出版社。該預存程序不使用任何參數。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘au_info_all‘ AND type = ‘P‘)
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
　　au_info_all 預存程序可以通過以下方法執行：
　　EXECUTE au_info_all
-- Or
EXEC au_info_all
　　如果該過程是批處理中的第一條語句，則可使用：
　　au_info_all
執行個體4：使用帶有參數的簡單過程
　　CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
　　au_info 預存程序可以通過以下方法執行：
　　EXECUTE au_info ‘Dull‘, ‘Ann‘
-- Or
EXECUTE au_info @lastname = ‘Dull‘, @firstname = ‘Ann‘
-- Or
EXECUTE au_info @firstname = ‘Ann‘, @lastname = ‘Dull‘
-- Or
EXEC au_info ‘Dull‘, ‘Ann‘
-- Or
EXEC au_info @lastname = ‘Dull‘, @firstname = ‘Ann‘
-- Or
EXEC au_info @firstname = ‘Ann‘, @lastname = ‘Dull‘
　　如果該過程是批處理中的第一條語句，則可使用：
　　au_info ‘Dull‘, ‘Ann‘
-- Or
au_info @lastname = ‘Dull‘, @firstname = ‘Ann‘
-- Or
au_info @firstname = ‘Ann‘, @lastname = ‘Dull‘
執行個體5：使用帶有萬用字元參數的簡單過程
CREATE PROCEDURE au_info2
@lastname varchar(30) = ‘D%‘,
@firstname varchar(18) = ‘%‘
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
　　au_info2 預存程序可以用多種組合執行。下面只列出了部分組合：
　　EXECUTE au_info2
-- Or
EXECUTE au_info2 ‘Wh%‘
-- Or
EXECUTE au_info2 @firstname = ‘A%‘
-- Or
EXECUTE au_info2 ‘[CK]ars[OE]n‘
-- Or
EXECUTE au_info2 ‘Hunter‘, ‘Sheryl‘
-- Or
EXECUTE au_info2 ‘H%‘, ‘S%‘
　　= ‘proc2‘
執行個體6：if...else
預存程序，其中@case作為執行update的選擇依據，用if...else實現執行時根據傳入的參數執行不同的修改.
--下面是if……else的預存程序：
if exists (select 1 from sysobjects where name = ‘Student‘ and type =‘u‘ )
drop table Student
go
if exists (select 1 from sysobjects where name = ‘spUpdateStudent‘ and type =‘p‘ )
drop proc spUpdateStudent
go
create table Student
(
fName nvarchar (10),
fAge
smallint ,
fDiqu varchar (50),
fTel int
)
go
insert into Student values (‘X.X.Y‘ , 28, ‘Tesing‘ , 888888)
go
create proc spUpdateStudent
(
@fCase int ,
@fName nvarchar (10),
@fAge smallint ,
@fDiqu varchar (50),
@fTel int
)
as
update Student
set fAge = @fAge, -- 傳 1,2,3 都要更新 fAge 不需要用 case
fDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ),
fTel = (case when @fCase = 3 then @fTel else fTel end )
where fName = @fName
select * from Student
go
-- 只改 Age
exec spUpdateStudent
@fCase = 1,
@fName = N‘X.X.Y‘ ,
@fAge = 80,
@fDiqu = N‘Update‘ ,
@fTel = 1010101
-- 改 Age 和 Diqu
exec spUpdateStudent
@fCase = 2,
@fName = N‘X.X.Y‘ ,
@fAge = 80,
@fDiqu = N‘Update‘ ,
@fTel = 1010101
-- 全改
exec spUpdateStudent
@fCase = 3,
@fName = N‘X.X.Y‘ ,
@fAge = 80,
@fDiqu = N‘Update‘ ,
@fTel = 1010101

sqlserver自訂函數與預存程序的區別 執行個體詳解