如何在 PL/SQL 中始终在数字上显示两位小数

How to always show two decimal places on a number in PL/SQL

提问by Jchieppa

Folks,

各位，

I have a existing stored procedure that I'm trying to update to always show two decimal places even when it's a whole number or single decimal number.

我有一个现有的存储过程，我试图更新它以始终显示两位小数，即使它是整数或单个十进制数。

This stored procedure builds out a message that has to show v_credit_amtas a two decimal number, yet the value assigned to v_credit_amtcan be either a whole number or single decimal or a two decimal value i.e. 175 should display as 175.00, 250.5 should display as 250.50, 395.95 should display as 395.95.

此存储过程生成的消息必须显示v_credit_amt为两位十进制数，但分配给的值v_credit_amt可以是整数或一位十进制数或两位十进制值，即 175 应显示为 175.00，250.5 应显示为 250.50、395.95应显示为 395.95。

Here is the relevant stock pl/sql

这是相关的股票pl/sql

v_credit_amt        number(22,2);
select NVL(sit_credit_limit,0)
  into v_credit_amt
  from sites
 where sit_pkey = p_site_id;

I thought that formatting via to_char during the select would solve this, ala

我认为在选择期间通过 to_char 格式化可以解决这个问题，唉

select NVL(to_char(sit_credit_limit, 'fm99999.00'),0)
  into v_credit_amt
  from sites
 where sit_pkey = p_site_id;

--v_credit_amt := to_char(v_credit_amt, 'fm99999.00');
insert into SQL_TXT values (v_credit_amt);
commit;

As you can see from the commented out line above, I've also tried it once the v_credit_amt variable is defined

从上面注释掉的行中可以看出，一旦定义了 v_credit_amt 变量，我也尝试过

I've also tried it with the to_number function

我也用 to_number 函数试过了

select NVL(to_number(sit_credit_limit, '99999.00'),0)
  into v_credit_amt
  from sites
 where sit_pkey = p_site_id;

However if the value stored in the sit_credit_limit column is a whole number without decimals, or a number like 250.5 v_credit_amtshows the same in all cases when querying SQL_TXT table I'm inserting to for debugging.

但是，如果存储在 sat_credit_limit 列中的值是一个没有小数的整数，或者像 250.5 这样的数字v_credit_amt在查询我插入用于调试的 SQL_TXT 表时在所有情况下都显示相同。

SQL> select * from SQL_TXT;

COL1
--------------------------------------------------------------------------------
175
250.5

This particular event simply concatenates multiple message portions into a single long message string that is returned i.e.

这个特定的事件只是将多个消息部分连接成一个长消息字符串，然后返回，即

if p_message_id = '14' then
   v_message := 'Comtrol Check In Message Posted';
   v_string  := v_string || '008' || lpad(length(v_fname || ' ' || v_lname), 3, '0') || v_fname || ' ' || v_lname;
   v_string  := v_string || '059' || lpad(1, 3, '0') || '0';
   --v_string  := v_string || '089' || lpad(1, 3, v_credit_amt) || to_char(v_credit_amt);
   v_string  := v_string || '089' || lpad(length(to_char(v_credit_amt, 'fm9999999.00')), 3, '0') || to_char(v_credit_amt, 'fm9999999.00');
   v_string  := v_string || '106' || lpad(length(nvl(v_acct_number,'')), 3, '0') || v_acct_number;
   --v_string  := v_string || '106' || v_acct_number;
   v_string  := v_string || '164' || lpad(1, 3, '0') || '0';
   v_string  := v_string || '174' || lpad(length(v_rm_phone_num), 3, '0') || v_rm_phone_num;
   v_string  := v_string || '175' || lpad(length(v_rm_id), 3, '0') || v_rm_id;
   v_string  := v_string || '183' || lpad(1, 3, '0') || '0';
endif;

However I cannot get the final string to properly have two decimals in all use cases.

但是，在所有用例中，我无法使最终字符串正确地具有两位小数。

For example if the value in the db is 125 I get this for a final string

例如，如果 db 中的值是 125，我会得到一个最终字符串

144450034629999008009Bob Smith05900100{89006125}1060073307542164001017400340917500 34091830010

144450034629999008009Bob Smith05900100{89006125}1060073307542164001017400340917500 34091830010

however it should have been

然而它应该是

144450034629999008009Bob Smith05900100{89007125.00}1060073307542164001017400340917500 34091830010

144450034629999008009Bob Smith05900100{89007125.00}1060073307542164001017400340917500 34091830010

Sorry for the formatting above, I can't see how to bold a section without a code block so I've highlighted the relative portions instead {}

抱歉上面的格式，我看不到如何在没有代码块的情况下加粗部分，所以我突出显示了相关部分，而不是 {}

What am I missing if I need to always display a number to two decimals even if a whole or 1 decimal value is given?

即使给出了整数或 1 个十进制值，如果我需要始终将数字显示为两位小数，我会遗漏什么？

回答by Maheswaran Ravisankar

You actually, have to insert the data with formatting. (Assuming the targeted column is VARCHAR) What ever format you fetch and put into a NUMBERvariable, will be a NUMBER.

实际上，您必须插入带有格式的数据。（假设目标列是VARCHAR）您获取并放入NUMBER变量的任何格式都将是NUMBER.

A number doesn't have a format to be saved after-all. Only for display, the formatting comes into picture.

毕竟，数字没有要保存的格式。仅用于显示，格式进入图片。

insert into SQL_TXT values (TO_CHAR(v_credit_amt,'FM99999.00'));
commit;

If the INSERT-edcolumn is NUMBERagain.

如果再次插入 INSERT列NUMBER。

You still want to go with

你还想和

SELECT TO_CHAR(COL1,'FM99999.00') FROM SQL_TEXT;