Assignment 3-12: Retrieving a Specific Pledge Create a PL/SQL block to retrieve
ID: 3721621 • Letter: A
Question
Assignment 3-12: Retrieving a Specific Pledge
Create a PL/SQL block to retrieve and display information for a specific pledge. Display the pledge ID, donor ID, pledge amount, total paid so far, and the difference between the pledged amount and total paid amount.
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
SQL:
DECLARE
PLEDGES DD_PLEDGE%ROWTYPE;
PLEDGE_TOTAL_TO_PAY DD_PLEDGE.PLEDGEAMT%TYPE;
bal DD_PLEDGE.PLEDGEAMT%TYPE;
PAID_MONTHS_ NUMBER (5);
BEGIN
SELECT *
INTO PLEDGES
FROM DD_PLEDGE
WHERE IDPLEDGE =& PLEDGE_ID;
PAID_MONTHS_ := PLEDGES.PAYMONTHS;
IF PLEDGES.PAYMONTHS = 0 THEN
PLEDGE_TOTAL_TO_PAY := PLEDGES.PLEDGEAMT;
ELSE PLEDGE_TOTAL_TO_PAY := PAID_MONTHS_ * (PLEDGES.PLEDGEAMT/PLEDGES.PAYMONTHS);
END IF;
bal := PLEDGES.PLEDGEAMT - PLEDGE_TOTAL_TO_PAY;
DBMS_OUTPUT.PUT_LINE('ID: ' || PLEDGES.IDPLEDGE || ', ID of the Donor: ' || PLEDGES.IDDONOR ||
', Amount: ' || PLEDGE_TOTAL_TO_PAY || ' ' || ', Balance: ' || bal);
END;