/*
|| CAST and Table Functions - Listing 1
||
|| Contains practical examples of CAST function and table functions
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various Oracle features and
|| should be carefully proofread before executing against any existing
|| Oracle database to insure that no potential damage can occur.
||
*/
-----
-- Listing 1.1: Sorting PL/SQL Contents With 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;
/
-----
-- Listing 1.2: Using CAST With Group Functions
-----
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;
/
-----
-- Listing 1.3: Create TYPEs and Table Function
-----
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
/*
|| Function: sf_gather_cost_centers
||
|| Description: Using the Cost Center type, accepts an Employee's current
|| list of preferred cost centers and walks up the Division / Department /
|| Employee hierarchy to find the appropriate one Cost Center for posting.
||
||
*/
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;
/
-----
-- Listing 1.4: Using a Table Function with 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;
-----
-- Listing 1.5: Using a PIPELINED Table Function
-----
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
/*
|| Function: sf_gather_cost_centers (PIPELINED)
||
|| Description: Using the Cost Center type, accepts an Employee's current
|| list of preferred cost centers and walks up the Division / Department /
|| Employee hierarchy to find the appropriate one Cost Center for posting.
||
||
*/
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;
/
-----
-- Listing 1.6: Using a PIPELINED Table Function with 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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/77580/viewspace-212727/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle 中用户登录时间问题？
下一篇：
merge 用途 及注意点
请登录后发表评论
登录
全部评论
