pl/sql问题

来源：百度知道 编辑：UC知道 时间：2024/06/11 03:43:32
declare
cursor c_table is
select long_id, long_value
from t_long@dblink_test_diana;
v_table t_long%rowtype;
begin
for v_table in c_table loop
insert into t_long values(v_table.long_id, v_table.long_value);
end loop;
commit;
end;

现在需要将其改写为过程或者函数供jdbc调用,
table_name和column_name为动态传入,
请问如何实现`
我自己写了下,如何获取传入table的rowtype就过不去了`

谢谢`
create or replace procedure pro_long(
table_name varchar2,
column_name varchar2
)
as
type t_cursor is ref cursor;
c_cursor t_cursor;
v_sql varchar2(2000);
v_table t_cursor;
begin
v_sql := 'select '||column_name||' from '||table_name;
dbms_output.put_line(v_sql);
open c_cursor for v_sql;
loop
fetch c_cursor into v_table;
dbms_output.put_line('FETCH');
exit when c_cursor%notfound;
end loop;
close c_cursor;
exception

create or replace procedure pro_long(
table_name varchar2,
column_name varchar2
)
as
type t_cursor is ref cursor;
c_cursor t_cursor;
v_sql varchar2(2000);
v_id varchar2(2000);
v_type varchar2(2000);
vv c_cursor%type;
begin
v_sql := 'select '||column_name||' from '||table_name;
dbms_output.put_line(v_sql);
open c_cursor for v_sql;
loop
fetch c_cursor into v_id,v_type;
dbms_output.put_line(v_id || ' ' || v_type);
exit when c_cursor%notfound;
DBMS_OUTPUT.PUT_LINE('fetch');
end loop;
close c_cursor;
exception
when others then
DBMS_OUTPUT.PUT_LINE(sqlcode);
DBMS_OUTPUT.PUT_LINE(sqlerrm);
rollback;
end;

exec pro_long('t_long','long_id, long_value');

SELECT COLUMN_NAME from DBA_TAB_COLUMNS where table_name='表名'
and date_type='LONG';

表名地