--游标
1.使用游标的原因
解决关系数据库中的操作会对整个行集产生影响,但是用户往往需要从结果集中逐一的读取记录。2.游标的概念
它是由结果集(可以是零条、一条或由相关的SELECT语句检索出的多条记录)和结果集中指向特定记录的游标位置组成的,游标相当于指针。
2.1 允许定位在结果集的特定行2.2 从结果集的当前位置检索一行或多行
2.3 支持对结果集中当前位置的行进行数据修改
3.游标分类
3.1 显示游标:它有明确的游标声明和定义,它需要用户自己编写代码,一切由用户控制。并且代开游标后,可以利用遍历语句遍历当前的所有记录。关闭游标后,用户将失去游标的控制权。
3.2 隐式游标:它由PL/SQL自动管理,用户无法控制。
例子:定义显示游标,查询BOOKINFO表中大众出版社和青
年出版社的图书信息,并能逐行处理查询结果中的数据。
语法:
CURSOR cursor_name [(parameter_name datatype,...)] IS select_statement
-关键字 -参数名和参数类型 -关键字 -select语句
1.声明游标
CURSOR book_cusor
IS
SELECT * FROM BOOKINFO WHERE PUBLISH ='大众出版社' ORDER BY PRICE;
/
2.打开游标
OPEN cursor_name
3.读取数据
FETCH cursor_name INTO Record_Name
4.关闭游标
CLOSE cursor_name
--创建BOOKINFO
CREATE TABLE BOOKINFO
(
bookid INT NOT NULL,
bookname CHAR NOT NULL,
publish VARCHAR(20),
pubdate VARCHAR(20),
price NUMBER(7,2) DEFAULT 0.0,
author CHAR,
store INT,
reader INT,
remarks VARCHAR(50)
);
--插入数据
INSERT INTO BOOKINFO
(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)
VALUES(4001,'HTML教程','青年出版社',to_date('2016-09-01','yyyy-mm-dd'),35,'王兵,李宏',
1,9702,null);
INSERT INTO BOOKINFO
(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)
VALUES
(4002,'Photoshop','大众出版社',to_date('2016-04-01','yyyy-mm-dd'),75,'李世宝',
1,9704,'very good!');
INSERT INTO BOOKINFO
(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)
VALUES(4003,'Java','猪猪侠出版社',to_date('2017-01-07','yyyy-mm-dd'),400,'康梦雅',
0,9703,null);
INSERT INTO BOOKINFO
(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)
VALUES(4004,'C#','清华出版社',to_date('2015-04-01','yyyy-mm-dd')),100,'朱书军',0,
9703,'simple');
INSERT INTO BOOKINFO
(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)
VALUES(4005,'HTML教程','青年出版社',to_date('2016-09-01','yyyy-mm-dd'),70,'王兵,李宏',1,
9702,null);
INSERT INTO BOOKINFO
(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)
VALUES(4006,'Photoshop','大众出版社',to_date('2016-04-01','yyyy-mm-dd'),300,'李世宝',1,
9709,'very good!');
INSERT INTO BOOKINFO
(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)
VALUES(4007,'Java','猪猪侠出版社',to_date('2017-01-07','yyyy-mm-dd'),500,'康梦雅',1,
9708,null);
INSERT INTO BOOKINFO
(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)
VALUES(4008,'C#','清华出版社',to_date('2015-04-01','yyyy-mm-dd'),70,'朱书军',0,
9705,'simple');
--游标操作
DECLARE
CURSOR book_cusor
IS
SELECT * FROM BOOKINFO WHERE PUBLISH
IN('大众出版社','青年出版社') ORDER BY PRICE;
cur_book_cusor BOOKINFO%ROWTYPE;--变量类型为行记录类型
BEGIN
OPEN book_cusor;
LOOP
FETCH book_cusor INTO cur_book_cusor;
EXIT WHEN book_cusor %NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cur_book_cusor.BOOKID
||'-'||cur_book_cusor.BOOKNAME||'_'||cur_book_cusor.PRICE
||'_'||cur_book_cusor.READER);
END LOOP;
CLOSE book_cusor;
END;
/
--在上例基础上,建立能够批量操作的游标
DECLARE
CURSOR book_cusor
IS
SELECT * FROM BOOKINFO WHERE PUBLISH
IN('大众出版社','青年出版社') ORDER BY PRICE;
TYPE BOOKINF_TAB IS TABLE OF BOOKINFO%ROWTYPE;--创建索引表数据类型
cur_book_cusor BOOKINF_TAB;
BEGIN
OPEN book_cusor;
LOOP
FETCH book_cusor BULK COLLECT INTO cur_book_cusor LIMIT 3;--提取游标中的数据,且每次提取3条记录
FOR i IN 1..cur_book_cusor.count LOOP
DBMS_OUTPUT.PUT_LINE(cur_book_cusor(i).BOOKID
||'-'||cur_book_cusor(i).BOOKNAME||'-'||cur_book_cusor(i).PRICE
||'-'||cur_book_cusor(i).READER);
END LOOP;
EXIT WHEN book_cusor %NOTFOUND;
END LOOP;
CLOSE book_cusor;
END;
/
注:红色是语句变化关键部分。
--显式游标的属性
1.%ISOPEN:用于判断游标是否打开
2.%FOUND:用于检测行数据是否有效
3.%NOTFOUND:如果没有提取出数据则返回TRUE,否则返回FALSE(上面例子中有使用)
4.%ROWCOUNT:计数器,累计使用FETCH提取的记录数
--在上例基础上,具体说明这几个属性的使用方法
DECLARE
CURSOR book_cusor
IS
SELECT * FROM BOOKINFO WHERE PUBLISH
IN('大众出版社','青年出版社') ORDER BY PRICE;
cur_book_cusor BOOKINFO%ROWTYPE;
BEGIN
IF book_cusor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('游标已经打开。');
ELSE
DBMS_OUTPUT.PUT_LINE('游标游标还没有打开,现在打开游标。');
OPEN book_cusor;
IF book_cusor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('游标现在已经打开。');
END IF;
LOOP
FETCH book_cusor INTO cur_book_cusor ;
IF book_cusor%FOUND THEN
DBMS_OUTPUT.PUT_LINE(cur_book_cusor.BOOKID
||'-'||cur_book_cusor.BOOKNAME||'-'||cur_book_cusor.PRICE
||'-'||cur_book_cusor.READER);
DBMS_OUTPUT.PUT_LINE('第'||book_cusor%ROWCOUNT||'记录已经输出完毕。');
ELSE
DBMS_OUTPUT.PUT_LINE('游标中没有数据再被提取。');
EXIT WHEN book_cusor %NOTFOUND;
END IF;
END LOOP;
END IF;
CLOSE book_cusor;
END;
/