Java Foundation---->java Call Oracle Stored procedure (RPM)

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 procedure Queryempinfo (Eno in Number,pename out varchar2,psal out number,pjob out varchar2) asbegin--get the Member Worker's name monthly and post select ename, Sal, job into Pename, Psal, pjob from emp where empno = Eno;end;

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

Package Com.tomhu.procedure;import Java.sql.callablestatement;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.resultset;import Java.sql.sqlexception;import oracle.jdbc.OracleTypes;    public class Procedure {private Connection conn;    Private CallableStatement Stat;    Private ResultSet 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 stored procedure public void callprocedure () {try {class.forname (drivername);            conn = drivermanager.getconnection (URL, username, password);            Stat = conn.preparecall (SQL);            One input parameter and three output parameters Stat.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);            int sal = 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 void close (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 void Main (string[] args) {new Procedure (). CallProcedure (); }}

Third, the resulting output:

Name:jones, sal:2975, Job:manager

Friendship Link

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

Http://www.cnblogs.com/huhx/p/JavaProcedure.html

Java Foundation---->java Call Oracle Stored procedure (RPM)