MySQL存储过程

来源：互联网发布：doujinmode新域名编辑：程序博客网时间：2024/05/01 12:08

DROP PROCEDURE IF EXISTS P_AUTO_SCHEDULING;CREATE PROCEDURE P_AUTO_SCHEDULING(IN v_hosptialId VARCHAR(50))BEGINDECLARE i INT DEFAULT 0;DECLARE v_days INT DEFAULT 1;DECLAREv_uuid VARCHAR(32);DECLARE v_curdate date;DECLAREv_weekTypeid VARCHAR(15);DECLARE v_hosptialName VARCHAR(255);DECLARE v_orgId VARCHAR(50);DECLARE v_orgName VARCHAR(255);SELECT orgname_hosp FROM t_hosp WHEREorgid_hosp = v_hosptialId LIMIT 1 INTO v_hosptialName;SELECT orgid,orgname FROM t_org WHERE orgId_hosp = v_hosptialId INTO v_orgId,v_orgName;WHILE i < v_days DOSET 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)VALUES (CONCAT('01',v_uuid),v_curdate,'3516767879440','01',127,0,0,0,0,20,v_hosptialId,v_hosptialName,v_orgId,v_orgName,v_weekTypeid),(CONCAT('02',uuid),v_curdate,'3516767879440','02',127,0,0,0,0,20,v_hosptialId,v_hosptialName,v_orgId,v_orgName,v_weekTypeid);SET i = i + 1;END WHILE;END;

业务需求：

从另一张表里取数据，赋给变量，然后变量一直是null，但是sql是没问题，搞了很久才找出原因，变量名和字段名只是大小写不一样。所以以后变量还是加个v_

0 0