Sql server выход из цикла



Сегодня узнаем, как работать с циклами, т.е. выполнять один и тот же запрос несколько раз. В MySQL для работы с циклами применяются операторы WHILE, REPEAT и LOOP.

Оператор цикла WHILE

Сначала синтаксис:

WHILE условие DO запрос END WHILE

Запрос будет выполняться до тех пор, пока условие истинно. Давайте посмотрим на примере, как это работает. Предположим, мы хотим знать названия, авторов и количество книг, которые поступили в различные поставки. Интересующая нас информация хранится в двух таблицах – Журнал Поставок (magazine_incoming) и Товар (products). Давайте напишим интересующий нас запрос:

SELECT magazine_incoming.id_incoming, products.name, products., magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product;

А что, если нам необходимо, чтобы результат выводился не в одной таблице, а по каждой поставке отдельно? Конечно, можно написать 3 разных запроса, добавив в каждый еще одно условие:

SELECT magazine_incoming.id_incoming, products.name, products., magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=1; SELECT magazine_incoming.id_incoming, products.name, products., magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=2; SELECT magazine_incoming.id_incoming, products.name, products., magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=3;

Но гораздо короче сделать это можно с помощью цикла WHILE:

DECLARE i INT DEFAULT 3; WHILE i>0 DO SELECT magazine_incoming.id_incoming, products.name, products., magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=i; SET i=i-1; END WHILE;

Т.е. мы ввели переменную i, по умолчанию равную 3, сервер выполнит запрос с id поставки равным 3, затем уменьшит i на единицу (SET i=i-1), убедится, что новое значение переменной i положительно (i>0) и снова выполнит запрос, но уже с новым значением id поставки равным 2. Так будет происходить, пока переменная i не получит значение 0, условие станет ложным, и цикл закончит свою работу.

Чтобы убедиться в работоспособности цикла создадим хранимую процедуру books и поместим в нее цикл:

DELIMITER // CREATE PROCEDURE books () begin DECLARE i INT DEFAULT 3; WHILE i>0 DO SELECT magazine_incoming.id_incoming, products.name, products., magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=i; SET i=i-1; END WHILE; end //

Теперь вызовем процедуру:

CALL books ()//

Теперь у нас 3 отдельные таблицы (по каждой поставке). Согласитесь, что код с циклом гораздо короче трех отдельных запросов. Но в нашей процедуре есть одно неудобство, мы объявили количество выводимых таблиц значением по умолчанию (DEFAULT 3), и нам придется с каждой новой поставкой менять это значение, а значит код процедуры. Гораздо удобнее сделать это число входным параметром. Давайте перепишем нашу процедуру, добавив входной параметр num, и, учитывая, что он не должен быть равен 0:

CREATE PROCEDURE books (IN num INT) begin DECLARE i INT DEFAULT 0; IF (num>0) THEN WHILE i

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

CREATE PROCEDURE books (IN num INT) begin DECLARE i INT DEFAULT 0; IF (num>0) THEN wet : WHILE i 10) THEN LEAVE wet; ENF IF; SELECT magazine_incoming.id_incoming, products.name, products., magazine_incoming.quantity FROM magazine_incoming, products WHERE magazine_incoming.id_product=products.id_product AND magazine_incoming.id_incoming=i; SET i=i+1; END WHILE wet; ELSE SELECT ‘Задайте правильный параметр’; END IF; end //

Итак, мы снабдили наш цикл меткой wet вначале (wet:) и в конце, а также добавили еще одно условие – если входной параметр больше 10 (число 10 взято произвольно), то цикл с меткой wet следует закончить (IF (i>10) THEN LEAVE wet). Таким образом, если мы случайно вызовем процедуру с большим значением num, наш цикл прервется после 10 итераций (итерация – один проход цикла).

Циклы в MySQL, так же как и операторы ветвления, на практике в web-приложениях почти не используются. Поэтому для двух других видов циклов приведем лишь синтаксис и отличия. Вряд ли вам доведется их использовать, но знать об их существовании все-таки надо.

Оператор цикла REPEAT

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

REPEAT запрос UNTIL условие END REPEAT

Оператор цикла LOOP

Этот цикл вообще не имеет условий, поэтому обязательно должен иметь оператор LEAVE. Синтаксис следующий:

LOOP запрос END LOOP

На этом мы заканчиваем уроки посвященные SQL. Конечно, мы рассмотрели не все возможности этого языка запросов, но в реальной жизни вам вряд ли придется столкнуться даже с тем, что вы уже знаете.

