The following articles mainly show you how to use java to call the return list of Oracle stored procedures so that there is no return value in Oracle stored procedures. All the return values of Oracle stored procedures are replaced by relevant parameters, if you are interested in the actual operations on the list returned by calling the Oracle stored procedure in java, you can browse our article.
The list is no exception, but because it is a set, you cannot use a common parameter. You must use pagkage. Therefore, the list must be divided into two parts,
1. Create a package. As follows:
CREATE OR REPLACE PACKAGE TESTPACKAGE
TYPE Test_CURSOR is ref cursor;
End TESTPACKAGE;
2. Create a stored procedure:
Create or replace procedure testc (p_CURSOR out TESTPACKAGE. Test_CURSOR) IS
BEGIN
OPEN p_CURSOR for select * from hyq. TESTTB;
End testc;
As you can see, Oracle storage regards the cursor as a pointer) and returns the value as an out parameter. Use the following code when calling in java:
- package com.hyq.src;
- import java.sql.*;
- import java.io.OutputStream;
- import java.io.Writer;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import oracle.jdbc.driver.*;
- public class TestProcedureTHREE {
- public TestProcedureTHREE() {
- }
- public static void main(String[] args ){
- String driver = "oracle.jdbc.driver.OracleDriver";
- String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
- Statement stmt = null;
- ResultSet rs = null;
- Connection conn = null;
- try {
- Class.forName(driver);
- conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
- CallableStatement proc = null;
- proc = conn.prepareCall("{ call hyq.testc(?) }");
- proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
- proc.execute();
- rs = (ResultSet)proc.getObject(1);
- while(rs.next())
- {
- System.out.println("<tr><td>" + rs.getString(1)
+ "</td><td>"+rs.getString(2)+"</td></tr>");
- }
- }
- catch (SQLException ex2) {
- ex2.printStackTrace();
- }
- catch (Exception ex2) {
- ex2.printStackTrace();
- }
- finally{
- try {
- if(rs != null){
- rs.close();
- if(stmt!=null){
- stmt.close();
- }
- if(conn!=null){
- conn.close();
- }
- }
- }
- catch (SQLException ex1) {
- }
- }
- }
- }
Note that the oracle driver package must be placed in the class path before execution. Otherwise, an error will be reported. the above is an introduction to the list of returned results of calling the Oracle stored procedure in java. I hope you will get some benefits.