Instructions
Requirements and Specifications
Source Code
/* Ex 6 Stored procedures and Functions */
/* The tasks for this exercise refer to the the tables of the ITAM schema that are owned by JAHERNA42 on the class server. You will create two stored procedures and a function that refer to the tables. You can also create the procedures and function on your cloud database instance connected as the user who owns the ITAM tables, or you should be able to connect as the user you named and created and complete the exercise that way as well. The problwm with the cloud instance is that it does not have as much data as the JAHERNA42 tables.*/
/*From a business perspective, the stored procedures and the function might be used by an application used to manage information about the IT assets owned by ABCCo, some of which are assigned to their employees to use to do their jobs.*/
/* The deliverable for Ex 6 is a Word document with screen shots documenting the creation of each stored procedure or function followed by screen shots verifying its use. Each screenshot should contain your name typed in Oracle SQL Developer in the pane where you develop your code.*/
/* Task 1. This task is to assure you that you have the proper privileges to create procedures in your own schema on the class server that refer to tables owned by JAHERNA42. Create the simple stored procedure shown below with an
output parameter that gets a value from a table of the ITAM schema tables of JAHERNA42. When you recreate the procedure below in your own schema, name the procedure and its output variable differently from the example. Also explain in a commented out statement what the indicated lines of code do. In Task 2, you
document your demonstration of running your stored procedure. */
--type your name here
--Ex 6 Task 1
create or replace procedure testit
(gotcha out jaherna42.it_asset_desc.asset_make%type)
as
ran_val number(10);
smallest number(10);
largest number(10);
begin
--State what the next command does.
select round(max(asset_desc_id)/100000,0) into largest from jaherna42.it_asset_desc;
--State what the next command does.
select min(asset_desc_id) into smallest from jaherna42.it_asset_desc;
--State what the next command does.
select round(dbms_random.value(smallest,largest),0)
into ran_val from dual;
--State what the next command does.
select distinct asset_make into gotcha
from jaherna42.it_asset_desc where asset_desc_id = ran_val;
exception
when others then
gotcha:= 'No make found';
end;
/* Task 2. Now demonstrate running your stored procedure two times to produce two different output values. First, demonstrate that the procedure can find an asset make in JAHERNA42's it_asset_desc table. Then demonstrate that it can return the value specified in the exception block.*/
--type your name here
--Ex 6 Task 2
--Use testit as many times as needed to get output that is a make and
--output that matches the value in the exception block. Capture both output
--values in one screenshot by scrolling in the Dbms Output window.
declare getit varchar2(20) := 'Hello!';
begin
testit(getit);
dbms_output.put_line(getit);
end;
/* Here is an example stored procedure similar to the one you are asked to create for Task 3 (Task 3 follows after the examples of calling this procedure.) It inserts into the invoices table based on the invoices table of the AP schema. The example is based on Figure 15-5 in the Murach book. */
create or replace procedure insert_invoice
(
vendor_id_param invoices.vendor_id%type,
invoice_number_param invoices.invoice_number%type,
invoice_date_param invoices.invoice_date%type,
invoice_total_param invoices.invoice_total%type default null,
payment_total_param invoices.payment_total%type default null,
credit_total_param invoices.credit_total%type default null,
terms_id_param invoices.terms_id%type default null,
invoice_due_date_param invoices.invoice_due_date%type default null,
payment_date_param invoices.payment_date%type default null,
success_param out char
)
as
invoice_id_var invoices.invoice_id%type;
terms_id_var invoices.terms_id%type;
invoice_due_date_var invoices.invoice_date%type;
terms_due_days_var number(8);
begin
select 'S' into success_param from dual;
if invoice_total_param < 0 then
raise VALUE_ERROR;
end if;
select invoice_id_seq.nextval into invoice_id_var from dual;
if terms_id_param IS null then
select default_terms_id into terms_id_var
from vendors where vendor_id = vendor_id_param;
else
terms_id_var := terms_id_param;
end if;
if invoice_due_date_param is null then
select terms_due_days into terms_due_days_var
from terms where terms_id = terms_id_var;
invoice_due_date_var
:= invoice_date_param + terms_due_days_var;
else
invoice_due_date_var := invoice_due_date_param;
end if;
insert into invoices values(invoice_id_var, vendor_id_param, invoice_number_param,
invoice_date_param, invoice_total_param, payment_total_param, credit_total_param, terms_id_var,
invoice_due_date_var, payment_date_param);
exception
when VALUE_ERROR then
select 'VE' into success_param from dual;
when others then
select 'F' into success_param from dual;
end;
drop procedure insert_invoice;
/
/* Next are examples of calling the stored procedure, capturing the value passed back that indicates success or failure of the insert operation to a calling application. The call examples were performed against jdoe22's invoices table that he has in his own schema, not against the AP schema's invoices table. The first call produced a success
code of S displayed in the DBMS_OUTPUT window; the second one produced a success code of F.*/
declare
success_or_fail char;
begin
insert_invoice(34, 'ZXA-081', '30-SEP-16', 14092.59,
null, null, 3, '29-OCT-16', null,success_or_fail);
dbms_output.put_line(success_or_fail);
end;
declare
success_or_fail char;
begin
insert_invoice(34, 'ZXA-081', '30-SEP-16', -14092.59,
null, null, 3, '29-OCT-16', null,success_or_fail);
dbms_output.put_line(success_or_fail);
end;
/
/* Task 3. Create a stored procedure in your own schema on the class server or create it on your cloud instance logged in as either ITAM or the user you named and created there. Name the procedure insert_asset_yourusername. The purpose of the procedure is to insert into jaherna42's (or ITAM's in your cloud) it_asset_desc table, performing a little data validation first. Your procedure must accept as input values for the type of asset (by description), the make of
the asset, the model, the asset_ext attribute information, and a value for an output variable whose value can mean an error state or success. The procedure should then attempt an insert of the input values into the it_asset_desc table
of jaherna42 (or ITAM in your cloud), but only if the combination of asset type, asset_make, asset_model, and asset_ext does not match exactly to an asset description row already in it_asset_desc table. (It serves no business purpose to have duplicate asset descriptions in the it_asset_desc table). The procedure should return a value to the calling application that signifies a successful insert or a failure to insert. Errors from the RDBMS should be handled by an exception block so that the calling application receives the "success" or
"failure" value from the output parameter of the procedure based on the error type. Your procedure should handle for at least one named error plus others. Note that the example stored procedure from the Murach book (preceding Task 3) has a lot of data validation built into it that is not necessary for an insert into the it_asset_desc table. Adjust your code accordingly, but perform necessary data validation as indicated. For Task 3 you document the code of your
procedure and your success with creating it. For Task 4 you document testing it.*/
--Type your name here
--Ex 6 Task 3
create or replace procedure insert_asset_aoakingb
(
passet_desc_id JAHERNA42.it_asset_desc.asset_desc_id%type,
passet_type_desc JAHERNA42.asset_type.asset_type_desc%type,
passet_make JAHERNA42.it_asset_desc.asset_make%type,
passet_model JAHERNA42.it_asset_desc.asset_model%type,
passet_ext JAHERNA42.it_asset_desc.asset_ext%type,
success_param out char,
error_reason out varchar2
)
as
existing_asset_desc_id JAHERNA42.it_asset_desc.asset_desc_id%type;
existing_asset_type_id JAHERNA42.asset_type.asset_type_id%type;
begin
select 'S' into success_param from dual;
-- If the asset_desc_id is null throw value error exception
if passet_desc_id is NULL then
error_reason := 'passet_desc_id is null';
raise VALUE_ERROR;
end if;
-- if the asset_type_description is null throw valueerror
if passet_type_desc is NULL then
error_reason := 'passet_type_desc is null';
raise VALUE_ERROR;
end if;
-- Check if there is an asset_type with the given description
existing_asset_type_id := -1;
SELECT asset_type_id INTO existing_asset_type_id
FROM JAHERNA42.asset_type WHERE asset_type_desc = passet_type_desc;
IF existing_asset_type_id = -1 THEN -- there is no asset type with that description
error_reason := 'existing_asset_type_id is -1';
raise VALUE_ERROR;
END IF;
--check if the params already exist in table
existing_asset_desc_id := 0;
SELECT COUNT(*) INTO existing_asset_desc_id
FROM JAHERNA42.it_asset_desc WHERE asset_desc_id = passet_desc_id
AND asset_make = passet_make AND asset_model = passet_model AND asset_ext = passet_ext;
-- check
IF existing_asset_desc_id < 1 THEN -- we can inssert
INSERT INTO JAHERNA42.IT_ASSET_DESC VALUES
(
passet_desc_id,
existing_asset_type_id,
passet_make,
passet_model,
passet_ext,
NULL,
sysdate
);
SELECT 'S' INTO success_param FROM dual;
ELSE
error_reason := 'existing_asset_desc_id is not -1';
SELECT 'F' INTO success_param FROM dual;
END IF;
exception
when VALUE_ERROR then
select 'VE' into success_param from dual;
when others then
select 'F' into success_param from dual;
end;
/* Task 4. Next provide examples of calling the stored procedure, capturing the value passed back (in the output variable) that indicates success or failure to a calling application. The call examples should demonstrate a success state result and a failure state result.*/
--Type your name here
--Ex 6 Task 4 Command 1
--Call the stored procedure and get a success result
/*Success*/
declare
success_or_fail char;
error_reason varchar2(50);
begin
insert_asset_aoakingb(4286747455, 'Computer', 'Canon', 'Model 1', 'Canon Super Awesome', success_or_fail, error_reason);
dbms_output.put_line(success_or_fail);
dbms_output.put_line(error_reason);
end;
--Type your name here
--Ex 6 Task 4 Command 2
--Verify that the record was inserted into it_asset_desc table.
SELECT * FROM JAHERNA42.it_asset_desc WHERE asset_desc_id = 4586747455;
--Type your name here
--Ex 6 Task 4 Command 3
--Call the stored procedure and get a failure result
/* Failure */
declare
success_or_fail char;
error_reason varchar2(50);
begin
insert_asset_aoakingb(4586757455, 'Wrong Type Desc', 'Canon', 'Model 1', 'Canon Super Awesome', success_or_fail, error_reason);
dbms_output.put_line(success_or_fail);
dbms_output.put_line(error_reason);
end;
/* Here is an example function similar to the one you are asked to create for Task 5. jdoe22 created it to run against the invoices table in his schema that is like the AP.invoices table. The example is loosely based on Figure 15-9 in the Murach book. What is the business purpose of this function; that is, what does it do (as you would explain it to your boss)? You do not have to include the answer to this question in your deliverable document. The question is here to help you think as you complete the exercise.*/
create or replace FUNCTION get_how_many_invoices
(
vendor_id_param number
)
RETURN number
AS
how_many_var number;
begin
select COUNT(*)
into how_many_var
from invoices
where vendor_id = vendor_id_param
AND invoice_total - payment_total - credit_total > 0;
RETURN how_many_var;
end;
/
/*Here is an example of using the the function.*/
select get_how_many_invoices(34) from dual;
/* Task 5. Create a function that returns how many of a particular IT asset description that has one or more instances in inventory (these are the configuration items) that are unassigned for use by an employee and are not disposed of. Not being assigned for use means the configuration item is, basically, in storage or it's a server or something else that gets assigned only for support. Pass two values into the function, both string literals, which are the make and model of the it_asset. After creating the function for Task 5, provide at least two examples of using it for Task 6.*/
--type your name here
/*Ex 6 Task 5 Create a function */
create or replace FUNCTION get_how_many_assets
(
passet_make varchar2,
passet_model varchar2
)
RETURN number
AS
how_many_var number;
begin
select COUNT(*)
into how_many_var
from JAHERNA42.IT_ASSET_INV_SUMMARY
where ASSET_DESC_ID IN (SELECT ASSET_DESC_ID FROM JAHERNA42.IT_ASSET_DESC WHERE ASSET_MAKE = passet_make AND ASSET_MODEL = passet_model)
AND NUM_AVAILABLE = 0;
RETURN how_many_var;
end;
/* Task 6. Demonstrate using the function two times. One use should show the function retrieving a number other than 0 and the other use should show the function finding no instances of the CI in storage. */
--type your name here
/*Ex 6 Task 6 Demonstrate using the function */
SELECT get_how_many_assets('Canon', 'Advance C5560i') FROM dual;
/* Task 7. Now create another function very similar to the function of Task 5. This function should determine how many of a particular asset, based on the input of asset make and model, are assigned for use (and they are realy in use and not disposed of or returned). It will be very similar to the function you created in Task 5. You demonstrate the use of this second function in Task 8.*/
--type your name here
/* Ex 6 Task 7 Create the function */
create or replace FUNCTION get_how_many_assets_in_use
(
passet_make varchar2,
passet_model varchar2
)
RETURN number
AS
how_many_var number;
begin
select COUNT(*)
into how_many_var
from JAHERNA42.IT_ASSET_INV_SUMMARY
where ASSET_DESC_ID IN (SELECT ASSET_DESC_ID FROM JAHERNA42.IT_ASSET_DESC WHERE ASSET_MAKE = passet_make AND ASSET_MODEL = passet_model)
AND NUM_ASSGND_USE > 0;
RETURN how_many_var;
end;
/* Task 8. Demonstrate using the function two times. One use should show the function retrieving a number other than 0 and the other use should show the function finding no instances of the CI in use. */
--type your name here
/* Ex 6 Task 8 Use the function */
SELECT get_how_many_assets_in_use('Canon', 'Advance C5560i') FROM dual;
/* Task 9. Now create a third function that determines how many of a particular asset, based on the input of asset make and model, are assigned for support. It will be very similar to the function you created in Task 7. In Task 10, you
demonstrate the use of the function.*/
--type your name here
/* Ex 6 Task 9 Create the function */
--You can do this!
create or replace FUNCTION get_how_many_assets_in_support
(
passet_make varchar2,
asset_model varchar2
)
RETURN number
AS
how_many_var number;
begin
select COUNT(*)
into how_many_var
from JAHERNA42.IT_ASSET_INV_SUMMARY
where ASSET_DESC_ID IN (SELECT ASSET_DESC_ID FROM JAHERNA42.IT_ASSET_DESC WHERE ASSET_MAKE = passet_make AND ASSET_MODEL = passet_model)
AND NUM_ASSGND_SUPPORT > 0;
RETURN how_many_var;
end;
/* Task 10. Demonstrate using the function two times. One use should show the function retrieving a number other than 0 and the other use should show the function finding no instances of the CI in use. */
--type your name here
/*Ex 6 Task 10 */
create or replace procedure update_asset_summary_aoakingb
(
passet_make JAHERNA42.it_asset_desc.asset_make%type,
passet_model JAHERNA42.it_asset_desc.asset_model%type,
success_param out char
)
as
existing_asset_desc_id JAHERNA42.it_asset_desc.asset_desc_id%type;
count_asset_id NUMBER;
n_available NUMBER;
n_use NUMBER;
n_support NUMBER;
begin
select 'S' into success_param from dual;
-- If the asset_make is null throw value error exception
if passet_make is NULL then
raise VALUE_ERROR;
end if;
-- if the asset_model is null, throw error
if passet_model is NULL then
raise VALUE_ERROR;
end if;
dbms_output.put_line('A');
-- Check if there is an it_asset_desc with the given make and model
existing_asset_desc_id := -1;
SELECT COUNT(*) INTO count_asset_id
FROM JAHERNA42.it_asset_desc WHERE asset_make = passet_make and asset_model = passet_model;
dbms_output.put_line('B');
IF count_asset_id < 1 THEN -- there is no it_asset_desc with that make and model
raise VALUE_ERROR;
END IF;
-- If we reach this line, it is because there exists and it_asset_desc with the given make and model
SELECT ASSET_DESC_ID INTO existing_asset_desc_id
FROM JAHERNA42.it_asset_desc WHERE asset_make = passet_make and asset_model = passet_model;
-- get counts
n_available := get_how_many_assets(passet_make, passet_model);
n_use := get_how_many_assets_in_use(passet_make, passet_model);
n_support := get_how_many_assets_in_support(passet_make, passet_model);
dbms_output.put_line('C');
-- update
UPDATE JAHERNA42.IT_ASSET_INV_SUMMARY SET NUM_AVAILABLE = n_available, NUM_ASSGND_USE = n_use, NUM_ASSGND_SUPPORT = n_support
WHERE ASSET_DESC_ID = existing_asset_desc_id;
dbms_output.put_line('D');
exception
when VALUE_ERROR then
select 'VE' into success_param from dual;
when others then
select 'F' into success_param from dual;
end;
/* Task 12. Demonstrate two uses of the stored procedure from Task 11. One use should capture an output value indicating "success". Follow that with a command that selects the record inserted into it_asset_inv_summary table. The third command should demonstrate use of the procedure that results in a captured output value indicating "failure".*/
--type your name here
/* Task 12 Command 1 Success */
declare
success_or_fail char;
begin
update_asset_summary_aoakingb('Canon', 'Advance C5560i', success_or_fail);
dbms_output.put_line(success_or_fail);
end;
--type your name here
/* Task 12 Command 2 Record Inserted by Success Use */
SELECT *
FROM JAHERNA42.IT_ASSET_INV_SUMMARY
WHERE
ASSET_DESC_ID = (SELECT ASSET_DESC_ID FROM JAHERNA42.IT_ASSET_DESC WHERE ASSET_MAKE = 'Canon' AND ASSET_MODEL = 'Advance C5560i');
--type your name here
/* Task 12 Command 1 Failure */
declare
success_or_fail char;
begin
update_asset_summary_aoakingb('APPLE', 'Iphone 13', success_or_fail);
dbms_output.put_line(success_or_fail);
end;