9 paskaita

1
Funkcijos

• 9 paskaita

2
Funkcijos - Turinys

• Kas yra PLSQL funkcija?
• Funkciju struktura
• Funkcijos kurimo sintakse
• Paprogramiu parametrai
• Funkcijos vykdymas
• Funkcijos šalinimas
• RETURN sakinio naudojimas
• Apribojimai
• PL/SQL paprogramiu paketavimas
• Paprogrames parametru busenos
• Paprogramiu vardu perkrovimas
• Perkrovimo apribojimai

3
Kas yra PLSQL funkcija?

• Funkcija tai paprograme, kurios tikslas
  suskaiciuoti norima reikšme
• Funkciju struktura yra labai panaši i jau minetu
  proceduru, skirtumas tik tas, kad funkcijos turi
  papildoma RETURN sakini
• F-ja gali buti saugoma duomenu bazeje kaip
  schemos objektas, kuri galima daug kartu vykdyti
• F-ja iškvieciama kaip išraiškos dalis

4
Funkciju kurimas

• Redaktorius (rašomas CREATE(OR REPLACE) FUNCTION)
• file.sql
• SQLPlus (load ir execute file.sql)
• Oracle (kodas)
• kompiliavimas
• P kodas (sukuriama funkcija)
• Vykdymas

Galima naudoti SHOW_ERRORS Kompiliavimo klaidu
peržiurai
5
Funkcijos kurimo sintakse
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
6
DB funkcijos kurimas SQLPluse
CREATE OR REPLACE FUNCTION get_sal (p_id IN
employees.employee_idTYPE) RETURN
NUMBER IS v_salary employees.salaryTYPE0 BEGI
N SELECT salary INTO v_salary FROM
employees WHERE employee_idp_id RETURN
v_salary END get_sal
7
Funkcijos - pavyzdys 1
FUNCTION sal_ok (salary REAL, title VARCHAR2)
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 gt min_sal) AND (salary lt max_sal) END
sal_ok
8
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
9
Funkcijos - pavyzdys 3
FUNCTION compound (years NUMBER, amount NUMBER,
rate NUMBER) RETURN NUMBER IS BEGIN RETURN
amount POWER((rate / 100) 1, years) END
compound
10
Funkcijos vykdymas

• Iškviesti f-ja kaip PL/SQL išraiškos dali
• Sukurti kintamaji, kuris saugos gražinama reikšme
• Vykdyti f-ja. Kintamajame bus patalpinta reikšme
  gražinama per RETURN sakini

11
Paprogramiu parametrai

• Formalus kintamieji, deklaruoti paprogrames
  specifikacijoje parametru saraše ir naudojami
  paprogrames kune
• Tikrieji (faktiniai)- kintamieji arba išraiškos
  nurodyti paprogrames kvietimo parametru saraše

12
Funkcijos vykdymas

• IF sal_ok(new_sal, new_title) THEN ...
• get_salary.sql failo vykdymas SQLPLUSe
• VARIABLE g_salary NUMBER
• EXECUTE g_salaryget_sal(117)
• PRINT g_salary

1
2
3
13
Vartotoju sukurtu f-ju privalumai SQL išraiškose

• SQL prapletimas, kai reikalingi skaiciavimai yra
  sudetingi arba ju neimanoma atlikti su SQL
• Padidina duomenu nepriklausomuma, kai sudetinga
  duomenu analize atliekama Oracle serveryje, o ne
  ištraukus visus duomenis ir skaiciuojant juos
  aplikacijoje
• Padidina užklausu efektyvuma, kai f-jos
  naudojamos užklausoje, o ne atliekamos
  aplikacijoje
• Galima manipuliuoti naujais duomenu tipais
  (ilguma/platuma) verciant juos character
  eilutemis ir atliekant operacijas su jais per
  f-jas

14
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_id100
15
Sritys iš kuriu g. b. kvieciama f-ja

