Untitled

14/03/2024
======================================================================================
Store Procedure continue:-

IN , OUT and INOUT parameters:-
================================

In :- Input from user
syntax:
create procedure procedure_name(IN input_variable datatype)
begin
statement
end (delimiter)

exa:-
mysql> create procedure countbysalsman(IN cust_id int)
    -> begin
    -> select count(salesman_id) from orders where customer_id=cust_id;
    -> end &&

call procedure:-
 call countbysalsman(3005)

-------------------------------------------------------------------------------------
OUT :- return output from procedure

synatx:
create procedure procedure_name(OUT Out_variable datatype)
begin
statement
end (delimiter)

exa:-
mysql> delimiter ##
mysql> create procedure ordersdetails(OUT max_id int)
    -> begin
    -> select max(ord_id) into max_id from orders;
    -> end ##

call procedure:-
--------------
mysql> call ordersdetails(@Max_ORD_Id) ##
select @Max_ORD_Id ##

-----------------------------------------------------------------------------------------------------
INOUT:-

mysql> create procedure inoutpara(INOUT obj int)
    -> begin
    -> select ord_id into obj from orders where customer_id=obj;
    -> end##

mysql> set @obj=3009##
mysql> call inoutpara(@obj)##
mysql> select @obj##


==============================================================================================================================
delete procedure :-
syntax:-
drop procedure procedure_name;
exa:-
 drop procedure countid;
=======================================================================================================================================
listing store procedure:

show procedure status where db="first";