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); -- 科室id
DECLARE v_orgname VARCHAR(255); -- 科室名称
DECLARE v_orgname_hosp VARCHAR(255); -- 诊所名称
DECLARE v_registid_am VARCHAR(50); -- 号别编码1
DECLARE v_registid_pm VARCHAR(50); -- 号别编码2
DECLARE 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 DO
SET 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;