Как сделать цикл sql

Добавил пользователь Алексей Ф.
Обновлено: 05.10.2024

чтобы мне пройтись по всем записям таблицы и брать от туда поочереди данные и помещать в переменную
типа

так правильно будет через курсор?

чтобы мне пройтись по всем записям таблицы и брать от туда поочереди данные и помещать в переменную
типа

так правильно будет через курсор?

отрабатывает только внутренний цикл while 30 раз всего лишь с одной переменной равной ПЕРВОЙ записи из курсора

Мне нужно во внешнем цикле получать данные из курсора (с первой по последнюю запись) и передавать в качестве переменной во внутренний цикл While.

отрабатывает только внутренний цикл while 30 раз всего лишь с одной переменной равной ПЕРВОЙ записи из курсора

такая отладка выдает:
Первая запись
"X row(s) affected"
"X row(s) affected"
"X row(s) affected"
.
Вторая запись
Третья запись
.
Сотая запись

SET @date_from = CAST('2013-10-01' AS datetime)
SET @date_to = CAST('2013-10-31' AS datetime)

Самый простой тип цикла в языке PL/SQL таков:

  1. EXIT - Безусловный выход из цикла. Используется посредством применения оператора IF.
  2. EXIT WHEN - Выход при выполнении условия.
  3. GOTO - Выход из цикла во внешний контекст.

Давайте рассмотрим пример с применением цикла LOOP EXIT WHEN. Запишем следующее:

Получаем после исполнения:

Видимых действий не было, но и ошибок то же! Первый цикл закончился после того как i стало равно 10. При этом оно получило значение 0 и произошел выход из цикла. Второй цикл применил вложенное предложение EXIT WHEN, что является более верным его использованием синтаксически. Тем не менее, применение условных операторов предполагает перед выходом из цикла проделать некоторые действия. Цикл LOOP EXIT WHEN END LOOP в последующем будет самым, часто используемым при построении конструкций курсоров. Рассмотрим еще одну разновидность вышеприведенного цикла:

Здесь в отличие от предыдущего цикла, действия выполняются до тех пор пока условие истинно. Если условие ложно, то цикл прекращается. Что хорошо видно из приведенного примера. В PL/SQL в конструкциях циклов нет такого, иногда полезного, оператора как CONTINUE, вследствие того, что выражение CONTINUE зарезервировано языком PL/SQL и используется для других целей. Но такую конструкцию как CONTINUE можно эмулировать, применив цикл вида LOOP EXIT WHEN END LOOP и используя весьма не популярный, но в данном случае очень полезный оператор GOTO!

Запишем следующее, выведем все нечетные числа до 20:

В результате применения GOTO в теле цикла получаем не сложную и понятную логику работы. Теперь давайте рассмотрим, не менее полезный и очень популярный в PL/SQL цикл FOR. Он к стати очень удобен при работе с курсорами, но об этом чуть позднее. Запишем следующее:

В данный момент FOR успешно ничего не делал аж сто раз! Итак, давайте рассмотрим его чуть ближе, IN как и в операторе SELECT задает диапазон значений итерации цикла, а ".." это как вы помните так называемый "оператор диапазона"! Вот так просто и ясно. Остальное уже знакомо. Замечу так же, что переменная цикла i является переменной только для чтения. По этому шаг цикла FOR изменить принудительно нельзя! Если это необходимо, то лучше применять цикл вида LOOP EXIT WHEN END LOOP! :) Теперь давайте поработаем с числами:

Та же задачка, только с циклом FOR. Да, функция MOD возвращает остаток от деления чисел, как вы, наверное, уже догадались. Так же в операторе FOR есть возможность задавать обратный отсчет, ну, например, перед стартом или взрывом! :) Вот так:

Нолика не было, но бабахнуло! Вот такой достаточно богатый набор операторов циклов в языке PL/SQL! Надеюсь, вы научитесь с легкостью их применять при построении серверных приложений ваших БД! :)

Динамический SQL это расширение, позволяющее писать подпрограммы на процедурном языке с использованием команд SQL. PostgreSQL поддерживает несколько процедурных языков как PL/Tcl и PL/Perl. Здесь же рассматривается PL/pgSQL, который очень близок по синтаксису и своим возможностям с Oracle PL/SQL. Процедурный язык в MySQL более ограничен и находится на стадии разработки.

Для использования языка PL/pgSQL, его надо установить в БД командой
CREATE LANGUAGE plpgsql;

Процедурные операторы

блоковая структура кода

