Курсоры в хранимых процедурах MySQL


Оглавление (нажмите, чтобы открыть):

FPublisher

Web-технологии: База знаний

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

Долго мучался с этим вопросом. Литературы в интернете мало, особенно на русском языке. Пришлось поспрашивать на зарубежных форумах, глубже покопаться в мануалах и разъяснить для себя некоторые непонятные моменты. Итак, коротко о хранимых процедурах в MySQL.

Stored procedures — что это?

Хранимые процедуры появились начиная с 5 версии MySQL. Они позволяют автоматизировать сложные процессы на уровне MySQL, нежели использовать для этого внешние скрипты. Это даёт нам наиболее высокую скорость выполнения, т.к. мы не гоняем большое количество запросов, а всего лишь один раз вызываем ту или иную процедуру (или функцию).

Что для этого нужно? Установите MySQL сервер версии 5 или выше (dev.mysql.com/downloads). Процедуры можно создавать как запросы, например через командную строку MySQL, но для удобства советую скачать MySQL GUI Tools (dev.mysql.com/downloads/gui-tools). Данный пакет включает в себя три программы — MySQL Administrator, MySQL Query Browser и MySQL Migration Toolkit. Нам понадобятся первые две. (Хотя можно обойтись одним MySQL Query Browser, но все эти $$ в хранимых процедурах иногда могут сбить с толку).

Первая хранимая процедура

Итак, открываем MySQL Administrator, подключаемся к серверу MySQL и создаем новую схему (базу данных): щелкните Catalogs, выберите Create New Schema в области Schemata (Ctrl+N). Назовите ее как-нибудь (например db). Откройте только что созданную схему, выберите вкладку Stored procedures и щелкните кнопку Create Stored Proc. Назовите свою процедуру procedure1. В тело процедуры (между BEGIN и END) впишите следующее:

И нажмите Execute SQL — процедура создана. Откройте MySQL Query Browser, выберите свою схему (db) и впишите следующий запрос:

Переменные в MySQL

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

Простые переменные

Системные переменные

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

Параметры в хранимых процедурах

Здесь тоже всё достаточно просто. Изменяем первую строку, объявляющая саму процедуру:

Здесь, ключевое слово IN указывает на то, что параметр указан только для чтения. Далее с этим параметром работаем как с обычной переменной внутри процедуры:

Условия, Циклы. IF THEN ELSE, WHILE

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

Простой пример

Один из хороших случаев применения хранимых процедур — тогда, когда вам нужно объединить несколько запросов в один, например добавление темы в форум и увеличение общего количества тем. Допустим таблица threads

Здесь title у нас будет заголовком новой темы. Ну и таблица, например с различными статистическими переменными сайта, в том числе общее количество тем в форме.

Тут вроде всё понятно, допустим у нас там есть запись с name = threads и value = 0. Создадим новую хранимую процедуру procedure2.

Объяснять особо нечего, просто два запроса объединили в один. Теперь мы можем вызвать эту процедуру таким образом:

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

Курсоры (MySQL Cursors)

Курсоры позволяют пройтись по всем полученным результатам запроса. На теории объяснить сложно, покажу на практике. Добавим еще одну таблицу к нашей базе данных — hits:

Сюда мы будем записывать все тэги из всех тем. Хранимая процедура будет выглядеть примерно так:

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

Курсор для запроса SELECT, который выберет теги из всех тем (WHERE 1). После курсора объявляем что-то вроде исключения — что делать, когда результаты кончатся (SQLSTATE ‘02000′ означает это окончание). В этом случае мы в переменную done запишем 1, чтобы в последствии выйти из цикла.

Открываем курсор, и получаем первую запись. Дальше в цикле — Выбираем количество совпадений из таблицы тегов для текущего тега и помещаем результат в переменную iCount. Если результатов нет, то запросом INSERT вставляем новый тег.

В конце концов закрываем курсор и выходим из процедуры. Ну вот и всё.

Извлечение данных

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

Объявляем две переменных — iTags — количество тегов, и iThreads — общее количество тем.

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

Курсоры в хранимых процедурах MySQL

6.3. Использование курсоров в хранимых процедурах

