Before you start this lab assignment, you have to run all examples from this module and understand the purpose of each line of code. You have to include the text of your code, not only screenshots. To test your answers the code will be copied from your report and run in SQL Developer. Objectives Learn how to write PL/SQL procedures, and functions 1. (3 points for code and screenshots of testing your code) The following code will concatenate two strings strl and str2 CREATE OR REPLACE PROCEDURE concatenate_strings as stri VARCHAR2(25) := 'bit'; str2 VARCHAR2(25) := 'coin'; result VARCHAR2(50); BEGIN result := stri || str2; dbms_output.put_line('The result is ' || result); dbms output.put_line('Date:' || SYSDATE ||' user:' || SYS_CONTEXT('USERENV','OS_USER')); END; SET SERVEROUTPUT ON; EXECUTE concatenate_strings; SET SERVEROUTPUT ON; EXECUTE concatenate_strings; Script Output * B . Task completed in 0.008 seconds PROCEDURE CONCATENATE_STRINGS compiled anonymous block completed The result is bitcoin Date: 06-JAN-18 user: speltsve Modify this procedure to accept two string parameters. Example of the output for EXECUTE concatenate_strings('Crypto', 'Currency'); The result is CryptoCurrency 26-Jan-18 SET SERVEROUTPUT ON; EXECUTE concatenate_strings('Crypto', 'Currency'); Script Output O S S Task completed in 0.004 seconds PROCEDURE CONCATENATE_STRINGS compiled anonymous block completed The result is CryptoCurrency Date: 06-JAN-18 user: speltsve Provide code and screenshot showing successful execution and results 2. (3 points for code and screenshots of testing your code) Write a function f_concatenate_strings that will do the same as concatenate_strings procedure from #1, but can be called from a select statement. Examples of output: SELECT f_concatenate strings('Crypto', 'Currency') FROM DUAL; Will return CryptoCurrency Date: 06-JAN-18 user: speltsve SELECT {_concatenate_strings('Crypto', 'Currency') FROM DUAL; Script Output * O S Task completed in 0.007 seconds FUNCTION F_CONCATENATE_STRINGS compiled F_CONCATENATE_STRINGS("CRYPTO', 'CURRENCY') CryptoCurrency Date: 06-JAN-18 user: speltsve SELECT f_concatenate_strings(ProductName, to_char(ListPrice, $999.99')) from ProductTable WHERE ProductID=302; First, the table ProductTable, then product with ID 302 will be located. Product name of that product will be concatenated with the price and current date. Will return Wind-Up Water Swimmers $2.00 06-JAN-18 user: speltsve KELECT (_concatenate_strings (ProductNane, to_char(listPrice, '$999.99')) fron Product Tal Script Output * Query... X H ER SQL All Rows Fetched: 1 in 0.012 seconds F_CONCATENATE STRINGS(PRODUCTNAME,TO_CHAR(LISTPRICE, '$999.99)) 1 Wind-Up Water Swimmers $2.00 Date: 06-JAN-18 user: speltave Provide code and screenshot showing successful execution and results. You have to provide at least two test cases. 3. (4 points for code and screenshots) Create a procedure that accepts product ID as a parameter and returns the name of the product from ProductTable table. Add exception handling to catch if product ID is not in the table. SET SERVEROUTPUT ON; execute p_product_information (302); execute p_product_information (299):| execute p_product_information(0); Script Output * Query Result x O S Task completed in 0.008 seconds anonymous block completed Product name for product ID 302 is Vind-Up Water Swimmers anonymous block completed Product name for product Id 299 is Chest anonymous block completed No Data found for SELECT on product ID 0 Use Product Table created earlier in the module. Provide code and screenshot showing successful execution and results. You have to provide at least two test cases. Before you start this lab assignment, you have to run all examples from this module and understand the purpose of each line of code. You have to include the text of your code, not only screenshots. To test your answers the code will be copied from your report and run in SQL Developer. Objectives Learn how to write PL/SQL procedures, and functions 1. (3 points for code and screenshots of testing your code) The following code will concatenate two strings strl and str2 CREATE OR REPLACE PROCEDURE concatenate_strings as stri VARCHAR2(25) := 'bit'; str2 VARCHAR2(25) := 'coin'; result VARCHAR2(50); BEGIN result := stri || str2; dbms_output.put_line('The result is ' || result); dbms output.put_line('Date:' || SYSDATE ||' user:' || SYS_CONTEXT('USERENV','OS_USER')); END; SET SERVEROUTPUT ON; EXECUTE concatenate_strings; SET SERVEROUTPUT ON; EXECUTE concatenate_strings; Script Output * B . Task completed in 0.008 seconds PROCEDURE CONCATENATE_STRINGS compiled anonymous block completed The result is bitcoin Date: 06-JAN-18 user: speltsve Modify this procedure to accept two string parameters. Example of the output for EXECUTE concatenate_strings('Crypto', 'Currency'); The result is CryptoCurrency 26-Jan-18 SET SERVEROUTPUT ON; EXECUTE concatenate_strings('Crypto', 'Currency'); Script Output O S S Task completed in 0.004 seconds PROCEDURE CONCATENATE_STRINGS compiled anonymous block completed The result is CryptoCurrency Date: 06-JAN-18 user: speltsve Provide code and screenshot showing successful execution and results 2. (3 points for code and screenshots of testing your code) Write a function f_concatenate_strings that will do the same as concatenate_strings procedure from #1, but can be called from a select statement. Examples of output: SELECT f_concatenate strings('Crypto', 'Currency') FROM DUAL; Will return CryptoCurrency Date: 06-JAN-18 user: speltsve SELECT {_concatenate_strings('Crypto', 'Currency') FROM DUAL; Script Output * O S Task completed in 0.007 seconds FUNCTION F_CONCATENATE_STRINGS compiled F_CONCATENATE_STRINGS("CRYPTO', 'CURRENCY') CryptoCurrency Date: 06-JAN-18 user: speltsve SELECT f_concatenate_strings(ProductName, to_char(ListPrice, $999.99')) from ProductTable WHERE ProductID=302; First, the table ProductTable, then product with ID 302 will be located. Product name of that product will be concatenated with the price and current date. Will return Wind-Up Water Swimmers $2.00 06-JAN-18 user: speltsve KELECT (_concatenate_strings (ProductNane, to_char(listPrice, '$999.99')) fron Product Tal Script Output * Query... X H ER SQL All Rows Fetched: 1 in 0.012 seconds F_CONCATENATE STRINGS(PRODUCTNAME,TO_CHAR(LISTPRICE, '$999.99)) 1 Wind-Up Water Swimmers $2.00 Date: 06-JAN-18 user: speltave Provide code and screenshot showing successful execution and results. You have to provide at least two test cases. 3. (4 points for code and screenshots) Create a procedure that accepts product ID as a parameter and returns the name of the product from ProductTable table. Add exception handling to catch if product ID is not in the table. SET SERVEROUTPUT ON; execute p_product_information (302); execute p_product_information (299):| execute p_product_information(0); Script Output * Query Result x O S Task completed in 0.008 seconds anonymous block completed Product name for product ID 302 is Vind-Up Water Swimmers anonymous block completed Product name for product Id 299 is Chest anonymous block completed No Data found for SELECT on product ID 0 Use Product Table created earlier in the module. Provide code and screenshot showing successful execution and results. You have to provide at least two test cases