- MySQL Home
- MySQL tutorial
- MySQL Installation
- MySQL Statements
- MySQL Data Types
- Mysq CreateDatabase
- MySQL Create Table
- MySQL Constraints
- MySQL Primary key
- MySQL Foreign key
- Auto Increment Field
- MySQL Default Cons
- MySQL Insert Into
- MySQL Update
- MySQL Drop
- MySQL Delete
- MySQL Select
- MySQL Where
- MySQL Group By
- MySQL Having clause
- MySQL Order By
- MySQL Alter table
- MySQL Alias
- MySQL Create Index
- MySQL Subqueries
- MySQL Operators
- Mysql IsNull IsNotNull
- MySQL And or
- MySQL Between
- MySQL In Operator
- MySql Like Operator
- Mysql Union Operator
- Mysql Storage Engine
- Mysql Stored Procedure
- Mysql if Statement
- Mysql Case Statement
- MySQL Loops
- MySQL Cursor
mysql stored procedure
MySQL stored procedure is set of sql code which is stored in database server and can be invoked by program or trigger.
the stored procedure in MySQL that calls itself is known as a recursive stored procedure.stored procedure is way to execute your business logic directly into database server.
advantages of mysql stored procedure:
- multiple applications are running in multiple environment and need to use the same database.by using stored procedure you can make your business logic independent of programming language.
- if you are using MySQL stored procedure then you do not have table access directly which is one more way to secure the data and transaction.
- stored procedure in MySQL increases performance of your application
- if your application is big or your database server on remote system then by using stored procedure you can decrease the traffic between your database server and application server
- Since stored procedure is written in your database server and application call it sepratly then the degree of re-usability increase because despite to going in much detail you can call stored procedure to perform your action
disadvantages of mysql stored procedure:
- if you overuse a large number of logical operations inside stored procedures, the cpu usage will also increase because database server is not well-designed for logical operations
- A constructs of stored procedures make it more difficult to develop stored procedures that have complicated business logic
- Managing stored procedure is bit difficult because it does not have any object oriented paradigm like things
- MySQL Stored procedure are set of sql command form our logic so sometime programmer need to debug the stored procedure. In mysql stored procedure it is very hard to debug
mysql Stored Procedure Syntax
we are going to develop a simple stored procedure named getusers() to help you get familiar with the syntax. the getusers() stored procedure selects all users from the 'users' table.
the create procedure statement is used to create procedure in mysql
launch the mysql client tool and type the following commands:
delimiter// create procedure getusers() begin select * from user; end delimiter ;
- we use the create procedure statement to create a new stored procedure.we specify the name of stored procedure after the create procedure statement.
- the code - section between begin and end is called the body of the stored procedure in MySQL.
calling stored procedures
in order to call a stored procedure, you use the following sql command:
you use the call statement to call a stored procedures e.g. to call the getusers stored procedure, use the following statement:
delete a stored procedures
if you want to delete a procedure you use the drop procedure statement
drop procedure if exists procedure_name
mySQL stored procedure variables
a variable is a named data object whose value can change during the stored procedure execution.
you must declare a variable before you can use it.
you use the declare statement to declare a variable inside a stored procedure.
declare variable_name datatype(size) default default_name ;
we declared two int variables x and y, and set their default values to zero
you use the set statement to assign a variable another value
declare x, y int default 0 set x = 10 ;
the value of the x variable is 10 after the assignment.
a variable has its own scope,which defines its life time.if you declare a variable inside a stored procedure,it will be out of scope when the end statement of stored procedure reached.
if you declare a variable inside begin end block,it will be out of scope if the end is reached
we discuss the variable scope- when a variable that begins with the @ sign at the beginning is session variable.it is available and accessible until the session ends
the parameters make the stored procedure more flexible and useful.in mysql, a parameter has one of three modes in, out or inout.
- in : is the default mode.
- out: the value of an out parameter can be changed inside the stored procedure and its new value is passed back to the calling program.
- inout : an inout parameter is the combination of in parameter and out parameter. it means that the calling program may pass the argument, and the stored procedure can modify the inout parameter and pass the new value back to the calling program.
how you can define parameters within a stored procedure
create procedure proc () : parameter list is empty
create procedure proc (in varname data-type) :one input parameter.in is optional because parameters are in by default
create procedure proc (out varname data-type) : one output parameter.
create procedure proc (inoutvarname data-type):one parameter which is both input and outpu.
note : you can define multiple parameters defined with different types
delimiter// create procedure proc_in (in var1 int) begin select var1+2 as result; end//
delimiter// create procedure proc_out (out name varchar(100)) begin set name = 'sandeep kumar nehra'; end//
delimiter// create procedure proc_inout (inout var1 int(4), in var2 int(4)) begin set var1 = 4+var2; end//