Project #89776 - Oracle SQL stored procedure/triggers/timestamp

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.

STORED PROCEDURE

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.

TABLES:

Description: Macintosh HD:Users:Aloki:Desktop:class 1.pngDescription: Macintosh HD:Users:Aloki:Desktop:class 2.pngDescription: Macintosh HD:Users:Aloki:Desktop:student 1.pngDescription: Macintosh HD:Users:Aloki:Desktop:student 2.pngDescription: Macintosh HD:Users:Aloki:Desktop:Screen Shot 2015-10-28 at 12.14.18 PM.pngDescription: Macintosh HD:Users:Aloki:Desktop:Screen Shot 2015-10-28 at 12.14.27 PM.png

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.  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.



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.  Errors from the RDBMS should be handled so that the calling application receives the "failure" value. 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 CALLs to each of the stored procedures demonstrating their correct success and failure functioning with SELECT statements that retrieve the data you inserted with each stored procedure and/or the succss/failure code, 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.

 

 

TRIGGERS

 

 

/* 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

format.*/

 

INSERT INTO ap.vendors (vendor_id, vendor_name,vendor_address1,vendor_city,

vendor_state,vendor_zip_code,vendor_phone,

vendor_contact_last_name,vendor_contact_first_name,default_terms_id,

default_account_number)

VALUES (vendor_id_seq.nextval,'Best West Sandwiches','1213 San Felipe',

'Houston','TX','77040','713-522-0303','Salazar','Benjamin',2,589);

 

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. */

 .

 

 

 

TIMESTAMP

 

 

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

Subject Computer
Due By (Pacific Time) 10/31/2015 12:00 am
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy