Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Sunday, 3 April 2016

PL/SQL Introduction

Advantages of PL/SQL

                        Tight Integration with SQL
                        Better Performance
                        Higher Productivity
                        Full Portability
                        Tight Security
                        Access to Pre-defined Packages
                        Support for Object-Oriented Programming
                        Support for Developing Web Applications and Pages
                                  Support for Developing Web Applications and Pages

You can use PL/SQL to develop Web applications and Server Pages (PSPs).

PL/SQL Block
PL/SQL is a block structured language
It is composed of one or more blocks
Types of Blocks
Anonymous Blocks: constructed dynamically and executed only once
Named Blocks: Subprograms, Triggers, etc.
Subprograms: are named PL/SQL blocks that are stored in the database and can be invoked explicitly as and when required; e.g. Procedures, Functions and Packages
Triggers: are named blocks that are also stored in the database; Invoked implicitly whenever the triggering event occurs; e.g. Database Triggers

PL/SQL Block Structure

      A PL/SQL block has the following structure

                                      Variables, cursors, user-defined exceptions, etc.
                                      SQL and PL/SQL statements

                                      Trapping Errors occurred in executable section

Note:  BEGIN & END are compulsory statements

PL/SQL Block consists of three sections

Declarative: Contains declarations of variables, constants, cursors, user-defined exceptions and types (Optional)
Executable: Contains SQL statements to manipulate data in the database and PL/SQL statements to manipulate data in the block
Exception Handling: Specifies the actions to perform when errors and abnormal conditions arise in the executable section (Optional)

Share this article :


Post a Comment