Untitled

21/02/2024
=============================================================================================================
Store procedure:
store procedure is used to group or multiple sql query for reuse.
store procedure is a blcok of sql code that you can save ,so the code can be reuse again and again.

Advantages:
1.Networks traffic reduce
2.database business logic in the database.
3. database  become more secure.
4.it is pre compiled
5.It is reusable
5.better performance


disadvantages:-
1.it is expencive.
2.it is difficult to debuge.
3.it is dependent on database.
--------------------------------------------------
delimiter:
delimiter &&

syntax:
create procedure procedure_name(parameters)
begin
#declaration
#statements
End delimiter_value



exa:
 delimiter //
create procedure customertable()


exa:-
use sp;
alter table customer modify  cust_id int auto_increment
delimiter //
create procedure customerdetails()
begin
insert into customer(cust_name,city) values("ravi","pune"),("neha","nagpur"),("ram","nashik");
update customer set city="nagar" where cust_name="ravi";
select * from customer;
end //

call customerdetails()
    -> Begin
    -> create table customer(cust_id int primary key,cust_name varchar(20),city varchar(20));
    -> insert into customer values(1,"aniket","nashik"),(2,"pranita","pune"),(3,"sweta","nagar");
    -> select * from customer;
    -> end //


call store procedure:-
delimiter ;
syntax:
call procedure_name;
call customertable;