Check our Courses - New Batch for: Selenium - starts from 19 November | Java (Core & Advanced) - starts from 3 December | Python - starts from 3 December

MYSQL Stored Procedure

Hi Freshers,

Giving you brief introduction about how to go with Stored Procedure (SP).

Basically, what do you mean by SP—
SP are the small functions which are globally kept at shared location which can be accessed from any location. This also reduces the network traffic which we do it running our normal query. In terms of data integrity also SP can be useful.

The data in the database can be accessed through procedures and run through procedure, which can also be run repeatedly n number of times along with complex behavior statements.

Mentioning about syntax of SP (can vary with application-current syntax from mysql workbench community)

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic …] routine_body

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,…]])
RETURNS type
[characteristic …] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
COMMENT ‘string’
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

routine_body:
Valid SQL routine statement

Stored Procedure can be of different types:
1. System defined SP
2. User defined SP
3. CLR SP
4. Extended Procedure

Lets take a simple example as mentioned in screenshots below.

I want to count the number of records in particular table, this I can do with simple query:

Select count(*) from ;

This can also be written using SP as given below

stored procedure

stored procedure

stored procedure

stored procedure

stored procedure

See below which can be written in simple query too:

stored procedure

So why then SP?

Because:
1. SP can combine the logic, you can change the code of SP without effecting the tables or structure.
2. You can access multiple tables in structured and secure way.
3. SP can call within itself upto 32 levels .
4. Database can stored the previous run so that SP can be run again and again with effecting its performance.
5. Passing of parameters is easy to understand.
6. The procedures comes under testability feature, can be tested at the unit level.
7. Can also prevent SQL Injection attacks.
8. You can simply change the parameters and names of tables without changing the procedure.
9. Parameters can be accepted bidirectional.
10. Can be recursively called.

I hope this helps

Happy Learning !

April 27, 2018