PL/SQL - SPLessons

PL/SQL Cursor

Chapter 19

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Cursor

PL/SQL Cursor

shape Description

PLSQL Cursor is a name given to an area and can handle the memory used by oracle for processing the SQL statements. For instance, it is used to fetch a bunch of rows from the order table or update the year & revenue, and expense information in the DWS accounting tables.

PLSQL Cursor is a fundamental and very important construct in PL/SQL. While issuing the select, update, insert or delete commands in SQL code, oracle creates an implicit cursor or an area in the memory to parse and execute the query and hold the results.

PLSQL Cursor is classified into two types, such as:

Implicit Cursor

shape Description

Whenever an SQL statement gets executed, oracle automatically creates the implicit cursors. The developer cannot handle the implicit cursor if there is no explicit cursor associated with it. For example, consider department table consisting of two columns- dept_id and dept_name. And, employee table consists of emp_name, emp_id, emp_location emp_name_id, and emp_sal columns. The emp_dept_id in employee table has been assigned the constraint of foreign key to the dept_id column of the department table. PLSQL Cursor implicit is open for issuing the select, insert or delete statements.

shape Syntax

Cursor_attribute ::=
{
cursor_name|cursor_variable_name|:host_cursor_variable_name
}
%{FOUND|ISOPEN|NOTFOUND|ROWCOUNT}

Cursor_name – The name of the cursor.

Cursor_variable_name – Selecting the name of a variable.

Oracle opens the cursors for fetching rows and automatically closes it. In the programming code, issue select statement is used to get dept_id and dept_name from the department table. And, fetch the I_dept_id and I_dept_name into the syntax of PL/SQL, which is different from the sql statement. Here, it needs the number and type of variables to be declared to fetch the select column in the sql query. For instance, I_dept_id is used to fetch the dept_id and I_dept_name is used to fetch the dept_name from the select statements. If the number and type of variables differ from the select column, oracle gives an error.

SQL%FOUND

SQL%FOUND is a built-in implicit cursor attribute and returns true if the select statement has fetched a row like updating or deleting a row. It returns false, if no such statement is found.

SQL@OPEN

 
SQL@OPEN consequently closes the SQL cursor after executing the SQL statement and returns false for implicit cursors.

SQL%NOTFOUND

SQL%NOTFOUND is opposite of SQL%FOUND.

SQL%ROWCOUNT

SQL%ROWCOUNT will give the number of rows fetched, updated or deleted.

shape Examples

The below example describes the implicit cursor:

SQL> DECLARE var_rows number(5);
  2  BEGIN
  3  UPDATE employee
  4  SET salary =salary+1000;
  5  IF SQL%NOTFOUND THEN
  6  dbms_output.put_line('None of the salaries where updated');
  7  ELSE SQL%FOUND THEN
  8  var_rows:=SQL%ROWCOUNT;
  9  dbms_output.put_line('salaries for'||var_rows||'employees are updated');
 10  END IF;
 11  END;
 12  /
PL/SQL Successfully executed.

In the above example, the emp table containing the employee salaries will be updated. If not, it will display a message ‘no salary has been updated’.

Explicit

shape Description

The cursor is defined in the declaration block of PL/SQL. It is designed on select statement and gives more than one row. For a cursor, one can assign a suitable name.

shape Syntax

The basic syntax is the keyword cursor followed by the cursor_name and then the select statement.

CURSOR<cursor_name>IS Select_statement;

Cursor_name – The name of the cursor.

Select statement – Selecting the name of the variable.

In the declaration block, two variables I_dept_name and I_dept_id are to be declared to hold the dept id and dept name values. And, then declare a cursor cur_get_department to select the dept_name and dept_id from the department where dept_id is equal to 1. Open a PLSQL Cursor in the execution section using the name. This will assign an area in memory for the cursor, parse and execute the query to get the desired output and then issue the fetch to get the first row from the result set. The number and type of variables should match the select columns. For instance, I_dept_id should match the type of dept_id and the I_dept_name should match the type of dept_name. If there are two columns in the select statement, there should be only two variables to fetch, otherwise oracle gives an error and then close the cursor.

One advantage of using this cursor is that even if there is no data found, it won’t show up any error. And, oracle gives cursor attributes in order to handle it. For example, fetching a single row from the department table. If we fetch more than one row like fetching the emp_id and emp_sal, it can be manipulated by 0.10.

%ROWTYPE

It declares a record based on the cursor column and can be accessed using dot notations.

%FOUND

If the cursor is said to be true, it will fetch the statements and returns at least one row. It doesn’t return a statement, if it is said to be false.

%NotFOUND

If the cursor is said to be true, it will fetch the statements and doesn’t return a row. It returns at least one row, if it said to be false.

ISOPEN

In the program, if the cursor is already open, it returns true. Otherwise it returns false, if the cursor is not opened in the program.

shape Examples

The below example explains the explicit cursor:

SQL> DECLARE
  2  emp_rec emp_tbl%rowtype;
  3  CURSOR emp_cur IS
  4  SELECT * FROM WHERE salary&amp;gt;10;
  5  BEGIN
  6  OPEN emp_cur;
  7  OPEN emp_cur;
  8  FETCH emp_cur INTO emp_rec;
  9  dbms_output.put_line(emp_rec.first_name||' '|| emp_rec.last_name);
 10  CLOSE emp_cur;
 11  END;
 12  /
PL/SQL program successfully executed

In the above example, emp_rec has created the emp_tbl and a record with the cursor by altering the table name with the cursor name. Declare the cursor emp_cur from the select statement. And, open the cursor to execute statements and then fetch the cursor record. After fetching, it will close the record and the cursor.

FOR Loop Cursor

shape Description

The cursor FOR loop is a very efficient way of fetching the data from cursors. The cursor For loop will get terminated when every record in the cursor has been fetched.

shape Syntax

FOR cur_rec IN<cursor_name or sql_query>LOOP
statements;
END LOOP;

Record_index – The record of the index.
Cursor_name – The cursor name that needs to get records.
Statements – Code for loop in the cursor.

shape Examples

The below example illustrates the cursor for loop statement.

SQL> DECLARE
  2  CURSOR cur_get_employee IS
  3  SELECT emp_id,emp_sal*0.01 bonus from employee;
  4  BEGIN
  5  FOR cur_get_employees_varINcur_get_employees LOOP
  6  DBMS_OUTPUT.PUT_LINE(cur_get_employees_var.emp_id);
  7  DBMS_OUTPUT.PUT_LINE(cur_get_employees_var.bonous);
  8  END LOOP;
  9  END;
 10  /
PL/SQL successfully executed.

In the above example, all the employees in the table will get bonus of 0.01% based on their emp_sal and will be added in emp_id’s.

Parameterized Cursor

shape Description

PL/SQL Parameterized cursor is used to pass parameters into a cursor and use them in a query. Parameter of datatypes and its length is not required in parameterized cursors. This cursor will be assigned a value by default and the scope of parameters is local. When the cursors are opened, then the parameterized cursors are known as static cursors. 

shape Examples

The below example explains about parameterized cursors:

SQL> declare
  2  cursor c(no number) is select * from employee25
  3  where emp_id=no;
  4  tmp employee25%rowtype;
  5  begin
  6  open c(2);
  7  for tmp IN c(2) LOOP
  8  dbms_output.put_line('EMP_Id: '||tmp.emp_no);
  9  dbms_output.put_line('EMP_Name:'||tmp.emp_name);
 10  dbms_output.put_line('EMP_Dept:'||tmp.emp_dept);
 11  dbms_output.put_line('EMP_Salary:'||tmp.emp_salary);
 12  END LOOP;
 13  CLOSE C;
 14  END;
 15  /
SQL>@parameter_cursor_demo
EMP_Id:2
EMP_Name:Maddi
EMP_Dept:Backend
EMP_Salary:13000
PL/SQL procedure successfully completed.

In the above example, parameterized cursor is implemented on employee25 database table. By assigning the cursor parameter on emp_id=2, the @parameter_cursor_demo will display the employee id(2) values such as emp_id, emp_name, emp_salary and emp_dept.

Summary

shape Key Points

  • PLSQL Cursor – Cursor is a handle or the name of an area.
  • Implicit cursor – To process the SQL statements, the database will open these implicit cursors.
  • Explicit cursors – Returns more than one row when created in the select statement.
  • Cursor For Loop – When every record in the cursor has been acquired, it will get terminated.
  • Parameterized cursors – Defines the datatype of a parameter.