【Oracle】游标显式游标与隐式游标

奥利文儿

已于 2022-07-07 11:38:41 修改

阅读量4k

点赞数 15

文章标签： oracle 数据库 dba sql

于 2022-07-06 17:52:50 首次发布

本文链接：https://blog.csdn.net/weixin_42594598/article/details/125608103

版权

文章目录

引言
隐式游标
显式游标
修改游标数据
游标变量
游标的综合应用
代码用例对应的数据库

引言

在使用SQL语句进行查询时，获得和处理的结果是整个查询结果集，若需要进一步查询结果中的每条数据进行业务操作，仅靠SQL语句则无法实现，所以PL/SQL提出游标概念解决此类问题。
游标是一种特殊的指针，提供了对一个结果集进行逐行处理的能力。使用游标可以对结果集按行、按条件进行数据的提取、修改和删除操作。在物理结构上，游标是Oracle为用户开设的一个数据缓冲区，存放SQL语句的执行结果。
在Oracle数据库中执行的每个SQL语句都有对应的单独的游标。Oracle主要由以下两种游标类型：

隐式游标：由系统自动进行操作，用于处理DML语句和返回单行数据的SELECT查询。通过隐式游标属性可以获取SQL语句信息。
显示游标：由用户显式声明和操作的游标，用于返回多行数据的SELECT查询。

游标主要用于对结果集中的每一条数据分别进行操作，当数据量较大时，游标的使用会带来性能的降低。

隐式游标

在Oracle中，所有SQL语句都有一个执行的缓冲区，隐式游标是指向该缓冲区的指针，由系统自动隐含地打开、处理和关闭，隐式游标又称为SQL游标。隐式游标主要用于处理DML操作（INSERT\DELETE\UPDATE）及单行的SELECT…INTO语句。
隐式游标由以下特点：

在使用隐式游标由系统自动声明、打开和关闭。
通过隐式游标的属性可以获得最近所执行的SQL语句的结果信息。

隐式游标属性如下：

属性名称	描述
SQL%ISOPEN	判断隐式游标是否已经打开，该属性对任何的隐式游标总是返回FALSE，因此为其操作时总会由系统自动打开，操作完即自动关闭。
SQL%FOUND	当DML操作影响到数据行或单行查询到数据时，该属性返回TURE，否则返回FALSE。
SQL%NOTFOUND	当DML操作没有影响到数据行或没有查询到数据时，返回TURE，否则返回FALSE。
SQL%ROWCOUNT	返回DML操作影响的行数或单行查询返回的行数。如果没有影响任何行，返回0，在执行操作前，值为NULL。

隐式游标的使用：

数据更新影响行数的判断：

begin
  update tb_users set username='陈六' where user_id=3;
  commit;--执行完上述语句后，直接进行提交
  if sql%rowcount=0 then
    dbms_output.put_line('更新失败');
  else 
    dbms_output.put_line('数据更新成功！共更新'||sql%rowcount||'行！');
  end if;
end;
select * from tb_users;

根据用户id查询用户的信息：

declare
 v_userid tb_users.user_id%type;
 v_username tb_users.username%type;
begin
  v_userid:=&用户编号;
  select username into v_username from tb_users where user_id=v_userid;
  if sql%found then
    dbms_output.put_line('职工的姓名是'||v_username);
  end if; 
exception
  when no_data_found then
    dbms_output.put_line('未找到该用户');
end;

显式游标

显式游标是可以由用户显式声明和操作的游标，专用于处理返回SELECT语句返回的多行数据。

显式游标的处理过程

显式游标的处理步骤包括：定义游标、打开游标、检索游标、关闭游标。

(1)定义游标
语法：CURSOR 游标名称 IS 查询语句；
注意：

游标必须定义在PL/SQL语句块的声明部分。
定义游标时必须明确定义要使用的SQL查询语句。
定义游标时并没有生成数据，只是将定义信息保存在数据字典中。
游标定义后，可以使用“游标名称%ROWTYPE”定义记录类型的变量。

(2)打开游标
使用OPEN操作为查询打开游标。
语法： OPEN 游标名称；
注意：

只有打开游标时，才能真正创建缓冲区，从而从数据库中检索数据。
如果游标定义中的变量值发生改变，则只能在下次打开游标时游标才起作用。
游标一旦打开，就不能再次打开，只能先关闭再打开。

(3)检索游标
游标打开后，可以使用FETCH操作将游标中的数据以记录为单温检索出来放进PL/SQL变量中，然后进行过程化处理。
语法： FETCH 游标名称 INTO 变量
注意：

