recent

Titulo

Implicit Cursor

A cursor is a work area or a section of memory in which SQL statements are processed in the Oracle server. A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or Data Manipulation Language statements like INSERT, UPDATE, DELETE or MERGE. Implicit and explicit are two types of PL/SQL statement cursor available on PL/SQL.

Implicit Cursor:

Implicit Cursors are declared automatically for all DML and SELECT statements issued within a PLSQL block. Oracle server automatically creates Implicit cursor when a SQL statement is executed. The cursor is a work area in a memory where the statement is processed and contain the result of a SQL statements. There are several cursor attributes which allow the results of an SQL statement to be checked whether the statement affect any rows and how many? Implicit cursors are used in statement that returns only one row. Explore Explicit cursor if the SQL statement returns more than one row.

Cursor Attributes:
  1. %FOUND: Has a DML statement changed Row? Returns TRUE if an INSERT, UPDATE or DELETE statement affected one or more rows or a SELECT INTO statements returned one or more rows. For everything else, it returns FALSE. 
  2. %NOTFOUND: Has a DML statement failed to change rows? The logical opposite of %FOUND. 
  3. %ROWCOUNT: How many rows affected so far? Returns the number of rows affected by INSERT, UPDATE, or DELETE statements or returned by SELECT INTO statements.
  4. SQL the name of the implicit cursor.

Examples Using SQL%FOUND
CREATE TABLE dept_temp AS
  SELECT *
  FROM   departments;

DECLARE
    dept_no NUMBER(4) := 270;
BEGIN
    DELETE FROM dept_temp
    WHERE  department_id = dept_no;

    IF SQL%FOUND THEN -- delete succeeded
      INSERT INTO dept_temp
      VALUES      (270,
                   'Personnel',
                   200,
                   1700);
    END IF;
END;
/

Example Using SQL%FOUND
CREATE TABLE dept_temp AS
  SELECT *
  FROM   departments;

DECLARE
    dept_no NUMBER(4) := 270;
BEGIN
    DELETE FROM dept_temp
    WHERE  department_id = dept_no;

    IF SQL%FOUND THEN -- delete succeeded
      INSERT INTO dept_temp
      VALUES      (270,
                   'Personnel',
                   200,
                   1700);
    END IF;
END;
/

Example using %ROWCOUNT after UPDATE
CREATE TABLE employees_temp AS
  SELECT *
  FROM   employees;

BEGIN
    UPDATE employees_temp
    SET    salary = salary * 1.05
    WHERE  salary < 5000;

    dbms_output.Put_line('Updated '
                         || SQL%rowcount
                         || ' salaries.');
END;
/

Example Using %ROWCOUNT after DELETE
CREATE TABLE employees_temp AS
  SELECT *
  FROM   employees;
DECLARE
    mgr_no NUMBER(6) := 122;
BEGIN
    DELETE FROM employees_temp
    WHERE  manager_id = mgr_no;
    dbms_output.Put_line ('Number of employees deleted: '

                          || To_char(SQL%rowcount));
END;
/

Implicit Cursor Best Practices.
  1. The cursor attribute always display the most recently executed SQL statement.
  2. The %NOTFOUND attribute is not useful with SELECT INTO statement. If a SELECT INTO statement fails to return a row, PL/SQL raises an exception of NO_DATA_FOUND immediately. A SELECT INTO statement that invokes a SQL aggregate function always returns a value or a null. After such statement, the %NOTFOUND attribute is always FALSE.therefore you need exception that catches error.
  3. Implicit cursor is believed to be faster compared to Explicit cursor therefore use Implicit where possible.
Source: docs.oracle.com.



Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

No comments

Powered by Blogger.