SQL 在Oracle中把整数转换成二进制|极客教程

SQL 在Oracle中把整数转换成二进制，在 Oracle 数据库中，你想把一个整数转换为二进制形式。例如，你希望返回 EMP 表里的所有工资，以二进制形式显示的工资也要作为结果集的一部分被返回。

SQL 在Oracle中把整数转换成二进制问题描述

在 Oracle 数据库中，你想把一个整数转换为二进制形式。例如，你希望返回 EMP 表里的所有工资，以二进制形式显示的工资也要作为结果集的一部分被返回，如下所示。

ENAME        SAL SAL_BINARY
---------- ----- --------------------
SMITH        800 1100100000
ALLEN       1600 11001000000
WARD        1250 10011100010
JONES       2975 101110011111
MARTIN      1250 10011100010
BLAKE       2850 101100100010
CLARK       2450 100110010010
SCOTT       3000 101110111000
KING        5000 1001110001000
TURNER      1500 10111011100
ADAMS       1100 10001001100
JAMES        950 1110110110
FORD        3000 101110111000
MILLER      1300 10100010100

SQL 在Oracle中把整数转换成二进制解决方案

本解决方案会用到 MODEL 子句，因此需要运行在 Oracle Database 10g 或更新的版本上。MODEL 具有迭代和以数组形式访问行值的能力，选择它来解决本问题是顺理成章的做法。（此处假设我们必须使用 SQL 解决本问题，如果能以函数的形式存在则更加适合。）就像本书的其他实例一样，或许你一时看不到那些解决方案的实际应用场景，但是我建议你重点关注其中展示的技术和技巧。关于 MODEL 子句，我们需要认识到它具有面向过程编程的能力，同时又没有丢掉 SQL 原有的面向集合编程的能力。因此，你可能会说：“我绝对不会用 SQL 做这些事。”这也没有关系。实际上，我不会建议你应该这样做或者不应该那样做。
我只是提醒你把注意力放在本解决方案展示的技术和技巧上，这样今后遇到更适当的场景时，它自然而然就会派上用场。
下面给出的解决方案从 EMP 表中取出了全部 ENAME 和 SAL，同时又以标量子查询的形式调用了 MODEL 子句。（该标量子查询的作用类似于一个独立存在的函数，接受输入，启动处理过程，最后返回一个值，非常像一个函数做的事。）

 1 select ename,
 2        sal,
 3        (
 4        select bin
 5          from dual
 6         model
 7         dimension by ( 0 attr )
 8         measures ( sal num,
 9                    cast(null as varchar2(30)) bin,
10                    '0123456789ABCDEF' hex
11                  )
12         rules iterate (10000) until (num[0] <= 0) (
13           bin[0] = substr(hex[cv()],mod(num[cv()],2)+1,1)||bin[cv()],
14           num[0] = trunc(num[cv()]/2)
15         )
16        ) sal_binary
17   from emp

SQL 在Oracle中把整数转换成二进制扩展知识

我在前面的“解决方案”部分中提到过，上述解决方案最好能以函数形式存在。事实上，本实例的灵感确实来自一个函数。我把一个名为 TO_BASE 的函数做了些改编从而写出了本实例，该函数的作者是 Oracle 公司的 Tom Kyte。就像你不会在实际工作中用到本书的其他许多实例一样，本实例的代码也可能对你没有什么实用价值，但不可否认它很好地展示了 MODEL 子句的部分功能，例如迭代和以数组形式访问行值的能力。
为了便于解释，我对上述包含 MODEL 子句的子查询做了一点轻微的改动。下面的代码基本上完全等同于上述解决方案里的那个子查询，但是我让它仅返回数字 2 的二进制形式。

select bin
  from dual
 model
 dimension by ( 0 attr )
 measures ( 2 num,
            cast(null as varchar2(30)) bin,
            '0123456789ABCDEF' hex
          )
 rules iterate (10000) until (num[0] <= 0) (
   bin[0] = substr (hex[cv()],mod(num[cv()],2)+1,1)||bin[cv()],
   num[0] = trunc(num[cv()]/2)
 )
　
BIN
----------

下面的查询输出了上述 RULES 迭代一次的返回值。

select 2 start_val,
       '0123456789ABCDEF' hex,
       substr('0123456789ABCDEF',mod(2,2)+1,1) ||
       cast(null as varchar2(30)) bin,
       trunc(2/2) num
  from dual
　
START_VAL HEX              BIN        NUM
--------- ---------------- ---------- ---
        2 0123456789ABCDEF 0            1

START_VAL 代表我们希望把它变成二进制形式的整数，本例中为 2。BIN 的值是针对 0123456789ABCDEF（别名为 HEX）执行子字符串操作的结果。NUM 值是为了测试何时退出循环。
如上述结果集所示，第一次循环的运算结果 BIN 等于 0，NUM 等于 1。由于 NUM 不满足小于或等于 0 的条件，因而会再循环一次。下面的 SQL 语句展示了下一次迭代的结果。

select num start_val,
       substr('0123456789ABCDEF',mod(1,2)+1,1) || bin bin,
       trunc(1/2) num
  from (
select 2 start_val,
       '0123456789ABCDEF' hex,
       substr('0123456789ABCDEF',mod(2,2)+1,1) ||
       cast(null as varchar2(30)) bin,
       trunc(2/2) num
  from dual
       )
　
START_VAL BIN        NUM
--------- ---------- ---
        1 10           0

下一次循环运算，针对 HEX 执行的子字符串操作结果返回了 1，它后面要接上前一次的 BIN 值 0。测试用的 NUM 现在等于 0，因此这会是最后一次迭代，而返回值 10 是数字 2 的二进制表示。如果你能理解截止目前的代码，则不妨去掉 MODEL 子句里的迭代语法，从而可以一步一步地理解最终结果是如何计算出来，如下所示。

select 2 orig_val, num, bin
  from dual
 model
 dimension by ( 0 attr )
 measures ( 2 num,
            cast(null as varchar2(30)) bin,
            '0123456789ABCDEF' hex
          )
 rules (
    bin[0] = substr (hex[cv()],mod(num[cv()],2)+1,1)||bin[cv()],
    num[0] = trunc(num[cv()]/2),
    bin[1] = substr (hex[0],mod(num[0],2)+1,1)||bin[0],
    num[1] = trunc(num[0]/2)
  )
　
ORIG_VAL NUM BIN
-------- --- ---------
       2   1 0
       2   0 10