7

Procedimientos de almacenados [STORED PROCEDURES]

Noté que muchos compañeros querían ahondar en este tema, la verdad es que yo vengo manejando base de datos ya más de un año y es mi pan de cada día. Y eh tocado este y otros temas, pero en SQL Server, y aunque son muy similares tienen sus diferencias.

Bueno antes que nada ya se debe tener en cuenta que es un DML y un DDL. Los procedures, triggers, vistas, entre otros pertenecen al *DDL y los comandos propios de un DDL son:

  • CREATE
  • ALTER
  • DROP
  • Entre otros

Teniendo esto en mente, es fácil deducir cual va a ser el comando a usar para crear un PROCEDURE.

Y cito la documentación de MySQL:
MySQL Documentation Stored Procedures

Bien lo primero a escribir es la creación del Procedure Ej:

CREATEPROCEDURESP_INS_AUTHORS

Donde CREATE PROCEDURE es la sentencia de creación, y SP_INS_AUTHORS es un nombre cualquiera, pero es buena practica escribir las iniciales de a que tipo de objeto hace referencia SP [STORED PROCEDURES], seguida de la acción que realizará INS [INSERT] y al final la tabla a la que afectará AUTHORS.

Pero ahí no termina todo, seguido de ello, vienen los parámetros, es decir los valores que recibirá el INSERT y aprovecho para decirles que aunque un procedure pertenezca al lenguaje DDL en su interior contendrá (por lo general) lenguaje DML. Pero vamos por paso, primero los parámetros.

CREATEPROCEDURESP_INS_AUTHORS(p_name VARCHAR(100), p_nationalityVARCHAR(100))

Ahora ya tenemos la primera linea. Si has trabajado antes con lenguajes de programación como python, php o java (por mencionar alguno) es lo mismo que cuando creas una función, entre paréntesis, deben ir los parámetros con su respectivo tipo de dato, idéntico a como lo definiste en la creación de la tabla para evitar conflictos. Además si te das cuenta antepuse una p_ a los nombres de mis campos, tan solo para hacerlos mas legible, y dar a notar mas adelante que me refiero a parámetros.

Ahora bien, pudiéramos dejar nuestro código de la siguiente manera:

CREATEPROCEDURESP_INS_AUTHORS(p_name VARCHAR(100), p_nationalityVARCHAR(100))
INSERTINTOauthors(name, nationality)VALUES(p_name, p_nationality);

Donde ya definimos nuestro DML INSERT (va a funcionar correctamente), pero es una buena práctica comenzar con un BEGIN y finalizar con un END. Quedando de la siguiente manera:

CREATEPROCEDURESP_INS_AUTHORS(p_name VARCHAR(100), p_nationalityVARCHAR(100))
BEGININSERTINTOauthors(name, nationality)VALUES(p_name, p_nationality);END;

Y como vemos así quedaría nuestro Procedure. Y si nos damos cuenta en VALUE van los valores que pasamos como parámetros.

  • El Insert ya no lo explico puesto eso ya se vio en el curso 😄

Ahora si queremos hacer uso de nuestro Procedure hacemos lo siguiente.

CALL SP_INS_AUTHORS('LUIS ARCE', 'ECU');

Y esto es como si se le pasara los valores directamente al INSERT

Aquí un ejemplo de como eliminar un procedure:

DROP PROCEDURESP_INS_AUTHORS;

Algo importante es que en MySQL no se puede modificar los parametros y/o el comportamiento del procedure (ALgo que en SQL Server si se puede 😄)

Alter Procedures

UPDATE con procedures

CREATEPROCEDURE SP_UPD_AUTHORS(p_name VARCHAR(100), p_nationality VARCHAR(100), p_id INTEGER)
BEGINUPDATEauthorsSETname = p_name, nationality = p_nationality WHERE author_id = p_id;
END;

CALL SP_UPD_AUTHORS('CARLOS VILLAGRAN', 'ECU', 193);

DELETE con procedures

CREATEPROCEDURESP_DEL_AUTHORS(p_id INTEGER)BEGINDELETEFROMauthorsWHEREauthor_id = p_id;END;

CALL SP_DEL_AUTHORS(193);

Espero les haya servido de mucho 😄

Escribe tu comentario
+ 2
Ordenar por:
2
2624Puntos

Muy bueno, gracias!

3
8449Puntos
3 años

Próximamente estaré subiendo Triggers con MySQL 😄

1
39404Puntos

enhorabuena!, excelente aporte, solo una consulta, he visto que algunos definen los parámetros como se definirían variables en PHP, esto es: en lugar de ser p_mi_parametro, lo definen así $p_mi_parametro(con el signo de pesos / dólar por delante), y funciona! … fuera de la diferencia visual, hay algo más que diferencie un uso del otro?

