oracle测试题（4月23）含完整的创建表空间和用户，调用输出参数为游标的存储过程

1.题目

• 见资源文件

2.答案

• 1.创建用户和表空间

--system用户下
--创建表空间
create tablespace school_tablespace
			datafile 'D:\oracleSpace\app\db\\school.dbf'
		 	size 10M
			autoextend on  next 2M MAXSIZE UNLIMITED;
--创建用户
create user c##school
			 IDENTIFIED  by www123
			 default tablespace school_tablespace;
--为用户授权
grant connect,resource,unlimited tablespace,create any view  to c##school;
--解锁用户
alter user c##school account unlock;

• 2.创建student表

create table student(
    stuid varchar2(10),
    stuname varchar2(10) not null,
    major varchar2(50) not null
)

• 3.创建book表

create table book(
    bid varchar2(10),
    title varchar2(50) not null,
    author varchar2(20) not null
)

• 4.创建borrow表

--创建borrow表
create table borrow(
    borrowid varchar2(10),
    stuid varchar2(10),
    bid varchar2(10),
    t_time date,
    b_time date
)

• 5.添加主外键约束

alter table student
add constraint pk_student_id primary key (stuid);
alter table book
add constraint pk_book_id primary key (bid);

alter table borrow
add constraint pk_borrow_id primary key (borrowid);
alter table borrow
add constraint fk_borrow_stuid foreign key(stuid) references student(stuid);
alter table borrow
add constraint fk_borrow_bid foreign key(bid) references book(bid);

• 6.添加student表、book表和borrow表数据，分别插入至少5条测试数据。

insert into book(bid,title,author) values('1001','java基础','黑马程序员');
insert into book(bid,title,author) values('1002','js前端','黑马程序员');
insert into book(bid,title,author) values('1003','jquery全书','黑马程序员');
insert into book(bid,title,author) values('1004','oracle数据库','黑马程序员');
insert into book(bid,title,author) values('1005','计算机网络','黑马程序员');

insert into student(stuid,stuname,major) values('A1','小明','java');
insert into student(stuid,stuname,major) values('A2','小红','前端');
insert into student(stuid,stuname,major) values('A3','张三','.net');
insert into student(stuid,stuname,major) values('A4','李四','计算机');
insert into student(stuid,stuname,major) values('A5','王五','网络工程');

insert into borrow(borrowid, STUID, BID, T_TIME, B_TIME)values('001','A1','1002',sysdate-1,null);
insert into borrow(borrowid, STUID, BID, T_TIME, B_TIME)values('002','A2','1002',sysdate-1,null);
insert into borrow(borrowid, STUID, BID, T_TIME, B_TIME)values('003','A3','1001',sysdate-2,sysdate-1);
insert into borrow(borrowid, STUID, BID, T_TIME, B_TIME)values('004','A4','1004',sysdate-1,sysdate);
insert into borrow(borrowid, STUID, BID, T_TIME, B_TIME)values('005','A5','1005',sysdate-3,null);

• 7.查询“计算机”专业学生在“2009-12-15”至“2012-6-8”时间段 内借书的学生编号、学生名称、图书编号、图书名称、借出日期

select s.stuid 学生编号,s.stuname 学生名称,b.bid 图书编号,bk.title 图书名称,b.t_time 借出日期 from student s,book bk,borrow b where bk.bid=b.bid and s.stuid=b.stuid and major='计算机';

• 8查询所有借过图书的学生编号、学生名称、专业

select stuid 学生编号,stuname 学生名称,major 专业 from student where stuid in( select stuid from borrow);

• 9.创建视图，查询所有借过的图书信息，包括学生姓名、图书名称、借出日期、归还日期

create or replace view view_borrow as
select s.stuid 学生名称,bk.title 图书名称,b.t_time 借出日期,b.b_time 归
还日期 from student s,book bk,borrow b where s.stuid=b.stuid and
 bk.bid=b.bid;
select * from view_borrow;

• 10.编写存储过程并调用，实现查询目前借书但未归还图书的学生名称及未还图书数量

create or replace PROCEDURE prop_borrowsearch(cur_msg out sys_refcursor)
as
   begin
    open cur_msg for select stuname 学生名称,b.sl 借书数量 from student s, (select stuid,count(*) sl from borrow  where  b_time is null group by stuid) b where s.stuid=b.stuid;
   end prop_borrowsearch;
   
--调用存储过程
--不会