कस्टम SQL सिंटैक्स सुविधाओं के कुछ उदाहरण

परिचय

लगभग डेढ़ साल पहले, मैंने OCP उन्नत PL / SQL डेवलपर के लिए परीक्षा उत्तीर्ण की, फिर काम की बारीकियों में थोड़ा बदलाव आया, और Oracle के मानक उत्पादन उपयोग के बाद, मैं कंप्यूटर भाषा विज्ञान की जरूरतों के लिए Oracle पर आधारित दो-स्तरीय क्लाइंट-सर्वर सिस्टम की वास्तुकला विकसित कर रहा था। इसके बाद प्रणाली के विकास का चरण और उसके आधार पर उच्च तकनीकी कार्यों का समाधान था; मुझे गैर-मानक समस्याओं और अन्य विशिष्ट चीजों को हल करने में पदानुक्रमित प्रश्नों के उपयोग से निपटना था। बारीकियों को गहरा करने का परिणाम आधार का एक निश्चित "उप-साक्ष्य" था, जिसका अर्थ है कि परीक्षा की तैयारी के लिए उपयोग की जाने वाली सामग्रियों को फिर से देखने का समय आ गया है।

नीचे sql प्रश्नों का उपयोग करने के कुछ गैर-मानक उदाहरण दिए गए हैं। ऐसे उदाहरण आमतौर पर अमूर्त को देखते समय ध्यान में आते हैं, जांचे जाते हैं, अन्य विशेषज्ञों के साथ चर्चा की जाती है और भुला दिया जाता है। इस बार मैं उनमें से कुछ को sql फ़ाइल में सहेजना चाहता था, बाद में प्रत्येक अनुरोध के लिए टिप्पणियों की आवश्यकता स्पष्ट हो गई। तो यह नोट दिखाई दिया।

ऑपरेटर द्वारा आदेश की बारीकियों के बारे में कुछ शब्द

क्या आपको लगता है कि इस अनुरोध को निष्पादित करने के परिणामस्वरूप कोई त्रुटि होगी?

-- №1 select * from dual order by 1+2||dummy 

उत्तर: कोई त्रुटि नहीं होगी, क्योंकि विनिर्देश द्वारा आदेश निम्नानुसार परिभाषित किया गया है:
ORDER BY {col (s) | अभिव्यक्ति | संख्यात्मक_पोस} [ASC | DESC] [NULLS FIRST | LAST] |
जहां अभिव्यक्ति - एक प्रकार की कुंजी अभिव्यक्ति, जैसे कि संख्यात्मक, स्ट्रिंग और डेटाइम अभिव्यक्ति। अभिव्यक्ति एक पंक्ति मूल्य अभिव्यक्ति भी हो सकती है जैसे कि एक स्केलर सबक्वेरी या केस एक्सप्रेशन।
विवरण के लिए, उदाहरण के लिए, यहाँ डाउनलोड करें ।oracle.com/javadb/10.6.2.1/ref/ rrefsqljj65656.html

इस प्रकार, हमने "3X" अभिव्यक्ति द्वारा दोहरी तालिका को सॉर्ट किया, जो कि व्यर्थ है, हालांकि, उदाहरण के लिए, आप छँटाई के लिए अभिव्यक्ति के रूप में रूट फ़ंक्शन के साथ अभिव्यक्ति का उपयोग कर सकते हैं। भावों का उपयोग करने की क्षमता महत्वपूर्ण है।

जैसा कि हम जानते हैं, दोहरी तालिका में एक कॉलम है, चाहे निम्नलिखित क्वेरी में कोई त्रुटि होगी:

 -- №2 select * from dual order by 2 

और यह एक?

 -- №3 select * from dual order by 2+0 

पहले मामले में, हम स्तंभों की स्थिति संकेत के साथ काम कर रहे हैं - अर्थात्। कॉलम नंबर 2 के संदर्भ में, जो क्रमशः मौजूद नहीं है, हमें एक त्रुटि मिलती है।

दूसरे मामले में, हम एक अभिव्यक्ति के साथ काम कर रहे हैं, अर्थात्। यह कॉलम नंबर नहीं है, लेकिन क्रमांक 1 में "3X" लाइन द्वारा छंटनी के समान, संख्या 2 से छंटनी है। इसलिए, कोई त्रुटि नहीं होगी।

निम्नलिखित उदाहरण पर एक नज़र डालें। इस क्वेरी के परिणामस्वरूप हमें क्या पंक्ति क्रम मिलता है? क्या कोई गलती होगी?

 -- №4 select 3, dummy from dual union all select 2, dummy from dual order by "3" 

