过程代码如下:create or replace procedure test_insert (ls_id in number)
as
id varchar2(10);
ic varchar2(10);
ie varchar2(40);
ir varchar2(10);
it number(12,2);
begin
declare cursor test_insert1 is
select c_sku_id ,c_kind_id ,c_goods_name ,c_supply_id,n_standard_price
into id,ic,ie,ir,it
from infor_sku
where it >= ls_id;
begin
for test2 in test_insert1 loop
insert into test (sku_id,kind_id,goods_name,supply_id,standard_price)values (id,ic,ie,ir,it);
end loop;
end;
end;小弟着急,在线等
as
id varchar2(10);
ic varchar2(10);
ie varchar2(40);
ir varchar2(10);
it number(12,2);
begin
declare cursor test_insert1 is
select c_sku_id ,c_kind_id ,c_goods_name ,c_supply_id,n_standard_price
into id,ic,ie,ir,it
from infor_sku
where it >= ls_id;
begin
for test2 in test_insert1 loop
insert into test (sku_id,kind_id,goods_name,supply_id,standard_price)values (id,ic,ie,ir,it);
end loop;
end;
end;小弟着急,在线等
as
cursor test_insert1 is
select c_sku_id ,c_kind_id ,c_goods_name ,c_supply_id,n_standard_price
from infor_sku
where it >= ls_id;
begin
for test2 in test_insert1 loop
begin
insert into test (sku_id,kind_id,goods_name,supply_id,standard_price)values (test2.c_sku_id ,test2.c_kind_id ,test2.c_goods_name ,test2.c_supply_id,test2.n_standard_price );
commit;
end;
end loop;
end;
as
id varchar2(10);
ic varchar2(10);
ie varchar2(40);
ir varchar2(10);
it number(12,2);
cursor test_insert1 is select c_sku_id ,c_kind_id ,c_goods_name ,c_supply_id,n_standard_price from infor_sku
where it >= ls_id;
begin
open test_insert1;
LOOP
FETCH test_insert1 into id,ic,ie,ir,it;
exit when test_insert1%NOTFOUND;
insert into test(sku_id,kind_id,goods_name,supply_id,standard_price)values (id,ic,ie,ir,it);
END LOOP ;
commit;
end;
/
我按照你的试了下,结果还是和以前一样,INSERT的语句没有执行,但是这个过程的效率比我原来的低了不少
我 忘了 close test_insert1;
没必要写那么复杂。 不需要用游标。create or replace procedure test_insert (ls_id in number)
as
begin
insert into test(sku_id,kind_id,goods_name,supply_id,standard_price)
select c_sku_id ,c_kind_id ,c_goods_name ,c_supply_id,n_standard_price
from infor_sku where it >= ls_id;
commit;
end;
/
from infor_sku where it >= ls_id; 能查到数据 才能插入。
DEVPDSPB:PDS_STAGING>create or replace procedure test_insert (ls_id in number)
2 as
3 id varchar2(10);
4 ic varchar2(10);
5 ie varchar2(40);
6 ir varchar2(10);
7 it number(12,2);
8 cursor test_insert1 is select c_sku_id ,c_kind_id ,c_goods_name ,c_supply_id,n_standard_price f
rom infor_sku
9 where it >= ls_id;
10 begin
11 open test_insert1;
12 LOOP
13 FETCH test_insert1 into id,ic,ie,ir,it;
14 exit when test_insert1%NOTFOUND;
15 insert into test(c_sku_id,c_kind_id,c_goods_name,c_supply_id,n_standard_price)values (id,ic
,ie,ir,it);
16 END LOOP ;
17 commit;
18 end;
19 / Procedure created.
insert into infor_sku (c_sku_id ,c_kind_id ,c_goods_name,c_supply_id ,n_standard_price , it )
select '1','2','3','4',5,6 from dual
commit
create table test as select * from infor_sku
truncate table test
DEVPDSPB:PDS_STAGING>execute test_insert(1);
PL/SQL procedure successfully completed.Elapsed: 00:00:00.00
DEVPDSPB:PDS_STAGING>select * from test; C_SKU_ID C_KIND_ID C_GOODS_NAME C_SUPPLY_ID N_STANDARD_PRICE IT
---------- ---------- ------------ ----------- ---------------- ----------
1 2 3 4 51 row selected.Elapsed: 00:00:00.00看看你的新表的结构 与数据间有没有什么问题
create or replace procedure test_insert (ls_id in number)
as
begin
declare cursor test_insert1 is
select c_sku_id ,c_kind_id ,c_goods_name ,c_supply_id,n_standard_price
from infor_sku
where n_standard_price >= ls_id;
begin
for test2 in test_insert1 loop
insert into test (sku_id,kind_id,goods_name,supply_id,standard_price)
(select c_sku_id ,
c_kind_id ,
c_goods_name ,
c_supply_id,
n_standard_price
from infor_sku
where n_standard_price >= ls_id);
end loop;
end;
end;
代码如下:
create or replace procedure test_insert (ls_id in number)
as
id varchar2(10);
ic varchar2(10);
ie varchar2(40);
ir varchar2(10);
it number(12,2);
begin
declare cursor test_insert1 is
select c_sku_id ,c_kind_id ,c_goods_name ,c_supply_id,n_standard_price
from infor_sku
where n_standard_price >= ls_id;
begin
open test_insert1;
loop
fetch test_insert1 into id,ic,ie,ir,it;
exit when test_insert1% notfound;
insert into test(sku_id,kind_id,goods_name,supply_id,standard_price) values(id,ic,ie,ir,it);
end loop;
close test_insert1;
commit;
end;
end;