Skip to main content

Introduction to MySQL Stored Procedures

A stored procedure is a segment of declarative SQL statements stored inside the database catalog. A stored procedure can be invoked by triggers, other stored procedures, and applications such as Java, Python, PHP. In a simple language its classes for the sql and we can use this for long term goal

A stored procedure that calls itself is known as a recursive stored procedure. Most database management systems support recursive stored procedures. However, MySQL does not support it very well. You should check your version of MySQL database before implementing recursive stored procedures in MySQL.

Stored Procedures in MySQL

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 procedures, stored functions, triggers, and events. Since MySQL version 5.0, those features were added to the MySQL database engine to make it more flexible and powerful.

MySQL stored procedures advantages

  • Typically, stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database. However, MySQL implements the stored procedures slightly different. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it into a cache and maintains its own stored procedure cache for every single connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used, otherwise, the stored procedure works like a query.
  • Stored procedures help reduce the traffic between application and database server because instead of sending multiple lengthy SQL statements, the application has to send only the name and parameters of the stored procedure.
  • Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so that developers do not have to develop functions that are already supported in stored procedures.
  • Stored procedures are secure. The database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permissions on the underlying database tables.
Besides those advantages, stored procedures have their own disadvantages, which you should be aware of before using them in your databases.

MySQL stored procedures disadvantages

  • If you use many stored procedures, the memory usage of every connection that is using those stored procedures will increase substantially. In addition, if you overuse a large number of logical operations inside stored procedures, the CPU usage will increase because the database server is not well-designed for logical operations.
  • Stored procedure’s constructs are not designed for developing complex and flexible business logic.
  • It is difficult to debug stored procedures. Only a few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.
  • It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required a specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance phases.
MySQL stored procedures have their own advantages and disadvantages. When you develop applications, you should decide whether or not to use stored procedures based on your application’s architecture.
In the following tutorials, we will show you how to leverage MySQL stored procedures in your database programming tasks with many practical examples.
Labels:

Comments

Post a Comment

Popular posts from this blog

Create facebook messenger chatbot using PHP

Chatbots are the latest sensation in social media communication channels. These automated chat systems are especially build to receive vistiors on social media chats and provide basic information to the visitors about your business. This information could include event schedules, product information, latest deals, store offers and general information about the brand. Entrepreneurs and brand marketers employ chatbots to handle the bulk of chats queries. This way, a large number of queries could be easily handled with minimum costs. Chatbots help reduces the dependence on human customer service representatives (CSR). These chatbots vet out common queries so that the human CSR cold focus on queries that require processing of multiple information sources. Since chatbots steer all conversation toward a pre-set direction, it is easy and time-efficient to use these chatbots instead of human CSR. In this article, I will create a simple Facebook chatbot that could carry out an
2 comments
Read more

Build chatbot with node js and react js

User Experience is given a lot of attention while building any application these days. More and more brands are leveraging chatbots to service their customers, market their brand, and even sell their products. There are a lot of awesome tools out there which helps in building an intelligent bot very easily like Google’s DialogFlow, Amazon Lex, etc, most of which implement their own Natural Language Processing (NLP) logic. However, in some cases, we don’t really need an intelligent bot. Whenever we have a small application having a limited set of options to choose from, it’s not really necessary to use NLP based tools like Google’s DialogFlow. You need to integrate with them (which is pretty easy though), and you need to make a network call to get the results. Instead, you would want to define your rules locally in those cases. Here we will build a simple chatbot using React Simple Chatbot library and add it to our pizza-builder app using which we can build ou
Post a Comment
Read more

Now About PHP Info

Have you ever heard about phpinfo(). Yes, this function is used to know about configuration details of PHP installed in our machine. Such detailed information returned by this phpinfo() includes platform information, PHP and server environment, HTTP header information, PHP core details like version and directives status, License information and etc. phpinfo() function has an optional argument. If this function is called with no argument, then will display all information. We can check it by executing the following code. <? php phpinfo (); ?> We can also request specific details to be displayed to the browser by passing available constants to this function. Following list shows such available options. INFO_GENERAL – This will return information about the platform, compiler, architecture, API and etc. INFO_CREDITS – This option provides a hyperlink which shows details about authors, documentation, QA and infrastructure team. INFO_CONFIGURATION – Disp
Post a Comment
Read more