Oracle переменная типа таблица
Но, начиная с 2005 версии ситуация несколько изменилась.
Появились, так называемые табличные переменные
В чем разница?
Во-первых в объявлении. Для объявления табличной переменной необходимо объявить ее:
declare @t1 table (tid int, tname nvarchar(80))
в дальнейшем использование этой переменной ничем не отличается от той же временной таблицы:
declare @t1 table (tid int, tname nvarchar(80))
insert into @t1 values (10,'Акулина');
insert into @t1 values (20,'Бронислав');
insert into @t1 values (30,'Богдан');
insert into @t1 values (40,'Борислав');
select tid, tname from @t1;
Преимущества:
• Автоматически очищаются в конце функции, хранимой процедуры или пакета, где они были определены
• При использовании в хранимых процедурах табличных переменных приходится прибегать к рекомпиляциям реже, чем при использовании временных таблиц
• Транзакции с использованием табличных переменных продолжаются только во время процесса обновления соответствующих табличных переменных. Поэтому табличные переменные реже подвергаются блокировке и требуют меньших ресурсов для ведения журналов регистрации
• Табличной переменной можно присвоить результат выполнения табличной функции, для повторного использования результатов
• Табличную переменную можно передавать как параметр в хранимую процедуру (SQL Server 2008)
Недостатки:
• На табличных переменных нельзя создавать некластерные индексы
• Табличные переменные не содержат статистику
• Табличные переменные не могут использоваться в INSERT EXEC или SELECT INTO
• Запросы, изменяющие табличные переменные, не создают параллельных планов выполнения запроса
В 2008 версии пошли немного дальше… и теперь, для того, что бы использовать какие-нибудь заранее предопределенные данные, уже не нужно создавать ни временную таблицу, ни табличную переменную…
Достаточно обойтись одним оператором value:
select * from (
values (10,'Акулина')
, (20,'Бронислав')
, (30,'Богдан')
, (40,'Борислав')
) t (tid, tname)
или например так:
select max(case when n=1 then id end) id , max(case when n=1 then r end) r1, min(case when n=0 then r end) r2
from
(
select sourceID id , ROW_NUMBER () over (order by sourceID) r
from LoyaltyDiff_BrokerMessagesReceived (nolock)
)t
cross join (values (0) ,(1))nn(n)
Прежде чем использовать переменную или константу в программе, ее почти всегда необходимо объявить. Все объявления должны размещаться в разделе объявлений программы PL/SQL. В PL/SQL объявления могут относиться к переменным, константам, TYPE (например, коллекциям или записям) и исключениям. В этой статье рассматриваются объявления переменных и констант.
Объявление переменной PL/SQL
Когда вы объявляете переменную, PL/SQL выделяет память для хранения ее значения и присваивает выделенной области памяти имя, по которому это значение можно извлекать и изменять. В объявлении также задается тип данных переменной; он используется для проверки присваиваемых ей значений. Базовый синтаксис объявления переменной или константы:
Здесь имя — имя переменной или константы, тип_данных — тип или подтип данных PL/SQL, определяющий, какие значения могут присваиваться переменной. При желании можно включить в объявление выражение NOT NULL ; если такой переменной не присвоено значение, то база данных инициирует исключение. Секция значение_по_умолчанию инициализирует переменную начальным значением; ее присутствие обязательно только при объявлении констант. Если переменная объявляется с условием NOT NULL , то при объявлении ей должно быть присвоено начальное значение.
Примеры объявления переменных разных типов:
Конструкция DEFAULT (см. l_right_now в приведенном примере) и конструкция с оператором присваивания ( l_favorite_flavor в приведенном примере) эквивалентны и взаимозаменяемы. Какой из двух вариантов использовать? Мы предпочитаем для констант использовать оператор присваивания (:=), а для инициализации переменных — ключевое слово DEFAULT . При объявлении константы задается не значение по умолчанию, а значение, которое не может быть изменено впоследствии, поэтому DEFAULT кажется неуместным.
5 Answers 5
In SQL you may only use table type which is defined at schema level (not at package or procedure level), and index-by table (associative array) cannot be defined at schema level. So - you have to define nested table like this
And then you can use it in SQL with TABLE operator, for example:
yes. you can use table type inside a function exactly the same way as in the anonymous PL/SQL block from this answer
actually you can use almost all described objects in package specifications/body. The only thing - you cannot create local type as objects. Only as records is allowed for packages
Prior to Oracle 12C you cannot select from PL/SQL-defined tables, only from tables based on SQL types like this:
In Oracle 12C it is now possible to select from PL/SQL tables that are defined in a package spec.
For example can I declare this kind of type TYPE my_type IS TABLE OF my_table%ROWTYPE instead using of your descibed custom columns?
You can't do it in a single query inside the package - you can't mix the SQL and PL/SQL types, and would need to define the types in the SQL layer as Tony, Marcin and Thio have said.
If you really want this done locally, and you can index the table type by VARCHAR instead of BINARY_INTEGER, you can do something like this:
So inside your loop, wherever you would have expected to use r0.exch_rt_eur you instead use exch_rt(r0.currency).exch_rt_eur , and the same for USD. Testing from an anonymous block:
Based on the answer Stef posted, this doesn't need to be in a package at all; the same results could be achieved with an insert statement. Assuming EXCH holds exchange rates of other currencies against the Euro, including USD with currency_key=1 :
With items valued at 19.99 GBP and 25.00 AUD, you get detail_items :
If you want the currency stuff to be more re-usable you could create a view:
Коллекции присутствую в том или ином виде в большинстве языков программирования и везде имеют схожую суть в плане использования. А именно – позволяют хранить набор объектов одного типа и проводить над всем набором какие-либо действия, либо в цикле проводить однотипные действия со всеми элементами набора.
Таким же образом коллекции используются и в Oracle.
Содержание статьи
Общие сведения о коллекциях в pl/sql
-
Создание коллекции происходит в два этапа
Сначала мы объявляем тип(type) коллекции (конструкции assoc_array_type_def, varray_type_def и nested_table_type_def будут приведены далее)
Типы коллекций
Тип коллекции | Количество элементов | Тип индекса | Плотная или разреженная | Без инициализации | Где объявляется | Использование в SQL |
---|---|---|---|---|---|---|
Ассоциативный массив (index by table) | Не задано | String Pls_integer | Плотная и разреженная | Empty | PL/SQL block Package | Нет |
Varray (variable-size array) | Задано | Integer | Только плотная | Null | PL/SQL block Package Schema level | Только определенные на уровне схемы |
Nested table | Не задано | Integer | При создании плотная, может стать разреженной | Null | PL/SQL block Package Schema level | Только определенные на уровне схемы |
Плотность коллекции означает, что между элементами коллекции нет пропусков, пустых мест. Некоторые коллекции, как видно из таблицы, могут быть разреженными – т.е. могут иметь разрывы между элементами. Это значит, что в коллекции, например, могут быть элементы с индексом 1 и 4, а с индексом 2 и 3 элементов нет. При этом слоты памяти под 2-й и 3-й элементы будут существовать и будут принадлежать коллекции (в случае nested table), но не содержать при этом объектов и попытка прочитать содержимое этих элементов вызовет ошибку no_data_found.
Подробности можно узнать из видео-лекции в конце статьи.
Ассоциативный массив
- Набор пар ключ-значение
- Данные хранятся в отсортированном по ключу порядке
- Не поддерживает DML-операции (не может участвовать в селектах, не может храниться в таблицах)
- При объявлении как константа должен быть сразу инициализирован функцией
- Порядок элементов в ассоциативном массиве с строковым индексом зависит от параметров NLS_SORT и NLS_COMP
- Нельзя объявить тип на уровне схемы, но можно в пакете
- Не имеет конструктора
- Индекс не может принимать значение null (но допускает пустую строку — подробности и ссылка на пример в первом комментарии)
- Datatype – это любой тип данных, кроме ref cursor
- Для помещения в память небольших таблиц-справочников
- Для передачи в качестве параметра коллекции
Restrictions:
При изменении параметров NLS_SORT и NLS_COMP во время сессии после заполнения ассоциативного массива, можем получать неожиданные результаты вызовов методов first, last, next, previous. Также могут возникнуть проблемы при передаче ассоциативного массива в качестве параметра на другую БД с иными настройками NLS_SORT и NLS_COMP
Varray
Представляет собой массив последовательно хранящихся элементов
Тип описывается следующим образом (varay_type_def):
- Размер задается при создании
- Индексируется с 1
- Инициализируется конструктором
- Если параметры в конструктор не передаются, возвращается пустая коллекция
- Datatype – это любой тип данных, кроме ref cursor
- Знаем максимально возможное количество элементов
- Доступ к элементам последовательный
Restrictions:
Максимальный размер – 2 147 483 647 элементов
Nested table
Тип описывается следующим образом (nested_table_type_def):
- Размер коллекции изменяется динамически
- Может быть в разряженном состоянии, как показано на картинке
- Инициализируется конструктором
- Если параметры в конструктор не передаются, возвращается пустая коллекция
- Datatype – это любой тип данных, кроме ref cursor
- Если содержит только одно скалярное значение, то имя колонки – Column_Value
Set operations с nested tables
Операции возможны только с коллекциями nested table. Обе коллекции, участвующие в операции, должны быть одного типа.
Результатом операции также является коллекция nested table.
Операция | Описание |
---|---|
MULTISET UNION | Возвращает объединение двух коллекций |
MULTISET UNION DISTINCT | Возвращает объединение двух коллекций с дистинктом (убирает дубли) |
MULTISET INTERSECT | Возвращает пересечение двух коллекций |
MULTISET INTERSECT DISTINCT | Возвращает пересечение двух коллекций с дистинктом (убирает дубли) |
SET | Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей) |
MULTISET EXCEPT | Возвращает разницу двух коллекций |
MULTISET EXCEPT DISTINCT | Возвращает разницу двух коллекций с дистинктом (убирает дубли) |
Небольшой пример (обратите внимание на результат операции MULTISET EXCEPT DISTINCT)
Те, кто пришел в Oracle из MSSQL, наверняка столкнулись (как и я) с массой неожиданностей.
create function Foo1 (param1 nvarchar, param2 decimal(18,2))
return table (
id number,
nn nvarchar(50)
)
as
.
Знакомо, не правда ли? Если подобная функция прекрасно возвращала ADO Recordset из MS SQL, то в Oracle такой халявы нет. Однако получать наборы данных из функций через ADO просто необходимо, если мы хотим придерживаться грамотной структуры объектной модели.
Создадим две таблицы — сотрудников и подразделений.
--создание табличных пространств
create tablespace ALEX_DATA datafile 'C:\oracle\user_data\tblsp_alexdata.dat'
size 10M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 200M;
create tablespace ALEX_INDEX datafile 'C:\oracle\user_data\tblsp_alexix.dat'
size 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 200M;
/
--создание таблиц
create table ALEX.T_EMPLOYEES(
id number(5) not null,
id_department number(5) not null,
empinfo nvarchar2(50) not null
) tablespace ALEX_DATA;
create table ALEX.T_DEPARTMENTS(
id number(5) not null,
depinfo nvarchar2(50) not null
) tablespace ALEX_DATA;
/
--создание индексов
create index IXPK_T_EMPLOYEES on ALEX.T_EMPLOYEES(id)
tablespace ALEX_INDEX;
create index IXPK_T_DEPARTMENTS on ALEX.T_DEPARTMENTS(id)
tablespace ALEX_INDEX;
/
--создание реляционных связей
alter table ALEX.T_DEPARTMENTS
add constraint PK_T_DEPARTMENTS primary key (ID) using index IXPK_T_DEPARTMENTS;
/
alter table ALEX.T_EMPLOYEES
add constraint PK_T_EMPLOYEES primary key (ID) using index IXPK_T_EMPLOYEES
add constraint FK_T_DEPARTMENTS foreign key (id_department)
references ALEX.T_DEPARTMENTS(id);
/
--демо-данные
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (1, 'Отдел кадров');
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (2, 'Информационный отдел');
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (3, 'Бухгалтерия');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(1, 1, 'Иванов');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(2, 1, 'Борисов');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(3, 2, 'Сергеев');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(4, 3, 'Никитин');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(5, 3, 'Александров');
Наша цель — написать функцию, которая бы возвращала список сотрудников отдела, id которого передается в качестве параметра.
Для начала нам нужно описать тип данных, возвращаемый таблицей.
--тип данных строки, возвращаемой GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, --привязка к типу поля empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);
Это тип данных строки. Атрибут TYPE объявляет для переменной тип, идентичный указанному полю. Создаем второй тип:
type tblGetEmployees is table of rowGetEmployees;
Это таблица из строк типа rowGetEmployees. Переменную этого типа будет возвращать наша функция:
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined;
Если параметр не передан, будем возвращать список всех сотрудников. Атрибут pipelined означает, что функция является конвейерной, результат возвращается клиенту немедленно при вызове директивы pipe row, поэтому оператор return необязателен. Фактически, по результирующему набору из запроса в теле функции проходит курсор, который при каждой итерации добавляет в рекордсет текущую строку.
Поместим типы данных и функцию в пакет. На выходе имеем
create or replace package ALEX.P_MY1 is
--тип данных строки, возвращаемой GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, --привязка к типу поля empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);
--тип данных таблицы из строк rowGetEmployees
type tblGetEmployees is table of rowGetEmployees;
--
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined;
create or replace package body ALEX.P_MY1 is
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined
is
begin
if prm_depID is null then
for curr in
(
select emp.empinfo, dep.depinfo
from ALEX.T_DEPARTMENTS dep inner join
ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
) loop
pipe row (curr);
end loop;
else
for curr in
(
select emp.empinfo, dep.depinfo
from ALEX.T_DEPARTMENTS dep inner join
ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
where dep.id = prm_depID
) loop
pipe row (curr);
end loop;
end if;
end GetEmployees;
Привязка к курсорам и таблицам
Мы уже рассмотрели примеры объявления переменных с привязкой к столбцу базы данных и другой переменной PL/SQL. Теперь давайте посмотрим, как используется атрибут привязки % ROWTYPE .
Допустим, нам нужно выбрать одну строку из таблицы books. Вместо того чтобы с помощью атрибута %TYPE объявлять для каждого столбца таблицы отдельную переменную, можно воспользоваться атрибутом %ROWTYPE :
Теперь предположим, что из таблицы book необходимо выбрать только имя автора и название книги. В этом случае мы сначала явно определим курсор, а затем на его основе объявим переменную:
Наконец, следующий пример демонстрирует неявное использование атрибута %ROWTYPE в объявлении записи book_rec цикла FOR :
Объявление константы PL/SQL
Между объявлениями переменных и констант существует два различия: объявление константы содержит ключевое слово CONSTANT , и в нем обязательно задается ее значение, которое не может быть изменено впоследствии:
Несколько примеров объявления констант:
Неименованная константа представляет собой литеральное значение — например, 2 или 'Bobby McGee'. Литерал не обладает именем, но имеет тип данных, который не объявляется, а определяется непосредственно на основании значения.
Объявления с привязкой
При объявлении переменной тип данных очень часто задается явно:
В Oracle также существует другой метод объявления переменных, называемый объявлением с привязкой (anchored declaration). Он особенно удобен в тех случаях, когда значение переменной присваивается из другого источника данных, например из строки таблицы.
Объявляя «привязанную» переменную, вы устанавливаете ее тип данных на основании типа уже определенной структуры данных. Таковой может являться другая переменная PL/SQL, заранее определенный тип или подтип ( TYPE или SUBTYPE ), таблица базы данных либо конкретный столбец таблицы.
В PL/SQL существует два вида привязки.
- Скалярная привязка. С помощью атрибута %TYPE переменная определяется на основании типа столбца таблицы или другой скалярной переменной PL/SQL.
- Привязка к записи. Используя атрибут %ROWTYPE , можно определить переменную на основе таблицы или заранее определенного явного курсора PL/SQL.
Синтаксис объявления переменной с привязкой:
Здесь имя_переменной — это имя объявляемой переменной, тип_атрибута — либо имя ранее объявленной переменной PL/SQL, либо спецификация столбца таблицы в формате таблица.столбец.
Привязка разрешается на стадии компиляции кода и не приводит к увеличению времени выполнения. Кроме того, привязка устанавливает зависимость между программным кодом и привязываемым элементом (таблицей, курсором или пакетом, содержащим переменную). Это означает, что при изменении данного элемента привязанный к нему программный код помечается как недействительный ( INVALID ). При повторной компиляции привязка выполняется заново, и таким образом код согласуется с измененным элементом.
На рис. 1 показано, как тип данных определяется на основе столбца таблицы базы данных и переменной PL/SQL.
Рис. 1. Атрибут TYPE при объявлении с привязкой
Пример привязки переменной к столбцу таблицы:
Аналогичным образом выполняется привязка к переменной PL/SQL; обычно это делается для того, чтобы избежать избыточных объявлений одного и того же жестко закодированного типа данных. В таких случаях в пакете обычно создается переменная, на которую затем ссылаются в программах с помощью атрибута %TYPE . (Также можно создавать в пакете подтипы SUBTYPE ; эта тема рассматривается далее в этой главе.) В следующем примере приведен фрагмент кода пакета, упрощающего использование Oracle Advanced Queuing (AQ):
Наличие объявлений с привязкой свидетельствует о том, что PL/SQL является не просто процедурным языком программирования, а разработан как расширение языка Oracle SQL. Корпорация Oracle приложила большие усилия к тому, чтобы интегрировать программные конструкции PL/SQL с базами данных, для работы с которыми используется SQL.
Одно из важнейших преимуществ объявлений с привязкой заключается в том, что они позволяют писать очень гибкие приложения, которые легко адаптируются к последующим изменениям структур данных.
NOT NULL
Если переменной присваивается значение по умолчанию, вы также можете указать, что переменная всегда должна оставаться определенной (отличной от NULL ). Для этого в объявление включается выражение NOT NULL . Например, следующее объявление инициализирует переменную company_name и указывает, что переменная всегда должна оставаться отличной от NULL :
При попытке выполнения следующей операции в программе будет инициировано исключение VALUE_ERROR :
Кроме того, следующее объявление приводит к ошибке компиляции, так как в объявлении не указано исходное значение:
Преимущества объявлений с привязкой
Во всех объявлениях тип переменной (символьный, числовой, логический и т. д.) задается явно. В каждом объявлении непосредственно указывается тип данных и, как правило, ограничение, налагаемое назначение этого типа. Это распространенный подход к объявлению переменных, но в некоторых ситуациях он может вызвать проблемы.
In the package header :
Declared the following row type:
And this table type:
Added a variable:
In the package body:
Fill this table variable with some data.
In a procedure in the package body:
I want to use the following statement:
How to do this in Oracle ?
Notes
Actually I'm looking for the 'Table Variable' solution in MSSQL:
And use this Table Variable inside my StoredProcedure.
As others have stated you can't do this with types declared in the package. It's not clear why you're doing this rather than using a real table to hold the exchange rates, or what you want to do with the selected data. Is this in a cursor? Are you really looking for data for a specific currency from your select? Are you manipulating an item value based on an exchange rate? More context might help inspire alternative approaches.
Читайте также: