MySQL Procedure - Introduction ~ MixBoxlk

A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular computing language, stored in database. A procedure has a name, a parameter list, and SQL statement(s). MySQL 5 introduce stored procedure.

it's just like function. it has parameter list which has input and outputs.

Why we need stored procedures?

1. Fast because of MySQL server takes some advantage of caching, just as prepared statements do.
2. Reduction of network traffic. (Process task in MySQL server)
3. repetitive task that requires checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that's stored on the server.
4. Stored procedures are portable. When you write your stored procedure in SQL, you know that it will run on every platform that MySQL runs on

Difference between SQL and MySQL procedures

MySQL procedures compiled and caching with user. SQL not. SQL procedures compiled and cache by general.

Advantages:

Typically stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database.
MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it into a cache. And MySQL maintains its own stored procedure cache for every single connection.
Stored procedures help reduce the traffic between application and database server because instead of sending multiple lengthy SQL statements, the application has to send only name and parameters of the stored procedure.
Stored procedures are reusable and transparent to any applications. it will run on every platform that MySQL runs on
Stored procedures are secure. The database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permissions on the underlying database tables.

Disadvantages:

If you use many stored procedures, the memory usage of every connection that is using those stored procedures will increase substantially.
It is difficult to debug stored procedures. Only a few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.

Pick a dis-limiter

The delimiter is the character or string of characters which is used to complete an SQL statement. By default we use semicolon (;) as a delimiter.
But this causes problem in stored procedure because a procedure can have many statements, and everyone must end with a semicolon.
So for your delimiter, pick a string which is rarely occur within statement or within procedure.
Here we have used double dollar sign i.e. $$.

We are using SQLYog software as MySQL editor.

SqlYog every database has Stored procs category. (Folder). You can create or alter your procs by right click on that folder.

Simple procedure

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `stagingxpo1`.`test1`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
SELECT * FROM `xpo_users`;
    END$$

DELIMITER ;

To run this procedure,
      call test1() in sql query editor.

Variables in Stored Programs

System variables and user-defined variables can be used in stored programs, just as they can be used outside stored-program context.

Local variables

Local variables are declared within stored procedures and are only valid within the BEGIN…END block where they are declared. Local variables can have any SQL data type.

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `stagingxpo1`.`test2`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
   BEGIN   /* declare local variables */
    DECLARE a INT DEFAULT 10;
    DECLARE b, c INT;    /* using the local variables */
    SET a = a + 100;
    SET b = 2;
    SET c = a + b;
        BEGIN      /* local variable in nested block */
            DECLARE c INT;
            SET c = 5;
            /* local variable c takes precedence over the one of the
            same name declared in the enclosing block. */
            SELECT a, b, c;
        END;
    SELECT a, b, c;
   END$$

DELIMITER ;

Run this procedure Call test2()

result 1:

Result 2 :

User variables

In MySQL stored procedures, user variables are referenced with an ampersand (@) prefixed to the user variable name (for example, @x and @y). The following example shows the use of user variables within the stored procedure :

DELIMITER $$

CREATE
    PROCEDURE `stagingxpo1`.`test3`()
    BEGIN
    SET @x = 15;
    SET @y = 10;
    SELECT @x, @y, @x-@y;
    END$$

DELIMITER ;

Run this procedure CALL test3();

Procedure Parameters

There are four types of procedures.

1. CREATE PROCEDURE sp_name () ...

2. CREATE PROCEDURE sp_name ([IN] param_name type)...

3. CREATE PROCEDURE sp_name ([OUT] param_name type)...

4. CREATE PROCEDURE sp_name ([INOUT] param_name type)...

Parameter IN example

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `stagingxpo1`.`test4`(IN var1 INT)
    BEGIN
    SELECT * FROM xpo_users LIMIT var1;
    END$$

DELIMITER ;

Run procedure : CALL test4(4);

This gives 4 users as output.

Parameter OUT example

DELIMITER $$

USE `stagingxpo1`$$

