MySQL Tutorial
Introduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdminProcedures in MySQL
Procedures are nothing but reusable SQL statements. We can write the SQL statements once in procedure and use the same procedure multiple times with different parameters. Procedure can take multiple parameters. There are 3 types of parameters in procedures.- IN – Input parameter passed by value
- OUT – Output parameter – This is used to specify that this parameter is going to be returned back
- INOUT – Input as well as output parameter. This is passed by reference
drop procedure if exists TestOutParam;
DELIMITER //
CREATE PROCEDURE TestOutParam(out P1 VARCHAR(22))
BEGIN
SELECT P1;
END //
DELIMITER;
SET @V1 = “hello”;
CALL TestOutParam(@V1);
Main body of the procedure starts with BEGIN keyword and ends with END keyword. We can declare the variable as shown in below syntax.
DECLARE myvariable datatype(size) DEFAULT defaultValue;
DECLARE counter INT DEFAULT 0;
We can assigne the value to variable using below syntax.
SET counter = 2;
We can access session and global variables using below syntax.
SELECT @mysessionvariabl1;
SELECT @@autocommit;
Procedure syntax in MySQL
delimiter $$
Create procedure PROCNAME(in param1 int, in param2 int)
BEGIN
Declare V1 INT;
…..statements
END$$
delimiter “;”
Conditional Statements in MySQL
IF expression THEN
…..statements;
ELSE
…..statements;
END IF;
IF expression THEN
…..statements;
ELSEIF elseif-expression THEN
…..statements;
ELSE
…..statements;
END IF;
Example of conditional statement in MySQL In below example, we have used If condition to check if the number is odd or even.
DROP PROCEDURE IF EXISTS p1;
DELIMITER $$
CREATE PROCEDURE p1 (IN mydata int)
BEGIN
IF (MOD(mydata,2) = 0) THEN
select ‘mydata is even’;
Else
select ‘mydata is odd’;
END IF;
END$$
Call p1(11);
SELECT IF(mod(12,2)=0,”Even”,”odd”);
Case Statements in MySQL
case exp
When ex1 Then
..statements
When ex2 Then
..statements
Else
..statements
End Case
DELIMITER $$
CREATE PROCEDURE p1 ()
BEGIN
DECLARE mycase int default 1;
case mycase
when 1 then select ‘mycase is 1’;
when 2 then select ‘mycase is 2’;
when 3 then select ‘mycase is 3’;
end case;
END$$
DELIMITER ;
Looping Statements in MySQL There are 3 types of loops in MySQL.- While
- Repeat
- Loop
While (exp) Do
..statements
End While;
Repeat
..statements
Until (exp)
End Repeat;
MyLabel:Loop
..statements
End Loop;
You can exit the loop using below statement.
Leave MyLabel;
You can continue the loop (skipping remaining rest of code in a block) using below statement. Iterate MyLabel; Example on loops in MySQL
DELIMITER $$
CREATE PROCEDURE p1 (IN mydata int)
BEGIN
DECLARE mycase int default 1;
DECLARE counter int default 5;
MyLabel1:While (counter > 0) Do
select concat(‘counter is ‘, CAST(counter as CHAR(50)));
SET counter = counter -1;
Leave MyLabel1;
End While;
SET counter = 5;
MyLabel2:Repeat
select concat(‘counter is ‘, CAST(counter as CHAR(50)));
Leave MyLabel2;
Until (exp)
End Repeat;
SET counter = 5;
MyLabel3:Loop
SET counter = counter -1;
IF (counter = 2) THEN
Iterate MyLabel3;
END IF;
select concat(‘counter is ‘, CAST(counter as CHAR(50)));
IF (counter = 0) THEN
Leave MyLabel3;
END IF;
End Loop;
END$$
DELIMITER ;
delimiter “;”
Create procedure addproc(in a int, in b int)
BEGIN
Declare c INT;
set c=(a+b);
SELECT concat(‘addition is ‘, c);
END$$
delimiter “;”
To call procedure you have to use below syntax
call addproc(2,3);
You can get all defined procedures in specific database by firing below query.
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=’PROCEDURE’ AND ROUTINE_SCHEMA=’YOUR_DB_NAME’;
Adding comments in Procedure We can add comments in 3 ways.
#This is a comment using # symbol
— This is a comment using — symbol
Finally you can add multi-line comment using below syntax.
/*
This is a Java style comment
Multi line
*/
Web development and Automation testing
solutions delivered!!