需要怎么写sql来算出有多少天呢?select ????? from daul?
解决方案 »
- 这个错误什么意思:数据库当前处于 OPEN 状态。但是由于存在以上错误, EM 无法连接到此数据库
- oracle 移植问题
- 高手帮我写个oracle的出发器 在线等~~~
- 数据库设计问题?急……
- 关于table()函数的问题
- ORA-00474 SMON process terminated with error 错误谁能知道原因???
- 高分求救!
- Oracle怎样把varchar2型转成number型
- 关于动态SQL的问题(immediate execute)
- Oracle 中 number类型,用java连接后,返回的数据类型变成BigBigDecimal,怎么救
- 请教,急急!!!!!
- 查询回车符号
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>
SQL> exec eric_1(sysdate-30, sysdate);
22PL/SQL procedure successfully completed.SQL> exec eric_1(to_date('2006-9-1', 'yyyy-mm-dd'),to_date('2006-11-1', 'yyyy-mm-dd'));
45PL/SQL procedure successfully completed.这个是计算两个日期相隔的天数。