PL/SQL Architecture

PL/SQL Architecture

A PL/SQL block contains both SQL statements and Procedural PL/SQL statements. The PL/SQL statements are executed by a PL/SQL execution engine, while the SQL statements are executed by the SQL Engine in the Oracle database.

The PL/SQL engine can reside either in the Oracle server or in a different server. When a PL/SQL block is submitted for execution, it first reached the PL/SQL engine. The PL/SQL engine executes the PL/SQL statements and sends the SQL statements to the SQL engine in the Oracle Database.

By submitting SQL and PL/SQL in blocks, we can reduce the number of round trips between the front end application and the Oracle database. Without PL/SQL we will have to perform the procedural and control statement execution in the front end and perform multiple round trips to the Oracle database to execute the SQL statements.

Welcome to http://plsql-guide.blogspot.com

Introduction

Welcome to the world of PL/SQL, wish you all a very Happy new year 2009 !!!

In this post we shall kick start our journey into the world of PL/SQL by defining PL/SQL, in the subsequent posts we shall uncover the features and benefits of using this powerful procedural language.

Let’s start by defining “What is PL/SQL?”

PL/SQL (Procedural Language/SQL) is Oracles answer for the need a procedural language to embed SQL statements and execute them within Oracle.

PL/SQL is more powerful than the raw SQL statements, PL/SQL contains structural and conditional statements which enables us to perform a bulk of logical operations using a single call to Oracle, which otherwise would have to be performed at the front end using multiple SQL calls from the front end to Oracle.

The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in the program. A block has the following structure:

    DECLARE

                /* Variable declaration goes here. */

    BEGIN

                /* SQL Statements, Conditional statements etc goes here */

    EXCEPTION

                /* Exception handling logic goes here. */

    END;