本文共 2311 字，大约阅读时间需要 7 分钟。

Oracle?????PL/SQL????????

????????????Oracle??????????????????????????????????????????????????

????????

Oracle???????????????????SYSDATE?LAST_DAY?ADD_MONTHS?MONTHS_BETWEEN?NEXT_DAY?????????????????????????????????????????????????????????

1. SYSDATE

SYSDATE??????????????????????????????

SELECT to_char(sysdate, 'yyyy-mm-dd') FROM dual; -- ???????????

2. LAST_DAY

LAST_DAY????????????????????????????????

SELECT to_char(last_day(sysdate), 'yyyy-mm-dd') FROM dual; -- ??????????

3. ADD_MONTHS

ADD_MONTHS?????????????????????????

SELECT add_months(sysdate, 2) FROM dual; -- ?????????

4. MONTHS_BETWEEN

MONTHS_BETWEEN???????????????????????????????????????????

SELECT months_between(to_date('2005-11-12', 'yyyy-mm-dd'), sysdate) FROM dual;

??PL/SQL????????

?PL/SQL?????????????????????????????????????????

1. ???????????

?????????????????????????????????

SELECT to_char(last_day(add_months(sysdate, 1), 'yyyy-mm-dd') FROM dual;

2. ?????????

??????????????????????????????????

SELECT to_char(trunc(sysdate, 'mm') + 1, 'yyyy-mm-dd') FROM dual;

3. ?????????

NEXT_DAY?????????????????????

SELECT next_day(to_date('20050620', 'yyyy-mm-dd'), 1) FROM dual;

?????????????????

??????????????????????????????????

CREATE OR REPLACE FUNCTION Get_WorkingDays(ny IN VARCHAR2) RETURN INTEGER ISBEGIN  Result := 0;  myts := to_char(last_day(to_date(ny, 'yyyymm')), 'dd');  LOOP    rq := TO_date(ny||substr(100 + ROWNUM, 2, 2), 'yyyymmdd');    scts := rq - to_date('2001-12-30', 'yyyy-mm-dd');    IF MOD(MOD(scts, 7) + 7, 7) NOT IN (0, 6) THEN      Result := Result + 1;    END IF;    djt := djt + 1;    EXIT WHEN djt > myts;  END LOOP;  RETURN Result;END Get_WorkingDays;

?????????????

??????????????????????TO_CHAR???????????????????????????????????

ALTER SESSION SET time_zone = '-11:00';SELECT sessiontimezone, current_timestamp FROM dual;

1. ???????

????LOCALTIMESTAMP??????????????????????

SELECT localtimestamp FROM dual;

2. ??????

TO_CHAR????????????????????

SELECT to_char(current_timestamp, 'yyyy-mm-dd hh24:mi:ss') FROM dual;

???????????

???Oracle?????????????????????

1. ????????????

??????????????????Oracle?????????????

2. ????????

????????????????????????????

3. ??????

??????????????????????

SELECT round((sysdate - sysdate - 3.111) * 1440) FROM dual;

????

??????????Oracle???????????????????????????????????????????