七、游标的使用-数据库-半夏微凉

七、游标的使用

游标种类：静态游标和REF游标

静态游标：显式游标、隐式游标

游标语法：CURSOR cursor_name [(parameter_name datatype,...) IS select_statement]

1.显式游标：

1.1游标使用步骤：

1）声明游标：DECLARE CURSOR cursor_name IS select_statement
2）打开游标：OPEN cursor_name
3）读取数据：FETCH cursor_name INTO record_name(变量)
4）关闭游标：CLOSE cursor_name

SET serveroutput ON;
DECLARE
  CURSOR cur_avg IS SELECT * FROM t_avg;
record_name t_avg%rowtype;
BEGIN
  OPEN cur_avg;
FETCH cur_avg INTO record_name;
dbms_output.put_line(record_name.id||'-'||record_name.name||'-'||record_name.jiage);
  CLOSE cur_avg;
END;
/
1-苹果-12

1.2游标中LOOP的使用：

SET serveroutput ON;
DECLARE
CURSOR cur_loop_avg IS SELECT t.id,t.name,t.jiage from t_avg t where t.shuliang>7;
cur_id varchar2(10);
cur_name t_avg.name%type;
cur_jiage t_avg.jiage%type;
BEGIN
OPEN cur_loop_avg;
LOOP
FETCH cur_loop_avg into cur_id,cur_name,cur_jiage;
EXIT WHEN cur_loop_avg%notfound;
dbms_output.put_line('商品ID：'||cur_id||'商品名称：'||cur_name||'商品价格:'||cur_jiage);
END LOOP;
END;
/
商品ID：2商品名称：桃子商品价格:30
商品ID：2商品名称：李子商品价格:77
商品ID：3商品名称：莉商品价格:33

1.3游标中使用BULK COLLECT 和FOR语句：

SET serveroutput ON;
DECLARE
CURSOR cur_loop_bulk is select * from t_avg t;
  TYPE tab_avg is table of t_avg%rowtype;
cur_1 tab_avg;
BEGIN
OPEN cur_loop_bulk;
LOOP
  FETCH cur_loop_bulk BULK COLLECT INTO cur_1 LIMIT 2;
    FOR i in 1..cur_1.count  LOOP --FOR循环语句
dbms_output.put_line('商品ID：'||cur_1(i).id||'商品名称：'||cur_1(i).name||'商品价格：'||cur_1(i).jiage);
END LOOP;
EXIT WHEN cur_loop_bulk%notfound;
END LOOP;
CLOSE cur_loop_bulk;
END;
/
商品ID：1商品名称：苹果商品价格：12
商品ID：1商品名称：芒果商品价格：45
商品ID：2商品名称：桃子商品价格：30
商品ID：2商品名称：李子商品价格：77
商品ID：3商品名称：莉商品价格：33

1.4游标中使用CURSOR FOR LOOP语句：

SET serveroutput ON;
DECLARE
CURSOR c1 is SELECT t.name,t.jiage FROM t_avg t WHERE t.shuliang>6 ;
BEGIN
FOR c2 IN c1 LOOP --把游标返回的数据放到c2中，该类型是个%rowtype类型
dbms_output.put_line('商品名称：'||c2.name||'商品价格：'||c2.jiage);
END LOOP;
END;
/
商品名称：桃子商品价格：30
商品名称：李子商品价格：77
商品名称：莉商品价格：33

1.5显式游标的4个属性：

1）%ISOPEN   判断游标是否打开，返回true和false.
2）%FOUND   检测游标行数据是否有效，返回true和false.
3）%NOTFOUND   跟上面的%found使用相反。
4）%ROWCOUNT   累计当前为止使用fetch的提取数据的行数。
SET serveroutput ON;
DECLARE
CURSOR cur_1 is select * from t_avg;
BEGIN
  IF cur_1%ISOPEN THEN
