Stored Procedure in MySQL

 

Select

        DELIMITER //

            CREATE PROCEDURE GetAllPicture()

            BEGIN

                     SELECT *  FROM image_infos;

             END //

        DELIMITER ;

         call  GetAllPicture();

Stored Procedures Parameters in MySQL

            In MySQL, a parameter has one of three modes IN, OUT and INOUT

           IN this is the default mode. IN indicates that a parameter can be passed into stored procedures but any 

           modification inside stored procedure does not change parameter.        

         

          OUT this mode indicates that stored procedure can change this parameter and pass back to the calling 

          program.

          INOUT obviously this mode is combined of IN and OUT mode; you can pass parameter into  

          storedprocedure and get it back with the new value from calling program.

 IN

              DELIMITER //

               CREATE PROCEDURE GetAllPictureById(IN user_id INT)
               BEGIN
                         SELECT * FROM image_infos WHERE  id = user_id;
               END //
               DELIMITER ;

OUT

             DELIMITER //

               CREATE PROCEDURE GetAllPictureById(IN user_id INT,OUT total_user INT)
               BEGIN
                         SELECT COUNT(*) AS total_user FROM image_infos WHERE  id = user_id;
               END //
               DELIMITER ;