Процедурный язык имеет блоковую структуру.
Группировка команд производится операторами BEGIN END.
Вложенные блоки разрешаются. Oracle позволяет независимый блок, у остальных внешний блок
должен быть в составе функции
или т.п. Оператор DECLARE позволяет объявить локальные переменные блока.
По умолчанию переменные инициализируются в null.
Для удобства в Oracle и PostgreSQL можно использовать два псевдотипа type и rowtype.
Первый определяет тип переменной такой же как у столбца указанной таблицы.
Второй определяет тип переменной как структуру соответствующую записи указанной таблицы.

присвоение

Для присвоения значения переменной используется операция :=.
В MySQL присвоение должно происходить в операторе SET (по стандарту).

условный оператор

Условный оператор IF позволяет выполнить ряд команд, если указанное условие истинно.
Внутри него может быть сколько угодно не обязательных алтернативных условий ELSE IF,
и одного необязательного слова ELSE,
определяющего что делать, если ни одно условие не выполнено.

оператор выбора

В первом синтаксисе оператор выбора CASE позволяет выполнить тот или иной код
в зависимости от значения указанного выражения. Во втором синтаксисе конструкция CASE аналогична условному оператору. Обратите внимание, что в отличие от CASE, используемого в обычных командах SQL, этот оператор завершается словами END CASE, а не просто END. Данный оператор не поддерживается в PostgreSQL.

метки

Метки полезны, когда есть вложенные циклы и нужно выйти из внешнего внутри вложенного. Фактически они именует блок или цикл. А в MySQL они обязательны для операторов выхода из цикла и продолжения итерации.

безусловный цикл

Безусловный цикл определяется оператором LOOP. Другими словами условие выхода указывается явно внутри тела цикла.

цикл с предусловием

Цикл с предусловием определяется оператором WHILE.

цикл по счетчику

MySQL не поддерживает цикл по счетчику. Счетчик в виде переменной объявлять не надо. По умолчанию счетчик изменяется на 1, если присутствует ключевое слово REVERSE на -1.

цикл по элементам

Для обработки записей запроса добавлен специальный цикл. В PostgreSQL переменная, в которой будет храниться текущая запись, должна быть объявлена явно с типом RECORD. Также вместо одной переменной можно указать список переменных, соответствующих столбцам запроса.

операторы выхода/продолжения итерации

Для выхода из цикла и перехода на следующую итерацию служат операторы EXIT и CONTINUE в Oracle и PostgreSQL. В MySQL это операторы LEAVE и ITERATE.

Выборка в переменные

В динамический SQL добавлен особый синтаксис для команды SELECT, позволяющий сохранить выбранные данные в указанные переменные. Если запрос возвращает более однойзаписи или ни одной, то возникает исключение.

Хранимые процедуры

Процедуры и функции основные места, где разрешено использования процедурного расширения, что позволяет решать более сложные задачи. Процедуры не возвращают значений как это делают функции, однако результат их работы можно сохранить в аргументах. Для этого им надо задать атрибут OUT, по умолчанию же аргументы обычно имеют атрибут IN.

Некоторые СУБД позволяют перегрузку, т.е. создание нескольких процедур или функций с одним именем, но с разным числом или типами аргументов.

Использование хранимых процедур уменьшает нагрузку на клиентские машины, за счет нагрузки на сервер. Во-вторых, они облегчают разработку клиентских программ, особенно их модернизацию. Так как при незначительных изменениях хранимой процедуры не нужно компилить заново клиентскую программу (вызывающую хранимую процедуру) и раздавать новые версии пользователям.

Для примера создадим процедуру вставки пяти записей в следующую таблицу:

Oracle

PostgreSQL

Для создания процедур и функций в PostgreSQL используется одна конструкция. В первом случае мы просто не указываем тип возвращаемого значения. Более того, это запрещено, если выходных параметров более одного. Если выходных аргументов нет, то в качестве возвращаемого типа надо указать void. Также нельзя задавать аргументам значение по умолчанию, но это компенсируется механизмом перегрузки.

MySQL

Ниже приведен пример процедуры для MySQL.

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

Хотя по стандарту вызов процедур делается командой CALL, в Oracle она разрешена только вне процедурного блока. А внутри блока вызывается обычным образом.
PostgreSQL вообще не поддерживает эту команду и для вызова процедуры используется команда SELECT.

Хранимые функции

Практически все сказанное о процедурах относится и к функциям. Для примера создадим функцию inc. Она будет принимать один целочисленный аргумент n и возвращать значение большее n на единицу. А для хранения единицы воспользуемся локальной переменной o.

Oracle

PostgreSQL

В PostgreSQL если функции не нужно процедурное расширение, то в качестве языка можно использовать SQL. В этом случае возвращаемое значение определяется последней командой select, а к параметрам можно обратится только по их номерам.

MySQL

В MySQL для функций есть дополнительные ограничения:

  • аргументы не могут быть выходными;
  • не могут использоваться некоторые команды SQL, например commit;
  • запрещена рекурсия.

Исключения

