“布尔"Oracle 存储过程的参数

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

我知道 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;
   ...

相关文章