PL/SQL Basics

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 statement.

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 set to 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 parameter.

The following table summarizes distinguishing characteristics of PL/SQL anonymous blocks and subprograms. Image of PL/SQL Subprogram Characteristics

Contact Us or call 1-877-932-8228