create or replace type emptype as object ( eno number(4), ename nvarchar2(20) );
注意: nvarchar2 如果定义成 varchar2 有可能会报一个
Non supported character set: oracle-character-set-xxx 的错误
CREATE OR REPLACE PROCEDURE "PRO_ADD_EMP2" (emp in EMPTYPE, ref_cur out sys_refcursor) is begin open ref_cur for select emp.eno || ' + 111',emp.ename || ' world !!!' from dual; end pro_add_emp2;
客户端调用:
CallableStatement cstmt = (OracleCallableStatement) conn.prepareCall("{call PRO_ADD_EMP2(?,?) }"); // 参入参数 Object[] obj = new Object[]{123,"hello"}; StructDescriptor ad = StructDescriptor.createDescriptor("EMPTYPE", conn); STRUCT str = new STRUCT(ad,conn,obj); cstmt.setObject(1, str); // 传入 cstmt.registerOutParameter(2, OracleTypes.CURSOR); // 注册返回值类型 cstmt.execute(); ResultSet rs = (ResultSet) cstmt.getObject(2); // 获取返回结果 while(rs.next()){ System.out.println(rs.getString(1)); System.out.println(rs.getString(2)); }
注意: StructDescriptor.createDescriptor("EMPTYPE",conn);
createDescriptor 的第一个参数, 必须与数据库的大小写匹配