在检索游标之前必须先打开游标，保证创建了缓冲区，且缓冲区中有数据。
第一次使用FETCH语句时，游标指针指向第一行记录，操作完后，游标指针自动指向下一条记录。
游标指针只能向下移动，不能回退。
into子句中变量的个数、顺序、数据类型必须与缓冲区中查询结果的字段类型数量、顺序和数据类型相匹配。
游标中的查询语句通常会返回多行记录，因此检索游标的过程是一个循环的过程。

(4)关闭游标
游标对应缓冲区的数据处理完后，可以使用CLOSE操作关闭游标。即使关闭游标可以尽快释放其所占用的系统资源。

语法：CLOSE 游标名称；

属性名称	描述
游标名称%ISOPEN	判断显式游标是否已经打开，如果已经打开，返回TURE，否则返回FALSE。
游标名称%FOUND	判断最近一次使用FETCH语句时是否从缓冲区中检索到数据，如果检索到数据返回TURE，否则返回FALSE。
游标名称%NOTFOUND	与%FOUND相反。如果最近一次使用FETCH语句没有从缓冲区中检索到数据返回TURE，否则返回FALSE。
游标名称%ROWCOUNT	返回到目前为止从游标缓冲区检索的记录的个数。

显式游标的检索
由于显式游标主要采用处理返回多行数据的SELECT语句，因此对游标对应的缓冲区结果集的检索需要采用循环的方式进行。根据循环方法的不同，游标的检索分为3种方式：LOOP循环检索、WHILE循环检索和FOR循环检索。

使用LOOP循环检索游标：

declare
 CURSOR cursor_user IS select user_id,username,sex from tb_users;
 v_user cursor_user%ROWTYPE; --声明一个变量，并将查询结果的类型赋值给该变量 --使用%ROWTYPE装载一行记录
begin
  open cursor_user;  --打开游标
  loop
    fetch cursor_user into v_user;
    exit when cursor_user%notfound; --当找不到匹配数据时离开循环
    dbms_output.put_line(cursor_user%rowcount
    ||' '||v_user.user_id||' '||v_user.username||' '||v_user.sex);
  end loop;  --关闭循环
  close cursor_user;
end;

使用WHILE循环检索游标：

DECLARE
 cursor cursor_user is select sex,count(user_id) cishu from tb_users group by sex; --给聚合函数中的结果指定别名，使游标可以用别名传参
 v_user cursor_user%rowtype;
 begin
   if cursor_user%isopen then null; --sql%isopen游标是否打开，属于布尔类型，默认值为null
   else
     open cursor_user;
   end if;
   fetch cursor_user into v_user;
   while cursor_user%found loop
     dbms_output.put_line(v_user.sex||' '||v_user.cishu);
     fetch cursor_user into v_user;
   end loop;
     close cursor_user;
end;

使用WHILE循环检索游标：

DECLARE
 cursor cursor_user is select sex,count(user_id) cishu from tb_users group by sex; --给聚合函数中的结果指定别名，使游标可以用别名传参
 v_user cursor_user%rowtype;
 begin
   if cursor_user%isopen then null; --sql%isopen游标是否打开，属于布尔类型，默认值为null
   else
     open cursor_user;
   end if;
   fetch cursor_user into v_user;
   while cursor_user%found loop
     dbms_output.put_line(v_user.sex||' '||v_user.cishu);
     fetch cursor_user into v_user;
   end loop;
     close cursor_user;
end;

使用FRO循环检索游标：

declare
 cursor cursor_user is select * from tb_users;
 begin
   for v_user in cursor_user loop 
     dbms_output.put_line(cursor_user%rowcount||' '||v_user.username);
   end loop;
end;

参数化游标
参数化游标是指在定义游标时使用参数，当通过不同的参数值打开游标时，可以产生不同的结果集。

定义语法：CURSOR 游标名称（参数名称类型[，参数名称类型...]）IS 查询语句

打开参数化游标： OPEN 游标名称（参数值[,参数值...]）

参数化游标的定义和使用：

declare
 cursor cursor_user(p_user tb_users.user_id%type) is select * from tb_users where user_id=p_user; --定义参数并完成传参
 v_user cursor_user%rowtype;
begin
  open cursor_user(1);--第一次传参
  loop
    fetch cursor_user into v_user;
    exit when cursor_user%notfound;
    dbms_output.put_line('传入参数（用户ID）是：'||v_user.user_id||'，  姓名是：'||v_user.username);
    end loop;
    close cursor_user;
    --重新打开游标，传递新的参数
    open cursor_user(2);  --第二次传参
    loop
      fetch cursor_user into v_user;
      exit when cursor_user%notfound;
      dbms_output.put_line('传入参数（用户ID）是：'||v_user.user_id||'，  姓名是：'||v_user.username);
    end loop;
    close cursor_user;
