PL/SQL代码
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
) RETURN INTEGER IS
result1 integer := 0;
last_day1 date;
first_day1 date;
daycounts integer := 0;
daycounts1 integer := 0;
v_day varchar2(10);
begin
--select to_date(ny,'yyyy-mm-dd') from dual;
--select to_date(20111205,'yyyy-mm-dd') from dual;
last_day1 := last_day(to_date(ny,'yyyy-mm-dd'));
first_day1 := add_months(last_day(to_date(ny,'yyyy-mm-dd')),-1) + 1;
daycounts1 := last_day1 - first_day1;
for daycounts in 0..daycounts1 loop
--dbms_output.put_line(daycounts);
--dbms_output.put_line(first_day1 + daycounts);
v_day := to_char(first_day1 + daycounts,'day');
dbms_output.put_line(v_day);
/**
case
when v_day = '星期一' then
result1++;
when v_day = '星期二' then
result1++;
when v_day = '星期三' then
result1++;
when v_day = '星期四' then
result1++;
when v_day = '星期五' then
result1++;
when v_day = '星期六' then
continue;
when v_day = '星期日' then
continue;
end case;
*/
if v_day = '星期一' then
result1++;
elsif v_day = '星期二' then
result1++;
elsif v_day = '星期三' then
result1++;
elsif v_day = '星期四' then
result1++;
elsif v_day = '星期五' then
result1++;
else
continue;
end if;
end loop;
return result1;
end;
/调试代码
select Get_WorkingDays('20111205') from dual;怎么这个PL/SQL代码一直编译出错啊?!
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
) RETURN INTEGER IS
result1 integer := 0;
last_day1 date;
first_day1 date;
daycounts integer := 0;
daycounts1 integer := 0;
v_day varchar2(10);
begin
--select to_date(ny,'yyyy-mm-dd') from dual;
--select to_date(20111205,'yyyy-mm-dd') from dual;
last_day1 := last_day(to_date(ny,'yyyy-mm-dd'));
first_day1 := add_months(last_day(to_date(ny,'yyyy-mm-dd')),-1) + 1;
daycounts1 := last_day1 - first_day1;
for daycounts in 0..daycounts1 loop
--dbms_output.put_line(daycounts);
--dbms_output.put_line(first_day1 + daycounts);
v_day := to_char(first_day1 + daycounts,'day');
dbms_output.put_line(v_day);
/**
case
when v_day = '星期一' then
result1++;
when v_day = '星期二' then
result1++;
when v_day = '星期三' then
result1++;
when v_day = '星期四' then
result1++;
when v_day = '星期五' then
result1++;
when v_day = '星期六' then
continue;
when v_day = '星期日' then
continue;
end case;
*/
if v_day = '星期一' then
result1++;
elsif v_day = '星期二' then
result1++;
elsif v_day = '星期三' then
result1++;
elsif v_day = '星期四' then
result1++;
elsif v_day = '星期五' then
result1++;
else
continue;
end if;
end loop;
return result1;
end;
/调试代码
select Get_WorkingDays('20111205') from dual;怎么这个PL/SQL代码一直编译出错啊?!
result1++;
改成
RESULT1 := RESULT1 + 1;
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
) RETURN INTEGER IS
*/
declare
ny varchar2(100) := '20111205';--写成函数时,该行做为输入参数,请删除该行
result1 integer := 0;
last_day1 date;
first_day1 date;
daycounts integer := 0;
daycounts1 integer := 0;
v_day varchar2(10);
begin
last_day1 := last_day(to_date(ny,'yyyy-mm-dd'));
first_day1 := add_months(last_day(to_date(ny,'yyyy-mm-dd')),-1) + 1;
daycounts1 := last_day1 - first_day1;
for daycounts in 0..daycounts1 loop
v_day := to_char(first_day1 + daycounts,'day');
dbms_output.put_line(v_day); if v_day = '星期一' then
result1 := result1 + 1;
elsif v_day = '星期二' then
result1 := result1 + 1;
elsif v_day = '星期三' then
result1 := result1 + 1;
elsif v_day = '星期四' then
result1 := result1 + 1;
elsif v_day = '星期五' then
result1 := result1 + 1;
else
continue;
end if;
end loop;
dbms_output.put_line('有效工作日统计:' || to_char(result1));
--return result1;
end;
但是我看了半天也看不出哪里出问题了!急等!
ny IN VARCHAR2
) RETURN INTEGER IS
result1 integer := 0;
last_day1 date;
first_day1 date;
daycounts integer := 0;
daycounts1 integer := 0;
v_day varchar2(10);
begin
--select to_date(ny,'yyyy-mm-dd') from dual;
--select to_date(20111205,'yyyy-mm-dd') from dual;
last_day1 := last_day(to_date(ny,'yyyy-mm-dd'));
first_day1 := add_months(last_day(to_date(ny,'yyyy-mm-dd')),-1) + 1;
daycounts1 := last_day1 - first_day1;
for daycounts in 0..daycounts1 loop
--dbms_output.put_line(daycounts);
--dbms_output.put_line(first_day1 + daycounts);
v_day := to_char(first_day1 + daycounts,'day');
dbms_output.put_line(v_day);
/**
case
when v_day = '星期一' then
result1++;
when v_day = '星期二' then
result1++;
when v_day = '星期三' then
result1++;
when v_day = '星期四' then
result1++;
when v_day = '星期五' then
result1++;
when v_day = '星期六' then
continue;
when v_day = '星期日' then
continue;
end case;
*/
-- 修改主要集中在这一段中
if v_day = '星期一' then
result1 := result1 + 1;
elsif v_day = '星期二' then
result1 := result1 + 1;
elsif v_day = '星期三' then
result1 := result1 + 1;
elsif v_day = '星期四' then
result1 := result1 + 1;
elsif v_day = '星期五' then
result1 := result1 + 1;
else
continue;
end if;
end loop;
return result1;
end;
解决了,把else
continue
给删除掉就没问题了
将跳出本次循环的语句 else
continue;
删除掉
else
continue
才能编译通过,谢谢大家的指点!!!!
CREATE OR REPLACE FUNCTION Get_WorkingDays(
ny IN VARCHAR2
) RETURN INTEGER IS
result1 integer := 0;
last_day1 date;
first_day1 date;
daycounts integer := 0;
daycounts1 integer := 0;
v_day varchar2(10);
begin
--select to_date(ny,'yyyy-mm-dd') from dual;
--select to_date(20111205,'yyyy-mm-dd') from dual;
last_day1 := last_day(to_date(ny,'yyyy-mm-dd'));
first_day1 := add_months(last_day(to_date(ny,'yyyy-mm-dd')),-1) + 1;
daycounts1 := last_day1 - first_day1;
for daycounts in 0..daycounts1 loop
--dbms_output.put_line(daycounts);
--dbms_output.put_line(first_day1 + daycounts);
v_day := to_char(first_day1 + daycounts,'day');
dbms_output.put_line(v_day);
if v_day = '星期一' then
result1:=+1;
elsif v_day = '星期二' then
result1:=+1;
elsif v_day = '星期三' then
result1:=+1;
elsif v_day = '星期四' then
result1:=+1;
elsif v_day = '星期五' then
result1:=+1;
end if;
end loop;
return result1;
end;
select Get_WorkingDays('20111205') from dual;
--------
兄弟你试试这个
result1 := result1 + 1; 2.continue; 的问题 oracle不支持的
可以考虑用 goto来代替