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}