Mysql Stored procedures

Question

How stored procedures are fast in Mysql .

Where it is suitable to use stored procedures (insert,update,select)

How can we make stored procedure of a complex query returning 100000 rows and how can we get these records on php form.

any example of such stored procedure using select query returning multiple rows ?

Answer 1

Following articles have most of your required answers:

• MySQL Stored Procedures:
• A DIY Approach to Stored Procedures in MySQL
• MySQL 5.0 New Features: Stored Procedures
• Stored Procedures in MySQL 5.0

Answer 2

Hi man, well for example we will work with the following mysql procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `ejemplos`.`queryEmployees` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `queryEmployees`(IN param VARCHAR(1))

BEGIN

IF param='1' THEN

  SELECT * FROM empleados;

END IF;

END $$

DELIMITER ;

And we get the records in php as the following :

<?php
$mysqli = new mysqli('localhost', 'root', '1234', 'ejemplos');

$resultado_consulta = $mysqli->query("CALL queryEmployees('1')");
?>
<table>
<?php

 while ($registro = $resultado_consulta->fetch_object()) 
       {
      ?><tr><td><?php echo $registro->first_name;?> </td></tr><?php
        }

?>
</table>

I hope this can help u...