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

Источник: 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