Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - What Is the Implicit Cursor

By: FYIcenter.com

(Continued from previous topic...)

What Is the Implicit Cursor?

The implicit cursor is the cursor automatically defined by PL/SQL for you. Whenever a SQL statement is executed, this cursor will be assigned to represent the execution of this statement. This implicit cursor is called SQL. It has many attributes representing some good information about the execution like:

  • SQL%FOUND - True, if the SQL statement has changed any rows.
  • SQL%NOTFOUND - True, if the SQL statement has not changed any rows.
  • SQL%ROWCOUNT - The number of rows affected by the SQL statement.

See the example below on how to use the implicit cursor:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL);
Table created.

DECLARE
  id NUMBER;
  first_name CHAR(10);
BEGIN
  id := 29;
  first_name := 'Bob';
  INSERT INTO student VALUES(id, first_name, 'Henry');
  first_name := 'Joe';
  INSERT INTO student VALUES(id+1, first_name, 'Bush');

  first_name := 'Fyi';
  UPDATE student SET first_name = first_name WHERE id = 29;
  IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('# of rows updated: ' ||
        SQL%ROWCOUNT);
  END IF;

  UPDATE student SET first_name = first_name
    WHERE id = id+1;
  IF SQL%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE('No records updated.');
  END IF;
    
  DELETE FROM student WHERE id = id;                    
  DBMS_OUTPUT.PUT_LINE('# of rows updated: ' ||
    SQL%ROWCOUNT);
END; 
/
# of rows updated: 1
No records updated.
# of rows updated: 2

(Continued on next topic...)

  1. Can DML Statements Be Used in PL/SQL?
  2. Can DDL Statements Be Used in PL/SQL?
  3. Can Variables Be Used in SQL Statements?
  4. What Happens If Variable Names Collide with Table/Column Names?
  5. How To Resolve Name Conflicts between Variables and Columns?
  6. How To Assign Query Results to Variables?
  7. Can You Assign Multiple Query Result Rows To a Variable?
  8. How To Invoke Built-in Functions in PL/SQL?
  9. How To Retrieve the Count of Updated Rows?
  10. What Is the Implicit Cursor?
  11. How To Assign Data of the Deleted Row to Variables?
  12. What Is a RECORD in PL/SQL?
  13. How To Define a Specific RECORD Type?
  14. How To Define a Variable of a Specific RECORD Type?
  15. How To Assign Values to Data Fields in RECORD Variables?
  16. How To Retrieve Values from Data Fields in RECORD Variables?
  17. How To Define a Data Field as NOT NULL?
  18. How To Define a RECORD Variable to Store a Table Row?
  19. How To Assign a Table Row to a RECORD Variable?
  20. How To Insert a RECORD into a Table?
  21. How To Update a Table Row with a RECORD?
  22. How To Define a Variable to Match a Table Column Data Type?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...