Use SQL Developer to complete this and future course assignments. Make sure your access as the user CIS_436 is permitted. If possible, please save a connection to enable the use of the SQL Developer software every time you need to practice. It is recommended that you use Notepad++ to type your PL/SQL code. Save the file first using the file extension .sql and then start working on your code. That way, you can take advantage of the PL/SQL syntax color-coding feature.

Before you begin, be sure to review the following resources:

• Oracle 11g PL/SQL programming. (2nd ed.).
  • Chapter 3: Handling Data in PL/SQL Blocks, pp.85-116.
  • Chapter 4: Cursors and Exception Handling, pp.125-164.
• Chapter 3: Handling Data in PL/SQL Blocks
• Chapter 4: Cursors and Exemption Handling

Instructions

In this assignment, you will work with the basic PL/SQL block structures such as anonymous block. You will explore the different sections of a block by writing code for different exercises. For your convenience, you can download a copy of the assignment: Week 2 Performance Assessment 1: Handling Data in PL/SQL

Separate your programs for each question below using a comment line to distinguish them from one another.

Assignment Scenario

At Brewbeans' headquarters, an application page is being developed for employees to enter a basket number and view shipping information for the order that includes the date, shipper, and shipping number. An IDSTAGE value of five (5) in the BB_BASKETSTATUS table indicates that the order has been shipped.

6. Create a PL/SQL block using scalar variables to hold the data retrieved from the database.
7. Create another PL/SQL block using record variable to retrieve the same result.
8. Create a PL/SQL block that retrieves and displays information for a specific project based on Project ID. Display the following on a single row of output: project ID, project name, the number of pledges made, total dollars pledged, and the average pledge amount.
9. Create a PL/SQL block to handle adding a new project. Create and use a sequence named DD_PROJID_SEQ to handle generating and populating the project ID. The first number issued by this sequence should be 530, and no caching should be used. Use a record variable to handle the data to be added. Data for the new row should be the following: project name = HK Animal Shelter Extension, start = 9/1/2016, end = 10/31/2016, and fundraising goal = $65,000. Any columns not addressed in the data list are currently unknown.
10. Create a PL/SQL block to retrieve and display data for all pledges made in a specified month. One row of output should be displayed for each pledge. Include the following in each row of output:
    • Pledge ID, donor ID, and pledge amount
    • If the pledge is being paid in a lump sum, display Lump Sum.
    • If the pledge is being paid in monthly payments, display Monthly - # (with the # representing the number of months for payment).
    • The list should be sorted to display all lump sum pledges first.
11. Create a PL/SQL block to retrieve and display information for a specific pledge. Display the pledge ID, donor ID, pledge amount, total paid so far, and the difference between the pledged amount and total paid amount.
12. Create a PL/SQL block to modify the fundraising goal amount for a specific project. Also, display the following information for the project being modified: project name, start date, previous fundraising goal amount, and new fundraising goal amount.