Sql в запросе переменная: MS SQL Server и T-SQL

python — Передать аргументы из переменных в SQL-запрос


Вопрос задан


Изменён
3 года назад


Просмотрен
2k раз

Есть таблица, в которой я хочу изменить какую-либо запись:

def insertUser(ID, AUTHOR):
con = lite.connect("something.db")
with con:
    cur = con.cursor()
    sql = """UPDATE someth
            SET used = 1, 
            dis_name = ?, 
            dis_id = ?
            WHERE used = '0' """
    cur.execute(sql, (ID, AUTHOR))

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

Благодарю!

  • python
  • mysql
  • sql
  • sqlite

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

  • Обычная конкатенция оператором +
  • Форматирования с помощью оператора %
  • Использовать метод format() для строк
  • Использовать f-строки (только для python 3)

Пример с использованием метода .format()

def insertUser(ID, AUTHOR): 
    con = lite.connect("something.db")     
    with con: 
        cur = con.cursor() 
        sql = """UPDATE someth 
                 SET used = 1, dis_name = {}, dis_id = {}
                 WHERE used = 0
                 ORDER BY id ASC
                 LIMIT 1""".format(AUTHOR, ID)
        cur.execute(sql)

Для того, чтобы обновить только первую запись, необходимо использовать команду LIMIT с значением 1 вместе ORDER BY (для сортировки по ключевому полю). В примере использована колонка id (как правило, так обозначают в таблицах primary key поля). Если у вас нет такого поля, можете заменить на существующее

UPDATE:

Если вы используете sqlite, скомпилированную без объявления опции

#define SQLITE_ENABLE_UPDATE_DELETE_LIMIT

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

def insertUser(ID, AUTHOR): 
        con = lite.connect("something.db")     
        with con: 
            cur = con.cursor() 
            sql = """UPDATE someth 
                     SET used = 1, dis_name = {}, dis_id = {}
                     WHERE used = (
                         SELECT id FROM someth 
                         WHERE id = 0
                         ORDER BY id ASC
                         LIMIT 1)
            """.format(AUTHOR, ID)
            cur.execute(sql)

Либо перекомпировать sqlite согласно инструкции с включенной опцией SQLITE_ENABLE_UPDATE_DELETE_LIMIT






8







Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации


Почта

Необходима, но никому не показывается




Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки


Подстановка амперсанта

Вы разработали хороший запрос и возможно захотите использовать его в будущем. Иногда полезно иметь заготовку запроса, в которой указаны переменные, которые будут заменены на значения при выполнении запросы. Oracle предоставляет такой функционал в виде так называемой заменты переменной (ampersand substitution). Каждый элемент команды SELECT может быть подставлен во время выполнения, и путём оставления в запросе только ключевых элементов и ввода динамических переменных вы можете избежать много скучной и повторяемой работы. Мы рассмотрим подстановку переменной и ключевые слова DEFINE и VERIFY.

 

Замена переменных

 

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

 

Замена одиночным амперсандом

 

Самая простая и популярная форма SQL элемента это замена одинарного амперсанта. Символ амперсанта (&) выбран для назначения переменной в запросе и переменная состоит из амперсанта и названия переменной без пробела между ними. Когда запрос выполняется, серверный процесс Oracle видит переменную для замены и пытается определить её значение двумя способами. Во первых просматривается определена ли переменная в сессии пользователя. (Команду DEFINE мы рассмотрим чуть позже). Если переменная не определена, то пользовательский процесс запрашивает значение на которое будет заменена сооветствующая переменная. После того как значение введено, запрос выполняется сервером Oracle. Замена переменной амперсанта происходит в момент выполнения запроса и иногда называется связывание во время выполнения  (runtime binding) или подстановка во время выполнения (runtime substitution).

Вам может требоваться поиск значения телефона по заданной фамилии или номеру сотрудника. Такой запрос может быть написан следующим образом

 

select employee_id, last_name, phone_number from employees

where last_name = &LASTNAME or employee_id = &EMPNO;

Когда вы запустите запрос, Oracle спросит входное значения для переменной с именем LASTNAME. Вы можете ввести фамилию сотрудника, если вы её знаете, например ‘King’. Если вы не знаете фамилию но знаете номер, вы можете ввести любой значение и нажать OK для ввода значения. Затем Oracle запросит значения для переменной EMPNO. После ввода значения, например 0, и нажатия OK, не остаётся переменных для замены и выполняется следующий запрос

 

select employee_id, last_name, phone_number from employees

where last_name = ‘King’ or employee_id = 0;

 

