PL/SQL Examples

PL/SQL Example:

     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);
          DBMS_OUTPUT.PUT_LINE ('qty ' ||v_itemqty);
          END;
         /
---------------------------------
--use of anchored variable
DECLARE
    v_studentid student.studentid%TYPE; --Mapping from database
    v_marks NUMBER(3) NOT NULL :=89;
    v_totalmarks v_marks%TYPE :=350; --mapping from previously declared variable
BEGIN
    v_studentid :='S001';
    DBMS_OUTPUT.PUT_LINE(v_studentid);
    DBMS_OUTPUT.PUT_LINE(v_marks);
    DBMS_OUTPUT.PUT_LINE(v_totalmarks);
END;
-----------------------------------------------------------------------
--Bind variable
VARIABLE g_doctorid VARCHAR2(20);
BEGIN
    :g_doctorid :='&docid';
END;
SQL> PRINT g_itemid
    G_ITEMID
    ----------------
    STN001
-----------------------------------------------------------------------
--subtitutional variable
DEFINE g_itemid ="PEN";
DECLARE
    v_itemname VARCHAR2(20) :='&g_itemid';
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_itemname);
END;
/
-----------------------------------------------------------------------
--IF ,ELSIF ,ELSE EXAMPLE : to find the largest number among 3
DECLARE
    v_num1 NUMBER :='&num1';
    v_num2 v_num1%TYPE :='&num2';
    v_num3 v_num2%TYPE :='&num3';
BEGIN
    DBMS_OUTPUT.PUT_LINE('The 3 numbers are :'||v_num1||',  '||v_num2||',  '||v_num3);
    IF (v_num1 > v_num2 AND v_num1 > v_num3) THEN
        DBMS_OUTPUT.PUT_LINE(v_num1 ||' is the largest number');
    ELSIF ( v_num2 > v_num1 AND v_num2 > v_num3) THEN
        DBMS_OUTPUT.PUT_LINE(v_num2 ||' is the largest number');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_num3 ||' is the largest number');
    END IF;
END;
-----------------------------------------------------------------------
--LOOP EXAMPLE(1)

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

--LOOP EXAMPLE(2) --Numeric FOR Loop
DECLARE
    v_num NUMBER ;--declaration is optional
BEGIN
    FOR v_price IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE('v_price: '||v_price);
    END LOOP;
END;

--LOOP EXAMPLE(3) --Numeric FOR Loop IN reverese

BEGIN
  FOR v_price IN REVERSE 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
  END LOOP;
END;
       
--LOOP EXAMPLE(3) -- while loop
DECLARE
    v_price NUMBER(5) :=1;
BEGIN
    WHILE v_price <= 5
    LOOP
        DBMS_OUTPUT.PUT_LINE('Price: '||v_price);
        v_price := v_price + 1;
    END LOOP;
END;
--------------------------------------------------------------
--use of select statement
--fetching few columns
DECLARE
    v_hostelid hostel.hostelid%TYPE;
    v_hostelfee hostel.hostelfee%TYPE;
BEGIN
    SELECT hostelid,hostelfee INTO v_hostelid,v_hostelfee FROM hostel WHERE studentid='S001';
    DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelid);
    DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelfee);
END;

Rules :
1.Only one row value can be returned to the variable_list
2.If no value is returned then the “No data found” exception is thrown
3.If more than one record is returned then the “Exact fetch returns more than requested number of rows” exception is thrown.


--Composite datatype : USING ROWTYPE
DECLARE
    v_hostelrec hostel%ROWTYPE;
BEGIN
    v_hostelrec.studentid :='&hostelid';
    SELECT * INTO v_hostelrec FROM hostel WHERE studentid=v_hostelrec.studentid;
    DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelrec.hostelid);
    DBMS_OUTPUT.PUT_LINE('room no :=' || v_hostelrec.roomno);
    DBMS_OUTPUT.PUT_LINE('student id :=' || v_hostelrec.studentid);
    DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelrec.hostelfee);
END;

--------------------------------------------------------------------------------------------------------
--Using SQL INSERT in PL/SQL 
--Can’t I write a insert statement as shown below to insert the record into the supplier table?
--yes we can insert
    INSERT INTO supplier(supplierid, suppliername, suppliercontactNo ) 
                                                VALUES ('&supplierid', '&suppliername', '&suppliercontactno');