dbms_output.put_line('该游标已经打开');
ELSE
dbms_output.put_line('游标未打开');
END IF;
END;
/
游标未打开
SET serveroutput ON;
DECLARE
CURSOR cur_1 is select * from t_avg;
c2 t_avg%rowtype;
BEGIN
OPEN cur_1;
LOOP
FETCH cur_1 into c2;
IF cur_1%FOUND THEN
dbms_output.put_line('行数据有效'||c2.name);
ELSE
dbms_output.put_line('行数据无效');
EXIT; --退出循环
END IF;
END LOOP;
END;
/
行数据有效苹果
行数据有效芒果
行数据有效桃子
行数据有效李子
行数据有效莉
行数据无效
SET serveroutput ON;
DECLARE
CURSOR cur_1 is select * from t_avg t;
TYPE tab_1 IS TABLE OF t_avg%rowtype;
c2 tab_1;
BEGIN
OPEN cur_1;
LOOP
FETCH cur_1 BULK COLLECT INTO c2 LIMIT 2;
FOR i IN c2.first..c2.last
LOOP
dbms_output.put_line('产品名称：'||c2(i).name||' 商品价格：'||c2(i).jiage);
END LOOP;
  IF mod(cur_1%ROWCOUNT,2)=0 then
dbms_output.put_line('当前行数'||cur_1%ROWCOUNT);
ELSE
dbms_output.put_line('最后行数'||cur_1%ROWCOUNT);
EXIT;
END IF;
END LOOP;
END;
/
产品名称：苹果商品价格：12
产品名称：芒果商品价格：45
当前行数2
产品名称：桃子商品价格：30
产品名称：李子商品价格：77
当前行数4
产品名称：莉商品价格：33
最后行数5

1.6带参数的游标：

SET serveroutput ON;
DECLARE
c1_id t_avg.id%type:=2;
c1_jiage t_avg.jiage%type:=34; --赋值给两个参数
c1 t_avg%rowtype;
CURSOR cur_1(c_id varchar2,c_jiage number) IS SELECT * FROM t_avg
WHERE id LIKE c_id||'%' AND jiage>c_jiage;
BEGIN
OPEN cur_1(c1_id,c1_jiage); --把两个参数传入游标中
LOOP
FETCH cur_1 into c1;
EXIT WHEN cur_1%NOTFOUND;
dbms_output.put_line('商品ID：'||c1.id||'商品名称：'||c1.name||' 商品价格：'||c1.jiage);
END LOOP;
CLOSE cur_1;
END;
/
商品ID：2商品名称：李子商品价格：77

2.隐式游标

2.1隐性游标的特点。

隐式游标由PL/SQL自动管理；
隐式游标的默认名称是SQL；
SELECT或DML操作产生隐式游标；
隐式游标的属性值始终是最新执行的SQL语句的。

2.2隐式游标的使用：

SET SERVEROUTPUT ON;
DECLARE
c1 t_avg.name%type;
c2 t_avg.jiage%type;
BEGIN
SELECT t.name,t.jiage INTO c1,c2 FROM t_avg t where t.id=3;
IF SQL%FOUND THEN
dbms_output.put_line('商品名称：'||c1||'商品价格：'||c2);
END IF;
END;
/
商品名称：莉商品价格：33

游标中使用异常处理：

SET SERVEROUTPUT ON;
DECLARE
c1 t_avg.name%type;
c2 t_avg.jiage%type;
BEGIN
SELECT t.name,t.jiage INTO c1,c2 FROM t_avg t where t.id=4 ;
IF SQL%FOUND THEN
dbms_output.put_line('商品名称：'||c1||'商品价格：'||c2);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('没有数据');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('数据过多');
END;
/

2.3隐式游标的属性

