Sql server 2005遷移至Oracle系列之三：在Oracle中建立sql中的常見函式

prompt

prompt Creating function CHARINDEX
prompt ===========================
prompt

CREATE OR REPLACE FUNCTION charindex
(
    search VARCHAR2,
    src    VARCHAR2,
    pos    INT := 0
) RETURN INT AS
    tp_pos INT;
    tp_src VARCHAR2(2000);
    v_pos  INT;
BEGIN
    v_pos  := 0;
    tp_pos := pos;
    tp_src := src;
    IF pos <= 0 THEN
        RETURN(instr(src, search, 1, 1));
    ELSE
        tp_src := substr(src, pos);
        v_pos  := nvl(instr(tp_src, search, 1, 1), 0);
        IF v_pos = 0 THEN
            RETURN(0);
        ELSE
            RETURN(v_pos + tp_pos - 1);
        END IF;
    END IF;
END charindex;
/

prompt

prompt Creating function DATEADD
prompt =========================
prompt

CREATE OR REPLACE FUNCTION dateadd
(
    format  VARCHAR2 := 'd',
    n       INT := 0,
    dateval DATE := SYSDATE
) RETURN DATE AS
    fmt VARCHAR2(10);
BEGIN
    fmt := upper(format);
    IF fmt IN ('D', 'DD', 'DAY') THEN
        RETURN(dateval + n);
    ELSIF fmt IN ('M', 'MM', 'MONTH') THEN
        RETURN(add_months(dateval, n));
    ELSIF fmt IN ('Y', 'YY', 'YYY', 'YYYY', 'YEAR') THEN
        RETURN(add_months(dateval, n * 12.0));
    ELSIF fmt IN ('H', 'HH', 'HOUR') THEN
        RETURN(dateval + n / 24.0);
    ELSIF fmt IN ('N', 'MI', 'MINUTE') THEN
        RETURN(dateval + n / 24.0 / 60.0);
    ELSIF fmt IN ('S', 'SS', 'SECOND') THEN
        RETURN(dateval + n / 24.0 / 60.0 / 60.0);
    ELSE
        RETURN(NULL);
    END IF;
END;
/

prompt

prompt Creating function DATEDIFF
prompt ==========================
prompt

CREATE OR REPLACE FUNCTION datediff
(
    format    VARCHAR2 := 'd',
    datebegin DATE := SYSDATE,
    dateend   DATE := SYSDATE
) RETURN INT AS
    fmt VARCHAR2(10);
BEGIN
    fmt := upper(format);
    IF fmt IN ('D', 'DD', 'DAY')
    THEN
        RETURN(datebegin - dateend);
    ELSIF fmt IN ('M', 'MM', 'MONTH')
    THEN
        RETURN((datebegin - dateend) / 30);
    ELSIF fmt IN ('Y', 'YY', 'YYY', 'YYYY', 'YEAR')
    THEN
        RETURN((datebegin - dateend) / 365);
    ELSIF fmt IN ('H', 'HH', 'HOUR')
    THEN
        RETURN((datebegin - dateend) * 24);
    ELSIF fmt IN ('N', 'MI', 'MINUTE')
    THEN
        RETURN((datebegin - dateend) * 24.0 * 60.0);
    ELSIF fmt IN ('S', 'SS', 'SECOND')
    THEN
        RETURN((datebegin - dateend) * 24.0 * 60.0 * 60.0);
    ELSE
        RETURN(NULL);
    END IF;
END;
/

prompt

prompt Creating function DATEPART
prompt ==========================
prompt

CREATE OR REPLACE FUNCTION datepart
(
    format  VARCHAR2 := 'd',
    dateval DATE := SYSDATE
) RETURN VARCHAR2 AS
    fmt VARCHAR2(10);
BEGIN
    fmt := upper(format);
    IF fmt IN ('D', 'DD', 'DAY')
    THEN
        RETURN(to_char(dateval, 'DD'));
    ELSIF fmt IN ('M', 'MM', 'MONTH')
    THEN
        RETURN(to_char(dateval, 'MM'));
    ELSIF fmt IN ('Y', 'YY', 'YYY', 'YYYY', 'YEAR')
    THEN
        RETURN(to_char(dateval, 'YYYY'));
    ELSIF fmt IN ('H', 'HH', 'HOUR')
    THEN
        RETURN(to_char(dateval, 'HH'));
    ELSIF fmt IN ('N', 'MI', 'MINUTE')
    THEN
        RETURN(to_char(dateval, 'MI'));
    ELSIF fmt IN ('S', 'SS', 'SECOND')
    THEN
        RETURN(to_char(dateval, 'SS'));
    ELSE
        RETURN(NULL);
    END IF;
