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);
oracle游标的使用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
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_id%type :='&toyid';
v_ame%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%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;
open toy_cursor;
loop
fetch toy_cursor into toysrow;
exit when toy_cursor%notfound;
dbms_output.put_line('编号为：'||id||'名字='||ame);
end loop;
end;
-----------------------定义⼀个带参数的游标
declare
toysrow toys%rowtype;
price%type;
cursor toy_cursor (price%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('编号为：'||id||'名字='||ame);
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_ame||'---'||price);
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_ame);
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_ame||'---'||price);
end loop;
close sss;
end;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论