oracle游标简单使用

游标用来临时存放查询的结果集

--查看TEST用户的表空间,用户名大写
select  *  from  dba_tables cc   where cc.OWNER='TEST'

　　

--先看看当前表空间有哪些表

select  bb.TABLE_NAME  from  dba_tables bb  where bb.TABLESPACE_NAME='TESTSPACE'

　　建表语句

create table public_memo(
   ids varchar2(32) not null,
   title varchar2(255) not null,
   contents clob not null,
   address varchar(255) not null,
   longitude number(13,10) not null,
   latitude number(13,10) not null,
   created_date date,
   updated_date date,
   status varchar2(4) not null
)
comment on table   public_memo is '备忘录';
 
comment on column public_memo.ids is '主键id';
 
comment on column public_memo.title is '标题';
 
comment on column public_memo.contents is '内容';
 
comment on column public_memo.address is '地址';
 
comment on column public_memo.longitude is '经度';
 
comment on column public_memo.latitudeis '纬度';
 
comment on column public_memo.created_date is '创建时间';
 
comment on column public_memo.updated_date is '修改时间';
 
comment on column public_memo.status is '状态';

　　

insert into public_memo(ids,title,contents,address,longitude,latitude，created_date,updated_date,status)
values (sys_guid(),'1','23','浦东大道',12.1234,13.2345,sysdate,sysdate,'1');
 
insert into public_memo(ids,title,contents,address,longitude,latitude，created_date,updated_date,status)
values (sys_guid(),'2','24','浦东大道001',12.1234,13.2345,sysdate,sysdate,'2');
 
 
insert into public_memo(ids,title,contents,address,longitude,latitude，created_date,updated_date,status)
values (sys_guid(),'3','25','浦东大道002',12.1234,13.2345,sysdate,sysdate,'3');

　　打印title为2的数据，输出

 

 

--游标使用
declare
 cursor memo_cursor is select  *  from  PUBLIC_MEMO tt  where tt.title=2;
 v_memo PUBLIC_MEMO%rowtype;
begin
 open  memo_cursor;   --打开游标
   loop
     
    fetch  memo_cursor into v_memo;  ---提取游标
    exit when  memo_cursor%notfound;
    DBMS_OUTPUT.put_line('地址:'||v_memo.address);
    
    
   end loop;
 close memo_cursor;    --关闭游标
end;

　带参数的游标

--游标使用
declare
 cursor memo_cursor(v_title varchar2) is select  *  from  PUBLIC_MEMO tt  where tt.title=v_title;
 v_memo PUBLIC_MEMO%rowtype;
begin
 open  memo_cursor(3);   --打开游标
   loop
     
    fetch  memo_cursor into v_memo;  ---提取游标
    exit when  memo_cursor%notfound;
    DBMS_OUTPUT.put_line('地址:'||v_memo.address);
    
    
   end loop;
 close memo_cursor;    --关闭游标
end;

　　循环打印

--游标使用
declare
 cursor memo_cursor(v_title varchar2) is select  *  from  PUBLIC_MEMO tt  where tt.title=v_title;
begin
    for v_memo in memo_cursor(1)
   loop
    DBMS_OUTPUT.put_line('地址:'||v_memo.address);
   end loop;
end;