Одним из способов возврата результатов работы хранимых процедур является формирование результирующего множества. Данное множество формируется при выполнении оператора SELECT . Оно записывается во временную таблицу — курсор. Применение курсора в процедурах осуществляется путем последовательного выполнения следующих шагов:

  • При помощи оператора DECLARE объявляется курсор для отдельного оператора SELECT или для отдельной процедуры.
  • Оператором OPEN производится открытие курсора.
  • Используя оператор FETCH , осуществляется установление указателя на требуемую запись курсора. При этом значения полей текущей записи присваиваются переменным, указываемым в операторе FETCH .
  • В процессе перемещения указателя текущей записи курсора при выходе указателя за пределы курсора выдается предупреждение row not found.
  • После того как курсор становится ненужным, он закрывается оператором CLOSE .

По умолчанию курсор закрывается автоматически в конце транзакции (операторы COMMIT или ROLLBACK ). Если при объявлении курсора указана фраза WITH HOLD (с сохранением), то курсор закрывается только явным образом оператором CLOSE . Такое использование курсора снимает все проблемы с остановкой функционирования процедур в среде утилиты ISQL, формирующих результирующие множества. В этом случае отпадает необходимость использования оператора RESUME .

В курсоре указатель может быть установлен:

  • до первой записи курсора;
  • на запись в пределах курсора;
  • за последнюю запись курсора.
  • Позиционирование курсора может осуществляться на:
  • первую/последнюю запись курсора;
  • на следующую/предыдущую запись;
  • на несколько записей вперед/назад относительно текущей записи;
  • на i-ю запись относительно начала курсора.
Мастер Йода рекомендует:  Злоумышленники сумели обойти проверку Google Play Protect

В соответствие с приведенными шагами рассмотрим пример обработки результатов работы процедуры Get_list_absent. Для этого создадим в учебных целях процедуру Count_publishers, которая для читателя по фамилии «Петрова В.А.», имеющей читательскую карточку с № 80, определяет количество книг, изданных в издательстве «Советское радио». Текст этой процедуры приводится ниже:

Чтобы убедиться в правильной работе процедуры Count_publishers необходимо выполнить следующие SQL-операторы:

В результате вызова этой процедуры в переменную Rez записано значение один.

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

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

Хранимая процедура MySQL, обработка нескольких курсоров и результатов запроса

Как я могу использовать два курсора в одной и той же программе? Если я удалю второе объявление курсора и цикл выборки, все работает отлично. Подпрограмма используется для добавления друга в мой webapp. Он принимает идентификатор текущего пользователя и адрес электронной почты друга, которого мы хотим добавить в качестве друга, затем он проверяет, имеет ли адрес электронной почты соответствующий идентификатор пользователя, а если нет отношения друзей, он будет создавать его. Любое другое рутинное решение, кроме этого, было бы замечательным.

Я, наконец, написал другую функцию, которая делает то же самое:

Надеюсь, это лучшее решение, все равно отлично работает. Спасибо, что сказали мне не использовать курсоры, когда это не нужно.

Вот простой пример использования двух курсоров в одной и той же программе:

Я знаю, что вы нашли лучшее решение, но я считаю, что ответ на ваш первоначальный вопрос заключается в том, что вам нужно установить Done = 0; между двумя курсорами, иначе второй курсор будет извлекать только одну запись перед выходом из цикла из-за Done = 1 из предыдущего обработчика.

Вместо того, чтобы использовать курсоры для проверки наличия записей, вы можете использовать предложение EXISTS в предложении WHERE:


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

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

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

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

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

Задайте составное PK или уникальное ограничение для users_friends, тогда вам не нужно беспокоиться о проверке отношений, а затем попробовать что-то вроде этого.

Сохраненные процедуры и курсоры в MySql

У меня есть две таблицы: Employee and Department. Они выглядят так:

Department.total_sal — это сумма всех зарплат сотрудников, которые принадлежат к этому Департаменту. Мне нужна хранимая процедура, которая использует курсор для повторения каждого сотрудника и обновляет соответствующую зарплату отдела. Я никогда раньше не работал с курсорами или хранимыми процедурами, и я немного смущен тем, как перебирать одну таблицу, но обновлять другую. Любая помощь/совет приветствуются.

Еще один быстрый вопрос: мне нравится делать все мои работы sql в SQLFIDDLE, кто-нибудь знает, поддерживает ли он также хранимые процедуры/курсоры?

Вот моя первая попытка, я полагаю, было бы неплохо стереть Department.Total_sal в начале этого тоже?

