Database assignmnet 1

SECTION A Create a procedure named DDPAY_SP that identifies wether 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. Otherwise, the value FALSE should be returned. Test the procedure with an anonymous block. Create a procedure named DDCKPAY_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. Base 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 isnt 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 amount. The database query in the procedure should br formulated so that no rows are returned if the pledge isnt on a monthly payment plan or the pledge isnt 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 $25. Then test with the same pledge ID but the payment amount $20. Finally, test the procedure with a pledge ID for a pledge that doesnt have monthly payments associated with it. 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. SECTION B . Overloading Packaged Procedures In this assignment, you create packaged procedures to retrieve shopper information. Brewbeans is adding an application page where customer service agents can retrieve shopper information by using ID or last name. Create a package named SHOP_QUERY_PKG containing overload procedures to perform these lookups. They should return the shoppers name, city, state, phone number, and e-mail address. Test the package twice. First call the procedure with shopper ID 23 and then call it with the last name Ratman. Both test values refer to the same shopper, so they should return the same shopper information. Creating a Package with Only a Specification In this assignment you create a package consisting of only a specification. The Brewbeans lead programmer has noticed that only a few states require internet sales tax, and the rates dont change often. Create a package named TAX_RATE_PKG to hold the following tax rates in packageg variables for reference pv_tax_nc = .035, pv_tax_tx = .05, and pv_tax_tn = .02. Code the variables to prevent the rates from being modified. Use an anonymous block with DBMS_OUTPUT statements to display the value of each packaged variable. Using a One-Time-Only Procedure in a Package The Brewbeans application currently contains a package used in the shopper logon process. However one of the developers wants to be able to reference the time users log on to determine when the session should be timed out and entries rolled back. Modify the LOGIN_PKG package (in assignment07-08.txt file in the Zip file in week 10). Use a one-time-only procedure to populate a packaged variable with the date and time of user logons. Use an anonymous block to verify that the one-time-only procedure works and populates the packaged variable. 50% of mark) DATABASE SQL ASSIGNMENT