oracle 从 pl/sql 中的游标计算行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38845510/
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
counting rows from a cursor in pl/sql
提问by networker
I'm trying to count the number of rows that will be returned from an sql statement . This statement is in a cursor
我正在尝试计算将从 sql 语句返回的行数。该语句在游标中
My code is like this
我的代码是这样的
DECLARE
v_counter int := 0 ;
select count(*) into v_counter from (
cursor get_sth is select * from table1 where condit..) ;
BEGIN
DBMS_OUTPUT.PUT_LINE (v_counter);
END ;
/
and it doesn't work
它不起作用
Is there any other solution that makes me counting the number of rows of a cursor result , I'm really noob
有没有其他解决方案让我计算游标结果的行数，我真的很菜
thanks helpers
感谢帮手
回答by Alex Poole
If your aim is to reuse an existing cursor definition and not have to repeat the query it's based on, you could loop over its results to get a count:
如果您的目标是重用现有的游标定义，而不必重复它所基于的查询，则可以遍历其结果以获取计数：
set serveroutput on
declare
v_counter pls_integer := 0;
cursor get_sth is select * from all_tables where owner = user; -- your query
begin
for sth in get_sth loop
v_counter := v_counter + 1;
end loop;
dbms_output.put_line (v_counter);
end;
/
You can't count the rows in the result set without fetching them, which the cursor loop does. (@MarcinWroblewski shows another way to that, with explicit fetches). Either way the cursor is consumed by the process. If you want to do anything with the returned data aftercounting it you'd have to re-execute and re-fetch the cursor.
如果不获取结果集中的行数，就无法计算它们，游标循环会这样做。（@MarcinWroblewski 展示了另一种方法，即显式提取）。无论哪种方式，进程都会消耗光标。如果您想在计数后对返回的数据执行任何操作，则必须重新执行并重新获取游标。
回答by Marcin Wroblewski
What about
关于什么
DECLARE
v_counter INT := 0;
BEGIN
SELECT COUNT(*) INTO v_counter FROM table1 WHERE condit..;
dbms_output.put_line(v_counter);
END;
/
?
?
If you already have cursor and just want to know how many records it returns, you have to fetch them all
如果您已经有游标并且只想知道它返回多少条记录，则必须全部获取
DECLARE
CURSOR get_sth IS
SELECT * FROM table1 WHERE condit..;
sth get_sth%ROWTYPE;
v_counter NUMBER;
BEGIN
OPEN get_sth;
LOOP
FETCH get_sth
INTO sth;
EXIT WHEN get_sth%NOTFOUND;
END LOOP;
v_counter := get_sth%ROWCOUNT;
dbms_output.put_line(v_counter);
CLOSE get_sth;
END;
/
回答by user9865188
to optimize runnign time take de cursor code and get into the body code
CURSOR MOVIMIENTO_ACTIV IS
SELECT X.CODMODFUE
, X.COD_DEP
, X.CODTIPREC
, SUM(X.VLR_COSTO)
, X.COD_ACTIVIDAD
, X.PERIODO
, Y.CLASE
, Y.ESTADO
, Y.redistri
, X.recurso
, ROWNUM NUMERO
FROM COS_MOVIMIENTO X
, COS_NIVELES Y
WHERE X.EMPRESA = PEMP AND
X.EMPRESA = Y.EMPRESA AND
X.COD_ACTIVIDAD = Y.COD_NIVEL AND
X.PERIODO = PPER AND
Y.CLASE = 'G' AND
Y.ESTADO='A' AND
Y.redistRI = 'S'
GROUP BY X.CODMODFUE
, X.COD_DEP
, X.CODTIPREC
, X.COD_ACTIVIDAD
, X.PERIODO
, Y.CLASE
, Y.ESTADO
, Y.redistri
, X.recurso
;
begin
SELECT COUNT(*) FROM (SELECT X.CODMODFUE
, X.COD_DEP
, X.CODTIPREC
, SUM(X.VLR_COSTO)
, X.COD_ACTIVIDAD
, X.PERIODO
, Y.CLASE
, Y.ESTADO
, Y.redistri
, X.recurso
FROM COS_MOVIMIENTO X
, COS_NIVELES Y
WHERE X.EMPRESA = '01' AND
X.EMPRESA = Y.EMPRESA AND
X.COD_ACTIVIDAD = Y.COD_NIVEL AND
X.PERIODO = '201803' AND
Y.CLASE = 'G' AND
Y.ESTADO='A' AND
Y.redistRI = 'S'
GROUP BY X.CODMODFUE
, X.COD_DEP
, X.CODTIPREC
, X.COD_ACTIVIDAD
, X.PERIODO
, Y.CLASE
, Y.ESTADO
, Y.redistri
, X.recurso
)W
end;
