STORED PROCEDURES IN MYSQL

http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure.

Declarative programming is where you say what you want without having to say how to do it. With procedural programming, you have to specify exact steps to get the result.

ADVANTAGES:-
    Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than uncompiled SQL commands which are sent from application.
    Stored procedure reduces the traffic between application and database server because instead of sending multiple uncompiled lengthy SQL commands statements, the application only has to send the stored procedure's name and get the data back.
    Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developers don't have to program the functions which are already supported in stored procedure in all external applications.
    Stored procedure is secured. Database administrator can grant the access right to application which wants to access stored procedures in database catalog without granting any permission on the underlying database tables.

SIMPLE STORED PROCEDURE:-
    CREATE PROCEDURE getALL()
    BEGIN
    select * from update_commands;
    END//
    DELIMITER ;

PARAMETERS IN STORED PROCEDURES:-
    In MySQL, a parameter has one of three modes IN, OUT and INOUT.
   
IN :-
    send an input parameter only

    DELIMITER //
    CREATE PROCEDURE getAll(IN commandName varchar(50))
    BEGIN
    select * from update_commands where command_name = commandName;
    END//
    DELIMITER;
   
    call getAll('send_me')
   
OUT:-

    send an input parameter AND get back another output parameter.
   
    DELIMITER //
    CREATE PROCEDURE getAll(IN commandName varchar(50), OUT total int)
    BEGIN
    select count(*) into total from update_commands where command_name like orderStatus ;
    END//
    DELIMITER;

    call getAll(1,@set);
    select @set as total


INOUT:-

    send an input parameter AND get back the same as output parameter after modification.
   
    DELIMITER //
    CREATE PROCEDURE getAll(INOUT a varchar(50))
    BEGIN
    select password into a from login where username = a;
    END//
    DELIMITER ;
   
    set @username = 'a';
    call getAll(@username);
    select @username as passsword
   
DECLARING A VARIABLE:-

    DECLARE variable_name datatype(size) DEFAULT default_value;
    e.g. DECLARE total_sale INT DEFAULT 0

VARIABLE SCOPE:-

    A variable has its own scope. If you declare a variable inside a stored procedure, it will be out of scope when the END of stored procedure reached. If you defined a variable inside block BEGIN/END inside a stored procedure it will be out of scope if the END reached. You can declare two variables or more variables with the same name in different scopes; the variable only is effective in its scope.

    A variable with the ‘@’ at the beginning is session variable. It exists until the session end.
   
CONDITIONAL CONTROL:-

    The IF Statement:-
        IF expression THEN commands
            ELSEIF expression THEN commands
            ELSE commands
        END IF;

    The CASE Statement:-
        CASE case_expression
            WHEN when_expression THEN commands
            WHEN when_expression THEN commands
            ELSE commands
        END CASE;
       
    The WHILE Statement:-
        WHILE expression DO
           Statements
        END WHILE
       
    The REPEAT Statement:-
        REPEAT
            Statements; UNTIL expression
        END REPEAT
   
    The LOOP loop, LEAVE and ITERATE Statment:-