Sqlserver2005迁移至Oracle系列之三：在Oracle中创建sql中的常见函数

核心提示：promptprompt Creating function CHARINDEXprompt ===promptCREATE OR REPLACE FUNCTION charindex( search VARCHAR2, src VARCHAR2, pos INT := 0) RETURN INT AS tp_pos

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;
/