概要描述

本文主要针对存储过程不同的入参出参类型，举例实现jdbc调用的不同方法，可供参考。

详细说明

创建样例表

> DROP TABLE IF EXISTS TEST_TB1;
> CREATE TABLE TEST_TB1 (ID INT,NAME STRING,AGE INT) CLUSTERED BY(ID) INTO 3 BUCKETS STORED AS ORC_TRANSACTION;

1. 无返回值的存储过程

存储过程实现对测试表的单条数据插入动作

CREATE OR REPLACE PROCEDURE proc_noout(PARA1 in INT ,PARA2 STRING ,PARA3 INT )
IS
BEGIN
  INSERT INTO TEST_TB1(ID,NAME,AGE) VALUES (PARA1,PARA2,PARA3);
END;

SQL调用：

> CALL proc_noout(1,'zhangsan',18)

JAVA调用：

import java.sql.*;

public class Proc_NoOut {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    public static void main(String[] args) throws SQLException {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
        //Hive2 JDBC URL with LDAP
        String jdbcURL = "jdbc:hive2://172.22.23.1:10000/default";
        String user = "hive";
        String password = "123456";
        Connection conn = DriverManager.getConnection(jdbcURL, user, password);
        CallableStatement cbstmt = conn.prepareCall("{call default.proc_noout(?,?,?)}");
        cbstmt.setString(1,"2");
        cbstmt.setString(2, "lisi");
        cbstmt.setString(3, "19");
        cbstmt.execute();
        cbstmt.close();
        conn.close();
    }
}

2. 有返回值的存储过程（非列表）

DROP PROCEDURE IF EXISTS proc_without;
CREATE OR REPLACE PROCEDURE proc_without(PARA1 IN STRING, PARA2 OUT INT) AS
BEGIN
  SELECT AGE INTO PARA2 FROM TEST_TB1 WHERE NAME = PARA1;
END;

SQL调用：

DECLARE
    v_out INT;
BEGIN
    proc_without('zhangsan',v_out)
    dbms_output.put_line(v_out)
END 

JAVA调用：

import java.sql.*;

public class Proc_WithOut {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    public static void main(String[] args) throws SQLException {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
        //Hive2 JDBC URL with LDAP
        String jdbcURL = "jdbc:hive2://172.22.23.1:10000/default";
        String user = "hive";
        String password = "123456";
        Connection conn = DriverManager.getConnection(jdbcURL, user, password);
        CallableStatement cbstmt = conn.prepareCall("{call default.proc_without(?,?)}");
        cbstmt.setString(1,"zhangsan");
        cbstmt.registerOutParameter(2, Types.INTEGER);
        cbstmt.execute();
        Integer testPrint = cbstmt.getInt(2);
        System.out.println(testPrint);
        cbstmt.close();
        conn.close();
    }
}

3. 有返回值的存储过程（列表 ref cursor）

ref cursor需要单独声明，在这里通过package统一声明

CREATE OR REPLACE PACKAGE TESTPACKAGE AS
  TYPE TEST_CURSOR IS REF CURSOR;
end;

CREATE OR REPLACE PROCEDURE TESTC(P_CURSOR out TESTPACKAGE.TEST_CURSOR) IS
BEGIN
  OPEN P_CURSOR FOR
    SELECT * FROM TEST_TB1;
END;

SQL调用：

DECLARE
    v_out   TESTPACKAGE.TEST_CURSOR;
    v_rows  DEFAULT.TEST_TB1%ROWTYPE;
BEGIN
    proc_without_ref_cursor(v_out)
    LOOP 
        FETCH v_out INTO v_rows;
        EXIT WHEN v_out%NOTFOUND;
        dbms_output.put_line(v_rows.id||","||v_rows.name)
    END LOOP
END

JAVA调用：

import java.sql.*;

public class Proc_WithOutList {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    public static void main(String[] args) throws SQLException {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
        //Hive2 JDBC URL with LDAP
        String jdbcURL = "jdbc:hive2://172.22.23.1:10000/default";
        String user = "hive";
        String password = "123456";
        ResultSet rs = null;
        Connection conn = DriverManager.getConnection(jdbcURL, user, password);
        CallableStatement cbstmt = conn.prepareCall("{call default.proc_without_ref_cursor(?)}");
        cbstmt.registerOutParameter(1, -10);
        cbstmt.execute();
        rs = (ResultSet) cbstmt.getObject(1);
        while (rs.next()) {
            System.out.println(rs.getString(1) + ","+ rs.getString(2));
        }
        cbstmt.close();
        conn.close();
    }
}

4. 有返回值的存储过程（列表 sys_refcursor）

与ref cursor不同，sys_refcursor不需要单独声明

CREATE OR REPLACE PROCEDURE proc_without_sys_refcursor(P_CURSOR out sys_refcursor) IS
BEGIN
  OPEN P_CURSOR FOR
    SELECT * FROM TEST_TB1;
END;

SQL调用：

DECLARE
    v_out   sys_refcursor;
    v_rows  DEFAULT.TEST_TB1%ROWTYPE;
BEGIN
    proc_without_sys_refcursor(v_out)
    LOOP 
        FETCH v_out INTO v_rows;
        EXIT WHEN v_out%NOTFOUND;
        dbms_output.put_line(v_rows.id||","||v_rows.name)
    END LOOP
END 

JAVA调用：

import java.sql.*;
public class JDBCexample {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";

    public static void main(String[] args) throws SQLException {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
        //Hive2 JDBC URL with LDAP
        String jdbcURL = "jdbc:hive2://172.22.23.1:10000/default";
        String user = "hive";
        String password = "123456";
        ResultSet rs = null;
        Connection conn = DriverManager.getConnection(jdbcURL, user, password);
        CallableStatement cbstmt = conn.prepareCall("call proc_without_sys_refcursor(?)");
        cbstmt.registerOutParameter(1, -10);
        cbstmt.execute();
        rs = (ResultSet) cbstmt.getObject(1);
        System.out.println();
        while (rs.next()) {
            System.out.println(rs.getString(1) + "," + rs.getString(2));
        }
        rs.close();
        cbstmt.close();
        conn.close();
    }
}