(см. наполнение БД в прил.)

CREATE OR REPLACE PACKAGE name_pkg IS

TYPE type_name_rec IS RECORD (name VARCHAR2(100));

TYPE type_name_refcur IS REF CURSOR RETURN type_name_rec;

FUNCTION open_name (p_table_txt IN VARCHAR2, p_id_num IN NUMBER) RETURN type_name_refcur;

FUNCTION get_name (p_table_txt IN VARCHAR2, p_id_num IN NUMBER) RETURN VARCHAR2;

END name_pkg;

CREATE OR REPLACE PACKAGE BODY name_pkg IS

FUNCTION open_name (p_table_txt IN VARCHAR2, p_id_num IN NUMBER)

RETURN type_name_refcur IS

lv_table_txt VARCHAR2(100) := UPPER(p_table_txt);

lv_name_rec type_name_refcur;

BEGIN

IF lv_table_txt = 'EMPLOYEE' THEN

OPEN lv_name_rec FOR

SELECT last_name || ', '|| first_name res

FROM employee

WHERE employee_id = p_id_num;

ELSIF lv_table_txt = 'CUSTOMER' THEN

OPEN lv_name_rec FOR

SELECT customer_name res

FROM customer

WHERE customer_id = p_id_num;

ELSIF lv_table_txt = 'PRODUCT' THEN

OPEN lv_name_rec FOR

SELECT product_name res

FROM product

WHERE product_id = p_id_num;

ELSE

RAISE_APPLICATION_ERROR (-20222,

'Invalid table specified for name request.');

END IF;

RETURN lv_name_rec;

END open_name;

FUNCTION get_name (p_table_txt IN VARCHAR2, p_id_num IN NUMBER)

RETURN VARCHAR2 IS

lv_name_rec type_name_rec;

lv_name_refcur type_name_refcur;

BEGIN

lv_name_refcur := open_name(p_table_txt, p_id_num);

FETCH lv_name_refcur INTO lv_name_rec;

IF (lv_name_refcur%NOTFOUND) THEN

CLOSE lv_name_refcur;

RAISE NO_DATA_FOUND;

ELSE

CLOSE lv_name_refcur;

END IF;

RETURN lv_name_rec.name;

END get_name;

END name_pkg;