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