MySQL Stored Routines

Introduction to MySQL Stored Routines

कई बार कुछ ऐसे statements होते है जिन्हें आपको बार बार execute करने की आवश्यकता होती है। उदाहरण के लिए आप बार बार select statement के द्वारा tables के records check करते है।

इससे आपका time भी waste होता है और यदि आप कोई MySQL से related application develop कर रहे है तो बार बार उन्हीं statement को execute करने की वजह से compile time भी बहुत ज्यादा हो जाता है। ऐसी situations के लिए MySQL आपको एक mechanism provide करती है जो stored routines कहलाते है।

एक Stored routine कुछ SQL statements का set होता है जिसे आप बाद में call करके reuse कर सकते है। ये set MySQL server में stored रहता है जिसे बाद में एक simple call statement के द्वारा execute करवाया जा सकता है।

Stored routines के बारे में एक ख़ास बात ये है की ये एक particular database से related होते है। यानि की जो stored routines आपने एक database के लिए create किये है उन्हें दूसरे databases के लिए यूज़ नहीं किया जा सकता है।

MySQL आपको 2 तरह के stored routines provide करती है।

  1. Stored procedures
  2. Stored functions

इससे से पहले की आप इनके बारे में detail से जानें आइये देखते है की stored routines को किस प्रकार यूज़ किया जा सकता है।

Uses of Stored Routines

निचे कुछ ऐसी situations दी जा रही है जिनमें normally stored routines को यूज़ किया जाता है।

  • Stored procedures के द्वारा आप ऐसे statements create कर सकते है जो server पर stored रहेंगे और जिन्हें different client applications server के through execute करवा सकती है। उदाहरण के लिए अलग अलग शहरों में कुछ clients आपकी application यूज़ कर रहे है तो particular statements को client application में store करने की बजाय server पर stored routines की form में store किया जा सकता है जँहा से सभी clients उन्हें execute करवा सकते है। इससे client application पर load कम हो जाता है और यदि कोई change करना है तो वो भी एक ही जगह करना होगा।
  • अपनी application में security add करने के लिए भी आप stored routines को यूज़ कर सकते है। यदि कुछ ऐसे SQL statements है जो security के लिहाज से important है तो उन्हें आप stored routines में server पर store कर सकते है ताकि केवल verified users ही उन्हें execute करवा सके जैसे की Administrator आदि।
  • यदि आप client applications के द्वारा data के access को control करना चाहते है तो इसके लिए भी stored routines को यूज़ किया जा सकता है।

Advantages of Stored Routines

आइये अब stored routines यूज़ करने की कुछ advantages के बारे में जानने का प्रयास करते है।

Fast Execution

Stored routines यूज़ करने से आपकी application का execution fast हो जाता है। एक stored routine सिर्फ एक बार ही compile किया जाता है और इसके बाद इसे server में store कर दिया जाता है।

जब भी इसे call किया जाता है तो ये दुबारा compile नहीं होता है और सीधा execute होता है। Compilation time बचने की वजह से आपकी application में execution पहले से fast हो जाता है।

Reduced client/server Traffic

बिना stored routines के आपको बहुत से SQL statements execute करवाने होंगे इससे server पर traffic ज्यादा होता है। लेकिन stored routines एक single SQL statement की तरह execute होते है इससे server पर traffic पहले से बहुत कम हो जाता है।

Centralized Control

Stored routines की एक प्रमुख advantage ये है की आप पुरे code को एक जगह server पर store कर पाते है। इससे Database Administrators (DBA’s ) को application को control करने की क्षमता प्राप्त होती है।

Security

Stored routines आपको एक extra security layer provide करते है। Stored routines से आप tables से related security control implement कर सकते है।

Types of Stored Routines

जैसा की मैने आपको पहले बताया stored routines 2 तरह के होते है।

  1. Stored Procedures
  2. Stored Functions

इन stored routines के बारे में detail से जानने से पहले आइये देखते है की इनमें क्या difference होता है।

Difference b/w stored procedures & stored functions

Stored Procedure

  1. Stored procedures द्वारा कोई value return नहीं की जाती है।
  2. Stored procedures में SELECT statement के साथ दूसरे DML statements भी यूज़ कर सकते है।
  3. Stored procedures में आप transactions यूज़ कर सकते है।
  4. Stored Procedure को CALL command के द्वारा call किया जाता है।

Stored Functions

  1. Stored functions द्वारा value return की जाती है।
  2. Stored functions में आप केवल SELECT statement ही यूज़ कर सकते है।
  3. Stored functions में आप ऐसे SQL statements नहीं यूज़ कर सकते है जो transaction commits और rollbacks perform करते है।
  4. Stored functions को SELECT command के द्वारा call किया जाता है।

Stored Procedures

एक stored procedure SQL statements का set होता है। इस set को CALL command द्वारा call किया जाता है। Stored procedures कोई value return नहीं करते है लेकिन output variables के द्वारा values को pass किया जा सकता है। Stored procedures create करने का basic syntax निचे दिया जा रहा है।

mysql >CREATE PROCEDURE procedure_name (parameter-list)
>BEGIN
> //sql statements
>END

जब भी आप एक procedure create करते है तो उससे पहले delimiter change करते है। जैसा की आप जानते है की default delimiter semicolon (;) होता है और इसे ही किसी भी MySQL statement का end माना जाता है।

