Write a program in PL/SQL to insert data into two tables from one table using an implicit cursor

A Cursor is a pointer to this context area. Oracle creates context area for processing an SQL statement which contains all information about the statement.

PL/SQL allows the programmer to control the context area through the cursor. A cursor holds the rows returned by the SQL statement. The set of rows the cursor holds is referred as active set. These cursors can also be named so that they can be referred from another place of the code.

In this tutorial you will learn-

• Implicit Cursor
• Explicit Cursor
• Cursor Attributes
• FOR Loop Cursor statement

The cursor is of two types.

• Implicit Cursor
• Explicit Cursor

Implicit Cursor

Whenever any DML operations occur in the database, an implicit cursor is created that holds the rows affected, in that particular operation. These cursors cannot be named and, hence they cannot be controlled or referred from another place of the code. We can refer only to the most recent cursor through the cursor attributes.

Explicit Cursor

Programmers are allowed to create named context area to execute their DML operations to get more control over it. The explicit cursor should be defined in the declaration section of the PL/SQL block, and it is created for the ‘SELECT’ statement that needs to be used in the code.

Below are steps that involved in working with explicit cursors.

• Declaring the cursor Declaring the cursor simply means to create one named context area for the ‘SELECT’ statement that is defined in the declaration part. The name of this context area is same as the cursor name.

• Opening CursorOpening the cursor will instruct the PL/SQL to allocate the memory for this cursor. It will make the cursor ready to fetch the records.

• Fetching Data from the CursorIn this process, the ‘SELECT’ statement is executed and the rows fetched is stored in the allocated memory. These are now called as active sets. Fetching data from the cursor is a record-level activity that means we can access the data in a record-by-record way. Each fetch statement will fetch one active set and holds the information of that particular record. This statement is same as ‘SELECT’ statement that fetches the record and assigns to the variable in the ‘INTO’ clause, but it will not throw any exceptions.

• Closing the CursorOnce all the record is fetched now, we need to close the cursor so that the memory allocated to this context area will be released.

Syntax:

• In the above syntax, the declaration part contains the declaration of the cursor and the cursor variable in which the fetched data will be assigned.
• The cursor is created for the ‘SELECT’ statement that is given in the cursor declaration.
• In execution part, the declared cursor is opened, fetched and closed.

Cursor Attributes

Both Implicit cursor and the explicit cursor has certain attributes that can be accessed. These attributes give more information about the cursor operations. Below are the different cursor attributes and their usage.

Explicit Cursor Example:
In this example, we are going to see how to declare, open, fetch and close the explicit cursor.

We will project all the employee’s name from emp table using a cursor. We will also use cursor attribute to set the loop to fetch all the record from the cursor.

Output

Code Explanation:

• Code line 2: Declaring the cursor guru99_det for statement ‘SELECT emp_name FROM emp’.
• Code line 3: Declaring variable lv_emp_name.

• Code line 5: Opening the cursor guru99_det.
• Code line 6: Setting the Basic loop statement to fetch all the records in the ’emp’ table.
• Code line 7: Fetches the guru99_det data and assign the value to lv_emp_name.
• Code line 9: Using the cursor attribute ‘%NOTFOUND’ to find whether all the record in the cursor is fetched. If fetched then it will return ‘TRUE’ and control will exit from the loop, else the control will keep on fetching the data from the cursor and print the data.
• Code line 11: EXIT condition for the loop statement.
• Code line 12: Print the fetched employee name.
• Code line 14: Using the cursor attribute ‘%ROWCOUNT’ to find the total number of records that got affected/fetched in the cursor.
• Code line 15: After exiting from the loop the cursor is closed and the memory allocated is set free.

FOR Loop Cursor statement

“FOR LOOP” statement can be used for working with cursors. We can give the cursor name instead of range limit in the FOR loop statement so that the loop will work from the first record of the cursor to the last record of the cursor. The cursor variable, opening of cursor, fetching and closing of the cursor will be done implicitly by the FOR loop.

Syntax:

• In the above syntax, the declaration part contains the declaration of the cursor.
• The cursor is created for the ‘SELECT’ statement that is given in the cursor declaration.
• In execution part, the declared cursor is setup in the FOR loop and the loop variable ‘I’ will behave as cursor variable in this case.

Oracle Cursor for Loop Example:
In this example, we will project all the employee name from emp table using a cursor-FOR loop.

Output

Code Explanation:

• Code line 2: Declaring the cursor guru99_det for statement ‘SELECT emp_name FROM emp’.
• Code line 4: Constructing the ‘FOR’ loop for the cursor with the loop variable lv_emp_name.
• Code line 5: Printing the employee name in each iteration of the loop.
• Code line 8: Exit the loop

Note: In Cursor-FOR loop, cursor attributes cannot be used since opening, fetching and closing of the cursor is done implicitly by FOR loop.