--UPDATE
 BEGIN
   UPDATE supplier SET suppliercontactno = '0012244' WHERE supplierid='S001';
 END;
Write a PL/SQL block to insert the values from user.

--Using SQL DELETE in PL/SQL
BEGIN
  DELETE FROM supplier WHERE supplierid='S001';
END;

---------------------------------------------------------------------------
                      --PREDEFINED EXCEPTION--
---------------------------------------------------------------------------
--Predefined Oracle Server Exception
ORA-1403    NO_DATA_FOUND         SELECT statement matches no rows
ORA-1422    TOO_MANY_ROWS         SELECT statement matches more than one rows
ORA-0001    DUP_VAL_ON_INDEX      Uniq constraints voilated
ORA-1476    ZERO_DIVIDE           Division by zero
ORA-6502    VALUE_ERROR           Truncation,arithmetic error
ORA-1722    INVALID_NUMBER        Conversion to the number failed ex.2A is not valid

--NO_DATA_FOUND - Predefined Exception
DECLARE
    v_hostelid hostel.hostelid%TYPE;
    v_hostelfee hostel.hostelfee%TYPE;
    v_studentid hostel.studentid%TYPE;
BEGIN
    v_studentid :='&stdid';
    SELECT hostelid,hostelfee INTO v_hostelid,v_hostelfee FROM hostel WHERE studentid=v_studentid;
    DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelid);
    DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelfee);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('STUDENT IS NOT EXIST');
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('STUDENT ID IS TOO LARGE');
END;
/

--If the input entered is very large, truncation happens resulting in value error
--INVALID_NUMBER predefined exception
--Inserting a record to billing table As ‘X’ a character value cannot be converted to integer value for billno an INVALID_NUMBER exception is thrown

----------------------------------------------------------------------------------------------
                                --NON-PREDEFINED EXCEPTION
----------------------------------------------------------------------------------------------
--USE OF PRAGMA EXCEPTION_INIT

DECLARE
    e_MissingNull Exception;
    PRAGMA EXCEPTION_INIT(e_MissingNull,-1400);
BEGIN
    INSERT INTO applicant (applicantid) VALUES (NULL);
EXCEPTION
    WHEN e_MissingNull THEN
    DBMS_OUTPUT.PUT_LINE('NULL VALUE CANT INSERT');
END;
/

--User-defined Exception
--Given an invalid itemid display appropriate error message If the given itemid is invalid, display Invalid Itemid
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
    v_studentid hostel.studentid%TYPE;
    v_count NUMBER;
    e_Invalid_Studentid EXCEPTION;
BEGIN
    v_studentid :='&studentid';
    SELECT count(*) INTO v_count FROM hostel WHERE studentid=v_studentid;
    IF v_count = 0 THEN
        RAISE e_Invalid_Studentid;
    END IF;
        DBMS_OUTPUT.PUT_LINE('Valid student_d');
EXCEPTION
        WHEN e_Invalid_Studentid THEN
        DBMS_OUTPUT.PUT_LINE('invalid student id');
END;
/

--NOTE : exception can be raise in the DECLARATIVE,EXECUTABLE and EXCEPTION section

PL/SQL Tutorial ( Exception, Cursor ) Part - 02

---------------------------------------------------------------------------
                      --PREDEFINED EXCEPTION--
---------------------------------------------------------------------------
--Predefined Oracle Server Exception
ORA-1403    NO_DATA_FOUND         SELECT statement matches no rows
ORA-1422    TOO_MANY_ROWS         SELECT statement matches more than one rows
ORA-0001    DUP_VAL_ON_INDEX      Uniq constraints voilated
ORA-1476    ZERO_DIVIDE           Division by zero
ORA-6502    VALUE_ERROR           Truncation,arithmetic error
ORA-1722    INVALID_NUMBER        Conversion to the number failed ex.2A is not valid

--NO_DATA_FOUND - Predefined Exception
DECLARE
    v_hostelid hostel.hostelid%TYPE;
    v_hostelfee hostel.hostelfee%TYPE;
    v_studentid hostel.studentid%TYPE;
