教程:使用 TRANSACT-SQL 编辑器来创建数据库对象, Azure Data StudioAzure Data StudioTutorial: Use the Transact-SQL editor to create database objects - Azure Data StudioAzure Data Studio

创建并运行查询、存储过程、脚本等是数据库专业人员的核心任务。Creating and running queries, stored procedures, scripts, etc. are the core tasks of database professionals. 本教程演示如何在 T-SQL 编辑器中创建数据库对象的主要功能。This tutorial demonstrates the key features in the T-SQL editor to create database objects.

您可以在本教學課程中,了解如何使用 Azure Data StudioAzure Data Studio 來完成下列工作:In this tutorial, you learn how to use Azure Data StudioAzure Data Studio to:

  • 搜索数据库对象Search database objects
  • 编辑表数据Edit table data
  • 使用代码段快速编写 T-SQLUse snippets to quickly write T-SQL
  • 使用查看数据库对象详细信息速览定义转到定义View database object details using Peek Definition and Go to Definition

必要條件Prerequisites

本教程需要安装 SQL Server 或 Azure SQL 数据库TutorialDBThis tutorial requires the SQL Server or Azure SQL Database TutorialDB. 若要创建TutorialDB数据库,请完成以下快速入门之一:To create the TutorialDB database, complete one of the following quickstarts:

快速找到数据库对象并执行常见任务Quickly locate a database object and perform a common task

Azure Data StudioAzure Data Studio 提供了搜索小组件以快速查找数据库对象。provides a search widget to quickly find database objects. 结果列表中与所选对象相关的常见任务提供了上下文菜单等编辑数据表。The results list provides a context menu for common tasks relevant to the selected object, such as Edit Data for a table.

  1. 打开服务器侧栏 (Ctrl + G),展开数据库,然后选择TutorialDBOpen the SERVERS sidebar (Ctrl+G), expand Databases, and select TutorialDB.

  2. 打开TutorialDB 仪表板通过右击TutorialDB ,然后选择管理从上下文菜单:Open the TutorialDB Dashboard by right-clicking TutorialDB and selecting Manage from the context menu:

    上下文菜单-管理

  3. 在仪表板中,右键单击dbo。客户(在搜索小组件),然后选择编辑数据On the dashboard, right-click dbo.Customers (in the search widget) and select Edit Data.

    提示

    对于具有多个对象的数据库,使用搜索小组件来快速查找表、 视图、 你正在寻找的等。For databases with many objects, use the search widget to quickly locate the table, view, etc. that you're looking for.

    快速搜索小组件

  4. 编辑电子邮件列中的第一行,类型 *orlando0@adventure-works.com* ,然后按Enter以保存更改。Edit the Email column in the first row, type *orlando0@adventure-works.com*, and press Enter to save the change.

    编辑数据

使用 T-SQL 代码段创建存储的过程Use T-SQL snippets to create stored procedures

Azure Data StudioAzure Data Studio 用于快速创建语句提供了许多内置的 T-SQL 代码片段。provides many built-in T-SQL snippets for quickly creating statements.

  1. 按打开新查询编辑器Ctrl + NOpen a new query editor by pressing Ctrl+N.

  2. 类型sql在编辑器中,向下箭头sqlCreateStoredProcedure,然后按选项卡密钥 (或Enter) 以加载创建存储过程代码段。Type sql in the editor, arrow down to sqlCreateStoredProcedure, and press the Tab key (or Enter) to load the create stored procedure snippet.

    snippet-list

  3. 创建存储的过程代码片段有两个字段设置以进行快速编辑StoredProcedureNameSchemaNameThe create stored procedure snippet has two fields set up for quick edit, StoredProcedureName and SchemaName. 选择StoredProcedureName,右键单击,然后选择更改所有匹配项Select StoredProcedureName, right-click, and select Change All Occurrences. 现在,键入getCustomer和全部StoredProcedureName条目更改为getCustomerNow type getCustomer and all StoredProcedureName entries change to getCustomer.

    代码片段

  4. 更改所有匹配项SchemaNamedboChange all occurrences of SchemaName to dbo.

  5. 代码段中包含占位符参数和需要更新的正文文本。The snippet contains placeholder parameters and body text that needs updating. EXECUTE语句还包含占位符文本,因为它不知道该过程将具有的参数的数目。The EXECUTE statement also contains placeholder text because it doesn't know how many parameters the procedure will have. 对于本教程中更新该代码段因此看起来如以下代码:For this tutorial update the snippet so it looks like the following code:

    -- Create a new stored procedure called 'getCustomer' in schema 'dbo'
    -- Drop the stored procedure if it already exists
    IF EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE SPECIFIC_SCHEMA = N'dbo'
    AND SPECIFIC_NAME = N'getCustomer'
    )
    DROP PROCEDURE dbo.getCustomer
    GO
    -- Create the stored procedure in the specified schema
    CREATE PROCEDURE dbo.getCustomer
    @ID int
    -- add more stored procedure parameters here
    AS
    -- body of the stored procedure
    SELECT  c.CustomerId, 
    c.Name, 
    c.Location, 
    c.Email
    FROM dbo.Customers c
    WHERE c.CustomerId = @ID
    FOR JSON PATH
    
    GO
    -- example to execute the stored procedure we just created
    EXECUTE dbo.getCustomer 1
    GO
    
  6. 若要创建存储的过程并对其进行测试运行,按F5To create the stored procedure and give it a test run, press F5.

