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 :


  1. Greetings Mate,

    Great info! I recently came across your blog and have been reading along.
    I thought I would leave my first comment. I don’t know what to say except that I have

    We have a weird
    issue with MSSQL 2012 on AMAZON (RDS), at one point the CPU spikes to 20 % and the 20% is now the new 0% until I change the threshold for parallelism. This means the cpu stays constant > 20% during the day. After the modification of the parameters the cpu drops and the sql server shows normal CPU signs going from 5% to 10% with some higher spikes. I have no idea why this happens and why this resolves itself when I change the max dop or the threshold for parallelism.

    Excellent tutorials - very easy to understand with all the details. I hope you will continue to provide more such tutorials.

    Ajeeth Kapoor

  2. Halo,

    Fully agree on PL/SQL Introduction. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    Currently I'm trying to make a test using create job as a way to have multiple process on database starting in a loop.
    Basically I'm getting different behaviors when testing on different database machines. I'll define the machine names are A and B.
    If I started running the anonymous block below in the machine A for a loop to create 3 multiple jobs, is working fine, i.e, the create job using enabled parameter, created the job and automatically start:
    L_job_name VARCHAR2(100);
    L_comments VARCHAR2(240);
    L_pls_block VARCHAR2(32000);
    L_thread NUMBER := 1; -- count of jobs
    L_max_threads NUMBER := 3; -- max number of jobs to be started
    L_lot_id NUMBER := 1234; -- add any number just to represent a lot

    while L_thread <= L_max_threads loop
    L_job_name := 'job_' || L_lot_id || L_thread;
    L_comments := 'Job test ' || L_lot_id || ' and thread ' || L_thread;
    L_pls_block := 'BEGIN logger.log(''job running of thread' || L_thread || '''); END;';
    dbms_scheduler.create_job(job_name => L_job_name,
    job_type => 'PLSQL_BLOCK',
    job_action => L_pls_block,
    start_date => SYSTIMESTAMP,
    comments => L_comments,
    enabled => true);


    L_thread := L_thread + 1;
    end loop;

    but if I start the same code in the machine B, nothing is happening and I need to uncomment the line about DBMS_SCHEDULER.RUN_JOB(L_job_name, TRUE);

    I did another test in the same machine B using enabled FALSE when creating the job and during the call from DBMS_SCHEDULER.RUN_JOB changed to false in the user_credentials but only one job runned, i.e, the result was logged from just one job, instead of the 3 jobs that had most be created.

    Do you have an idea about what I need to look? Is there something related about database setup, credentials that I need to take a look?

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thank you,