Використання CAST і табличних функцій в PL / SQL (Листинги)

/*

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


Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*