यूनियन सभी यूनियन सेट्स (यूनियन के विपरीत) की पंक्तियों को क्रमबद्ध नहीं करते हैं, अर्थात्। आदेश के बिना, हमें अनुरोध में निर्दिष्ट पंक्तियाँ मिलती हैं (संघ सभी मूल पंक्ति क्रम के संरक्षण की गारंटी देता है)। अब यह मुख्य प्रश्न है कि इस मामले में "3" क्या है? चूंकि हम दोहरे उद्धरण चिह्नों का उपयोग करते हैं "एकल उद्धरणों के बजाय," 3 "स्तंभ का उपनाम है। जैसा कि आप जानते हैं, सेट के साथ संचालन में समान डेटा प्रकारों के उपयोग की आवश्यकता होती है, और कॉलम नाम पहली क्वेरी से लिए जाते हैं, क्योंकि हमने स्पष्ट रूप से पहले कॉलम का नाम निर्दिष्ट नहीं किया था। , फिर, डिफ़ॉल्ट रूप से, इसे अभिव्यक्ति का नाम प्राप्त हुआ, अर्थात्, "3." ऐसे उपनामों के साथ काम दिखाया गया है, उदाहरण के लिए, अनुरोध संख्या 5 में (मुख्य बात ऐप मामले के बारे में नहीं भूलना है)।

डिफ़ॉल्ट सॉर्ट हमेशा asc होता है, अर्थात क्वेरी परिणाम # 4 पहले कॉलम द्वारा आरोही क्रम में क्रमबद्ध हैं। परिणाम: लाइन "2, एक्स", फिर "3, एक्स"।

 -- №5 select substr(dummy,1,1) from dual order by "SUBSTR(DUMMY,1,1)" 

सेट पर क्वेरी नंबर 3 से प्रयोग दोहराएं। क्वेरी का परिणाम क्या होगा?

 -- №6 select 3, dummy from dual union all select 2, dummy from dual order by 2+0 

ऐसा लग सकता है कि क्वेरी का परिणाम निम्नानुसार होगा: लाइन "3, एक्स" फिर "2, एक्स"। इसके लिए आवश्यक शर्तें निम्नानुसार हैं: यह आदेश सभी को यूनियन की गारंटी देता है, और अभिव्यक्ति 2 + 0 रिकॉर्ड की छंटाई को प्रभावित नहीं करेगा (जैसा कि क्वेरी नंबर 7 में दिखाया गया है)। तो यह होगा, अगर एक "लेकिन" के लिए नहीं: सेट के साथ संचालन में, आदेश द्वारा पहली क्वेरी के नाम या स्तंभ संख्या के साथ एक समग्र क्वेरी के अंत में उपयोग किया जा सकता है, तो अभिव्यक्ति की अनुमति नहीं है:
सेट ऑपरेटर UNION, INTERSECT, MINUS, या UNION ALL वाले यौगिक प्रश्नों के लिए, ORDER BY क्लॉज को स्पष्ट भावों के बजाय स्थान या उपनाम निर्दिष्ट करना चाहिए। साथ ही, ORDER BY खंड अंतिम घटक क्वेरी में ही दिखाई दे सकता है। ORDER BY क्लॉज पूरे कंपाउंड क्वेरी द्वारा लौटी सभी पंक्तियों को आदेश देता है। download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries005.htm#i2053998

इसलिए, क्वेरी 6 नंबर एक त्रुटि लौटाएगा।

 -- №7 select * from (select 3, dummy from dual union all select 2, dummy from dual) order by 2+0 

में उपयोग नहीं

निश्चित रूप से, अधिकांश लोग जो विभिन्न पाठ्यक्रमों से गुजरे हैं, उन्हें याद है कि ऑपरेटर में नहीं बचा जाना चाहिए, और इसी तरह की कार्यक्षमता का उपयोग करके या मौजूद ऑपरेटरों से प्राप्त किया जा सकता है। इस नकारात्मक रवैये का कारण अशक्त मूल्यों के साथ उनके काम की बारीकियों में निहित नहीं है।

निम्नलिखित प्रश्न का परिणाम निर्धारित करें:

 -- №1 select * from dual where 1 not in (select 2 from dual union select null from dual) 

सबसे पहले, इस प्रश्न पर विचार करें:

 -- №2 select * from dual where 2 in (select 2 from dual union select null from dual) 

प्रश्न संख्या 2 में कुछ भी असामान्य नहीं है: उपश्रेणी "2" और "अशक्त" मानों के साथ दो पंक्तियों का एक सेट लौटाती है, जहां स्थिति सही है, पूरे क्वेरी नंबर 2 में 1 पंक्ति - मानक व्यवहार है।

