PL/SQL Tutorial Part - 01

1) what is the plsql block structure?

[DECLARE]  --Optional
 BEGIN
[EXCEPTION]--Optional
 END;

Example:

 DECLARE
    --v_billingdate TIMESTAMP(9):= SYSTIMESTAMP;
    v_billingdate TIMESTAMP := SYSTIMESTAMP;
    v_transactiondate DATE:= SYSDATE;
    BEGIN
    DBMS_OUTPUT.PUT_LINE(v_billingdate);
    DBMS_OUTPUT.PUT_LINE(v_transactiondate);
    END;
    /
    16-OCT-13 01.25.24.846209000 PM
    16-OCT-13

    PL/SQL procedure successfully completed.

2) Describe procedure under DBMS_OUTPUT packages .

    - DBMS_OUTPUT.PUT_LINE
       An oracle supplied packaged procedure

    - DBMS_OUTPUT is a package and PUT_LINE is a procedure within the package
       The string which has to be printed should be specified in parenthesis, following the
        PUT_LINE keyword

    - Using SET SERVEROUTPUT ON this package should be enabled in SQLPLUS

   Example:

     SET SERVEROUTPUT ON
     DECLARE
          v_productcategory VARCHAR2(20) :='Mobile Phone';
          v_itemqty PLS_INTEGER := 1000;
          v_itemprice NUMBER(7,2) DEFAULT 60.50;
          v_transactiondate DATE:= SYSDATE;
          v_billingdate TIMESTAMP := SYSTIMESTAMP;
          v_test BOOLEAN:=TRUE;
        BEGIN
          DBMS_OUTPUT.PUT_LINE(v_productcategory);
          DBMS_OUTPUT.PUT_LINE(v_transactiondate);
          DBMS_OUTPUT.PUT_LINE (v_billingdate);
          END;
         /

More about DBMS_OUTPUT package

• Used to display messages to the screen from an anonymous PL/SQL block
• Debugging is the most popular use of this package
• When executing a PL/SQL block, any DBMS_OUTPUT lines are placed in an output buffer,     which displays its contents on the screen when the program has completed execution

3) What are the procedures under the DBMS_OUTPUT package?

Procedure available                      Description
----------------------------                  ------------------      

DBMS_OUTPUT.ENABLE          Allow msg display (not necessary if  SERVEROUTPUT set ON in the SQL*PLUS)                            

DBMS_OUTPUT.DISABLE         Does not allow message display
DBMS_OUTPUT.PUT                  Place information in the buffer
DBMS_OUTPUT.PUT_LINE       Place information in the buffer followed by an end-of-line marker
DBMS_OUTPUT.NEW_LINE      Place an end-of-line marker in the buffer

Example1
 
    BEGIN
        DBMS_OUTPUT.PUT('This is ');
        DBMS_OUTPUT.put('line one ');
        DBMS_OUTPUT.PUT_LINE('This is line one continued');
        DBMS_OUTPUT.PUT('This is line two');
    END;


    OUTPUT: This is line one This is line one continued

Example2
    BEGIN
        DBMS_OUTPUT.PUT('This is');
        DBMS_OUTPUT.PUT('line one ');
        DBMS_OUTPUT.NEW_LINE;
        DBMS_OUTPUT.PUT_LINE('This is line two ');
        DBMS_OUTPUT.PUT('This is line three');
        DBMS_OUTPUT.NEW_LINE;
    END;

    /*
    This isline one
    This is line two
    This is line three
*/

NOTE :
 DBMS_OUTPUT.NEW_LINE('INBETWEEN'); --NOT ALLOWED
 DBMS_OUTPUT.NEW_LINE();  --VALID
 DBMS_OUTPUT.NEW_LINE;  --VALID

4) What is the Anchored declarations in PL/SQL ?
    Variables that directly maps to a column definition in the database

    SQL> DECLARE
      1 -- variablename  TABLENAME.COLUMNNAME%TYPE; --Syntax
      2  v_itemcode ITEM.ITEMCODE%TYPE;
    . . . .
    . . . .

   Rules

