BOOL支持Oracle SQL [英] Bool support Oracle SQL

问题描述

这是我的一个恒定的无奈，甲骨文的PL / SQL支持布尔数据类型，而Oracle SQL不。当你要处理一个PL / SQL布尔返回值返回到你的日常SQL（下面的示例）这是在众所周知的一个很大的痛苦。

It's a constant frustration of mine that Oracle PL/SQL supports the bool data-type, while Oracle SQL does not. It's a big pain in the proverbial when you want to process a PL/SQL boolean return-value back into your everyday SQL (example below).

连问汤姆网站是漠不关心这个错配，报告，你应该code布尔列作为固定值'Y'/'N'CHAR 列，这是这么多不同层次的，我不知道从哪里开始批评它了这样的黑脸出答案。事实上，这种反应的唯一可取之处是，（据我最近发现的），很多其他数据库引擎不支持布尔数据类型无论是事实。

Even the ask-Tom website is blasé about this misfit, reporting that you should code boolean columns as fixed-values 'Y'/'N' CHAR columns, which is a such a bad cop-out answer on so many different levels that I don't know where to start criticising it. In fact, the only redeeming quality of this response is the fact that (as far as I've recently discovered), many other database-engines don't support the boolean data-type either.

总之 - 问题...

Anyhow - the question...

我有一个变通以下问题（尽管混乱和冗长的），所以我提出这个问题出于好奇而不是必需品。但是，那令我感到奇怪更多的几件事情之一就是聪明的程序员的创造力，所以这里的希望你可以想出一个解决方案，下面的内容。

I have a work-around for the following problem (albeit messy and verbose), so I'm asking this question out of curiosity rather than necessity. But one of the few things that surprises me any more is the ingenuity of clever programmers, so here's hoping that one of you can come up with a solution to the following.

在下面的示例，函数 stock_pkg.is_in_stock（）（这是我的应用程序的一个固有部分）返回一个BOOL值，渲染SQL无效（请记住， SQL不支持BOOL）：

In the following sample, the function stock_pkg.is_in_stock() (which is an inherent part of my application) returns a BOOL value, rendering the SQL invalid (remember, SQL doesn't support BOOL):

SELECT part_no, stock_pkg.is_in_stock(part_no) in_stock
FROM   parts_table

我需要的是找到使用上述函数调用生成格式的有效字符串（VARCHAR）输出的方式：

What I need is to find a way of using the above function-call to generate a valid string (varchar) output of the format:

PART_NO IN_STOCK
------- ------------
AA      YES
BB      NO
CC      NO

（您可以替代是/否'的'真/假'，'绿/红'，'保守党/劳动，甚至数字1/0，我才不在乎 - 只是，只要产量下降到一个的两种不同的类别。）

(You may substitute 'yes/no' for 'true/false', 'green/red', 'tory/labour' or even numeric 1/0 for all I care - just so long as the output falls into one of two distinct categories.)

不幸的是，我没有特权改写原来的函数返回一个不同的数据类型。除此之外，还有成千上万的各地较大的应用程序点缀这样的功能，使其不切实际重写他们。

Unfortunately, I don't have the privilege to rewrite the original function to return a different data-type. And besides, there are thousands of functions like this dotted around the larger application, making it impractical to rewrite them all.

因此​​，在这个意义上说，该解决方案必须是一个通用一个（即不特定于该函数调用）。例如，它是不够的重写功能 stock_pkg.is_in_stock_chr（），因为这将意味着不必在我的申请重新写所有其他类似的功能了。

So in this sense, the solution must be a 'generic' one (i.e. not specific to this function call). For example, it is not sufficient to rewrite the function as stock_pkg.is_in_stock_chr(), because that would mean having to re-write all the other similar functions in my application too.

我已经尝试过：

SELECT part_no,
       CASE WHEN stock_pkg.is_in_stock(part_no) THEN 'y' ELSE 'n' END in_stock
FROM   parts_table

甚至我自己的包装功能：

and even my own wrapper function:

SELECT part_no,
       my_bool_to_str(stock_pkg.is_in_stock(part_no)) in_stock
FROM   parts_table

不过，即使包装等功能性结构里面布尔似乎并没有被Oracle SQL（至少在Oracle 10g中）被允许

But even wrapping booleans inside other functional constructs doesn't seem to be allowed by Oracle SQL (at least not in Oracle 10g).

还有写 IN_STOCK 列内的子选择的选择，但可能得到极端的例子过于复杂过了，也将是具体案件。

There's also the option of writing a sub-select inside the in_stock column, but that could get excessively complicated in extreme examples too, and would also be case-specific.

正如我所说，我希望有一个巧妙的解决外面的某个地方（或者至少是一个非常简单的，我碰巧忽略）。

As I say, I hope there's an ingenious solution out there somewhere (or at least a very simple one which I happen to have overlooked).

感谢您的时间。

推荐答案

您可以编写自己的包装是这样的：

You can write your own wrapper like this:

CREATE OR REPLACE FUNCTION my_bool_to_str(f varchar2) RETURN VARCHAR2 IS

  b varchar2(2);

BEGIN

  EXECUTE IMMEDIATE 'declare bl boolean; begin bl := ' || f ||
                    '; if bl then :1 := ''y''; else :1 := ''n''; end if; end;'
    using out b;

  return b;

END;

然后，你可以这样调用它：

Then you can call it like this:

SELECT part_no,
       my_bool_to_str('stock_pkg.is_in_stock('|| part_no|| ')') in_stock
FROM   parts_table

从您的包装不同的是，它得到一个varchar作为输入，而不是其中的SQL引擎不能识别一个布尔

The difference from your wrapper is that it gets a varchar as input and not a boolean which the SQL engine doesn't recognize

这篇关于BOOL支持Oracle SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！