Использование табличнозначных параметров

Скачать драйвер JDBC

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

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

Дополнительные сведения о табличнозначных параметрах см. в следующих ресурсах.

Передача нескольких строк данных в предыдущих версиях SQL Server

До появления в SQL Server 2008 параметров, возвращающих табличное значение, были ограниченны возможности передачи нескольких строк данных в хранимую процедуру или параметризованную команду SQL. Разработчик может выбрать один из следующих вариантов передачи нескольких строк на сервер.

• Использовать ряд отдельных параметров, чтобы представить значения в нескольких столбцах и строках данных. Объем данных, которые можно передать с помощью этого метода, ограничен количеством допустимых параметров. В процедурах SQL Server можно использовать не более 2100 параметров. Для сборки этих отдельных значений в табличную переменную или временную таблицу для обработки требуется логика на стороне сервера.

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

• Создать ряд отдельных инструкций SQL для изменений данных, затрагивающих несколько строк. Изменения можно отправлять на сервер по отдельности или объединять в группы. Тем не менее даже при отправке в пакетах, содержащих несколько инструкций, каждая из них выполняется на сервере отдельно.

• Используйте служебную программу bcp или SQLServerBulkCopy, чтобы загрузить в таблицу большое количество строк. Хотя этот метод эффективен, он не поддерживает обработку на стороне сервера, если данные не загружены во временную таблицу или табличную переменную.

Создание типов табличных параметров

Параметры табличного типа основаны на строго типизированных табличных структурах, которые определяются с помощью инструкций Transact-SQL CREATE TYPE. Прежде чем использовать в клиентских приложениях параметры, имеющие табличное значение, необходимо создать в SQL Server табличный тип и определить его структуру. Дополнительные сведения о создании типов таблиц см. в разделе Определяемые пользователем табличные типы электронной документации на SQL Server.

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int, CategoryName nvarchar(50) )  

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

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

Изменение данных с использованием табличных параметров (Transact-SQL)

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

В следующей Transact-SQL UPDATE инструкции показано, как использовать табличный параметр, присоединив его к таблице "Категории". При использовании возвращающего табличное значение параметра со значением JOIN в предложении FROM необходимо также присвоить ему псевдоним, как показано здесь, где параметр с табличным значением имеет псевдоним "ec":

UPDATE dbo.Categories  
    SET Categories.CategoryName = ec.CategoryName  
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec  
    ON dbo.Categories.CategoryID = ec.CategoryID;  

В этом примере Transact-SQL показано, как выбрать строки из табличного параметра для выполнения INSERT в рамках одной операции над набором.

INSERT INTO dbo.Categories (CategoryID, CategoryName)  
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;  

Ограничения табличных параметров

У табличных параметров есть несколько ограничений:

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

• Для табличных параметров можно создавать индексы только для поддержки ограничений UNIQUE или PRIMARY KEY. SQL Server не поддерживает статистику для параметров табличного типа.

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

• Нельзя использовать инструкции ALTER TABLE для изменения структуры параметров с табличным значением.

• В параметре с табличным значением можно передавать большие объекты в потоковом режиме.

Настройка табличного параметра

Начиная с Microsoft JDBC Driver 6.0 для SQL Server, табличнозначные параметры поддерживаются в параметризованном операторе или параметризованной хранимой процедуре. Параметры табличного типа можно заполнить из SQLServerDataTable, из ResultSet или из предоставленной пользователем реализации интерфейса ISQLServerDataRecord. При задании параметра табличного типа для подготовленного запроса необходимо указать имя типа, которое должно совпадать с именем совместимого типа, ранее созданного на сервере.

В следующих двух фрагментах кода показано, как настроить параметр табличного типа с помощью SQLServerPreparedStatement и SQLServerCallableStatement для вставки данных. Здесь объект sourceTVPObject может быть объектом SQLServerDataTable, результирующим набором или объектом ISQLServerDataRecord. В примерах предполагается, что подключение является активным объектом Connection.

// Using table-valued parameter with a SQLServerPreparedStatement.  
SQLServerPreparedStatement pStmt =
    (SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO dbo.Categories SELECT * FROM ?");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject);  
pStmt.execute();  

// Using table-valued parameter with a SQLServerCallableStatement.  
SQLServerCallableStatement pStmt =
    (SQLServerCallableStatement) connection.prepareCall("exec usp_InsertCategories ?");
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject);;  
pStmt.execute();  

Передача табличного параметра как объекта SQLServerDataTable

Начиная с Microsoft JDBC Driver 6.0 для SQL Server, класс SQLServerDataTable представляет таблицу реляционных данных в памяти. В этом примере показано, как создать возвращающий табличное значение параметр из данных в памяти с помощью объекта SQLServerDataTable. Сначала код создает объект SQLServerDataTable, определяет его схему и заполняет таблицу данными. Затем код настраивает SQLServerPreparedStatement, который передает эту таблицу данных в SQL Server как табличный параметр.

/* Assumes connection is an active Connection object. */

// Create an in-memory data table.  
SQLServerDataTable sourceDataTable = new SQLServerDataTable();
  
// Define metadata for the data table.  
sourceDataTable.addColumnMetadata("CategoryID" ,java.sql.Types.INTEGER);
sourceDataTable.addColumnMetadata("CategoryName" ,java.sql.Types.NVARCHAR);
  
// Populate the data table.  
sourceDataTable.addRow(1, "CategoryNameValue1");
sourceDataTable.addRow(2, "CategoryNameValue2");
  
// Pass the data table as a table-valued parameter using a prepared statement.  
SQLServerPreparedStatement pStmt =
        (SQLServerPreparedStatement) connection.prepareStatement(  
            "INSERT INTO dbo.Categories SELECT * FROM ?;");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceDataTable);  
pStmt.execute();  

Этот пример аналогичен предыдущему. Единственное отличие состоит в том, что он задает имя TVP для SQLServerDataTable вместо того, чтобы полагаться на приведение PreparedStatement к SQLServerPreparedStatement для использования метода setStructured.

/* Assumes connection is an active Connection object. */

// Create an in-memory data table.
SQLServerDataTable sourceDataTable = new SQLServerDataTable();
sourceDataTable.setTvpName("dbo.CategoryTableType");

// Define metadata for the data table.
sourceDataTable.addColumnMetadata("CategoryID" ,java.sql.Types.INTEGER);
sourceDataTable.addColumnMetadata("CategoryName" ,java.sql.Types.NVARCHAR);

// Populate the data table.
sourceDataTable.addRow(1, "CategoryNameValue1");
sourceDataTable.addRow(2, "CategoryNameValue2");

// Pass the data table as a table-valued parameter using a prepared statement.
PreparedStatement pStmt =
        connection.prepareStatement(
            "INSERT INTO dbo.Categories SELECT * FROM ?;");
pStmt.setObject(1, sourceDataTable);
pStmt.execute();

Передача табличного параметра в виде объекта ResultSet

В этом примере показано, как выполнять потоковую передачу строк данных из ResultSet в табличный параметр. Сначала код извлекает данные из исходной таблицы, создает объект SQLServerDataTable, определяет его схему и заполняет таблицу данными. Затем код настраивает SQLServerPreparedStatement для передачи этой таблицы данных в SQL Server в качестве табличного параметра.

/* Assumes connection is an active Connection object. */

// Create the source ResultSet object. Here SourceCategories is a table defined with the same schema as Categories table.
ResultSet sourceResultSet = connection.createStatement().executeQuery("SELECT * FROM SourceCategories");  

// Pass the source result set as a table-valued parameter using a prepared statement.  
SQLServerPreparedStatement pStmt =
        (SQLServerPreparedStatement) connection.prepareStatement(  
                "INSERT INTO dbo.Categories SELECT * FROM ?;");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceResultSet);  
pStmt.execute();  

Передача параметра табличного типа как объекта ISQLServerDataRecord

Начиная с Microsoft JDBC Driver 6.0 для SQL Server, доступен новый интерфейс ISQLServerDataRecord для потоковой передачи данных с использованием табличного параметра (в зависимости от того, как пользователь реализует этот интерфейс). В следующем примере показано, как реализовать интерфейс ISQLServerDataRecord и передать его в качестве возвращающего табличное значение параметра. Для простоты в следующем примере в параметр табличного типа передается только одна строка с жестко заданными значениями. В идеале пользователь будет реализовывать этот интерфейс для потоковой передачи строк из любого источника, например из текстовых файлов.

class MyRecords implements ISQLServerDataRecord  
{  
    int currentRow = 0;  
    Object[] row = new Object[2];  
  
    MyRecords(){  
        // Constructor. This implementation has just one row.
        row[0] = new Integer(1);  
        row[1] = "categoryName1";  
    }  
  
    public int getColumnCount(){  
        // Return the total number of columns, for this example it is 2.  
        return 2;  
    }  
  
    public SQLServerMetaData getColumnMetaData(int columnIndex) {  
        // Return the column metadata.  
        if (1 == columnIndex)  
            return new SQLServerMetaData("CategoryID", java.sql.Types.INTEGER);  
        else  
            return new SQLServerMetaData("CategoryName", java.sql.Types.NVARCHAR);  
    }  
  
    public Object[] getRowData(){  
        // Return the columns in the current row as an array of objects. This implementation has just one row.  
        return row;
    }  
  
    public boolean next(){  
        // Move to the next row. This implementation has just one row, after processing the first row, return false.  
        currentRow++;  
        if (1 == currentRow)  
            return true;  
        else  
            return false;  
    }
}

// Following code demonstrates how to pass MyRecords object as a table-valued parameter.  
MyRecords sourceRecords = new MyRecords();  
SQLServerPreparedStatement pStmt =
        (SQLServerPreparedStatement) connection.prepareStatement(  
                "INSERT INTO dbo.Categories SELECT * FROM ?;");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceRecords);  
pStmt.execute();  

API параметров табличного типа для драйвера JDBC

SQLServerMetaData

Этот класс представляет метаданные для столбца. Он используется в интерфейсе ISQLServerDataRecord для передачи метаданных столбцов табличному параметру. В этом классе используются следующие методы.

SQLServerSortOrder

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

SQLServerDataTable

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

SQLServerDataColumn

Этот класс представляет столбец таблицы данных в памяти, представленный SQLServerDataTable. В этом классе используются следующие методы.

ISQLServerDataRecord

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

SQLServerPreparedStatement

Для поддержки передачи параметров табличного типа в этот класс были добавлены следующие методы.

SQLServerCallableStatement

В этот класс были добавлены следующие методы для поддержки передачи параметров табличного типа.

См. также

Общие сведения о JDBC Driver