以下是一个MySQL存储过程,是游标嵌套的,在第二个游标中要使用到第一个游标所fetch到的值,如下代码:BEGIN
#Routine body goes here...
DECLARE v_CITY_ID varchar(20);
DECLARE v_ADMIN_TAB varchar(100);
DECLARE v_TYPE_USE INT;
DECLARE v_Sql_Select varchar(500);
DECLARE v_Sql_DELETE varchar(500);
DECLARE v_Sql_Create varchar(500);
DECLARE v_Sql_IDX varchar(500);
DECLARE v_Sql_Update varchar(500);
DECLARE done1,done2 INT DEFAULT 0;
DECLARE cur_poi_name CURSOR FOR
SELECT CITY_ID,POI_ADMIN_TAB FROM POI_ADMIN_DEFINE
WHERE substr(CITY_ID,3,2) <> 00 ORDER BY 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = 1;
OPEN cur_poi_name; cur1_loop:LOOP
FETCH cur_poi_name INTO v_CITY_ID,v_ADMIN_TAB; IF done1=1 THEN
LEAVE cur1_loop;
END IF;
BEGIN
#用FETCH到的v_ADMIN_TAB找出各市POI_TYPE_USE的值
DECLARE cur_poi_type CURSOR FOR
SELECT DISTINCT poi_type_use FROM 'v_ADMIN_TAB' WHERE poi_type_use IS NOT NULL ORDER BY poi_type_use; #此处有问题,其中v_ADMIN_TAB是变量,是从上面的cur_poi_name游标得到的
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
OPEN cur_poi_type; cur2_loop:LOOP
FETCH cur_poi_type INTO v_TYPE_USE;
IF done2=1 THEN
LEAVE cur2_loop;
END IF; #创建各市POI_TYPE_USE表
SET @v_Sql_Create = CONCAT('create table ',v_ADMIN_TAB,'_IDX_TYPE_USE',v_TYPE_USE,' as select * from ',v_ADMIN_TAB,' where poi_type_use = ',v_TYPE_USE,'order by display desc');
PREPARE p_sql FROM @v_Sql_Create;
EXECUTE p_sql; #表建索引
SET @v_Sql_IDX = CONCAT('CREATE UNIQUE INDEX ',v_ADMIN_TAB,'_USE',v_TYPE_USE,'_IDX ON ',v_ADMIN_TAB,'_IDX_TYPE_USE',v_TYPE_USE,' (POI_ID)');
PREPARE p_idx FROM @v_Sql_IDX;
EXECUTE p_idx;
END LOOP cur2_loop; CLOSE cur_poi_type;
END;
END LOOP cur1_loop;
CLOSE cur_poi_name;
END在以上代码 DECLARE cur_poi_type CURSOR FOR SELECT DISTINCT poi_type_use FROM 'v_ADMIN_TAB' WHERE poi_type_use IS NOT NULL ORDER BY poi_type_use; 中,红色标识的部分有问题,不知道v_ADMIN_TAB变量在这如何使用,还请高手指点。谢谢!
#Routine body goes here...
DECLARE v_CITY_ID varchar(20);
DECLARE v_ADMIN_TAB varchar(100);
DECLARE v_TYPE_USE INT;
DECLARE v_Sql_Select varchar(500);
DECLARE v_Sql_DELETE varchar(500);
DECLARE v_Sql_Create varchar(500);
DECLARE v_Sql_IDX varchar(500);
DECLARE v_Sql_Update varchar(500);
DECLARE done1,done2 INT DEFAULT 0;
DECLARE cur_poi_name CURSOR FOR
SELECT CITY_ID,POI_ADMIN_TAB FROM POI_ADMIN_DEFINE
WHERE substr(CITY_ID,3,2) <> 00 ORDER BY 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = 1;
OPEN cur_poi_name; cur1_loop:LOOP
FETCH cur_poi_name INTO v_CITY_ID,v_ADMIN_TAB; IF done1=1 THEN
LEAVE cur1_loop;
END IF;
BEGIN
#用FETCH到的v_ADMIN_TAB找出各市POI_TYPE_USE的值
DECLARE cur_poi_type CURSOR FOR
SELECT DISTINCT poi_type_use FROM 'v_ADMIN_TAB' WHERE poi_type_use IS NOT NULL ORDER BY poi_type_use; #此处有问题,其中v_ADMIN_TAB是变量,是从上面的cur_poi_name游标得到的
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
OPEN cur_poi_type; cur2_loop:LOOP
FETCH cur_poi_type INTO v_TYPE_USE;
IF done2=1 THEN
LEAVE cur2_loop;
END IF; #创建各市POI_TYPE_USE表
SET @v_Sql_Create = CONCAT('create table ',v_ADMIN_TAB,'_IDX_TYPE_USE',v_TYPE_USE,' as select * from ',v_ADMIN_TAB,' where poi_type_use = ',v_TYPE_USE,'order by display desc');
PREPARE p_sql FROM @v_Sql_Create;
EXECUTE p_sql; #表建索引
SET @v_Sql_IDX = CONCAT('CREATE UNIQUE INDEX ',v_ADMIN_TAB,'_USE',v_TYPE_USE,'_IDX ON ',v_ADMIN_TAB,'_IDX_TYPE_USE',v_TYPE_USE,' (POI_ID)');
PREPARE p_idx FROM @v_Sql_IDX;
EXECUTE p_idx;
END LOOP cur2_loop; CLOSE cur_poi_type;
END;
END LOOP cur1_loop;
CLOSE cur_poi_name;
END在以上代码 DECLARE cur_poi_type CURSOR FOR SELECT DISTINCT poi_type_use FROM 'v_ADMIN_TAB' WHERE poi_type_use IS NOT NULL ORDER BY poi_type_use; 中,红色标识的部分有问题,不知道v_ADMIN_TAB变量在这如何使用,还请高手指点。谢谢!
set @asql=concat('cretae table newtt as SELECT DISTINCT poi_type_use FROM ',v_ADMIN_TAB,' WHERE poi_type_use IS NOT NULL ORDER BY poi_type_use');
prepare stml from @asql;
execute stml;
DECLARE cur_poi_type CURSOR FOR select * from newtt ;
......
BEGIN
SET @v_Sql_Select=CONCAT('CREATE TABLE TMP_TABLE AS SELECT DISTINCT poi_type_use FROM ',v_ADMIN_TAB,' WHERE poi_type_use IS NOT NULL ORDER BY poi_type_use');
PREPARE p_sel FROM @v_Sql_Select;
EXECUTE p_sel;
DECLARE cur_poi_type CURSOR FOR SELECT * FROM TMP_TABLE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
OPEN cur_poi_type;
.......
放在 DECLARE done1,done2 INT DEFAULT 0;后面
create procedure x ()
begin
declare a,b,c,d int;
declare done int default 0;
DECLARE cur1 CURSOR FOR SELECT id FROM t1 where id<5;
DECLARE cur2 CURSOR FOR SELECT id,f1 FROM t2 where id=a;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
WHILE done=0 DO
FETCH cur1 INTO a;
OPEN cur2;
FETCH cur2 INTO b,c;
CLOSE cur2;
select a,b,c;
END WHILE;
CLOSE cur1;
end//
delimiter ;
SET @v_Sql_Select=CONCAT('CREATE TABLE TMP_TABLE AS SELECT DISTINCT poi_type_use FROM ',v_ADMIN_TAB,' WHERE poi_type_use IS NOT NULL ORDER BY poi_type_use');
PREPARE p_sel FROM @v_Sql_Select;
EXECUTE p_sel;