set serveroutput on declare v_column varchar2(20); cursor t1_sor is select column_name from dba_cons_columns where owner = 'IDG_USER' and constraint_name in ( select constraint_name from dba_constraints where owner = 'IDG_USER' and table_name = 'AREA' and constraint_type = 'P' ); cursor t2_sor is select 62 as tableId, a.column_name, a.column_id, a.column_name , a.data_type, a.data_length, null as description, b.comments from dba_tab_columns a, dba_col_comments b where a.owner = 'IDG_USER' and a.table_name = 'AREA' and b.owner = a.owner and b.table_name = a.table_name; begin for v2_sor in t2_sor loop for v1_sor in t1_sor loop if v2_sor.column_name=v1_sor.column_name then v_column;=1; else v_column:=0; end if; end loop; dbms_output.put_line(v2_sor.tableId, v2_sor.column_name, v2_sor.column_id, v_column,v2_sor.data_type, v2_sor.data_length, v2_sor.description,v2_sor.comments); end loop; end;
得把中间的那部分可以写成一个函数来处理
declare
v_column varchar2(20);
cursor t1_sor is
select column_name from dba_cons_columns where owner = 'IDG_USER' and constraint_name in ( select constraint_name from dba_constraints where owner = 'IDG_USER' and table_name = 'AREA' and constraint_type = 'P' );
cursor t2_sor is
select 62 as tableId, a.column_name, a.column_id,
a.column_name , a.data_type, a.data_length, null as description, b.comments from dba_tab_columns a, dba_col_comments b where a.owner = 'IDG_USER' and a.table_name = 'AREA' and b.owner = a.owner and b.table_name = a.table_name;
begin
for v2_sor in t2_sor loop
for v1_sor in t1_sor loop
if v2_sor.column_name=v1_sor.column_name then
v_column;=1;
else
v_column:=0;
end if;
end loop;
dbms_output.put_line(v2_sor.tableId, v2_sor.column_name, v2_sor.column_id, v_column,v2_sor.data_type, v2_sor.data_length, v2_sor.description,v2_sor.comments);
end loop;
end;