/******************************************************************************
  3 examples below use non-SELECT statements 
  (i.e. statemments that do not return a result set.
  Example insert, update, delete, DDL create, grant, etc.)
******************************************************************************/
/*====================================================================
  Performing insert (or update / delete)
=====================================================================*/
DROP PROCEDURE proc_1a
/
CREATE PROCEDURE proc_1a()
BEGIN
    INSERT INTO output
    VALUES ('This is a new row');
END
/
CALL proc_1a()
/



/*====================================================================
  Performing update using 2 input parameters
=====================================================================*/
DROP PROCEDURE proc_1b
/
CREATE PROCEDURE proc_1b(p_last VARCHAR(20), p_first VARCHAR(20))
BEGIN
    UPDATE student
       SET fname=p_first
     WHERE lname=p_last;
END
/
CALL proc_1b('Sultan','Samuel')
/



/*====================================================================
  Performing create, insert, update and delete
=====================================================================*/
DROP PROCEDURE proc_1c
/
CREATE PROCEDURE proc_1c( p_id INT )
BEGIN
        DECLARE i INT DEFAULT 1;        
        SET autocommit=0;							/*autocommit=OFF */
        DROP   TABLE IF EXISTS test_table;          /*Example of a DDL*/
        CREATE TABLE test_table 
        (
            id   INT PRIMARY KEY,
            data VARCHAR(30)
        )
        ENGINE=innodb;
        WHILE (i<=10) DO
            INSERT INTO test_table VALUES(i,CONCAT("record ",i));
            SET i=i+1;
        END WHILE;  
        UPDATE test_table                           /*update*/
           SET data=CONCAT("I updated row ",p_id)
         WHERE id = p_id;
        DELETE FROM test_table                      /*delete*/ 
         WHERE id > p_id; 
END;
/
CALL proc_1c(5);
/
SELECT * FROM test_table;