These two are tripping me up. PL/SQL Assignment 4-9: Using an Explicit Cursor Cr
ID: 3721672 • Letter: T
Question
These two are tripping me up. PL/SQL
Assignment 4-9: Using an Explicit Cursor
Create a block to retrieve and display pledge and payment information for a specific donor. For each pledge payment from the donor, display the pledge ID, pledge amount, number of monthly payments, payment date, and payment amount. The list should be sorted by pledge ID and then by payment date. For the first payment made for each pledge, display “first payment” on that output row.
Assignment 4-10: Using a Different Form of Explicit Cursors
Redo Assignment 4-9, but use a different cursor form to perform the same task.
DD DONOR DD PAYMENT COLUMN NAMEDATA TYPE NULLABLE DATA-DEFAULT |COLUMN. ID|?COMENTS! COLUMN NAMEDATA TYPE NLLLABLE DATA. DEFAULT |?COLUMN-ID | COMMENTS NUIIBER (4,0) VARCHAR2 (15 BYTE) Yes VARCHAR2 (30 BYTE) Yes CHAR (l BYTE) VARCHAR2(40 BYTE) Yes VARCHAR2 (20 BYTE) Yes CHAR (2 BYTE) VARCHAR2(9 BYTE) Yes VARCHAR2(10 BYTE) Yes VARCHAR2(10 BYTE) Yes VARCHAR2 (25 BYTE) Yes CHAR(1 BYTE) DATE 1 IDDOIOR 2 FIRSTNAME 3 LASTTAME 4 TYPECODE IDPAY 2 IDPLEDGE 3 PAYAMT 4 PAYDATE S PAYMETHOD NUIBER(6,0) No NUMBER(5,0) Yes NUBER (8,2) Yes DATE CHAR (2 BYTE) Yes null) (null) null) (null) 1 (null) 2 (null) nu 4 (null) (null) (null) nu 2 (null) 3 (null) 4 (null) 5 (null) nu (null) STREET 6 CITY 7 STATE 8 2IP (null) (null) DD PROJECT COLUMN NAMEDATA TYPE NULLABLE DATA DEFAULT COLUMN _ID COMMENTS 8 (null;) 9 (null) nu 1IDPROT 2 PROJNAME 3 PROJSTARTDATE DATE 4 PROJENDDATE DATE 5 PROJFUNDGOAL NUMBER(12,2) 6 PROJCOORD NUMBER(6,0) VARCHAR2 (60 BYTE) Yes null) (null) 1 (null) 2 (null) PHONE nu (null) (null) (null) SYSDATE 11 EMAIL (null) (null) null) 4 (null) 5 (null) 6 (null) 13 DTENTERED 13 (nul1) VARCHAR2 (20 BYTE) Yes DD PLEDGE DATA-TYPE Ut COLUMN NAME 1 IDPLEDGE 2 IDDONOR |?, NULLABLE DATA-DEFAULT ?V COLUMN-ID?COMMENTS DD STATUS (null) l (null) COLUMN,NAME NULLABLE DATA DEFAULT COLUMN IDCOMMENTS DATA_TYPE NUNBER (2,0) VARCHAR2(15 BYTE) Yes NUNBER (4,0) Yes nu inull) inullj (null) (null) 1 IDSTATUS (null) PLEDGEDATE DATE 4 PLEDGEAMT NUIBER (8,2) Ye:s S IDPROJ 6 IDSTATUS 7 URITEOFF 8 PAYMONTHS 9 CAMPAIGN 10 FIRSTPLEDGE CHAR(1 BYTE) Yes 3 (null) 4 fnull) 5 (null) 6 (null) 2 STATUSDESC inull) NUIIBER (5,0) Yes NUBER (2,0) Yes NUBER (8,2) Yes NUIBER3,0) Yes NUIBER(4,0) Yes nu inull) 8 null) (null) 10 (null)Explanation / Answer
Assignment 4-9:
1> Declare
CURSOR PLEDGE_PAYMENT IS
SELECT DD_PLEDGE.IDPLEDGE, DD_PLEDGE.PLEDGEAMT, DD_PLEDGE.PAYMONTHS, DD_PAYMENT.PAYDATE, DD_PAYMENT.PAYAMT FROM ((DD_DONOR INNER JOIN DD_PLEDGE ON DD_DONOR.IDDONOR=DD_PLEDGE.IDDONOR) INNER JOIN DD_PAYMENT on DD_PLEDGE.IDPLEDGE = DD_PAYMENT.IDPLEDGE) ORDER BY IDPLEDGE, PAYDATE.
Rec_Pledge_Payment Pledge_Payment%rowtype;
BEGIN
OPEN PLEDGE_PAYMENT;
LOOP
FETCH PLEDGE_PAYMENT INTO Rec_Pledge_Payment;
EXIT When PLEDGE_PAYMENT%NOTFOUND:
dbms_output.putline(PLEDGE_PAYMENT.IDPLEDGE||' '|| PLEDGE_PAYMENT.PLEDGEAMT||' '||PLEDGE_PAYMENT.PAYMONTHS||' '||PLEDGE_PAYMENT.PAYDATE||' '||PLEDGE_PAYMENT.PAYAMT);
ENDLOOP;
END;
ASSIGNMENT 4-10
BEGIN
SELECT DD_PLEDGE.IDPLEDGE, DD_PLEDGE.PLEDGEAMT, DD_PLEDGE.PAYMONTHS, DD_PAYMENT.PAYDATE, DD_PAYMENT.PAYAMT FROM ((DD_DONOR INNER JOIN DD_PLEDGE ON DD_DONOR.IDDONOR=DD_PLEDGE.IDDONOR) INNER JOIN DD_PAYMENT on DD_PLEDGE.IDPLEDGE = DD_PAYMENT.IDPLEDGE) ORDER BY IDPLEDGE, PAYDATE.
IF SQL%NOTFOUND THEN
dbms_output.put_line('Records end');
ELSEIF SQL%FOUND THEN
dbms_output.putline(PLEDGE_PAYMENT.IDPLEDGE||' '|| PLEDGE_PAYMENT.PLEDGEAMT||' '||PLEDGE_PAYMENT.PAYMONTHS||' '||PLEDGE_PAYMENT.PAYDATE||' '||PLEDGE_PAYMENT.PAYAMT);
ENDIF;
END;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.