sqlserver_utilities
create or replace
PACKAGE sqlserver_utilities AS
identity NUMBER(10);
trancount NUMBER(10):=0;
var_number NUMBER(10):=0;
FUNCTION CONVERT_(P_DATATYPE IN VARCHAR2, P_EXPR IN VARCHAR2, P_STYLE IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
--FUNC YEAR truyen kieu VARCHAR2
FUNCTION YEAR_(P_DATE_STR IN VARCHAR2) RETURN NUMBER;
--FUNC YEAR truyen kieu DATE
FUNCTION YEAR_(P_DATE_STR IN DATE) RETURN NUMBER;
---------------------------------------------------------
FUNCTION stuff(p_expr VARCHAR2, p_startIdx NUMBER, p_len NUMBER, p_replace_expr VARCHAR2) RETURN VARCHAR2;
PROCEDURE incrementTrancount;
FUNCTION dateadd(p_interval IN VARCHAR2, p_interval_val IN NUMBER, p_date_str IN VARCHAR2) RETURN DATE;
FUNCTION dateadd(p_interval IN VARCHAR2, p_interval_val IN NUMBER, p_date_str IN date) RETURN DATE;
FUNCTION isdate(p_expr IN VARCHAR2) RETURN NUMBER;
FUNCTION stats_date(p_table IN VARCHAR2, p_index IN VARCHAR2) RETURN DATE;
FUNCTION rand(p_seed NUMBER DEFAULT NULL) RETURN NUMBER;
FUNCTION to_base(p_dec NUMBER, p_base NUMBER) RETURN VARCHAR2;
FUNCTION patindex(p_pattern IN VARCHAR2, p_expr IN VARCHAR2) RETURN NUMBER;
FUNCTION DATEDIFF(P_DATEPART VARCHAR2, P_START_DATE_STR VARCHAR2, P_END_DATE_STR VARCHAR2) RETURN NUMBER;
FUNCTION DATEDIFF(P_DATEPART VARCHAR2, P_START_DATE_STR DATE, P_END_DATE_STR DATE) RETURN NUMBER;--Them
--FUNCTION day_(p_date_str IN VARCHAR2) RETURN NUMBER;
--FUNC DAY truyen kieu VARCHAR2
FUNCTION day_(p_date_str IN VARCHAR2) RETURN NUMBER;
--FUNC DAY truyen kieu DATE
FUNCTION DAY_(P_DATE_STR IN DATE) RETURN NUMBER;
--------------------------------------------------
FUNCTION ident_incr(p_sequence IN VARCHAR2) RETURN NUMBER;
FUNCTION isnumeric(p_expr IN VARCHAR2) RETURN NUMBER;
FUNCTION hex(p_num VARCHAR2) RETURN VARCHAR2;
PROCEDURE decrementTrancount;
FUNCTION difference(p_expr1 IN VARCHAR2, p_expr2 IN VARCHAR2) RETURN NUMBER;
FUNCTION datepart(p_part_expr IN VARCHAR2, p_date_str IN VARCHAR2) RETURN NUMBER;
FUNCTION datepart(p_part_expr IN VARCHAR2, p_date in date) RETURN NUMBER;
FUNCTION radians(p_degree IN NUMBER) RETURN NUMBER;
FUNCTION reverse_(p_expr IN VARCHAR2) RETURN VARCHAR2;
FUNCTION parsename(p_object_name IN VARCHAR2, p_object_piece IN NUMBER) RETURN VARCHAR2;
FUNCTION ROUND_(P_EXPR NUMBER, P_LEN NUMBER, P_FUNCTION NUMBER DEFAULT 0) RETURN NUMBER;
--FUNCTION month_(p_date_str IN VARCHAR2) RETURN NUMBER;
--FUNC MONTH truyen kieu VARCHAR2
FUNCTION MONTH_(P_DATE_STR IN VARCHAR2) RETURN NUMBER;
--FUNC MONTH truyen kieu DATE
FUNCTION MONTH_(P_DATE_STR IN DATE)RETURN NUMBER;
-----------------------------------------------------------
PROCEDURE commit_transaction;
FUNCTION pi RETURN NUMBER;
PROCEDURE resetTrancount;
FUNCTION oct(p_num VARCHAR2) RETURN VARCHAR2;
FUNCTION str(p_expr IN NUMBER, p_len IN NUMBER DEFAULT 10, p_scale IN NUMBER DEFAULT 0) RETURN VARCHAR2;
FUNCTION degrees(p_angle_radians IN NUMBER) RETURN NUMBER;
FUNCTION datename(p_part_expr IN VARCHAR2, p_date_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION ident_seed(p_sequence IN VARCHAR2) RETURN NUMBER;
FUNCTION quotename(p_str IN VARCHAR2, p_delimiters IN VARCHAR2 DEFAULT '[]') RETURN VARCHAR2;
FUNCTION str_to_date(p_date_expr IN VARCHAR2) RETURN DATE;
FUNCTION fetch_status(p_cursorfound IN BOOLEAN) RETURN NUMBER;
END sqlserver_utilities;
------------------------------------------+++--------------------------------------------------
create or replace
PACKAGE BODY sqlserver_utilities AS
FUNCTION str_to_date(p_date_expr IN VARCHAR2)
RETURN DATE
IS
temp_val NUMBER;
temp_exp VARCHAR2(50);
format_str VARCHAR2(50) := NULL;
BEGIN
IF p_date_expr IS NULL THEN
RETURN NULL;
END IF;
temp_exp := TRIM(p_date_expr);
-- only for 10g
IF NOT DBMS_DB_VERSION.VER_LE_9_2 THEN
IF REGEXP_INSTR(temp_exp,
'^\{d[[:space:]]*''([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})''\}$') = 1 THEN -- ISO861 format
-- e.g. {d '2004-05-23' }
temp_exp := REGEXP_REPLACE(temp_exp,
'^\{d[[:space:]]*''([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})''\}$',
'\1-\2-\3');
format_str := 'YYYY-MM-DD';
ELSIF REGEXP_INSTR(temp_exp,
'^\{t[[:space:]]*''([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?''\}$',
1, 1, 0, 'i') = 1 THEN -- ISO861 format
-- e.g. { t '14:25:10.487' }
temp_exp := REGEXP_REPLACE(temp_exp,
'^\{t[[:space:]]*''([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?''\}$',
TO_CHAR(SYSDATE, 'YYYY-MM-DD') || ' \1:\2:\3');
format_str := 'YYYY-MM-DD HH24:MI:SS';
ELSIF REGEXP_INSTR(temp_exp,
'^\{ts[[:space:]]*''([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})[[:space:]]*' ||
'([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?''\}$',
1, 1, 0, 'i') = 1 THEN -- ISO861 format
-- e.g. { ts '2005-05-23 14:25:10'}
temp_exp := REGEXP_REPLACE(temp_exp,
'^\{ts[[:space:]]*''([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})[[:space:]]*' ||
'([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?''\}$',
'\1-\2-\3 \4:\5:\6');
format_str := 'YYYY-MM-DD HH24:MI:SS';
ELSIF REGEXP_INSTR(temp_exp,
'^([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})T([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?$') = 1 THEN -- ISO861 format
-- e.g. 2004-05-23T14:25:10.487
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})T([[:digit:]]{2}):([[:digit:]]{2}):([[:digit:]]{2})(\.[[:digit:]]{3})?$',
'\1-\2-\3 \4:\5:\6');
format_str := 'YYYY-MM-DD HH24:MI:SS';
ELSIF REGEXP_INSTR(temp_exp,
'^([[:digit:]]{1,2})[[:space:]]*(am|pm)$',
1, 1, 0, 'i') = 1 THEN -- time format
-- e.g. 4PM or 4 pm
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{1,2})[[:space:]]*(am|pm)$',
TO_CHAR(SYSDATE, 'YYYY-MM-DD') || ' \1\2', 1, 1, 'i');
format_str := 'YYYY-MM-DD HH12' || UPPER(REGEXP_SUBSTR(temp_exp, '(am|pm)$', 1, 1, 'i'));
ELSIF REGEXP_INSTR(temp_exp,
'^([[:digit:]]{1,2}):([[:digit:]]{2})(:[[:digit:]]{2})?([\.:][[:digit:]]{1,3})?[[:space:]]*(am|pm)?$',
1, 1, 0, 'i') = 1 THEN -- time format
-- e.g. 14:30 or 14:30:20.9 or 4:30:10 PM
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{1,2})(:[[:digit:]]{2})(:[[:digit:]]{2})?([\.:][[:digit:]]{1,3})?[[:space:]]*(am|pm)?$',
TO_CHAR(SYSDATE, 'YYYY-MM-DD') || '\1\2\3\5', 1, 1, 'i');
format_str := 'YYYY-MM-DD HH24:MI:SS';
IF REGEXP_INSTR(temp_exp, '(am|pm)$', 1, 1, 0, 'i') <> 0 THEN
format_str := REPLACE(format_str, 'HH24', 'HH12') || UPPER(REGEXP_SUBSTR(temp_exp, '(am|pm)$', 1, 1, 'i'));
END IF;
ELSIF REGEXP_INSTR(temp_exp, '^([[:digit:]]{4})$') = 1 THEN -- unseparated string format
-- 4 digits is interpreted as year, century must be specified
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{4})$',
'(01) 01 \1');
format_str := '(DD) MM YYYY';
ELSIF REGEXP_INSTR(temp_exp, '^[[:digit:]]{6,8}$') = 1 THEN -- unseparated string format
IF LENGTH(temp_exp) = 6 THEN
format_str := 'YYYYMMDD';
-- default two-digit year cutoff is 2050 i.e.
-- if the two digit year is greater than 50 the century prefix is interpreted as 19 otherwise it is 20.
IF TO_NUMBER(SUBSTR(temp_exp, 1, 2)) > 50 THEN
temp_exp := '19' || temp_exp;
ELSE
temp_exp := '20' || temp_exp;
END IF;
ELSE
format_str := 'YYYYMMDD';
END IF;
ELSIF REGEXP_INSTR(temp_exp, '[-/\\.]') = 0 THEN -- alphanumeric date format
IF REGEXP_INSTR(temp_exp,
'^([[:alpha:]]+)[[:space:]]*,?[[:space:]]*([[:digit:]]{4})$') = 1 THEN
-- e.g. APRIL, 1996 or APR 1996
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:alpha:]]+)[[:space:]]*,?[[:space:]]*([[:digit:]]{4})$',
'(01) \1 \2');
ELSIF REGEXP_INSTR(temp_exp,
'^([[:alpha:]]+)[[:space:]]+([[:digit:]]{1,2})?,?[[:space:]]+([[:digit:]]{2,4})$') = 1 THEN
-- e.g. APRIL 15, 1996 or APR 15 96
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:alpha:]]+)[[:space:]]+([[:digit:]]{1,2})?,?[[:space:]]+([[:digit:]]{2,4})$',
'(\2) \1 \3');
ELSIF REGEXP_INSTR(temp_exp,
'^([[:alpha:]]+)[[:space:]]+([[:digit:]]{2,4})([[:space:]]+)?([[:digit:]]{1,2})?$') = 1 THEN
-- e.g. APRIL 1996 or APRIL 1996 15
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:alpha:]]+)[[:space:]]+([[:digit:]]{2,4})([[:space:]]+)?([[:digit:]]{1,2})?$',
'(\4) \1 \2');
ELSIF REGEXP_INSTR(temp_exp,
'^([[:digit:]]{1,2})?[[[:punct:]]]+([[:alpha:]]+),?[[:punct:]]+([[:digit:]]{2,4})$') = 1 THEN
-- e.g. 15 APR, 1996 or 15 APR 96 or APRIL 1996
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{1,2})?[[:punct:]]+([[:alpha:]]+),?[[:punct:]]+([[:digit:]]{2,4})$',
'(\1) \2 \3');
temp_exp := REPLACE(temp_exp, ',', '');
ELSIF REGEXP_INSTR(temp_exp,
'^([[:digit:]]{1,2})?[[:space:]]+([[:digit:]]{2,4})[[:space:]]+([[:alpha:]]+)$') = 1 THEN
-- e.g. 15 1996 APRIL or 1996 APR
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{1,2})?[[:space:]]+([[:digit:]]{2,4})[[:space:]]+([[:alpha:]]+)$',
'(\1) \3 \2');
ELSIF REGEXP_INSTR(temp_exp,
'^([[:digit:]]{2,4})[[:space:]]+([[:alpha:]]+)([[:space:]]+)?([[:digit:]]{1,2})?$') = 1 THEN
-- e.g. 1996 APRIL 15 or 1996 APR
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{2,4})[[:space:]]+([[:alpha:]]+)([[:space:]]+)?([[:digit:]]{1,2})?$',
'(\4) \2 \1');
ELSIF REGEXP_INSTR(temp_exp,
'^([[:digit:]]{2,4})[[:space:]]+([[:digit:]]{1,2})?[[:space:]]+([[:alpha:]]+)$') = 1 THEN
-- e.g. 1996 15 APRIL or 1996 APR
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{2,4})[[:space:]]+([[:digit:]]{1,2})?[[:space:]]+([[:alpha:]]+)$',
'(\2) \3 \1');
END IF;
temp_exp := REPLACE(temp_exp, '()', '(1)');
IF TO_NUMBER(REGEXP_SUBSTR(temp_exp, '[[:digit:]]{2,4}$')) < 100 THEN
IF TO_NUMBER(REGEXP_SUBSTR(temp_exp, '[[:digit:]]{2,4}$')) > 50 THEN
temp_exp := REGEXP_REPLACE(temp_exp, '([[:digit:]]{2,4})$', '19' || '\1');
ELSE
temp_exp := REGEXP_REPLACE(temp_exp, '([[:digit:]]{2,4})$', '20' || '\1');
END IF;
END IF;
format_str := '(DD) MON YYYY';
ELSIF REGEXP_INSTR(temp_exp, '[-/\\.]') <> 0 THEN -- numeric date format
-- require the setting for SET FORMAT to determine the interpretation of the numeric date format,
-- default is mdy
IF REGEXP_INSTR(temp_exp,
-- e.g. 4/15/1996 or 15/4/1996 or 4/96/15
'^([[:digit:]]{1,2})[-/\.]([[:digit:]]{1,2})[-/\.]([[:digit:]]{2,4})$') = 1 THEN
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{1,2})[-/\.]([[:digit:]]{1,2})[-/\.]([[:digit:]]{2,4})$',
'\1/\2/\3');
ELSIF REGEXP_INSTR(temp_exp,
'^([[:digit:]]{1,2})[-/\.]([[:digit:]]{2,4})[-/\.]([[:digit:]]{1,2})$') = 1 THEN
-- e.g. 15/96/4
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{1,2})[-/\.]([[:digit:]]{2,4})[-/\.]([[:digit:]]{1,2})$',
'\1/\3/\2');
ELSIF REGEXP_INSTR(temp_exp,
'^([[:digit:]]{2,4})[-/\.]([[:digit:]]{1,2})[-/\.]([[:digit:]]{1,2})$') = 1 THEN
-- e.g. 1996/4/15 or 1996/15/4
temp_exp := REGEXP_REPLACE(temp_exp,
'^([[:digit:]]{2,4})[-/\.]([[:digit:]]{1,2})[-/\.]([[:digit:]]{1,2})$',
'\2/\3/\1');
END IF;
-- first component
temp_val := TO_NUMBER(SUBSTR(temp_exp, 1, INSTR(temp_exp, '/') - 1));
IF temp_val > 31 AND temp_val < 100 THEN
format_str := 'YYYY/';
IF temp_val > 50 THEN
temp_exp := '19' || temp_exp;
ELSE
temp_exp := '20' || temp_exp;
END IF;
ELSIF temp_val > 12 THEN
format_str := 'DD/';
ELSE
format_str := 'MM/';
END IF;
-- second component
temp_val := TO_NUMBER(SUBSTR(temp_exp, INSTR(temp_exp, '/') + 1, INSTR(temp_exp, '/', 1, 2) - INSTR(temp_exp, '/') - 1));
IF temp_val > 31 AND temp_val < 100 THEN
format_str := format_str || 'YYYY/';
IF temp_val > 50 THEN
temp_exp := REGEXP_REPLACE(temp_exp, '/([[:digit:]]{2,4})/', '/19' || '\1/');
ELSE
temp_exp := REGEXP_REPLACE(temp_exp, '/([[:digit:]]{2,4})/', '/20' || '\1/');
END IF;
ELSIF temp_val > 12 THEN
format_str := format_str || 'DD/';
ELSE
IF INSTR(format_str, 'MM') > 0 THEN
format_str := format_str || 'DD';
ELSE
format_str := format_str || 'MM/';
END IF;
END IF;
IF INSTR(format_str, 'MM') = 0 THEN
format_str := format_str || 'MM';
ELSIF INSTR(format_str, 'DD') = 0 THEN
format_str := format_str || 'DD';
ELSE
IF TO_NUMBER(REGEXP_SUBSTR(temp_exp, '[[:digit:]]{2,4}$')) < 100 THEN
IF TO_NUMBER(REGEXP_SUBSTR(temp_exp, '[[:digit:]]{2,4}$')) > 50 THEN
temp_exp := REGEXP_REPLACE(temp_exp, '([[:digit:]]{2,4})$', '19' || '\1');
ELSE
temp_exp := REGEXP_REPLACE(temp_exp, '([[:digit:]]{2,4})$', '20' || '\1');
END IF;
END IF;
format_str := format_str || '/YYYY';
END IF;
END IF;
END IF;
IF format_str IS NOT NULL THEN
RETURN TO_DATE(temp_exp, format_str);
ELSE
RETURN TO_DATE(temp_exp, 'DD-MON-YYYY HH24:MI:SS');
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END STR_TO_DATE;
FUNCTION convert_(p_dataType IN VARCHAR2, p_expr IN VARCHAR2, p_style IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
v_ret_value VARCHAR2(50);
v_format VARCHAR2(30);
v_year_format VARCHAR2(5) := 'YY';
v_format_type NUMBER;
v_numeric_dataType BOOLEAN := TRUE;
v_is_valid_date BINARY_INTEGER := 0;
BEGIN
IF INSTR(UPPER(p_dataType), 'DATE') <> 0 OR INSTR(UPPER(p_dataType), 'CHAR') <> 0 OR
INSTR(UPPER(p_dataType), 'CLOB') <> 0 THEN
v_numeric_dataType := FALSE;
END IF;
IF NOT v_numeric_dataType THEN
SELECT NVL2(TO_DATE(p_expr), 1, 0) INTO v_is_valid_date FROM DUAL;
END IF;
IF (str_to_date(p_expr) IS NOT NULL OR v_is_valid_date != 0 ) THEN
IF p_style IS NULL THEN
v_ret_value := TO_NCHAR(p_expr);
ELSE -- convert date to character data
v_format_type := TO_NUMBER(p_style);
IF v_format_type > 100 THEN
v_year_format := 'YYYY';
END IF;
v_format := CASE
WHEN v_format_type = 1 OR v_format_type = 101 THEN 'MM/DD/' || v_year_format
WHEN v_format_type = 2 OR v_format_type = 102 THEN v_year_format || '.MM.DD'
WHEN v_format_type = 3 OR v_format_type = 103 THEN 'DD/MM/' || v_year_format
WHEN v_format_type = 4 OR v_format_type = 104 THEN 'DD.MM.' || v_year_format
WHEN v_format_type = 5 OR v_format_type = 105 THEN 'DD-MM-' || v_year_format
WHEN v_format_type = 6 OR v_format_type = 106 THEN 'DD MM ' || v_year_format
WHEN v_format_type = 7 OR v_format_type = 107 THEN 'MON DD, ' || v_year_format
WHEN v_format_type = 8 OR v_format_type = 108 THEN 'HH12:MI:SS'
WHEN v_format_type = 9 OR v_format_type = 109 THEN 'MON DD YYYY HH12:MI:SS.FF3AM'
WHEN v_format_type = 10 OR v_format_type = 110 THEN 'MM-DD-' || v_year_format
WHEN v_format_type = 11 OR v_format_type = 111 THEN v_year_format || '/MM/DD'
WHEN v_format_type = 12 OR v_format_type = 112 THEN v_year_format || 'MMDD'
WHEN v_format_type = 13 OR v_format_type = 113 THEN 'DD MON YYYY HH12:MI:SS.FF3'
WHEN v_format_type = 14 OR v_format_type = 114 THEN 'HH24:MI:SS.FF3'
WHEN v_format_type = 20 OR v_format_type = 120 THEN 'YYYY-MM-DD HH24:MI:SS'
WHEN v_format_type = 21 OR v_format_type = 121 THEN 'YYYY-MM-DD HH24:MI:SS.FF3'
WHEN v_format_type = 126 THEN 'YYYY-MM-DD HH12:MI:SS.FF3'
WHEN v_format_type = 127 THEN 'YYYY-MM-DD HH12:MI:SS.FF3'
WHEN v_format_type = 130 THEN 'DD MON YYYY HH12:MI:SS:FF3AM'
WHEN v_format_type = 131 THEN 'DD/MM/YY HH12:MI:SS:FF3AM'
END;
v_ret_value := CASE
WHEN v_format_type = 9 OR v_format_type = 109 OR
v_format_type = 13 OR v_format_type = 113 OR
v_format_type = 14 OR v_format_type = 114 OR
v_format_type = 20 OR v_format_type = 120 OR
v_format_type = 21 OR v_format_type = 121 OR
v_format_type = 126 OR v_format_type = 127 OR
v_format_type = 130 OR v_format_type = 131 THEN
CASE UPPER(p_dataType)
WHEN 'DATE' THEN TO_CHAR(TO_TIMESTAMP(p_expr, v_format))
ELSE TO_CHAR(TO_TIMESTAMP(p_expr), v_format)
END
ELSE
CASE UPPER(p_dataType)
WHEN 'DATE' THEN TO_CHAR(TO_DATE(p_expr, v_format))
ELSE TO_CHAR(TO_DATE(p_expr), v_format)
END
END;
END IF;
ELSE
-- convert money or smallmoney to character data
IF SUBSTR(p_expr, 1, 1) = '$' THEN
v_ret_value := CASE TO_NUMBER(NVL(p_style, 1))
WHEN 1 THEN TO_CHAR(SUBSTR(p_expr, 2), '999999999999999990.00')
WHEN 2 THEN TO_CHAR(SUBSTR(p_expr, 2), '999,999,999,999,999,990.00')
WHEN 3 THEN TO_CHAR(SUBSTR(p_expr, 2), '999999999999999990.0000')
END;
ELSE -- convert numeric data to character data
v_ret_value := TO_CHAR(p_expr);
END IF;
END IF;
RETURN v_ret_value;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END CONVERT_;
--HAM YEAR truyen vao kieu VARCHAR2
FUNCTION year_(p_date_str IN VARCHAR2)
RETURN NUMBER
IS
v_date DATE;
BEGIN
v_date := str_to_date(p_date_str);
IF V_DATE IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER(TO_CHAR(v_date, 'YYYY'));
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END YEAR_;
--END YEAR_CHAR
--Ham YEAR truyen vao kieu DATE
FUNCTION year_(p_date_str IN DATE)
RETURN NUMBER
IS
v_date DATE;
BEGIN
v_date := str_to_date(TO_CHAR(p_date_str,'DD MON YYYY'));
IF V_DATE IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER(TO_CHAR(v_date, 'YYYY'));
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END YEAR_;
--END YEAR_DATE
FUNCTION stuff(p_expr VARCHAR2, p_startIdx NUMBER, p_len NUMBER, p_replace_expr VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN REPLACE(p_expr, SUBSTR(p_expr, p_startIdx, p_len), p_replace_expr);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END stuff;
PROCEDURE incrementTrancount
IS
BEGIN
trancount := trancount + 1;
END incrementTrancount;
--Ham DATEADD truyen vao ngay thang la kieu VARCHAR2
FUNCTION dateadd(p_interval IN VARCHAR2, p_interval_val IN NUMBER, p_date_str IN VARCHAR2)
RETURN DATE
IS
v_ucase_interval VARCHAR2(10);
v_date DATE;
BEGIN
v_date := str_to_date(p_date_str);
v_ucase_interval := UPPER(p_interval);
IF v_ucase_interval IN ('YEAR', 'YY', 'YYYY')
THEN
RETURN ADD_MONTHS(v_date, p_interval_val * 12);
ELSIF v_ucase_interval IN ('QUARTER', 'QQ', 'Q')
THEN
RETURN ADD_MONTHS(v_date, p_interval_val * 3);
ELSIF v_ucase_interval IN ('MONTH', 'MM', 'M')
THEN
RETURN ADD_MONTHS(v_date, p_interval_val);
ElSIF v_ucase_interval IN ('DAYOFYEAR', 'DY', 'Y', 'DAY', 'DD', 'D', 'WEEKDAY', 'DW', 'W')
THEN
RETURN v_date + p_interval_val;
ElSIF v_ucase_interval IN ('WEEK', 'WK', 'WW')
THEN
RETURN v_date + (p_interval_val * 7);
ElSIF v_ucase_interval IN ('HOUR', 'HH')
THEN
RETURN v_date + (p_interval_val / 24);
ElSIF v_ucase_interval IN ('MINUTE', 'MI', 'N')
THEN
RETURN v_date + (p_interval_val / 24 / 60);
ElSIF v_ucase_interval IN ('SECOND', 'SS', 'S')
THEN
RETURN v_date + (p_interval_val / 24 / 60 / 60);
ElSIF v_ucase_interval IN ('MILLISECOND', 'MS')
THEN
RETURN v_date + (p_interval_val / 24 / 60 / 60 / 1000);
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END DATEADD;
--Ham DATEADD truyen vao ngay thang la kieu DATE
FUNCTION dateadd(p_interval IN VARCHAR2, p_interval_val IN NUMBER, p_date_str IN DATE)
RETURN DATE
IS
v_ucase_interval VARCHAR2(10);
v_date DATE;
BEGIN
v_date := str_to_date(TO_CHAR(p_date_str,'DD MON YYYY'));
v_ucase_interval := UPPER(p_interval);
IF v_ucase_interval IN ('YEAR', 'YY', 'YYYY')
THEN
RETURN ADD_MONTHS(v_date, p_interval_val * 12);
ELSIF v_ucase_interval IN ('QUARTER', 'QQ', 'Q')
THEN
RETURN ADD_MONTHS(v_date, p_interval_val * 3);
ELSIF v_ucase_interval IN ('MONTH', 'MM', 'M')
THEN
RETURN ADD_MONTHS(v_date, p_interval_val);
ElSIF v_ucase_interval IN ('DAYOFYEAR', 'DY', 'Y', 'DAY', 'DD', 'D', 'WEEKDAY', 'DW', 'W')
THEN
RETURN v_date + p_interval_val;
ElSIF v_ucase_interval IN ('WEEK', 'WK', 'WW')
THEN
RETURN v_date + (p_interval_val * 7);
ElSIF v_ucase_interval IN ('HOUR', 'HH')
THEN
RETURN v_date + (p_interval_val / 24);
ElSIF v_ucase_interval IN ('MINUTE', 'MI', 'N')
THEN
RETURN v_date + (p_interval_val / 24 / 60);
ElSIF v_ucase_interval IN ('SECOND', 'SS', 'S')
THEN
RETURN v_date + (p_interval_val / 24 / 60 / 60);
ElSIF v_ucase_interval IN ('MILLISECOND', 'MS')
THEN
RETURN v_date + (p_interval_val / 24 / 60 / 60 / 1000);
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END DATEADD;
------------------------------------------------
FUNCTION isdate(p_expr IN VARCHAR2)
RETURN NUMBER
IS
v_is_valid_date BINARY_INTEGER := 0;
BEGIN
IF str_to_date(p_expr) IS NOT NULL THEN
RETURN 1;
ELSE
SELECT NVL2(TO_DATE(p_expr), 1, 0) INTO v_is_valid_date FROM DUAL;
RETURN v_is_valid_date;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END isdate;
FUNCTION stats_date(p_table IN VARCHAR2, p_index IN VARCHAR2)
RETURN DATE
IS
v_last_analyzed DATE;
BEGIN
SELECT last_analyzed INTO v_last_analyzed
FROM USER_IND_STATISTICS
WHERE table_name LIKE UPPER(p_table)
AND index_name LIKE UPPER(p_index);
RETURN v_last_analyzed;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END stats_date;
FUNCTION rand(p_seed NUMBER DEFAULT NULL)
RETURN NUMBER
IS
v_rand_num NUMBER;
BEGIN
IF p_seed IS NOT NULL THEN
DBMS_RANDOM.SEED(p_seed);
END IF;
v_rand_num := DBMS_RANDOM.VALUE();
RETURN v_rand_num;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END rand;
FUNCTION to_base(p_dec NUMBER, p_base NUMBER)
RETURN VARCHAR2
IS
v_str VARCHAR2(255);
v_num NUMBER;
v_hex VARCHAR2(16) DEFAULT '0123456789ABCDEF';
BEGIN
v_num := p_dec;
IF p_dec IS NULL OR p_base IS NULL THEN
RETURN NULL;
END IF;
IF TRUNC(p_dec) <> p_dec OR p_dec < 0 THEN
RAISE PROGRAM_ERROR;
END IF;
LOOP
v_str := SUBSTR(v_hex, MOD(v_num, p_base) + 1, 1) || v_str;
v_num := TRUNC(v_num / p_base);
EXIT WHEN v_num = 0;
END LOOP;
RETURN v_str;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END to_base;
FUNCTION patindex(p_pattern IN VARCHAR2, p_expr IN VARCHAR2)
RETURN NUMBER
IS
v_search_pattern VARCHAR2(100);
v_pos NUMBER := 0;
BEGIN
IF p_pattern IS NULL OR p_expr IS NULL THEN
RETURN NULL;
END IF;
IF NOT DBMS_DB_VERSION.VER_LE_9_2 THEN
v_search_pattern := p_pattern;
v_search_pattern := REPLACE(v_search_pattern, '\', '\\');
v_search_pattern := REPLACE(v_search_pattern, '*', '\*');
v_search_pattern := REPLACE(v_search_pattern, '+', '\+');
v_search_pattern := REPLACE(v_search_pattern, '?', '\?');
v_search_pattern := REPLACE(v_search_pattern, '|', '\|');
v_search_pattern := REPLACE(v_search_pattern, '^', '\^');
v_search_pattern := REPLACE(v_search_pattern, '$', '\$');
v_search_pattern := REPLACE(v_search_pattern, '.', '\.');
v_search_pattern := REPLACE(v_search_pattern, '{', '\{');
v_search_pattern := REPLACE(v_search_pattern, '_', '.');
IF SUBSTR(v_search_pattern, 1, 1) != '%' AND
SUBSTR(v_search_pattern, -1, 1) != '%' THEN
v_search_pattern := '^' || v_search_pattern || '$';
ELSIF SUBSTR(v_search_pattern, 1, 1) != '%' THEN
v_search_pattern := '^' || SUBSTR(v_search_pattern, 1, LENGTH(v_search_pattern) - 1);
ELSIF SUBSTR(v_search_pattern, -1, 1) != '%' THEN
v_search_pattern := SUBSTR(v_search_pattern, 2) || '$';
ELSE
v_search_pattern := SUBSTR(v_search_pattern, 2, LENGTH(v_search_pattern) - 2);
END IF;
v_pos := REGEXP_INSTR(p_expr, v_search_pattern);
ELSE
v_pos := 0;
END IF;
RETURN v_pos;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END patindex;
FUNCTION datediff(p_datepart VARCHAR2, p_start_date_str VARCHAR2, p_end_date_str VARCHAR2)
RETURN NUMBER
IS
v_ret_value NUMBER := NULL;
v_part VARCHAR2(15);
v_start_date DATE;
v_end_date DATE;
BEGIN
v_start_date := str_to_date(p_start_date_str);
v_end_date := str_to_date(p_end_date_str);
v_part := UPPER(p_datepart);
IF v_part IN ('YEAR', 'YY', 'YYYY') THEN
IF EXTRACT(YEAR FROM v_end_date) - EXTRACT(YEAR FROM v_start_date) = 1 AND
EXTRACT(MONTH FROM v_start_date) = 12 AND EXTRACT(MONTH FROM v_end_date) = 1 AND
EXTRACT(DAY FROM v_start_date) = 31 AND EXTRACT(DAY FROM v_end_date) = 1 THEN
-- When comparing December 31 to January 1 of the immediately succeeding year,
-- DateDiff for Year ("yyyy") returns 1, even though only a day has elapsed.
v_ret_value := 1;
ELSE
v_ret_value := ROUND(MONTHS_BETWEEN(v_end_date, v_start_date) / 12);
END IF;
ELSIF v_part IN ('QUARTER', 'QQ', 'Q') THEN
v_ret_value := ROUND(MONTHS_BETWEEN(v_end_date, v_start_date) / 3);
ELSIF v_part IN ('MONTH', 'MM', 'M') THEN
v_ret_value := ROUND(MONTHS_BETWEEN(v_end_date, v_start_date));
ElSIF v_part IN ('DAYOFYEAR', 'DY', 'Y') THEN
v_ret_value := ROUND(v_end_date - v_start_date);
ElSIF v_part IN ('DAY', 'DD', 'D') THEN
v_ret_value := ROUND(v_end_date - v_start_date);
ElSIF v_part IN ('WEEK', 'WK', 'WW') THEN
v_ret_value := ROUND((v_end_date - v_start_date) / 7);
ELSIF v_part IN ('WEEKDAY', 'DW', 'W') THEN
v_ret_value := TO_CHAR(v_end_date, 'D') - TO_CHAR(v_start_date, 'D');
ElSIF v_part IN ('HOUR', 'HH') THEN
v_ret_value := ROUND((v_end_date - v_start_date) * 24);
ElSIF v_part IN ('MINUTE', 'MI', 'N') THEN
v_ret_value := ROUND((v_end_date - v_start_date) * 24 * 60);
ElSIF v_part IN ('SECOND', 'SS', 'S') THEN
v_ret_value := ROUND((v_end_date - v_start_date) * 24 * 60 * 60);
ElSIF v_part IN ('MILLISECOND', 'MS') THEN
v_ret_value := ROUND((v_end_date - v_start_date) * 24 * 60 * 60 * 1000);
END IF;
RETURN v_ret_value;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END DATEDIFF;
------------------------DATEDIFFF dung DATE-------------------------------
FUNCTION datediff(p_datepart VARCHAR2, p_start_date_str DATE, p_end_date_str DATE)
RETURN NUMBER
IS
v_ret_value NUMBER := NULL;
v_part VARCHAR2(15);
v_start_date DATE;
v_end_date DATE;
BEGIN
V_START_DATE := STR_TO_DATE(TO_CHAR(P_START_DATE_STR,'DD MON YYYY'));
v_end_date := str_to_date(TO_CHAR(p_end_date_str,'DD MON YYYY'));
v_part := UPPER(p_datepart);
IF v_part IN ('YEAR', 'YY', 'YYYY') THEN
IF EXTRACT(YEAR FROM v_end_date) - EXTRACT(YEAR FROM v_start_date) = 1 AND
EXTRACT(MONTH FROM v_start_date) = 12 AND EXTRACT(MONTH FROM v_end_date) = 1 AND
EXTRACT(DAY FROM v_start_date) = 31 AND EXTRACT(DAY FROM v_end_date) = 1 THEN
-- When comparing December 31 to January 1 of the immediately succeeding year,
-- DateDiff for Year ("yyyy") returns 1, even though only a day has elapsed.
v_ret_value := 1;
ELSE
v_ret_value := ROUND(MONTHS_BETWEEN(v_end_date, v_start_date) / 12);
END IF;
ELSIF v_part IN ('QUARTER', 'QQ', 'Q') THEN
v_ret_value := ROUND(MONTHS_BETWEEN(v_end_date, v_start_date) / 3);
ELSIF v_part IN ('MONTH', 'MM', 'M') THEN
v_ret_value := ROUND(MONTHS_BETWEEN(v_end_date, v_start_date));
ElSIF v_part IN ('DAYOFYEAR', 'DY', 'Y') THEN
v_ret_value := ROUND(v_end_date - v_start_date);
ElSIF v_part IN ('DAY', 'DD', 'D') THEN
v_ret_value := ROUND(v_end_date - v_start_date);
ElSIF v_part IN ('WEEK', 'WK', 'WW') THEN
v_ret_value := ROUND((v_end_date - v_start_date) / 7);
ELSIF v_part IN ('WEEKDAY', 'DW', 'W') THEN
v_ret_value := TO_CHAR(v_end_date, 'D') - TO_CHAR(v_start_date, 'D');
ElSIF v_part IN ('HOUR', 'HH') THEN
v_ret_value := ROUND((v_end_date - v_start_date) * 24);
ElSIF v_part IN ('MINUTE', 'MI', 'N') THEN
v_ret_value := ROUND((v_end_date - v_start_date) * 24 * 60);
ElSIF v_part IN ('SECOND', 'SS', 'S') THEN
v_ret_value := ROUND((v_end_date - v_start_date) * 24 * 60 * 60);
ElSIF v_part IN ('MILLISECOND', 'MS') THEN
v_ret_value := ROUND((v_end_date - v_start_date) * 24 * 60 * 60 * 1000);
END IF;
RETURN v_ret_value;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END DATEDIFF;
------------------------------------------------------------------------------------------------
/*
FUNCTION day_(p_date_str IN VARCHAR2)
RETURN NUMBER
IS
v_date DATE;
BEGIN
v_date := str_to_date(TO_CHAR(p_date_str);
IF v_date IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER(TO_CHAR(v_date, 'DD'));
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END day_;
*/
--FUNC DAY truyen kieu VARCHAR2
FUNCTION day_(p_date_str IN VARCHAR2)
RETURN NUMBER
IS
v_date DATE;
BEGIN
v_date := str_to_date(p_date_str);
IF v_date IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER(TO_CHAR(v_date, 'DD'));
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END DAY_;
--DEN DAY_CHAR
--FUNC DAY truyen kieu DATE
FUNCTION day_(p_date_str IN DATE)
RETURN NUMBER
IS
v_date DATE;
BEGIN
v_date := str_to_date(TO_CHAR(p_date_str,'DD MON YYYY'));
IF v_date IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER(TO_CHAR(v_date, 'DD'));
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END DAY_;
--END DAY_DATE
FUNCTION ident_incr(p_sequence IN VARCHAR2)
RETURN NUMBER
IS
v_incr_by NUMBER;
BEGIN
SELECT increment_by INTO v_incr_by
FROM USER_SEQUENCES
WHERE sequence_name LIKE UPPER(p_sequence);
RETURN v_incr_by;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END ident_incr;
FUNCTION isnumeric(p_expr IN VARCHAR2)
RETURN NUMBER
IS
numeric_val NUMBER;
temp_str VARCHAR2(50);
BEGIN
temp_str := p_expr;
IF SUBSTR(temp_str, 1, 1) = '$' THEN
temp_str := SUBSTR(temp_str, 2);
END IF;
numeric_val := TO_NUMBER(temp_str);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END isnumeric;
FUNCTION hex(p_num VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN to_base(p_num, 16);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END hex;
PROCEDURE decrementTrancount
IS
BEGIN
IF trancount > 0 THEN
trancount := trancount - 1;
END IF;
END decrementTrancount;
FUNCTION difference(p_expr1 IN VARCHAR2, p_expr2 IN VARCHAR2)
RETURN NUMBER
IS
sound_ex_val_1 CHAR(4);
sound_ex_val_2 CHAR(4);
similarity NUMBER := 0;
idx NUMBER := 1;
BEGIN
IF p_expr1 IS NULL OR p_expr2 IS NULL THEN
RETURN NULL;
END IF;
sound_ex_val_1 := SOUNDEX(p_expr1);
sound_ex_val_2 := SOUNDEX(p_expr2);
LOOP
IF SUBSTR(sound_ex_val_1, idx, 1) = SUBSTR(sound_ex_val_2, idx, 1) THEN
similarity := similarity + 1;
END IF;
idx := idx + 1;
EXIT WHEN idx > 4;
END LOOP;
RETURN similarity;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END difference;
FUNCTION datepart(p_part_expr IN VARCHAR2, p_date_str IN VARCHAR2)
RETURN NUMBER
IS
v_part VARCHAR2(15);
v_date DATE;
BEGIN
v_date := str_to_date(p_date_str);
v_part := UPPER(p_part_expr);
IF v_part IN ('YEAR', 'YY', 'YYYY') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'YYYY'));
ELSIF v_part IN ('QUARTER', 'QQ', 'Q') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'Q'));
ELSIF v_part IN ('MONTH', 'MM', 'M') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'MM'));
ElSIF v_part IN ('DAYOFYEAR', 'DY', 'Y') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'DDD'));
ELSIF v_part IN ('DAY', 'DD', 'D') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'DD'));
ELSIF v_part IN ('WEEKDAY', 'DW', 'W') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'D'));
ElSIF v_part IN ('WEEK', 'WK', 'WW') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'IW'));
ElSIF v_part IN ('HOUR', 'HH') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'HH24'));
ElSIF v_part IN ('MINUTE', 'MI', 'N') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'MI'));
ElSIF v_part IN ('SECOND', 'SS', 'S') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'SS'));
ElSIF v_part IN ('MILLISECOND', 'MS') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'FF3'));
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END datepart;
FUNCTION datepart(p_part_expr IN VARCHAR2, p_date in date)
RETURN NUMBER
IS
v_part VARCHAR2(15);
v_date DATE;
BEGIN
v_date := str_to_date(TO_CHAR(p_date,'DD MON YYYY'));
v_part := UPPER(p_part_expr);
IF v_part IN ('YEAR', 'YY', 'YYYY') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'YYYY'));
ELSIF v_part IN ('QUARTER', 'QQ', 'Q') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'Q'));
ELSIF v_part IN ('MONTH', 'MM', 'M') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'MM'));
ElSIF v_part IN ('DAYOFYEAR', 'DY', 'Y') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'DDD'));
ELSIF v_part IN ('DAY', 'DD', 'D') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'DD'));
ELSIF v_part IN ('WEEKDAY', 'DW', 'W') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'D'));
ElSIF v_part IN ('WEEK', 'WK', 'WW') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'IW'));
ElSIF v_part IN ('HOUR', 'HH') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'HH24'));
ElSIF v_part IN ('MINUTE', 'MI', 'N') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'MI'));
ElSIF v_part IN ('SECOND', 'SS', 'S') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'SS'));
ElSIF v_part IN ('MILLISECOND', 'MS') THEN RETURN TO_NUMBER(TO_CHAR(v_date, 'FF3'));
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END datepart;
FUNCTION radians(p_degree IN NUMBER)
RETURN NUMBER
IS
v_rad NUMBER;
BEGIN
v_rad := p_degree / 180 * pi();
RETURN v_rad;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END radians;
FUNCTION reverse_(p_expr IN VARCHAR2)
RETURN VARCHAR2
IS
v_result VARCHAR2(2000) := NULL;
BEGIN
FOR i IN 1..LENGTH(p_expr) LOOP
v_result := v_result || SUBSTR(p_expr, -i, 1);
END LOOP;
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END reverse_;
FUNCTION parsename(p_object_name IN VARCHAR2, p_object_piece IN NUMBER)
RETURN VARCHAR2
IS
ret_val VARCHAR2(150) := NULL;
pos NUMBER;
v_next_pos NUMBER;
BEGIN
IF p_object_name IS NULL THEN
RETURN NULL;
END IF;
-- for 10g
IF NOT DBMS_DB_VERSION.VER_LE_9_2 THEN
IF p_object_piece = 1 THEN -- object name
ret_val := REGEXP_SUBSTR(p_object_name, '(^[^\.]+$)|(\.[^\.]+$)');
ret_val := REPLACE(ret_val, '.', '');
ELSIF p_object_piece = 2 THEN -- schema name
ret_val := REGEXP_SUBSTR(p_object_name, '([^\.]+)\.([^\.]+$)');
ret_val := REGEXP_REPLACE(ret_val, '\.([^\.]+$)', '');
ELSIF p_object_piece = 3 THEN -- database name
ret_val := REGEXP_SUBSTR(p_object_name, '([^\.]+)\.([^\.]*)\.([^\.]+$)');
ret_val := REGEXP_REPLACE(ret_val, '\.([^\.]*)\.([^\.]+$)', '');
ELSIF p_object_piece = 4 THEN -- server name
ret_val := REGEXP_SUBSTR(p_object_name, '^([^\.]+)\.([^\.]*)\.([^\.]*)\.([^\.]+$)');
IF ret_val IS NOT NULL THEN
ret_val := REGEXP_REPLACE(p_object_name, '^([^\.]+)\.([^\.]*)\.([^\.]*)\.([^\.]+$)', '\1');
END IF;
END IF;
ELSE
ret_val := p_object_name;
v_next_pos := LENGTH(p_object_name);
FOR i IN 1 .. p_object_piece LOOP
pos := INSTR(p_object_name, '.', -1, i);
IF pos > 0 THEN
ret_val := SUBSTR(p_object_name, pos + 1, v_next_pos - pos);
END IF;
v_next_pos := pos;
END LOOP;
IF LENGTH(ret_val) = 0 THEN
RETURN NULL;
END IF;
END IF;
RETURN ret_val;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END parsename;
FUNCTION round_(p_expr NUMBER, p_len NUMBER, p_function NUMBER DEFAULT 0)
RETURN NUMBER
IS
v_ret_value NUMBER;
BEGIN
IF p_function = 0 THEN
v_ret_value := ROUND(p_expr, p_len);
ELSE
v_ret_value := TRUNC(p_expr, p_len);
END IF;
RETURN v_ret_value;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END ROUND_;
--Ham MONTH truyen kieu DATE
FUNCTION month_(p_date_str IN DATE)
RETURN NUMBER
IS
v_date DATE;
BEGIN
v_date := sqlserver_utilities.str_to_date(TO_CHAR(p_date_str,'DD MON YYYY'));
IF v_date IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER(TO_CHAR(v_date, 'MM'));
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END MONTH_;
--END MONTH_DATE
--Ham MONTH truyen kieu VARCHAR
FUNCTION month_(p_date_str IN VARCHAR2)
RETURN NUMBER
IS
v_date DATE;
BEGIN
v_date := sqlserver_utilities.str_to_date(p_date_str);
IF v_date IS NULL THEN
RETURN NULL;
END IF;
RETURN TO_NUMBER(TO_CHAR(v_date, 'MM'));
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END MONTH_;
--END MONTH_CHAR
PROCEDURE commit_transaction
IS
BEGIN
IF trancount <= 1 THEN
COMMIT;
END IF;
resetTrancount;
END commit_transaction;
FUNCTION pi
RETURN NUMBER
IS
pi NUMBER := 3.141592653589793116;
BEGIN
RETURN pi;
END pi;
PROCEDURE resetTrancount
IS
BEGIN
trancount := 0;
END resetTrancount;
FUNCTION oct(p_num VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN to_base(p_num, 8);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END oct;
FUNCTION str(p_expr IN NUMBER, p_len IN NUMBER DEFAULT 10, p_scale IN NUMBER DEFAULT 0)
RETURN VARCHAR2
IS
v_ret_val VARCHAR2(50);
v_temp_val NUMBER;
v_format_str VARCHAR2(50);
BEGIN
IF p_len < LENGTH(TO_CHAR(p_expr)) THEN
RETURN '**';
END IF;
v_temp_val := p_expr;
v_temp_val := ROUND(v_temp_val, p_scale);
IF p_scale > 0 THEN
v_format_str := LPAD(' ', p_len - p_scale, '9');
v_format_str := TRIM(v_format_str) || '.';
v_format_str := RPAD(v_format_str, p_len, '0');
ELSE
v_format_str := LPAD('', p_len, '9');
END IF;
v_ret_val := TO_CHAR(v_temp_val, v_format_str);
RETURN v_ret_val;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END str;
FUNCTION degrees(p_angle_radians IN NUMBER)
RETURN NUMBER
IS
BEGIN
IF p_angle_radians IS NULL THEN
RETURN NULL;
END IF;
RETURN p_angle_radians / pi() * 180;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END degrees;
FUNCTION datename(p_part_expr IN VARCHAR2, p_date_str IN VARCHAR2)
RETURN VARCHAR2
IS
v_part VARCHAR2(15);
v_date DATE;
BEGIN
v_date := str_to_date(p_date_str);
v_part := UPPER(p_part_expr);
IF v_part IN ('YEAR', 'YY', 'YYYY') THEN RETURN TO_CHAR(v_date, 'YYYY');
ELSIF v_part IN ('QUARTER', 'QQ', 'Q') THEN RETURN TO_CHAR(v_date, 'Q');
ELSIF v_part IN ('MONTH', 'MM', 'M') THEN RETURN TO_CHAR(v_date, 'MONTH');
ElSIF v_part IN ('DAYOFYEAR', 'DY', 'Y') THEN RETURN TO_CHAR(v_date, 'DDD');
ELSIF v_part IN ('DAY', 'DD', 'D') THEN RETURN TO_CHAR(v_date, 'DD');
ELSIF v_part IN ('WEEKDAY', 'DW', 'W') THEN RETURN TO_CHAR(v_date, 'DAY');
ElSIF v_part IN ('WEEK', 'WK', 'WW') THEN RETURN TO_CHAR(v_date, 'IW');
ElSIF v_part IN ('HOUR', 'HH') THEN RETURN TO_CHAR(v_date, 'HH24');
ElSIF v_part IN ('MINUTE', 'MI', 'N') THEN RETURN TO_CHAR(v_date, 'MI');
ElSIF v_part IN ('SECOND', 'SS', 'S') THEN RETURN TO_CHAR(v_date, 'SS');
ElSIF v_part IN ('MILLISECOND', 'MS') THEN RETURN TO_CHAR(v_date, 'FF3');
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END datename;
FUNCTION ident_seed(p_sequence IN VARCHAR2)
RETURN NUMBER
IS
v_seed NUMBER;
BEGIN
SELECT min_value INTO v_seed
FROM USER_SEQUENCES
WHERE sequence_name LIKE UPPER(p_sequence);
RETURN v_seed;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END ident_seed;
FUNCTION quotename(p_str IN VARCHAR2, p_delimiters IN VARCHAR2 DEFAULT '[]')
RETURN VARCHAR2
IS
v_ret_val VARCHAR2(150) := NULL;
BEGIN
IF p_delimiters = '[]' THEN
v_ret_val := '[' || REPLACE(p_str, ']', ']]') || ']';
ELSIF p_delimiters = '"' THEN
v_ret_val := '"' || p_str || '"';
ELSIF p_delimiters = '''' THEN
v_ret_val := '''' || p_str || '''';
END IF;
RETURN v_ret_val;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, DBMS_UTILITY.FORMAT_ERROR_STACK);
END quotename;
FUNCTION fetch_status(p_cursorfound IN BOOLEAN)
RETURN NUMBER
IS
v_fetch_status NUMBER := 0;
BEGIN
CASE
WHEN p_cursorfound THEN
v_fetch_status := 0;
ELSE
v_fetch_status := -1;
END CASE;
return v_fetch_status;
END fetch_status;
END sqlserver_utilities;
Bạn đang đọc truyện trên: Truyen247.Pro