What is Stored Procedure and Function in SQL/MySQL ? Difference B/w Stored Procedure vs Function vs View

Aj is article me ham parhen gen ke SQL routine kia hoti hay? Stored Function vs Stored Procedure me kia difference hay, Stored Procedure aur View me kia farq hota hay aur kis ko kahan use krna behter hay.

What is SQL Stored Routine?

Sab se phele hamen ye pata hona chaiye ke stored routine kia hoti hay? Stored routines hamare pas aisi statements hoti hain jin ko ham database server me compile krke store krlete hain aur phir zarorat parne per ham us routine ko call krlete hain.

Is ka faida yeh hota hay ke agar hamen ek hi kam bar bar krna hay to bajae iske ke ham har bar sql statements likhen ham eek hi bar unhen database me store krlete hain aur zarorart prne per sirf routine ko call krlete hain jis me wo sql statements store hain.

Stored routines do tarah ki hoti hain, stored function and stored procedure, MySQL in donu tarah ki routine ko support krta hay.

Stored Procedure?

Stored procedure ek stored routine hota hay, Stored procedure jese ke name se hi zahir hay ek bunch of script hoti hay, Jo SQL statements, If else conditions etc per consist hoti hay.

Stored procedure key faidey ki bat karen to is ke kafi faidey hain, Like:

1-Aisi  sql statements jo hamen apni application me bar bar likhni parti hay unko bar bar likhne ke bajae ham ek stored procedure bana kr wahan store krden gen, Ab jab bhi hamen un sql statement ko execute krna hoga to ham sirf us stored procedure ko call karen gen.

2-Security wiese aisa hoskta hay ke ham nh chahtey ke developers database ke har table ko access krske, Balke sirf specific procedures ke zariye hi wo tables me kuch manipulation krske.

3-Isi tarah performance wise bhi iska faida hay, Application bajae iski ke har bar ek lengthy sql query server per sent kare, Procedures ki madad se sirf bohat short sql statement server ke pass jaengi, Iska faida yeh hay ke network traffic reduce hoga, Aur hamari bandwidth bachey gi.

How to Create Stored Procedure?

Stored procedure create krne ke liye syntax is tarah hay:

1.  CREATE PROCEDURE `show_products`()  

2.  BEGIN  

3.  select * from products;  

4.  END  

Yahan per suppose karen hame product table se products ki information chaiye, Ab bajae iske ke har bar ham iske liye apni application me query llikhen, Ham is ka stored procedure banaden gen ab agli bar jab hamen products ki information chaiye to ham simply procedure ko call kare gen is tarah:

1.  call `show_products`()  

Parameters in Stored Procedure:

Stored procedure me hamara pas teen type ke parameters hote hain, IN, OUT, INOUT

1-In- Ye aisa parameters hota hay jis me value insert hoti hay procedure ko call krte we.

2-Out- Ye aisa parameter hota hay jis me ham stored procedure se koi result is parameter me store krskte hain aur phir calling program me iski value use krskte hain.

Variables/If Else in Stored Procedure:

Isi tarah programming me jis tarah ham variables, If else, loop wagera isitimal krte hain aisey hi ham sql stored procedure me bhi ye tamam expressions use krskte hain.

Calling One stored procedure from other:

Ham ek stored procedure me dusra stored stored procedure bhi call krskte hain, Example dekhen:

1.  CREATE PROCEDURE `show_stock`(IN `start` DATE, IN `end` DATE)  

2.  BEGIN  

3.    

4. SELECT `date`, sum(size_kg) as `total_size` FROM `stock` WHERE date>=start AND date<=end;  

5.    

6.  END  

Stored Functions:

Stored function hamare pas ek aur tarah ki sub routine hote hain, Stored function vs Stored procedure me kafi  differences hain jo ye hain:

1- Stored functions ke through ham simply koi computed value return krskte hain jabke stored procedure ki madad se ham complex set of sql statements ko execute krskte hain.

2-Mysql me stored function sirf scaler values return krskte hain, results set nh. Jabke stored procedure me ham result sets bhi return krskte hain

3-Stored procedures se ham out parameter use krke multiple values return krskte hain, Jabke function se sirf ham single value return krskte hain.

4-Function ko ham SQL select statement me use krskte hain, Uske ilawa where clause wagera me bhi ham functions ko use krskte hain. Jabke Stored procedure ko ham select statement, where clause me kahin bhi use nh krskte.

5-Is tarah ham stored procedure me transactions control statements use krsskte hain jabke functions me use nh krskte.

In short stored function simplified version hay stored procedure ka, Jab hamen simply koi single value return krni ho jese  total products etc tab hamen functions use krna chaiye. 

On the other hand jab hamen koi complex set of queries execute krni ho jin me if else, loop wagera sab use ho ya result set return ho jab hamen stored procedure use krna chaiyee.

How to Create Stored Function:

Functions ko create krne ka bhi tarika bhi almost same hay procedure ke tarikey se, Example dekhen:

1.  CREATE FUNCTION `total_stock_qty`() RETURNS int(20)  

2.  BEGIN  

3.   DECLARE total int(20);  

4.    SET total= (SELECT COUNT(*) FROM stock) ;   

5.    RETURN total;   

6.  END  

Stored Procedure vs View:

Stored routines ko samjhne ke bad ap ke zehan me ek sawal arha hoga ke stored procedure aur view me kia farq hay? Products ki information hasil krni hay to ham view ka bhi use krskte hain ya stored procedure ka bhi

To yad rkhiye jese ke name me se zahir hay “View”  yani kisi muskil cheez ko asan bana kr pesh krdena, Jese ek join query ko ham view ke zriye asan banadete hain aur bar bar complex join query use krne se bach jate hain, Bas similarity view aur stored procedure me yahi khatam hojati hay.

Stored procedure se ham result return krne ke sath sath bohat kuch krskte hain, Like loop, if else, case statements and much more jabke view eke sath nahi.

So in short basic difference yad rkhe:

1-View- View ko jab use kren jab apko complex join query ko bar bar likhne se bachna ho

2-Stored Procedure: jab use ki jiye jab apko complex set of queries ko execute krna ho, Input/Output parameters ko use krte hue with loop,if else and different statements.

3- Stored Function: jab use kijiye jab apko simply kisi value ko compute krna ho like sum, count etc.

Agar apko is article se thora se bhi faida hua ho to isko apni coding community ke sath zaror share karen take ziada se ziada log jin ko programming sikhni me muskilat hoti hay ya language barrier hote hay un tak ye knowledge pouch ske , remember Sharing is Caring !