Напомню, на реальных сайтах, вы обычно вводите информацию в какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java…) извлекает эти данные из формы и заносит их в БД. При необходимости происходит обратный процесс, т.е. данные извлекаются из БД и выводятся на страницы сайта. Оба процесса происходят посредством SQL-запросов. HTML вы знаете, с базами данных разобрались, SQL-запросы писать научились, осталось изучить PHP, чтобы ваши сайты превратились в полноправные web-приложения. Это и есть ваш следующий шаг. До встречи в уроках PHP.

Предыдущий урок Вернуться в раздел 

Видеоуроки php + mysql

• Видеокурс “PHP и MySQL с Нуля до Гуру 2.0”
• Как создаётся движок сайта на PHP и MySQL

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.

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

Источник

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

Наиболее «жизненный» пример – вывести 20 самых старых задач, числящихся на списке сотрудников (например, в рамках одного подразделения). Для различных управленческих «дашбордов» с краткими выжимками по участкам работы похожая тема требуется достаточно часто.

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

Возьмем тестовый набор данных из предыдущей статьи. Чтобы выводимые записи не «скакали» от раза к разу при совпадении сортируемых значений, расширим предметный индекс добавлением первичного ключа. Заодно это сразу придаст ему уникальность, и гарантирует нам однозначность порядка сортировки:

CREATE INDEX ON task(owner_id, task_, id); — а старый – удалим DROP INDEX task_owner_id_task__idx;

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

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

SELECT * FROM task WHERE owner_id = ANY(‘{1,2,4,8,16,32,64,128,256,512}’::integer[]) ORDER BY task_, id LIMIT 20;

[посмотреть на explain.tensor.ru]

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

unnest + ARRAY

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

Воспользуемся тем же механизмом извлечения и «разворота в столбцы» целостной записи таблицы, что и в прошлой статье. А также применим свертку в массив с помощью функции ARRAY():

WITH T AS ( SELECT unnest(ARRAY( SELECT t FROM task t WHERE owner_id = unnest ORDER BY task_, id LIMIT 20 — ограничиваем тут… )) r FROM unnest(‘{1,2,4,8,16,32,64,128,256,512}’::integer[]) ) SELECT (r).* FROM T ORDER BY (r).task_, (r).id LIMIT 20; — … и тут – тоже

[посмотреть на explain.tensor.ru]

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

Материализация записей таблиц через CTE

Обращу внимание, что в некоторых случаях попытка сразу работать с полями записи после ее поиска в подзапросе, без «оборачивания» в CTE может приводить к «умножению» InitPlan пропорционально количеству этих самых полей:

SELECT (( SELECT t FROM task t WHERE owner_id = 1 ORDER BY task_, id LIMIT 1 ).*);

Result (cost=4.77..4.78 rows=1 width=16) (actual =0.063..0.063 rows=1 loops=1) Buffers: d hit=16 InitPlan 1 (returns $0) -> Limit (cost=0.42..1.19 rows=1 width=48) (actual =0.031..0.032 rows=1 loops=1) Buffers: d hit=4 -> Index Scan using task_owner_id_task__id_idx on task t (cost=0.42..387.57 rows=500 width=48) (actual =0.030..0.030 rows=1 loops=1) Index Cond: (owner_id = 1) Buffers: d hit=4 InitPlan 2 (returns $1) -> Limit (cost=0.42..1.19 rows=1 width=48) (actual =0.008..0.009 rows=1 loops=1) Buffers: d hit=4 -> Index Scan using task_owner_id_task__id_idx on task t_1 (cost=0.42..387.57 rows=500 width=48) (actual =0.008..0.008 rows=1 loops=1) Index Cond: (owner_id = 1) Buffers: d hit=4 InitPlan 3 (returns $2) -> Limit (cost=0.42..1.19 rows=1 width=48) (actual =0.008..0.008 rows=1 loops=1) Buffers: d hit=4 -> Index Scan using task_owner_id_task__id_idx on task t_2 (cost=0.42..387.57 rows=500 width=48) (actual =0.008..0.008 rows=1 loops=1) Index Cond: (owner_id = 1) Buffers: d hit=4″ InitPlan 4 (returns $3) -> Limit (cost=0.42..1.19 rows=1 width=48) (actual =0.009..0.009 rows=1 loops=1) Buffers: d hit=4 -> Index Scan using task_owner_id_task__id_idx on task t_3 (cost=0.42..387.57 rows=500 width=48) (actual =0.009..0.009 rows=1 loops=1) Index Cond: (owner_id = 1) Buffers: d hit=4

Одна и та же запись «поискалась» 4 раза… Вплоть до PostgreSQL 11 такое поведение встречается регулярно, и решением является «оборачивание» в CTE, что является безусловной границей для оптимизатора в этих версиях.

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

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

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

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

