update table 复杂问题二解！

表A结构如下：参考：http://www.itpub.net/showthread.php?s=&postid=5632610#post5632610

表B结构如下：

请问，如何才能使用一条SQL语句（或者更可能高效的语句）按照trans_code表给出的转换规则将B表code进行更新？谢谢！
更新后A表结果应该是：

方法一，过程嵌套游标解决

致谢：Oracle QQ群 真空

---

create or replace procedure wsp_test_code
as
v_sql varchar2(1000);
v_col_name varchar2(50);
v_fromcode varchar2(30);
v_tocode varchar2(30);
cursor codecursor is select col_name,fromcode,tocode from trans_code ;
begin

open codecursor;
loop



fetch codecursor into v_col_name,v_fromcode,v_tocode ;

EXIT WHEN codecursor%NOTFOUND;

v_sql := 'update a set '|| v_col_name || '='||v_tocode||' where '|| v_col_name


||'='||chr(39)||v_fromcode||chr(39);



execute immediate v_sql;

commit;


end loop;


end wsp_test_code;

---

方法2：没有给出update

致谢：itpub 斑竹oldwain

如果列数有限， 那么像下面这样就行了：
scott@O9I.US.ORACLE.COM> select a0.id,
2 nvl(t1.tocode, code1),
3 nvl(t2.tocode, code2),
4 nvl(t3.tocode, code3),
5 nvl(t4.tocode, code4)
6 from a0, (select * from trans_code where col_name = 'code1') t1,
7 (select * from trans_code where col_name = 'code2') t2,
8 (select * from trans_code where col_name = 'code3') t3,
9 (select * from trans_code where col_name = 'code4') t4
10 where a0.code1 = t1.fromcode (+)
11 and a0.code2 = t2.fromcode (+)
12 and a0.code3 = t3.fromcode (+)
13 and a0.code4 = t4.fromcode (+);

ID NVL(T1.TOC NVL(T2.TOC NVL(T3.TOC NVL(T4.TOC
---- ---------- ---------- ---------- ----------
1 0011 0012 1011 1111
2 0011 0022 1000 1012

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/593324/viewspace-376137/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/593324/viewspace-376137/