1. Same datatype will be applied as it is in Database.
2. Change of column precision or datatype of ITEMCODE in the database, would in turn change v_itemcode automatically as the variable is anchored
3. Convenient way of associating PL/SQL variable with database column definitions
4. NOT NULL constraint or CHECK constraint associated with table column would not be applicable to PL/SQL variable

   Usage 2: is to declare variables that directly maps to a datatype of previously declared variable

    SQL> DECLARE
      2  v_quantityonhand  NUMBER(7) NOT NULL:= 500;
      3  v_reorderqty      v_quantityonhand%TYPE :=50;
        . . . .
        . . . .
 Rules
  1.The datatype of v_quantityonhand and NOT NULL constraint is applied to v_reorderqty. 
  2.Value of v_quantityonhand would not be copied to v_reorderqty


 5)What do you mean by Bind variables in PL/SQL ?

1. Declared in the host environment such as SQL*PLUS
2. Used to pass runtime values out of one or more PL/SQL programs to the host environment
3. PRINT command helps us in displaying the value of one bind variable
4. Would be alive only in the current session

    SET SERVEROUTPUT ON
    VARIABLE g_itemid varchar2(20);
    BEGIN
       :g_itemid :='&itemid';
       END;
    /

    PL/SQL procedure successfully completed.

SQL> PRINT g_itemid
    G_ITEMID
    ----------------
    STN001

6) What is the unconstitutional variable in PL/SQL ?

1. Declared in the host environment such as SQL*PLUS
2. Used to pass runtime values into one or more PL/SQL programs
3. You will not be prompted to enter value during execution of the block
4. Would be alive only in the current session

Example:
    SET SERVEROUTPUT ON
    DEFINE g_itemid = "PEN";
    DECLARE
         v_itemname VARCHAR2(30);
        BEGIN
          v_itemname :='&g_itemid';
        DBMS_OUTPUT.PUT_LINE(v_itemname);
        END;
       /

    PEN
    PL/SQL procedure successfully completed.

7) How to accept user input in PL/SQL ?
    --Accepting input in PL/SQL
    1.Declare the variables in the declaration section of PL/SQL block
    2.Accept the value for variables in the executable block
    3.Display the accepted values as shown below
   
    SET SERVEROUTPUT ON
    DECLARE
         v_itemname VARCHAR2(30);
    BEGIN
         v_itemname :='&v_item';
         DBMS_OUTPUT.PUT_LINE(v_itemname);
    END;
    /
    Enter value for v_item:

Note :
PL/SQL is not interactive. Please follow the code snippet to understand the same.

DECLARE
    v_customername VARCHAR2(20);
    v_qtyrequired NUMBER;
    BEGIN
    v_customername := '&v_customername';
    DBMS_OUTPUT.PUT_LINE('Customer Name : '||v_customername);
    v_qtyrequired := &v_qtyrequired;
    DBMS_OUTPUT.PUT_LINE('Required Qty : '||v_qtyrequired);
    END;
   /

    Enter value for v_customername: JAMES
    old   5: v_customername := '&v_customername';
    new   5: v_customername := 'JAMES';
    Enter value for v_qtyrequired: 20
    old   7: v_qtyrequired := &v_qtyrequired;
    new   7: v_qtyrequired := 20;
    Customer Name : JAMES
    Required Qty : 20

Rules

• While executing the above PL/SQL block, the system would ask us to enter the customer name and quantity required both,  only after which it will
• display the entered customer name and quantity required details.  Even though there is a presence of DBMS_OUTPUT.PUT_LINE  statement immediately
• after accepting the customer name, the system would not do so. Please be aware of this behavior.

8) What is the SET VERIFY ON/OFF in PL/SQL ?
    Prints two lines for every substitution performed within a PL/SQL block
    ON --> Displays the substitution performed (Default)
    OFF--> Suppress the display of substitution performed

