Oracle Database Basic Operations (v)--calling stored procedures using Java

First, the Environment preparation

Log into the Oracle database Scott account and use EMP to operate.

1. Create a proc_getyearsal stored procedure

1 --get an annual salary for a specified employee2 Create or Replace procedureProc_getyearsal (vempnoinch  Number, vyearsal out Number)3  is4        5 begin6    SelectSal* A+NVL (Comm,0) intoVyearsal fromEmpwhereEmpno=Vempno;7 End;

2. Create a Proc_gettemps stored procedure (cursor)

 1  create  or  replace  procedure   Proc_gettemps (vemps out Sys_refcursor)  2  is  3  begin  4  open  vemps for  select  *  from  emp where  deptno =  20  ;  5  end ; 

3. Import the database driver package--ojdbc14.jar

Second, Java code example

1  Packagecom.pri.test;2 3  Public classTestProcedure {4 5  　　 /*6 java Invoke stored procedure template (i)7 get single-value operations8 　　 */9 @TestTen      Public voidTEST01 ()throwsException { One         //1. Registration Driver AClass.forName ("Oracle.jdbc.driver.OracleDriver"); -         //2. Get the connection -String url = "JDBC:ORACLE:THIN:@10.211.55.29:1521/ORCL"; theString user = "Zhangsan"; -String password = "zs123"; -Connection conn =drivermanager.getconnection (URL, user, password); -         //3. Get the object that executes SQL +String sql = "{call Proc_getyearsal (?,?)}"; -CallableStatement CallableStatement =conn.preparecall (SQL); +         //3.1 Setting output parameters ACallablestatement.setint (1,7369); at         //3.2 Registering the output type -Callablestatement.registeroutparameter (2, types.double); -         //4. Execute SQL - Callablestatement.execute (); -         //5. Implementation results -         DoubleYearsal = callablestatement.getdouble (2); inSYSTEM.OUT.PRINTLN ("Annual Salary:" +yearsal); -         //6. Releasing Resources to callablestatement.close (); + conn.close (); -     } the  　　  *  　　 /* $ java Call stored procedure template (ii)Panax Notoginseng multi-row record (cursor) operations -  　　 */ the @Test +      Public voidtest03 ()throwsException { A         //1. Registration Driver theClass.forName ("Oracle.jdbc.driver.OracleDriver"); +         //2. Get the connection -String url = "JDBC:ORACLE:THIN:@10.211.55.29:1521/ORCL"; $String user = "Zhangsan"; $String password = "zs123"; -Connection conn =drivermanager.getconnection (URL, user, password); -         //3. Get the object that executes SQL theString sql = "{call Proc_gettemps (?)}"; -CallableStatement CallableStatement =conn.preparecall (SQL);Wuyi         //3.1 Registering the output type theCallablestatement.registeroutparameter (1, oracletypes.cursor); -         //4. Execute SQL Wu Callablestatement.execute (); -         //5. Get Results About System.out.println (Callablestatement.getclass (). GetName ()); $ //t4ccallablestatent call2 = () callablestatement; -Oraclecallablestatement call2 =(oraclecallablestatement) callablestatement; -ResultSet rs = call2.getcursor (1); -  A          while(Rs.next ()) { +System.out.println (Rs.getobject ("Empno")); theSystem.out.println (Rs.getobject ("ename")); -System.out.println (Rs.getobject ("Sal")); $System.out.println ("------------------------"); the         } the         //6. Releasing Resources the rs.close (); the callablestatement.close (); - conn.close (); in     } the  the}

Oracle Database Basic Operations (v)--calling stored procedures using Java