如何在 Azure Cosmos DB 中编写存储过程、触发器和用户定义的函数How to write stored procedures, triggers, and user-defined functions in Azure Cosmos DB

Azure Cosmos DB 提供 JavaScript 的语言集成式事务执行用于编写存储过程触发器用户定义的函数 (UDF)Azure Cosmos DB provides language-integrated, transactional execution of JavaScript that lets you write stored procedures, triggers, and user-defined functions (UDFs). 在 Azure Cosmos DB 中使用 SQL API 时,可以采用 JavaScript 语言定义存储过程、触发器和 UDF。When using the SQL API in Azure Cosmos DB, you can define the stored procedures, triggers, and UDFs in JavaScript language. 可在 JavaScript 中编写逻辑,并在数据库引擎内部执行该逻辑。You can write your logic in JavaScript and execute it inside the database engine. 可以使用 Azure 门户Azure Cosmos DB 中的 JavaScript 语言集成式查询 APICosmos DB SQL API 客户端 SDK 来创建及执行触发器、存储过程与 UDF。You can create and execute triggers, stored procedures, and UDFs by using Azure portal, the JavaScript language integrated query API in Azure Cosmos DB and the Cosmos DB SQL API client SDKs.

若要调用存储过程、触发器和用户定义的函数,需将其注册。To call a stored procedure, trigger, and user-defined function, you need to register it. 有关详细信息,请参阅如何在 Azure Cosmos DB 中使用存储过程、触发器和用户定义的函数For more information, see How to work with stored procedures, triggers, user-defined functions in Azure Cosmos DB.

备注

对于已分区的容器,在执行存储过程时,必须在请求选项中提供分区键值。For partitioned containers, when executing a stored procedure, a partition key value must be provided in the request options. 存储过程的范围始终限定为分区键。Stored procedures are always scoped to a partition key. 存储过程看不到具有不同分区键值的项。Items that have a different partition key value will not be visible to the stored procedure. 这一点也适用于触发器。This also applied to triggers as well.

提示

Cosmos 支持使用存储过程、触发器和用户定义的函数部署容器。Cosmos supports deploying containers with stored procedures, triggers and user-defined functions. 有关详细信息,请参阅使用服务器端功能创建 Azure Cosmos DB 容器For more information see Create an Azure Cosmos DB container with server-side functionality.

如何编写存储过程How to write stored procedures

存储过程是使用 JavaScript 编写的,它们可以在 Azure Cosmos 容器中创建、更新、读取、查询和删除项。Stored procedures are written using JavaScript, they can create, update, read, query, and delete items inside an Azure Cosmos container. 存储过程按集合注册,可以针对该集合中的任何文档或附件运行。Stored procedures are registered per collection, and can operate on any document or an attachment present in that collection.

下面是一个可以返回“Hello World”响应的简单存储过程。Here is a simple stored procedure that returns a "Hello World" response.

var helloWorldStoredProc = {
    id: "helloWorld",
    serverScript: function () {
        var context = getContext();
        var response = context.getResponse();

        response.setBody("Hello, World");
    }
}

上下文对象提供对所有可在 Azure Cosmos DB 中执行的操作的访问,以及对请求和响应对象的访问。The context object provides access to all operations that can be performed in Azure Cosmos DB, as well as access to the request and response objects. 在本例中,我们将使用响应对象来设置要发回到客户端的响应正文。In this case, you use the response object to set the body of the response to be sent back to the client.

编写存储过程后,必须将其注册到集合。Once written, the stored procedure must be registered with a collection. 有关详细信息,请参阅如何在 Azure Cosmos DB 中使用存储过程一文。To learn more, see How to use stored procedures in Azure Cosmos DB article.

使用存储过程创建项Create an item using stored procedure

使用存储过程创建某个项时,该项将会插入到 Azure Cosmos 容器,并返回新建项的 ID。When you create an item by using stored procedure, the item is inserted into the Azure Cosmos container and an ID for the newly created item is returned. 创建项是一种异步操作,依赖于 JavaScript 回调函数。Creating an item is an asynchronous operation and depends on the JavaScript callback functions. 回调函数包含两个参数 - 一个参数用于操作失败时返回的错误对象,另一个参数用于返回值(在本例中为创建的对象)。The callback function has two parameters - one for the error object in case the operation fails and another for a return value; in this case, the created object. 在回调内部,可以处理异常或引发错误。Inside the callback, you can either handle the exception or throw an error. 如果未提供回调并出现错误,则 Azure Cosmos DB 运行时将引发错误。In case a callback is not provided and there is an error, the Azure Cosmos DB runtime will throw an error.

存储过程还包含一个用于设置说明的参数(一个布尔值)。The stored procedure also includes a parameter to set the description, it's a boolean value. 如果该参数设置为 true,同时缺少说明,则存储过程将引发异常。When the parameter is set to true and the description is missing, the stored procedure will throw an exception. 否则,存储过程的剩余部分将继续运行。Otherwise, the rest of the stored procedure continues to run.

以下示例存储过程采用新 Azure Cosmos 项的数组作为输入,将其插入 Azure Cosmos 容器,然后返回插入项的计数。The following example stored procedure takes an array of new Azure Cosmos items as input, inserts it into the Azure Cosmos container and returns the count of the items inserted. 此示例利用快速入门 .NET SQL API 中的 ToDoList 示例In this example, we are leveraging the ToDoList sample from the Quickstart .NET SQL API

function createToDoItems(items) {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();
    var count = 0;

    if (!items) throw new Error("The array is undefined or null.");

    var numItems = items.length;

    if (numItems == 0) {
        getContext().getResponse().setBody(0);
        return;
    }

    tryCreate(items[count], callback);

    function tryCreate(item, callback) {
        var options = { disableAutomaticIdGeneration: false };

        var isAccepted = collection.createDocument(collectionLink, item, options, callback);

        if (!isAccepted) getContext().getResponse().setBody(count);
    }

    function callback(err, item, options) {
        if (err) throw err;
        count++;
        if (count >= numItems) {
            getContext().getResponse().setBody(count);
        } else {
            tryCreate(items[count], callback);
        }
    }
}

将数组用作存储过程的输入参数Arrays as input parameters for stored procedures

使用 Azure 门户定义存储过程时,输入参数始终以字符串的形式发送到该存储过程。When defining a stored procedure in Azure portal, input parameters are always sent as a string to the stored procedure. 即使将字符串数组作为输入传递,该数组也会转换为字符串发送到存储过程。Even if you pass an array of strings as an input, the array is converted to string and sent to the stored procedure. 若要解决此问题,可在存储过程中定义一个函数以将字符串作为数组进行分析。To work around this, you can define a function within your stored procedure to parse the string as an array. 以下代码演示如何将字符串输入参数作为数组进行分析:The following code shows how to parse a string input parameter as an array:

function sample(arr) {
    if (typeof arr === "string") arr = JSON.parse(arr);

    arr.forEach(function(a) {
        // do something here
        console.log(a);
    });
}

存储过程中的事务Transactions within stored procedures

可以使用存储过程对容器中的项实现事务。You can implement transactions on items within a container by using a stored procedure. 以下示例使用梦幻足球游戏应用中的事务,通过单个操作在两支球队之间交易球员。The following example uses transactions within a fantasy football gaming app to trade players between two teams in a single operation. 该存储过程尝试读取两个 Azure Cosmos 项,其中每个项对应于作为参数传递的球员 ID。The stored procedure attempts to read the two Azure Cosmos items each corresponding to the player IDs passed in as an argument. 如果找到了这两个球员,则存储过程将通过交换其所在球队来更新这些项。If both players are found, then the stored procedure updates the items by swapping their teams. 如果在此过程中遇到了任何错误,则存储过程将引发 JavaScript 异常,从而隐式终止事务。If any errors are encountered along the way, the stored procedure throws a JavaScript exception that implicitly aborts the transaction.

