TABLE в Oracle SQL — 100 практических примеров

ORACLE

🟢 Введение

Слово TABLE в Oracle кажется очень простым, но на деле у него сразу несколько ярких ролей.
С одной стороны, это фундамент всего реляционного мира — таблицы как структура хранения данных.
С другой — это ещё и table expression, когда ты работаешь с коллекциями, nested table, объектными типами и результатами табличных функций прямо в FROM.
И вот за это я особенно люблю Oracle: одно слово, а за ним и классический SQL, и очень мощные объектно-реляционные возможности.

Синтаксис

-- обычная таблица
CREATE TABLE table_name (
  col1 NUMBER,
  col2 VARCHAR2(100)
)

-- TABLE expression для коллекции
SELECT *
FROM TABLE(collection_expression)

-- TABLE expression для коллекции-колонки
SELECT *
FROM parent_table p,
     TABLE(p.collection_column) c

В Oracle table expression используют для разворота nested table и varray в обычные строки результата.
Документация Oracle прямо описывает TABLE(...) как table expression для обращения к collection-данным, а nested table и varray создаются через CREATE TYPE. :contentReference[oaicite:0]{index=0}

Где используют

  • разворачивание nested table и varray в строки
  • работа с объектными типами и коллекциями
  • передача наборов значений в SQL без временных таблиц
  • обход результатов pipelined и table functions
  • сложные объектно-реляционные модели, где коллекции живут внутри строк родительской таблицы

100 примеров

1. Базовая реляционная структура для сотрудников

CREATE TABLE employees (
  emp_id NUMBER,
  emp_name VARCHAR2(100)
)

2. Справочник стран с PRIMARY KEY

CREATE TABLE countries (
  country_id NUMBER PRIMARY KEY,
  country_name VARCHAR2(100)
)

3. Таблица заказов с FOREIGN KEY

CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  customer_id NUMBER REFERENCES customers(customer_id),
  order_date DATE
)

4. Временная структура для расчётов

CREATE GLOBAL TEMPORARY TABLE temp_ids (
  id NUMBER
) ON COMMIT DELETE ROWS

5. Таблица с CHECK-ограничением

CREATE TABLE ratings (
  rating NUMBER CHECK (rating BETWEEN 1 AND 5)
)

6. Таблица с GENERATED AS IDENTITY

CREATE TABLE logs (
  log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
  log_text VARCHAR2(400)
)

7. Таблица с DEFAULT SYSDATE

CREATE TABLE audit_events (
  event_id NUMBER,
  created_at DATE DEFAULT SYSDATE
)

8. Таблица с виртуальным столбцом

CREATE TABLE invoice_lines (
  net_amt NUMBER(10,2),
  tax_amt NUMBER(10,2),
  gross_amt GENERATED ALWAYS AS (net_amt + tax_amt) VIRTUAL
)

9. Таблица на отдельном TABLESPACE

CREATE TABLE archive_rows (
  row_id NUMBER,
  body_txt VARCHAR2(200)
) TABLESPACE users

10. CTAS через AS SELECT

CREATE TABLE employees_copy AS
SELECT emp_id,emp_name
FROM employees

11. Коллекционный тип nested table из NUMBER

CREATE TYPE num_list_t AS TABLE OF NUMBER

12. Коллекционный тип nested table из VARCHAR2

CREATE TYPE code_list_t AS TABLE OF VARCHAR2(30)

13. VARRAY для коротких списков

CREATE TYPE phone_arr_t AS VARRAY(5) OF VARCHAR2(20)

14. OBJECT type для адреса

CREATE TYPE address_t AS OBJECT (
  city VARCHAR2(80),
  zip_code VARCHAR2(20)
)

15. Nested table из OBJECT type

CREATE TYPE address_list_t AS TABLE OF address_t

16. Родительская таблица с коллекцией телефонов

CREATE TABLE contact_book (
  contact_id NUMBER,
  phones phone_arr_t
)

17. Таблица с nested table-колонкой

