输入指标变量 [英] input indicator variables
问题描述
几个星期前，我提出了一个问题，即我如何为谓词条件是动态的查询创建游标。这意味着查询
可能希望拥有几个可能的谓词之一。请参考以下
查询，例如：
- 检查分行/帐户和金额
SELECT BRCH_NBR，ACCT_NBR， POST_DATE，AMOUNT，SERIAL_NBR，SEQUENCE_NBR，
POST_FLAG
来自FILM.FILM_TRANSACTIONS
WHERE BRCH_NBR = 001 AND ACCT_NBR = 1234567 AND AMOUNT = 25.00;
- 检查分行/帐号和序列号
SELECT BRCH_NBR，ACCT_NBR，POST_DATE，AMOUNT，SERIAL_NBR，SEQUENCE_NBR，
POST_FLAG
来自FILM.FILM_TRANSACTIONS
WHERE BRCH_NBR = 001 AND ACCT_NBR = 1234567 AND SERIAL_NBR = 0;
- check分支/账户以及金额和序列号
SELECT BRCH_NBR，ACCT_NBR，POST_DATE，AMOUNT，SERIAL_NBR，SEQUENCE_NBR，
POST_FLAG
来自FILM .FILM_TRANSACTIONS
WHERE BRCH_NBR = 001 AND ACCT_NBR = 1234567 AND AMOUNT = 25.00 AND
SERIAL_NBR = 1670;
使用动态SQL并建立预测以编程方式取决于
输入是一个选项，但我真的在寻找一种使用
静态SQL的方法。为什么？好吧，因为那就是我想要的！ :-)
无论如何，我正在阅读DB2 Server for VSE&虚拟机应用程序编程
手册（所有东西！）并且碰巧在谓词中使用输入指示符变量偶然发现了
的可能性。这是手册
所说的：
----------------------
....有些情况下，在谓词中设置负输入指示符
变量可以证明是有用和有效的。例如，如果
应用程序提示用户以交互方式提供将b $ b识别员工的信息（通过数字或名称），您可以设计程序
只使用一个select语句从
数据库中提取指示的员工数据。
这是伪代码：
从用户获取empno或lastname
如果输入empno，则empnoind = 0，否则empnoind = -1
如果输入姓氏，则nameind = 0，否则nameind = -1
SELECT * FROM EMPLOYEE
WHERE EMPNO =：EMPNO：EMPNOIND
或LASTNAME =：NAME：NAMEIND
----------------------
这似乎正是我正在寻找的。所以我写了一个小的
程序来测试它，看哪它似乎工作了！我发布这个
有几个原因：
1）看看我是否正确使用它（它似乎工作，但
或许是出于其他原因？）。
2）看看是否有关于为什么我不应该使用这种
编码的评论。 （老实说，如果我没有看过这个，我看过一个类似
的SELECT，我在下面的示例中我永远不知道它是什么
做什么。）
3）让其他不知道这个看似非常有用的
功能的人可用。
4）看看是否有人对我使用嵌入式SQL有任何其他意见。
（想要让它变得更好等等）
5）看看是否记录在任何地方在DB2 LUW手册中。我的测试
程序实际上是使用DB2 LUW，但DB2 Server for VSE，但我找不到
这个功能记录在LUW手册的任何地方。
无论如何，这是我的（COBOL）计划：
program-id。 filmqry。
环境部门。
配置部分。
特殊名称。
控制台是控制台。
数据部门。
工作存储部分。
copy" sqlenv.cbl"。
copy" sql.cbl"。
copy" sqlca.cbl" ;.
exec sql begin declare section end-exec。
01电影交易。
05 ft-brch-nbr pic S9（3）comp-3。
05 ft-acct-nbr pic S9（7）comp-3。
05 ft-post-date pic x（10）。
05 ft-amount pic S9（9）v99 comp-3。
05 ft-serial-nbr pic S9（9）comp-3。
05 ft-sequence-nbr pic S9（9）comp-3。
05 ft-post-flag pic x。
01指标。
05 ind pic s9（4）comp发生10.
exec sql end declare section end-exec。
77 errloc pic x（80）。
01账户。
05 brch-in pic 9（3）。
05 acct-in pic 9（7）。
77金额为x（12）。
77 serial-in pic x（9）。
77 record-status pic x。
88记录发现值''Y''。
88记录未找到值''N''。
01搜索-flags。
05 search-amount-flag pic 9.
88搜索金额''Y''。
05搜索 - serial-flag pic 9.
88 search-serial value''Y''。
exec sql声明ft_select游标为
从FILM.FILM_TRANSACTIONS选择BRCH_NBR，ACCT_NBR，POST_DATE，AMOUNT，
SERIAL_NBR，SEQUENCE_NBR，POST_FLAG
其中BRCH_NBR =：ft-brch -nbr
和
ACCT_NBR =：ft-acct-nbr
和
（
AMO UNT =：ft-amount：ind（1）
或
SERIAL_NBR =：ft-serial-nbr：ind（2）
或
（
AMOUNT =：ft-amount：ind（3）
和
SERIAL_NBR =：ft- serial-nbr：ind（4）
）
）
end-exec。
程序师。
执行连接
执行get-account
执行get-input
执行直到金额=空格
和序列输入=空格
执行主线
执行获取输入
end-perform
执行断开
退出计划。
get-account。
从命令接受账户入账-line
将brch-in移至ft-brch-nbr
将acct-in移至ft-acct-nbr
Frank Swarbrick写道：
这是伪代码：
如果empno是ent，则从用户获取empno或lastname
ered然后empnoind = 0，否则empnoind = -1
如果输入lastname然后nameind = 0，否则nameind = -1
SELECT * FROM EMPLOYEE
WHERE EMPNO =：EMPNO：EMPNOIND
或LASTNAME =：NAME：NAMEIND
我不确定它是做什么的特别是因为那些''null
指标''，所以实际上设置可能会将值变为
NULL。
SELECT * FROM EMPLOYEE
WHERE EMPENO =''somevalue''
或LASTNAME = NULL
哪些应该可以正常工作只要列不允许
为NULL。
Richard< ri **** @ Azonic.co .nz10 / 18/06 1:11 PM>>>
>
Frank Swarbrick写道：
>这是伪代码：
从用户获取empno或lastname
如果输入empno然后empnoind = 0，否则empnoind = -1
如果输入lastname则nameind = 0，否则nameind = -1
SELECT * FROM EMPLOYEE
在哪里EMPNO =：EMPNO：EMPNOIND
或LASTNAME =：NAME：NAMEIND
我不确定它是否做了什么特别的事，因为那些是'null
指标''，所以实际上设置可能会将值变为
NULL。
SELECT * FROM EMPLOYEE
WHERE EMPENO =''somevalue''
或LASTNAME = NULL
只要不允许列为空，哪个应该正常工作
。
似乎是一个空指标。在谓词中不一样
检查NULL。相反，谓词的那一部分没有被检查。
这里有更多来自我正在阅读的手册：
----------- ---------
不要在搜索条件（WHERE或HAVING
子句）中使用输入指示符变量来测试空值。测试空值的正确方法是使用
NULL谓词（如前所述）：
WHERE MGRNO为空**正确**
这将返回MGRNO为NULL的每一行。
WHERE MGRNO =：MGR：MGRIND
如果MGRIND设置为负，则使MGR null，真值是
？UNKNOWN ?,什么也不会退回。
------------------ -
上面的第一个示例如何工作是如果EMPNOIND是-1那么
谓词被解释为WHERE LASTNAME =：NAME 。如果NAMEIND是-1则
它被解释为WHERE EMPNO =：EMPNO。如果两者都不是-1那么它只是WHERE EMPNO =：EMPNO或LASTNAME =：NAME。在任何情况下，实际上都没有检查是否为
EMPNO或LASTNAME的NULL值。如果有人希望这样做（这不是我想要的那样）那么你必须检查
" = NULL"
作为另一个例子，我认为以下是*不*冗余;它确实
与上述任何一个例子的含义不同：
SELECT * FROM EMPLOYEE
WHERE EMPNO =：EMPNO：EMPNOIND
或EMPNO = NULL
或LASTNAME =：NAME：NAMEIND
或LASTNAME = NULL
Frank
---
Frank Swarbrick
高级开发人员/分析师 - 大型机应用程序
FirstBank数据公司 - Lakewood ，CO USA
Frank Swarbrick写道：
如何第一个例子如果EMPNOIND为-1，则上面似乎有效，那么
谓词被解释为WHERE LASTNAME =：NAME。如果NAMEIND是-1则
它被解释为WHERE EMPNO =：EMPNO。如果两者都不是-1那么它只是WHERE EMPNO =：EMPNO或LASTNAME =：NAME。在任何情况下，实际上都没有检查是否为
EMPNO或LASTNAME的NULL值。如果有人希望这样做（这不是我想要的那样），那么你必须检查
" = NULL"。
也许那时-1指标的含义是非价值。那么
两个测试都可以完成，而且一个测试永远不会是真的，因为没有
值可以有。
A few weeks ago I had posed a question about how one my create a cursor for
a query where the predicate condition is dynamic. Meaning that the query
might want to have one of several possible predicates. Take the following
queries, for instance:
-- check for branch/account and amount
SELECT BRCH_NBR, ACCT_NBR, POST_DATE, AMOUNT, SERIAL_NBR, SEQUENCE_NBR,
POST_FLAG
FROM FILM.FILM_TRANSACTIONS
WHERE BRCH_NBR = 001 AND ACCT_NBR = 1234567 AND AMOUNT = 25.00;
-- check for branch/account and serial number
SELECT BRCH_NBR, ACCT_NBR, POST_DATE, AMOUNT, SERIAL_NBR, SEQUENCE_NBR,
POST_FLAG
FROM FILM.FILM_TRANSACTIONS
WHERE BRCH_NBR = 001 AND ACCT_NBR = 1234567 AND SERIAL_NBR = 0;
-- check for branch/account and both amount and serial number
SELECT BRCH_NBR, ACCT_NBR, POST_DATE, AMOUNT, SERIAL_NBR, SEQUENCE_NBR,
POST_FLAG
FROM FILM.FILM_TRANSACTIONS
WHERE BRCH_NBR = 001 AND ACCT_NBR = 1234567 AND AMOUNT = 25.00 AND
SERIAL_NBR = 1670;
Using dynamic SQL and building the predicate programatically depending on
the input is one option, but I was really looking at a way to do it using
static SQL. Why? Well, because that''s what I wanted! :-)
Anyway, I was reading the DB2 Server for VSE & VM Application Programming
manual (of all things!) and just happened to stumble on the possibility of
using input indicator variables in the predicate. Here''s what the manual
says:
----------------------
....there are cases where setting up a negative input indicator
variable in the predicate can prove useful and efficient. For example, if
an
application prompts the user to interactively supply information that will
identify an employee (by either number or name), you can design the program
to use only one select-statement to extract the indicated employee data from
the
database.
Here is the pseudocode:
get either empno or lastname from user
if empno is entered then empnoind = 0, else empnoind = -1
if lastname is entered then nameind = 0, else nameind = -1
SELECT * FROM EMPLOYEE
WHERE EMPNO = :EMPNO:EMPNOIND
OR LASTNAME = :NAME:NAMEIND
----------------------
This appears to be exactly what I''m looking for. So I wrote a little
program to test it, and lo and behold it appears to work! I am posting this
for a few reasons:
1) To see if I appear to be using it correctly (it appears to work, but
perhaps for some other reason?).
2) To see if there are comments on perhaps why I should not use this kind of
coding. (Too be honest, if I had not read this and I had seen a SELECT like
I have below in my example I would never have figured out what it is
doing.)
3) To let others who don''t know about it that this seemingly quite useful
feature is available.
4) To see if anyone has any other comments on my use of imbedded SQL.
(Ideas to make it better, etc.)
5) To see if this is documented anywhere in the DB2 LUW manuals. My test
program is actually using DB2 LUW, but DB2 Server for VSE, but I can''t find
this feature documented anywhere in the LUW manuals.
Anyway, here is my (COBOL) program:
program-id. filmqry.
environment division.
configuration section.
special-names.
console is console.
data division.
working-storage section.
copy "sqlenv.cbl".
copy "sql.cbl".
copy "sqlca.cbl".
exec sql begin declare section end-exec.
01 film-transactions.
05 ft-brch-nbr pic S9(3) comp-3.
05 ft-acct-nbr pic S9(7) comp-3.
05 ft-post-date pic x(10).
05 ft-amount pic S9(9)v99 comp-3.
05 ft-serial-nbr pic S9(9) comp-3.
05 ft-sequence-nbr pic S9(9) comp-3.
05 ft-post-flag pic x.
01 indicators.
05 ind pic s9(4) comp occurs 10.
exec sql end declare section end-exec.
77 errloc pic x(80).
01 account-in.
05 brch-in pic 9(3).
05 acct-in pic 9(7).
77 amount-in pic x(12).
77 serial-in pic x(9).
77 record-status pic x.
88 record-found value ''Y''.
88 record-not-found value ''N''.
01 search-flags.
05 search-amount-flag pic 9.
88 search-amount value ''Y''.
05 search-serial-flag pic 9.
88 search-serial value ''Y''.
exec sql declare ft_select cursor for
select BRCH_NBR, ACCT_NBR, POST_DATE, AMOUNT,
SERIAL_NBR, SEQUENCE_NBR, POST_FLAG
from FILM.FILM_TRANSACTIONS
where BRCH_NBR = :ft-brch-nbr
and
ACCT_NBR = :ft-acct-nbr
and
(
AMOUNT = :ft-amount:ind(1)
or
SERIAL_NBR = :ft-serial-nbr:ind(2)
or
(
AMOUNT = :ft-amount:ind(3)
and
SERIAL_NBR = :ft-serial-nbr:ind(4)
)
)
end-exec.
procedure division.
perform connect
perform get-account
perform get-input
perform until amount-in = spaces
and serial-in = spaces
perform mainline
perform get-input
end-perform
perform disconnect
exit program.
get-account.
accept account-in from command-line
move brch-in to ft-brch-nbr
move acct-in to ft-acct-nbr
Frank Swarbrick wrote:
Here is the pseudocode:
get either empno or lastname from user
if empno is entered then empnoind = 0, else empnoind = -1
if lastname is entered then nameind = 0, else nameind = -1
SELECT * FROM EMPLOYEE
WHERE EMPNO = :EMPNO:EMPNOIND
OR LASTNAME = :NAME:NAMEINDI am not sure that it is doing anything special because those are ''null
indicators'', so in effect the setting may be turning the value into
NULL.
SELECT * FROM EMPLOYEE
WHERE EMPENO = ''somevalue''
OR LASTNAME = NULL
Which should work correctly as long as the columns are not allowed to
be NULL.
Richard<ri****@Azonic.co.nz10/18/06 1:11 PM >>>>
Frank Swarbrick wrote:
>Here is the pseudocode:
get either empno or lastname from user
if empno is entered then empnoind = 0, else empnoind = -1
if lastname is entered then nameind = 0, else nameind = -1
SELECT * FROM EMPLOYEE
WHERE EMPNO = :EMPNO:EMPNOIND
OR LASTNAME = :NAME:NAMEIND
I am not sure that it is doing anything special because those are ''null
indicators'', so in effect the setting may be turning the value into
NULL.
SELECT * FROM EMPLOYEE
WHERE EMPENO = ''somevalue''
OR LASTNAME = NULL
Which should work correctly as long as the columns are not allowed to
be NULL.It appears that a "null indicator" in a predicate is not the same as
checking for NULL. Instead, that part of the predicate is not checked.
Here''s more from the manual I was reading:
--------------------
Do not use input indicator variables in search conditions (WHERE or HAVING
clauses) to test for null values. The correct way to test for nulls is with
the
NULL predicate (described earlier):
WHERE MGRNO IS NULL ** correct **
This will return every row where MGRNO is NULL.
WHERE MGRNO = :MGR:MGRIND
If MGRIND has been set negative to make MGR null, the truth value is
?UNKNOWN?, and nothing will be returned.
--------------------
How the first example above appears to work is if EMPNOIND is -1 then the
predicate is interpreted as "WHERE LASTNAME = :NAME". If NAMEIND is -1 then
it is interpreted as "WHERE EMPNO = :EMPNO". If neither is -1 then it''s
simply "WHERE EMPNO = :EMPNO OR LASTNAME = :NAME". In no case is either
EMPNO or LASTNAME actually being checked for a NULL value. If one wishes to
do that (which was not what I was wanting) then you would have to check for
"= NULL".
As a further example, I believe the following is *not* redundant; it does
not have the same meaning as either of the above examples:
SELECT * FROM EMPLOYEE
WHERE EMPNO = :EMPNO:EMPNOIND
OR EMPNO = NULL
OR LASTNAME = :NAME:NAMEIND
OR LASTNAME = NULL
Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA
Frank Swarbrick wrote:
How the first example above appears to work is if EMPNOIND is -1 then the
predicate is interpreted as "WHERE LASTNAME = :NAME". If NAMEIND is -1 then
it is interpreted as "WHERE EMPNO = :EMPNO". If neither is -1 then it''s
simply "WHERE EMPNO = :EMPNO OR LASTNAME = :NAME". In no case is either
EMPNO or LASTNAME actually being checked for a NULL value. If one wishes to
do that (which was not what I was wanting) then you would have to check for
"= NULL".Perhaps then the meaning of a -1 indicator is ''NOT A VALUE''. Then the
two tests can be done and the one -1ed with never be true because no
value can ever have that.
这篇关于输入指标变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