Очевидно, что наш «целевой» список из 20 записей должен начинаться с «первых» записей по одному из наших owner_id-ключей. Поэтому сначала найдем такие «самые первые» по каждому из ключей и занесем в список, отсортировав его в порядке, который хотим – (task_, id).

Шаг 2: находим «следующие» записи

Теперь, если мы возьмем из нашего списка первую запись и начнем «шагать» дальше по индексу с сохранением owner_id-ключа, то все найденные записи – как раз следующие в результирующей выборке. Конечно, только пока мы не пересечем прикладной ключ второй записи в списке.

Если получилось, что мы вторую запись «пересекли», то последняя прочитанная запись должна быть добавлена в список вместо первой (с тем же owner_id), после чего список снова пересортировываем.

То есть у нас все время получается, что в списке есть не более одной записи по каждому из ключей (если записи кончились, а мы не «пересекли», то из списка первая запись просто пропадет и ничего не добавится), причем они всегда отсортированы в порядке возрастания прикладного ключа (task_, id).

Шаг 3: фильтруем и «разворачиваем» записи

В части строк нашей рекурсивной выборки некоторые записи rv дублируются – сначала мы находим такие как «пересекающую границу 2-й записи списка», а потом подставляем как 1-ю из списка. Так вот первое появление надо отфильтровать.

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

WITH RECURSIVE T AS ( — #1 : заносим в список “первые” записи по каждому из ключей набора WITH wrap AS ( — “материализуем” record’ы, чтобы обращение к полям не вызывало умножения InitPlan/SubPlan WITH T AS ( SELECT ( SELECT r FROM task r WHERE owner_id = unnest ORDER BY task_, id LIMIT 1 ) r FROM unnest(‘{1,2,4,8,16,32,64,128,256,512}’::integer[]) ) SELECT array_agg(r ORDER BY (r).task_, (r).id) list — сортируем список в нужном порядке FROM T ) SELECT list , list[1] rv , FALSE not_cross , 0 size FROM wrap UNION ALL — #2 : вычитываем записи 1-го по порядку ключа, пока не перешагнем через запись 2-го SELECT CASE — если ничего не найдено для ключа 1-й записи WHEN X._r IS NOT DISTINCT FROM NULL THEN T.list[2:] — убираем ее из списка — если мы НЕ пересекли прикладной ключ 2-й записи WHEN X.not_cross THEN T.list — просто протягиваем тот же список без модификаций — если в списке уже нет 2-й записи WHEN T.list[2] IS NULL THEN — просто возвращаем пустой список ‘{}’ — пересортировываем словарь, убирая 1-ю запись и добавляя последнюю из найденных ELSE ( SELECT coalesce(T.list[2] || array_agg(r ORDER BY (r).task_, (r).id), ‘{}’) FROM unnest(T.list[3:] || X._r) r ) END , X._r , X.not_cross , T.size + X.not_cross::integer FROM T , LATERAL( WITH wrap AS ( — “материализуем” record SELECT CASE — если все-таки “перешагнули” через 2-ю запись WHEN NOT T.not_cross — то нужная запись – первая из спписка THEN T.list[1] ELSE ( — если не пересекли, то ключ остался как в предыдущей записи – отталкиваемся от нее SELECT _r FROM task _r WHERE owner_id = (rv).owner_id AND (task_, id) > ((rv).task_, (rv).id) ORDER BY task_, id LIMIT 1 ) END _r ) SELECT _r , CASE — если 2-й записи уже нет в списке, но мы хоть что-то нашли WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN TRUE ELSE — ничего не нашли или “перешагнули” coalesce(((_r).task_, (_r).id) < ((list[2]).task_, (list[2]).id), FALSE) END not_cross FROM wrap ) X WHERE T.size < 20 AND — ограничиваем тут количество T.list IS DISTINCT FROM ‘{}’ — или пока список не кончился ) — #3 : “разворачиваем” записи – порядок гарантирован по построению SELECT (rv).* FROM T WHERE not_cross; — берем только “непересекающие” записи

[посмотреть на explain.tensor.ru]

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

В любом случае, время выполнения получилось лучше, чем в «наивном» первом варианте. Но каким из этих 3 вариантов пользоваться – выбирать вам.

Источник

Сегодня будем рассматривать очень много чего интересного, например как запустить уже созданную процедуру, которая принимает параметры, массово, т.е. не только со статическим параметрами, а с параметрами, которые будут меняться, например, на основе какой-нибудь таблицы, как обычная функция, и в этом нам помогут как раз курсоры и циклы, и как это все реализовать сейчас будем смотреть.

Как Вы поняли, курсоры и циклы мы будем рассматривать применимо к конкретной задачи. А какой задачи, сейчас расскажу.

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

EXEC test_PROCEDURE par1, par2

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

SELECT my_fun(id) FROM test_table

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

Примечание! Все примеры будем писать в СУБД MS SQL Server 2008, используя Management Studio. Также все нижеперечисленные действия требуют определённых знаний языка SQL, а точнее Transact-SQL. Начинающим могу посоветовать посмотреть мой видеокурс по T-SQL, на котором рассматриваются все базовые конструкции.

И так приступим, и перед тем как писать процедуру, давайте рассмотрим исходные данные нашего примера.

Допустим, есть таблица test_table

CREATE TABLE [dbo].[test_table]( [number] [numeric](18, 0) NULL, [pole1] [varchar](50) NULL, [pole2] [varchar](50) NULL ) ON [PRIMARY] GO

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

CREATE PROCEDURE [dbo].[my_proc_test] (@number numeric, @pole1 varchar(50), @pole2 varchar(50)) AS BEGIN INSERT INTO dbo.test_table (number, pole1, pole2) VALUES (@number, @pole1, @pole2) END GO

Она просто принимает три параметра и вставляет их в таблицу.

И допустим эту процедуру, нам нужно запустить столько раз, сколько строк в какой-нибудь таблице или представлении (VIEWS) , другими словами запустить ее массово для каждой строки источника.

И для примера создадим такой источник, у нас это будет простая таблица test_table_vrem, а у Вас это может быть, как я уже сказал свой источник, например временная таблица или представление:

CREATE TABLE [dbo].[test_table_vrem]( [number] [numeric](18, 0) NULL, [pole1] [varchar](50) NULL, [pole2] [varchar](50) NULL ) ON [PRIMARY] GO

Заполним ее тестовыми данными:

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

exec my_proc_test 1, ‘pole1_str1’, ‘pole2_str1’

И так еще три раза, с соответствующими параметрами.

Но нам так не охота, поэтому мы напишем еще одну дополнительную процедуру, которая и будет запускать нашу основную процедуру столько раз, сколько нам нужно.

Первый вариант.

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

Перейдем сразу к делу и напишем процедуру (my_proc_test_all), код я как всегда прокомментировал:

CREATE PROCEDURE [dbo].[my_proc_test_all] AS –объявляем переменные DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) –объявляем курсор DECLARE my_cur CURSOR FOR SELECT number, pole1, pole2 FROM test_table_vrem –открываем курсор OPEN my_cur –считываем данные первой строки в наши переменные FETCH NEXT FROM my_cur INTO @number, @pole1, @pole2 –если данные в курсоре есть, то заходим в цикл –и крутимся там до тех пор, пока не закончатся строки в курсоре WHILE @@FETCH_US = 0 BEGIN –на каждую итерацию цикла запускаем нашу основную процедуру с нужными параметрами exec dbo.my_proc_test @number, @pole1, @pole2 –считываем следующую строку курсора FETCH NEXT FROM my_cur INTO @number, @pole1, @pole2 END –закрываем курсор CLOSE my_cur DEALLOCATE my_cur GO

