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

Данная статья посвящена хранимым процедурам и функциям в MySQL.Она основана на различных источниках сети интернет.

• -серия статей на mysqltutorial.org
• -официальная документация mysql
• и др. источники

Содержание статьи

1. Что такое параметры процедур?
2. Простейший пример с хранимой процедурой без параметров
3. Пример  хранимой процедуры с входными IN параметрами
4. Пример  хранимой процедуры с выходными OUT параметрами
5. Пример  хранимой процедуры с входными / выходными INOUT параметрами
6. Пример процедуры со множественными INOUT параметрами

Вообще говоря информации по MySQL достаточно много. Здесь я постараюсь на простых примерах разобраться как пользоваться “хранимками” в MySQL. Почти все примеры составлены самостоятельно, протестированы в консоли MySQL и клиенте Delphi. В посте про использование хранимых процедур в Delphi FireDAC мы видели следующее

Что такое параметры процедур?

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

– IN параметры. Входящие. Это то, что мы отправляем вместе с SQL запросом. Эти параметры уходят в процедуру, процедура их обрабатывает при исполнении.

–OUT параметры. Исходящие. Это что-то вроде результата. Процедура отработала и через OUT параметры мы можем получить свой результат.

–INOUT параметры. Входящие и Исходящие одновременно. Их можно использовать как IN или как OUT или одновременно, например, при создании счетчика.

Простейший пример с хранимой процедурой без параметров

Разберем несколько примеров. Начнем с простого, создадим на сервере простейшую хранимую процедуру.

DROP PROCEDURE IF EXISTS SELECT_ALL;

DELIMITER //

CREATE PROCEDURE SELECT_ALL()

BEGIN

SELECT*FROM FIRSTTABLE;

END//

DELIMITER ;

DROP PROCEDURE IF EXISTS SELECT_ALL; DELIMITER // CREATE PROCEDURE SELECT_ALL() BEGIN SELECT*FROM FIRSTTABLE; END// DELIMITER ;

1. Добавим её на сервер через консоль
2. 
3. Теперь проверим есть ли она там на самом деле через следующую инструкцию

select name from mysql.proc where db='array_db';

select name from mysql.proc where db='array_db';

• Да, действительно процедура добавлена и мы видим следующее
• 
• Теперь далее, попробуем вызвать нашу процедуру при помощи следующего кода

call select_all();

Всё Ок, на сервере процедура работает. Теперь попробуем её вызвать в клиенте Delphi таким образом.

1. Построим такого клиента (новое VCL приложение)
2. 
3. В свойстве FDStoredProc1.StoredProcName укажем следующее значение

FDStoredProc1.StoredProcName:='array_db.SELECT_ALL'

FDStoredProc1.StoredProcName:='array_db.SELECT_ALL'

На кнопке сделаем такой код

procedure TForm1.Call_ProcedureClick(Sender: TObject);
begin
FDStoredProc1.StoredProcName:='array_db.SELECT_ALL';
FDStoredProc1.Open();
//FDStoredProc1.Active:=true; // Также сработает
end;

procedure TForm1.Call_ProcedureClick(Sender: TObject);beginFDStoredProc1.StoredProcName:='array_db.SELECT_ALL'; FDStoredProc1.Open(); //FDStoredProc1.Active:=true; // Также сработает end;

Результат

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

Теперь создадим на сервере простейшую хранимую процедуру с параметрами.

delimiter //

create procedure MyProcWithPar(p1 varchar(10),p2 varchar(10))
begin
insert into firsttable (field1,field2,field3,field4,field5)
values (p1,p1,p2,p2,p2);

end//

delimiter ;

delimiter // create procedure MyProcWithPar(p1 varchar(10),p2 varchar(10)) begin insert into firsttable (field1,field2,field3,field4,field5) values (p1,p1,p2,p2,p2); end// delimiter ;

Здесь мы не указали параметры типы параметров, они по умолчанию IN, то есть можно было бы написать

…
create procedure MyProcWithPar(IN p1 varchar(10),IN p2 varchar(10))
…

…create procedure MyProcWithPar(IN p1 varchar(10),IN p2 varchar(10))…

