create or replace PROCEDURE
JUDGE_NUMBER(p_STR VARCHAR2,p_pose out pls_integer,p_char out varchar2)
is
-- Author : 青云
-- Created : 2010-10-02 15:37:17
-- Purpose : 判断字符串是否为纯数字，假设不是，返回位置和字符；假设是，返回0
begin
WITH A AS
(SELECT LEVEL lvl, substr(p_STR, LEVEL, 1) AS chr
FROM dual
CONNECT BY LEVEL <= length(p_STR))
SELECT lvl ,chr into p_pose,p_char
FROM A
WHERE chr NOT IN (SELECT TO_CHAR(LEVEL - 1) AS X
FROM DUAL
CONNECT BY LEVEL <= 10)
AND ROWNUM=1;
exception
when no_data_found then
p_pose:=0;
end JUDGE_NUMBER; |
