-- File: proc2.sql
-- Creates and demonstrates stored procedures 

-- 1. A procedure with parameters - Give all salesmen a specified amount 

-- Don't respond yet to ';' statement delimiters inside the procedure,
-- save them until the procedure is executed
-- Remember to reset delimiter when finished: DELIMITER ;
DELIMITER //

DROP PROCEDURE IF EXISTS add_comm //

CREATE PROCEDURE add_comm( IN amount DECIMAL )
COMMENT 'Add commission for salesmen'
BEGIN
    UPDATE  emp1
    SET     comm = comm + amount
    WHERE   job = 'SALESMAN';
END //
----------------------------------------------------------------------
-- Verify definition - horizontal format not friendly
SHOW CREATE PROCEDURE add_comm //

SELECT 2 //

-- Verify definition - display fields in vertical format (\G)
SHOW CREATE PROCEDURE add_comm\G

-- Display further info about the procedure. Note wild-card '%'
SHOW PROCEDURE STATUS LIKE 'add%' \G

SELECT 3 //
----------------------------------------------------------------------
-- Now let's use the procedure
-- List the salesmen and their initial commissions
SELECT  ename,
        comm
FROM    emp1
WHERE   job = 'SALESMAN' //

-- now update their commissions with the ADD_COMM procedure
CALL    add_comm(100) //

-- then list the salesmen and their updated commissions
SELECT  ename,
        comm
FROM    emp1
WHERE   job = 'SALESMAN' //

SELECT 4 //
----------------------------------------------------------------------
-- Creates a stored procedure called NEW_EMP 
-- which adds a new recruit to the company
-- Usage: CALL new_emp('name', 'job');

DROP PROCEDURE IF EXISTS new_emp //

CREATE PROCEDURE 
    new_emp( IN name VARCHAR(20), IN job VARCHAR(20) )
COMMENT 'Add new employee to table'
BEGIN
    DECLARE next_num INTEGER(4);
    
    -- Get the highest Employee No
    SELECT  MAX(empno) 
    INTO    next_num
    FROM    emp1;
    -- Increment Employee No
    SET next_num = next_num + 1;
    -- Insert new row
    INSERT INTO emp1 VALUES(
        next_num,
        UPPER(name),
        UPPER(job),
        NULL,
        SYSDATE(),
        0,
        NULL,
        NULL);
END //

-- Verify definition - display fields in vertical format (\G)
SHOW CREATE PROCEDURE new_emp\G

-- All procedures
SHOW PROCEDURE STATUS\G

SELECT 5 //
----------------------------------------------------------------------
-- Let's use the procedure to add a new employee
CALL new_emp('Rookie', 'Clerk') //

-- and see if it worked
SELECT  *
FROM    emp1
WHERE   ename = 'ROOKIE' //

SELECT 6 //
----------------------------------------------------------------------
-- Let's modify new_emp, to output details of new employee
DROP PROCEDURE IF EXISTS new_emp //

CREATE PROCEDURE 
    new_emp( IN name VARCHAR(20), IN job VARCHAR(20) )
COMMENT 'Add new employee to table, display details'
BEGIN
    DECLARE next_num INTEGER(4);
    
    -- Get the highest Employee No
    SELECT  MAX(empno) 
    INTO    next_num
    FROM    emp1;
    -- Increment NEXT_NUM
    SET next_num = next_num + 1;
    -- Insert new row
    INSERT INTO emp1 VALUES(
        next_num,
        UPPER(name),
        UPPER(job),
        NULL,
        SYSDATE(),
        0,
        NULL,
        NULL);
    -- Display new row, using input parameter in WHERE clause
    SELECT  *
    FROM    emp1
    WHERE   ename = name;
END //

----------------------------------------------------------------------
-- Try the new procedure.
CALL new_emp('Starter', 'Trainee') //

-- Resetting delimiter does not work within script file!
-- Type the following (without the --) at the command prompt:
-- DELIMITER ;