Store Procedure que devuelva una tabla

[ricardo_03]

Hola a todos. !!

Estoy queriendo hacer un store procedure en mysql que me devuelva una tabla o cursor. Quisiera saber si esto es posible. Tengo instalado MySql 5.01. Gracias

[gnzsoloyo]

En general no hay problema. Para eso sólo se necesita que la última sentencia del SP sea un SELECT.

La pregunta es de qué forma recuperas esa tabla, porque los métodos de manejo de la salida varían con el lenguaje de programación: Ten en cuenta que el resultado no es una tabla conectada actualizable, ya que no estás realizando una consulta normal sino un SP.

[ricardo_03]

Gracias por responder.

Estoy usando php para recibir ese resultado. No estoy seguro si va a recibir el resultado, voy a probar.

[cribelo]

Hola, yo necesito hacer algo parecido, pero no me funciona.
Tengo un store procedure que recibe una cadena que seria la cadena where
osea
CREATE PROCEDURE `getPersonasConParametros`(IN cadenaWhere varchar(1000), IN cadenaLimit varchar(20))

Ahora como hago para concatenar estas variables a la sentencia select y devolverlo para despues recorrerlo con php y mostrar las personas.

Si no tiene parametros anda perfecto, osea
Select * From personas
END;

anda joya,pero no se como hacer esto de concatenar la cadena y que lo devuelba, grcias

[gnzsoloyo]

Para eso debes crear, en el SP, una sentencia preparada con los parámetos que estás pasando.
En el PHP debes usar la interfaz MySQLI, y no la MySQL, porque con mysql_query() no se puede recorrer una tabla devuelta por un stored proecedure.

[cribelo]

Hola, intente hacerlo andar con un statement, algo asi
DROP PROCEDURE IF EXISTS `getpersonas`;
CREATE PROCEDURE `getPersonas`(IN cadenaWhere varchar(1000), IN cadenaLimit varchar(20))
SET @ss='select * FROM personas '+cadenaWhere;
DECLARE stmt nvarchar(1000);
PREPARE stmt FROM @ss;
EXECUTE stmt;


ahora en php lo tomo como siempre, con mysqli, con mysqli_query, y lo recorro con fetch, pero no me funciona, que estoy haciendo mal, lo he echo sin statement, osea con un select comun al final del store, pero no con execute y demas.
que diferencia tiene un statement de un SP, me podes explicar, grcias, hace rato que ya quiero soloconar esto..., grciass

[gnzsoloyo]

No estás usando SQL Server ni Oracle. Tienes que fijarte en el manual de referencia para poder usar correctamente las funciones.
El signo "+" no se usa en MySQL para concatenar textos, sino la función CONCAT(), que a diferencia de Oracle, no encadena dos cadenas solamente, sino más.
Además, NVARCHAR no es un tipo de dato de MySQL, como tampoco lo es VARCHAR2.

Probemos:

Código MySQL:

DELIMITER $$
DROP PROCEDURE IF EXISTS `getpersonas`$$
CREATE PROCEDURE `getPersonas`(
    IN cadenaWhere varchar(1000))
BEGIN
    SET @ss=CONCAT('select * FROM personas ', cadenaWhere);
    DECLARE stmt varchar(1000);
    PREPARE stmt FROM @ss;
    EXECUTE stmt;
    DALLOCATE PREPARE stmt;
END$$
DELIMITER ;
 

Nota: El script así escrito está preparado para ser cargado con el MySQL Query Browser...

[cribelo]

Hola, gracias por la ayuda, sigo intentando, lo copie en el q browser tal como me lo distre y le doy al boton step y me dice

