当前位置： 文江博客话题详情

如何声明变量并在同一个 Oracle SQL 脚本中使用它？

发布于 2024-09-15 21:35:11 字数 527 浏览 6 评论 0原文

我想编写可重用的代码，需要在开头声明一些变量并在脚本中重用它们，例如：

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;

如何声明变量并在后面的语句中重用它，例如使用它 SQLDeveloper。

尝试

  • 使用 DECLARE 部分并在 BEGIN 和 END; 中插入以下 SELECT 语句。使用 &stupidvar 访问变量。
  • 使用关键字DEFINE并访问该变量。
  • 使用关键字VARIABLE并访问变量。

但我在尝试期间遇到了各种错误（未绑定变量、语法错误、预期 SELECT INTO...）。

原文

I want to write reusable code and need to declare some variables at the beginning and reuse them in the script, such as:

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;

How can I declare a variable and reuse it in statements that follow such as in using it SQLDeveloper.

Attempts

  • Use a DECLARE section and insert the following SELECT statement in BEGIN and END;. Acces the variable using &stupidvar.
  • Use the keyword DEFINE and access the variable.
  • Using the keyword VARIABLE and access the the variable.

But I am getting all kinds of errors during my tries (Unbound variable, Syntax error, Expected SELECT INTO...).

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（11）

烟织青萝梦 2024-09-22 21:35:11

在 SQL*Plus 脚本中声明变量的方法有多种。

第一种是使用 VAR，声明绑定变量。为 VAR 赋值的机制是通过 EXEC 调用：

SQL> var name varchar2(20)
SQL> exec :name := 'SALES'

PL/SQL procedure successfully completed.

SQL> select * from dept
  2  where dname = :name
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL>

当我们想要调用具有 OUT 参数或函数的存储过程时，VAR 特别有用。

或者我们可以使用替代变量。这些对于交互模式很有用：

SQL> accept p_dno prompt "Please enter Department number: " default 10
Please enter Department number: 20
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 20

ENAME             SAL
---------- ----------
CLARKE            800
ROBERTSON        2975
RIGBY            3000
KULASH           1100
GASPAROTTO       3000

SQL>

当我们编写一个调用其他脚本的脚本时，预先定义变量会很有用。该代码片段运行时不会提示我输入值：

SQL> def p_dno = 40
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 40

no rows selected

SQL>

最后是匿名 PL/SQL 块。如您所见，我们仍然可以交互式地为声明的变量赋值：

SQL> set serveroutput on size unlimited
SQL> declare
  2      n pls_integer;
  3      l_sal number := 3500;
  4      l_dno number := &dno;
  5  begin
  6      select count(*)
  7      into n
  8      from emp
  9      where sal > l_sal
 10      and deptno = l_dno;
 11      dbms_output.put_line('top earners = '||to_char(n));
 12  end;
 13  /
Enter value for dno: 10
old   4:     l_dno number := &dno;
new   4:     l_dno number := 10;
top earners = 1

PL/SQL procedure successfully completed.

SQL>

There are a several ways of declaring variables in SQL*Plus scripts.

The first is to use VAR, to declare a bind variable. The mechanism for assigning values to a VAR is with an EXEC call:

SQL> var name varchar2(20)
SQL> exec :name := 'SALES'

PL/SQL procedure successfully completed.

SQL> select * from dept
  2  where dname = :name
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL>

A VAR is particularly useful when we want to call a stored procedure which has OUT parameters or a function.

Alternatively we can use substitution variables. These are good for interactive mode:

SQL> accept p_dno prompt "Please enter Department number: " default 10
Please enter Department number: 20
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 20

ENAME             SAL
---------- ----------
CLARKE            800
ROBERTSON        2975
RIGBY            3000
KULASH           1100
GASPAROTTO       3000

SQL>

When we're writing a script which calls other scripts it can be useful to DEFine the variables upfront. This snippet runs without prompting me to enter a value:

SQL> def p_dno = 40
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 40

no rows selected

SQL>

Finally there's the anonymous PL/SQL block. As you see, we can still assign values to declared variables interactively:

SQL> set serveroutput on size unlimited
SQL> declare
  2      n pls_integer;
  3      l_sal number := 3500;
  4      l_dno number := &dno;
  5  begin
  6      select count(*)
  7      into n
  8      from emp
  9      where sal > l_sal
 10      and deptno = l_dno;
 11      dbms_output.put_line('top earners = '||to_char(n));
 12  end;
 13  /
Enter value for dno: 10
old   4:     l_dno number := &dno;
new   4:     l_dno number := 10;
top earners = 1

PL/SQL procedure successfully completed.

SQL>
回复 原文
找回味觉 2024-09-22 21:35:11

如果它是 char 变量，请尝试使用双引号：

DEFINE stupidvar = "'stupidvarcontent'";

或

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata  
FROM stupidtable  
WHERE stupidcolumn = '&stupidvar'

upd：

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 25 17:13:26 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn od/od@etalon
Connected.
SQL> define var = "'FL-208'";
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = 'FL-208'

CODE
---------------
FL-208

SQL> define var = 'FL-208';
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = FL-208
select code from product where code = FL-208
                                      *
ERROR at line 1:
ORA-06553: PLS-221: 'FL' is not a procedure or is undefined

Try using double quotes if it's a char variable:

DEFINE stupidvar = "'stupidvarcontent'";

or

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata  
FROM stupidtable  
WHERE stupidcolumn = '&stupidvar'

upd:

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 25 17:13:26 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn od/od@etalon
Connected.
SQL> define var = "'FL-208'";
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = 'FL-208'

CODE
---------------
FL-208

SQL> define var = 'FL-208';
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = FL-208
select code from product where code = FL-208
                                      *
ERROR at line 1:
ORA-06553: PLS-221: 'FL' is not a procedure or is undefined
回复 原文
梦年海沫深 2024-09-22 21:35:11

在PL/SQL v.10中，

关键字declare用于声明变量

DECLARE stupidvar varchar(20);

以分配值，您可以在声明时设置它

DECLARE stupidvar varchar(20) := '12345678';

，或者使用INTO语句将某些内容选择到该变量中，但是您需要包装语句在 BEGIN 和 END 中，您还需要确保只返回单个值，并且不要忘记分号。

所以完整的语句如下：

DECLARE stupidvar varchar(20);
BEGIN
    SELECT stupid into stupidvar FROM stupiddata CC 
    WHERE stupidid = 2;
END;

你的变量只能在 BEGIN 和 END 内使用，所以如果你想使用多个，你将不得不执行多个 BEGIN END 包装

DECLARE stupidvar varchar(20);
BEGIN
    SELECT stupid into stupidvar FROM stupiddata CC 
    WHERE stupidid = 2;

    DECLARE evenmorestupidvar varchar(20);
    BEGIN
        SELECT evenmorestupid into evenmorestupidvar FROM evenmorestupiddata CCC 
        WHERE evenmorestupidid = 42;

        INSERT INTO newstupiddata (newstupidcolumn, newevenmorestupidstupidcolumn)
        SELECT stupidvar, evenmorestupidvar 
        FROM dual

    END;
END;

希望这可以节省您一些时间

In PL/SQL v.10

keyword declare is used to declare variable

DECLARE stupidvar varchar(20);

to assign a value you can set it when you declare

DECLARE stupidvar varchar(20) := '12345678';

or to select something into that variable you use INTO statement, however you need to wrap statement in BEGIN and END, also you need to make sure that only single value is returned, and don't forget semicolons.

so the full statement would come out following:

DECLARE stupidvar varchar(20);
BEGIN
    SELECT stupid into stupidvar FROM stupiddata CC 
    WHERE stupidid = 2;
END;

Your variable is only usable within BEGIN and END so if you want to use more than one you will have to do multiple BEGIN END wrappings

DECLARE stupidvar varchar(20);
BEGIN
    SELECT stupid into stupidvar FROM stupiddata CC 
    WHERE stupidid = 2;

    DECLARE evenmorestupidvar varchar(20);
    BEGIN
        SELECT evenmorestupid into evenmorestupidvar FROM evenmorestupiddata CCC 
        WHERE evenmorestupidid = 42;

        INSERT INTO newstupiddata (newstupidcolumn, newevenmorestupidstupidcolumn)
        SELECT stupidvar, evenmorestupidvar 
        FROM dual

    END;
