上面说错了,不能用execute immediate执行存储过程. SQL> create table store_parameter 2 (id number(1));
Table created
SQL> insert into store_parameter select rownum from dual connect by rownum<8;
8 rows inserted
SQL> commit;
Commit complete
SQL> SQL> create table store_parameter1 2 (id number(1));
Table created
SQL> SQL> create or replace procedure proc_test(id number) 2 as 3 begin 4 insert into store_parameter1 values(id); 5 end; 6 /
Procedure created
SQL> SQL> declare 2 cv_test sys_refcursor; 3 v_num number(1); 4 begin 5 open cv_test for select id from store_parameter; 6 loop 7 fetch cv_test into v_num; 8 exit when cv_test%notfound; 9 proc_test(v_num); 10 end loop; 11 end ; 12 /
PL/SQL procedure successfully completed
SQL> select * from store_parameter1;
ID -- 1 2 3 4 5 6 7 8
8 rows selected
SQL> 你只需要把上面的匿名块放到what里面就可以了
SQL> truncate table store_parameter1;
Table truncatedSQL> declare 2 v_num number(20); 3 begin 4 dbms_job.submit( job =>v_num,what =>'declare 5 cv_test sys_refcursor; 6 v_num number(1); 7 begin 8 open cv_test for select id from store_parameter; 9 loop 10 fetch cv_test into v_num; 11 exit when cv_test%notfound; 12 proc_test(v_num); 13 end loop; 14 end ;',next_date => sysdate,interval => 'SYSDATE+1/24/60'); 15 end; 16 /
在job里调用不同过程,还不知道,
关注中。。
SQL> create table store_parameter
2 (id number(1));
Table created
SQL> insert into store_parameter select rownum from dual connect by rownum<8;
8 rows inserted
SQL> commit;
Commit complete
SQL>
SQL> create table store_parameter1
2 (id number(1));
Table created
SQL>
SQL> create or replace procedure proc_test(id number)
2 as
3 begin
4 insert into store_parameter1 values(id);
5 end;
6 /
Procedure created
SQL>
SQL> declare
2 cv_test sys_refcursor;
3 v_num number(1);
4 begin
5 open cv_test for select id from store_parameter;
6 loop
7 fetch cv_test into v_num;
8 exit when cv_test%notfound;
9 proc_test(v_num);
10 end loop;
11 end ;
12 /
PL/SQL procedure successfully completed
SQL> select * from store_parameter1;
ID
--
1
2
3
4
5
6
7
8
8 rows selected
SQL>
你只需要把上面的匿名块放到what里面就可以了
Table truncatedSQL> declare
2 v_num number(20);
3 begin
4 dbms_job.submit( job =>v_num,what =>'declare
5 cv_test sys_refcursor;
6 v_num number(1);
7 begin
8 open cv_test for select id from store_parameter;
9 loop
10 fetch cv_test into v_num;
11 exit when cv_test%notfound;
12 proc_test(v_num);
13 end loop;
14 end ;',next_date => sysdate,interval => 'SYSDATE+1/24/60');
15 end;
16 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * from store_parameter1;
ID
--
1
2
3
4
5
6
7
8
8 rows selected
那这个匿名块里可不可以放 select * from dual之类的查询语句,为什么要用游标呢