如何在PL/SQL中始终显示数字的小数点后两位 [英] How to always show two decimal places on a number in PL/SQL
问题描述
伙计，
我有一个现有的存储过程，我试图将其更新为始终显示两位小数，即使它是整数或单个小数.
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.
此存储过程生成一条消息，该消息必须将v_credit_amt显示为两位十进制数字，但是分配给v_credit_amt的值可以是整数或单个十进制或两位十进制值
即175应该显示为175.00，250.5应该显示为250.50，395.95应该显示为395.95.
This stored procedure builds out a message that has to show v_credit_amt as a two decimal number, yet the value assigned to v_credit_amt can 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.
这是相关的库存pl/sql
Here is the relevant stock 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;
我认为在选择过程中通过to_char格式化会解决此问题，
I thought that formatting via to_char during the select would solve this, ala
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;
从上面的注释行中可以看到，一旦定义了v_credit_amt变量，我也尝试过
As you can see from the commented out line above, I've also tried it once the v_credit_amt variable is defined
我也尝试过使用to_number函数
I've also tried it with the to_number function
select NVL(to_number(sit_credit_limit, '99999.00'),0)
into v_credit_amt
from sites
where sit_pkey = p_site_id;
但是，如果存储在sit_credit_limit列中的值是不带小数的整数，或者在查询插入的SQL_TXT表时，在所有情况下类似250.5 v_credit_amt的值在所有情况下都显示相同.
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_amt shows the same in all cases when querying SQL_TXT table I'm inserting to for debugging.
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.
例如，如果数据库中的值为125，我将得到一个最终字符串
For example if the value in the db is 125 I get this for a final string
144450034629999008009Bob Smith05900100{89006125}1060073307542164001017400340917500
34091830010
144450034629999008009Bob Smith05900100{89006125}1060073307542164001017400340917500
34091830010
无论如何应该
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 {}
即使给出整数或1个十进制值，我总是要显示两位小数，我会丢失什么?
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?
推荐答案
实际上，您必须插入具有格式的数据. (假设目标列是VARCHAR).提取并放入NUMBER变量的任何格式都将是NUMBER.
You actually, have to insert the data with formatting. (Assuming the targeted column is VARCHAR) What ever format you fetch and put into a NUMBER variable, will be a 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;
如果 INSERT-ed 列再次为NUMBER.
您仍然想和
SELECT TO_CHAR(COL1,'FM99999.00') FROM SQL_TEXT;
这篇关于如何在PL/SQL中始终显示数字的小数点后两位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
