Шаг 57 - PL/SQL - составные типы данных

Пришло, я думаю, время разобрать составной тип, который, по моему мнению, является наиболее мощным и простым средством скоростной обработки больших массивов данных. Такой составной тип имеет название TABLE! Рассмотрим его синтаксис: (он подобен синтаксису RECORD)

----- TYPE - тип таблицы - IS TABLE OF тип - INDEX BY BINARY_INTEGER ---------

Вот так просто объявить составной тип TABLE! В своей сути это одномерный массив скалярного типа. Он не может содержать тип RECORD или другой тип TABLE. Но может быть объявлен от любого другого стандартного типа. И это еще не все! TABLE может быть объявлен от атрибута %ROWTYPE! Вот где скрывается, по истине огромная мощь, этого типа данных! Но пока, все по порядку. Тип TABLE, можно представить как одну из разновидностей коллекции. Хотя в более глубоком понимании это не совсем так. При первом рассмотрении он похож на массив языка C. Массив TABLE индексируется типом BINARY_INTEGER и может содержать, что-то вроде - 2,147,483,647 - 0 - + 2,147,483,647 как в положительную, так и в отрицательную часть. Начинать индексацию можно с 0 или 1 или любого другого числа. Если массив будет иметь разрывы, то это не окажет какой-либо дополнительной нагрузки на память. Так же следует помнить, что для каждого элемента, например, типа VARCHAR2 будет резервироваться столько памяти, сколько вы укажете, по этому определяйте размерность элементов по необходимости. Скажем не стоит объявлять VARCHAR2(255), если хотите хранить строки менее 100 символов! :) Итак, давайте объявим массив и посмотрим, как он работает. Запишем такой блок:

SET SERVEROUTPUT ON
DECLARE

	TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
	INDEX BY BINARY_INTEGER;

	TYPE m_SmplTblData IS TABLE OF DATE
	INDEX BY BINARY_INTEGER;

	
	MY_TBL m_SmplTable;
	MY_TBL_DT m_SmplTblData;

BEGIN

MY_TBL(1) := 'Buber';
MY_TBL_DT(2) := SYSDATE - 1;

DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(TO_CHAR(MY_TBL(1)));
DBMS_OUTPUT.put_line(TO_CHAR(MY_TBL_DT(2)));

END;
/

Получаем после обработки в SQL*Plus:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  
  3  	TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
  4  	INDEX BY BINARY_INTEGER;
  5  
  6  	TYPE m_SmplTblData IS TABLE OF DATE
  7  	INDEX BY BINARY_INTEGER;
  8  
  9  
 10  	MY_TBL m_SmplTable;
 11  	MY_TBL_DT m_SmplTblData;
 12  
 13  BEGIN
 14  
 15  MY_TBL(1) := 'Buber';
 16  MY_TBL_DT(2) := SYSDATE - 1;
 17  
 18  DBMS_OUTPUT.enable;
 19  DBMS_OUTPUT.put_line(TO_CHAR(MY_TBL(1)));
 20  DBMS_OUTPUT.put_line(TO_CHAR(MY_TBL_DT(2)));
 21  
 22  END;
 23  /
Buber
01.11.03                                                                        

Процедура PL/SQL успешно завершена.

Рассмотрим, что же здесь происходит. Мы объявили две одномерные коллекции m_SmplTable, m_SmplTblData, одна из них содержит элементы размерностью VARCHAR2(128), другая DATE. Затем объявили две переменные данного типа - MY_TBL, MY_TBL_DT и присвоили первому элементу строкового массива значение "Buber", а второму элементу массива дат, значение текущей даты минус 1. Результат вывели на консоль! Вот собственно и все. При этом хорошо видно, что тип TABLE очень схож с таблицей БД и содержит обычно два ключа KEY и VALUE, ключ и значение. Ключ имеет тип BINARY_INTEGER:

-- переменная(KEY)VALUE --

В нашем случае имеет место:

Ключ (KEY)  Значение (VALUE)
	MY_TBL
1          Buber
	MY_TBL_DT
2         01.11.03

При этом можете обратить внимание на следующее:

  1. Число строк таблицы ни чем не ограничено. Единственное ограничение это значения, которые могут быть представлены типом BINARY_INTEGER.
  2. Порядок элементов таблицы PL/SQL не обязательно должен быть строго определен. Эти элементы хранятся в памяти не подряд как массивы и по этому могут вводится с произвольными ключами.
  3. Ключи, используемые в таблице PL/SQL, не обязательно должны быть последовательными. В качестве индекса таблицы может быть использовано любое значение или выражение имеющее тип BINARY_INTEGER.

Вот такие правила действуют при определении и работе с таблицами PL/SQL (не путать с таблицами БД!!!). Так же при обращении к несуществующему элементу получите сообщение об ошибке! Давайте запишем вот такой блок:

SET SERVEROUTPUT ON
DECLARE

	TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
	INDEX BY BINARY_INTEGER;

	MY_TBL m_SmplTable;
	
BEGIN

FOR i IN 1..10 LOOP 
MY_TBL(i) := TO_CHAR(i+5); 
END LOOP;

DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(TO_CHAR(MY_TBL(5)));

END;
/

Получаем:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  
  3  	TYPE m_SmplTable IS TABLE OF VARCHAR2(128)
  4  	INDEX BY BINARY_INTEGER;
  5  
  6   	MY_TBL m_SmplTable;
  7  
  8  BEGIN
  9  
 10  FOR i IN 1..10 LOOP
 11  MY_TBL(i) := TO_CHAR(i+5);
 12  END LOOP;
 13  
 14  DBMS_OUTPUT.enable;
 15  DBMS_OUTPUT.put_line(TO_CHAR(MY_TBL(5)));
 16  
 17  END;
 18  /
10                                                                              

Процедура PL/SQL успешно завершена.

Здесь мы объявили коллекцию из строковых переменных, и с помощью известного вам цикла FOR записали с 1-го по 10-й элемент значениями исходный плюс пять. Затем мы вывели на экран пятый элемент, как и следовало ожидать его значение равно 10 (5+5). Вот один из способов наполнения массива значениями. Но этот способ бесполезен и показан в качестве примера. А, вот следующий пример более осмыслен. Перепишем блок из предыдущего шага вот так:

DECLARE

TYPE is_Customers IS TABLE OF CUSTOMERS%ROWTYPE
	INDEX BY BINARY_INTEGER;
	
MY_CUST is_Customers;

BEGIN

SELECT * INTO MY_CUST(100) 
FROM CUSTOMERS WHERE CUST_NUM = 2108;

DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(TO_CHAR(MY_CUST(100).CUST_NUM)||' '||MY_CUST(100).COMPANY||' '||
TO_CHAR(MY_CUST(100).CUST_REP)||' '||TO_CHAR(MY_CUST(100).CREDIT_LIMIT));

END;
/

Получаем:

SQL> DECLARE
  2  
  3  TYPE is_Customers IS TABLE OF CUSTOMERS%ROWTYPE
  4  	INDEX BY BINARY_INTEGER;
  5  
  6  MY_CUST is_Customers;
  7  
  8  BEGIN
  9  
 10  SELECT * INTO MY_CUST(100)
 11  FROM CUSTOMERS WHERE CUST_NUM = 2108;
 12  
 13  DBMS_OUTPUT.enable;
 14  DBMS_OUTPUT.put_line(TO_CHAR(MY_CUST(100).CUST_NUM)||' '||MY_CUST(100).COMPANY||' '||
 15  TO_CHAR(MY_CUST(100).CUST_REP)||' '||TO_CHAR(MY_CUST(100).CREDIT_LIMIT));
 16  
 17  END;
 18  /
2108 Унесенные ветром 109 55,323                                                

Процедура PL/SQL успешно завершена.

Здесь мы объявили коллекцию, с типом запись из таблицы CUSTOMERS! И получили тот же результат как в прошлый раз, но более элегантно! Итак, объявляем тип:

TYPE is_Customers IS TABLE OF CUSTOMERS%ROWTYPE
INDEX BY BINARY_INTEGER;

Это значит, что каждая строка коллекции содержит полную запись из таблицы БД CUSTOMERS. Замечательно. Далее 100 элементу массива присваиваем значение записи таблицы с индексом 2108. Вот так это выглядит изнутри:

MY_CUST(98)
MY_CUST(99)
.
MY_CUST(100) ->
MY_CUST(100).CUST_NUM MY_CUST(100).COMPANY MY_CUST(100).CUST_REP MY_CUST(100).CREDIT_LIMIT
  2108                   Унесенные ветром            109                   55,323
.
.
.		
MY_CUST(n)

Обращаться к отдельному элементу (полю) записи можно через точечную нотацию. Что мы и проделали с вами в примере! Так же скажу, что используя привязку таблиц PL/SQL к массивам интерфейса OCI можно достичь очень высокой скорости передачи данных в клиентских приложениях БД. В следующий раз рассмотрим атрибуты таблиц PL/SQL.


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей - 02.11.2003