--1) 查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的
--学生编号、学生名称、图书编号、图书名称、借出日期;
select s.stuid, s.stuname, b.bid, b.title, bo.t_time
from borrow bo
join student s on bo.stuid = s.stuid
join book b on bo.bid = b.bid
where bo.t_time between to_date('2007-12-15', 'yyyy-mm-dd') and
to_date('2008-01-08', 'yyyy-mm-dd') and s.major = '计算机';
--2) 用pl/sql匿名块实现,查询所有借过图书的学生编号、学生名称、专业;(提示:用游标)
declare
cursor c_student is
select * from student;
cursor c_borrow is
select * from borrow;
begin
dbms_output.put_line('学生编号 ' || ' 学生姓名 ' || ' 专业');
for v_student in c_student loop
for v_borrow in c_borrow loop
if v_student.stuid = v_borrow.stuid then
dbms_output.put_line(v_student.stuid || ' ' ||
v_student.stuname || ' ' ||
v_student.major);
exit;
end if;
end loop;
end loop;
end;
--3) 查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期;
select s.stuname, b.title, bo.t_time, bo.b_time
from borrow bo
join student s on bo.stuid = s.stuid
join book b on bo.bid = b.bid
where b.author = '安意如';
--4) 查询目前借书但未归还图书的学生名称及未还图书数量;
--(要求: 未还图书数,用函数实现,并在sql语句中调用)
create or replace type t_borrowlist_object as object(stuid varchar2(20),
books number);
create or replace type t_borrowlist_table as table of t_borrowlist_object;
create or replace function f_borrowlist
return t_borrowlist_table is v_rs t_borrowlist_table;
begin
select t_borrowlist_object(s.stuname, count(*)) BULK COLLECT
INTO v_rs
from borrow bo
join student s on bo.stuid = s.stuid
join book b on bo.bid = b.bid
where bo.b_time is null
group by s.stuname;
return v_rs;
end;
select * from table(f_borrowlist());
--5)用一个存储过程完成还书功能,输入参数为学号和书号,把当前日期作为还书日期。
create or replace function f_rebook(v_stuid student.stuid%type,
v_bid book.bid%type) return varchar2 is
PRAGMA AUTONOMOUS_TRANSACTION;
v_flag number;
v_borrowid borrow.borrowid%type;
v_rs varchar2(200);
begin
select count(*)
INTO v_flag
from borrow
where b_time is null
and stuid = v_stuid
and bid = v_bid;
if v_flag >= 1 then
select borrowid
INTO v_borrowid
from borrow
where b_time is null
and stuid = v_stuid
and bid = v_bid
and rownum <= 1;
v_rs := v_stuid || ' 号同学还 ' || v_bid || ' 图书 成功';
update borrow set b_time = sysdate where borrowid = v_borrowid;
commit;
else
v_rs := v_stuid || ' 号同学还 ' || v_bid || ' 图书 失败';
end if;
return v_rs;
end;
select * from borrow;
select f_rebook('1001', 'B001') from dual;