使用 InterSystems IRIS 的动态和嵌入式 SQL

文章

Nicky Zhu · 14 hr 前阅读大约需 9 分钟

大家好！

在本文中，我们将在 InterSystems IRIS 的背景下探讨动态 SQL（Dynamic SQL）和嵌入式 SQL（Embedded SQL ）的概念，提供实际示例， 并研究它们的区别，以帮助您了解如何在应用程序中利用它们。

InterSystems SQL 提供全套标准关系功能，包括定义表模式、执行查询、定义和执行存储过程。您可以通过管理门户交互式地执行 InterSystems SQL，也可以使用 SQL shell 界面以编程方式执行 InterSystems SQL。嵌入式 SQL 使您可以在 ObjectScript 代码中嵌入 SQL 语句，而动态 SQL 使您可以在运行时从 ObjectScript 执行动态 SQL 语句。静态 SQL 查询提供可预测的性能，而动态和嵌入式 SQL 则分别提供灵活性和集成性。

动态 SQL

动态 SQL 是指在运行时构建和执行的 SQL 语句，而静态 SQL 是预先定义并直接嵌入到应用程序代码中的。当查询结构事先不知道或需要根据用户输入或应用程序逻辑进行动态调整时，动态 SQL 尤其有用。

在 InterSystems IRIS 中，动态 SQL 通过 %SQL.Statement 类实现，该类提供了动态准备和执行 SQL 语句的方法。

动态 SQL 的主要优势

灵活性：动态 SQL 允许以编程方式建立查询，因此非常适合具有复杂或不断变化要求的应用程序。
适应性强：您可以根据运行时条件（如用户输入或应用程序状态）修改查询。
临时查询：如果应用程序需要根据用户输入生成自定义查询，动态 SQL 允许在运行时构建这些查询。
复杂连接和条件：在连接或条件的数量会根据数据发生变化的情况下，动态 SQL 可以构建复杂的查询。

实际案例

1. 动态表创建：快速构建数据库模式

该示例演示了如何使用 InterSystems Dynamic SQL 在运行时动态创建表，从而实现灵活、自适应的数据库模式管理。

ClassMethod CreateDynamicTable(tableName As %String, columns As %String) As %Status
{
    // Construct sql text
    Set sql = "CREATE TABLE " _ tableName _ " (" _ columns _ ")"
    //Create an instance of %SQL.Statement
    Set statement = ##class(%SQL.Statement).%New()
    //Prepare the query
    Set status = statement.%Prepare(sql)
    If $$$ISERR(status) {
        Quit status
    }
    //Execute the query
    Set result = statement.%Execute()
    //Check for errors
    If result.%SQLCODE = 0 {
        Write "Table created successfully!", !
    } Else {
        Write "Error: ", result.%SQLCODE, " ", result.%SQLMSG, !
    }
    Quit $$$OK
}

调用方法

USER>do ##class(dc.DESql).CreateDynamicTable("Books","BookID NUMBER NOT NULL,Title VARCHAR(100),Author VARCHAR(300),PublicationYear  NUMBER NULL, AvailableFlag  BIT")

输出

2. 动态表搜索：使用用户定义的过滤器查询数据

本示例说明如何根据用户定义的条件执行动态表搜索，从而实现灵活、可调整的查询。

ClassMethod DynamicSearchPerson(name As %String = "", age As %Integer = "") As %Status
{
    // Create an instance of %SQL.Statement
    set stmt = ##class(%SQL.Statement).%New()

    // Base query
    set query = "SELECT ID, Name, Age, DOB FROM Sample.Person"
    // Add conditions based on input parameters
    if name '= "" {
        set query = query _ " WHERE Name %STARTSWITH ?"
    }
    if (age '= "") && (name '= "") {
        set query = query _ " AND Age = ?"
    }
    if (age '= "") && (name = "") {
        set query = query _ " WHERE Age = ?"
    }
    
    // Prepare the query
    set status = stmt.%Prepare(query)
    if $$$ISERR(status) {
        do $System.Status.DisplayError(status)
        quit status
    }
   
    // Execute the query with parameters
    if (age '= "") && (name '= "") {
        set rset = stmt.%Execute(name, age)
    }
    if (age '= "") && (name = "") {
        set rset = stmt.%Execute(age)
    }
    if (age = "") && (name '= "") {
        set rset = stmt.%Execute(name)
    }

    // Display results
    while rset.%Next() {
        write "ID: ", rset.ID, " Name: ", rset.Name, " Age: ", rset.Age,  !
    }

    quit $$$OK
}

调用方法

do ##class(dc.DESql).DynamicSearchPerson("Y",67)

输出

3. 动态透视表：转换数据以获得分析见解

该示例展示了如何使用 InterSystems Dynamic SQL 动态生成数据透视表，将原始数据转换为结构化摘要。

ClassMethod GeneratePivotTable(tableName As %String, rowDimension As %String, columnDimension As %String, valueColumn As %String) As %Status
{
    // Simplified example; real pivot tables can be complex
    Set sql = "SELECT " _ rowDimension _ ", " _ columnDimension _ ", SUM(" _ valueColumn _ ") FROM " _ tableName _ " GROUP BY " _ rowDimension _ ", " _ columnDimension
    //Create an instance of %SQL.Statement
    Set statement = ##class(%SQL.Statement).%New()
    // Prepare the query
    Set status = statement.%Prepare(sql)
   
    If $$$ISERR(status) {
        Quit status
    }
    // Execute the query
    Set result = statement.%Execute()
    // Check for errors
    If result.%SQLCODE = 0 {
        While result.%Next() {
            do result.%Display()
        }
    } Else {
        Write "Error: ", result.%SQLCODE, " ", result.%SQLMSG, !
    }
    Quit $$$OK
}

调用方法

Do ##class(dc.DESql).GeneratePivotTable("Sales", "Region", "ProductCategory", "Revenue")

输出

4. 模式探索：使用动态 SQL 解锁数据库元数据

本示例演示如何动态探索和检索数据库模式的元数据，从而深入了解表结构和列定义。

ClassMethod ExploreTableSchema(tableName As %String) As %Status
{
    // Create a new SQL statement object
    set stmt = ##class(%SQL.Statement).%New()
    
    // Construct the query dynamically
    set sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA||'.'||TABLE_NAME = ?"
    // Prepare the query
    set status = stmt.%Prepare(sql)
    if $$$ISERR(status) {
        do $System.Status.DisplayError(status)
        quit status
    }
   
    // Execute the query
    set result = stmt.%Execute(tableName)
    
    // Display schema information
    write !, "Schema for Table: ", tableName
    write !, "-------------------------"
    write !, "Column Name",?15, "Data Type", ?30, "Nullable ",?45,"Column#"
    write !, "-------------------------"
    while result.%Next() {
        
        write !, result.%Get("COLUMN_NAME"),?15, result.%Get("DATA_TYPE"), ?30,  result.%Get("IS_NULLABLE"), ?45,result.%Get("ORDINAL_POSITION") 
        
    }
    
    quit $$$OK
}

调用方法

Do ##class(dc.DESql).ExploreTableSchema("Sample.Person")

输出

嵌入式 SQL

嵌入式 SQL 是一种在编程语言（这里指 ObjectScript 或其他 InterSystems 兼容语言）中直接包含 SQL 语句的方法。在编译包含嵌入式 SQL 的例程时，不会编译嵌入式 SQL。当与 InterSystems IRIS 的对象访问功能结合使用时，嵌入式 SQL 的编译将非常强大。

您可以在 InterSystems IRIS® 数据平台使用的 ObjectScript 代码中嵌入 SQL 语句。嵌入式 SQL 特别适用于执行查询、插入、更新和删除记录等数据库操作。

嵌入式 SQL 有两种：

简单的嵌入式 SQL 查询只能从单行中返回值。简单嵌入式 SQL 也可用于单行插入、更新和删除以及其他 SQL 操作。
基于游标的嵌入式 SQL 查询可以遍历查询结果集，返回多行的值。基于游标的嵌入式 SQL 也可用于多行更新和删除 SQL 操作。

嵌入式 SQL 的主要优势

无缝集成：嵌入式 SQL 允许您直接在 ObjectScript 代码中编写 SQL 语句，无需外部调用或复杂的接口。
性能：通过在 ObjectScript 中嵌入 SQL，您可以优化数据库交互并减少开销。
简单：嵌入式 SQL 可简化数据库工作流程，因为它无需单独的 SQL 脚本或外部工具。
错误处理：嵌入式 SQL 允许更好地处理错误，因为 SQL 代码是应用程序逻辑的一部分。

实际案例

1. 记录创建：使用嵌入式 SQL 插入数据

本示例演示如何使用嵌入式 SQL 向表中插入新记录，确保无缝数据集成。

ClassMethod AddBook(bookID As %Integer, title As %String, author As %String, year As %Integer, available As %Boolean) As %Status
{
    // Embedded SQL to insert a new book
    &sql(
        INSERT INTO SQLUser.Books (BookID, Title, Author, PublicationYear, AvailableFlag)
        VALUES (:bookID, :title, :author, :year, :available)
    )

    // Check for errors
    if SQLCODE '= 0 {
        write "Error inserting book: ", %msg, !
        quit $$$ERROR($$$GeneralError, "Insert failed")
    }

    write "Book added successfully!", !
    quit $$$OK
}

调用方法

Do ##class(dc.DESql).AddBook(1,"To Kill a Mockingbird","Harper Lee", 1960,1)

输出

2. Data Retrieval（数据检索）：使用嵌入式 SQL 获取和显示记录

此示例使用嵌入式 SQL 从数据库中获取图书列表，展示了如何高效地获取和显示数据。

ClassMethod ListBooks()
{
    // Embedded SQL to query books
    &sql(
        DECLARE BookCursor CURSOR FOR
        SELECT BookID, Title, Author, PublicationYear, AvailableFlag
        FROM SQLUser.Books
        WHERE AvailableFlag = 1
    )

    // Open the cursor
    &sql(OPEN BookCursor)

    // Fetch and display results
    for {
        &sql(FETCH BookCursor INTO :bookID, :title, :author, :year, :available)
        quit:(SQLCODE '= 0)

        write "Book ID: ", bookID, !
        write "Title: ", title, !
        write "Author: ", author, !
        write "Publication Year: ", year, !
        write "Available: ", available, !
        write "-----------------------------", !
    }

    // Close the cursor
    &sql(CLOSE BookCursor)
}

调用方

Do ##class(dc.DESql).ListBooks()

输出

3. 事务管理：使用嵌入式 SQL 确保数据完整性

本示例演示了如何使用嵌入式 SQL 管理数据库事务，确保资金转移过程中的数据完整性。

ClassMethod TransferFunds(fromAccount As %Integer, toAccount As %Integer, amount As %Decimal) As %Status
{
    // Start a transaction
    TSTART
    // Deduct amount from the source account
    &sql(UPDATE Accounts
         SET Balance = Balance - :amount
         WHERE AccountID = :fromAccount)
    
    if SQLCODE '= 0 {
        TROLLBACK
        quit $$$ERROR($$$GeneralError, "Failed to deduct amount from source account.")
    }
    
    // Add amount to the destination account
    &sql(UPDATE Accounts
         SET Balance = Balance + :amount
         WHERE AccountID = :toAccount)
    
    if SQLCODE '= 0 {
        TROLLBACK
        quit $$$ERROR($$$GeneralError, "Failed to add amount to destination account.")
    }
    
    // Commit the transaction
    TCOMMIT
    write !, "Funds transferred successfully."
    quit $$$OK
}

调用方法

do ##class(MyApp.FundManager).TransferFunds(101, 102, 500.00)

输出

4. 验证用户名可用性

此示例通过查询数据库确保用户名不存在，从而检查用户名是否可用。

ClassMethod ValidateUserName(username As %String) As %Boolean
{
    // Embedded SQL to check if the username exists
    &sql(SELECT COUNT(*) INTO :count
         FROM SQLUser.Users
         WHERE Name = :username)
    //Check for errors
    if SQLCODE = 0 {
        if count > 0 {
            write !, "Username already exists."
            quit 0
        } else {
            write !, "Username is available."
            quit 1
        }
    } else {
        write !, "Error validating username: ", %msg
        quit 0
    }
}

调用方法

Do ##class(dc.DESql).ValidateUserName("Admin")

输出

动态 SQL 与嵌入式 SQL 的比较

结论

动态 SQL 和嵌入式 SQL 是 InterSystems IRIS 中的强大工具，可满足不同的使用情况。动态 SQL 为运行时查询构建提供了灵活性，而嵌入式 SQL 则为静态查询提供了性能优势。通过了解它们的优势并将其有效结合，您可以在 InterSystems IRIS 平台上构建强大而高效的应用程序。

感谢阅读！

查看原帖由 @Muhammad Waseem 撰写