例如:
create or replace procedure test(p_table_name varchar2)
as
cursor cur_a is
select * from p_table_name;
begin
end test;这样编译会报错。说p_table_name 不是一个表名。。
请问如何编写这个过程啊。。
create or replace procedure test(p_table_name varchar2)
as
cursor cur_a is
select * from p_table_name;
begin
end test;这样编译会报错。说p_table_name 不是一个表名。。
请问如何编写这个过程啊。。
begin 上面是声明变量的
楼上说的对 动态游标
v_sfc_trans t_sfc_trans;
begin open v_sfc_trans for select * from .....
close v_sfc_trans;end
create or replace procedure table_data_copy1(pSourceTable in varchar2) is
type mycursor is ref cursor;
c_data mycursor;
/* cursor c_data is
select * from test3; */
v_temp test2%rowtype;
begin
open c_data for select * from pSourceTable;
loop
fetch c_data into v_temp;
insert into test2 values v_temp;
exit when c_data%notfound;
end loop;
close c_data;
end table_data_copy1;仍会报编译错误Compilation errors for PROCEDURE TEST1.TABLE_DATA_COPY1Error: PL/SQL: ORA-00942: 表或视图不存在
Line: 12
Text: open c_data for select * from pSourceTable;Error: PL/SQL: SQL Statement ignored
Line: 12
Text: open c_data for select * from pSourceTable;
............
s:='select * from '||pSourceTable||'';open c_data for s
v_sql varchar2(50);
BEGIN
v_sql:='SELECT * from ' || p_table_name ;
OPEN p_cursor FOR
v_sql;
END ll_02;
/
TYPE EmpCurTyp IS REF CURSOR;
c_tab_Cur EmpCurTyp;
cur_sql varchar2(2000);
begin
cur_sql :='select * from ' ||p_table_name;
open c_tab_Cur for cur_sql;
......
end test;
create or replace procedure table_data_copy(SourceTable in varchar2) is
type mycursor is ref cursor;
c_data mycursor;
/* cursor c_data is
select * from test3; */
v_temp b%rowtype;
begin
open c_data for 'select * from '||SourceTable;
loop
fetch c_data into v_temp; --注意这里语句的顺序
exit when c_data%notfound;
insert into b values v_temp; --跟你新学的。
end loop;
close c_data;
end table_data_copy;