PLSQL के साथ काम करते समय कुछ सुझाव

मैंने पोस्ट को धन्यवाद देने के लिए छोटे नोटों की एक श्रृंखला लिखने का फैसला किया पीएल / एसक्यूएल में अच्छी आदतें , जिन सुझावों पर मैं विचार करूंगा।

इस भाग में हम चर्चा करेंगे:

1. % TYPE /% ROWTYPE की बारीकियाँ
2. अपडेट के लिए चयन करें
3. संग्रह के साथ काम करें

1.% TYPE /% ROWTYPE की मात्रा

एक परीक्षण मामला बनाएं - एक प्रकार की इंटेगर के साथ एक प्लेट बनाएं और इसमें एक परीक्षण मान जोड़ें:

create table test_integer(a integer );<br> create type integer_table as table of integer ;<br>/<br> insert into test_integer values (1E125);<br> commit ;<br> select * from test_integer;

जैसा कि आप देख सकते हैं, अर्थ पूरी तरह से डाला गया है। और अब इसे pl / sql ब्लॉक में प्राप्त करने का प्रयास करते हैं:

declare <br> l_a test_integer.a%type;<br> begin <br> select a into l_a from test_integer;<br> dbms_output.put_line( ' , =' || l_a);<br> exception <br> when others then <br> dbms_output.put_line( ' ! ' ||sqlerrm);<br> end ;<br>/

 सब बुरा है!  ORA-06502: PL / SQL: संख्यात्मक या मान त्रुटि: संख्या परिशुद्धता बहुत बड़ी है
 PL / SQL प्रक्रिया सफलतापूर्वक पूर्ण हुई।
 बीता हुआ: 00: 00: 00.02

त्रुटि का कारण इस तथ्य में निहित है कि SQL में INTEGER नंबर (*, 0) है - एक फ्लोटिंग-पॉइंट नंबर स्केल = 0 के साथ, और PL / SQL में INTEGER "SUBTYPE INTEGER IS NUMBER (38,0) है;"

उस लेख के प्रकार के बारे में क्या है " सृजित टाइप आईडी_लिस्ट ऑफ टेबल ऑफ इंटीजर ?"
स्वाभाविक रूप से, यह pl / sql पूर्णांकों का एक संग्रह है:

 DB1> घोषणा
   2 l_int_tab पूर्णांक_टेबल;
   3 शुरू
   4 एक का चयन करें
   5 बल्क l_int_tab में इकट्ठा होते हैं
   6 से test_integer;
   7 dbms_output.put_line ('सब कुछ ठीक है!');
   8 अपवाद तब जब अन्य
   9 dbms_output.put_line ('सब कुछ खराब है! अपवाद:' || sqlerrm)?
  10 अंत;
  11 /
 सब बुरा है!  अपवाद: ORA-06502: PL / SQL: संख्यात्मक या मूल्य त्रुटि: थोक बाँध: परिभाषित करने में त्रुटि

 PL / SQL प्रक्रिया सफलतापूर्वक पूर्ण हुई।

 बीता हुआ: 00: 00: 00.00

Id_list प्रकार जिसे मैंने पूर्णांक_ रूप में बनाया है, _TABLE पोस्टफिक्स के साथ संग्रह प्रकार बनाने के लिए सिर्फ मेरी इच्छा है। इसके अलावा, मैं आमतौर पर तालिका के प्रत्येक टेबल के लिए एक प्रकार नहीं बनाता हूं_नाम% पंक्तिपट्टी, लेकिन केवल अक्सर उपयोग किए जाने वाले या यदि इन प्रकार का उपयोग प्रक्रियाओं और कार्यों के मापदंडों में किया जाता है। वैसे, यदि आपके पास oracle <11g है और आप पैकेज में टाइप बनाते हैं और फिर इसे सर्किट लेवल ऑब्जेक्ट्स के पैरामीटर्स या फ़ील्ड्स में उपयोग करते हैं, तो यह स्वचालित रूप से "मल्टी-वैल्यूएबल" टाइप नामों के साथ सर्किट स्तर पर टाइप करता है: SYS -PLSQL_2906526_17_1, 11g में ये टाइप बस हैं छिपा हुआ है।

2. अद्यतन के लिए चयन करें

यह कोई रहस्य नहीं है कि अगर हम चयन में कुछ अपडेट करना चाहते हैं, तो हमें संपूर्ण पंक्तियों या फ़ील्ड्स को ब्लॉक करने के लिए अपडेट के लिए चयन का उपयोग करना चाहिए। उदाहरण के लिए, चर्चा के तहत लेख से उस प्रक्रिया में अद्यतन खंड के लिए कोई परिणाम नहीं है और परिणाम कर सकते हैं (उन कर्मचारियों के लिए वेतन में बदलाव, यदि विभाग एक समानांतर सत्र में बदल गया, एक और वेतनमान के समानांतर वेतन परिवर्तन के मामले में वेतन परिवर्तन का नुकसान 1 के अनुक्रम के आधार पर होता है) 1 या दूसरा सत्र), इसलिए इस तरह से फिर से लिखना चाहिए:

SELECT e.employee_id,e.salary,e.hire_date<br> FROM employee e <br> WHERE department_id = dept_in<br> FOR UPDATE OF e.salary;

ध्यान दें कि " ई.सालरी " का मतलब यह नहीं है कि केवल ई.सैलरी फील्ड को ब्लॉक किया जाएगा (यह असंभव है), ई अलियास के साथ टेबल की सभी पंक्तियों को ब्लॉक किया जाएगा:

OF के क्लॉज में कॉलम केवल यह दर्शाता है कि कौन सी टेबल या व्यू रो हैं।

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

create table test_integer<br>pctfree 99<br>pctused 1<br> as <br> select level id,<br> trunc(10*dbms_random. value ) val,<br> rpad( 'x' ,100) padding<br> from dual connect by level <=100<br>/<br> create index test_indeger_idx on test_integer(id)<br>/<br> exec dbms_stats.gather_table_stats(ownname => user ,tabname => 'TEST_INTEGER' , cascade => true );

देरी का अनुकरण एक समारोह होगा:

create or replace function integer_delay(p_i in integer ,p_interval in integer )<br> return integer <br> is <br> begin <br> dbms_lock.sleep(seconds => p_interval);<br> return p_i;<br> end ;<br>

और समानांतर सत्रों में चलाएं:
1:

begin <br> for rec in (<br> select --+ index (test_integer_idx)<br> id,val<br> from test_integer<br> where integer_delay(id,1) in (100,30,1)<br> order by id desc <br> for update <br> )<br> loop<br> dbms_output.put_line(rec.id);<br> end loop;<br> commit ;<br> end ;

2।

begin <br> for rec in (<br> select --+ index (test_integer_idx)<br> id,val<br> from test_integer<br> where integer_delay(id,1) in (100,30,1)<br> order by id desc <br> for update <br> )<br> loop<br> dbms_output.put_line(rec.id);<br> end loop;<br> commit ;<br> end ;

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

select * from test_integer where id in (1,3,4,8) for update ;

दूसरा सत्र:

select id<br> ,row_number() over ( order by id) rn<br> ,rownum<br> from test_integer t<br> where id < 10<br> order by id <br> for update skip locked

3. संग्रह के साथ काम करें

उस पोस्ट को पढ़ने में बहुत ही मजेदार लगा कि आपको हमेशा संग्रह के साथ काम करने की आवश्यकता है, और यहां तक ​​कि उन्हें पूरी तरह से संसाधित करें। "LOOP के लिए" और बल्क ऑपरेशन टॉम के लिए इष्टतम राशि यहां शामिल है और आपको अपनी विशिष्ट स्थिति में सीधे उस नंबर का पता लगाना है, जिसकी आपको आवश्यकता है।
FORALL के मामले में, एक साथ कई बिंदुओं को स्पष्ट करना आवश्यक है:

1. FORALL परमाणु नहीं है - अर्थात, यदि वह डेटा जो देखेगा वह उस समय नहीं है जब FORAL निष्पादन प्रारंभ होता है, लेकिन उस समय वह अपना विशेष सेट पुनर्प्राप्ति चक्र चलाता है, और यह बाद में अपडेट होगा जब यह तैयार संशोधित सेट प्राप्त करता है (यह पढ़ने के लिए प्रतिबद्ध है, के लिए) क्रमबद्ध हो जाओ ora-08177)।
   
   उदाहरण को फिर से test_ab से चलाते हैं, केवल सभी कॉलम के लिए पहले b = 1 को फिर से सेट करें।
   
   
   • हम पहला सत्र शुरू करते हैं:
     

     declare <br> type number_table is table of number;<br> <br> procedure bulk_update(p_nt in number_table) is <br> begin <br> forall n in p_nt. first ..p_nt. last <br> update test_ab<br> set b=b*10 <br> where a=p_nt(n)<br> and a=integer_delay(a,10);<br> end bulk_update;<br> begin <br> bulk_update(number_table(1,3,7,10));<br> end ;

     
     
   • और फिर कुछ सेकंड के बाद हम दूसरा सत्र शुरू करते हैं और चुपचाप उसी सेट से किसी भी बी को बदलते हैं:
     

     update test_ab set b=10 where a=7;

     
     जैसा कि आप देख सकते हैं, जब तक हम दूसरे लेनदेन को पूरा नहीं करते हैं, तब तक पहला ब्लॉक किया जाएगा, और इस मामले में दूसरे का मूल्य पहले लेनदेन में दिखाई देगा, हालांकि हमने इसे अंतिम लॉन्च किया था। आप निश्चित रूप से, तालिका test_ab को पहले ही लॉक कर सकते हैं, उदाहरण के लिए, अपडेट के लिए उसी चयन के माध्यम से, लेकिन क्या यह इतना सुविधाजनक है और क्या यह हमेशा आवश्यक है?
     
   
   
2. FORALL में निष्पादन योजनाओं का प्रबंधन जटिल है।
   विभिन्न एसक्यूएल वेरिएंट का उपयोग करने के मामले में, हम बड़े पैमाने पर अपडेट के लिए योजना को आसानी से ठीक कर सकते हैं, जो कि फॉरल के मामले में मुश्किल है, दूसरी ओर, यह संभव है कि कुछ भी नहीं सुधारा जाए (उदाहरण के लिए, इंडेक्स स्कैन के मामले में)।
   

सामान्य तौर पर, यह क्षण अस्पष्ट है और स्पष्ट रूप से सभी मामलों में एक बात की सलाह देना असंभव है।

पुनश्च। जारी रखने के लिए ...

लेख में कोड सोर्स कोड हाइलाइटर का उपयोग करके लिखा गया है।