代码大概是这样
begin
...
V_SQL := 'SELECT ' || V_COLNAME || ' FROM ' || V_TBNAME ||
' WHERE ' || V_COLNAME || '=''' || IN_VAR || '''';
---这里是动态SQL
..OPEN MY_CUR FOR V_SQL;
FETCH MY_CUR BULK COLLECT
INTO MY_VARRAY;FOR I IN MY_VARRAY.FIRST .. MY_VARRAY.LAST LOOP
INSERT INTO AAA_TEMP
(TBNAME, COLNAME, INDEXDATA, OPDATE, DATAROWID, USERS)
VALUES
(V_TBNAME, V_COLNAME, MY_VARRAY(i), sysdate, null, V_USER);
END LOOP;end;MY_VARRAY(i)只能取一列信息,如果我还想取ROWID,就不行了理论想SQL这么写
V_SQL := 'SELECT ' || V_COLNAME || ',rowid FROM ' || V_TBNAME ||
' WHERE ' || V_COLNAME || '=''' || IN_VAR || '''';
。。
INSERT INTO AAA_TEMP
(TBNAME, COLNAME, INDEXDATA, OPDATE, DATAROWID, USERS)
VALUES
(V_TBNAME, V_COLNAME, MY_VARRAY(i), sysdate, MY_VARRAY(i).rowid, V_USER);
但这样不行,有什么解决办法?
begin
...
V_SQL := 'SELECT ' || V_COLNAME || ' FROM ' || V_TBNAME ||
' WHERE ' || V_COLNAME || '=''' || IN_VAR || '''';
---这里是动态SQL
..OPEN MY_CUR FOR V_SQL;
FETCH MY_CUR BULK COLLECT
INTO MY_VARRAY;FOR I IN MY_VARRAY.FIRST .. MY_VARRAY.LAST LOOP
INSERT INTO AAA_TEMP
(TBNAME, COLNAME, INDEXDATA, OPDATE, DATAROWID, USERS)
VALUES
(V_TBNAME, V_COLNAME, MY_VARRAY(i), sysdate, null, V_USER);
END LOOP;end;MY_VARRAY(i)只能取一列信息,如果我还想取ROWID,就不行了理论想SQL这么写
V_SQL := 'SELECT ' || V_COLNAME || ',rowid FROM ' || V_TBNAME ||
' WHERE ' || V_COLNAME || '=''' || IN_VAR || '''';
。。
INSERT INTO AAA_TEMP
(TBNAME, COLNAME, INDEXDATA, OPDATE, DATAROWID, USERS)
VALUES
(V_TBNAME, V_COLNAME, MY_VARRAY(i), sysdate, MY_VARRAY(i).rowid, V_USER);
但这样不行,有什么解决办法?
test_cur MY_CUR%rowtype;INSERT INTO AAA_TEMP
(TBNAME, COLNAME, INDEXDATA, OPDATE, DATAROWID, USERS)
VALUES
(V_TBNAME, V_COLNAME, test_cur.||V_COLNAME ,sysdate, test_cur.rowid, V_USER);
试一下~
set serveroutput on;
declare
type t_varchar64 is table of varchar2(64) index by binary_integer ;
type t_rowid is table of ROWID index by binary_integer ;
vt_par1 t_varchar64;
vt_rowid t_rowid;
v_colname varchar2(100) := 'DEPTNO';
v_tbname varchar2(100) := 'test_yixl_emp';
in_var varchar2(100) := '10';
V_SQL varchar2(1000):= 'SELECT ' || V_COLNAME || ',rowid FROM ' || V_TBNAME ||
' WHERE ' || v_colname || '=''' || in_var || '''';
vs_msg varchar2(100) := '';
begin
dbms_output.put_line(v_sql);
execute immediate v_sql BULK COLLECT INTO vt_par1, vt_rowid;
for i in vt_par1.first .. vt_par1.last loop
vs_msg := to_char(i) || '-' || vt_par1(i) || '-' || to_char(vt_rowid(i));
dbms_output.put_line(vs_msg);
end loop;
end;