iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 18
0
Software Development

以資料庫為開發核心,利用通用 API 玩轉後端資料存取的概念與實作系列 第 18

Day18:不同資料庫,常用的 SQL 語法轉換原則 II

  • 分享至 

  • xImage
  •  

今天我會重新改寫 Day16 的 MS-SQL 版程式,改為 MariaDB(MySQL) 版,也讓各位能更直觀的感受到不同資料庫語法轉換的一些基本概念。

首先,我先說明一下如何建立 Store procedure 及 user define function。

create procedure getCustomerList
(
  _pid varchar(100),
  _addr nvarchar(100)
)
/*
  function   : 客戶一覽表 (要能依業務、部分地址(%中山北路%) 查詢)
*/
begin
  select * from zen_customer where pid = _pid and deliveraddress like concat('%' ,_addr ,'%');
end

差異為 MariaDB 的變數名稱不能用 @開頭,@開頭是系統變數。
再來就是字串相加必須改用 concat(),MS-SQL 是直接用 + 。
https://ithelp.ithome.com.tw/upload/images/20181026/20111421mcTsGVxCKY.jpg
然後,呼叫執行
https://ithelp.ithome.com.tw/upload/images/20181026/20111421w4ILAkJ3Z5.jpg
就可以看到一支最簡單的 Store Procedure 已經被建立並執行。

接下來,我們改寫 udf_split 這支 udf,但因為 T-SQL 是回傳 Table,MariaDB 沒有這種東西,所以就改為如下的寫法,將資料寫到實體暫存檔。

create procedure udf_split
(
  _uuid varchar(100),
  _SOURCE  VARCHAR(2000),
  _SPLIT   VARCHAR(2)
)
/*
  Function   : 將傳入的字串, 依 _split 切割為多筆資料, 傳回 _split 這個 table(利用 uuid 識別)
  Description: 呼叫範例  select * from dbo.GEXFUNC_SPLIT('A1,A2,A3',',')
  Build Date : 2011/09/08
  
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    1    Michael    2011/09/08 新增
*/  
BEGIN
  /* 先計算出 _split 出現的次數, 要跑 loop */
  declare _i int default 1;
  declare _cnt int default 1;
  declare _nowstr varchar(2000);
  
  SELECT COUNT(*) into _cnt FROM information_schema.TABLES WHERE TABLE_NAME='_split';

  if (_cnt <= 0) then
    CREATE TABLE _split(
      guid varchar(100),
	  rowid int,
      col varchar(600)
    );
  end if;
  
  set _nowstr = '';
  set _i = 1;
  set _cnt = 1;
  while (_i <= CHAR_LENGTH(_SOURCE)) do
    if (SUBSTRING(_SOURCE, _i, CHAR_LENGTH(_SPLIT)) = _SPLIT) then
	  insert into _split (guid, rowid, col) values (_uuid, _cnt, _nowstr);
	  set _nowstr = '';
	  set _cnt = _cnt + 1;
	else
	  set _nowstr = concat(_nowstr, SUBSTRING(_SOURCE, _i, CHAR_LENGTH(_SPLIT)));
	end if;
	
    set _i=_i+1;
  end while;
  
  /* 最後一筆 */
  if (_nowstr != '') then
    insert into _split (guid, rowid, col) values (_uuid, _cnt, _nowstr);
  end if;
  
END;

以下是建立及執行的畫面
https://ithelp.ithome.com.tw/upload/images/20181026/20111421Qw2rs6f3t6.jpg
https://ithelp.ithome.com.tw/upload/images/20181026/20111421ElQoQq4dxs.jpg

修改一下第一支 Store Procedure,配合其他 store procedure 的運用。

create or replace procedure getCustomerList
(
  _pid varchar(200),
  _addr varchar(100)
)
/*
  function   : 客戶一覽表 (要能依業務、部分地址(%中山北路%) 查詢)
  modify history
    item who        date       modify docs
    ==== ========== ========== ================================================
    1    michael    2018/10/24 擴充 _pid 可以傳入多個業務編號
*/
begin
  declare _uuid varchar(100);
  set _uuid = uuid();
  call udf_split(_uuid, _pid, ',');
  
  select * from zen_customer 
  where pid in (select col from _split where guid = _uuid)
  and deliveraddress like concat('%' , _addr ,'%');
  
  delete from _split where guid = _uuid;
end

再來看看修改後的 Store Procedure 的執行結果
https://ithelp.ithome.com.tw/upload/images/20181026/20111421hURK5ksthE.png

最後,再補一支簡單的 store function 範例,請參考

create or replace function customerlevel
(
  p_creditlimit double
) returns varchar(10)
/*
  根據傳入的數字,回傳
*/
begin
    declare lvl varchar(10);
 
    if p_creditlimit > 50000 then
    set lvl = '白金';
    elseif (p_creditlimit <= 50000 and p_creditlimit >= 10000) then
        set lvl = '黃金';
    elseif p_creditlimit < 10000 then
        set lvl = '白銀';
    end if;
 
 return (lvl);
end

https://ithelp.ithome.com.tw/upload/images/20181026/20111421pLaDCnf7hg.png

今天就到這裡,感謝您的收看。


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

尚未有邦友留言

立即登入留言