Вы находитесь на страницах старой версии сайта.
Переходите на новую версию Interface.Ru

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

© Джим Козупрински, PC Week/RE
© Статья была опубликована на сайте www.citforum.ru
© Впервые эта статья была опубликована в журнале ORACLE MAGAZINE Русское издание

/*
 
|| Листинг 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

За дополнительной информацией обращайтесь в компанию Interface Ltd.

Обсудить на форуме Oracle

Рекомендовать страницу

INTERFACE Ltd.
Телефон/Факс: +7 (495) 925-0049
Отправить E-Mail
http://www.interface.ru
Rambler's Top100
Ваши замечания и предложения отправляйте редактору
По техническим вопросам обращайтесь к вебмастеру
Дата публикации: 14.05.06