存储过程cursor有关问题_Oracle管理

存储过程cursor问题
请看以下存储过程中蓝色字部分，谢谢！

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_DELETE varchar2(500);
v_Sql_Create varchar2(500);
v_Sql_Update varchar2(500);

CURSOR cur_poi_name IS
SELECT CITY_ID,POI_ADMIN_TAB
FROM scott.POI_ADMIN_DEFINE where substr(CITY_ID,3,2) <> 00 and POI_ADMIN_TAB='POI_ADMIN_3101';
CURSOR cur_poi_type IS
SELECT POI_TYPE_USE FROM scott.'|| v_ADMIN_TAB ||'; -->这个红色的地方有问题,我想用上面的那个cursor得到的recode变量值放这，不知道如何用。
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的值
FOR recode_poi_type IN cur_poi_type LOOP
v_TYPE_USE := recode_poi_type.POI_TYPE_USE;
--v_Sql_Select := 'select poi_type_use from scott.'|| v_ADMIN_TAB ||' ';
-- EXECUTE IMMEDIATE v_Sql_Select INTO v_TYPE_USE;
-- dbms_output.put_line(v_Sql_Select);

--重新创建各市POI_TYPE_USE表
v_Sql_Create := 'create table scott.' || v_ADMIN_TAB || ' _USE'|| v_TYPE_USE ||' as select * from scott.'|| v_ADMIN_TAB ||' where poi_type_use = '|| v_TYPE_USE ||' ';
dbms_output.put_line(v_Sql_Create);
--execute immediate v_Sql_Create;
END LOOP;
END LOOP;
END;

------解决方案--------------------
使用动态游标...
v_sql:='SELECT WLBMC FROM T_CLEAR WHERE TRIM(WLBMC) IN ('||v_str||') ORDER BY BZ DESC';
OPEN c_tab FOR v_sql;

------解决方案--------------------

SQL code

-- 给个例子给你：SET SERVEROUTPUT ONDECLARE  CURSOR c_student IS    SELECT first_name, last_name, student_id      FROM student     WHERE last_name LIKE 'J%';  CURSOR c_coruse (i_student_id IN student.student_id%TYPE) IS    SELECT c.description, s.section_id sec_id      FROM course c, section s, enrollment e     WHERE e.student_id = i_studnet_id       AND c.course_no = s.course_no       AND s.section_id = e.section_id;  COURSOR c_grade(i_section_id IN section.section_id%TYPE, i_student_id IN student.student_id%TYPE) IS    SELECT gt.description grd_desc, TO_CHAR(AVG(g.numeric_grade), '999.99') num_grd      FROM enrollment e, grade g, grade_type gt     WHERE e.section_id = i_section_id       AND e.student_id = g.student_id       AND e.student_id = i_student_id       AND e.section_id = g.section_id       AND g.grade_type_code = gt.grade_type_code     GROUP BY gt.description;BEGIN  FOR r_student IN c_student  LOOP    DBMS_OUTPUT.PUT_LINE(CHR(10));    DBMS_OUTPUT.PUT_LINE(r_student.first_name||' '||r_student.last_name);    FOR r_course IN c_course(r_student.student_id)    LOOP      DBMS_OUTPUT.PUT_LINE('Grades for course: '||r_course.description);      FOR r_grade IN c_grade(r_course.sec_id, r_student.student_id)      LOOP        DBMS_OUTPUT.PUT_LINE(r_grade.num_grd||' '||r_grade.grd_desc);      END LOOP;    END LOOP;  END LOOP;END;/
------解决方案--------------------
SQL code--如果数据库对象作为变量，需要使用动态游标declare v_tableName varchar2(20) := 'user_tables';v_str varchar2(50);type tcur is ref cursor; cur tcur;begin  open cur  for 'select table_name from '||v_tableName;loop  fetch cur into v_str;  exit when cur%notfound ;    dbms_output.put_line(v_str);end loop ;close cur;end ;