代码之家 › 专栏 › 技术社区 › user3655855

游标定义中的PL/SQL预定义语句

plsql loops sql

user3655855 · 技术社区 · 8 年前

我们有一个 select 根据开关的不同而不同的语句。然后我们对结果进行循环。

if switch_a then
    for rec in 
    (
        select * 
                order by decode(x, y,1,0),
                decode(x,a,2,0),
                decode(x,z,3,0), pos
    )
    loop ...
end loop;
else 
for rec in 
        (
            select * 
                    order by decode(x, y,1,0),
                    decode(x,z,3,0), pos
        )
        loop ...
end loop;
end if;

现在我想把代码转换成只有一个循环。所以我同意:

if (switch_a) then
    Statement call := select*...
else
    Statement call := select*...
end if;

for rec in call
loop

end loop;

问题是,我不知道这是否适用于for rec in,也不知道如何定义可执行的SQL语句。这可能吗?如果可能,你能举个例子吗?

2 回复 | 直到 8 年前

Boneist 8 年前

考虑到这只是order by不同,我会使用非布尔(SQL不知道Oracle中的布尔值)标志来决定是否在order by中包含列,例如:

WITH sample_data AS (SELECT 1 col1, 100 col2, 200 col3 FROM dual UNION ALL
                     SELECT 2 col1, 90 col2, 210 col3 FROM dual UNION ALL
                     SELECT 3 col1, 80 col2, 220 col3 FROM dual UNION ALL
                     SELECT 4 col1, 70 col2, 230 col3 FROM dual UNION ALL
                     SELECT 5 col1, 60 col2, 240 col3 FROM dual)
SELECT col1,
       col2,
       col3
FROM   sample_data
ORDER BY CASE WHEN :p_switch_a_flag = 'y' THEN col2 END,
         col3;

用它运行 :p_switch_a_flag = 'n' :

      COL1       COL2       COL3
---------- ---------- ----------
         1        100        200
         2         90        210
         3         80        220
         4         70        230
         5         60        240

用它运行 :p_switch_a_flag = 'y' :

      COL1       COL2       COL3
---------- ---------- ----------
         5         60        240
         4         70        230
         3         80        220
         2         90        210
         1        100        200

-- if you can't change the switch_a Boolean to a varchar2 flag, then
-- manually set the switch_a flag, otherwise you'd simply pass in
-- the flag as a varchar2, rather than a Boolean.
if switch_a then 
  switch_a_flag := 'y';
end if;

for rec in (select * 
            from    ....
            order by decode(x, y,1,0),
                     case when switch_a_flag = 'y' then decode(x,a,2,0) end,
                     decode(x,z,3,0),
                     pos)
loop
  ...
end loop;

I3rutt 8 年前

可能是这样的:

declare 
  Statement_call varchar2(4000); 
  rec sys_refcursor;  
  l_row number;
begin
    if (switch_a) then
      Statement_call := 'select 1 as q from dual';
    else
      Statement_call := 'select 2 as q from dual';
    end if;

    OPEN rec FOR Statement_call;
    loop
      FETCH rec INTO l_row;
      EXIT WHEN rec%NOTFOUND;

      dbms_output.put_line(l_row);
    end loop;   
    CLOSE rec;

end;