END;

Hope this saves you some time

回复 原文
白首有我共你 2024-09-22 21:35:11

如果您想声明日期然后在 SQL Developer 中使用它。

DEFINE PROPp_START_DT = TO_DATE('01-SEP-1999')

SELECT * 
FROM proposal 
WHERE prop_start_dt = &PROPp_START_DT

If you want to declare date and then use it in SQL Developer.

DEFINE PROPp_START_DT = TO_DATE('01-SEP-1999')

SELECT * 
FROM proposal 
WHERE prop_start_dt = &PROPp_START_DT
回复 原文
森林很绿却致人迷途 2024-09-22 21:35:11

问题是关于在脚本中使用变量对我来说意味着它将在 SQL*Plus 中使用。

问题是您错过了引号，Oracle 无法将值解析为数字。

SQL> DEFINE num = 2018
SQL> SELECT &num AS your_num FROM dual;
old   1: SELECT &num AS your_num FROM dual
new   1: SELECT 2018 AS your_num FROM dual

  YOUR_NUM
----------
      2018

Elapsed: 00:00:00.01

由于自动类型转换（或任何名称），该示例工作正常。

如果您通过在 SQL*Plus 中键入 DEFINE 进行检查，它将显示 num 变量是 CHAR。

SQL>define
DEFINE NUM             = "2018" (CHAR)

在这种情况下这不是问题，因为 Oracle 可以处理将字符串解析为数字（如果它是有效数字）。

当字符串无法解析为数字时，Oracle无法处理它。

SQL> DEFINE num = 'Doh'
SQL> SELECT &num AS your_num FROM dual;
old   1: SELECT &num AS your_num FROM dual
new   1: SELECT Doh AS your_num FROM dual
SELECT Doh AS your_num FROM dual
       *
ERROR at line 1:
ORA-00904: "DOH": invalid identifier

带引号，所以不要强迫 Oracle 解析为数字，就可以了：

17:31:00 SQL> SELECT '&num' AS your_num FROM dual;
old   1: SELECT '&num' AS your_num FROM dual
new   1: SELECT 'Doh' AS your_num FROM dual

YOU
---
Doh

所以，要回答原来的问题，应该像这个示例那样做：

SQL> DEFINE stupidvar = 'X'
SQL>
SQL> SELECT 'print stupidvar:' || '&stupidvar'
  2  FROM dual
  3  WHERE dummy = '&stupidvar';
old   1: SELECT 'print stupidvar:' || '&stupidvar'
new   1: SELECT 'print stupidvar:' || 'X'
old   3: WHERE dummy = '&stupidvar'
new   3: WHERE dummy = 'X'

'PRINTSTUPIDVAR:'
-----------------
print stupidvar:X

Elapsed: 00:00:00.00

还有另一种方法可以在 SQL*Plus 中存储变量，方法是使用 查询列值。

COL[UMN] 具有 new_value 选项来存储按字段名称查询的值。

SQL> COLUMN stupid_column_name new_value stupid_var noprint
SQL> SELECT dummy || '.log' AS stupid_column_name
  2  FROM dual;

Elapsed: 00:00:00.00
SQL> SPOOL &stupid_var.
SQL> SELECT '&stupid_var' FROM DUAL;
old   1: SELECT '&stupid_var' FROM DUAL
new   1: SELECT 'X.log' FROM DUAL

X.LOG
-----
X.log

Elapsed: 00:00:00.00
SQL>SPOOL OFF;

正如你所看到的，X.log值被设置到了stupid_var变量中，因此我们可以在当前目录中找到一个X.log文件，里面有一些日志。

The question is about to use a variable in a script means to me it will be used in SQL*Plus.

The problem is you missed the quotes and Oracle can not parse the value to number.

SQL> DEFINE num = 2018
SQL> SELECT &num AS your_num FROM dual;
old   1: SELECT &num AS your_num FROM dual
new   1: SELECT 2018 AS your_num FROM dual

  YOUR_NUM
