Create a block to retrieve and display pledge and payment information for a spec
ID: 3816472 • Letter: C
Question
Create a block to retrieve and display pledge and payment information for a specific donor. Foreach pledge payment from the donor, display the pledge ID, pledge amount, number of monthlypayments, payment date, and payment amount. The list should be sorted by pledge ID and thenby payment date. For the first payment made for each pledge, display “first payment” on thatoutput row.
We need to pl/sql languange and have to use explicit cursor.
here is the table:
CREATE TABLE DD_Donor (
idDonor number(4),
Firstname varchar2(15),
Lastname varchar2(30),
Typecode CHAR(1),
Street varchar2(40),
City varchar2(20),
State char(2),
Zip varchar2(9),
Phone varchar2(10),
Fax varchar2(10),
Email varchar2(25),
News char(1),
dtentered date DEFAULT SYSDATE,
CONSTRAINT donor_id_pk PRIMARY KEY(idDonor) );
CREATE TABLE DD_Project (
idProj number(6),
Projname varchar2(60),
Projstartdate DATE,
Projenddate DATE,
Projfundgoal number(12,2),
ProjCoord varchar2(20),
CONSTRAINT project_id_pk PRIMARY KEY(idProj),
CONSTRAINT project_name_uk UNIQUE (Projname) );
CREATE TABLE DD_Status (
idStatus number(2),
Statusdesc varchar2(15),
CONSTRAINT status_id_pk PRIMARY KEY(idStatus) );
CREATE TABLE DD_Pledge (
idPledge number(5),
idDonor number(4),
Pledgedate DATE,
Pledgeamt number(8,2),
idProj number(5),
idStatus number(2),
Writeoff number(8,2),
paymonths number(3),
Campaign number(4),
Firstpledge char(1),
CONSTRAINT pledge_id_pk PRIMARY KEY(idPledge),
CONSTRAINT pledge_idDonor_fk FOREIGN KEY (idDonor)
REFERENCES dd_donor (idDonor),
CONSTRAINT pledge_idProj_fk FOREIGN KEY (idProj)
REFERENCES dd_project (idProj),
CONSTRAINT pledge_idStatus_fk FOREIGN KEY (idStatus)
REFERENCES dd_status (idStatus));
CREATE TABLE DD_Payment (
idPay number(6),
idPledge number(5),
Payamt number(8,2),
Paydate DATE,
Paymethod char(2),
CONSTRAINT payment_id_pk PRIMARY KEY(idPay),
CONSTRAINT pay_idpledge_fk FOREIGN KEY (idPledge)
REFERENCES dd_pledge (idPledge) );
Explanation / Answer
Below is the code for explicit cursor for the mentioned condition below.
Brief explanation of what explicit cursor does is also explained before the code.
Since its been asked to define the cursor on the data we need to code explicit cursor for it.
Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.
Working with an explicit cursor includes the following steps
CURSOR cursor_name IS
SELECT ………………….;
OPEN cursor_name;
FETCH cursor_name INTO……………………………..;
CLOSE cursor_name;
==================================================================================
PL/SQL CURSOR CODE
DECLARE
did PL.idDonor number%type
plamt PL.Pledgeamt number%type
plpmnth PL.paymonths number%type
pdate PA.Paydate%type
pmnth PA.Payamt number%type
CURSOR donors_pledge_info is
SELECT PL.idDonor number,PL.Pledgeamt number,PL.paymonths number,PA.Paydate,PA.Payamt number
FROM DD_Donor AS D ,DD_Pledge AS PL , DD_Payment AS PA
WHERE D.idDonor number=PL.idDonor number AND PL.idPledge number=PA.idPledge number
ORDER BY PLEDGE ID,PAYMENT DATE
BEGIN
OPEN donors_pledge_info;
LOOP
FETCH donors_pledge_info into did,plamt,plpmnth,pdate,pmnth;
EXIT WHEN donors_pledge_info %notfound;
dbms_output.put_line(did || ' ' || plamt || ' ' || plpmnth || ' ' || pdate || ' ' || pmnth);
END LOOP;
CLOSE donors_pledge_info;
END;
======================================================================================
please let me know in case any clarification is required.