Funkcijos 9 paskaita Funkcijos Turinys Kas yra PLSQL

Funkcijos 9 paskaita

Funkcijos - Turinys • • • Kas yra PLSQL funkcija? Funkcijų struktūra Funkcijos kūrimo sintaksė Paprogramių parametrai Funkcijos vykdymas Funkcijos šalinimas RETURN sakinio naudojimas Apribojimai PL/SQL paprogramių paketavimas Paprogramės parametrų būsenos Paprogramių vardų perkrovimas Perkrovimo apribojimai

Kas yra PLSQL funkcija? • Funkcija – tai paprogramė, kurios tikslas suskaičiuoti norimą reikšmę • Funkcijų struktūra yra labai panaši į jau minėtų procedūrų, skirtumas tik tas, kad funkcijos turi papildomą RETURN sakinį • F-ja gali būti saugoma duomenų bazėje kaip schemos objektas, kurį galima daug kartų vykdyti • F-ja iškviečiama kaip išraiškos dalis

Funkcijų kūrimas Redaktorius (rašomas CREATE(OR REPLACE) FUNCTION) file. sql SQL*Plus (load ir execute file. sql) Oracle (kodas) kompiliavimas P kodas (sukuriama funkcija) Vykdymas } Galima naudoti SHOW_ERRORS Kompiliavimo klaidų peržiūrai

Funkcijos kūrimo sintaksė [CREATE [OR REPLACE ] ] FUNCTION function_name [ ( parameter [mode] datatype [ , parameter ]. . . )] RETURN datatype [ AUTHID { DEFINER | CURRENT_USER } ] [ PARALLEL_ENABLE [ { [CLUSTER parameter BY (column_name [, column_name ]. . . ) ] | [ORDER parameter BY (column_name [, column_name ]. . . ) ] } ] [ ( PARTITION parameter BY { [ {RANGE | HASH } (column_name [, column_name]. . . )] | ANY } ) ] ] [DETERMINISTIC] [ PIPELINED [ USING implementation_type ] ] [ AGGREGATE [UPDATE VALUE] [WITH EXTERNAL CONTEXT] USING implementation_type ] {IS | AS} [ PRAGMA AUTONOMOUS_TRANSACTION; ][ local declarations ] BEGIN executable statements RETURN!!!! [ EXCEPTION exception handlers ] END [ name ];

DB funkcijos kūrimas SQL*Plus’e CREATE OR REPLACE FUNCTION get_sal (p_id IN employees. employee_id%TYPE) RETURN NUMBER IS v_salary employees. salary%TYPE: =0; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id=p_id; RETURN v_salary; END get_sal;

Funkcijos - pavyzdys 1 FUNCTION sal_ok (salary REAL, title VARCHAR 2) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT losal, hisal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok;

Funkcijos - pavyzdys 2 FUNCTION balance (acct_id INTEGER) RETURN REAL IS acct_bal REAL; BEGIN SELECT bal INTO acct_bal FROM accts WHERE acct_no = acct_id; RETURN acct_bal; END balance;

Funkcijos - pavyzdys 3 FUNCTION compound (years RETURN NUMBER IS NUMBER, amount NUMBER, BEGIN RETURN amount * POWER((rate / 100) + 1, years); END compound; rate NUMBER)

Funkcijos vykdymas • Iškviesti f-ją kaip PL/SQL išraiškos dalį • Sukurti kintamąjį, kuris saugos grąžinamą reikšmę • Vykdyti f-ją. Kintamajame bus patalpinta reikšmė grąžinama per RETURN sakinį

Paprogramių parametrai • Formalūs – kintamieji, deklaruoti paprogramės specifikacijoje parametrų sąraše ir naudojami paprogramės kūne • Tikrieji (faktiniai)- kintamieji arba išraiškos nurodyti paprogramės kvietimo parametrų sąraše

Funkcijos vykdymas • IF sal_ok(new_sal, new_title) THEN. . . • get_salary. sql failo vykdymas SQL*PLUS’e: 1 VARIABLE g_salary NUMBER 2 EXECUTE : g_salary: =get_sal(117) 3 PRINT g_salary

