标签归档:转化

Oracle PLSQL 将数字转为对应的英文单词

用途
    将数字转为对应的英文单词,技术写代码可引用这一段
使用场景
    发票、对账单等处输出金额

命令如下,可点击sql查看,下面的内容直接贴出来似乎不能直接编译。
/*  FUNCTION money_format_us(p_money_amount IN NUMBER) RETURN VARCHAR2 IS*/
DECLARE
  p_money_amount NUMBER := -2.04;
  TYPE lt_array_type IS VARRAY(50) OF VARCHAR2(20);
   /*一维数组,字符串类型 */
  l_array        lt_array_type := lt_array_type('ONE ',
                                                'TWO ',
                                                'THREE ',
                                                'FOUR ',
                                                'FIVE ',
                                                'SIX ',
                                                'SEVEN ',
                                                'EIGHT ',
                                                'NINE ',
                                                'TEN ',
                                                'ELEVEN ',
                                                'TWELVE ',
                                                'THIRTEEN ',
                                                'FOURTEEN ',
                                                'FIFTEEN ',
                                                'SIXTEEN ',
                                                'SEVENTEEN ',
                                                'EIGHTEEN ',
                                                'NINETEEN ',
                                                'TWENTY ',
                                                'THIRTY ',
                                                'FORTY ',
                                                'FIFTY ',
                                                'SIXTY ',
                                                'SEVENTY ',
                                                'EIGHTY ',
                                                'NINETY ',
                                                'HUNDRED ',
                                                'THOUSAND ',
                                                'MILLION ',
                                                'BILLION ');
  c_money_amount VARCHAR2(14);
  l_string       VARCHAR2(600);
  n              CHAR;
  l_pre_n        CHAR;
  l_length       NUMBER;
  i              NUMBER;
  tmp            NUMBER;
  l_decimal_flag VARCHAR2(1);
  l_money_amount NUMBER;
  l_sign         VARCHAR2(10);
BEGIN
  l_money_amount := round(abs(p_money_amount), 2);
  IF p_money_amount < 0 THEN
    l_sign := 'NEGATIVE ';
  ELSE
    l_sign := '';
  END IF;
  tmp            := l_money_amount * 100;
  c_money_amount := rtrim(ltrim(to_char(tmp, '999999999999999999')));
  l_length       := length(c_money_amount);
  i              := 0;
  WHILE i < l_length – 2 LOOP
    i := i + 1;
    IF MOD(l_length – 2 – i, 3) = 2 THEN
      n := substr(c_money_amount, i, 1);
      IF n <> '0' THEN
        l_string := l_string || l_array(to_number(n));
        l_string := l_string || l_array(to_number(28));
      END IF;
    END IF;
    IF MOD(l_length – 2 – i, 3) = 1 THEN
      n := substr(c_money_amount, i, 1);
      IF n = '1' THEN
        l_pre_n := n;
      END IF;
      IF n NOT IN ('0', '1') THEN
        l_string := l_string || l_array(to_number(n) + 18);
      END IF;
    END IF;
    IF MOD(l_length – 2 – i, 3) = 0 THEN
      IF nvl(l_pre_n, 'X') = '1' THEN
        n        := substr(c_money_amount, i, 1);
        l_string := l_string || l_array(to_number(l_pre_n || n));
        l_pre_n  := NULL;
      ELSE
        n := substr(c_money_amount, i, 1);
        IF n <> '0' THEN
          l_string := l_string || l_array(to_number(n));
        END IF;
      END IF;
    END IF;
    IF l_length – i = 5 AND substr(c_money_amount, i – 2, 3) <> '000' THEN
      l_string := l_string || l_array(to_number(29));
    END IF;
    IF l_length – i = 8 AND substr(c_money_amount, i – 2, 3) <> '000' THEN
      l_string := l_string || l_array(to_number(30));
    END IF;
    IF l_length – i = 11 THEN
      l_string := l_string || l_array(to_number(31));
    END IF;
  END LOOP;
  n := substr(c_money_amount, l_length – 1, 1);
  IF n NOT IN ('0', '1') THEN
    l_decimal_flag := 'Y';
    l_string       := l_string || 'AND ';
    l_string       := l_string || l_array(to_number(n) + 18);
  END IF;
  IF n = '1' THEN
    l_decimal_flag := 'Y';
    l_string       := l_string || 'AND ';
    l_pre_n        := n;
    n              := substr(c_money_amount, l_length, 1);
    l_string       := l_string || l_array(to_number(l_pre_n || n));
  ELSE
    n := substr(c_money_amount, l_length, 1);
    IF n <> '0' THEN
      IF nvl(l_decimal_flag, 'N') = 'N' THEN
        l_string := l_string || 'AND ';
      END IF;
      l_decimal_flag := 'Y';
      l_string       := l_string || l_array(to_number(n));
    END IF;
  END IF;
  IF l_decimal_flag = 'Y' THEN
    l_string := l_string || 'CENTS ';
  ELSE
    l_string := l_string;
  END IF;
  l_string := l_sign || l_string; /*RETURN l_string;*/
  dbms_output.put_line(l_string); /* EXCEPTION  WHEN OTHERS THEN    RETURN(SQLERRM);*/
END;

–此段引用自:http://catonisland.cn/post/138.html