SQL存储过程检查表中的值是/否并执行sql [英] SQL stored procedure to check value yes/no in a table and execute sql

问题描述

检查table1中是否value=Y，然后立即执行sql_select

Check if value=Y in table1 and then execute immediate sql_select

if
(select value1 from table1 where value_desc='Indicator' and value1='Y')
then 
    execute immediate sql_select_yes
else 
    execute immediate sql_select_no

推荐答案

PL/SQL 语法中没有 if (cursor) 构造或任何 exists 运算符.您需要执行以下操作:

There is no if (cursor) construction or indeed any exists operator in PL/SQL syntax. You will need to do something like this:

declare
    somevar number;
begin
    select count(*) into somevar
    from   table1
    where  value_desc = 'Indicator'
    and    value1 = 'Y'
    and    rownum = 1;

    if somevar > 0 then
        execute immediate sql_select_yes
    else 
        execute immediate sql_select_no
    end;
end;

and rownum = 1 条件只是为了防止存在大量行，因为您不需要它来计算所有行以进行存在测试.(如果它必须计算一百万行，它不会影响结果，当您只关心是否存在一行时，这只是浪费时间.)您可以同样使用这样的东西进行存在检查:

The and rownum = 1 condition is just in case there are a large number of rows, as you don't need it to count all of them for an existence test. (It won't affect the result if it has to count a million rows, it's just a waste of time when you only care if one row exists.) You could equally use something like this for the existence check:

select count(*) into somevar from dual
where  exists
       ( select 1
         from   table1
         where  value_desc = 'Indicator'
         and    value1 = 'Y'
         and    rownum = 1 );

这篇关于SQL存储过程检查表中的值是/否并执行sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！