Oracle中循环控制语句及游标使用

create tablespace tableu
datafile 'D:\tableu.dbf'
size 50m
autoextend on 
next 50m maxsize 2048m;

create user student identified by student default tablespace tableu;
grant connect,resource to student;

create table stuinfo
(
stuid int primary key,
stuname varchar(10) not null,
age int check(age>0 and age<100),
sex varchar(10) default '男',
createTime date default sysdate
)

create table stuscore
(
       scoreid int primary key,  
       stuid int references stuinfo(stuid) not null,  
       subject varchar(10) not null,  
       score float check(score>=0 and score<=100)     
)

create table toys
(
  toyid int primary key,
  toyname varchar(10) not null,
  toyprice float not null
)

insert into toys(toyid,toyname,toyprice) values (SQ_ccc.Nextval,'MP3',300);
insert into toys(toyid,toyname,toyprice) values (SQ_ccc.Nextval,'平板电脑',6000);
insert into toys(toyid,toyname,toyprice) values (SQ_ccc.Nextval,'手机',2700);
insert into toys(toyid,toyname,toyprice) values (SQ_ccc.Nextval,'手表',500);
insert into toys(toyid,toyname,toyprice) values (SQ_ccc.Nextval,'VR',1600);
select * from toys;
delete from toys;


insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'11111',18);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'22222',16);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'33333',26);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'44444',32);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'55555',15);
insert into stuinfo(stuid,stuname,age) values (SQ_aaa.Nextval,'66666',16);
select * from stuinfo;
delete from stuinfo;

select * from user_sequences;

insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,1,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,2,'jsp',76);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,3,'jsp',56);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,4,'jsp',93);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,5,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bbb.Nextval,6,'jsp',75);
select * from stuscore;
delete from stuscore;

-----------赋值-----------
declare
       numbers int;
       myscore stuscore.score%type;
begin
     numbers :=3;
     select score into myscore from stuscore where stuid=numbers;
     Dbms_Output.put_line('编号为：'||numbers||'学生的成绩是：'||myscore);
end;      

-----------if分支判断-----------
declare 
       myscore float;
begin
     myscore:=59;
     if myscore<60 then Dbms_Output.put_line('快回去修炼吧！');
     elsif myscore <80 then Dbms_Output.put_line('厉害了，Word哥！');
     else Dbms_Output.put_line(',,,,,,,,,,,,,,,');
     end if;
end;

----------case分支判断-----------
declare
       myscore stuscore.score%type;
begin
     select score into myscore from stuscore where stuid=2;
     case
     when myscore<60
     then Dbms_Output.put_line('低等成绩');
     when myscore>=60 and myscore<=80
     then Dbms_Output.put_line('中等成绩');
     else Dbms_Output.put_line('优秀成绩');
     end case;
end;

---------循环控制语句----------
------Loop语句------
declare
       cou int :=10;
       result int :=0;
begin
     Dbms_Output.put_line('循环开始');
Loop
    exit when cou>20;
    result :=result+cou;
    Dbms_Output.put_line('进入循环...'||cou||' 结果是：'||result);
    cou:=cou+1;
    end loop;
    Dbms_Output.put_line('循环结束，结果为：'||result);
end;

-----For循环-----
declare 
       result int :=0;
begin 
     Dbms_Output.put_line('循环开始。。。。。。');
     for cou in 10..20
     loop
       result :=result+cou;
       Dbms_Output.put_line('进入循环...'||cou||' 结果是：'||result);
    end loop;
    Dbms_Output.put_line('循环结束，结果为：'||result);
end;
-----While循环-----
declare
       cou int :=10;
       result int :=0;
begin
     Dbms_Output.put_line('循环开始.......');
     while cou <20
     loop
         result :=result+cou;
         Dbms_Output.put_line('进入循环...'||cou||' 结果是：'||result);
         cou :=cou+1;
    end loop;
    Dbms_Output.put_line('循环结束，结果为：'||result);
end;

--------------------------游标--------------------------

------------只有在 DML 语句影响一行或多行时，才返回 True
begin
     update toys set toyprice=2700
     where toyid='03';
     if sql%found then
       dbms_output.put_line('表已更新');
     end if;
end;
--------------如果 DML 语句不影响任何行，则返回 True 
declare
       v_toyid toys.toyid%type :='&toyid';
       v_toyname toys.toyname%type :='&toyname';
begin
     update toys set name=v_toyname
     where toyid=V_toyid;
     if sql%notfound then
       dbms_output.put_line('编号未找到');
     else
       dbms_output.put_line('表已更新');  
     end if;
end;
-------------返回 DML 语句影响的行数
begin 
     update toys
     set toyname='VC'
     where toyid='1';
     dbms_output.put_line(sql%rowcount);
end;
-------------
declare
       my_toy_price toys.toyprice%type;
cursor toy_cur is                       --声明游标
       select toyprice from toys
       where toyprice<2700;
begin
     open toy_cur;                      --打开游标
     loop
      fetch toy_cur into my_toy_price;  --提取行
      exit when toy_cur%notfound;
      dbms_output.put_line('toyprice=:玩具单价=：'||my_toy_price);
      end loop;
      close toy_cur;                    --关闭游标
end;

-----------------------每次获取游标中一整行数据
declare
       toysrow toys%rowtype;    
       cursor toy_cursor is select * from toys where toyprice<1500;
begin
     open toy_cursor;
     loop
         fetch toy_cursor into toysrow;
         exit when toy_cursor%notfound;
         dbms_output.put_line('编号为：'||toysrow.toyid||'名字='||toysrow.toyname);
     end loop;
end;
-----------------------定义一个带参数的游标
declare
       toysrow toys%rowtype;
       foundprice toys.toyprice%type;
       cursor toy_cursor (price toys.toyprice%type) is select * from toys where toyprice<price;
begin
     foundprice :='&要查询的条件';
     open toy_cursor(foundprice);
     loop
         fetch toy_cursor into toysrow;
         exit when toy_cursor%notfound;
         dbms_output.put_line('编号为：'||toysrow.toyid||'名字='||toysrow.toyname);
         end loop;
end;

----------------------带参数的游标
declare
       cursor toy_cur(price float) is select * from toys where toyprice>price;
       toysRow toys%rowtype;   ---声明一个变量，变量数据类型为表的一行的结构
begin
     open toy_cur(100);
     loop
     fetch toy_cur into toysRow;
     exit when toy_cur%notfound;
     dbms_output.put_line(toysRow.toyname||'---'||toyRow.toyprice);
     end loop;
     close toy_cur;
end;  
----------------------循环游标
declare
       cursor toy_cur(price float) is select * from toys where toyprice>price;
       toys toys%rowtype; --声明一个变量，变量数据类型为表的一行的结构
begin
     for mydata in toy_cur
     loop
         dbms_output.put_line(mydata.toyname);
         end loop;
end;
----------------------引用游标
declare
       type ref_toy_cur is ref cursor; --声明引用游标类型
       sss ref_toy_cur; --声明一个游标变量
       toysRow toys%rowtype;
begin
     open sss for select * from toys;
     loop
     fetch sss into toyRow;
     exit when sss%notfound;
     dbms_output.put_line(toysRow.toyname||'---'||toysRow.toyprice);
         end loop;
         close sss;
end;