Funkcja to konstrukt, zwracający wartość do środowiska, z którego został wywołany

Krótko o nazewnictwie

W nazwie funkcji nie używam czasownika get/fetch/generate.

Funkcja zawsze coś zwraca, więc lepiej od razu przejść do rzeczy i w nazwie odpowiedzieć na pytanie “co zwraca ta funkcja”.
Im więcej mamy funkcji dotyczących np. obiektu employees, tym bardziej specyficzni musimy być w nazewnictwie.

ŹLE:
get_employee_ids
get_random_string
get_meetings

DOBRZE:
employee_ids
random_string
meeting_dates

1. Co zwraca funkcja

Funkcja może zwracać wartość skalarną lub złożoną (w praktyce oznacza to record lub collection np. Nested Table).

1.1 Funkcja zwraca wartość skalarną

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION random_string(in_length IN PLS_INTEGER)
RETURN VARCHAR2
AUTHID DEFINER IS
   co_random_string_option CONSTANT CHAR(1) := 'u';
   l_random_string         VARCHAR2(5);
BEGIN
   l_random_string := DBMS_RANDOM.string (co_random_string_option
                                         ,in_length);
   RETURN l_random_string;
END random_string;
/
SELECT random_string(5) FROM dual;

table_functions_055.png

1.2 Funkcja zwraca wartość złożoną

1.2.1 Funkcja zwraca kolekcję

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE OR REPLACE TYPE t_employees_type IS TABLE OF VARCHAR2 (100);
/
CREATE OR REPLACE FUNCTION random_employees_one_col (in_count IN PLS_INTEGER)
RETURN t_employees_type
AUTHID DEFINER IS
   co_lower_bound           CONSTANT SIMPLE_INTEGER := 1;
   co_emp_first_name_length CONSTANT SIMPLE_INTEGER := 5;
   co_emp_last_name_length  CONSTANT SIMPLE_INTEGER := 7;
   co_emp_sep               CONSTANT CHAR(1)        := ' ';
   co_random_string_option  CONSTANT CHAR(1)        := 'u';
   t_employees              t_employees_type        := t_employees_type ();
BEGIN
   t_employees.EXTEND (in_count);
   <<add_random_employees>>
   FOR i IN co_lower_bound..in_count
   LOOP
      t_employees (i) := DBMS_RANDOM.string (co_random_string_option
                                            ,co_emp_first_name_length) || 
                                             co_emp_sep                || 
                         DBMS_RANDOM.string (co_random_string_option
                                            ,co_emp_last_name_length);
   END LOOP add_random_employees;
   RETURN t_employees;
END;
/
SELECT random_employees_one_col(5)
  FROM dual;
/

table_functions_060.png

1.2.2 Funkcja zwraca kolekcję w postaci relacyjnej (tabeli) w pojedynczej kolumnie.

Nazwa Table Function odnosi się nie tyle do samego kodu funkcji, co do sposobu jej wywołania. Funkcję random_employees_one_col z przykładu powyżej możemy uruchomić na 2 sposoby:

1.2.2.1 Uruchamiamy jako “normalną” funkcję (czyli identycznie, jak w punkcie 1.2.1)
SELECT random_employees_one_col(5)
  FROM dual;
/

table_functions_060.png

1.2.2.2 Uruchamiamy jako Table Function
SELECT *
  FROM TABLE (random_employees_one_col(5));
/

table_functions_065.png

Wystarczy tylko umieścić funkcję (razem z parametrem) w klauzuli TABLE, którą odpytujemy tak, jak normalną tabelę - np. employees.

Od wersji Oracle Database 12c Release 2 (12.2.0.1.0), użycie klauzli TABLE jest opcjonalne. Moglibyśmy po prostu napisać SELECT * FROM random_employees_one_col(5);

Forma relacyjna może być zwrócona dla 1 z 2 typów danych:

  • Nested Table
  • Varray

Każy inny typ wywoła błąd. Spróbujmy użyć “skalarnej” funkcji (typ zwracanych wartości to VARCHAR2) z początku artykułu:

SELECT * 
  FROM TABLE (random_string(5));
/

table_functions_070.png

1.2.3 Funkcja zwraca kolekcję w postaci relacyjnej (tabeli) w wielu kolumnach.

Sprawa odrobinę się komplikuje. Musimy najpierw zdeklarować typ Object, którego instancje zastąpią pojedyncze wartości (jak np. 'RANDO MVALU') z poprzedniej kolekcji.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE OR REPLACE TYPE employee_ot IS OBJECT (
   first_name VARCHAR2(5 CHAR)
  ,last_name  VARCHAR2(7 CHAR));
/
CREATE OR REPLACE TYPE t_employees_type IS TABLE OF employee_ot;
/
--
create or replace FUNCTION radom_employees_multi_cols (in_count IN PLS_INTEGER)
RETURN t_employees_type
AUTHID DEFINER IS
   co_lower_bound           CONSTANT SIMPLE_INTEGER := 1;
   co_emp_first_name_length CONSTANT SIMPLE_INTEGER := 5;
   co_emp_last_name_length  CONSTANT SIMPLE_INTEGER := 7;
   co_random_string_option  CONSTANT CHAR(1)        := 'u';
   t_employees              t_employees_type        := t_employees_type ();
BEGIN
   t_employees.EXTEND (in_count);
   <<add_random_employees>>
   FOR i IN co_lower_bound..in_count
   LOOP
      t_employees (i) := employee_ot (DBMS_RANDOM.string (co_random_string_option
                                                         ,co_emp_first_name_length)
                                     ,
                                      DBMS_RANDOM.string (co_random_string_option
                                                         ,co_emp_last_name_length));
   END LOOP add_random_employees;
   RETURN t_employees;
END;
/

Tak jak w przypadku funkcji z pojedynczą kolumną, możemy uruchomić tę funkcję jako “normalną” funkcję lub jako Table Function.

1.2.3.1 Uruchamiamy jako “normalną” funkcję
SELECT radom_employees_multi_cols(50) 
  FROM dual;
/

table_functions_080.png

1.2.3.2 Uruchamiamy jako Table Function
SELECT * 
  FROM TABLE (radom_employees_multi_cols(50));
/

table_functions_075.png


Tabelę (a dokładniej - instancję kolekcji), stworzoną przez Table Function możemy odpytywać indentycznie jak “zwykłą” tabelę.

Przykłady takich query:

NATURAL JOIN
SELECT *
  FROM TABLE (radom_employees_multi_cols(1))
            ,(radom_employees_multi_cols(1));
UNION
SELECT *
  FROM TABLE (radom_employees_multi_cols(1))
 UNION ALL
SELECT *
  FROM TABLE (radom_employees_multi_cols(1));
CREATE TABLE AS (…)
CREATE TABLE raem AS SELECT * 
  FROM TABLE (radom_employees_multi_cols(1));
/
SELECT * FROM raem;
CREATE VIEW
CREATE OR REPLACE VIEW random_employees_v AS
   SELECT *
     FROM TABLE (radom_employees_multi_cols(10));
/
SELECT *
  FROM random_employees_v;
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW random_employees_mv AS
   SELECT *
     FROM TABLE (radom_employees_multi_cols(10));
/
SELECT *
  FROM random_employees_mv;

Widok to tylko konstrukt logiczny - każdy SELECT (z prawdopodobieństwem ekstremalnie bliskim P = 1) wygeneruje zawartość kolekcji różną od poprzedniej.
W przypadku widoku zmaterializowanego, przy kolejnych SELECT, dane będą oczywiście takie, jak w momencie jego stworzenia.

Źródła:

  1. https://docs.oracle.com/cd/B19306_01/appdev.1…
  2. https://www.youtube.com/watch?v=YixSfXZ8wPI