PL/SQL

INTRODUCTION

What is PL/SQL?
PL/SQL is a database-oriented programming language that extends Oracle SQL with procedural capabilities.
CHARACTERSTICS:

  • Highly structured, readable and accessible language.
  • Standard and Protable language.
  • Embedded language.
  • Improved execution authority.

10g FEATURES

  1. Optimized compiler
    To change the optimizer settings for the entire database, set the database parameter PLSQL_OPTIMIZE_LEVEL. Valid settings are as follows
    0 – No optimization
    1 – Moderate optimization
    2 – Aggressive optimization
    These settings are also modifiable for the current session.
    SQL> alter session set plsql_optimze_level=2;
    Oracle retains optimizer settings on a module-by-module basis. When you recompile a particular module with nondefault settings, the settings will stick allowing you to recompile later on using REUSE SETTINGS.
    SQL> Alter procedure proc compile plsql_optimize_level=1;
    SQL> Alter procedure proc compile reuse settings;
  2. Compile-time warnings.
    Starting with oracle database 10g release 1 you can enable additional compile-time warnings to help make your programs more robust. The compiler can detect potential runtime problems with your code, such as identifying lines of code that will never be run. This process, also known as lint checking.
    To enable these warnings fo the entire database, set the database parameter PLSQL_WARNINGS. These settings are also modifiable for the current session.
    SQL> alter session set plsql_warnings = ‘enable:all’;
    The above can be achieved using the built-in package DBMS_WARNING.
  3. Conditional compilation.
    Conditional compilation allows the compiler to allow to compile selected parts of a program based on conditions you provide with the $IF directive.
  4. Support for non-sequential collections in FORALL.
  5. Improved datatype support.
  6. Backtrace an exception to its line number.
    When handling an error, how can you find the line number on which the error was originally raised?
    In earlier release, the only way to do this was allow you exception to go unhandled and then view the full error trace stack.
    Now you can call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to obtain that stack and manipulate it programmatically within your program.
  7. Set operators for nested tables.
  8. Support for regular expressions.
    Oracle database 10g supports the use of regular expressions inside PL/SQL code via four new built-in functions.
    REGEXP_LIKE
    REGEXP_INSTR
    REGEXP_SUBSTR
    REGEXP_REPLACE
  9. Programmer-defined quoting mechanism.
    Starting with oracle database 10g release 1, you can define your own quoting mechanism for string literals in both SQL and PL/SQL.
    Use the characters q’(q followed by a single quote) to note the programmer-defined deliemeter for you string literal.
    Ex:
    DECLARE
    v varchar(10) := ‘computer’;
    BEGIN
    dbms_output.put_line(q’*v = *’ || v);
    dbms_output.put_line(q’$v = $’ || v);
    END;Output:
    v = computer
    v = computer
  10. Many new built-in packages.
    DBMS_SCHEDULER
    Represents a major update to DBMS_JOB. DBMS_SCHEDULER provides much improved functionality for scheduling and executing jobs defined via stored procedures.
    DBMS_CRYPTO
    Offers the ability to encrypt and decrypt common oracle datatype, including RAWs, BLOBs, and CLOBs. It also provides globalization support for encrypting data across different charactersets.
    DBMS_MONITOR
    Provides an API to control additional tracing and statistics gathering of sessions.
    DBMS_WARNING
    Provides an API into the PL/SQL compiler warnings module, allowing you to read and change settings that control which warnings are suppressed, displayed, or treated as errors.

STANDARD PACKAGE
Oracle has defined in this special package. Oracle defines quite a few identifiers in this package, including built-in exceptions, functions and subtypes.
You can reference the built-in form by prefixing it with STANDARD.
The basic unit in any PL/SQL program is block. All PL/SQL programs are composed of blocks which can occur sequentially or nested.

BLOCK STRUCTURE

Declare
— declarative section
Begin
— executable section
Exception
— exception section
End;

In the above, declarative and exception sections are optional.
BLOCK TYPES

  • Anonymous blocks
  • Named blocks:

 Labeled blocks
Subprograms
Triggers

ANONYMOUS BLOCKS
An anonymous block implies basic block structure.
Ex:
BEGIN
Dbms_output.put_line(‘My first program’):
END;

LABELED BLOCKS
Labeled blocks are anonymous blocks with a label which gives a name to the block.
Ex:
<<my_bloock>>
BEGIN
Dbms_output.put_line(‘My first program’):
END;

SUBPROGRAMS
Subprograms are procedures and functions. They can be stored in the database as stand-alone objects, as part of package or as methods of an object type.

TRIGGERS
Triggers consists of a PL/SQL block that is associated with an event that occur in the database.

NESTED BLOCKS
A block can be nested within the executable or exception section of an outer block.

IDENTIFIERS
Identifiers are used to name PL/SQL objects, such as variables, cursors, types and subprograms. Identifiers consists of a letter, optionally followed by any sequence of characters, including letters, numbers, dollar signs, underscores, and pound signs only. The maximum length for an identifier is 30 characters.

QUOTED IDENTIFIERS
If you want to make an identifier case sensitive, include characters such as spaces or use a reserved word, you can enclose the identifier in double quotation marks.

Ex:

DECLARE
“a” number := 5;
“A” number := 6;
BEGIN
dbms_output.put_line(‘a = ‘ || a);
dbms_output.put_line(‘A = ‘ || A);
END;
Output:
a = 6
A = 6

COMMENTS
Comments improve readability and make your program more understandable. They are ignored by the PL/SQL compiler. There are two types of comments available.

  • Single line comments
  • Multiline comments

SINGLE LINE COMMENTS
A single-line comment can start any point on a line with two dashes and continues until the end of the line.
Ex:
BEGIN
Dbms_output.put_line(‘hello’); — sample program
END;

MULTILINE COMMENTS
Multiline comments start with the /* delimiter and ends with */ delimiter.
Ex:
BEGIN
Dbms_output.put_line(‘hello’); /* sample program */
END;

VARIABLE DECLERATIONS
Variables can be declared in declarative section of the block;
Ex:
DECLARE
a number;
b number := 5;
c number default 6;

CONSTANT DECLERATIONS
To declare a constant, you include the CONSTANT keyword, and you must supply a default value.
Ex:
DECLARE
b constant number := 5;
c constant number default 6;

NOT NULL CLAUSE
You can also specify that the variable must be not null.
Ex:
DECLARE
b constant number not null:= 5;
c number not null default 6;

ANCHORED DECLERATIONS
PL/SQL offers two kinds of anchoring.

  • Scalar anchoring
  • Record anchoring

SCALAR ANCHORING
Use the %TYPE attribute to define your variable based on table’s column of some other PL/SQL scalar variable.
Ex:
DECLARE
dno dept.deptno%type;
Subtype t_number is number;
a t_number;
Subtype t_sno is student.sno%type;
V_sno t_sno;

RECORD ANCHORING
Use the %ROWTYPE attribute to define your record structure based on a table.
Ex:
DECLARE
V_dept dept%rowtype;
BENEFITS OF ANCHORED DECLARATIONS
Synchronization with database columns.
Normalization of local variables.

PROGRAMMER-DEFINED TYPES
With the SUBTYPE statement, PL/SQL allows you to define your own subtypes or aliases of predefined datatypes, sometimes referred to as abstract datatypes.
There are two kinds of subtypes.

  • Constrained
  • Unconstrained

CONSTRAINED SUBTYPE
A subtype that restricts or constrains the values normally allowd by the datatype itself.
Ex:
Subtype positive is binary_integer range 1..2147483647;
In the above declaration a variable that is declared as positive can store only ingeger greater than zero even though binary_integer ranges from -2147483647..+2147483647.

UNCONSTRAINED SUBTYPE
A subtype that does not restrict the values of the original datatype in variables declared with the subtype.
Ex:
Subtype float is number;

DATATYPE CONVERSIONS
PL/SQL can handle conversions between different families among the datatypes.
Conversion can be done in two ways.

  • Explicit conversion
  • Implicit conversion

EXPLICIT CONVERSION
This can be done using the built-in functions available.
IMPLICIT CONVERSION
PL/SQL will automatically convert between datatype families when possible.
Ex:
DECLARE
a varchar(10);
BEGIN
select deptno into a from dept where dname=’ACCOUNTING’;
END;
In the above variable a is char type and deptno is number type even though, oracle will automatically converts the numeric data into char type assigns to the variable.
PL/SQL can automatically convert between

  • Characters and numbers
  • Characters and dates

VARIABLE SCOPE AND VISIBILITY
The scope of a variable is the portion of the program in which the variable can be accessed. For PL/SQL variables, this is from the variable declaration until the end of the block. When a variable goes out of scope, the PL/SQL engine will free the memory used to store the variable.

