mysql带参存储过程

主要解决了数字位数不足0的填充问题，类似0001在数据库是1的问题，添加判断为了对接系统的星期字典。

DROP PROCEDURE IF EXISTS SP_INITCHIS_SECOND_STEP_QH;CREATE PROCEDURE SP_INITCHIS_SECOND_STEP_QH(IN v_orgid_hosp VARCHAR(50), OUT result VARCHAR(2))BEGIN-- 变量定义及初始化DECLARE schdays INT DEFAULT 3; -- 排班时间天数DECLARE x INT DEFAULT 0; -- 用于循环DECLARE i INT(4) ZEROFILL DEFAULT 0001 ; -- registid递增DECLARE j INT(3) ZEROFILL DEFAULT 001 ; -- 挂号费用定义uuid递增DECLARE k INT(6) ZEROFILL DEFAULT 000001 ; -- 排班计划uuid 递增DECLARE l INT(6) ZEROFILL DEFAULT 000001 ; -- 排班uuid递增DECLARE v_orgid VARCHAR(50); -- 科室idDECLARE v_orgname VARCHAR(255); -- 科室名称DECLARE v_orgname_hosp VARCHAR(255); -- 诊所名称DECLARE v_registid_am VARCHAR(50); -- 号别编码1DECLARE v_registid_pm VARCHAR(50); -- 号别编码2DECLARE v_am VARCHAR(15) DEFAULT '01'; -- 上午DECLARE v_pm VARCHAR(15) DEFAULT '02'; -- 下午DECLARE v_week_typeid VARCHAR(15); -- 周几DECLARE v_curday DATE;DECLARE v_rowno INT DEFAULT 0; -- 号别费用表rowno-- 为变量赋值SELECT orgid,orgname FROM t_org WHERE orgId_hosp = v_orgid_hosp AND isstop = 0 LIMIT 1 INTO v_orgid,v_orgname; -- 科室编码，科室名称SELECT orgname_hosp FROM t_hosp WHERE orgId_hosp = v_orgid_hosp AND isstop = 0 LIMIT 1 INTO v_orgname_hosp; -- 诊所名称WHILE x < schdays DOSET v_registid_am = CONCAT(v_orgid_hosp,'|',i);SET v_registid_pm = CONCAT(v_orgid_hosp,'|',i+1);SET v_curday = DATE_ADD(CURRENT_DATE, INTERVAL x DAY);IF DAYOFWEEK(v_curday) = 1 THEN SET v_week_typeid ='07';ELSEIF DAYOFWEEK(v_curday) = 2 THEN SET v_week_typeid ='01';ELSEIF DAYOFWEEK(v_curday) = 3 THEN SET v_week_typeid ='02';ELSEIF DAYOFWEEK(v_curday) = 4 THEN SET v_week_typeid ='03';ELSEIF DAYOFWEEK(v_curday) = 5 THEN SET v_week_typeid ='04';ELSEIF DAYOFWEEK(v_curday) = 6 THEN SET v_week_typeid ='05';ELSEIF DAYOFWEEK(v_curday) = 7 THEN SET v_week_typeid ='06';END IF;-- 挂号表（未写字段默认为空）INSERT INTO t_register(registid,registname,regist_typeid,isstop,orgid,orgname,orgid_hosp)VALUES(v_registid_am,'全科普通号','02',0,v_orgid,v_orgname,v_orgid_hosp),(v_registid_pm,'全科普通号','02',0,v_orgid,v_orgname,v_orgid_hosp);-- 号别费用表INSERT INTO t_register_item(uuid,registid,regist_typeid,rowno,itemid,orgid_hosp)VALUES(CONCAT(v_registid_am,j),v_registid_am,'02',v_rowno,CONCAT(v_orgid_hosp,'B00001'),v_orgid_hosp),(CONCAT(v_registid_pm,j+1),v_registid_pm,'02',v_rowno+1,CONCAT(v_orgid_hosp,'B00001'),v_orgid_hosp);-- 排班计划INSERT INTO t_register_plan(uuid,registid,week_typeid,date_typeid,limit_amount,limitbook_amount,resource_stopflag,bookqueue_startno,limitbook_addamount,orgid,orgname,orgid_hosp,orgname_hosp)VALUES(CONCAT(v_registid_am,'|',k),v_registid_am,v_week_typeid,v_am,100,10,0,1,100,v_orgid,v_orgname,v_orgid_hosp,v_orgname_hosp),(CONCAT(v_registid_pm,'|',k+1),v_registid_pm,v_week_typeid,v_pm,100,10,0,1,100,v_orgid,v_orgname,v_orgid_hosp,v_orgname_hosp);-- 排班INSERT INTO t_pao_clinic(uuid,curdate,registid,date_typeid,limit_amount,used_amount,stop_flag,resstartno,usedres_amount,limit_resamount,orgid_hosp,orgname_hosp,orgid,orgname,week_typeid)VALUES(CONCAT(v_registid_am,'|',v_curday,l),-- 号别编码+“|”+“年”+“月”+“日” +000001（年四位，月两位，日两位递增流水号）v_curday,v_registid_am,v_am,100,0,'0',1,0,10,v_orgid_hosp,v_orgname_hosp,v_orgid,v_orgname,v_week_typeid),(CONCAT(v_registid_am,'|',v_curday,l+1),v_curday,v_registid_pm,v_pm,100,0,'0',1,0,10,v_orgid_hosp,v_orgname_hosp,v_orgid,v_orgname,v_week_typeid);-- 诊所表/机构表(表结构不一样)-- UPDATE t_hosp SET state='03' WHERE orgid_hosp=v_orgid_hosp;SET i = i + 2;SET j = j + 2;SET k = k + 2;SET l = l + 2;  SET v_rowno = v_rowno + 2;SET x = x + 1;END WHILE;SET result = 1;END;