oracle 如何在游标中查找行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4934134/
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
How to find number of rows in cursor
提问by Avi
I would like to find the number of rows in a cursor. Is there a keyword that can help? Using COUNT, we have to write a query. Any help will be greatly appreciated.
我想找到游标中的行数。有没有可以帮助的关键字？使用COUNT，我们必须编写一个查询。任何帮助将不胜感激。
采纳答案by Chandu
You can use %ROWCOUNTattribute of a cursor.
您可以使用%ROWCOUNT游标的属性。
e.g:
例如：
DECLARE
CURSOR lcCursor IS
SELECT *
FROM DUAL;
BEGIN
OPEN lcCursor ;
DBMS_OUTPUT.PUT_LINE(lcCursor%ROWCOUNT);
CLOSE lcCursor ;
END;
回答by Brij
The cursor_variable.%ROWCOUNTis the solution. But its value will be 0 if you check it after opening. You need to loop through all the records, to get the total row count. Example below:
这cursor_variable.%ROWCOUNT就是解决方案。但是如果打开后检查它的值将为0。您需要遍历所有记录，以获得总行数。下面的例子：
DECLARE
cur sys_refcursor;
cur_rec YOUR_TABLE%rowtype;
BEGIN
OPEN cur FOR
SELECT * FROM YOUR_TABLE;
dbms_output.put_line(cur%rowcount);--returning 0
LOOP
FETCH cur INTO cur_rec;
EXIT WHEN cur%notfound;
dbms_output.put_line(cur%rowcount);--will return row number beginning with 1
dbms_output.put_line(cur_rec.SOME_COLUMN);
END LOOP;
dbms_output.put_line('Total Rows: ' || cur%rowcount);--here you will get total row count
END;
/
回答by Oracle McSnackers
You must open the cursor and then fetch and count every row. Nothing else will work.
您必须打开游标，然后获取并计算每一行。没有别的办法了。
回答by eifla001
You can also use BULK COLLECT so that a LOOP is not needed,
您还可以使用 BULK COLLECT 以便不需要 LOOP，
DECLARE
CURSOR c
IS SELECT *
FROM employee;
TYPE emp_tab IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp_tab emp_tab;
BEGIN
OPEN c;
FETCH c BULK COLLECT INTO v_emp_tab;
DBMS_OUTPUT.PUT_LINE(v_emp_tab.COUNT);
CLOSE c;
END;
/
回答by sgrpwr
You can use following simple single line code to print cursor count
您可以使用以下简单的单行代码打印光标计数
dbms_output.put_line(TO_CHAR(cur%rowcount));
回答by Ramkumar
DECLARE @STRVALUE NVARCHAR(MAX),
@CREATEDDATE DATETIME,
@STANTANCEVALUE NVARCHAR(MAX),
@COUNT INT=0,
@JOBCODE NVARCHAR(50)='JOB00123654',
@DATE DATETIME=GETDATE(),
@NAME NVARCHAR(50)='Ramkumar',
@JOBID INT;
CREATE TABLE #TempContentSplitValue (ITEMS NVARCHAR(200))
SELECT @JOBID = i.Id FROM JobHeader_TBL i WHERE Id=1201;
IF EXISTS (SELECT 1 FROM JobHeader_TBL WHERE Id=@JOBID)
BEGIN
SELECT @STRVALUE= Description from ContentTemplate_TBL where Id=1
INSERT INTO #TempContentSplitValue SELECT * FROM dbo.split(@STRVALUE, '_')
SET @STRVALUE=''
DECLARE db_contentcursor CURSOR FOR SELECT ITEMS FROM #TempContentSplitValue
OPEN db_contentcursor
FETCH NEXT FROM db_contentcursor
INTO @STANTANCEVALUE
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @STRVALUE += @STANTANCEVALUE + 'JOB00123654'
SET @COUNT += 1
SELECT @COUNT
FETCH NEXT FROM db_contentcursor INTO @STANTANCEVALUE
END
CLOSE db_contentcursor
DEALLOCATE db_contentcursor
DROP TABLE #TempContentSplitValue
SELECT @STRVALUE
END
回答by ambakick
Here I am trying to count the total number of customers with age greater than 25. So store the result in the cursor first. Then count the size of the cursor inside the function or in the main begin itself.
在这里，我试图计算年龄大于 25 岁的客户总数。因此首先将结果存储在游标中。然后计算函数内部或主开始本身中光标的大小。
DECLARE
cname customer24.cust_name%type;
count1 integer :=0;
CURSOR MORETHAN is
SELECT cust_name
FROM customer24
where age>25;
BEGIN
OPEN MORETHAN;
LOOP
FETCH MORETHAN into cname;
count1:=count1+1;
EXIT WHEN MORETHAN%notfound;
END LOOP;
-- dbms_output.put_line(count1);
dbms_output.put_line(MORETHAN%ROWCOUNT);
CLOSE MORETHAN;
END;
回答by Amila
This should work for you
这应该适合你
DECLARE
CURSOR get_data_ IS
SELECT *
FROM table_abc_
WHERE owner = user_; -- your query
counter_ NUMBER:= 0;
BEGIN
FOR data_ IN get_data_ LOOP
counter_ := counter_ + 1;
END LOOP;
dbms_output.put_line (counter_);
END;
回答by PHdAustin
There is a possible work around that may be useful/needed because of the overhead of accessing a database server over a network (e.g., when using Ajax calls)
由于通过网络访问数据库服务器的开销（例如，在使用 Ajax 调用时），有一种可能的解决方法可能有用/需要
Consider this:
考虑一下：
CURSOR c_data IS
SELECT per_first_name , null my_person_count
FROM person
UNION
SELECT null as per_first_name , count( distinct per_id ) as my_person_count
FROM person
order by my_person_count ;
The first row fetched has the count of records. One MUST add specific columns fetched (the use of the * does not work), and one can add additional filters.
获取的第一行包含记录数。必须添加获取的特定列（使用 * 不起作用），并且可以添加其他过滤器。
回答by PHdAustin
You can't have cursor count at start. For that you need to fetch complete cursor; that is the way get cursor count.
开始时不能有游标计数。为此，您需要获取完整的游标；这就是获取游标计数的方式。
declare
cursor c2 is select * from dept;
var c2%rowtype;
i number :=0;
begin
open c2;
loop
fetch c2 into var;
exit when c2%NOTFOUND;
i: = i+1;
end loop;
close c2;
dbms_output.put_line('total records in cursor'||i);
end;
