使用动态游标... v_sql:='SELECT WLBMC FROM T_CLEAR WHERE TRIM(WLBMC) IN ('||v_str||') ORDER BY BZ DESC'; OPEN c_tab FOR v_sql;
看了lz的问题自己也学习了下,你找下ref cursor的东西看能否满足你的要求
-- 要将其 select 字段取一个别名!
-- 给个例子给你:SET SERVEROUTPUT ON DECLARE 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; /
--如果数据库对象作为变量,需要使用动态游标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 ;
v_sql:='SELECT WLBMC FROM T_CLEAR WHERE TRIM(WLBMC) IN ('||v_str||') ORDER BY BZ DESC';
OPEN c_tab FOR v_sql;
DECLARE
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;
/
--如果数据库对象作为变量,需要使用动态游标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 ;