oracle中的千位分隔符功能? [英] Thousand Seperator function in oracle?

问题描述

当我执行这个函数时，它被编译并且没有给出任何错误，但是在 select 语句中使用该函数时它给出了错误.请帮我解决这个问题.选择查询是这样的.

When i am executing this function it is compiled and doesn't give any errors,but when using the function in select statement it is giving error. please help me about this . the select query is like this.

select numericformat(facval,5) from dual;

select numericformat(facval,5) from dual;

 create or replace FUNCTION NumericFormat
  (num IN decimal,
   decimals IN int 
  ) 
RETURN VARCHAR2
IS
 ReturnVal VARCHAR(50) ;
 sPDecFormat VARCHAR(20) ;
 sDecFormat VARCHAR(50) ;
 counter INT;
BEGIN
   counter := 4 ;
   sPDecFormat :='.00';
   WHILE counter<=decimals
     LOOP
        IF counter > 2 then
        sPDecFormat :=sPDecFormat + '0';
          counter :=counter+1;
     END IF;
     END LOOP;
     sDecFormat := '###0' + sPDecFormat ;
    ReturnVal := TO_CHAR(num,sdecformat);
RETURN ReturnVal;
END NUMERICFORMAT;

错误是错误报告:

SQL Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "ACC07.NUMERICFORMAT", line 21
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

推荐答案

counter := 2 ;
   sPDecFormat :='.00';
     WHILE counter<=decimals
     LOOP
      IF counter > 2 then
        sPDecFormat :=sPDecFormat+'0';
          counter :=counter+1;
      END IF;   
      END LOOP;

您的函数将进入无限循环，永远不会退出.虽然它可以编译，但这并不意味着该函数可以正常工作，因为问题会在运行时发生.

Your function will go into an infinite LOOP an will never come out of it. Though it compiles, that doesn't mean the function would work fine, since the issue will occur at run time.

WHILE 条件始终为 TRUE，并且计数器永远不会增加，因为函数永远不会进入 IF 条件.

The WHILE condition is always TRUE, and the counter never increments, since the function never goes into the IF condition.

你已经设置了 counter :=2 然后你的 IF 条件是:

You have set counter :=2 and then your IF condition is:

IF 计数器 > 2 然后

IF counter > 2 then

这怎么可能是真的?2 永远不会大于 2，因此 计数器永远不会增加，因为它位于 IF-END IF 块内.

How could it ever be true? 2 is never greater than 2, therefore the counter is never incremented, since you have it inside the IF-END IF block.

当你执行这个函数时，它永远不会跳出无限循环.

When you execute the function, it never comes out of the infinite loop.

满足您的要求

oracle 中的千位分隔符函数?

Thousand Seperator function in oracle?

当 Oracle 已经为您提供千位分隔符时，您为什么要重新发明轮子.

Why do you want to reinvent the wheel when Oracle already provides you the thousand separator.

来自文档，

• 元素:G

• Element : G

示例:9G999

描述:返回指定位置的组分隔符(NLS_NUMERIC_CHARACTER 参数的当前值).您可以在数字格式模型中指定多个组分隔符.

Description : Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.

例如

SQL> SELECT TO_CHAR(sal,'999G999') FROM emp;

TO_CHAR(
--------
     800
   1,600
   1,250
   2,975
   1,250
   2,850
   2,450
   3,000
   5,000
   1,500
   1,100
     950
   3,000
   1,300

14 rows selected.

如果希望函数名与SQL Server函数名相同，那么只需在Oracle数据库中创建一个同名的用户自定义函数即可.逻辑与上面的查询相同.

If you want the function name to be same as that of SQL Server function, then just create a user-defined function in Oracle database with the same name. The logic would be same as the above query.

例如

SQL> CREATE OR REPLACE FUNCTION NumericFormat(
  2        col NUMBER)
  3      RETURN VARCHAR2
  4    AS
  5      o_num VARCHAR2(20);
  6  BEGIN
  7      o_num:=TO_CHAR(col,'999G999');
  8      RETURN o_num;
  9  END;
 10  /

Function created.

SQL>
SQL> sho err
No errors.
SQL>

让我们执行函数:

SQL> SELECT NumericFormat(sal) FROM emp;

NUMERICFORMAT(SAL)
----------------------------------------------
     800
   1,600
   1,250
   2,975
   1,250
   2,850
   2,450
   3,000
   5,000
   1,500
   1,100
     950
   3,000
   1,300

14 rows selected.

SQL>

这篇关于oracle中的千位分隔符功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！