The visibility of a variable is the portion of the program where the variable can be accessed without having to qualify the reference. The visibility is always within the scope. If it is out of scope, it is not visible.
Ex1:
DECLARE
           a number; — scope of a
BEGIN
——–
    DECLARE
         b number; — scope of b
   BEGIN
     —–
   END;
——
END;
Ex2:
DECLARE
       a number;
      b number;
BEGIN
  — a , b available here
  DECLARE
      b char(10);
  BEGIN
    — a and char type b is available here
  END;
—–
END;

Ex3:
<<my_block>>
DECLARE
        a number;
       b number;
   BEGIN
       — a , b available here
  DECLARE
      b char(10);
  BEGIN
      — a and char type b is available here
      — number type b is available using <<my_block>>.b
 END;
——
END;

 

PL/SQL CONTROL STRUCTURES
PL/SQL has a variety of control structures that allow you to control the behaviour of the block as it runs. These structures include conditional statements and loops.

  1. If-then-else
  2. Case:  a. Case with no else  b. Labeled case c. Searched case
  3. Simple loop
  4. While loop
  5. For loop
  6. Goto and Labels

IF-THEN-ELSE
Syntax:
If <condition1> then
Sequence of statements;
Elsif <condition1> then
Sequence of statements;
……
Else
Sequence of statements;
End if;

EX:

DECLARE
    dno number(2);
BEGIN
    select deptno into dno from dept where dname = ‘ACCOUNTING’;
    if dno = 10 then
        dbms_output.put_line(‘Location is NEW YORK’);
   elsif dno = 20 then
       dbms_output.put_line(‘Location is DALLAS’);
   elsif dno = 30 then
     dbms_output.put_line(‘Location is CHICAGO’);
  else
    dbms_output.put_line(‘Location is BOSTON’);
  end if;
END;

Output:
Location is NEW YORK

CASE
Syntax:
Case test-variable
When value1 then sequence of statements;
When value2 then sequence of statements;
……
When valuen then sequence of statements;
Else sequence of statements;
End case;

Ex:
   DECLARE
                  dno number(2);
       BEGIN
                select deptno into dno from dept where dname = ‘ACCOUNTING’;
                case dno
                     when 10 then
                                dbms_output.put_line(‘Location is NEW YORK’);
                     when 20 then
                                dbms_output.put_line(‘Location is DALLAS’);
                     when 30 then
                               dbms_output.put_line(‘Location is CHICAGO’);
                     else
                              dbms_output.put_line(‘Location is BOSTON’);
               end case;
      END;

Output:
Location is NEW YORK

CASE WITHOUT ELSE
Syntax:
Case test-variable
When value1 then sequence of statements;
When value2 then sequence of statements;
……
When valuen then sequence of statements;
End case;

Ex:
      DECLARE
              dno number(2);
      BEGIN
              select deptno into dno from dept where dname = ‘ACCOUNTING’;
             case dno
                    when 10 then
                       dbms_output.put_line(‘Location is NEW YORK’);
                   when 20 then
                       dbms_output.put_line(‘Location is DALLAS’);
                   when 30 then
                       dbms_output.put_line(‘Location is CHICAGO’);
                  when 40 then
                       dbms_output.put_line(‘Location is BOSTON’);
           end case;
    END;

Output:
Location is NEW YORK

LABELED CASE
Syntax:
<<label>>
Case test-variable
When value1 then sequence of statements;
When value2 then sequence of statements;
……
When valuen then sequence of statements;
End case;

Ex:
             DECLARE
                  dno number(2);
            BEGIN
                   select deptno into dno from dept where dname = ‘ACCOUNTING’;
                  <<my_case>>
                 case dno
                          when 10 then
                               dbms_output.put_line(‘Location is NEW YORK’);
                         when 20 then
                             dbms_output.put_line(‘Location is DALLAS’);
                        when 30 then
                             dbms_output.put_line(‘Location is CHICAGO’);
                        when 40 then
                             dbms_output.put_line(‘Location is BOSTON’);
                end case my_case;
END;

Output:
Location is NEW YORK

SEARCHED CASE
Syntax:
Case
When <condition1> then sequence of statements;
When <condition2> then sequence of statements;
……
When <conditionn> then sequence of statements;
End case;