लेकिन क्योंकि एक procedure एक complete statement की तरह execute होता है इसलिए यदि आप procedure में semicolon (;) यूज़ करते है जो की आप करेंगे सभी SQL statements के बाद तो procedure को वही पर end मान लिया जाता है। Procedure के अंदर यूज़ होने वाला semicolon procedure को end करने wale semicolon से conflict हो जायेगा।

ऐसी situation में आप procedure को end करने वाले delimiter को change कर देते है। ताकि आप semicolon (;) procedure के अंदर यूज़ कर सके। इसके लिए आप DELIMITER statement का यूज़ करते है। इस statement के बाद आप वह character लिखते है जिसे आप DELIMITER बनाना चाहते है। इसका उदाहरण निचे दिया रहा है।

mysql >delimiter //

इस statement के execute होने के बाद कोई भी statement जिसे आप execute करवाना चाहते है उसे // से end किया जायेगा। साथ ही अब आप procedure के अंदर SQL statements को semicolon (;) से end कर सकते है।

अब जब आपका procedure end होगा तो वह इसी delimiter से end होगा। Procedure के end होने के बाद आप delimiter को semicolon (;) ऊपर दिए गए उदाहरण के अनुसार बना सकते है।

आइये procedure कैसे create करते है ये एक उदाहरण से समझने का प्रयास करते है।

mysql > delimiter //
 >create procedure myProcedure
 >begin
 >select * from Employee;
 >end //
mysql >delimiter;

ऊपर दिए गए उदाहरण में एक बहुत ही simple stored procedure create किया गया है। Call किये जाने पर ये stored procedure Employee table से सभी rows को select करके show करता है।

किसी भी stored procedure को call करने के लिए CALL statement यूज़ किया जाता है। इसका उदाहरण निचे दिया जा रहा है।

mysql >call myProcedure;

Stored Procedures with Parameters

Stored procedures कोई value return नहीं करते है इसलिए इनमें value pass करने और value वापस प्राप्त करने के लिए MySQL आपको कुछ built इन keyword provide करती है।

इन keywords की मदद से आप किसी भी procedure में input भी कर सकते है और output प्राप्त भी कर सकते है। आइये इन keywords के बारे में जानने का प्रयास करते है।

  • IN – IN keyword procedure में input pass करने के लिए यूज़ किया जाता है। इस keyword के बाद आप parameter का नाम लिखते है और उसके बाद parameter type define करते है। जो parameter आप इस keyword के द्वारा पास करते है उसे procedure के बाहर access नहीं किया जा सकता है।
  • OUT – OUT keyword procedure से output प्राप्त करने के लिए use किया जाता है। इस keyword के बाद आप parameter का नाम देते है और उसके बाद parameter का type define करते है। इस keyword के द्वारा define किये गए parameter को procedure के बाहर access किया जा सकता है।
  • INOUT – ये keyword IN और OUT दोनों का सम्मिलित रूप है। इससे से आप value input भी कर सकते है और output भी प्राप्त कर सकते है।

आइये अब इन parameters के यूज़ को एक उदाहरण से समझने का प्रयास करते है।

mysql >delimiter //
>create procedure add(in num int, out result int)  
>begin
>set result=num+2;
>end //
mysql >delimiter;

ऊपर दिए गए उदाहरण में add procedure में दो parameter pass किये गए है। इनमे num input parameter है और result output parameter है। जब भी आप इसे call करेंगे तो एक integer number pass करेंगे और procedure आपको उस number में 2 add कर देगा और उस value को result में store कर देगा। इस procedure को आप इस प्रकार call कर सकते है।

mysql >call add(8, @result);

जब भी आप किसी ऐसे procedure को call करते है जो parameters यूज़ करता है तो output parameter से पहले @ symbol लगाया जाता है। अब इस procedure से result में store की गयी value प्राप्त करने के लिए आप इस प्रकार SELECT statement यूज़ कर सकते है।

mysql >select @result;

Stored Functions

Stored procedures की तरह ही stored functions भी SQL statements के set होते है। लेकिन ये values return करते है। Stored functions को SELECT statement द्वारा call किया जाता है। Stored functions create करने का basic syntax निचे दिया जा रहा है।

mysql >CREATE FUNCTION function_name (parameter-list) RETURNS return_type
>BEGIN
> //SQL statements
>END

Stored function create करने से पहले आप stored procedure की तरह ही delimiter को change करते है। इसके बारे में पहले बताया जा चूका है इसलिए यँहा पर नहीं बताया जा रहा है। आइये अब stored functions को एक उदाहरण से समझने का प्रयास करते है।

mysql >delimiter //
mysql >create function myFunction returns int(10)
>begin
>set num=5;
>return num;
>end
mysql >delimiter;

ऊपर दिए गए उदाहरण में एक stored function create किया गया है। इस stored function में एक variable set किया गया है num और उसकी value 5 set की गयी है। Call होने पर ये stored function simply num variable की value को return करता है।

जैसा की आपको पहले बताया जा चूका है की किसी भी stored function को call करने के लिए SELECT statement यूज़ किया जाता है। ऊपर दिए गए stored function को आप इस प्रकार call कर सकते है।

mysql >select myFunction();

Stored Functions with Parameters

Stored functions में parameters आप किसी भी normal method की तरह यूज़ कर सकते है। Stored functions में parameters declare करने का general syntax निचे दिया जा रहा है।

mysql >CREATE FUNCTION function-name(parameter-name parameter-type (size));

आइये stored functions में parameters के यूज़ को उदाहरण से समझने का प्रयास करते है।

mysql >delimiter //
mysql >create function add(num int(10))
>returns int (10)
>begin
>return num+2;
>end
mysql >delimiter;

Related Posts