java代码中调用存储过程PROCEDURE以及函数Function

由于网络上这种方面的文章相对较少较乱，所以特别来写一篇。

一、java调用PROCEDURE

1.1 新建存储过程

如下代码中，关键字说明：

• IN ：表示入参
• OUT：表示出参
• RETURNING ：表示语句执行完后的返回值。这里返回的是刚插入的数据的id
• SEQ_WORK_REPORT_ID.nextval：表示的是序列，读者自行新建序列。有关序列的创建，请自行搜索。

CREATE OR REPLACE PROCEDURE p_save_note_details(i_login_user_id  IN  NUMBER, i_receive_user_id IN VARCHAR2,
                                                i_work_content   IN  VARCHAR2, i_next_content IN VARCHAR2,
                                                i_work_help      IN  VARCHAR2, i_remark IN VARCHAR2,
                                                i_start_date     IN  VARCHAR2, i_end_date IN VARCHAR2,
                                                i_id_for_save    IN  VARCHAR2,
                                                r_note_id OUT NUMBER, r_send_id OUT NUMBER) IS
  v_seq_id NUMBER;   --定义变量
  BEGIN
    -- 拿出序列id
    SELECT SEQ_WORK_REPORT_ID.nextval
    INTO v_seq_id
    FROM dual;

    --  插入主表
    INSERT INTO T_WEEK_NOTE_DETAIL (ID, LOGIN_USER_ID, RECEIVE_USER_ID, WORK_CONTENT, NEXT_CONTENT, WORK_HELP, REMARK,
                                             START_DATE, END_DATE)
    VALUES (v_seq_id, i_login_user_id, i_receive_user_id, i_work_content, i_next_content, i_work_help, i_remark,
            to_date(i_start_date, 'yyyy-mm-dd hh24:mi:ss'), to_date(i_end_date, 'yyyy-mm-dd hh24:mi:ss'))
    RETURNING ID INTO r_note_id;

    -- 插入关联表
    INSERT INTO T_WEEK_REPORT_SEND (ID, LOGIN_USER_ID, OPTION_TYPE, SEND_TYPE)
      SELECT
        v_seq_id                                                   AS ID,
        to_number(tmp.column_value)                                AS LOGIN_USER_ID,
        1                                                          AS OPTION_TYPE,
        decode(to_number(tmp.column_value), i_login_user_id, 1, 2) AS SEND_TYPE
      FROM
        (SELECT column_value
         FROM TABLE (fn_split(i_id_for_save, ','))) tmp;

	--  返回序列id给出参，用来校验的。防止上一步的insert报错退出而不知道。
	SELECT v_seq_id
    INTO r_send_id
    FROM dual;

	-- 提交修改
    COMMIT;
  END;

1.2 java调用

核心代码如下。

• 先获取 java.sql.Connection
• 调用conn.prepareCall 得到CallableStatement
• 设置CallableStatement入参以及设置出参类型
• 获取出参，校验完成。

 			java.sql.Connection conn = context.getDBService().getDBConnection(dbName);//获取对应数据库连接池

               //设置入参
            CallableStatement statmentCall = conn.prepareCall("{call p_save_note_details(?,?,?,?,?,?,?,?,?,?,?)}");
            statmentCall.setInt(1, Integer.valueOf(login_user_id));
            statmentCall.setString(2, receive_user_id);
            statmentCall.setString(3, work_content);
            statmentCall.setString(4, next_content);
            statmentCall.setString(5, work_help);
            statmentCall.setString(6, remark);
            statmentCall.setString(7, start_date);
            statmentCall.setString(8, end_date);
            if (receive_user_id.contains(login_user_id)) {//剔除接收人是自己的记录
                statmentCall.setString(9, receive_user_id);
            } else {
                statmentCall.setString(9, login_user_id + "," + receive_user_id);
            }
            //设置出参类型
            statmentCall.registerOutParameter(10, OracleTypes.NUMBER);
            statmentCall.registerOutParameter(11, OracleTypes.NUMBER);

            statmentCall.execute();

            //获取出参
            Integer note_id = statmentCall.getInt(10);
            Integer send_id = statmentCall.getInt(11);

            if (note_id != null && send_id != null && note_id.intValue() > 0 && note_id.intValue() == send_id.intValue()) {
                response.setErrorNo(0);
                response.setErrorInfo("新增成功");
                return;
            } else {
                response.setErrorNo(-1);
                response.setErrorInfo("新增失败");
                log.warn("491722 rollback 新增失败");
                return;
            }

二、java调用Funtion

2.1 新建Funtion

oracle 的function 语法格式和存储过程类似，如下：

• return number：表明函数返回值为整型。可以看出 ，该函数正常结束时返回值为0，异常退出时返回-1。
• r_cur：游标类型出参。返回的具体结果可以放在这里面。通过OPEN FOR关键字来赋值。

create or replace function fun_user_visit_add(i_login_user_id   IN NUMBER, -- 登录id | 发送人id
                                              i_receive_user_id IN VARCHAR2, -- 接收人id,多个id之间用英文逗号隔开
                                              i_work_content    IN VARCHAR2, -- 主要事宜
                                              i_respondent      IN VARCHAR2, -- 拜访对象
                                              i_visit_type      IN VARCHAR2, -- 拜访方式:0其他，1当面，2电话，3聊天软件
                                              i_work_result     IN VARCHAR2, -- 拜访结果
                                              i_remark          IN VARCHAR2, -- 备注
                                              i_id_for_save     IN VARCHAR2, -- 剔除重复的发送人id后的值，用来存储到send表中
                                              r_cur             OUT cjtype.t_cursor) -- 出參
 return number

 is
  v_seq_id NUMBER;

  midstr          varchar2(400);
  midint          number;
  log_func_no     number; --功能号
  log_sp_name     varchar2(4000); --sp名称
  log_table_name  varchar2(4000); --操作的表
begin

  --日志记录相关变量
  log_func_no     := 'xxxxxx';
  log_sp_name     := '';
  log_table_name  := 'T_WEEK_VISIT_DETAIL,T_WEEK_REPORT_SEND';

  ----业务操作区域------------------------------------------------------------

  -- 拿出序列id
  SELECT SEQ_WORK_REPORT_ID.nextval INTO v_seq_id FROM dual;

  -- 插入主表
  INSERT INTO T_WEEK_VISIT_DETAIL
    (ID,
     LOGIN_USER_ID,
     RECEIVE_USER_ID,
     WORK_CONTENT,
     RESPONDENT,
     VISIT_TYPE,
     WORK_RESULT,
     REMARK)
  VALUES
    (v_seq_id,
     i_login_user_id,
     i_receive_user_id,
     i_work_content,
     i_respondent,
     i_visit_type,
     i_work_result,
     i_remark);
  
  -- 插入关联表
  INSERT INTO T_WEEK_REPORT_SEND
    (ID, LOGIN_USER_ID, OPTION_TYPE, SEND_TYPE)
    SELECT v_seq_id AS ID,
           to_number(tmp.column_value) AS LOGIN_USER_ID,
           3 AS OPTION_TYPE,
           decode(to_number(tmp.column_value), i_login_user_id, 1, 2) AS SEND_TYPE
      FROM (SELECT column_value FROM TABLE(fn_split(i_id_for_save, ','))) tmp;

  COMMIT;

  -- 返回结果
  open r_cur for
    select v_seq_id as seq_id from dual;
  return 0;

exception
  when others then
    midstr := sqlerrm;
    midint := sqlcode;
    --必须回滚
    rollback;
  
    -------------------------------------------------------------------------------
    --------          日志记录区                                          --------
    ------------------------------------------------------------------------------
  
    open r_cur for
      select -1 as error_no, log_func_no || ':' || midstr as error_info
        from dual;
  
    return - 1;
  
end fun_user_visit_add;

2.2 java调用Function

	  java.sql.Connection conn = context.getDBService().getDBConnection(dbName);//获取对应数据库连接池

	 	//入参
	  CallableStatement statmentCall = conn.prepareCall("{? = call fun_user_outside_add(?,?,?,?,?,?,?,?)}");
      statmentCall.registerOutParameter(1, OracleTypes.NUMBER);
      statmentCall.setInt(2, Integer.valueOf(login_user_id));
      statmentCall.setString(3, receive_user_id);
      statmentCall.setString(4, work_content);
      statmentCall.setString(5, start_date);
      statmentCall.setString(6, end_date);
      statmentCall.setString(7, remark);
      if (receive_user_id.contains(login_user_id)) {//剔除接收人是自己的记录
          statmentCall.setString(8, receive_user_id);
      } else {
          statmentCall.setString(8, login_user_id + "," + receive_user_id);
      }
      //出参
      statmentCall.registerOutParameter(9, OracleTypes.CURSOR);

      statmentCall.execute();

      //获取出参
      int ret = statmentCall.getInt(1);

      if (ret < 0) {
          response.setErrorNo(-1);
          response.setErrorInfo("新增失败");
          log.warn("491724  新增失败");
          return;
      } else {
          response.setErrorNo(0);
          response.setErrorInfo("新增成功");
          ResultSet resultSet = (ResultSet) statmentCall.getObject(9);
          if (resultSet.next()) {
              int r_seq_id = resultSet.getInt("seq_id");
             // 获取值后 do something...
          }
          return;
      }