CallableStatement:调用存储过程的参数边界

  ── PostgreSQL 默认(select),调用函数
  {call add_out(3, 4, ?)}:execute() = false;OUT 参数 getInt(3) = 7;getUpdateCount = -1;getMoreResults = false
  INOUT 参数 {call inout_double(?)}:输入 21 → getInt(1) = 42
  函数返回值 {? = call fn_square(?)}:getInt(1) = 81
  名字参数(setInt("a", …)):SQLFeatureNotSupportedException(0A000): Method org.postgresql.jdbc.PgCallableStatement.setInt(String,int) is not yet implemented.
  不 registerOutParameter 就 execute:PSQLException(22023): No value specified for parameter 3.
  getInt 在 registerOutParameter 之后读取不存在的索引 4:PSQLException(2200G): Parameter of type java.sql.Types=0 was registered, but call to getInt (sqltype=java.sql.Types=4) was made.
  OUT 参数登记成错误的类型(VARCHAR)再 getInt:PSQLException(42821): A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=4 however type java.sql.Types=12 was registered.
  OUT 参数是 NULL 时:getInt = 0;wasNull = true;getObject = null
  参数元数据 getParameterMetaData:PSQLException(42883): ERROR: function add_out(unknown, unknown, unknown) does not exist
  ── PostgreSQL 默认(select),调用过程
  {call add_out(3, 4, ?)}:PSQLException(42883): ERROR: function add_out_p(integer, integer) does not exist
  INOUT 参数 {call inout_double(?)}:PSQLException(42809): ERROR: inout_double_p(integer) is a procedure
  函数返回值 {? = call fn_square(?)}:getInt(1) = 81
  名字参数(setInt("a", …)):SQLFeatureNotSupportedException(0A000): Method org.postgresql.jdbc.PgCallableStatement.setInt(String,int) is not yet implemented.
  不 registerOutParameter 就 execute:PSQLException(22023): No value specified for parameter 3.
  getInt 在 registerOutParameter 之后读取不存在的索引 4:PSQLException(42883): ERROR: function add_out_p(integer, integer) does not exist
  OUT 参数登记成错误的类型(VARCHAR)再 getInt:PSQLException(42883): ERROR: function add_out_p(integer, integer) does not exist
  OUT 参数是 NULL 时:PSQLException(42883): ERROR: function out_null_p() does not exist
  参数元数据 getParameterMetaData:PSQLException(42809): ERROR: add_out_p(unknown, unknown, unknown) is a procedure
  ── PostgreSQL callIfNoReturn,调用过程
  {call add_out(3, 4, ?)}:execute() = false;OUT 参数 getInt(3) = 7;getUpdateCount = -1;getMoreResults = false
  INOUT 参数 {call inout_double(?)}:输入 21 → getInt(1) = 42
  函数返回值 {? = call fn_square(?)}:getInt(1) = 81
  名字参数(setInt("a", …)):SQLFeatureNotSupportedException(0A000): Method org.postgresql.jdbc.PgCallableStatement.setInt(String,int) is not yet implemented.
  不 registerOutParameter 就 execute:PSQLException(22023): No value specified for parameter 3.
  getInt 在 registerOutParameter 之后读取不存在的索引 4:PSQLException(2200G): Parameter of type java.sql.Types=0 was registered, but call to getInt (sqltype=java.sql.Types=4) was made.
  OUT 参数登记成错误的类型(VARCHAR)再 getInt:PSQLException(42821): A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=4 however type java.sql.Types=12 was registered.
  OUT 参数是 NULL 时:getInt = 0;wasNull = true;getObject = null
  参数元数据 getParameterMetaData:3 个 [1:int4 mode=1][2:int4 mode=1][3:int4 mode=1]
  ── PostgreSQL call,调用过程
  {call add_out(3, 4, ?)}:execute() = false;OUT 参数 getInt(3) = 7;getUpdateCount = -1;getMoreResults = false
  INOUT 参数 {call inout_double(?)}:输入 21 → getInt(1) = 42
  函数返回值 {? = call fn_square(?)}:PSQLException(42883): ERROR: procedure fn_square(unknown, integer) does not exist
  名字参数(setInt("a", …)):SQLFeatureNotSupportedException(0A000): Method org.postgresql.jdbc.PgCallableStatement.setInt(String,int) is not yet implemented.
  不 registerOutParameter 就 execute:PSQLException(22023): No value specified for parameter 3.
  getInt 在 registerOutParameter 之后读取不存在的索引 4:PSQLException(2200G): Parameter of type java.sql.Types=0 was registered, but call to getInt (sqltype=java.sql.Types=4) was made.
  OUT 参数登记成错误的类型(VARCHAR)再 getInt:PSQLException(42821): A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=4 however type java.sql.Types=12 was registered.
  OUT 参数是 NULL 时:getInt = 0;wasNull = true;getObject = null
  参数元数据 getParameterMetaData:3 个 [1:int4 mode=1][2:int4 mode=1][3:int4 mode=1]
  ── PostgreSQL call,调用函数
  {call add_out(3, 4, ?)}:PSQLException(42809): ERROR: add_out(integer, integer, unknown) is not a procedure
  INOUT 参数 {call inout_double(?)}:PSQLException(42809): ERROR: inout_double(integer) is not a procedure
  函数返回值 {? = call fn_square(?)}:PSQLException(42883): ERROR: procedure fn_square(unknown, integer) does not exist
  名字参数(setInt("a", …)):SQLFeatureNotSupportedException(0A000): Method org.postgresql.jdbc.PgCallableStatement.setInt(String,int) is not yet implemented.
  不 registerOutParameter 就 execute:PSQLException(22023): No value specified for parameter 3.
  getInt 在 registerOutParameter 之后读取不存在的索引 4:PSQLException(42809): ERROR: add_out(integer, integer, unknown) is not a procedure
  OUT 参数登记成错误的类型(VARCHAR)再 getInt:PSQLException(42809): ERROR: add_out(integer, integer, unknown) is not a procedure
  OUT 参数是 NULL 时:PSQLException(42809): ERROR: out_null(unknown) is not a procedure
  参数元数据 getParameterMetaData:PSQLException(42809): ERROR: add_out(unknown, unknown, unknown) is not a procedure

  ── MySQL
  {call add_out(3, 4, ?)}:execute() = false;OUT 参数 getInt(3) = 7;getUpdateCount = 0;getMoreResults = false
  INOUT 参数 {call inout_double(?)}:输入 21 → getInt(1) = 42
  函数返回值 {? = call fn_square(?)}:getInt(1) = 81
  名字参数(setInt("a", …)):OUT r = 7
  不 registerOutParameter 就 execute:execute 成功;getInt(3) = 3
  getInt 在 registerOutParameter 之后读取不存在的索引 4:SQLException(S1009): Parameter index of 4 is out of range (1, 3)
  OUT 参数登记成错误的类型(VARCHAR)再 getInt:getString = 3;getInt = 3
  OUT 参数是 NULL 时:getInt = 0;wasNull = true;getObject = null
  参数元数据 getParameterMetaData:3 个 [1:INT mode=1][2:INT mode=1][3:INT mode=4]

  ── PostgreSQL(默认)
  {call multi_rs(2)}:[结果集 1:1 列 2 行]
  PG 函数返回 refcursor(autoCommit = false):读取到 4 行
  同样的 refcursor 在 autoCommit = true 下:PSQLException(34000): ERROR: cursor "mycur" does not exist
  {call multi_rs_proc(?)}(过程,INOUT):PSQLException(42809): ERROR: multi_rs_proc(integer) is a procedure
  {call fn_square(?)}(函数,不用 ? =):execute()=true;结果集第 1 行 = 81
  原生 CALL add_out_p(?, ?, ?):OUT = 7
  ── PostgreSQL(call 模式,过程)
  {call multi_rs(2)}:PSQLException(42809): ERROR: multi_rs(integer) is not a procedure
  PG 函数返回 refcursor(autoCommit = false):PSQLException(42883): ERROR: procedure fn_cursor(unknown) does not exist
  同样的 refcursor 在 autoCommit = true 下:PSQLException(42883): ERROR: procedure fn_cursor(unknown) does not exist
  {call multi_rs_proc(?)}(过程,INOUT):getInt(1) = 3
  {call fn_square(?)}(函数,不用 ? =):PSQLException(42809): ERROR: fn_square(integer) is not a procedure
  原生 CALL add_out_p(?, ?, ?):OUT = 7
  ── MySQL
  {call multi_rs(2)}:[结果集 1:2 列 1 行][结果集 2:1 列 1 行][更新计数 0]
  MySQL 既返回结果集又有 OUT 参数:结果集 1 行;读 OUT 参数 getInt(2) = 500
  没有先读完结果集就读 OUT 参数:500

  ── PostgreSQL(默认,函数)
  调用不存在的过程:PSQLException(42883): ERROR: function no_such_proc(integer) does not exist
  参数个数不对(只传 2 个):成功
  过程里 RAISE / SIGNAL 异常:PSQLException(45000): ERROR: boom 1
  没有设置 IN 参数:PSQLException(22023): No value specified for parameter 2.
  registerOutParameter 的 JDBC 类型与数据库类型不匹配(INTEGER 过程登记为 TIMESTAMP):PSQLException(42821): A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=4 however type java.sql.Types=93 was registered.
  重复使用同一个 CallableStatement 执行多次:10 11 12
  executeBatch 调用存储过程:executeBatch → [0, 0, 0];log 表 3 行
  在 autoCommit = false 下过程内部出错后是否可继续:PSQLException(25P02): ERROR: current transaction is aborted, commands ignored until end of transaction block
  ── PostgreSQL(call 模式,过程)
  调用不存在的过程:PSQLException(42883): ERROR: procedure no_such_proc(integer) does not exist
  参数个数不对(只传 2 个):PSQLException(42883): ERROR: procedure add_out_p(integer, integer) does not exist
  过程里 RAISE / SIGNAL 异常:PSQLException(45000): ERROR: boom 1
  没有设置 IN 参数:PSQLException(22023): No value specified for parameter 2.
  registerOutParameter 的 JDBC 类型与数据库类型不匹配(INTEGER 过程登记为 TIMESTAMP):PSQLException(42821): A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=4 however type java.sql.Types=93 was registered.
  重复使用同一个 CallableStatement 执行多次:10 11 12
  executeBatch 调用存储过程:executeBatch → [-1, -1, -1];log 表 6 行
  在 autoCommit = false 下过程内部出错后是否可继续:PSQLException(25P02): ERROR: current transaction is aborted, commands ignored until end of transaction block
  ── MySQL
  调用不存在的过程:SQLSyntaxErrorException(42000): PROCEDURE lab.no_such_proc does not exist
  参数个数不对(只传 2 个):SQLException(S1009): Parameter r is not registered as an output parameter
  过程里 RAISE / SIGNAL 异常:SQLException(45000): boom
  没有设置 IN 参数:SQLException(07001): No value specified for parameter 2
  registerOutParameter 的 JDBC 类型与数据库类型不匹配(INTEGER 过程登记为 TIMESTAMP):SQLDataException(22018): Unsupported conversion from LONG to java.sql.Timestamp
  重复使用同一个 CallableStatement 执行多次:10 11 12
  executeBatch 调用存储过程:executeBatch → [1, 1, 1];log 表 3 行
  在 autoCommit = false 下过程内部出错后是否可继续:后续查询 → 1

  ── PostgreSQL(默认,函数)
  3000 次调用(同一连接):每次 prepareCall 27 µs;复用同一个 CallableStatement 26 µs;同样逻辑的 PreparedStatement 查询 25 µs
  ── PostgreSQL(call 模式,过程)
  3000 次调用(同一连接):每次 prepareCall 31 µs;复用同一个 CallableStatement 30 µs;同样逻辑的 PreparedStatement 查询 25 µs
  ── MySQL
  3000 次调用(同一连接):每次 prepareCall 658 µs;复用同一个 CallableStatement 102 µs;同样逻辑的 PreparedStatement 查询 29 µs