Question

Note: There is no database to test against

FactoryUsers

WorkerId	Number(4)	Not Null [PK]
WorkerName	Varchar2(20)
CreatedDate	DATE
CreatedBy	Varchar2(20)

Create a stored procedure (SP) called Add_Worker that will add an employee to a factory. This SP has two parameters:

a) A parameter (WorkerId) that the SP can read or write to. When the procedure is called it will contain the PK value that should be used to add the row.

b) A parameter (WorkerName) that the SP can read only, it contains the new worker's username.

c) A parameter (Result) that the SP can write to. If the new record is added successfully, the parameter will be set to the value 'Entered Successfully'. If the add fails the parameter is set to the value 'Unsuccessful'.

Declare an exception named WORKER_NOT_ADDED. Associate the number -20444 with this exception.

Execute an insert statement to add the row to the table. Use the values of the passed parameters on the insert statement. Today's date should be used for the Created_date column. Use 'abcuser' for the CreatedBy column.

If the workerID is added:

a) Set the parameter 'Result' to 'Entered Successfully'

b) Commit the changes

Next, add exception processing to the SP

a) For all exceptions, set the parameter 'Result' to 'Unsuccessful'

b) If the WORKER_NOT_ADDED exception occurs, display the message 'New WorkerId already exists on the table.'

c_ For any other error, display the error number and the message associated with that error number.

Answer 1

The code snippet for the stored procedure Add_Worker is attached below, the code is documented and relevant information is available at the end.

--Drop the procedure if it already exists

DROP PROCEDURE IF EXISTS Add_Worker;

DELIMITER $$

--creating procedure Add_Worker

CREATE PROCEDURE Add_Worker(
    IN WorkerId INT(4), 
    IN WorkerName varchar(20),
    OUT Result varchar(20)
)
BEGIN

    -- error handling
    -- exit if the duplicate key occurs

    DECLARE CONTINUE HANDLER FOR 1062

    BEGIN
        SELECT CONCAT('Duplicate key (',WorkerId,') occurred') AS message;
    SET Result = "Unsucessful";
    END;
    
    -- insert a new row into the Worker Table

    INSERT INTO Worker(WorkerId,WorkerName,CreatedDate,CreatedBy)
    VALUES(WorkerId,WorkerName,NOW(),'abcuser');
    
    -- if insert successful than
    
    IF @ROWCOUNT > 0 THEN
       SET Result = "Entered successfully";
    ELSE
       SET Result = "Unsucessful";
    END IF;
    
END$$

DELIMITER;

In the above code, the following things happen in sequence:

Drop if the stored procedure already exists.

Create procedure Add_Worker

2 parameters two IN and one OUT( WorkerId, WorkerName, Result )

Error handling - If WorkerId already exists then a 1062 error message will be shown and Result will be set to unsuccessful

Insert into Worker table values WorkerId,WorkerName, NOW() which is today's date and 'abcuser' for createdBy

If @ROWCOUNT > 0 meaning if a row is added then set Result = "Entered successfully" else Result = "Unsuccessful"

END