What is the structure of a PL/SQL block?

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

What is the structure of a PL/SQL block?

Rating:

Procedural Language/Structured Query Language (PL/SQL) is a procedural language extension to SQL, the standard data access language for relational databases. PL/SQL is not an Oracle product in its own right; rather, it is a technology used by Oracle server and certain Oracle tools.

PL/SQL offers modern software engineering features (such as data encapsulation, exception handling, object orientation, and information hiding) and brings hi-tech programming to the Oracle server and toolset. It also incorporates many of the advanced features of the third generation programming languages. It allows the SQL statements to be included in block-structured and procedural units of code. In this way, PL/SQL combines the ease and flexibility of SQL with the structured programming language constructs such as IF...THEN, WHILE, and LOOP, thereby making PL/SQL a powerful transaction processing language.

PL/SQL blocks are passed to and processed by a PL/SQL engine, which may reside within the Oracle tool that passes the blocks or within the Oracle server. The engine that is actually used depends on from where the PL/SQL block is being invoked. When PL/SQL blocks are submitted from a Pro*C or a Pro*Cobol program, iSQL*Plus, or Server Manager, then the PL/SQL engine in the Oracle server processes them. The PL/SQL engine separates the SQL statements and sends them individually to the SQL statements executer.

A PL/SQL program is comprised of one or more PL/SQL blocks. These blocks can be entirely separate or nested within one another. A PL/SQL block may consist of the following three sections:

• Declarative: The declarative section of a PL/SQL block contains all variables, constants, cursors, and user-defined exceptions that are referenced in the executable and declarative sections. The declarative section is optional in a PL/SQL block.



• Executable: The executable section contains SQL and PL/SQL statements to manipulate data in the database and in the block, respectively. The executable section is mandatory in a PL/SQL block.



• Exceptional: The exceptional section specifies the actions to be performed when errors or abnormal conditions arise in the executable section. This section is optional.

Following is the structure of a PL/SQL block:

DECLARE     -Optional
   Variables, cursors, user-defined exceptions
BEGIN     -Mandatory
   SQL statements
   PL/SQL statements
EXCEPTION     -Optional
   Actions to be performed when errors occur
END;     -Mandatory

A semicolon (;) must be placed at the end of a SQL statement or PL/SQL control statement. Section keywords (DECLARE, BEGIN, and EXCEPTION) are not followed by semicolons. The END section keyword and all other PL/SQL statements require a semicolon to terminate the statement. The statements can be stringed together on the same line, but this method is not recommended for clarity and editing. When a PL/SQL block is executed successfully without unhandled errors or compile errors, a message reading 'PL/SQL procedure successfully completed' should be displayed.

Basically, there are two types of PL/SQL blocks: anonymous block and subprogram. The basic units (subprograms and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub blocks. Therefore, one block can represent a small part of another block, which in turn can be a part of the whole unit of code.

Anonymous block: An anonymous block is an unnamed PL/SQL block. In an application, an anonymous block is declared at the point where it is to be executed and is passed to the PL/SQL engine for execution at run time. An anonymous block can be embedded within a precompiler program, iSQL*Plus, or Server Manager. Triggers in Oracle Developer components consist of such blocks.

Subprogram: A subprogram is a named PL/SQL block that can accept parameters and can be invoked. There are two types of subprograms: procedure and function. Subprograms can be stored at the server or application level. By using Oracle Developer components (Forms, Reports, and Graphics), procedures and functions can be declared as part of an application (a form or report) and be called from other procedures, functions, and triggers within the same application whenever necessary.

• Procedure: A procedure is a named PL/SQL block that can accept parameters (sometimes referred to as arguments) and be invoked. Generally, a procedure is used to perform an action. A procedure has a header, a declaration section, an executable section, and an optional exception-handling section. A procedure may or may not return values to its calling environment.
  
  A procedure can be compiled and stored in the database as a schema object for repeated execution. A procedure stored in the database is referred to as a stored procedure. Procedures promote reusability and maintainability. When validated, they can be used in any number of applications. If the functional requirements of the application change, only the procedure needs to be updated.



• Function: A function is a named PL/SQL block that can accept parameters and be invoked. Generally, a function is used to compute a value. A function is similar to a procedure except that a function must return a value to the calling environment, whereas a procedure may or may not return values to its calling environment. Like a procedure, a function has a header, a declarative part, an executable part, and an optional exception-handling part. A function must have a RETURN clause in the header and at least one RETURN statement in the executable section.
  
  Functions can be stored in the database as a schema object for repeated execution. A function stored in the database is referred to as a stored function. Functions can also be created at client-side applications. Functions promote reusability and maintainability. They can be used in any number of applications. If the processing requirements of an application change, only the function needs to be updated.

Rating: