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
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
同意二楼
但有一点vQty,vQpc 是不是应该加冒号改为这样 :vQty,:vQpc
PL/SQL: ORA-00911: 无效字符
ORA-06550: 第 21 行, 第 5 列:
PL/SQL: SQL Statement ignored报这个错误
--这样写吧
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);
end loop;
commit;
END;
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;
PL/SQL: ORA-00923: 未找到预期 FROM 关键字
ORA-06550: 第 19 行, 第 5 列:
PL/SQL: SQL Statement ignored以上的这样报错,究竟什么问题啊,晕
改成:
select nvl(sum(fqty), 0) ,fqpc into vQty, vQpc from tbinarticles where fbin = r.fbin and farticle = r.farticle;
ORA-06512: 在line 19
写法早知试过了,还是报错,想不通.....
vQty integer;
vQpc integer;
去掉其中一个试试,估计你的insert语句写的有问题
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;
就会报“未找到数据 ”的错误
--因为楼主根本就没有这条数据,插入的是空值
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
先确定是否有且仅有一条记录,然后再执行
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;
这样又正确
insert into A表 select * from B表
是正确的格式
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;这样写又可以
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这个字段有问题,要如何解决呢