将参数传递给DB2 udf的IN谓词 [英] Passing a parameter to IN predicate of DB2 udf
问题描述
亲爱的，
我在将VARCHAR参数（这是一个接受值的
列表）传递给IN谓词时遇到了问题一个DB2用户定义的
函数。
例如，我有一个表（mytable）存储ppl名称&他们的
对应的团体。我想创建一个UDF（myfunc），它选择
来自（mytable）的ppl名称列表，这些名称属于某些动态选择
组。我尝试过以下编码，但是如果我试图提供一组指定的组选择作为单个
参数，它就不起作用。
有人可以帮忙吗？非常感谢！
Tracy
////参考SQL：
创建表mytable（
名称VARCHAR（10），
组CHAR（1）
）;
插入mytable值（''Peter''，''A''）;
插入mytable值（''John''，''A''）;
INSERT into mytable values（''Mary''，''B''）;
INSERT into mytable values（''Susan''，''C''）;
CREATE FUNCTION myfunc（cgroup VARCHAR（20））
退货表
（
名称VARCHAR（10） ）
）
语言SQL
读取SQL数据
无外部行动
DETERMINISTIC
返回
SELECT
名称
来自
mytable
where
group in（cgroup）
;
db2" select * from mytable"
NAME GROUP
---------- -----
Peter A
John A
Mary B
Susan C
4条记录被选中。
db2" select * from table（myfunc（char（''A''）））as t1"
NAME
----------
Peter
John
2条记录被选中。
db2" select * from table（myfunc（char（''A，B''）））为t1" ;
NAME
----------
0条记录被选中。
db2" select * from table（myfunc（char（''''''''''''''''''''''''）））t1"
NAME
----------
0条记录（s ）选择。
Tracy写道：亲爱的，
我在将VARCHAR参数（可接受值的列表）传递给DB2用户定义的
函数的IN谓词时遇到了问题。
例如，我有一个表（mytable）存储ppl名称&他们的相应群体。我想创建一个UDF（myfunc），它选择（mytable）属于某些动态选择组的ppl名称列表。我尝试过以下编码，但是如果我试图提供一组指定的组选择作为单个参数，它就无法工作。
任何人都可以帮忙吗？非常感谢！
Tracy
////参考SQL：
CREATE TABLE mytable（
名称VARCHAR（10），
组CHAR（1）
）;
插入mytable值（''Peter''，''A''）;
INSERT into mytable values（ ''John''，''A''）;
INSERT into mytable values（''Mary''，''B''）;
INSERT into mytable values（'''Susan''， ''C''）;
创建功能myfunc（cgroup VARCHAR（20））
返回表
（
名称VARCHAR（10）
）
语言SQL
阅读SQL数据
无外部动作
决定性
返回
选择
名称
FROM
mytable
其中
分组（cgroup）
;
db2" select * from mytable"
NAME GROUP
---------- -----
Peter A John A
Mary B
Susan C
4条记录被选中。
db2" select * from table（ myfunc（char（''A''）））为t1"
名称
----------
彼得 John
2条记录被选中。
db2" select * from table（myfunc（char（''A，B''）） ）如t1"
名称
----------
0条记录被选中。
db2" select * from table（myfunc（char（''''''''''''''''''''''''）））t1"
in列表将参数视为单个VARCHAR，而不是
字符串列表。获得目标的最简单方法是使用动态
仅在SQL过程中允许的SQL：
CREATE PROCEDURE myproc（IN cgroup VARCHAR） （20））
BEGIN
DECLARE curtxt VARCHAR（1000）;
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET curtxt =''SELECT * FROM mytab WHERE group IN（''|| cgroup ||''）'';
PREPARE stmt ;
OPEN cur;
END
CALL myproc（''''''''''''''' ''B'''''）
如果你的团体有一个固定的长度（简单说单个字符）
并且你想坚持纯SQL你可以尝试这个：
CREATE FUNCTION myfun（cgroup VARCHAR（20））
RETURNS TABLE（名称VARCHAR（10））
RETURN
WITH rec（name，num）
AS SELECT name，1
FROM mytab WHERE group = substr（cgroup， 1,1）
UNION ALL
SELECT mytab.name，num + 1
FROM mytab，rec
WHERE group = substr（cgroup，NULLIF（num + 1，LENGTH） （cgroup）），1）
来自rec的SELECT名称;
SELECT * FROM TABLE（myfun（''AB''））AS F;
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
Serge Rielau写道：Tracy写道：亲爱的，
我遇到了一个问题将VARCHAR参数（可接受值的列表）传递给DB2用户定义的
函数的IN谓词。
例如，我有一个表（mytable） ）存储人名和他们的相应群体。我想创建一个UDF（myfunc），它选择（mytable）属于某些动态选择组的ppl名称列表。我尝试过以下编码，但是如果我试图提供一组指定的组选择作为单个参数，它就无法工作。
任何人都可以帮忙吗？非常感谢！
Tracy
////参考SQL：
CREATE TABLE mytable（
名称VARCHAR（10），
组CHAR（1）
）;
插入mytable值（''Peter''，''A''）;
INSERT into mytable values（ ''John''，''A''）;
INSERT into mytable values（''Mary''，''B''）;
INSERT into mytable values（'''Susan''， ''C''）;
创建功能myfunc（cgroup VARCHAR（20））
返回表
（
名称VARCHAR（10）
）
语言SQL
阅读SQL数据
无外部动作
决定性
返回
选择
名称
FROM
mytable
其中
分组（cgroup）
;
db2" select * from mytable"
NAME GROUP
---------- -----
Peter A John A
Mary B
Susan C
4条记录被选中。
db2" select * from table（ myfunc（char（''A''）））为t1"
名称
----------
彼得 John
2条记录被选中。
db2" select * from table（myfunc（char（''A，B''）） ）如t1"
名称
----------
0条记录被选中。
db2" select * from table（myfunc（char（''''''''''''''''''''''''）））t1"
in列表将参数视为单个VARCHAR，而不是
字符串列表。获得目标的最简单方法是使用SQL程序中允许的动态SQL：
CREATE PROCEDURE myproc（IN cgroup VARCHAR（20））
BEGIN
DECLARE curtxt VARCHAR（1000）;
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET curtxt =''SELECT * FROM mytab WHERE group IN（' '|| cgroup ||'''''';
PREPARE stmt;
OPEN cur;
END
CALL myproc（'''''''' '''，'''''''''''
如果你的小组有一个固定的长度（简单来说就是单个字符）
你想坚持使用纯SQL你可以试试这个：
创建功能myfun（cgroup VARCHAR（20））
返回表（名称VARCHAR（10））
返回
用rec（名字， num）
AS SELECT名称，1
FROM mytab WHERE group = substr（cgroup，1,1）
UNION ALL
SELECT mytab.name，num + 1
来自mytab，rec
WHERE gr oup = substr（cgroup，NULLIF（num + 1，LENGTH（cgroup）），1）
来自rec的SELECT名称;
SELECT * FROM TABLE（myfun（''AB''）） ）AS F;
哎呀：
NULLIF（num + 1，LENGTH（cgroup）+ 1）
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
即使没有固定长度，只要逗号不是有效字符：
CREATE FUNCTION myfun（cgroup VARCHAR（20））返回表（名称
VARCHAR （10））
RETURN SELECT from FROM mytable WHERE POSSTR（''，''||| cgroup || ''，''，
group）> 0
嗯，这是未经测试的（我不能在临时桌上制作功能。）
B.
Dear all,
I have encountered a problem in passing a VARCHAR parameter (which is a
list of accepted values) to the IN predicate of a DB2 user-defined
function.
For example, I have a table (mytable) storing ppl names & their
corresponding groups. I would like to create a UDF (myfunc) that select
a list of ppl names from (mytable) who belong to some dynamic choices
of groups. I have tried the following codings but it doesn''t work if I
try to provide a list of designated choices of groups as a single
parameter.
Can anyone help? Thanks a lot!
Tracy
////Reference SQLs:
CREATE TABLE mytable (
name VARCHAR(10),
group CHAR(1)
);
INSERT into mytable values (''Peter'',''A'');
INSERT into mytable values (''John'',''A'');
INSERT into mytable values (''Mary'',''B'');
INSERT into mytable values (''Susan'',''C'');
CREATE FUNCTION myfunc (cgroup VARCHAR(20))
RETURNS TABLE
(
name VARCHAR(10)
)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT
name
FROM
mytable
where
group in (cgroup)
;
db2 "select * from mytable"
NAME GROUP
---------- -----
Peter A
John A
Mary B
Susan C
4 record(s) selected.
db2 "select * from table(myfunc(char(''A''))) as t1"
NAME
----------
Peter
John
2 record(s) selected.
db2 "select * from table(myfunc(char(''A,B''))) as t1"
NAME
----------
0 record(s) selected.
db2 "select * from table(myfunc(char(''''''A'''',''''B''''''))) as t1"
NAME
----------
0 record(s) selected.
Tracy wrote:Dear all,
I have encountered a problem in passing a VARCHAR parameter (which is a
list of accepted values) to the IN predicate of a DB2 user-defined
function.
For example, I have a table (mytable) storing ppl names & their
corresponding groups. I would like to create a UDF (myfunc) that select
a list of ppl names from (mytable) who belong to some dynamic choices
of groups. I have tried the following codings but it doesn''t work if I
try to provide a list of designated choices of groups as a single
parameter.
Can anyone help? Thanks a lot!
Tracy
////Reference SQLs:
CREATE TABLE mytable (
name VARCHAR(10),
group CHAR(1)
);
INSERT into mytable values (''Peter'',''A'');
INSERT into mytable values (''John'',''A'');
INSERT into mytable values (''Mary'',''B'');
INSERT into mytable values (''Susan'',''C'');
CREATE FUNCTION myfunc (cgroup VARCHAR(20))
RETURNS TABLE
(
name VARCHAR(10)
)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT
name
FROM
mytable
where
group in (cgroup)
;db2 "select * from mytable"
NAME GROUP
---------- -----
Peter A
John A
Mary B
Susan C
4 record(s) selected.db2 "select * from table(myfunc(char(''A''))) as t1"
NAME
----------
Peter
John
2 record(s) selected.db2 "select * from table(myfunc(char(''A,B''))) as t1"
NAME
----------
0 record(s) selected.db2 "select * from table(myfunc(char(''''''A'''',''''B''''''))) as t1"
The in list sees the parameter as a single VARCHAR, not as a list of
strings. Th eeasiest way to get where you want to be is to use dynamic
SQL which is allowed in SQL Procedures only:
CREATE PROCEDURE myproc(IN cgroup VARCHAR(20))
BEGIN
DECLARE curtxt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET curtxt = ''SELECT * FROM mytab WHERE group IN ('' || cgroup || '')'';
PREPARE stmt;
OPEN cur;
END
CALL myproc(''''''A'''', ''''B'''''')
If your groups have a fixed length (say single character for simplicity)
and you want to stick with pure SQL you can try this:
CREATE FUNCTION myfun(cgroup VARCHAR(20))
RETURNS TABLE(name VARCHAR(10))
RETURN
WITH rec(name, num)
AS SELECT name, 1
FROM mytab WHERE group = substr(cgroup, 1, 1)
UNION ALL
SELECT mytab.name, num + 1
FROM mytab, rec
WHERE group = substr(cgroup, NULLIF(num+1, LENGTH(cgroup)), 1)
SELECT name from rec;
SELECT * FROM TABLE(myfun(''AB'')) AS F;
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge Rielau wrote:Tracy wrote:Dear all,
I have encountered a problem in passing a VARCHAR parameter (which is a
list of accepted values) to the IN predicate of a DB2 user-defined
function.
For example, I have a table (mytable) storing ppl names & their
corresponding groups. I would like to create a UDF (myfunc) that select
a list of ppl names from (mytable) who belong to some dynamic choices
of groups. I have tried the following codings but it doesn''t work if I
try to provide a list of designated choices of groups as a single
parameter.
Can anyone help? Thanks a lot!
Tracy
////Reference SQLs:
CREATE TABLE mytable (
name VARCHAR(10),
group CHAR(1)
);
INSERT into mytable values (''Peter'',''A'');
INSERT into mytable values (''John'',''A'');
INSERT into mytable values (''Mary'',''B'');
INSERT into mytable values (''Susan'',''C'');
CREATE FUNCTION myfunc (cgroup VARCHAR(20))
RETURNS TABLE
(
name VARCHAR(10)
)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT
name
FROM
mytable
where
group in (cgroup)
;db2 "select * from mytable"
NAME GROUP
---------- -----
Peter A
John A
Mary B
Susan C
4 record(s) selected.db2 "select * from table(myfunc(char(''A''))) as t1"
NAME
----------
Peter
John
2 record(s) selected.db2 "select * from table(myfunc(char(''A,B''))) as t1"
NAME
----------
0 record(s) selected.db2 "select * from table(myfunc(char(''''''A'''',''''B''''''))) as t1"
The in list sees the parameter as a single VARCHAR, not as a list of
strings. Th eeasiest way to get where you want to be is to use dynamic
SQL which is allowed in SQL Procedures only:
CREATE PROCEDURE myproc(IN cgroup VARCHAR(20))
BEGIN
DECLARE curtxt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR WITH RETURN FOR stmt;
SET curtxt = ''SELECT * FROM mytab WHERE group IN ('' || cgroup || '')'';
PREPARE stmt;
OPEN cur;
END
CALL myproc(''''''A'''', ''''B'''''')
If your groups have a fixed length (say single character for simplicity)
and you want to stick with pure SQL you can try this:
CREATE FUNCTION myfun(cgroup VARCHAR(20))
RETURNS TABLE(name VARCHAR(10))
RETURN
WITH rec(name, num)
AS SELECT name, 1
FROM mytab WHERE group = substr(cgroup, 1, 1)
UNION ALL
SELECT mytab.name, num + 1
FROM mytab, rec
WHERE group = substr(cgroup, NULLIF(num+1, LENGTH(cgroup)), 1)
SELECT name from rec;
SELECT * FROM TABLE(myfun(''AB'')) AS F;
Oops:
NULLIF(num+1, LENGTH(cgroup) + 1)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Even without fixed length, as long as comma is not a valid character:
CREATE FUNCTION myfun(cgroup VARCHAR(20)) RETURNS TABLE(name
VARCHAR(10))
RETURN SELECT name FROM mytable WHERE POSSTR('','' || cgroup || '','',
group) > 0
Well, it''s untested (i can''t make a FUNCTION on a temp table.)
B.
这篇关于将参数传递给DB2 udf的IN谓词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
