如果(从表中选择计数(列))>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.

相关文章