Untitled
unknown
plain_text
7 months ago
2.1 kB
12
Indexable
Never
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 $$
Leave a Comment