PL/SQL features

PL/SQL statements and scripts can be compiled and executed using database server interfaces.

You can execute the following PL/SQL statements:
  • Anonymous blocks; for example, DECLARE...BEGIN...END
  • CREATE OR REPLACE FUNCTION statement
  • CREATE OR REPLACE PACKAGE statement
  • CREATE OR REPLACE PACKAGE BODY statement
  • CREATE OR REPLACE PROCEDURE statement
  • CREATE OR REPLACE TRIGGER statement
  • CREATE OR REPLACE TYPE statement
  • DROP PACKAGE statement
  • DROP PACKAGE BODY statement

PL/SQL procedures and functions can be invoked from other PL/SQL statements or from SQL PL statements. You can call a PL/SQL procedure from SQL PL by using the CALL statement.

The following statements and language elements are supported in PL/SQL contexts:
  • Type declarations:
    • Associative arrays
    • Record types
    • VARRAY types
  • Subtype declarations
  • Variable declarations:
    • %ROWTYPE
    • %TYPE
  • Basic statements, clauses, and statement attributes:
    • Assignment statement
    • NULL statement
    • RETURNING INTO clause
    • Statement attributes, including SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT
  • Control statements and structures:
    • CASE statements:
      • Simple CASE statement
      • Searched CASE statement
    • Exception handling
    • EXIT statement
    • FOR statement
    • GOTO statement
    • IF statement
    • LOOP statement
    • PIPE ROW statement
    • RETURN statement
    • WHILE statement
  • Static cursors:
    • CLOSE statement
    • Cursor FOR loop statement
    • FETCH statement (including FETCH INTO a %ROWTYPE variable)
    • OPEN statement
    • Parameterized cursors
    • Cursor attributes
  • REF CURSOR support:
    • Variables and parameters of type REF CURSOR
    • Strong REF CURSORs
    • OPEN FOR statement
    • Returning REF CURSORs to JDBC applications
  • Error support:
    • RAISE_APPLICATION_ERROR procedure
    • RAISE statement
    • SQLCODE function
    • SQLERRM function