BEGIN
    v_studentid :='&stdid';
    SELECT hostelid,hostelfee INTO v_hostelid,v_hostelfee FROM hostel WHERE studentid=v_studentid;
    DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelid);
    DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelfee);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('STUDENT IS NOT EXIST');
    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('STUDENT ID IS TOO LARGE');
END;
/

--If the input entered is very large, truncation happens resulting in value error
--INVALID_NUMBER predefined exception
--Inserting a record to billing table As ‘X’ a character value cannot be converted to integer value for billno an INVALID_NUMBER exception is thrown

----------------------------------------------------------------------------------------------
                                --NON-PREDEFINED EXCEPTION
----------------------------------------------------------------------------------------------
--USE OF PRAGMA EXCEPTION_INIT

DECLARE
    e_MissingNull Exception;
    PRAGMA EXCEPTION_INIT(e_MissingNull,-1400);
BEGIN
    INSERT INTO applicant (applicantid) VALUES (NULL);
EXCEPTION
    WHEN e_MissingNull THEN
    DBMS_OUTPUT.PUT_LINE('NULL VALUE CANT INSERT');
END;
/

--User-defined Exception
--Given an invalid itemid display appropriate error message If the given itemid is invalid, display Invalid Itemid
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
    v_studentid hostel.studentid%TYPE;
    v_count NUMBER;
    e_Invalid_Studentid EXCEPTION;
BEGIN
    v_studentid :='&studentid';
    SELECT count(*) INTO v_count FROM hostel WHERE studentid=v_studentid;
    IF v_count = 0 THEN
        RAISE e_Invalid_Studentid;
    END IF;
        DBMS_OUTPUT.PUT_LINE('Valid student_d');
EXCEPTION
        WHEN e_Invalid_Studentid THEN
        DBMS_OUTPUT.PUT_LINE('invalid student id');
END;
/

--NOTE : exception can be raise in the DECLARATIVE,EXECUTABLE and EXCEPTION section

--------------------------------------------------------------------------------------
                                --CURSOR
--------------------------------------------------------------------------------------
--Implicit Cursor Attributes
Using SQL cursor attributes, you can test the outcome of your SQL statements
implicit Cursor Attribute          Meaning
----------------------------------------------------------------------------------
SQL%ROWCOUNT              Number of records affected by the most recent SQL statement
SQL%FOUND                  Evaluates to TRUE if the most recent SQL statement affects one or more rows
SQL%NOTFOUND              Evaluates to TRUE if the most recent SQL statement does not affect any rows
SQL%ISOPEN               Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed

--Do not use implicit cursor attribute to check the unsuccessfulness of SELECT statement, because PL/SQL returns exception when the SELECT statement
--fails. check below example
DECLARE
    v_hostelid hostel.hostelid%TYPE;
    v_hostelfee hostel.hostelfee%TYPE;
    v_studentid hostel.studentid%TYPE;
BEGIN
    v_studentid :='&studentid';
    SELECT hostelid,hostelfee INTO v_hostelid,v_hostelfee FROM hostel WHERE studentid=v_studentid;
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('NO RECORD FOUND');
    ELSE
            DBMS_OUTPUT.PUT_LINE('FOLLOWING RECORD FOUND');
            DBMS_OUTPUT.PUT_LINE('hostel id :=' || v_hostelid);
            DBMS_OUTPUT.PUT_LINE('hostel fee :=' || v_hostelfee);
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found exception thrown');
END;

-----------------------------------------------------------------------------------------

DECLARE
    v_studentid hostel.studentid%TYPE;
BEGIN
    v_studentid :='&studentid';
    UPDATE hostel SET hostelfee=hostelfee+100 WHERE studentid > v_studentid;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||'Rows has been updated');
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('NO RECORD FOUND');
    END IF;
    COMMIT;
EXCEPTION
    WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('VALUE IS TO LARGE');
END;
  
--Explicit Cursors :
--------------------
--Explicit cursors example - Simple loop (1 of 2)
DECLARE
    CURSOR cur_hosteldet IS SELECT studentid,userid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
    v_studentid hostel.studentid%TYPE;
    v_userid student.userid%TYPE;
