SQL CONNECT BY

SELECT TO_CHAR(TO_DATE('201501', 'yyyymm '), 'dd') TS
  FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('201501', 'yyyymm ') + (ROWNUM), 'dd') TS
  FROM DUAL
CONNECT BY ROWNUM <
           TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('201501', 'yyyymm')), 'dd'))
;

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;

SELECT F_HEX_TO_DEC('16') FROM dual;

 

CREATE OR REPLACE FUNCTION F_HEX_TO_DEC(P_STR IN VARCHAR2) RETURN VARCHAR2 IS
  ----------------------------------------------------------------------------------------------------------------------
  -- 对象名称: f_hex_to_dec
  -- 对象描述: 十六进制转换十进制
  -- 输入参数: p_str 十六进制字符串
  -- 返回结果: 十进制字符串
  -- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;
  ----------------------------------------------------------------------------------------------------------------------
  V_RETURN VARCHAR2(4000);
BEGIN
  SELECT SUM(DATA)
    INTO V_RETURN
    FROM (SELECT (CASE UPPER(SUBSTR(P_STR, ROWNUM, 1))
                   WHEN 'A' THEN '10'
                   WHEN 'B' THEN '11'
                   WHEN 'C' THEN '12'
                   WHEN 'D' THEN '13'
                   WHEN 'E' THEN '14'
                   WHEN 'F' THEN '15'
                   ELSE SUBSTR(P_STR, ROWNUM, 1) END) * POWER(16, LENGTH(P_STR) - ROWNUM) DATA
            FROM DUAL
          CONNECT BY ROWNUM <= LENGTH(P_STR));
  RETURN V_RETURN;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;