More

    Introduction to Oracle PL/SQL

    PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural language extension for SQL and the Oracle relational database.

    PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM DB2 (since version 9.7).

    Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

    BASIC STRUCTURE OF PL/SQL

    PL/SQL programs consist of declaration statements (after DECLARE), executable statements (after BEGIN) and exception handling statements (after EXCEPTION) as in:

    DECLARE

      var1 INTEGER := 5;

      var2 INTEGER := 0;

      resultVar INTEGER;

    BEGIN

      resultVar := var1 / var2;

      DBMS_OUTPUT.PUT_LINE(‘result = ‘ || resultVar);

    EXCEPTION

      WHEN ZERO_DIVIDE THEN

      DBMS_OUTPUT.PUT_LINE(‘Division by zero not allowed.’);

    END;

    /

    The forward slash character (/) at the end marks the end of the PL/SQL block, and is needed if you are executing in SQL Plus, you should not use it while executing in clients such as DBVisualizer.

    DECLARE and EXCEPTION blocks are optional.

    DBMS_OUTPUT package is a built-in package that contains procedures that allow you to output values to the screen. DBMS_OUTPUT is useful when working with SQL PLUS.

    EXECUTABLE CODE

    BEGIN and EXCEPTION blocks can contain executable code, which include conditional logic and loops.

    Conditional logic

    You can use the IF, THEN, ELSE, ELSIF, and END IF keywords to perform conditional logic as in:

    IF var1 > 0 THEN

      resultVar := ‘var1 is positive’;

      IF var2 > 0 THEN

    resultVar := ‘var1 and var2 are positive’;

      END IF;

    ELSIF var1 = 0 THEN

      resultVar := ‘var1 is zero’;

    ELSE

      resultVar := ‘var1 is negative’;

    END IF;

    LOOP

    You can use simple LOOPs, WHILE loops or FOR loops to execute code iteratively.

    Simple loops have to be explicitly stopped using EXIT or an EXIT WHEN statement as in:

    LOOP

      IF var1 > 5 THEN

        EXIT;

      END IF;

      var1 := var1 + 1;

      EXIT WHEN var1 = 5;

    END LOOP;

    Similar to EXIT or an EXIT WHEN, you can use CONTINUE and CONTINUE WHEN to skip the remainder of current iteration and move to the next iteration. This feature is available from 11g.

    Unlike a simple LOOP, a WHILE loop can specify a condition in its definition and it will run as long as the condition is true:

    WHILE var1 < 6 LOOP

      var1 := var1 + 1;

    END LOOP;

    A FOR loop will also do the initialization and increment of variable in forward or reverse order:

    FOR var1 IN 1..5 LOOP

      DBMS_OUTPUT.PUT_LINE(var1);

    END LOOP;

    Unlike WHILE, you don’t have to declare var1 here.

    Next we will see cursors, procedures, functions and packages

    REFERENCES: 

    http://en.wikipedia.org/wiki/PL/SQL

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox