Question

ORACLE

TASK 2-2 USING A FOR LOOP

Create a PL/SQL block using a FOR loop to generate a payment schedule for a donor’s pledge, which is to be paid monthly in equal increments. Values variable for the block are starting payment due date, monthly payment amount and number of total monthly payments for the pledge. The list that’s generated should display a line for each monthly payment showing payment number, date due, payment amount, and donation balance (remaining amount of pledge owed).

USING A BASIC LOOP

Accomplish the task in TASK 2-2 above by using a basic loop structure.

USING A WHILE LOOP

Accomplish the task in TASK 2-2 by using a WHILE loop structure. Instead of displaying the donation balance (remaining amount of pledge owed) on each line of output, display the total paid to date.

USING A CASE EXPRESSION

Donors can select one of three payment plans for a pledge indicated by the following codes: 0 = one-time (lump sum) payment, 1 = monthly payments over one year, and 2 = monthly payments over two years. A local business has agreed to pay matching amounts on pledge payments during the current month. A PL/SQL block is needed to identify the matching amount for a pledge payment. Create a block using input values of a payment plan code and a payment amount. Use a CASE expression to calculate the matching amount, based on the payment plan codes 0 = 25%, 1 = 50%, 2 = 100%, and other = 0. Display the calculated amount.

USING NESTED IF STATEMENTS

An organization has committed to matching pledge amounts based on the donor type and pledge amount. Donor types include I = Individual, B = Business organization, and G = Grant funds. The matching percents are to be applied as follows:

Donor Type	Pledge Amount	Matching (%)
I	$100-$249	50%
I	$250-$499	30%
I	$500 or more	20%
B	$100-$499	20%
B	$500-$999	10%
B	$1,000 or more	5%
G	$100 or more	5%

          Create a PL/SQL block using nested IF statements to accomplish the task. Input value for the block are the donor type code and the pledge amount.

Answer 1

Task 2-2 Using for loop

SET SERVEROUTPUT ON
DECLARE
number_of_payments NUMBER(2);    --Total # of payments
payment_num NUMBER (2);          --current payment # of total
start_date DATE;                 --payment start date
due_date DATE;                   --payment due date
monthly_payment_amt NUMBER (8,2; --monthly amt date
donation_balance NUMBER (8,2);   --remaining balance after pay
pledgeamt NUMBER (8,2)           --total amt to pay over term

BEGIN
SELECT PLEDGEAMT, PLEDGEDATE, PAYMONTHS INTO pledgeamt, start_date, num_of_payments
FROM DD_PLEDGE
WHERE IDPLEDGE = &IDPLEDGE;
monthly_payment_amt: = pledgeamt/num_of_payments;
date_due: = start_date;
donation_balance: = (pledgeamt - monthly_payment_amt);
payment_num: = 0;
FOR i IN 1..num_of_payments LOOP
payment_num: = payment_num + 1;
date_due: = add_months (date_due, 1);
DBMS_OUTPUT.PUT_LINE ('payment number:'||payment_num||'due_date:'
||date_due||'payment_amount:'||monthly_payment_amount||'balance:'
||to_char(donation_balance,'$9999.99'));
donation_balance: = donation_balance - monthly_payment_amt;
EXIT WHEN donation_balance < 0; --LOOP until condition is met
END LOOP;
END;