Переменной можно назначить любой символьное значение с валидным именем. Значение-литерала на которое будет произведена замена должно быть соответственного типа данных, иначе вы получите ошибку “ORA-00904: invalid identifier”. Если переменная подразумевает символьное значение или значение данных, то литерал должен быть заключен в одинарные кавычки. Полезным способом избежать ошибки типа данных является обрамление переменной в кавычки при необходимости. Тогда пользователю нет необходимости знать о типе данных.

 

select employee_id, last_name, phone_number from employees

where last_name = ‘&LASTNAME’ or employee_id = &EMPNO;

 

Двойной амперсант

 

Когда переменная используется несколько раз в запросе, Oracle будет запрашивать значение каждый раз когда встречается переменная в запросе. Для сложных запросов, это может быть очень неэффективно и приводить к ошибкам. Следующий запрос выбирает FIRST_NAME и LAST_NAME из таблицы EMPLOYEES который содержит символы в обоих столбцах

 

select first_name, last_name from employees

where last_name like ‘%&SEARCH%’ and first_name like ‘%&SEARCH%’;

 

Два условия одинаковые но они проверяются для разных столбцов. Когда запрос будет выполняться, вначале Oracle потребует ввод значения для переменной SEARCH используемом в первом условии со столбцом LAST_NAME. Затем потребуется ввод данных для замены значения переменной SEARCH используемом при сравнении с FIRST_NAME. Это привносит две проблемы. Во первых это неэффективно вводить одно и тоже значение два раза, и во вторых, что более важно, можно допустить опечатку при повторном вводе, так как Oracle не проверяет идентичность ввода значения для переменной. В этом примере, допущено логическое предположение что значение переменных должно быть одинаковым, но тот факт что у переменной одинаковое имя не значит для Oracle что значение должно быть одинаковым. На первом примере на рисунке 9-7 отображён результат выполнения запроса и ввода двух разных значений для подстановки переменной. В этом примере результат неверный, так как исходно требование было таким, что фамилия и имя сотрудника должны содержать одинаковые литералы.

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

На втором примере на рисунке 9-7 показано как использовать переменную SEARCH с двумя амперсантами в условии для столбца LAST_NAME, а затем переменная с тем же именем и одним амперсантом не затребует ввода значения. Когда запрос будет выполняться, Oracle запросит значение для SEARCH только один раз установит значение переменной SEARCH для сессии введённое значение и будет использовать его дальше. Для того чтобы сбросить это значение вам необходимо будет выполнить команду UNDEFINE.

 

Рисунок 9-7 Использование двойного амперсанта

 

Подстановка названий столбцов

 

До этого мы обсуждали подстановку литералов в секции WHERE, но можно заменять любой элемент SQL запроса. В следующем примере столбцы FIRST_NAME и JOB_ID статическиу и будут возвращены в любом случае, но третий столбец это переменная для подстановки во время выполнения с именем COL. Результат также сортируется используя этот столбец-переменную указанную в директиве ORDER BY

 

select first_name, job_id, &&col

from employees

where job_id in (‘MK_MAN’,’SA_MAN’)

order by &col;

 

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

 

Подстановка выражений и текста

 

Практически все элементы SQL запроса могут быть заменены во время выполнения. Ограничение, установленное Oracle – только первое слово должно быть статичным. В случае команды SELECT, минимальной командой будет ключевое слово SELECT, а всё остальное может быть заменено во время выполнения как в следующем примере

 

select &rest_of_statement;

 

Когда команда будет выполняться, будет выведен запрос для ввода значения для переменной с именем REST_OF_STATEMENT, которая добавится к слову SELECT. Лучшими кандидатами для использования подстановки переменных являются запросы, которые выполняются много раз, и незначительно отличаются друг от друга.

 

Команды DEFINE и VERIFY

 

Двойной амперсант используется чтобы избежать ввода значения несколько раз для одной и тоже переменной которая используется несколько раз в запросе. Когда встречается двойной амперсант, переменная сохраняется как переменная сессии. Когда запрос выполняется, все остальные вхождения переменной автоматически заменяются, используя сохраненную переменную. Все последующие выполнения запроса в той же сессии автоматически определяют значения переменной из переменной сессии. Это не всегда то что требуется и также ограничивает функционал подстановки переменной. Как-бы там ни было, Oracle предоставляет механизм для очистки переменных сессии. Команда VERIFY это команда с клиентской стороны и она контролирует будут или нет выводиться пользователю заменённые элементы перед выполнением запроса, который использует переменные.

 

DEFINE и UNDEFINE

 

