Использование CAST и табличных функций в PL/SQL

Резюме: В Oracle8 i была введена функция CAST, которая позволяет обрабатывать PL/SQL-коллекции (collection), как обычные таблицы. Когда CAST применяется в сочетании с с табличными функциями, это становится еще более мощным средством манипулирования данными. Эта статья содержит краткое технологическое толкование работы CAST и табличных функций на нескольких реальных примерах, практическое использование которых может представлять интерес.

Я помогал одному из наших разработчиков разобраться с проблемой в довольно сложной хранимой процедуре, которая широко используется нашим головным приложением по вводу данных, чтобы определить все приемлемые кредитные организации, где некие служащие потенциально могут получить кредит на расходы. Вот краткий перечень используемых при этом бизнес-правил:

служащий - это часть отдела (department), который в свою очередь является частью отделения (division);

каждый служащий может обратиться в от одной до пяти кредитных организаций;

каждый отдел имеет до пяти заданных по умолчанию кредитных организаций, в которых можно взять деньги;

каждое отделение имеет до пяти заданных по умолчанию кредитных организаций, в которых можно взять деньги;

коль скоро списки потенциально имеющих такое право служащих, заданных по умолчанию кредитных организаций для отделов и отделений уже определены, то только те кредитные организации, в которых может обращаться любой служащий, следует сохранить в списке;

из окончательного списка должны быть удалены дубли имен кредитных организаций.

Чтобы наиболее правильно поставить вопросы, хранимая процедура, которая реализует эти бизнес-правила, должна возвратить ответный набор в форме ссылочного курсора (REF CURSOR), как того требует приложение. К сожалению, приложение не может принять в качестве входного параметра ни одного типа Oracle-коллекций без существенного изменения устаревшего основанного на Power Builder кода.

Когда я открыл эту хранимую процедуру, я заметил, что она когда-то была конвертирована из Sybase-оригинала в нашу нынешнюю базу данных Oracle. База данных Sybase имеет несколько интересных возможностей по организации хранения временных данных - огромное, по существу, TEMP-пространство, которое постоянно доступно для использования любой хранимой процедурой. И все, кто конвертировали такую процедуру в Oracle, подражали этой методике, используя GTT (GLOBAL TEMPORARY TABLE - глобальную временную таблицу), чтобы сохранить данные.

GTT-таблицы, конечно, имеются в базе данных Oracle, но они обладают некоторыми недостатками. Во-первых, GTT - все-таки таблица, а, как я заметил, разработчики часто забывают выполнить COMMIT для фиксации изменений после записи в GTT. Кроме того, накладные издержки от создания и поддержания схемы для GTT в ситуациях, подобно описанной, часто являются слишком большими. В конце концов, наибольшее число записей, которые когда-либо ко мне возвращались в этом ответом наборе, было 15.

Я также столкнулся с проблемами при попытке открыть базу данных горячего резервирования в режиме READ ONLY, а затем пробовать выполнить хранимые процедуры, которые должны были использовать GTT-таблицы. Поскольку GTT-таблицы принадлежат табличному пространству SYSTEM, и это табличное пространство находится в режиме read-only (только для чтения). Когда же резервная база открывается таким способом для составления отчетов, хранимые процедуры, использовавшиеся для этой цели, просто прекращали работать. Есть обходные пути, чтобы разрешить эту ситуацию, но они не очень элегантны.

К счастью, Oracle реализовал некоторые возможности, которые позволили мне преодолеть зависимость от GTT: функция CAST и способность создавать хранимые функции, которые возвращают типы PL/SQL-коллекций, известные также как табличные функции. Когда эти возможности используется в сочетании друг с другом, то формируется мощный комплект инструментальных средств, который подчиняет себе GTT-таблицы, использовавшиеся до этого способа. (Кроме того, они [CAST + табличные функции] работают настолько хорошо, даже если вам не нужно бороться с какими-то ни было GTT-таблицами, почему бы их не попробовать!)

Функция CAST

