Back to JDBC Hub

Nearby lessons

20 of 37
JDBC - Batch ProcessingJDBC - Stored ProceduresJDBC - CursorsJDBC - Streaming DataJDBC - RowSet

JDBC - Cursors and Stored Functions

📌 What You Will Learn
  • What cursors are and the two types
  • SYS_REFCURSOR — returning a ResultSet from a procedure
  • Stored functions vs stored procedures
  • Working with Excel files (modern approach)

Cursors and Stored Functions is a fundamental part of database programming with JDBC. This lesson explains What is a Cursor?, SYS_REFCURSOR — Procedure Returning a ResultSet and Stored Functions vs Stored Procedures with complete, runnable code examples, clear step-by-step explanations, and common mistakes to avoid with exam-style MCQs at the end.

What is a Cursor?

A cursor is a database object used to access the results of a SQL query row by row. When a SELECT runs, the database stores the result in an area — a cursor lets you walk through those rows one at a time.

In simple words: A cursor is like a pointer that walks through the rows of a query result one at a time — the database creates it when a SELECT runs, and your program reads the rows through it.

There are two types of cursors:

TypeWho creates itExample
Implicit cursorThe database automatically, for every SQL statement%ROWCOUNT, %FOUND (Oracle)
Explicit cursorThe developer, for a particular querySYS_REFCURSOR (Oracle)

Two useful Oracle implicit-cursor attributes:

AttributeWhat it tells
%ROWCOUNTHow many rows were affected by the last SQL statement
%FOUNDWhether any row was affected (true/false)

SYS_REFCURSOR — Procedure Returning a ResultSet

A normal OUT parameter returns a single value. To return a whole result set from a procedure, Oracle uses the SYS_REFCURSOR type. From JDBC we register it with OracleTypes.CURSOR and read it as a ResultSet.

Example02
JCode Cell
1-- Oracle procedure that returns all employees
2CREATE OR REPLACE PROCEDURE getAllEmpInfo(emps OUT SYS_REFCURSOR) AS
3BEGIN
4 OPEN emps FOR SELECT * FROM employee;
5END;

SYS_REFCURSOR — Procedure Returning a ResultSet

Trainer's Note: JDBC itself has no SYS_REFCURSOR type, so we use the vendor-specific OracleTypes.CURSOR. That is why this pattern is database-specific (Oracle). MySQL typically returns such data through a simple SELECT in the procedure instead.
In simple words: SYS_REFCURSOR is an OUT parameter that lets a stored procedure return a whole result set to Java — you register it with OracleTypes.CURSOR, and Java reads it as a normal ResultSet.
Example03
JCode Cell
1import java.sql.*;
2 
3public class RefCursorDemo {
4 public static void main(String[] args) throws Exception {
5 Connection con = DriverManager.getConnection(
6 "jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
7 
8 CallableStatement cst = con.prepareCall("{call getAllEmpInfo(?)}");
9 cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
10 cst.execute();
11 
12 // The OUT parameter holds a ResultSet - read it with getObject
13 ResultSet rs = (ResultSet) cst.getObject(1);
14 while (rs.next()) {
15 System.out.println(rs.getInt(1) + " " + rs.getString(2));
16 }
17 con.close();
18 }
19}

Stored Functions vs Stored Procedures

A function is like a procedure but it returns a value and is used inside SQL expressions.

PointStored procedureStored function
ReturnsZero or more values (OUT params), or nothingExactly one value
Called fromJava (CallableStatement)Java or inside SQL (SELECT fun() ...)
KeywordCREATE PROCEDURECREATE FUNCTION
Used forActions (insert, transfer, report)Computing a value (salary bonus, tax)
Example04
JCode Cell
1-- Create a function that returns the annual salary
2CREATE FUNCTION annualSal(sal DOUBLE) RETURNS DOUBLE
3RETURN sal * 12;

Stored Functions vs Stored Procedures

Example05
JCode Cell
1// Call it from Java
2String sql = "SELECT annualSal(salary) FROM employee WHERE id=101";
3ResultSet rs = st.executeQuery(sql);
4rs.next();
5System.out.println("Annual salary: " + rs.getDouble(1));
📝 Key Takeaways
  • Cursor = database object to walk through SQL results row by row.
  • Implicit cursors are auto-created (%ROWCOUNT, %FOUND); explicit ones are created by the developer (SYS_REFCURSOR).
  • SYS_REFCURSOR lets a procedure return a whole ResultSet; register it with OracleTypes.CURSOR.
  • Function returns one value and can be used inside SQL; procedure performs actions.
  • The old Excel-via-ODBC approach is gone; use Apache POI to read/write Excel today.

🧠 Test Your Knowledge

4 Questions
Progress: 0 / 4