PL/SQL (Procedural Language/Structured Query Language) je procedurální nadstavba jazykaSQL firmyOracle založená na programovacím jazykuAda.
Tato nadstavba se rozšířila a její deriváty převzaly i jiné relační databáze.Sybase aMicrosoft SQL Server majíTransact-SQL,PostgreSQL máPL/pgSQL aIBM DB2 máSQL PL. Existuje též projektFyracle, jehož cílem je umožnit spouštění PL/SQL v relační databáziFirebird.
PL/SQL přidává k jazyku SQL konstrukceprocedurálního programování. Výsledkem jestrukturální jazyk, mocnější než samotné SQL. Základním stavebním kamenem v PL/SQL je blok. Program v PL/SQL se skládá z bloků, které mohou být vnořeny jeden do druhého. Obyčejně každý blok spouští jednu logickou akci v programu. Blok má následující strukturu:
DECLARE/* Deklarace obsahuje proměnné, typy a lokální subprogramy. */BEGIN/* Výkonná sekce: zde běží procedury a SQL kód. *//* Toto je jediná sekce, která je v bloku povinná. */EXCEPTION/* Oblast zpracování výjimek: zde se zpracovávají chybové události. */END;
Pouze výkonná sekce je povinná, ostatní jsou doporučené. Jediné příkazy jazyka SQL, které jsou ve výkonné sekci povolené, jsou SELECT, INSERT, UPDATE, DELETE a několik dalších pro manipulaci s daty a pro kontrolu transakcí. Definiční příkazy jazyka SQL jako CREATE, DROP nebo ALTER nejsou povoleny. PL/SQL není citlivé na velikost písmen a mohou být použity komentáře ve stylu jazyka C.
Proměnné můžeme rozdělit do několika skupin, dle různých kritérií, nejčastějším dělením je podle datového typu na číselné (NUMBER, PLS_INTEGER, SIMPLE_INTEGER..), stringové (CHAR, VARCHAR2..), datumové (DATE, TIMESTAMP..), typové proměnné (RECORD) a proměnné typu LOB (large object). Na rozdíl například od jazyka Java zde dochází k implicitní datové konverzi mezi jednotlivými typy (pokud to hodnota dovoluje). Dále lze rozdělit datové typy na nativní (PLS_INTEGER, DOUBLE_FLOAT..), které jsou počítány v hardware a na ty, které jsou interpretované (NUMBER) a jsou počítány pomocí vnitřní logiky databáze.
Například definujeme proměnnou part_no jako čtyřmístné číslo a in_stock jako logickou proměnnou:
part_noNUMBER(4);in_stockBOOLEAN;
Hodnoty můžeme přiřazovat třemi způsoby, prvním způsob je pomocí operátoru (:=):
in_stock:=FALSE;
Druhý způsob je vybrání hodnoty z databáze a její přímé přiřazení do proměnné (v tomto případě je onou proměnnoubonus), např.:
SELECTsal*0.10INTObonusFROMempWHEREempno=emp_id;
Třetí způsob přiřazuje hodnotu proměnné pomocí podprogramu:
DECLAREmy_salREAL(7,2);PROCEDUREadjust_salary(emp_idINT,salaryINOUTREAL)IS..BEGINSELECTAVG(sal)INTOmy_salFROMemp;adjust_salary(7788,my_sal);
Konstanty se deklarují stejně jako proměnné, ale navíc je použito klíčové slovo CONSTANT:
credit_limitCONSTANTREAL:=5000.00;
Databázové systémy jako např. Oracle používají tzv. pracovní oblasti pro vykonávání SQL příkazů a pro ukládaní procesních informací. Pro přístup k těmto informacím se používá konstrukce nazývanákurzor, která umožňuje pojmenovat jednotlivé pracovní oblasti a přistupovat k nim. Existují dva druhy kurzorů: implicitní a explicitní. PL/SQL implicitně deklaruje kurzor pro všechny SQL příkazy, které manipulují s daty (i pro ty, které vrací jen jeden řádek). Explicitně můžeme např. deklarovat kurzor na jednotlivý řádek SQL příkazu, který vrací řádků více:
DECLARECURSORc1ISSELECTempno,ename,jobFROMempWHEREdeptno=20;
Stejně jako kurzory ukazují kurzorové proměnné na řádek víceřádkového výsledku dotazu, ale oproti kurzorům nejsou vázány na jeden konkrétní typ dotazu, ale je možno je přiřadit k jakémukoliv dotazu, který se liší pouze v typu. Kurzorová proměnná se chová jako klasická proměnná jazyka PL/SQL. Následující příklad otevře kurzorovou proměnnou generic_cv pro zvolený databázový dotaz
PROCEDUREopen_cv(generic_cvINOUTGenericCurTyp,choiceNUMBER)ISBEGINIFchoice=1THENOPENgeneric_cvFORSELECT*FROMemp;ELSIFchoice=2THENOPENgeneric_cvFORSELECT*FROMdept;ELSIFchoice=3THENOPENgeneric_cvFORSELECT*FROMsalgrade;ENDIF;...END;
Proměnné a kurzory jazyka PL/SQL mají atributy, které určují typ struktury na kterou ukazují bez opakování její definice. Indikátorem atributu je značka procenta (%).
Atribut%TYPE reprezentuje typ proměnné nebo sloupec v databázi. Kód
my_titlebooks.title%TYPE;
zajišťuje, že proměnnámy_title bude vždy stejného typu jako sloupectitle v tabulcebooks. Pokud se změní typ sloupce v tabulce změní se za běhu i typ proměnnémy_title.
Atribut%ROWTYPE reprezentuje typ proměnné nebo řádek v databázi. Například:
DECLAREdept_recdept%ROWTYPE;
Občas je nezbytné vybírat mezi více akcemi podle okamžitých okolností, pro tyto účely slouží konstrukce IF-THEN-ELSE:
DECLAREacct_balanceNUMBER(11,2);acctCONSTANTNUMBER(4):=3;debit_amtCONSTANTNUMBER(5,2):=500.00;BEGINSELECTbalINTOacct_balanceFROMaccountsWHEREaccount_id=acctFORUPDATEOFbal;IFacct_balance>=debit_amtTHENUPDATEaccountsSETbal=bal-debit_amtWHEREaccount_id=acct;ELSEINSERTINTOtempVALUES(acct,acct_balance,'Insufficient funds');-- vkládáme účet, stav účtu a zprávuENDIF;COMMIT;END;
Další možností je použití příkazu CASE:
CASEWHENshape='square'THENarea:=side*side;WHENshape='circle'THENBEGINarea:=pi*(radius*radius);DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.');END;WHENshape='rectangle'THENarea:=length*width;ELSEBEGINDBMS_OUTPUT.PUT_LINE('No formula to calculate area of a'||shape);RAISEPROGRAM_ERROR;END;ENDCASE;
Další možností řízení toku je řízení za pomocí cyklů. Lze použít tyto konstrukce: LOOP, FOR-LOOP, WHILE-LOOP a EXIT-WHEN.
LOOP/* kód */ENDLOOP;FORnumIN1..500LOOPINSERTINTOrootsVALUES(num,SQRT(num));ENDLOOP;DECLAREsalaryemp.sal%TYPE:=0;mgr_numemp.mgr%TYPE;last_nameemp.ename%TYPE;starting_empnoemp.empno%TYPE:=7499;BEGINSELECTmgrINTOmgr_numFROMempWHEREempno=starting_empno;WHILEsalary<=2500LOOPSELECTsal,mgr,enameINTOsalary,mgr_num,last_nameFROMempWHEREempno=mgr_num;ENDLOOP;INSERTINTOtempVALUES(NULL,salary,last_name);COMMIT;EXCEPTIONWHENNO_DATA_FOUNDTHENINSERTINTOtempVALUES(NULL,NULL,'Not found');COMMIT;END;LOOP...total:=total+salary;EXITWHENtotal>25000;-- je-li splněna podmínka, vyskočíme z cykluENDLOOP;
Jazyk obsahuje příkaz GOTO:
IFrating>90THENGOTOcalc_raise;-- skok na návěštíENDIF;...<<calc_raise>>IFjob_title='SALESMAN'THEN-- provádění programu pokračuje zdeamount:=commission*0.25;ELSEamount:=salary*0.10;ENDIF;
Modularita umožňuje rozdělit komplexní problém na sérii menších problémů, dobře definovaných modulů. Těmito moduly mohou být bloky, subprogramy nebo balíčky.
Subprogramy můžeme rozdělit na anonymní bloky, funkce a procedury. Funkce je podprogram, který musí obsahovat v deklaraci klauzuli RETURN a musí vracet hodnotu. Funkce může i nemusí obsahovat vstupní parametry. Procedura je podprogram, který v deklaraci neobsahuje klauzuli RETURN (vyskočit z procedury pomocí slova RETURN bez návratu hodnoty je však možné). Procedura může obsahovat vstupní (IN) i výstupní (OUT) parametry. Anonymní kód na rozdíl od funkce a procedury může vrátit hodnotu pouze pomocí vázané proměnné.
PROCEDUREaward_bonus(emp_idNUMBER)ISbonusREAL;comm_missingEXCEPTION;BEGIN-- jádro úlohySELECTcomm*0.15INTObonusFROMempWHEREempno=emp_id;IFbonusISNULLTHENRAISEcomm_missing;ELSEUPDATEpayrollSETpay=pay+bonusWHEREempno=emp_id;ENDIF;EXCEPTION-- obsluha výjimekWHENcomm_missingTHEN...ENDaward_bonus;
Balíčky umožňují slučovat proměnné, kurzory a podprogramy. Balíčky mají obvykle dvě části a to část specifikace a tělo balíčku. Př.:
CREATEPACKAGEemp_actionsAS-- specifikace balíčkuPROCEDUREhire_employee(empnoNUMBER,enameCHAR,...);PROCEDUREfire_employee(emp_idNUMBER);ENDemp_actions;CREATEPACKAGEBODYemp_actionsAS-- tělo balíčkuPROCEDUREhire_employee(empnoNUMBER,enameCHAR,...)ISBEGININSERTINTOempVALUES(empno,ename,...);ENDhire_employee;PROCEDUREfire_employee(emp_idNUMBER)ISBEGINDELETEFROMempWHEREempno=emp_id;ENDfire_employee;ENDemp_actions;
Pomocí abstrakce dat můžeme extrahovat klíčové proměnné a ignorovat v danou chvíli nepotřebná data. Pro abstrakci dat slouží tří konstrukce: kolekce, záznamy a objektové typy.
DECLARETYPEStaffISTABLEOFEmployee;stafferEmployee;FUNCTIONnew_hires(hiredateDATE)RETURNStaffISBEGIN...END;BEGINstaffer:=new_hires('10-NOV-98')(5);...END;
DECLARETYPETimeRecISRECORD(hoursSMALLINT,minutesSMALLINT);TYPEMeetingTypISRECORD(date_heldDATE,durationTimeRec,-- záznam uvnitř jiného záznamulocationVARCHAR2(20),purposeVARCHAR2(50));
CREATETYPEBank_AccountASOBJECT(acct_numberINTEGER(5),balanceREAL,statusVARCHAR2(10),MEMBERPROCEDUREopen(amountINREAL),MEMBERPROCEDUREverify_acct(numININTEGER),MEMBERPROCEDUREclose(numININTEGER,amountOUTREAL),MEMBERPROCEDUREdeposit(numININTEGER,amountINREAL),MEMBERPROCEDUREwithdraw(numININTEGER,amountINREAL),MEMBERFUNCTIONcurr_bal(numININTEGER)RETURNREAL);
PL/SQL podporuje výjimky, uživatelsky definovanou obsluhu chybových stavů.
DECLARE...comm_missingEXCEPTION;-- deklarace výjimkyBEGIN...IFcommissionISNULLTHENRAISEcomm_missing;-- vyvolání výjimkyENDIF;bonus:=(salary*0.10)+(commission*0.15);EXCEPTIONWHENcomm_missingTHEN...-- zpracování výjimkyWHENOTHERSTHEN...-- zpracování všech výše neodchycených výjimekEND;
| SQL | |
|---|---|
| příkazy jazyka SQL | |
| klíčová slova pro dotazování | |
| objekty | |
| ostatní pojmy | |
