Untitled

07/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.


______________________________________________________________________________________________________________
changing default delimiter

syntax:-delimiter //
___________________________________________________________________________________________________________-
creating store procedure:
---------------------------------
create procedure proce_name()
begin
stament;
2statement
......
end (delimeter)


ex:
create procedure createemptable()
    -> begin
    -> create table if not exists employee1(
    -> emp_id int primary key,f_name varchar(222),l_name varchar(222),address varchar(222));
    -> insert into employee1 value(1,"nikita","punde","pune"),(2,"rahul","patil","nagpur"),(3,"neha","shinde","mumbai");
    -> select * from employee1;
    -> end //
_______________________________________________________________________________________________________________
call store procedure:
syntax:
call proce_name;
exa:
call createemptable;
_______________________________________________________________________________________________________________

Variables
----------
synatx: 
declare variable_name datatype default_value;
set variable_name=value

mysql> create procedure countemp()
    -> begin
    -> declare total int default 0;
    -> select count(*) into total from employee1;
    -> select total;
    -> end $$

mysql> create procedure countemp1()
    -> begin
    -> declare total int;
    -> select count(*) into total from employee1;
    -> select total;
    -> end $$