call stored procedure
openGauss supports directly invoking pre-created stored procedures through JDBC, the steps are as follows:
Call the prepareCall method of Connection to create a call statement object.
Connection myConn = DriverManager.getConnection("url","user","password");
CallableStatement cstmt = myConn.prepareCall("{? = CALL TESTPROC(?,?,?)}");
Call the setInt method of CallableStatement to set parameters.
cstmt.setInt(2, 50);
cstmt.setInt(1, 20);
cstmt.setInt(3, 90);
Call the registerOutParameter method of CallableStatement to register the output parameters.
cstmt.registerOutParameter(4, Types.INTEGER);
Call CallableStatement's execute to execute the method call.
cstmt.execute();
Call the getInt method of CallableStatement to get the output parameters.
int out = cstmt.getInt(4);
Example:
create or replace procedure testproc
(
psv_in1 in integer,
psv_in2 in integer,
psv_inout in out integer
)
as
begin
psv_inout := psv_in1 + psv_in2 + psv_inout;
end;
/
Call the close method of CallableStatement to close the calling statement.
cstmt.close();
illustrate:
Many database classes such as Connection, Statement, and ResultSet have a close() method, and they should be closed after using the objects. It should be noted that the closing of the Connection will indirectly close all the Statements associated with it, and the closing of the Statement will indirectly close the ResultSet.
Some JDBC drivers also provide named parameter methods to set parameters. The method of naming parameters allows parameters to be set by name rather than order. If the parameter has a default value, the default value of this parameter can be used without specifying the parameter value. Even if the order of parameters in a stored procedure changes, the application does not have to be modified. Currently the JDBC driver of the openGauss database does not support this method.
The openGauss database does not support functions with output parameters, nor does it support stored procedures and function parameter default values.
myConn.prepareCall("{? = CALL TESTPROC(?,?,?)}"), when executing the stored procedure binding parameters, you can bind the parameters according to the order of the placeholders, register the first parameter as the output parameter, and also The parameters can be bound according to the order of the parameters in the stored procedure, and the fourth parameter is registered as an output parameter. For the above use case, the fourth parameter is registered as an output parameter.
Notice:
When a cursor is used as the return value of a stored procedure, if the stored procedure is called using JDBC, the returned cursor will not be available.
Stored procedures cannot be executed in the same statement as ordinary SQL.
The inout type parameter in the stored procedure must be registered as an out parameter.
Top comments (0)