Цикл SQL WHILE IF BREAK. Ms sql цикл

Цикл SQL WHILE IF BREAK MS SQL Server

В хранимой процедуре SQL Server 2012 у меня есть несколько вложенных структур. Я хочу вырваться из одного слоя из них.

Я думал, что описание BREAK в msdn https://msdn.microsoft.com/en-CA/library/ms181271.aspx было на моей стороне. Но я получаю какое-то странное поведение во время его запуска на одном шаге с помощью отладки. Я говорю странно, потому что это непротиворечиво. Иногда он ускользает от слоя, который я ожидаю. Иногда он пропускает пару.

WHILE ... BEGIN stuff1 IF...BEGIN stuff2 WHILE ... BEGIN stuff3 IF .... BEGIN stuff4 IF @NumberRecords=0 BREAK stuff5 END --stuff6 if @NumberRecords=0 and @loopBOMRowCount=@ResultsSOloopstart-1 break --on the last occasion I observed, @loopBOMRowCount was 6 and @ResultsSOloopstart 71 and it never highlighted this section, either way SET @loopBOMRowCount = @loopBOMRowCount + 1 END stuff7 --nothing actually here END --stuff8 SET @periodloopcount=@periodloopcount+1 --this is where it ended up highlighting on that last occasion END stuff9

Так что если NumberRecords = 0, то следующий op должен быть if if на stuff6, правильно? Даже если stuff4 включает, скажем, таблицу INSERT INTO из вызова EXEC в хранимую процедуру? Ничто не должно смущать стек из его слоев?

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

РЕДАКТИРОВАТЬ

Мне удалось настроить его так, как я хотел, добавив фиктивный цикл WHILE вокруг внутреннего IF, который я хочу вырваться из первого. Но мне очень хотелось бы знать, как я неверно истолковываю информацию msdn. Кажется, что BREAK должен выйти из IF, если у него есть утверждение END.

Выходит из самого внутреннего цикла в инструкции WHILE или инструкции IF … ELSE в цикле WHILE. Выполняются любые утверждения, появляющиеся после ключевого слова END, обозначающего конец цикла.

Solutions Collecting From Web of "Цикл SQL WHILE IF BREAK"

Я согласен, что документация немного запутанна. Кажется, эта линия предлагает вам BREAK из IF.

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

Этот пример демонстрирует это.

Пример 1

DECLARE @X INT = 1; PRINT 'Start' /* WHILE loop required to use BREAK. */ WHILE @X = 1 BEGIN /* Outer IF. */ IF 1 = 1 BEGIN /* Inner IF. */ IF 2 = 2 BEGIN BREAK PRINT '2' END PRINT '1' END SET @X = @X + 1; END PRINT 'End'

Печатается только текст «Начало и конец». 1 не печатается, потому что BREAK существует WHILE.

Вы также можете увидеть это поведение здесь:

Пример 2.

/* Anti-Pattern. * Breaking outside a WHILE is not allowed. */ IF 1 = 1 BEGIN BREAK PRINT 1 END

Этот запрос возвращает ошибку:

Msg 135, Level 15, State 1, Line 4 Невозможно использовать оператор BREAK за пределами инструкции WHILE.

Если вы действительно хотели вырваться из инструкции IF, чтобы напечатать «Start, 1, End», как в приведенном выше примере, вы можете сделать следующее

DECLARE @X INT = 1; PRINT 'Start' /* WHILE loop required to use BREAK. */ WHILE @X = 1 BEGIN /* Outer IF. */ IF 1 = 1 BEGIN /* Inner IF. */ IF 2 = 2 BEGIN GOTO skip2 PRINT '2' END skip2: PRINT '1' END SET @X = @X + 1; END PRINT 'End'

Теперь, когда вы можете использовать это для обработки примера OP, используя следующие

