Stored Procedure in SQL

Callable Statement:
  • It is an interface of java.sql.* and It is used to Execute
    Stored Procedure.
Stored Procedure :
  •  Stored procedure is a set of SQL query and it is present at database side.
  • Stored procedure improve the performance because it present at database so it is like compile one time use many time.
How to create Stored Procedure

syntax:

CREATE PROCEDURE procedure_name()
BEGIN
.......
procedure logic goes here
.......
END;
Steps to create and run Stored procedure
1. First change the delimiter using command
Delimiter delimiter_symbol;

 DELIMITER &

2. Create procedure
Create Procedure to display all data of student_detail table.

CREATE PROCEDURE getAllStudent()
BEGIN
Select *from student_detail;
END;

procedre1

3. Again make delimiter as it is and call procedure using below command

CALL getAllstudent();

change-delimiter

scenario how store procedure is important:
Create store procedure insert data into table, if data is already exist
then update otherwise insert itIn our case we already created student_detail table

table_student

step: 1 change delimiter

 DELIMITER @

step: 2 create procedure

 CREATE PROCEDURE student_insert_update(in id int,
                                        in fname varchar(10),
                                        in lname varchar(10))
 
 BEGIN
 DECLARE regno_count int;

 select count(*) into regno_count from student_detail where user_id=id;
 
 if regno_count>0 then

 update student_detail
 set f_name=fname,
 l_name=lname where user_id=id;

 else
 insert into student_detail values(id,fname,lname);
 end if;
 
 END @

step:3 again make delimiter as it is

DELIMITER ;

step 4 :call procedure

CALL student_insert_update(2,'vivek','patel');

note: Here we pass some data in procedure.
this record is already exist in table
so procedure will update that data

Mysql step by step Execution of procedure

final_dtaa
Here We updated data whose user_id=2

Leave a Reply

Your email address will not be published. Required fields are marked *