Oracle 常用函数整理

 

--Author:Zhang
--Create Date:2011-12-06
--Description:判断一个字符串是否为数字
CREATE OR REPLACE FUNCTION ISNUMERIC(MyStr VARCHAR2) RETURN NUMBER
IS
  STR VARCHAR2(400);
  V_NUM NUMBER;
BEGIN
     V_NUM:=0;
     STR:=TRIM(MyStr);
     IF TRIM(STR) IS NULL THEN
        GOTO NON_NUMBER;
     END IF;
       BEGIN
            V_NUM:=TO_NUMBER(STR);

            EXCEPTION
            WHEN INVALID_NUMBER THEN
                 NULL;
            WHEN OTHERS THEN
                 NULL;
       END;
       <<NON_NUMBER>>
     RETURN V_NUM;
END;

 

Oracle 9i 树形结构查询

create or replace function GET_TREE_FULL_NAME(PARAM INT) return varchar2 is
  my_reulst varchar2(100);
  str       varchar2(100);
  cursor mycur is
    select TNAME
      from NK_SYS_TREEBOOK
     start with tid = PARAM
    connect by prior tpid = tid
     order by tid asc;
begin
  open mycur;
  fetch mycur
    into str;
  while mycur%found loop
    dbms_output.put_line(str);
    my_reulst := my_reulst || ',' || str;
    fetch mycur
      into str;
  end loop;
  close mycur;
  return(substr(my_reulst, 2, length(my_reulst)));
end GET_TREE_FULL_NAME;