• Добавим и проверим её…
• 
• Вроде все на месте, теперь пробуем в клиенте Delphi с помощью FireDAC
• Немного преобразим форму, добавив пару компонент и кнопок…
• 

После того как в объектном инспекторе мы выбираем свойство FDStoredProc2.StoredProcName  у нас автоматически заполняются параметры процедуры в свойстве FDStoredProc2.Params

procedure TForm1.CallProcedure2Click(Sender: TObject);
begin
FDStoredProc2.StoredProcName:='array_db.MyProcWithPar';
FDStoredProc2.ExecProc('MyProcWithPar',['Hello','Wow']);

DataSource1.DataSet:=FDQuery1;
FDQuery1.Open('select*from firsttable');

end;

procedure TForm1.CallProcedure2Click(Sender: TObject);beginFDStoredProc2.StoredProcName:='array_db.MyProcWithPar'; FDStoredProc2.ExecProc('MyProcWithPar',['Hello','Wow']); DataSource1.DataSet:=FDQuery1; FDQuery1.Open('select*from firsttable'); end;

Результат

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

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

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$
CREATE PROCEDURE my_sqrt(IN input_number INT, OUT out_number FLOAT)
BEGIN
SET out_number=SQRT(input_number);
END$$

DELIMITER ;

DELIMITER $$ DROP PROCEDURE IF EXISTS my_sqrt$$ CREATE PROCEDURE my_sqrt(IN input_number INT, OUT out_number FLOAT) BEGIN     SET out_number=SQRT(input_number); END$$ DELIMITER ;

1. Тестируем в консоли…
2. 
3. Тестируем в Delphi, добавим пару компонентов на форму
4. 
5. Теперь код на кнопке call_sqrt

procedure TForm1.call_sqrtClick(Sender: TObject);
begin
FDStoredProc3.ExecProc('array_db.my_sqrt',[15]); //

Источник: http://digital-flame.ru/2016/01/27/mysql-hranimyie-protseduryi-i-funktsii/

Использование курсоров и циклов в Transact-SQL | Info-Comp.ru — IT-блог для начинающих

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

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

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

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

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

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

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

1. exec my_proc_test 1, ‘pole1_str1’, ‘pole2_str1’
2. И так еще три раза, с соответствующими параметрами.
3. Но нам так не охота, поэтому мы напишем еще одну дополнительную процедуру, которая и будет запускать нашу основную процедуру столько раз, сколько нам нужно.
4. Первый вариант.

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

Перейдем сразу к делу и напишем процедуру (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_STATUS = 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

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

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

Источник: https://info-comp.ru/obucheniest/352-cursors-loops-in-transact-sql.html

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

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

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

Курсор не может использоваться в MySQL сам по себе. Он является важным компонентом хранимых процедур. Я бы сравнил курсор с «указателем» в C / C + + или итератором в PHP-операторе foreach.

Такая операция по обработке записи может быть также исполнена на PHP-уровне, что значительно уменьшает объем передаваемых на PHP-уровень данных, так как мы можем просто вернуть обработанный сводный / статистический результат обратно (тем самым устраняя процесс обработки select – foreach на стороне клиента).

Поскольку курсор реализуется в хранимой процедуре, он имеет все преимущества (и недостатки), присущие ХП (контроль доступа, пре-компиляция, трудность отладки и т.д.)

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

• На моем персональном сайте есть страница с результатами игр моей любимой команды НБА: Лейкерс.
• Структура таблицы этой страницы довольно проста:
• 
  Рис 1. Структура таблицы результатов игр Лейкерс
• Я заполняю эту таблицу с 2008 года. Некоторые из последних записей с результатами игр Лейкерс в сезоне 2013-14 приведены ниже:

Рис. 2. Данные таблицы результатов игр Лейкерс (частичные) в сезоне 2013-2014

(Я использую MySQL Workbench в качестве GUI-инструмента для управления базой данных MySQL. Вы можете использовать другой инструмент по своему выбору).

Что ж, должен признать, что баскетболисты Лейкерс в последнее время играют не очень здорово. 6 поражений подряд по состоянию на 15 января. Я определил эти «6 поражений подряд», посчитав вручную, сколько матчей подряд, начиная с текущей даты (и вниз к более ранним играм) имеют в колонке winlose значение «L» (поражение).

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

Можем ли мы сделать то же самое с помощью одного оператора SQL? Я не являюсь экспертом SQL, потому не смог придумать, как достичь нужного результата («6 поражений подряд») через один оператор SQL. Мнения гуру будут для меня очень ценными — оставьте их в х ниже.

Можем ли мы сделать это через PHP? Да, конечно. Мы можем получить данные по играм (конкретно, столбец winlos) этого сезона и перебрать записи для вычисления длительности текущей серии побед / поражений подряд.

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

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

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

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

1. Так как же можно сделать это лучше?
2. Как вы могли догадаться из названия нынешней статьи, лучшей альтернативой (на мой взгляд) для решения этой проблемы является использование курсора в хранимой процедуре.
3. Давайте создадим в MySQL Workbench первую ХП:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1))
BEGIN
declare current_win char(1);
declare current_streak int;
declare current_status char (1);

