Oracle面试题

要求:

 有一个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;