Stored procedure with cursor

Disclaimer: This presentation is prepared by trainees of
baabtra as a part of mentoring program. This is not official
document of baabtra –Mentoring Partner
Baabtra-Mentoring Partner is the mentoring division of baabte System Technologies Pvt .
Ltd

STORED PROCEDURE
WITH
CURSORS
Jaseena A P
jsnp65@gmail.com
www.facebook.com/Jaseena
Muhammed A P
twitter.com/username
in.linkedin.com/in/profilena
me
9539443588

WHAT IS STORED PROCEDURE?
 A stored procedure is a subroutine available to applications that
access a relational database system.
 Extensive or complex processing that requires execution of
several SQL statements is moved into stored procedures, and all
applications call the procedures.
 Typical uses for stored procedures include data
validation (integrated into the database) or access
control mechanisms.
 Stored procedures can consolidate and centralize logic that was
originally implemented in applications.

WHY WE USE STORED PROCEDURE
 Stored procedures should run faster
-Once created, stored procedures are compiled and stored in
the data base.
 Saving resources
-code is stored in a pre-compiled form ;syntactically valid and
does not need to be compiled again.
 Improving the scalability of applications
-each user of the stored procedure will use exactly the
same form of queries that means the code is reused.

WHY WE USE STORED PROCEDURE
 Less network traffic
-instead of sending multiple lengthy SQL
statements, the application has to send only name and
parameters of the stored procedure.
 Stored procedures are secure.
- Database administrator can grant
appropriate permissions to applications that access stored
procedures in the database without giving any permission on
the underlying database tables.

LIMITATIONS OF STORED PROCEDURE
 For a lot of stored procedures, the memory usage of every
connection will increase substantially.
 Overuse a large number of logical operations inside store
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.
 It is difficult to debug stored procedures. Only few database
management systems allow you to debug stored procedures
 It is not easy to develop and maintain stored procedures; Required
specialized skill set that not all application developers possess

Working with SP
Mysql> CREATE DATABASE my_db;
Mysql>USE my_db;
Mysql>CREATE TABLE Tbl_student(RollNo int,Name varchar(20));
Mysql> INSERT INTO Tbl_student VALUES (1,’Dilna’),(2,’Diya’),(3,’Dinan’);
Mysql>SELECT * FROM Tbl_student;
+--------+-------+
| RollNo | Name |
+--------+-------+
| 1 | Dilna |
| 2 | Diya |
| 3 | Dinan |
+--------+-------+

Working with SP
Musql>delimiter //
Mysql> CREATE PROCEDURE proc1 ()
BEGIN
DECLARE a INT;
SET a = 3;
INSERT INTO Tbl_student VALUES (5,’Name’);
SELECT * FROM Tbl_student WHERE RollNo=a;
END; //
Mysql>delimiter ;
Mysql>call proc1();
DEFAULT 0
+--------+-------+
| RollNo | Name |
+--------+-------+
| 3 | Dinan |
+--------+-------+

Working with SP-parameter passing
 CREATE PROCEDURE proc.Name() ...
 CREATE PROCEDURE proc.Name ([IN] name data-type) ...
 CREATE PROCEDURE proc.Name (OUT name data-type) ...
 CREATE PROCEDURE proc.Name (INOUT name data-type)

Working with SP-parameter passing
CREATE PROCEDURE proc1 (OUT count int)
BEGIN
DECLARE a INT;
SET a = 3;
INSERT INTO Tbl_student VALUES (5,’Name’);
SELECT count(RollNo) into countFROM Tbl_student ;
END; //
Mysql>call proc1(@count);
Mysql>select @count;
+--------+
| @count |
+--------+
| 4 |
+--------+

CASE
Mysql>create procedure proc4(IN mrk int,IN rlno int)
begin
case mrk
when 90 then update Tbl_student set Grade=‘A’ where RollNo=rlno;
when 80 then update Tbl_student set Grade='B' where RollNo=rlno;
else update Tbl_student set Grade='C' where RollNo=rlno;
end case;
end;//
Mysql>select * from Tbl_student;
+--------+-------+-------+
| RollNo | Name | Grade |
+--------+-------+-------+
| 1 | Dilna | A |
| 2 | Diya | B |
| 3 | Dinan | C |
| 5 | Name | NULL |
+--------+-------+-------+

WHILE
mysql> create procedure proc6()
begin
declare v int;
set v=0;
create table student(count int);
while v<5 do
insert into student(count) values(v);
set v=v+1;
end while;
end;//
mysql> call proc6();
mysql> select * from student;
+-------+
| count |
+-------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+-------+

REPEAT
mysql>create procedure proc9()
begin
declare v int;
set v=0;
create table value(v1 int);
repeat
insert into value values (v);
set v=v+1;
until v>=5
end repeat;
end;//
mysql> call proc9();
mysql> select * from value;
+------+
| v1 |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+

LOOP
CREATE PROCEDURE p16 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
The LEAVE statement means "exit the loop". The actual syntax of the
LEAVE statement is the word LEAVE and a statement label.

CURSOR IN MYSQL
 A cursor allows us to fetch one or more rows from a SQL result
set into stored program variables, usually with the intention of
performing some row-by-row processing on the result set.
 A cursor has the following properties:
 Asensitive: The server may or may not make a copy of its result
table
 Read only: Not updatable
 Nonscrollable: Can be traversed only in one direction and
cannot skip rows

CURSORS
create procedure my_proc(OUT return_val int)
begin
declare b int;
declare a int default 1;
declare mycur_1 cursor for select pk_student_id from Tbl_student;
declare continue handler for not found set a=0;
open mycur_1;
repeat
fetch mycur_1 into b;
set return_val=b;
until a=0
end repeat;
close mycur_1;
end;//

If this presentation helped you, please visit our
page facebook.com/baabtra and like it.
Thanks in advance.
www.baabtra.com | www.massbaab.com |www.baabte.com

Contact Us
Emarald Mall (Big Bazar Building)
Mavoor Road, Kozhikode,
Kerala, India.
Ph: + 91 – 495 40 25 550
NC Complex, Near Bus Stand
Mukkam, Kozhikode,
Kerala, India.
Ph: + 91 – 495 40 25 550
Start up Village
Eranakulam,
Kerala, India.
Email: info@baabtra.com

Stored procedure with cursor

Recommended

More Related Content

What's hot (19)

Viewers also liked (7)

Similar to Stored procedure with cursor (20)

More from baabtra.com - No. 1 supplier of quality freshers (20)

Recently uploaded (20)

Stored procedure with cursor