现有如下2张表..结构如下.
t1:id,val,col_id
t2:id,c1,c2,c3....c100.
如何将将t1表中的数据
1, v1, c1,
1, v4, c2,
2, v3, c3,
2, v4, c4,
3, v5, c100,
3,v6,c6, 插入到表t2中,插入后的数据如下.
id c1 c2 c3 c4 c5 c6 ... c100
1, v1 v4
2, v3 v4
3, v6 v5
[code="sql"]
--写个过程
PROCEDURE inserttot2 IS
BEGIN
DECLARE
CURSOR c_t1 IS
SELECT * FROM t1;
v_id t1.id%TYPE := 0;
v_val t1.val%TYPE := 0;
v_col_id t1.col_id%TYPE := 0;
BEGIN
OPEN c_t1;
LOOP
FETCH c_t1
INTO v_id, v_val, v_col_id;
EXIT WHEN c_t1%NOTFOUND;
EXECUTE IMMEDIATE 'update t2 set '||v_col_id||'=''' || v_val ||
''' where id=' || v_id;
IF (SQL%ROWCOUNT = 0) THEN
EXECUTE IMMEDIATE 'insert into t2(id,' || v_col_id || ')values(' ||
v_id ||',''' || v_val || ''')';
END IF;
END LOOP;
CLOSE c_t1;
END;
END inserttot2;
[/code]
[quote]
id c1 c2 c3 c4 c5 c6 ... c100
1, v1 v4
2, v3 v4
3, v6 v5[/quote]
表2的设计有很大的数据浪费（或空数据冗余）。
采用动态sql
这不是语句, 是个过程
报的什么错?
你的t1表中是不是没有id字段呀?