Инструкция use базы данных не разрешено в процедура функция или триггер

I’ve got the following code I’ve put together which seems to work just fine. However, I need to put it within a stored procedure, but I get the following error:

a use statement is not allowed in a procedure, function or trigger

Anyone know of a work around?

CREATE TABLE #TableRowCounts1
(
      [TableName] VARCHAR(128), 
      [RowCount] INT
) 

CREATE TABLE #TableRowCounts2
(
      [TableName] VARCHAR(128), 
      [RowCount] INT
) 

use Database1
GO

INSERT INTO #TableRowCounts1
(
      [TableName], 
      [RowCount]
      )
EXEC sp_MSforeachtable 
      'SELECT ''?'' 
      [TableName], 
      COUNT(*) [RowCount] 
      FROM ?'

use Database2
GO

INSERT INTO #TableRowCounts2
(
      [TableName], 
      [RowCount]
      )
EXEC sp_MSforeachtable 
      'SELECT ''?'' 
      [TableName], 
      COUNT(*) [RowCount] 
      FROM ?'

Вопрос:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_copytable_TEST]
@SourceServer nvarchar(255),
@SourceDatabase nvarchar(255),
@SourceSchema nvarchar(255),
@DestinationSchema nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@SQL NVARCHAR(MAX),
@id uniqueidentifier = NEWID()

