问题描述
我正在写一个由h2数据库支持的会计系统.帐户树存储在ACCOUNTS表中，其中PARENT_ID列将链接存储在树中.
I am writing an accounting system backed by an h2 database. The tree of accounts is stored in the ACCOUNTS table, with the PARENT_ID column storing the links in the tree.
要获取树中给定节点的路径，我具有以下存储过程:
To get the path to a given node in the tree, I have the following stored procedure:
public static Object[] getAncestorPKs(Long id)
其工作是产生一个整数数组，该整数是给定节点与树的根之间的PARENT_ID值.我将程序以ANCESTOR_PKS的名称注册到数据库中，如下所示:
whose job is to produce an array of integers, being the PARENT_ID values between the given node and the root of the tree. I register the procedure in the database under the name ANCESTOR_PKS like this:
CREATE ALIAS IF NOT EXISTS ANCESTOR_PKS FOR "xxx.yyy.zzz.getAncestorPKs"
我的问题是，在不触发SQLException的情况下，我似乎找不到找到使用该事物的方法！我需要通过以下方式使用它:
My problem is I can't seem to find a way to use the thing without triggering a SQLException! I need to use it in the following way:
SELECT ID FROM ACCOUNTS WHERE [...] AND ID IN ANCESTOR_PKS(5) [for example]
我收到以下错误:
Syntax error in SQL statement "SELECT ID FROM ACCOUNTS WHERE ID IN ANCESTOR_PKS[*](5) "; expected "("
但是在ANCESTOR_PKS之后有一个 括号！这到底是怎么回事?有什么方法可以将IN与存储过程的结果一起使用?我进行了搜索，但是我看到的所有使用IN的示例都使用文字数组，似乎没有人想到显示如何使用作为数组的返回值.存储过程.
But there is a bracket after ANCESTOR_PKS!!! What on earth is going on here? Is there some way to use IN with the result of a stored procedure? I have searched and searched, but all the examples of the use of IN that I see are with literal arrays, and no-one seems to think of showing how to use an array which is the return value of a stored procedure.
帮助?
推荐答案
我不熟悉h2，但是此页面说，您必须在数组周围放置().
I'm unfamiliar with h2, but this page says you must place () around the array.
SELECT ID FROM ACCOUNTS WHERE ID IN (ANCESTOR_PKS(5))
也许您甚至必须添加一个子选择
Maybe you even have to add a subselect
SELECT ID FROM ACCOUNTS WHERE ID IN (SELECT ANCESTOR_PKS(5))
这篇关于存储过程返回一个数组；如何在SQL中使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！