asp网站转php,wordpress如何发布文件夹,中国网站建设公司前十名,王战营一.存储过程和函数的区别函数调用有返回值存储过程调用用call语句#xff0c;函数调用直接饮用函数名参数IN#xff0c;OUT#xff0c;INOUT只适用于存储过程#xff0c;对函数而言所有参数默认都是输入参数IN用于把数值传入到存储过程中OUT用于输出参数将数值传递给调用者…一.存储过程和函数的区别函数调用有返回值存储过程调用用call语句函数调用直接饮用函数名参数INOUTINOUT只适用于存储过程对函数而言所有参数默认都是输入参数IN用于把数值传入到存储过程中OUT用于输出参数将数值传递给调用者INOUT输入输出参数把数据传入到存储过程在存储过程中修改后再传递给调用者二.存储过程实践功能:格式化数据DELIMITER //CREATE PROCEDURE sieAttendanceService.checkData(IN myId BIGINT, OUT spaceId BIGINT)SQL SECURITY INVOKERBEGINDECLARE 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 doset 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 tset t.company_code 032128where id myId;/*去掉空格*/update sie_attendance_external_t tset 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 tset 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 tset 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 tset 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 tset t.on_time (select case when length(tt.on_time)7then concat(0,tt.on_time)elsett.on_timeendfrom (select * from sie_attendance_external_t) tt where t.id tt.id)where id myId;/*更新下班时间格式*/update sie_attendance_external_t tset t.off_time (select case when length(tt.off_time)7then concat(0,tt.off_time)elsett.off_timeendfrom (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)beginDECLARE 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 thenset str (select left(param,postion));end if;/*开始循环判断*/set len char_length(str);lop:beginwhile len 0 doif(ascii(mid(str,len,1))47 and ascii(mid(str,len,1))58) thenset 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中使用直接拿到返回值。