अब में और नहीं के व्यवहार में अंतर पर विचार करें, यहाँ हमें शून्य अंकगणित के ज्ञान की आवश्यकता है:
1) अशक्त वापसी नल के साथ कोई अंकगणितीय संचालन
2) बूलियन ऑपरेटरों के साथ अशक्त काम निम्नानुसार हैं:

• null and false = false
• झूठी और अशक्त = झूठी
• null and true = null
• सच्चा और अशक्त = अशक्त
• null or true = true
• सच्चा या अशक्त = true
• null or false = null
• झूठी या अशक्त = अशक्त

अब तक, सब कुछ तार्किक है। अब विचार करें कि IN # 2 में कैसे काम करता है:
2 IN (2, null) => (2 = 2) OR (2 = null) => सत्य या अशक्त => सत्य
प्रश्न संख्या 1 में NOT के काम पर विचार करें:
1 NOT (2, null) => (1! = 2) AND (1! = Null) => सत्य और अशक्त => null

इस प्रकार, जहां क्वेरी # 1 में खंड को शून्य में बदल दिया जाता है यदि कम से कम एक ऑपरेंड शून्य है, तो संपूर्ण क्वेरी # 1 लगभग निम्नलिखित क्वेरी के बराबर है:

 -- №3 select * from dual where null=null 

जाहिर है, क्वेरी नंबर 3 क्रमशः एक पंक्ति नहीं लौटेगी, क्वेरी नंबर 1 भी एक पंक्ति नहीं लौटेगी।

निहित प्रकार रूपांतरण

स्पष्ट और निहित प्रकार के रूपांतरण का विषय बहुत व्यापक है, इसलिए, इसे समग्र रूप से कवर करने की कोशिश किए बिना, मैं केवल एक उदाहरण पर विचार करना चाहूंगा। आज 10.09.11 10:00:00 और Oracle को कॉन्फ़िगर किया गया है ताकि DD.MM.RR प्रारूप को डिफ़ॉल्ट रूप से पहचाना जाए, कौन सी क्वेरी एक पंक्ति में वापस आएगी?

 -- №1 select * from dual where sysdate>'10.09.11'; -- №2 select * from dual where sysdate||''='10.09.11'; 

जवाब दोनों है। यह आम तौर पर क्यों और कैसे संभव है? उत्तर निहित प्रकार रूपांतरण के तंत्र में निहित है।

क्वेरी नंबर 1 पर विचार करें: जहां हम स्ट्रिंग के साथ दिनांक की तुलना करते हैं, इस मामले में Oracle स्ट्रिंग को तिथि में बदलने की कोशिश करता है यदि स्ट्रिंग प्रारूप डिफ़ॉल्ट तिथि स्वरूपों में से एक से मेल खाता है (सत्र के लिए डिफ़ॉल्ट तिथि प्रारूप को NLS_DATE_FORMAT पैरामीटर में क्वेरी चलाकर देखा जा सकता है
nls_session_parameters से) का चयन करें। यदि स्ट्रिंग प्रारूप डिफ़ॉल्ट तिथि प्रारूप के अनुरूप नहीं है, तो हमें एक त्रुटि मिलेगी। हमारे मामले में, प्रारूप मेल खाते हैं और स्ट्रिंग '10 .09.11 'को दिनांक 09/10/11 00:00:00 में बदल दिया जाता है, क्योंकि sysdate = 09/10/11 10:00:00, फिर क्वेरी नंबर 1 1 पंक्ति में वापस आ जाएगा।

क्वेरी नंबर 2 पर विचार करें: जहां तारीख को स्ट्रिंग के साथ समाहित किया जाता है, ऐसे ऑपरेशन का परिणाम एक स्ट्रिंग होगा, और जब तारीख को एक स्ट्रिंग में बदल दिया जाता है, तो डेटा को DD.MM.RR प्रारूप में काट दिया जाता है, अर्थात हम दो पंक्तियों '10 .09.11 'और '10 .09.11' की तुलना करते हैं। नतीजतन, जहां क्लॉज संतुष्ट है, और क्वेरी एकल पंक्ति देता है।

निष्कर्ष

यह समीक्षा ओरेकल कार्यक्षमता का कोई भी कवरेज प्रदान करने का दिखावा नहीं करती है, यह मनोरंजन के लिए बनाया गया था और इसका उद्देश्य एसक्यूएल सिंटैक्स सुविधाओं के गैर-मानक उपयोग के कई मामलों को प्रदर्शित करना है।