游标的rowcount是做到吗?
假如记录有10条
如果rowcount=1的话,就是循环到第一条的意思吗?
假如记录有10条
如果rowcount=1的话,就是循环到第一条的意思吗?
解决方案 »
- Oracle数据类型
- PL/SQL中编写一个函数:取原字符串 123;asd;33;e4 以分号为分隔符的字符串,例如我需要取第一个,得到的值应该为123 ,取第四个就是e4
- 用OCI涵数连接ORACL的时候,那个参数决定了可以访问那个SKIM.
- to_date用法
- 如何使用函数得到正确的结果?
- 给表增加一个记录,默认值是字符1,如何写
- 请问column os heading 'Os User' format a10语句中a10是什么意思?
- 在同一计算机上有两个库a,b,用户分别是ua,ub,当连接到库a中时,如何关联库b中的表?sql语句如何写?
- 如何取出表的字段名称!简单的sql
- 如何将Acess整个数据库导入oracle中
- drop table 删除的表不在回收站里
- 散分---从哪里可以买到这本书
1、显式 explicit
2、隐式 implicit游标属性:
1、%FOUND 有数据可取(bool)
2、%ISOPEN 是否打开
3、%NOTFOUND 没有数据可取(bool)
4、%ROWCOUNT 已经取得游标的数量
一、DEFINITION OF CURSOR
DECLARE
CURSOR mycur IS
SELECT * FROM books; --DECLARE CURSOR
myrecord books%ROWTYPE; --DECLARE A RECORD ,AND THE RECORD PROPERTY IS FROM THE TABLE BOOKS
BEGIN
OPEN mycur; --OPEN CURSOR
FETCH mycur INTO myrecord;--FETCH ONE Record TO RECORD FROM CURSOR
WHILE mycur%FOUND LOOP -- LOOP WHEN HAS RECORDS
DBMS_OUTPUT.PUL_LINE(myrecord.books_id||','||myrecord.books_name);
FETCH mycur INTO myrecord;--FETCH ONE Record TO RECORD FROM CURSOR AGAIN
END LOOP; --END LOOP
CLOSE mycur;--CLOSE CURSOR
END;
/二、CURSOR WITH PARAMETER
DECLARE
CURSOR cur_para(id varchar2) IS --The parameter just need a type
SELECT books_name FROM books WHERE books_id=id;
t_name books.books_name%TYPE;
BEGIN
OPEN cur_para('0001');
LOOP
FETCH cur_para INTO t_name;
EXIT WHEN cur_para%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(t_name);
END LOOP;
CLOSE cur_para;
END;
/
三、DECLARE CURSOR WITH PARAMETER USE FOR..LOOP
DECLARE
CURSOR cur_para(id varchar2) IS
SELECT books_name FROM books WHERE books_id=id;
BEGIN
DBMS_OUTPUT.PUT_LINE('*********************');
FOR cur IN cur_para('0001') LOOP --FOR LOOP, NO NEED TO DECLARE THE cur.
DBMS_OUTPUT.PUT_LINE(cur.books_name);
END LOOP;
END;
/四、DECLARE CURSOR WITH PARAMETER (%ISOPEN)
DECLARE
t_name books.books_name%TYPE;
CURSOR cur(id varchar2) IS
SELECT books_name FROM books WHERE books_id=id;
BEGIN
IF cur%ISOPEN THEN --%ISOPEN
DBMS_OUTPUT.PUT_LINE('CURSOR HAS BEEN OPENED!');
ELSE
OPEN CUR('0003');
END IF;
FETCH cur INTO t_name;
CLOSE cur;
DBMS_OUTPUT.PUT_LINE(t_name);
END;
/五、DECLARE CURSOR (%ROWCOUNT)
DECLARE
t_name varchar2(10);
CURSOR mycur IS SELECT books_name FROM books;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO t_name;
EXIT WHEN mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE('游标mycur的ROWCOUNT是:'||mycur%ROWCOUNT);
--GET THE CURRENT ROW NUMBER
END LOOP;
CLOSE mycur;
DECLARE
t_name varchar2(10);
部分代码
CURSOR mycur IS SELECT books_name FROM books;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO t_name;
EXIT WHEN mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE('游标mycur的ROWCOUNT是:'||mycur%ROWCOUNT);
--GET THE CURRENT ROW NUMBER
END LOOP;
CLOSE mycur;
像下面这样,主要是为了做一个边界的判断,如果是第一条,和后面的处理方式不同。
IF c1%ROWCOUNT <> 1 then
ELSE
xxx
END IF;
可以这么做,是吧?