1

como puedo ejecutar un procedimiento almacenado dentro de otro y si falla utilizar un ROLLBACK en ambos procedimientos?

1
8449Puntos
3 años

Que tal disculpa la demora eh estado ausente un tiempo. Para llamar a un procedimiento dentro de otro basta con hacer CALL Tu_procedimiento

Dicho de otra manera como en el ejempo final del post.

Aparte de eliminar el autor, antes quiero eliminar los libros que tiene ese autor. Entonces planteamos tener en mente una relación 1 Autor tiene muchos Libros (Para este ejemplo, dado que un libro también puede tener muchos autores, pero para este ejemplo nos quedaremos con 1 a *). Dicho esto asumimos tener un procedimiento llamado

SP_DELETE_BOOKS_BY_AUTHORS(author_id INTEGER)

Dicho procedimiento eliminará los libros por el Id de autor que reciba. Entonces antes de eliminar mis autores, debo eliminar mis libros que dependen de ese autor para evitar una restricción de clave foránea, esto quiere decir que no te dejará eliminar un registro si hay otros que dependan de él. (Hay maneras para evitar eso CASCADE, SET NULL, etc. Te invito a investigar.

CREATEPROCEDURESP_DEL_AUTHORS(p_id INTEGER)BEGINCALLSP_DELETE_BOOKS_BY_AUTHORS(p_id);
  DELETE FROM authors WHERE author_id = p_id;
END;

Así de sencillos llamamos a un procedimiento dentro de otro.

1
8449Puntos
3 años

Oh me olvidaba para usar el Rollback debes usar un Try Catch, además de Transacciones, en MySQL es un poco engorroso, asumo que para PostgreSQL igual, para SQL Server es un poco mas sencillo. Te dejo un link para que heches unos vistazos.

Link 1

Link 2

1

Excelente, pero si quiero obtener el ultimo id de otra tabla, para asi poder unir con fk

1
8449Puntos
3 años

Para obtener el ultimo id, en MySQL (No lo eh probado, fue una búsqueda rápida), tenemos una función que se llama LAST_INSERT_ID() y la llamas con un SELECT luego de haber hecho el INSERT. Espero funcione tan bien como el SCOPE_IDENTITY() de SQL Server. Te invito a investigar un poco más acerca de estas funciones.

1
2075Puntos

Y cuando tengo definido con BIT, como es que debo realizarlo?
Estaba creando algo pero me manda un error …

CREATE PROCEDURE SP_INS_CondicionesParticulares
(
@p_CondicionesParticularesIDINT,
@p_PolizaIdINT,
@p_WaterMark VARCHAR(70),
@p_StaticYPositionINT,
@p_StaticXPositionINT,
@p_StaticText VARCHAR(MAX),
@p_StaticCSS VARCHAR(MAX),
@p_PageNum BIT,
@p_PageFoot VARCHAR(100),
@p_PageFootXINT,
@p_mTopINT,
@p_mBottomINT,
@p_mLeftINT,
@p_mRightINT,
@p_ProtectCopy BIT,
@p_Text TEXT,
@p_CSSFont TEXT
)
as
begin
SET NOCOUNT ON

INSERT INTO [MIDOC].[dbo].[CondicionesParticulares]
(CondicionesParticularesID,PolizaId, WaterMark, StaticYPosition,
StaticXPosition, StaticText,StaticCSS,p_PageNum,PageFoot,PageFootX ,mTop ,mBottom ,mLeft ,mRight,ProtectCopy,
Text,CSSFont)
VALUES (@p_CondicionesParticularesID,
@p_PolizaId,
@p_WaterMark,
@p_StaticYPosition ,
@p_StaticXPosition ,
@p_StaticText,
@p_StaticCSS,
@p_PageNum,
@p_PageFoot,
@p_PageFootX,
@p_mTop,
@p_mBottom,
@p_mLeft,
@p_mRight,
@p_ProtectCopy,
@p_Text,
@p_CSSFont)

END 
GO

EXEC SP_INS_CondicionesGenerales '1','1','watermark','23','23','23','23','23','45','45','0','9','10','11','12','text','CSSFont'

el error que me manda es que dice:

Msg 207, Level 16, State 1, ProcedureSP_INS_CondicionesParticulares, Line 47
Invalidcolumnname 'p_PageNum'.
Msg 2812, Level 16, State 62, Line 70
Couldnotfindstoredprocedure 'SP_INS_CondicionesGenerales'.```