Example:
    SET VERIFY OFF
    SET SERVEROUTPUT ON
    DECLARE
         v_itemname VARCHAR2(30);
       BEGIN
         v_itemname :='&v_item';
         DBMS_OUTPUT.PUT_LINE(v_itemname);
       END;
       /

    Enter value for v_item: STN001
    STN001
    PL/SQL procedure successfully completed.

9) What are the operators in the PL/SQL ?
    1.Concatenation Operator ( || )
    2.Arithmetic Operators( +, -, *, /,**)
    3.Relational Operators( =, !=, <, >, <=, >=)
    4.Logical Operators (AND, OR and NOT)

  Concatenation Operator
    || is the concatenation operator
    Concatenates  two or more strings together
    Do not use || instead of Logical operator OR
   
    DECLARE
       v_customername varchar2(10):='John';
    BEGIN
       v_customername := v_customername || '10';
       DBMS_OUTPUT.PUT_LINE('value of v_customername : '|| v_customername);
     END;

Output:  Value of v_customername : John10

Arithmetic Operator - Addition

    DECLARE
      v_reorderlevel NUMBER;
    BEGIN
     v_reorderlevel := v_reorderlevel+10; -- NULL +10
      DBMS_OUTPUT.PUT_LINE('value of v_reorderlevel : '|| v_reorderlevel);
    END;

    Value of v_reorderlevel :       (NULL)

    --Addition of NULL with any number is NULL
    --Only numeric and date data types can be used along with arithmetic operators

Arithmetic Operator - Exponentiation
  ** is the exponential operator

    DECLARE
      v_number NUMBER:=2;
    BEGIN
      v_number:=v_number **2;
     DBMS_OUTPUT.PUT_LINE( 'value of v_number : '|| v_number);
    END;

    value of v_number : 4

Arithmetic operator With Date
  Arithmetic operators can be used with date

    SET SERVEROUTPUT ON
    DECLARE
      v_today DATE := '10-MAR-2009';
      v_tomorrow DATE;
    BEGIN
      v_tomorrow := v_today + 1;
      DBMS_OUTPUT.PUT_LINE('Tomorrow''s date is '||v_tomorrow);
    END;

10) what is Nested PL/SQL blocks  and what are the rules of using?
    1.A PL/SQL block defined within another PL/SQL block is called nested PL/SQL block.
    2.Can be nested in the executable section or in exception handling section
    3.One or more nested blocks can be present within an anonymous PL/SQL block
    4.Overlapping of nested blocks are not allowed
   
Example:
    DECLARE
     --declaration of variables in the enclosed block
        BEGIN
           --SQL and PL/SQL statement(s)
             DECLARE
             --- declaration of variables in the nested block
             BEGIN
             -- SQL & PL/SQL statement(s) in nested block
             END;  
             DECLARE
             -- declaration of variables in the nested block
             BEGIN
             -- SQL & PL/SQL statement(s) in nested block
             END;  
         --SQL and PL/SQL statement(s)
         END;

Example2:
 
DECLARE
       --declaration of variables in the enclosed block
    BEGIN
     --SQL and PL/SQL statement(s)
         DECLARE
           -- declaration of variables in the nested block
         BEGIN
                -- SQL & PL/SQL statement(s) in nested block
                DECLARE
                   -- declaration of variables
                BEGIN
                    -- SQL and PL/SQL statement(s)
                END;
         END;
         --SQL and PL/SQL statement(s)
    END;

Example3:
 
    DECLARE
           --declaration of variables in the enclosed block
        BEGIN
         --SQL and PL/SQL statement(s)
             DECLARE
               -- declaration of variables in the nested block
             BEGIN
            -- SQL & PL/SQL statement(s) in nested block
            DECLARE
             -- declaration of variables
         END;    
            BEGIN
                -- SQL and PL/SQL statement(s)
            END;
    --SQL and PL/SQL statement(s)
        END;

Overlapping of PL/SQL blocks are not allowed. The above is an example of the same.

11) Scope of variables   

1. Variables declared in the DECLARE section would be visible in the EXECUTABLE section and EXCEPTION section
2. Lifetime of variables declared in the nested block will be only within the nested block
3. Variables declared in the outermost block are visible in all the nested blocks

Example1:
 

    SET SERVEROUTPUT ON;
    DECLARE
    v_qoh NUMBER:=10;
    BEGIN
        DECLARE
            v_price NUMBER :=20;
        BEGIN
            DBMS_OUTPUT.PUT_LINE('The value of v_qoh: '||v_qoh);
            DBMS_OUTPUT.PUT_LINE('The value of v_price :'||v_price);
        END;
         DBMS_OUTPUT.PUT_LINE('The value of v_qoh: '||v_qoh);
         --DBMS_OUTPUT.PUT_LINE('The value of v_price :'||v_price);
    END;
/

Example2:

 
    DECLARE
        v_itemid  NUMBER(4) := 1001;
        BEGIN
              DECLARE
                 v_itemid  NUMBER(4) := 1002;
              BEGIN
                 DBMS_OUTPUT.PUT_LINE( v_itemid);
              END;  
              DECLARE
                 v_quantityonhand NUMBER(4):=300;
              BEGIN
                DBMS_OUTPUT.PUT_LINE( v_itemid);
              END;  
            DBMS_OUTPUT.PUT_LINE(v_itemid);
      END;
/*
1002
1001
1001
*/

Note:
    v_itemid with value 1001 declared in the outermost block is visible in both the nested blocks
    But another v_itemid with value 1002 is declared in the first nested block with similar name as that of outer block
    As local variable takes higher precedence within any block only v_itemid with value 1002 would be visible in the first nested block 

12)What is the qualifying identifier in PL/SQL ?
   Anonymous PL/SQL blocks can be qualified with identifiers (or names). While qualifying use << and >> angle brackets to enclose the identifiers.
   If the variable name is same then we can refer each variable with the help of the labels
        <<supplier>>
        DECLARE
          v_price Number:=10;
        BEGIN
          <<shopkeeper>>
          DECLARE
            v_price Number:=20;
          BEGIN
             DBMS_OUTPUT.PUT_LINE('Supplier Price: '||supplier.v_price);
             DBMS_OUTPUT.PUT_LINE('Shopkeeper Price: '||shopkeeper.v_price);
          END;
             DBMS_OUTPUT.PUT_LINE('Supplier Price: '||supplier.v_price);
             --DBMS_OUTPUT.PUT_LINE('Shopkeeper Price: '||shopkeeper.v_price);--this variable will not be visible here
        END;

13) IF conditions in the PL/SQL Block.

    Condition can be any expression,variable,constant, or identifier compared to any other expression, variable, constant, or identifier.
    The condition must evaluate to TRUE, FALSE or NULL

--IF-THEN
    IF condition
    THEN
      action;
    END IF;

 Example:
    DECLARE
      v_qoh NUMBER :=10;
      v_itemrequired NUMBER :=&b_itemrequired;
    BEGIN
      IF v_itemrequired>v_qoh THEN
        DBMS_OUTPUT.PUT_LINE('Item not available ');
      END IF;
    END;


    Enter value for b_itemrequired: 11
    old   3:   v_itemrequired NUMBER :=&b_itemrequired;
    new   3:   v_itemrequired NUMBER :=11;
    Item not available

--IF-THEN-ELSE
    IF condition
    THEN
      action;
    ELSE
      action;
    END IF;

Example:
    DECLARE --Comparing NUMBER datatypes
      v_qoh NUMBER :=10;
      v_itemrequired NUMBER :=&b_itemrequired;
    BEGIN
        IF v_itemrequired > v_qoh THEN
        DBMS_OUTPUT.PUT_LINE('Item not available ');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Item available ');
      END IF;
    END;

    Enter value for b_itemrequired: 9
    old   3:   v_itemrequired NUMBER :=&b_itemrequired;
    new   3:   v_itemrequired NUMBER :=9;
    Item available

