请问,哪位大侠能帮忙将以下oracle的存储过程转为MySQL的存储过程,谢谢!create or replace PROCEDURE PRO_POI_TYPE_USE_TAB AS
v_CITY_ID varchar2(100);
v_ADMIN_TAB varchar2(100);
v_TYPE_USE number;
v_Sql_Select varchar2(500);
v_Sql_Create varchar2(500);
TYPE T_CUR IS REF CURSOR;
cur_poi_type T_CUR;CURSOR cur_poi_name IS
SELECT CITY_ID,POI_ADMIN_TAB
FROM POI_ADMIN_DEFINE where substr(CITY_ID,3,2) <> 00;BEGIN FOR recode_POI_NAME IN cur_poi_name LOOP
v_CITY_ID := recode_POI_NAME.CITY_ID;
v_ADMIN_TAB := recode_POI_NAME.POI_ADMIN_TAB; --找出各市POI_TYPE_USE的值
v_Sql_Select := 'select distinct poi_type_use from '|| v_ADMIN_TAB ||' where poi_type_use is not null order by poi_type_use ';
OPEN cur_poi_type FOR v_Sql_Select;
LOOP
FETCH cur_poi_type INTO v_TYPE_USE;
EXIT WHEN cur_poi_type%NOTFOUND; --未查到数据退出
--DBMS_OUTPUT.PUT_LINE(v_TYPE_USE);
--创建各市POI_TYPE_USE表
v_Sql_Create := '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 ';
--dbms_output.put_line(v_Sql_Create);
execute immediate v_Sql_Create;
END LOOP;
CLOSE cur_poi_type;
END LOOP;
END;
v_CITY_ID varchar2(100);
v_ADMIN_TAB varchar2(100);
v_TYPE_USE number;
v_Sql_Select varchar2(500);
v_Sql_Create varchar2(500);
TYPE T_CUR IS REF CURSOR;
cur_poi_type T_CUR;CURSOR cur_poi_name IS
SELECT CITY_ID,POI_ADMIN_TAB
FROM POI_ADMIN_DEFINE where substr(CITY_ID,3,2) <> 00;BEGIN FOR recode_POI_NAME IN cur_poi_name LOOP
v_CITY_ID := recode_POI_NAME.CITY_ID;
v_ADMIN_TAB := recode_POI_NAME.POI_ADMIN_TAB; --找出各市POI_TYPE_USE的值
v_Sql_Select := 'select distinct poi_type_use from '|| v_ADMIN_TAB ||' where poi_type_use is not null order by poi_type_use ';
OPEN cur_poi_type FOR v_Sql_Select;
LOOP
FETCH cur_poi_type INTO v_TYPE_USE;
EXIT WHEN cur_poi_type%NOTFOUND; --未查到数据退出
--DBMS_OUTPUT.PUT_LINE(v_TYPE_USE);
--创建各市POI_TYPE_USE表
v_Sql_Create := '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 ';
--dbms_output.put_line(v_Sql_Create);
execute immediate v_Sql_Create;
END LOOP;
CLOSE cur_poi_type;
END LOOP;
END;
改的中间如果有什么问题,可以把你的语句和错误信息一起贴出来大家一起参考。MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
#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 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;
OPEN cur_poi_name;
cursor_loop:LOOP
FETCH cur_poi_name INTO v_CITY_ID,v_ADMIN_TAB; #用FETCH到的v_ADMIN_TAB找出各市POI_TYPE_USE的值
SET @v_Sql_Select = CONCAT('select distinct poi_type_use from ',v_ADMIN_TAB,' where poi_type_use is not null order by poi_type_use');
OPEN cur_poi_type FOR v_Sql_Select;
LOOP
FETCH cur_poi_type INTO v_TYPE_USE; #创建各市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;
END LOOP;
END LOOP cursor_loop;
CLOSE cur_poi_name;
END
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 ;