CREATE TABLE bundles (
  bundle_id NUMBER,
  nums num_list_t
) NESTED TABLE nums STORE AS bundles_nums

18. Родительская строка с коллекцией адресов

CREATE TABLE customer_addr (
  customer_id NUMBER,
  addresses address_list_t
) NESTED TABLE addresses STORE AS customer_addr_nt

19. Разворачивание числа из коллекции через TABLE()

SELECT column_value AS num_val
FROM TABLE(num_list_t(10,20,30))

20. Разворачивание строковой коллекции

SELECT column_value AS code_val
FROM TABLE(code_list_t('A','B','C'))

Еще 20 примеров

21. Сумма элементов коллекции через TABLE()

SELECT SUM(column_value) AS total_val
FROM TABLE(num_list_t(1,2,3,4,5))

22. Среднее значение по коллекции

SELECT AVG(column_value) AS avg_val
FROM TABLE(num_list_t(10,20,30))

23. Минимум и максимум из nested list

SELECT MIN(column_value) AS min_val,
       MAX(column_value) AS max_val
FROM TABLE(num_list_t(7,15,2,40))

24. DISTINCT по значениям коллекции

SELECT DISTINCT column_value
FROM TABLE(code_list_t('X','X','Y','Z'))

25. ORDER BY по значениям из TABLE()

SELECT column_value
FROM TABLE(num_list_t(9,3,7,1))
ORDER BY column_value

26. Фильтрация элементов коллекции через WHERE

SELECT column_value
FROM TABLE(num_list_t(5,10,15,20))
WHERE column_value > 10

27. Применение CASE к данным из TABLE()

SELECT column_value,
       CASE WHEN column_value >= 10 THEN 'BIG' ELSE 'SMALL' END AS bucket
FROM TABLE(num_list_t(3,11,8,20))

28. Разворот VARRAY телефонов

SELECT column_value AS phone_no
FROM TABLE(phone_arr_t('+33111111111','+33222222222'))

29. Табличное выражение из OBJECT-коллекции

SELECT a.city,a.zip_code
FROM TABLE(
       address_list_t(
         address_t('Paris','75001'),
         address_t('Lyon','69000')
       )
     ) a

30. Фильтр по city внутри object collection

SELECT a.city
FROM TABLE(
       address_list_t(
         address_t('Paris','75001'),
         address_t('Rome','00100')
       )
     ) a
WHERE a.city = 'Paris'

31. Join обычной таблицы с inline-коллекцией id

SELECT u.user_id,u.email
FROM users u
JOIN TABLE(num_list_t(10,20,30)) t
ON u.user_id = t.column_value

32. Join справочника со списком кодов

SELECT s.status_code
FROM statuses s
JOIN TABLE(code_list_t('NEW','DONE')) t
ON s.status_code = t.column_value

33. IN через TABLE() и subquery

SELECT user_id,email
FROM users
WHERE user_id IN (
  SELECT column_value
  FROM TABLE(num_list_t(1,2,3))
)

34. EXISTS с TABLE()

SELECT *
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM TABLE(num_list_t(10,20,30)) t
  WHERE t.column_value = u.user_id
)

35. COUNT строк коллекции

SELECT COUNT(*) AS cnt_val
FROM TABLE(code_list_t('A','B','C','D'))

36. Агрегация строковой коллекции через LISTAGG

SELECT LISTAGG(column_value,',') WITHIN GROUP (ORDER BY column_value) AS csv_val
FROM TABLE(code_list_t('B','A','C'))

37. Верхний регистр для элементов из TABLE()

SELECT UPPER(column_value) AS code_uc
FROM TABLE(code_list_t('aa','bb','cc'))

38. Хэш bucket по значениям из коллекции

SELECT column_value,
       MOD(column_value,4) AS bucket_no
FROM TABLE(num_list_t(11,12,13,14,15))

39. Присвоение row_number данным из TABLE()

SELECT column_value,
       ROW_NUMBER() OVER(ORDER BY column_value) AS rn
