关于sql替换

Question

当我运行以下查询时，第二个查询失败，因为 prev_test_ref1 变量未定义。如果我删除第一个查询中的插入语句，再次运行，那么它就可以工作并在第二个查询中使用第一个 sql 查询中的 prev_test_ref1 值。是因为变量作用域吗？我怎样才能用插入语句解决这个问题。

查询1

column prev_test_ref1 new_value prev_test_ref1  ;

insert into testing.test_ref_details(TEST_TYPE,TEST_REF_NO)
select  '1',max(test_ref_no) as prev_test_ref1
from    testing.test_runs_status
where   test_type = 1
and run_status = 1
and test_end_dt = (select last_day(add_months(trunc(sysdate),-6))+2 from dual)
group by test_end_dt
;

查询2

column last_test_end_dt new_value last_test_end_dt;

select to_char(test_completion_dt,'DD-MON-YYYY HH24:MI:SS') as last_test_end_dt
from testing.test_runs_status
where test_ref_no = '&prev_test_ref1';

Answer 1

在 SQLPlus 中，替换变量只能用 SELECT 语句定义。您的第一次插入不会返回行，因此它不起作用（想一想：它只返回 插入的 1 行。，SQLPlus 无法知道插入的值。）

我建议您添加一个步骤将值保存到变量中（或使用 PL/SQL 块）：

column prev_test_ref1 new_value prev_test_ref1  ;

SELECT MAX(test_ref_no) AS prev_test_ref1
  FROM testing.test_runs_status
 WHERE test_type = 1
   AND run_status = 1
   AND test_end_dt = (SELECT last_day(add_months(trunc(SYSDATE), -6)) + 2 
                        FROM dual)
 GROUP BY test_end_dt;

 INSERT INTO testing.test_ref_details(TEST_TYPE,TEST_REF_NO) 
    VALUES ('1', &prev_test_ref1);

 SELECT ...

Answer 2

declare
prev_test_ref1 number(10);
begin
  insert into ...select ...;
  select ... into prev_test_ref1 from ...;
end;
/

Answer 3

INSERT 语句有一个 RETURNING 子句。我们可以使用它来访问表中的“未知”值。以下示例使用 RETURNING 从序列中获取分配的 nextval，但我们可以返回行中的任何列：

SQL> var prev_id number
SQL> insert into t23 (id, name) values (my_seq.nextval, 'MAISIE')
  2  returning id into :prev_id
  3  /

1 row created.

SQL> select * from t23
  2  where id = :prev_id
  3  /

NAME               ID
---------- ----------
MAISIE            122

SQL> 

不幸的是，RETURNING 子句仅适用于单行 SQL。

Answer 4

目前还不清楚整个脚本的目的是什么，特别是考虑到评论“我有类似的 sql 查询，它返回多行。在这种情况下，我不能有单独的插入语句。”

如果您想使用选择的结果，请查看多表插入< /a> 符合要求。您的 select 语句可以插入主表和第二个表（例如全局临时表）。然后，您可以查询全局临时表以查看插入了哪些行。