Problem #1:
SQL> select * from dept;

   DEPTNO DNAME          LOC
--------- -------------- -------------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO
       40 OPERATIONS     BOSTON
Explain the code below including an explanation of what would happen if the user entered 20 and what would happen if the user entered 24.
SET SERVEROUTPUT ON
SET VERIFY OFF
  ACCEPT p_deptno PROMPT 'Enter the dept no ';
DECLARE 
  v_deptno      dept.deptno%TYPE := &p_deptno; 
  v_dname       dept.dname%TYPE;
  v_loc         dept.loc%TYPE;  
BEGIN
  SELECT dname, loc
     INTO v_dname, v_loc
     FROM dept
     WHERE deptno = v_deptno;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('Invalid Department ' || TO_CHAR(v_deptno));
END;
/
SET SERVEROUTPUT OFF
SET VERIFY ON


Problem #2:
CREATE OR REPLACE TRIGGER sal_check
   BEFORE UPDATE OF sal ON emp_copy
   FOR EACH ROW
   WHEN (new.sal < old.sal)
BEGIN
   RAISE_APPLICATION_ERROR(-20002,'Salary reduction');
END sal_check;
/
SQL> update emp_copy
  2  set sal = 2000
  3  where empno = 7788;

SQL> update emp_copy 
  2  set sal = 4000
  3  where empno = 7788;
Explain the results of the two updates shown above.

Problem #3:
SET VERIFY OFF
DECLARE

  v_idno       copy_donation.idno%TYPE;
  v_driveno    copy_donation.driveno%TYPE;
  v_contamt    copy_donation.contamt%TYPE;
  v_newcontamt copy_donation.contamt%TYPE;
  v_code       VARCHAR2(2);
  CURSOR donor_cursor IS
    SELECT idno, driveno, contamt
       FROM copy_donation;
  FUNCTION CalcNewDon
    (f_code   VARCHAR2, f_contamt NUMBER)
    RETURN NUMBER IS
      f_newcontamt  quiz_donation.contamt%TYPE;
    BEGIN
      IF f_code = 'AA' THEN
         f_newcontamt := f_contamt * 1.3;
      ELSE
         IF f_code = 'BB' THEN
            f_newcontamt := f_contamt * 1.2;
         ELSE
            IF f_code = 'CC' THEN
               f_newcontamt := f_contamt * 1.1;
            ELSE
               f_newcontamt := f_contamt;
            END IF;
         END IF;
       END IF;
       RETURN f_newcontamt;
   END CalcNewDon;  
 
  PROCEDURE AddDonProc 
    (p_idno        quiz_donation.idno%TYPE,
     p_driveno     quiz_donation.driveno%TYPE,
     p_contamt     quiz_donation.contamt%TYPE) AS
  BEGIN
     INSERT INTO quiz_donation
        VALUES(p_idno, p_driveno, sysdate, p_contamt);
  END AddDonProc;

    

BEGIN
  OPEN donor_cursor;
  FETCH donor_cursor INTO v_idno, v_driveno, v_contamt;
  WHILE donor_cursor%FOUND LOOP
   IF v_contamt > 500 THEN
     v_code := 'AA';
     v_newcontamt := CalcNewDon(v_code, v_contamt);
   ELSE
     IF v_contamt > 200 THEN
        v_code := 'BB';
        v_newcontamt := CalcNewDon(v_code, v_contamt);  
     ELSE
       IF v_contamt > 75 THEN
          v_code := 'CC';
          v_newcontamt := CalcNewDon(v_code, v_contamt);
       ELSE
          v_code := 'DD';
          v_newcontamt := CalcNewDon(v_code, v_contamt);
       END IF;
     END IF;
   END IF;
   AddDonProc (v_idno, v_driveno, v_newcontamt);
   FETCH donor_cursor INTO v_idno, v_driveno, v_contamt;
  END LOOP;
  CLOSE donor_cursor;
END;
/
SET VERIFY ON
Here is a copy of copy_donation.
SQL> select * from copy_donation;

IDNO  DRI CONTDATE    CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99        25
12121 200 23-FEB-99        40
23456 100 03-MAR-99        20
33333 300 10-MAR-99        10
22222 100 14-MAR-99        10
12121 100 04-JUN-99        50
23456 300 14-JUN-99        10
11111 200 12-JUN-99        35

8 rows selected.
And here is the layout of quiz_donation.
SQL> desc quiz_donation;
 Name                            Null?    Type
 ------------------------------- -------- ----
 IDNO                                     VARCHAR2(5)
 DRIVENO                                  VARCHAR2(3)
 CONTDATE                                 DATE
 CONTAMT                                  NUMBER(6,2)

Show the output that would be produced from the code above. Explain the processing very completely.

Problem #4: You can do this problem by modifying existing programs or creating new ones. Use the three kinds of exceptions in a PL/SQL program or programs(s). Note: different from the assignment you already did on this. Document and explain.

In problem #5 #6, and #7, make sure you have one external function, one internal function, one external procedure and one internal procedure.

Problem #5: Modify one of the cursor programs you did and put some of the code in a procedure that you call.

Problem #6: Modify one of the cursor programs you did and put some of the code in a function that you call.

Problem #7: Modify one of the cursor programs you did and put some of the in a procedure that you call and some in a function that you call. Note you can also use one of my cursor programs that I did not change to have procedures or functions.

Problem #8: Set up a trigger so that when you run one of your cursor programs, you fire your trigger. Run the program and demonstrate that it caused the trigger to fire.

Problem #9: Do something with cursors, tables and records. Note I am talking about tables and records as used in the handout Record and Table types and the examples I went over in class.