end;

修改游标数据

显式游标在处理SELECT语句返回的多行数据时，还可以对检索的游标进行修改操作。在利用游标更新或删除数据库中的数据时，需要在游标定义使用FRO UPDATE子句对游标提取出来的数据进行行级锁定，这样在某个用户的数据库操作会话期间，其他用户的会话就不能再对当前游标中的数据进行修改了。

修改游标数据的游标的定义

语法：CRUSOR 游标名称 IS 查询语句 [FRO UPDATE [OF 数据列,...] [NOWAIT] ];
打开游标时，为游标中的数据增加行级锁定，游标在更新时，其他用户的会话将无法更新。
若操作数据已经被其他会话加锁，则当前会话会挂起等待，若指定了NOWAIT子句，则不等待，返回Oracle错误。
多表查询时，可以通过OF子句指定某个要加锁的表的列，从而对该表加锁，而其他表不加锁，否则所有的表都加锁。
当用户执行COMMIT或ROLLBACK操作时，数据上的锁会自动被释放。

游标变量

游标变量在定义游标时不绑定具体的查询，而是动态地打开指定类型的查询，这样的游标将更加灵活。
游标变量是一个指向多行查询结果集的指针，不与特定的查询绑定，只在打开游标变量时才能定义查询。

游标变量的使用包括5个步骤：定义游标引用类型、声明游标变量、打开游标变量、检索游标变量、关闭游标变量。

语法：定义游标引用类型 TYPE 游标引用类型名称 IS REF CURSOR [RETURN 数据类型]；

RETURN子句用于指定定义的游标类型返回的结果集类型，该类型必须是记录类型。
如果RETURN子句不省略，表示此游标只能匹配指定的查询返回结果，用其定义的变量称为强游标变量；否则称为弱游标变量，表示此游标类型可以保存任何的查询结果。

语法：声明游标变量 游标变量名称游标引用类型；

语法：打开游标变量 OPEN 游标变量 ROR 查询语句；

在打开游标变量时指定该游标变量所对应的查询语句。
如果打开的游标变量是强游标变量，则查询语句的返类型必须与游标引用类型定义中的RETURN子句指定的放回类型相匹配。

语法：检索游标变量

LOOP
  FETCH 游标变量 INTO 变量；
  EXIT WHEN 游标变量 % NOTFOUND;
  ...
END LOOP;

其中，检索游标变量只能使用LOOP循环或WHILE循环，不能使用FRO循环。

语法：关闭游标变量CLOOSE 游标变量；

强游标变量的定义：

declare
 type ref_cursor_type is ref cursor return tb_users%rowtype;--定义游标引用类型
 cursor_users ref_cursor_type; --声明游标变量：游标变量名称 游标引用类型
 v_users tb_users%rowtype;
begin
  open cursor_users for select * from tb_users;--打开游标变量：open 游标变量 for 查询语句；
  loop
    fetch cursor_users into v_users;
    exit when cursor_users%notfound;
    dbms_output.put_line('user_id:'||v_users.user_id||' '||'username:'||v_users.username);
  end loop;
  close cursor_users; --关闭游标变量
end;

弱游标变量的定义：

DECLARE 
 TYPE ref _ cursor _ type IS REF cURSOR ; 
 ref _ cursor ref _ cursor _ type ; 
 V _ emp emp$ROWTYPE ; 
 V _ dept dept$ROwTYPE ; 
 V _ choose VARCHAR2(1):= UPPER ( SUBSTR (' &E 或 D ',1,1)); 
