1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

What is PL SQL

Discussion in 'SQL PL/SQL' started by professional, Aug 9, 2008.

  1. professional

    professional Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hello

    PL/SQL is a block structured language which extends sql with

    *declarations

    *control structures

    *error handling
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Just adding some points to the above thread.

    Actiually PL/SQL Block consists of three sections:

    * Declaration section (optional)

    * Execution section (mandatory)

    * Exception Handling section (optional)


    NB : Control sections are the part of execution section and it is the only mandatory part for the proper execution of PL/SQL block.

    A PL/SQL block contains 1 ore more PL/SQL statements. Such a block must at least have the two keywords begin and end:

    Code (Text):

    begin
      sql statements
    end;
     
    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.

    Code (Text):

    declare
      variable declarations
    begin
      sql statements
    end;
     
    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.

    Code (Text):

    declare
      variable declarations
    begin
      sql statements
    exception
      exception handler
    end;
     
    A block can be named. Such a label can then be the target of a goto and exit statement.

    Code (Text):

    <<name-for-block>>
    declare
      variable declarations
    begin
      sql statements
    end name-for-block;
     
    A block can be (recursively) nested, that is, a block can contain other blocks, which in turn can again contain other blocks and so on .

    Code (Text):

    declare
      variable declarations
    begin
      sql statements

      declare
        variable declarations
      begin
        sql statements
      exception
        exception handler
      end;

      further-sql-statements

    exception
      exception handler
    end;
     
    If Exception occurs in the inner block , it checks for the exception handle inside the same block and if not found checks for it in the outer block and if it fails in the outermost block program terminates with error. If the exceetion occurs in the outer block , it never check the exception handle outside the block.

    .