-- (no param) 
DELIMITER $$
CREATE PROCEDURE getInstructor()
  SELECT * FROM instructor;
$$
DELIMITER ;

-- to call the stored procedure, use the keyword CALL followed by the procedure name
CALL getInstructor();

-- -------------------

-- (IN param) 
-- make sure there exists an "instructor" table
DELIMITER $$
CREATE PROCEDURE raiseSalary (IN oldSalary DECIMAL(8,2),
                              IN newSalary DECIMAL(8,2))
    UPDATE instructor SET salary = newSalary WHERE salary = oldSalary;
$$
DELIMITER ;

-- to run
SET @p0='80000';  
SET @p1='80002';
CALL raiseSalary(@p0, @p1);

-- -------------------

-- (OUT param) 
DELIMITER $$
CREATE PROCEDURE countInstructor (OUT number_instr INT)
   SELECT COUNT(*) INTO number_instr
   FROM instructor;
$$ 
DELIMITER ; 

-- to run
CALL countInstructor(@out_value);  
SELECT @out_value as number_of_instructors;

-- -------------------

-- (INOUT param)
-- create a stored procedure that take INOUT param
DELIMITER $$
CREATE PROCEDURE setCounter (INOUT counter INT,
                             IN inc INT)
BEGIN 
    SET counter = counter + inc; 
END                              
$$
DELIMITER ;

-- to run
SET @p0='0'; SET @p1='1';
CALL setCounter(@p0, @p1);
SELECT @p0 AS My_Counter;

-- -------------------

-- loop
DELIMITER $$
CREATE PROCEDURE loop_counter_increment (INOUT counter INT, IN inc INT)
BEGIN 
   DECLARE i INT DEFAULT 1;
   myloop: LOOP
      SET counter = counter + inc; 
      IF i = 5 THEN  
         LEAVE myloop;
      END IF;
      SET i = i + 1;
   END LOOP myloop;
END;                              
$$
DELIMITER ;

-- to call
SET @p0='20'; 
SET @p1='2'; 
CALL loop_counter_increment(@p0, @p1); 
SELECT @p0 AS counter; 

-- -------------------

DELIMITER $$
CREATE PROCEDURE loop_counter_increment2 (INOUT counter INT, IN inc INT)
BEGIN 
   DECLARE i INT DEFAULT 1;
   myloop: LOOP
      SET counter = counter + inc; 
      IF i = 5 THEN  
         LEAVE myloop;
      ELSE
         SET counter = counter + 2;
      END IF;
      SET i = i + 1;
   END LOOP myloop;
END;                              
$$
DELIMITER ;

-- to run
SET @p0='20'; 
SET @p1='2'; 
CALL loop_counter_increment2(@p0, @p1); 
SELECT @p0 AS counter;

-- -------------------

DELIMITER $$
CREATE PROCEDURE loop_counter_increment3 (INOUT counter INT, IN inc INT)
BEGIN 
   DECLARE i INT DEFAULT 1;
   myloop: LOOP
      SET counter = counter + inc; 
      IF i = 5 THEN  
         LEAVE myloop;
      ELSEIF counter < 30 THEN
         SET counter = counter + 2;
      ELSE
         SET counter = counter + 3;         
      END IF;      
      SET i = i + 1;
   END LOOP myloop;
END;                              
$$
DELIMITER ;

-- to run
SET @p0='20'; 
SET @p1='2'; 
CALL loop_counter_increment3(@p0, @p1); 
SELECT @p0 AS counter;

-- --------------

-- to show all stored procedures
SHOW PROCEDURE STATUS;

-- drop a specific stored procedure (IF EXISTS is optional)
DROP PROCEDURE IF EXISTS getInstructor;

-- from CS phpMyAdmin >> get from Routine >> select the procedure >> export