//創(chuàng)建用戶自定義函數(shù)------標(biāo)量函數(shù)
create function dbo.bmrs(@bmh as int)
returns int
as
begin
declare @bmrs int
select @bmrs=count(工號(hào)) from 銷售人員 where 部門號(hào)=@bmh
return @bmrs
end
go
exec sp_help? 銷售人員
go
//標(biāo)量函數(shù)的調(diào)用
declare @bmrs int
select @bmrs=dbo.bmrs(2)
select '2號(hào)部門人數(shù)'=@bmrs
go
select '2號(hào)部門人數(shù) '+convert(varchar(4),dbo.bmrs(3))
select 部門經(jīng)理,部門名稱,實(shí)際人數(shù)=dbo.bmrs(部門號(hào)) from 部門信息
go
select 部門經(jīng)理,部門名稱,實(shí)際人數(shù)=dbo.bmrs(部門號(hào)) from 部門信息 where 部門號(hào)=2
//創(chuàng)建內(nèi)嵌表值函數(shù)
----------a.訂單號(hào),銷售人員=b.姓名,客戶=c.姓名,供應(yīng)商=d.名稱,a.數(shù)量,a.總金額
------------以部門名稱作為輸入?yún)?shù)，創(chuàng)建部門訂單內(nèi)嵌表值函數(shù)
select * from 部門信息
select * from 訂單信息
select * from 客戶信息
select * from 供應(yīng)商信息
select * from 銷售人員
go
----------創(chuàng)建內(nèi)嵌表值函數(shù)
create function dbo.bmdd(@bmmc as varchar(10))
returns table
as
return( select a.訂單號(hào),銷售人員=b.姓名,客戶=c.姓名,供應(yīng)商=d.名稱,a.總金額,a.數(shù)量
from 訂單信息 as a inner join 銷售人員 as b on a.銷售工號(hào)=b.工號(hào) inner join 客戶信息 as c
on a.客戶編號(hào)=c.員工編號(hào) inner join 供應(yīng)商信息 as d on a.貨品編碼=d.編碼 inner join 部門信息 as e
on b.部門號(hào)=e.部門號(hào) where 部門名稱=e.部門名稱)
go
----------內(nèi)嵌表值函數(shù)的調(diào)用
select * from dbo.bmdd('銷售部')
--------------創(chuàng)建內(nèi)嵌表值函數(shù)的語法格式
create function 函數(shù)所有者.函數(shù)名稱(標(biāo)量參數(shù) as 標(biāo)量參數(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ù)名(標(biāo)量參數(shù) as 標(biāo)量數(shù)據(jù)類型)
returns 標(biāo)量參數(shù)數(shù)據(jù)類型
as
begin
函數(shù)體
return 變量//標(biāo)量表達(dá)式
end
----------多語句表值函數(shù)的返回值是表，有函數(shù)體，在函數(shù)體中可以使用表變量。
創(chuàng)建多語句表值函數(shù)語法格式：
create function [所有者名稱.]函數(shù)名稱(@參數(shù)名稱 as 標(biāo)量數(shù)據(jù)類型)
returns @表變量 table 表的定義
as
begin
函數(shù)體
return
end
go
說明：表(table)變量是一種特殊的數(shù)據(jù)類型，可用于函數(shù)、存儲(chǔ)過程和批處理中，用于存儲(chǔ)結(jié)果集以供以后處理。該數(shù)據(jù)類型主要用于臨時(shí)存儲(chǔ)一組行，這些行將作為表值函數(shù)的結(jié)果集返回。
table變量的行為類似于局部變量，其作用域?yàn)槁暶髟撟兞康暮瘮?shù)、存儲(chǔ)過程或批處理，其生命周期在定義table變量的
函數(shù)、存儲(chǔ)過程呢過和批處理結(jié)束時(shí)將自動(dòng)清楚table變量。
表(table)變量的聲明語法：declare @local_variable table(表的定義)
/*根據(jù)訂單信息表查詢得出每個(gè)銷售人員所開的訂單數(shù)。查詢的結(jié)果包括銷售人
員工號(hào)和訂單數(shù)。下面，我們采用表變量的方式將查詢的結(jié)果集保存下來，以便程序使用。*/
declare @ordernum
table(銷售工號(hào) int,
訂單數(shù) int)
---使用insert....select語句向表變量中插入記錄
insert @ordernum
select 銷售工號(hào),訂單數(shù)=count(銷售工號(hào)) from 訂單信息 group by 銷售工號(hào)
select * from @ordernum
go
/*在market數(shù)據(jù)庫中，創(chuàng)建一個(gè)多語句表值函數(shù)，它可以查詢指
定部門每個(gè)銷售人員的訂單數(shù)，該函數(shù)接收輸入的部門號(hào)，通過查詢“訂單信息”表返回銷售人員的工號(hào)、姓名和訂單數(shù)。*/
---創(chuàng)建多語句表值函數(shù)
create function dbo.info(@bmh as int)
returns @xsry table(工號(hào) int primary key,
姓名 varchar(10),
訂單數(shù) int)
as
begin
declare @ordernum
table(銷售工號(hào) int,訂單數(shù) int)
insert @ordernum select 銷售工號(hào),訂單數(shù)=count(銷售工號(hào)) from 訂單信息 group by 銷售工號(hào)
insert @xsry select a.工號(hào),a.姓名,b.訂單數(shù) from 銷售人員 as a left join @ordernum as b on a.工號(hào)=b.銷售工號(hào)
where a.部門號(hào)=@bmh
return
end
go
下載文檔
