IN MYSQL Part 1 The following code will concatenate two stri

IN MYSQL...

Part 1

The following code will concatenate two strings str1 and str2

CREATE OR REPLACE PROCEDURE concatenate_strings as

   str1 VARCHAR2(25) := \'begin\';

   str2 VARCHAR2(25) := \'end\';

   result VARCHAR2(50);

   BEGIN   

       result := str1 || \'_\' || str2;

       dbms_output.put_line(\'The result is \' || result);

       dbms_output.put_line(SYSDATE);

END;

/

SET SERVEROUTPUT ON;

EXECUTE concatenate_strings;

Modify this procedure to accept two string parameters. Example of the output for

EXECUTE concatenate_strings(\'big\',\'dog\');

The result is big_dog

26-Jan-16

Provide code and screenshot showing successful execution and results

Part 2

(3 points for code and screenshots of testing your code) Write a 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(\'big\',\'dog\') FROM DUAL;

Will return big_dog 27-JAN-16

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 27-JAN-16

Provide code and screenshot showing successful execution and results. You have to provide at least two test cases.

Part 3

2. (4 points for code and screenshots) Create a procedure that accepts product ID as a parameter and returns the price from ProductTable table. Add exception handling to catch if product ID is not in the table.

Provide code and screenshot showing successful execution and results. You have to provide at least two test cases.

Solution

Create or replace procedure concatenate_strings (

Str1 IN VARCHAR2(25)

Str2 IN VARCHAR2(25)) as

Result VARCHAR2(50);

BEGIN

Result:=str1 || ‘_’ || str2;

Dbms_output.put_line(‘result is’||result);

Dbms_output.put_line(SYSDATE);

END;

/

SET SERVEROUTPUT ON;

EXECUTE concatenate_strings(‘big’,’dog’);

Procedure to accept two string parameters.

Example o/p:

EXECUTE concatenate_strings(‘big’,’dog’);

Result: big_dog 26-Jan-16

SELECT concatenate_strings(ProductName,to_char(ListPrice,’$999.99’))

For ProductTable WHERE ProductID=302;

output:Wind-up Water Swimmers_$2.00 27-JAN-16

Create or replace procedure concatenate_strings (

Str1 IN VARCHAR2(25)

Str2 IN VARCHAR2(25)) as

Result VARCHAR2(50);

BEGIN

Result:=str1 || ‘_’ || str2;

Dbms_output.put_line(‘result is’||result);

Dbms_output.put_line(SYSDATE);

END;