iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 16
3

前幾個系列文,大致重點說明了基礎及較進階的一些 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 來建立到資料庫中
https://ithelp.ithome.com.tw/upload/images/20181024/20111421fVNJuvAb6D.png
然後,呼叫執行
https://ithelp.ithome.com.tw/upload/images/20181024/20111421ynPDQlYeND.png
就可以看到一支最簡單的 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,其他資料庫沒有辦法這樣使用。這個範例程式雖然不大,但是用到了一些不錯的小技巧,可以多參考。

以下是建立及執行的畫面
https://ithelp.ithome.com.tw/upload/images/20181024/20111421smyu7y0Ovq.png
https://ithelp.ithome.com.tw/upload/images/20181024/20111421i7FfDrGOVy.png

但是要如何將 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 的執行結果
https://ithelp.ithome.com.tw/upload/images/20181024/20111421lgPQNk1TJG.png

再來就是發揮您的想像力了,反正就我的個人經驗,只要是資料庫能存取到的資料,就一定可以透過 Store Procedure 配合 udf 處理並計算取得。如果做不到,一般是自己的功力還不夠,所有的商業邏輯,一定都可以利用前述的簡單範例的做法來達成。

今天雖然看起來沒有新進度,但是適度的調整步伐是很有必要的。加深對 T-SQL 的了解,厚實基礎,對於深化「以資料庫為開發核心」這個開發方法論是非常有必要的。今天就寫到這邊,明天繼續努力。


上一篇
Day15:進階的 SQL 語法簡介 I
下一篇
Day17:不同資料庫,常用的 SQL 語法轉換原則 I
系列文
以資料庫為開發核心,利用通用 API 玩轉後端資料存取的概念與實作30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言