返回游标:
CREATE OR REPLACE PACKAGE BODY MyTest
IS PROCEDURE zhbtest(P_CUSTOMER_ID c_well.wellno %TYPE, Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
OPEN V_CURSOR FOR
select wellname from c_well ;
Re_CURSOR := V_CURSOR;
END;
END;
public class Protest {
private static Connection conn = null;
private static oracle.jdbc.OracleCallableStatement call = null;
private static ResultSet rs = null;
private static String url = "jdbc:oracle:thin:@192.168.100.145:1521:kdc";
private static String name = "liuyi";
private static int cout = 0; public static void main(String[] args){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url,"kdcerp2","123");
call = (oracle.jdbc.OracleCallableStatement)conn.prepareCall("{call mytest.zhbtest(?,?)}");
call.setString(1, "4050608006");
call.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
call.execute();
rs = call.getCursor(2);
while(rs.next()){
System.out.println(rs.getString(1));
cout++;
}
System.out.println(cout);
}catch(java.lang.ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
System.out.println(e.toString());
}
}
}
CREATE OR REPLACE PACKAGE BODY MyTest
IS PROCEDURE zhbtest(P_CUSTOMER_ID c_well.wellno %TYPE, Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
OPEN V_CURSOR FOR
select wellname from c_well ;
Re_CURSOR := V_CURSOR;
END;
END;
public class Protest {
private static Connection conn = null;
private static oracle.jdbc.OracleCallableStatement call = null;
private static ResultSet rs = null;
private static String url = "jdbc:oracle:thin:@192.168.100.145:1521:kdc";
private static String name = "liuyi";
private static int cout = 0; public static void main(String[] args){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url,"kdcerp2","123");
call = (oracle.jdbc.OracleCallableStatement)conn.prepareCall("{call mytest.zhbtest(?,?)}");
call.setString(1, "4050608006");
call.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
call.execute();
rs = call.getCursor(2);
while(rs.next()){
System.out.println(rs.getString(1));
cout++;
}
System.out.println(cout);
}catch(java.lang.ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
System.out.println(e.toString());
}
}
}
解决方案 »
- 執行成功但結果不對
- 小白问题:直接访问其他用户的表,不用在表前加用户名。
- ORA-01475: 必须对光标重新进行语法分析来改变赋值变量的数据类型
- 导入数据报错误ORA-01659
- 第一回写oracle的存储过程,不知道那里错了很多
- 公交汽车站这个数据库应该怎么设计?
- 有其他的SQL客户端工具可以连接Oracle服务器吗?
- 更新一个表中的数据来源于另一个表中数据,如何写?
- !!在这月圆之夜,祝大家中秋快乐。。。万事顺心。。!!!
- 诸位谁有discoverer告诉好吗?或告诉我哪有下载的。我100分赠送,多谢了。
- 使用occi如何远程访问oracle数据库?
- 启动数据库出错怎么办呀?请各位大侠指点一下。
楼上贴的缺了 ref cursor 的定义和procedure 的定义:
CREATE OR REPLACE PACKAGE MyTest
as
type T_CUSOR is ref cursor; PROCEDURE zhbtest(P_CUSTOMER_ID c_well.wellno %TYPE,
Re_CURSOR OUT T_CURSOR);
end;
/
PL/SQL stored procedures are called from within JDBC programs by means of the prepareCall() method of the Connection object created above. A call to this method takes variable bind parameters as input parameters as well as output variables and creates an object instance of the CallableStatement class.The following line of code illustrates this:CallableStatement stproc_stmt = conn.prepareCall
("{call procname(?,?,?)}");
Here conn is an instance of the Connection class.The input parameters are bound to this object instance using the setXXX() methods on the CallableStatement object. For each input bind parameter, a setXXX() method (e.g., setInt(), setString(),) is called. The following line of code illustrates this:stproc_stmt.setXXX(...)
The output parameters are bound to this object instance using registerOutParameter() method on the CallableStatement object, as shown below:stproc_stmt.registerOutParameter(2, OracleTypes.CHAR);
The above statement registers the second parameter passed to the stored procedure as an OUT parameter of type CHAR. For each OUT parameter, a registerOutParameter() method is called.Once a CallableStatement object has been constructed, the next step is to execute the associated stored procedure or function. This is done by using the executeUpdate() method of the CallableStatement object. The following line of code illustrates this using the stproc_stmt object created above:stproc_stmt.executeUpdate();
prepareCall() MethodThe three different kinds of stored sub-programs, namely, stored procedures, stored functions, and packaged procedures and functions can be called using the prepareCall() method of the CallableStatement object.The syntax for calling stored functions is as follows:CallableStatement stproc_stmt = conn.prepareCall
("{ ? = call _funcname(?,?,?)}");
The first ? refers to the return value of the function and is also to be registered as an OUT parameter.Packaged Procedures and FunctionsPackaged procedures and functions can be called in the same manner as stored procedures or functions except that the name of the package followed a dot "." prefixes the name of the procedure or function.Once the stored procedure or function has been executed, the values of the out parameters can be obtained using the getXXX() methods (for example, getInt() and getString()) on the CallableStatement object. This is shown below:String op1 stproc_stmt.getString(2);
This retrieves the value returned by the second parameter (which is an OUT parameter of the corresponding PL/SQL stored procedure being called and has been registered as an OUT parameter in the JDBC program) into the Java String variable op1.A complete example is shown below. Consider a procedure that returns the highest paid employee in a particular department. Specifically, this procedure takes a deptno as input and returns empno, ename, and sal in the form of three out parameters.The procedure is created as follows:CREATE OR REPLACE PROCEDURE p_highest_paid_emp
(ip_deptno NUMBER,
op_empno OUT NUMBER,
op_ename OUT VARCHAR2,
op_sal OUT NUMBER)
IS
v_empno NUMBER;
v_ename VARCHAR2(20);
v_sal NUMBER;
BEGIN
SELECT empno, ename, sal
INTO v_empno, v_ename, v_sal
FROM emp e1
WHERE sal = (SELECT MAX(e2.sal)
FROM emp e2
WHERE e2.deptno = e1.deptno
AND e2.deptno = ip_deptno)
AND deptno = ip_deptno;
op_empno := v_empno;
op_ename := v_ename;
op_sal := v_sal;
END;
/
Here we assume that there is only one highest paid employee in a particular department.Next we write the JDBC program that calls this procedure. This is shown below:import java.sql.*;public class StProcExample {
public static void main(String[] args)
throws SQLException {
int ret_code;
Connection conn = null;
try {
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
Oracle", "oratest", "oratest");
int i_deptno = 10;
CallableStatement pstmt = conn.prepareCall("{call p_highest_
paid_emp(?,?,?,?)}");
pstmt.setInt(1, i_deptno);
pstmt.registerOutParameter(2, Types.INTEGER);
pstmt.registerOutParameter(3, Types.VARCHAR);
pstmt.registerOutParameter(4, Types.FLOAT);
pstmt.executeUpdate(); int o_empno = pstmt.getInt(2);
String o_ename = pstmt.getString(3);
float o_sal = pstmt.getFloat(4);
System.out.print("The highest paid employee in dept "
+i_deptno+" is: "+o_empno+" "+o_ename+" "+o_sal);
pstmt.close();
conn.close();
} catch (SQLException e) {ret_code = e.getErrorCode();
System.err.println(ret_code + e.getMessage()); conn.close();}
}
}
Calling Java Stored Procedures
Java stored procedures can also be called from JDBC programs using the corresponding call specifications created to publish the Java methods into the Oracle 8i database. In other words, calling the published call specs executes the corresponding Java methods and the syntax for calling these is the same as calling PL/SQL stored procedures.Here we will use the Java stored procedures created in Chapter 2, "Java Stored Procedures." The following JDBC program calls the packaged procedure pkg_empmaster.fire_emp (a Java stored procedure that corresponds to the Java method empMaster.fireEmp()). Specifically it deletes the record in emp table where empno = 1002.Before executing the above Java stored procedure, the record corresponding to empno 1002 in emp table is as follows:EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1002 DAVID ANALYST 1001 01-JAN-01 6000 1000 10
The JDBC program to call the Java stored procedure is as follows:import java.sql.*;
public class JavaProcExample {
public static void main(String[] args)
throws SQLException {
int ret_code;
Connection conn = null;
try {
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
Oracle", "oratest", "oratest");
int i_empno = 1002;
CallableStatement pstmt =
conn.prepareCall("{call pkg_empmaster.fire_emp(?)}");
pstmt.setInt(1, i_empno);
pstmt.executeUpdate(); pstmt.close();
conn.close();
} catch (SQLException e) {ret_code = e.getErrorCode();
System.err.println(ret_code + e.getMessage()); conn.close();}
}
}
The output of the above program can be verified as follows:SQL> select * from emp where empno = 1002;no rows selectedSQL>