Oracle 游标
登录
标签搜索
侧边栏壁纸
博主昵称
少年

  • 累计撰写 189 篇文章
  • 累计收到 24 条评论
数据库

Oracle 游标

admin
admin
2022-12-06 / 0 评论 / 158 阅读 / 正在检测是否收录...
温馨提示：
本文最后更新于2022年12月07日，已超过717天没有更新，若内容或图片失效，请留言反馈。

一、游标

使用游标，我们可以对具体操作数据，比如查询的结果，对行，列数据进行更加细致的处理。以及对其他DML
操作进行判断等操作；类似一个数据集合。

1、语法

declare cursor 游标名称 is 查询的sql;

示例：

declare cursor cu_book is select bookname,price from t_book;

注意：游标默认是关闭的。

2、打开右边

open 游标名；

示例：

open cu_book;

3、关闭游标

close 游标名;

示例：

close cu_book;

4、游标循环

fetch 游标名 into 临时值1,临时值2...;

循环将游标中的字段保存到临时值中。

5、判断游标是否还有记录

游标名%found

二、示例

set serverout on;
--定义游标哦
declare cursor cu_book is select bookname,price from t_book;
        b_name varchar(20);
        b_price number;
begin
  open cu_book; --打开游标
  fetch cu_book into b_name,b_price; --循环游标取值
  while cu_book%found loop --如果游标里面还有值就循环处理其它逻辑
    dbms_output.put_line('书名：' || b_name || '价格' || b_price);
    fetch cu_book into b_name,b_price;
  end loop;
  close cu_book;
end;

输出结果：

image-20221206202343749

三、注意事项

1、定义的游标默认是关闭的。

2、while条件注意跳出。

3、注意关闭游标。

4、loop要有对应的end loop

四、显示游标

1、显示游标

上面定义的临时变量的类型必须和游标里面查询表的字段类型一样，如果表的字段类型改了，定义的临时变量就会报错，类型不一样。

因此就出现了显示游标。

定义：

 临时变量名 表明.字段名%type;

上面一中示例改进成显示游标：

set serverout on;
--定义游标哦
declare cursor cu_book is select bookname,price from t_book;
        b_name t_book.bookname%type;
        b_price t_book.price%type;
begin
  open cu_book; --打开游标
  fetch cu_book into b_name,b_price; --循环游标取值
  while cu_book%found loop --如果游标里面还有值就循环处理其它逻辑
    dbms_output.put_line('书名：' || b_name || '价格' || b_price);
    fetch cu_book into b_name,b_price;
  end loop;
  close cu_book;
end;

输出结果：

image-20221206203547580

2、字段类型

表示表中字段数据类型。

%type

3、行类型

表示表中一行数据类型。

%rowtype

示例：

set serverout on;
--定义游标哦
declare cursor cu_book is select * from t_book;
        book t_book%rowtype;  -- 行类型
begin
  open cu_book; --打开游标
  fetch cu_book into book; --循环游标取值
  while cu_book%found loop --如果游标里面还有值就循环处理其它逻辑
    dbms_output.put_line('书名：' || book.bookname || '价格' || book.price);
    fetch cu_book into book;
  end loop;
  close cu_book;
end;

注意：

1、游标数据变成select * 查询全部了。

2、fetch循环游标时，是将游标里面记录复制到定义的book行类型变量里面了。

3、取值时，是用行类型变量.字段名取值。

输出结果：

image-20221206204806574

五、隐式游标

不用显示定义游标，但是显示的功能更强大。

1、DML 执行过程中为真，结束后为假

SQL%ISOPEN 布尔型DML 执行过程中为真，结束后为假

示例：

begin 
  if sql%isopen then 
    dbms_output.put_line('游标打开的');
    else 
      dbms_output.put_line('游标未打开');
  end if;
end;

输出结果：

image-20221206211927471

2、DML 语句成功执行的数据行数

SQL%ROWCOUNT 整型代表DML 语句成功执行的数据行数

示例：