declare cur cursor for select winlose from lakers where year=cur_year and winlose'' order by id desc;

set current_streak=0;

open cur;

fetch cur into current_win;
set current_streak = current_streak +1;

start_loop: loop
fetch cur into current_status;
if current_status current_win then
leave start_loop;
else
set current_streak=current_streak+1;
end if;

end loop;

close cur;

select current_streak into longeststreak;
select current_win into `status`;
END

В этой ХП у нас есть один входящий параметр и два исходящих. Это определяет подпись ХП.

В теле ХП мы также объявили несколько локальных переменных для серии результатов (выигрышей или проигрышей, current_win), текущей серии и текущего статуса выигрыш /проигрыш конкретного матча:

declare cur cursor for select winlose from lakers where year=cur_year and winlose'' order by id desc;

Эта строка является объявлением курсора. Мы объявили курсор с именем cur и набор данных, связанных с этим курсором, который является статусом победа /поражение для тех матчей (значение столбца winlose может быть либо «W», либо «L», но не пустое) в конкретном году, которые упорядочены по идентификатору id (последние сыгранные игры будут иметь более высокий ID) в порядке убывания.

Для расчета количества побед / поражений подряд мы начинаем с последнего (и первого в приведенном наборе данных) матча. Когда курсор открыт, он всегда начинается с первой записи в соответствующем наборе данных.

После того, как первые данные загружены, курсор перемещается к следующей записи. Таким образом, поведение курсора похоже на очередь, перебирающую набор данных по системе FIFO (First In First Out). Это именно то, что нам нужно.

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

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

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

Далее мы можем повысить контроль доступа ХП, как это описано в моей предыдущей статье.

Чтобы проверить работу этой ХП, мы можем написать короткий PHP-скрипт:

Источник: https://www.internet-technologies.ru/articles/kursory-v-hranimyh-procedurah-mysql.html

Курсоры в Mysql

По долгу службы мне пришлось сталкиваться с курсорами. Хотелось бы рассказать, что это такое и о некоторых особенностях работы с ними. Официальная документация тут — dev.mysql.com/doc/refman/5.1/en/cursors.html Википедия даёт такое определение курсору курсор: Курсор — ссылка на контекстную область памяти.

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

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

