Recent Posts

Wednesday, 14 November 2018

Cursors

 
     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. 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.

Eg 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(); 
   }
}

Next Tutorial : Functions

Previous Tutorial : Stored Procedures and CallableStatement


No comments:

Post a Comment