3 ответа

2 PaulG [2014-04-29 15:45:00]

нет необходимости использовать курсоры для этого. Существует несколько способов достижения тех же результатов, включая объединения и подвыборы. В приведенном ниже коде я использовал sub select (потому что мне нужно вернуться к работе и быстро):

Переместите это в раздел «Схема сборки»:

В исполняемом разделе выполните следующее:

и вы заметите, что total_sal теперь 43000, а не исходное значение 63000, которое было вставлено.

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

Если вы настаиваете на использовании курсора, вы можете что-то сделать следующим образом:

Здесь вы довольно близки. Я думаю, вы хотите использовать временную переменную, чтобы удерживать сумму всех транзакций, пока не достигнете конца цикла. Когда вы доберетесь до конца, вы обновите зарплату Департамента с суммированной стоимостью. Я просто использовал sum_sal.

Оператору обновления требуется предложение where, в основном вы будете иметь курсор, полный значений, которые вы

Использование курсоров и циклов в Transact-SQL

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

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

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

EXEC test_PROCEDURE par1, par2

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

SELECT my_fun(id) FROM test_table

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

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

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

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

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

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

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

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

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

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

exec my_proc_test 1, ‘pole1_str1’, ‘pole2_str1’

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

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

Мастер Йода рекомендует:  PHP массивы – путеводитель для начинающих PHP

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

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

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

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

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

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

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

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

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

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

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

Вложенные курсоры в Mysql

У меня есть три таблицы.
Project (Id), атрибут (Id), project_attribute (Id, project_id, attribute_id) .

Я хочу создать записи в таблице project_attribute , используя все атрибуты из таблицы attribute для каждого проекта из таблицы project .


Для создания таких записей я использую следующую процедуру хранения.

Но эта процедура создает записи только для 1 проекта в таблице project_attribute, хотя в таблице Project содержится 50 проектов. Ожидаемое количество записей: count (projectId) * count (attributeId).

Введение в хранимые процедуры MySQL 5

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

Введение

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

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

  • Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
  • Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
  • Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
  • Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Против

  • Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая — на клиентской.
  • Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
  • Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
  • Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL — это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.

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

Шаг 1: Ставим ограничитель

Ограничитель — это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.

Шаг 2: Как работать с хранимыми процедурами

Создание хранимой процедуры

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

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

4 характеристики хранимой процедуры:

  • Language: в целях обеспечения переносимости, по умолчанию указан SQL.
  • Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию — NOT DETERMINISTIC.
  • SQL Security: во время вызова идет проверка прав пользователя. INVOKER — это пользователь, вызывающий хранимую процедуру. DEFINER — это “создатель” процедуры. Значение по умолчанию — DEFINER.
  • Comment: в целях документирования, значение по умолчанию — «»

Вызов хранимой процедуры

Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.

Изменение хранимой процедуры

В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.

Удаление хранимой процедуры

Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.

Шаг 3: Параметры

Давайте посмотрим, как можно передавать в хранимую процедуру параметры.

  • CREATE PROCEDURE proc1 (): пустой список параметров
  • CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию — IN (входящие).
  • CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один возвращаемый параметр.
  • CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.

Естественно, вы можете задавать несколько параметров разных типов.

Пример параметра IN

Пример параметра OUT

Пример параметра INOUT

Шаг 4: Переменные

Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.

Синтаксис объявления переменной выглядит так:

Давайте объявим несколько переменных:

Работа с переменными

Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:

Шаг 5: Структуры управления потоками

MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.

Конструкция IF

С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:


Конструкция CASE

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

Конструкция WHILE

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

Мастер Йода рекомендует:  Красивый хак — всё по этой теме для программистов

Шаг 6: Курсоры

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

MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.

В этом примере мы проведем кое-какие простые операции с использованием курсора:

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

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

Заключение

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

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

Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: www.net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/
Перевел: Станислав Протасевич
Урок создан: 7 Июля 2011
Просмотров: 202094
Правила перепечатки

5 последних уроков рубрики «Разное»

Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов

Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.

Разработка веб-сайтов с помощью онлайн платформы Wrike

Создание вебсайта — процесс трудоёмкий, требующий слаженного взаимодействия между заказчиком и исполнителем, а также между всеми членами коллектива, вовлечёнными в проект. И в этом очень хорошее подспорье окажет онлайн платформа Wrike.

