CREATE OR REPLACE PACKAGE set_data
AS
PROCEDURE set_jc_lsb(pdlx IN NUMBER);
type jc_info is record
(
bill jc_产成品出库.bill%type,
N1 jc_产成品出库详细.sl%type,
N2 jc_产成品出库详细.sl%type,
N3 jc_产成品出库详细.sl%type,
N4 jc_产成品出库详细.sl%type,
N5 jc_产成品出库详细.sl%type,
cksj jc_产成品出库.cksj%type,
thdw jw_供应商.mc%type
);END set_data; CREATE OR REPLACE PACKAGE BODY set_data AS
jc_data jc_info;
PROCEDURE set_jc_lsb(pdlx IN NUMBER) is
BEGIN
--先清空数据
delete from JC_临时表;
if pdlx = 1 then
select jc_ccpck.bill bill,
case jc_ccp.gg when '优级' then jc_ccpckxx.sl else 0 end as n1,
case jc_ccp.gg when '一级' then jc_ccpckxx.sl else 0 end as n2,
case jc_ccp.gg when '合格' then jc_ccpckxx.sl else 0 end as n3,
case jc_ccp.gg when '次品' then jc_ccpckxx.sl else 0 end as n4,
case jc_ccpck.lb when '礼品' then jc_ccpckxx.sl else 0 end as n5,
jc_ccpck.cksj cksj,jw_gys.mc thdw into jc_data from jc_产成品出库详细 jc_ccpckxx,jc_产成品出库 jc_ccpck,jc_产成品 jc_ccp,jw_供应商 jw_gys where jc_ccpck.bill=jc_ccpckxx.bill and jc_ccpck.khbh = jw_gys.bh and jc_ccpckxx.bh=jc_ccp.bh and jc_ccp.mc = '绵白糖'; Insert into JC_临时表(ckbh,N1,N2,N3,N4,N5,CKSJ,THDW) Values(jc_data.bill,jc_data.N1,jc_data.N2,jc_data.N3,jc_data.N4,jc_data.N5,jc_data.cksj,jc_data.thdw); end if;
END set_jc_lsb; --存储过程set_jc_lsb定义结束
end set_data;
JC_临时表,表结构
create table JC_临时表
(
ckbh varchar2(20),
scys varchar2(2),
scts varchar2(5),
bc varchar2(2),
gc varchar2(10),
N1 NUMBER(18,3),
N2 NUMBER(18,3),
N3 NUMBER(18,3),
N4 NUMBER(18,3),
N5 NUMBER(18,3),
N6 NUMBER(18,3),
cksj date,
thdw varchar2(50)
)
我执行过程,错误是:
ERROR 位于第 1 行:
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在"ZHXT.SET_DATE", line 9
ORA-06512: 在line 1用什么方法才能更好的解决阿?
AS
PROCEDURE set_jc_lsb(pdlx IN NUMBER);
type jc_info is record
(
bill jc_产成品出库.bill%type,
N1 jc_产成品出库详细.sl%type,
N2 jc_产成品出库详细.sl%type,
N3 jc_产成品出库详细.sl%type,
N4 jc_产成品出库详细.sl%type,
N5 jc_产成品出库详细.sl%type,
cksj jc_产成品出库.cksj%type,
thdw jw_供应商.mc%type
);END set_data; CREATE OR REPLACE PACKAGE BODY set_data AS
jc_data jc_info;
PROCEDURE set_jc_lsb(pdlx IN NUMBER) is
BEGIN
--先清空数据
delete from JC_临时表;
if pdlx = 1 then
select jc_ccpck.bill bill,
case jc_ccp.gg when '优级' then jc_ccpckxx.sl else 0 end as n1,
case jc_ccp.gg when '一级' then jc_ccpckxx.sl else 0 end as n2,
case jc_ccp.gg when '合格' then jc_ccpckxx.sl else 0 end as n3,
case jc_ccp.gg when '次品' then jc_ccpckxx.sl else 0 end as n4,
case jc_ccpck.lb when '礼品' then jc_ccpckxx.sl else 0 end as n5,
jc_ccpck.cksj cksj,jw_gys.mc thdw into jc_data from jc_产成品出库详细 jc_ccpckxx,jc_产成品出库 jc_ccpck,jc_产成品 jc_ccp,jw_供应商 jw_gys where jc_ccpck.bill=jc_ccpckxx.bill and jc_ccpck.khbh = jw_gys.bh and jc_ccpckxx.bh=jc_ccp.bh and jc_ccp.mc = '绵白糖'; Insert into JC_临时表(ckbh,N1,N2,N3,N4,N5,CKSJ,THDW) Values(jc_data.bill,jc_data.N1,jc_data.N2,jc_data.N3,jc_data.N4,jc_data.N5,jc_data.cksj,jc_data.thdw); end if;
END set_jc_lsb; --存储过程set_jc_lsb定义结束
end set_data;
JC_临时表,表结构
create table JC_临时表
(
ckbh varchar2(20),
scys varchar2(2),
scts varchar2(5),
bc varchar2(2),
gc varchar2(10),
N1 NUMBER(18,3),
N2 NUMBER(18,3),
N3 NUMBER(18,3),
N4 NUMBER(18,3),
N5 NUMBER(18,3),
N6 NUMBER(18,3),
cksj date,
thdw varchar2(50)
)
我执行过程,错误是:
ERROR 位于第 1 行:
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在"ZHXT.SET_DATE", line 9
ORA-06512: 在line 1用什么方法才能更好的解决阿?
jc_data jc_info;
Cursor into_data is
select jc_ccpck.bill bill,
case jc_ccp.gg when '优级' then jc_ccpckxx.sl else 0 end as n1,
case jc_ccp.gg when '一级' then jc_ccpckxx.sl else 0 end as n2,
case jc_ccp.gg when '合格' then jc_ccpckxx.sl else 0 end as n3,
case jc_ccp.gg when '次品' then jc_ccpckxx.sl else 0 end as n4,
case jc_ccpck.lb when '礼品' then jc_ccpckxx.sl else 0 end as n5,
jc_ccpck.cksj cksj,jw_gys.mc thdw into jc_data from jc_产成品出库详细 jc_ccpckxx,jc_产成品出库 jc_ccpck,jc_产成品 jc_ccp,jw_供应商 jw_gys where jc_ccpck.bill=jc_ccpckxx.bill and jc_ccpck.khbh = jw_gys.bh and jc_ccpckxx.bh=jc_ccp.bh and jc_ccp.mc = '绵白糖';
PROCEDURE set_jc_lsb(pdlx IN NUMBER) is
BEGIN
--先清空数据
delete from JC_临时表;
if pdlx = 1 then
open into_data;
LOOP
FETCH into_data
INTO jc_data;
EXIT WHEN into_data%NOTFOUND;
BEGIN
Insert into JC_临时表(ckbh,N1,N2,N3,N4,N5,CKSJ,THDW) Values(jc_data.bill,jc_data.N1,jc_data.N2,jc_data.N3,jc_data.N4,jc_data.N5,jc_data.cksj,jc_data.thdw);
END;
END LOOP;
CLOSE into_data;
end if;
END set_jc_lsb; --存储过程set_jc_lsb定义结束
end set_date;我现在把包体改成这样了,可以执行了,但是,表里还是没有数据,那个大虾看看给看看阿!