DOCUMENT EACH STEP WITH SCREENSHOTS AND WRITE A 5-10 word description of the screenshot underneath. Label stored procedure, trigger, and timestamp sections separately for organization purposes.
Using the student, class, and student_to_class tables you created for Ex Two-01, create a stored procedure named insert_class. The stored procedure's functionality is that it accepts as inputs values for all of the required and optional fields of the class table and attempts an insert of the values into the class table. In addition, if a value of end_date is not passed to the stored procedure by the calling application, the stored procedure should compute an end_date value that is two weeks later than the start_date and insert this computed value for the end_date.The stored procedure should return a value to the calling application that signifies either success or failure of the insert. Errors from the RDBMS should be handled so that the calling application receives the "failure" value.
Create a stored procedure named insert_student_w_class. The stored procedure's functionality is that is accepts as inputs values for all of the required and optional fields of the student table, plus all of the required fields of the student_to_class table. The procedure then attempts an insert of the values into the student and student_to_class tables. If either insert fails, both inserts should be rolled back.
Now create a stored procedure named insert_student_w_class2, based on the previous stored procedure. Add functionality so that the stored procedure checks the class information passed to it to make sure that such a class already exists in the class table. If the class does not yet exist, the procedure should execute the insert_class procedure to insert it before it attempts the insert into the student_to_class table. This stored procedure should also return a success or failure value to the calling application. In addition, add one data validation procedure of your choosing to your stored procedure.
The deliverable is a Word document with (1) screen shots that document the CREATE scripts for each stored procedure (2) two , and (3) two anonymous blocks that call each stored procedure without using the CALL command and that demonstrate their correct success and failure functioning with SELECT statements that retrieve the data your inserted and/or the success/failure code.
/* The vendors table has a field named vendor_phone that is
varchar2(50) data type. All the phone numbers inserted by
the author's script are in the format (999) 999-9999. Run the
following command to verify.*/
SELECT vendor_phone FROM ap.vendors;
/*There is no infrastructure to make sure that phone numbers entered
by a user or an application are in the format used by convention. For example,
the following insert statement inserts a phone number in a different
INSERT INTO ap.vendors (vendor_id, vendor_name,vendor_address1,vendor_city,
VALUES (vendor_id_seq.nextval,'Best West Sandwiches','1213 San Felipe',
SELECT vendor_phone FROM ap.vendors WHERE vendor_name = 'Best West Sandwiches';
/* Task1. Work alone or with a partner to write a command to implement a trigger that would
reformat phone numbers to the format used by convention throughout the ap.vendors table.
Reformatting could occur just prior to data insertion or just after data insertion. You will not be
able to create a trigger on the class server ap schema. So you will have to create the trigger
in your own schema where you have created a vendors table.
Your documentation should be a Word document with:
(1) a screen shot of your command script for creating the trigger and a confirmation of creating the
trigger in the results area,
(2) a screen shot showing the location of the schema on the class server where the trigger is implemented
or a statement telling me in which schema the trigger is implemented,
(3) a set of commands to test the trigger (not screen shots, because I want to be able to copy and paste
the command to test the trigger) with
(a) an insert of a phone number in the incorrect format followed by
(b) a select statement for the record that was just inserted that then shows the result set with the phone number
in the correct format,
(4) Screen shots of your own test case that you ran to test the trigger and the results./*
/* Some of the logic you may need is as follows. Compare the string to be inserted to a formated string
to see if the format matches. If it does, no action is needed. Determine if the string to be inserted contains
exactly 10 numbers. If it does not, then I would cause the value to be entered to be null (vendor_phone is nullable).
If it does have 10 characters, then parse the string to find the first three numbers.
Parse for the next three numbers. Insert dashes and parentheses as needed. */
/* If you prefer, you may create a similar trigger for a table in your project database. If you want to do this, you should
clear it with Professor Miertschin. */
In the script for Chapter 17 that has line numbers in it, there is an instruction for you to do something at line 205 followed by a question (Q) that starts at line 209. Do this exercise. The deliverable is a Word document with screen shots and captions that document your work, plus an answer to the question posed at line 209.
At the end of the Chapter 17 script, there is a task described that starts at line 276 in the script with line numbers. Complete this task as well. The deliverable will be 14 SELECT commands with results documented by screen shots with captions.
LOOK AT attached TIMESTAMP DATA PDF FOR THIS SECTION
|Due By (Pacific Time)||10/31/2015 12:00 am|
out of 1971 reviews
out of 766 reviews
out of 1164 reviews
out of 721 reviews
out of 1600 reviews
out of 770 reviews
out of 766 reviews
out of 680 reviews