{"id":254,"date":"2013-05-23T10:36:20","date_gmt":"2013-05-23T10:36:20","guid":{"rendered":"http:\/\/www.siriinnovations.com\/blog\/?p=254"},"modified":"2014-07-03T06:55:40","modified_gmt":"2014-07-03T06:55:40","slug":"basics-of-stored-procedures","status":"publish","type":"post","link":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/","title":{"rendered":"Basics of Stored Procedures"},"content":{"rendered":"

Definition of stored procedure:<\/strong><\/p>\n

A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure.<\/p>\n

A stored procedure which calls itself is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL does not support it well. You should check your version of MySQL database before implementing recursive stored procedures.<\/p>\n

Stored Procedure in MySQL:<\/strong><\/p>\n

MySQL is known as the most popular open source RDBMS which is widely used by both community and enterprise. However during the first decade of its existence, it did not support stored procedure, trigger, event\u2026etc. Since MySQL version 5.0, those features have been added to MySQL database engine to allow MySQL to be more flexible and powerful.<\/p>\n

Before starting the tutorial series about stored procedure, it is required that you have MySQL version\u00a0 5.x+\u00a0 installed in your computer or server.<\/p>\n

Stored Procedures Advantages:<\/strong><\/p>\n

Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than uncompiled SQL commands which are sent from application.<\/p>\n

Stored procedure reduces the traffic between application and database server because instead of sending multiple uncompiled lengthy SQL commands statements, the application only has to send the stored procedure’s name and get the data back.<\/p>\n

Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developers don’t have to program the functions which are already supported in stored procedure in all external applications.<\/p>\n

Stored procedure is secured. Database administrator can grant the access right to application which wants to access stored procedures in database catalog without granting any permission on the underlying database tables.<\/p>\n

Besides those advantages, stored procedure has its own disadvantages which you should be aware of before deciding using it.<\/p>\n

Syntax & Execution:<\/strong><\/p>\n

You simply need to select the SQL option and type in your procedure remembering to set the Delimiter to \/\/ in the box underneath.<\/p>\n

Here\u2019s a stored procedure to try:<\/p>\n

CREATE PROCEDURE sp_number_example_records()\r\nBEGIN SELECT \u2018Number of records: \u2018, count(*) from example;\r\nEND\/\/\r\n<\/pre>\n
where \u201cexample\u201d is the table name.
\nOn clicking \u201cGO\u201d the stored procedure is created and appears as a \u201croutine\u201d below the list of tables on the main \u201cstructure\u201d page for the database.
\nUse the following SQL to call the Stored Procedure from your code:<\/p>\n
CALL sp_number_example_records();
\nHow-to-list-view-stored-procedure-in-phpmyadmin
\nIf you want to list all stored procedures you can use this query
\nSelect\u00a0*\u00a0from information_schema.routines where routine_type = ‘procedure’<\/p>\n
A) In order to limit the result to a specific database:<\/p>\n
select * from information_schema.routines\r\nwhere routine_type = 'procedure' and routine_schema = 'your_db'\r\nDetails of particular stored procedure, show create procedure stored_procedure_name<\/pre>\n
Drop the stored procedure:<\/strong><\/p>\n
DROP PROCEDURE\u00a0 producer_name\u00a0 \/\/(delimiter);\r\nOr\r\nDROP PROCEDURE IF EXISTS producer_name\u00a0 $$<\/pre>\n
Creating the stored procedure:<\/strong><\/p>\n
CREATE PROCEDURE producer_name\u00a0 ()\r\nBEGIN\r\nSELECT * FROM table_name;\r\nEND $$<\/pre>\n
To get the particular procedures:<\/strong><\/p>\n
select routine_definition from information_schema.routines where routine_schema = 'db_name' and routine_name = 'procedure_name';<\/pre>\n
Syntax of Like<\/strong><\/p>\n
CREATE PROCEDURE procedure_name(IN STORENUM INT)\r\nBEGIN\r\nSET @pattern = CONCAT('%',STORENUM,'%');\r\nSET @sql = 'select * from table_name \u00a0where field_name like ?';\r\nPREPARE stmt_name FROM @sql;\r\nEXECUTE stmt_name USING @pattern;\r\nDEALLOCATE PREPARE stmt_name ;\r\nEND;\r\n<\/pre>\n
Joins syntax:<\/strong><\/p>\n
BEGIN\r\nSELECT * FROM\u00a0 table1 t1\u00a0 INNER JOIN table2 t2\r\nON t1.fieldname = t2.fieldname;\r\nEND<\/pre>\n","protected":false},"excerpt":{"rendered":"
Definition of stored procedure: A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure. A stored procedure which calls itself is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL […]<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,6],"tags":[],"class_list":["post-254","post","type-post","status-publish","format-standard","hentry","category-development","category-tutorials"],"yoast_head":"\nBasics of Stored Procedures - Siri Innovations<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Basics of Stored Procedures - Siri Innovations\" \/>\n<meta property=\"og:description\" content=\"Definition of stored procedure: A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure. A stored procedure which calls itself is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/\" \/>\n<meta property=\"og:site_name\" content=\"Siri Innovations\" \/>\n<meta property=\"article:published_time\" content=\"2013-05-23T10:36:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-07-03T06:55:40+00:00\" \/>\n<meta name=\"author\" content=\"Harika Kesa\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Harika Kesa\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/\"},\"author\":{\"name\":\"Harika Kesa\",\"@id\":\"https:\/\/siriinnovations.com\/blog\/#\/schema\/person\/b497c397739cf36d05e731c41a96e9ea\"},\"headline\":\"Basics of Stored Procedures\",\"datePublished\":\"2013-05-23T10:36:20+00:00\",\"dateModified\":\"2014-07-03T06:55:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/\"},\"wordCount\":457,\"commentCount\":0,\"articleSection\":[\"Development\",\"Tutorials\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/\",\"url\":\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/\",\"name\":\"Basics of Stored Procedures - Siri Innovations\",\"isPartOf\":{\"@id\":\"https:\/\/siriinnovations.com\/blog\/#website\"},\"datePublished\":\"2013-05-23T10:36:20+00:00\",\"dateModified\":\"2014-07-03T06:55:40+00:00\",\"author\":{\"@id\":\"https:\/\/siriinnovations.com\/blog\/#\/schema\/person\/b497c397739cf36d05e731c41a96e9ea\"},\"breadcrumb\":{\"@id\":\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/siriinnovations.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Basics of Stored Procedures\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/siriinnovations.com\/blog\/#website\",\"url\":\"https:\/\/siriinnovations.com\/blog\/\",\"name\":\"Technical blog from Siri Innovations\",\"description\":\"Innovative like no other\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/siriinnovations.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/siriinnovations.com\/blog\/#\/schema\/person\/b497c397739cf36d05e731c41a96e9ea\",\"name\":\"Harika Kesa\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/siriinnovations.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec9d4987cbf8cc2c9c65b072d21243e33a3a3e5cd56bb40fe551b6ee23e0a609?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec9d4987cbf8cc2c9c65b072d21243e33a3a3e5cd56bb40fe551b6ee23e0a609?s=96&d=mm&r=g\",\"caption\":\"Harika Kesa\"},\"url\":\"https:\/\/siriinnovations.com\/blog\/author\/harika-kesa\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Basics of Stored Procedures - Siri Innovations","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/","og_locale":"en_US","og_type":"article","og_title":"Basics of Stored Procedures - Siri Innovations","og_description":"Definition of stored procedure: A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another stored procedure. A stored procedure which calls itself is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL […]","og_url":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/","og_site_name":"Siri Innovations","article_published_time":"2013-05-23T10:36:20+00:00","article_modified_time":"2014-07-03T06:55:40+00:00","author":"Harika Kesa","twitter_misc":{"Written by":"Harika Kesa","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/#article","isPartOf":{"@id":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/"},"author":{"name":"Harika Kesa","@id":"https:\/\/siriinnovations.com\/blog\/#\/schema\/person\/b497c397739cf36d05e731c41a96e9ea"},"headline":"Basics of Stored Procedures","datePublished":"2013-05-23T10:36:20+00:00","dateModified":"2014-07-03T06:55:40+00:00","mainEntityOfPage":{"@id":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/"},"wordCount":457,"commentCount":0,"articleSection":["Development","Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/","url":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/","name":"Basics of Stored Procedures - Siri Innovations","isPartOf":{"@id":"https:\/\/siriinnovations.com\/blog\/#website"},"datePublished":"2013-05-23T10:36:20+00:00","dateModified":"2014-07-03T06:55:40+00:00","author":{"@id":"https:\/\/siriinnovations.com\/blog\/#\/schema\/person\/b497c397739cf36d05e731c41a96e9ea"},"breadcrumb":{"@id":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/siriinnovations.com\/blog\/basics-of-stored-procedures\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/siriinnovations.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Basics of Stored Procedures"}]},{"@type":"WebSite","@id":"https:\/\/siriinnovations.com\/blog\/#website","url":"https:\/\/siriinnovations.com\/blog\/","name":"Technical blog from Siri Innovations","description":"Innovative like no other","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/siriinnovations.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/siriinnovations.com\/blog\/#\/schema\/person\/b497c397739cf36d05e731c41a96e9ea","name":"Harika Kesa","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/siriinnovations.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec9d4987cbf8cc2c9c65b072d21243e33a3a3e5cd56bb40fe551b6ee23e0a609?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec9d4987cbf8cc2c9c65b072d21243e33a3a3e5cd56bb40fe551b6ee23e0a609?s=96&d=mm&r=g","caption":"Harika Kesa"},"url":"https:\/\/siriinnovations.com\/blog\/author\/harika-kesa\/"}]}},"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/posts\/254","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/comments?post=254"}],"version-history":[{"count":13,"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/posts\/254\/revisions"}],"predecessor-version":[{"id":884,"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/posts\/254\/revisions\/884"}],"wp:attachment":[{"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/media?parent=254"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/categories?post=254"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/siriinnovations.com\/blog\/wp-json\/wp\/v2\/tags?post=254"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}