大家帮忙看下下面的存储过程有什么错误,怎么改正,谢谢了!!create or replace procedure th_getHour(eid in number,
sDate in date,
eDate in date,
oHour out number,
pNum number,
wNum number,
mDate date,
mNum number,
wStr char
)
as
begin
select count(*) into pNum from th_holiday;/*get public holiday's number*/
eid :=pNum;
loop
select to_date(sDate,'yyyy-mm-dd')+1 mDate from dual/*every time the date add 1*/
if mDate=eDate then/*提示有错误*/
exit;
else
select to_char(to_date(mDate, 'yyyy-mm-dd'), 'dy') wStr from dual;/*get week string*/
if wStr='sat' or wStr='sun' then
select count(*) mNum from th_workingday where cdate=to_date(mDate,'yyyy-mm-dd')/* */
if(mNum==0) then/* if not working day*/
eid++;
end if;
end if;
end if;
end loop;
oHour :=eid*8.5;
end;
sDate in date,
eDate in date,
oHour out number,
pNum number,
wNum number,
mDate date,
mNum number,
wStr char
)
as
begin
select count(*) into pNum from th_holiday;/*get public holiday's number*/
eid :=pNum;
loop
select to_date(sDate,'yyyy-mm-dd')+1 mDate from dual/*every time the date add 1*/
if mDate=eDate then/*提示有错误*/
exit;
else
select to_char(to_date(mDate, 'yyyy-mm-dd'), 'dy') wStr from dual;/*get week string*/
if wStr='sat' or wStr='sun' then
select count(*) mNum from th_workingday where cdate=to_date(mDate,'yyyy-mm-dd')/* */
if(mNum==0) then/* if not working day*/
eid++;
end if;
end if;
end if;
end loop;
oHour :=eid*8.5;
end;
这是怎么回事啊,谢谢了!create or replace procedure th_getHour(eid in number,
sDate in date,
eDate in date,
oHour out number,
pNum number,
wNum number,
mDate date,
mNum number,
wStr char
)
as
begin
select count(*) into pNum from th_holiday where cdate>=to_date(sDate,'yyyy-mm-dd') and cdate<=to_date(eDate,'yyyy-mm-dd');/*get public holiday's number*/
oHour :=pNum;
loop
select to_date(sDate,'yyyy-mm-dd')+1 mDate from dual;/*every time the date add 1*/
if to_date(mDate,'yyyy-mm-dd')>to_date(eDate,'yyyy-mm-dd') then
exit;
else
select to_char(to_date(mDate, 'yyyy-mm-dd'), 'dy') wStr from dual;/*get week string*/
if wStr='sat' or wStr='sun' then
select count(*) mNum from th_workingday where cdate=to_date(mDate,'yyyy-mm-dd');/* */
if mNum=0 then /* if not working day*/
oHour :=oHour+1;
end if;
end if;
end if;
end loop;
oHour :=oHour*8.5;
end;
sDate in date,
eDate in date,
oHour out number,
pNum number,
wNum number,
mDate date,
mNum number,
wStr char
)
as
begin
........................
...
end th_getHour; -- 这里加th_getHour
PLS-00403: expression 'PNUM' cannot be used as an INTO-target of a SELECT/FETCH statement
你可以设几个本地变量,如:create or replace procedure th_getHour(eid in number,
sDate in date,
eDate in date,
oHour out number,
pNum number,
wNum number,
mDate date,
mNum number,
wStr char
)
asl_num NUMBER;
l_date DATE;
l_str VARCHAR2(20); begin
select count(*) into l_num from th_holiday where cdate>=to_date(sDate,'yyyy-mm-dd') and cdate<=to_date(eDate,'yyyy-mm-dd');/*get public holiday's number*/
oHour :=pNum;
loop
select to_date(sDate,'yyyy-mm-dd')+1 into l_date from dual;/*every time the date add 1*/
if to_date(mDate,'yyyy-mm-dd')>to_date(eDate,'yyyy-mm-dd') then
exit;
else
select to_char(to_date(mDate, 'yyyy-mm-dd'), 'dy') into l_str from dual;/*get week string*/
if wStr='sat' or wStr='sun' then
select count(*) into l_num from th_workingday where cdate=to_date(mDate,'yyyy-mm-dd');/* */
if mNum=0 then /* if not working day*/
oHour :=oHour+1;
end if;
end if;
end if;
end loop;
oHour :=oHour*8.5;
end;
wNum number,
mDate date,
mNum number,
wStr char
--------
这几个是定义的变量,还是过程的参数?如果是参数,IN类型的参数只读,所以不INTO!