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;
动态sql,动态游标
最新推荐文章于 2021-04-03 15:34:07 发布