Oracle学习笔记：游标Cursor

PL/SQL代码块中只能用select语句进行赋值 select…into…from….，不能查询一个结果集
(如需用表请将鼠标移到底部，有创建表代码)
(1).隐式游标

begin
     dbms_output.put_line('执行之前，影响的行数：'||SQL%ROWCOUNT);
     delete from orderinfo where orderid=20160002;
     dbms_output.put_line('执行之后，影响的行数：'||SQL%ROWCOUNT);
end;

（2）.显示游标:定义游标时指定查询语句

     CURSOR  游标名  IS  操作语句；   --1、定义游标 

     OPEN   游标名；  --2、打开游标
     LOOP
       FETCH   游标名   INTO    变量名；  --3.读取游标
             EXIT  WHEN   游标名%NOTFOUND;
             处理语句；
      END   LOOP;
      CLOSE    游标名；--4.关闭游标

    create or replace procedure proc1
    as
      cursor cur_user is select * from userinfo; --1.定义游标
      theUser userinfo%rowtype; --定义变量与userinfo数据行映射，用于存储游标读取到的数据 
    begin
      --2.打开游标
      open cur_user;
      LOOP
        --3.获取游标的下一行数据
        FETCH CUR_USER INTO theUser;
        --游标没有读到数据退出循环
        exit when CUR_USER%notfound; 
        ---游标读到数据就输出
        dbms_output.put_line(theUser.userId||'  '||theUser.userName);
      END LOOP; 
      dbms_output.put_line('数据备份完毕！');
      --4.关闭游标
     close cur_user;
     end;

---

示例2：

create or replace procedure proc1(upoint int)
as
  cursor cur_user is select * from userinfo where userPoint>upoint; --1.定义游标
  theUser userinfo%rowtype; --定义变量与userinfo数据行映射，用于存储游标读取到的数据 
begin
  --2.打开游标
  open cur_user;
  LOOP
    --3.获取游标的下一行数据
    FETCH CUR_USER INTO theUser;
    --游标没有读到数据退出循环
    exit when CUR_USER%notfound; 
    ---游标读到数据就输出
    dbms_output.put_line(theUser.userId||'  '||theUser.userName);
  END LOOP; 
  dbms_output.put_line('数据备份完毕！');
  --4.关闭游标
 close cur_user;
end;
call proc1(0);

示例3：带参数的游标

create or replace procedure proc1
as
  cursor cur_user(upoint int) is select * from userinfo where userPoint>upoint; --1.定义游标
  theUser userinfo%rowtype; --定义变量与userinfo数据行映射，用于存储游标读取到的数据 
begin
  --2.打开游标
  open cur_user(2);
  LOOP
    --3.获取游标的下一行数据
    FETCH CUR_USER INTO theUser;
    --游标没有读到数据退出循环
    exit when CUR_USER%notfound; 
    ---游标读到数据就输出
    dbms_output.put_line(theUser.userId||'  '||theUser.userName);
  END LOOP; 
  dbms_output.put_line('数据备份完毕！');
  --4.关闭游标
 close cur_user;
end;
call proc1();

示例4：for循环读取游标

create or replace procedure proc1
as
  cursor cur_user is select * from userinfo; --1.定义游标
begin

   for theUser in cur_user
   loop
       dbms_output.put_line(theUser.userId||'  '||theUser.userName);
   end loop;
  dbms_output.put_line('数据备份完毕！');
end;

call proc1();

4.引用游标：定义时不指定查询语句，打开时指定查询语句

    TYPE    游标类型名   IS  REF   CURSOE; --定义类型
    游标变量名  游标类型名；  --使用类型定义游标变量
    OPNE   游标变量名   FOR   查询语句；

create or replace procedure proc1(i int)
as
  type refType is ref cursor;--定义类型 ，类型名为refType
  theCursor refType;--通过类型定义游标 游标名为theCursor
  theUser userInfo%rowtype;
  thePro proInfo%rowtype; 
begin
   if i=1 then
      open theCursor for select * from userInfo;
      loop
          fetch theCursor into theUser;
          exit when theCursor%notfound;
          dbms_output.put_line(theUser.userId||'  '||theUser.userName);
      end loop;
      close theCursor;
   elsif i=2 then
      open theCursor for select * from proInfo;
      loop
          fetch theCursor into thePro;
          exit when theCursor%notfound;
          dbms_output.put_line(thePro.proId||'  '||thePro.proName);
      end loop;
      close theCursor;
   else
     dbms_output.put_line('选择错误！');  
   end if;  
