存储过程,请参考:CREATE OR REPLACE FUNCTION APPS.SGDF_WORK_DAY( START_DATE IN DATE ,END_DATE IN DATE ) RETURN NUMBER IS DAY_COUNT NUMBER :=0 ; BEGIN DECLARE V_DAy NUMBER :=0; v_start_date date ; begin v_start_date:=start_date ; while v_start_date <= end_date loop SELECT TO_CHAR(v_start_date,'D') INTO V_DAY FROM DUAL; IF (V_DAY!='1') and (v_day!='7') THENDAY_COUNT := DAY_COUNT +1 ; END IF ; v_start_date :=v_start_date + 1 ; end loop;RETURN(DAY_COUNT);EXCEPTION WHEN NO_DATA_FOUND THEN RETURN '0'; end; END ;
给个列子 DECLARE v_date char(8); v_int int; v_sum int; v_sql varchar2(200); begin v_date := '20120201'; v_sum := 0; while (to_date('20120207','yyyymmdd')-to_date(v_date,'yyyymmdd')>0) loop v_sql :='select to_char(to_date('''||v_date||''',''yyyymmdd''),''D'') from dual'; execute immediate v_sql into v_int; if v_int <>7 and v_int <>1 then v_sum := v_sum+1; end if; v_date := to_char(to_date(v_date,'yyyymmdd')+1,'yyyymmdd'); end loop; dbms_output.put_line(v_sum); end;
DECLARE V_DAy NUMBER :=0;
v_start_date date ;
begin
v_start_date:=start_date ;
while v_start_date <= end_date loop
SELECT TO_CHAR(v_start_date,'D') INTO V_DAY FROM DUAL;
IF (V_DAY!='1') and (v_day!='7') THENDAY_COUNT := DAY_COUNT +1 ;
END IF ;
v_start_date :=v_start_date + 1 ;
end loop;RETURN(DAY_COUNT);EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '0';
end;
END ;
DECLARE
v_date char(8);
v_int int;
v_sum int;
v_sql varchar2(200);
begin
v_date := '20120201';
v_sum := 0;
while (to_date('20120207','yyyymmdd')-to_date(v_date,'yyyymmdd')>0)
loop
v_sql :='select to_char(to_date('''||v_date||''',''yyyymmdd''),''D'') from dual';
execute immediate v_sql into v_int;
if v_int <>7 and v_int <>1 then
v_sum := v_sum+1;
end if;
v_date := to_char(to_date(v_date,'yyyymmdd')+1,'yyyymmdd');
end loop;
dbms_output.put_line(v_sum);
end;