Stored Procedures in SQL
In this blog, we will discuss Stored Procedures in SQL. Like we have functions in programming in which we can write multiple statements together just like that we can write multiple or single queries in stored procedures and in functions. If we have to execute a single or some set of queries again and again on the database instead of writing and executing those queries repeatedly what we can do is, we can write query(s) once in a stored procedure and execute/call that procedure over multiple number of times. basically, stored procedures and functions are used to achieve the reusability of queries in MySQL. Master the language of databases with our SQL Classes in Pune. Unlock the skills for seamless SQL Certification and propel your career.
A stored procedure is a group of some SQL statements/queries that can be executed upon calling it. A stored procedure can be parameterized or non-parameterized
Let’s assume stud table given below:
select * from stud;
+—–+———-+——–+——-+——+————+
| id | name | course | marks | age | phone |
+—–+———-+——–+——-+——+————+
| 110 | XYZ | net | 45 | 15 | 67882822 |
| 111 | ram | .net | 78 | 23 | 97882822 |
| 117 | sham | java | 56 | 43 | 882822 |
| 119 | sham | test | 99 | 16 | 99882822 |
| 20 | sharvari | test | 57 | 98 | 99882822 |
| 132 | sharvari | web | 10 | 66 | 99882822 |
| 147 | yash | net | 76 | 33 | 7666882822 |
+—–+———-+——–+——-+——+————+
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
#1.creating non-non-parameterized stored procedure
delimiter %%
create procedure s_info( )
begin
select * from stud where marks > 90;
select count(*) from stud;
select * from stud where course=”java”;
end %
———————-
The above-stored procedure consists of 3 queries. All 3 queries will get executed when we call the above stored procedure
Calling stored procedure
call s_info( );
After executing the above statement, we will get the following result. We have 3 written 3 queries in the above stored procedure. So, will get 3 results in the output.
—–+——+——–+——-+——+———-+
| id | name | course | marks | age | phone |
+—–+——+——–+——-+——+———-+
| 119 | sham | test | 99 | 16 | 99882822 |
+—–+——+——–+——-+——+———-+
+———-+
| count(*) |
+———-+
| 7 |
+———-+
+—–+——+——–+——-+——+——–+
| id | name | course | marks | age | phone |
+—–+——+——–+——-+——+——–+
| 117 | sham | java | 56 | 43 | 882822 |
+—–+——+——–+——-+——+——–+
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
#2.creating parameterized stored procedure with IN parameter(for insert operation)
delimiter @
create procedure s_insert(IN i int,IN nm varchar(56),IN cs varchar(67),IN m int, IN a int)
begin
insert into stud(id,name,course,marks,age) values(i,nm,cs,m,a);
end @
Calling stored procedure
call s_insert(101,”riya”,”dotnet”,89,26);
After executing the above statement the given values 101,”riya”,”dotnet”,89,26 will be copied inside the parameters in,m,a. Then the values of these parameters will be placed inside the insert query.
#3.creating parameterized stored procedure with OUT parameter(for select operation)
delimiter @
create procedure studOut(OUT mk int)
begin
select max(marks) into mk from stud;
end @
In the above query, the out parameter stores the result of the select query that we have written in the procedures, and it outs that result from this procedure into the session variable
Calling stored procedure
call studOut(@v);
select @v as maxMarksFromStudTable;
#here, @v is the session variable, we can use any name to this session variable. This session variable @v holds the value which is returned by stored procedure studOut();
Later in next line we are printing the value of that session variable using select statement.
For Free, Demo classes Call: 020 7117 1500
Registration Link: SQL Training in Pune!
#4.creating parameterized stored procedure with INOUT parameter(for select operation)
delimiter $$
create procedure studInOut1(INOUT i int)
begin
select marks into i from stud where id =i;
end $$
#here,INOUT parameter i acts as both input and output parameter means it takes id as input from the user into the i variable and returns marks as an output in the same i variable
set @y=95; #setting value of id in @y session variable
call studInOut1(@y); #passing @y as a IN(input) parameter to the studInOut1()
procedure
select @y as studMArks; #receiving output in @y as a Out(input) parameter from
studInOut1() procedure
Do watch our Channel to learn more: Click Here
Author:-
Pooja Nandode-Bhavsar
Call the Trainer and Book your free demo class for SQL now!!!
© Copyright 2020 | SevenMentor Pvt Ltd.