Call oracle stored procedure with java Summary

2010-06-09  来源:本站原创  分类:Java  人气:207 

1, what is stored procedure. Stored procedure is a section of the database server-side program, it has two types. A kind of similar to the SELECT query used to retrieve the data, the data can be retrieved in the form of a data set returned to the customer. Another similar to the INSERT, or DELETE query, it does not return data, only one action. Some servers allow the same stored procedure can return only the data they can perform the action.
2, when to use stored procedure stored procedure if the server is defined, should be required to decide whether to use stored procedures. Stored procedures are often to perform some tasks, these tasks are often carried out for the large number of records. Executed on the server stored procedure, can improve application performance. This is because:
. Servers often have a strong computing power and speed.
. To avoid a large amount of data downloaded to the client, reducing network throughput.
For example, suppose an application requires a data calculation, this data needs to involve many records. If you do not use stored procedures, then these data downloaded to the client, resulting in dramatic increase in traffic on the network.
Not only that, the client may be one obsolete computer, its operation speed is slow. The switch to the stored procedure, the server will soon put the data calculated, and just pass a data to the client, its efficiency is very high apparent.
3, stored procedure parameters to stored procedure execution on the server, usually a number of parameters to be passed. These parameters are divided into four types:
The first is called the input parameters, the client passes the value to the stored procedure.
The second is called the output parameter from stored procedure returns the result to the client.
The third is called input / output parameters, either by the client pass values to a stored procedure can also be from the stored procedure returns the result to the client.
The fourth is called state parameters, the stored procedure returns an error message to the client.
To show that not all servers support these four types of parameters, such as, InterBase does not support the state parameters.
4, oracle stored procedure, the basic syntax

1. The basic structure

CREATE OR REPLACEPROCEDURE  The stored procedure name
(
     Parameters  1 IN NUMBER,
     Parameters  2 IN NUMBER
) IS
 Variable  1 INTEGER :=0;
 Variable  2 DATE;
BEGIN
END  The stored procedure name

2.SELECT INTO STATEMENT
To select the query results into the variables, you can simultaneously store multiple variables in multiple columns, there must be a record, or an exception (if there is no record throw NO_DATA_FOUND)
Examples:

BEGIN
  SELECT col1,col2 into  Variable 1, variable  2 FROM typestruct where xxx;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
  END;

A: No return value of stored procedure stored procedure as follows:

CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
BEGIN
  INSERT INTO HYQ.B_ID (I_ID,I_NAME) S (PARA1, PARA2);
END TESTA;

Then, when called in the java in the following code:

package com.hyq.src;

import java.sql.*;
import java.sql.ResultSet;

public class TestProcedureOne {
public TestProcedureOne() {
}
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;
    CallableStatement cstmt = null;

    try {
      Class.forName(driver);
      conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
      CallableStatement proc = null;
      proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");
      proc.setString(1, "100");
      proc.setString(2, "TestOne");
      proc.execute();
    }
    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) {
      }
    }
}
}

Of course, this first required to build the tables TESTTB, which the two fields (I_ID, I_NAME).

Second: There is a stored procedure return value (non-list)
Stored Procedures are:

CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
  SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;

When is call in the java code with the following:

package com.hyq.src;

public class TestProcedureTWO {
public TestProcedureTWO() {
}
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.TESTB(?,?) }");
      proc.setString(1, "100");
      proc.registerOutParameter(2, Types.VARCHAR);
      proc.execute();
      String testPrint = proc.getString(2);
      System.out.println("=testPrint=is="+testPrint);
    }
    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 here proc.getString (2) the value of 2 is not arbitrary, but in the process and store the corresponding column out, if out in the first place, that is proc.getString (1), if the first three positions, is proc.getString (3), of course, can also have multiple return values, that is, parameters and then add a few out.

3: Back to the list of oracle stored procedure because there is no return value, it's all the return values are replaced by out parameters, the list also no exception, but because it is set, it can not use normal parameters, we must use pagkage the. So to be divided into two parts,
1, to build a package. As follows:

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;

2, the establishment of a stored procedure, stored procedures are:

CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
    OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
END TESTC;

You can see, it is the cursor (which can be interpreted as a pointer), as a out parameter to return value.
When is call in the java code with the following:

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) {
      }
    }
}
}

Here to note that the former must be first in the implementation of the oracle's driver package into the class path in, otherwise it will error.