现已创建存储的过程,并结果窗格将显示在 JSON 中返回的客户。The stored procedure is now created, and the RESULTS pane displays the returned customer in JSON. 若要查看格式化的 JSON,请单击返回的记录。To see formatted JSON, click the returned record.

使用查看定义Use Peek Definition

Azure Data StudioAzure Data Studio 提供的功能,若要查看使用窥视定义功能的对象定义。provides the ability to view an objects definition using the peek definition feature. 本部分中创建第二个存储的过程,并使用查看定义查看一个表以快速创建存储过程的正文中包括的列。This section creates a second stored procedure and uses peek definition to see what columns are in a table to quickly create the body of the stored procedure.

  1. 按打开的新编辑器Ctrl + NOpen a new editor by pressing Ctrl+N.

  2. 类型sql在编辑器中,向下箭头sqlCreateStoredProcedure,然后按选项卡密钥 (或Enter) 以加载创建存储过程代码段。Type sql in the editor, arrow down to sqlCreateStoredProcedure, and press the Tab key (or Enter) to load the create stored procedure snippet.

  3. 在键入setCustomer有关StoredProcedureNamedboSchemaNameType in setCustomer for StoredProcedureName and dbo for SchemaName

  4. 替换为@param占位符替换以下参数定义:Replace the @param placeholders with the following parameter definition:

    @json_val nvarchar(max)
    
  5. 存储过程的主体替换为以下代码:Replace the body of the stored procedure with the following code:

    INSERT INTO dbo.Customers
    
  6. 在中插入行只是添加了,右键单击dbo。客户,然后选择查看定义In the INSERT line you just added, right-click dbo.Customers and select Peek Definition.

    查看定义

  7. 表定义会显示您可以快速查看表中包括的列。The table definition appears so you can quickly see what columns are in the table. 请参阅要轻松地填写你的存储过程的语句的列列表。Refer to the column list to easily complete the statements for your stored procedure. 完成创建您以前添加来完成的存储过程的正文并关闭速览定义窗口的 INSERT 语句:Finish creating the INSERT statement you added previously to complete the body of the stored procedure, and close the peek definition window:

    INSERT INTO dbo.Customers (CustomerId, Name, Location, Email)
        SELECT CustomerId, Name, Location, Email
        FROM OPENJSON (@json_val)
        WITH(   CustomerId int, 
                Name nvarchar(50), 
                Location nvarchar(50), 
                Email nvarchar(50)
     )
    
  8. 刪除 (或註解)查詢底下的 EXECUTE 命令。Delete (or comment out) the EXECUTE command at the bottom of the query.

  9. 整个语句应如以下代码所示:The entire statement should look like the following code:

    -- Create a new stored procedure called 'setCustomer' in schema 'dbo'
    -- Drop the stored procedure if it already exists
    IF EXISTS (
    SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE SPECIFIC_SCHEMA = N'dbo'
        AND SPECIFIC_NAME = N'setCustomer'
    )
    DROP PROCEDURE dbo.setCustomer
    GO
    -- Create the stored procedure in the specified schema
    CREATE PROCEDURE dbo.setCustomer
        @json_val nvarchar(max) 
    AS
        -- body of the stored procedure
        INSERT INTO dbo.Customers (CustomerId, Name, Location, Email)
        SELECT CustomerId, Name, Location, Email
        FROM OPENJSON (@json_val)
        WITH(   CustomerId int, 
                Name nvarchar(50), 
                Location nvarchar(50), 
                Email nvarchar(50)
        )
    GO
    
  10. 若要创建setCustomer存储过程中,按F5To create the setCustomer stored procedure, press F5.

