Документация Oracle на русском языке





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

Главная :: Карта


Oracle Database или Oracle RDBMS — объектно-реляционная система управления базами данных компании Oracle.



 

DeepEdit!

Программирование баз данных на Oracle, техническая документация, литература, статьи и публикации

  • Увеличить размер шрифта
  • Размер шрифта по умолчанию
  • Уменьшить размер шрифта

Использование 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
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
 


каталог свадебных платьев фото . . b154pw02
jAntivirus