oracle 无函数的 PL/SQL 基础转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3086083/
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
PL/SQL base conversion without functions
提问by Sarah Vessels
Is there any way to convert decimal to binary, or binary to decimal, in Oracle 10g without having to first define a function? I have limited database access (SELECT only) and all the solutions for this I've found online seem to involve CREATE FUNCTION, which does not work for me.
在 Oracle 10g 中，有没有什么方法可以将十进制转换为二进制，或将二进制转换为十进制，而无需先定义函数？我的数据库访问权限有限（仅限 SELECT），我在网上找到的所有解决方案似乎都涉及CREATE FUNCTION，这对我不起作用。
回答by Adam Musch
If hexadecimal is good enough, then TO_CHAR and TO_NUMBER can work:
如果十六进制足够好，那么 TO_CHAR 和 TO_NUMBER 可以工作：
SQL> select to_char(31, '0x') from dual;
TO_
---
1f
SQL> select to_number('1f', '0x') from dual;
TO_NUMBER('1F','0X')
--------------------
31
You may be able to use the RAWTOHEX()and HEXTORAW()functions to make the hex to binary transition as well.
您也可以使用RAWTOHEX()和HEXTORAW()函数进行十六进制到二进制的转换。
回答by APC
Frank Zhou who specializes in gnarly SQL puzzlers has devised a pure SQL solution for this problem. You can find it on his OraQA site. But be warned: it is reallygnarly.
专门研究复杂 SQL 谜题的 Frank Zhou 为这个问题设计了一个纯 SQL 解决方案。您可以在他的 OraQA 网站上找到它。但请注意：它真的很粗糙。
update
更新
Original link to OraQA is broken: The Wayback Machine has an archived version here.
OraQA 的原始链接已断开：Wayback Machine 在此处有一个存档版本。
回答by FrustratedWithFormsDesigner
Can you execute PL/SQL in an SQLPlus script like this:
你能像这样在 SQLPlus 脚本中执行 PL/SQL：
declare
procedure bin_2_dec(/*some parameters here*/) is
begin
/*do computation and print result*/
end;
begin
bin_2_dec('11110000');
end;
/
I'm not sure but I don't think the function will be created permanently in the database, I think it will only exist temporarily for the duration of the script so this mightwork. It's worth a shot, right? ;)
我不确定，但我认为该函数不会在数据库中永久创建，我认为它只会在脚本运行期间暂时存在，因此这可能会起作用。值得一试，对吧？;)
Or if that doesn't work, you could SELECT ... from dualto convert, though that will probably be awkward and will only work if you know the number of digits - maybe (I'll try to throw it together if I can get a few minutes, and if it's possible).
或者，如果这不起作用，您可以SELECT ... from dual进行转换，尽管这可能会很尴尬，并且只有在您知道位数的情况下才有效 - 也许（如果我能得到几分钟，我会尝试将它放在一起，并且如果它是可能的）。
回答by Allan
A crude, but straight-forward solution for decimal-to-binary:
十进制到二进制的粗略但直接的解决方案：
SELECT REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(TO_CHAR (100,'FMxxx'),
'0','0000'),
'1','0001'),
'2','0010'),
'3','0011'),
'4','0100'),
'5','0101'),
'6','0110'),
'7','0111'),
'8','1000'),
'9','1001'),
'A','1010'),
'B','1011'),
'C','1100'),
'D','1101'),
'E','1110'),
'F','1111')
FROM DUAL;
Binary-to-decimal would be trickier. You might be able to use connect byto split the string into 4-character segments, convert them in a similar fashion, then concatenate them back together (a second connect byusing SYS_CONNECT_BY_PATH?), but that's a little too tedious for me to work out tonight.
二进制到十进制会更棘手。您也许可以使用connect by将字符串拆分为 4 个字符的段，以类似的方式转换它们，然后将它们重新连接在一起（connect by使用SYS_CONNECT_BY_PATH?
On second thought, here's the Binary-to-decimal solution (I'm a sucker for connect byproblems):
再想一想，这是二进制到十进制的解决方案（我是connect by问题的傻瓜）：
SELECT TO_NUMBER(
REPLACE (
SYS_CONNECT_BY_PATH (octet, '!'),
'!', ''),
'xxxxxx')
FROM (SELECT CASE SUBSTR
(LPAD (a,
CEIL (LENGTH(a)/4)*4, '0'),
(LEVEL-1)*4+1, 4)
WHEN '0000'
THEN '0'
WHEN '0001'
THEN '1'
WHEN '0010'
THEN '2'
WHEN '0011'
THEN '3'
WHEN '0100'
THEN '4'
WHEN '0101'
THEN '5'
WHEN '0110'
THEN '6'
WHEN '0111'
THEN '7'
WHEN '1000'
THEN '8'
WHEN '1001'
THEN '9'
WHEN '1010'
THEN 'A'
WHEN '1011'
THEN 'B'
WHEN '1100'
THEN 'C'
WHEN '1101'
THEN 'D'
WHEN '1110'
THEN 'E'
WHEN '1111'
THEN 'F'
END AS octet,
LEVEL AS seq,
CEIL (LENGTH(a)/4) AS max_level
FROM (SELECT '101010101010101010' AS a
FROM DUAL)
CONNECT BY LEVEL <= CEIL(LENGTH(a)/4))
WHERE LEVEL = max_level
CONNECT BY PRIOR seq = seq-1
This solution only works for one row at a time as currently written. To make it work with multiple rows, you'd need to add some sort of unique identifier to the outermost connect by.
此解决方案一次仅适用于当前编写的一行。要使其适用于多行，您需要向最外层的connect by.
回答by Saikat Sen
I was trying this in Oracle using CONNECT BYto covert decimal to binary in a simple SELECTstatement. Finally got the desired output. You can use the below, it is working fine.
我在 Oracle 中尝试使用此方法CONNECT BY在一个简单的SELECT语句中将十进制转换为二进制。终于得到了想要的输出。你可以使用下面的，它工作正常。
WITH INPUT AS
(SELECT &N AS X FROM DUAL)
SELECT SUM(MOD(FLOOR(X*POWER(0.5,LEVEL-1)),2)*POWER(10,LEVEL-1)) AS
OUTPUT FROM INPUT CONNECT BY POWER(2,LEVEL-1)<=X;