Script line: 3 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* IN cadenaWhere VARCHAR(1000))
BEGIN
* * SET @ss=CONCAT('select * FROM per' at line 2
Sabes que pasa? a vos te lo corrio bien?? grciasss de nuevo

[gnzsoloyo]

Se debe ejecutar como script, no en el área de ejecución directa de SQL.



Y no se carga con STEP sino con EXECUTE. Los SP no son debuggeables.

[cribelo]

No funciona chee, uso xampp,
Servidor: localhost via TCP/IP
Versión del servidor: 5.1.41
Versión del protocolo: 10
Usuario: root@localhost

a vos te funciono? o no lo probaste, lo probe por codigo php con $q="DELIMITER $$
DROP PROCEDURE IF EXISTS `getpersonas`$$
CREATE PROCEDURE `getpersonas`(IN cadenaWhere VARCHAR(1000))
BEGIN
* * SET @ss=CONCAT('select * FROM personas ', cadenaWhere);
* * DECLARE stmt VARCHAR(1000);
* * PREPARE stmt FROM @ss;
* * EXECUTE stmt;
* * DALLOCATE PREPARE stmt;
END$$
DELIMITER ;";
$rrr=mysqli_query($q);


lo probe en el mysql brower en new result set, en script etc, sigue con el mismo error
a vos te andubo?? para saber si es my servidor o es el script, me va a volver loco esto.

[gnzsoloyo]

- Los DECLARE deben estar al principio, cuando se usan sentencias preparadas.
- Los SP, para cargarlos programáticamente (desde un lenguaje de programación) sólo deben contener el cuerpo del código:

Código PHP:

  // $db es el result de la conexión.
// $query es una variable string
// $res es el result de ejecución
// \n permite agregar un salto de linea en la cadena almacenada.

$query = "DROP PROCEDURE IF EXISTS `getpersonas`";
$res = mysql_query($query, $db);
$query = "CREATE PROCEDURE `getPersonas`( IN cadenaWhere VARCHAR(1000))\n";
$query. = "BEGIN\n";
$query. = "    DECLARE stmt VARCHAR(1000);\n";
$query. = "    SET @ss=CONCAT('select * FROM personas ', cadenaWhere);\n";
$query. = "    PREPARE stmt FROM @ss;\n";
$query. = "    EXECUTE stmt;\n";
$query. = "    DEALLOCATE PREPARE stmt;\n";
$query. = "END";
$res = mysql_query($query, $db);  
   

- Recordar que cuando un SP se carga, sigue existiendo todo el tiempo hasta borrarlo, por lo que no es necesario volverlo a cargar si no ha sido modificado.

[cribelo]

Groso groso, ahora me anduvo todo ok, mil gracias, lo que si ejecutandolo desde php me funciono, desde el query browser no, pero es cuestion de intentar y buscar donde esta el error.
Ahora lo que me pasa es q en otra consulta INSERT yo tomo el mysqli_insert_id
y lei por ahi que no se puede hacer esto con sP,
osea pase el insert a store procedure y no puedo recoger el last insert id, pense hacerlo asi, pero no se como recojerlo desde php
DELIMITER $$
DROP PROCEDURE IF EXISTS `insertarPersona`$$
CREATE PROCEDURE `insertarPersona`(
IN unNombre VARCHAR(40)
OUT ultid INT
)
BEGIN
INSERT INTO personas (nombre)
VALUES
(unNombre);
SET ultid=LAST_INSERT_ID();
END$$
DELIMITER;

he googleado pero no encunetro como tomar esto con php, desde ya mil grcias

[gnzsoloyo]

En lugar de asignarlo a una variable, haz que te lo devuelva como tabla:

Código MySQL:

DELIMITER $$
DROP PROCEDURE IF EXISTS `insertarPersona`$$
CREATE PROCEDURE `insertarPersona`(IN unNombre VARCHAR(40))
BEGIN
    INSERT INTO personas (nombre)
    VALUES(unNombre);
    SELECT LAST_INSERT_ID() ULTIMO_ID;
END$$
DELIMITER;
 

Rústico, pero funciona. Lo único que debes hacer es leer el campo "ULTIMO_ID" de la tabla devuelta.

[cribelo]

Estas seguro que es asi? porque en la linea 7 no hay ninguna asignacion no nada, osea eso devuelve una tabla?
y la tomo ?
$result=mysqli_query($query);
$row=mysqli_fetch_array($result)
echo $row["ULTIMO_ID"];

Asi no me funciona, lo que no entiendo es que qeures decir con devolver una tabla, seria un arrray con os resultados, osea como me duevuelve siempre la consulta mysql_fetch_array?
Bueno grciass!

[gnzsoloyo]

Asi no me funciona, lo que no entiendo es que qeures decir con devolver una tabla, seria un arrray con os resultados, osea como me duevuelve siempre la consulta mysql_fetch_array?
Bueno grciass!

Dialéctica: Esto es bases de datos, nunca hablamos de arrays, porque en MySQL no existen los arrays. Si en PHP eso es un array de arrays, es medio irrelevante. Conceptualmente nosotros lo pensamos como tablas...
Sé positivamente que un SP en esas condiciones devuelve una tabla, porque lo uso en consultas de funciones PHP y en funciones de VB.Net, y porque ejecutado en MySQL Workbench, obtengo como resultado una tabla. El cómo lo interprete el PHP a nivel de programación es tema d PHP.
Lo importante es que si la sentencia fue correctamente ejecutada en MySQL, eso debe devolver una tabla de resultados con una sola columna y un solo registro:

Código MySQL:

mysql> SELECT LAST_INSERT_ID() ULTIMO_ID;
+-----------+
| ULTIMO_ID |
+-----------+
|        23 |
+-----------+
1 row in set (0.00 sec)
 

[cribelo]

Iniciado por gnzsoloyo

Dialéctica: Esto es bases de datos, nunca hablamos de arrays, porque en MySQL no existen los arrays. Si en PHP eso es un array de arrays, es medio irrelevante. Conceptualmente nosotros lo pensamos como tablas...
Sé positivamente que un SP en esas condiciones devuelve una tabla, porque lo uso en consultas de funciones PHP y en funciones de VB.Net, y porque ejecutado en MySQL Workbench, obtengo como resultado una tabla. El cómo lo interprete el PHP a nivel de programación es tema d PHP.
Lo importante es que si la sentencia fue correctamente ejecutada en MySQL, eso debe devolver una tabla de resultados con una sola columna y un solo registro:

Código MySQL:

mysql> SELECT LAST_INSERT_ID() ULTIMO_ID;
+-----------+
| ULTIMO_ID |
+-----------+
|        23 |
+-----------+
1 row in set (0.00 sec)
 

yo de nuevo!!! aja


Me funciono, pero tengo otro problema, que despues de insertar la persona tengo que insertar el estado (no importa explicar que es el estado de la persona)
Y quiero hacerlo directamente en el store procedure, y no por fuera con php,
algo asi

DELIMITER $$
DROP PROCEDURE IF EXISTS `personas`.`insertarPersona`$$
CREATE PROCEDURE `insertarPersona`(IN unnombre VARCHAR(50),IN unapellido VARCHAR(50))
BEGIN
DECLARE a INT;
DECLARE ultimoId INT;
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO personas.personas (nombre,apellido)
VALUES
(unnombre,unapellido);
SET ultimoId=(SELECT LAST_INSERT_ID() as ULTIMO_ID FROM personas.personas);
INSERT INTO seguimientosPersonas (idPersona, idEstado, idUsuario,hora,fecha) VALUES (ultimoId, 1,unIdUsuario,CURRENT_TIMESTAMP,CURRENT_DATE());
SET a=(SELECT @@error_count);
IF a=0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END$$
DELIMITER;


El tema es que me da error, hay alguna forma de que el error muestre algo mas detallado? porque solo me dice MysqlError, no hay alguna forma de imprimir el mensaje de error y verlo?
porque el store en el mysql browser se corre bien y se genera bien, pero cuando lo ejecuto me da el error.
Grcias gnzsoloyo!!

[gnzsoloyo]

En realidad, si lo que quieres es establecer si la acción de INSERT de la segunda instancia se produjo o no, es muchísimo más simple verificar si efectivamente se insertó algo, y no si se produjo un error. Esto se hace con ROW_COUNT(), que devuelve un valor que expresa la cantidad de registros efectivamente afectados por la última sentencia, sea INSERT, UPDATE o DELETE.

Código MySQL:

DELIMITER $$
DROP PROCEDURE IF EXISTS `personas`.`insertarPersona`$$
CREATE PROCEDURE `insertarPersona`(IN unnombre VARCHAR(50),IN unapellido VARCHAR(50))
BEGIN
    DECLARE a INT;
    DECLARE ultimoId INT;
    SET AUTOCOMMIT=0;
    START TRANSACTION;
    INSERT INTO personas.personas (nombre,apellido)
    VALUES(unnombre,unapellido);
    SET ultimoId = LAST_INSERT_ID();
    INSERT INTO seguimientosPersonas(idPersona, idEstado, idUsuario,hora,fecha)
    VALUES (ultimoId, 1,unIdUsuario,CURRENT_TIMESTAMP,CURRENT_DATE());
    SET a= ROW_COUNT();
    IF a > 0 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END$$
DELIMITER;
 

[cribelo]

Bueno hice lo que me dijiste, mil graciass!! Grciassss!!!

[gnzsoloyo]