oracle 如何找出预言机号码的位数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6941413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find out the number of digits of an oracle number
提问by Ioan Paul Pirau
I have a table in oracle that looks like this:
我在 oracle 中有一个表，如下所示：
name | type | nullable
------------------------------------------
person_name | varchar(20) | yes
weight_coeficient | number | yes
...
How can I figure out how many digits a value of weight_coeficienthas ? For example:
我怎样才能算出weight_coeficient 的值有多少位数？例如：
3.0123456789has 11 digits (precision= 11) and 10 digits after the decimal (scale= 10)
3.0123456789具有11位（精度= 11）和10个数字的十进制后（标度= 10）
Is there any sql command/function that does that, something like GetPrecision( select.. )that returns 11 ?
是否有任何 sql 命令/函数可以做到这一点，比如GetPrecision( select.. )返回 11 ？
Note also that the definition of the table does not specify scale and precision. So as far as I know the maximum precision is applied for all the numbers. So I'm not interested in finding out the precision (= 48) of the definition, but the precision of a specific value in the table. Is that possible just using oracle commands ?
另请注意，表的定义未指定比例和精度。所以据我所知，最大精度适用于所有数字。所以我对找出定义的精度（= 48）不感兴趣，而是对表中特定值的精度感兴趣。是否可以仅使用 oracle 命令？
Thank you in advance,
JP
提前谢谢你，
JP
回答by Kevin Burton
How about....
怎么样....
SELECT LENGTH(TRANSLATE(TO_CHAR(3.0123456789),'1234567890.-','1234567890'))
FROM dual
The translate simply removes the non numeric characters .-
翻译只是删除非数字字符 .-
回答by Guido Leenders
Slight improvement is to use:
稍微改进是使用：
length(to_char(:number)) - coalesce(length(translate(to_char(:number), 'x1234567890', 'x')), 0)
When you to_char inserts an 'E' for exponential or a different grouping character or decimal separator, it will still work.
当您 to_char 为指数或不同的分组字符或小数点分隔符插入 'E' 时，它仍然有效。
回答by mancini0
Use this script to generate a the appropriate casts for your data. I wrote this to help move NUMBER data with unspecified precision & scale in Oracle to Postgres, via Kafka-Connect. Kafka-Connect lets one select data to copy over to another database via query, but since we did not have our number precision set on the Oracle side, Kafka-Connect was inserting everything into Postgres as a big decimal. I.e, an Oracle 1 would be inserted as 1.000000000<30 decimals>.
使用此脚本为您的数据生成适当的转换。我写这篇文章是为了帮助通过 Kafka-Connect 将 Oracle 中未指定精度和规模的 NUMBER 数据移动到 Postgres。Kafka-Connect 允许一个选择的数据通过查询复制到另一个数据库，但由于我们没有在 Oracle 端设置数字精度，Kafka-Connect 将所有内容作为大十进制数插入 Postgres。即，Oracle 1 将插入为 1.000000000<30 位小数>。
SET SERVEROUTPUT ON;
DECLARE
Q1 VARCHAR2 (4000 CHAR);
str VARCHAR2 (300 CHAR);
BEGIN
FOR rec
IN (SELECT column_name AS column_name
FROM all_tab_cols
WHERE owner = 'YOUR_SCHEMA'
AND TABLE_NAME = 'YOUR_TABLE'
AND DATA_TYPE = 'NUMBER')
LOOP
q1 :=
'SELECT REPLACE(''cast( ''
|| :1
|| '' as NUMBER(''
|| TO_CHAR (MAX (LENGTH_OF_DECIMAL) + MAX (length_of_integer))
|| '',''
|| TO_CHAR (MAX (length_of_decimal))
|| ''))'',''NUMBER(0,0)'',''NUMBER'') as result
FROM (SELECT charnum,
CASE
WHEN INSTR (charnum, ''.'') > 0
THEN
SUBSTR (charnum, INSTR (charnum, ''.'') + 1)
ELSE
NULL
END
AS decimal_part,
CASE
WHEN INSTR (charnum, ''.'') > 0
THEN
REPLACE (
REPLACE (
SUBSTR (charnum,
1,
INSTR (charnum, ''.'') - 1),
''-'',
''''),
''+'',
'''')
ELSE
REPLACE (REPLACE (charnum, ''-'', ''''), ''+'', '''')
END
AS integer_part,
CASE
WHEN INSTR (charnum, ''.'') > 0
THEN
LENGTH (
SUBSTR (charnum, INSTR (charnum, ''.'') + 1))
ELSE
0
END
AS length_of_decimal,
CASE
WHEN INSTR (charnum, ''.'') > 0
THEN
NVL (
LENGTH (
REPLACE (
REPLACE (
SUBSTR (charnum,
1,
INSTR (charnum, ''.'') - 1),
''-'',
''''),
''+'',
'''')),
0)
ELSE
NVL (
LENGTH (
REPLACE (REPLACE (charnum, ''-'', ''''),
''+'',
'''')),
0)
END
AS length_of_integer
FROM (SELECT cast(col_name2 AS VARCHAR2 (50))
AS charnum
FROM YOUR_TABLE)) T1';
q1 := REPLACE (q1, 'col_name2', rec.column_name);
EXECUTE IMMEDIATE REPLACE (q1, 'col_name2', rec.column_name)
INTO str
USING rec.column_name;
DBMS_OUTPUT.PUT_LINE (str);
END LOOP;
END;
/
Test cases:
测试用例：
create table precision_tester(test_val number);
--change YOUR_TABLE to PRECISION_TESTER
--将 YOUR_TABLE 更改为 PRECISION_TESTER
(run script, verify output, and delete from precision_tester after each test)
（运行脚本，验证输出，并在每次测试后从 precision_tester 中删除）
insert into precision_tester(test_val) values (null);
insert into precision_tester(test_val) values (+1);
insert into precision_tester(test_val) values (-1);
insert into precision_tester(test_val) values (-1.00);
insert into precision_tester(test_val) values (+1.001);
insert into precision_tester(test_val) values (+12.001);
Yields the below dbms output:
产生以下 dbms 输出：
cast( TEST_VAL as NUMBER)
cast( TEST_VAL as NUMBER(1,0))
cast( TEST_VAL as NUMBER(1,0))
cast( TEST_VAL as NUMBER(1,0))
cast( TEST_VAL as NUMBER(4,3))
cast( TEST_VAL as NUMBER(5,3))
