0

Stored procedures in PHP and MySQL

Today, a majority of databases support stored procedures. A stored procedure is simply a set of SQL instructions that perform a particular task which is stored in the database to help associated programming languages and database engine retrieve it anytime. Stored procedures are a great way to increase the performance and reduce network traffic. They usually accept input parameters and can be used by different clients over the network.

Why stored procedures are used?

There are many benefits of using stored procedures and some of them are discussed below:

Modular programming:

Stored procedure enables modular programming. You can always create a procedure for recurrent tasks and simply call it as many times as you need it.

Faster execution

Stored procedures are usually created to remove the requirement of writing the same code again and again. If the process needs a set of SQL instructions to perform repetitively, stored procedures always come handy. These stored procedures are optimized and parsed when they are called for the first time, and the complied version stays in the memory cache to use it afterwards.

Reduces network traffic

A task requiring a set of SQL instructions can be performed by calling a stored procedure. All you need is a single statement that calls a stored procedure instead of writing hundreds of statements again and again over the network.

Better security

Stored procedures can be executed depending on the permissions assigned to the users. No user can use them unless they do not have the permissions to access it.

Stored-Procedure1

Types of stored procedures:

There are basically three types of stored procedures.

System

The system stored procedures, as the name suggests, are used for systems. They are saved in the master database and usually start with a sp_ prefix. These procedures are used to perform many different tasks such as supporting SQL server functions.

User defined

These types of stored procedures are stored in the user database and are created to perform a variety of tasks in the same database. They start with sp_ prefix since it first checks the master database before checking the user database.

Extended

These types of procedures call functions from DLL files. However, a majority of programmers have depreciated the use of extended stored procedures.

Creating a procedure in MySQL

Stored procedures accept three parameters – IN, INOUT and OUT. IN parameter can be called with CALL statement.

Calling a procedure using PHP

Handling result sets in stored procedures

 

Save

Prakash S

Prakash S

I would like to introduce myself as a Software professional opting for the career in software industry. I'm Prakash S, a MCA graduate and trained as industry level practice for Software technology. Basically I am a PHP Developer but now days exploring more in HTML5, CSS, AngularJS and jQuery libraries.