Oracle 存储过程的“布尔”参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4079662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Boolean" parameter for Oracle stored procedure
提问by Jay S
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 没有用于参数的布尔类型，并且目前正在接受一个 NUMBER 类型，它的真/假为 1/0（而不是 'Y'/'N' CHAR(1)方法）。
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:
我不是一个非常高级的 Oracle 程序员，但是在进行了一些挖掘和阅读了一些 ASKTOM 帖子之后，似乎您可以使用列格式来限制字段，例如：
MyBool NUMBER(1) CHECK (MyBool IN (0,1))
MyBool NUMBER(1) CHECK (MyBool IN (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.
有没有办法将相同类型的检查约束应用于存储过程的输入参数？我想将可能的输入限制为 0 或 1，而不是在收到输入后明确检查它。
回答by Tony Andrews
You canuse Booleans as parameters to stored procedures:
您可以使用布尔值作为存储过程的参数：
procedure p (p_bool in boolean) is...
However you cannot use Booleans in SQL, e.g. select statements:
但是你不能在 SQL 中使用布尔值，例如 select 语句：
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;
...
回答by Gary Myers
Yes and no. You can do..
是和否。你可以做..
create or replace package t_bool is
subtype t_bool_num IS PLS_INTEGER RANGE 0..1;
function f_test (i_bool_num t_bool_num) return varchar2;
end t_bool;
/
create or replace package body t_bool is
function f_test (i_bool_num t_bool_num) return varchar2 is
begin
if i_bool_num = 0 then
return 'false';
elsif i_bool_num = 1 then
return 'true';
elsif i_bool_num is null then
return 'null';
else
return to_char(i_bool_num);
end if;
end;
end t_bool;
/
The good news is that, if you do
好消息是，如果你这样做
exec dbms_output.put_line(t_bool.f_test(5));
it reports an error.
它报告错误。
The bad news is that if you do
坏消息是，如果你这样做
select t_bool.f_test(5) from dual;
then you don't get an error
那么你就不会得到错误
