mikelemus27

DELIMITER $$
CREATE PROCEDURE `hr`.`my_proc_CASE` 
(INOUT no_employees INT, IN salary INT)
BEGIN
CASE
WHEN (salary>10000) 
THEN (SELECT COUNT(job_id) INTO no_employees 
FROM jobs 
WHERE min_salary>10000);
WHEN (salary<10000) 

mikelemus27

CREATE PROCEDURE dorepeat(p1 INT)
       BEGIN
         SET @x = 0;
         REPEAT
           SET @x = @x + 1;
         UNTIL @x > p1 END REPEAT;
       END
       //

mysql> CALL dorepeat(1000)//

mikelemus27

CREATE PROCEDURE SP_COMPARE(a INT, b INT)
BEGIN
    DECLARE message VARCHAR(100) DEFAULT "";
    IF a > b THEN
        SET message = CONCAT("The first parameter is greater than the second parameter.", a, ">", b);
        ELSEIF a < b THEN
            SET message = CONCAT("The first parameter is less than the second parameter.", a, "<", b);
        ELSE
            SET message = CONCAT("Values of both parameters are the same.", a, "=", b);
    END IF;

mikelemus27

select version() as 'mysql version';
#delimiter \\:
drop procedure if exists p1;
create procedure p1()
begin
declare ctr int default 0;
declare i int default 10;
myloop: WHILE (ctr < i) DO 
   #SET cnt = (SELECT COUNT(*) FROM temp_results WHERE result = "true");
    #select @ctr := ctr + 1;#only works for @variables( variales globales)

mikelemus27

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';
drop procedure if exists sp_compare;
CREATE PROCEDURE SP_COMPARE(a INT, b INT)
BEGIN
    DECLARE message VARCHAR(100) DEFAULT ""; 

mikelemus27

SHOW CREATE PROCEDURE <name>
===================================================
mysqldump --user=<user> -p --no-data --routines <database>

===================================================
mysql> SELECT body FROM mysql.proc
WHERE db = 'yourdb' AND name = 'procedurename' ;

*******Note that you must have a grant for select to mysql.proc:
************mysql> GRANT SELECT ON mysql.proc TO 'youruser'@'yourhost' IDENTIFIED BY 'yourpass' ;

mikelemus27

Listing stored procedures using SHOW PROCEDURE STATUS statement
Here is the basic syntax of the SHOW PROCEDURE STATUS statement:

 SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]
The SHOW PROCEDURE STATUS statement shows all characteristic of stored procedures including stored procedure names. It returns stored procedures that you have a privilege to access.

The following statement shows all stored procedure in the current MySQL server:

 SHOW PROCEDURE STATUS;
Here is the partial output:

mikelemus27

you will learn about variables in the stored procedure, how to declare, and use variables. In addition, you will learn about the scopes of variables.

A variable is a named data object whose value can change during the stored procedure execution. You typically use variables in stored procedures to hold immediate results. These variables are local to the stored procedure.

Before using a variable, you must declare it.

Declaring variables
To declare a variable inside a stored procedure, you use the DECLARE  statement as follows:

 DECLARE variable_name datatype(size) [DEFAULT default_value];

mikelemus27

--OK, let's use some parameters:

DROP PROCEDURE IF EXISTS sayHello //
CREATE PROCEDURE sayHello(IN name VARCHAR(20))
  SELECT CONCAT('Hello ', name, '!') AS Greeting;
//

--The 'IN' keyword tells MySQL that is should be expecting an input value for
--the parameter......hunh? Why would a parameter NOT have an input value? You will
--see in a little bit. First, let's see if sayHello works:

mikelemus27

------Creating Stored Procedures in MySQL------



--Make sure you have version 5 of MySQL:

SELECT VERSION();

+-----------+
| VERSION() |