WHILE ... BEGIN stuff1 IF...BEGIN stuff2 WHILE ... BEGIN stuff3 IF .... BEGIN stuff4 IF @NumberRecords=0 GOTO startstuff6 stuff5 END startstuff6: --stuff6 if @NumberRecords=0 and @loopBOMRowCount=@ResultsSOloopstart-1 GOTO startstuff7 --on the last occasion I observed, @loopBOMRowCount was 6 and @ResultsSOloopstart 71 and it never highlighted this section, either way SET @loopBOMRowCount = @loopBOMRowCount + 1 END startstuff7: stuff7 --nothing actually here END --stuff8 SET @periodloopcount=@periodloopcount+1 --this is where it ended up highlighting on that last occasion END stuff9

Обычно считается лучшим подходом к инверсии логической логики, например:

IF NOT @NumberRecords=0 BEGIN stuff5 END

sqlserver.bilee.com

Выполняем код для каждой строки в выборке Transact SQL с использованием курсора

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

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

-- обьявляем курсор declare some_cursor cursor --- sql запрос любой сложности, формирующий набор данных для курсора for select SOME_INT_FIELD, SOME_VARCHAR_FIELD from SOME_TABLE -- открываем курсор open some_cursor -- курсор создан, обьявляем переменные и обходим набор строк в цикле declare @counter int declare @int_var int, @string_var varchar(100) set @counter = 0 -- выборка первой строки fetch next from some_cursor INTO @int_var, @string_var -- цикл с логикой и выборкой всех последующих строк после первой while @@FETCH_STATUS = 0 begin --- логика внутри цикла set @counter = @counter + 1 if @counter >= 5 break -- возможный код для проверки работы, прерываем после пятой итерации -- отладочный select, на большом количестве строк выборка данных в sql server management studio может привести к ошибке переполнения памяти SELECT @int_var, @string_var INSERT INTO OTHER_TABLE (SOME_FIELD1, SOME_FIELD2) VALUES (@string_var, 'Мегастрока') DELETE FROM OTHER_TABLE2 WHERE ID_FIELD = @int_var exec some_stored_procedure -- выборка следующей строки fetch next from some_cursor INTO @int_var, @string_var -- завершение логики внутри цикла end select @counter as final_count -- закрываем курсор close some_cursor deallocate some_cursor

Курсоры (Transact-SQL)FETCH (Transact-SQL)@@FETCH_STATUS (Transact-SQL)

nullpro.info

Цикл в MSSQL

Вопрос: Гетерогенный сервис с MSSQL

Уважаемые коллеги прошу помощи!

Настраиваю тут вторую неделю (уже как) гетерогенный сервис к БД MSSQL... Были разные косяки и проблемы, но в основном все победил. Тестировал настроенный unixODBC через tsql и isql, connect проходит успешно, удаленная БД отдает данные. Далее настраиваю гетерогенный сервис, листнер, tnsnames. проверяю tnsping. Вроде бы все хорошо (хотя пингует сам себя). Создаю DB Link, успешно. А вот далее начинаются непонятные грабли. А именно при попытке сформировать SQL запрос в удаленную БД получаю такую ошибку:

ORA-28500: соединение ORACLE с посторонней системой выдало сообщение:[unixODBC][FreeTDS][SQL Server]Cannot open server "MSSQL" requested by the login. The login failed. {42000,NativeErr = 40532}[unixODBC][FreeTDS][SQL Server]Unable to connect to data source {08001}ORA-02063: предшествующий 2 lines из DBL28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"*Cause: The cause is explained in the forwarded message.*Action: See the non-Oracle system's documentation of the forwardedmessage.Error at Line: 1 Column: 23

Пароль проверял, все корректно, единственное в пароле есть символ "*" и поэтому весь пароль приходится брать в кавычки при создании DB Link...

P.S. DB Link создается с любым паролем, пробовал, проверка видимо происходит на уровне запроса.

Ответ: Вот заметки на полях для других -----------------------

Доступ к гетерогенным БД осуществляется через ODBC. Чтобы добавить сервис для создания DBLink на какой-либо БД Oracleтребуется создать сначала источник данных(DSN) на сервере.

Пример конфигурирования DSN для MSSQL сервера.

Чтобы добавить DSN - заходим на хост с правами root(по ssh). На хосте уже установлены и сконфигурированыUnixODBC и FreeTDS(для MSSQL). Переходим в каталог /usr/local/etc и правим файл freetds.conf, добавляем описание сервера MSSQL (ip, port ). Выглядит это так:

[mssqlserver] <--- алиас сервера для DSNhost = <ip или dns имя>port = 1433tds version = 8.0client charset = UTF-8

Установленные драйвера для БД можно посмотреть в /etc/odbcinst.ini , а DSN описываются в /etc/odbc.ini. Чтобы добавить DSN для MSSQL следующего вида:

[sccm] <----- DSN - должно быть уникальным, проверить в /etc/odbc.iniDriver=FreeTDS <--- имя драйвера, см /etc/odbcinst.iniDescription=mssqlTDS_Version = 8.0 Trace=NoServerName=mssqlserver <---алиас сервера, см freetds.confPort=1433 Database= <DB>UID= логин в БДPWD= пароль

DSN добавлен - можно проверить работоспособность:

$> isql -v sccm логин пароль

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

Создание сервиса

У нас есть рабочий DSN, теперь можем создать сервис, который смогут использовать другие сервера БД Oracle.Сервис добавляется в listener.ora, который транслирует вызовы в HS. Логинимся пользователем oracle,добавляем сервис(например SCCM):

SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sccm) <--- имя сервиса, регистровозависимое, регистр тот же, что и в $ORACLE_HOME/hs/admin/init<сервис>.ora(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)(PROGRAM=dg4odbc) <--- используем ODBC гетерогенный доступ(ENVS=LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/xe))(SID_DESC=(SID_NAME=fp)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)(PROGRAM=dg4odbc)(ENVS=LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/xe)))

Перезапускаем листенер и проверяем, что сервис запущен и слушает входящие соединения

$ lsnrctl reload

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-APR-2015 11:14:34

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.2.0 - ProductionStart Date 17-APR-2015 15:07:38Uptime 6 days 20 hr. 6 min. 55 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFDefault Service XEListener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/serverdb/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverdb)(PORT=1521)))Services Summary...Service "fp" has 1 instance(s).Instance "fp", status UNKNOWN, has 1 handler(s) for this service...Service "sccm" has 1 instance(s).Instance "sccm", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully

Создаем сам гетерогенный сервис(под пользователем oracle)

$ cd $ORACLE_HOME/hs/admin

Создаем файл init<имясервиса>.ora ( в нашем примере initsccm.ora - регистр sccm тот же, что в listener.ora) соследующим содержимым:

## HS init parameters#HS_FDS_CONNECT_INFO = sccm <--- имя сервисаHS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.soHS_FDS_SQLLEN_INTERPRETATION=64HS_FDS_TRACE_LEVEL = off

HS_NLS_NCHAR=UCS2 <--- кодировка для MS SQL, чтобы у клиентов правильно отображалась кириллицаHS_LANGUAGE = AMERICAN_AMERICA.CL8MSWIN1251

set ODBCINI=/etc/odbc.ini

Теперь можно локально проверить, работает ли гетерогенный сервис. Логинимся пользователем oracle.Добавляем в локальный $ORACLE_HOME/network/admin/tnsnames.ora сервис SCCM:

SCCM =(DESCRIPTION =(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = serverdb)(PORT = 1521)))(CONNECT_DATA =(SID = SCCM))(HS = OK))

Проверяем доступность:

$ tnsping SCCM

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 24-APR-2015 11:30:14

Used parameter files:

Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = serverdb)(PORT = 1521))) (CONNECT_DATA = (SID = SCCM)) (HS = OK))OK (100 msec)

Создаем тестовый DBLink и проверяем работоспособность

$ sqlplus / as sysdba

$SQL> create database link TST connect to <логин ODBC> identified by <пароль ODBC> using 'SCCM';

$SQL> select count(*) from sysusers@TST;

$SQL> drop database link TST;

-------Ну вот как-то так

forundex.ru