Переменные сессии создаются, когда они встречаются в запросах использующих двойной амперсант. Они существуют всё время жизни сессии или пока они явно неудалены. Сессия завершается, когда пользователь выходит из клиентской программы к примеру, SQL *Plus, или, когда пользовательский процесс принудительно завершается.

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

 

UNDEFINE variablename

 

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

 

select last_name, &&COLNAME

from employees where department_id=30 order by &COLNAME;

 

Первый раз когда запрос будет выполняться, появится запрос на ввод значения для переменной COLNAME. Предположим вы ввели SALARY. Это значение подменяется и запрос выполняется. Все последующие выполнения этого запроса в той же сессии не будут запрашивать значения для переменной COLNAME, так как уже создалась переменная и её значение SALARY. Переменная может быть удалена командой UNDEFINE COLNAME. После того как переменная сессии удалена – следующее выполнение запроса заново запросит пользователя ввести значение для переменной COLNAME.

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

 

DEFINE;

DEFINE variable=value;

Как показано на рисунке 9-8, переменная с именем EMPNAME явно определяется со значением ‘King’. Команда DEFINE без параметров возвращает список переменных сессии явно заданных и исользованных в запросах с двойным амперсантом.

Рисунок 9-8 – Пример использования команд DEFINE и UNDEFINE

 

Затем выполняются два простых запроса в которых используется явно определённая переменная EMPNAME. Затем, переменная удаляется.

Поддержка переменных-сессии может быть включена или отключена, когда необходимо с помощью команды SET DEFINE ON|OFF. Команда SET это не команда SQL, это команда управления окружением SQL. Если вы указываете SET DEFINE OFF, клиентская программа (к примеру, SQL *Plus) не сохраняет переменные сессии, а считает, что амперсант — это обычный литерал. Таким образом команда SET DEFINE OFF|ON управляет доступна ли замена переменной для вашей сессии. Следующий запрос использует символ амперсанта как обычный литерал. Когда запрос будет выполняться, будет выведен запрос для ввода значения дла переменной SID.

select ‘Coda&Sid’ from dual;

 

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

 

SET DEFINE OFF;

select ‘Coda&Sid’ from dual;

SET DEFINE ON;

 

После выполнения запроса, команда SET DEFINE ON может быть использована для включения функционала подстановки переменной обратно. Если подстановка переменной выключена и амперсанд используется не как литерал, запрос вернёт ошибку.

 

Команда VERIFY

 

Существует два типа команд при работе с Oracle: SQL команды и команды управления окружением SQL. Команда SELECT это команда языка SQL, команда SET управляет окружением. Доступно много параметров команды SET, но для контроля подстановки переменной доступны всего две: DEFINE и VERIFY.

Команда VERIFY управляет выводом введённого значения на экран, чтобы вы могли убедиться (verify) что подстановка осуществлена правильно. Сообщение выводится на экран в формате старого запроса, за ним идёт новый запрос с замененным значением. Команда VERIFY включает или выключает вывод на экран используя параметр OFF или ON. Если проверка выключена, то во время выполнения запроса с использованием амперсанта у вас запрашивается ввод значения, переменная заменяется, запрос выполняется и отображается результат. Если проверка включена и выполняется тот же запрос, то после ввода значения, но перед выводом результата, Oracle отображает раздел, в котором находилась переменная как старое значение с добавлением номеров строк, а затем ниже отображается новое значение с уже замененным значением.

sql — Запрос с переменными

Можно ли устанавливать/считывать переменные из запроса?

псевдокод: 

 ВЫБЕРИТЕ имя_животного,
    @tallest_animal = (выберите 1 верхний рост из списка животных по убыванию роста) как самый высокий,
    @smallest_animal = (выберите рост первых 1 животных в порядке возрастания роста) как наименьший
ОТ животных
ГДЕ высота МЕЖДУ @smallest_animal И @tallest_animal

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

Речь идет о Microsoft SQL Server. 🙂

  • sql
  • sql-сервер
  • подзапрос
  • запрос-переменные

2

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

Для этого вам нужно:

 SELECT @YourVariable = Column
ОТ животных

Примечание. Вы не можете использовать AS при назначении поля переменной.

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

Оператор SELECT, присваивающий значение переменной, не должен сочетаться с операциями извлечения данных.

Чтобы решить эту проблему, просто присвойте AnimalName переменной @AnimalName.

Изменить: 

 DECLARE @AnimalName VARCHAR(20)
DECLARE @TallestAnimal INT
DECLARE @SmallestAnimal INT
ВЫБЕРИТЕ @AnimalName = имя_животного,
   @TallestAnimal = (выберите 1 верхний рост из списка животных по описанию роста),
   @SmallestAnimal = (выберите 1 верхний рост из списка животных по возрастанию роста)
ОТ животных
ГДЕ высота МЕЖДУ @SmallestAnimal И @TallestAnimal

Этот код предполагает, что поле высоты имеет тип INT.

5

Нет, это невозможно, используйте вот так:

 DECLARE @самое высокое_животное целое, @самое маленькое_животное целое
SET @tallest_animal=(ВЫБЕРИТЕ максимальную (высоту) из животных)
SET @smallest_animal=(ВЫБЕРИТЕ мин.(высоту) из животных)
ВЫБЕРИТЕ имя_животного из числа животных, высота которых находится между @самым высоким_животным И @маленьким_животным

Что-то вроде этого будет работать, но я не уверен в том, что вы ищете.

1

Вместо переменных можно использовать производные таблицы.

 выберите A.имя_животного, M.самый высокий, M.самый маленький
от животных А
  внутреннее соединение
      (
        выберите максимальную (высоту) как самую высокую,
               min(высота) как наименьший
        от животного
      ) М
    на A.высоте между M.самым маленьким и M.самым высоким

0

Оператор select не может присваивать значения переменным и возвращать набор результатов в том же операторе SELECT — это ограничение SQL Server. Было бы здорово, если бы это было возможно!

Почему вы хотите использовать здесь переменные, если вам нужен один оператор? Вам не подойдет следующее?

 С ктэ (самый высокий, самый маленький) КАК (
    ВЫБЕРИТЕ МАКС(рост), МИН(рост) ОТ животных
)
ВЫБЕРИТЕ имя_животного ИЗ животных, cte ГДЕ высота МЕЖДУ самым маленьким И самым высоким

Если вы хотите использовать переменные позже в хранимой процедуре, то единственным вариантом является использование двух операторов выбора: один для присваивания и один для выбора:
ВЫБЕРИТЕ @самый высокий = МАКС(рост), @самый маленький = МИН(рост) ОТ животных
ВЫБЕРИТЕ имя_животного ИЗ животных, ГДЕ высота МЕЖДУ @самым маленьким И @самым высоким

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

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

sql server — SQL добавляет переменную в запрос

Задавать вопрос

спросил

Изменено
6 лет, 7 месяцев назад

Просмотрено
16 тысяч раз

Как создать переменные, которые указываются один раз, а затем используются в запросах сценария? Эти переменные могут использоваться несколько раз в запросе и в нескольких запросах в сценарии. я использую @x в качестве такой переменной в приведенных ниже примерах.

Я хочу сделать что-то вроде:

 Объявить @Query nvarchar(1000)
Объявите @x nvarchar(40)
Установите @x = 'тест'
Установите @Query = 'Выберите [имя]
                     , ' + @x + ' как [TestCase]
              Из моей таблицы'
Исполнитель (@Query)
-- возвращает "Недопустимое имя столбца "тест""

Что возвращает ошибку, упомянутую выше. Я хотел бы получить эквивалент:

 Declare @Query nvarchar(1000)
Объявите @x nvarchar(40)
Установите @x = 'тест'
Установите @Query = 'Выберите [имя]
                     , ''тест'' как [TestCase]
              Из моей таблицы'
Исполнитель (@Query)
-- Возвращает, например.
-- Имя тестового случая
-- Алиса Тест
-- Боб Тест

Я также отмечаю, что следующее не работает и возвращает ту же ошибку, что и первая:

 Declare @Query nvarchar(1000)
Объявите @x nvarchar(40)
Установите @x = 'тест'
Установите @Query = 'Выберите [имя]
                     , ' + 'тест' + ' как [TestCase]
              Из моей таблицы'
Исполнитель (@Query)
-- возвращает "Недопустимое имя столбца "тест""

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

  • sql
  • sql-server
  • ssms

Поскольку вы не пытаетесь использовать переменную в качестве имени столбца, вам вообще не нужно использовать динамический SQL. (Что хорошо, поскольку динамический SQL следует использовать с большой осторожностью, так как он является отличной поверхностью для атаки.)
установить @x = ‘тест’
выберите [Имя], @x как TestCase
из моей таблицы

подойдет.

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

 declare @x nvarchar(40)
объявить @query nvarchar (1000)
установить @x = 'тест'
set @query = 'выберите [Имя], @x как TestCase из mytable'
exec sp_executesql @query, N'@x nvarchar(1000)', @x

2

Вы пропустили кавычки.