Write a PL-SQL procedure as detailed below. Do not forget to SET SERVEROUTPUT ON to see the results.

This procedure is named yourlastname_charges. It uses the patients table that you have previously constructed with the following structure and data. If you do not have the table and data create your table now and populate the data as shown below:

PATID CHAR(3)

PATFNAME VARCHAR2(20)

PATLNAME VARCHAR2(20)

ADMITDATE DATE

DISCHDATE DATE

DEPT CHAR(2)

CHARGES NUMBER(10,2)

100 John Gullett 10-JUN-16 11-JUN-16 12 22545.87

101 Mary Upton 05-DEC-16 08-DEC-16 10 13139.69

102 Marie Pappenheimer 12-JAN-16 15-JAN-16 11 31521.54

103 Jason Manning 07-NOV-15 12-NOV-15 13 38567.23

104 Margaret Whistler 02-FEB-15 06-FEB-15 12 22398.21

105 David Mayaka 17-OCT-15 21-OCT-15 10 94298.39

106 Gary Lewis 21-JAN-16 23-JAN-16 13 63876.29

107 Kevin Hoffer 26-SEP-16 29-SEP-16 11 31876.22

108 Lindsey Prescott 12-AUG-16 15-AUG-16 10 52952.87

109 Helen Andersson 09-DEC-15 12-DEC-15 13 53456.21

110 Joyce Silverman 20-FEB-15 25-FEB-15 11 94829.91

111 Mala Srinivasan 06-FEB-15 10-FEB-15 12 65923.72

SELECT AVG(charges)

FROM patients

WHERE dept = 12 AND

Dischadate - Admitdate >= 2;

The above SQL statement, as you can infer, calculates the average for department 12 and for those patients where the duration of stay exceeds or equal to 2. The purpose of this procedure is to generalize this for users for who will supply the dept and the duration of stay. The procedure takes two parameters: duration days and department. It then calculates the average charges for the target department when the duration days equals or exceeds the number of days specified. When there are no admissions with these specified criteria, it prints out a message indicating that there are no admissions matching the criteria.

Note: Ensure that the parameter and variable types use data types referring back to the data types of the underlying table columns whenever possible. Use the naming conventions that we have used in class: p_ prefix for all parameters and v_ prefix for all variables. Submit a printout that includes your PL-SQL code, your procedure call and the output produced. Do not forget to SET SERVEROUTPUT ON to see the results.