1）%ISOPEN   该属性永远返回false值，由oracle自己控制；
2）%FOUND   反应DML语句是否影响到数据，影响时返回ture值，也可以反映出 select into语句是否返回值，返回值时为true.
3）%NOTFOUND   跟上面的%found使用相反。
4）%ROWCOUNT   反映DML操作对数据影响的数量。
SET SERVEROUTPUT ON;
DECLARE
c1 t_avg.name%type;
BEGIN
select t.name into c1 from t_avg t where t.id=3;
IF SQL%ISOPEN THEN
dbms_output.put_line('游标已打开');
ELSE
dbms_output.put_line('游标已关闭');
END IF;
END;
/
INSERT INTO T_AVG (id,Name) values('4','龙眼');
SET SERVEROUTPUT ON;
DECLARE
c1 t_avg.name%type;
c2 t_avg.jiage%type;
BEGIN
SELECT t.name,t.jiage INTO c1,c2 FROM t_avg t WHERE t.id=1;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
IF SQL%FOUND THEN
dbms_output.put_line('%FOUND为TRUE值');
DELETE FROM t_avg WHERE id =4;
IF SQL%FOUND THEN
COMMIT;
dbms_output.put_line('删除数据成功');
END IF;
END IF;
END;
/
%FOUND为TRUE值
删除数据成功
SET SERVEROUTPUT ON;
DECLARE
c1 t_avg.name%type;
c2 t_avg.jiage%type;
t1 varchar2(8);
BEGIN
SELECT t.name,t.jiage INTO c1,c2 FROM t_avg t;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(SQL%ROWCOUNT);
dbms_output.put_line('没有有效数据');
WHEN TOO_MANY_ROWS THEN
    t1:=SQL%ROWCOUNT;
dbms_output.put_line('SQL%ROWCOUNT的值为'||t1);
UPDATE t_avg SET t_avg.name='';
dbms_output.put_line('修改脚本影响记录数：'||SQL%ROWCOUNT);
  ROLLBACK;
dbms_output.put_line('脚本回滚后：'||SQL%ROWCOUNT);
END;
/
SQL%ROWCOUNT的值为1
修改脚本影响记录数：5
脚本回滚后：0
注意：
1.当select into 语句发生too_many_rows异常时，%rowcount属性依然返回1条记录，而不是实际的记录数目。
2.%rowcount 属性和事务没有关系，即使事务回滚，它的值也不会变成上次操作的值。

3.游标使用案例：

SET SERVEROUTPUT ON;
DECLARE
c_id categoryinfo.categroyid%type;
c_name categoryinfo.categroyname%type;
t1 productinfo%rowtype;
t_num number;
CURSOR c1 is select * from productinfo t where t.productprice>1000 and t.origin in ('中国','杭州');
CURSOR c2 is select u.categroyid,u.categroyname from categoryinfo u;
BEGIN
----把符合要求的数据放到productinfo_tmp表
OPEN c1;
LOOP
FETCH c1 INTO t1;
IF c1%FOUND THEN
INSERT INTO productinfo_tmp (productid,productname,productprice,quantity,category,desperation,origin) VALUES(t1.productid,t1.productname,t1.productprice,t1.quantity,t1.category,t1.desperation,t1.origin);
ELSE
dbms_output.put_line('已经读取完数据，共插入'||c1%rowcount||'条数据');
EXIT;
END IF;
END LOOP;
COMMIT;
----把商品类型编号转换成商品类型
OPEN c2;
t_num:=0;
LOOP
FETCH c2 INTO c_id,c_name;
IF c2%FOUND THEN
UPDATE productinfo_tmp t set t.category=c_name where t.category=c_id;
IF SQL%FOUND THEN
t_num:=t_num+SQL%ROWCOUNT;
END IF;
ELSE
dbms_output.put_line('转换成功，共转换'||t_num||'条');
EXIT;
END IF;
END LOOP;
----价格调整
UPDATE productinfo_tmp k SET k.productprice=k.productprice*0.95 WHERE k.productprice>2000;
dbms_output.put_line('价格调整完毕，共调整'||SQL%ROWCOUNT||'条');
COMMIT;
END;
/
已经读取完数据，共插入4条数据
转换成功，共转换4条
价格调整完毕，共调整3条

半夏微凉

七、游标的使用

评论回复