mysql 嵌套存储过程_mysql存储过程 嵌套查询

DELIMITER $$

CREATE PROCEDURE test_cursor(IN studyId Long)

BEGIN

-- 需要执行的SQL语句

DECLARE v_sql_1 VARCHAR(2000);

DECLARE v_sql_2 VARCHAR(2000);

/*定义变量*/

DECLARE crf_form_id Long;

DECLARE table_name VARCHAR(255);

DECLARE field_sql VARCHAR(2000);

-- 定义循环标识，默认值为 FALSE

DECLARE done INT DEFAULT FALSE;

-- 定义游标，并将sql结果集赋值到游标中

DECLARE My_Cursor CURSOR FOR (

select

c.id, c.table_name

from

study s, crf c, study_crf sc

where

s.id=sc.study_id

and c.id=sc.crf_form_id

and s.deleted=0

and c.deleted=0

and c.generate_to_db=1

and c.table_name is not null

and s.id=studyId

);

-- 将结束标志绑定到游标，若没有数据返回,程序继续,并将变量done设为TRUE

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- 打开游标

OPEN My_Cursor;

myLoop2 : LOOP

-- 循环游标中的数据，并赋值到变量中

FETCH My_Cursor INTO crf_form_id, table_name ;

IF done THEN

LEAVE myLoop2;

ELSE

-- 动态拼接sql并赋值v_sql_1

SET v_sql_1 = CONCAT('

select

GROUP_CONCAT(cdf.field_name, " as \'", f.label, "\'") into @param1

from

crf_dict_field cdf, study_crf sc, dictionary d, field f

where cdf.crf_form_id=sc.crf_form_id

and cdf.dict_id=d.id

and cdf.field_id=f.id

and cdf.deleted=0

and d.deleted=0

and f.deleted=0

and cdf.generate_to_db=1

and cdf.field_name is not null

and cdf.crf_form_id=', crf_form_id, ' and sc.study_id=', studyId

);

-- 需要用@转换下,直接v_sql_1执行不了

SET @sql_1 = v_sql_1;

-- 预处理需要执行的动态SQL，其中stmt是一个变量

PREPARE stmt1 FROM @sql_1;

-- 执行SQL语句

EXECUTE stmt1;

-- 释放掉预处理段

DEALLOCATE PREPARE stmt1;

-- @param1赋值给maxId2

SET field_sql = @param1;

-- 动态拼接sql并赋值v_sql_2

SET v_sql_2 = CONCAT('select id, objectId, ', field_sql, ' from ', table_name, ' where deleted=0 and objectId in (select so.id from study_object so where so.deleted=0 and so.study_id=', studyId, ') ');

SET @sql_2 = v_sql_2;

-- 预处理需要执行的动态SQL，其中stmt是一个变量

PREPARE stmt2 FROM @sql_2;

-- 执行SQL语句

EXECUTE stmt2;

-- 释放掉预处理段

DEALLOCATE PREPARE stmt2;

-- 嵌套使用是内部游标结束后给done置 FALSE

SET done=FALSE;

END IF;

END LOOP myLoop2;

-- 关闭游标

CLOSE My_Cursor;

END

$$

DELIMITER ;