Assignment 1: Creating a Procedure
Create a procedure named DDPAY_SP that identities whether a donor currently has an active pledge with monthly payments. A donor ID is the input to the procedure. Using the donor ID.the procedure needs to determine whether the donor has any currently active pledges based on the status field and is on a monthly payment plan. If so.the procedure is to return the Boolean value TRUE. Othenvise.the value FALSE should be returned. Test the procedure with an anonymous block.
Assignment 2: Creating a Procedure
Create a procedure named DDCRPAY_SP that confirms whether a monthly pledge payment is the correct amount. The procedure needs to accept two values as input: a payment amount and a pledge ID. Based on these inputs.the procedure should confirm that the payment is the correct monthly increment amount. based on pledge data in the database. If it isn't.a custom Oracle error using error number 20050 and the message "Incorrect payment amount - planned payment = ??" should be raised. The ??should be replaced by the correct payment amount. The database query in the procedure should be formulated so that no rows are returned if the pledge isn't on a monthly payment plan or the pledge isn't found. If the query returns no rows.the procedure should display the message "No payment information: Test the procedure with the pledge ID 104 and the payment amount 525. Then test with the same pledge ID but the payment amount 520. Finally.test the procedure with a pledge ID for a pledge that doesn't have monthly payments associated with it.
Assignment 3: Creating a Procedure
Create a procedure named DDCKBAL_SP that verifies pledge payment information. The procedure should accept a pledge ID as input and return three values for the specified pledge: pledge amount. payment total to date, and remaining balance. Test the procedure with an anonymous block.
1. Develop and run a CREATE FUNCTION statement to create the Ntaf_PURCH_SP function. The function code needs to tally the number of orders (using an Oracle built-in function) by shopper. Keep in mind that the ORDERPLACED column contains a 1 if an order has been placed.
2. Create a SELECT query by using the NUM_PURCH_SF function on the IDSHOPPER column of the BB_SHOPPER table. Be sure to select only shopper 23.