Skip to main content

Cursor and Types of Cursor in Oracle with Examples #Diksha Oracle Training Centre

Cursor and Types of Cursor in Oracle with Examples.

 

Hello Everyone,

Today I will discuss about Cursor in Oracle and it’s uses in PL/SQL anonymous block. Cursor is very important part in PL/SQL .Cursor act as a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. For example: The number of rows processed through multiple table and that cursor can be used in that block. For our convenient we name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time.

There are three types of cursors  in Oracle

·        Implicit cursors : Maintained By Oracle Server Itself

·        Explicit cursors : Controlled by programmer so in Explicit Cursor Programmer need to follow the below step to use a cursor in PL/SQL Block.

            Declare the Cursor

            Open the Cursor

            Fetch the data from into PL/SQL variables or in tables

            Close the Cursor


·       The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record.

Let’s understand the Syntax and Program for both Explicit Cursor as well as cursor FOR Loop.

Syntax for Explicit Cursor:

DECLARE

CURSOR <cursor_name> IS <SELECT statement>

<cursor_variable declaration>

BEGIN

OPEN <cursor_name>;

FETCH <cursor_name> INTO <cursor_variable>;

CLOSE <cursor_name>;

END;

Example: We take data from two tables in Cursor and fetch the records and display that record by using Cursor in Pl/SQL block. Below is the Program.

declare

v_id employee.emp_id%type;   

v_name employee.emp_name%type;

v_address employee.address%type;

v_dept department.dept_name%type;

v_salary department.salary%type;

cursor c1 is select a.emp_id,a.emp_name,a.address,b.dept_name,b.salary

from employee a , department b

where a.emp_id=b.emp_id

AND B.LOCATION IN('DELHI','BANGALORE');

BEGIN

OPEN c1;

LOOP

FETCH c1 into v_id,v_name,v_address,v_dept,v_salary;

exit when c1%notfound;

dbms_output.put_line(v_id||' '||v_name||' '||v_address||' '||v_dept||' '||v_salary);

end loop;

close c1;

end;

In above program we simply fetch the records from employee and department table by using Cursor name C1 and put that value in variables and display records by using dbms_output.put_line . I use %type in variable as it automatically takes the data type of particular column from different tables also we can use varchar2 and number instead of %type , but for programmer it’s good to use %type so that they don’t need to check table description again and again to see the data types of particular column.

Note : Details about %type and %rowtype attribute in Oracle I will cover in next blog.

Below is the output of above program in Oracle.

 


 

Another example of PL/SQL Anonymous block where I put the values in a table by using explicit cursor. Program will be same only thing we put the data in empty table. Below is the program.

declare

v_id employee.emp_id%type;   

v_name employee.emp_name%type;

v_address employee.address%type;

v_dept department.dept_name%type;

v_salary department.salary%type;

cursor c1 is select a.emp_id,a.emp_name,a.address,b.dept_name,b.salary

from employee a , department b

where a.emp_id=b.emp_id

AND B.LOCATION IN('DELHI','BANGALORE');

BEGIN

OPEN c1;

LOOP

FETCH c1 into v_id,v_name,v_address,v_dept,v_salary;

exit when c1%notfound;

insert into dummy values(v_id,v_name,v_address,v_dept,v_salary);

end loop;

close c1;

end;

 

Below is the output of above Program as Cursor data insert the value in empty dummy table.

 


Now we will do the same by using cursor for loop as it’s a shortcut method and easy to use.

Syntax for FOR CURSOR LOOP :

DECLARE

CURSOR <cursor_name> IS <SELECT statement>;

BEGIN

  FOR I IN <cursor_name>

  LOOP

  ..

  END LOOP;

END;

 

Let’s take example of  displaying records by using FOR CURSOR LOOP in shortcut method. Below is the Program.

declare

cursor c1 is select a.emp_id,a.emp_name,a.address,b.dept_name,b.salary

from employee a , department b

where a.emp_id=b.emp_id;

BEGIN

for i in c1

loop

dbms_output.put_line(i.emp_id||' '||i.emp_name||' '||i.address||' '||i.dept_name||' '||i.salary);

end loop;

end;

/

Here we see all the values is in I variable so I not declared any variable and used that variable in PL/SQL block to display the value. Below is the output of above Program.



Let’s take another example of  inserting records in dummy table by using FOR CURSOR LOOP in shortcut method. Below is the Program.

declare

cursor c1 is select a.emp_id,a.emp_name,a.address,b.dept_name,b.salary

from employee a , department b

where a.emp_id=b.emp_id;

BEGIN

for i in c1

loop

insert into dummy values(i.emp_id,i.emp_name,i.address,i.dept_name,i.salary);

end loop;

end;

/

 

Below is the output of above program in PL/SQL block in shortcut for cursor loop method.



Note: You can also use Condition(IF-ELSE) in above program by simply using one variable and can manipulate the data against the condition . If any issue writing any conditional statement please let me know.

Example of one conditional statement in FOR CURSOR loop where I check if salary is greater than 70000 data will be inserted in dummy table else it will update the dummy table and increment the salary . Below is the Program

set serveroutput on;

declare

cursor c1 is select a.emp_id,a.emp_name,a.address,b.dept_name,b.salary

from employee a , department b

where a.emp_id=b.emp_id;

BEGIN

for i in c1

loop

if i.salary>70000

THEN

insert into dummy values(i.emp_id,i.emp_name,i.address,i.dept_name,i.salary);

else

update dummy set salary=salary+salary*30/100;

end if;

end loop;

end;

/

You can Change the logic as per your Program. Below is the output of above program.

 


Please go through above programs and try to write as many cursors as you can so that it will be good for you once you start writing PL/SQL procedures, functions , packages etc.

Read this blog carefully and let me know if you have any questions. This blog is very important for PL/SQL developer as cursor plays important role in writing PL/SQL sub-programs/Named block and once you have good hands on Anonymous block and good experience in writing cursors you can able to write Stored Procedures/Functions and Packages in Oracle. This blog is also good for candidate who is preparing for job interview as database developer. In next blog I will come up with another topic related to Oracle SQL and PL/SQL.

Thanks.

 


Comments

Post a Comment

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows

Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database. Hello Everyone, Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view. https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose. Let’s understand   about   Materialized view : A materialized view is a view that stores the results of the view’s query. Whenever you query the ma

Top 50 Interview Questions On SQL and PL/SQL #DOTC_Mdb

                    Top 50 Interview Questions On SQL and PL/SQL. Today we will Discuss Top 50 interview questions and answers of SQL and PL/SQL which is frequently asked in interview.     Question 1: What is SQL and Classification of SQL? Answer SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.   Classification of SQL is as below: DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc DML (Data Manipulation Language) : Commands are  insert,update and delete . TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point. DCL (Data Control Language) : Commands are Grant , Revoke Question 2:    What is meant by Joins? What are the types of join? Answer Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.   There are various types of Joins as li