Anonymous Blocks in PL/SQL

Anonymous blocks:

  • Form the basic PL/SQL block structure
  • Initiate PL/SQL processing tasks from applications
  • Can be nested within the executable section of any PL/SQL block

PL/SQL Block Structure

DECLARE (optional)
      –Variables, cursors, user-defined exceptions
BEGIN (mandatory)
       –SQL statements
       –PL/SQL statements
EXCEPTION (optional)
   –Actions to perform when errors occur
–END; (mandatory)

Example:

DECLARE
    v_lname VARCHAR2(20);
    v_fname VARCHAR2(20);
BEGIN
   SELECT last_name,first_name
   INTO v_lname, v_fname
   FROM employees WHERE employee_id=101;
   dbms_output.put_line('last name '||v_lname);
   dbms_output.put_line('first name '||v_fname);
END;

Output:

dbms output:

Difference between Anonymous Blocks and Subprograms


Leave a Reply

Your email address will not be published. Required fields are marked *