Довольно теории давайте рассмотрим пример: У нас есть база данных (база немного не хорошая, это одна из моих лабораторных работ, но наш препод по базам данных настаивал на такой структуре )/*данные о банке */ CREATE TABLE `bank` (  `BankId` INTEGER(11) NOT NULL,  `BankName` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',  `Address` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',  `Phone` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',  PRIMARY KEY (`BankId`) )ENGINE=InnoDB

CHARACTER SET 'utf8' COLLATE 'utf8_bin';

/*данные о вкладах */

CREATE TABLE `bankdistribution` (

 `BankId` INTEGER(11) NOT NULL,  `Persent` INTEGER(11) DEFAULT NULL,  `ContributeAmount` DECIMAL(10,0) NOT NULL,  `ClientId` INTEGER(11) NOT NULL,  PRIMARY KEY (`BankId`, `ClientId`),  KEY `BankId` (`BankId`),  KEY `ClientId` (`ClientId`),  CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCES `bank` (`BankId`),  CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCES `client` (`ClientId`) )ENGINE=InnoDB /*данные о вкладчиках*/

CREATE TABLE `client` (

 `ClientId` INTEGER(3) NOT NULL AUTO_INCREMENT,  `CreditCardId` BIGINT(10) NOT NULL,  `Surname` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',  `Name` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',  `FirstName` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',  `Phone` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',  `Address` VARCHAR(50) COLLATE utf8_bin NOT NULL DEFAULT '',  `SafeId` INTEGER(5) NOT NULL,  PRIMARY KEY (`ClientId`, `CreditCardId`),  KEY `ClientId` (`ClientId`) )ENGINE=InnoDB

AUTO_INCREMENT=11 CHARACTER SET 'utf8' COLLATE 'utf8_bin'

* This source code was highlighted with Source Code Highlighter.

Допустим нам нужно получать по очереди каждый банк и производить с ним какие то действия, помочь в этом нам мог бы такой вот запрос Select `bank`.* FROM `bank` LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1
. Таким образом используя LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1 мы извлекаем в цикле из таблицы bank по очереди каждую запись и производим с ней нужные нам действия, при этом увеличивая значение НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ на 1. Теперь сделаем то же самое но используя курсор Begin /* переменные куда мы извлекаем данные */

Declare vBankId integer;

Declare vBankName VARCHAR(50); Declare vAddress VARCHAR(50); Declare vPhone VARCHAR(50); /* переменная hadler — a*/

/*Объявление курсора*/

Declare BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` where 1;

/*HANDLER назначение, которого поясним чуть ниже*/

• DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
• Open BankCursor;
• WHILE done = 0 DO

/* открытие курсора */ /*извлекаем данные */   FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;   делаем нужные нам действия

END WHILE;

/*закрытие курсора */

Close BankCursor;

END;

* This source code was highlighted with Source Code Highlighter.

Поясним теперь подробнее. Сначала HANDLER, он нужен для обработки исключения — что делать когда данные закончатся ( то есть курсор будет пустым ). Таким образом когда данные закончатся, не с генерируется сообщение об ошибке, а значение переменной done выставиться в 1, изначально done = 0; подробнее об SQLSTATE читаем тут — dev.mysql.com/doc/refman/5.1/en/error-messages-server.html;

Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA) Message: No data — zero rows fetched, selected, or processed SQLSTATE: 02000 срабатывает когда достигнут конец курсора, или когда select или update возвращяет пустую строку. Следующей строкой мы объявили курсор DECLARE cursor_name CURSOR FOR select_statement; Открываем курсор Open cursor_name; Дальше пока не достигаем конец курсора (WHILE done = 0 DO ) извлекаем данные и обрабатываем их. Перед выходом из хранимой процедуры необходимо курсор закрыть. Close cursor_name; Вроде ничего сложного. Но с SQLSTATE '02000' связанно много подводных камней. WHILE done = 0 DO   FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;   /* извлечем для банка сумму любого из его вкладов */

  Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;

  делаем какие то действия

END WHILE;

* This source code was highlighted with Source Code Highlighter.

Всё хорошо, и правильно с точки зрения синтаксиса. Но с логической точки зрения нет. Может случиться так что вкладчики не открыли счетов в каком то банке, тогда для Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1; сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом WHILE done = 0 DO   FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;   /* извлечем для банка сумму любого из его вкладов */

1.   Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
2.   if (vContributeAmountSUM > 0) then
3.     Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;

  /* проверим действительно ли есть вклады в этом банке */     /* извлечем для банка сумму любого из его вкладов */   end if;   делаем какие то действия

END WHILE;

* This source code was highlighted with Source Code Highlighter.

первым запросом мы проверили а есть ли вклады (если их нет то vContributeAmountSUM == 0 ) и только если таковые имеются мы извлекаем данные. level up теперь допустим нам нужно излечь общую сумму на счетах в разных банках у каждого клиента

Declare ClientSummCursor Cursor for Select sum

Declare ClientSummCursor Cursor for Select sum(`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) where 1 group by `bankdistribution`.`ClientId`;

Open ClientSummCursor;

WHILE done = 0 DO   FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;   /* извлечем для банка сумму любого из его вкладов */

