1.Oracle方法 判断是否是日期
CREATE OR REPLACE FUNCTION is_date (parmin VARCHAR2)
RETURN NUMBER IS
val DATE;
BEGIN
val := TO_DATE (NVL (parmin, 'a'), 'yyyy-MM-dd');
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
2.判断是否是数字
CREATE OR REPLACE FUNCTION "IS_NUMBER" (i_str varchar2)
return number
is
v_number number;
begin
select to_number(i_str) into v_number from dual;
if v_number is not null then
return 1;
else
return 0;
end if;
EXCEPTION
WHEN OTHERS
THEN
return 0;
end;
3.转换为中文
CREATE OR REPLACE FUNCTION "TRANS_CHINESE" (p_inputStr IN NVARCHAR2, p_inputType IN NUMBER) RETURN VARCHAR2
IS
/***********************************************************
名称:TRANS_CHINESE
功能描述:简繁体转换
测试函数:select TRANS_CHINESE('中国',0) from dual;
修订记录:
版本号 编辑时间 编辑人 修改描述
1.0.0 2017/9/18 10:47:04 qingping.li 创建此函数
入参出参描述:
N/A
***********************************************************/
v_ret NVARCHAR2(600) := '';
i INT := 0;
v_str VARCHAR2(3);
v_count INT := 0;
BEGIN
if p_inputStr is null or p_inputStr = '' then
return null;
end if;
if p_inputType = 0 then
for i in 1.. length(p_inputStr)
loop
select count(*) into v_count from chineseword where simpleword = substr(p_inputStr, i, 1);
if v_count > 0 then
select traditionword into v_str from chineseword where simpleword = substr(p_inputStr, i, 1);
v_ret := v_ret || v_str;
else
v_ret := v_ret || substr(p_inputStr, i, 1);
end if;
end loop;
else
for i in 1.. length(p_inputStr)
loop
select count(*) into v_count from chineseword where traditionword = substr(p_inputStr, i, 1);
if v_count > 0 then
select simpleword into v_str from chineseword where traditionword = substr(p_inputStr, i, 1);
v_ret := v_ret || v_str;
else
v_ret := v_ret || substr(p_inputStr, i, 1);
end if;
end loop;
end if;
RETURN v_ret;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
Raise;
END TRANS_CHINESE;