声明&在 Select 语句中设置变量

2021-12-24 00:00:00 sql variables oracle plsql

我正在尝试编写一个简单的查询，在其中声明一些变量，然后在 Oracle 的选择语句中使用它们.我之前在 SQL Server 中已经能够做到这一点:

I'm attempting to write a simple query where I declare some variables and then use them in a select statement in Oracle. I've been able to do this before in SQL Server with the following:

DECLARE @date1   DATETIME
SET @date1 = '03-AUG-2010'

SELECT U.VisualID
FROM Usage u WITH(NOLOCK)
WHERE U.UseTime > @Date1

从我所做的搜索来看，您似乎无法在 Select 语句中声明和设置这样的变量.这是正确的还是我在发什么信息?

From the searching I've done it appears you can not declare and set variables like this in Select statements. Is this right or am I mssing something?

推荐答案

从我所做的搜索来看，您不能在 Select 语句中声明和设置这样的变量.这是正确的还是我遗漏了什么?

在 Oracle PL/SQL 和 SQL 中是两种独立的语言，具有两个独立的引擎.您可以在 PL/SQL 中嵌入 SQL DML，这将为您提供变量.比如下面的匿名PL/SQL块.注意最后的 / 不是 PL/SQL 的一部分，而是告诉 SQL*Plus 发送前面的块.

Within Oracle PL/SQL and SQL are two separate languages with two separate engines. You can embed SQL DML within PL/SQL, and that will get you variables. Such as the following anonymous PL/SQL block. Note the / at the end is not part of PL/SQL, but tells SQL*Plus to send the preceding block.

declare 
    v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');
    v_Count number;
begin
    select count(*) into v_Count
    from Usage
    where UseTime > v_Date1;
    dbms_output.put_line(v_Count);
end;
/

问题是与您的 T-SQL 代码等效的块将不起作用:

The problem is that a block that is equivalent to your T-SQL code will not work:

SQL> declare 
  2      v_Date1 date := to_date('03-AUG-2010', 'DD-Mon-YYYY');
  3  begin
  4      select VisualId
  5      from Usage
  6      where UseTime > v_Date1;
  7  end;
  8  /
    select VisualId
    *
ERROR at line 4:
ORA-06550: line 4, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement

要将查询的结果传递出 PL/SQL，无论是匿名块、存储过程还是存储函数，都必须声明、打开游标，然后将其返回给调用程序.(超出了回答这个问题的范围.请参阅获取oracle存储过程的结果集)

To pass the results of a query out of an PL/SQL, either an anonymous block, stored procedure or stored function, a cursor must be declared, opened and then returned to the calling program. (Beyond the scope of answering this question. see Get resultset from oracle stored procedure)

连接到数据库的客户端工具可能有自己的绑定变量.在 SQL*Plus 中:

The client tool that connects to the database may have it's own bind variables. In SQL*Plus:

SQL> -- SQL*Plus does not all date type in this context
SQL> -- So using varchar2 to hold text
SQL> variable v_Date1 varchar2(20)
SQL>
SQL> -- use PL/SQL to set the value of the bind variable
SQL> exec :v_Date1 := '02-Aug-2010';

PL/SQL procedure successfully completed.

SQL> -- Converting to a date, since the variable is not yet a date.
SQL> -- Note the use of colon, this tells SQL*Plus that v_Date1
SQL> -- is a bind variable.
SQL> select VisualId
  2  from Usage
  3  where UseTime > to_char(:v_Date1, 'DD-Mon-YYYY');

no rows selected

注意以上是在 SQLPlus 中，可能(可能不会)在 Toad PL/SQL 开发人员等中工作.以变量和 exec 开头的行是 SQLPlus 命令.它们不是 SQL 或 PL/SQL 命令.由于表为空，因此未选择任何行.

Note the above is in SQLPlus, may not (probably won't) work in Toad PL/SQL developer, etc. The lines starting with variable and exec are SQLPlus commands. They are not SQL or PL/SQL commands. No rows selected because the table is empty.

相关文章