“布尔" Oracle存储过程的参数 [英] "Boolean" parameter for Oracle stored procedure

问题描述

我知道Oracle没有用于参数的布尔类型，并且当前正在接受NUMBER类型，该类型的True/False值将为1/0(而不是'Y'/'N'CHAR (1)方法.

I'm aware that Oracle does not have a boolean type to use for parameters, and am currently taking in a NUMBER type which would have 1/0 for True/False (instead of the 'Y'/'N' CHAR(1) approach).

我不是一个非常高级的Oracle程序员，但是在进行了一些挖掘和阅读一些ASKTOM帖子之后，似乎可以使用诸如以下列的格式来限制字段:

I'm not a very advanced Oracle programmer, but after doing some digging and reading some ASKTOM posts, it seems like you can restrict a field using a format for the column like:

MyBool NUMBER(1) CHECK (MyBool IN (0,1))

是否有一种方法可以将相同类型的检查约束应用于存储过程的输入参数?我想将可能的输入限制为0或1，而不是在收到输入后显式地检查它.

Is there a way to apply the same sort of a check constraint to an input parameter to a stored procedure? I'd like to restrict the possible inputs to 0 or 1, rather than checking for it explicitly after receiving the input.

推荐答案

您可以使用布尔值作为存储过程的参数:

You can use Booleans as parameters to stored procedures:

procedure p (p_bool in boolean) is...

但是，您不能在SQL中使用布尔值，例如选择语句:

However you cannot use Booleans in SQL, e.g. select statements:

select my_function(TRUE) from dual; -- NOT allowed

对于数字参数，无法在其上声明性地添加检查约束"，您将必须编写一些验证代码，例如

For a number parameter there is no way to declaratively add a "check constraint" to it, you would have to code some validation e.g.

procedure p (p_num in number) is
begin
   if p_num not in (0,1) then
      raise_application_error(-20001,'p_num out of range');
   end if;
   ...

这篇关于“布尔" Oracle存储过程的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！