DBMS_SQL का उपयोग कर CSV को एसक्यूएल

अक्सर, सिस्टम एकीकरण समस्याओं को हल करते समय, एक प्रारूप या किसी अन्य में एक निश्चित मात्रा में डेटा पेश करना आवश्यक होता है। उसी समय, कोई भी डेटा उपभोक्ता हो सकता है, लेकिन स्रोत लगभग हमेशा एक कॉर्पोरेट डेटाबेस होता है। उदाहरण के लिए, एक निर्माता को आपूर्तिकर्ता को एक्सएलएसएक्स या एक्सएमएल प्रारूप आदि में अपने माल की आवाजाही पर समय-समय पर रिपोर्ट करने की आवश्यकता हो सकती है।

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

यदि हम मानते हैं कि वास्तव में, SQL क्वेरी डेटा चयन प्रक्रिया के मूल में है, तो आदर्श रूप से परिवर्तनों की श्रृंखला यह देखने के लिए वांछनीय होगी:

$$

$$d '= f (SQL (d))$$

जहाँ
$$$ड$ - स्रोत डेटा,
$$$SQL (d)$ - डेटा प्राप्त करने के लिए SQL क्वेरी,
$$$च$ - एक फ़ंक्शन जो चयन को वांछित प्रारूप में परिवर्तित करता है,
$$$d '$ - आवश्यक प्रारूप में डेटा।

Oracle PL / SQL के लिए, कई अंतर्निहित और तृतीय-पक्ष पैकेज हैं जो इस कार्यक्षमता को लागू करते हैं। ये हैं DBMS_XMLGEN, DBMS_XMLQUERY, AS_XLSX, PL / JSON और अन्य।

हालांकि, जब सवाल सीएसवी प्रारूप में डेटा परिवर्तित करने के बारे में उठता है, तो किसी कारण से कोई तैयार समाधान नहीं थे। मुझे इसे खुद करना था, फिर दिखाया जाएगा कि कैसे।

समस्या का बयान

एक उपकरण (पीएल / एसक्यूएल पैकेज) बनाएँ जो इनपुट के रूप में एक स्ट्रिंग के रूप में या कर्सर चर के रूप में एक मनमाना चयन क्वेरी प्राप्त करता है, और आउटपुट पर सीएसवी प्रारूप में डेटा टाइप करने वाली CLOB प्रकार की वस्तु देता है। किसी भी त्रुटि के मामले में, NULL को लौटाया जाना चाहिए। CSV प्रारूप को स्वयं का प्रतिनिधित्व करने की आवश्यकता नहीं है - ये ऐसे तार हैं जिनके तत्व किसी न किसी चरित्र द्वारा अलग किए जाते हैं, सबसे अधिक बार ";", लेकिन सामान्य स्थिति में एक मनमाना चरित्र एक विभाजक के रूप में कार्य कर सकता है। मान लें कि वर्ण 0x0D + 0x0A का उपयोग स्ट्रिंग्स को अलग करने के लिए किया जाता है। CSV फ़ाइल में पहली पंक्ति आमतौर पर हेडर होती है और कॉलम नामों को परिभाषित करती है।

पैकेज इंटरफ़ेस को परिभाषित करें

CREATE OR REPLACE PACKAGE pp_csv AS PROCEDURE query2sheet( stmt IN VARCHAR2, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); PROCEDURE query2sheet( ref_cursor IN OUT SYS_REFCURSOR, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); END; 

दो अतिभारित प्रक्रियाएं हैं, उनके बीच का अंतर यह है कि उनमें से एक स्ट्रिंग के रूप में अनुरोध प्राप्त करता है, और दूसरा कर्सर के लिंक के रूप में। दूसरा पैरामीटर आउटपुट है; यह CLOB ऑब्जेक्ट में वांछित परिणाम है। अंत में, तीसरा पैरामीटर CSV सेपरेटर है।

इन प्रक्रियाओं के कार्यान्वयन में, अंतर्निहित DBMS_SQL पैकेज हमारी मदद करेगा, जो हमें गतिशील कर्सर के साथ काम करने की अनुमति देता है जब यह अग्रिम में (संकलन चरण में) ज्ञात नहीं है कि चयन में कितने कॉलम शामिल हैं।

DBMS_SQL विशेषताएं आपको गतिशील रूप से पार्स करने और मनमाने प्रश्नों को निष्पादित करने की अनुमति देती हैं। ऐसी प्रक्रिया के लिए जो एक अनुरोध को एक स्ट्रिंग के रूप में स्वीकार करती है, यह इस तरह होता है:

 AS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; --     DBMS_SQL ignore INTEGER; BEGIN DBMS_SQL.PARSE(cur, stmt, DBMS_SQL.NATIVE); ignore := DBMS_SQL.EXECUTE(cur); 

एक कर्सर चर लेने वाली प्रक्रिया के लिए, सब कुछ सरल है - Oracle के 11 वें संस्करण से शुरू होकर, रूपांतरण "कर्सर चर → ​​SQL कर्सर नंबर" उपलब्ध हो गया है।