BEGIN
    OPEN cur_hosteldet;
    LOOP
        FETCH cur_hosteldet INTO v_studentid,v_userid;
        EXIT WHEN cur_hosteldet%NOTFOUND;
        IF v_studentid > 'S007' THEN
            UPDATE hostel SET hostelfee=hostelfee+100;
        END IF;
        DBMS_OUTPUT.PUT_LINE(v_studentid || ' : ' || v_userid);
    END LOOP;
    CLOSE cur_hosteldet;
COMMIT;
END;

--explicit cursur attributes
----------------------------
                    %FOUND      %ISOPEN  %NOTFOUND  %ROWCOUNT
After OPEN          NULL          TRUE       NULL             0
After 1st FETCH     TRUE          TRUE       FALSE         1
After 2nd FETCH     TRUE          TRUE       FALSE         2
After Last FETCH     FALSE          TRUE      TRUE          Data dependent
After CLOSE         exception     FALSE       exception  exception
      
--Explicit Cursor - Record variables (1 of 2)
CURSOR cur_itemdet2 IS SELECT itemid, qtypurchased, netprice FROM Customerpurchase WHERE netprice > 20;
v_itemrec cur_itemdet2%ROWTYPE;
v_olditemrec v_itemrec%TYPE;

--Example of Explicit cursors – Record variables (2 of 2)
DECLARE
    CURSOR cur_hosteldet IS SELECT studentid,userid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
    v_hostelrec cur_hosteldet%ROWTYPE;
BEGIN
    OPEN cur_hosteldet;
    LOOP
        FETCH cur_hosteldet INTO v_hostelrec;
        EXIT WHEN cur_hosteldet%NOTFOUND;
        IF v_hostelrec.studentid > 'S007' THEN
            UPDATE hostel SET hostelfee=hostelfee+100;
        END IF;
        DBMS_OUTPUT.PUT_LINE(v_hostelrec.studentid || ' : ' || v_hostelrec.userid);
    END LOOP;
    CLOSE cur_hosteldet;
COMMIT;
END;

--Explicit Cursor – WHILE LOOP (1 of 2)

DECLARE
    CURSOR cur_studentdet IS SELECT studentid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
    v_studentid student.studentid%TYPE;
BEGIN
    OPEN cur_studentdet;
    FETCH cur_studentdet INTO v_studentid;
    WHILE cur_studentdet%FOUND
    LOOP
        UPDATE student SET password='lion' WHERE studentid=v_studentid;
        DBMS_OUTPUT.PUT_LINE(v_studentid);
        FETCH cur_studentdet INTO v_studentid;
    END LOOP;
    CLOSE cur_studentdet;
    COMMIT;
END;

--Cursor FOR LOOP (1 of 2)
• Implicit open, fetch, exit condition check, close
• Implicit record variable declaration

DECLARE
    CURSOR cur_studentdet IS SELECT studentid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
BEGIN
    FOR v_studentrec IN cur_studentdet
    LOOP
        UPDATE student SET password='tiger' WHERE studentid=v_studentrec.studentid;
        DBMS_OUTPUT.PUT_LINE(v_studentrec.studentid);
    END LOOP;
COMMIT;
END;

--implicit FOR Loops (2 of 2)
the cursor itself can be implicitly declared

BEGIN
    FOR v_studentrec IN (SELECT studentid FROM student WHERE studentid IN (SELECT studentid FROM hostel))
    LOOP
        UPDATE student SET password='lion' WHERE studentid=v_studentrec.studentid;
        DBMS_OUTPUT.PUT_LINE(v_studentrec.studentid);
    END LOOP;
COMMIT;
END;
      
--Predefined Oracle Server Exception –Cursor related
oracle error    predefined exception    description
------------    --------------------    -----------
ORA-1001         INVALID_CURSOR            Illegale cursor operation
ORA-6511         CURSOR_ALREADY_OPEN    Attempt open a cursor that already open

DECLARE
    CURSOR cur_studentdet IS SELECT studentid FROM student WHERE studentid IN (SELECT studentid FROM hostel);
    v_studentid student.studentid%TYPE;
BEGIN
    OPEN cur_studentdet;
    FETCH cur_studentdet INTO v_studentid;
    WHILE cur_studentdet%FOUND
    LOOP
        UPDATE student SET password='lion' WHERE studentid=v_studentid;
        DBMS_OUTPUT.PUT_LINE(v_studentid);
        FETCH cur_studentdet INTO v_studentid;
    END LOOP;
    CLOSE cur_studentdet;
    COMMIT;
EXCEPTION
    WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE('Invalid cursor exception thrown');
    WHEN CURSOR_ALREADY_OPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor already open exception thrown');
END;

--Parameterized cursors (1 of 2)

DECLARE
    CURSOR cur_studentdet(p_password VARCHAR2) IS SELECT * FROM student WHERE password=p_password;
    v_studentrec cur_studentdet%ROWTYPE;
BEGIN
    OPEN cur_studentdet('lion');
    LOOP
        FETCH cur_studentdet INTO v_studentrec;
        EXIT WHEN cur_studentdet%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_studentrec.studentid ||'  ' ||v_studentrec.userid ||'  '|| v_studentrec.password);
        END LOOP;
    CLOSE cur_studentdet;
END;

--FOR UPDATE cursor declaration (4 of 5)
DECLARE
    CURSOR cur_itemdet1 IS SELECT * FROM ITEM WHERE ITEMID LIKE 'STN%' FOR UPDATE OF unitprice;
    v_itemrec cur_itemdet1%ROWTYPE;
BEGIN
    OPEN cur_itemdet1;
    LOOP
        FETCH cur_itemdet1 INTO v_itemrec;
        EXIT WHEN cur_itemdet1%NOTFOUND;
            UPDATE item SET unitprice=unitprice+1 WHERE CURRENT OF cur_itemdet1;
             DBMS_OUTPUT.PUT_LINE(v_itemrec.itemid);
    END LOOP;
    CLOSE cur_itemdet1;
COMMIT;
END;

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;

Learn SQL Trigger ( MYSQL)

Introduction to SQL Trigger

A SQL trigger is a set of  SQL statements stored in the database. A SQL trigger is executes whenever an event(SQL INSERT, UPDATE, or DELETE statement) associated with a table occurs.

Trigger is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.

Advantages of using SQL triggers

• SQL triggers provide an alternative way to check the integrity of data.
• SQL triggers can catch errors in business logic in the database layer.
• SQL triggers provide an alternative way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked  automatically before or after a change  is made to the data in the tables.
• SQL triggers are very useful to audit the changes of data in tables.

MySQL Triggers Implementation
 
A trigger can be defined to be invoked either before or after the data is changed by INSERT, UPDATE or DELETE statement.

BEFORE INSERT/UPDATE/DELETE – activated before data is inserted/updated/deleted into the table.
AFTER INSERT/UPDATE/DELETE – activated after data is inserted/updated/deleted into the table.

MySQL triggers storage

MySQL stores triggers in a data directory e.g., /data/classicmodels/ with the files named tablename.TRG and triggername.TRN :

- The tablename.TRG file maps the trigger to the corresponding table.
- The triggername.TRN file contains the trigger definition.
- You can back up the MySQL triggers by copying the trigger files to the backup folder.
- You can also backup the triggers using the mysqldump tool.

Creating Trigger in MySQL
 
MySQL trigger syntax

Following illustrates the syntax of the CREATE TRIGGER statement:

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON table_name
    FOR EACH ROW
 BEGIN
 ...
 END;

Where:
trigger_name : Trigger name and trigger name should follow the naming convention [trigger time]_[table name]_[trigger event]. Example: before_customer_update.
trigger_time : Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you define a trigger.
trigger_event: The trigger event can be INSERT, UPDATE or DELETE.

Note:
A trigger must be associated with a specific table. Without a table trigger would not exist therefore you have to specify the table name after the ON keyword.
You place the SQL statements between BEGIN and END block.

Example of MySQL trigger.
Let us start creating a tringger in mysql database before that just consider a table CUSTOMER as following for our demo.

+------------+-----------+----------+------+------------+------------------+
| customerID | firstname | lastname | sex  | DOB        | customer_email   |
+------------+-----------+----------+------+------------+------------------+
|          1 | mukesh    | kumar    | M    | 0000-00-00 | mukesh@gmail.com |
|          2 | shivam    | shakya   | M    | 2011-11-12 | shivam@gmail.com |
|          5 | ritu      | shakya   | F    | 2011-11-14 | ritu@gmail.com   |
|          6 | Abhishek  | Shakya   | M    | 1992-11-11 | abi@gmail.com    |
+------------+-----------+----------+------+------------+------------------+

Create a new table named customer_audit to keep the changes of the employee table.

CREATE TABLE customer_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    customer_lname VARCHAR(50) NOT NULL,
    changedat DATETIME DEFAULT NULL,
    action VARCHAR(50) DEFAULT NULL
);

Next, create a BEFORE UPDATE trigger that is invoked before a change is made to the CUSTOMER table.

DELIMITER $$
CREATE TRIGGER before_CUSTOMER_update
    BEFORE UPDATE ON CUSTOMER
    FOR EACH ROW
BEGIN
    INSERT INTO CUSTOMER_audit
    SET action = 'update',
         customerID = OLD.customerID,
        lastname = OLD.lastname,
        changedat = NOW();
END$$
DELIMITER ;

Here inside this trigger, we used the OLD keyword to access customerID and lastname column of the row affected by the trigger.

Note :

• In a trigger defined for INSERT, you can use NEW keyword only.  
• In the trigger defined for DELETE, there is no new row so you can use the OLD keyword only.
• In the UPDATE trigger, OLD refers to the row before it is updated and NEW refers to the row after it is updated.

Once trigger created you use SHOW TRIGGERS statement as follows:

mysql> show triggers;

Testing the trigger
Update table CUSTOMER to check if trigger is invoking or not. before update you can check customer_audit table.

Update table  CUSTOMER
SET
    lastname='Singh'
WHERE
    customerID=6;
  
Now as you can see below table that trigger had been invoked and make the old entry into customer_audit table.  

mysql> select * from customer_audit;
+----+-------------+----------------+---------------------+--------+
| id | customer_id | customer_lname | changedat           | action |
+----+-------------+----------------+---------------------+--------+
|  1 |           6 | Shakya         | 2017-03-29 06:42:50 | update |
+----+-------------+----------------+---------------------+--------+

Managing Triggers in MySQL

After creating a trigger, you can display its definition in the data folder, which contains trigger definition file. A trigger is stored as a plain text file in the following database folder:

/data_folder/database_name/table_name.trg

You can also display the trigger in MYSQL by querying the triggers table in the information_schema database as follows:

SELECT
    *
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'database_name'
        AND trigger_name = 'trigger_name';

The statement allows you to view both content of the trigger and its metadata such as associated table name and definer, which is the name of MySQL user who created the trigger.

To find all triggers associated with a particular table, you use the following query:

SELECT
    *
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'database_name'
        AND event_object_table = 'table_name';


MySQL SHOW TRIGGERS statement
Another quick way to display triggers in a particular database is to use SHOW TRIGGERS statement as follows:

SHOW TRIGGERS [FROM|IN] database_name
[LIKE expr | WHERE expr];

For example, if you want to view all triggers in the current database, you can use the SHOW TRIGGERS statement as follows:

SHOW TRIGGERS

To get all triggers in a specific database, you specify the database name in the SHOW TRIGGERS  statement as the statement below:

SHOW TRIGGERS FROM demodb;

It returns all triggers in the demodb database.

To get all the trigger associated with a specific table, you use the WHERE clause in the SHOW TRIGGERS statement. The following statement returns all triggers associated with the employees table:

SHOW TRIGGERS FROM demodb WHERE `table` = 'CUSTOMER';

Removing a trigger
To remove an existing trigger, you use DROP TRIGGER statement as follows:

DROP TRIGGER table_name.trigger_name;

DROP TRIGGER CUSTOMER.before_CUSTOMER_update;

NOTE:
To modify a trigger, you have to delete it first and recreate it with the new code. There is no such ALTER TRIGGER statement available in MySQL, therefore, you cannot modify an existing trigger like modifying other database objects such as tables, views, and stored procedures.