Basic Oracle Database Operations (5) -- use java to call stored procedures and oracle stored procedures

Basic Oracle Database Operations (5) -- use java to call stored procedures and oracle stored procedures

I. Environment preparation

Log on to the Oracle database scott account and use emp to perform operations.

1. Create the proc_getyearsal Stored Procedure

1 -- get the annual salary of a specified EMPLOYEE 2 create or replace procedure proc_getyearsal (vempno in number, vyearsal out number) 3 is4 5 begin6 select sal * 12 + nvl (comm, 0) into vyearsal from emp where empno = vempno; 7 end;

2. Create a proc_gettemps Stored Procedure (cursor)

1 create or replace procedure proc_gettemps(vemps out sys_refcursor)2 is3 BEGIN4 open vemps for select * from emp where deptno = 20;5 end;

3. Import the database driver package-ojdbc14.jar

Ii. java code example

1 package com. pri. test; 2 3 public class TestProcedure {4 5/* 6 java call stored procedure template (1) 7 get a single value operation 8 */9 @ Test10 public void test01 () throws Exception {11 // 1. register the driver 12 Class. forName ("oracle. jdbc. driver. oracleDriver "); 13 // 2. obtain the connection 14 String url = "jdbc: oracle: thin: @ 10.211.55.29: 1521/orcl"; 15 String user = "zhangsan"; 16 String password = "zs123 "; 17 Connection conn = DriverManager. getConnection (url, user, passwo Rd); 18 // 3. Get the SQL Execution Object 19 String SQL = "{call proc_getyearsal (?,?)} "; 20 CallableStatement callableStatement = conn. prepareCall (SQL); 21 // 3.1 sets the output parameter 22 callableStatement. setInt (3.2); 23 // registers the output type 24 callableStatement. registerOutParameter (2, Types. DOUBLE); 25 // 4. run SQL26 callableStatement.exe cute (); 27 // 5. execution result 28 double yearsal = callableStatement. getDouble (2); 29 System. out. println ("annual salary:" + yearsal); 30 // 6. release resource 31 callableStatement. close (); 32 conn. close (); 33} 34 35/* 3 6 java calls the stored procedure template (ii) 37 multi-row record (cursor) operation 38 */39 @ Test40 public void test03 () throws Exception {41 // 1. register the driver 42 Class. forName ("oracle. jdbc. driver. oracleDriver "); 43 // 2. obtain the connection 44 String url = "jdbc: oracle: thin: @ 10.211.55.29: 1521/orcl"; 45 String user = "zhangsan"; 46 String password = "zs123 "; 47 Connection conn = DriverManager. getConnection (url, user, password); 48 // 3. get the object 49 String SQL = "{call proc_gettemps (? )} "; 50 CallableStatement callableStatement = conn. prepareCall (SQL); 51 // 3.1 register the output type 52 callableStatement. registerOutParameter (1, OracleTypes. CURSOR); 53 // 4. run SQL54 callableStatement.exe cute (); 55 // 5. obtain the Result 56 System. out. println (callableStatement. getClass (). getName (); 57 // T4CCallableStatent call2 = () callableStatement; 58 OracleCallableStatement call2 = (OracleCallableStatement) callableStatement; 59 ResultSet rs = call2.getCursor (1); 60 61 while (rs. next () {62 System. out. println (rs. getObject ("empno"); 63 System. out. println (rs. getObject ("ename"); 64 System. out. println (rs. getObject ("sal"); 65 System. out. println ("------------------------"); 66} 67 // 6. release resource 68 rs. close (); 69 callableStatement. close (); 70 conn. close (); 71} 72 73}