Oracle PL/SQL संग्रहित प्रक्रिया और फ़ंक्शन उदाहरणों के साथ
प्रक्रियाएँ और फ़ंक्शन उप-प्रोग्राम हैं जिन्हें डेटाबेस में डेटाबेस ऑब्जेक्ट के रूप में बनाया और सहेजा जा सकता है। उन्हें अन्य ब्लॉकों के अंदर भी बुलाया या संदर्भित किया जा सकता है।
इसके अलावा, हम इन दो उप-कार्यक्रमों के बीच प्रमुख अंतरों को भी कवर करेंगे। साथ ही, हम चर्चा करने जा रहे हैं Oracle अंतर्निहित कार्य।
PL/SQL सबप्रोग्राम में शब्दावली
PL/SQL सबप्रोग्राम के बारे में जानने से पहले, हम उन विभिन्न शब्दावलियों पर चर्चा करेंगे जो इन सबप्रोग्राम का हिस्सा हैं। नीचे वे शब्दावलियाँ दी गई हैं जिन पर हम चर्चा करने जा रहे हैं।
प्राचल
पैरामीटर किसी भी मान्य पैरामीटर का परिवर्तनशील या प्लेसहोल्डर है PL/SQL डेटाटाइप जिसके माध्यम से PL/SQL सबप्रोग्राम मुख्य कोड के साथ मानों का आदान-प्रदान करता है। यह पैरामीटर सबप्रोग्राम को इनपुट देने और इन सबप्रोग्राम से निकालने की अनुमति देता है।
- इन मापदंडों को निर्माण के समय उप-कार्यक्रमों के साथ परिभाषित किया जाना चाहिए।
- इन पैरामीटर्स को इन सबप्रोग्राम्स के कॉलिंग स्टेटमेंट में शामिल किया जाता है ताकि इन मानों को सबप्रोग्राम्स के साथ इंटरैक्ट किया जा सके।
- सबप्रोग्राम और कॉलिंग स्टेटमेंट में पैरामीटर का डेटाटाइप समान होना चाहिए।
- पैरामीटर घोषणा के समय डेटाटाइप के आकार का उल्लेख नहीं किया जाना चाहिए, क्योंकि इस प्रकार के लिए आकार गतिशील होता है।
उनके उद्देश्य के आधार पर मापदंडों को इस प्रकार वर्गीकृत किया गया है
- IN पैरामीटर
- आउट पैरामीटर
- इन आउट पैरामीटर
IN पैरामीटर
- इस पैरामीटर का उपयोग उपप्रोग्रामों को इनपुट देने के लिए किया जाता है।
- यह सबप्रोग्राम के अंदर केवल पढ़ने के लिए उपलब्ध वैरिएबल है। सबप्रोग्राम के अंदर इनके मान नहीं बदले जा सकते।
- कॉलिंग स्टेटमेंट में, ये पैरामीटर एक चर या एक शाब्दिक मान या एक अभिव्यक्ति हो सकते हैं, उदाहरण के लिए, यह '5*8' या 'a/b' जैसी अंकगणितीय अभिव्यक्ति हो सकती है, जहां 'a' और 'b' चर हैं।
- डिफ़ॉल्ट रूप से, पैरामीटर IN प्रकार के होते हैं।
आउट पैरामीटर
- इस पैरामीटर का उपयोग उपप्रोग्राम से आउटपुट प्राप्त करने के लिए किया जाता है।
- यह सबप्रोग्राम के अंदर एक रीड-राइट वैरिएबल है। इनके मान सबप्रोग्राम के अंदर बदले जा सकते हैं।
- कॉलिंग स्टेटमेंट में, इन पैरामीटर्स को हमेशा वर्तमान सबप्रोग्राम्स से मान रखने के लिए एक चर होना चाहिए।
इन आउट पैरामीटर
- इस पैरामीटर का उपयोग इनपुट देने और उपप्रोग्राम से आउटपुट प्राप्त करने दोनों के लिए किया जाता है।
- यह सबप्रोग्राम के अंदर एक रीड-राइट वैरिएबल है। इनके मान सबप्रोग्राम के अंदर बदले जा सकते हैं।
- कॉलिंग स्टेटमेंट में, इन पैरामीटर्स को हमेशा सबप्रोग्राम्स से मान रखने के लिए एक चर होना चाहिए।
उप-प्रोग्राम बनाते समय इन पैरामीटर प्रकारों का उल्लेख किया जाना चाहिए।
वापसी
RETURN वह कीवर्ड है जो कंपाइलर को सबप्रोग्राम से कंट्रोल को कॉलिंग स्टेटमेंट पर स्विच करने का निर्देश देता है। सबप्रोग्राम में RETURN का सीधा मतलब है कि कंट्रोल को सबप्रोग्राम से बाहर निकलने की जरूरत है। एक बार जब कंट्रोलर को सबप्रोग्राम में RETURN कीवर्ड मिल जाता है, तो इसके बाद का कोड छोड़ दिया जाएगा।
आम तौर पर, पैरेंट या मेन ब्लॉक सबप्रोग्राम को कॉल करेगा, और फिर कंट्रोल उन पैरेंट ब्लॉक से कॉल किए गए सबप्रोग्राम में शिफ्ट हो जाएगा। सबप्रोग्राम में RETURN कंट्रोल को वापस उनके पैरेंट ब्लॉक में लौटा देगा। फ़ंक्शन के मामले में RETURN स्टेटमेंट वैल्यू भी लौटाता है। इस वैल्यू का डेटाटाइप हमेशा फ़ंक्शन घोषणा के समय बताया जाता है। डेटाटाइप किसी भी वैध PL/SQL डेटा टाइप का हो सकता है।
PL/SQL में प्रक्रिया क्या है?
A प्रक्रिया PL/SQL में एक सबप्रोग्राम इकाई है जिसमें PL/SQL कथनों का एक समूह होता है जिसे नाम से बुलाया जा सकता है। PL/SQL में प्रत्येक प्रक्रिया का अपना एक अनूठा नाम होता है जिसके द्वारा उसे संदर्भित और बुलाया जा सकता है। PL/SQL में यह सबप्रोग्राम इकाई Oracle डेटाबेस को डेटाबेस ऑब्जेक्ट के रूप में संग्रहीत किया जाता है।
नोट: सबप्रोग्राम एक प्रक्रिया के अलावा कुछ नहीं है, और इसे आवश्यकता के अनुसार मैन्युअल रूप से बनाया जाना चाहिए। एक बार बनाए जाने के बाद उन्हें डेटाबेस ऑब्जेक्ट के रूप में संग्रहीत किया जाएगा।
PL/SQL में प्रक्रिया उपप्रोग्राम इकाई की विशेषताएं नीचे दी गई हैं:
- प्रक्रियाएं किसी प्रोग्राम के स्वतंत्र ब्लॉक होते हैं जिन्हें प्रोग्राम में संग्रहीत किया जा सकता है। डेटाबेस.
- PL/SQL कथनों को निष्पादित करने के लिए इन PLSQL प्रक्रियाओं को उनके नाम का संदर्भ देकर कॉल किया जा सकता है।
- इसका उपयोग मुख्यतः PL/SQL में किसी प्रक्रिया को निष्पादित करने के लिए किया जाता है।
- इसमें नेस्टेड ब्लॉक हो सकते हैं, या इसे अन्य ब्लॉक या पैकेज के अंदर परिभाषित और नेस्टेड किया जा सकता है।
- इसमें घोषणा भाग (वैकल्पिक), निष्पादन भाग, अपवाद प्रबंधन भाग (वैकल्पिक) शामिल हैं।
- मानों को इसमें पारित किया जा सकता है Oracle प्रक्रिया या मापदंडों के माध्यम से प्रक्रिया से प्राप्त किया गया।
- इन पैरामीटर्स को कॉलिंग स्टेटमेंट में शामिल किया जाना चाहिए।
- SQL में एक प्रक्रिया में नियंत्रण को कॉलिंग ब्लॉक पर वापस करने के लिए RETURN कथन हो सकता है, लेकिन यह RETURN कथन के माध्यम से कोई भी मान वापस नहीं कर सकता है।
- प्रक्रियाओं को सीधे SELECT कथनों से नहीं बुलाया जा सकता। उन्हें किसी अन्य ब्लॉक से या EXEC कीवर्ड के माध्यम से बुलाया जा सकता है।
वाक्य - विन्यास
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameterl IN/OUT <datatype> .. . ) [ IS | AS ] <declaration_part> BEGIN <execution part> EXCEPTION <exception handling part> END;
- CREATE PROCEDURE संकलक को नई प्रक्रिया बनाने का निर्देश देता है Oracleकीवर्ड 'OR REPLACE' संकलन को मौजूदा प्रक्रिया (यदि कोई हो) को वर्तमान प्रक्रिया से बदलने का निर्देश देता है।
- प्रक्रिया का नाम अद्वितीय होना चाहिए.
- कीवर्ड 'IS' का उपयोग तब किया जाएगा, जब संग्रहीत प्रक्रिया Oracle कुछ अन्य ब्लॉक में नेस्टेड है। यदि प्रक्रिया स्टैंडअलोन है तो 'AS' का उपयोग किया जाएगा। इस कोडिंग मानक के अलावा, दोनों का अर्थ एक ही है।
उदाहरण 1: प्रक्रिया बनाना और EXEC का उपयोग करके उसे कॉल करना
इस उदाहरण में, हम एक बनाने जा रहे हैं Oracle प्रक्रिया जो नाम को इनपुट के रूप में लेती है और स्वागत संदेश को आउटपुट के रूप में प्रिंट करती है। हम प्रक्रिया को कॉल करने के लिए EXEC कमांड का उपयोग करने जा रहे हैं।
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) IS BEGIN dbms_output.put_line (‘Welcome '|| p_name); END; / EXEC welcome_msg (‘Guru99’);
कोड स्पष्टीकरण:
- कोड लाइन 1: 'welcome_msg' नाम और 'IN' प्रकार के एक पैरामीटर 'p_name' के साथ प्रक्रिया का निर्माण करना।
- कोड लाइन 4: इनपुट नाम को संयोजित करके स्वागत संदेश प्रिंट करना।
- प्रक्रिया सफलतापूर्वक संकलित की गई है।
- कोड लाइन 7: पैरामीटर 'Guru99' के साथ EXEC कमांड का उपयोग करके प्रक्रिया को कॉल करना। प्रक्रिया निष्पादित होती है, और संदेश "स्वागत है Guru99" के रूप में प्रिंट होता है।
फ़ंक्शन क्या है?
फंक्शन एक स्टैंडअलोन PL/SQL सबप्रोग्राम है। PL/SQL प्रक्रिया की तरह, फंक्शन का एक अनूठा नाम होता है जिसके द्वारा इसे संदर्भित किया जा सकता है। इन्हें PL/SQL डेटाबेस ऑब्जेक्ट के रूप में संग्रहीत किया जाता है। नीचे फंक्शन की कुछ विशेषताएँ दी गई हैं।
- फंक्शन एक स्टैंडअलोन ब्लॉक है जिसका उपयोग मुख्य रूप से गणना के उद्देश्य से किया जाता है।
- फ़ंक्शन मान लौटाने के लिए RETURN कीवर्ड का उपयोग करता है, और इसका डेटाटाइप निर्माण के समय परिभाषित किया जाता है।
- किसी फ़ंक्शन को या तो मान लौटाना चाहिए या अपवाद उठाना चाहिए, अर्थात फ़ंक्शन में रिटर्न अनिवार्य है।
- बिना DML कथन वाले फ़ंक्शन को सीधे SELECT क्वेरी में कॉल किया जा सकता है, जबकि DML ऑपरेशन वाले फ़ंक्शन को केवल अन्य PL/SQL ब्लॉकों से ही कॉल किया जा सकता है।
- इसमें नेस्टेड ब्लॉक हो सकते हैं, या इसे अन्य ब्लॉक या पैकेज के अंदर परिभाषित और नेस्टेड किया जा सकता है।
- इसमें घोषणा भाग (वैकल्पिक), निष्पादन भाग, अपवाद प्रबंधन भाग (वैकल्पिक) शामिल हैं।
- मानों को फ़ंक्शन में पास किया जा सकता है या पैरामीटर्स के माध्यम से प्रक्रिया से प्राप्त किया जा सकता है।
- इन पैरामीटर्स को कॉलिंग स्टेटमेंट में शामिल किया जाना चाहिए।
- PLSQL फ़ंक्शन RETURN के अलावा OUT पैरामीटर के माध्यम से भी मान लौटा सकता है।
- चूंकि यह हमेशा मान लौटाएगा, इसलिए कॉलिंग स्टेटमेंट में यह हमेशा वैरिएबल्स को पॉप्युलेट करने के लिए असाइनमेंट ऑपरेटर के साथ आता है।
वाक्य - विन्यास
CREATE OR REPLACE FUNCTION <procedure_name> ( <parameterl IN/OUT <datatype> ) RETURN <datatype> [ IS | AS ] <declaration_part> BEGIN <execution part> EXCEPTION <exception handling part> END;
- CREATE FUNCTION कंपाइलर को एक नया फ़ंक्शन बनाने का निर्देश देता है। कीवर्ड 'OR REPLACE' कंपाइलर को मौजूदा फ़ंक्शन (यदि कोई हो) को वर्तमान फ़ंक्शन से बदलने का निर्देश देता है।
- फ़ंक्शन का नाम अद्वितीय होना चाहिए.
- RETURN डेटाटाइप का उल्लेख किया जाना चाहिए.
- जब प्रक्रिया किसी अन्य ब्लॉक में नेस्ट की जाती है, तो कीवर्ड 'IS' का उपयोग किया जाएगा। यदि प्रक्रिया स्टैंडअलोन है तो 'AS' का उपयोग किया जाएगा। इस कोडिंग मानक के अलावा, दोनों का अर्थ एक ही है।
उदाहरण 1: फ़ंक्शन बनाना और उसे अनाम ब्लॉक का उपयोग करके कॉल करना
इस प्रोग्राम में, हम एक ऐसा फ़ंक्शन बनाने जा रहे हैं जो नाम को इनपुट के रूप में लेता है और स्वागत संदेश को आउटपुट के रूप में लौटाता है। हम फ़ंक्शन को कॉल करने के लिए अनाम ब्लॉक और सेलेक्ट स्टेटमेंट का उपयोग करने जा रहे हैं।
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2 IS BEGIN RETURN (‘Welcome ‘|| p_name); END; / DECLARE lv_msg VARCHAR2(250); BEGIN lv_msg := welcome_msg_func (‘Guru99’); dbms_output.put_line(lv_msg); END; SELECT welcome_msg_func(‘Guru99:) FROM DUAL;
कोड स्पष्टीकरण:
- कोड लाइन 1: बनाना Oracle 'welcome_msg_func' नाम वाला फ़ंक्शन और 'IN' प्रकार का एक पैरामीटर 'p_name'।
- कोड लाइन 2: रिटर्न प्रकार को VARCHAR2 के रूप में घोषित करना
- कोड लाइन 5: संयोजित मान 'स्वागत' और पैरामीटर मान लौटा रहा है।
- कोड लाइन 8: उपरोक्त फ़ंक्शन को कॉल करने के लिए अनाम ब्लॉक.
- कोड लाइन 9: फ़ंक्शन के रिटर्न डेटाटाइप के समान डेटाटाइप के साथ वेरिएबल को घोषित करना।
- कोड लाइन 11: फ़ंक्शन को कॉल करना और 'lv_msg' वेरिएबल में रिटर्न मान भरना।
- कोड लाइन 12: वेरिएबल वैल्यू प्रिंट करना। यहाँ आपको जो आउटपुट मिलेगा वह है “वेलकम गुरु99”
- कोड लाइन 14: SELECT कथन के माध्यम से समान फ़ंक्शन को कॉल करना। वापसी मान सीधे मानक आउटपुट पर निर्देशित किया जाता है।
प्रक्रिया और कार्य के बीच समानताएं
- दोनों को अन्य PL/SQL ब्लॉकों से बुलाया जा सकता है।
- यदि उपप्रोग्राम में उठाया गया अपवाद उपप्रोग्राम में नियंत्रित नहीं किया जाता है उपवाद सम्भालना अनुभाग, फिर यह कॉलिंग ब्लॉक में प्रसारित होगा।
- दोनों में आवश्यकतानुसार कई पैरामीटर हो सकते हैं।
- दोनों को PL/SQL में डेटाबेस ऑब्जेक्ट के रूप में माना जाता है।
प्रक्रिया बनाम कार्य: मुख्य अंतर
प्रक्रिया | समारोह |
---|---|
मुख्य रूप से किसी निश्चित प्रक्रिया को निष्पादित करने के लिए उपयोग किया जाता है | मुख्य रूप से कुछ गणना करने के लिए उपयोग किया जाता है |
SELECT कथन में कॉल नहीं किया जा सकता | एक फ़ंक्शन जिसमें कोई DML कथन नहीं है उसे SELECT कथन में बुलाया जा सकता है |
मान लौटाने के लिए OUT पैरामीटर का उपयोग करें | मान लौटाने के लिए RETURN का उपयोग करें |
मान लौटाना अनिवार्य नहीं है | मान लौटाना अनिवार्य है |
RETURN बस उपप्रोग्राम से नियंत्रण से बाहर निकल जाएगा। | RETURN उपप्रोग्राम से नियंत्रण से बाहर निकल जाएगा और मान भी लौटाएगा |
निर्माण के समय रिटर्न डेटाटाइप निर्दिष्ट नहीं किया जाएगा | निर्माण के समय रिटर्न डेटाटाइप अनिवार्य है |
PL/SQL में अंतर्निहित फ़ंक्शन
PL / SQL स्ट्रिंग्स और डेट डेटाटाइप के साथ काम करने के लिए इसमें कई बिल्ट-इन फ़ंक्शन शामिल हैं। यहाँ हम आमतौर पर इस्तेमाल किए जाने वाले फ़ंक्शन और उनके उपयोग को देखने जा रहे हैं।
रूपांतरण फ़ंक्शन
इन अंतर्निहित फ़ंक्शनों का उपयोग एक डेटाटाइप को दूसरे डेटाटाइप में परिवर्तित करने के लिए किया जाता है।
फ़ंक्शन का नाम | प्रयोग | उदाहरण |
---|---|---|
TO_चर | अन्य डेटाटाइप को वर्ण डेटाटाइप में परिवर्तित करता है | TO_CHAR(123); |
TO_DATE ( स्ट्रिंग, प्रारूप ) | दिए गए स्ट्रिंग को दिनांक में बदलता है। स्ट्रिंग को प्रारूप से मेल खाना चाहिए। |
TO_DATE('2015-JAN-15', 'YYYY-MON-DD'); उत्पादन: 1 / / 15 2015 |
TO_NUMBER (पाठ, प्रारूप) |
पाठ को दिए गए प्रारूप के संख्या प्रकार में परिवर्तित करता है। सूचना '9' अंकों की संख्या को दर्शाता है |
डुअल से TO_NUMBER('1234′,'9999') चुनें;
उत्पादन: 1234 डुअल से TO_NUMBER('1,234.45′,'9,999.99') चुनें; उत्पादन: 1234 |
स्ट्रिंग फ़ंक्शंस
ये वे फंक्शन हैं जो कैरेक्टर डेटाटाइप पर उपयोग किए जाते हैं।
फ़ंक्शन का नाम | प्रयोग | उदाहरण |
---|---|---|
INSTR(पाठ, स्ट्रिंग, प्रारंभ, घटना) | दिए गए स्ट्रिंग में विशेष पाठ की स्थिति बताता है।
|
दोहरे से INSTR('AEROPLANE','E',2,1) का चयन करें
उत्पादन: 2 दोहरे से INSTR('AEROPLANE','E',2,2) का चयन करें उत्पादन: 9 (2 .)nd ई की घटना) |
SUBSTR (पाठ, प्रारंभ, लंबाई) | मुख्य स्ट्रिंग का उपस्ट्रिंग मान देता है.
|
डुअल से substr('aeroplane',1,7) चुनें
उत्पादन: एरोप्ला |
ऊपरी ( पाठ ) | प्रदान किए गए पाठ का अपरकेस लौटाता है | दोहरे से ऊपरी ('guru99') का चयन करें;
उत्पादन: गुरु99 |
निचला ( पाठ ) | प्रदान किए गए पाठ का लोअरकेस लौटाता है | दोहरे से निचले ('AerOpLane') का चयन करें;
उत्पादन: हवाई जहाज |
INITCAP ( पाठ) | दिए गए पाठ को प्रारंभिक अक्षर के साथ बड़े अक्षरों में लौटाता है। | दोहरे से ('guru99') का चयन करें
उत्पादन: गुरु99 दोहरे से ('मेरी कहानी') का चयन करें उत्पादन: मेरी कहानी |
लंबाई ( पाठ ) | दी गई स्ट्रिंग की लंबाई लौटाता है | दोहरी से लंबाई ('guru99') का चयन करें;
उत्पादन: 6 |
LPAD ( पाठ, लंबाई, पैड_चरित्र) | दिए गए अक्षर के साथ दी गई लंबाई (कुल स्ट्रिंग) के लिए बाईं ओर स्ट्रिंग को पैड करता है | डुअल से LPAD('guru99', 10, '$') चुनें;
उत्पादन: $$$$गुरु99 |
RPAD (पाठ, लंबाई, पैड_चरित्र) | दिए गए अक्षर के साथ दी गई लंबाई (कुल स्ट्रिंग) के लिए स्ट्रिंग को दाईं ओर पैड करता है | दोहरे से RPAD('guru99′,10,'-') का चयन करें
उत्पादन: गुरु99—- |
LTRIM ( पाठ ) | पाठ से प्रमुख सफेद स्थान को काटता है | दोहरे से LTRIM(' Guru99') का चयन करें;
उत्पादन: गुरु99 |
RTRIM ( पाठ ) | पाठ से अंतिम सफेद स्थान को काटता है | दोहरे से RTRIM('Guru99 ') का चयन करें;
उत्पादन; गुरु99 |
दिनांक फ़ंक्शन
ये वे फंक्शन हैं जिनका उपयोग तिथियों के साथ छेड़छाड़ करने के लिए किया जाता है।
फ़ंक्शन का नाम | प्रयोग | उदाहरण |
---|---|---|
ADD_MONTHS (तारीख, महीनों की संख्या) | दिए गए महीनों को तारीख में जोड़ता है | ADD_MONTH('2015-01-01',5);
उत्पादन: 05 / / 01 2015 |
SYSDATE | सर्वर की वर्तमान तिथि और समय लौटाता है | दोहरे से SYSDATE का चयन करें;
उत्पादन: 10/4/2015 2:11:43 अपराह्न |
TRUNC | दिनांक चर को न्यूनतम संभव मान पर पूर्णांकित करना | डुअल से sysdate, TRUNC(sysdate) का चयन करें;
उत्पादन: 10/4/2015 2:12:39 अपराह्न 10/4/2015 |
दौर | दिनांक को निकटतम सीमा तक पूर्णांकित करता है, या तो उच्चतर या निम्नतर | sysdate, ROUND(sysdate) को dual से चुनें
उत्पादन: 10/4/2015 2:14:34 अपराह्न 10/5/2015 |
महीनों_के बीच | दो तिथियों के बीच महीनों की संख्या लौटाता है | दोहरे से MONTHS_BETWEEN (sysdate+60, sysdate) का चयन करें
उत्पादन: 2 |
सारांश
इस अध्याय में हमने निम्नलिखित बातें सीखीं।
- प्रक्रिया कैसे बनाएं और इसे कॉल करने के विभिन्न तरीके
- फ़ंक्शन कैसे बनाएं और इसे कॉल करने के विभिन्न तरीके
- प्रक्रिया और कार्य के बीच समानताएं और अंतर
- PL/SQL उपप्रोग्राम में पैरामीटर और RETURN सामान्य शब्दावली
- सामान्य अंतर्निहित फ़ंक्शन Oracle PL / SQL