mysql的函数和存储过程的比较，以及在实际场景中的使用案例

一.存储过程和函数的区别

• 函数调用有返回值

• 存储过程调用用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中使用，直接拿到返回值。