Информационное обеспечение
жүктеу/скачать 256,49 Kb.
|
| Цикл WHILE имеет следующий синтаксис:
WHILE условие LOOP исполняемые_операторы; END LOOP; Цикл завершается, когда условие принимает значение FALSE или NULL. Цикл FOR. Цикл FOR может использоваться с числовым счетчиком или с курсором. В случае цикла FOR с числовым счетчиком количество итераций из- вестно до начала выполнения цикла. Общий вид цикла FOR: FOR индекс_цикла IN [REVERSE] начальное_значение .. конечное_значение LOOP исполняемые_операторы; END LOOP; При использовании цикла FOR следует придерживаться правил: переменную цикла можно не объявлять – она будет объявлена неявно с типом INTEGER и иметь область действия – тело цикла, поэтому ссылаться на нее вне цикла нельзя; внутри цикла нельзя изменять счетчик цикла и границы диапазона; используйте REVERSE для обратного счета, когда счетчик уменьшает- ся от начального значения к конечному; в цикле нельзя задавать шаг приращения, счетчик всегда увеличивается на 1; допускается использовать операторы EXIT / EXIT WHEN. /* первый пример с диапазоном, определяемым переменной*/ DECLARE m INTEGER := 3 * 2; BEGIN FOR i IN 1 ..m LOOP DBMS_OUTPUT.put_line ('m=' || m || ' i=' || i); END LOOP; END; / /* второй пример с диапазоном, определяемым переменной*/ BEGIN
DBMS_OUTPUT.put_line ('i='||i); END LOOP; END; / Циклу можно присвоит имя, воспользовавшись для этого меткой: < FOR i IN 1..4 LOOP . . . END LOOP; Использование меток цикла упрощает сопровождение программы, когда используется длинный цикл со вложенными в него другими циклами. Оператор GOTO. Оператор GOTO передает управление операторам, сле- дующим за указанной в GOTO меткой. GOTO <<имя метки>> После метки должен быть хотя бы один исполняемый оператор. /* пример для схемы HR */ DECLARE cntreg INTEGER := 6; reg INTEGER; BEGIN SELECT MAX (region_id) INTO reg FROM regions; FOR i IN reg + 1 ..reg + cntreg LOOP IF i> 8 THEN GOTO label_exit; END IF; INSERT INTO regions (region_id, region_name ) VALUES (i, 'TEST'); END LOOP; < END; / В этом примере показано также использование оператора NULL. Опера- тор NULL ничего не делает, а просто передает управление следующему за ним оператору, поэтому используется в случае, когда по правилам синтаксиса обя- зательно присутствие хотя бы одного оператора, но по логике программы не надо выполнять никаких действий. В примере, если не использовать NULL по- сле метки < Оператор GOTO в языках высокого уровня является объектом критики, поскольку его чрезмерное применение приводит к созданию нечитаемого и не- поддерживаемого кода. Тем не менее использование GOTO иногда допустимо, так как может значительно упростить код (например, избавить от необходимо- сти создания вспомогательных переменных и операторов условия). Ограничения при использовании GOTO: В PL/SQL на использование операторов GOTO налагаются определенные ограничения: нельзя передавать управление программой во внутренний блок, цикл или оператор IF; запрещается передавать управление из одной последовательности опе- раторов условного оператора IF в другую; нельзя передавать управление из обработчика исключительных ситуа- ций обратно в текущий блок. Работа с курсорами и курсорными переменными. Основное назначение языка PL/SQL – создание программ для работы с БД. Программа может взаи- модействовать с БД только посредством SQL, используя DML-операторы, кур- соры и динамический SQL. При обработке любого SQL-оператора Oracle выделяет область памяти, называемую контекстной областью (context area). Она содержит информацию, необходимую для начала и завершения обработки SQL-оператора, а именно: число строк, обрабатываемых оператором, указатель на представление этого оператора после синтаксического анализа (parsing) и активный набор (activeset) – набор строк, возвращаемых запросом. Таким образом, все базовые операторы DML – это курсоры. При выполне- нии INSERT, UPDATE и других операторов в PGA – глобальной системной об- ласти – всегда открывается курсор. Следовательно, курсор – это указатель на контекстную область памяти, с помощью которого программа на PL/SQL может управлять контекстной областью и ее состоянием во время обработки оператора. Курсор может представлять собой любое допустимое предложение языка SQL. Также курсор является основным базовым «кирпичиком» для построения блоков PL/SQL. Курсоры обеспечивают циклический механизм оперирования наборами данных в БД. Курсор может возвращать одну или несколько строк данных или вообще ни одной. В программах PL/SQL используется два типа курсоров: явные и неявные. Неявные курсоры, как правило, возвращают одну строку. Если курсор возвра- щает более одной строки, то следует использовать явные курсоры или курсоры в цикле FOR-LOOP. Неявные курсоры используются: для внесения изменений в таблицы БД с помощью операторов INSERT, UPDATE или DELETE; для извлечения данных с помощью оператора SELECT INTO, который возвращает скалярное значение или только одну строку в результирующем множестве запроса. Неявный курсор вида SELECT INTO позволяет делать выборку данных в переменную или переменные. /* пример курсора для схемы HR */ DECLARE s VARCHAR2 (500); BEGIN SELECT first_name || ' ' || last_name || ':' || email INTO s FROM employees WHERE employee_id = 105; DBMS_OUTPUT.put_line (s); END; / Для работы со строками, извлекаемыми из результирующего множества многострочного запроса, необходимо использовать явный курсор. Типичная последовательность действий при операциях с явными курсорами следующая: Объявление курсора и структуры данных, в которую будут помещены найденные строки. Открытие курсора. Последовательная выборка данных. Закрытие курсора. Рассмотрим полный синтаксис объявления явного курсора в разделе объ- явления переменных: CURSOR имя_курсора [(параметр1[, параметр2]...)] [RETURN возвращаемый_тип] IS SELECT_выражение; где возвращаемый_тип – запись или строка из базы данных. Примеры объявления явного курсора: DECLARE
IS SELECT employee_id, job_id, salary FROM employees; -- курсор с параметрами CURSOR emp_cur_par (dep IN NUMBER) IS SELECT employee_id, job_id, salary FROM employees WHERE department_id = dep; -- курсор с предложением RETURN CURSOR emp_cur_return (dep IN NUMBER) RETURN employees%ROWTYPE IS SELECT * FROM employees WHERE department_id = dep; BEGIN NULL; END; / Открытие курсора выполняется с помощью оператора OPEN: OPEN имя_курсора [аргумент 1 [, аргумент2 ..]]; При попытке открыть уже открытый курсор генерируется ошибка ORA- 06511: PL/SQL: cursor already open. Для проверки состояния курсора существуют следующие атрибуты: %ISOPEN – возвращает TRUE, если курсор открыт, иначе FALSE. %FOUND – возвращает TRUE, если последний FETCH вернул данные, иначе FALSE. Сразу после открытия имеет значение NULL. %NOT FOUND – альтернатива %FOUND, если последний FETCH не вер- нул данные, иначе FALSE. Сразу после открытия имеет значение NULL. %ROWCOUNT – возвращает количество строк, выбранных на данный момент из курсора. Сразу после открытия и до первого оператора FETCH имеет значение 0. Извлечение данных выполняется при помощи оператора FETCH, имею- щего синтаксис: FETCH имя_курсора INTO переменные_или_курсорная_переменная; Закрытие курсора производится оператором CLOSE имя_курсора Пусть необходимо увеличить заработную плату всем сотрудникам депар- тамента на 10 % и вывести изменения на экран. /* Изменение заработной платы сотруднику, схема HR */ DECLARE -- отделIT vdep departments.department_id%TYPE := 60; -- % повышения (снижения) заработной платы pctsal NUMBER (5, 2) := 30; -- курсор с параметром CURSOR listemp_cur (dep IN NUMBER) IS SELECT emp.employee_id, emp.job_id, emp.salary, jobs.min_salary, jobs.max_salary FROM employees emp, jobs WHERE department_id = dep AND emp.job_id = jobs.job_id; -- курсорная переменная listemp listemp_cur%ROWTYPE; newsal employees.salary%TYPE; BEGIN OPEN listemp_cur(vDep); FETCH listemp_cur INTO listemp; WHILE listemp_cur%FOUND LOOP newsal := listemp.salary * (100 + pctsal) / 100; IF newsal < listemp.min_salary THEN newsal := listemp.min_salary; ELSIF newsal > listemp.max_salary THEN newsal:= listemp.max_salary; END IF; DBMS_OUTPUT.put_line ( 'Cотрудник ' || listemp.employee_id || ' старая заработная плата =' || listemp.salary || ' новая заработная плата =' || ' = ' || newsal); FETCH listemp_cur INTO listemp; END LOOP; CLOSE listemp_cur; END; / В примере обращение к данным, возвращаемым курсором, происходит при помощи курсорной переменной listemp. Курсорная переменная в отличие от обычной представляет собой указатель на рабочую область, где размещают- ся данные курсора. Для создания курсорной переменной есть два способа: определение REF CURSOR TYPE и непосредственное объявление переменной в блоке PL/SQL. При объявлении REF CURSOR TYPE возможно либо жестко прописы- вать тип, возвращаемый курсором, либо не прописывать тип вообще. Ссылка на курсор дает возможность не заводить структуры курсора в кли- ентской программе, а ограничиться в ней выделением памяти только для адреса курсора, в то время как сам курсор будет располагаться целиком в СУБД. Чтобы завести в PL/SQL переменную-ссылку на курсор, нужно сначала опи- сать ее тип. Это делается в разделе объявлений с помощью предложения TYPE: TYPE имя_типа_ссылки_на_курсор IS REF CURSOR [RETURN тип_записи]; Если конструкция RETURN присутствует, ссылка на курсор называется строгой; если нет – нестрогой. Нестрогая ссылка на курсор может ссылаться на любой курсор (запрос), а строгая – только на тот, который возвращает резуль- тат указанного типа. Пример объявления типов курсорных переменных – ссылок на курсор: -- нестрогий указатель на курсор TYPE dep_cur_type IS REF CURSOR; --строгий указатель на курсор с привязкой к таблице employees TYPE emp_cursor_type IS REF CURSOR RETURN employees%ROWTYPE; -- строгий указатель на курсор с привязкой к записи TYPE jobs_type IS RECORD ( job_id VARCHAR2 (10), min_salary NUMBER (6), max_salary NUMBER (7)); TYPE job_cursor_type IS REF CURSOR RETURN jobs_type; Курсорная переменная, объявленная как REF CURSOR, может использо- ваться в качестве параметра процедуры/функции или в качестве значения, воз- вращаемого функцией. Объявление курсорной переменной описанных выше типов ссылок на курсоры: v_dep_cur dep_cur_type; v_emp_cursor emp_cursor_type; v_job_cursor job_cursor_type; Далее в программе происходит открытие курсора с помощью переменной – ссылки на курсор: OPEN ссылка_на_курсор FOR предложение_SELECT; Команды FETCH и CLOSE используются как обычно, только вместо име- ни курсора указывается имя переменной – ссылки на курсор. жүктеу/скачать 256,49 Kb. Достарыңызбен бөлісу:
|