Re: Out parameters for functions.
От | Mark Rotteveel |
---|---|
Тема | Re: Out parameters for functions. |
Дата | |
Msg-id | 0e17cd67-ee15-1422-5062-b2f5d24ee396@lawinegevaar.nl обсуждение исходный текст |
Ответ на | Re: Out parameters for functions. (Robert Zenz <robert.zenz@sibvisions.com>) |
Список | pgsql-jdbc |
On 26-9-2018 10:46, Robert Zenz wrote: > Okay...I've whipped up the following test cases. Feel free to correct any > misconceptions and things that I got wrong here. > > This is how it looks in Oracle: > > dba.getConnection().createStatement().execute("create or replace function > TESTCALL(" > + "VALUE2 in number," > + "VALUE3 out number," > + "VALUE4 in out number)" > + "return number is begin " > + "value3 := 300;" > + "value4 := 400 + VALUE4;" > + "return 100 + VALUE2;" > + "end;"); > > CallableStatement statement = dba.getConnection().prepareCall("{ ? = call > TESTCALL(?, ?, ?) }"); > // RETURN > statement.registerOutParameter(1, Types.NUMERIC); > // VALUE3 > statement.registerOutParameter(3, Types.NUMERIC); > // VALUE4 > statement.registerOutParameter(4, Types.NUMERIC); > // VALUE2 > statement.setObject(2, Integer.valueOf(1)); > // VALUE4 > statement.setObject(4, Integer.valueOf(2)); > > statement.execute(); > > Assert.assertEquals(BigDecimal.valueOf(101), statement.getObject(1)); > Assert.assertEquals(BigDecimal.valueOf(300), statement.getObject(3)); > Assert.assertEquals(BigDecimal.valueOf(402), statement.getObject(4)); > > And this is how it looks in PostgreSQL: > > dba.getConnection().createStatement().execute("create function TEST(" > + "out VALUE1 numeric," > + "in VALUE2 numeric," > + "out VALUE3 numeric," > + "inout VALUE4 numeric)" > + "returns record as $$ declare ret record; begin " > + "value1 := 100 + VALUE2;" > + "value3 := 300;" > + "value4 := 400 + VALUE4;" > + "end; $$ language plpgsql;"); > > CallableStatement statement = dba.getConnection().prepareCall("{ ? = call > TEST(?, ?) }"); > // RETURN > statement.registerOutParameter(1, Types.NUMERIC); > // VALUE1 > statement.registerOutParameter(2, Types.NUMERIC); > // VALUE3 > statement.registerOutParameter(3, Types.NUMERIC); > // VALUE2 > statement.setObject(2, Integer.valueOf(1)); > // VALUE4 > statement.setObject(3, Integer.valueOf(2)); > > statement.execute(); > > Assert.assertEquals(BigDecimal.valueOf(101), statement.getObject(1)); > Assert.assertEquals(BigDecimal.valueOf(300), statement.getObject(2)); > Assert.assertEquals(BigDecimal.valueOf(402), statement.getObject(3)); > > Note that the first parameter here is used as replacement for the return value. > > Did I get the PostgreSQL example right? I don't have time to test right now, maybe later this week. I think you should be able to drop the `returns record` clause from your function definition, and then use `{class TEST(?, ?, ?, ?)}` instead. Your `registerOutParameter` indexes seem off as well, as you're currently registering an IN parameter as an OUT parameter (as far as I understand; I haven't done that much with stored procedure calling using PostgreSQL JDBC). Mark -- Mark Rotteveel
В списке pgsql-jdbc по дате отправления: