布尔支持 Oracle SQL

2022-01-19 00:00:00 sql boolean oracle

Oracle PL/SQL 支持 bool 数据类型，而 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).

即使是 ask-Tom 网站也对这种不合适的情况不屑一顾，报告说您应该将布尔列编码为固定值 '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.

无论如何-问题...

我有一个解决以下问题的方法(尽管很混乱且冗长)，所以我出于好奇而不是必要而提出这个问题.但让我感到惊讶的少数几件事之一是聪明的程序员的独创性，所以在这里希望你们中的一个人能想出​​解决以下问题的办法.

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 代替我所关心的——只要输出为 1两个不同的类别.)

(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

相关文章