{"id":1161,"date":"2024-07-31T11:35:41","date_gmt":"2024-07-31T09:35:41","guid":{"rendered":"https:\/\/blog.sutilweb.eu\/?page_id=1161"},"modified":"2024-07-31T11:35:42","modified_gmt":"2024-07-31T09:35:42","slug":"38-procedimientos-almacenados","status":"publish","type":"page","link":"https:\/\/sutilweb.eu\/index.php\/databases\/sql\/38-procedimientos-almacenados\/","title":{"rendered":"38. Procedimientos almacenados"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Un <strong>Stored Procedure<\/strong> es un conjunto de instrucciones <strong>SQL<\/strong> que se almacenan en una <strong>base de datos<\/strong> como si fuera una <strong>funci\u00f3n,<\/strong> por lo tanto, lo podemos llamar y ejecutar tantas veces como lo necesitemos. Pueden recibir <strong>par\u00e1metros de entrada<\/strong> y devolver <strong>par\u00e1metros de salida<\/strong> como si fuera una funci\u00f3n en cualquier lenguaje de programaci\u00f3n, y se utilizan para encapsular la <strong>l\u00f3gica de negocio<\/strong> y reducir la complejidad de las aplicaciones a la hora de que est\u00e1n interactuando con la base de datos.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p class=\"wp-block-paragraph\">Podemos desarrollar y ejecutar <strong>procedimientos almacenados<\/strong> en nuestras <strong>bases de datos<\/strong>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Ejercicio<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Vamos a crear un ejercicio completo para entender su funcionamiento del uso de los <strong>procedimientos almacenados<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Ejm<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE subscripciones (\nsubscripcion_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\nsubscripcion VARCHAR(30) NOT NULL,\ncosto DECIMAL(5,2) NOT NULL\n);\n\nINSERT INTO subscripciones VALUES \n(0,'Bronce',199.99),\n(0,'Plata',299.99),\n(0,'Oro',399.99);\n\nCREATE TABLE clientes (\ncliente_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\nnombre VARCHAR(30) NOT NULL,\nemail VARCHAR(50) UNIQUE\n);\n\nCREATE TABLE tarjetas (\ntarjeta_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\ncliente INT UNSIGNED,\nTARJETA BLOB,\nFOREIGN KEY(cliente)\n REFERENCES clientes(cliente_id)\n ON DELETE RESTRICT\n ON UPDATE CASCADE,\n);\n\nCREATE TABLE servicios (\nservicio_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\ncliente INT UNSIGNED,\ntarjeta INT UNSIGNED,\nsubscripcion INT UNSIGNED,\nFOREIGN KEY(cliente)\n REFERENCES clientes(cliente_id)\n ON DELETE RESTRICT\n ON UPDATE CASCADE,\nFOREIGN KEY(tarjeta)\n REFERENCES tarjetas(tarjeta_id)\n ON DELETE RESTRICT\n ON UPDATE CASCADE,\nFOREIGN KEY(subscripcion)\n REFERENCES subscripciones(subscripcion_id)\n ON DELETE RESTRICT\n ON UPDATE CASCADE\n);<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Sintaxis para poder crear un store procedure<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Un <strong>stored procedure<\/strong> va a ser una especie de <strong>funci\u00f3n.<\/strong> En un <strong>stored procedure<\/strong> vamos a ejecutar m\u00e1s de una linea de c\u00f3digo, y hay que delimitarle y hacerle ver a <strong>SQL<\/strong> que este <strong>stored procedure<\/strong> va a tener m\u00e1s de una ejecuci\u00f3n. Para ello tenemos una clausula denominada <strong><em>DELIMITER<\/em><\/strong> , como el punto y coma lo utilizamos para el t\u00e9rmino de cada instrucci\u00f3n, hay que establecer un <strong>delimitador<\/strong> para que entienda que de una linea de c\u00f3digo a otra se van a estar ejecutando varias instrucciones, para que se tome la delimitaci\u00f3n del <strong>stored procedure<\/strong> hasta despu\u00e9s de la clausula <strong><em>DELIMITER<\/em><\/strong> de cierre, y como <strong>delimitador<\/strong> podemos usar cualquier car\u00e1cter especial, aunque se suelen utilizar el s\u00edmbolo de d\u00f3lar ($) o dos diagonales (\/\/). Veamos su sintaxis para entenderlo mejor.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Sintaxis (invocar un store procedure)<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DELIMITER \/\/\n\nCREATE PROCEDURE sp_obtener_subscripciones()\n BEGIN\n  -- Lineas que queramos ejecutar\n END \/\/\n\nDELIMITER ; (El punto y coma tiene que ir separado)<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Veamos un ejm.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Ejm<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">-- Invocamos un store procedure\nDELIMITER \/\/\n\nCREATE PROCEDURE sp_obtener_subscripciones()\n BEGIN\n  SELECT * FROM subscripciones;\n END \/\/\n\nDELIMITER ;\n\n-- Llamamos un store procedure con la sentencia CALL\nCALL sp_obtener_subscripciones();\n\n-- Mandar llamar a todos los store procedures\nSHOW PROCEDURE STATUS WHERE db = 'base de datos';\n\n-- Eliminar un store procedure\nDROP PROCEDURE sp_obtener_subscripciones;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Par\u00e1metros de entrada y salida en PA<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Vamos a utilizar los <strong>datos<\/strong> de las <strong>tablas<\/strong> que pusimos m\u00e1s arriba para hacer un ejercicio completo de <strong>stored procedure<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Ejm<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DELIMITER \/\/\n\nCREATE PROCEDURE sp_asignar_servicio(\n IN i_subscripcion INT UNSIGNED,\n IN i_nombre VARCHAR(30).\n IN i_email VARCHAR(50),\n IN i_tarjeta VARCHAR(16),\n OUT o_respuesta VARCHAR(50)\n)\n\n BEGIN\n  DECLARE existe_correo INT DEFAULT 0; \n  DECLARE cliente_id INT DEFAULT 0;\n  DECLARE tarjeta_id INTE DEFAULT 0;\n\n  START TRANSACTION;\n   SELECT COUNT(*) INTO existe_correo\n    FROM clientes\n    WHERE correo = i_correo;\n\n   IF existe_correo &lt;&gt; 0 THEN\n    SELECT 'Tu correo ya ha sido registrado' INTO o_respuesta;\n   ELSE\n    INSERT INTO clientes VALUES (0,i_nombre, i_correo);\n\n    SELECT LAST_INSERT_ID() INTO cliente_id;\n    INSERT INTO tarjetas \n     VALUES (0, cliente_id, AES_ENCRYPT(i_tarjeta, cliente_id));\n\n   SELECT LAST_INSERT_ID() INTO tarjeta_id;\n\n   INSERT INTO servicios VALUES (0, cliente_id, tarjeta_id, i_subscripcion);\n\n   SELECT 'Servicio asignado con \u00e9xito' INTO o_respuesta;\n   END IF;\n  COMMIT;\n END \/\/\n\nDELIMITER ;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Ahora vamos ahora a mandar a llamar a nuestro <strong>stored procedure<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT * FROM subscripciones;\nSELECT * FROM clientes;\nSELECT * FROM tarjetas;\nSELECT * FROM servicios;\n\nCALL sp_asignar_servicio(3,'Francisco', 'info@sutilweb.eu','1234567890123456', @res);\nSELECT @res;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Un Stored Procedure es un conjunto de instrucciones SQL que se almacenan en una base de datos como si fuera [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1076,"menu_order":37,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"class_list":["post-1161","page","type-page","status-publish","hentry"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Sutil Web","author_link":"https:\/\/sutilweb.eu\/index.php\/author\/sutilweb\/"},"uagb_comment_info":0,"uagb_excerpt":"Un Stored Procedure es un conjunto de instrucciones SQL que se almacenan en una base de datos como si fuera [&hellip;]","_links":{"self":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1161","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/comments?post=1161"}],"version-history":[{"count":1,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1161\/revisions"}],"predecessor-version":[{"id":1162,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1161\/revisions\/1162"}],"up":[{"embeddable":true,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1076"}],"wp:attachment":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/media?parent=1161"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}