Theory
Queries Copy-Paste કરવાનું દુઃસ્વપ્ન
કલ્પો કે તમે TicketDesk system સંભાળો છો. જ્યારે પણ એક agent ને એક ticket બંધ કરવું હોય, એમણે ત્રણ અલગ SQL statements ચલાવવાં પડે: ticket નું status update કરવું, resolution નો timestamp ગોઠવવો, અને એક audit log માં એક હરોળ નાખવી. જો TicketDesk માટે 50 જુદા developers code લખતા હોય, એ બધાએ બિલકુલ આ જ queries copy, paste, અને ચલાવવી પડે. જો કોઈ audit log નું પગલું ભૂલી જાય તો શું થાય? Database નો data બગડી જાય છે. તો આપણે આ તાર્કિક ક્રમને database ની અંદર જ કાયમ માટે કેવી રીતે જકડીએ?
Theory
રસોડાનું Food Processor
Raw SQL statements ને દરરોજ હાથે અલગ અલગ શાક સમારવા, પાણી ઉકાળવા, અને મસાલા વઘારવા જેવાં વિચારો. એક PL/SQL subprogram એ custom જોડાણનાં બટન વાળા આધુનિક food processor જેવો છે. અલગ અલગ હાથે કરવાનાં કામ દોહરાવવાને બદલે, તમે તમારી સામગ્રી અંદર મૂકો છો, 'Make Soup' બટન દબાવો છો, અને પહેલેથી program કરેલા સાધનને આંતરિક પગલાં આપોઆપ સંભાળવા દો છો. એક package એ ખાલી રસોડાનું કબાટ છે જે આ બધાં ખાસ સાધનોને એક જ સાફ જગ્યાએ સુઘડ રીતે વ્યવસ્થિત રાખે છે.
Theory
નામવાળા PL/SQL Subprograms અને Packages
Oracle database ના dialect માં, એક stored procedure એ નામવાળો PL/SQL block છે જે એક ચોક્કસ ક્રિયા કરે છે અને માંગ પ્રમાણે ચલાવી શકાય છે. એક stored function એ મળતો આવતો ફરીથી વાપરી શકાય એવો block છે જે મુખ્યત્વે એક RETURN clause દ્વારા એક જ value ગણવા અને પાછી આપવા રચાયેલો છે. એક package એ બે ભાગનું schema object છે જે તાર્કિક રીતે સંબંધિત procedures, functions, variables, અને exceptions ને એક જ modular ડબ્બામાં જૂથબદ્ધ કરે છે. અનામી blocks થી વિપરીત, આ નામવાળા એકમો એક વાર compile થાય છે અને system ના catalog માં કાયમ માટે સંઘરાય છે.
At a glance
Oracle PL/SQL ના modular ઘટકો વચ્ચેના સંરચનાત્મક ભેદ
| લક્ષણ | Stored Procedure | Stored Function | PL/SQL Package |
|---|---|---|---|
| મુખ્ય હેતુ | જટિલ business ની ક્રિયાઓ ચલાવે છે | એક જ value ગણે છે | સંબંધિત objects ને સાથે જૂથબદ્ધ કરે છે |
| પાછી અપાતી Value | OUT parameters દ્વારા શૂન્ય કે અનેક પાછી આપે છે | RETURN clause દ્વારા બિલકુલ એક value પાછી આપવી જ પડે | સીધી values પાછી આપતું નથી |
| SQL Select માં? | SELECT ની અંદર સીધું બોલાવી શકાતું નથી | SELECT statements ની અંદર સીધું જડી શકાય છે | એની અંદરના subprograms પોતાના નિયમો પાળે છે |
Practical
Creating the Ticket Closure Procedure
-- Enable console output
SET SERVEROUTPUT ON;
-- Create the procedure to automate ticket closure
CREATE OR REPLACE PROCEDURE close_ticket(
p_ticket_id IN NUMBER,
p_status OUT VARCHAR2
) IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM tickets WHERE id = p_ticket_id;
IF v_count = 0 THEN
p_status := 'NOT FOUND';
ELSE
UPDATE tickets SET status = 'Closed' WHERE id = p_ticket_id;
p_status := 'SUCCESS';
DBMS_OUTPUT.PUT_LINE('Ticket ' || p_ticket_id || ' closed successfully.');
END IF;
END;
/Theory
close_ticket ની Syntax ને ઉકેલવી
Code ને ધ્યાનથી જુઓ. આપણે CREATE OR REPLACE વાપરીએ છીએ જેથી પછીથી procedure ને પહેલાં કાઢી નાખ્યા વગર બદલી શકીએ. Parameters નોંધો: p_ticket_id IN mode વાપરે છે કારણ કે એ procedure માં data લાવે છે, જ્યારે p_status બોલાવનારા વાતાવરણને પરિણામ પાછું મોકલવા OUT mode વાપરે છે. અનામી blocks થી વિપરીત, આપણે variable ની વ્યાખ્યાની જગ્યા શરૂ કરવા DECLARE ને બદલે IS keyword વાપરીએ છીએ. Compilation server ની બાજુએ થાય છે, જે execution ને વીજળીની ઝડપે બનાવે છે.
Quiz
કયો parameter mode એક PL/SQL stored procedure ને બોલાવનાર પાસેથી એક શરૂઆતની value સ્વીકારવા અને એ જ બોલાવનારને એક બદલાયેલી value પાછી આપવા બંને દે છે?
- IN
- OUT
- IN OUT
- RETURN
Show the answer
IN OUT
IN OUT parameter mode એ બે-માર્ગી રસ્તો છે: એ subprogram માં એક શરૂઆતની value આપે છે અને subprogram ને એ જ variable દ્વારા એક નવી value ફરીથી લખવા અને પાછી આપવા દે છે. RETURN clause functions માટે અનન્ય છે, parameter modes માટે નહીં.
Watch out
બે ભાગના Package નું છૂટું પડવું
University ની lab exams માં સૌથી સામાન્ય ભૂલ છે package નું body લખ્યા વગર package નું specification લખવું, કે એથી ઊલટું. યાદ રાખો કે એક Oracle package ના બે અલગ ઘટકો હોય છે. Specification એ જાહેર ચહેરો છે જે મથાળાં અને parameters જાહેર કરે છે. Body માં ખરેખરો છુપાયેલો code નો અમલ હોય છે. જો તમે તમારા package ના specification માં એક procedure જાહેર કરો પણ package ના body ની અંદર એનો બિલકુલ code લખવાનું ભૂલી જાઓ, તમારો code INVALID ના object ના status સાથે compile થવામાં નિષ્ફળ જશે.
Think first
SELECT Queries ની અંદર Procedures
જો તમે એક standard SQL SELECT statement ની અંદર સીધો એક table ની હરોળ update કરતો stored procedure બોલાવવાનો પ્રયાસ કરો, તો શું થશે? Tap કરતાં પહેલાં મનમાં SQL ના execution ના નિયમો વિચારો.
Show the answer
Oracle એક ગંભીર runtime execution ની error ફેંકશે! Standard SELECT queries સખ્તાઈથી માત્ર વાંચવા માટેની છે અને એમને database ની સ્થિતિ બદલતી આડઅસરો કરવાની પરવાનગી નથી. માત્ર એ જ stored functions જે database ના tables બદલતાં નથી એ સીધાં એક SELECT query ના statement ની અંદર ચલાવી શકાય છે.
Theory
Enterprise APIs અને આગલા Semester ના Objects
Production ના enterprise વાતાવરણોમાં, વ્યાવસાયિક backend developers ક્યારેય raw database tables mobile કે web ના clients સામે ખુલ્લાં મૂકતા નથી. એને બદલે, એ packages અને procedures ને સુરક્ષિત APIs તરીકે ખુલ્લાં મૂકે છે. તમે આગલા semester માં તમારા Sem 3 BCA303 ના SQLite mobile course માં આ ડિઝાઇનનું ધોરણ પાછું જોશો. Modular packages બનાવવું તમને Java અને C++ માં classes, જાહેર interfaces, અને ખાનગી encapsulation ની પદ્ધતિઓ જેવા Object-Oriented Programming ના ખ્યાલો માટે સીધા તૈયાર કરે છે.
Summary
Key takeaways
- Stored procedures માંગ પ્રમાણે જટિલ business ની કામગીરી ચલાવે છે અને વાત કરવા IN કે OUT parameters વાપરે છે.
- Stored functions માં એક RETURN clause હોવો જ જોઈએ અને એ એક જ value ગણવા અને પાછી આપવા optimized છે.
- Packages બે ભાગના ડબ્બા તરીકે કામ કરે છે જે જાહેર specifications ને ખાનગી છુપાયેલા bodies થી અલગ પાડે છે.
- નામવાળા subprograms એક વાર compile થાય છે અને કાયમી database ના data dictionary ના catalog ની અંદર સંઘરાય છે.
- Stored objects વાપરવાથી બેવડો network નો traffic ઘટે છે અને data ની logic કેન્દ્રિત કરીને data ની અખંડિતતા બચે છે.
- Memory hook: ક્રિયાઓ માટે Procedure, values માટે function, ખોખાં માટે package, અને ચલાવતાં પહેલાં compile કરો!