JDBC calls Oracle stored procedure operations

Create a parameter stored function findempnameandsal (number), query number No. 7902 employee's name and monthly salary, "return multiple values, demonstrate the use of out"
When 2 or more values are returned, an out symbol must be used
When 1 values are returned, there is no need for an out symbol

Create or Replace function findempnameandsal(pempno in Number,pename out VARCHAR2) return number
As
Psal Emp.sal%type;
Begin
Select Ename,sal to Pename,psal from EMP where empno=pempno;
--Return monthly salary
return psal;
End
/

---------------------------------------Reciprocal values

The following is a Test call

Declare
Psal Emp.sal%type;
Pename Emp.ename%type;
Begin
Psal: = Findempnameandsal (7902,pename);
Dbms_output.put_line (' Name of Employee No. No. 7902 ' | | pename| | ', Salary is ' | | PSAL);
End
/

Stored Procedures

Create a parameter stored procedure findempnameandsalandjob (number), query number No. 7902 employee's name, position, monthly salary "Demo out usage"
Create or Replace procedure
Findempnameandsalandjob (Pempno in Number,pename out varchar2,pjob off varchar2,psal out number)
As
Begin
Select Ename,job,sal to Pename,pjob,psal from EMP where empno=pempno;
End
/

The following test calls

Declare
Pename Emp.ename%type;
Pjob Emp.job%type;
Psal Emp.sal%type;
Begin
Findempnameandsalandjob (7902,pename,pjob,psal);
Dbms_output.put_line (' The name of Employee No. No. 7902 is ' | | pename| | ': ' | | pjob| | ': ' | | PSAL);
End
/

JDBC Connect Oracle

1  PackageCn.itcast.web.oracle.util;2 3 Importjava.sql.Connection;4 ImportJava.sql.DriverManager;5 ImportJava.sql.ResultSet;6 Importjava.sql.Statement;7 8  Public classJdbcutil {9     Private StaticString Driver = "Oracle.jdbc.driver.OracleDriver";Ten     //1521 is the primary port, or it may be a different port to connect to the Oracle database One     Private StaticString url = "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL"; A     Private StaticString username = "Scott"; -     Private StaticString password = "Friends"; -     Private StaticConnection Conn; the     Private StaticStatement stmt; -     Private StaticResultSet rs; -     //Registering Database Drivers -     Static{ +         Try { - Class.forName (driver); +}Catch(Exception e) { A e.printstacktrace (); at             Throw NewRuntimeException ("Oracle Driver registration Failed"); -         } -     } -     //Get database connection -      Public StaticConnection getconnection () { -Connection conn =NULL; in         Try { -conn =drivermanager.getconnection (Url,username,password); to}Catch(Exception e) { + e.printstacktrace (); -             Throw NewRuntimeException ("Oracle Connection Acquisition failed"); the         } *         returnConn; $     }Panax Notoginseng     //Close the Connection object -          Public Static voidClose (Connection conn) { the             if(conn!=NULL){ +                 Try { A conn.close (); the}Catch(Exception e) { + e.printstacktrace (); -                     Throw NewRuntimeException ("Oracle Connection shutdown failed"); $                 } $             } -         } -          Public Static voidClose (Statement stmt) { the             if(stmt!=NULL){ -                 Try {Wuyi stmt.close (); the}Catch(Exception e) { - e.printstacktrace (); Wu                     Throw NewRuntimeException ("Oracle Connection shutdown failed"); -                 } About             } $         } -          Public Static voidClose (ResultSet rs) { -             if(rs!=NULL){ -                 Try { A rs.close (); +}Catch(Exception e) { the e.printstacktrace (); -                     Throw NewRuntimeException ("Oracle Connection shutdown failed"); $                 } the             } the         } the}

Test

1     @Test 2      Public void testconnection () 3     {4         Connection conn=jdbcutil.getconnection (); 5         SYSTEM.OUT.PRINTLN (conn==null? " No ":" Yes "); 6     }7     

Test stored Procedures

1 //Java calls stored procedures and functions2  Public classOracledao {3     //call a stored procedure4 @Test5      Public voidCallProcedure ()throwsexception{6Connection conn =jdbcutil.getconnection ();7         //first parameter: in number 79028         //second parameter: Out name9         //third parameter: Out workTen         //Fourth parameter: Out salary OneString sql = "Call Findempnameandsalandjob (?,?,?,?)"; A         //Create an object dedicated to calling a procedure or function -CallableStatement cstmt =conn.preparecall (SQL); -         //set in, out values for the? Placeholder the         //hibernate starting from 0, JDBC starting from 1 -Cstmt.setint (1,7902);//In value -Cstmt.registeroutparameter (2,types.varchar);//Out value -Cstmt.registeroutparameter (3,types.varchar);//Out value +Cstmt.registeroutparameter (4,types.integer);//Out value -Cstmt.execute ();//throwing a call to a stored procedure +         //receive 3 return values in turn AString ename = cstmt.getstring (2); atString job = cstmt.getstring (3); -Integer sal = Cstmt.getint (4); -         //Show -System.out.println (ename+ "The job is:" + Job + ", it is the salary is" +sal); -         //Close the Connection object - Jdbcutil.close (cstmt); in Jdbcutil.close (conn); -     } to     //calling a stored function + @Test -      Public voidCallFunction ()throwsexception{ theConnection conn =jdbcutil.getconnection (); *         //parameter one: in number numeric type $         //parameter two: Out name string typePanax Notoginseng         //return value: Out of salary numeric type -String sql = "{? = Call Findempnameandsal (?,?)} "; theCallableStatement cstmt =conn.preparecall (SQL); +Cstmt.setint (2,7788);//inch ACstmt.registeroutparameter (3,types.varchar);// out theCstmt.registeroutparameter (1,types.integer);//return value +Cstmt.execute ();//Execute call store function -String ename = cstmt.getstring (3); $Integer sal = cstmt.getint (1); $System.out.println (ename+ "'s salary is" +sal); -         //Close the Connection object - Jdbcutil.close (cstmt); the Jdbcutil.close (conn); -     }Wuyi}