Create a procedure named DDCKPAY_SP that confirms whether a monthly pledge payme

ID: 3821247 • Letter: C

Question

Create a procedure named DDCKPAY_SP that confirms whether a monthly pledge payment is thecorrect amount. The procedure needs to accept two values as input: a payment amount and apledge ID. Based on these inputs, the procedure should confirm that the payment is the correctmonthly increment amount, based on pledge data in the database. If it isn’t, a custom Oracle errorusing error number 20050 and the message “Incorrect payment amount - planned payment = ??”should be raised. The ?? should be replaced by the correct payment amount. The databasequery in the procedure should be formulated so that no rows are returned if the pledge isn’t ona monthly payment plan or the pledge isn’t found. If the query returns no rows, the procedure shoulddisplay the message “No payment information.” Test the procedure with the pledge ID 104 and thepayment amount $25 . Then test with the same pledge ID but the payment amount $20 . Finally, testthe procedure with a pledge ID for a pledge that doesn’t have monthly payments associated with it.

// USING PROCEDURE IN PL/SQL LANGUAGE

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

Hi,

Please find below the required stored procedure-

Create or Replace procedure Calculate1(pledgor_id Number,payment_amount Number) as
declare
v_pledgor_id number;
v_payment_amount number;
begin
select Payamt into v_payment_amount from DD_Payment where idPledge =pledgor_id;
if(payment_amount!=v_payment_amount) then
RAISE_APPLICATION_ERROR(20050,'Incorrect payment amount - planned payment = ',v_payment_amount, 'should be raised');
else
dbms_output.put_line('This is a valid payment amount');

end if;
exception
when NO_DATA_FOUND then
dbms_output.put_line('No payment information.');
end;

Navigate

Create a procedure named CalcGrade that receives an integer value between 0 and

Create a procedure named DDPAY_SP that identifies whether a donor currently has

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

Create a procedure named DDCKPAY_SP that confirms whether a monthly pledge payme

Question

Explanation / Answer

Related Questions

Navigate