select C.column_name from USER_TAB_COLUMNS C , USER_TABLES T where C.TABLE_NAME = T.TABLE_NAME and T.TABLE_NAME = 'PRODUCT' 这样可以取出所有字段名,但是不知道怎么将字段名和值对应起来
STR1 STR2 TEST -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- asd fdfd 1 sdsd wewe 2 3 3 3set serveroutput on size 20000; declare v_column_name char(50); v_table_name varchar2(50) := 'test_sj'; v_sql varchar2(4000); v_l_count number; v_l_src number := 1; v_l_des number := 2; v_l_interval number := 1; v_value_src char(50); v_value_des char(50); cursor v_cursor is select a.column_name from user_tab_columns a where lower(a.TABLE_NAME)=lower(v_table_name) order by a.COLUMN_ID; begin v_sql := 'select count(1) from '||v_table_name; execute immediate v_sql into v_l_count; for i in 1..v_l_count-v_l_interval loop v_l_src := i; v_l_des := i+v_l_interval;
dbms_output.put_line('************************************************************'); open v_cursor; loop fetch v_cursor into v_column_name; exit when v_cursor%NOTFOUND; v_sql := 'select '||v_column_name||' from (select rownum as rn ,a.* from '||v_table_name||' a) a where a.rn = '||to_char(v_l_src); execute immediate v_sql into v_value_src ; v_sql := 'select '||v_column_name||' from (select rownum as rn ,a.* from '||v_table_name||' a) a where a.rn = '||to_char(v_l_des); execute immediate v_sql into v_value_des; if v_value_src <> v_value_des then dbms_output.put_line('列名:'||lower(v_column_name)||'源:'||v_value_src||'目的:'||v_value_des||'源行:'||to_char(v_l_src)||' 目的行:'||to_char(v_l_des)); end if; end loop; close v_cursor;
end loop;
exception when others then dbms_output.put_line('Exception'); end; /SQL>
、select * from tableA t,tableB t1 where t.id != t1.id
code name employeeCode ...
001 名字1 code1
001 名字2 code2
例如上面两条数据,比较一下,应该输出name: 名字1 名字2;employeeCode: code1 code2;...
但是我并不知道这张表里具体有哪些字段。这个应该怎么写呢?
from USER_TAB_COLUMNS C
, USER_TABLES T
where C.TABLE_NAME = T.TABLE_NAME
and T.TABLE_NAME = 'PRODUCT'
这样可以取出所有字段名,但是不知道怎么将字段名和值对应起来
ID USERID USERNAME
-- -------------------- --------------------
1 .net1 张三
1 .net2 李四
1 .net3 王五
2 .net4 陈六
2 .net5 赵七
SQL> select id,wm_concat(userid) usid ,wm_concat(username) uname from a group by id;
ID USID UNAME
-- -------------------- ------------------------------
1 .net1,.net2,.net3 张三,王五,李四
2 .net4,.net5 陈六,赵七
可是如果这张表有50个字段,不能select id,wm_conc……都罗列出来啊,那该怎么办呢?而且我只想输出值不同的字段,这样的话所有的值都输出来了
以前用java写过,封装到类,比较对象属性!
SQL> select * from test_sj;
STR1 STR2 TEST
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
asd fdfd 1
sdsd wewe 2
3 3 3set serveroutput on size 20000;
declare
v_column_name char(50);
v_table_name varchar2(50) := 'test_sj';
v_sql varchar2(4000);
v_l_count number;
v_l_src number := 1;
v_l_des number := 2;
v_l_interval number := 1;
v_value_src char(50);
v_value_des char(50);
cursor v_cursor is select a.column_name from user_tab_columns a where lower(a.TABLE_NAME)=lower(v_table_name) order by a.COLUMN_ID;
begin
v_sql := 'select count(1) from '||v_table_name;
execute immediate v_sql into v_l_count;
for i in 1..v_l_count-v_l_interval
loop
v_l_src := i;
v_l_des := i+v_l_interval;
dbms_output.put_line('************************************************************');
open v_cursor;
loop
fetch v_cursor into v_column_name;
exit when v_cursor%NOTFOUND;
v_sql := 'select '||v_column_name||' from (select rownum as rn ,a.* from '||v_table_name||' a) a where a.rn = '||to_char(v_l_src);
execute immediate v_sql into v_value_src ;
v_sql := 'select '||v_column_name||' from (select rownum as rn ,a.* from '||v_table_name||' a) a where a.rn = '||to_char(v_l_des);
execute immediate v_sql into v_value_des;
if v_value_src <> v_value_des then
dbms_output.put_line('列名:'||lower(v_column_name)||'源:'||v_value_src||'目的:'||v_value_des||'源行:'||to_char(v_l_src)||' 目的行:'||to_char(v_l_des));
end if;
end loop;
close v_cursor;
end loop;
exception
when others then
dbms_output.put_line('Exception');
end;
/SQL>
************************************************************
列名:str1 源:asd 目的:sdsd 源行:1 目的行:2
列名:str2 源:fdfd 目的:wewe 源行:1 目的行:2
列名:test 源:1 目的:2 源行:1 目的行:2
************************************************************
列名:str1 源:sdsd 目的:3 源行:2 目的行:3
列名:str2 源:wewe 目的:3 源行:2 目的行:3
列名:test 源:2 目的:3 源行:2 目的行:3
PL/SQL procedure successfully completed