oracle 如何将NULL输入参数限制到oracle存储过程中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/116525/
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
How to restrict NULL input parameters into oracle stored procedure
提问by RBS
I have writtent some Oracle storedprocedures in these there are more then 20 input parameters and from them morethen 10 parameters are required , I want all with some value and do not want to accept null values for that , Is there anything that I can declare in the Procedure defination itself which can restrict null input parameter or Will I have to check for each value and Raise the exception if the required value is null ?
我已经写了一些 Oracle 存储过程，其中有超过 20 个输入参数，其中需要 10 个参数，我希望所有参数都具有某些值，并且不想为此接受空值，有什么可以声明的吗？过程定义本身可以限制空输入参数，或者我是否必须检查每个值并在所需值为空时引发异常？
采纳答案by Ed Griebel
In PL/SQL I don't know of a way around checking each one.
在 PL/SQL 中，我不知道检查每一个的方法。
If you are calling the stored procedure from an external library, that library might have that functionality. This is probably not likely because frequently NULL input parameters are required.
如果您从外部库调用存储过程，则该库可能具有该功能。这可能不太可能，因为经常需要 NULL 输入参数。
You could make a helper PL/SQL procedure that, given a value, will raise an exception if it is null to save on redundant code. You could then write a chunk of perl/python/groovy that would slurp up your procedure declaration and crank out these calls to your null check procedure.
您可以创建一个辅助 PL/SQL 过程，给定一个值，如果它为空，则会引发异常以节省冗余代码。然后，您可以编写一大块 perl/python/groovy，它会包含您的过程声明并将这些调用发送到您的空检查过程。
回答by Neil Vass
I know this is an old question, but there is another option (described here):
我知道这是一个老问题，但还有另一种选择（在此处描述）：
SUBTYPE varchar2_not_null IS VARCHAR2 NOT NULL;
You can define this type (and number_not_null, etc) either in the same package as your stored procedures, or in their own package if you want to use them in lots of places. You can then declare parameters of being these types.
number_not_null如果您想在很多地方使用它们，您可以在与存储过程相同的包中或在它们自己的包中定义此类型（和等）。然后，您可以声明这些类型的参数。
If NULLgets passed as an argument, you'll get a very useful error message:
如果NULL作为参数传递，您将收到一条非常有用的错误消息：
cannot pass NULL to a NOT NULL constrained formal parameter