Ex:
                DECLARE
                     dno number(2);
                BEGIN
                    select deptno into dno from dept where dname = ‘ACCOUNTING’;
                   case dno
                        when dno = 10 then
                           dbms_output.put_line(‘Location is NEW YORK’);
                       when dno = 20 then
                           dbms_output.put_line(‘Location is DALLAS’);
                       when dno = 30 then
                           dbms_output.put_line(‘Location is CHICAGO’);
                      when dno = 40 then
                           dbms_output.put_line(‘Location is BOSTON’);
                  end case;
           END;

Output:
Location is NEW YORK

 SIMPLE LOOP

Syntax:
Loop
Sequence of statements;
Exit when <condition>;
End loop;
In the syntax exit when <condition> is equivalent to
If <condition> then
Exit;
End if;
Ex:
           DECLARE
              i number := 1;
          BEGIN
             loop
                 dbms_output.put_line(‘i = ‘ || i);
                i := i + 1;
                exit when i > 5;
           end loop;
        END;

Output:
i = 1
i = 2
i = 3
i = 4
i = 5

WHILE LOOP
Syntax:
While <condition> loop
Sequence of statements;
End loop;

Ex:
                DECLARE
                     i number := 1;
                BEGIN
                    While i <= 5 loop
                       dbms_output.put_line(‘i = ‘ || i);
                       i := i + 1;
                    end loop;
                END;

Output:
i = 1
i = 2
i = 3
i = 4
i = 5

FOR LOOP
Syntax:
For <loop_counter_variable> in low_bound..high_bound loop
Sequence of statements;
End loop;
Ex1:
          BEGIN
              For i in 1..5 loop
                 dbms_output.put_line(‘i = ‘ || i);
              end loop;
         END;
Output:
i = 1
i = 2
i = 3
i = 4
i = 5

Ex2:
      BEGIN
             For i in reverse 1..5 loop
                 dbms_output.put_line(‘i = ‘ || i);
            end loop;
      END;
Output:
i = 5
i = 4
i = 3
i = 2
i = 1

NULL STATEMENT
Usually when you write a statement in a program, you want it to do something. There are cases, however, when you want to tell PL/SQL to do absolutely nothing, and that is where the NULL comes.
The NULL statement deos nothing except pass control to the next executable statement.
You can use NULL statement in the following situations.

1 Improving program readability.
Sometimes, it is helpful to avoid any ambiguity inherent in an IF statement that doesn’t cover all         possible cases. For example, when you write an IF statement, you do not have to include an ELSE clause.
2 Nullifying a raised exception.
When you don’t want to write any special code to handle an exception, you can use the NULL statement to make sure that a raised exception halts execution of the current PL/SQL block but does not propagate any exceptions to enclosing blocks.
3 Using null after a label.
In some cases, you can pair NULL with GOTO to avoid having to execute additional statements. For example, I use a GOTO statement to quickly move to the end of my program if the state of my data indicates that no further processing is required. Because I do not have to do anything at the termination of the program, I place a NULL statement after the label because at least one executable statement is required there. Even though NULL deos nothing, it is still an executable statement.

GOTO AND LABELS
Syntax:
Goto label;
Where label is a label defined in the PL/SQL block. Labels are enclosed in double angle brackets. When a goto statement is evaluated, control immediately passes to the statement identified by the label.
Ex:
             BEGIN
                   For i in 1..5 loop
                      dbms_output.put_line(‘i = ‘ || i);
                      if i = 4 then
                        goto exit_loop;
                      end if;
                  end loop;
                <<exit_loop>>
                Null;
          END;

Output:
i = 1
i = 2
i = 3
i = 4

RESTRICTIONS ON GOTO

  •  It is illegal to branch into an inner block, loop.
  •  At least one executable statement must follow.
  •  It is illegal to branch into an if statement.
  •  It is illegal to branch from one if statement to another if statement.
  •  It is illegal to branch from exception block to the current block.

PRAGMAS
Pragmas are compiler directives. They serve as instructions to the PL/SQL compiler. The compiler will act on the pragma during the compilation of the block.
Syntax:
PRGAMA instruction_to_compiler.

PL/SQL offers several pragmas:

  •  AUTONOMOUS_TRANSACTION
  •  EXCEPTION_INIT
  •  RESTRICT_REFERENCES
  •  SERIALLY_REUSABLE

 

Leave a Reply