动态sql,动态游标

create or replace package body two_table is
　　
　　PROCEDURE two_tables(p_type_cols OUT type_cols)
　　IS
　　v_column_name VARCHAR2(100);
　　v_sql VARCHAR2(32767);
　　v_sqls VARCHAR2(32767);
　　ref_cursor sys_refcursor;
　　
　　type record_class is record(
　　 rn VARCHAR2(100),
　　 actual VARCHAR2(100),
　　 expected VARCHAR2(100),
　　 column_id VARCHAR2(100)
　　 );
　　rec_class record_class;
　　
　　BEGIN
　 　 FOR rec_column IN( SELECT * FROM dba_tab_cols
　　 WHERE upper(table_name) = upper('class')
　　 AND upper(owner)=upper('etl')) LOOP
　　
　 　 v_column_name := rec_column.column_name;
　　 v_sql := v_sql
　　 || 'select a.rn,' || 'to_char(' || 'a.' || v_column_name || ')' || ' AS actual' || ','
　　 || 'to_char(' || 'b.' || v_column_name || ')' || ' AS expected' || ','
　 　 || 'to_char(' || rec_column.column_id || ')' || ' AS column_id'
　　 || chr(10)
　　 || 'FROM'
　　 || chr(10)
　　 || '(select ROWNUM rn,a.* from ( select * from class order by c_id) a) a,'
　 　 || chr(10)
　　 || '(select ROWNUM rn,b.* from ( select * from class order by c_id) b) b'
　　 || chr(10)
　　 || 'where a.rn = b.rn'
　　 || chr(10)
　　 || 'union all'
　　 || chr(10);
　　
　　 END LOOP;
　　
　 　 --去掉最后的union all
　　 v_sql := substr(v_sql, 1,length(v_sql) - 10);
　　 /*v_sql := rtrim(trim(v_sql), chr(10) || 'union all');*/
　 　
　　 v_sql := 'select * from (' || chr(10) || v_sql || chr(10) || ') c order by c.rn,c.column_id';
　　 /* dbms_output.put_line(v_sql);*/
　　
　　 --动态游标
　　 open ref_cursor for v_sql;
　　 loop
　　 fetch ref_cursor into rec_class;
　　
　　 p_type_cols(rec_class.rn).testCase := 'procedure';
　　 p_type_cols(rec_class.rn).row_number := rec_class.rn;
　　 p_type_cols(rec_class.rn).col_number := rec_class.column_id;
　　 p_type_cols(rec_class.rn).expected := rec_class.expected;
　　 p_type_cols(rec_class.rn).actual := rec_class.actual;
　　
　　 CASE
　　 WHEN rec_class.actual = rec_class.expected THEN p_type_cols(rec_class.rn).equal := 'Y';
　　 ELSE p_type_cols(rec_class.rn).equal := 'N';
　　 END CASE;
　　
　　 exit when ref_cursor%notfound;
　　 end loop;
　 　 close ref_cursor;
　　
　　END;
　　
　　end two_table;