Vartotojų sukurtų f-jų privalumai SQL išraiškose • SQL praplėtimas, kai reikalingi skaičiavimai yra sudėtingi arba jų neįmanoma atlikti su SQL • Padidina duomenų nepriklausomumą, kai sudėtinga duomenų analizė atliekama Oracle serveryje, o ne ištraukus visus duomenis ir skaičiuojant juos aplikacijoje • Padidina užklausų efektyvumą, kai f-jos naudojamos užklausoje, o ne atliekamos aplikacijoje • Galima manipuliuoti naujais duomenų tipais (ilguma/platuma) verčiant juos character eilutėmis ir atliekant operacijas su jais per f-jas

F-jos iškvietimas SQL išraiškose pavyzdys CREATE OR REPLACE FUNCTION tax (p_value IN NUMBER) RETURN NUMBER IS BEGIN RETURN (v_salary * 0. 08); END tax; / SELECT employee_id, last_name, salary, tax(salary) FROM employees WHERE department_id=100;

Sritys iš kurių g. b. kviečiama f -ja • Reikšmių sąraše SELECT sakinyje • Sąlygos sakiniuose WHERE ar HAVING • CONNECT BY, START WITH, ORDER BY ir GROUP sakiniuose • VALUES sakinys INSERT komandoje • SET sakinys UPDATE komandoje

Apribojimai kviečiant f-jas iš SQL išraiškų Kad f-ją būtų galima kviesti iš SQL išraiškų ji turi būti: • Saugoma duomenų bazėje • Priimti tik IN parametrus • Priimti tik galiojančius SQL duomenų tipus (negalima PL/SQL spec. tipų kaip parametrai) • Grąžinti duomenų tipus, kurie yra galiojantys SQL duomenų tipai (ne PL/SQL spec. tipai)

Apribojimai kviečiant f-jas iš SQL išraiškų Kad funkcija galėtų būti kviečiama iš SQL sakinių, ji turi atitikti tam tikrus reikalavimus: • F-jose kviečiamose iš SQL išraiškų negali būti DML sakinių • Kai funkcija yra kviečiama sakinyje SELECT ar paraleliniuose (parallelized) sakiniuose INSERT, UPDATE, DELETE – funkcija negali keisti duomenų bazės lentelių • Kai funkcija yra kviečiama sakiniuose INSERT, UPDATE, DELETE funkcija negali vykdyti užklausų ar keisti duomenų bazės lentelių • Kai funkcija yra kviečiama sakiniuose SELECT, INSERT, UPDATE ar DELETE funkcija negali vykdyti SQL transakcijos valdymo sakinių (COMMIT) , darbo (session) valdymo sakinių (SET ROLE) ar sistemos valdymo sakinių (ALTER SYSTEM). Taip pat tokios funkcijos negali vykdyti DLL vykdymo sakinių (CREATE), nes juose automatiškai naudojamas COMMIT sakinys

Apribojimai kviečiant f-jas iš SQL išraiškų CREATE OR REPLACE FUNCTION dml_call_sql (p_sal NUMBER) RETURN NUMBER IS BEGIN INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary) VALUES (1, ‘employee 1’, ‘emp 1@company. com’, SYSDATE, ‘SA_MAN’, 1000); RETURN (p_sal+100); END; / UPDATE employees SET salary =dml_call_sql(2000) WHERE employee_id=170; UPDATE employees SET salary=dml_call_sql(2000) ERROR at line 1 ORA-06512 table PL/SQL EMPLOYEES is mutating tiger/function may not see it ORA-06512 at “PL/SQL DML_CALL_SQL” line 4

Funkcijos šalinimas • Sintaksė: DROP FUNCTION function_name; • Pavyzdys: DROP FUNCTION sal_ok; • Visos privilegijos ant f-jos po jos pašalinimo yra atimamos • CREATE or REPLACE sintaksė yra analogiška DROP ir CREATE, tačiau ją naudojant nedingsta privilegijos ant f-jos

RETURN sakinio naudojimas • RETURN sakinys pabaigia funkcijos darbą ir valdymą perduoda iškvietėjui. • Paprogramė gali turėti daugiau nei viena RETURN sakinį, o paskutinis jos sakinys nebūtinai turi būti RETURN sakinys. • Priėjus bet kurį paprogramėje esantį RETURN sakinį jos darbas nutraukiamas. • Didelis išėjimų iš paprogramės (RETURN sakinių) skaičius nėra gerai. • RETURN sakinys privalo turėti skaitinę išraišką, kuri yra apskaičiuojama, kai vykdomas pats RETURN sakinys. • Gražinamoji reikšmė yra priskiriama funkcijos vardui, ir vėliau gali būti naudojama kaip kintamasis raktiniu žodžiu RETURN nurodyto tipo.

Skirtumai nuo procedūrų • Priešingai nei procedūrose, funkcijose RETURN sakinys privalo turėti skaitinę išraišką Kviečianti aplinka PROCEDURE IN parameter OUT parameter IN OUT parameter (DECLARE). . . BEGIN. . . EXCEPTION. . . END; Kviečianti aplinka FUNCTION IN parameter (DECLARE). . . BEGIN. . . EXCEPTION. . . END;

Procedūrų ir funkcijų palyginimas Procedūros • Vykdo PL/SQL sakinį • Neturi RETURN sakinio • Gali grąžinti nė vienos, vieną arba keletą reikšmių • Gali turėti RETURN sakinį Funkcijos l Iškviečiamos, kaip išraiškos dalis l Privalo turėti RETURN sakinį antraštėje l Turi grąžinti vienintelę reikšmę l Turi turėti bent vieną RETURN sakinį

DB saugomų procedūrų ir funkcijų privalumai • Geresnis vykdymas • • • Išvengiama pakartotino nagrinėjimo, kai vartotojai naudoja shared SQL area Išvengiama pakartotino PL/SQL nagrinėjimo vykdymo metu, atliekant tai kompiliacijos metu Sumažinamas kreipinių į d. b. skaičius ir sumažinamas tinklo apkrovimas sugrupavus komandas • Lengvesnis palaikymas • • • Programos keičiamos online, neįtakoja kt. vartotojų darbo Pakeitimas 1 programoje pakeičia daugelį aplikacijų Pakeitimas 1 programoje eliminuoja daugkartinį testavimą • Geresnis duomenų saugumas ir vientisumas • • Kontroliuojamas neprivilegijuotų vartotojų netiesioginis priėjimas prie d. b. objektų Užtikrinama, kad susiję veiksmai bus atliekami kartu, arba nebus atliekami išvis • Aiškesnis kodas: naudojant atitinkamus žymėjimus, kurie aprašo programos veiksmus, sumažinamas komentarų kiekis ir kodas yra lengviau skaitomas

PL/SQL paprogramių paketavimas • • • Logiškai susijusias paprogrames galima sugrupuoti į paketą, kuris bus įrašytas į duomenų bazę Tokiu būdu paprogramėmis galės naudotis daug aplikacijų Paprogramės specifikacija rašoma paketo specifikacijoj Paprogramės kūnas – paketo kūne, kur jie yra nematomi aplikacijoms Taigi paketai leidžia paslėpti įgyvendinimo detales

Paprogramių vardų perkrovimas • PL/SQL leidžia perkrauti paprogrames ir tipų metodus. T. y. galima naudoti tą patį vardą kelioms skirtingoms paprogramėms, jei skiriasi jų formalių parametrų kiekis, tvarka, arba duomenų tipas • Perkrautas funkcijas galima įrašyti į tą patį bloką, paprogramę ar paketą • Patikrinant formalius parametrus, PL/SQL nustato, kuri iš tų funkcijų yra kviečiama

Perkrovimo apribojimai Tik lokalios arba paketuose paprogramės gali būti perkrautos. Negalima perkrauti: • standalone paprogramių • paprogramių, kurios skiriasi tik formalių parametrų vardais arba jų būsenom • dviejų paprogramių jeigu jų formalūs parametrai skiriasi tik duomenų tipais, kurie yra tos pačios duomenų tipų šeimos • dviejų paprogramių jeigu jų formalūs parametrai skiriasi tik potipiais, kurie yra paremti tipais iš tos pačios šeimos • dviejų funkcijų, kurios skiriasi tik grąžinamos reikšmės tipu, net jeigu tipai yra iš skirtingų šeimų

Santrauka Sužinojome, kad funkcija: • vardą turintis PL/SQL blokas, kuris turi grąžinti reikšmę • kuriama su CREATE FUNCTION komanda • iškviečiama kaip išraiškos dalis • saugoma d. b. gali būti kviečiama SQL sakiniuose • g. b. šalinama iš DB su DROP FUNCTION komanda • Dažniausiai • procedūra naudojama veiksmams atlikti • f-ja naudojama reikšmei suskaičiuoti