Unit test code for calling stored procedures in Oracle in Java

Stored Procedures in Oracle:

/*

Create a storage function and return the name, salary, and annual income of the specified employee.

*/

Create or replace function queryEmp2 (eno in number, empname out VARCHAR2, empsal out NUMBER)

-- Returns the annual income.

Return NUMBER

As

Begin

Select ename, sal into empname, empsal from emp where empno = eno;

-- Returns the annual income.

Return empsal * 12 + nvl (empsal, 0 );

End;

/

Call the unit test of the above stored procedure:

Package demo;

Import java. SQL. CallableStatement;

Import java. SQL. Connection;

Import java. SQL. DriverManager;

Import org. junit. Test;

Public class Demo {

@ Test

Public void testQueryEmpl () throws Exception {

// Register the driver

Class. forName ("oracle. jdbc. OracleDriver ");

// If the port is not 1521, change it.

String url = "jdbc: oracle: thin :@ localhost: 1521: orcl ";

// Scott in orcale is used by default.

String user = "scott ";

// Scott's username and password are tiger

String password = "tiger ";

// Call the stored procedure.

String SQL = "{call queryEmpl2 (?,?,?)} ";

Connection conn = DriverManager. getConnection (url, user, password );

CallableStatement call = conn. prepareCall (SQL );

// Assign the parameter value

Call. setInt (1, 7839 );

// For the out Parameter

// The following method tells the parameter type of the output data, that is, the varchar type in Oracle.

Call. registerOutParameter (2, oracle. jdbc. OracleTypes. VARCHAR );

Call. registerOutParameter (3, oracle. jdbc. OracleTypes. NUMBER );

// Execute

Call.exe cute ();

// Get the result and get the name and salary

String name = call. getString (2 );

Double sal = call. getDouble (3 );

System. out. println (name + "" + sal );

// Close the resource

Call. close ();

Conn. close ();

}

}