sql - 如何让变量在 Oracle SQL Developer 中真正起作用？

Question

在 Oracle SQL Developer 中，以这种方式使用替换变量是可行的:

DEFINE var_t1_id int = 2601;
DEFINE var_t2_id int = (SELECT t2_id FROM table1 WHERE t1_id = &var_t1_id);
DEFINE var_t3_id int = (SELECT t3_id FROM table1 WHERE t1_id = &var_t1_id);

SELECT *
FROM table2
WHERE t2_id = &var_t2_id;

SELECT *
FROM table3
WHERE t3_id = &var_t3_id;

不过，当我将 2601 更改为其他内容并重新运行查询时，SQL Developer 继续使用 2601 进行替换!!!不是我想要的...

我试过这个:

variable var_t1_id number;
variable var_t2_id number;
variable var_t3_id number;

exec :var_t1_id := 2601;
exec SELECT t2_id INTO :var_t2_id FROM table1 WHERE t1_id = :var_t1_id;
exec SELECT t3_id INTO :var_t3_id FROM table1 WHERE t1_id = :var_t1_id;

SELECT *
FROM table2
WHERE t2_id = :var_t2_id;

SELECT *
FROM table3
WHERE t3_id = :var_t3_id;

还有这个:

DEFINE var_t1_id int = 2601;
DEFINE var_t2_id int = (SELECT t2_id FROM table1 WHERE t1_id = (SELECT &&var_t1_id from dual));
DEFINE var_t3_id int = (SELECT t3_id FROM table1 WHERE t1_id = (SELECT &&var_t1_id from dual));

SELECT *
FROM table2
WHERE t2_id = (SELECT &&var_t2_id FROM dual);

SELECT *
FROM table3
WHERE t3_id = (SELECT &&var_t3_id FROM dual);

但没有一个能正常工作——我的意思是，脚本应该在没有提示的情况下运行我的值(value)观...

我该如何解决这个问题？

我习惯了 SQL Server，所有这些都非常简单......当然，我想念 Oracle 知识......我在这里询问提示的原因。

Answer 1

您可以尝试使用 INTO 关键字

DECLARE
    --all your variables
    yout_variable_0 int := 1608;
    your_variable_1 int;
BEGIN
    select [...]
    into your_variable_1
    from your_table
    where your_column = your_variable_0;
END;

现在您的结果存储在 your_variable_1 中。

你可以试试这个简单的查询

DECLARE
  --all your variables
  your_variable_0 varchar2(200) := '201605';
  your_variable_1 varchar2(200);
BEGIN
  select your_variable_0
  into your_variable_1 
  from dual;

  dbms_output.put_line('Output ...' || your_variable_1);

END;