业务需求:
通过传入的参数x 从A表拿A.NAME(唯一一条记录),从B表拿B.ID,B.NAME(多条记录),保存至C表。
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;