PL SQL Tutorial

• Pl Sql Introduction
• Pl Sql Blocks
• Pl Sql Operators
• Pl Sql Variables
• Pl Sql Conditional Statements
• Pl Sql Iterative Statements
• Pl Sql Implicit Cursors
• Pl Sql Explicit Cursors

Pl Sql Tutorial

Pl Sql Implicit Cursors

When you run a command SQL, Oracle Server opens a range of memory (context area) in which the command is executed. The cursor is a pointer to this zone.

PL/SQL use two types of cursors:
Implicit Cursors: all instructions stated to PL/SQL type DML (INSERT/UPDATE/DELETE);
Explicit Cursors: declared and managed by the programmer.

Implicit Cursors

- It is declared by PL/SQL by default for all commands for handling the data (INSERT, UPDATE, DELETE);
- If a DML statement does not affect a line of the table does not generate an error, but the exception should be treated using special attributes of cursors;

Implicit Cursors attributes:
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN

Example:

Delete a product from the PRODUCTS table and counts the number of rows deleted.

SET SERVEROUTPUT ON DECLARE
  v_rez NUMBER (2);
BEGIN
  DELETE FROM products
  WHERE id = 121;
  v_rez: = SQL%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE (v_rez || ' article deleted ');
  COMMIT;
END;