Как изменить значение выводимой переменной в sql

Переменные (Transact-SQL)

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

• в качестве счетчика цикла;
• для хранения значения, которое необходимо проверить инструкцией управления потоком;
• для хранения значения, возвращенного функцией или хранимой процедурой.

• Имена некоторых системных функций Transact-SQL начинаются с двух символов @ (@@). Хотя в предыдущих версиях сервера SQL Server @@функции называются глобальными переменными, @@функции не являются переменными и используются иначе. @@functions являются системными функциями, а их синтаксис использует правила для функций.
• В представлении нельзя использовать переменные.
• Откат транзакции не влияет на изменения переменных.

Следующий скрипт создает небольшую тестовую таблицу из 26 строк. Переменная используется в скрипте в качестве:

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

-- Create the table. CREATE TABLE TestTable (cola INT, colb CHAR(3)); GO SET NOCOUNT ON; GO -- Declare the variable to be used. DECLARE @MyCounter INT; -- Initialize the variable. SET @MyCounter = 0; -- Test the variable to see if the loop is finished. WHILE (@MyCounter < 26) BEGIN; -- Insert a row into the table. INSERT INTO TestTable VALUES -- Use the variable to provide the integer value -- for cola. Also use it to generate a unique letter -- for each row. Use the ASCII function to get the -- integer value of 'a'. Add @MyCounter. Use CHAR to -- convert the sum back to the character @MyCounter -- characters after 'a'. (@MyCounter, CHAR( ( @MyCounter + ASCII('a') ) ) ); -- Increment the variable to count this iteration -- of the loop. SET @MyCounter = @MyCounter + 1; END; GO SET NOCOUNT OFF; GO -- View the data. SELECT cola, colb FROM TestTable; GO DROP TABLE TestTable; GO 

Объявление переменных в языке Transact-SQL

Инструкция DECLARE инициализирует переменную Transact-SQL следующим образом:

• Назначение имени. Имя должно иметь один @ в качестве первого символа.
• Назначение длины и типа данных, определяемого системой или пользователем. Для числовых переменных задаются также точность и масштаб. Для переменных типа XML может быть дополнительно задана коллекция схем.
• Присваивает созданной переменной значение NULL.

Например, следующая инструкция DECLARE создает локальную переменную @mycounter типа int.

DECLARE @MyCounter INT; 

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

Например, следующая инструкция DECLARE создает три локальные переменные с именем @LastName, @FirstName и @StateProvince, присваивая каждой из них значение NULL:

DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2); 

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

USE AdventureWorks2022; GO DECLARE @MyVariable INT; SET @MyVariable = 1; -- Terminate the batch by using the GO keyword. GO -- @MyVariable has gone out of scope and no longer exists. -- This SELECT statement generates a syntax error because it is -- no longer legal to reference @MyVariable. SELECT BusinessEntityID, NationalIDNumber, JobTitle FROM HumanResources.Employee WHERE BusinessEntityID = @MyVariable; 

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

DECLARE @MyVariable INT; SET @MyVariable = 1; EXECUTE sp_executesql N'SELECT @MyVariable'; -- this produces an error 

Присвоение значения переменной в языке Transact-SQL

При объявлении переменной присваивается значение NULL. Чтобы изменить значение переменной, применяется инструкция SET. Этот способ присвоения значений переменным является предпочтительным. Кроме того, переменной можно присвоить значение, указав ее в списке выбора инструкции SELECT.

Чтобы присвоить значение переменной при помощи инструкции SET, необходимо указать ее имя и присваиваемое значение. Этот способ присвоения значений переменным является предпочтительным. Например, следующий пакет объявляет две переменные, присваивает им значения и использует их в предложении WHERE инструкции SELECT :

USE AdventureWorks2022; GO -- Declare two variables. DECLARE @FirstNameVariable NVARCHAR(50), @PostalCodeVariable NVARCHAR(15); -- Set their values. SET @FirstNameVariable = N'Amy'; SET @PostalCodeVariable = N'BA5 3HX'; -- Use them in the WHERE clause of a SELECT statement. SELECT LastName, FirstName, JobTitle, City, StateProvinceName, CountryRegionName FROM HumanResources.vEmployee WHERE FirstName = @FirstNameVariable OR PostalCode = @PostalCodeVariable; GO 

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

USE AdventureWorks2022; GO DECLARE @EmpIDVariable INT; SELECT @EmpIDVariable = MAX(EmployeeID) FROM HumanResources.Employee; GO 

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

Если инструкция SELECT возвращает более одной строки и переменная ссылается на нескалярное выражение, ей присваивается значение, которое возвращается для выражения в последней строке результирующего набора. Например, в следующем пакете переменной @EmpIDVariable присваивается значение идентификатора BusinessEntityID последней возвращенной строки, равное 1:

USE AdventureWorks2022; GO DECLARE @EmpIDVariable INT; SELECT @EmpIDVariable = BusinessEntityID FROM HumanResources.Employee ORDER BY BusinessEntityID DESC; SELECT @EmpIDVariable; GO 

Переименование столбцов и вычисления в результирующем наборе стр. 1

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

Консоль

Выполнить

переименует столбец ram в Mb (мегабайты), а столбец hd в Gb (гигабайты). Этот запрос возвратит объемы оперативной памяти и жесткого диска для тех компьютеров, которые имеют 24-скоростной CD-ROM:

Переименование особенно желательно при использовании в предложении SELECT выражений для вычисления значения. Эти выражения позволяют получать данные, которые не находятся непосредственно в таблицах. Если выражение содержит имена столбцов таблицы, указанной в предложении FROM , то выражение подсчитывается для каждой строки выходных данных. Так, например, чтобы вывести объем оперативной памяти в килобайтах, можно написать:

Консоль

Выполнить

Теперь будет получен следующий результат:

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

Консоль

Выполнить

даст следующий результат:

Если же явно не указать имя для выражения, то будет принят способ именования по умолчанию, который зависит от используемой СУБД. Так, в MS Access будут использованы имена типа выражение1 и т. д., а выходной столбец в MS Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server вообще не будет иметь заголовка.

Накопление в переменную SQL Server

В MySQL есть такая интересная штука, как накопление в переменную. Это когда мы прямо в запросе можем декларировать, инициировать, менять значение и использовать переменную.

SELECT @I := @I + Value FROM Table ORDER BY OrderValue 

При этом порядок изменений значений переменной соответствует предложению ORDER BY , если оно есть. Это иногда очень красиво заменяет отсутствующую в MySQL рекурсию. В SQL Server такая возможность частично тоже есть. Т.е. мы можем использовать накопление в переменную:

SELECT @I += Value FROM Table 

Однако, в отличии от MySQL, такие запросы не возвращают датасэт, и использовать эти переменные внутри запроса нельзя(в добавок мы должны декларировать переменные заранее, но это не суть). Т.е. либо инициализируем переменную, либо используем. Вопрос. Влияет ли сортировка на порядок обновления переменной в SQL Server?

--Имеет ли смысл ORDER BY? SELECT @I += Value FROM Table ORDER BY OrderValue 

Если не имеет, то гарантирует ли уникальный кластерный ключ таблицы Table порядок обновлений? Если тоже нет. То зачем SQL Server оставил возможность накопления в переменную? Аккумулирую вопросы: 1) влияет ли сортировка на порядок обновления переменной в SQL Server? 2) (если ответ на 1 нет)гарантирует ли уникальный кластерный ключ таблицы Table порядок обновлений? 3) (если ответ на 2 нет)зачем SQL Server оставил возможность накопления в переменную?

Присвоить переменной результат запроса sql

Как переменной присвоить результат select-а
есть вот такой select. select add_months(trunc(sysdate,'MM'),-1) + level -1 x from dual connect.

Инициализация переменной через результат запроса
Здравствуйте. Можно ли в PL/SQL задать значение переменной через запрос? Например как-то так.

Как строке присвоить результат запроса
Есть запрос QueryClientsALLMan который возвращает одно значение поля Name из таблицы В VBA.

SQL. узнать результат выполнения запроса
Дана реляционная модель базы данных Таблица Customer содержит информацию о клиентах.

763 / 664 / 195
Регистрация: 24.11.2015
Сообщений: 2,158

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

1 2 3 4

SELECT a.id INTO var_id FROM vdealpersonloan_all a , vcontragent c WHERE dealno LIKE '%'||:dealno||'%' AND c.id = a.contragentid

Регистрация: 20.02.2015
Сообщений: 170
AGK
ошибка ora-00905 missing keyword
763 / 664 / 195
Регистрация: 24.11.2015
Сообщений: 2,158

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

1 2 3 4 5 6 7 8 9 10 11 12 13

DECLARE var_id NUMBER; v_deal varchar2(100) := :dealno; BEGIN FOR vv IN (SELECT REPLACE(a.dealno,'_KI','') dealno , a.id FROM vdealpersonloan_all a , vcontragent c WHERE dealno LIKE '%'||v_deal||'%' AND c.id = a.contragentid) loop var_id := vv.id; -- любые нужные вам операторы END loop; END;

Добавлено через 4 минуты

Сообщение от sergey23031978

ошибка ora-00905 missing keyword

Насколько я понимаю, вместо :dealno нужно подставить значение. Поскольку это варчар, что будет что-то типа 'ABCDE' (строка в апострофах)

Регистрация: 20.02.2015
Сообщений: 170

AGK
после етого скрипта нужно разместить другой скрипт которий возьмет в работу переменную var_id
подскажите как ето сделать

763 / 664 / 195
Регистрация: 24.11.2015
Сообщений: 2,158

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

1 2 3 4 5 6 7

BEGIN . END; BEGIN . END;

превращается в

1 2 3 4

BEGIN . . END;

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

Регистрация: 20.02.2015
Сообщений: 170
етот код находит var_id

1 2 3 4 5 6 7 8 9 10 11

DECLARE var_id NUMBER; BEGIN FOR vv IN (SELECT REPLACE(a.dealno,'_KI','') dealno , a.id FROM vdealpersonloan_all a , vcontragent c WHERE dealno LIKE '%'||:dealno||'%' AND c.id = a.contragentid) loop var_id := vv.id; END loop; END;

затем значение var_id нужно подставить в

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SELECT rownum , a.* FROM (SELECT SUM(v.summa/100) SUM , d.valuedate valuedate FROM vdealpersonloan_all a , vcontragent c , varc_document v , dealdoctransaction d WHERE a.id=var_id AND c.id = a.contragentid AND (v.accountano LIKE '2620%' OR v.accountano LIKE '2909%' OR v.accountano LIKE '3720%' OR v.accountano LIKE '1207%') AND a.corraccountno = v.accountbno AND a.currencyid = v.currencyid AND d.documentid = v.id AND d.valuedate BETWEEN '19.05.2016' AND '01.08.2017' AND v.platpurpose NOT LIKE 'Сторно%' GROUP BY d.valuedate UNION ALL SELECT SUM(v.summa/100) SUM , v.arcdate valuedate FROM vdealpersonloan_all a , vcontragent c , varc_document v WHERE a.id=var_id AND c.id = a.contragentid AND (v.accountano LIKE '2620%' OR v.accountano LIKE '2909%' OR v.accountano LIKE '3720%' OR v.accountano LIKE '1207%') AND a.corraccountno = v.accountbno AND a.currencyid = v.currencyid AND v.id NOT IN (SELECT documentid FROM dealdoctransaction WHERE DEALID = var_id) AND v.arcdate BETWEEN '19.05.2016' AND '01.08.2017' AND v.platpurpose NOT LIKE 'Сторно%' GROUP BY v.arcdate ORDER BY valuedate) a )

как ето сделать?
a.id и DEALID должни подставлятся(константа которая определяется в начале), чтоби не подвисала база

763 / 664 / 195
Регистрация: 24.11.2015
Сообщений: 2,158

Сообщение от sergey23031978

как ето сделать?

Селект, обычно, не может вызываться просто так в анонимном блоке.
Его надо либо вызывать интерактивно (в каком-то средстве разработки или просмотра БД), и тогда Вы видите результат на экране, либо его результат надо вставлять в какую-то другую таблицу. Если объем информации выводимых записей мал, его можно вывести в спул (на экран компьютера в Вашей сесии), но на это лучше не ориентироваться.
Ваш второй селект написан с ошибками.
1. Нельзя использовать служебные слова (например, SUM) в качестве алиасов. При попытке запустить селект будет ошибка.
2. Очень дурной тон использовать неявное преобразование типов. У Вас дата задается в виде строки, а ее надо задать в виде даты, иначе правильность селекта будет зависеть от NLS-настроек компьютера. Например, у меня Ваш селект просто не пройдет.

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

Регистрация: 20.02.2015
Сообщений: 170

AGK
а можете помочь с "первый селект засунуть в конструкцию WITH, а во втором селекте взять значение поля id из этой конструкции. Тогда можно будет просто запустить один селект, в котором будут выполнены обе части Вашей задачи. "

763 / 664 / 195
Регистрация: 24.11.2015
Сообщений: 2,158

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

WITH xx AS (SELECT REPLACE(a.dealno,'_KI','') dealno , a.id FROM vdealpersonloan_all a , vcontragent c WHERE dealno LIKE '%'||'значение переменной :dealno'||'%' AND c.id = a.contragentid) SELECT rownum , a.* FROM ( SELECT SUM(v.summa/100) sss, d.valuedate valuedate FROM vdealpersonloan_all a , vcontragent c , varc_document v , dealdoctransaction d, xx WHERE a.id=xx.id AND c.id = a.contragentid AND (v.accountano LIKE '2620%' OR v.accountano LIKE '2909%' OR v.accountano LIKE '3720%' OR v.accountano LIKE '1207%') AND a.corraccountno = v.accountbno AND a.currencyid = v.currencyid AND d.documentid = v.id AND d.valuedate BETWEEN to_date('19.05.2016','dd.mm.yyyy') AND to_date('01.08.2017','dd.mm.yyyy') AND v.platpurpose NOT LIKE 'Сторно%' GROUP BY d.valuedate UNION ALL SELECT SUM(v.summa/100) sss , v.arcdate valuedate FROM vdealpersonloan_all a , vcontragent c , varc_document v, xx WHERE a.id=xx.id AND c.id = a.contragentid AND (v.accountano LIKE '2620%' OR v.accountano LIKE '2909%' OR v.accountano LIKE '3720%' OR v.accountano LIKE '1207%') AND a.corraccountno = v.accountbno AND a.currencyid = v.currencyid AND v.id NOT IN (SELECT documentid FROM dealdoctransaction d WHERE d.DEALID = xx.id) AND v.arcdate BETWEEN to_date('19.05.2016','dd.mm.yyyy') AND to_date('01.08.2017','dd.mm.yyyy') AND v.platpurpose NOT LIKE 'Сторно%' GROUP BY v.arcdate ORDER BY valuedate) a

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