测试在java程序中调用GBase 8s的函数调用。

1, OUT及RETURN返回值

1）、首先创建需要调用的函数或者存储过程

-- out value 是 out变量值
-- return value 是函数返回值
create function myfunc2(f1 int,out f2 varchar(128)) returns varchar(128);
  let f2 = "out value: myfunc2";
  return "return value: myfunc2  and  f1: " || f1;
end function;

2）、java程序调用函数myfunc2示例

package cn.gbase.spl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import cn.gbase.util.DB;

public class CallSpl {
    
    public static Connection connection = DB.getConn();  //调用数据库连接
    
    public static void main(String[] args) {
        callSplout();
    }
    
    public static void callSplout() {
        CallableStatement cs = null;
        ResultSet rSet = null;
        String splSQL = "{call myfunc2(?,?)}";     //第一个参数为in输入，第二个参数为out参数
        try {
            cs=connection.prepareCall(splSQL);
            cs.setInt(1, 123);
            cs.registerOutParameter(2, java.sql.Types.VARCHAR);
            rSet = cs.executeQuery();
            System.out.println(cs.getString(2));  // 输出OUT的值
            while(rSet.next()) {                  // 输出RETURN的值
                System.out.println(rSet.getString(1));     
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rSet.close();
                cs.close();
            } catch (SQLException e) {
            }        
        }
    }
}

返回结果：

out value: myfunc2
return value: myfunc2  and  f1: 123

OUT游标
1）、创建需要调用的函数或者存储过程，参数是out类型的sys_refcursor

drop procedure if exists proc_outcursor;
CREATE PROCEDURE proc_outcursor(out p SYS_REFCURSOR)
  OPEN p FOR 'select skip 10 first 10 tabid,tabname,ustlowts from systables';
end procedure;

2)、java程序调用存储过程

package com.gbasedbt.jdbc.Test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.gbasedbt.DBConn.DBC;
import com.gbasedbt.lang.IfxTypes;

public class OutCursor {

    public static Connection connection = DBC.getConn();  //调用数据库连接
    
    public static void main(String[] args) {
        callSplout();
    }
     
    public static void callSplout() {
        CallableStatement cs = null;
        ResultSet cursorSet = null;
        String splSQL = "{call proc_outcursor(?)}";     // 参数为out参数
        try {
            cs=connection.prepareCall(splSQL);
            cs.registerOutParameter(1, IfxTypes.IFX_TYPE_CURSOR);
            cs.executeQuery();
            cursorSet = (ResultSet) cs.getObject(1);   // 获取out 
            while(cursorSet.next()) {                  // 输出out 的值
                System.out.println(cursorSet.getString("tabid") + "\t" + cursorSet.getString("tabname"));     
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                cursorSet.close();
                cs.close();
            } catch (SQLException e) {
            }        
        }
    }

}

返回结果：

11    sysconstraints
12    sysreferences
13    syschecks
14    sysdefaults
15    syscoldepend
16    sysprocedures
17    sysprocbody
18    sysprocplan
19    sysprocauth
20    sysblobs

2， RETURN游标参数

1）、创建需要调用的函数或者存储过程

drop procedure if exists proc_cursor;
CREATE PROCEDURE proc_cursor() returns SYS_REFCURSOR;
  define my_cursor SYS_REFCURSOR;
  OPEN my_cursor FOR 'select skip 10 first 10 tabid,tabname,ustlowts from systables';
  return my_cursor;
end procedure;

2）、java程序调用存储过程proc_cursor示例

package testGBasedbt;

import java.sql.Connection;
import java.sql.DriverManager;
import com.gbasedbt.jdbc.IfxCallableStatement;
import com.gbasedbt.jdbc.IfxResultSet;

public class TestSPL {

    public static void main(String[] args) {
        
        String url = "jdbc:gbasedbt-sqli://192.168.1.71:9088/testdb:GBASEDBTSERVER=gbase01;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;NEWCODESET=UTF-8,utf8,57372";
        String sql = "{call proc_cursor()}";
        
        try {
            Class.forName("com.gbasedbt.jdbc.IfxDriver");
            Connection connection = DriverManager.getConnection(url,"gbasedbt","GBase123");
            IfxCallableStatement cs = (IfxCallableStatement) connection.prepareCall(sql);
                        
            IfxResultSet resultSet = (IfxResultSet) cs.executeQuery(); 
            while (resultSet.next()) {                                  // 输出RETURN的值
                System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getTimestamp(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

返回结果

11    sysconstraints    2019-06-08 16:28:02.0
12    sysreferences    2019-06-08 16:28:02.0
13    syschecks    2019-06-08 16:28:02.0
14    sysdefaults    2019-06-08 16:28:02.0
15    syscoldepend    2019-06-08 16:28:02.0
16    sysprocedures    2019-06-08 16:28:02.0
17    sysprocbody    2019-06-08 16:28:02.0
18    sysprocplan    2019-06-08 16:28:03.0
19    sysprocauth    2019-06-08 16:28:03.0
20    sysblobs    2019-06-08 16:28:03.0