使用将查询结果保存为 JSON,以便测试 setCustomer 存储过程Use save query results as JSON to test the setCustomer stored procedure

SetCustomer在上一节中创建的存储的过程需要 JSON 数据传递到 *@json_val* 参数。The setCustomer stored procedure created in the previous section requires JSON data be passed into the *@json_val* parameter. 本部分演示如何获取少量格式正确的 JSON 传递到参数,以便可以测试存储的过程。This section shows how to get a properly formatted bit of JSON to pass into the parameter so you can test the stored procedure.

  1. 在中服务器右键单击侧栏dbo。客户表,然后单击选择前 1000年行In the SERVERS sidebar right-click the dbo.Customers table and click SELECT TOP 1000 Rows.

  2. 在结果视图中选择的第一行,请确保选择整行 (单击最左边的列中的数字 1),然后选择另存为 JSONSelect the first row in the results view, make sure the entire row is selected (click the number 1 in the left-most column), and select Save as JSON.

  3. 将文件夹更改为你会记得以便可以删除的文件更高版本 (对于示例桌面版) 并单击的位置保存Change the folder to a location you'll remember so you can delete the file later (for example desktop) and click Save. JSON 格式设置文件随即打开。The JSON formatted file opens.

    将另存为 JSON

  4. 在编辑器中选择的 JSON 数据并将其复制。Select the JSON data in the editor and copy it.

  5. 按打开的新编辑器Ctrl + NOpen a new editor by pressing Ctrl+N.

  6. 上一步骤演示了如何轻松地获取格式正确的数据,以完成对调用setCustomer过程。The previous steps show how you can easily get the properly formatted data to complete the call to the setCustomer procedure. 您可以看到下面的代码与新客户详细信息使用相同的 JSON 格式,因此我们可以测试setCustomer过程。You can see the following code uses the same JSON format with new customer details so we can test the setCustomer procedure. 语句包含语法来声明参数和运行新的 get 和 set 过程。The statement includes syntax to declare the parameter and run the new get and set procedures. 可以粘贴前一部分中复制的数据并对其进行编辑,因此,以下示例中,与相同或只需将以下语句粘贴到查询编辑器。You can paste the copied data from the previous section and edit it so it is the same as the following example, or simply paste the following statement into the query editor.

    -- example to execute the stored procedure we just created
    declare @json nvarchar(max) =
    N'[
        {
            "CustomerId": 5,
            "Name": "Lucy",
            "Location": "Canada",
            "Email": "lucy0@adventure-works.com"
        }
    ]'
    
    EXECUTE dbo.setCustomer @json_val = @json
    GO
    
    EXECUTE dbo.getCustomer @ID = 5
    
  7. 执行该脚本通过按F5Execute the script by pressing F5. 该脚本插入新客户,并以 JSON 格式返回新客户的信息。The script inserts a new customer and returns the new customer's information in JSON format. 单击要打开格式化的视图的结果。Click the result to open a formatted view.

    测试结果

后续步骤Next steps

在本教程中,你将了解:In this tutorial, you learned how to:

  • 快速搜索架构对象Quick search schema objects
  • 编辑表数据Edit table data
  • 编写使用代码段的 T-SQL 脚本Writing T-SQL script using snippets
  • 了解如何使用查看定义的数据库对象详细信息,并转到定义Learn about database object details using Peek Definition and Go to Definition

若要了解如何启用五个最慢的查询小组件中,完成下一教程:To learn how to enable the five slowest queries widget, complete the next tutorial: