Extractvalue.

Служит для получения значения требуемого тега (требуемый тег задается при помощи xpath). Возвращает именно значение, отбрасывая xml-теги.

EXISTSNODE.

Функция EXISTSNODE дает возможность использовать в SQL условие отбора XPath (язык отбора, принятый в технологиях XML):

SELECT id, b.description.XMLDATA
FROM books b
WHERE b.description.EXISTSNODE('/cover[author="Sanjay Mishra"]')=1;

GETSTRINGVAL() – используется для получения строки varchar2 из содержимого объекта xmltype (преобразует кодировку, но возвращает все теги).

GETROOTELEMENT.

Для получения корневого элемента документа.

XMLELEMENT.

Эта функция создает элемент XML из задаваемого выражения. В простейшем виде она имеет следующий синтаксис:

XMLElement(NAME имя_элемента, имя_столбца)

С помощью фразы NAME задается имя элемента, а второй аргумент указывает, на основании значения какого столбца формируется элемент XML. Приведем пример:

SELECT XMLElement(NAME "TchName", t.Name)

FROM TEACHER t

WHERE t.TchPK = 1;

--

XMLELEMENT(NAME "TCHNAME”, T.NAME)

<TchName>BH6poBCKiM</TchName>

XMLATTRIBUTES.

Функция XMLAttributes используется для указания атрибутов элемента. Она может использоваться только в составе функции XMLElement и имеет следующий синтаксис:

XMLAttributes(столбец [AS имя_атрибута][, столбец [AS имя_атрибута]]..,)

Список ее аргументов содержит имена столбцов (или выражений над столбцами) с возможными именами атрибутов. Если имена атрибутов отсутствуют, в их

качестве используются имена столбцов.

XMLAGG.

Функция XMLAgg относится к классу агрегатных функций SQL. На вход к ней подается множество строк одной из выделенных групп таблицы. По каждой из

таких строк формируется значение XML согласно аргументу функции. И затем эти значения XML сцепляются в одно значение XML. Синтаксис функции

следующий:

XMLAGG(ХМL_значение [ORDER BY порядок_сортировки])

SELECT XMLELEMENT("TEACHER", XMLATTRIBUTES(T.TEACHER AS "ID", T.TEACHER_NAME AS "NAME")) AS "RESULT" INTO VARXM FROM TEACHER T WHERE TEACHER LIKE ID;

SELECT XMLELEMENT("PULPIT", XMLATTRIBUTES(T.PULPIT AS "ID", SYSDATE AS "DATE", COUNT(*) AS "TOTAL"), XMLAGG(XGET_TEACHER(T.TEACHER))) INTO VARXM FROM TEACHER T WHERE PULPIT=ID GROUP BY PULPIT;

SELECT XMLROOT(XTEXT, VERSION '1.0', STANDALONE YES) INTO VARXM FROM DUAL;

36. Процедурные объекты: хранимые процедуры.

Храни́мая процеду́ра — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

Три типа параметров:
IN

OUT

INOUT.

Благодаря выходным параметрам процедура может косвенно возвращать результаты выполнения к точке вызова.

Необходимые привилегии:
GRANT CREATE PROCEDURE TO JEU_12;

Синтаксис:

CREATE OR REPLACE PROCEDURE XSUM(

MIN_CY IN AUDITORIUM.AUDITORIUM_CAPACITY%TYPE,

MAX_CY IN OUT AUDITORIUM.AUDITORIUM_CAPACITY%TYPE,

N_AUD OUT NUMBER)

IS

M_MAX_CY AUDITORIUM.AUDITORIUM_CAPACITY%TYPE;

M_N_AUD NUMBER:= 0;

BEGIN

SELECT COUNT(*), MAX(AUDITORIUM_CAPACITY) INTO M_N_AUD, M_MAX_CY FROM AUDITORIUM

WHERE AUDITORIUM_CAPACITY>=MIN_CY AND AUDITORIUM_CAPACITY<=MAX_CY;

MAX_CY:=M_MAX_CY;

N_AUD:=M_N_AUD;

EXCEPTION

WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);

END XSUM;

Вызов процедур:

DECLARE

MIN_CY NUMBER;

MAX_CY NUMBER;

N_AUD NUMBER;

BEGIN

MIN_CY:= 10;

MAX_CY:= 40;

XSUM(

MIN_CY => MIN_CY,

MAX_CY => MAX_CY,

N_AUD => N_AUD

);

DBMS_OUTPUT.PUT_LINE('MAX_CY = ' || MAX_CY);

DBMS_OUTPUT.PUT_LINE('N_AUD = ' || N_AUD);

END;

