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 !
No comments:
Post a Comment