Java Foundation---->java Call Oracle Stored procedure

Stored procedures are in a large database system, a set of SQL statements to complete a specific function, stored in the database, after the first compilation after the call does not need to compile again, the user by specifying the name of the stored procedure and give the parameters (if the stored procedure with parameters) to execute it. Today, we start learning about the stored procedures that call Oracle in Java.

Calling Oracle's stored procedures in Java

The project structure is as follows:

A script that creates stored procedures in the database, if you are using a local Oracle database, you need to turn on services: Oracleoradb11g_home1tnslistener and ORACLESERVICEORCL.

Stored Procedure implementation: Enter the user's work number, output the user's name, salary and work.

Create or Replace procedureQueryempinfo (Enoinch  Number, Pename outvarchar2, Psal out Number, Pjob outvarchar2) asbegin--get the employee's name monthly salary and positionSelectename, Sal, Job intoPename, Psal, Pjob fromEmpwhereEmpno=Eno;End;

Second, in the project to introduce the Oracle JDBC Jar package, the procedure code is as follows:

 Packagecom.tomhu.procedure;Importjava.sql.CallableStatement;Importjava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importoracle.jdbc.OracleTypes; Public classProcedure {PrivateConnection Conn; Privatecallablestatement Stat; PrivateResultSet rs; String URL= "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL"; String drivername= "Oracle.jdbc.driver.OracleDriver"; String username= "Scott"; String Password= "******"; String SQL= "Call Queryempinfo (?,?,?,?)"; //call a stored procedure     Public voidcallprocedure () {Try{class.forname (drivername); Conn=drivermanager.getconnection (URL, username, password); Stat=conn.preparecall (SQL); //one input parameter and three output parametersStat.setint (1, 7566); Stat.registeroutparameter (2, Oracletypes.varchar); Stat.registeroutparameter (3, Oracletypes.number); Stat.registeroutparameter (4, Oracletypes.varchar);            Stat.execute (); String name= Stat.getstring (2); intSal = Stat.getint (3); String Job= Stat.getstring (4); System.out.println ("Name:" + name + ", sal:" + sal + ", Job:" +job); } Catch(Exception e) {e.printstacktrace (); } finally{Close (conn, stat, RS); }    }    //Close Connection     Public voidClose (Connection conn, callablestatement Stat, ResultSet rs) {if(rs! =NULL) {            Try{rs.close (); } Catch(SQLException e) {e.printstacktrace (); } finally{RS=NULL; }        }        if(Stat! =NULL) {            Try{stat.close (); } Catch(SQLException e) {e.printstacktrace (); } finally{stat=NULL; }        }        if(Conn! =NULL) {            Try{conn.close (); } Catch(SQLException e) {e.printstacktrace (); } finally{conn=NULL; }        }    }     Public Static voidMain (string[] args) {NewProcedure (). CallProcedure (); }}

Third, the resulting output:

2975, Job:manager

Friendship Link

JDBC Jar Package: Http://pan.baidu.com/s/1jHUTPRo

Java Foundation---->java Call Oracle Stored procedure