菜鸟刚进公司拖了大家的后腿，包体存储过程表达式类型错误和statement ignored

[jidu01]

create or replace package pkg_haharm
is

-------创建pl/sql记录
TYPE REC_QASSET is record(
MIS_CODE varchar2(20),
INV_NAME varchar2(80),
MAT_CD varchar2(20),
PROD_NAME varchar2(40),
STATUS_CD varchar2(20),
FINANCE_CD VARCHAR2(20),
BUCkET_ID number(15),
EXPIRT_DT DATE,
PRICE NUMBER(12,2),
SERIAL_NUM VARCHAR2(80),
SERIAL_COUNT NUMBER(10)
);



/*查询资产，按照号段合并，如果返回的Bucket_id,过期日期、价格等一致，那么顺序的序列号将会合并成一个号段
@param ResultTab 返回的字符串数组，多个字段用逗号分隔，返回字段有 MIS_CODE,INV_NAME,PROD_NAME,STATUS_CD,EXPIRT_DT,PRICE,SERIAL_NUM,SERIAL_COUNT,MAT_CD
@param iInvId 仓库Id，不能为空
@param iProdId 产品Id,为空表示所有产品
@param psts 产品状态Id,为空表示所有状态
@param fsts 财务状态，为空表示所有状态
*/

procedure QASSET_BY_INV_PROD_STS(ResultTab out VARRAYSTR, invId number, prodId number, psts number, fsts number);


end pkg_haharm;


---------------------------------------------------------------------------------------------

create or replace package body pkg_haharm
is

function TO_NUM_NULL ( c VARCHAR2) return number
is
begin
begin
return to_number(c);
exception
when others then return null;
end;
end;


------合号连续的序列号
procedure QASSET_BY_INV_PROD_STS(ResultTab out VARRAYSTR,invId number, prodId number, psts number, fsts number)
is

numpart_len constant integer := 6;

fixPart varchar2(30);
numPart varchar2(30);
lastNum number(10);
firstNum number(10);
newNum number(10);
i integer;

cursor CUR_QASSET is
select MIS_CODE, i.name INV_NAME, p.MAT_CD, p.PROD_NAME, cts.STATUS_CD, bucket_id, a.expirt_dt, a.price, a.serial_num,1 serial_count
from RM_ASSET a, RM_BUCKET B, RM_INVENTORY i, RM_PRODUCT p,RM_CLASS_STAT cts, rm_finance_stat fts
where a.bucket_id = b.row_id and b.inv_id = i.row_id and p.row_id = b.prod_id and cts.row_id = b.class_stat_id and i.row_id = invId and (p.row_id = prodId or prodId = -1) and (cts.row_id = psts or psts = -1) and (fts.row_id = fsts or fsts = -1) order by bucket_id, a.expirt_dt, price, a.serial_num;

firstRec REC_QASSET;
lastRec REC_QASSET;
newRec REC_QASSET;

begin

ResultTab := VARRAYSTR();

i := 0;
for newRec in CUR_QASSET loop
numPart := substr(newRec.serial_num, length(newRec.serial_num)-numpart_len+1,numpart_len);
newNum := TO_NUM_NULL(numPart);
if firstRec.BUCKET_ID is null then
------第一次初始化一个新号段
-------------------------------------------------------------------------------------
firstRec:=newRec;
lastRec:=newRec;
-------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
--上面虚线中间的地方报的错
lastNum:= newNum;
firstNum := newNum;
fixpart:=substr(newRec.serial_num,0,length(newRec.serial_num)-numpart_len);
elsif newRec.bucket_id<>firstRec.bucket_id
or newRec.expirt_dt<>firstRec.expirt_dt
or newNum is null
or fixpart<>substr(newRec.serial_num,0,length(newRec.serial_num)-numpart_len)
or newNum<>lastNum+1 then
--------------结束旧号段
ResultTab.extend;
i := i+1;
if firstRec.serial_num<>lastRec.serial_num then
firstRec.serial_num := trim(firstRec.serial_num)||'-'||trim(lastRec.serial_num);
firstRec.serial_count := lastNum - firstNum+1;
end if;
ResultTab(i) := firstRec.MIS_CODE||','||firstRec.INV_NAME||','||firstRec.PROD_NAME||','||firstRec.STATUS_CD||','||to_char(firstRec.expirt_dt,'yyyy-mm-dd')||','||firstRec.price||','||firstRec.serial_num||','||firstRec.serial_count||','||firstRec.MAT_CD||','||firstRec.FINANCE_CD;
-----------------开始一个新号段
firstRec := newRec;
lastRec := newRec;
lastNum := newNum;
firstNum := newNum;
fixPart := substr(newRec.serial_num,0,length(newRec.serial_num)-numpart_len);
else
---------在号段中
lastRec := newRec;
lastNum := newNum;
end if;
end loop;
if firstRec.bucket_id is not null then
-------结束旧号段
ResultTab.extend;
i := i+1;
if(firstRec.serial_num<>lastRec.serial_num) then
firstRec.serial_num := trim(firstRec.serial_num)||'-'||trim(lastRec.serial_num);
firstRec.serial_count := lastNum-firstNum+1;
end if;
ResultTab(i) := firstRec.MIS_CODE||','||firstRec.INV_NAME||','||firstRec.PROD_NAME||','||firstRec.STATUS_CD||','||to_char(firstRec.expirt_dt,'yyyy-mm-dd')||','||firstRec.price||','||firstRec.serial_num||','||firstRec.serial_count||','||firstRec.MAT_CD||','||firstRec.FINANCE_CD;
end if;
end QASSET_BY_INV_PROD_STS;

end pkg_haharm;



其中varraystr是我定义的变成数组。。。
请问这事神马原因呢？自己搞了2天了，还是没发现错误，刚接触pl/sql编程这块，是在有点是不从心

[tangren]

记录声明与游标中的字段个数不对应，个类类型都要对应

TYPE REC_QASSET IS RECORD(
MIS_CODE VARCHAR2(20),
INV_NAME VARCHAR2(80),
MAT_CD VARCHAR2(20),
PROD_NAME VARCHAR2(40),
STATUS_CD VARCHAR2(20),
FINANCE_CD VARCHAR2(20),
BUCkET_ID NUMBER(15),
EXPIRT_DT DATE,
PRICE NUMBER(12, 2),
SERIAL_NUM VARCHAR2(80),
SERIAL_COUNT NUMBER(10));
-----------------------------------------------------
SELECT MIS_CODE,
i.name INV_NAME,
p.MAT_CD,
p.PROD_NAME,
cts.STATUS_CD,
bucket_id,
a.expirt_dt,
a.price,
a.serial_num,
1 serial_count
FROM RM_ASSET a,
RM_BUCKET B,
RM_INVENTORY i,
RM_PRODUCT p,
RM_CLASS_STAT cts,
rm_finance_stat fts
WHERE a.bucket_id = b.row_id
AND b.inv_id = i.row_id
AND p.row_id = b.prod_id
AND cts.row_id = b.class_stat_id
AND i.row_id = invId
AND (p.row_id = prodId OR prodId = -1)
AND (cts.row_id = psts OR psts = -1)
AND (fts.row_id = fsts OR fsts = -1)
ORDER BY bucket_id, a.expirt_dt, price, a.serial_num;

[jidu01]

哦，上午在地铁上想了下感觉是字段没有匹配的问题。。。非常感谢1楼的前辈

[xumeng929]

灌水..........