Understanding PL/SQL blocks
A PL/SQL program comprises one or more blocks. These blocks can be entirely separate or nested within another block.
There are three types of blocks that make up a PL/SQL program:
- Anonymous blocks
Anonymous blocks are unnamed blocks. They are declared inline at the point in an application where they are to be executed and are compiled each time the application is executed. These blocks are not stored in the database. They are passed to the PL/SQL engine for execution at run time. Triggers in Oracle Developer components consist of such blocks.
If you want to execute the same block again, you have to rewrite the block. You cannot invoke or call the block that you wrote earlier because blocks are anonymous and do not exist after they are executed.
Procedures are named objects that contain SQL and/or PL/SQL statements.
Functions are named objects that contain SQL and/or PL/SQL statements. Unlike a procedure, a function returns a value of a specified data type.
Subprograms are complementary to anonymous blocks. They are named PL/SQL blocks that are stored in the database. Because they are named and stored, you can invoke them whenever you want (depending on your application). You can declare them either as procedures or as functions. You typically use a procedure to perform an action and a function to compute and return a value.
Subprograms can be stored at the server or application level. Using Oracle Developer components (Forms, Reports), you can declare procedures and functions as part of the application (a form or report) and call them from other procedures, functions, and triggers within the same application, whenever necessary.
The following table outlines a variety of PL/SQL program constructs that use the basic PL/SQL block. The program constructs are available based on the environment in which they are executed.
|Anonymous blocks||Unnamed PL/SQL blocks that are embedded within an application or are issued interactively||All PL/SQL environments|
|Application procedures or functions||Named PL/SQL blocks that are stored in an Oracle Forms Developer application or a shared library; can accept parameters and can be invoked repeatedly by name||Oracle Developer tools components (for example, Oracle Forms Developer, Oracle Reports)|
|Stored procedures or functions||Named PL/SQL blocks that are stored in the Oracle server; can accept parameters and can be invoked repeatedly by name||Oracle server or Oracle Developer tools|
|Packages (application or stored)||Named PL/SQL modules that group related procedures, functions, and identifiers||Oracle server and Oracle Developer tools components (for example, Oracle Forms Developer)|
|Database triggers||PL/SQL blocks that are associated with a database table and are fired automatically when triggered by various events||Oracle server or any Oracle tool that issues the DML|
|Application triggers||PL/SQL blocks that are associated either with a database table or system events. They are fired automatically when triggered by a DML or a system event respectively.||Oracle Developer tools components (for example, Oracle Forms Developer)|
|Object types||User-defined composite data types that encapsulate a data structure along with the functions and procedures needed to manipulate data||Oracle server and Oracle Developer tools|
Examining an Anonymous Block
An anonymous block in the SQL Developer workspace:
To create an anonymous block by using SQL Developer, enter the block in the workspace as shown above.
The example block has the declarative section and the executable section. You need not pay attention to the syntax of statements in the block; you learn the syntax later in the course. The anonymous block gets the first_name of the employee whose employee_id is 100, and stores it in a variable called v_fname.
Executing an Anonymous Block
To execute an anonymous block, click the Run Script button (or press F5).