oracle IS_NUMBERIC

CREATE OR REPLACE FUNCTION SIHFDATA.IS_NUMBERIC(MyStr VARCHAR2)
RETURN INTEGER
IS
ISNUM NUMBER;
BEGIN
ISNUM:=TO_NUMBER(MyStr);
RETURN 1;
EXCEPTION
WHEN INVALID_NUMBER THEN
RETURN 0;
WHEN OTHERS THEN
RETURN 0;
END;

 

 

 

生成拼音码函数:

CREATE OR REPLACE FUNCTION SIHFDATA.PYM (P_NAME IN VARCHAR2)
RETURN VARCHAR2
AS
V_COMPARE VARCHAR2 (100);
V_RETURN VARCHAR2 (4000);

FUNCTION F_NLSSORT (P_WORD IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN NLSSORT (P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
END;
BEGIN
FOR I IN 1 .. LENGTH (P_NAME)
LOOP
V_COMPARE := F_NLSSORT (SUBSTR (P_NAME, I, 1));

IF V_COMPARE >= F_NLSSORT (' 吖 ') AND V_COMPARE <= F_NLSSORT ('驁 ')
THEN
V_RETURN := V_RETURN || 'a';
ELSIF V_COMPARE >= F_NLSSORT ('八 ')
AND V_COMPARE <= F_NLSSORT ('簿 ')
THEN
V_RETURN := V_RETURN || 'b';
ELSIF V_COMPARE >= F_NLSSORT ('嚓 ')
AND V_COMPARE <= F_NLSSORT ('錯 ')
THEN
V_RETURN := V_RETURN || 'c';
ELSIF V_COMPARE >= F_NLSSORT ('咑 ')
AND V_COMPARE <= F_NLSSORT ('鵽 ')
THEN
V_RETURN := V_RETURN || 'd';
ELSIF V_COMPARE >= F_NLSSORT ('妸 ')
AND V_COMPARE <= F_NLSSORT ('樲 ')
THEN
V_RETURN := V_RETURN || 'e';
ELSIF V_COMPARE >= F_NLSSORT ('发 ')
AND V_COMPARE <= F_NLSSORT ('猤 ')
THEN
V_RETURN := V_RETURN || 'f';
ELSIF V_COMPARE >= F_NLSSORT ('旮 ')
AND V_COMPARE <= F_NLSSORT ('腂 ')
THEN
V_RETURN := V_RETURN || 'g';
ELSIF V_COMPARE >= F_NLSSORT ('妎 ')
AND V_COMPARE <= F_NLSSORT ('夻 ')
THEN
V_RETURN := V_RETURN || 'h';
ELSIF V_COMPARE >= F_NLSSORT ('丌 ')
AND V_COMPARE <= F_NLSSORT ('攈 ')
THEN
V_RETURN := V_RETURN || 'j';
ELSIF V_COMPARE >= F_NLSSORT ('咔 ')
AND V_COMPARE <= F_NLSSORT ('穒 ')
THEN
V_RETURN := V_RETURN || 'k';
ELSIF V_COMPARE >= F_NLSSORT ('垃 ')
AND V_COMPARE <= F_NLSSORT ('擽 ')
THEN
V_RETURN := V_RETURN || 'l';
ELSIF V_COMPARE >= F_NLSSORT ('嘸 ')
AND V_COMPARE <= F_NLSSORT ('椧 ')
THEN
V_RETURN := V_RETURN || 'm';
ELSIF V_COMPARE >= F_NLSSORT ('拏 ')
AND V_COMPARE <= F_NLSSORT ('瘧 ')
THEN
V_RETURN := V_RETURN || 'n';
ELSIF V_COMPARE >= F_NLSSORT ('筽 ')
AND V_COMPARE <= F_NLSSORT ('漚 ')
THEN
V_RETURN := V_RETURN || 'o';
ELSIF V_COMPARE >= F_NLSSORT ('妑 ')
AND V_COMPARE <= F_NLSSORT ('曝 ')
THEN
V_RETURN := V_RETURN || 'p';
ELSIF V_COMPARE >= F_NLSSORT ('七 ')
AND V_COMPARE <= F_NLSSORT ('裠 ')
THEN
V_RETURN := V_RETURN || 'q';
ELSIF V_COMPARE >= F_NLSSORT ('亽 ')
AND V_COMPARE <= F_NLSSORT ('鶸 ')
THEN
V_RETURN := V_RETURN || 'r';
ELSIF V_COMPARE >= F_NLSSORT ('仨 ')
AND V_COMPARE <= F_NLSSORT ('蜶 ')
THEN
V_RETURN := V_RETURN || 's';
ELSIF V_COMPARE >= F_NLSSORT ('侤 ')
AND V_COMPARE <= F_NLSSORT ('籜 ')
THEN
V_RETURN := V_RETURN || 't';
ELSIF V_COMPARE >= F_NLSSORT ('屲 ')
AND V_COMPARE <= F_NLSSORT ('鶩 ')
THEN
V_RETURN := V_RETURN || 'w';
ELSIF V_COMPARE >= F_NLSSORT ('夕 ')
AND V_COMPARE <= F_NLSSORT ('鑂 ')
THEN
V_RETURN := V_RETURN || 'x';
ELSIF V_COMPARE >= F_NLSSORT ('丫 ')
AND V_COMPARE <= F_NLSSORT ('韻 ')
THEN
V_RETURN := V_RETURN || 'y';
ELSIF V_COMPARE >= F_NLSSORT ('帀 ')
AND V_COMPARE <= F_NLSSORT ('咗 ')
THEN
V_RETURN := V_RETURN || 'z';
END IF;
END LOOP;

RETURN V_RETURN;
END;