Типы сооружений для обработки осадков: Септиками называются сооружения, в которых одновременно происходят осветление сточной жидкости...
Поперечные профили набережных и береговой полосы: На городских территориях берегоукрепление проектируют с учетом технических и экономических требований, но особое значение придают эстетическим...
Топ:
Основы обеспечения единства измерений: Обеспечение единства измерений - деятельность метрологических служб, направленная на достижение...
Марксистская теория происхождения государства: По мнению Маркса и Энгельса, в основе развития общества, происходящих в нем изменений лежит...
Интересное:
Отражение на счетах бухгалтерского учета процесса приобретения: Процесс заготовления представляет систему экономических событий, включающих приобретение организацией у поставщиков сырья...
Распространение рака на другие отдаленные от желудка органы: Характерных симптомов рака желудка не существует. Выраженные симптомы появляются, когда опухоль...
Влияние предпринимательской среды на эффективное функционирование предприятия: Предпринимательская среда – это совокупность внешних и внутренних факторов, оказывающих влияние на функционирование фирмы...
Дисциплины:
2022-12-20 | 27 |
5.00
из
|
Заказать работу |
|
|
9.3.1. Предложение SELECT … FOR UPDATE
Используется для упреждающей блокировки набора строк, подпадающего под действие SELECT, при выполнении OPEN для курсора. Блокировка будет снята ближайшим оператором COMMIT или ROLLBACK.
Синтаксис:
SELECT … FROM … FOR UPDATE [OF список_имен_столбцов ] [NOWAIT];
Если указан список_имен_столбцов, то блокировка будет касаться только строк, на которые этот список распространяется. Пример:
DECLARE
CURSOR clerks_in_new_york IS
SELECT empno, sal, comm
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND job = 'CLERK'
AND loc = 'NEW YORK'
FOR UPDATE OF emp.sal;
BEGIN
…
Здесь при открытии (OPEN) курсора будут заблокированы только строки таблицы emp, так как только ее столбцы фигурируют во конструкции FOR UPDATE.
Возможность изменять строки, выбираемые курсором
Если объявлен курсор с конструкцией FOR UPDATE, то к нему можно применять операторы UPDATE и DELETE со специальным указанием во фразе WHERE:
[UPDATE | DELETE] … WHERE CURRENT OF имя _курсора;
В этом случае модифицироваться будут строки, охваченные курсором с FOR UPDATE, так что дополнительного повторения UPDATE/DELETE … WHERE … не потребуется.
Упражнение. Выполнить следующий код и объяснить его работу:
SAVEPOINT start_perestroika;
DECLARE
CURSOR emp_cur IS
SELECT empno, sal, job, deptno
FROM emp
WHERE job = 'CLERK' OR deptno = 10
FOR UPDATE;
BEGIN
FOR emp_rec IN emp_cur LOOP
CASE
WHEN emp_rec.job = 'CLERK' THEN
DELETE FROM emp WHERE CURRENT OF emp_cur;
WHEN emp_rec.deptno = 10 THEN
UPDATE emp SET sal = sal * 10 WHERE CURRENT OF emp_cur;
ELSE NULL;
END CASE;
END LOOP;
END;
/
SELECT * FROM emp ORDER BY deptno, job;
ROLLBACK TO SAVEPOINT start_perestroika;
SELECT * FROM emp ORDER BY deptno, job;
Конструкция WHERE CURRENT OF позволяет вносить изменения только в одну базовую таблицу предложения SELECT. Если SELECT … FOR UPDATE блокирует строки сразу нескольких таблиц, конструкция WHERE CURRENT OF работать не будет.
|
Ссылки на курсор
Ссылка на курсор – это именованная структура данных ссылающаяся на курсор, ссылающийся, в свою очередь, на массив выбираемых курсором данных. Ее можно использовать начиная с версии Oracle 7.2 для более простого извлечения данных-результата или для маскировки незначительных изменений в SQL-запросе. Синтаксис объявления типа следующий:
TYPE имя_типа_ссылки_на_курсор IS REF CURSOR [RETURN тип_записи ];
Если конструкция RETURN присутствует, ссылка на курсор называется строгой; если нет – нестрогой. Нестрогая может ссылаться на любой запрос, а строгая – только тот, что возвращает результат указанного типа.
Пример описания обоих типов ссылки на курсор:
DECLARE
TYPE any_curtype IS REF CURSOR;
generic_curvar any_curtype;
TYPE company_curtype IS REF CURSOR RETURN dept%ROWTYPE;
company_cur company_curtype;
BEGIN
…
Открытие курсора с помощью переменной-ссылки на курсор:
OPEN ссылка_на_курсор FOR предложение_SELECT;
Команды FETCH и CLOSE используются как обычно.
Для удобства программирования поддерживается «системный» тип SYS_REFCURSOR нестрогой ссылки на курсор:
DECLARE
generic_cursor SYS_REFCURSOR;
someemployee emp.ename%TYPE;
BEGIN
OPEN generic_cursor FOR ' SELECT ename FROM emp ';
FETCH generic_cursor INTO someemployee;
DBMS_OUTPUT.PUT_LINE('Some employee: ' || someemployee);
CLOSE generic_cursor;
END;
/
Предложения TYPE здесь не понадобилось. Более полные примеры использования приводятся выше («Встроенный динамический SQL») и ниже по тексту.
Ограничения на использование ссылки на курсор:
- ссылки на курсор не могут объявляться как переменные пакета PL/SQL, и их нельзя передавать через переменные пакета
- ссылку на курсор нельзя связывать с блокирующим предложением SELECT … FOR UPDATE (в версии 10 уже можно)
- ссылкам на курсор нельзя присваивать значение NULL (в версии 10 уже можно) и их нельзя сравнивать друг с другом (но их можно присваивать друг другу)
- ссылки на курсор нельзя хранить в столбцах таблиц и в элементах коллекции
- ссылки на курсор нельзя передавать от сервера к серверу с помощью RPC
|
- ссылки на курсор нельзя использовать с пакетом DBMS_SQL
Примеры техники использования
Ссылка на курсор позволяет открыть курсор процедурой на сервере и передать с помощью ссылки в клиентской программе возможность работать с курсором. Ниже эта техника иллюстрируется в простом и в более реальным вариантах.
9.4.1.1. Простой пример
Выдадим в SQL*Plus:
VARIABLE refcur REFCURSOR
DECLARE
TYPE rct IS REF CURSOR;
somename VARCHAR2(20);
PROCEDURE getcur1 (rc OUT rct) IS BEGIN OPEN rc FOR ' SELECT ename FROM emp'; END;
PROCEDURE getcur2 (rc OUT rct) IS BEGIN OPEN rc FOR ' SELECT dname FROM dept'; END;
PROCEDURE getcur3 (rc OUT rct, num IN NUMBER) IS
BEGIN OPEN rc FOR ' SELECT dname FROM dept WHERE deptno = ' || TO_CHAR (num); END;
PROCEDURE fetchandclose (rc IN rct) IS
BEGIN
LOOP
FETCH rc INTO somename;
EXIT WHEN rc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (somename);
END LOOP;
CLOSE rc;
END;
BEGIN
getcur1 (:refcur); fetchandclose (:refcur);
getcur2 (:refcur); fetchandclose (:refcur);
getcur3 (:refcur, 30); fetchandclose (:refcur);
END;
/
Пример более реальный
В реальной программе описание типа (RCT) из примера выше чаще всего будет вынесено в пакет, а переменная привязки (REFCUR) и программа обработки курсора (FETCHANDCLOSE) будут вынесены в клиентскую программу, написанную, например, на Java, C или на Object Pascal, в то время как программы вычисления курсора (GETCUR1, GETCUR2, GETCUR3) останутся на сервере. Вот пример, как это можно организовать.
Создадим обобщенный пакет:
CREATE OR REPLACE PACKAGE generic_ref_cursor AS
TYPE refcur IS REF CURSOR;
PROCEDURE get_ref_cursor (sqlselect IN VARCHAR2, rc OUT refcur);
END;
/
CREATE OR REPLACE PACKAGE BODY generic_ref_cursor AS
PROCEDURE get_ref_cursor (sqlselect IN VARCHAR2, rc OUT refcur) AS
BEGIN
OPEN rc FOR sqlselect;
END;
END;
/
Возможный вариант использования пакета в PL/SQL и SQL*Plus:
SET SERVEROUTPUT ON
VARIABLE refcur REFCURSOR
DECLARE
PROCEDURE fetchandclose (rc IN generic_ref_cursor.refcur) IS
somename VARCHAR2(20);
BEGIN
LOOP
FETCH rc INTO somename;
EXIT WHEN rc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (somename);
END LOOP;
CLOSE rc;
END;
BEGIN
generic_ref_cursor.get_ref_cursor ('SELECT ename FROM emp',:refcur);
fetchandclose (:refcur);
generic_ref_cursor.get_ref_cursor ('SELECT dname FROM dept',:refcur);
fetchandclose (:refcur);
generic_ref_cursor.get_ref_cursor ('SELECT ename, sal FROM emp',:refcur);
-- ... а результат выдадим в SQL * Plus:
END;
/
PRINT refcur
BEGIN
generic_ref_cursor.get_ref_cursor ('SELECT * FROM emp',:refcur);
END;
/
PRINT refcur
Возможный вариант использования этой же техники в клиентской программе на Java:
import java.sql.CallableStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
...
...
CallableStatement cst;
OracleCallableStatement ocst;
ResultSet rs;
cst = cn.prepareCall
(" BEGIN generic_ref_cursor.get_ref_cursor (?,?); END; ");
|
cst.setString (1, " SELECT sal FROM emp ");
cst.registerOutParameter (2, OracleTypes.CURSOR);
cst.execute ();
ocst = (OracleCallableStatement) cst;
rs = ocst. getCursor (2);
while (rs.next ()) { System.out.println (rs.getInt (1)); }
...
10. Несколько примеров использования циклов и курсоров
Ниже приводится несколько типичных примеров разных использования курсоров и циклов разными способами.
Пример «низкоуровневого» использования:
DECLARE
CURSOR dept_cur IS SELECT dname FROM dept;
dname_var dept.dname%TYPE;
BEGIN
OPEN dept_cur;
LOOP
FETCH dept_cur INTO dname_var;
EXIT WHEN dept_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (dname_var);
END LOOP;
CLOSE dept_cur;
END;
/
Аналогичный курсор типа «строка»:
DECLARE
CURSOR dept_cur IS SELECT * FROM dept;
dept_rec dept_cur%ROWTYPE;
BEGIN
OPEN dept_cur;
LOOP
FETCH dept_cur INTO dept_rec;
EXIT WHEN dept_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (dept_rec.dname);
END LOOP;
CLOSE dept_cur;
END;
/
Пример перебора результата SQL-запроса более высокого уровня:
BEGIN
FOR department IN (SELECT * FROM dept) LOOP
DBMS_OUTPUT.PUT_LINE (department.dname);
END LOOP;
END;
/
То же самое с помощью явного курсора. Обратите внимание, что в этом случае появляется возможность обратиться к атрибуту курсора:
DECLARE
CURSOR departments IS SELECT * FROM dept;
BEGIN
FOR department IN departments LOOP
DBMS_OUTPUT.PUT_LINE (departments%ROWCOUNT || department.dname);
END LOOP;
END;
/
Пример перебора для курсора с параметром:
DECLARE
CURSOR coworkers (dnumber IN NUMBER) IS
SELECT ename FROM emp WHERE deptno = dnumber;
BEGIN
FOR employee IN coworkers (20) LOOP
DBMS_OUTPUT.PUT_LINE (employee.ename);
END LOOP;
END;
/
То же самое, но параметр с умолчанием:
DECLARE
CURSOR coworkers (dnumber IN NUMBER := 10) IS
SELECT ename FROM emp WHERE deptno = dnumber;
BEGIN
FOR employee IN coworkers (20) LOOP
DBMS_OUTPUT.PUT_LINE (employee.ename);
END LOOP;
FOR employee IN coworkers () LOOP -- скобки можно и опустить
DBMS_OUTPUT.PUT_LINE (employee.ename);
END LOOP;
END;
/
Пример перебора по ссылке на курсор см. выше («Встроенный динамический SQL»).
|
|
Наброски и зарисовки растений, плодов, цветов: Освоить конструктивное построение структуры дерева через зарисовки отдельных деревьев, группы деревьев...
История развития хранилищ для нефти: Первые склады нефти появились в XVII веке. Они представляли собой землянные ямы-амбара глубиной 4…5 м...
История развития пистолетов-пулеметов: Предпосылкой для возникновения пистолетов-пулеметов послужила давняя тенденция тяготения винтовок...
Двойное оплодотворение у цветковых растений: Оплодотворение - это процесс слияния мужской и женской половых клеток с образованием зиготы...
© cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста.
Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы!