SQL Cursors

SQL Cursors

In this tutorial, we are going to discuss about SQL Cursors. SQL cursors are database objects used to traverse the result set of a SQL query one row at a time. They provide a mechanism for iterative processing of query results, enabling finer-grained control over data retrieval and manipulation within database programs.

The results of SQL Queries will be stored in special memory area inside database software. This memory area is called Context Area. To access Results of this context area,Some pointers are required and these pointers are nothing but cursors. Hence the main objective of cursor is to access results of SQL Queries.

SQL Cursors

There are 2 types of cursors

  1. Implicit cursors
  2. Explicit cursors
1. Implicit Cursors

These cursors will be created automatically by database software to hold results whenever a particular type of SQL query got executed. 

2. Explicit Cursors

These cursors will be created explicitly by the developer to hold results of particular SQL queries.

E.g 1

SYS_REFCURSOR can be used to access result of select query i.e., to access ResultSet.

E.g 2

%ROWCOUNT is an implicit cursor provided by Oracle to represent the number of rows effected becausez of insert, delete and update queries.

E.g 3

%FOUND is an implicit cursor provided by Oracle to represent whether any rows effected or not b’z of insert,delete and update operations(non-select query).

SYS_REFCURSOR VS OracleTypes.CURSOR

To register SYS_REFCURSOR type OUT parameter JDBC does not contain any type. To handle this situation, Oracle people provided OracleTypes.CURSOR

OracleTypes is a java class present in oracle.jdbc package and it is available as the part of ojdbc6.jar If OUT parameter is SYS_REFCURSOR type,then we can get ResultSet by using getObject() method. But return type of getObject() method is Object and hence we should perform typecasting.

ResultSet rs = (ResultSet)cst.getObject(1);

create or replace prodecure getAllEmpInfo(emps OUT SYS_REFCURSOR) as
BEGIN
   OPEN emps for
   select * from emp;
end;

CallableStatement cst = con.prepareCall("{call getAllEmpInfo(?)}");
cst.registerOutParameter(1,OracleTypes.CURSOR);
cst.execute(); 
ResultSet rs = (ResultSet)cst.getObject(1); 
while(rs.next()) { 
   System.Out.P(rs.getInt(1)+".."+rs...); 
}

/

JDBC Program to call StoredProcedure which returns all Employees info by using SYS_REFCURSOR

create or replace prodecure getAllEmpInfo(sal IN num, emps OUT SYS_REFCURSOR) as
BEGIN
   OPEN emps for
   select * from emp where esal<sal;
end;
import java.sql.*; 
import java.util.*; 
import oracle.jdbc.*;// for OracleTyes.CURSOR and it is present in ojdbc6.jar
/**
 * 
 * @author ashok.mariyala
 *
 */
class StoredProceduresDemo { 
   public static void main(String[] args) throws Exception { 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","ashok","ashok");
      CallableStatement cst=con.prepareCall("{call getAllEmpInfo2(?,?)}"); 
      Scanner sc = new Scanner(System.in); 
      System.out.println("Enter initial characters of the name"); 
      String initialchars=sc.next()+"%";
      cst.setString(1,initialchars);
      cst.registerOutParameter(2,OracleTypes.CURSOR);
      cst.execute(); 
      ResultSet rs = (ResultSet)cst.getObject(2); 
      boolean flag= false; 
      System.out.println("Eno\tEName\tESalary\tEAddress"); 
      System.out.println("-----------------------");
      while(rs.next()) { 
         flag=true; 
   System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getFloat(3)+"\t"+rs.getString(4));
      } 
      if(flag== false) {
         System.out.println("No Recors Available");
      }
      con.close(); 
   }
}

In summary, SQL cursors offer a powerful mechanism for iterative processing of query results within database programs. While they provide flexibility and control, they should be used judiciously and optimized for performance to avoid potential drawbacks.

That’s all about the SQL Cursors. If you have any queries or feedback, please write us email at contact@waytoeasylearn.com. Enjoy learning, Enjoy Java.!!

SQL Cursors
Scroll to top