Лабораторные_БД_ЭВМ_20 (AutoRecovered). Методические указания по выполнению лабораторных работ по дисциплине (модулю)
 Скачать 0.75 Mb.
|
Лабораторная работа №11Хранимые процедуры 1. Цель и задачи работы Целью лабораторной работы является изучение и практическое применение хранимых процедур. 2. Порядок выполнения работы - ознакомится с теоретическими сведениями; - выполнить задание; - оформить отчет; - ответить на контрольные вопросы, заданные преподавателем. 3. Оформление отчета Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе. 4. Теоретические сведения PL/SQL – это язык, структурированный блоками. Это значит, что основные единицы (процедуры, функции и анонимные блоки), составляющие программу PL/SQL, являются логическими БЛОКАМИ, которые могут содержать любое число вложенных в них подблоков. Структура блока имеет вид: [DECLARE -- описание переменных, констант и пользовательских типов данных] BEGIN -- операторы SQL -- управляющие операторы PL / SQL [EXCEPTION -- действия, выполняемые при возникновении ошибки] END; Программы PL/SQL могут быть неименованными (анонимными блоками), но чаще всего используются именованные программы: процедуры, функции, пакеты и триггеры. Циклы Простой цикл.Синтаксис: LOOPоператор 1;. . .EXIT [WHENусловие]; END LOOP; Простой цикл бесконечен. Завершить цикл можно оператором EXIT.Цикл FOR. Синтаксис: FORиндексIN[REVERSE]нижняя_граница.верхняя_границаLOOPоператор 1; . . . ENDLOOP;индекс– неявно описанная целая переменная, значение которой автоматически увеличивается или уменьшается на 1 при каждом выполнении цикла, пока не будет достигнута верхняя граница. Объявлять индекс не нужно!!! REVERSE – вызывает уменьшение индекса. Внутри цикла FOR к индексу цикла можно обращаться как к константе. Поэтому индекс может встречаться в выражениях, но ему нельзя присваивать значений, как показывает следующий пример: FOR ctr IN 1..10 LOOP ... IF NOT finished THEN INSERT INTO ... VALUES (ctr, ...); -- законноfactor := ctr * 2; -- законно ...ELSEctr := 10; -- незаконно END IF; ENDLOOP;  Цикл WHILE. Цикл WHILE используется для повторения последовательности операторов в течение всего времени, пока значение условия равно TRUE. Синтаксис:WHILEусловиеLOOPоператор 1;. . .ENDLOOP;Пример:WHILEtotal<= 25000 LOOP ... SELECT sal INTO salary FROM empWHERE ...total := total + salary; END LOOP; Циклы могут быть вложены один в другой на несколько уровней. Управление курсорами Явные курсоры Множество строк, возвращаемых запросом (активное множество), может состоять из нуля, одной или нескольких строк, в зависимости от того, сколько строк удовлетворяют вашим поисковым условиям. Когда запрос возвращает несколько строк, вы можете явно определить курсор для обработки этих строк. Вы определяете курсор в декларативной части блока PL/SQL, подпрограммы или пакета путем задания его имени и специфицирования запроса. После этого вы манипулируете курсором при помощи трех команд: OPEN, FETCH и CLOSE. Прежде всего вы инициализируете курсор предложением OPEN, которое идентифицирует активное множество. Затем с помощью предложения FETCH вы извлекаете первую строку. Вы можете повторять FETCH неоднократно, пока не будут извлечены все строки. После обработки последней строки вы освобождаете курсор предложением CLOSE. Вы можете обрабатывать параллельно несколько запросов, объявив и открыв несколько курсоров. Объявление курсора Ссылки в перед недопустимы в PL/SQL. Поэтому вы должны объявить курсор, прежде чем сможете ссылаться на него в других предложениях. Объявляя курсор, вы даете ему имя и ассоциируете его с конкретным запросом. В следующем примере объявляется курсор с именем c1: DECLARE CURSORc 1ISSELECTename ,deptnoFROMempWHEREsal> 2000; ... BEGIN... Имя курсора - это необъявленный ранее идентификатор, а не переменная PL/SQL; его можно использовать только для обращения к запросу. Вы не можете присваивать значений имени курсора или использовать его в выражениях. Параметризованные курсоры Курсоры могут принимать параметры, как показывает следующий пример. Параметр курсора может появляться в запросе всюду, где допускается появление константы. CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHER sal> median; Для объявления формальных параметров курсора используется синтаксис: CURSOR имя[ (параметр [, параметр, ...]) ] IS SELECT... гдепараметр, в свою очередь, имеет следующий синтаксис:имя_переменной [IN] тип_данных [{:= | DEFAULT} значение] Как показывает следующий пример, вы можете инициализировать параметры курсора умалчиваемыми значениями. Таким способом вы можете передавать курсору различное число фактических параметров, принимая или перекрывая умалчиваемые значения по своему желанию. Более того, вы можете добавлять в курсор новые формальные параметры без необходимости отыскивать и исправлять все обращения к курсору в тексте программы. DECLARE CURSOR c1 (low INTEGER DEFAULT 0,high INTEGER DEFAULT 99) IS SELECT ... ... Сфера параметров курсора локальна в этом курсоре, что означает, что к этим параметрам можно обращаться лишь в запросе, который участвует в объявлении курсора. Значения параметров курсора используются ассоциированным запросом в момент открытия курсора. Открытие курсора Открытие курсора предложением OPEN исполняет предложение SELECT и идентифицирует АКТИВНОЕ МНОЖЕСТВО, т.е. все строки, удовлетворяющие поисковым условиям запроса. Для курсоров, объявленных с фразой FOR UPDATE, предложение OPEN также осуществляет блокировку этих строк. Пример предложения OPEN: OPEN c1; Предложение OPEN не извлекает строк активного множества. Для этого используется предложение FETCH. Передача параметров Курсору могут быть переданы параметры при открытии. Например, при объявлении курсора CURSOR c1 (my_ename CHAR, my_commNUMBER) IS SELECT ... любое из следующих предложений открывает этот курсор: OPEN c1('ATTLEY', 300); OPENc1(employee_name, 150); OPEN c1('THURSTON', my_comm); В последнем примере переменная, специфицированная в предложении OPEN, имеет такое же имя, что и параметр в объявлении курсора. Когда идентификатор my_comm используется в объявлении курсора, он обозначает формальный параметр курсора. Когда этот же идентификатор используется вне объявления курсора, он обозначает переменную PL/SQL с этим именем. Однако, для ясности, рекомендуется использовать уникальные идентификаторы. Если вы не хотите принять умалчиваемые значения, каждому формальному параметру в объявлении курсора следует сопоставить соответствующий фактический параметр в предложении OPEN. Формальные параметры, объявленные с умалчиваемым значением, могут и не иметь соответствующих им фактических параметров. В этом случае они просто принимают свое умалчиваемое значение во время выполнения OPEN. Не забывайте, что формальные параметры курсора должны быть параметрами IN, так что они не могут возвращать значений фактическим параметрам. Извлечение данных из курсора Предложение FETCH извлекает очередную строку из активного множества. При каждом выполнении FETCH курсор продвигается к следующей строке в активном множестве. Пример предложения FETCH: FETCHc1 INTO my_empno, my_ename, my_deptno; Для каждого значения столбца, извлекаемого запросом, ассоциированного с курсором, в списке INTO должна быть соответствующая переменная, имеющая совместимый с этим столбцом тип данных. Обычно вы используете неиндивидуальные переменные, а запись: ... OPEN c1; LOOP FETCH c1 INTO my_record; EXITWHENc 1%NOTFOUND ; -- обработатьизвлеченные данные END LOOP; Любые переменные в фразе WHERE запроса, ассоциированного с курсором, вычисляются лишь в момент открытия курсора. Как показывает следующий пример, запрос может обращаться к переменным PL/SQL внутри своей сферы: DECLARE my_salemp.sal%TYPE; my_jobemp.job%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT factor*sal FROM empWHERE job = my_job; BEGIN ... OPEN c1; -- здесь factor равен 2 LOOP FETCH c1 INTO my_sal; EXIT WHEN c1%NOTFOUND; ... factor := factor + 1; -- не окажет влияния на FETCH END LOOP ; END ; В этом примере каждое извлекаемое значение sal будет умножаться на 2, несмотря на то, что значение factor увеличивается после каждой операции FETCH. Чтобы изменить активное множество или значения переменных в запросе, вы должны закрыть и заново открыть курсор с новыми значениями входных параметров. Однако для каждой операции FETCH на одном и том же курсоре вы можете использовать собственный список INTO. Каждая FETCH извлекает строку и присваивает значения своим переменным INTO, как показывает следующий пример: DECLARE CURSOR c1 IS SELECT ename FROM emp;name1 emp.ename%TYPE;name2 emp.ename%TYPE;name3 emp.ename%TYPE; BEGIN OPEN c1; FETCH c1 INTO name1; --извлекаетпервуюстроку FETCH c1 INTO name2; -- извлекает вторую строку FETCH c1 INTO name3; -- извлекает третью строку ... CLOSEc 1; END ; Если вы выдаете FETCH, но в активном множестве больше нет строк, то значения переменных в списке INTO не определены. Закрытие курсора Предложение CLOSE деактивирует курсор, и активное множество становится неопределенным. Пример предложения CLOSE: CLOSE c1; После того, как курсор закрыт, вы можете снова открыть его. Атрибуты явного курсора Каждый курсор, явно объявленный вами, имеет четыре атрибута: % NOTFOUND, % FOUND, % ROWCOUNT и % ISOPEN. Атрибуты позволяют вам получать полезную информацию о выполнении многострочного запроса. Для обращения к атрибуту просто присоедините его имя к имени курсора. Атрибуты явного курсора можно использовать в процедурных предложениях, но не в предложениях SQL. Использование %NOTFOUND Когда курсор открыт, строки, удовлетворяющие ассоциированному запросу, идентифицированы и образуют активное множество. Эти строки извлекаются операцией FETCH по одной за раз. Если последняя операция FETCH вернула строку, %NOTFOUND дает FALSE. Если последняя операция FETCH не смогла вернуть строку (так как активное множество исчерпано), %NOTFOUND дает TRUE. Операция FETCH должна в конце концов исчерпать активное множество, так что, когда это происходит, никакого исключения не возбуждается. В следующем примере вы используете %NOTFOUND, чтобы выйти из цикла, когда FETCH не сможет вернуть строку: LOOP FETCH c1 INTO my_ename, my_deptno;EXIT WHEN c1%NOTFOUND; ... END LOOP; Перед первой операцией FETCH атрибут % NOTFOUND дает NULL . Поэтому, если FETCH ни разу не выполнится успешно, вы никогда не выйдете из этого цикла. Причина в том, что предложение EXIT WHEN выполняется только в том случае, когда условие WHEN дает TRUE. Поэтому для безопасности вы можете предпочесть такой вариант предложения EXIT: EXIT WHEN c1%NOTFOUND OR c1%NOTFOUNDIS NULL; Вы можете открыть несколько курсоров, а затем использовать %NOTFOUND, чтобы проверять, в каких курсорах еще есть строки.. Использование %FOUND %FOUND логически противоположен атрибуту %NOTFOUND. После открытия явного курсора, но до первой операции FETCH, %FOUND дает NULL. Впоследствии он дает TRUE, если последняя операция FETCH вернула строку, или FALSE, если последняя операция FETCH не смогла извлечь строку, так как больше нет доступных строк. Следующий пример использует %FOUND, чтобы выбрать одно из двух альтернативных действий: LOOP FETCH c1 INTO my_ename, my_deptno; IF c1%FOUND THEN --извлечение успешно INSERT INTO temp VALUES (...); ELSE EXIT; ... END LOOP; Вы можете открыть несколько курсоров, а затем использовать %FOUND, чтобы проверять, в каких курсорах еще есть строки. Использование %ROWCOUNT Когда вы открываете курсор, его атрибут %ROWCOUNT обнуляется. Перед первой операцией FETCH %ROWCOUNT возвращает 0.Впоследствии, ROWCOUNT возвращает число строк, извлеченных операциями FETCH из активного множества на данный момент. Это число увеличивается, если последняя FETCH вернула строку. Следующий пример использует %ROWCOUNT, чтобы предпринять определенные действия, если выбрано более10 строк: LOOP FETCH c1 INTO my_ename, my_deptno; IFc 1%ROWCOUNT > 10THEN--выбрано больше 10 строк ... END IF; END LOOP; Вы можете открыть несколько курсоров, а затем использовать %ROWCOUNT, чтобы проверять, сколько строк извлечено из каждого курсора. Использование %ISOPEN %ISOPEN дает TRUE, если явный курсор открыт, и FALSE в противном случае. Следующий пример использует %ISOPEN для выбора действия: IF с1%ISOPEN THEN -- курсор открыт ... ELSE -- курсор закрыт, открыть его OPENc 1; ENDIF ; Курсорные циклы FOR Курсорный цикл FOR неявно объявляет свой индекс цикла как запись типа %ROWTYPE, открывает курсор, в цикле извлекает строки из активного множества в поля записи, и закрывает курсор, когда все строки обработаны или когда вы выходите из цикла. В большинстве ситуаций, которые требуют явного курсора, текст программы может быть упрощен при использовании "курсора в цикле FOR", заменяющего команды OPEN, FETCH и CLOSE. Курсор в цикле FOR: - неявно объявляет индекс цикла записью, поля которой соответствуют столбцам (псевдонимам) предложения SELECT ... из описания курсора; - передает параметры курсора (если они есть) и открывает курсор; - выбирает в цикле строки из полученного набора в индекс цикла (поля записи); – закрывает курсор после обработки всех строк набора или досрочному выходу из него с помощью команд EXIT или GOTO. Синтаксис курсора в цикле FOR имеет вид: FOR var_rec_name IN cursor_name [(value [,value]...) ] LOOPТЕЛОЦИКЛАEND LOOP,  где – var_rec_name индекс цикла, в котором при первом прохождении цикла хранится первая строка набора, при втором прохождении цикла - вторая строка и т.д.; – список значений ("value") используется для передачи параметров курсора (он заменяет в данном случае список из команды OPEN); - ТЕЛО ЦИКЛА содержит нужные строки повторяющейся части программы, в которых используются переменные с именами var_rec_name.column_name, а column_name имя столбца из перечня столбцов предложения SELECT в описании курсора. Рассмотрим следующий блок PL/SQL, который анализирует данные, собранные в ходе лабораторных экспериментов, и помещает результаты во временную таблицу. Переменная c1rec, используемая как индекс в курсорном цикле FOR, неявно объявляется как запись, хранящая все элементы данных, возвращаемые одной операцией FETCH для курсора c1. Вы обращаетесь к элементам данных, хранящимся в полях записи, используя квалифицированные ссылки. DECLARE result temp.col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1; BEGIN FOR c1rec IN c1 LOOP /* вычислить и сохранить результаты */ result := c 1 rec . n 2 / ( c 1 rec . n 1 + c 1 rec . n 3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP ; COMMIT ; END; То же самое без использования цикла FOR : DECLARE num1 data_table.n1%TYPE; -- Объявить переменные num2 data_table.n2%TYPE; -- с теми же типами , num3 data_table.n3%TYPE; -- что и столбцы таблицы result temp.col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROMdata_table WHERE exper_num = 1; BEGIN OPEN c1; LOOP FETCH c1 INTOnum1, num2, num3; EXIT WHEN c1%NOTFOUND; -- условиеc1%NOTFOUNDбудетравноTRUE, -- когда FETCH необнаружит больше строк /* вычислить и сохранить результаты */ result := num 2/( num 1 + num 3); INSERT INTO temp VALUES (result,NULL, NULL); END LOOP; CLOSE c1; COMMIT; END; Процедуры Процедура – это подпрограмма, которая выполняет специфическое действие. Вы пишете процедуры, используя синтаксис PROCEDUREимя [ (параметр [, параметр, ...]) ] IS [локальные объявления]BEGIN исполняемые предложения [EXCEPTIONобработчики исключений]END [имя];где каждый "параметр" имеет следующий синтаксис: имя_перем [IN | OUT | IN OUT] тип_данных [{:= | DEFAULT} знач] Каждый параметр сопровождается режимом и типом. В качестве режима можно задавать: IN (только чтение), OUT (только запись) и INOUT (чтение и запись). Замечание: в описании типа нельзя задавать длину. Например: CHAR(10) или VARCHAR(20) не допустимые описания, а CHAR или VARCHAR – допустимые. Количество символов (длина) будет определена при вызове процедуры по фактическому параметру (аргументу) функции. Процедура начинается с ключевых слов CREATE PROCEDURE, за которыми следует ее имя и список параметров. В качестве ключевого слова (описателя) вместо CREATE может использоваться ORREPLACE. Преимущество использования этого ключевого слова в том, что если процедура с каким-то именем уже определена, то новое определение с тем же именем не вызовет ошибки. С другой стороны, предыдущее определение процедуры с аналогичным именем заменится новым определением, и старая процедура перестанет существовать. Процедура имеет две части: спецификацию и тело. Спецификация процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Объявления параметров необязательны. Если процедура не принимает параметров, скобки также не кодируются. Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END, за которым может следовать имя процедуры. Тело процедуры состоит из трех частей: декларативной части, исполняемой части и необязательной части обработки исключений. |