{"id":10152,"date":"2024-04-13T22:41:02","date_gmt":"2024-04-13T19:41:02","guid":{"rendered":"https:\/\/sunucun.com.tr\/bilgi\/?post_type=dt_articles&#038;p=10152"},"modified":"2026-11-15T22:57:05","modified_gmt":"2026-11-15T19:57:05","slug":"stored-procedures-in-mysql-writing-reusable-sql-code","status":"publish","type":"post","link":"https:\/\/sunucun.com.tr\/blog\/stored-procedures-in-mysql-writing-reusable-sql-code\/","title":{"rendered":"Top 5 Benefits of Using Stored Procedures in MySQL for Efficient Database Management"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_90 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/sunucun.com.tr\/blog\/stored-procedures-in-mysql-writing-reusable-sql-code\/#1_What_Is_a_Stored_Procedure\" >1. What Is a Stored Procedure?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/sunucun.com.tr\/blog\/stored-procedures-in-mysql-writing-reusable-sql-code\/#2_Advantages_of_Stored_Procedures\" >2. Advantages of Stored Procedures<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/sunucun.com.tr\/blog\/stored-procedures-in-mysql-writing-reusable-sql-code\/#3_How_to_Create_a_Stored_Procedure\" >3. How to Create a Stored Procedure<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/sunucun.com.tr\/blog\/stored-procedures-in-mysql-writing-reusable-sql-code\/#4_How_to_Use_a_Stored_Procedure\" >4. How to Use a Stored Procedure<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/sunucun.com.tr\/blog\/stored-procedures-in-mysql-writing-reusable-sql-code\/#5_How_to_Update_or_Delete_a_Stored_Procedure\" >5. How to Update or Delete a Stored Procedure<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/sunucun.com.tr\/blog\/stored-procedures-in-mysql-writing-reusable-sql-code\/#6_Example_Use_Cases_for_Stored_Procedures\" >6. Example Use Cases for Stored Procedures<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/sunucun.com.tr\/blog\/stored-procedures-in-mysql-writing-reusable-sql-code\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n\n<p class=\"wp-block-paragraph\">In MySQL, <strong>stored procedures<\/strong> are a powerful tool for writing reusable, modular SQL code. A stored procedure is a named block of SQL statements stored in the database that carries out a database task when it is called. In database management, especially in large projects, repeated operations and complex queries come up often. For these, stored procedures help reduce repeated code, simplify management and keep business logic in one place. In this article we look in detail at what stored procedures are in MySQL, how they are created and how they are used.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1_What_Is_a_Stored_Procedure\"><\/span>1. What Is a Stored Procedure?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A stored procedure is a routine made up of SQL statements that is stored on the database server, can be called by name and is used to carry out specific tasks. You can think of it as a reusable block of code for a particular job, for example updating customer data, producing certain reports or combining complex queries. Stored procedures not only reduce repeated code; they can also reduce traffic between the application and the database and help control access to data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2_Advantages_of_Stored_Procedures\"><\/span>2. Advantages of Stored Procedures<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Reusability:<\/strong> Procedures provide reusable code blocks for specific tasks. This saves time when the same operation is needed in several places or by several applications.<\/li>\n<li><strong>Performance:<\/strong> Instead of sending many separate queries over the network, the application makes a single call and the work is done on the server. This can reduce network round trips and the load on the application.<\/li>\n<li><strong>Security:<\/strong> You can let users run a procedure (with the <code>EXECUTE<\/code> privilege) without giving them direct access to the underlying tables. This is especially important when sensitive data must be protected and access limited.<\/li>\n<li><strong>Ease of maintenance:<\/strong> Using stored procedures collects the logic in one place. This makes maintenance easier and saves time when updates are needed later.<\/li>\n<li><strong>Modular structure:<\/strong> Stored procedures let you structure code in a modular way, which makes complex operations easier to manage.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img fetchpriority=\"high\" decoding=\"async\" width=\"800\" height=\"450\" class=\"wp-image-10085\" style=\"width: 743px; height: auto;\" title=\"Creating a stored procedure in MySQL\" src=\"https:\/\/sunucun.com.tr\/blog\/wp-content\/uploads\/2024\/04\/indir.webp\" alt=\"Creating a MySQL stored procedure\" srcset=\"https:\/\/sunucun.com.tr\/blog\/wp-content\/uploads\/2024\/04\/indir.webp 800w, https:\/\/sunucun.com.tr\/blog\/wp-content\/uploads\/2024\/04\/indir-300x169.webp 300w, https:\/\/sunucun.com.tr\/blog\/wp-content\/uploads\/2024\/04\/indir-768x432.webp 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3_How_to_Create_a_Stored_Procedure\"><\/span>3. How to Create a Stored Procedure<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In MySQL, a stored procedure is created with the <code>CREATE PROCEDURE<\/code> statement. Once defined, it can be used again and again. Here is an example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DELIMITER \/\/\nCREATE PROCEDURE sp_example(IN param1 INT, OUT result INT)\nBEGIN\n    -- SQL statements that carry out a specific task\n    SELECT param1 * 2 INTO result;\nEND \/\/\nDELIMITER ;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, a stored procedure called <code>sp_example<\/code> is created. The SQL statements inside it carry out a specific task. The <code>IN<\/code> and <code>OUT<\/code> keywords define the procedure&#8217;s input and output parameters, so we can send a value to the procedure and get its result back (there is also <code>INOUT<\/code> for a parameter that is both). The <code>DELIMITER<\/code> command temporarily changes the statement terminator in the MySQL client so that the semicolons inside the procedure body do not end the <code>CREATE<\/code> statement early.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"4_How_to_Use_a_Stored_Procedure\"><\/span>4. How to Use a Stored Procedure<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A stored procedure is used by calling it with the <code>CALL<\/code> statement. For example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CALL sp_example(123, @result);\nSELECT @result;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This example calls the <code>sp_example<\/code> procedure and stores its result in the <code>@result<\/code> variable (246 in this case). You can call the procedure many times with different input values and get a different result each time. This is a great way to automate repeated operations and keep your code clean. You can list the procedures in a database with <code>SHOW PROCEDURE STATUS WHERE Db = 'database_name';<\/code> and see a procedure&#8217;s definition with <code>SHOW CREATE PROCEDURE sp_example;<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5_How_to_Update_or_Delete_a_Stored_Procedure\"><\/span>5. How to Update or Delete a Stored Procedure<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">An existing stored procedure can be changed or deleted. MySQL does not support <code>CREATE OR REPLACE PROCEDURE<\/code> (MariaDB does), and <code>ALTER PROCEDURE<\/code> only changes characteristics such as the comment or security settings, not the body. To change a procedure&#8217;s code, you delete it with <code>DROP PROCEDURE<\/code> and create it again. For example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP PROCEDURE IF EXISTS sp_example;\nDELIMITER \/\/\nCREATE PROCEDURE sp_example(IN param1 INT, OUT result INT)\nBEGIN\n    -- Updated SQL statements\n    SELECT param1 + 100 INTO result;\nEND \/\/\nDELIMITER ;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">With these commands you can update an existing procedure or delete it completely. If you only want to delete it, <code>DROP PROCEDURE IF EXISTS sp_example;<\/code> on its own is enough. Creating and dropping procedures requires the <code>CREATE ROUTINE<\/code> and <code>ALTER ROUTINE<\/code> privileges.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"6_Example_Use_Cases_for_Stored_Procedures\"><\/span>6. Example Use Cases for Stored Procedures<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>A procedure that updates user information:<\/strong> For example, you can create a procedure that updates a user&#8217;s address details after validating the input.<\/li>\n<li><strong>A procedure that returns a specific customer&#8217;s orders:<\/strong> This kind of procedure can be used to fetch customer orders quickly and consistently from every application that needs them.<\/li>\n<li><strong>A procedure that joins tables and produces a report:<\/strong> For example, you can write a stored procedure that combines sales data and customer information to produce monthly reports.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Stored procedures in MySQL are a powerful tool for writing reusable, modular SQL code. In this article you learned what stored procedures are, how they are created and how they can be used. Their advantages include reusability, fewer network round trips, controlled access and easier maintenance. By using stored procedures in your database strategy, you can build a more effective, secure and maintainable database structure, and start using them in your own projects.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To run MySQL with full control over users, privileges and configuration, you can use a <a href=\"https:\/\/sunucun.com.tr\/en\/vds-server\">VDS server<\/a> with root access.<\/p>\n\n","protected":false},"excerpt":{"rendered":"<p>In MySQL, stored procedures are a powerful tool for writing reusable, modular SQL code. A stored procedure is a named block of SQL statements stored in the database that carries out a database task when it is called. In database management, especially in large projects, repeated operations and complex queries come up often. For these,&hellip;<\/p>\n","protected":false},"author":1,"featured_media":10065,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[1470],"tags":[],"class_list":["post-10152","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/posts\/10152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/comments?post=10152"}],"version-history":[{"count":2,"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/posts\/10152\/revisions"}],"predecessor-version":[{"id":19767,"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/posts\/10152\/revisions\/19767"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/media\/10065"}],"wp:attachment":[{"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/media?parent=10152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/categories?post=10152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sunucun.com.tr\/blog\/wp-json\/wp\/v2\/tags?post=10152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}