SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 23.03.2007 02:58:52

Golova
Завсегдатай
Зарегистрирован: 23.03.2007
Сообщений: 92

хранимые процедуры: DECLARE или SET

зачем вообще нужно DECLARED если можно использовать SET, а можно даже и SET не пользовать, а создавать переменные в контексте выполнения запросов.
и еще насчет области видимости переменных, в доках пишут что видимость ограничена блоком BEGIN END; но и за пределами хранимой процедуры я читаю переменные, которые инициализировались(там же и создавались) внутри процедуры.

Неактивен

 

#2 23.03.2007 21:28:01

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6732

Re: хранимые процедуры: DECLARE или SET

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

Пользовательские переменные создаются в момент присвоения им
значения. Это значение хранится в переменной до завершения
соединения с сервером. Оно, разумеется, доступно и внутри хранимого
кода:

mysql> SET @var=123;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @var;
+------+
| @var |
+------+
| 123  |
+------+
1 row in set (0.00 sec)

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

mysql> \d ;;
mysql> CREATE PROCEDURE proc ()
    -> BEGIN
    ->   DECLARE pvar INT;
    ->   SET pvar = 2;
    ->   SELECT pvar;
    -> END;;
Query OK, 0 rows affected (0.15 sec)

mysql> \d ;
mysql> CALL proc ();
+------+
| pvar |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT pvar;
ERROR 1054 (42S22): Unknown column 'pvar' in 'field list'

Неактивен

 

#3 27.03.2007 00:01:15

Golova
Завсегдатай
Зарегистрирован: 23.03.2007
Сообщений: 92

Re: хранимые процедуры: DECLARE или SET

спасибо очень доходчиво описали про переменные, различия в них я уже и сам просек после 2-х дней изучения написания хранимых процедур.
Но проблемы все равнво остались:
Имеется надобность в выполнении запроса и записи текста запроса в какую то таблицу(лог изменений, вносимых хранимыми процедурами), т.е. как программист на любом другом языке, я стану дублировать текст запроса, а попытаюсь использовать строку, которую сначала выполню, а затем запишу ее в лог.
Теперь о том что у меня получилось и что меня не устраивает:
--------------------------------------------------------------------
SET @sQuery = "SELECT COUNT(*) FROM logs INTO @log_count";
PREPARE stmt FROM @sQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

INSERT INTO logs SET log_datetime=NOW(), msg=@sQuery;
--------------------------------------------------------------------
как видно из примера приходится пользоваться переменными пользователя: @sQuery, @log_count
что потенциально не очень хорошо, т.к. на время подключения пользователя они существуют в одной и тойже области видимости, а использовать переменные хранимого кода не получилось использовать т.к.
1. для sQuery мы не знаем длинну строки
2. при исполнении log_count не видна почему то, даже если ее выше обьявить.

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

Неактивен

 

#4 27.03.2007 01:39:36

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6732

Re: хранимые процедуры: DECLARE или SET

Да, к сожалению, в случае PREPARE Вам не удастся не использовать
глобальные переменные. И дело тут даже не столько в длине строки,
сколько в том, что PREPARE может работать только с глобальными переменными
(ну или с чистой строкой, разумеется).

Если Вам нужен лог запросов - включите general query log. Только учтите,
что на загруженных серверах это очень быстро приводит к огромным логам.

Если Вам нужен лог изменений - включите binary log. В случае 5.0, сервер пишет
запросы, изменяющие данные. В случае 5.1, Вам необходимо это в явном виде
указать (иначе он может писать реальные изменения, а не запросы).

Если Вам нужно записывать текст хранимой процедуры в лог-таблицу
(хотя, честно говоря, не могу себе представить, зачем это может быть нужно),
Вы можете сделать "обходной путь":

В начале процедуры Вы можете поставить выражение типа

INSERT INTO logdb.logtable (`query`)
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='dbname' AND ROUTINE_NAME='procname';

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

Неактивен

 

#5 27.03.2007 01:51:22

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6732

Re: хранимые процедуры: DECLARE или SET

Кстати, придумал еще способ, PREPARE-based:

Нужно организовать некоторый "стек" и не портить пользовательское
окружение. Например, так:

DECLARE myvar VARCHAR(1000)
-- вот тут единственная сложность... но в действительности число можно
-- поставить достаточно большое - ведь это пользовательская память.
-- разумно ставить эту длину такой же, как размер поля в БД лога (если,
-- конечно, там не LONGTEXT smile)

SET myvar = @query
SET @query = ' ... '
PREPARE...

-- после всего выполнения
SET @query = myvar

Неактивен

 

#6 27.03.2007 02:43:40

Golova
Завсегдатай
Зарегистрирован: 23.03.2007
Сообщений: 92

Re: хранимые процедуры: DECLARE или SET

paulus написал:

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

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

Отредактированно Golova (27.03.2007 02:44:10)

Неактивен

 

#7 27.03.2007 02:50:20

Golova
Завсегдатай
Зарегистрирован: 23.03.2007
Сообщений: 92

Re: хранимые процедуры: DECLARE или SET

paulus написал:

Кстати, придумал еще способ, PREPARE-based:
Нужно организовать некоторый "стек" и не портить пользовательское

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

Неактивен

 

#8 27.03.2007 11:05:44

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6732

Re: хранимые процедуры: DECLARE или SET

Да, их, по-видимому, прийдется создать несколько - в стеке.

Если Вы программировали когда-нибудь на ассемблере - Вы должны
помнить, как там организуется память при входе в процедуры. Обычно
при входе Вы делаете pusha, что проталкивает значение всех регистров
процессора в стек, а при выходе - popa, что возвращает значения.

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

