Untitled

24/02/2024
======================================================================
store procedure:-

variables:-variables are a container to storing data values:
syntax:-declare variable_name dataype 

use sp;
select * from customer;
delimiter //
create procedure countcustomer()
begin
declare total int;
select count(*) into total from customer;
select total;
end //

call countcustomer();

=====================================================
parameter:
1. In :- input
2. Out:- output
3. INOUT:-InputOutput
========================================================================
In:
syntax:
create procedure procedure_name(IN inputvariable datatype)
begin
#declaration
#statements
End delimiter_value

delimiter //

create procedure getdetails(IN Name varchar(222))
begin
select * from customer where cust_name=Name;
end //


call getdetails("ram")

--------------------------------------------------------------------------------------------------------------
2.Out:

delimiter //

create procedure getmaxid(OUT max_id int)
begin
select max(cust_id) into max_id from customer;
end//

call getmaxid(@id)

select @id
---------------

create procedure sumofid(OUT sum_id int)
begin
select sum(cust_id) into sum_id  from customer;
end//


call sumofid(@sunid);

select @sunid;

==================================================================================
INOUT:-
delimiter //

create  procedure inoutpara1(In city1 varchar(222), OUT minname varchar(222))
begin
select min(cust_name) into minname from customer where city=city1;
end//

 call inoutpara1("nashik",@name);
 select @name;
call inoutpara1("nagpur", @nam1);

select @nam1

-------------------------------------------------------------------------------------------
delimiter //

create  procedure avgofid1(INOUT avgid float , In city1 varchar(222))
begin
select avg(cust_id) into avgid from customer where city=city1;
end//

 call avgofid1(@avgId ,"nashik");

select @avgId;


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

drop store procedure:
syntax:
drop procedure proce_name;
exa:-
drop procedure inoutpara;

----------------------------------------------------------------------------------
listing store procedure

show procedure status where db="sp";