oracle 动态检查变量是否在 PL SQL 中具有值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8360352/
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
Dynamically check if a variable has value in PL SQL
提问by flaria
What I need to do is check if a series of variables in a procedure have value, but the tricky part is that the list of which variables I have to check is in a table. Let me explain further:
我需要做的是检查过程中的一系列变量是否具有值，但棘手的部分是我必须检查的变量列表在表中。让我进一步解释：
We have a table where we keep all the variable names and an indicator for which variable shouldn't be null. This is so we can change in the that table which fields are required without having to change the code.
我们有一个表格，其中保存了所有变量名称和一个指示哪个变量不应为空的指标。这样我们就可以在该表中更改哪些字段是必需的，而无需更改代码。
What we want to implement is something similar to the NAME_IN built-in in forms, where you have something like: v_value := GetValue('v_variable');and that would assing the value of v_variableto v_value. And afterwards I would just check if v_valueis null. This whole thing would be inside a LOOP of a cursor that would get all the variables in the table I mentioned earlier that were marked as required.
我们想要实现的是类似于表单中内置的 NAME_IN 的东西，在那里你有类似的东西：v_value := GetValue('v_variable');并且会v_variable分配 to的值v_value。之后我会检查是否v_value为空。这整个事情将在游标的 LOOP 中，该游标将获取我之前提到的表中标记为需要的所有变量。
So far I've tried with EXECUTE IMMEDIATE to get the variable values assigned dynamically, but that doesn't work because EXECUTE IMMEDIATE runs in it's own scope and so it's not able to "see" the variables in the procedure scope.
到目前为止，我已经尝试使用 EXECUTE IMMEDIATE 来获取动态分配的变量值，但这不起作用，因为 EXECUTE IMMEDIATE 在它自己的范围内运行，因此它无法“看到”过程范围内的变量。
The other thing I've tried is PL/SCOPE which allows me to actually see if the variables exists within my scope by supplying the name, but it has no mechanism to get the values of the variables that do exist.
我尝试过的另一件事是 PL/SCOPE，它允许我通过提供名称来实际查看变量是否存在于我的范围内，但它没有获取确实存在的变量值的机制。
Well, I hope anyone can help me. Help will be greatly appreciated.
嗯，我希望任何人都可以帮助我。帮助将不胜感激。
Here is an example:
下面是一个例子：
Say I got the following table named tblConfig with two columns: variable_name and required_ind.
假设我得到了以下名为 tblConfig 的表，其中包含两列：variable_name 和 required_ind。
variable_name | required_ind
-----------------------------
var1 | Y
var2 | N
var3 | N
Then I would have a procedure called check_variables like this:
然后我会有一个名为 check_variables 的过程，如下所示：
PROCEDURE check_variables (
var1 VARCHAR2,
var2 VARCHAR2,
var3 VARCHAR2)
IS
CURSOR c_var_check IS
SELECT variable_name
FROM tblConfig
WHERE required_ind = 'Y';
BEGIN
FOR rec_var IN c_var_check LOOP
... check if rec_var.variable_name is the name of variable that has value ...
END LOOP;
END;
In this fisrt scenario, the loop would have to check if var1has value. If I changed the values of required_ind for the other variables, they would be checked too.
在这个第一个场景中，循环必须检查是否var1有值。如果我更改了其他变量的 required_ind 值，它们也会被检查。
I've read that article about soft coding... it's a good read, unfortunately in this scenario is not a choice I made as the developer. This is necessary because the table with the required config is managed by the user, not the development team.
我读过那篇关于软编码的文章……这是一本很好的读物，不幸的是，在这种情况下，我不是作为开发人员做出的选择。这是必要的，因为具有所需配置的表由用户管理，而不是开发团队。
回答by APC
PL/SQL doesn't have much in the way of reflection. There's certainly no equivalent of NAME_IN. I couldn't solve this with dynamic SQL but I have found a solution.
PL/SQL 没有太多的反射方式。肯定没有 NAME_IN 的等价物。我无法使用动态 SQL 解决此问题，但我找到了解决方案。
Here is a proecdure. It has three procedures. Note that they are all mandatory, but we can pass NULL in a parameter's slot. This of course is one of my objections to such "soft coding": it obfuscates the API. Describing a procedure is no longer sufficient to know what arguments it demands.
这是一个程序。它有三个程序。请注意，它们都是强制性的，但我们可以在参数的插槽中传递 NULL。这当然是我对这种“软编码”的反对意见之一：它混淆了 API。描述一个过程已经不足以知道它需要什么参数。
create or replace procedure do_something
(p1 in varchar2
, p2 in varchar2
, p3 in varchar2)
is
args sys.dbms_debug_vc2coll;
begin
args := new sys.dbms_debug_vc2coll(p1, p2, p3);
for r in ( select s.varname, a.position
from syscfg s
join user_arguments a
on (s.procname = a.object_name
and s.varname = a.argument_name)
where s.procname = 'DO_SOMETHING'
and s.mandatory = 'Y'
order by a.position
)
loop
if args(r.position) is null
then
raise_application_error(-20000, r.varname ||' cannot be null');
end if;
end loop;
dbms_output.put_line('Procedure executed successfully!');
end;
/
The "dynamic" parameter check works by populating a collection with the parameters in signature order. We get the position of the configured parameters by joing a data dictionary view with our config table. We then use the position as an index to the array. Note that the collection takes strings. I declared all my parameters as Varchars, but you might need to cast dates or numbers.
“动态”参数检查的工作原理是使用签名 order 中的参数填充集合。我们通过将数据字典视图与我们的配置表连接来获取配置参数的位置。然后我们使用该位置作为数组的索引。请注意，该集合采用字符串。我将所有参数声明为 Varchars，但您可能需要转换日期或数字。
So, yes, it is clunky, but "this quest of avoidance often leads towards [...] complication, convolution, and all-around unmaintainable code." :)
所以，是的，它很笨重，但是“这种逃避的追求通常会导致 [...] 复杂化、卷积和全面不可维护的代码。”:)
Here is the content of the config table:
这是配置表的内容：
SQL> select * from syscfg
2 /
PROCNAME VARNAME M
------------------------------ ------------------------------ -
DO_SOMETHING P1 Y
DO_SOMETHING P2 Y
DO_SOMETHING P3 N
SQL>
So, let's roll!
所以，让我们滚吧！
SQL> set serveroutput on
SQL> exec do_something('A', 'Y', null)
Procedure executed successfully!
PL/SQL procedure successfully completed.
SQL> exec do_something('A', null, 'X')
BEGIN do_something('A', null, 'X'); END;
*
ERROR at line 1:
ORA-20000: P2 cannot be null
ORA-06512: at "APC.DO_SOMETHING", line 24
ORA-06512: at line 1
SQL>
Looks good, but to prove there's nothing up my sleeve....
看起来不错，但要证明我没有袖手旁观......
SQL> update syscfg
set mandatory = 'N'
where varname = 'P2'
/
2 3 4
1 row updated.
SQL> select * from syscfg
2 /
PROCNAME VARNAME M
------------------------------ ------------------------------ -
DO_SOMETHING P1 Y
DO_SOMETHING P2 N
DO_SOMETHING P3 N
SQL> exec do_something('A', null, 'X')
Procedure executed successfully!
PL/SQL procedure successfully completed.
SQL>
Perhaps your clients are nutty enough to think this ultra flexiblility would be handy in other places. Well the good news is this solution could easily be extracted into a standalone procedure which takes the PROCNAME and the array as parameters.
也许您的客户足够疯狂，认为这种超强的灵活性在其他地方会很方便。好消息是这个解决方案可以很容易地提取到一个独立的过程中，该过程将 PROCNAME 和数组作为参数。
回答by someuser1
Wow, pretty strange setup (and thanks for the link APC), but since you're defining/tracking variable names in the config table, why not have the procedure update that table (or some other table) to tell you if the variable "has a value"? (assuming you mean null or not null). Also, not sure what to do with local variables that can change value (or be null/not null) depending on session level stuff (or who's executing the proc, privileges, location, etc).
哇，非常奇怪的设置（感谢链接 APC），但是既然您在配置表中定义/跟踪变量名称，为什么不让过程更新该表（或其他表）以告诉您变量“有价值”？（假设您的意思是 null 或非 null）。此外，不确定如何处理可以根据会话级别的内容（或谁正在执行 proc、权限、位置等）更改值（或为空/非空）的局部变量。
回答by user272735
This is essentially the same than APC's answer. I have just made the following modifications:
这与 APC 的回答基本相同。我刚刚做了以下修改：
- use default parameter values
- use named parameters
- separate null check logic into a dedicated procedure
- 使用默认参数值
- 使用命名参数
- 将空检查逻辑分离到专用程序中
create table syscfg (
procname varchar2(30) not null,
varname varchar2(30) not null,
mandatory varchar2(1) not null
);
insert all
into syscfg values('DO_SOMETHING', 'P_1', 'Y')
into syscfg values('DO_SOMETHING', 'P_2', 'Y')
into syscfg values('DO_SOMETHING', 'P_3', 'N')
into syscfg values('DO_SOMETHING_TWO', 'P_1', 'Y')
into syscfg values('DO_SOMETHING_TWO', 'P_2', 'Y')
into syscfg values('DO_SOMETHING_TWO', 'P_3', 'N')
into syscfg values('DO_SOMETHING_TWO', 'P_4', 'N')
into syscfg values('DO_SOMETHING_TWO', 'P_5', 'N')
select 1 from dual;
col procname for a20
col varname for a5
col mandatory for a1
select * from syscfg;
/* Supports only up to 5 parameters. */
create or replace procedure is_missing_mandatory_args (
p_procname in varchar2,
p_1 in varchar2 default null,
p_2 in varchar2 default null,
p_3 in varchar2 default null,
p_4 in varchar2 default null,
p_5 in varchar2 default null
) as
args constant sys.dbms_debug_vc2coll :=
sys.dbms_debug_vc2coll(p_1, p_2, p_3, p_4, p_5);
begin
for r in ( select s.varname, a.position
from syscfg s
join user_arguments a
on ( s.procname = a.object_name
and s.varname = a.argument_name)
where s.procname = upper(p_procname)
and s.mandatory = 'Y'
order by a.position
)
loop
if args(r.position) is null then
raise_application_error(-20000, upper(p_procname) || '.' || r.varname
||' cannot be null');
end if;
end loop;
end;
/
show errors
create or replace procedure do_something (
p_1 in varchar2 default null,
p_2 in varchar2 default null,
p_3 in varchar2 default null
) as
begin
is_missing_mandatory_args('do_something', p_1, p_2, p_3);
/* The real work takes place here. */
dbms_output.put_line('do_something() executed successfully !');
end;
/
show errors
create or replace procedure do_something_two (
p_1 in varchar2 default null,
p_2 in varchar2 default null,
p_3 in varchar2 default null,
p_4 in varchar2 default null,
p_5 in varchar2 default null
) as
begin
is_missing_mandatory_args('do_something_two', p_1, p_2, p_3, p_4, p_5);
/* The real work takes place here. */
dbms_output.put_line('do_something_two() executed successfully !');
end;
/
show errors
SQL> exec do_something(p_1 => 'foo', p_2 => 'foo');
do_something() executed successfully !
PL/SQL procedure successfully completed.
SQL> exec do_something(p_2 => 'foo');
BEGIN do_something(p_2 => 'foo'); END;
*
ERROR at line 1:
ORA-20000: DO_SOMETHING.P_1 cannot be null
ORA-06512: at "JANI.IS_MISSING_MANDATORY_ARGS", line 23
ORA-06512: at "JANI.DO_SOMETHING", line 7
ORA-06512: at line 1
SQL> exec do_something(p_1 => 'foo');
BEGIN do_something(p_1 => 'foo'); END;
*
ERROR at line 1:
ORA-20000: DO_SOMETHING.P_2 cannot be null
ORA-06512: at "JANI.IS_MISSING_MANDATORY_ARGS", line 23
ORA-06512: at "JANI.DO_SOMETHING", line 7
ORA-06512: at line 1
SQL> exec do_something_two(p_2 => 'baz', p_1 => 'buz', p_5 => 'boz');
do_something_two() executed successfully !
PL/SQL procedure successfully completed.
SQL> exec do_something_two(p_1 => 'baz');
BEGIN do_something_two(p_1 => 'baz'); END;
*
ERROR at line 1:
ORA-20000: DO_SOMETHING_TWO.P_2 cannot be null
ORA-06512: at "JANI.IS_MISSING_MANDATORY_ARGS", line 23
ORA-06512: at "JANI.DO_SOMETHING_TWO", line 9
ORA-06512: at line 1
SQL>