DROP PROCEDURE IF EXISTS `test5`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test5`(OUT highest_no INT)
BEGIN
    SELECT MAX(`company`) INTO highest_no FROM xpo_users;
    END$$

DELIMITER ;

Run procedure: CALL test5(@M)
This saved output to session variable named @M. To view this output run SELECT @M in sql query browser.

Parameter INOUT example

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `stagingxpo1`.`test6`(INOUT mfgender INT, IN emp_gender CHAR(1))

    BEGIN
    SELECT COUNT(gender) INTO mfgender FROM xpo_users WHERE gender = emp_gender ;
    END$$

DELIMITER ;

Run procedure: test6(@C, 'M')
THis will find count gender = M and saved it to session variable named @C.

Run SELECT @C; to view output.

MySQL : Flow Control Statements

If Statement

DELIMITER $$

USE `stagingxpo1`$$

DROP PROCEDURE IF EXISTS `test7`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test7`(INOUT user_name VARCHAR(16), IN user_id INT)
BEGIN
    DECLARE uname VARCHAR(16);
    SELECT first_name INTO uname
    FROM xpo_users
    WHERE id = user_id;
       IF user_id = "1"
           THEN
           SET user_name = "uname1";
       ELSEIF user_id = "2"
           THEN
           SET user_name = "uname2";
       ELSEIF user_id = "3"
           THEN
           SET user_name = "uname3";
       END IF;
END$$

Run this procedure : CALL test7(@N, 1);
this will save the out put to session variable name @N. to view it run SELECT @N;

Case Statement

The CASE statement is used to create complex conditional construct within stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `stagingxpo1`.`test8`(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)
            THEN (SELECT COUNT(job_id) INTO no_employees
                FROM jobs
                WHERE min_salary<10000);
        ELSE (SELECT COUNT(job_id) INTO no_employees
            FROM jobs WHERE min_salary=10000);
    END CASE;
    END$$

DELIMITER ;

LOOP Statement

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `stagingxpo1`.`test9`(IN num INT)
       BEGIN
    DECLARE X INT;
    SET X = 0;
    loop_label: LOOP
       INSERT INTO number VALUES (RAND());
       SET X = X + 1;
       IF X >= num
           THEN
           LEAVE loop_label;
       END IF;
    END LOOP;

    END$$

DELIMITER ;

CALL my_proc_LOOP(3);

select * from number;

+--------------+
| rnumber      |
+--------------+
| 0.1228974146 |
| 0.2705919913 |
| 0.9842677433 |
+--------------+

REPEAT Statement

The REPEAT statement executes the statement(s) repeatedly as long as the condition is true. The condition is checked every time at the end of the statements.

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `stagingxpo1`.`test10`(IN n INT)
    BEGIN
    SET @sum = 0;
    SET @x = 1;
    REPEAT
        IF MOD(@x, 2) = 0
            THEN
            SET @sum = @sum + @x;
        END IF;
        SET @x = @x + 1;
    UNTIL @x > n
    END REPEAT;
    END$$

DELIMITER ;

to run this procedure CALL test10(5);

To view output SELECT @sum;

+------+
| @sum |
+------+
|    6 |
+------+

WHILE Statement

The WHILE statement executes the statement(s) as long as the condition is true. The condition is checked every time at the beginning of the loop. Each statement is terminated by a semicolon (;)

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `stagingxpo1`.`test11`(IN n INT)
    BEGIN
   SET @sum = 0;
   SET @x = 1;
   WHILE @x<n
       DO
           IF MOD(@x, 2) <> 0 THEN
               SET @sum = @sum + @x;
           END IF;
       SET @x = @x + 1;
   END WHILE;
    END$$

DELIMITER ;

To run this procedure CALL test11(5)

To view output SELECT @sum;
+------+
| @sum |
+------+
|   30 |
+------+

Call procedures in Laravel

DB::statement('call new_user(?, ?, ?, ?)',["myemail@test.com","mypassword","myname","mysurname"]);

Reference : https://www.w3resource.com/mysql/mysql-procedure.php

Monday, January 1, 2018