MySQLストアド・プロシージャ別の2つのテーブルからデータを取り出して統合し、他のデータを新しいテーブルに保存します.

DROP PROCEDURE IF EXISTS P_AUTO_SCHEDULING;

CREATE PROCEDURE P_AUTO_SCHEDULING(IN v_hosptialId VARCHAR(50))
BEGIN

DECLARE i INT DEFAULT 0;
DECLARE v_days INT DEFAULT 30;
DECLARE	v_uuid VARCHAR(32);
DECLARE v_curdate date;
DECLARE	v_weekTypeid VARCHAR(15);
DECLARE v_hosptialName VARCHAR(255);
DECLARE v_orgId VARCHAR(50);
DECLARE v_orgName VARCHAR(255);
DECLARE orgid_hosp VARCHAR(50);
DECLARE orgid VARCHAR(50);
DECLARE orgname_hosp VARCHAR(255);
DECLARE orgname VARCHAR(255);

DECLARE idx INT DEFAULT 0;    /*             */
DECLARE cur_1 CURSOR FOR SELECT 
	t1.orgid_hosp AS orgid_hosp,
	t2.orgid AS orgid,
	t1.orgname_hosp AS orgname_hosp,
	t2.orgname AS orgname
FROM
	t_hosp t1,
	t_org t2
WHERE
	t1.orgid_hosp = t2.orgId_hosp
AND t1.orgid_hosp = v_hosptialId;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET idx = 1;
OPEN cur_1;
FETCH cur_1 INTO 
	orgid_hosp,
	orgid,
	orgname_hosp,
	orgname; 
/*       */
IF   idx<>1 THEN

WHILE i < v_days DO

SET v_uuid = UNIX_TIMESTAMP(TIMESTAMPADD(DAY, i, CURRENT_TIMESTAMP));
SET v_curdate = DATE_ADD(CURRENT_DATE, INTERVAL i DAY);
SET v_weekTypeid = DAYOFWEEK(DATE_ADD(CURRENT_DATE, INTERVAL i DAY));

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
) ( SELECT * FROM (SELECT
	UUID() AS uuid,
	DATE_ADD(CURRENT_DATE, INTERVAL i DAY) AS curdate,
	UUID() AS registid,
	'01' AS date_typeid,
	127 AS limit_amount,
	0 AS used_amount,
	0 AS stop_flag,
	0 AS resstartno,
	0 AS usedres_amount,
	20 AS limit_resamount,
	t1.orgid_hosp AS orgid_hosp,
	t2.orgid AS orgid,
	t1.orgname_hosp AS orgname_hosp,
	t2.orgname AS orgname,
	DAYOFWEEK(DATE_ADD(CURRENT_DATE, INTERVAL i DAY))AS week_typeid
FROM
	t_hosp t1,
	t_org t2
WHERE
	t1.orgid_hosp = t2.orgId_hosp
AND t1.orgid_hosp = v_hosptialId
UNION
SELECT
	UUID() AS uuid,
	DATE_ADD(CURRENT_DATE, INTERVAL i DAY) AS curdate,
	UUID() AS registid,
	'02' AS date_typeid,
	127 AS limit_amount,
	0 AS used_amount,
	0 AS stop_flag,
	0 AS resstartno,
	0 AS usedres_amount,
	20 AS limit_resamount,
	t1.orgid_hosp AS orgid_hosp,
	t2.orgid AS orgid,
	t1.orgname_hosp AS orgname_hosp,
	t2.orgname AS orgname,
	DAYOFWEEK(DATE_ADD(CURRENT_DATE, INTERVAL i DAY))AS week_typeid
FROM
	t_hosp t1,
	t_org t2
WHERE
	t1.orgid_hosp = t2.orgId_hosp
AND t1.orgid_hosp = v_hosptialId)
AS t3); 

SET i = i + 1; 

FETCH cur_1 INTO 
	orgid_hosp,
	orgid,
	orgname_hosp,
	orgname; 
/*       */

END WHILE;

END IF;
close cur_1; 

END;