BEGIN 
 IF v _ choose =' E ' THEN OPEN ref _ cursor OR SELECТ * FRO emp ; 
 DBMs _ oUTPU Т. put _ line (＇员工信息＇);L0OP FEТCH ref _ cursor INТO v _ emp ; 
 EXIT WHEN ref _ cursor 왕 No Т FOUND ; 
 DBMs _ oUTPU Т. put _ line ( v _ emp . empnol [' v _ emp . ename ); 
 END LOOP ; 
 CLOSE ref _ cursor ; 
ELSIF
 v _ choose =' D ' THEN OPEN ref _ cursor FOR SELЕСТ, FROМ dept ; 
 DBMS _ oUTPUT . put _ line ('部门信息＇); 
LOOP 
 FETCH ref _ cursor INTo v _ dept ; 
 EXIT WHEN ref _ cursor % NOTFOUND ; 
 DBMs _ oUTPU Т. put _ line ( v _ dept . deptnoll · v _ dept . dname ); 
END LOOP ; 
CLOSE ref _ cursor ; 
ELSE 
 DBMS _ oUTPUT . put _ line ('请输人 E （员工信息）或 D （部门信息）'); 
END IF; 
END ;

游标的综合应用

用户动态及评论和评论回复查询：

declare
 cursor cursor_dynamics(p_userid number) --定义游标变量，用于传递userid
  is select dynamic_id,idea,send_time,dtype from tb_personal_dynamics where user_id=p_userid;--查询语句，条件为参数传递的值
 cursor cursor_comment(p_dynamicid number) --定义游标变量，用于传递
  is select * from tb_comment where dynamic_id=p_dynamicid;
 cursor cursor_photo(p_dynamicid number)
  is select * from tb_photos_dynamics where dynamic_id=p_dynamicid order by display_order;
 --先声明游标，再声明给游标传参的变量
 v_dynamics cursor_dynamics%rowtype;
 v_comment cursor_comment%rowtype;
 v_photo cursor_photo%rowtype;
 v_article tb_artics_dynamics%rowtype;
 v_reply tb_comment_reply%rowtype;
begin
  open cursor_dynamics(1); --查询编号为1的用户
  loop
    fetch cursor_dynamics into v_dynamics;
    exit when cursor_dynamics%notfound;
    dbms_output.put_line('动态：'||v_dynamics.idea||'-'||v_dynamics.send_time);
    --查询动态对应的照片动态或动态文章
    if v_dynamics.dtype=1 then open cursor_photo(v_dynamics.dynamic_id);
     loop
       fetch cursor_photo into v_photo;
       exit when cursor_photo%notfound;
       dbms_output.put_line('相册：'||v_photo.photo_id);
     end loop;
     close cursor_photo;
    elsif v_dynamics.dtype=2 then
      select * into v_article from tb_artics_dynamics where dynamic_id=v_dynamics.dynamic_id; --在动态文章库2022中获取数据
      dbms_output.put_line('文章：'||v_article.article_url);
    end if;
    --查询动态对应的评论及评论回复
    open cursor_comment(v_dynamics.dynamic_id);
    loop
      fetch cursor_comment into v_comment;
      exit when cursor_comment%notfound;
      dbms_output.put_line(v_comment.user_id||'评论：'||v_comment.dycomment);
      select * into v_reply from tb_comment_reply where comment_id=v_comment.comment_id;
      dbms_output.put_line(v_reply.user_id||'回复：'||v_reply.reply_content);
    end loop;
    close cursor_comment;
  dbms_output.put_line('-----------------------');
  end loop;
  close cursor_dynamics;
exception
  when no_data_found then
    dbms_output.put_line('');
end;

代码用例对应的数据库

--TB_USERS(用户)
drop table tb_users;
CREATE TABLE tb_users(
 user_id number primary key,
 username varchar2(20) not null,
 userpwd varchar2(20) not null,
 nickname varchar2(30),
 uprofile BLOB,
 sex char(3),
 telephone varchar2(20),
 email varchar2(50),
 address varchar2(100), 
 signature varchar2(100),
 note varchar2(200),
 constraint chk_sex check(sex='男' or sex='女')
)tablespace ts_qmicrochat;

--TB_FRIENDS(好友关系)
create table tb_friends(
 user_id number,
 friend_id number,
 constraint pk_user_friend primary key(user_id,friend_id)
);

--TB_PERSONAL_DYNAMICS(个人动态)
create table tb_personal_dynamics(
 dynamic_id number primary key,
 user_id number not null,
 send_time date not null,
 send_address varchar2(50),
 idea varchar2(1000) not null,
 dtype number,
 authority number,
 constraint chk_type check(dtype=1 or dtype=2),
 constraint chk_authority check(authority=1 or authority=2),
 constraint fk_personal_dynamics_users foreign key (user_id)
   references TB_USERS(user_id)
);
--TB_PHOTOS_DYNAMICS(相册动态)
CREATE TABLE tb_photos_dynamics (
 photo_id NUMBER PRIMARY KEY,
 dynamic_id NUMBER NOT NULL, 
 photo BLOB, 
 display_order NUMBER NOT NULL, 
 CONSTRAINT fk_photos_personal_dynamics FOREIGN key (dynamic_id) 
  REFERENCES tb_personal_dynamics(dynamic_id)
  );
 -- TB_ARTIcs_DYNAMICS （文章动态） 
CREATE TABLE tb_artics_dynamics ( 
 article_id NUMBER PRIMARY KEY , 
 dynamic_id NUMBER NOT NULL , 
 picture BLOB, 
 article_url VARCHAR2(500) NOT NULL , 
 reading_num NUMBER DEFAULT 0 , 
 report_num NUMBER DEFAULT 0 , 
 CONSTRAINT fk_artics_personal_dynamics FOREIGN key (dynamic_id)
  REFERENCES tb_personal_dynamics (dynamic_id)
  );
--- B_cOMMENT （动态评论） 
CREATE TABLE tb_comment ( 
 comment_id NUMBER PRIMARY KEY , 
 dynamic_id NUMBER NOT NULL , 
 user_id NUMBER NOT NULL , 
 dycomment VARCHAR2(500) NOT NULL , 
 comm_time DATE , 
 CONSTRAINT fk_comment_dynamics FOREIGN key (dynamic_id) 
  REFERENCES tb_personal_dynamics (dynamic_id), 
 CONSTRAINT fk_comment_users FOREIGN key (user_id)
  REFERENCES tb_users(user_id)
  );
-- TB_coMMENT_REPLY （评论回复） 
CREATE TABLE tb_comment_reply (
 reply_id NUMBER PRIMARY KEY , 
 comment_id NUMBER NOT NULL , 
 user_id NUMBER NOT NULL ,
 reply_content VARCHAR2(500) NOT NULL , 
 CONSTRAINT fk_comment_reply_comment FOREIGN key (comment_id)
  REFERENCES tb_comment (comment_id), 
 CONSTRAINT fk_comment_reply_users FOREIGN key (user_id)
  REFERENCES tb_users( user_id)
  );
-- TB_USER_CHAT （私聊记录表）-- 
CREATE TABLE tb_user_chat (
 userchat_id NUMBER PRIMARY KEY , 
 send_user_id NUMBER NOT NULL , 
 receive_user_id NUMBER NOT NULL , 
 chat_content VARCHAR2(500) NOT NULL , 
 chat_time DATE NOT NULL , 
 CONSTRAINT fk_chat_send_users FOREIGN key (send_user_id)
  REFERENCES tb_users (user_id), 
 CONSTRAINT fk_chat_receive_users FOREIGN key (receive_user_id)
  REFERENCES tb_users (user_id)
  );
-- TB_GROUPS （微聊群） 
CREATE TABLE tb_groups (
 group_id NUMBER PRIMARY KEY , 
 group_name VARCHAR2(50) NOT NULL , 
 group_logo BLOB, 
 user_id NUMBER NOT NULL , 
 creation_time DATE NOT NULL , 
 max_person_num NUMBER (4) NOT NULL , 
 real_person_num NUMBER (4) NOT NULL , 
 CONSTRAINT fk_groups_users FOREIGN key (user_id)
  REFERENCES tb_users (user_id)
  );
-- USERS_GROUPS （用户＿群） 
CREATE TABLE users_groups (
 user_id NUMBER , 
 group_id NUMBER , 
 group_nickname VARCHAR2(20), 
 top_group NUMBER , 
 escape_disturb NUMBER , 
 CONSTRAINT pk_user_group PRIMARY KEY (user_id,group_id), 
 CONSTRAINT chk_top_group CHECk (top_group =0 or top_group =1), 
 CONSTRAINT chk_escape_disturb CHECk (escape_disturb =0 or escape_disturb =1)
 );
--TB_GROUP_CHAT （群聊记录）-- 
CREATE TABLE tb_group_chat (
 groupchat_id NUMBER PRIMARY KEY , 
 group_id NUMBER NOT NULL, 
 user_id NUMBER NOT NULL,
 send_time DATE NOT NULL,
 send_content VARCHAR2(500) NOT NULL,
 CONSTRAINT fk_group_chat_groups FOREIGN key(group_id)
  REFERENCES tb_groups(group_id),
 CONSTRAINT fk_group_chat_users foreign key(user_id)
  REFERENCES tb_users(user_id)
  );

奥利文儿

关注

15
点赞
踩
30

收藏

觉得还不错? 一键收藏
0
评论
【Oracle】游标显式游标与隐式游标

在使用SQL语句进行查询时，获得和处理的结果是整个查询结果集，若需要进一步查询结果中的每条数据进行业务操作，仅靠SQL语句则无法实现，所以PL/SQL提出游标概念解决此类问题。游标是一种特殊的指针，提供了对一个结果集进行逐行处理的能力。使用游标可以对结果集按行、按条件进行数据的提取、修改和删除操作。在物理结构上，游标是Oracle为用户开设的一个数据缓冲区，存放SQL语句的执行结果。在Oracle数据库中执行的每个SQL语句都有对应的单独的游标。Oracle主要由以下两种游标类型：游标主要用于对结果集中
复制链接

扫一扫