end proc1;
call proc1(2);

创建表空间

create tablespace comDB
datafile 'f:/comDB.dbf'
size 3M;
--会员表
--drop table userInfo
CREATE TABLE USERINFO
(
 USERID INT PRIMARY KEY,
 USERNAME VARCHAR(20) UNIQUE NOT NULL,
 USERPWD VARCHAR(20) NOT NULL,
 USERPOINT INT DEFAULT(0)
);

创建序列

create sequence USERID
start with 1
increment by 1;

添加数据

INSERT INTO USERINFO VALUES(USERID.NEXTVAL,'刘宇','123456',DEFAULT);
INSERT INTO USERINFO VALUES(USERID.NEXTVAL,'spring','123456',DEFAULT);

商品类型表

drop table typeInfo;
create table typeInfo
(
typeId int primary key,
typeName varchar(20) unique not null,
typeDesp varchar(20) not null
)tablespace comDB;

序列

drop sequence typeId;
create sequence typeId
start with 1
increment by 1;

添加数据

insert into typeInfo(typeId,typeName,typeDesp) values(typeId.Nextval,'肉类','香，美味');
insert into typeInfo(typeId,typeName,typeDesp) values(typeId.Nextval,'水果','甜，好吃');

商品信息表

create table proInfo
(
proId int primary key,
proName varchar(20) unique not null,
typeId int references typeInfo(typeId) not null,
proprice float check(proprice>0) not null,
proNum int check(proNum>=0) not null
)tablespace comDB;

序列

--drop sequence proId;
create sequence proId
start with 101
increment by 1;

添加数据

insert into proInfo(proId,proName,typeId，proprice，proNum)  values(proId.Nextval,'牛肉',1,34,45);
insert into proInfo(proId,proName,typeId，proprice，proNum)  values(proId.Nextval,'火龙果',2,17,56);
insert into proInfo(proId,proName,typeId，proprice，proNum)  values(proId.Nextval,'羊肉',1,23,33);
insert into proInfo(proId,proName,typeId，proprice，proNum)  values(proId.Nextval,'梨',2,10,67);

订单信息表

CREATE TABLE ORDERINFO
(
 ORDERID INT PRIMARY KEY,
 USERID INT REFERENCES USERINFO(USERID),
 PROID INT REFERENCES PROINFO(PROID),
 ORDERNUM INT CHECK(ORDERNUM>0),
 ORDERDATA DATE DEFAULT(SYSDATE)
);

创建序列

create sequence ORDERID
start with 20160001
increment by 1;

订购商品的存储过程：
–会员订购商品的业务：会员编号 商品编号 订购数量
–涉及的操作：会员积分+2(update) 商品的库存减少(update) 添加订单(insert)
–创建存储过程：
–输入参数：会员编号 商品编号 订购数量
–输出参数：订单编号

create or replace procedure orderPro(orderUserId int,orderProId int,orderCount int,newOrderId out int)
as
begin
     update userInfo set userPoint=userPoint+2 where userId=orderUserId; --会员积分+2(update)
     update proInfo set proNum=proNum-orderCount where proId=orderProId; --商品的库存减少
     insert into orderInfo VALUES(ORDERID.Nextval,orderUserId,orderProId,orderCount,default);--添加订单
     select ORDERID.CURRVAL into newOrderId from dual; --查询生成的订单编号并赋值
exception
     when others then
          newOrderId:=-1;
          rollback; --出现异常回滚操作
end orderPro;

测试:调用存储过程

declare
   newOrderId int;
begin
   orderPro(1,101,50,newOrderId); 
   if newOrderId<0 then
      DBMS_OUTPUT.put_line('订单失败！'); 
   else
      DBMS_OUTPUT.put_line('订购成功，订单编号为：'||newOrderId); 
   end if;
end;

查询数据

SELECT * FROM USERINFO; --用户
select * from TYPEINFO; --商品类型表
select * from PROINFO; --商品信息表
select * from ORDERINFO; --订购信息表