declare b_count number;
begin
  select count(*) into b_count from t_book;
  dbms_output.put_line('游标捕获行数：'|| sql%rowcount);
end;

输出结果：只成功执行了一次DML查询操作。

image-20221206212639067

示例2：

begin
  update t_book set bookname='xxxx' where id=11111;
  if sql%rowcount=1 then
    dbms_output.put_line('执行修改成功');
    else 
      dbms_output.put_line('未执行成功');
  end if;
end;

输出结果：

image-20221206213828592

未成功，因为没有id=11111的记录。

修改id=1，重新执行；

begin
  update t_book set bookname='xxxx' where id=1;
  if sql%rowcount=1 then
    dbms_output.put_line('执行修改成功');
    else 
      dbms_output.put_line('未执行成功');
  end if;
end;

image-20221206213945560

修改成功，因为有id=1的记录，成功执行修改了的。

3、插入、删除、更新或单行查询操作成功

SQL%FOUND 布尔型值为TRUE 代表插入、删除、更新或单行查询操作成功

4、与SQL%FOUND 属性返回值相反

SQL%NOTFOUND 布尔型与SQL%FOUND 属性返回值相反

六、动态游标

1、定义强类型动态游标

declare type 类名 is ref cursor return 表明%rowtype;

2、定义一个动态游标对象

游标对象 类名;

示例：查询表中是否有"jvm并发"的书，如果有就输出改记录信息，没有则查询全部记录，并输出。

--定义一个booktype类似一个类
declare type booktype is ref cursor return t_book%rowtype;
--定义一个动态游标对象
cu_book booktype;
--返回行数
b_count number;
--定义一个行类型变量
b_rowdata t_book%rowtype;
begin
  select count(*) into b_count from t_book where bookname='jvm并发1';
  --如果没有查到JVM并发这本书，就查询全部记录,保存到动态游标中。
  if b_count=0 then
    open cu_book for select * from t_book; --注意打开游标
    else 
      open cu_book for select * from t_book where bookname='jvm并发';
  end if;
  
  --循环游标将每条记录，保存到行变量中。   因为cu_book动态游标里面可能一条数据或全部的记录。
  fetch cu_book into b_rowdata;
  --判断游标是否还有数据，有就循环输出行变量中字段数据
  while cu_book%found 
    loop
    dbms_output.put_line('书名：'||b_rowdata.bookname || '价格：' || b_rowdata.price);
    --还有数据就循环将游标数据保存到行变量中
    fetch cu_book into b_rowdata;
  end loop;
  close cu_book;
end;

当bookname='jvm并发1'时，不存在该记录，因此查询全部记录信息。

输出结果：

image-20221206221432209

当bookname='jvm并发'时，存在该记录，因此输出记录信息。

image-20221206221533405

3、定义弱类型动态游标

弱类型比强类型强大。

弱类型动态游标定义，只是少了返回类型的定义，没有了返回值。

declare type 游标类型 is ref cursor;

需求：查询t_book中是否有书名为“jvm并发”的记录，有就输出t_book所有记录数据，没有则输出t_booktype表所有记录数据。

原始表数据：

image-20221206225120050

image-20221206225140903

代码：

--定义弱类型动态游标 
declare type notknowtype is ref cursor;
--定义一个动态游标对象
notknow notknowtype;
--用于统计是否存在记录
b_count number;
cu_book t_book%rowtype;
cu_booktype t_Booktype%Rowtype;
begin
  select count(*) into b_count from t_book where bookname='jvm并发';
  if b_count >0 then --不存在
    open notknow for select * from t_book; --查询所有t_book数据到游标
    --将游标对象循环赋值到t_book行数据中
    fetch notknow into cu_book;
    while notknow%found  --如果游标对象中有数据，则循环输出全部记录信息
      loop
        dbms_output.put_line('书ID:' || cu_book.id || '书名：' || cu_book.bookname || '价格：' || cu_book.price);
        fetch notknow into cu_book;--继续循环一下条数据
      end loop;
    close notknow;
  else 
    open notknow for select * from t_booktype;
    fetch notknow into cu_booktype;
    while notknow%found 
      loop
        dbms_output.put_line('t_booktype id:' || cu_booktype.id || 't_booktype name:' || cu_booktype.booktypename);
        fetch notknow into cu_booktype;
      end loop;
      close notknow;
  end if;
