Description
Create a table called MyExpenses to hold the expenses by date and in various categories, for example, food, commute and misc.
For example, the table should have data as shown below.
CDATE FOOD COMMUTE MISC
21-Oct-19 30 5 15
22-Oct-19 50 0 25
23-Oct-19 80 10 60
Note: You are free to choose the expense categories (3 categories are shown in the example). You must use Date as the datatype for CDATE and this is the primary key of the table.
Insert some data of your choice into the MyExpenses table.
Example:
insert into MyExpenses values (’21-OCT-19′,30.00,5.00,15.00);
Hint: The standard Oracle date format is DD-MON-YY, as shown above.
a) Write a PLSQL function called getExpensesByDate to calculate the total expenses (expenses for all categories) for a single date, where the date is passed as a parameter.
Example:
Create or Replace Function getExpensesByDate(v_date In Date)Return Number IS
— Complete the function
Now test your function by calling the function as follows:
Select getExpensesByDate(’21-OCT-19′) from Dual;
Note: You can replace the date by one of your choice.
b) Write a PLSQL function called getExpensesBetweenDates() which takes two parameters of type Date and returns the total for all the days between the two dates. See the signature of the function below:
Create or Replace Function getExpensesBetweenDates(v_fromdate In Date, v_toDate In Date)Return Number IS
— Complete this function
Test your function as follows:
Select getExpensesBetweenDates(’21-OCT-19′, ’23-OCT-19′) from Dual;
Run the queries and capture the results in assign1_output.txt, using spool.
What to submit:
a) Your PLSQL code segments.
b) The data in your table (Do a Select * .. and capture it in the spool file)
c) The spool file that shows how you are calling the functions and output.
References:
Date functions
