oracle 相关 方法 （function）

1 分隔字符串 方法

create or replace function split (p_list clob, p_sep varchar2 := ',')
  return tabletype
  pipelined

is
  l_idx    pls_integer;
  v_list  varchar2 (32676) := to_char(p_list);
begin
  loop
      l_idx  := instr (v_list, p_sep);

      if l_idx > 0
      then
        pipe row (substr (v_list, 1, l_idx - 1));
        v_list  := substr (v_list, l_idx + length (p_sep));
      else
        pipe row (v_list);
        exit;
      end if;
  end loop;
end;

 2 生成 UUID

CREATE OR REPLACE FUNCTION get_uuid
RETURN VARCHAR
IS
guid VARCHAR (50);
BEGIN
guid := lower(RAWTOHEX(sys_guid()));
RETURN
substr(guid,1,8)||'-'||substr(guid,9,4)||'-'||substr(guid,13,4)||'-'||substr(guid,17,4)||'-'||substr(guid,21,12);
END get_uuid;

 3 MD5 加密

CREATE OR REPLACE FUNCTION MD5(passwd IN VARCHAR2)RETURN VARCHAR2
IS
    retval varchar2(32);
BEGIN
    retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd)) ;
RETURN retval;
END;