end;

输出结果：

书ID:3书名：jvm并发价格：66
书ID:4书名：c++价格：99
书ID:5书名：C#价格：51
书ID:1书名：xxxx价格：55
书ID:2书名：go秘籍价格：78

修改条件中书名为不存在书名，重新执行：

--定义弱类型动态游标 
declare type notknowtype is ref cursor;
--定义一个动态游标对象
notknow notknowtype;
--用于统计是否存在记录
b_count number;
cu_book t_book%rowtype;
cu_booktype t_Booktype%Rowtype;
begin
  select count(*) into b_count from t_book where bookname='jvm并发111';
  if b_count >0 then --不存在
    open notknow for select * from t_book; --查询所有t_book数据到游标
    --将游标对象循环赋值到t_book行数据中
    fetch notknow into cu_book;
    while notknow%found  --如果游标对象中有数据，则循环输出全部记录信息
      loop
        dbms_output.put_line('书ID:' || cu_book.id || '书名：' || cu_book.bookname || '价格：' || cu_book.price);
        fetch notknow into cu_book;--继续循环一下条数据
      end loop;
    close notknow;
  else 
    open notknow for select * from t_booktype;
    fetch notknow into cu_booktype;
    while notknow%found 
      loop
        dbms_output.put_line('t_booktype id:' || cu_booktype.id || 't_booktype name:' || cu_booktype.booktypename);
        fetch notknow into cu_booktype;
      end loop;
      close notknow;
  end if;
end;

输出结果：

t_booktype id:2t_booktype name:go
t_booktype id:1t_booktype name:java history

注意:成对的结束，最后的分号，关闭游标。

七、查看游标

1、查看系统游标数(最大游标数)

select value from v$parameter where name = 'open_cursors';

image-20221207221603938

2、查看当前打开的游标数目

select count(*) from v$open_cursor;

image-20221207221544460

3、查看游标使用情况

select o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type, count(*) num_curs 
from v$open_cursor o, v$session s 
where user_name = '用户名' 
and o.sid = s.sid 
group by o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type 
order by num_curs desc;

image-20221207221523205

4、修改Oracle最大游标数

alter system set open_cursors=2000 scope=both;

5、各用户的打开游标总数

SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME;

image-20221207221459143

6、查找数据库各用户各个终端的缓存游标数

SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) 
FROM (
  SELECT A.VALUE, S.MACHINE, S.USERNAME 
  FROM V$SESSTAT A, V$STATNAME B, V$SESSION S 
  WHERE A.STATISTIC# = B.STATISTIC# 
  AND S.SID = A.SID 
  AND B.NAME = 'session cursor cache count'
) AA 
GROUP BY AA.USERNAME, AA.MACHINE 
ORDER BY AA.USERNAME, AA.MACHINE;

image-20221207221425248

7、查找数据库各用户各个终端的打开游标数

SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) 
FROM (
  SELECT A.VALUE, S.MACHINE, S.USERNAME 
  FROM V$SESSTAT A, V$STATNAME B, V$SESSION S 
  WHERE A.STATISTIC# = B.STATISTIC# 
  AND S.SID = A.SID 
  AND B.NAME = 'opened cursors current'
) AA 
GROUP BY AA.USERNAME, AA.MACHINE 
ORDER BY AA.USERNAME, AA.MACHINE;

image-20221207221712529

3
oracle游标cursor存储过程
版权属于： admin
本文链接： https://yanxizhu.com/index.php/archives/181/
作品采用： 《 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 》许可协议授权
相关推荐
Oracle 存储过程和函数
Oracle 存储过程和函数
 Oracle 触发器
Oracle 触发器
 Oracle 权限、视图、约束
Oracle 权限、视图、约束
 Oracle常用函数
Oracle常用函数

评论 (0)

取消