湘潭大学Oracle期末复习题

（4）select to_char(to_date(‘65-10-21’,‘yy-mm-dd’),‘yyyy-mm-dd’) from emp;

create or replace function F24 (n integer) return integer IS
    i integer :=1;
    total integer :=0;
begin
   for i in 1..n loop
   total :=total+i;
   end loop;
   return total;
end;
.
/
select F24(100) from dual;

create sequence seq100 increment by 1 minvalue 1;

create table 读者
(
读者号 varchar2(10) not null,
姓名 varchar2(20) not null,
生日 date ,
primary key (读者号));

create index idx03 on 读者 (姓名 asc, 生日 desc);

create or replace view s19 as
select 姓名 from a_db.学生
where 学号 in（select 学号 from A_DB.成绩 where 分数 is null )
order by 姓名 desc;

create or replace view s20 as
select 课程号 from a_db.成绩 group by 课程号 having count(课程号)>20 order by 课程号 asc;
select * from s20;

create or replace view s21 as
select distinct 课程号，姓名
from (select 成绩.*,rank()over(partition by 课程号
order by 分数 desc nulls last)RK
from a_db.成绩)R left join a_db.学生 on R.学号=学生.学号 where rk=1
order by 课程号,姓名 asc;

create view s22 as
select to_number(to_char(hiredate,‘yyyy’)) as 入职年份
from a_db.emp where sal between 3000 and 4000
order by 入职年份 asc;

create view s23 as
select deptno, round(avg(sal)) as 平均工资
from a_db.emp group by deptno;
select * from a_db.emp;

create or replace function F24 return integer as
s integer :=0;
n integer;
begin
select 参数值 into n from a_db.输入参数表 where id =1;
for e in 1…n loop
s:=s+e;
end loop;
return s;
end;
.
/
select f24() from dual;

create or replace function F25 return integer as
m integer :=0;
begin
for e in(select * from A_DB.tanbn1)
loop
if e.a>=100 and e.a<1000 then
if(e.a=trunc(e.a/100)**3+mod(trunc(e.a/10),10)**3+mod(e.a,10)**3) then
m:=m+1;
end if;
end if;
end loop;
return m;
end;
select f25( ) result from dual;