Неактивен

 

#9 27.03.2007 11:15:11

Golova
Завсегдатай
Зарегистрирован: 23.03.2007
Сообщений: 92

Re: хранимые процедуры: DECLARE или SET

с механизмом push/pop при call в ассемблере я знаком конечно, и писал

"но есть еще куча переменных тоже пользовательских, в которые вынимаются данные в этих запросах, а вот их имена получается должны быть уникальными  если мы хотим полной безопасности(я говорю про такие пепеременные как @log_count"

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

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

Если найду когда-нибудь лучший способ - отпишусь.
Спасибо за дискуссию.

Отредактированно Golova (27.03.2007 11:21:35)

Неактивен

 

#10 27.03.2007 11:32:44

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6732

Re: хранимые процедуры: DECLARE или SET

Ну, если Вы их используете 5-6 штук, то не накладно (даже если они по 100кбайт каждая).
Если Вы используете их 100+, то Вам стоит написать собственное расширение для сервера
(которое будет записывать в файл то, что выполняют процедуры), т.к. при таком количестве
переменных, код будет выполняться действительно долго.

Смысл в том, что Вам нужно сохранять не все переменные, а только те, которые изменяет Ваша
процедура. Остальные, разумеется, останутся неизменными и на выходе из нее. А процедура
обычно изменяет 1-2...

Если будете садиться за код - советую сделать патч, чтобы PREPARE работал с локальными
переменными - это будет куда полезнее и Вам, и сообществу вцелом.

Неактивен

 

#11 27.03.2007 18:53:12

Golova
Завсегдатай
Зарегистрирован: 23.03.2007
Сообщений: 92

Re: хранимые процедуры: DECLARE или SET

ну насчет патча  вы загнули, во-первых мне до этого как до луны, а во-вторых начальство не одобрит какие либо изменения официального кода.
Вот еще возникли сомнения насчет переменных пользователя живущих во время сессии, допустим меня не волнуют значение переменной @query до и после вызова хранимой процедуры, т.к. я ее всегда инициализирую внутри её. Но если вспомнить о синхронизации потоков то встает вопрос, если клиент будет использовать одно и тоже соединение при вызове процедур, а в них я буду использовать @query, то не затру ли я во время выполнения эту переменную совершенно другим запросом ? Тоже относиться и ко всем другим @переменным, которые используются для получения значений в запросе.

Неактивен

 

#12 27.03.2007 19:05:52

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6732

Re: хранимые процедуры: DECLARE или SET

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

Ну а разные сессии будут иметь разные @query.

Неактивен

 

#13 27.03.2007 21:17:54

Golova
Завсегдатай
Зарегистрирован: 23.03.2007
Сообщений: 92

Re: хранимые процедуры: DECLARE или SET

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

Неактивен

 

#14 02.07.2012 18:46:43

Joy_25
Участник
Зарегистрирован: 02.07.2012
Сообщений: 2

Re: хранимые процедуры: DECLARE или SET

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

1. Мне присылают дамп бд сделанный обычным способом.
2. но в результате каких то перверсий - точно не известно - дамп получается не простой а с вложенными DECLARE
3. мне необходимо из этого дампа забрать четыре таблицы и перенаправить их в другую базу.
4. Я делаю дамп этих четырех таблиц обычным mysqldump -t basa tb1 tb2 tb3 tb4 > c:/basa4.sql
5. В этом дампе опять оказывается DECLARE
6. Когда пытаюсь положить этот дамп четырех таблиц в нужную мне БД то выдает:
ERROR 1304 (42000): FUNCTION join_period already exists
7. если пытаюсь вручную почистить созданный дамп от DECLARE, то заливает, но неизвестно всю ли информацию и как правильно.

Вопрос - как сделать дамп без DECLARE - возможно ли это ?
ключи дампа просмотрел - вроде невозможною
на моей машине mysql стоит 5.0, могу поставить 5.5 - если необходимо.
на конечный (mysql  Ver 14.12 Distrib 5.0.45,) и начальный источник повлиять не могу.

Просидел весь день в инете с этой проблемой, но как новичок в mysql и со слабым английским - слаборезультативно.
=
Буду рад хотя бы более полной инфе о DECLARE - можно ли ее безболезненно удалять вручную из дампа, к примеру.

Отредактированно Joy_25 (02.07.2012 18:48:22)

Неактивен

 

#15 02.07.2012 18:54:55

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3857

Re: хранимые процедуры: DECLARE или SET

Восстанавливайте дамп в чистую базу, а потом переносите нужные Вам таблицы в другую базу (можно новым дампом или CREATE TABLE db2.A like db1.S; INSERT INTO db2.S SELECT * FROM db1.A)

Если mysqldump запущен без ключа -R, то хранимые процедуры в дамп не попадают.

Неактивен

 

#16 03.07.2012 11:10:56

Joy_25
Участник
Зарегистрирован: 02.07.2012
Сообщений: 2

Re: хранимые процедуры: DECLARE или SET

Спасибо за совет - копирование потаблично помогло убрать DECLARE
==
распишу вашу запись чуть подробнее для других гостей вашего форума:

MySql скопировать таблицу из одной базы в другую

CREATE TABLE db2.table LIKE db1.table;
INSERT INTO db2.table SELECT * FROM db1.table;

Неактивен

 

#17 07.07.2012 21:41:06

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5730

Re: хранимые процедуры: DECLARE или SET

Joy_25 написал:

CREATE TABLE db2.table LIKE db1.table;

Учтите, что при этом будет потерян foreign key.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson