活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

测试在java程序中调用GBase 8s的函数调用。
###1, OUT及RETURN返回值
1）、首先创建需要调用的函数或者存储过程
```sql
-- 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示例

```java
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) {
			}		
		}
	}
}
```
返回结果：
```text
out value: myfunc2
return value: myfunc2  and  f1: 123
```
**OUT游标**
1）、创建需要调用的函数或者存储过程，参数是out类型的sys_refcursor
```sql
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程序调用存储过程
```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) {
            }        
        }
    }

}
```
返回结果：
```text
11	sysconstraints
12	sysreferences
13	syschecks
14	sysdefaults
15	syscoldepend
16	sysprocedures
17	sysprocbody
18	sysprocplan
19	sysprocauth
20	sysblobs
```

###2， RETURN游标参数
1）、创建需要调用的函数或者存储过程
```sql
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示例
```java
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();
		}
	}
}
```
返回结果
```text
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
```

在java中调用GBase 8s的函数示例

评论

热门帖子