stored procedure mysql

Stored procedure is a group of SQL statements which can be executed repeatedly. It allows variable declarations, flow control and other useful programming techniques. Parameters are the important part of procedures.

The parameters make the stored procedure more flexible and useful. Stored procedures can increase productivity by writing once and using it many times.

Advantages of Stored Procedure

  • Stored procedure can Share logic with different applications.
  • Stored procedure can isolate users from data tables.
  • This gives you the ability to grant access to the stored procedures that manipulate the data but not directly to the tables.
  • Shared procedure can provide security that means users can input data and also they can change it but they could not write procedure because of data access controls on them.
  • Stored procedure can be used to improve performance because it reduces network traffic. With a stored procedure, multiple calls can be kept into one.

Disadvantages of Stored Procedure

  • Stored procedure can increase load on.
  • There’s a docent /earning curve. First you should have knowledge of MySQL statements then only you will write stored procedures.
  • You are repeating the logic of your application in two different places: your server code and the stored procedures code, making things a bit more difficult to maintain.
  • Migrating to a different database management system may potentially be more difficult.

Types of stored procedure
1. System defined stored procedure

System Defined stored procedure System Defined stored procedure are already defined in the database

2. User defined stored procedure

User Defined stored procedure User Defined stored procedures are created by the user.

For example :

My_procedure( )

Proc( )

Benefits of Stored Procedure

Explain benefits of stored procedures?

  • Stored procedure is fast because it is precompiled.
  • It is easy to maintain.
  • It improves reliability and reduced development time.
  • Increased security-database administrator can control the users who access the stored procedure.
  • Reduce network usage among servers and clients.

create stored procedure mysql

How to create stored procedure ?
Creating a stored procedure can be done using following syntax.

In which procedure_name is the name of procedure IN parameter can be used to accept the value when procedure gets called and OUT parameter is used to set parameter value.

Syntax

DELIMITER//
CREATE procedure procedure _name ([parameter(s))) 
STATEMENTS 
DELIMITER//

Executing Stored Procedure mysql

How to execute stored pi?

After creating stored procedure, you can execute it. Stored procedures that take no arguments can be invoked as follows:

Now the display2() procedure can be called as

CALL display2();

Leave a Reply