修改PLSQL函数以从同一列返回多行

本文介绍了修改PLSQL函数以从同一列返回多行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我是PLSQL初学者用户，我有一个可能很简单的问题.

I am a beginner PLSQL user, and I have what might be a rather simple question.

我创建了以下SQL函数，该函数返回其公司ID与我提供的公司ID相匹配的流程的创建日期.我已经将它连接到我的JDBC，它返回的值就很好.

I have created the following SQL Function, which returns the created date of the process whose corporate ID matches the corporate ID that I have given it. I have this connected to my JDBC, and it returns values just fine.

但是，我只是意识到我忽略了一个重要问题-完全有可能多个流程的公司ID与我输入的ID值相匹配，在这种情况下，我将需要能够访问ID返回匹配项的所有创建的日期值.

However, I just realized I overlooked an important issue--it's entirely possible that more than one process will have a corporate ID that matches the ID value I've inputted, and in cases like that I will need to be able to access all of the created date values where the IDs return a match.

CREATE OR REPLACE FUNCTION FUNCTION_1( c_id IN INT) RETURN INT AS p_date process.date_created%TYPE; BEGIN SELECT process.date_created FROM PROCESS WHERE process.corporate_id = c_id ORDER BY process.corporate_id; RETURN p_id; END FUNCTION_1; /

有没有一种方法可以修改我的函数以从同一列返回多行，然后调用该函数以使用JDBC返回某种数组?或者，如果不可能的话，有没有办法我可以使用PLSQL过程或仅将普通SQL与JDBC结合起来返回我需要的东西?我在这里浏览了其他问题，但似乎都与我需要了解的内容无关.

Is there a way that I can modify my function to return multiple rows from the same column, and then call that function to return some sort of array using JDBC? Or, if that isn't possible, is there a way I can return what I need using PLSQL procedures or just plain SQL combined with JDBC? I've looked through other questions here, but none seemed to be about quite what I need to know.

感谢任何可以提供帮助的人！

Thanks to anyone who can help!

推荐答案

您需要对函数进行一些更改.在Java方面，这很容易选择

you need make some changes in your function. on java side it will be simple select

• 您需要将函数的类型从int更改为collection 在此处阅读有关表函数的信息表函数
• 用户oracle table()函数将函数结果转换为表 它使您可以在查询中使用函数.在此处详细了解语法:表集合:示例

• you need change type of your function from int to collection read about the table functions here Table Functions
• user oracle table() function to convert result of your function to table it let you use your function in queries. read more about the syntax here: Table Collections: Examples

这里是如何从java调用函数的示例:

here the example how to call your function from java:

select t.column_value as process_id from table(FUNCTION_1(1)) t --result PROCESS_ID 1 1 2 2 --we need create new type - table of integers CREATE OR REPLACE TYPE t_process_ids IS TABLE OF int; --and make changes in function CREATE OR REPLACE FUNCTION FUNCTION_1( c_id IN INT) RETURN t_process_ids AS l_ids t_process_ids := t_process_ids(); BEGIN --here I populated result of select into the local variables SELECT process.id bulk collect into l_ids FROM PROCESS WHERE process.corporate_id = c_id ORDER BY process.corporate_id; --return the local var return l_ids; END FUNCTION_1; --the script that I used for testing create table process(id int, corporate_id int, date_created date); insert into process values(1, 1, sysdate); insert into process values(2, 1, sysdate); insert into process values(3, 2, sysdate);