Hands-On Assignments Part II Assignment 7-9: Creating a Package for Pledges Crea

ID: 3719784 • Letter: H

Question

Hands-On Assignments Part II Assignment 7-9:
Creating a Package for Pledges Create a package named PLEDGE_PKG that includes
two functions for determining dates of pledge payments.
Use or create the functions described in Chapter 6 for Assignments 6-12 and 6-13,
using the names DD_PAYDATE1_PF and DD_PAYEND_PF for these packaged functions.
Test both functions with a specific pledge ID, using an anonymous block.
Then test both functions in a single query showing all pledges and associated payment dates.

Assignment 7-10: Adding a Pledge Display Procedure to the Package Modify the package created in Assignment 7-9 as follows: •
Add a procedure named DD_PLIST_PP that displays the donor name and all associated pledges (including pledge ID, first payment due date, and last payment due date). A donor ID is the input value for the procedure.
•   Make the procedure public and the two functions private.
Test the procedure with an anonymous block.
Assignment 7-11: Adding a Payment Retrieval Procedure to the Package Modify the package created in Assignment 7-10 as follows:
•   Add a new procedure named DD_PAYS_PP that retrieves donor pledge payment information and returns all the required data via a single parameter.
•   A donor ID is the input for the procedure.
•   The procedure should retrieve the donor’s last name and each pledge payment made so far (including payment amount and payment date).
•   Make the procedure public. Test the procedure with an anonymous block.
The procedure call must handle the data being returned by means of a single parameter in the procedure.
For each pledge payment, make sure the pledge ID, donor’s last name,
pledge payment amount, and pledge payment date are displayed.

oracle 11g PL/SQL Programming joan casteel hands on assigment

Explanation / Answer

7-9

CREATE OR REPLACE PACKAGE Pledge_pkg AS

FUNCTION DD_PAYDATE1_PF (P_ID Number)

RETURN Pay_date;

Begin

Select pay_date from pledge where pledgeID=P_ID;

Return pay_date;

End;

FUNCTION DD_PAYEND_PF (Pledge_ID Number)

RETURN Pay_end;

Begin

Select pay_end from pledge where pledgeID=P_ID;

Return pay_end;

End;

END Pledge_pkg;

7 -10

CREATE OR REPLACE PACKAGE Pledge_pkg IS

FUNCTION DD_PAYDATE1_PF (P_ID Number)

FUNCTION DD_PAYEND_PF (Pledge_ID Number) // Private made with

//forward declaratiom

Procedure DD_PLIST_PP(d_id Number)

Begin

For x in (select PledgeID, Pay_date, pay_end from Pledge where donor_id=d_id

Loop

Dbms_output.putline(‘Pledge ‘|| x.pledge_id);

Dbms_output.putline(‘Pay First Date ‘|| x.Pay_date);

Dbms_output.putline(‘Due date ‘|| x.pay_end);

End Loop;

End DD_Plist_PP;

FUNCTION DD_PAYDATE1_PF (P_ID Number)

RETURN Pay_date;

Begin

Select pay_date from pledge where pledgeID=P_ID;

Return pay_date;

End;

FUNCTION DD_PAYEND_PF (Pledge_ID Number)

RETURN Pay_end;

Begin

Select pay_end from pledge where pledgeID=P_ID;

Return pay_end;

End;

END Pledge_pkg;

7-11

//Assuming we have another table for donor information
CREATE OR REPLACE PACKAGE Pledge_pkg IS
FUNCTION DD_PAYDATE1_PF (P_ID Number)
   FUNCTION DD_PAYEND_PF (Pledge_ID Number) // Private made with
//forward declaratiom
TYPE out_ref IS RECORD (
    Don_id    NUMBER(6),
    Name VARCHAR2(30),
    Pay1 Date,
Pay2 Date,
Amount Number);
TYPE rec IS TABLE OF rec_typ;
As
FUNCTION DD_Pays_PP(D_ID Number)
   RETURN Myrec PIPELINED IS

     rec1 rec_typ;
    Myrec p%ROWTYPE;
BEGIN
LOOP
    FETCH d_ID INTO Myrec;
    EXIT WHEN p%NOTFOUND;
    rec1.Don_id := Myrec.Don_id;
rec1.Name := Myrec.Name;
    rec1.Pay1 := Myrec.Pay2;
rec1.Pay2 := Myrec.Pay2;
    rec1.Amount := Myrec.Amount;
    PIPE ROW(rec1);

END LOOP;
RETURN;
END;

-- select for above function is
-- SELECT * FROM Table(
Pledge_pkg. DD_Pays_PP (CURSOR
(select d.lastname, p.Pay_date, p.pay_end, p.payamt from Pledge p, donor d where p.donor_id=d_id)));

Procedure DD_PLIST_PP(d_id Number)
Begin
For x in (select PledgeID, Pay_date, pay_end from Pledge where donor_id=d_id
Loop
Dbms_output.putline(‘Pledge ‘|| x.pledge_id);
Dbms_output.putline(‘Pay First Date ‘|| x.Pay_date);
Dbms_output.putline(‘Due date ‘|| x.pay_end);
End Loop;
End DD_Plist_PP;

FUNCTION DD_PAYDATE1_PF (P_ID Number)
RETURN Pay_date;
Begin
Select pay_date from pledge where pledgeID=P_ID;
Return pay_date;
End;

FUNCTION DD_PAYEND_PF (Pledge_ID Number)
RETURN Pay_end;
Begin
Select pay_end from pledge where pledgeID=P_ID;
Return pay_end;
End;
END Pledge_pkg;

Navigate

Hands-On Assignments Create and execute SQL statements to perform the following

Hands-On Assignments To perform the following assignments, refer to the tables c

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Hands-On Assignments Part II Assignment 7-9: Creating a Package for Pledges Crea

Question

Explanation / Answer

Related Questions

Navigate