要求:如果两个日期之间有星期六星期天,要减去星期六星期天的个数。
解决方案 »
- oracle 访问FTP目录的文本,然后解析文件,更新到本地数据库中,怎么做
- 安装Oracle9的问题
- oracle8如何与ldap相连?拜求各位帮忙!!(50分)
- Oracle 10.2.0.1 如何安装 ProC ?????
- 我该怎么写这条语句?
- 怎么样把77.8.9转换成1977-08-09?
- 请教:想根据一个DATETIME时间字段得出在一天内的上午、下午、晚上的记录!!请大下帮忙啊!在线等!!!
- 有关Oracle8i的安装问题,菜鸟问题,答对马上给分
- 8i单机版数据如何导出?
- 请问这个触发器什么地方有错误?
- 有谁知道oracle的报价呀
- 100分,问个比较难的统计的问题,涉及行之间的数据比较
2 IS
3 i number := 1;
4 d date;
5 date1 date;
6 date2 date;
7 day1 varchar2(10) := 'friday';
8 day2 varchar2(10) := 'friday';
9 cnt number;
10
11 BEGIN
12 date1 := date11;
13 date2 := date22;
14 select date2 - date1 into cnt from dual;
15 select date2 - date1 into cnt from dual;
16 select to_char(date1,'day') into day1 from dual;
17 select to_char(date1,'day') into day2 from dual;
18
19 if instr(day1, 'sunday') > 0
20 then
21 cnt := cnt - 1;
22 date1 := date1 + 1;
23 end if;
24
25 if instr(day1, 'saturday') > 0
26 then
27 cnt := cnt - 2;
28 date1 := date1 + 2;
29 end if;
30
31 if day2 = 'sunday' then
32 cnt := cnt - 2;
33 date1 := date1 - 6;
34 elsif day1 = 'saturday' then
35 cnt := cnt - 1;
36 date1 := date1 - 5;
37 end if;
38
39 date1 := date1 + 7;
40
41 WHILE date2 - date1 > 0
42 LOOP
43 cnt := cnt - 2;
44 date1 := date1 + 7;
45 END LOOP;
46
47 DBMS_OUTPUT.put_line (cnt);
48 END;
49 /Procedure created.SQL>
SQL> exec eric_1(sysdate-30, sysdate);
22PL/SQL procedure successfully completed.SQL>
45PL/SQL procedure successfully completed.
CREATE OR REPLACE PROCEDURE eric_1 (date11 DATE, date22 DATE)
IS
date1 DATE;
date2 DATE;
day1 VARCHAR2 (10);
day2 VARCHAR2 (10);
cnt NUMBER;
BEGIN
date1 := date11;
date2 := date22; SELECT date2 - date1
INTO cnt
FROM DUAL; SELECT TO_CHAR (date1, 'day')
INTO day1
FROM DUAL; SELECT TO_CHAR (date2, 'day')
INTO day2
FROM DUAL; IF cnt > 7
THEN
IF INSTR (day1, 'sunday') > 0
THEN
cnt := cnt - 1;
date1 := date1 + 1;
ELSIF INSTR (day1, 'saturday') > 0
THEN
cnt := cnt - 2;
date1 := date1 + 2;
ELSIF INSTR (day1, 'friday') > 0
THEN
cnt := cnt - 2;
date1 := date1 + 3;
ELSIF INSTR (day1, 'thursday') > 0
THEN
cnt := cnt - 2;
date1 := date1 + 4;
ELSIF INSTR (day1, 'wednesday') > 0
THEN
cnt := cnt - 2;
date1 := date1 + 5;
ELSIF INSTR (day1, 'tuesday') > 0
THEN
cnt := cnt - 2;
date1 := date1 + 6;
END IF;
---------------------------------
IF INSTR (day2, 'sunday') > 0
THEN
cnt := cnt - 2;
date2 := date2 - 6;
ELSIF INSTR (day2, 'saturday') > 0
THEN
cnt := cnt - 1;
date2 := date2 - 5;
ELSIF INSTR (day2, 'friday') > 0
THEN
date2 := date2 - 4;
ELSIF INSTR (day2, 'thursday') > 0
THEN
date2 := date2 - 3;
ELSIF INSTR (day2, 'wednesday') > 0
THEN
date2 := date2 - 2;
ELSIF INSTR (day2, 'tuesday') > 0
THEN
date2 := date2 - 1;
END IF; date1 := date1 + 7; WHILE date2 - date1 >= 0
LOOP
cnt := cnt - 2;
date1 := date1 + 7;
END LOOP;
ELSE
IF INSTR (day1, 'saturday') > 0
THEN
cnt := cnt - 1;
date1 := date1 + 2;
ELSIF INSTR (day1, 'sunday') > 0
THEN
date1 := date1 + 1;
END IF; WHILE date2 - date1 >= 0
LOOP
SELECT TO_CHAR (date2, 'day')
INTO day2
FROM DUAL; IF INSTR (day2, 'saturday') > 0 OR INSTR (day2, 'sunday') > 0
THEN
cnt := cnt - 1;
END IF; date2 := date2 - 1;
END LOOP;
END IF; DBMS_OUTPUT.put_line (cnt);
END;
/Procedure created.SQL> COMMIT
2 /Commit complete.SQL> exec eric_1(to_date('2006-9-23', 'yyyy-mm-dd'),to_date('2006-9-25', 'yyyy-mm-dd'));
1PL/SQL procedure successfully completed.SQL> exec eric_1(to_date('2006-9-24', 'yyyy-mm-dd'),to_date('2006-9-25', 'yyyy-mm-dd'));
1PL/SQL procedure successfully completed.SQL> exec eric_1(to_date('2006-9-22', 'yyyy-mm-dd'),to_date('2006-9-24', 'yyyy-mm-dd'));
0PL/SQL procedure successfully completed.SQL> exec eric_1(to_date('2006-9-22', 'yyyy-mm-dd'),to_date('2006-9-23', 'yyyy-mm-dd'));
0PL/SQL procedure successfully completed.SQL> exec eric_1(to_date('2006-9-21', 'yyyy-mm-dd'),to_date('2006-9-23', 'yyyy-mm-dd'));
1PL/SQL procedure successfully completed.SQL> exec eric_1(to_date('2006-9-23', 'yyyy-mm-dd'),to_date('2006-9-24', 'yyyy-mm-dd'));
0PL/SQL procedure successfully completed.SQL> exec eric_1(to_date('2006-9-1', 'yyyy-mm-dd'),to_date('2006-11-1', 'yyyy-mm-dd'));
43PL/SQL procedure successfully completed.