In this tutorial we will learn Cursor in PL/SQL which is nothing but a pointer to the work area or context area of oracle engine.

A cursor is a pointer to the work area or context area, used by the oracle engine for executing SQL statements. Such a work area is privately used for SQL operations by the oracle engine.

When the oracle engine executes an SQL statements, the row of data returned is stored in cursor and is called active data set. And the cursor occupies memory size required to hold the number of rows in the active dataset.

Cursor containing the values retrieved from a table are opened in the predefined area of the main memory by oracle engine. This data is then transferred to the client machine via network.

Types of Cursor in PL/SQL

Cursor can be divided into two types based on the condition under which they are created and used:

1. Implicit Cursor
2. Explicit Cursor

Implicit Cursor

The cursor which is automatically created, maintained and closed by the Oracle engine while execution of any DML(Data Manipulation Language) queries like INSERT, UPDATE or DELETE are called Implicit Cursor.

Implicit Cursors are controlled by Oracle and programmers cannot access its information.

When a DML statement is executed an implicit cursor is created and attached to it.

Explicit Cursor

The cursor which has to be created, maintained and closed by a program through PL/SQL code for the execution of any SELECT query that returns more than one row is called Explicit Cursor.

It is a user-defined cursor declared in the Declare section of PL/SQL block and is used in its Executable section.

Using Explicit Cursor

To define a cursor, one need to follow the following steps:

• DECLARE the Cursor

  It is done in the Declare section of the PL/SQL code by writing SQL statement that retrieves data for processing.

  Syntax: CURSOR <cursor_name> IS <SELECT query>;

  For example, if we have users table with columns id,name and email, then for executing a SELECT query this is how we can declare a cursor:

  CURSOR c_users IS SELECT id, name, email FROM users;
  

• OPEN the Cursor

  It is done in the Begin section of the PL/SQL code. By opening the cursor, the cursor is allocated the memory for fetching records.

  Syntax: OPEN <cursor_name>;

  For the c_users cursor declared above, it will be:

  OPEN c_users;
  

• FETCH the Cursor

  To fetch the data from the cursor one row at a time into memory variables we use the FETCH command.

  Syntax:

  fetch <cursor_name> into <list_of_variables>;
  

• To check whether the cursor is open or not we use the %ISOPEN attribute of the cursor.

  Syntax:

  if <cursor_name> %ISOPEN
• To determine whether fetch was successful or not. This can be done by using FOUND or NOT FOUND attributes because if fetch command fails to retrieve any row from cursor then it sets found as false and not found as true.
• For processing the data held in the memory variables we need a loop(We will come to this in a short while).
• Use the Exit statement to exit from the loop after the processing is complete.
• Then, to Close the cursor, use the CLOSE command.
Syntax: CLOSE cursorname;

To work with the cursor whether Implicit or Explicit cursor, there are following attributes which are used:

Time for an Example!

Finally, let's see a cursor in action. Below we have a student table with 4 columns namely, ROLLNO, SNAME, AGE, COURSE.

Above table student will be used in following program, where we will use the SELECT query to fetch the names of all the students, store them in a cursor and then loop around the cursor to print the names.

Anu Asha Arpit Chetan Nihal Total Record: 5 PL/SQL procedure successfully completed.

In the above program,

• We used a cursor named as student_cursor.
• The output shows the names of students with the total number of records found.

Cursor FOR LOOP

A Cursor FOR LOOP is a loop meant for the cursor which automatically checks for the row count and exits the loop when all the data stored in the cursor is iterated. A cursor FOR loop automatically does the following:

• Implicitly declares its loop index as a %rowtype record
• Opens the cursor
• Retrieves the record from the cursor for each iteration
• Closes the cursor after processing all the records.
• A cursor can also be closed by using EXIT or GOTO statements.

Examples showing use of CURSOR FOR LOOP

Below we have a simple PL/SQL code block showing the use of Cursor For Loop:

Anu Asha Arpit Chetan Nihal PL/SQL procedure successfully completed.

In the above program,

• The cursor named as student_cursor is used.
• The output shows the name of students.

What is Parameterized Cursor?

A parameterized cursor is a cursor with arguments and it allows us to create dynamic SQL queries with conditions containing the variables.

Here we have the syntax for declaration of Parameterized Cursor:

After declaring a parameterized cursor, when we open it we have to provide the value to be used in the parameterized cursor, like this:

Let's take an example to demonstrate the use of Parameterized Cursors:

Enter the value for d:12 -------------------- | SNAME | COURSE | ==================== | Asha | BCOM | -------------------- PL/SQL procedure successfully completed.

In the above program,

• The cursor named as student_cursor is used.
• The output shows the name and course of student whose rollno is entered by the user during execution of the program.