Skip to main content

Stored Procedure Calls

Hint

This article is generated by AI translation.

dbVisitor supports stored procedures with IN/OUT arguments. Typical scenarios include:

Execute a Stored Procedure​

Example MySQL procedure that accepts one argument and returns one result set.

1. MySQL procedure
create procedure proc_select_table(in userName varchar(200))
begin
    select * from test_user where name = userName;
end;
2. Compose the call
➊ {call proc_select_table(?)} ➋ @{resultSet,name=res,javaType=net.demo.UserDTO}
  • ➊ Calls proc_select_table with positional argument binding.
  • ➋ Uses the RESULT rule to map the result set to UserDTO.
3. Execute
JdbcTemplate jdbc = ...
Map<String, Object> res = jdbc.call(➊ "{call proc_select_table(?)} @{resultSet,name=res,javaType=net.demo.UserDTO}",
                                    ➋ "Kevin");
List<UserDTO> result = (List<UserDTO>)res.get("res"); ➌
  • ➊ Final SQL with rule applied.
  • ➋ Input argument.
  • ➌ Retrieve the result set named res.
info

If the procedure emits multiple result sets, add more RESULT rules to handle each one.

Named Arguments​

Execute with named argument
JdbcTemplate jdbc = ...
Map<String, Object> res = jdbc.call(➊ "{call proc_select_table(:userName)} @{resultSet,name=res,javaType=net.demo.UserDTO}",
                                    ➋ CollectionUtils.asMap("userName", "Kevin"));
List<UserDTO> result = (List<UserDTO>)res.get("res"); ➌

Same as above, but replaces ? with :userName.

info

See Arguments for more binding styles.

OUT Arguments​

1. MySQL procedure with OUT argument
create procedure proc_select_table(in userName varchar(200), ➊
                                   out outName varchar(200)) ➋ -- OUT arg
begin
    select * from test_user where name = userName; ➌
    set outName = concat(userName,'-str');
end;
2. Compose the call
{
 call proc_select_table(➊ #{userName,jdbcType=varchar},
                        ➋ #{outName, mode=out,jdbcType=varchar})
} ➌ @{resultSet,name=res,javaType=net.demo.UserDTO}
3. Execute
JdbcTemplate jdbc = ...
Map<String, Object> res = jdbc.call(
        "{call proc_select_table(#{userName,jdbcType=varchar}, " +
        "                        #{outName, mode=out,jdbcType=varchar})" +
        "} @{resultSet,name=res,javaType=net.demo.UserDTO}",
        CollectionUtils.asMap("userName", "Kevin"));

String outName = (String)res.get("outName");
List<UserDTO> result = (List<UserDTO>)res.get("res");

OUT Arguments with Cursor​

1. Oracle procedure returning a cursor
create or replace procedure proc_out_cursor(
    ➊ userName in varchar2,
    ➋ tableCursor out sys_refcursor)
as
begin
    open tableCursor for select * from test_user where name = userName;
end;
2. Compose the call
{
 call proc_out_cursor(➊ #{userName,jdbcType=varchar},
                      ➋ #{tableCursor,mode=cursor,javaType=net.demo.UserDTO})
}
3. Execute
JdbcTemplate jdbc = ...
Map<String, Object> res = jdbc.call(
        "{call proc_out_cursor(#{userName,jdbcType=varchar}," +
        "                      #{tableCursor,mode=cursor,javaType=net.demo.UserDTO})" +
        "}", CollectionUtils.asMap("userName", "Kevin"));

List<UserDTO> result = (List<UserDTO>)res.get("tableCursor");
  • For cursors, set mode=cursor when declaring the OUT argument.
info

Some databases require an active transaction to fetch cursor OUT arguments. Consult your database documentation.