如果(从表中选择计数(列))>0 那么

2021-12-05 00:00:00 oracle plsql

我需要检查一个条件.即:

I need to check a condition. i.e:

if (condition)> 0 then
  update table
else do not update
end if

是否需要使用 select into 将结果存储到变量中?

Do I need to store the result into a variable using select into?

例如:

declare valucount integer
begin
  select count(column) into valuecount from table
end
if valuecount > o then
  update table
else do 
  not update

推荐答案

不能在 PL/SQL 表达式中直接使用 SQL 语句:

You cannot directly use a SQL statement in a PL/SQL expression:

SQL> begin
  2     if (select count(*) from dual) >= 1 then
  3        null;
  4     end if;
  5  end;
  6  /
        if (select count(*) from dual) >= 1 then
            *
ERROR at line 2:
ORA-06550: line 2, column 6:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
...
...

您必须改用变量:

SQL> set serveroutput on
SQL>
SQL> declare
  2     v_count number;
  3  begin
  4     select count(*) into v_count from dual;
  5
  6     if v_count >= 1 then
  7             dbms_output.put_line('Pass');
  8     end if;
  9  end;
 10  /
Pass

PL/SQL procedure successfully completed.

当然，您可以在 SQL 中完成整个操作:

Of course, you may be able to do the whole thing in SQL:

update my_table
set x = y
where (select count(*) from other_table) >= 1;

很难证明某些事情是不可能的.除了上面的简单测试用例，您可以查看 IF 语句的语法图；您不会在任何分支中看到 SELECT 语句.

It's difficult to prove that something is not possible. Other than the simple test case above, you can look at the syntax diagram for the IF statement; you won't see a SELECT statement in any of the branches.

相关文章