存储过程返回一个数组；如何在SQL中使用? [英] Stored procedure returns an array; how to use in SQL?

问题描述

我正在写一个由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中使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！