相关文章
  • Calling oracle stored procedure with java Summary 2009-07-18

    1: no return value stored procedure Stored procedure as follows: CREATE OR REPLACE PROCEDURE TESTA (PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID, I_NAME) VALUES (PARA1, PARA2); END TESTA; Then, when called inside the java

  • Call oracle stored procedure with java Summary (transfer) 2010-03-07

    A: No return value stored procedure Stored procedure is: CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID,I_NAME) VALUES (PARA1, PARA2); END TESTA; Then, when called in the java in the followi

  • Call oracle stored procedure with java Summary 2010-06-09

    1, what is stored procedure. Stored procedure is a section of the database server-side program, it has two types. A kind of similar to the SELECT query used to retrieve the data, the data can be retrieved in the form of a data set returned to the cus

  • oracle stored procedure call java program 2011-02-28

    oracle stored procedure call java program Write java program to be called: import java.io.FileWriter; public class Procedure2JavaTest { public static void sendMsg (java.lang.String title, java.lang.String content) throws Exception { String str = titl

  • Oracle stored procedure exception handling summary 2011-04-11

    1, the advantages of abnormal If no exception, in the program, you should check the success or failure of each command, such as BEGIN SELECT ... - Check for 'no data found' error SELECT ... - Check for 'no data found' error SELECT ... - Check for 'no

  • Written in Oracle stored procedure using java 2010-11-03

    Oracle comes with a JVM, built-in support for Java, Java can do any thing you do can be put inside Oracle, you can even inside operation db2. This allows Oracle has a very large expansion of capacity, as long as you wish, can develop a trigger, it at

  • JAVA calling Oracle stored procedure (transfer) 2010-12-10

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether it is in the database system stored procedures or triggers, through SQL statements and control flow statements to complete the set. Re

  • JAVA ORACLE stored procedure call summary 2010-06-13

    1, no return value stored procedure Stored procedure is: (the process is to add the department dept) create or replace procedure adddept (deptno number, dname varchar2, loc varchar2) as begin if deptno is not null then insert into dept values (deptno

  • java call oracle stored procedure sample summary 2010-09-18

    oracle java stored procedure is called an example of a conclusion: no return value stored procedure Stored procedure is: create or replace procedure adddept (colno number, colname varchar2, colc varchar2) as begin insert into table values (colno, col

  • java call the Oracle stored procedure 2010-03-30

    Time to start learning to write stored procedures, the main reason is because the work requires it, would have thought that simple, but after several setbacks, the heroic spirit vanished, but finally pass out, and in order to avoid later, few detours

  • JAVA and ORACLE stored procedure 2010-07-19

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • JAVA Oracle stored procedure input parameter in the LIST 2010-11-28

    Today, they want nothing to do over the weekend to try a combination of JAVA and ORACLE extent, the results reflect the combination of these two is really very powerful weapon, never been done before, or since. Hey ~ ~ JAVA for the weekend to help my

  • JAVA calling Oracle stored procedure (to) 2010-12-10

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • Java ORACLE stored procedure call 2010-08-21

    1, what is stored procedure. Stored procedure is a database server-side program, it has two types. A similar SELECT query to retrieve the data, the data can be retrieved in the form of a data set returned to the customer. Another similar to the INSER

  • Java call Oracle stored procedure 2010-12-03

    1, the stored procedure is stored in the database and perform the procedure on the database side, it has two types. A similar SELECT query to retrieve the data, the data can be retrieved in the form of a data set returned to the customer. Another sim

  • Java program to call Oracle stored procedure 2011-05-16

    A: no return value stored procedure Stored procedure is: CREATE OR REPLACE PROCEDURE TESTA (PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID, I_NAME) VALUES (PARA1, PARA2); END TESTA; And then, when to call the java code with

  • java call oracle stored procedure return set 2011-06-08

    java call oracle stored procedure return set 1, pl / sql stored procedures CREATE OR REPLACE PROCEDURE retCursor(ret_cursor OUT SYS_REFCURSOR) IS ret_cursor_value SYS_REFCURSOR; BEGIN OPEN ret_cursor_value FOR SELECT * FROM emp; ret_cursor := ret_cur

  • JAVA to create Oracle stored procedure to achieve 2011-10-11

    First look at how to use JAVA to write Oracle Stored procedures, we Oracle Written statement in the SQL Plus . I. start SQL PLUS ⅱ. preparation JAVA Classes definition JAVA The category name, this stored procedure is simply output squares of paramete

  • spring calling Oracle stored procedure and returns a complete example of the result set 2010-06-12

    This is a summary of previous call to Oracle stored procedure using the spring, and with the cursor result set to return a complete example and I hope you can help. 1. Create a table: Java code create table TEST_USERS ( USER_ID VARCHAR2 (10) not null

  • Oracle stored procedure PL / Sql stored procedure call (transfer) 2011-08-01

    Summary record of the stored procedure call syntax with Java program mode A stored procedure First, we establish a simple test stored procedure table create table xuesheng (id integer, xing_ming varchar2 (25), yu_wen number, shu_xue number); insert i