MENU
Главная » Статьи » Без категории
SQL
https://metanit.com/sql/sqlserver/10.4.php
 
Временные таблицы                           Вопрос А что лучше использовать: табличные переменные, временные таблицы или производные таблицы? По сути, отличий толком нет, особенно между табличными переменными и временными таблицами.
Временные таблицы существуют на протяжении сессии базы данных.                   Ответ ЕСТЬ большая разница. Попробуйте создать табличную переменную и сделать запрос SELECT * FROM без её повторного объявления. А в случае временной таблице, её можно создать один раз и на протяжении всех запросов она будет хранить данные, пока мы её не удалим либо не закроем вкладку запроса.
Если такая таблица создается в редакторе запросов (Query Editor), то таблица будет существовать пока открыт редактор запросов.        
Таким образом, к временной таблице можно обращаться из разных скриптов внутри редактора запросов.              
После создания все временные таблицы сохраняются в таблице tempdb, которая имеется по умолчанию в MS SQL Server.          
Если необходимо удалить таблицу до завершения сессии базы данных, то для этой таблицы следует выполнить команду DROP TABLE.        
Название временной таблицы начинается со знака решетки #.                        
Если используется один знак #, то создается локальная таблица, которая доступна в течение текущей сессии.            
Если используются два знака ##, то создается глобальная временная таблица.                    
В отличие от локальной глобальная временная таблица доступна всем открытым сессиям базы данных.              
                                     
Например, создадим локальную временную таблицу:   Можно забирать данные из уже готовых таблиц:              
                                     
  USE productsdb;         SELECT ProductId,                   
              SUM(ProductCount) AS TotalCount,               
  CREATE TABLE #ProductSummary     SUM(ProductCount * Price) AS TotalSum              
  (ProdId INT IDENTITY,       INTO #OrdersSummary                  
  ProdName NVARCHAR(20),       FROM Orders                    
  Price MONEY)         GROUP BY ProductId                  
                                     
  INSERT INTO #ProductSummary     SELECT Products.ProductName, #OrdersSummary.TotalCount, #OrdersSummary.TotalSum      
  VALUES ('Nokia 8', 18000),       FROM Products                    
  ('iPhone 8', 56000)     JOIN #OrdersSummary ON Products.Id = #OrdersSummary.ProductId          
                                     
  SELECT * FROM #ProductSummary                            
                                     
Подобным образом определяются глобальные временные таблицы, единственное, что их имя начинается с двух знаков ##          
                                     
Производные таблицы                                
Кроме временных таблиц MS SQL Server позволяет создавать производные таблицы,                   
которые в плане производительности являются более эффективным решением, чем временные. Производная таблица задается с помощью ключевого слова WITH:    
  WITH OrdersInfo AS                              
  (                                  
  SELECT ProductId,                               
  SUM(ProductCount) AS TotalCount,                           
  SUM(ProductCount * Price) AS TotalSum                          
  FROM Orders                                
  GROUP BY ProductId                              
  )                                  
                                     
  SELECT * FROM OrdersInfo -- здесь нормально                          
  SELECT * FROM OrdersInfo -- здесь ошибка                          
  SELECT * FROM OrdersInfo -- здесь ошибка                          
                                     
В отличие от временных таблиц производные хранятся в оперативной памяти и существуют               
только во время первого выполнения запроса, который представляет эту таблицу.                
 
