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).[1] 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.

 

Reference

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

Quick Notes Finder Tags

Activities (1) advanced java (1) agile (3) App Servers (6) archived notes (2) Arrays (1) Best Practices (12) Best Practices (Design) (3) Best Practices (Java) (7) Best Practices (Java EE) (1) BigData (3) Chars & Encodings (6) coding problems (2) Collections (15) contests (3) Core Java (All) (55) course plan (2) Database (12) Design patterns (8) dev tools (3) downloads (2) eclipse (9) Essentials (1) examples (14) Exception (1) Exceptions (4) Exercise (1) exercises (6) Getting Started (18) Groovy (2) hadoop (4) hibernate (77) hibernate interview questions (6) History (1) Hot book (5) http monitoring (2) Inheritance (4) intellij (1) java 8 notes (4) Java 9 (1) Java Concepts (7) Java Core (9) java ee exercises (1) java ee interview questions (2) Java Elements (16) Java Environment (1) Java Features (4) java interview points (4) java interview questions (4) javajee initiatives (1) javajee thoughts (3) Java Performance (6) Java Programmer 1 (11) Java Programmer 2 (7) Javascript Frameworks (1) Java SE Professional (1) JPA 1 - Module (6) JPA 1 - Modules (1) JSP (1) Legacy Java (1) linked list (3) maven (1) Multithreading (16) NFR (1) No SQL (1) Object Oriented (9) OCPJP (4) OCPWCD (1) OOAD (3) Operators (4) Overloading (2) Overriding (2) Overviews (1) policies (1) programming (1) Quartz Scheduler (1) Quizzes (17) RabbitMQ (1) references (2) restful web service (3) Searching (1) security (10) Servlets (8) Servlets and JSP (31) Site Usage Guidelines (1) Sorting (1) source code management (1) spring (4) spring boot (3) Spring Examples (1) Spring Features (1) spring jpa (1) Stack (1) Streams & IO (3) Strings (11) SW Developer Tools (2) testing (1) troubleshooting (1) user interface (1) vxml (8) web services (1) Web Technologies (1) Web Technology Books (1) youtube (1)