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;