• Reikšmiu saraše SELECT sakinyje
• Salygos sakiniuose WHERE ar HAVING
• CONNECT BY, START WITH, ORDER BY ir GROUP
  sakiniuose
• VALUES sakinys INSERT komandoje
• SET sakinys UPDATE komandoje

16
Apribojimai kvieciant f-jas iš SQL išraišku

• Kad f-ja butu galima kviesti iš SQL
• išraišku ji turi buti
• Saugoma duomenu bazeje
• Priimti tik IN parametrus
• Priimti tik galiojancius SQL duomenu tipus
  (negalima PL/SQL spec. tipu kaip parametrai)
• Gražinti duomenu tipus, kurie yra galiojantys SQL
  duomenu tipai (ne PL/SQL spec. tipai)

17
Apribojimai kvieciant f-jas iš SQL išraišku

• Kad funkcija galetu buti kvieciama iš SQL
  sakiniu, ji turi atitikti tam tikrus
• reikalavimus
• F-jose kvieciamose iš SQL išraišku negali buti
  DML sakiniu
• Kai funkcija yra kvieciama sakinyje SELECT ar
  paraleliniuose (parallelized) sakiniuose INSERT,
  UPDATE, DELETE funkcija negali keisti duomenu
  bazes lenteliu
• Kai funkcija yra kvieciama sakiniuose INSERT,
  UPDATE, DELETE funkcija negali vykdyti užklausu
  ar keisti duomenu bazes lenteliu
• Kai funkcija yra kvieciama sakiniuose SELECT,
  INSERT, UPDATE ar DELETE funkcija negali vykdyti
  SQL transakcijos valdymo sakiniu (COMMIT) , darbo
  (session) valdymo sakiniu (SET ROLE) ar sistemos
  valdymo sakiniu (ALTER SYSTEM). Taip pat tokios
  funkcijos negali vykdyti DLL vykdymo sakiniu
  (CREATE), nes juose automatiškai naudojamas
  COMMIT sakinys

18
Apribojimai kvieciant f-jas iš SQL išraišku
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, emp1_at_company.com, SYSDATE,
SA_MAN, 1000) RETURN (p_sal100) END /
UPDATE employees SET salary dml_call_sql(2000) WH
ERE employee_id170 UPDATE employees SET
salarydml_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
19
Funkcijos šalinimas

• Sintakse DROP FUNCTION function_name
• Pavyzdys DROP FUNCTION sal_ok
• Visos privilegijos ant f-jos po jos pašalinimo
  yra atimamos
• CREATE or REPLACE sintakse yra analogiška DROP ir
  CREATE, taciau ja naudojant nedingsta
  privilegijos ant f-jos

20
RETURN sakinio naudojimas

• RETURN sakinys pabaigia funkcijos darba ir
  valdyma perduoda iškvietejui.
• Paprograme gali tureti daugiau nei viena RETURN
  sakini, o paskutinis jos sakinys nebutinai turi
  buti RETURN sakinys.
• Priejus bet kuri paprogrameje esanti RETURN
  sakini jos darbas nutraukiamas.
• Didelis išejimu iš paprogrames (RETURN sakiniu)
  skaicius nera gerai.
• RETURN sakinys privalo tureti skaitine išraiška,
  kuri yra apskaiciuojama, kai vykdomas pats RETURN
  sakinys.
• Gražinamoji reikšme yra priskiriama funkcijos
  vardui, ir veliau gali buti naudojama kaip
  kintamasis raktiniu žodžiu RETURN nurodyto tipo.

21
Skirtumai nuo proceduru

• Priešingai nei procedurose, funkcijose RETURN
  sakinys privalo tureti skaitine išraiška

PROCEDURE IN parameter OUT parameter IN OUT
parameter (DECLARE) ... BEGIN ... EXCEPTION ... EN
D
FUNCTION IN parameter (DECLARE) ... BEGIN ... EX
CEPTION ... END
Kviecianti aplinka
Kviecianti aplinka
22
Proceduru ir funkciju palyginimas

