TYPE t_product_record IS RECORD (
product_id INTEGER,
name VARCHAR2(15),
quantity INTEGER
);
- объявляем тип курсора для указания на записи о продуктах
TYPE t_product_cursor IS REF CURSOR RETURN t_product_record;
- объявляем курсор
v_product_cursor t_product_cursor;
- объявляем переменную для хранения записи о продукте
v_product t_product_record;
XML и база данных Oracle 651
BEGIN
- открываем v_produ ct_cursor для чтения produ ct_id, name, and q u a n tity
для-
каждого продукта, хранящегося в XML столбца xml_purchase_order
- в таблице purchase_order
OPEN v_produ ct_cursor FOR
SELECT
EXTRACTVALUE(product.COLUMN_VALUE, '/p ro d u c t/ р ro d u c t_ id ’ )
AS produ ct_id,
EXTRACTVALUE(product.COLUMN_VALUE, ’ /product/name') AS name,
EXTRACTVALUE(product.COLUMN_VALUE, '/ p r o d u c t / q u a n t it y ') AS q u a n tity
FROM TABLE(
SELECT
XMLSEQUENCE(EXTRACT(xml_purchase_order, '/p u rch a s e _ o rd e r//p ro d u c t') )
FROM purchase_order
WHERE purchase_order_id = p_purchase_order_id
) product;
- - цикл по содержимому v_product_cursor
LOOP
- выбираем запись о продукте из v_produ ct_cursor и выходим, если
- не найдено больше записей
FETCH v_produ ct_cursor INTO v_product;
EXIT WHEN v_product_cursor%NOTFOUND;
- расширяем v_nested_table_p rod ucts, чтобы продукт мог храниться в
ней
v_nested_table_products.EXTEND;
- создаем новый продукт и сохраняем его в v_nested_table_p rod ucts
v_nested_table_p rod ucts(v_count) :=
t_ p ro d u c t(v _ p ro d u c t.p ro d u c t_ id , v_product.name, v_product.
q u a n t it y ) ;
- показываем новый продукт, сохраненный в v_nested_table_p rod ucts
DBMS_OUTPUT.PUT_LINE(' produ ct_id = ' ||
v _ n e s te d _ tab le_ p ro d u c ts (v_ co u n t). pro d u ct_ id );
DBMS_OUTPUT.PUT_LINE(' name = ' ||
v _ n e s te d _ tab le _ p rod u c ts (v_ co u n t). name);
DBMS_OUTPUT.PUT_LINE('quantity = ' ||
v _ n e s te d _ tab le _ p rod u c ts (v_ co u n t). q u a n t it y ) ;
- увеличиваем v_count на единицу для следующего шага в цикле
v_count := v_count + 1;
END LOOP;
- закрываем v_produ ct_cursor
CLOSE v_product_cursor;
- изменяем таблицу purchase_order используя значения, извлеченные из
- XML, хранящегося в столбце xml_purchase_order (вложенная таблица
- products установлена в v_nested_table_p rod ucts, в которую
- внесены изменения в предыдущем цикле)
UPDATE purchase_order
SET
customer_order_id =
EXTRACTVALUE(xml_purchase_order,
'/p u rch a se _ o rd e r/cu s tom e r_ o rd e r_ id ') ,
order_date =
652 Глава 17
TO_DATE(EXTRACTVALUE(xml_purchase_order,
'/purchase_order/order_date'), 'YYYY-MM-DD'),
customer_name =
EXTRACTVALUE(xml_purchase_order, '/purchase_order/customer_name') ,
street =
EXTRACTVALUE(xml_purchase_order, '/purchase_order/street') ,
city =
EXTRACTVALUE(xml_purchase_order, '/purchase_order/city') ,
state =
EXTRACTVALUE(xml_purchase_order, '/piirchase_order/state'),
zip =
EXTRACTVALUE(xml_purchase_order, '/purchase_order/zip'),
phone_number =
EXTRACTVALUE(xiTil_purchase_order, '/purchase_order/phone_number'),
products = v_nested_table_products
WHERE purchase_order_id = p_purchase_order_id;
- фиксируем транзакцию
COMMIT;
END update_purchase_order;
/ _ .
В следующем примере включается вывод на сервере и вызывается
update_purchase_order() для обновления заказа №1:
□ SET SERVEROUTPUT ON
CALL update_purchase_order(1);
product_id = 1
name = Supernova video
quantity = 5
product_id = 2
name = Oracle SQL book
quantity = 4
Следующий запрос производит выборку столбцов из заказа №1:
□ SELECT purchase_order_id, customer_order_id, order_date, customer_name,
street, city, state, zip, phone_number, products
FROM purchase_order
WHERE purchase_order_id = 1;
PURCHASE_ORDER_ID CUSTOMER_ORDER_ID 0RDER_DAT CUST0MER_
NAME
STREET CITY ST ZIP PHONE_NUMBER
PRODUCTS(PRODUCT_ID, NAME, QUANTITY)
1 176 17-MAY-07 Best Products 456 Inc.
10 Any Street Any City CA 94440 555-121-1234
T_NESTED_TABLE_PRODUCT(
T_PR0DUCT(1, ‘ Supernova video’ , 5),
T_PR0DUCT(2, ‘ Oracle SQL book’ , 4)
)
XML и база данных Oracle 653
Вложенная таблица products содержит те же самые данные, которые
хранятся в XML элементах товара в столбце xml_purchase_order. Автор добавил
пустые строки, чтобы отделить товары в результатах примера для
более простого восприятия.
Итоги
В этой главе вы узнали, как:
■ Создавать XML из реляционных данных.
■ Сохранять XML в базе данных, а затем читать XML для обновления
связанных столбцов.
Эта короткая глава лишь поверхностно затронула богатую функциональность
XML, доступную в базе данных Oracle. Более подробную информацию
можно найти в руководствах Oracle XML Developer’s K it (Пакет разработчика
XML Oracle) и Oracle XML DB Developer’s Guide (Руководство разработчика
базы данных Oracle по XM L ), публикуемых корпорацией Oracle.
Кроме приложений, которые размещены в конце, на этом книга заканчивается.
Автор надеется, что вы нашли эту книгу информативной и полезной,
а ваше внимание не ослабевало на протяжении всей книги.
Приложение
Типы данных Oracle
В этом приложении содержатся две таблицы, в которых перечислены имеющиеся
в SQL Oracle типы данных, которые можно использовать для
определения столбцов таблиц, а также дополнительные типы, поддерживаемые
PL/SQL Oracle.
Типы SQL Oracle
В таблице А.1 показаны типы SQL Oracle.
Таблица А.1. Типы SQL Oracle
Тип Описание