PL/SQL Block Structure

PL/SQL program units organize the code into blocks. A block without name is known as anonymous block. The anonymous block is the simplest unit in PL/SQL. It is called anonymous block because it is not saved in the database. The anonymous blocks are only one-time use and useful in certain situations such as creating test units. 

The PL/SQL Engine:

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).
A Simple PL/SQL Block:

DECLARE
     Variable declaration
BEGIN
     Program Execution
EXCEPTION
     Exception handling
END;


Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

A PL/SQL Block consists of three sections:
  • The Declaration section (optional).
  • The Execution section (mandatory).
  • The Exception (or Error) Handling section (optional).

Declaration Section:

The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:

The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:

The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

1 comments:

SQL BASIC

  • Sql-Overview
  • Sql-Sysntax
  • Sql-Normalization
  • RDBMS Concept
  • Sql-Data Type
  • Sql-Operator
  • Sql-Expression
  • Create database
  • Sql-Delete
  • Sql-Select
  • Sql-Create
  • Sql-Like
  • Sql-Join
  • Sql-Insert
  • Sql-Drop Table
  • Sql-Wild Card
  • Sql-Order By
  • Sql-Group By
  • Sql-Index
  • Not Null Constraints
  • Transaction Control
  • Sql-Transaction
  • Sql-In
  • Sql-Distinct
  • Check Constraint
  • Sql Alias
  • Sql-Primary
  • Sql-Where
  • Sql-Update
  • Sql-Alias
  • Sql-Top-Rownum
  • Primary key vs Unique key
  • SQL Interview Question
  • PL/SQL BASIC

  • Variable
  • Block Structure
  • Function
  • Procedure
  • Nested Blog
  • If Statement
  • While Loop
  • For Loop
  • SEO

  • Introduction Seo
  • Top Social Bookmarking List
  • Directory Submission List
  • Classified Ads
  • Key Word Research
  • Html

  • Introduction Html
  • Introduction Css
  • Introduction Java Script
  • Unix

  • Unix
  • Software Testing

  • Software Testing
  • Computer Network

  • Computer Network
  •