CAST часто называют pseudo-table function ( псевдотабличной функцией) , потому что она позволяет мне cast a variable ( приводить переменную ) - а именно, PL/SQL-коллекцию - в другой тип данных (datatype): в табличную структуру. К табличной структуре может быть сделан стандартный SQL-запрос точно так же, как к любой другой таблице Oracle на SQL.

Листинг 1.1 показывает пример, как может быть использована функция CAST в анонимном PL/SQL-блоке, чтобы прочитать PL/SQL-коллекцию, определенную декларируемым TYPE. CAST используется здесь для сортировки результирующих строк в обратном алфавитном порядке. Я мог бы создать TYPE как истинный объект и построить функцию сортировки для этого объекта, но CAST позволяет мне использовать добрый старый SQL, чтобы выполнить сортировку.

Листинг 1.2 показывает другой пример CAST. На сей раз я заполняю PL/SQL - коллекцию набором случайных чисел. Затем я использую CAST, чтобы набрать данные из коллекции и применить различные групповые функции, как-то SUM(), MIN() и MAX() на результирующем наборе. И опять же я мог объявить объектный тип и написать некие специальные функции группировки. Но снова я использовал CAST, чтобы сделать работу при помощи обычных групповых SQL-функций.

Табличные функции

Табличная функция - это хранимая функция, которая возвращает PL/SQL - коллекцию как результирующий набор, и функция CAST может затем этот набор читать и им манипулировать. В качестве примера табличной функции, sf_gather_cost_centers, которую я создал в Листинге 1.3, используется PL/SQL - коллекция, хранящая собранные названия кредитных организаций для указанного служащего и возвращающая список всех кредитных организаций, которыми служащий имеет право пользоваться, в зависимости от отношений его к отделу и отделению.

На Листинге 1.4 показаны три примера, которые собирают результаты табличной функции, которую я только что создал через псевдо-функциональную CAST-таблицу для более поздних манипуляций. Результирующий набор этих SQL-предложений может быть затем возвращен в ссылочном курсоре, сгенерированном с несколькими модификациями внутри существующей хранимой процедуры, как мне предписано в соответствии с первоначальными требованиями.

Конвейерные табличные функции

Табличные функции были доступны, начиная с Oracle 8i , они были расширены в Oracle 9i так, что результирующие наборы могут быть pipelined (конвейерными) . Кратко, конвейерная (pipelined) табличная функция не требует, чтобы псевдотабличная CAST- функция возвращала результирующий набор.

Листинг 1.5 показывает модифицированную версию той же самой функции, которую я создал в Листинге 1.3, а на Листинге 1.6 показаны те же самые примеры, что и на Листинге 1.4, но теперь не связанных с псевдофункцией CAST.

Заключение

CAST и табличные функции стали мощным набором инструментальных средств моей PL/SQL-среды разработки. И я рассчитываю на ваш интерес в надежде, что вы также захотите поэкспериментировать с этими средствами. Если Вы пожелаете поработать с моими примерами, я на Листинге 2 поместил необходимый язык описания данных и DML-инструкции, чтобы изменить стандартную демонстрационную схему HR.

// Листинг 1. Функция CAST и табличные функции

//Содержит практические примеры применения

// CAST и табличных функций

//автор: Jim Czuprynski

// Предупреждение:

// Этот скрипт предназначен только для демонстрации различных

// возможности Oracle и должен быть тщательнопроверен перед выполнением

// на любой работающей базе данных Oracle, чтобы исключить какую-либо

// потенциальную опасность.

-----

-- Листинг1.1: Сортировка PL/SQL, включаемая по CAST

-----

DROP TYPE person_names_t;

CREATE OR REPLACE TYPE person_names_t AS TABLE OF VARCHAR2(100);

SET SERVEROUTPUT ON

DECLARE

-- List of presidents since 1932, in no particular order

presidents_t person_names_t := person_names_t(

"Bush, George W. - 2000",

"Bush, George H. W. - 1988",

"Johnson, Lyndon B. - 1963",

"Reagan, Ronald W. - 1980",

"Clinton, William J. -1992",

"Truman, Harry S. - 1945",

"Roosevelt, Franklin D. - 1932",

"Eisenhower, Dwight D. - 1952",

"Kennedy, John F. - 1960",

"Nixon, Richard M. - 1968",

"Ford, Gerald R. - 1976",

"Carter, Jimmy - 1980"

);

BEGIN

-- Display all table entries in descending sequence

DBMS_OUTPUT.PUT_LINE("Presidents after 1932,

in reverse alphabetical order:");

FOR rec IN (SELECT column_value favs

FROM TABLE (CAST (presidents_t AS person_names_t))

ORDER BY column_value DESC)

LOOP

DBMS_OUTPUT.PUT_LINE(rec.favs);

END LOOP;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

статья

-----

-- Листинг 1.2: Использование CAST с групповыми функциями

-----

DROP TYPE numbers_t;

CREATE OR REPLACE TYPE numbers_t AS TABLE OF NUMBER(10);

DECLARE

random_numbers numbers_t := numbers_t(

1000,

100,

500,

3000,

4000,

2000,

300,

400,

200

);

tot_entries NUMBER(10) := 0;

sum_number NUMBER(10) := 0;

min_number NUMBER(10) := 0;

max_number NUMBER(10) := 0;

BEGIN

SELECT

SUM(Column_value) total,

COUNT(Column_value) tally,

MIN(Column_value) bottom,

MAX(Column_value) top

INTO

sum_number,

tot_entries,

min_number,

max_number

FROM TABLE(CAST(random_numbers AS numbers_t));

DBMS_OUTPUT.PUT_LINE("Results from Random Number Survey");

DBMS_OUTPUT.PUT_LINE("Count: " // tot_entries );

DBMS_OUTPUT.PUT_LINE("Total: " // sum_number );

DBMS_OUTPUT.PUT_LINE("Minimum: " // min_number );

DBMS_OUTPUT.PUT_LINE("Maximum: " // max_number );

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

статья

-----

-- Листинг 1.3: Создание объектных TYPE и табличная функция

-----

DROP TYPE wgt_cost_ctr;

DROP TYPE wgt_cost_ctr_t;

CREATE OR REPLACE TYPE wgt_cost_ctr IS OBJECT (

cc_lvl NUMBER(3),

cc_nbr NUMBER(5),

cc_value VARCHAR2(32)

);

CREATE OR REPLACE TYPE wgt_cost_ctr_t AS TABLE OF wgt_cost_ctr;

CREATE OR REPLACE FUNCTION hr.sf_gather_cost_centers (

a_employee_id IN hr.employees.employee_id%TYPE

) RETURN hr.wgt_cost_ctr_t

// Функция: sf_gather_cost_centers

// Описание: Использование типа Cost Center, ассоциированного с имеющимся

// списком служащих для выбора кредитных организаций, подходящих к иерархии

// Отделение/Отдел/Служащий

l_department_id NUMBER(5) := 0;

l_division_id NUMBER(5) := 0;

retval wgt_cost_ctr_t := wgt_cost_ctr_t();

CURSOR cur_cost_ctr_asgn (

a_entity_id IN hr.cost_center_assignments.entity_id%TYPE,

a_entity_type IN hr.cost_center_assignments.entity_type%TYPE

) IS

SELECT

DECODE(a_entity_type,"V", 1, "D", 2, "E", 3, NULL) cc_lvl,

CCA.cost_ctr_id cc_nbr,

CC.description cc_value

FROM

hr.cost_center_assignments CCA,

hr.cost_centers CC

WHERE CCA.COST_CTR_ID = CC.COST_CTR_ID

AND CCA.entity_id = a_entity_id

AND CCA.entity_type = a_entity_type;

PROCEDURE expand_collection (cc_in IN wgt_cost_ctr)

// Procedure: expand_collection

// Adds the specified entry to the collection

BEGIN

retval.EXTEND;

retval(retval.LAST) := cc_in;

END;

BEGIN

-- Get the Department ID and Division ID for the specified Employee

SELECT

E.department_id,

D.division_id

INTO

l_department_id,

l_division_id

FROM

hr.employees E,

hr.departments D,

hr.divisions V

WHERE E.Department_Id = D.Department_Id

AND D.division_id = V.division_id

AND E.employee_id = a_employee_id;

-- Gather eligible Cost Centers for the specified Employee

FOR rec_cost_ctr_asgn IN cur_cost_ctr_asgn(a_employee_id, "E")

LOOP

expand_collection(wgt_cost_ctr(

rec_cost_ctr_asgn.cc_lvl,

rec_cost_ctr_asgn.cc_nbr,

rec_cost_ctr_asgn.cc_value)

);

END LOOP;

-- Gather eligible Cost Centers for the specified Employee"s Department

FOR rec_cost_ctr_asgn IN cur_cost_ctr_asgn(l_department_id, "D")

LOOP

expand_collection(wgt_cost_ctr(

rec_cost_ctr_asgn.cc_lvl,

rec_cost_ctr_asgn.cc_nbr,

rec_cost_ctr_asgn.cc_value)

);

END LOOP;

-- Gather eligible Cost Centers for the specified Employee"s Division

FOR rec_cost_ctr_asgn IN cur_cost_ctr_asgn(l_division_id, "V")

LOOP

expand_collection(wgt_cost_ctr(

rec_cost_ctr_asgn.cc_lvl,

rec_cost_ctr_asgn.cc_nbr,

rec_cost_ctr_asgn.cc_value)

);

END LOOP;

RETURN retval;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line("Fatal error encountered!");

RETURN retval;

END sf_gather_cost_centers;

статья

-----

-- Листинг 1.4: Использование табличной функции с CAST

-----

SELECT *

FROM TABLE (CAST (sf_gather_cost_centers (114)

AS wgt_cost_ctr_t));

SELECT *

FROM TABLE (CAST (sf_gather_cost_centers (120)

AS wgt_cost_ctr_t));

SELECT * FROM (

SELECT

DISTINCT *

FROM TABLE (CAST (sf_gather_cost_centers (120)

AS wgt_cost_ctr_t))

ORDER BY cc_lvl DESC

)

WHERE rownum <= 5;

статья

-----

-- Листинг 1.5: Использование коныейерной (PIPELINED) табличной функции

-----

CREATE OR REPLACE FUNCTION hr.sf_gather_cost_centers (

a_employee_id IN hr.employees.employee_id%TYPE

) RETURN hr.wgt_cost_ctr_t PIPELINED

// Функция: sf_gather_cost_centers (конвейерная)

l_department_id NUMBER(5) := 0;

l_division_id NUMBER(5) := 0;

CURSOR cur_cost_ctr_asgn (

a_entity_id IN hr.cost_center_assignments.entity_id%TYPE,

a_entity_type IN hr.cost_center_assignments.entity_type%TYPE

) IS

SELECT

DECODE(a_entity_type,"V", 1, "D", 2, "E", 3, NULL) cc_lvl,

CCA.cost_ctr_id cc_nbr,

CC.description cc_value

FROM

hr.cost_center_assignments CCA,

hr.cost_centers CC

WHERE CCA.COST_CTR_ID = CC.COST_CTR_ID

AND CCA.entity_id = a_entity_id

AND CCA.entity_type = a_entity_type;

BEGIN

-- Get the Department ID and Division ID for the specified Employee

SELECT

E.department_id,

D.division_id

INTO

l_department_id,

l_division_id

FROM

hr.employees E,

hr.departments D,

hr.divisions V

WHERE E.Department_Id = D.Department_Id

AND D.division_id = V.division_id

AND E.employee_id = a_employee_id;

-- Gather eligible Cost Centers for the specified Employee

FOR rec_cost_ctr_asgn IN cur_cost_ctr_asgn(a_employee_id, "E")

LOOP

PIPE ROW(wgt_cost_ctr(

rec_cost_ctr_asgn.cc_lvl,

rec_cost_ctr_asgn.cc_nbr,

rec_cost_ctr_asgn.cc_value)

);

END LOOP;

-- Gather eligible Cost Centers for the specified Employee"s Department

FOR rec_cost_ctr_asgn IN cur_cost_ctr_asgn(l_department_id, "D")

LOOP

PIPE ROW(wgt_cost_ctr(

rec_cost_ctr_asgn.cc_lvl,

rec_cost_ctr_asgn.cc_nbr,

rec_cost_ctr_asgn.cc_value)

);

END LOOP;

-- Gather eligible Cost Centers for the specified Employee"s Division

FOR rec_cost_ctr_asgn IN cur_cost_ctr_asgn(l_division_id, "V")

LOOP

PIPE ROW(wgt_cost_ctr(

rec_cost_ctr_asgn.cc_lvl,

rec_cost_ctr_asgn.cc_nbr,

rec_cost_ctr_asgn.cc_value)

);

END LOOP;

RETURN;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line("Fatal error encountered!");

RETURN;

END sf_gather_cost_centers;

статья

-----

-- Листинг 1.6: Использование конвейерной табличной функции с CAST

-----

SELECT *

FROM TABLE (sf_gather_cost_centers (114));

SELECT *

FROM TABLE (sf_gather_cost_centers (120));

SELECT * FROM (

SELECT

DISTINCT *

FROM TABLE (sf_gather_cost_centers (120))

ORDER BY cc_lvl DESC

)

WHERE rownum <= 5;

статья

-------------------------------------------------------------------------

Листинг 2.

// Примеры CAST и табличных функций

// Этот скрипт содержит DDL- и DML- предложения, требуемые для

// создания новых таблиц и модификации существующих в учебной

// схеме HR базы данных Oracle для демонстрации возможностей функции

// CAST и табличных функций

// Автор: Jim Czuprynski

/ Предупреждение:

// Этот скрипт предназначен только для демонстрации различных

// возможности Oracle и должен быть тщательнопроверен перед выполнением

// на любой работающей базе данных Oracle, чтобы исключить какую-либо

// потенциальную опасность.

-----

-- Create and load new table in the HR schema to store Divisions

-----

DROP TABLE hr.divisions CASCADE CONSTRAINTS;

CREATE TABLE hr.divisions (

division_id NUMBER(5) PRIMARY KEY,

description VARCHAR2(32) NOT NULL

);

INSERT INTO hr.divisions (division_id, description)

VALUES (10000, "Executive");

INSERT INTO hr.divisions (division_id, description)

VALUES (20000, "Administrative");

INSERT INTO hr.divisions (division_id, description)

VALUES (30000, "Construction");

COMMIT;

-----

-- Create and populate new DIVISION_ID column in the DEPARTMENTS table

-----

ALTER TABLE hr.departments ADD division_id NUMBER(5);

ALTER TABLE hr.departments

ADD CONSTRAINT department_division_fk

FOREIGN KEY (division_id)

REFERENCES hr.divisions(division_id);

-- Assign departments to Executive division

UPDATE hr.departments

SET division_id = 10000

WHERE department_id IN (10, 20, 40, 60, 70, 80, 90);

COMMIT;

-- Assign departments to Construction division

UPDATE hr.departments

SET division_id = 30000

WHERE department_id IN (30, 50, 170, 180, 190, 200);

COMMIT;

-- Assign all other departments to Administrative division

UPDATE hr.departments

SET division_id = 20000

WHERE division_id IS NULL;

COMMIT;

-----

-- Create and load a new table in the HR schema to store Cost Centers

-----

DROP TABLE hr.cost_centers CASCADE CONSTRAINTS;

CREATE TABLE hr.cost_centers (

cost_ctr_id NUMBER(5) PRIMARY KEY,

description VARCHAR2(32) NOT NULL,

selectable CHAR(1) NOT NULL

);

ALTER TABLE hr.cost_centers

ADD CONSTRAINT cc_selectable_ck

CHECK (selectable IN ("Y","N"));

INSERT INTO hr.cost_centers