Untung99 menawarkan beragam permainan yang menarik, termasuk slot online, poker, roulette, blackjack, dan taruhan olahraga langsung. Dengan koleksi permainan yang lengkap dan terus diperbarui, pemain memiliki banyak pilihan untuk menjaga kegembiraan mereka. Selain itu, Untung99 juga menyediakan bonus dan promosi menarik yang meningkatkan peluang kemenangan dan memberikan nilai tambah kepada pemain.
Berikut adalah artikel atau berita tentang Harian untung99play.xyz dengan judul untung99play.xyz: Introduction to Stored Procedure in MySQL yang telah tayang di untung99play.xyz terimakasih telah menyimak. Bila ada masukan atau komplain mengenai artikel berikut silahkan hubungi email kami di [email protected], Terimakasih.
Summary: in this tutorial, you will learn about MySQL stored procedures, their advantages, and disadvantages.
Getting started with stored procedures
SELECT statement returns all rows in the table
customers from the sample database:
SELECT customerName, city, state, postalCode, country FROM customers ORDER BY customerName;Code language: SQL (Structured Query Language) (sql)
This picture shows the partial output of the query:
When you use MySQL Workbench or mysql shell to issue the query to MySQL Server, MySQL processes the query and returns the result set.
If you want to save this query on the database server for execution later, one way to do it is to use a stored procedure.
CREATE PROCEDURE statement creates a new stored procedure that wraps the query above:
DELIMITER $ CREATE PROCEDURE GetCustomers() BEGIN SELECT customerName, city, state, postalCode, country FROM customers ORDER BY customerName; END$ DELIMITER ;Code language: SQL (Structured Query Language) (sql)
By definition, a stored procedure is a segment of declarative SQL statements stored inside the MySQL Server. In this example, we have just created a stored procedure with the name
Once you save the stored procedure, you can invoke it by using the
CALL GetCustomers();Code language: SQL (Structured Query Language) (sql)
And the statement returns the same result as the query.
The first time you invoke a stored procedure, MySQL looks up for the name in the database catalog, compiles the stored procedure’s code, place it in a memory area known as a cache, and execute the stored procedure.
If you invoke the same stored procedure in the same session again, MySQL just executes the stored procedure from the cache without having to recompile it.
A stored procedure can have parameters so you can pass values to it and get the result back. For example, you can have a stored procedure that returns customers by country and city. In this case, the country and city are parameters of the stored procedure.
A stored procedure may contain control flow statements such as
LOOP that allow you to implement the code in the procedural way.
A stored procedure can call other stored procedures or stored functions, which allows you to modulize your code.
Note that you will learn step by step how to create a new stored procedure in the next tutorial.
MySQL stored procedures advantages
The following are the advantages of stored procedures.
Reduce network traffic
Stored procedures help reduce the network traffic between applications and MySQL Server. Because instead of sending multiple lengthy SQL statements, applications have to send only the name and parameters of stored procedures.
Centralize business logic in the database
You can use the stored procedures to implement business logic that is reusable by multiple applications. The stored procedures help reduce the efforts of duplicating the same logic in many applications and make your database more consistent.
Make database more secure
The database administrator can grant appropriate privileges to applications that only access specific stored procedures without giving any privileges on the underlying tables.
MySQL stored procedures disadvantages
Besides those advantages, stored procedures also have disadvantages:
If you use many stored procedures, the memory usage of every connection will increase substantially.
Besides, overusing a large number of logical operations in the stored procedures will increase the CPU usage because the MySQL is not well-designed for logical operations.
It’s difficult to debug stored procedures. Unfortunately, MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server.
Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance.
In this tutorial, you have learned about the MySQL stored procedures, their advantages, and disadvantages. Let’s move on to learn how to create a new stored procedure in MySQL.