----------
      2018

Elapsed: 00:00:00.01

This sample is works fine because of automatic type conversion (or whatever it is called).

If you check by typing DEFINE in SQL*Plus, it will shows that num variable is CHAR.

SQL>define
DEFINE NUM             = "2018" (CHAR)

It is not a problem in this case, because Oracle can deal with parsing string to number if it would be a valid number.

When the string can not parse to number, than Oracle can not deal with it.

SQL> DEFINE num = 'Doh'
SQL> SELECT &num AS your_num FROM dual;
old   1: SELECT &num AS your_num FROM dual
new   1: SELECT Doh AS your_num FROM dual
SELECT Doh AS your_num FROM dual
       *
ERROR at line 1:
ORA-00904: "DOH": invalid identifier

With a quote, so do not force Oracle to parse to number, will be fine:

17:31:00 SQL> SELECT '&num' AS your_num FROM dual;
old   1: SELECT '&num' AS your_num FROM dual
new   1: SELECT 'Doh' AS your_num FROM dual

YOU
---
Doh

So, to answer the original question, it should be do like this sample:

SQL> DEFINE stupidvar = 'X'
SQL>
SQL> SELECT 'print stupidvar:' || '&stupidvar'
  2  FROM dual
  3  WHERE dummy = '&stupidvar';
old   1: SELECT 'print stupidvar:' || '&stupidvar'
new   1: SELECT 'print stupidvar:' || 'X'
old   3: WHERE dummy = '&stupidvar'
new   3: WHERE dummy = 'X'

'PRINTSTUPIDVAR:'
-----------------
print stupidvar:X

Elapsed: 00:00:00.00

There is an other way to store variable in SQL*Plus by using Query Column Value.

The COL[UMN] has new_value option to store value from query by field name.

SQL> COLUMN stupid_column_name new_value stupid_var noprint
SQL> SELECT dummy || '.log' AS stupid_column_name
  2  FROM dual;

Elapsed: 00:00:00.00
SQL> SPOOL &stupid_var.
SQL> SELECT '&stupid_var' FROM DUAL;
old   1: SELECT '&stupid_var' FROM DUAL
new   1: SELECT 'X.log' FROM DUAL

X.LOG
-----
X.log

Elapsed: 00:00:00.00
SQL>SPOOL OFF;

As you can see, X.log value was set into the stupid_var variable, so we can find a X.log file in the current directory has some log in it.

回复 原文
盛夏已如深秋| 2024-09-22 21:35:11

只是想添加 Matas 的答案。
也许这是显而易见的，但我搜索了很长时间才发现 变量只能在 BEGIN-END 结构中访问，所以如果您稍后需要在某些代码中使用它，您需要将此代码放入 BEGIN-END 块内。

请注意，这些块可以嵌套：

DECLARE x NUMBER;
BEGIN
    SELECT PK INTO x FROM table1 WHERE col1 = 'test';

    DECLARE y NUMBER;
    BEGIN
        SELECT PK INTO y FROM table2 WHERE col2 = x;

        INSERT INTO table2 (col1, col2)
        SELECT y,'text'
        FROM dual
        WHERE exists(SELECT * FROM table2);

        COMMIT;
    END;
END;

Just want to add Matas' answer.
Maybe it's obvious, but I've searched for a long time to figure out that the variable is accessible only inside the BEGIN-END construction, so if you need to use it in some code later, you need to put this code inside the BEGIN-END block.

Note that these blocks can be nested:

DECLARE x NUMBER;
BEGIN
    SELECT PK INTO x FROM table1 WHERE col1 = 'test';

    DECLARE y NUMBER;
    BEGIN
        SELECT PK INTO y FROM table2 WHERE col2 = x;

        INSERT INTO table2 (col1, col2)
        SELECT y,'text'
        FROM dual
        WHERE exists(SELECT * FROM table2);

        COMMIT;
    END;
END;
回复 原文
醉态萌生 2024-09-22 21:35:11

在 Toad 中，我使用此方法：

declare 
    num number;
begin 
    ---- use 'select into' works 
    --select 123 into num from dual;

    ---- also can use :=
    num := 123;
    dbms_output.Put_line(num);
end;

然后该值将打印到 DBMS 输出窗口。

参考此处和此处2< /a>.

In Toad I use this works:

declare 
    num number;
begin 
    ---- use 'select into' works 
    --select 123 into num from dual;

    ---- also can use :=
    num := 123;
    dbms_output.Put_line(num);
end;

Then the value will be print to DBMS Output Window.

Reference to here and here2.

回复 原文
半世蒼涼 2024-09-22 21:35:11

这是你的答案：

DEFINE num := 1;       -- The semi-colon is needed for default values.
SELECT &num FROM dual;

Here's your answer:

DEFINE num := 1;       -- The semi-colon is needed for default values.
SELECT &num FROM dual;
回复 原文
眼趣 2024-09-22 21:35:11

您可以使用 with 子句并将过滤条件从 where 移动到 join。

它在这里有帮助：使用 DEFINE 的 Oracle SQL 替代方案。

with
 mytab as (select 'stupidvarcontent' as myvar from dual)
SELECT
 stupiddata
FROM
  stupidtable a 
 inner join
  mytab b
 on
  a.stupidcolumn = b.myvar
WHERE ...;

它适用于 Oracle 12R2。
它仅适用于一个 SQL 命令。
这是标准 ANSI 表示法。
我在 SQL Developer 中使用它。

You can use a with clause and move filter criteria from a where to a join.

It helps here: Oracle SQL alternative to using DEFINE.

with
 mytab as (select 'stupidvarcontent' as myvar from dual)
SELECT
 stupiddata
FROM
  stupidtable a 
 inner join
  mytab b
 on
  a.stupidcolumn = b.myvar
WHERE ...;

It works in Oracle 12R2.
It works for one SQL command only.
It is standard ANSI notation.
I'm using it in SQL Developer.

回复 原文
薄暮涼年 2024-09-22 21:35:11

如果您只需要指定一次参数并将其复制到多个位置，一种可能的方法是执行以下操作：

SELECT
  str_size  /* my variable usage */
  , LPAD(TRUNC(DBMS_RANDOM.VALUE * POWER(10, str_size)), str_size, '0') rand
FROM
  dual  /* or any other table, or mixed of joined tables */
  CROSS JOIN (SELECT 8 str_size FROM dual);  /* my variable declaration */

此代码生成一串 8 个随机数字。

请注意，我创建了一种名为 str_size 的别名，其中包含常量 8。它是交叉连接的，可以在查询中多次使用。

One possible approach, if you just need to specify a parameter once and replicate it in several places, is to do something like this:

SELECT
  str_size  /* my variable usage */
  , LPAD(TRUNC(DBMS_RANDOM.VALUE * POWER(10, str_size)), str_size, '0') rand
FROM
  dual  /* or any other table, or mixed of joined tables */
  CROSS JOIN (SELECT 8 str_size FROM dual);  /* my variable declaration */

This code generates a string of 8 random digits.

Notice that I create a kind of alias named str_size that holds the constant 8. It is cross-joined to be used more than once in the query.

回复 原文
指尖上的星空 2024-09-22 21:35:11

有时您需要使用宏变量而不要求用户输入值。大多数情况下，这必须使用可选的脚本参数来完成。以下代码功能齐全

column 1 noprint new_value 1
select '' "1" from dual where 2!=2;
select nvl('&&1', 'VAH') "1" from dual;
column 1 clear
define 1

在rdbms/sql 目录中以某种方式找到了类似的代码。

Sometimes you need to use a macro variable without asking the user to enter a value. Most often this has to be done with optional script parameters. The following code is fully functional

column 1 noprint new_value 1
select '' "1" from dual where 2!=2;
select nvl('&&1', 'VAH') "1" from dual;
column 1 clear
define 1

Similar code was somehow found in the rdbms/sql directory.

回复 原文
~没有更多了~

关于作者

新人笑

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

琉璃梦幻

文章 0 评论 0

qq_4zWU6L

文章 0 评论 0

话少情深

文章 0 评论 0

西西弗的石头怪

文章 0 评论 0

彻夜缠绵

文章 0 评论 0

千寻…

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文