Ограничения  
Определяемые пользователем функции не могут выполнять действия, изменяющие состояние базы данных.
Определяемые пользователем функции не могут содержать предложение OUTPUT INTO, целью которого является таблица.
Определяемые пользователем функции не могут возвращать несколько результирующих наборов.Используйте хранимую процедуру, если нужно возвращать несколько результирующих наборов.
Обработка ошибок в функциях, определяемых пользователем, ограниченна. UDF не поддерживает инструкции TRY…CATCH, @ERROR и RAISERROR.
Определяемые пользователем функции не могут вызывать хранимую процедуру, но могут вызывать расширенную хранимую процедуру.
Определяемые пользователем функции не могут использовать динамический SQL и временные таблицы.Табличные переменные разрешены к использованию.
Инструкцию SET нельзя использовать в определяемых пользователем функциях.
Предложение FOR XML не допускается к использованию.
Определяемые пользователем функции могут быть вложенными, то есть из одной функции может быть вызвана другая.
Уровень вложенности увеличивается на единицу каждый раз, когда начинается выполнение вызванной функции и уменьшается на единицу, когда ее выполнение завершается.
Вложенность определяемых пользователем функций не может превышать 32 уровней. Превышение максимального уровня вложенности приводит к ошибке выполнения для всей цепочки вызываемых функций.
Каждый вызов управляемого кода из определяемой пользователем функции Transact-SQL считается одним уровнем вложенности из 32 возможных. Методы, вызываемые из управляемого кода, под это ограничение не подпадают.
Следующие инструкции компонента Service Broker не могут быть включены в определение определяемой пользователем функции Transact-SQL:
BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE  
SEND  
   
Разрешения  
Требуется разрешение CREATE FUNCTION на базу данных и разрешение ALTER на схему, в которой создается функция. Если в функции указан определяемый пользователем тип, требуется разрешение EXECUTE на этот тип.
   
Скалярные функции
Функция имеет один входной параметр ProductIDи возвращает одно значение — количество указанного товара на складе
  IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
      DROP FUNCTION ufnGetInventoryStock;  
  GO  
  CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
  RETURNS int   
  AS   
   -- Returns the stock level for the product.
  BEGIN  
      DECLARE @ret int;  
      SELECT @ret = SUM(p.Quantity)   
      FROM Production.ProductInventory p   
      WHERE p.ProductID = @ProductID   
          AND p.LocationID = '6';  
       IF (@ret IS NULL)   
          SET @ret = 0;  
      RETURN @ret;  
  END;
   
В следующем примере функция ufnGetInventoryStock используется для получения сведений о количестве товаров с идентификаторами ProductModelID от 75 до 80.
  SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply FROM Production.Product WHERE ProductModelID BETWEEN 75 and 80;
   
Функции с табличными значениями
  IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL  
      DROP FUNCTION Sales.ufn_SalesByStore;  
  GO  
  CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
  RETURNS TABLE  
  AS  
  RETURN   
  (  
      SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'  
      FROM Production.Product AS P   
      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
      JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
      WHERE C.StoreID = @storeid  
      GROUP BY P.ProductID, P.Name  
  );
   
В следующем примере функция вызывается с идентификатором 602.
  SELECT * FROM Sales.ufn_SalesByStore (602);
   
   
  IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL  
      DROP FUNCTION dbo.ufn_FindReports;  
  GO  
  CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)  
  RETURNS @retFindReports TABLE   
  (  
      EmployeeID int primary key NOT NULL,  
      FirstName nvarchar(255) NOT NULL,  
      LastName nvarchar(255) NOT NULL,  
      JobTitle nvarchar(50) NOT NULL,  
      RecursionLevel int NOT NULL  
  )  
   --Returns a result set that lists all the employees who report to the
  --specific employee directly or indirectly.*/  
  AS  
  BEGIN  
  WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns  
      AS (  
          SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n  
          FROM HumanResources.Employee e   
  INNER JOIN Person.Person p   
  ON p.BusinessEntityID = e.BusinessEntityID  
          WHERE e.BusinessEntityID = @InEmpID  
          UNION ALL  
          SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor  
          FROM HumanResources.Employee e   
              INNER JOIN EMP_cte  
              ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode  
  INNER JOIN Person.Person p   
  ON p.BusinessEntityID = e.BusinessEntityID  
          )  
   -- copy the required columns to the result of the function
     INSERT @retFindReports  
     SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
     FROM EMP_cte   
     RETURN  
  END;  
  GO  
   -- Example invocation
  SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
  FROM dbo.ufn_FindReports(1);
Категория: Без категории | Добавил: clownsaround (05.12.2018)
Просмотров: 551 | Рейтинг: 0.0/0
Всего комментариев: 0
Войдите:
avatar