• Proceduros
• Vykdo PL/SQL sakini
• Neturi RETURN sakinio
• Gali gražinti ne vienos, viena arba keleta
  reikšmiu
• Gali tureti RETURN sakini

• Funkcijos
• Iškvieciamos, kaip išraiškos dalis
• Privalo tureti RETURN sakini antrašteje
• Turi gražinti vienintele reikšme
• Turi tureti bent viena RETURN sakini

23
DB saugomu proceduru ir funkciju privalumai

• Geresnis vykdymas
• Išvengiama pakartotino nagrinejimo, kai
  vartotojai naudoja shared SQL area
• Išvengiama pakartotino PL/SQL nagrinejimo vykdymo
  metu, atliekant tai kompiliacijos metu
• Sumažinamas kreipiniu i d.b. skaicius ir
  sumažinamas tinklo apkrovimas sugrupavus komandas
• Lengvesnis palaikymas
• Programos keiciamos online, neitakoja kt.
  vartotoju darbo
• Pakeitimas 1 programoje pakeicia daugeli
  aplikaciju
• Pakeitimas 1 programoje eliminuoja daugkartini
  testavima
• Geresnis duomenu saugumas ir vientisumas
• Kontroliuojamas neprivilegijuotu vartotoju
  netiesioginis priejimas prie d.b. objektu
• Užtikrinama, kad susije veiksmai bus atliekami
  kartu, arba nebus atliekami išvis
• Aiškesnis kodas naudojant atitinkamus žymejimus,
  kurie aprašo programos veiksmus, sumažinamas
  komentaru kiekis ir kodas yra lengviau skaitomas

24
PL/SQL paprogramiu paketavimas

• Logiškai susijusias paprogrames galima sugrupuoti
  i paketa, kuris bus irašytas i duomenu baze
• Tokiu budu paprogramemis gales naudotis daug
  aplikaciju
• Paprogrames specifikacija rašoma paketo
  specifikacijoj
• Paprogrames kunas paketo kune, kur jie yra
  nematomi aplikacijoms
• Taigi paketai leidžia paslepti igyvendinimo
  detales

25
Paprogramiu vardu perkrovimas

• PL/SQL leidžia perkrauti paprogrames ir tipu
  metodus. T.y. galima naudoti ta pati varda
  kelioms skirtingoms paprogramems, jei skiriasi ju
  formaliu parametru kiekis, tvarka, arba duomenu
  tipas
• Perkrautas funkcijas galima irašyti i ta pati
  bloka, paprograme ar paketa
• Patikrinant formalius parametrus, PL/SQL
  nustato, kuri iš tu funkciju yra kvieciama

26
Perkrovimo apribojimai

• Tik lokalios arba paketuose paprogrames gali buti
  
• perkrautos. Negalima perkrauti
• standalone paprogramiu
• paprogramiu, kurios skiriasi tik formaliu
  parametru vardais arba ju busenom
• dvieju paprogramiu jeigu ju formalus parametrai
  skiriasi tik duomenu tipais, kurie yra tos pacios
  duomenu tipu šeimos
• dvieju paprogramiu jeigu ju formalus parametrai
  skiriasi tik potipiais, kurie yra paremti tipais
  iš tos pacios šeimos
• dvieju funkciju, kurios skiriasi tik gražinamos
  reikšmes tipu, net jeigu tipai yra iš skirtingu
  šeimu

27
Santrauka

• Sužinojome, kad funkcija
• varda turintis PL/SQL blokas, kuris turi gražinti
  reikšme
• kuriama su CREATE FUNCTION komanda
• iškvieciama kaip išraiškos dalis
• saugoma d.b. gali buti kvieciama SQL sakiniuose
• g.b. šalinama iš DB su DROP FUNCTION komanda
• Dažniausiai
• procedura naudojama veiksmams atlikti
• f-ja naudojama reikšmei suskaiciuoti