FROM TABLE(num_list_t(20,10,30))

40. Генерация XML из TABLE() значений

SELECT XMLELEMENT(
         "ids",
         XMLAGG(XMLELEMENT("id",column_value))
       ) AS ids_xml
FROM TABLE(num_list_t(1,2,3))

Еще 20 примеров

41. Разворачивание nested table-колонки из родительской таблицы

SELECT b.bundle_id,n.column_value AS num_val
FROM bundles b,
     TABLE(b.nums) n

42. Фильтрация элементов коллекции родительской строки

SELECT b.bundle_id,n.column_value
FROM bundles b,
     TABLE(b.nums) n
WHERE n.column_value > 100

43. Сумма коллекции в каждой родительской строке

SELECT b.bundle_id,
       SUM(n.column_value) AS total_val
FROM bundles b,
     TABLE(b.nums) n
GROUP BY b.bundle_id

44. Количество элементов nested table по строке

SELECT b.bundle_id,
       COUNT(*) AS item_cnt
FROM bundles b,
     TABLE(b.nums) n
GROUP BY b.bundle_id

45. Join parent row и object collection addresses

SELECT c.customer_id,a.city,a.zip_code
FROM customer_addr c,
     TABLE(c.addresses) a

46. Поиск клиентов по городу внутри nested table

SELECT DISTINCT c.customer_id
FROM customer_addr c,
     TABLE(c.addresses) a
WHERE a.city = 'Paris'

47. Группировка по city внутри collection-колонки

SELECT a.city,
       COUNT(*) AS cnt_val
FROM customer_addr c,
     TABLE(c.addresses) a
GROUP BY a.city

48. Сериализация адресов в CSV через LISTAGG

SELECT c.customer_id,
       LISTAGG(a.city,',') WITHIN GROUP (ORDER BY a.city) AS cities_csv
FROM customer_addr c,
     TABLE(c.addresses) a
GROUP BY c.customer_id

49. Вытаскивание телефонов из contact_book

SELECT c.contact_id,p.column_value AS phone_no
FROM contact_book c,
     TABLE(c.phones) p

50. Поиск контактов по номеру телефона

SELECT DISTINCT c.contact_id
FROM contact_book c,
     TABLE(c.phones) p
WHERE p.column_value = '+33111111111'

51. Объединение users и их phone-array

SELECT u.user_id,u.email,p.column_value AS phone_no
FROM users u
JOIN contact_book c ON c.contact_id = u.user_id
JOIN TABLE(c.phones) p ON 1 = 1

52. Получение первой строки из TABLE() по ORDER BY

SELECT column_value
FROM TABLE(num_list_t(30,20,10))
ORDER BY column_value
FETCH FIRST 1 ROW ONLY

53. Последние два значения через OFFSET FETCH

SELECT column_value
FROM TABLE(num_list_t(10,20,30,40))
ORDER BY column_value
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY

54. TABLE() в CTE через WITH

WITH nums AS (
  SELECT column_value AS n
  FROM TABLE(num_list_t(1,2,3,4))
)
SELECT n,
       n * n AS sqr_val
FROM nums

55. Сумма только чётных значений из collection

SELECT SUM(column_value) AS even_sum
FROM TABLE(num_list_t(1,2,3,4,5,6))
WHERE MOD(column_value,2) = 0

56. Категоризация строковой коллекции по длине

SELECT column_value,
       CASE WHEN LENGTH(column_value) <= 3 THEN 'SHORT' ELSE 'LONG' END AS len_band
FROM TABLE(code_list_t('AA','BBBB','CCC'))

57. Преобразование элементов в JSON-like строку

SELECT '{"id":' || column_value || '}' AS json_txt
FROM TABLE(num_list_t(101,102,103))

58. Уникальные города из object collection

SELECT DISTINCT a.city
FROM TABLE(
       address_list_t(
         address_t('Paris','75001'),
         address_t('Paris','75008'),
         address_t('Rome','00100')
       )
     ) a

59. Средняя длина phone number

SELECT AVG(LENGTH(column_value)) AS avg_len
FROM TABLE(phone_arr_t('1111','22222','333333'))

60. XMLAgg по городам из object collection

SELECT XMLELEMENT(
         "cities",
         XMLAGG(XMLELEMENT("city",a.city))
       ) AS cities_xml
FROM TABLE(
       address_list_t(
         address_t('Paris','75001'),
         address_t('Berlin','10115')
       )
     ) a

Еще 20 примеров

61. Табличная функция через SYS.ODCINUMBERLIST

SELECT column_value
FROM TABLE(SYS.ODCINUMBERLIST(100,200,300))

62. Строковый список через SYS.ODCIVARCHAR2LIST

SELECT column_value
FROM TABLE(SYS.ODCIVARCHAR2LIST('Paris','Rome','Tokyo'))

63. Join таблицы users со встроенным ODCINUMBERLIST

SELECT u.user_id,u.email
FROM users u
JOIN TABLE(SYS.ODCINUMBERLIST(1,5,9)) t
ON u.user_id = t.column_value

64. Пересечение заказов с inline-набором id

SELECT o.order_id,o.status
FROM orders o
WHERE o.order_id IN (
  SELECT column_value
  FROM TABLE(SYS.ODCINUMBERLIST(1001,1002,1003))
)

65. Мини-справочник через ODCIVARCHAR2LIST и ORDER BY

SELECT column_value
FROM TABLE(SYS.ODCIVARCHAR2LIST('B','A','C'))
ORDER BY column_value

66. LISTAGG для встроенного строкового списка

SELECT LISTAGG(column_value,'|') WITHIN GROUP (ORDER BY column_value) AS line_txt
FROM TABLE(SYS.ODCIVARCHAR2LIST('x','y','z'))

67. Группировка bucket-ов из ODCINUMBERLIST

SELECT MOD(column_value,3) AS bucket_no,
       COUNT(*) AS cnt_val
FROM TABLE(SYS.ODCINUMBERLIST(1,2,3,4,5,6,7))
GROUP BY MOD(column_value,3)

68. Применение SIGN к списку чисел

SELECT column_value,
       SIGN(column_value) AS sign_code
FROM TABLE(SYS.ODCINUMBERLIST(-10,0,15))

69. CASE-бандинг для встроенного number list

SELECT column_value,
       CASE WHEN column_value >= 100 THEN 'HI' ELSE 'LO' END AS band_val
FROM TABLE(SYS.ODCINUMBERLIST(50,100,150))

70. Генерация slug-подобных значений из string list

SELECT LOWER(REPLACE(column_value,' ','-')) AS slug_val
FROM TABLE(SYS.ODCIVARCHAR2LIST('New York','Los Angeles'))

71. Левое коррелированное TABLE() по коллекции-колонке

SELECT c.customer_id,a.city
FROM customer_addr c,
     TABLE(c.addresses) a
WHERE a.zip_code LIKE '75%'

72. Join object collection и country_dim по city mapping

SELECT c.customer_id,a.city,d.country_code
FROM customer_addr c,
     TABLE(c.addresses) a
JOIN city_dim d ON d.city_name = a.city

73. Агрегация object collection по zip-prefix

SELECT SUBSTR(a.zip_code,1,2) AS zip_pref,
       COUNT(*) AS cnt_val
FROM customer_addr c,
     TABLE(c.addresses) a
GROUP BY SUBSTR(a.zip_code,1,2)

74. JSON-подобный output из nested number list

SELECT '[' || LISTAGG(column_value,',') WITHIN GROUP (ORDER BY column_value) || ']' AS json_arr
FROM TABLE(num_list_t(3,1,2))

75. COUNT DISTINCT по таблице-выражению

SELECT COUNT(DISTINCT column_value) AS uniq_cnt
FROM TABLE(code_list_t('A','A','B','C'))

76. Превращение TABLE() в source dataset для MERGE

MERGE INTO statuses t
USING (
  SELECT column_value AS status_code
  FROM TABLE(code_list_t('NEW','DONE'))
) s
ON (t.status_code = s.status_code)
WHEN NOT MATCHED THEN
  INSERT (status_code)
  VALUES (s.status_code)

77. Вставка строк из TABLE() в обычную таблицу

INSERT INTO temp_ids(id)
SELECT column_value
FROM TABLE(num_list_t(101,102,103))

78. Обновление таблицы по значениям из TABLE()

UPDATE users
SET email = LOWER(email)
WHERE user_id IN (
  SELECT column_value
  FROM TABLE(num_list_t(1,2,3))
)

79. Удаление по списку id из TABLE()

DELETE FROM temp_ids
WHERE id IN (
  SELECT column_value
  FROM TABLE(num_list_t(101,102))
)

80. Псевдо-календарь как table-expression join с CONNECT BY

SELECT d.dt_val,t.column_value AS bucket_no
FROM (
  SELECT TRUNC(SYSDATE) + LEVEL - 1 AS dt_val
  FROM dual
  CONNECT BY LEVEL <= 3
) d
JOIN TABLE(num_list_t(1,2)) t ON 1 = 1

Еще 20 примеров

81. Полный пример object collection в FROM

SELECT a.city,a.zip_code,
       a.city || ' ' || a.zip_code AS label_txt
FROM TABLE(
       address_list_t(
         address_t('Madrid','28001'),
         address_t('Lisbon','1100')
       )
     ) a

82. Фильтр object collection по zip prefix

SELECT a.city
FROM TABLE(
       address_list_t(
         address_t('Paris','75001'),
         address_t('Paris','92000'),
         address_t('Rome','00100')
       )
     ) a
WHERE a.zip_code LIKE '75%'

83. Сортировка object collection по city и zip

SELECT a.city,a.zip_code
FROM TABLE(
       address_list_t(
         address_t('Rome','00100'),
         address_t('Paris','75001'),
         address_t('Berlin','10115')
       )
     ) a
ORDER BY a.city,a.zip_code

84. Агрегация object collection в XML

SELECT XMLELEMENT(
         "addresses",
         XMLAGG(
           XMLELEMENT("address", XMLFOREST(a.city AS "city", a.zip_code AS "zip"))
         )
       ) AS addr_xml
FROM TABLE(
       address_list_t(
         address_t('Paris','75001'),
         address_t('Rome','00100')
       )
     ) a

85. Преобразование object collection в JSON-like text

SELECT '{"city":"' || a.city || '","zip":"' || a.zip_code || '"}' AS json_txt
FROM TABLE(
       address_list_t(
         address_t('Paris','75001'),
         address_t('Rome','00100')
       )
     ) a

86. Обратное использование collection как фильтра городов

SELECT city_name
FROM city_dim
WHERE city_name IN (
  SELECT a.city
  FROM TABLE(
         address_list_t(
           address_t('Paris','75001'),
           address_t('Berlin','10115')
         )
       ) a
)

87. CTE с object collection и последующей агрегацией

WITH addr AS (
  SELECT a.city,a.zip_code
  FROM TABLE(
         address_list_t(
           address_t('Paris','75001'),
           address_t('Paris','75008'),
           address_t('Rome','00100')
         )
       ) a
)
SELECT city,
       COUNT(*) AS cnt_val
FROM addr
GROUP BY city

88. TABLE() по nested numbers с окном SUM OVER

SELECT column_value,
       SUM(column_value) OVER(ORDER BY column_value) AS running_sum
FROM TABLE(num_list_t(5,10,15))

89. RANK на значениях collection

SELECT column_value,
       RANK() OVER(ORDER BY column_value DESC) AS rnk_val
FROM TABLE(num_list_t(100,80,80,50))

90. Табличное выражение для top-N без физических таблиц

SELECT column_value
FROM TABLE(num_list_t(9,1,4,7))
ORDER BY column_value DESC
FETCH FIRST 2 ROWS ONLY

91. Преобразование string list в длины строк

