--有兩個以上參數的預存程序
create proc 員工薪資查詢
@name varchar(12),
@salary money
as
begin
select * from 員工
where 姓名 like '%'+ @name+ '%' and 薪水>=@salary
end
exec 員工薪資查詢 '陳', 20000
exec 員工薪資查詢 @salary=20000,@name='陳'
修改預存程序 alter
參數給定預設值
兩個參數 如果有預設值 若只給1個 也不會抱錯 所以感覺預設值要給模糊查詢萬用字元
--有預設值的參數
alter proc 員工居住地查詢
@city char(5) ='台北',
@name varchar(12)
as
begin
select * from 員工
where 姓名 like '%'+ @name+ '%' or 城市=@city
end
exec 員工居住地查詢 '桃園 ', '陳'
exec 員工居住地查詢 @name='陳'
exec 員工居住地查詢 default,'陳'
新增產品會有個問題 ( 要怎麼做?
如果是規則性的 則按照 邏輯 產生新的東西
要先寫一段程式確定是否重複
新的 與 現有的比較 如果不存在 則 ... ( 商業邏輯
可以寫在資料庫端 ( tsql 與 資料庫是最緊密的
alter proc check_productID
@productID char(4)
as
begin
declare @pid char(4)
select @pid=Product_ID from Products where Product_ID=@productID
if @@rowcount=0
select '可使用'
else
select '編號重複'
end
exec check_productID 'P011'
--傳出參數
create proc 薪水查詢
@name varchar(12),
@salary money output
as
begin
select @salary=薪水 from 員工 where 姓名=@name
end
alter proc 加薪試算
@name varchar(12)
as
begin
declare @MySalary money
exec 薪水查詢 @name, @salary=@MySalary output
print @MySalary*1.03
end
exec 加薪試算 '王心零'
--函數(function)
--純量值函數(式)
create function fnGetSalary()
returns money
as
begin
declare @salary money
select @salary=薪水 from 員工
return @salary
end
會抓到最後一筆 上一筆會被覆蓋
看returns 就會自動辨識是哪種函數
在資料庫寫函數主要是因為能夠跟資料庫做緊密結合
呼叫函數
自訂函數必須把型態寫清楚
--呼叫函數
print getdate()
select getdate()
print dbo.fnGetSalary()
select dbo.fnGetSalary()
go
有參數的函數
tsql 語法通常先寫名稱在寫資料型態
函數裡面只要看到return以下就不會再執行 所以不用寫 else
--有參數的函數
alter function fnGetSalary2
(
@name varchar(12)
)
returns money
as
begin
declare @salary money
select @salary=薪水 from 員工 where 姓名=@name
if @@ROWCOUNT=0
return 0
return @salary
end
函數要用在適合他的地方 預存程序也是
create proc 薪水查詢_fn
@name varchar(12)
as
begin
if dbo.fngetsalary2(@name)=0
print '查無此人'
else
print dbo.fngetsalary2(@name)
end
exec 薪水查詢_fn '王心7777零'
資料表值函數
最大差別在於 returns table
--資料表值函數--
create function fnStudentQuery
(@stuNo char(4))
returns table
return(
select 班級.教授編號,員工.姓名 as 老師姓名,教授.科系,教授.職稱,班級.學號,學生.姓名,學生.性別,
班級.課程編號,課程.名稱,課程.學分,班級.上課時間,班級.教室
from 班級
inner join 學生 on 班級.學號=學生.學號
inner join 課程 on 班級.課程編號=課程.課程編號
inner join 教授 on 班級.教授編號=教授.教授編號
inner join 員工 on 教授.身份證字號=員工.身份證字號
where 學生.學號=@stuNo
)
呼叫 資料表值函數
select * from dbo.fnStudentQuery('S001')
進階資料表值函數應用
2008後才能用 offset fetch 所以在那之前的版本只能自己寫函數
select e.身份證字號,e.姓名,e.電話
from 員工 as e order by e.身份證字號
offset 3 rows
fetch next 3 rows only
死機碼
唯一識別值 流水號 identity
撈出值在放入 資料表變數中 ( 資料表變數必須寫好型態
create function fnOffset
(@m int, @n int)
returns @resultTable table(
sn int identity,
id char(10),
name varchar(12),
tel varchar(20)
)
begin
insert into @resultTable
select e.身份證字號,e.姓名,e.電話
from 員工 as e order by e.身份證字號
delete @resultTable where sn<@m or sn>@n
return
end
select * from dbo.fnOffset(4,6)