--IF-THEN-ELSE
Inequality of 2 given numbers or dissimilarity of strings can be checked with either    !=   or  <>

    SET SERVEROUTPUT ON
    DECLARE --Comparing VARCHAR2 datatypes
      v_string1 VARCHAR2(10) := 'hello';
      v_string2 VARCHAR2(10) := 'hello';
    BEGIN
      IF v_string1 <> v_string2 THEN
         DBMS_OUTPUT.PUT_LINE('Both are unequal');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Both are equal');
      END IF;
    END;

    OP:Both are equal

--IF-THEN-ELSIF
    IF condition
    THEN
      action;
    ELSIF condition
    THEN
      action;
    [ELSE
       action;]
    END IF;

Example:
    DECLARE
      v_qoh NUMBER :=10;
      v_itemrequired NUMBER :=&b_itemrequired;
    BEGIN
      IF v_itemrequired>v_qoh   THEN
        DBMS_OUTPUT.PUT_LINE('Item not available ');
      ELSIF v_itemrequired=10 THEN
        DBMS_OUTPUT.PUT_LINE('Item available but no more stock ');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Item not available at all');
      END IF;
    END;

Note:
    For every IF condition there must be END IF.
    If we are using ELSE IF condtion.. Then for every IF we are using we have to use END IF.
    The advantage of using ELSIF is, we can close all the IF s with one END IF .
    Please note the spelling of ELSIF.

14) give some examples of loops in PL/SQL.

    LOOP
        action
    END LOOP;

Example:
    BEGIN
      LOOP
        DBMS_OUTPUT.PUT_LINE('I AM IN LOOP!!!');
      END LOOP; 
    END;


DECLARE
  v_price NUMBER:=1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
    v_price:=v_price+1;
    EXIT WHEN v_price >5;
  END LOOP;
END;

Price: 1
Price: 2
Price: 3
Price: 4
Price: 5

--Numeric FOR Loop
FOR counter IN low_number .. high_number
LOOP
    action;
END LOOP;

BEGIN
  FOR v_price IN 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
  END LOOP;
END;
OUTPUT:
---------
Price: 1
Price: 2
Price: 3
Price: 4
Price: 5

The variable is automatically declared and initialized
What happens if we declare and initialize the same variable?

--Numeric FOR Loop REVERSE
FOR counter IN REVERSE low_number .. high_number
LOOP
    action;
END LOOP;

BEGIN
  FOR v_price IN REVERSE 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
  END LOOP;
END;

OUTPUT:
---------
Price: 5
Price: 4
Price: 3
Price: 2
Price: 1

The variable is automatically declared and initialized
What happens if we declare and initialize the same variable, which was used in the FOR loop?


--WHILE condition
LOOP
  action;
END LOOP;

Example:
    DECLARE
      v_price NUMBER:=1;
    BEGIN
      WHILE v_price <=5
      LOOP
        DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
        v_price:=v_price+1;
      END LOOP;
    END;

    OUTPUT:
    ---------
    Price: 1
    Price: 2
    Price: 3
    Price: 4
    Price: 5

15) What are the coding standards in PL/SQL Block ?

v_variable for  variables
    Example: v_itemname, v_productname, v_employeeno

c_constant  for constants
    Example: c_discount

e_Exception for user-defined exceptions (Discussed later)
    Example: e_Invalid_ItemId, e_Invalid_EmpId
   
In PL/SQL

cur_cursorname for cursor variable (Discussed later)
Example: cur_branch, cur_emp

All table names and column names in lower case
Example: emp, employeename

All Keywords should be in UPPER case
Example: SELECT, FROM, WHERE, GROUP BY, ORDER BY
Usage: SELECT ename FROM emp WHERE empno=v_empno;