BEGIN
IF NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'TablesLog_TEST')
CREATE TABLE [dbo].[TablesLog_TEST]
(
[id] [uniqueidentifier] NOT NULL,
[SourceServer] [nvarchar](255) NULL,
[SourceDatabase] [nvarchar](255) NULL,
[SourceSchema] [nvarchar](255) NULL,
[source] [nvarchar](max) NULL,
[destination] [nvarchar](255) NULL,
[timestamp] [datetime] NULL,
[message] [nvarchar](max) NULL,
[type] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO dbo.TablesLog_TEST
SELECT
@id, @SourceServer, @SourceDatabase, @SourceSchema,
'GetAllTables', @DestinationSchema, CURRENT_TIMESTAMP,
'Start Copy', 1
BEGIN TRY
INSERT INTO dbo.TablesLog_TEST
SELECT
@id, @SourceServer, @SourceDatabase, @SourceSchema,
'GetAllTables', @DestinationSchema, CURRENT_TIMESTAMP,
'try 1', 1

-- Create destination schema if it doesn't exist
SELECT @SQL = N'IF NOT EXISTS (SELECT * FROM [' + @SourceDatabase + '].SYS.SCHEMAS WHERE NAME = [' + @DestinationSchema + '])
BEGIN
SELECT @SQL = N'USE ['+ @SourceDatabase +'];
EXEC(''CREATE SCHEMA '+ @DestinationSchema +' '')'
EXEC sp_executesql @SQL
END'
EXEC sp_executesql @SQL

END TRY
BEGIN CATCH
INSERT INTO dbo.TablesLog_TEST
SELECT @id, @SourceServer, @SourceDatabase, @SourceSchema,
'GetAllTables', @DestinationSchema, CURRENT_TIMESTAMP,
'ERROR: ' + ERROR_MESSAGE(), 0

END CATCH

--Return value:
SELECT 1;
END

Лучший ответ:

    SELECT @SQL = N'IF NOT EXISTS (SELECT * FROM [' + @SourceDatabase + '].SYS.SCHEMAS WHERE NAME = ''' + @DestinationSchema + ''')
        BEGIN
            DECLARE @SQL NVARCHAR(MAX);

            SELECT @SQL = N''USE ['+ @SourceDatabase +']; 
                            EXEC(''''CREATE SCHEMA '+ @DestinationSchema +' '''')''
            EXEC sp_executesql @SQL
        END'
    EXEC sp_executesql @SQL         

EXEC usp_copytable_TEST 'SourceServer', 'SourceDatabase', 'SourceSchema', 'DestinationSchema'

Ответ №1

USE [CORE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[usp_copytable_TEST]
@SourceServer nvarchar(255),
@SourceDatabase nvarchar(255),
@SourceSchema nvarchar(255),
@DestinationSchema nvarchar(255)
AS
BEGIN

SET NOCOUNT ON;
DECLARE
@SQL NVARCHAR(MAX),
@id uniqueidentifier = NEWID()

BEGIN
If NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo'  AND  TABLE_NAME = 'TablesLog_TEST')
CREATE TABLE [dbo].[TablesLog_TEST](
[id] [uniqueidentifier] NOT NULL,
[SourceServer] [nvarchar](255) NULL,
[SourceDatabase] [nvarchar](255) NULL,
[SourceSchema] [nvarchar](255) NULL,
[source] [nvarchar](max) NULL,
[destination] [nvarchar](255) NULL,
[timestamp] [datetime] NULL,
[message] [nvarchar](max) NULL,
[type] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO dbo.TablesLog_TEST SELECT @id, @SourceServer,@SourceDatabase, @SourceSchema, 'GetAllTables', @DestinationSchema, CURRENT_TIMESTAMP, 'Start Copy', 1

BEGIN TRY
INSERT INTO dbo.TablesLog_TEST SELECT @id, @SourceServer,@SourceDatabase, @SourceSchema, 'GetAllTables', @DestinationSchema, CURRENT_TIMESTAMP, 'try 1', 1

-- Create destination schema if it doesn't exist
SELECT @SQL = N'IF NOT EXISTS (SELECT * FROM [' + @SourceDatabase + '].SYS.SCHEMAS WHERE NAME = [' + @DestinationSchema + '])
BEGIN
SELECT @SQL = N''USE ['+ @SourceDatabase +'];
EXEC(''CREATE SCHEMA '+ @DestinationSchema +' '')''
EXEC sp_executesql @SQL
END'
EXEC sp_executesql @SQL

END TRY
BEGIN CATCH
INSERT INTO dbo.TablesLog_TEST SELECT @id, @SourceServer,@SourceDatabase, @SourceSchema, 'GetAllTables', @DestinationSchema, CURRENT_TIMESTAMP, 'ERROR: ' + ERROR_MESSAGE(), 0

END CATCH
--Return value:
SELECT 1;
END
END

Ответ №2

SELECT @SQL = N'IF NOT EXISTS (SELECT * FROM [' + @SourceDatabase + '].SYS.SCHEMAS WHERE NAME = [' + @DestinationSchema + '])
BEGIN
SELECT @SQL = N''USE ['+ @SourceDatabase +'];
EXEC(''CREATE SCHEMA '+ @DestinationSchema +' '')''
EXEC sp_executesql @SQL
END'

Ответ №3

Спасибо @Sandip Patel за вашу помощь, спасибо Пол Bambury за вашу помощь Это работает !!! Проблема заключалась в отсутствии кавычек при создании моего динамического SQL. --Create Temporary table SELECT @SQL ='SELECT * INTO SANDBOX.dbo.TempTables FROM OPENQUERY(['+@SourceServer+'], '' SELECT * FROM ICTNONID.information_schema.tables where TABLE_SCHEMA = '''''+@SourceSchema+''''' '') WHERE TABLE_NAME NOT in (SELECT IGNORENAME FROM CORE.dbo.CopyTablesProdIgnore where IGNORETYPE = ''TABLE'' AND schemaname = '''+@SourceSchema+''') ' EXEC sp_executesql @SQL: --Create Temporary table SELECT @SQL ='SELECT * INTO SANDBOX.dbo.TempTables FROM OPENQUERY(['+@SourceServer+'], '' SELECT * FROM ICTNONID.information_schema.tables where TABLE_SCHEMA = '''''+@SourceSchema+''''' '') WHERE TABLE_NAME NOT in (SELECT IGNORENAME FROM CORE.dbo.CopyTablesProdIgnore where IGNORETYPE = ''TABLE'' AND schemaname = '''+@SourceSchema+''') ' EXEC sp_executesql @SQL

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

Данные импорта Mysql сообщают об отсутствии обработки разрешений SUPER Privilege, как показано ниже:

ERROR 1419 (HY000): You do not have the SUPER Privilege and Binary Logging is Enabled

Функция импорта и триггер в базу данных MySQL и сообщение об ошибке:

You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”.

причина:

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

Решение такое:

1) Пользователь, импортирующий данные, нуждается не только в разрешениях CREATE ROUTINE, ALTER ROUTINE, CREATE TRIGGER, ALTER TRIGGER, CREATE FUNCTION и ALTER FUNCTION, но и в правах SUPER для импорта данных с использованием суперпользователей.

2) Предоставление всем пользователям полномочий на выполнение аналогичных функций опасно и не рекомендуется.

o by specifying it on the server start, like: –log-bin-trust-function-creators=1
 o by setting it to 1 through the SET GLOBAL statement, like:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;

3) Если копировать не нужно, или если это из библиотеки, отключите binlog,

# binary logging – not required for slaves, but recommended
#log-bin=mysql-bin
# binary logging format – mixed recommended
#binlog_format=mixed

Источник статьи:http://www.jb51.net/article/54635.htm

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

Главная

Внимание! Данная работа построена на основе перевода раздела «17.1. Stored Routines and the Grant Tables» описания ПО MySQL 5.0.19, «Reference Manual. It documents MySQL 5.0 through 5.0.19. Document generated on: 2006-01-23 (revision:995)»
«Сначала прочти все, а потом пробуй примеры»

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

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

Хранимые программы (процедуры и функции) поддерживаются в MySQL 5.0. Хранимые процедуры – набор SQL-выражений, который может быть сохранен на сервере. Как только это сделано, клиенту уже не нужно повторно передавать запрос, а требуется просто вызвать хранимую программу.

Это может быть полезным тогда, когда:

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

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

Хранимые подпрограммы также позволяют вам использовать библиотеки функций, хранимые в БД сервера. Эта возможность представлена для многих современных языков программирования, которые позволяют вызывать их непосредственно (например, используя классы).

MySQL следует в синтаксисе за SQL:2003 для хранимых процедур, который уже используется в IBM’s DB2.

От слов к делу…

Хранимые процедуры требуют наличия таблицы proc в базе mysql. Эта таблица обычно создается во время установки сервера БД. Если вы апгрейдите СУБД более ранних версий, то вам следует почитать секцию 2.10.3, “Upgrading the Grant Tables”. Сейчас вместо нее читайте Upgrade MySQL System Tables.

При создании, модификации, удалении хранимых подпрограмм сервер манипулирует с таблицей mysql.proc

Начиная с MySQL 5.0.3 требуются следующие привилегии:

CREATE ROUTINE для создания хранимых процедур

ALTER ROUTINE необходимы для изменения или удаления процедур. Эта привилегия автоматически назначается создателю  процедуры (функции)

EXECUTE привилегия потребуется для выполнения подпрограммы. Тем не менее, автоматически назначается создателю процедуры (функции). Также, по умолчанию, SQL SECURITY параметр для подпрограммы DEFINER , который разрешает пользователям, имеющим доступ к БД вызывать подпрограммы, ассоциированные с этой БД.

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

Хранимая подпрограмма представляет собой процедуру или функцию. Хранимые подпрограммы создаются с помощью выражений CREATE PROCEDURE или CREATE FUNCTION. Хранимая подпрограмма вызывается, используя выражение CALL , причем только возвращающие значение переменные используются в качестве выходных. Функция может быть вызвана подобно любой другой функции и может возвращать скалярную величину. Хранимые подпрограммы могут вызывать другие хранимые подпрограммы.

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

• Когда подпрограмма вызывается, то подразумевается, что надо произвести вызов USE db_name (и отменить использование базы, когда подпрограмма завершилась, и база больше не потребуется)
• Вы можете квалифицировать обычные имена с именем базы данных. Это может быть использовано, чтобы ссылаться на подпрограмму, которая — не в текущей базе данных. Например, для выполнения хранимой процедуры p или функции f которые связаны с БД test, вы можете сказать интерпретатору команд так: CALL test.p() или test.f().
• Когда база данных удалена, все загруженные подпрограммы связанные с ней тоже удаляются. В MySQL 5.0.0, загруженные подпрограммы — глобальные и не связанны с базой данных. Они наследуют по умолчанию базу данных из вызывающего оператора. Если USE db_name выполнено в пределах подпрограммы, оригинальная текущая БД будет восстановлена после выхода из подпрограммы (Например текущая БД db_11, делаем вызов подпрограммы, использующей db_22, после выхода из подпрограммы остается текущей db_11 )

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

CREATE PROCEDURE – создать хранимую процедуру.

CREATE FUNCTION – создать хранимую функцию.

Синтаксис:

CREATE PROCEDURE имя_процедуры ([параметр_процедуры[,...]])
[характеристёика ...] тело_подпрограммы
CREATE FUNCTION имя_функции ([параметр_функции[,...]])
RETURNS тип
[характеристика ...] тело_подпрограммы
параметр_процедуры:
 [ IN | OUT | INOUT ] имя_параметра тип
параметр_функции:
 имя_параметра тип
тип:
 Любой тип данных MySQL
характеристика:
 LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string'
тело_подпрограммы:
 Правильное  SQL выражение.

Рассмотрим все на практике.

Сначала создадим хранимую процедуру следующим запросом:

CREATE PROCEDURE `my_proc`(OUT t INTEGER(11))
    NOT DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
select val1+val2 into 't' from `my` LIMIT 0,1;
END;

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

После этого вызовем ее:

CALL my_proc(@a);
SELECT @a;

Для отделения внутреннего запроса от внешнего всегда используют разделитель отличный от обычно (для задания используют команду DELIMITER <строка/символ>)

Вот еще один пример с учетом всех требований. 

mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
|  @a  |
+------+
|  3   |
+------+
1 row in set (0.00 sec)

Готово.

Весь процесс можно пронаблюдать на рисунке ниже:

Триггеры

Поддержка триггеров появилась в MySQL начиная с версии 5.0.2.

Триггер – поименованный объект БД, который ассоциирован с таблицей и активируемый при наступлении определенного события, события связанного с этой таблицей.

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

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

Объявим переменную sum и присвоим ей значение 1. После этого при каждой вставке в таблицу account значение этой переменной будет увеличивать согласно вставляемой части.

Замечание. Если значение переменной не инициализировано, то триггер работать не будет!

Синтаксис создания триггера

CREATE
[DEFINER = { имя_ пользователя | CURRENT_USER }]
TRIGGER имя_триггера время_триггера событие_срабатывания_триггера
ON имя_таблицы FOR EACH ROW выражение_выполняемое_при_срабатывании_триггера

Если с именем триггера и именем пользователя все понятно сразу, то о «времени триггера» и «событии» поговорим отдельно.

время_триггера

Определяет время свершения действия триггера. BEFORE означает, что триггер выполнится до завершения события срабатывания триггера, а AFTER означает, что после. Например, при вставке записей (см. пример выше) наш триггер срабатывал до фактической вставки записи и вычислял сумму. Такой вариант уместен при предварительном вычислении каких-то дополнительных полей в таблице или параллельной вставке в другую таблицу.

событие_срабатывания_триггера

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

• INSERT: т.е. при операциях вставки или аналогичных ей выражениях (INSERT, LOAD DATA, и REPLACE)
• UPDATE: когда сущность (строка) модифицирована
• DELETE: когда запись удаляется (запросы, содержащие выражения DELETE и/или REPLACE)

MySQL_dc.chm (1.7 MB) — подборка документации по MySQL.

Дополнительная литература

• Chapter 19. Stored Programs and Views (англ.)
• Полный перевод руководства (рус.)

Автор в Google+

Реклама:

Метки:
MySQL, stored, routines, triggers, procedure, хранимые процедуры.

Комментарии:

имя:

e-mail (не публикуется):

комментарий: