Scribd
Upload
14 views9 pages

Stored Procedures

The document discusses various SQL procedures including simple procedures, procedures with parameters, loops, functions, and updating data using procedures. Procedures are created to perform operations like selecting, inserting, updating and deleting data from tables. Functions are also created to return values like calculating age from date of birth.

Uploaded by

Vilasini Rajesh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
14 views9 pages

Stored Procedures

The document discusses various SQL procedures including simple procedures, procedures with parameters, loops, functions, and updating data using procedures. Procedures are created to perform operations like selecting, inserting, updating and deleting data from tables. Functions are also created to return values like calculating age from date of birth.

Uploaded by

Vilasini Rajesh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 9

PROCEDURES

create database DDBB;


use DDBB;
create table branch(bid int,bname varchar(25),address varchar(20));
select * from branch;
insert into branch values(4,"ICICI","CCC");

SIMPLE PROCEDURE
delimiter $$
create procedure getbranches()
begin
select * from branch;
end $$
delimiter ;
call getbranches()

drop procedure If exists getbranches;

create table employee(empid int,ename varchar(20),jobdesc varchar(20),salary int,bid int);


select * from employee;
insert into employee values(4,"Allu","Software Developer",40000,3);

VARIABLE DECLARATION
delimiter $$
create procedure employeecnt()
begin
declare total int default 0;
select count(empid) into total from employee;
select total;
end $$
delimiter;
call employeecnt()

THREE PARAMETERS

IN
OUT
INOUT

USING IN

delimiter $$
create procedure employeecnt1(IN jdesc varchar(20))
begin
select count(empid) from employee where jobdesc=jdesc;
end $$
delimiter ;
call employeecnt1('Administrator');

USING OUT
delimiter $$
create procedure employeecnt2(IN jdesc varchar(20),OUT total int)
begin
select count(empid) into total from employee where jobdesc=jdesc;
end $$
delimiter ;
call employeecnt2('Administrator', @total);

USING INOUT
delimiter $$
create procedure incrcounter(INOUT counter int ,in inc int)
begin
set counter=counter+inc;
end $$
delimiter ;
set @counter=10;
call incrcounter(@counter,2);
select @counter;
call incrcounter(@counter,3);
select @counter;

IF ELSE
delimiter $$
create procedure getstate(in id int)
begin
if id=1 or id=2 then
select 'Tamil Nadu';
elseif id=3 then
select 'Maharashtra';
else
select 'AP';
end if;
end$$
call getstate(3);

Using Case
LOOP

delimiter $$
create procedure loopdemo()
begin
declare i int;
declare str varchar(20);
set i=1;
set str='’’;
looplabel:loop
if i>10 then
leave looplabel;
end if;
set str=concat(str,i,'’);
set i=i+1;
end loop
select str;
end $$
call loopdemo()

Using While
Simple select query
delimiter $$
create procedure selectperson()
begin
select ename from employee where ename="sunitha";
end $$
delimiter ;;
call selectperson()

Insert
delimiter ;;
call selectperson()
delimiter $$
create procedure insertperson(in bid int,in bname varchar(20),address varchar(20))
begin
insert into branch(bid,bname,address) values(bid,bname,address);
end $$
delimiter ;;
call insertperson(5,'ic','eee')

Update
delimiter $$
create procedure updateperson(in branchname varchar(20),in branchid int)
begin
update branch set bname=branchname where bid=branchid;
end $$
delimiter ;;
call updateperson('ICICIC',5)

DELETE
delimiter $$
create procedure deleteperson(in branchid int)
begin
delete from branch where bid=branchid;
end $$
delimiter ;
call deleteperson(5)

Using update in IF ELSE

create table student1(rollno int,sname varchar(20),marks int,status1 varchar(20));


insert into student1 values(1,"SUNITHA",100,"pass");
select * from student1;
insert into student1 values(2,"PRANAV",80,"NULL");
insert into student1 values(1,"NANDHINI",70,"NULL");
insert into student1 values(1,"ARUL",25,"NULL");

delimiter $$
create procedure updatestatus3(in mymark int)
begin
declare mark1 int default 0;
set mark1=mymark;
if mark1>35 then
update student1 set status1='pass' where marks=mark1;
else
update student1 set status1='fail' where marks=mark1;
end if;
end $$
delimiter ;
call updatestatus3(25);
loop to update

use sunitha;
delimiter $$
create procedure hello5()
begin
declare counter int default 0;
declare sub int default 5;
select count(*) into counter from student1;
select counter;
while counter>0 do
update student1 set marks=marks-sub;
set counter = counter - 1;
end while;
end $$
delimiter ;
call hello5()

FUNCTIONS

select ename,getBranchAddr(bid) from employee;


select * from branch;
delimiter $$
create function getBranchAddr(id int)
returns varchar(50)
deterministic
begin
declare fulladdress varchar(50);
select concat(address,' ',bname)into fulladdress from branch where bid=id;
return fulladdress;
end $$
delimiter ;

Using IF
delimiter $$
CREATE FUNCTION Demoavg(item int) RETURNS INT
deterministic
begin
declare myitem int default 0;
if item<10000 then
set myitem=item+(item*0.01);
else
set myitem=item+(item*0.05);
end if;
return (myitem);
end $$
delimiter ;
select empid,ename,Demoavg(salary) from employee;

UPDATE USING CASE:

use sunitha;
CREATE TABLE EMPSAL
(
ID INT ,
NAME VARCHAR(40),
SALARY FLOAT,
INCENTIVES FLOAT
)
use sunitha;
INSERT INTO EMPSAL VALUES (1,'ABHI',500,NULL);
INSERT INTO EMPSAL VALUES (2,'BABY',600,NULL);
INSERT INTO EMPSAL VALUES (3,'CHARAN',3000,NULL);
INSERT INTO EMPSAL VALUES (4,'DHANA',4000,NULL);
INSERT INTO EMPSAL VALUES (5,'EMO',5000,NULL);
INSERT INTO EMPSAL VALUES (6,'FARAN',6000,NULL);
INSERT INTO EMPSAL VALUES (7,'GEO',7000,NULL);
INSERT INTO EMPSAL VALUES (8,'JAHANGEER',8000,NULL);
select * from empsal;
Update EMPSAL
Set INCENTIVES =
Case
WHEN Salary < 1000 then SALARY * 1.05
WHEN Salary < 5000 AND salary >= 1000 THEN SALARY * 1.07
ELSE SALARY
End

Age Calculation Using Function

CREATE TABLE Employee1


(
EmployeeId INT PRIMARY KEY,
Name VARCHAR(50),
Salary INT,
DOB Date
);

INSERT INTO Employee1(EmployeeId, Name, Salary, DOB) VALUES(1001, 'Pranaya', 10000,


'1988-02-29');
INSERT INTO Employee1(EmployeeId, Name, Salary, DOB) VALUES(1002, 'Anurag', 20000,
'1992-06-22');
INSERT INTO Employee1(EmployeeId, Name, Salary, DOB) VALUES(1003, 'Sambit', 30000,
'1978-04-12');

DELIMITER $$
CREATE FUNCTION Func_Calculate_Age
(
Age date
)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE TodayDate DATE;
SELECT CURRENT_DATE() INTO TodayDate;
RETURN YEAR(TodayDate) - YEAR(Age);
END$$
DELIMITER ;

SELECT EmployeeId, Name, Salary, DOB, Func_Calculate_Age(DOB) AS Age FROM


Employee1;
https://www.youtube.com/watch?v=wat1ricQPF8

You might also like