•   Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
•   if (vContributeAmountSUM > 0) then
•     Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;

  /* проверим действительно ли есть вклады в этом банке */     /* извлечем для банка сумму любого из его вкладов */   end if;   /* извлекаем нужные нам данные */

  FETCH ClientSummCursor INTO vSum,vClientId;

  делаем какие то действия .

END WHILE;

* This source code was highlighted with Source Code Highlighter.

может возникнуть та же ситуация, когда данные в курсоре ClientSummCursor, закончатся раньше чем данные в BankCursor, сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом Open ClientSummCursor; WHILE done = 0 DO   FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;   /* извлечем для банка сумму любого из его вкладов */

1.   Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
2.   if (vContributeAmountSUM > 0) then
3.     Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;

  /* проверим действительно ли есть вклады в этом банке */     /* извлечем для банка сумму любого из его вкладов */   end if;   /* до извлечения данных из второго курсора запомним состояние sqlstate */   SET old_status = done;   /* извлекаем нужные нам данные */

  FETCH ClientSummCursor INTO vSum,vClientId;

  /* проверяем были ли извлечены данные , не стработал ли sqlstate 0200 */   if (done = 0 ) then     делаем какие то действия .

  end if;

  /* перед окончанием while восттановим значение переменной done */   set done = old_status; END WHILE;

* This source code was highlighted with Source Code Highlighter.

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

Источник: https://habr.com/post/46333/

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

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

Введение

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

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

За

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

Против

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

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

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

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

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

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

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

В этом уроке я использую строку “//” в качестве ограничителя.

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

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

DELIMITER //

CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
SELECT 'Hello World !';
END//

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

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

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

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

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

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

CALL stored_procedure_name (param1, param2, ….)

CALL procedure1(10 , 'string parameter' , @parameter_var);

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

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

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

DROP PROCEDURE IF EXISTS p2;

Это простая команда. Выражение 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

DELIMITER //

CREATE PROCEDURE `proc_IN` (IN var1 INT)
BEGIN
SELECT var1 + 2 AS result;
END//

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

DELIMITER //

CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN
SET var1 = 'This is a test';
END //

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

DELIMITER //

CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
BEGIN
SET var1 = var1 * 2;
END //

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

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

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

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

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

DECLARE a, b INT DEFAULT 5;

DECLARE str VARCHAR(50);

DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;

DECLARE v1, v2, v3 TINYINT;

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

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

DELIMITER //

CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
BEGIN
DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT;

INSERT INTO table1 VALUES (a);
SET str = 'I am a string';
SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5;
END //

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

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

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

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

DELIMITER //

CREATE PROCEDURE `proc_IF` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;

IF variable1 = 0 THEN
SELECT variable1;
END IF;

IF param1 = 0 THEN
SELECT 'Parameter value = 0';
ELSE
SELECT 'Parameter value 0';
END IF;
END //

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

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

DELIMITER //

CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;

CASE variable1
WHEN 0 THEN
INSERT INTO table1 VALUES (param1);
WHEN 1 THEN
INSERT INTO table1 VALUES (variable1);
ELSE
INSERT INTO table1 VALUES (99);
END CASE;

END //

или:

DELIMITER //

CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;

CASE
WHEN variable1 = 0 THEN
INSERT INTO table1 VALUES (param1);
WHEN variable1 = 1 THEN
INSERT INTO table1 VALUES (variable1);
ELSE
INSERT INTO table1 VALUES (99);
END CASE;

END //

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

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

DELIMITER //

CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
BEGIN
DECLARE variable1, variable2 INT;
SET variable1 = 0;

WHILE variable1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

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

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

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

DECLARE cursor-name CURSOR FOR SELECT …; /*Объявление курсора и его заполнение */
DECLARE CONTINUE HANDLER FOR NOT FOUND /*Что делать, когда больше нет записей*/
OPEN cursor-name; /*Открыть курсор*/
FETCH cursor-name INTO variable [, variable]; /*Назначить значение переменной, равной текущему значению столбца*/
CLOSE cursor-name; /*Закрыть курсор*/

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

DELIMITER //

CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
BEGIN
DECLARE a, b, c INT;
DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur1;

SET b = 0;
SET c = 0;

WHILE b = 0 DO
FETCH cur1 INTO a;
IF b = 0 THEN
SET c = c + a;
END IF;
END WHILE;

CLOSE cur1;
SET param1 = c;

END //

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

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

Заключение

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

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

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

Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.

Источник: https://ruseller.com/lessons.php?id=1189

Курсоры в MySQL. Применение и синтаксис. Примеры

Разработка сайтов и программного обеспечения, системное администрирование, обучение программированию и работе с СУБД MySQL

Главная → Статьи → Курсоры в MySQL

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

Курсоры должны быть объявлены до их использования. Переменные с условиями объявляются прежде курсоров. Обработчики объявляются строго после объявления курсоров.

Например: 

CREATE PROCEDURE curdemo()BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b,c INT; DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;

OPEN cur2;

REPEAT

FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF NOT done THEN IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF; END IF;UNTIL done END REPEAT;

CLOSE cur1;

CLOSE cur2;END

Объявление курсоров

DECLARE cursor_name CURSOR FOR select_statement

Это выражение объявляет курсор c именем cursor_name. select_statement указывает на конструкцию типа SELECT … FROM … Можно объявить много курсоров в подпрограмме, но каждый курсор в данном блоке должен иметь уникальное имя. Выражение SELECT не должно содержать указание INTO.

Открывание курсоров

OPEN cursor_name

Выражение открывает ранее объявленный курсор

Выборка из курсора в переменную

FETCH cursor_name INTO var_name [, var_name] …

Это выражение выбирает следующую строку (если строка существует), используя указанный открытый курсор, и продвигает указатель курсора.

Если более строк не доступно, происходит изменение значения переменной SQLSTATE в 02000. Для отлова этого события вы должны установить обработчик: HANDLER FOR SQLSTATE '02000'

Закрытие курсора

CLOSE cursor_name

Закрывает курсор cursor_name. Если явно не указано, то курсор закрывается автоматически при закрытии соответствующего блока подпрограммы.

Как использовать курсоры

Применение курсора в процедурах осуществляется путем последовательного выполнения следующих шагов:

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

Примеры курсоров

Приведу еще один пример курсора. Курсор предназначен для выборки данных (идентификаторов записей) в строку с разделителем ввиде запятой по переданным параметрам. Курсор находится внутри хранимой функции get_pedplan(). В нее передается три параметра: lip-номер лаборатории, ti-номер пары и dt — дата проведения занятия.

01: CREATE DEFINER = 'for_spammers'@'zoonman.ru' FUNCTION `get_pedplan`(lip INTEGER(11), ti INTEGER(11), dt DATE)02:  RETURNS char(64) CHARSET latin103:  DETERMINISTIC04:  CONTAINS SQL05:  SQL SECURITY INVOKER06:  COMMENT 'Функция возвращает список id из таблицы raspisanie'07: BEGIN08: 09:  DECLARE done INT DEFAULT 0;10:  DECLARE a INT;11:  DECLARE retv CHAR(64);12:  DECLARE flg INT;13: 14:  DECLARE cur1 CURSOR FOR SELECT id FROM raspisanie WHERE timeintv=ti AND rdate=dt AND labip=lip ;15:  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;16:  OPEN cur1;17:  SET retv:='';18:  SET flg:=0;19:  REPEAT21:  FETCH cur1 INTO a;22:  IF NOT done THEN 23:  IF flg!=0 THEN24:  SET retv:= CONCAT(retv,',' , a);25:  ELSE26:  SET retv:=a;27:  SET flg:=1; 28:  END IF;29:  END IF; 30:  UNTIL done END REPEAT;31:  CLOSE cur1;32:  RETURN retv;33: END;

Курсор определен на строке 14. Открыт на 16 строчке. С 19-й начат проход по выборке полученной курсором. На каждом шаге цикла происходит считывание записи (21-я).

Если запись первая, то присваиваем retv текущее значение выборки (26-я) и устанавливаем флаг (27-я), иначе объединяем значение retv с текущим значением выборки (строка 24). После прохода по курсору закрываем его (31-я) и возвращаем значение (32-я строка).

• Советую прочитать что такое хранимые процедуры и функции.

Источник: http://www.zoonman.ru/library/mysql_cursors.htm