查询字符类型转换date类型后值的函数

 create or replace FUNCTION get_internal_value (p_value IN VARCHAR2)
  RETURN VARCHAR2
  IS
    temp_n NUMBER := 0;
  BEGIN
    FOR i IN 1..15
    LOOP
      temp_n := temp_n + POWER(256, 15 - i) * ASCII(SUBSTR(RPAD(p_value, 15, CHR(0)), i, 1));
    END LOOP;
    RETURN TO_CHAR(ROUND(temp_n, -21));
  EXCEPTION
    WHEN OTHERS THEN
      RETURN p_value;
  END get_internal_value;
/

create or replace FUNCTION get_external_value (p_value IN VARCHAR2)
  RETURN VARCHAR2
  IS
    temp_n NUMBER;
    temp_i INTEGER;
    my_result VARCHAR2(32767) := NULL;

  BEGIN
    IF LENGTH(p_value) <> 36 OR
       SUBSTR(p_value, 16) <> '000000000000000000000' OR
       p_value > POWER(256, 15) OR
       p_value < POWER(256, 14) THEN
      RETURN p_value; -- cannot get external value
    END IF;

    temp_n := p_value / POWER(256, 14); -- get most significant digits

    -- decoding most significant digits then shift multiplying by 256
    FOR i IN 1..14
    LOOP
      temp_i := TRUNC(temp_n);
      temp_n := (temp_n - temp_i) * 256;
      IF temp_i NOT BETWEEN 32 AND 126 OR temp_n NOT BETWEEN 32 AND 126 THEN
        EXIT; -- reached the tail
      END IF;
      my_result := my_result||CHR(temp_i); -- all but last byte
    END LOOP;


    IF temp_i NOT BETWEEN 32 AND 126 THEN
      RETURN my_result||'?'; -- cannot decode
    END IF;

    -- scan to the right starting at temp_i
    FOR i IN temp_i..126
    LOOP
      IF get_internal_value(my_result||CHR(i)) = p_value THEN
        RETURN my_result||CHR(i); -- approximate value
      END IF;
    END LOOP;

    -- scan to the left starting at temp_i
    FOR i IN 32..temp_i
    LOOP
      IF get_internal_value(my_result||CHR(temp_i + 32 - i)) = p_value THEN
        RETURN my_result||CHR(temp_i + 32 - i); -- approximate value
      END IF;
    END LOOP;


    RETURN my_result||CHR(temp_i); -- this is the best we could do
  EXCEPTION
    WHEN OTHERS THEN
      RETURN p_value;
  END get_external_value;
/


select get_internal_value('AAA') from dual;
select get_external_value('338822802259565000000000000000000000') from dual;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28551528/viewspace-2122577/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28551528/viewspace-2122577/