In PL/SQL code groups of commands are arranged within a block.
A block groups related declarations or statements.
A block can be contained within another block or in a larger programming unit called a subprogram.
A subprogram can be a stored procedure, function, trigger or package.
A stored procedure or simply procedure is essentially an encapsulated named block of code
stored within the database. This distinguishes it from an anonymous block which is an unnamed block
of PL/SQL code which is not stored in the Oracle database. Although an anonymous block is not stored
within the database, it is executed within the database.
A function is distinguished from a procedure in that it is able to return a value.
Both functions and procedures can take parameters and are called interactively. A procedure
cannot be called directly from SQL. A function (in most cases) can be called from a SQL
A trigger cannot take parameters and is called automatically when a database
event occurs. Examples of triggering events include the execution of DML statements (an insert, update
or delete) or DDL statements (create, alter, drop). A Package serves as a container for
functions, procedures and PL/SQL types.
One or more parameters can be specified for stored procedures or functions. A parameter is created
with a name, data type, and mode. As PL/SQL is tightly integrated with SQL, PL/SQL data types are a
superset of SQL datatypes. The parameter mode determines a parameter's behavior. It can be
input only (IN),
output only (OUT), or
input and output (IN OUT). Therefore,
even though a procedure cannot directly return a value, a similar effect is possible using an output
The following table summarizes distinguishing characteristics of PL/SQL anonymous blocks and subprograms.