37. Процедурные объекты: хранимые функции.

Хранимые функции являются разновидностью хранимых процедур. Они включены в состав программных объектов баз данных с целью наибольшего соответствия языкам программирования, например Си или Паскаль. Как и в этих языках программирования, каждая хранимая функция рассматривается в качестве выражения, формирующего одно единственное значение. Хранимые функции применяются для расширения функциональных возможностей операторов SELECT и ряда других SQL-операторов.

С тем различием что функции явно возвращают значение в точку вызова они практически идентичны процедурам.

Синтаксис:

CREATE OR REPLACE FUNCTION SELCY(

MIN_CY IN AUDITORIUM.AUDITORIUM_CAPACITY%TYPE,

MAX_CY IN OUT AUDITORIUM.AUDITORIUM_CAPACITY%TYPE

)

RETURN NUMBER IS

RC NUMBER(5);

BEGIN

SELECT COUNT(*), MAX(AUDITORIUM_CAPACITY) INTO RC, MAX_CY FROM AUDITORIUM

WHERE AUDITORIUM_CAPACITY>=MIN_CY AND AUDITORIUM_CAPACITY<=MAX_CY GROUP BY MAX_CY;

RETURN RC;

EXCEPTION

WHEN OTHERS THEN RETURN -1;

END SELCY;

Вызов функций в plsql блоках:

DECLARE

MIN_CY NUMBER;

MAX_CY NUMBER;

v_Return NUMBER;

BEGIN

MIN_CY:= 20;

MAX_CY:= 60;

v_Return:= SELCY(

MIN_CY => MIN_CY,

MAX_CY => MAX_CY

);

DBMS_OUTPUT.PUT_LINE('MAX_CY = ' || MAX_CY);

DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);

END;

Функции можно вызывать и в рамках обычных sql-запросов, однако при этом на функции накладываются дополнительные ограничения:

- SELECT: не может модифицировать таблицы;

- INSERT,UPDATE, DELETE: не может DML c таблицей, заданной в операторе;

- параллелизация DML: не может модифицировать таблицы;

- DML: нельзя TCL, ALTER SESSION, SET ROLE, ALTER SYSTEM;

- вызываемые функции: имеют те же ограничения;

- функция должна быть хранимой: нельзя использовать локальные функции;

- параметры только IN: нельзя OUT, IN OUT;

- формальные параметры: имеют тип базы данных или CREATE TYPE;

- возвращаемый тип: тип базы данных или CREATE TYPE.

38. Процедурные объекты: пакеты.

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

Это означает, что на время сессии значения этих пере

менных хранятся ораклом и при последующих обращениях в рамках одной сессии пользователь “застает их в том же виде, как оставил их в прошлый раз”.

Особенностью пакетов так же является то, что они хранятся в виде двух компонент:

- описание (заголовок) – содержит прототипы функций и процедур, объявления переменных.

- тело – содержит реализацию функций и процедур.

Разделение отчасти сделано для возможности позднего связывания зависимых функций и процедур.

Пример:

CREATE OR REPLACE

PACKAGE TESTPACK AS

N NUMBER(15);

FUNCTION PRSLT(F NUMBER, S NUMBER) RETURN NUMBER;

PROCEDURE EXRSLT(K NUMBER, F NUMBER, S NUMBER);

END TESTPACK;

CREATE OR REPLACE PACKAGE BODY TESTPACK AS

FUNCTION PRSLT(F NUMBER, S NUMBER) RETURN NUMBER

IS

TB NUMBER(15);

BEGIN

TB:=POWER(F, S);

DBMS_OUTPUT.PUT_LINE('N IN PRSLT = '||N);

RETURN TB;

EXCEPTION

WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);

END PRSLT;

PROCEDURE EXRSLT(K NUMBER, F NUMBER, S NUMBER)

IS

TB NUMBER(15);

BEGIN

TB:=PRSLT(F, S);

TB:=TB*K;

DBMS_OUTPUT.PUT_LINE('N IN EXRSLT = '||N);

DBMS_OUTPUT.PUT_LINE('TB = '||TB);

EXCEPTION

WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);

END EXRSLT;

END TESTPACK;

--использование пакетов

BEGIN

TESTPACK.N:=10;

TESTPACK.EXRSLT(3, 5, 2);

END;

39. Процедурные объекты: триггеры.

Три́ггер (англ. trigger) — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.

Типы триггеров Oracle:

DML/Instead of/System

Before/After

Insert/update/delete

For each rows/operators

TCL операторы можно выполнять только в рамках автономной транзакции, триггеры относятся к той же транзакции что и событие его вызвавшее.

Дата добавления: 2015-11-16