SQL自定義函數(shù)

//創(chuàng)建用戶自定義函數(shù)------標量函數(shù)

create function dbo.bmrs(@bmh as int)

returns int

as

begin

declare @bmrs int

select @bmrs=count(工號) from 銷售人員 where 部門號=@bmh

return @bmrs

end

go

exec sp_help? 銷售人員

go

//標量函數(shù)的調(diào)用

declare @bmrs int

select @bmrs=dbo.bmrs(2)

select '2號部門人數(shù)'=@bmrs

go

select '2號部門人數(shù) '+convert(varchar(4),dbo.bmrs(3))

select 部門經(jīng)理,部門名稱,實際人數(shù)=dbo.bmrs(部門號) from 部門信息

go

select 部門經(jīng)理,部門名稱,實際人數(shù)=dbo.bmrs(部門號) from 部門信息 where 部門號=2

//創(chuàng)建內(nèi)嵌表值函數(shù)

----------a.訂單號,銷售人員=b.姓名,客戶=c.姓名,供應商=d.名稱,a.數(shù)量,a.總金額

------------以部門名稱作為輸入?yún)?shù)，創(chuàng)建部門訂單內(nèi)嵌表值函數(shù)

select * from 部門信息

select * from 訂單信息

select * from 客戶信息

select * from 供應商信息

select * from 銷售人員

go

----------創(chuàng)建內(nèi)嵌表值函數(shù)

create function dbo.bmdd(@bmmc as varchar(10))

returns table

as

return( select a.訂單號,銷售人員=b.姓名,客戶=c.姓名,供應商=d.名稱,a.總金額,a.數(shù)量

from 訂單信息 as a inner join 銷售人員 as b on a.銷售工號=b.工號 inner join 客戶信息 as c

on a.客戶編號=c.員工編號 inner join 供應商信息 as d on a.貨品編碼=d.編碼 inner join 部門信息 as e

on b.部門號=e.部門號 where 部門名稱=e.部門名稱)

go

----------內(nèi)嵌表值函數(shù)的調(diào)用

select * from dbo.bmdd('銷售部')

--------------創(chuàng)建內(nèi)嵌表值函數(shù)的語法格式

create function 函數(shù)所有者.函數(shù)名稱(標量參數(shù) as 標量參數(shù)數(shù)據(jù)類型)

returns table

as

return (select語句)

-----------創(chuàng)建視圖的語法格式

create view 視圖名 as select語句

----------------用create function語句創(chuàng)建函數(shù)。

函數(shù)名在數(shù)據(jù)庫匯總必須唯一，用戶定義函數(shù)可以有輸入?yún)?shù)并返回值，但是沒有輸出參數(shù)。

create function 所有者.函數(shù)名(標量參數(shù) as 標量數(shù)據(jù)類型)

returns 標量參數(shù)數(shù)據(jù)類型

as

begin

函數(shù)體

return 變量//標量表達式

end

----------多語句表值函數(shù)的返回值是表，有函數(shù)體，在函數(shù)體中可以使用表變量。

創(chuàng)建多語句表值函數(shù)語法格式：

create function [所有者名稱.]函數(shù)名稱(@參數(shù)名稱 as 標量數(shù)據(jù)類型)

returns @表變量 table 表的定義

as

begin

函數(shù)體

return

end

go

說明：表(table)變量是一種特殊的數(shù)據(jù)類型，可用于函數(shù)、存儲過程和批處理中，用于存儲結(jié)果集以供以后處理。該數(shù)據(jù)類型主要用于臨時存儲一組行，這些行將作為表值函數(shù)的結(jié)果集返回。

table變量的行為類似于局部變量，其作用域為聲明該變量的函數(shù)、存儲過程或批處理，其生命周期在定義table變量的

函數(shù)、存儲過程呢過和批處理結(jié)束時將自動清楚table變量。

表(table)變量的聲明語法：declare @local_variable table(表的定義)

/*根據(jù)訂單信息表查詢得出每個銷售人員所開的訂單數(shù)。查詢的結(jié)果包括銷售人

員工號和訂單數(shù)。下面，我們采用表變量的方式將查詢的結(jié)果集保存下來，以便程序使用。*/

declare @ordernum

table(銷售工號 int,

訂單數(shù) int)

---使用insert....select語句向表變量中插入記錄

insert @ordernum

select 銷售工號,訂單數(shù)=count(銷售工號) from 訂單信息 group by 銷售工號

select * from @ordernum

go

/*在market數(shù)據(jù)庫中，創(chuàng)建一個多語句表值函數(shù)，它可以查詢指

定部門每個銷售人員的訂單數(shù)，該函數(shù)接收輸入的部門號，通過查詢“訂單信息”表返回銷售人員的工號、姓名和訂單數(shù)。*/

---創(chuàng)建多語句表值函數(shù)

create function dbo.info(@bmh as int)

returns @xsry table(工號 int primary key,

姓名 varchar(10),

訂單數(shù) int)

as

begin

declare @ordernum

table(銷售工號 int,訂單數(shù) int)

insert @ordernum select 銷售工號,訂單數(shù)=count(銷售工號) from 訂單信息 group by 銷售工號

insert @xsry select a.工號,a.姓名,b.訂單數(shù) from 銷售人員 as a left join @ordernum as b on a.工號=b.銷售工號

where a.部門號=@bmh

return

end

go