oracle 在游标声明中使用 if 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4036885/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using an if statment into a cursor declaration
提问by Giancarlo Solarino
Im using a cursor to go over some data but I need to declare diferents sql statments according to a parameter. The problem seems to be that Im no able to use if statmets into this declaratios:
我使用游标来查看一些数据，但我需要根据参数声明不同的 sql 语句。问题似乎是我无法使用 if statmets 进入这个声明：
DECLARE
CURSOR c IS SELECT * FROM TRAFICO
IF TipoConsulta = 'mes' then
BEGIN
WHERE TO_CHAR(FECHA_BUSQUEDA, 'MM-YYYY') =To_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM-YYYY')
ELSE
WHERE FECHA_BUSQUEDA >= SYSDATE -7
END IF;
ORDER BY ID_TRAFICO;
begin
FOR r IN C LOOP
BEGIN
Utl_File.Put_Line(Arch, r.ID_TRAFICO );
i:=i+1;
END;
END LOOP;
END;
I just need to change the sql statment using an if.
我只需要使用 if 更改 sql 语句。
How do I do this??
我该怎么做呢？？
TNKS
天津大学
回答by bobs
I think you might want to try the following:
我想你可能想尝试以下方法：
SELECT * FROM TRAFICO
WHERE (TipoConsulta = 'mes'
and TO_CHAR(FECHA_BUSQUEDA, 'MM-YYYY') =To_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM-YYYY'))
or (TipoConsulta <> 'mes'
and FECHA_BUSQUEDA >= SYSDATE -7)
ORDER BY ID_TRAFICO;
回答by erbsock
try using a dynamic cursor REF CURSOR (i'm assuming TripoConsulta is a variable and not one of the predicates of the query):
尝试使用动态游标 REF CURSOR（我假设 TripoConsulta 是一个变量而不是查询的谓词之一）：
declare
type some_cursor is ref cursor;
my_cursor some_cursor;
my_rec trafico%rowtype;
begin
if TripoConsulta = 'mes' then
open my_cursor for select * from trafico where ...;
else
open my_cursor for select * from trafico where ...;
end if;
loop
fetch my_cur into my_rec;
exit when my_cur%notfound;
Utl_File.Put_Line(my_rec.Arch, my_cur.ID_TRAFICO );
end loop;
close my_cursor;
end;
Clearly, i don't know what the variable all mean so i had to make a number of assumptions, but i'm thinking this is what you meant. I also don't guaranetee the syntax since i haven't actually tested it.
显然，我不知道变量的全部含义，所以我不得不做出一些假设，但我认为这就是你的意思。我也不保证语法，因为我还没有真正测试过它。
回答by OMG Ponies
This uses implicit cursors, rather than splinter an explicit cursor with an OR clause or use dynamic SQL:
这使用隐式游标，而不是使用 OR 子句拆分显式游标或使用动态 SQL：
CASE TipoConsulta
WHEN 'mes' THEN
FOR b IN (SELECT a.*
FROM TRAFICO a
WHERE TO_CHAR(a.fecha_busqueda, 'MM-YYYY') = To_CHAR(ADD_MONTHS(SYSDATE, -1), 'MM-YYYY')
ORDER BY a.id_trafico)
UTL_FILE.PUT_LINE(Arch, b.id_trafico);
END LOOP;
ELSE
FOR d IN (SELECT c.*
FROM TRAFICO c
WHERE c.fecha_busqueda >= SYSDATE - 7
ORDER BY c.id_trafico)
UTL_FILE.PUT_LINE(Arch, d.id_trafico);
END LOOP;
END CASE;
This also uses a PLSQL CASE expression, rather than the ANSI CASE expression -- you can tell by the use of END CASE, when ANSI CASE just uses END.
这也使用了 PLSQL CASE 表达式，而不是 ANSI CASE 表达式——END CASE当 ANSI CASE 只使用END.
It doesn't appear that you posted the entire query - I don't know where archis coming from, nor the need for the incrementing variable i... Or why you've got SELECT * FROM TRAFICObut only using the id_traficocolumn...
您似乎没有发布整个查询 - 我不知道arch来自哪里，也不知道是否需要递增变量i......或者为什么你有SELECT * FROM TRAFICO但只使用id_trafico列......
