JDBC Tutorial
Store Procedures are named precompiled PL/SQL block of code,stored in the database. Stored Procedures are mainly used to shift business logic from application to database. Which may have parameters. Parameters are default IN parameters. Parameters can also be OUT, IN OUT. [IN] parameters are input to the stored procedure. Updating IN parameter ,will not reflect to the caller. [OUT] parameters are output from stored procedure. Stored proc assigns the value to output parameter. Input supplied to out parameter will be ignored. [IN OUT] parameters is input output parameter, Input value will be considered by stored proc and updates it, updated value will be visible to outside world.
Store Functions/Functions are also precompiled PL/SQL block of code stored in the Database, which must return a value. Function must have RETURN statement in the execution section. Return value can be any of SQL datatype or Ref Cursor. Stored Functions also accepts parameters, IN, OUT, IN OUT parameter
This Procedure uses Oracle's scott schema and emp Table. emp table has 2 columns, one is empno and another one is mgr no, mgr number is reference to employee number in the same table(emp). In this example Inner Query first finds Mgr number for a given employee number, then it searches for a match in the emp table empno. Some employees doesn't have Manager, in that case it gets 'No Manager Found'.
eno is a Employee Number. That is IN parameter.
mgrName is OUT parameter. procedure assigns the value to OUT parameter.
CREATE OR REPLACE PROCEDURE getManagerName (
eno IN NUMBER,
mgrname OUT VARCHAR2
) IS
BEGIN
SELECT
ename
INTO mgrname
FROM
emp
WHERE
empno = (
SELECT
mgr
FROM
emp e
WHERE
e.empno = eno
);
EXCEPTION
WHEN no_data_found THEN
mgrname := 'No Manager found';
END getManagerName;
Here Connection Object's prepareCall method is called to execute oracle stored procedure. It returns CallableStatement Object. Stored proc accepts one IN parameter and one OUT parameter. All OUT parameters must be registered using registerOutParameter. execute method excutes or runs the given stored procedure on the server. Once execution is successful, get the value of the OUT parameter.
CallableStatement stmt = conn.prepareCall("{call getManagerName(?,?)}");
stmt.setInt(1, 7934);
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.execute();
System.out.println("Manager "+stmt.getString(2));
This function uses Oracle's scott schema and emp Table. In emp table every employee has salary + commission. you need to add these 2 values,and return it. commission is optional field, so it can be 0,NULL,or value. If it is null, convert it to 0 using NVL function, then add both sal and commission i.e sal+NVL(commission,0). Finally Function returns salary with commission. If programmer supplied employee no, doesnot exists in the database,then NO_DATA_FOUND exception will be thrown. In that case Exception block will return NULL value.
eno is a Employee Number. That is IN parameter.
FUNCTION Returns salary +commisions.
CREATE OR REPLACE NONEDITIONABLE FUNCTION getsal (
eno IN NUMERIC
) RETURN NUMERIC AS
full_sal NUMERIC(10) := NULL;
BEGIN
SELECT
sal + nvl(comm, 0)
INTO full_sal
FROM
emp
WHERE
empno = eno;
RETURN full_sal;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('INVALID EMPNO');
RETURN full_sal;
END;
Here Connection Object's prepareCall method is called to execute oracle stored function. It returns CallableStatement Object. Stored function getsal accepts one IN parameter and RETURNS a value. RETURN value from a function must be registered using registerOutParameter, in order to access the value. execute method excutes or runs the given stored function on the server. Once execution is successful(with or without exception), a Value will be returned.
Synatx for calling a stored function in JDBC. {call ?= function(?,?,?)} With in curly braces use "call" followed by Question mark(return value) then PL/SQL assignement operator := followed by function name , optional parameters. Function accepts parameters then each param should have one Question mark. Note: JDBC calls column index in Functions also, it should start from 1. In this column Index 1 is RETURN value, 2 ,3 ... column Indexes for parameters
CallableStatement stmt = conn.prepareCall("{call ?:= getsal(?)}");
stmt.setInt(2, 7934);
stmt.registerOutParameter(1, java.sql.Types.INTEGER);
stmt.execute();
System.out.println(stmt.getInt(1));
create or replace function emp_managers
return SYS_REFCURSOR
as
manager_cur SYS_REFCURSOR;
begin
open manager_cur for select e1.empno "employee number",e1.ename "employee name",e2.ename "manager" from emp e1,emp e2
where e1.mgr = e2.empno;
return manager_cur;
end;
import oracle.jdbc.OracleCallableStatement; import oracle.jdbc.OracleTypes;
String SQL= "{call ?:= emp_managers()}";
try( OracleCallableStatement callStmt = (OracleCallableStatement) conn.prepareCall(SQL))
{
callStmt.registerOutParameter(1, OracleTypes.CURSOR);
callStmt.execute();
ResultSet rs =(ResultSet)callStmt.getObject(1);
while(rs.next())
{
System.out.println(rs.getRow()+" "+
rs.getInt("employee number")+" "+
rs.getString("employee name")+" "+
rs.getString("manager")
);
}
}
create or replace procedure manager_names(manager_cur OUT SYS_REFCURSOR)
as
begin
open manager_cur for
select concat(first_name,concat(' ',last_name)) "full name" from employees
where employee_id in(
select manager_id from employees);
end manager_names;
declare
c SYS_REFCURSOR;
full_name varchar2(50);
begin
manager_names(c);
if NOT c%isopen then
dbms_output.put_line('cursor is not open');
return;
end if;
loop
fetch c into full_name;
EXIT WHEN C%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(full_name);
end loop;
end;
JDBC API Calling Prcedure.
String SQL= "{call manager_names(?) }";
try(OracleCallableStatement callStmt = (OracleCallableStatement) conn.prepareCall(SQL)){
callStmt.registerOutParameter("manager_cur", OracleTypes.CURSOR);
callStmt.execute();
ResultSet rs =(ResultSet)callStmt.getObject("manager_cur");
while(rs.next()) {
System.out.println(rs.getRow()+" "+ rs.getString("full name"));
}
}
ADS