Procedures: CH Vengaiah, Asst Professor Department of Cse

EXPERIMENT-9
Aim: Procedures
Procedure:
A procedure is a subroutine (like a subprogram) in a regular scripting

language,stored in a database. In the case of MySQL, procedures are
written in MySQL and stored in the MySQL database/server.
A MySQL procedure has a name, a parameter list, and SQL
statement(s).
Section 1. Stored procedure basics
• Introduction to Stored Procedures in MySQL – introduce you

to stored procedures, their advantages, and disadvantages.
• Changing the default delimiter in MySQL – learn how to

change the default delimiter in MySQL.
• Creating new stored procedures – show you how to create use

the CREATE PROCEDURE statement to create a new stored
procedure in the database.
• Removing stored procedures – show you how to use the DROP
PROCEDURE statement to drop an existing stored procedure.
• Variables – guide on you how to use variables to hold immediate
result inside stored procedures.
• Parameters – introduce you to various types of parameters used

in stored procedures including IN, OUT, and INOUT parameter.
CH VENGAIAH, Asst Professor Department Of CSE

• Altering stored procedure – show you step by step how to alter
a stored procedure using a sequence of DROP
PROCEDURE and CREATE PROCEDURE statements in
MySQL Workbench.
• Listing stored procedures – provide you with some useful
commands to list stored procedures from databases.
Section 2. Conditional Statements
• IF statement – show you how to use the IF THEN statement in

stored procedures.
• CASE statement – introduce you to the CASE statements
including simple CASE and searched CASE statements.
Section 3. Loops
• LOOP – learn how to execute a list of statements repeatedly

based on a condition.
• WHILE Loop – show you how to execute a loop as long as a

condition is true.
• REPEAT Loop – show you how to execute a loop until a search

condition is true.
• LEAVE statement – guide you on how to exit a loop

immediately.
Section 4. Error Handling
• Handing exceptions – show you how to handle exception and

errors in stored procedures.

• Raising errors – learn how to use SIGNAL and RESIGNAL to
raise errors in stored procedures.
Section 5. Cursors
• Cursors – learn how to use cursors to process row by row in a

result set.
Section 6. Stored Functions
• Creating a stored function – show you how to use the create

stored functions in the database.
• Removing a stored function – use the DROP

FUNCTION statement to remove a stored function.
• Listing stored functions – learn how to list all stored functions
in the database by using the SHOW FUNCTION STATUS or
querying from the data dictionary.
Section 7. Stored Program Security
• Stored object access control – learn how to control the security

of the stored objects.

mysql> create table item(itemid int,iDesc varchar(20),qoh
int,price int ,category varchar(20));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into item values(7,'ponds',59,180,'cosmotics');

Query OK, 1 row affected (0.03 sec)
mysql> insert into item values(8,'perfume',69,380,'cosmotics');

mysql> insert into item values(9,'ponds',59,180,'cosmotics');

mysql> insert into item values(10,'kb',59,180,'toys');

mysql> insert into item values(11,'deo',59,0,'cosmotics');

mysql> insert into item values(12,'lux',59,0,'cosmotics');

mysql> insert into item values(13,'snake',59,0,'toys');

mysql> insert into item values(14,'train',59,0,'toys');

mysql> select * from item;
8 rows in set (0.00 sec)

Creating a procedure
In MySQL, a procedure can also be created. A procedure can return

one or more than one value through parameters or may not return at
all. The procedure can be used in SQL queries.
Syntax:
CREATE PROCEDURE procedure_name[ (parameter datatype [, p

arameter datatype]) ]
BEGIN
Declaration_section
Executable_section
END;
Example:
mysql> delimiter $$
mysql> create procedure get_student()
-> begin
-> select * from item;
-> end$$
mysql> call get_student(); \G

Arguments Passing
mysql> delimiter /
mysql> create procedure disp_item()
-> begin
-> select * from item;
-> end;
-> /
mysql> call disp_item/
8 rows in set (0.00 sec)

mysql> create procedure find_item(IN id int)
-> begin
-> select * from item where itemid=id;
-> end ;
-> /

mysql> call find_item(1)/
Empty set (0.00 sec)

mysql> call find_item(10)/
1 row in set (0.00 sec)
mysql> create procedure findQty(INOUT x int)

-> begin
-> select qoh into x from item where itemid=x;
-> end;
-> /
mysql> call findQty(@h)/

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @h/
mysql> set @h=10/

mysql> call findQty(@h)/

mysql> select @h/
Drop a procedure
In MySQL, a procedure can also be dropped. When a procedure id
dropped, it is removed from the database.
Syntax:
Drop procedure[ IF EXISTS ] procedure_name;
Parameter:
procedure_name: name of the procedure to be dropped.
Example :
drop procedure get_student;

mysql> drop procedure get_student;
-> \g

Procedures: CH Vengaiah, Asst Professor Department of Cse

Uploaded by

Copyright:

Available Formats

Procedures: CH Vengaiah, Asst Professor Department of Cse

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Procedures: CH Vengaiah, Asst Professor Department of Cse

Uploaded by

Copyright:

Available Formats

EXPERIMENT-9

A procedure is a subroutine (like a subprogram) in a regular scripting

Section 1. Stored procedure basics

• Introduction to Stored Procedures in MySQL – introduce you

• Changing the default delimiter in MySQL – learn how to

• Creating new stored procedures – show you how to create use

• Parameters – introduce you to various types of parameters used

CH VENGAIAH, Asst Professor Department Of CSE

Section 2. Conditional Statements

• IF statement – show you how to use the IF THEN statement in

• LOOP – learn how to execute a list of statements repeatedly

• WHILE Loop – show you how to execute a loop as long as a

• REPEAT Loop – show you how to execute a loop until a search

• LEAVE statement – guide you on how to exit a loop

Section 4. Error Handling

• Handing exceptions – show you how to handle exception and

CH VENGAIAH, Asst Professor Department Of CSE

• Cursors – learn how to use cursors to process row by row in a

Section 6. Stored Functions

• Creating a stored function – show you how to use the create

• Removing a stored function – use the DROP

• Stored object access control – learn how to control the security

CH VENGAIAH, Asst Professor Department Of CSE

mysql> insert into item values(7,'ponds',59,180,'cosmotics');

mysql> insert into item values(8,'perfume',69,380,'cosmotics');

mysql> insert into item values(9,'ponds',59,180,'cosmotics');

mysql> insert into item values(10,'kb',59,180,'toys');

mysql> insert into item values(11,'deo',59,0,'cosmotics');

mysql> insert into item values(12,'lux',59,0,'cosmotics');

mysql> insert into item values(13,'snake',59,0,'toys');

mysql> insert into item values(14,'train',59,0,'toys');

8 rows in set (0.00 sec)

CH VENGAIAH, Asst Professor Department Of CSE

In MySQL, a procedure can also be created. A procedure can return

CREATE PROCEDURE procedure_name[ (parameter datatype [, p

mysql> call get_student(); \G

CH VENGAIAH, Asst Professor Department Of CSE

mysql> call disp_item/

8 rows in set (0.00 sec)

CH VENGAIAH, Asst Professor Department Of CSE

Query OK, 0 rows affected (0.00 sec)

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> create procedure findQty(INOUT x int)

mysql> call findQty(@h)/

CH VENGAIAH, Asst Professor Department Of CSE

mysql> set @h=10/

mysql> call findQty(@h)/

mysql> select @h/

Drop procedure[ IF EXISTS ] procedure_name;

procedure_name: name of the procedure to be dropped.

drop procedure get_student;

CH VENGAIAH, Asst Professor Department Of CSE

You might also like