使用游标插入数据报错

[killuakidd]

DECLARE
vQty integer;
vQpc integer;

cursor c is
select rowid, fpickarea, fbin, farticle from fu_tbinarticles_20081228;

BEGIN

DELETE FROM fu_tbinarticles_20081228;
COMMIT;


INSERT INTO fu_tbinarticles_20081228(farticle, fpickarea, fbin)
select a.fgid farticle,
(select fpickarea from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickarea,
(select fpickbin from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickbin
from tarticle a;

for r in c loop
select nvl(sum(fqty), 0) into vQty ,fqpc into vQpc from tbinarticles where fbin = r.fbin and farticle = r.farticle;
update fu_tbinarticles_20081228 set fqty = vQty,fqpc=vQpc where rowid = r.rowid;
end loop;
commit;
END;


ORA-06550: 第 21 行, 第 46 列:
PL/SQL: ORA-00923: 未找到预期 FROM 关键字
ORA-06550: 第 21 行, 第 5 列:
PL/SQL: SQL Statement ignored

[watson110]

DECLARE 
  vQty integer; 
  vQpc integer; 
  v_sql varchar(1000); 
  cursor c is 
  select rowid, fpickarea, fbin, farticle from fu_tbinarticles_20081228; 

BEGIN 
DELETE FROM fu_tbinarticles_20081228; 
COMMIT; 

  INSERT INTO fu_tbinarticles_20081228(farticle, fpickarea, fbin) 
  select a.fgid farticle, 
  (select fpickarea from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickarea, 
  (select fpickbin from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickbin 
  from tarticle a; 

  for r in c loop 
    select nvl(sum(fqty), 0) into vQty ,fqpc into vQpc from tbinarticles where fbin = r.fbin and farticle = r.farticle; 
  v_sql := 'update fu_tbinarticles_20081228 set fqty = '||vQty||',fqpc='||vQpc||' where rowid = r.rowid'; 
  dbms_output.put_line(v_sql);
   EXECUTE IMMEDIATE (v_sql) ;
   EXECUTE IMMEDIATE ('COMMIT');
  end loop; 
  commit; 
END;

[killuakidd]

ORA-06550: 第 19 行, 第 46 列:
PL/SQL: ORA-00923: 未找到预期 FROM 关键字
ORA-06550: 第 19 行, 第 5 列:
PL/SQL: SQL Statement ignored

以上的这样报错,究竟什么问题啊,晕

[linzi]

select nvl(sum(fqty), 0) into vQty ,fqpc into vQpc from tbinarticles where fbin = r.fbin and farticle = r.farticle;
改成：
select nvl(sum(fqty), 0) ,fqpc into vQty, vQpc from tbinarticles where fbin = r.fbin and farticle = r.farticle;

[killuakidd]

楼上的,这个ORA-01403: 未找到数据
ORA-06512: 在line 19
写法早知试过了,还是报错,想不通.....

[killuakidd]

但是还是报错啊!!!!

[killuakidd]

ORA-06550: 第 21 行, 第 29 列:
PL/SQL: ORA-00911: 无效字符
ORA-06550: 第 21 行, 第 5 列:
PL/SQL: SQL Statement ignored

报这个错误

[watson110]

你先把两个变量改成一个变量试试
vQty integer;
vQpc integer;
去掉其中一个试试，估计你的insert语句写的有问题

[watson110]

终于知道楼主的错误了，我给你做个演示：

CREATE TABLE A
(
  DATETIME  VARCHAR2(20 BYTE),
  NUM       VARCHAR2(10 BYTE)
)
insert into A values('200801',8);
insert into A values('200802',4);
insert into A values('200803',3);

这时按照楼主的写法

declare
v1 varchar(20);
v2 varchar(10);
begin
select datetime into v1,num into v2 from A where num='8';
end;

就会报这个错误：
ORA-06550: 第 19 行, 第 46 列:
PL/SQL: ORA-00923: 未找到预期 FROM 关键字
ORA-06550: 第 19 行, 第 5 列:
PL/SQL: SQL Statement ignored
--按照正确的写法：

declare
v1 varchar(20);
v2 varchar(10);
begin
select datetime ,num into v1, v2 from A where num='9';
end;

就会报“未找到数据 ”的错误
--因为楼主根本就没有这条数据，插入的是空值

[watson110]

而

declare
v1 varchar(20);
v2 varchar(10);
begin
select datetime ,num into v1, v2 from A where num='8';
end;

就是正确的
所以楼主应当在前面判断一下count,
select count(*) into v_count from tbinarticles where fbin = r.fbin and farticle = r.farticle;
if v_count==1 then
先确定是否有且仅有一条记录，然后再执行

[watson110]

判断一下fqpc是不是空，是空值就单独更新那个字段，如果不是空就两个字段都更新

[killuakidd]

我不用update都不行哦,不更新也是报错

[irrational]

模拟了一个执行，感觉是对的！

[killuakidd]

语法的确是百份百准确.就是报没有效数据,郁闷,真不知是什么问题,插入哪里也没有问题啊

[killuakidd]

DECLARE
vQty integer;
vQpc integer;
v_count integer;

cursor c is
select rowid, fpickarea, fbin, farticle from fu_tbinarticles_20081228;

BEGIN

DELETE FROM fu_tbinarticles_20081228;
COMMIT;



INSERT INTO fu_tbinarticles_20081228(farticle, fpickarea, fbin)
select a.fgid farticle,
(select fpickarea from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickarea,
(select fpickbin from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickbin
from tarticle a;

for r in c loop
select count(*) into v_count from tbinarticles where fbin = r.fbin and farticle = r.farticle;


update fu_tbinarticles_20081228 set fqty = vQty,fqpc=vQpc where rowid = r.rowid;
end loop;
commit;
END;
这样又正确

[watson110]

因为如果A表和B表结构相同的话

insert into A表 select * from B表

是正确的格式

[killuakidd]

DECLARE
vQty integer;

cursor c is
select rowid, fpickarea, fbin, farticle from fu_tbinarticles_20081228;

BEGIN

DELETE FROM fu_tbinarticles_20081228;
COMMIT;

INSERT INTO fu_tbinarticles_20081228(farticle, fpickarea, fbin)
select a.fgid farticle,
(select fpickarea from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickarea,
(select fpickbin from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickbin
from tarticle a;

for r in c loop
select nvl(sum(fqty), 0) into vQty from tbinarticles where fbin = r.fbin and farticle = r.farticle
;
update fu_tbinarticles_20081228 set fqty = vQty where rowid = r.rowid;
end loop;
commit;
END;

这样写又可以

[killuakidd]

就是那个不行

[killuakidd]

为什么会这样的

[watson110]

因为你出现了空值的情况，本来没有那个值，你非得要into，当然报错了

[killuakidd]

DECLARE
vQpc integer;
cursor c is
select rowid, fpickarea, fbin, farticle from fu_tbinarticles_20081228;

BEGIN

DELETE FROM fu_tbinarticles_20081228;
COMMIT;

INSERT INTO fu_tbinarticles_20081228(farticle, fpickarea, fbin)
select a.fgid farticle,
(select fpickarea from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickarea,
(select fpickbin from tpickschemedtl where fcode = '-' and farticle = a.fgid and FITEMNO = 1) fpickbin
from tarticle a;

for r in c loop
select fqpc into vQpc from tbinarticles where fbin = r.fbin and farticle = r.farticle
group by fqpc;
update fu_tbinarticles_20081228 set fqpc=vQpc where rowid = r.rowid;
end loop;
commit;
END;

这样就报错了,果估是fqpc这个字段有问题,要如何解决呢