Oracle书写方法（后续参考语法）

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;