下面是我的存储过程create or replace procedure PRO_WL_I_EndToNOAHARK(BeginTime in VARCHAR2,EndTime in VARCHAR2,SupType in VARCHAR2,cEntityCode in VARCHAR2)
as
e_year VARCHAR2(6);
e_month VARCHAR2(4);
k_pk_corp VARCHAR2(20);
k_pk_glorgbook VARCHAR2(100);
k_pk_glbook VARCHAR2(100);begin
e_year := SUBSTR(EndTime,1,4);
e_month := SUBSTR(EndTime,6,2);
select corp.pk_corp,gb.pk_glorgbook,gb.pk_glbook into k_pk_corp,k_pk_glorgbook,k_pk_glbook
from bd_corp corp,bd_glorg gl, bd_glorgbook gb
where corp.unitcode =cEntityCode and NVL (gl.dr, 0) = 0 AND NVL (gb.dr, 0) = 0
AND corp.pk_corp = gl.pk_entityorg AND gl.pk_glorg = gb.pk_glorg and gb.type = 0;
delete from TBL_Trans_inter_data_ict where Entity=cEntityCode;
if SupType='0000' then
insert into TBL_Trans_inter_data_ict
select 1 as datatype,'' as vouchid,'' as vouno ,lrzxbm as profit,ywfbbm as bustype,
'' as sap3,'' as TR_FenPei,'' as TR_ShortText,'' as TR_LongText, case when balanorient=1 then '借' else '贷' end as Direct,
'0' as SAPFJZ,'' as VouDate,''
as IsReturned ,'' as BookDate ,ksbm as SupCode,corp.unitcode as Entity,'' expensesAcc,code as AcctCode,
currtypecode as TR_CUR,'' as TR_AMT,
'' as TR_LAMT, amount_yb as TR_UnOffAMT,amount_bb as TR_LUnOffAMT,
'' as TR_RATE,'' as AgingDate,'' as OffGroup,'' as OffDate,'' as IsDelete,'' as isInvalid,'' as IsOffed, '' as VouUser,'NC' as FinType,'' as UpdateTime
from (select substr(b.subjcode, 1, 6) as code,
tz.valuecode as ksbm,
tz.valuename as ksmc,
sum(debitamount) - sum(creditamount)
as amount_yb,
sum(localdebitamount) - sum(localcreditamount)
as amount_bb,
tz1.valuecode as ywfbbm,
tz1.valuename as ywfbmc,
tz2.valuecode as lrzxbm,
tz2.valuename as lrzxmc,
b.balanorient,
curr.currtypecode,
a.pk_corp
from (SELECT debitamount, creditamount, localdebitamount, localcreditamount, assid, pk_accsubj,pk_currtype,pk_corp
from gl_balance
where period = '00'
and year = e_year
and nvl(dr, 0) = 0
and pk_corp =k_pk_corp
and pk_glorgbook=k_pk_glorgbook
union all
select
b.debitamount,
b.creditamount,
b.localdebitamount,
b.localcreditamount,
b.assid,
pk_accsubj,
b.pk_currtype,a.pk_corp
from gl_voucher a, GL_DETAIL b
where a.PK_VOUCHER = b.PK_VOUCHER
and nvl(a.dr, 0) = 0
and nvl(b.dr, 0) = 0
and a.period > '00'
and a.pk_corp = b.pk_corp
and a.pk_corp = k_pk_corp
and a.year = e_year
and a.
period <= e_month
and a.pk_glorgbook=k_pk_glorgbook
and a.PREPAREDDATE <= EndTime
and (a.errmessage is null and (a.discardflag <> 'Y') or
a.errmessage is not null or (a.discardflag = 'Y'))
and a.voucherkind <> 2
and a.voucherkind <> 255) a,
bd_accsubj b,
bd_currtype curr,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '00010000000000000073'
and nvl(dr, 0) = 0) tz,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000XWS'
and nvl(dr, 0) = 0) tz1, (SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000Y8E'
and nvl(dr, 0) = 0) tz2
where tz.freevalueid = a.assid
and tz1.freevalueid = a.assid
and b.pk_accsubj = a.pk_accsubj
and substr(b.subjcode, 1, 4) in ('1131', '2121', '1151')
and curr.pk_currtype=a.pk_currtype
group by tz1.valuecode,
tz1.valuename,
tz.valuecode,
tz2.valuename,
tz2.valuecode,
tz.valuename,
b.balanorient,
curr.currtypecode,
substr(b.subjcode, 1, 6),
a.pk_corp) m,bd_corp corp
where m.amount_bb <> 0 and corp.pk_corp=m.pk_corp and substr(m.ksbm,1,4)<>'9999';
END;
运行通过,编译也没问题,进入测试阶段
begin
-- Call the procedure
pro_wl_i_endtonoahark('2009-01-18' => :begintime,
'2009-01-18' => :endtime,
'0000' => :suptype,
'241500' => :centitycode);
end;这样测提示PLS-00306 参数或类型错误
如果写成begin
-- Call the procedure
pro_wl_i_endtonoahark(begintime => :2009-01-18,
endtime => :2009-01-18,
suptype => :0000,
centitycode => :241500);
end;则提示非法的变量名/编号
求高手解决问题
as
e_year VARCHAR2(6);
e_month VARCHAR2(4);
k_pk_corp VARCHAR2(20);
k_pk_glorgbook VARCHAR2(100);
k_pk_glbook VARCHAR2(100);begin
e_year := SUBSTR(EndTime,1,4);
e_month := SUBSTR(EndTime,6,2);
select corp.pk_corp,gb.pk_glorgbook,gb.pk_glbook into k_pk_corp,k_pk_glorgbook,k_pk_glbook
from bd_corp corp,bd_glorg gl, bd_glorgbook gb
where corp.unitcode =cEntityCode and NVL (gl.dr, 0) = 0 AND NVL (gb.dr, 0) = 0
AND corp.pk_corp = gl.pk_entityorg AND gl.pk_glorg = gb.pk_glorg and gb.type = 0;
delete from TBL_Trans_inter_data_ict where Entity=cEntityCode;
if SupType='0000' then
insert into TBL_Trans_inter_data_ict
select 1 as datatype,'' as vouchid,'' as vouno ,lrzxbm as profit,ywfbbm as bustype,
'' as sap3,'' as TR_FenPei,'' as TR_ShortText,'' as TR_LongText, case when balanorient=1 then '借' else '贷' end as Direct,
'0' as SAPFJZ,'' as VouDate,''
as IsReturned ,'' as BookDate ,ksbm as SupCode,corp.unitcode as Entity,'' expensesAcc,code as AcctCode,
currtypecode as TR_CUR,'' as TR_AMT,
'' as TR_LAMT, amount_yb as TR_UnOffAMT,amount_bb as TR_LUnOffAMT,
'' as TR_RATE,'' as AgingDate,'' as OffGroup,'' as OffDate,'' as IsDelete,'' as isInvalid,'' as IsOffed, '' as VouUser,'NC' as FinType,'' as UpdateTime
from (select substr(b.subjcode, 1, 6) as code,
tz.valuecode as ksbm,
tz.valuename as ksmc,
sum(debitamount) - sum(creditamount)
as amount_yb,
sum(localdebitamount) - sum(localcreditamount)
as amount_bb,
tz1.valuecode as ywfbbm,
tz1.valuename as ywfbmc,
tz2.valuecode as lrzxbm,
tz2.valuename as lrzxmc,
b.balanorient,
curr.currtypecode,
a.pk_corp
from (SELECT debitamount, creditamount, localdebitamount, localcreditamount, assid, pk_accsubj,pk_currtype,pk_corp
from gl_balance
where period = '00'
and year = e_year
and nvl(dr, 0) = 0
and pk_corp =k_pk_corp
and pk_glorgbook=k_pk_glorgbook
union all
select
b.debitamount,
b.creditamount,
b.localdebitamount,
b.localcreditamount,
b.assid,
pk_accsubj,
b.pk_currtype,a.pk_corp
from gl_voucher a, GL_DETAIL b
where a.PK_VOUCHER = b.PK_VOUCHER
and nvl(a.dr, 0) = 0
and nvl(b.dr, 0) = 0
and a.period > '00'
and a.pk_corp = b.pk_corp
and a.pk_corp = k_pk_corp
and a.year = e_year
and a.
period <= e_month
and a.pk_glorgbook=k_pk_glorgbook
and a.PREPAREDDATE <= EndTime
and (a.errmessage is null and (a.discardflag <> 'Y') or
a.errmessage is not null or (a.discardflag = 'Y'))
and a.voucherkind <> 2
and a.voucherkind <> 255) a,
bd_accsubj b,
bd_currtype curr,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '00010000000000000073'
and nvl(dr, 0) = 0) tz,
(SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000XWS'
and nvl(dr, 0) = 0) tz1, (SELECT valuecode,
valuename,
freevalueid,
checktype,
checkvalue
FROM gl_freevalue
WHERE checktype = '0001V510000000000Y8E'
and nvl(dr, 0) = 0) tz2
where tz.freevalueid = a.assid
and tz1.freevalueid = a.assid
and b.pk_accsubj = a.pk_accsubj
and substr(b.subjcode, 1, 4) in ('1131', '2121', '1151')
and curr.pk_currtype=a.pk_currtype
group by tz1.valuecode,
tz1.valuename,
tz.valuecode,
tz2.valuename,
tz2.valuecode,
tz.valuename,
b.balanorient,
curr.currtypecode,
substr(b.subjcode, 1, 6),
a.pk_corp) m,bd_corp corp
where m.amount_bb <> 0 and corp.pk_corp=m.pk_corp and substr(m.ksbm,1,4)<>'9999';
END;
运行通过,编译也没问题,进入测试阶段
begin
-- Call the procedure
pro_wl_i_endtonoahark('2009-01-18' => :begintime,
'2009-01-18' => :endtime,
'0000' => :suptype,
'241500' => :centitycode);
end;这样测提示PLS-00306 参数或类型错误
如果写成begin
-- Call the procedure
pro_wl_i_endtonoahark(begintime => :2009-01-18,
endtime => :2009-01-18,
suptype => :0000,
centitycode => :241500);
end;则提示非法的变量名/编号
求高手解决问题
或者 开个sql窗口
begin
pro_wl_i_endtonoahark('2009-01-18','2009-01-18','0000','241500');
end;
begin
pro_wl_i_endtonoahark(to_char('2009-01-18'),to_char('2009-01-18'),'0000','241500');
end;
--这样做,交换你的形参和实参顺序!
begin
pro_wl_i_endtonoahark(
begintime => '2009-01-18',
endtime => '2009-01-18',
suptype => '0000',
centitycode => '241500');
end;
pro_wl_i_endtonoahark(to_char('2009-01-18'),to_char('2009-01-18'),'0000','241500');
end;
这样也报错吗?
create or replace procedure PRO_WL_I_EndToNOAHARK
(BeginTime in VARCHAR2 default ' ',
EndTime in VARCHAR2 default ' ',
SupType in VARCHAR2,default ' '
cEntityCode in VARCHAR2 default ' ')
begin
pro_wl_i_endtonoahark('2009-01-18','2009-01-18','0000','241500');
end;在plsql下测试并不准确 建议去oracle 自带的sqlplus下运行你的过程
以及运行的时候得出具体在哪行错误
begin
-- Call the procedure
pro_wl_i_endtonoahark(:begintime => ‘2009-01-18’,
:endtime => ’2009-01-18‘,
:suptype => ‘0000’,
:centitycode => ‘241500’);
end;再次感谢大家!