Рассматриваемые здесь СУБД позволяют по-своему обработать исключения — ситуации
возникновения ошибок. В Oracle и Postgre это достигается добавлением в блок секции EXCEPTION, в котором и происходит обработка исключения.

Для конкретности рассмотрим исключения, возникающие во время SELECT INTO для следующей таблицы с данными.

Oracle

PostgreSQL

Ключевое слово strict позволяет генерировать исключения.

MySQL

В MySQL обработчик исключения объявляется вначале блока при этом указывается продолжать ли выполение после обработки (CONTINUE) или выйти из блока (EXIT). Сама обработка должна занимать только один оператор, обычно это SET или BEGIN END.

Курсоры

Курсор — объект, позволяющий работать с областью памяти, в которой сохраняются записи
указанного запроса данных. В стандарте определены три команды управления курсором:

  • OPEN — открывает курсор;
  • FETCH — берет текущую запись из результирующего набора записей. После каждого
    применения FETCH следующая запись становится активной. Структурная переменная или
    список переменных указанных во фрагменте INTO должны соответствовать столбцам записи;
  • CLOSE — закрывает курсор, в большинстве СУБД закрытие курсора делается автоматически в конце блока, в котором курсор объявлен.

Oracle

В Oracle для определения успешного выбора записи используются два атрибута курсора:

  • FOUND — истина, если запись найдена;
  • NOTFOUND — истина, если запись не найдена.

Для сохранения данных всех записей курсора в массивы используется конструкция
BULK COLLECT. Ниже приведен пример функции подсчитывающий число записей в таблице tblname с четным идентификатором с помощью курсоров.

PostgreSQL

В PostgreSQL для определения успешного выбора записи курсора используется специальная
переменная FOUND. Кроме стандартных возможностей позволяется перемещаться по курсору командой MOVE без выбора данных. Во-вторых, выбор записи и перемещение можно делать не последовательно, а перепрыгивая через записи и в любом направлении. Ниже приведен аналогичный пример для PostgreSQL.

MySQL

Ниже приведен аналогичный пример для MySQL.

Триггеры

это процедура обработки события связанного с таблицей, которая вызывается автоматически. Ниже приведен список стандартных событий:

  • удаление записи из таблицы;
  • вставка записи в таблицу;
  • изменение записи в таблице.

Дополнительно можно указать до или после производить обработку события. Внутри триггера доступны две особых переменных определяющих значение старой и новой записи.
Для изменения доступна только вторая переменная, и то, если обработка происходит до
наступления события. Для примера создадим триггер на вставку для следующей таблицы:

Назначение нашего тригера в подмене null значений поля dt текущей датой перед вставкой новой записи.

Oracle

PostgreSQL

В PostgreSQL триггер не содержит кода, а только указывает на функцию, которую нужно
запускать. Причем не любая функция, а возвращающая тип trigger.
Кроме переменных new и old в триггерных процедурах доступны дополнительные переменные,
которые тоже могут пригодится.

MySQL

Числа прописью

В практической жизни для печати различных документов полезна функция преобразования числа в строку, где число указано словами. Ниже приведен пример решения этой задачи на Oracle для чисел меньших миллиона.
При желании диапазон чисел легко расширить до необходимого. Переписать код для другой СУБД также не представляет никакой сложности.Возможно можно и проще, но привожу, что первое пришло в голову.

Периодически возникает задача поиска связанных данных по набору ключей, пока не наберем нужное суммарное количество записей.

Как слышится, так и пишется

Сначала набросаем самый простой вариант запроса, передавая ID исполнителей массивом в качестве входного параметра :

Немного грустно — мы заказывали всего 20 записей, а Index Scan вернул нам 960 строк, которые потом еще и сортировать пришлось… А давайте попробуем читать поменьше.

unnest + ARRAY

Первое соображение, которое нам поможет — если нам надо всего 20 отсортированных записей, то достаточно читать не более 20 отсортированных в том же порядке по каждому ключу. Благо, подходящий индекс (owner_id, task_date, id) у нас есть.

О, уже намного лучше! На 40% быстрее, и в 4.5 раза меньше данных пришлось читать.

Рекурсивный аккумулятор

В предыдущем варианте суммарно мы прочитали 200 строк ради нужных 20. Уже не 960, но еще меньше — можно?

Давайте попробуем воспользоваться знанием, что нам надо всего 20 записей. То есть будем итерировать вычитку данных только до достижения нужного нам количества.

Шаг 1: стартовый список

Страшный итоговый запрос

Таким образом, мы обменяли 50% чтений данных на 20% времени выполнения. То есть если у вас есть причины полагать, что чтение может быть долгим (например, данные зачастую не в кэше, и приходится за ними ходить на диск), то таким способом можно зависеть от чтения меньше.

Читайте также: