前幾個系列文,大致重點說明了基礎及較進階的一些 SQL 語法,雖然有附上一些程式片段,但是如果原先基礎並不深厚的童鞋,可能還是會一頭霧水。所以今天我把進度放慢,並用一些完整但較簡短的幾支小程式,讓 SQL 的語法能說明的更清楚,更容易實際運用到實際的工作中。
首先,我先說明一下如何建立 Store procedure 及 user define function。因為限於篇幅,所以仍然以 MS-SQL 的語法為主,後續會有幾篇文章說明,如何轉換成 MariaDB 或 PostgreSQL 的相近語法。
create procedure getCustomerList
(
@pid varchar(10),
@addr nvarchar(100)
)
/*
function : 客戶一覽表 (要能依業務、部分地址(%中山北路%) 查詢)
*/
as
begin
select * from zen_customer where pid = @pid and deliveraddress like '%' + @addr + '%'
end
寫 Store Procedure 實際上非常簡單,以上面這個範例來看,真正的 SQL 語法只有一行,這個 sp 會傳入2個參數,這和一般函數程式的寫法大同小異,只不過要注意,在 T-SQL 中,變數名稱前面都會是 @ 開頭,如此而已。
寫好了 Store Procedure 後,利用我們先前介紹的 database.NET 來建立到資料庫中
然後,呼叫執行
就可以看到一支最簡單的 Store Procedure 已經被建立並執行。
接下來,我們看看 user define function 如何建立並運用
create function dbo.udf_split
(
@source varchar(max),
@split varchar(2)
)
/*
function : 將傳入的字串, 依 @split 切割為多筆資料, 傳回 table
description: 呼叫範例 select * from dbo.udf_split('a1,a2,a3',',')
build date : 2011/09/08
modify history
item who date modify docs
==== ========== ========== ================================================
1 michael 2011/09/08 新增
*/
returns @table table(col varchar(max))
as
begin
while(charindex(@split,@source)<>0)
begin
insert @table(col) values (substring(@source,1,charindex(@split,@source)-1))
set @source = stuff(@source,1,charindex(@split,@source),'')
end
insert @table(col) values (@source)
return
end
在程式註解中,已經說明了這支 udf 的用途,他就是將傳入的字串,利用分隔符號,傳回分割好的內容,請注意,他可以傳回 Table,其他資料庫沒有辦法這樣使用。這個範例程式雖然不大,但是用到了一些不錯的小技巧,可以多參考。
以下是建立及執行的畫面
但是要如何將 Store Procedure 和 udf 整合再一起使用? 讓我們修改一下第一支 Store Procedure,加入 udf 的運用。
alter procedure getCustomerList
(
@pid varchar(200),
@addr nvarchar(100)
)
/*
function : 客戶一覽表 (要能依業務、部分地址(%中山北路%) 查詢)
modify history
item who date modify docs
==== ========== ========== ================================================
1 michael 2018/10/24 擴充 @pid 可以傳入多個業務編號
*/
as
begin
select * from zen_customer
where pid in (select col from dbo.udf_split(@pid,','))
and deliveraddress like '%' + @addr + '%'
end
再來看看修改後的 Store Procedure 的執行結果
再來就是發揮您的想像力了,反正就我的個人經驗,只要是資料庫能存取到的資料,就一定可以透過 Store Procedure 配合 udf 處理並計算取得。如果做不到,一般是自己的功力還不夠,所有的商業邏輯,一定都可以利用前述的簡單範例的做法來達成。
今天雖然看起來沒有新進度,但是適度的調整步伐是很有必要的。加深對 T-SQL 的了解,厚實基礎,對於深化「以資料庫為開發核心」這個開發方法論是非常有必要的。今天就寫到這邊,明天繼續努力。