// JavaScript source code
function tradePlayers(playerId1, playerId2) {
    var context = getContext();
    var container = context.getCollection();
    var response = context.getResponse();

    var player1Document, player2Document;

    // query for players
    var filterQuery =
    {
        'query' : 'SELECT * FROM Players p where p.id = @playerId1',
        'parameters' : [{'name':'@playerId1', 'value':playerId1}] 
    };

    var accept = container.queryDocuments(container.getSelfLink(), filterQuery, {},
        function (err, items, responseOptions) {
            if (err) throw new Error("Error" + err.message);

            if (items.length != 1) throw "Unable to find both names";
            player1Item = items[0];

            var filterQuery2 =
            {
                'query' : 'SELECT * FROM Players p where p.id = @playerId2',
                'parameters' : [{'name':'@playerId2', 'value':playerId2}]
            };
            var accept2 = container.queryDocuments(container.getSelfLink(), filterQuery2, {},
                function (err2, items2, responseOptions2) {
                    if (err2) throw new Error("Error" + err2.message);
                    if (items2.length != 1) throw "Unable to find both names";
                    player2Item = items2[0];
                    swapTeams(player1Item, player2Item);
                    return;
                });
            if (!accept2) throw "Unable to read player details, abort ";
        });

    if (!accept) throw "Unable to read player details, abort ";

    // swap the two players' teams
    function swapTeams(player1, player2) {
        var player2NewTeam = player1.team;
        player1.team = player2.team;
        player2.team = player2NewTeam;

        var accept = container.replaceDocument(player1._self, player1,
            function (err, itemReplaced) {
                if (err) throw "Unable to update player 1, abort ";

                var accept2 = container.replaceDocument(player2._self, player2,
                    function (err2, itemReplaced2) {
                        if (err) throw "Unable to update player 2, abort"
                    });

                if (!accept2) throw "Unable to update player 2, abort";
            });

        if (!accept) throw "Unable to update player 1, abort";
    }
}

存储过程中的绑定执行Bounded execution within stored procedures

下面是将项批量导入到 Azure Cosmos 容器的存储过程示例。The following is an example of a stored procedure that bulk-imports items into an Azure Cosmos container. 存储过程通过检查来自 createDocument 的布尔返回值来处理绑定执行,然后使用每次调用存储过程时插入的项计数,来跟踪不同的批及恢复其进度。The stored procedure handles bounded execution by checking the boolean return value from createDocument, and then uses the count of items inserted in each invocation of the stored procedure to track and resume progress across batches.

function bulkImport(items) {
    var container = getContext().getCollection();
    var containerLink = container.getSelfLink();

    // The count of imported items, also used as current item index.
    var count = 0;

    // Validate input.
    if (!items) throw new Error("The array is undefined or null.");

    var itemsLength = items.length;
    if (itemsLength == 0) {
        getContext().getResponse().setBody(0);
    }

    // Call the create API to create an item.
    tryCreate(items[count], callback);

    // Note that there are 2 exit conditions:
    // 1) The createDocument request was not accepted.
    //    In this case the callback will not be called, we just call setBody and we are done.
    // 2) The callback was called items.length times.
    //    In this case all items were created and we don't need to call tryCreate anymore. Just call setBody and we are done.
    function tryCreate(item, callback) {
        var isAccepted = container.createDocument(containerLink, item, callback);

        // If the request was accepted, callback will be called.
        // Otherwise report current count back to the client,
        // which will call the script again with remaining set of items.
        if (!isAccepted) getContext().getResponse().setBody(count);
    }

    // This is called when container.createDocument is done in order to process the result.
    function callback(err, item, options) {
        if (err) throw err;

        // One more item has been inserted, increment the count.
        count++;

        if (count >= itemsLength) {
            // If we created all items, we are done. Just set the response.
            getContext().getResponse().setBody(count);
        } else {
            // Create next document.
            tryCreate(items[count], callback);
        }
    }
}

