一.存儲過程和函數的區別
函數調用有返回值
存儲過程調用用call語句，函數調用直接飲用函數名+參數
IN，OUT，INOUT
只適用于存儲過程，對函數而言所有參數默認都是輸入參數
IN用于把數值傳入到存儲過程中
OUT用于輸出參數將數值傳遞給調用者
INOUT輸入輸出參數把數據傳入到存儲過程，在存儲過程中修改后再傳遞給調用者
二.存儲過程實踐
功能:格式化數據
DELIMITER //
CREATE PROCEDURE sieAttendanceService.checkData(IN myId BIGINT, OUT spaceId BIGINT)
SQL SECURITY INVOKER
BEGIN
DECLARE space_num INT DEFAULT 0;
DECLARE temp VARCHAR(50);
DECLARE num_index INT DEFAULT 0;
select count(*) from sie_attendance_external_t t where t.on_time='' and t.off_time='' and t.id > myId into space_num;
while num_index < space_num do
set num_index = num_index + 1;
select id from sie_attendance_external_t t where t.on_time='' and t.off_time='' and t.id > myId into spaceId;
end while;
/*更新供應商編號*/
update sie_attendance_external_t t
set t.company_code = '032128'
where id > myId;
/*去掉空格*/
update sie_attendance_external_t t
set t.staff_name = trim(t.staff_name),
t.staff_id_no = trim(t.staff_id_no),
t.swipe_date = trim(t.swipe_date),
t.on_time = trim(t.on_time),
t.off_time = trim(t.off_time)
where t.id > myId;
/*將？替換成空*/
update sie_attendance_external_t t
set t.staff_name = replace(t.staff_name,CHAR(120),''),
t.staff_id_no = replace(t.staff_id_no,CHAR(120),''),
t.swipe_date = replace(t.swipe_date,CHAR(120),''),
t.on_time = replace(t.on_time,CHAR(120),''),
t.off_time = replace(t.off_time,CHAR(120),'')
where t.id > myId;
/*將.替換成：*/
update sie_attendance_external_t t
set t.staff_name = replace(t.staff_name,CHAR(190),CHAR(16)),
t.staff_id_no = replace(t.staff_id_no,CHAR(190),CHAR(16)),
t.swipe_date = replace(t.swipe_date,CHAR(190),CHAR(16)),
t.on_time = replace(t.on_time,CHAR(190),CHAR(16)),
t.off_time = replace(t.off_time,CHAR(190),CHAR(16))
where t.id > myId;
/*去掉回車和換行*/
update sie_attendance_external_t t
set t.staff_name = REPLACE(REPLACE(t.staff_name, CHAR(10), ''), CHAR(13),''),
t.staff_id_no = REPLACE(REPLACE(t.staff_id_no, CHAR(10), ''), CHAR(13),''),
t.swipe_date = REPLACE(REPLACE(t.swipe_date, CHAR(10), ''), CHAR(13),''),
t.on_time = REPLACE(REPLACE(t.on_time, CHAR(10), ''), CHAR(13),''),
t.off_time = REPLACE(REPLACE(t.off_time, CHAR(10), ''), CHAR(13),'')
where t.id > myId;
/*更新上班時間格式*/
update sie_attendance_external_t t
set t.on_time = (
select case when length(tt.on_time)=7
then concat('0',tt.on_time)
else
tt.on_time
end
from (select * from sie_attendance_external_t) tt where t.id = tt.id)
where id > myId;
/*更新下班時間格式*/
update sie_attendance_external_t t
set t.off_time = (
select case when length(tt.off_time)=7
then concat('0',tt.off_time)
else
tt.off_time
end
from (select * from sie_attendance_external_t) tt where t.id = tt.id)
where id > myId;
END
//
函數實踐
作用：?從不規則的數據中截取時間戳。
DELIMITER $$
create function getnum(param varchar(50))
returns varchar(50)
begin
DECLARE postion INT;
DECLARE len INT;
DECLARE str varchar(50) DEFAULT param;
DECLARE tmp varchar(50) DEFAULT '';
set postion = (select InStr (param,'_')-1);
/*判斷是否存在下劃線*/
if postion <> -1 then
set str = (select left(param,postion));
end if;
/*開始循環判斷*/
set len = char_length(str);
lop:begin
while len > 0 do
if(ascii(mid(str,len,1))>47 and ascii(mid(str,len,1))<58) then
set tmp = concat(tmp,mid(str,len,1));
else
/*如果不是數字，直接返回*/
LEAVE lop;
END IF;
SET len = len - 1;
END WHILE;
end lop;
/*返回結果集*/
return REVERSE(tmp);
end $$
總結：存儲過程的使用需用call 關鍵字調用，
函數直接select中使用，直接拿到返回值。
