--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;