Back to Top

How To use Store Procedure with PDO

To use Store Procedure with PDO

Many databases support the concept of prepared statements.Prepared statements are useful to execute SQL statements with some patterns and optimize the query.Today I am going to add the post for PDO.It is my first post for PDO.I think almost all developers have an idea about store procedure. I am going to cover store procedure with PDO that is how we can use store procedure with PDO.

Many of the databases support store procedures.Store procedure is a one kind of script that are executed on your database typically in a database(MySQL).Stored Procedure allows us to manipulate the data close to the location where the data is held and reduce the bandwidth.A stored procedure is stored in a database that can be called by the database engine and also reduces network traffic overhead.

What is Store Procedure?

Store Procedure maintains the separation of the data from the script logic and allows multiple systems to access the data in a uniform manner so its save you valuable coding and debugging time.Finally, stored procedures increase query speeds with the use of predefined execution code and with Stored Procedure we can prevent direct interaction with the data.

NOTE: Each database have different store procedure language so check your database syntax for store procedure first.

Example to use Store Procedure with PDO

Let’s start with PDO.To use a PDO with store procedure is very simple.Let me explain with example so you will clear with

Above is the simple store procedure to retrieve data from table category.Here is code with which you can run store procedure directly in MySQL:

Once GetAllCategory Store Procedure will be created that can be called at any time. It returns all categories from the table with executing above code.The Store Procedure allows you to use a data in the more powerful way in order to meet business needs.

Must READ:
How to use Triggers in MySQL
Database BackUp Script
NoSQL and MongoDB

In the example above, which was made for MySQL, you’ll notice that I have used the SELECT statements in store procedure but you can use DECLARE, SET as per your query. Generally, these are needed in any MySQL stored procedure to retrieve data. Nothing special is needed to retrieve the data from the stored procedure, we just call the Store procedure and pass store procedure with method PREPARE to grab the value returned from the SELECT statement in the stored procedure.

If you have any question or query, please leave your comments.I would be happy help you in any issue.

Share:
Share on Facebook0Share on LinkedIn0Tweet about this on TwitterShare on Google+0Buffer this pageDigg thisShare on Reddit0

Leave a Reply

Your email address will not be published. Required fields are marked *

Most Popular Posts

The Reader’s Poll – August 2014

Posted on 3 years ago

Bhumi

Understanding of Polymorphism in PHP

Posted on 1 year ago

Bhumi

How to get Last Post in WordPress

Posted on 5 years ago

Bhumi

What’s New in Docker 1.12

Posted on 1 year ago

Bhumi

What is Predefined Interface in PHP

Posted on 2 years ago

Bhumi

Facade implementation in Laravel

Updated 3 weeks ago

Bhumi

MySQL coalesce() function

Updated 18 years ago

Bhumi

How to use JavaScript Promise API?

Updated 18 years ago

Bhumi

What are Lambdas and Closures in PHP?

Updated 18 years ago

Bhumi

The Reader’s Poll – June 2015

Posted on 2 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 3 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 4 years ago

Bhumi