Untitled

14/02/2024
----------------------------------------------------------------------------------------------------------
store procedure
--------------------
store procedure is used for group or multiple sql query for reuse.
store procedure is a block of code 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.It is pre compiled 
4. better performance.
5. it is reusable.



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

syntax:-
create procedure pro_name(IN,OUT,INOUT)
begin
statement;
2statement
......
end (delimeter)


----------------------------------------------------

changing default delimiter

syntax:-delimiter //

mysql> delimiter $$

create store procedure:-

mysql> create procedure createusertable()
    -> begin
    -> create table user(user_id int primary key ,user_name varchar(225) ,city varchar(222),age int);
    -> insert into user values(101,"prakash","gopndia",23),(102,"rohini","pune",32);
    -> select * from user;
    -> end $$


--------------------------------------------------------------------
call store procedure

syntax:- call proce_name;

----------------------------------------------------------------------------
Variable:
syntax:- declare variablename datatype [default value]

mysql> delimiter //
mysql> create procedure usercount()
    -> begin
    -> declare total int;
    -> select count(*) into total from user;
    -> select total;
    -> end //
Query OK, 0 rows affected (0.02 sec)

mysql> call usercount()//
+-------+
| total |
+-------+
|     2 |
+-------+