END;
/

prompt

prompt Creating function DAY
prompt =====================
prompt

create or replace function day(v_date date := sysdate) return int as
begin
    return(
           to_number(TO_CHAR(v_date, 'dd'))
           );
end;
/

prompt

prompt Creating function LEFT
prompt ======================
prompt

CREATE OR REPLACE FUNCTION left(src VARCHAR2,n INT) RETURN VARCHAR2 AS
BEGIN
    RETURN(SUBSTR(src,     0,     n));
END left;
/

prompt

prompt Creating function MONTH
prompt =======================
prompt

create or replace function month(v_date date := sysdate) return int as
begin
    return(
           to_number(TO_CHAR(v_date, 'mm'))
           );
end;
/

prompt

prompt Creating function REPLICATE
prompt ===========================
prompt

CREATE OR REPLACE FUNCTION replicate
(
    v_str VARCHAR2,
    n     INT
)

 RETURN VARCHAR2 AS
    v_dest VARCHAR(2000);
BEGIN
    IF n <= 0 THEN
        RETURN('');
    ELSE
   
        FOR i IN 1 .. n LOOP
       
            v_dest := v_dest || v_str;
        END LOOP;
    END IF;

    RETURN(v_dest);
END;
/

prompt

prompt Creating function RIGHT
prompt =======================
prompt

CREATE OR REPLACE FUNCTION right(src VARCHAR2,n INT) RETURN VARCHAR2 AS
BEGIN
    RETURN(SUBSTR(src,   -n));
END right;
/

prompt

prompt Creating function STR
prompt =====================
prompt

CREATE OR REPLACE FUNCTION str
(
    numberval NUMBER,
    len       INT := 10,
    decima    INT := 0
) RETURN VARCHAR2 AS
    v_decima    INT;
    v_numberval VARCHAR(300);
    v_pos       INT;
BEGIN
    v_decima    := abs(decima);
    v_numberval := to_char(numberval);
    v_pos       := instr(v_numberval, '.');
    IF v_decima > 16
    THEN
        v_decima := 16;
    END IF;
    IF v_pos = 0
    THEN
        v_pos := length(v_numberval);
        IF abs(len) < v_pos
        THEN
            RETURN(lpad('*', len, '*'));
        ELSE
            RETURN(substr(v_numberval, 0, v_pos));
        END IF;
    ELSE
        IF abs(len) < v_pos - 1
        THEN
            RETURN(lpad('*', len, '*'));
        ELSE
            IF least(v_decima, abs(len) - v_pos) > 0
            THEN
                v_numberval := to_char(round(numberval, least(v_decima, abs(len) - v_pos)));
                RETURN(v_numberval);
            ELSE
                RETURN(rtrim(substr(v_numberval, 0, least((v_pos + v_decima), abs(len))), '.'));
            END IF;
        END IF;
    END IF;
END str;
/

prompt

prompt Creating function STUFF
prompt =======================
prompt

CREATE OR REPLACE FUNCTION stuff
(
    v_str       VARCHAR2,
    v_start     INT,
    v_len       INT,
    v_repacestr VARCHAR2
) RETURN VARCHAR2 AS
    tp_str VARCHAR2(4000);
BEGIN
    tp_str := v_str;
    IF v_start <= 0
       OR v_len < 0 THEN
        RETURN('');
    ELSIF v_start > length(v_str) THEN
        RETURN('');
    ELSE
        tp_str := substr(v_str, 0, greatest(1, v_start) - 1) || v_repacestr || substr(v_str, greatest(1, v_start) + v_len);
        RETURN(tp_str);
    END IF;
END;
/

prompt

prompt Creating function SUBSTRING
prompt ===========================
prompt

CREATE OR REPLACE FUNCTION substring
(
    str VARCHAR2,
    pos INT := 0,
    len INT := 0
) RETURN VARCHAR2 AS
BEGIN
    IF len <= 0 THEN
        RETURN('');
    ELSIF (pos + len) <= 0 THEN
        RETURN('');
    ELSE
        RETURN(substr(str, greatest(pos, 0), least(len, len + pos)));
    END IF;
END substring;
/

prompt

prompt Creating function YEAR
prompt ======================
prompt

create or replace function year(v_date date := sysdate) return int as
begin
    return(
           to_number(TO_CHAR(v_date, 'yyyy'))
           );
end;
/

原文地址：http://www.cnblogs.com/jinzhenshui/articles/1360216.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-598136/，如需轉載，請註明出處，否則將追究法律責任。