Untitled

13/03/2024
==========================================================================
Store procedure
-------------------
store procedure is used to group or multiple sql query for reuse.
store procedure is a block 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)

exa:-

mysql> create procedure createstudent()
    -> begin
    -> create table Student(roll_no int primary key,name varchar(22), marks int);
    -> insert into Student values(11,"shital",90);
    -> select * from Student;
    -> end //

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 procedure_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 $$


 create procedure SumMarks()
    -> begin
    -> declare totalmarks int;
    -> select sum(marks) into totalmarks from Student;
    -> select totalmarks;
    -> end //

=======================================================================================================================================