Welcome

JDBC Tutorial


JDBC- Calling stored Functions and Procedures

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

Get Manager name for a given employee number

      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;

     
     

Calling Stored Procedure in JDBC (with one IN param and one OUT param)

      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));

      
      

     

Get employee salary with commission

      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;
      

Calling Stored Function in JDBC (with IN param and RETURNS a value)

      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));

      
      

ADS