KnowForge/Apache DataFusion 简体中文
SQL 参考

预处理语句

师成师成· 更新于 2026-09-28· 阅读 5 分钟· 0 次阅读
登录后可跨设备保存划线和私人笔记登录

预处理语句

PREPARE 语句用于创建并存储带有占位参数的 SQL 语句。

之后，这些预处理语句可以被高效地反复执行。

SQL 示例

创建一个名为 greater_than 的预处理语句，用于选择列 “a” 大于该参数的所有记录：

PREPARE greater_than(INT) AS SELECT * FROM example WHERE a > $1;

预处理语句随后可根据需要带参数执行：

EXECUTE greater_than(20);

Rust 示例

use datafusion::prelude::*;

#[tokio::main]
async fn main() -> datafusion::error::Result<()> {
  // Register the table
  let ctx = SessionContext::new();
  ctx.register_csv("example", "tests/data/example.csv", CsvReadOptions::new()).await?;

  // Create the prepared statement `greater_than`
  let prepare_sql = "PREPARE greater_than(INT) AS SELECT * FROM example WHERE a > $1";
  ctx.sql(prepare_sql).await?;

  // Execute the prepared statement `greater_than`
  let execute_sql = "EXECUTE greater_than(20)";
  let df = ctx.sql(execute_sql).await?;

  // Execute and print results
  df.show().await?;
  Ok(())
}

推断类型

如果未指定参数类型，则可以在执行时进行推断：

SQL 示例

创建预处理语句 greater_than

PREPARE greater_than AS SELECT * FROM example WHERE a > $1;

执行预处理语句 greater_than

EXECUTE greater_than(20);

Rust 示例

    // Create the prepared statement `greater_than`
    let prepare_sql = "PREPARE greater_than AS SELECT * FROM example WHERE a > $1";
    ctx.sql(prepare_sql).await?;

    // Execute the prepared statement `greater_than`
    let execute_sql = "EXECUTE greater_than(20)";
    let df = ctx.sql(execute_sql).await?;

位置参数

当存在多个参数时，预处理语句可以使用位置参数：

SQL 示例

创建预处理语句 greater_than

PREPARE greater_than(INT, DOUBLE) AS SELECT * FROM example WHERE a > $1 AND b > $2;

执行预处理语句 greater_than

EXECUTE greater_than(20, 23.3);

Rust 示例

  // Create the prepared statement `greater_than`
  let prepare_sql = "PREPARE greater_than(INT, DOUBLE) AS SELECT * FROM example WHERE a > $1 AND b > $2";
  ctx.sql(prepare_sql).await?;

  // Execute the prepared statement `greater_than`
  let execute_sql = "EXECUTE greater_than(20, 23.3)";
  let df = ctx.sql(execute_sql).await?;

评论

登录后参与评论

正在加载评论…