Se trataran diferentes escenarios y en toda la entrada se trabajara la sentencia SELECT en un procedimiento almacenado en MySQL que variara según el numero y el tipo de parámetros que acepte o que entregara. La tabla a utilizarse tiene por nombre empleado_php la cual tiene solamente dos campos varchar: ID(PK 8) y Nombre(50).
- Procedimiento Almacenado sin Parámetros
Este procedimiento solamente recupera con las sentencia select * from empleado_php todos los registros de la tabla, no aplica ningún filtro ni recibe o entrega ningún tipo de parámetros
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `select_sp`$$
CREATE DEFINER=`root`@`localhost`
PROCEDURE `select_sp`()
BEGIN
SELECT * FROM empleado_php;
END$$
DELIMITER ;
Ahora el codigo php que se utilizara para invocar el procedimiento almacenado y en este mismo se mostraran los datos que devuelve el procedimiento almacenado.
<?php
$host="localhost";
$dbname="ejemplo";
$username="root";
$password="";
try {
$conn = new PDO("mysql:host=$host;dbname=$dbname",
$username, $password);
$sql = 'CALL select_sp()';
$q = $conn->query($sql);
$q->setFetchMode(PDO::FETCH_ASSOC); }
catch (PDOException $pe) {
die("Error occurred:" . $pe->getMessage());
}
while ($r = $q->fetch()):
echo "ID del empleado: " .$r['ID'];
echo "Nombre: ". $r['Nombre'];
endwhile;
?>
- Procedimiento Almacenado con Parámetro IN
Este procedimiento almacenado recupera el registro de un empleado de acuerdo a su ID que funcionara como criterio de busqueda, este ID es recibido en el procedimiento almacenado como parametro de entrada
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `selectOne_sp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `selectOne_sp`(
IN id_val VARCHAR(8)
)
BEGIN
SELECT * FROM empleado_php WHERE ID=id_val;
END$$
DELIMITER ;
Ahora el código php que se utilizara para invocar el procedimiento almacenado, desde este código se envía el parámetro que servirá como criterio de búsqueda y en este mismo se mostraran los datos que devuelve el procedimiento almacenado.
<?php
$host="localhost";
$dbname="ejemplo";
$username="root";
$password="";
$idEmpleado = "PHP001";
try {
$conn = new PDO("mysql:host=$host;dbname=$dbname",
$username, $password);
$sql = 'CALL selectOne_sp(:id)';
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $idEmpleado, PDO::PARAM_STR, 100);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$num= $stmt->rowCount();
//while ($r = $stmt->fetch(PDO::FETCH_ASSOC)):
if($num>0){
while ($r = $stmt->fetch()):
echo "ID del empleado: " .$r['ID'];
echo "Nombre: ". $r['Nombre'];
//echo '$' . number_format($r['creditlimit'],2)
endwhile; }
else{
echo "No se encontraron registros con el ID " .$idEmpleado;
}
}
catch (PDOException $pe) {
die("Error occurred:" . $pe->getMessage());
}
?>
- Procedimiento Almacenado con Parámetro OUT
Este procedimiento almacenado no recibe ningún parámetro de entrada y controla un parámetro de salida que entrega el numero total de registros de la tabla empleado_php
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `selectCount_sp`$$
CREATE DEFINER=`root`@`localhost`
PROCEDURE `selectCount_sp`(
OUT totalEmpleados INT
)
BEGIN
SELECT COUNT(Nombre) INTO totalEmpleados FROM empleado_php;
END$$
DELIMITER ;
Ahora el código php que se utilizara para invocar el procedimiento almacenado, para llamadas a procedimientos almacenados que manejan parámetros de salida deben realizarse dos consultas con la conexión a la base de datos obtenida, en una consulta se hará el CALL al procedimiento almacenado y con la segunda consulta se logra recuperar los parámetros output del procedimiento.
<?php
$host="localhost";
$dbname="ejemplo";
$username="root";
$password="";
$idEmpleado = "PHP001";
try {
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$sql = 'CALL selectCount_sp(@total)';
$stmt = $conn->prepare($sql);
$stmt->execute();
$stmt->closeCursor(); //permite limpiar y ejecutar la segunda query
// este codigo es para recuperar un valor
$r = $conn->query('select @total');
$total = $r->fetchColumn();
echo $total;
// este codigo serviria para recuperar mas de un valor output desde un SP.
//$r = $conn->query('select @total')->fetch();
//echo $r['@total'];
}
catch (PDOException $pe) {
die("Error occurred:" . $pe->getMessage());
}
?>
- Procedimiento Almacenado con Parámetros OUT
Este procedimiento entregara mas de un parametro de salida
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `selectOuts_sp`$$
CREATE DEFINER=`root`@`localhost`
PROCEDURE `selectOuts_sp`(
OUT totalEmpleados INT,
OUT avgEmpleados VARCHAR(100))
BEGIN
SELECT COUNT(Nombre) INTO totalEmpleados FROM empleado_php;
SELECT Nombre INTO avgEmpleados FROM empleado_php WHERE ID="PHP001";
END$$
DELIMITER ;
La llamada al procedimiento almacenado debe hacerse con dos query, deben realizarse dos consultas con la conexión a la base de datos obtenida, en una consulta se hará el CALL al procedimiento almacenado y con la segunda consulta se logra recuperar los parámetros output del procedimiento.
<?php
$host="localhost";
$dbname="ejemplo";
$username="root";
$password="";
$idEmpleado = "PHP001";
try {
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$sql = 'CALL selectOuts_sp(@total, @avg)';
$stmt = $conn->prepare($sql);
$stmt->execute();
$stmt->closeCursor(); //permite limpiar y ejecutar la segunda query
/* este codigo es para recuperar un valor
$r = $conn->query('select @total, @avg');
$total = $r->fetchColumn();
echo $total;*/
// este codigo serviria para recuperar mas de un valor output desde un SP.
$r = $conn->query('select @total, @avg')->fetch();
echo $r['@total'];
echo $r['@avg'];
}
catch (PDOException $pe) {
die("Error occurred:" . $pe->getMessage());
}
?>
- Procedimiento Almacenado con Parámetros IN-OUT
Procedimiento tratara los tipos de parámetro de entrada y salida,
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `get_user`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user`(
IN userId INT,
OUT firstName VARCHAR(100)
)
BEGIN
SELECT Nombre
INTO firstName
FROM empleado_php
WHERE ID = userId;
END$$
DELIMITER ;
Para utilizar este procedimiento almacenado se deben realizarse dos consultas con la conexión a la base de datos obtenida, en una consulta se hará el CALL al procedimiento almacenado y con la segunda consulta se logra recuperar los parámetros output del procedimiento.
<?php
$host="localhost";
$dbname="ejemplo";
$username="root";
$password="";
$idEmpleado = "PHP001";
try {
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$sql = 'CALL get_user(:id,@lnombre)';
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $idEmpleado, PDO::PARAM_STR, 100);
$stmt->execute();
$stmt->closeCursor(); //permite limpiar y ejecutar la segunda query
$r = $conn->query("SELECT @lnombre AS nombre")->fetch(PDO::FETCH_ASSOC);
if ($r['nombre']) {
echo sprintf('Nombre del empleado %s es %s', $idEmpleado, $r['nombre']);
}
else
echo sprintf('Nombre del empleado %s no esta especificado', $idEmpleado);
}
catch (PDOException $pe) {
die("Error occurred:" . $pe->getMessage());
}
?>