20 ресурсов для прототипирования

Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.

Топ 10 бесплатных хостингов

Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.

Быстрая заметка: массовый UPDATE в MySQL

Ни для кого не секрет как в MySQL реализовать массовый INSERT, а вот с UPDATE-ом могут возникнуть сложности. Чтобы не прибегать к манипуляциям события ON_DUPLICATE можно воспользоваться специальной конструкцией CASE … WHEN … THEN.

MySQL Cursor

Summary: in this tutorial, you will learn how to use MySQL cursor in stored procedures to iterate through a result set returned by a SELECT statement.

Introduction to MySQL cursor

To handle a result set inside a stored procedure, you use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row individually.

MySQL cursor is read-only, non-scrollable and asensitive.

  • Read-only: you cannot update data in the underlying table through the cursor.
  • Non-scrollable: you can only fetch rows in the order determined by the SELECT statement. You cannot fetch rows in the reversed order. In addition, you cannot skip rows or jump to a specific row in the result set.
  • Asensitive: there are two kinds of cursors: asensitive cursor and insensitive cursor. An asensitive cursor points to the actual data, whereas an insensitive cursor uses a temporary copy of the data. An asensitive cursor performs faster than an insensitive cursor because it does not have to make a temporary copy of data. However, any change that made to the data from other connections will affect the data that is being used by an asensitive cursor, therefore, it is safer if you do not update the data that is being used by an asensitive cursor. MySQL cursor is asensitive.

Working with MySQL cursor

First, declare a cursor by using the DECLARE statement:

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

Written on 12 Января 2009 . Posted in MySQL

Первая хранимая процедура

Переменные в MySQL

Простые переменные

Системные переменные

Параметры в хранимых процедурах

Условия, Циклы. IF THEN ELSE, WHILE

Простой пример

Курсоры (MySQL Cursors)

Курсоры позволяют пройтись по всем полученным результатам запроса. На теории объяснить сложно, покажу на практике. Добавим еще одну таблицу к нашей базе данных — hits:
Сюда мы будем записывать все тэги из всех тем. Хранимая процедура будет выглядеть примерно так:
Подробно. Процедура пройдет через каждую тему, каждый тег пробьет по таблице tags, и если данный тег отсутствует, то она его добавит.

Курсор для запроса SELECT, который выберет теги из всех тем (WHERE 1). После курсора объявляем что-то вроде исключения — что делать, когда результаты кончатся (SQLSTATE ‘02000′ означает это окончание). В этом случае мы в переменную done запишем 1, чтобы в последствии выйти из цикла.

Открываем курсор, и получаем первую запись. Дальше в цикле — Выбираем количество совпадений из таблицы тегов для текущего тега и помещаем результат в переменную iCount. Если результатов нет, то запросом INSERT вставляем новый тег.

В конце концов закрываем курсор и выходим из процедуры. Ну вот и всё.

Извлечение данных

Вспомним системные переменные и рассмотрим еще одну манипуляцию над нашими таблицами — получить общее количество тегов и тем. Перейдем сразу к процедуре:
Объявляем две переменных — iTags — количество тегов, и iThreads — общее количество тем.

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

mysql — выполнение хранимой процедуры с курсором в переполнении стека

У меня есть хранимая процедура, которую я пытаюсь вызвать с моего php. Вот хранимая процедура:

Непосредственно в MySQL верстак, вызывая это работает. В php это не работает. Вот мой php:

При подключении вот так я получаю следующую ошибку

Я проследил свою проблему до проблемы с $data = $result->fetch_assoc() потому что, когда я комментирую это и помещаю в print_r Я получаю что-то на самом деле возвращается, что mysqli_result Object ( [current_field] => 0 [field_count] => 9 [lengths] => [num_rows] => 0 [type] => 1 ) , Я сделал вывод, что это не работает, потому что [num_rows] => 0 ,

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

Решение

На основе обсуждений в чате для 3 групп, и это обеспечено SQLF > для тестовых данных (не так много данных там).

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

Так что, в конечном итоге, исключите это и измените 4 ссылки в коде, который его использует (обратите внимание, что Группа 3 использует его дважды).

Запрос:

Вывод (мой клиентский инструмент на данный момент содержит текст):

Смотрите мои общие комментарии в верхней части этот мой ответ для расширенного использования переменных.

Добавить комментарий