Источник: CITFORUM
Джим Козупрински, PC Week/RE
/* // Листинг 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