SELECT column_value,
       LENGTH(column_value) AS len_val
FROM TABLE(code_list_t('oracle','sql','ai'))

92. Prefix-группы для строкового TABLE()

SELECT SUBSTR(column_value,1,1) AS pref,
       COUNT(*) AS cnt_val
FROM TABLE(code_list_t('ALPHA','ATOM','BETA','BOOK'))
GROUP BY SUBSTR(column_value,1,1)

93. Нормализация строк из TABLE() через LOWER/TRIM

SELECT LOWER(TRIM(column_value)) AS norm_val
FROM TABLE(code_list_t(' A ','b ',' C'))

94. Декартово произведение двух коллекций

SELECT a.column_value AS x_val,
       b.column_value AS y_val
FROM TABLE(num_list_t(1,2)) a
JOIN TABLE(code_list_t('A','B')) b ON 1 = 1

95. Конструирование composite label из двух TABLE() источников

SELECT a.column_value || '-' || b.column_value AS label_txt
FROM TABLE(num_list_t(1,2)) a
JOIN TABLE(code_list_t('X','Y')) b ON 1 = 1

96. Join TABLE() и orders по bucket-логике

SELECT o.order_id,t.column_value AS bucket_no
FROM orders o
JOIN TABLE(num_list_t(0,1,2,3)) t
ON MOD(o.order_id,4) = t.column_value

97. Разворот collection и фильтр по BETWEEN

SELECT column_value
FROM TABLE(num_list_t(5,15,25,35))
WHERE column_value BETWEEN 10 AND 30

98. Семантика all-values для TABLE() через HAVING

SELECT COUNT(*) AS cnt_all
FROM TABLE(num_list_t(2,4,6,8))
HAVING MIN(MOD(column_value,2)) = 0
   AND MAX(MOD(column_value,2)) = 0

99. TABLE() как источник для INSERT ALL подготовки

INSERT ALL
  INTO temp_ids(id) VALUES (val_id)
SELECT column_value AS val_id
FROM TABLE(num_list_t(201,202,203))

100. Финальный пример: TABLE() + object collection + XMLAgg + аналитика в одном запросе

WITH addr AS (
  SELECT a.city,a.zip_code
  FROM TABLE(
         address_list_t(
           address_t('Paris','75001'),
           address_t('Rome','00100'),
           address_t('Paris','75008')
         )
       ) a
)
SELECT city,
       COUNT(*) AS cnt_val,
       XMLAGG(XMLELEMENT("zip",zip_code) ORDER BY zip_code) AS zip_xml
FROM addr
GROUP BY city

Заключение

TABLE в Oracle — это гораздо больше, чем просто “таблица”.
Это и обычная реляционная основа, и мост в мир коллекций, nested table, varray и объектных типов.
Когда начинаешь пользоваться table expression осознанно, SQL становится очень гибким:
можно передавать наборы значений без временных объектов, разворачивать коллекции в строки и писать гораздо выразительнее.
Oracle здесь особенно хорош: он позволяет соединить строгий реляционный подход и богатую модель коллекций в одном языке.

Официальная документация Oracle:
Operations on Collection Data Types — TABLE expression
CREATE TYPE Statement
CREATE TABLE
SELECT

 

🔜 Следующая статья:

Оператор PRIMARY в Oracle SQL — как задать основной ключ таблицы

0
Понравилась статья? Поделиться с друзьями:
Вам также может быть интересно

DUAL в Oracle SQL — 100 практических примеров

Есть в Oracle одна маленькая, почти медитативная штука — DUAL. Таблица крошечная, а пользы в ней море. Хочешь быстро проверить выражение, вызвать функцию, собрать тестовый SELECT, подготовить константы…

SELECT в Oracle SQL — 100 простых примеров для старта

SELECT в Oracle SQL. Введение SELECT — основной оператор выборки в Oracle SQL. Он строит результирующие наборы из таблиц и представлений, поддерживает фильтрацию, сортировку, группировку, подзапросы, соединения, аналитические…

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest
0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии