简单的Oracle变量SQL分配 [英] Simple Oracle variable SQL Assignment

问题描述

尽管花了一个小时进行研究，但我似乎无法弄清楚如何正确定义一个变量，然后在SQL中使用它.

Despite having spent an hour researching I can't seem to figure out how to correctly define a variable and then use it in your SQL.

这是我到目前为止所产生的:

This is what I have so far produced:

DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy');

我得到的答复:

ORA-06550:第1行，第63列:PLS-00103:出现符号 预期以下情况之一时为文件结束":

ORA-06550: line 1, column 63: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

开始功能包编译指示过程子类型类型使用当前表格 光标

begin function package pragma procedure subtype type use form current cursor

详细信息:DECLARE startDate DATE:= to_date('03/11/2011'， 'dd/mm/yyyy');第1行的错误ORA-06550:第1行，第63列: PLS-00103:预期出现以下情况之一时遇到符号文件结尾" 以下:

Details: DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy'); Error at line 1 ORA-06550: line 1, column 63: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

开始功能包编译指示过程子类型类型使用当前表格 光标

begin function package pragma procedure subtype type use form current cursor

我很想知道如何完成这样一个简单的任务！

I'd love to find out how to do such a simple task!

推荐答案

您的变量声明正确.

DECLARE关键字用于定义作用域为PL/SQL块(其主体由BEGIN和END;分隔)的变量.您想如何使用此变量?

The DECLARE keyword is used to define variables scoped in a PL/SQL block (whose body is delimited by BEGIN and END;). How do you want to use this variable?

以下PL/SQL对我来说很好用:

The following PL/SQL works fine for me:

DECLARE 
    startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy');
    reccount INTEGER;
BEGIN
    SELECT count(*) INTO reccount 
        FROM my_table tab 
        WHERE tab.somedate < startDate;
    dbms_output.put_line(reccount);
END;

您还可以使用 DEFINE 语句以使用简单的字符串替换变量.它们适用于SQL/PLUS或TOAD之类的客户端.

You can also use the DEFINE statement to use simple string substitution variables. They are suitable for a client like SQL/PLUS or TOAD.

DEFINE start_date = "to_date('03/11/2011', 'dd/mm/yyyy')"
SELECT COUNT(*) from my_table tab where tab.some_date < &start_date;

这篇关于简单的Oracle变量SQL分配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！