今天我會重新改寫 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 是直接用 + 。
然後,呼叫執行
就可以看到一支最簡單的 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;
以下是建立及執行的畫面
修改一下第一支 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 的執行結果
最後,再補一支簡單的 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
今天就到這裡,感謝您的收看。