DBMS_SQL.TO_CURSOR_NUMBER फ़ंक्शन REFCURSOR चर (दृढ़ता से या कमजोर रूप से टाइप किया गया) को SQL कर्सर नंबर में परिवर्तित करता है, जिसे बाद में DBMS_SQL दिनचर्या में परिवर्तित किया जा सकता है। इस स्थिति में, कर्सर चर को DBMS_SQL.TO_CURSOR_NUMBER फ़ंक्शन पर जाने से पहले खुला होना चाहिए।

 cur PLS_INTEGER := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor); 

इस प्रकार, डायनेमिक कर्सर की संख्या और संबंधित डेटा सेट प्राप्त करने के लिए कॉल के दोनों संस्करण नीचे आए। अगला कदम इस सेट के कॉलम के बारे में जानकारी प्राप्त करना और डेटा को स्वयं निकालना है।

DMBS_SQL पैकेज आपको एक गतिशील कर्सर के कॉलम का वर्णन करने की अनुमति देता है, जो प्रत्येक स्तंभ के बारे में जानकारी को अभिलेखीय सारणी में दर्ज करता है।

ऐसा करने के लिए, आपको संग्रह प्रकार DBMS_SQL.DESC_TAB (या DESC_TAB2 पर यदि क्वेरी 30 से अधिक वर्ण स्तंभ नाम वापस कर सकते हैं) के आधार पर PL / SQL संग्रह की घोषणा करनी चाहिए। उसके बाद, आप तालिका पर पुनरावृति करने और कर्सर जानकारी प्राप्त करने के लिए संग्रह विधियों का उपयोग कर सकते हैं।

 AS cols DBMS_SQL.DESC_TAB2; ncols NUMBER; --     col_val_chr VARCHAR2(32767); BEGIN DBMS_SQL.DESCRIBE_COLUMNS2(cur, ncols, cols); 

इसके बाद, DBMS_SQL पैकेज को डायनामिक क्वेरी का उपयोग करके चयनित प्रत्येक कॉलम के प्रकार की जानकारी दी जानी चाहिए। यह DEFINE_COLUMN कहकर किया जाता है।

 FOR i IN 1 .. ncols LOOP DBMS_SQL.DEFINE_COLUMN(cur, i, col_val_chr, 32767); END LOOP; 

दूसरे तर्क DEFINE_COLUMN में, एक संख्या पारित की गई है - सूची में कॉलम की अनुक्रमिक स्थिति। तीसरा तर्क कर्सर कॉलम के डेटा प्रकार को सेट करता है। यह उपयुक्त प्रकार की अभिव्यक्ति को पारित करता है। दूसरे शब्दों में, DBMS_SQL.DEFINE_COLUMN एक प्रकार के नाम के साथ एक स्ट्रिंग नहीं है (जैसे, "VARCHAR2"), लेकिन एक प्रकार VARCHAR2 के साथ परिभाषित चर।

चौथे तर्क में एक वर्ण प्रकार के कॉलम को परिभाषित करते समय, आपको उन मानों की अधिकतम लंबाई निर्दिष्ट करनी होगी जिन्हें कर्सर में लोड किया जा सकता है।

प्रारंभिक ऑपरेशन पूरा हो गया है, अब आप एक हेडर लाइन बना सकते हैं और डेटा निकालना शुरू कर सकते हैं।

 AS cap CLOB; --    CSV- BEGIN DBMS_LOB.CREATETEMPORARY(cap, TRUE, DBMS_LOB.SESSION); FOR i IN 1 .. ncols LOOP DBMS_LOB.APPEND(cap, cols(i).col_name || delimeter); END LOOP; 

अलग-अलग कॉलम के मान प्राप्त करने के लिए DBMS_SQL.FETCH_ROWS और बाद के कॉल DBMS_SQL.COLUMN_VALUE का उपयोग करके डेटा को पंक्ति द्वारा प्राप्त किया जाता है।

 AS bod CLOB; --   CSV- c_line_break CONSTANT VARCHAR2(2) := chr(13) || chr(10); BEGIN DBMS_LOB.CREATETEMPORARY(bod, TRUE, DBMS_LOB.SESSION); WHILE DBMS_SQL.FETCH_ROWS(ur) > 0 LOOP FOR i IN 1 .. ncols LOOP DBMS_SQL.COLUMN_VALUE(cur, i, col_val_chr); DBMS_LOB.APPEND(bod, col_val_chr || delimeter); END LOOP; DBMS_LOB.APPEND(bod, c_line_break); END LOOP; 

तब यह केवल परिणामी CSV को इकट्ठा करने के लिए ही रहता है

 DBMS_LOB.APPEND(sheet, cap); DBMS_LOB.APPEND(sheet, c_line_break); DBMS_LOB.APPEND(sheet, bod); 

त्रुटियों को संभालें

 EXCEPTION WHEN OTHERS THEN sheet := NULL; 

और कर्सर बंद करें

 DBMS_SQL.CLOSE_CURSOR(cur); 

पैकेज उपयोग विकल्प

 DECLARE csv CLOB; BEGIN DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet('SELECT empcode, fio FROM employee WHERE ROWNUM < 10', csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END; 

 DECLARE csv CLOB; cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT empcode, fio FROM employee WHERE ROWNUM < 10; DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet(cur, csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END; 

यह, वास्तव में, सभी है, स्रोत कोड संलग्न है ।

किताब ने विकास में मदद की
फुएरस्टीन एस।, अराइव्ड बी - ओरेकल पीएल / एसक्यूएल। पेशेवरों के लिए।