И теперь осталось нам ее вызвать и проверить результат:

Код:

–до выполнения процедуры SELECT * FROM test_table –вызов процедуры EXEC dbo.my_proc_test_all –после выполнения процедуры SELECT * FROM test_table

Как видите, все у нас отработало как надо, другими словами процедура my_proc_test сработала все три раза, а мы всего лишь один раз запустили дополнительную процедуру.

Второй вариант.

Используем только цикл в процедуре

Сразу скажу, что здесь требуется нумерация строк во временной таблице, т.е. каждая строка должна быть пронумерована, например 1, 2, 3 таким полем у нас во временной таблице служит number.

Пишем процедуру my_proc_test_all_v2

CREATE PROCEDURE [dbo].[my_proc_test_all_v2] AS –объявляем переменные DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) DECLARE @cnt int DECLARE @i int –узнаем количество строк во временной таблице SELECT @cnt=count(*) FROM test_table_vrem –задаем начальное значение идентификатора SET @i=1 WHILE @cnt >= @i BEGIN –присваиваем значения нашим параметрам SELECT @number=number, @pole1= pole1, @pole2=pole2 FROM test_table_vrem WHERE number = @I –на каждую итерацию цикла запускаем нашу основную процедуру с нужными параметрами EXEC dbo.my_proc_test @number, @pole1, @pole2 –увеличиваем шаг set @i= @i+1 END GO

И проверяем результат, но для начала очистим нашу таблицу, так как мы же ее только что уже заполнили по средствам процедуры my_proc_test_all:

–очистим таблицу DELETE test_table –до выполнения процедуры SELECT * FROM test_table –вызов процедуры EXEC dbo.my_proc_test_all_v2 –после выполнения процедуры SELECT * FROM test_table

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

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Источник