oracle创建存储过程，hibernate调用

1,创建存储过程进行批量更新（存储过程无输出参数）。hibernate调用，传入输入参数完成批量更新

CREATE OR REPLACE 
PROCEDURE CESHI_TEST(startDate in VARCHAR2,endDate in VARCHAR2,tId in VARCHAR2) AS
TYPE cursor_variable is REF cursor;   --定义游标变量,用于动态关联sql语句
cursor_v cursor_variable;             --游标
igtii IMS_GTII%rowtype; --行变量，用于接收遍历游标的结果集
v_sqlstr   varchar2(1000);                  --查询结果集sql语句，动态拼接
BEGIN
--动态拼接sql 当tId is not null 时，增加检索条件 tenantId = tId
v_sqlstr := 'select * from IMS_GTII G WHERE status = 0 AND bdate >= TO_DATE (''' || startDate
           || ''',''yyyy-mm-dd hh24:mi:ss'') AND bdate <= TO_DATE ('''|| endDate
           || ''',''yyyy-mm-dd hh24:mi:ss'') AND INVOICETYPE = 0 AND EXISTS (SELECT DISTINCT VATCODE,VATSNUMBER FROM EI_HEAD H WHERE G .VATCODE = H .infotypecode_js AND G .VATSNUMBER = H .infonumber_js)';  

IF tId is not null then
    v_sqlstr :=  (v_sqlstr || ' AND tenantId = ''' || tId || '''');
end IF;
dbms_output.put_line(v_sqlstr);
--将游标cursor_v 与查询语句sql相关联
open cursor_v for v_sqlstr;
        --遍历游标，进行批量更新
        FETCH cursor_v into igtii;
        while cursor_v%found loop
            --批量更新IMS_GTII 中数据
            UPDATE IMS_GTII SET INVOICETYPE = 1,UPDATETIME = sysdate where INVID = igtii.INVID;
            dbms_output.put_line('更新数据id:' || igtii.INVID);
            FETCH cursor_v into igtii;
        end loop;
        close cursor_v;
END;

以上存储过程逻辑：输入参数 startDate   endDate   tId

定义游标变量 cursor_variable 用于动态绑定sql查询语句

定义字符串变量 v_sqlstr  拼接sql查询语句，根据输入参数tId是否为null,动态添加检索条件

游标cursor_v与 v_sqlstr 绑定，遍历获取id，根据id主键更新表数据

(IMS_GTII，EI_HEAD 表结构这里就不提供了，查询sql可套用自己的表)

hibernate调用：

findSqlProcess("{CALL CESHI_TEST(?,?,?)}",“2022-05-18”,“2022-05-18”,"tid");

public void findSqlProcess(String queryString, Object... values) {
    SQLQuery query = getSession().createSQLQuery(queryString);
    for (int i = 0; i < values.length; i++) {
        query.setParameter(i, values[i]);
    }
    query.executeUpdate();
}

2,调用带返回参数的存储过程，并获取结果集

CREATE OR REPLACE 
PROCEDURE CESHI_CURSOR_RESULTS(v_pram1 in int,v_param2 in NUMBER,v_param3 in VARCHAR2,dataCursor out sys_refcursor) AS
BEGIN
    open dataCursor FOR select * from IMS_GTII where INVOICETYPE = v_pram1 and RECHECKNAME = v_param3 and TAXRATE = v_param2;
END;

返回参数dataCursor 接收查询语句返回的多行数据

hibernate调用，并获取结果集进行遍历

findSqlProcessResults("{CALL CESHI_CURSOR_RESULTS(?,?,?,?)}",1,0.13,"李四");

public ResultSet findSqlProcessResults(String queryString,Object... values) throws SQLException {
    Connection connection = getSession().connection();
    CallableStatement callable = connection.prepareCall(queryString);

    for (int i = 0; i < values.length; i++) {
        if(values[i] instanceof Integer){
            int v = (Integer) values[i];
            callable.setInt(i+1,v);
        }else if(values[i] instanceof  Double){
            double v = (Double) values[i];
            callable.setDouble(i+1,v);
        }else{
            //其他统一字符串类型
            callable.setString(i+1,values[i].toString());
        }
    }

    int outindex = values.length + 1;
    callable.registerOutParameter(outindex, OracleTypes.CURSOR);
    callable.execute();
    ResultSet resultSet = (ResultSet)callable.getObject(outindex);
    //遍历结果集resultSet
    while (resultSet.next()){
        //获取行的索引2的字段并打印出来
        System.out.println(resultSet.getString(2));
    }
    return resultSet;
}