要求:
有一个emp表,表中有age,empname,email三个字段,要求查询出年龄大于23且email以'abc'开头的数据
按年龄倒序排列
/*创建表tt*/
create table tt
(
age number ,
empname varchar2(9),
email varchar2(20)
)
/*向表冲插入信息*/
insert into tt values (24,'h','ads@qq.com');
insert into tt values (25,'t','abc@qq.com');
insert into tt values (26,'l','abca@qq.com);
commit;
select * from tt
/*符合要求的SQL语句*/
select * from tt where age >23 and email like 'abc%' order by age desc;
/*创建游标 cur*/
declare
cursor cur is select * from tt where age >23 and email like 'abc%' order by age desc;
c cur%rowtype; /*创建rowtype型的变量*/
begin
open cur;/*打开游标*/
for i in 1..3/*循环范围*/
loop
fetch cur into c;/*将游标中每行数据插入到变量中*/
exit when cur%notfound;/*游标退出*/
/*打印出相关信息*/
dbms_output.put_line(c.age||' '||c.empname||' '||c.email);
end loop;
close cur;
commit;
end;