First 01 may first 01 feb

This preview shows page 28 - 33 out of 33 pages.

First payment: 20 108 01-May-2013 24 480 First payment: 20 109 01-Feb-2013 12 360 First payment: 30
Image of page 28
109 01-Mar-2013 12 360 First payment: 30 109 01-Apr-2013 12 360 First payment: 30 109 01-May-2013 12 360 First payment: 30 110 01-Mar-2013 12 300 First payment: 25 110 01-Apr-2013 12 300 First payment: 25 110 01-May-2013 12 300 First payment: 25 111 01-Mar-2013 0 1500 First payment: 1500 112 01-Apr-2013 12 240 First payment: 20 112 01-May-2013 12 240 First payment: 20 Statement processed. 0.00 seconds 11 – DECLARE TYPE type_input1 IS RECORD ( type dd_donor.typecode%TYPE, pamt dd_pledge.pledgeamt%TYPE); TYPE type_input2 IS TABLE OF type_input1 INDEX BY PLS_INTEGER; tbl_input2 type_input2; CURSOR cur_pledges (p_type VARCHAR2, p_amt NUMBER ) IS SELECT firstname || ' ' || lastname Name, pledgeamt FROM dd_donor INNER JOIN dd_pledge USING(iddonor) WHERE typecode= p_type
Image of page 29
AND pledgeamt > p_amt; BEGIN tbl_input2(1).type := 'I'; tbl_input2(1).pamt := 250; tbl_input2(2).type := 'B'; tbl_input2(2).pamt := 500; FOR i IN 1..tbl_input2.COUNT LOOP FOR rec_pledges IN cur_pledges(tbl_input2(i).type, tbl_input2(i).pamt) LOOP DBMS_OUTPUT.PUT_LINE(rec_pledges.name || ' - ' || rec_pledges.pledgeamt); END LOOP; END LOOP; END; Patrick Lee - 1200 Betty Konklin - 480 Mary Treanor - 360 Terry Venor - 300 Coastal Developers - 1500 Statement processed.
Image of page 30
0.02 seconds 13 - - Declare ex_donor_pk EXCEPTION; PRAGMA EXCEPTION_INIT(ex_donor_pk, -00001); Begin Insert into dd_donor(iddonor) values(302); Exception When ex_donor_pk Then Dbms_output.put_line('This ID is Already Assigned'); end; This ID is Already Assigned Statement processed. 0.00 seconds Please ma’am am doing this for extra marks Case project Case 4-1. Block one is an example of user defined exception while block two is an example of a predefined exception. PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows.
Image of page 31
CASE_NOT_FOUND. It is raised when none of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause. 4-2 Alter table mm_movie ADD stk_flag varchar2(5) Table altered. 0.04 seconds Declare Cursor cur_inv is Select * from mm_movie For update nowait; Begin For rec_inv in cur_inv loop if rec_inv.stk_flag >= 75 then update mm_movie set stk_flag = '*'; Else update mm_movie Set stk_flag ='' where current of cur_inv; End if; end loop; end;
Image of page 32
1 row(s) updated. 0.01 seconds
Image of page 33

You've reached the end of your free preview.

Want to read all 33 pages?

  • Spring '16
  • Mary Gable
  • Salary, Annual Salary, Stored procedure, new salary

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture