我想写可重用的代码,需要在开头声明一些变量,并在脚本中重用,比如:
DEFINE stupidvar = 'stupidvarcontent';
SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;
如何声明一个变量并在随后的语句中重用它,例如在 SQLDeveloper 中使用它。
尝试
使用 DECLARE 部分并在 BEGIN 和 END; 中插入以下 SELECT 语句。使用 &stupidvar 访问变量。
使用关键字 DEFINE 并访问变量。
使用关键字 VARIABLE 并访问该变量。
但是我在尝试期间遇到了各种错误(未绑定变量、语法错误、预期 SELECT INTO
...)。
在 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>
如果它是 char 变量,请尝试使用双引号:
DEFINE stupidvar = "'stupidvarcontent'";
或者
DEFINE stupidvar = 'stupidvarcontent';
SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = '&stupidvar'
更新:
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
ORA-01008: not all variables bound
。
DEFINE num = 1; SELECT &num FROM dual;
导致:ORA-01008: not all variables bound
在 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;
希望这可以节省您一些时间
如果要声明日期,然后在 SQL Developer 中使用它。
DEFINE PROPp_START_DT = TO_DATE('01-SEP-1999')
SELECT *
FROM proposal
WHERE prop_start_dt = &PROPp_START_DT
问题是关于在脚本中使用变量对我来说意味着它将在 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 的值被设置到了studt_var 变量中,因此我们可以在当前目录中找到一个X.log 文件,其中包含一些日志。
只想添加 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;
这是你的答案:
DEFINE num := 1; -- The semi-colon is needed for default values.
SELECT &num FROM dual;
您可以使用 with
子句并将过滤条件从 where
移动到 join
。
它在这里有所帮助: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 ...;
它适用于 Oracle 12R2。它仅适用于一个 SQL 命令。它是标准的 ANSI 表示法。我在 SQL Developer 中使用它。
如果您只需要指定一次参数并在多个位置复制它,一种可能的方法是执行以下操作:
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
。它是交叉连接的,可以在查询中多次使用。
有时您需要使用宏变量而不要求用户输入值。大多数情况下,这必须使用可选的脚本参数来完成。以下代码功能齐全
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 目录中以某种方式找到了类似的代码。
不定期副业成功案例分享