使用存储过程的 async awaitAsync await with stored procedures

下面是使用 helper 函数将 async-await 与 Promises 结合使用的存储过程的示例。The following is an example of a stored procedure that uses async-await with Promises using a helper function. 存储过程会查询项并将其替换。The stored procedure queries for an item and replaces it.

function async_sample() {
    const ERROR_CODE = {
        NotAccepted: 429
    };

    const asyncHelper = {
        queryDocuments(sqlQuery, options) {
            return new Promise((resolve, reject) => {
                const isAccepted = __.queryDocuments(__.getSelfLink(), sqlQuery, options, (err, feed, options) => {
                    if (err) reject(err);
                    resolve({ feed, options });
                });
                if (!isAccepted) reject(new Error(ERROR_CODE.NotAccepted, "replaceDocument was not accepted."));
            });
        },

        replaceDocument(doc) {
            return new Promise((resolve, reject) => {
                const isAccepted = __.replaceDocument(doc._self, doc, (err, result, options) => {
                    if (err) reject(err);
                    resolve({ result, options });
                });
                if (!isAccepted) reject(new Error(ERROR_CODE.NotAccepted, "replaceDocument was not accepted."));
            });
        }
    };

    async function main() {
        let continuation;
        do {
            let { feed, options } = await asyncHelper.queryDocuments("SELECT * from c", { continuation });

            for (let doc of feed) {
                doc.newProp = 1;
                await asyncHelper.replaceDocument(doc);
            }

            continuation = options.continuation;
        } while (continuation);
    }

    main().catch(err => getContext().abort(err));
}

如何编写触发器How to write triggers

Azure Cosmos DB 支持前触发器和后触发器。Azure Cosmos DB supports pre-triggers and post-triggers. 前触发器是在修改数据库项之前执行的,后触发器是在修改数据库项之后执行的。Pre-triggers are executed before modifying a database item and post-triggers are executed after modifying a database item.

前触发器Pre-triggers

以下示例演示如何使用前触发器来验证正在创建的 Azure Cosmos 项的属性。The following example shows how a pre-trigger is used to validate the properties of an Azure Cosmos item that is being created. 此示例利用快速入门 .NET SQL API 中的 ToDoList 示例,将时间戳属性添加到新添加的项(如果其中不包含此属性)。In this example, we are leveraging the ToDoList sample from the Quickstart .NET SQL API, to add a timestamp property to a newly added item if it doesn't contain one.

function validateToDoItemTimestamp() {
    var context = getContext();
    var request = context.getRequest();

    // item to be created in the current operation
    var itemToCreate = request.getBody();

    // validate properties
    if (!("timestamp" in itemToCreate)) {
        var ts = new Date();
        itemToCreate["timestamp"] = ts.getTime();
    }

    // update the item that will be created
    request.setBody(itemToCreate);
}

预触发器不能有任何输入参数。Pre-triggers cannot have any input parameters. 使用触发器中的请求对象来处理与操作关联的请求消息。The request object in the trigger is used to manipulate the request message associated with the operation. 在前面的示例中,创建 Azure Cosmos 项时将运行前触发器,请求消息正文包含要以 JSON 格式创建的项。In the previous example, the pre-trigger is run when creating an Azure Cosmos item, and the request message body contains the item to be created in JSON format.

注册触发器后,可以指定可对哪些操作运行该触发器。When triggers are registered, you can specify the operations that it can run with. 应使用 TriggerOperation.CreateTriggerOperation 值创建此触发器,这意味着,不允许在以下代码所示的 replace 操作中使用此触发器。This trigger should be created with a TriggerOperation value of TriggerOperation.Create, which means using the trigger in a replace operation as shown in the following code is not permitted.

有关如何注册和调用前触发器的示例,请参阅前触发器后触发器文章。For examples of how to register and call a pre-trigger, see pre-triggers and post-triggers articles.

后触发器Post-triggers

以下示例演示了一个后触发器。The following example shows a post-trigger. 此触发器查询元数据项,并在其中更新有关新建项的详细信息。This trigger queries for the metadata item and updates it with details about the newly created item.

function updateMetadata() {
var context = getContext();
var container = context.getCollection();
var response = context.getResponse();

// item that was created
var createdItem = response.getBody();

// query for metadata document
var filterQuery = 'SELECT * FROM root r WHERE r.id = "_metadata"';
var accept = container.queryDocuments(container.getSelfLink(), filterQuery,
    updateMetadataCallback);
if(!accept) throw "Unable to update metadata, abort";

function updateMetadataCallback(err, items, responseOptions) {
    if(err) throw new Error("Error" + err.message);
        if(items.length != 1) throw 'Unable to find metadata document';

        var metadataItem = items[0];

        // update metadata
        metadataItem.createdItems += 1;
        metadataItem.createdNames += " " + createdItem.id;
        var accept = container.replaceDocument(metadataItem._self,
            metadataItem, function(err, itemReplaced) {
                    if(err) throw "Unable to update metadata, abort";
            });
        if(!accept) throw "Unable to update metadata, abort";
        return;
}

必须注意的一个要点是 Azure Cosmos DB 中触发器的事务执行。One thing that is important to note is the transactional execution of triggers in Azure Cosmos DB. 后触发器作为基础项本身的同一事务的一部分运行。The post-trigger runs as part of the same transaction for the underlying item itself. 后触发器执行期间的异常将导致整个事务失败。An exception during the post-trigger execution will fail the whole transaction. 提交的任何内容都将回退并返回异常。Anything committed will be rolled back and an exception returned.

有关如何注册和调用前触发器的示例,请参阅前触发器后触发器文章。For examples of how to register and call a pre-trigger, see pre-triggers and post-triggers articles.

如何编写用户定义的函数How to write user-defined functions

以下示例创建一个 UDF 用于计算各个收入阶层的所得税。The following sample creates a UDF to calculate income tax for various income brackets. 然后,在查询中使用此用户定义的函数。This user-defined function would then be used inside a query. 此示例假设有一个名为“Incomes”的容器,其中包含以下属性:For the purposes of this example assume there is a container called "Incomes" with properties as follows:

{
   "name": "Satya Nadella",
   "country": "USA",
   "income": 70000
}

下面是用于计算各个收入阶层的所得税的函数定义:The following is a function definition to calculate income tax for various income brackets:

function tax(income) {

        if(income == undefined)
            throw 'no input';

        if (income < 1000)
            return income * 0.1;
        else if (income < 10000)
            return income * 0.2;
        else
            return income * 0.4;
    }

有关如何注册和使用用户定义的函数的示例,请参阅如何在 Azure Cosmos DB 中使用用户定义的函数一文。For examples of how to register and use a user-defined function, see How to use user-defined functions in Azure Cosmos DB article.

日志记录Logging

使用存储过程、触发器或用户定义的函数时,可以使用 console.log() 命令来记录步骤。When using stored procedure, triggers or user-defined functions, you can log the steps using the console.log() command. EnableScriptLogging 设置为 true 时,该命令会专注于一个字符串进行调试,如以下示例所示:This command will concentrate a string for debugging when EnableScriptLogging is set to true as shown in the following example:

var response = await client.ExecuteStoredProcedureAsync(
document.SelfLink,
new RequestOptions { EnableScriptLogging = true } );
Console.WriteLine(response.ScriptLog);

后续步骤Next steps

详细了解概念以及如何在 Azure Cosmos DB 中编写或使用存储过程、触发器和用户定义的函数:Learn more concepts and how-to write or use stored procedures, triggers, and user-defined functions in Azure Cosmos DB: