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

Джим Козупрински
 была опубликована в журнале ORACLE MAGAZINE Русское издание
Оригинал: CASTing About For a Solution: Using CAST and Table Functions in PL/SQL, by Jim Czuprynski, журнал DataBase, 20 мая, 2004

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

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

  • служащий - это часть отдела (department), который в свою очередь является частью отделения (division);
  • каждый служащий может обратиться в от одной до пяти кредитных организаций;
  • каждый отдел имеет до пяти заданных по умолчанию кредитных организаций, в которых можно взять деньги;
  • каждое отделение имеет до пяти заданных по умолчанию кредитных организаций, в которых можно взять деньги;
  • коль скоро списки потенциально имеющих такое право служащих, заданных по умолчанию кредитных организаций для отделов и отделенияй уже определены, то только те кредитные организации, в которых может обращаться любой служащий, следует сохранить в списке;
  • из окончательного списка должны быть удалены дубли имен кредитных организаций.

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

Когда я открыл эту хранимую процедуру, я заметил, что она когда-то была конвертирована из 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.

Ссылки и дополнительная лиература

A96595-01 Oracle 9i Data Cartridge Developer's Guide, Chapter 12
A96624-01 Oracle 9i PL/SQL User's Guide and Reference, Chapter 8

/*
 
|| Листинг 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
IS
/*
|| Функция: 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)
    IS
    /*
    || 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 
IS
/*
|| Функция: 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 
(cost_ctr_id, description, selectable)
VALUES (10000, 'Sales and Management', 'N');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (20000, 'Administrative', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (30000, 'Homebuilding', 'Y');

INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (11000, 'Owners', 'N');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (12000, 'Sales and Marketing', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (12100, 'Outside Sales', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (12200, 'Sales Support', 'Y');

INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (21000, 'Office Supplies', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (22000, 'Human Resources', 'N');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (23000, 'Architectural', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (23100, 'Blueprinting', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (23200, 'Planning', 'Y');

INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (31000, 'Exterior Construction', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (31100, 'Wall and Floor Setting', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (31200, 'Concrete and Foundation', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (31300, 'Rough Landscaping', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (31400, 'Finish Landscaping', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (32000, 'Carpentry - General', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (32100, 'Rough Carpentry', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (32200, 'Finish Carpentry', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (33000, 'Plumbing - General', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (33100, 'Rough-In Plumbing', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (33200, 'Finish Plumbing', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (34000, 'Physical Plant', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (34100, 'HVAC', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (35000, 'Interior Construction', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (35100, 'Wallboarding and Plastering', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (35200, 'Painting', 'Y');
INSERT INTO hr.cost_centers 
(cost_ctr_id, description, selectable)
VALUES (35300, 'Flooring', 'Y');

COMMIT;

-----
-- Create table for Cost Center Assignments
-----
DROP TABLE hr.cost_center_assignments CASCADE CONSTRAINTS;
CREATE TABLE hr.cost_center_assignments (
    entity_id       NUMBER(5)       NOT NULL,
    entity_type     CHAR(1)         NOT NULL,
    seq_nbr         NUMBER(5)       NOT NULL,
    cost_ctr_id     NUMBER(5)       NOT NULL
);

ALTER TABLE hr.cost_center_assignments
    ADD CONSTRAINT cost_center_assignments_pk
    PRIMARY KEY (entity_id, entity_type, seq_nbr);

ALTER TABLE hr.cost_center_assignments
    ADD CONSTRAINT cost_center_assignments_fk
    FOREIGN KEY (cost_ctr_id)
    REFERENCES hr.cost_centers(cost_ctr_id);

-- Load Division-level cost center assignments
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (10000, 'V', 1, 10000);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (20000, 'V', 1, 20000);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (30000, 'V', 1, 30000);
COMMIT;

-- Load Department-level cost center assignments
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (30, 'D', 1, 21000);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (30, 'D', 2, 23100);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (30, 'D', 3, 23200);

INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 1, 31000);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 2, 33000);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 3, 33000);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 4, 34000);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (50, 'D', 5, 35000);

INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (80, 'D', 1, 12000);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (80, 'D', 2, 12100);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (80, 'D', 3, 12200);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (80, 'D', 4, 21000);

INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (110, 'D', 1, 21000);

-- Load Employee-level cost center assignments

INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (114, 'E', 1, 35100);

INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (120, 'E', 1, 35100);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (120, 'E', 2, 35200);
INSERT INTO hr.cost_center_assignments 
(entity_id, entity_type, seq_nbr, cost_ctr_id)
VALUES (120, 'E', 3, 35300);

COMMIT;

-----
-- Sample queries
-----
      
-- Show all Cost Center entries
SELECT * 
  FROM HR.cost_centers 
 ORDER BY cost_ctr_id

-- Show all Cost Center Assignment entries
SELECT * 
  FROM HR.cost_center_assignments 

-- Show all Division / Department / Employee hierarchy entries and details
SELECT 
    D.division_id,
    V.description,
    D.department_name,
    E.department_id,
    E.employee_id,
    E.last_name
  FROM 
    divisions V,
    departments D,
    employees E
 WHERE E.department_id = D.department_id
   AND d.division_id = V.division_id
  ORDER BY D.Division_Id, e.department_id, e.employee_id


Опубликовал admin
24 Окт, Воскресенье 2004г.



Программирование для чайников.