SQL 什么是即席查询？

What is Ad Hoc Query?

提问by zihado

I'm reading a book about SQL. In that book there's the term Ad Hoc Query, which I don't understand.

我正在阅读一本关于 SQL 的书。在那本书中有术语Ad Hoc Query，我不明白。

What exactly is an ad hoc query?

什么是临时查询？

回答by dxh

Ad hocis latin for "for this purpose". You might call it an "on the fly" query, or a "just so" query. It's the kind of SQL query you just loosely type out where you need it

Ad hoc是拉丁语，意为“为此目的”。您可以将其称为“即时”查询，或“只是这样”查询。这是一种 SQL 查询，您只需在需要的地方松散地键入

var newSqlQuery = "SELECT * FROM table WHERE id = " + myId;

...which is an entirely different query each time that line of code is executed, depending on the value of myId. The opposite of an ad hoc query is a predefined query such as a Stored Procedure, where you have created a single query for the entire generalized purpose of selecting from that table (say), and pass the ID as a variable.

...每次执行该行代码时，这是一个完全不同的查询，具体取决于 的值myId。与即席查询相反的是预定义查询，例如存储过程，您在其中创建了一个查询，用于从该表中进行选择的整个通用目的（例如），并将 ID 作为变量传递。

回答by haltunbay

An Ad-Hoc Query is a query that cannot be determined prior to the moment the query is issued. It is created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools.

Ad-Hoc 查询是在发出查询之前无法确定的查询。它是为了在需要时获取信息而创建的，它由动态构造的 SQL 组成，通常由桌面驻留的查询工具构造。

Check: http://www.learn.geekinterview.com/data-warehouse/dw-basics/what-is-an-ad-hoc-query.html

检查：http: //www.learn.geekinterview.com/data-warehouse/dw-basics/what-is-an-ad-hoc-query.html

回答by xli

Also want to add that ad hoc query is vulnerable to SQL injection attacks. We should try to avoid using it and use parameterized SQLs instead (like PreparedStatement in Java).

还想补充一点，即席查询容易受到 SQL 注入攻击。我们应该尽量避免使用它，而是使用参数化的 SQL（如 Java 中的 PreparedStatement）。

回答by Ketan Anand

An Ad-Hoc Query is a query that cannot be determined prior to the moment the query is issued. It is created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools. An ad hoc query does not reside in the computer or the database manager but is dynamically created depending on the needs of the data user.

Ad-Hoc 查询是在发出查询之前无法确定的查询。它是为了在需要时获取信息而创建的，它由动态构造的 SQL 组成，通常由桌面驻留的查询工具构造。即席查询不驻留在计算机或数据库管理器中，而是根据数据用户的需要动态创建的。

In SQL, an ad hoc query is a loosely typed command/query whose value depends upon some variable. Each time the command is executed, the result is different, depending on the value of the variable. It cannot be predetermined and usually comes under dynamic programming SQL query. An ad hoc query is short lived and is created at runtime.

在 SQL 中，即席查询是一种松散类型的命令/查询，其值取决于某个变量。每次执行命令，结果都不同，取决于变量的值。它不能预先确定，通常属于动态编程 SQL 查询。即席查询是短暂的，是在运行时创建的。

回答by PV22

An Ad-hoc queryis one created to provide a specific recordset from any or multiple merged tables available on the DB server. These queries usually serve a single-use purpose, and may not be necessary to incorporate into any stored procedure to run again in the future.

一个即席查询是一个建立提供具体的记录从任一或多个合并的DB服务器上的可用表。这些查询通常用于一次性用途，可能不需要合并到任何存储过程中以便将来再次运行。

Ad-hoc scenario: You receive a request for a specific subset of data with a unique set of variables. If there is no pre-written query that can provide the necessary results, you must write an Ad-hoc query to generate the recordset results.

Ad-hoc 方案：您收到对具有一组唯一变量的特定数据子集的请求。如果没有可以提供必要结果的预先编写的查询，则必须编写临时查询来生成记录集结果。

Beyond a single use Ad-hoc query are stored procedures; i.e. queries which are stored within the DB interface tool. These stored procedures can then be executed in sequence within a module or macro to accomplish a predefined task either on demand, on a schedule, or triggered by another event.

超越单次使用的即席查询是存储过程；即存储在数据库接口工具中的查询。然后可以在模块或宏中按顺序执行这些存储过程，以按需、按计划或由另一个事件触发来完成预定义的任务。

Stored Procedure scenario: Every month you need to generate a report from the same set of tables and with the same variables (these variables may be specific predefined values, computed values such as “end of current month”, or a user's input values). You would created the procedure as an ad-hoc query the first time. After testing the results to ensure accuracy, you may chose to deploy this query. You would then store the query or series of queries in a module or macro to run again as needed.

存储过程场景：每个月您都需要从相同的一组表和相同的变量（这些变量可能是特定的预定义值、计算值如“当月末”或用户输入值）生成报告。您将在第一次将过程创建为即席查询。在测试结果以确保准确性后，您可以选择部署此查询。然后，您可以将查询或一系列查询存储在模块或宏中，以便根据需要再次运行。

回答by A.HADDAD

Ad-hoc Statments are just T-SQL Statements that it has a Where Clause , and that Where clause can actualy have a literal like :

Ad-hoc Statments 只是 T-SQL 语句，它有一个 Where 子句，而 Where 子句实际上可以具有如下文字：

Select * from member where member_no=285;

or a variable :

或变量：

declare @mno INT=285;
Select * from member where member_no=@mno

回答by user3134258

Ad hoc queries are those that are not already defined that are not needed on a regular basis, so they're not included in the typical set of reports or queries

即席查询是那些尚未定义且不需要定期进行的查询，因此它们不包含在典型的报告或查询集中

回答by Ravi

An Ad-Hoc query is:

Ad-Hoc 查询是：

1. Pre-planned question.
2. Pre-scheduled question.
3. spur of the moment question.
4. Question that will not return any results.

1. 预先计划好的问题。
2. 预先安排的问题。
3. 一时兴起的问题。
4. 不会返回任何结果的问题。

回答by Sanyog Tiwari

Ad hoc query is type of computer definition. Which means this query is specially design to obtain any information when it is only needed. Predefined. refer this https://www.youtube.com/watch?v=0c8JEKmVXhU

即席查询是计算机定义的类型。这意味着此查询是专门设计用于仅在需要时获取任何信息。预定义。参考这个https://www.youtube.com/watch?v=0c8JEKmVXhU

回答by Expansion

In Sql Server "Ad Hoc Query" is also used for Ad Hoc Distributed Queries. These are ad hoc queries on an other server via OpenRowset or OpenDatasource. Ad Hoc Distributed Queries are only allowed after configuring so. This is a Server configuration option.

在 Sql Server 中，“Ad Hoc Query”也用于 Ad Hoc 分布式查询。这些是通过 OpenRowset 或 OpenDatasource 在其他服务器上的即席查询。Ad Hoc Distributed Queries 仅在配置后才允许。这是服务器配置选项。