... Родила царица в ночь
Не то сына, не то дочь;
Не мышонка, не лягушку,
А неведому зверюшку.
А.С. Пушкин, "Сказка о царе Салтане"
Содержание
- Аннотация
- Ссылки на курсор в предложении SELECT
- Программная обработка
- Применение в XSQL
- Дополнительная информация
Аннотация
Рассматриваются возможности использования ссылок на курсор в предложения SQL в Oracle.
Ссылки на курсор в предложении SELECT
Ссылки на курсор могут использоваться не только в программе, но и в предложении SQL. Это обеспечивается указанием во фразе SELECT предложения SELECT выражения CURSOR для формирования столбца результата. Если мы попытаемся сделать это в SQL*Plus, результат покажется довольно неожиданым:
| SQL> SELECT | |
| 2 | dname |
| 3 | , CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno ) |
| 4 | FROM dept; |
| DNAME | CURSOR(SELECTENAMEFR |
| - | - |
| ACCOUNTING | CURSOR STATEMENT : 2 |
| CURSOR STATEMENT : 2 | |
| ENAME | |
| - | |
| CLARK | |
| KING | |
| MILLER | |
| RESEARCH | CURSOR STATEMENT : 2 |
| CURSOR STATEMENT : 2 | |
| ENAME | |
| - | |
| SMITH | |
| JONES | |
| SCOTT | |
| ADAMS | |
| FORD | |
| SALES | CURSOR STATEMENT : 2 |
| ... ... ... ... | |
На деле произошло вот что: предложение SELECT ... FROM dept вернуло во втором столбце результата каждой своей строки ссылку на курсор, сформированный динамически предложением внутри CURSOR ( ... ). Программа SQL*Plus проинтерпретировала эту ссылку, перебрав строки, словно как это делали мы в примерах предыдущей статьи, и выдала строки на экран. В результате мы увидели на экране перечень четырех строк-отделов, и для каждого отдела - перечень сотрудников.
Форма выдачи занятная, но, кажется, практически бесполезная. Так зачем же нужно выражение CURSOR в предложении SELECT? Чтобы ответить на этот вопрос, снова обратимся к программированию.
Программная обработка
Рассмотрим следующий пример блока PL/SQL, где используется (а) обычный курсор для SELECT и (б) курсорное выражение внутри:
SET SERVEROUTPUT ON
DECLARE
CURSOR main_cur IS
SELECT
dname
, CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno )
FROM dept
;
deptname dept.dname%TYPE;
empname emp.ename%TYPE;
inner_cur SYS_REFCURSOR;
BEGIN
OPEN main_cur;
LOOP
FETCH main_cur INTO deptname, inner_cur;
EXIT WHEN main_cur%NOTFOUND;
dbms_output.put_line ( '-' // deptname );
LOOP
FETCH inner_cur INTO empname;
EXIT WHEN inner_cur%NOTFOUND;
dbms_output.put_line( empname );
END LOOP;
CLOSE inner_cur;
CLOSE main_cur;
END;
/
В результате такой организации запроса мы не просто выдали список отделов и их сотрудников, но смогли обработать каждый раз возникающий перечень сотрудников программно. Если бы не было выражения CURSOR, такого рода программную обработку можно было бы организовать, только заведя в программе два курсора, а не один (а при использовании в одном предложении SELECT сразу нескольких курсорных выражений, возможно вложенных, - более чем два).
Более того, этот один программный курсор с выражениями CURSOR просто и точно указывает на логику обработки предложения SELECT (то есть играет документирующую роль), в то время как определение в программе нескольких курсоров без курсорных выражений ничего не говорит о логике их совместной обработки, так что понять последнюю мы сможем только по процедурным конструкциям, а давно известно, что понять, что делает программа, по процедурным конструкциям гораздо труднее, чем по описаниям ее структур.
Более того, при использовании курсорного выражения мы избавлены от необходимости его открывать, сразу приступая к операциям FETCH, что быстрее в исполнении и экономнее в тексте.
Резюме: использование ссылки на курсор в предложении SELECT упрощает код программной обработки, улучшает читаемость кода и повышает эффективность его исполнения.
Применение в XSQL
В некоторых случаях использование курсорного выражения в SELECT дает выгоду и вне программы.
Средства XSQL, поставляемые в рамках штатной поставки ПО СУБД Oracle ("XML Development Kit", XDK), позволяют хранить на сервере web сервлет, порождающий по запросу SELECT к базе данных документ XML. Этот сервлет способен обрабатывать ссылки на курсор, порождаемые курсорным выражением.
Подготовим файл departments.xsql:
| <?xml version="1.0"?> <xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql"> SELECT dname , CURSOR ( SELECT ename FROM emp WHERE emp.deptno = dept.deptno ) AS employees FROM dept </xsql:query> |
Создадим каталог company в месте файловой системы, предусмотренным в Oracle для расположения файлов формата XSQL. Например, при установленном расширении XDK ПО Oracle версии 9.2 этим местом может быть $ORACLE_HOME/xdk/demo/java . Обратимся по адресу http://localhost:7778/xsql/company/departments.xsql (сервер HTTP при этом должен быть запущен). Получим примерно такой результат:
В этом изображении для краткости узлы ROW с атрибутами num = "2" и num = "3" свернуты средствами браузера.
Полученный документ XML может играть для сервера приложений в web как самостоятельную роль (выдавая результат обращений по определенному адресу в web в формате XML), так и роль исходного материала для дальнейшего преобразования XSL (выдавая результат в конечном итоге в формате HTML, в том числе с графическим оформлением). Эти преобразования здесь не рассматриваются, как не относящиеся к возможностям собственно Oracle.
Замечательно, что обрабатывающий запросы XSQL сервлет, разработанный фирмой Oracle, способен обрабатывать не только одиночные, но и вложенные выражения типа CURSOR. Подготовим еще один файл multicursor.xsql и положим его в тот же каталог company:
| <?xml version="1.0"?> <xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql"> SELECT TO_CHAR ( SYSTIMESTAMP,'SS:FF3' ) AS seconds , CURSOR ( SELECT TO_CHAR ( SYSTIMESTAMP,'SS:FF3' ) secondsone , CURSOR ( SELECT TO_CHAR( SYSTIMESTAMP,'SS:FF3' ) AS secondstwo AS leveltwo FROM DUAL ) AS leveloneFROM dept </xsql:query> |
Таблица DEPT здесь взята только ради примера из малого числа строк.
Обратимся по адресу http://localhost:7778/xsql/company/multicursor.xsql и увидим примерно такой результат:
Как и в первом случае, некоторые узлы документа свернуты средствами браузера ради экономии в статье места, занимаемого картинкой.
Обращение к SYSTIMESTAMP позволяет наблюдать процесс вычисления ответа сервлетом Oracle.
Нетрудно сообразить, что в случае "запроса" на XSQL, равно как и в случае запроса с курсорным выражением в SQL*Plus, мы имеем дело с иллюзией отсутствия программной обработки, которая на деле присутствует в тексте сервлета и в SQL*Plus, во встроенном виде. Но ведь также мы имеем дело с иллюзией таблицы, работая с view, что никоим образом не снижает практической ценности этого понятия.