最新Oracle数据库-图书管理系统实验报告.doc

《最新Oracle数据库-图书管理系统实验报告.doc》由会员分享，可在线阅读，更多相关《最新Oracle数据库-图书管理系统实验报告.doc（41页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-dateOracle数据库-图书管理系统实验报告Oracle数据库-图书管理系统实验报告图书管理系统（Oracle）1、创建表空间student_library2、（1）创建表Administrator（2）创建表library（3）创建表student（4）创建表lent_library（5）创建表booktype3、(1)创建表空间student_librarycreat

2、e tablespace student_library datafile C:oracleproduct10.2.0oradataorclstudent_library size 100M autoextend on;(2)创建表Administratorcreate table Administrator( AdministratorID number(20) primary key, AdministratorName varchar2(25), sex varchar2(5), beizhu varchar2(30), bookID varchar2(20),foreign key(b

3、ookID) references library(bookID)tablespace student_library；向Administrator表插入数据insert into Administratorvalues(100421101,张三,男, ,100421)insert into Administratorvalues(100421102,李四,女, ,100422)insert into Administratorvalues(100421103,王五,男, ,100423)对Administrator表删除数据delete from Administrator where Ad

4、ministratorID=100421103删除Administrator表中管理员编号为100421103的一行数据对Administrator表查询数据select * from Administrator order by AdministratorID按照管理员的ID号对Administrator表进行排列向Administrator表修改数据update Administrator set AdministratorName=王明where AdministratorID=100421101对Administrator表中管理员编号为100421101的姓名改为“王明”update

5、 Administrator set sex=女where AdministratorID=100421103对Administrator表中管理员编号为100421103的性别改为“女”(3)创建表librarycreate table library( bookID number(20) primary key, bookName varchar2(20), author varchar2(10), press varchar2(20), publishDate varchar2(50), price number(20), leibienumber number(20), state v

6、archar2(8), studentID number(20), foreign key(studentID) references student(studentID)tablespace student_library;向library表插入数据insert into library values(100421,Oracle数据库管理,马晓玉,清华大学出版社,2011-11-8,40,013999, ,100421203)insert into library values(100422,ASP.NET网站设计,唐慧,南京大学出版社,2011-5-18,32,165923, ,10042

7、1205)insert into library values(100423,JavaScript程序设计,徐元,苏州大学出版社,2011-2-12,28,265894, ,100421207)对library表删除数据delete from library where bookID=100422删除library表中书籍编号为100422的一行数据向library表查询数据select bookID,bookName,author,press,publishDate,price from library order by bookID查询library表中的bookID,bookName,a

8、uthor,press,publishDate,price的数据根据书籍的编号进行排序向library表修改数据update library set author=张敏where bookID=100422对library表中书籍编号为100422的作者改为“张敏”(4)创建表studentcreate table student( studentID number(20) primary key, studentName varchar2(50), sex varchar2(2), department varchar2(10), studentPhone number(15), borro

9、wID number(20), password number(10)tablespace student_library;向student表插入数据insert into studentvalues(100421203,王逸,男,信电系,13913645678,123456,521568)insert into studentvalues(100421205,刘娟,女,食品系,18862312345,256302,269756)insert into studentvalues(100421207,张一凡,男,园林园艺系,13962369850,687456,305621)对student表

10、删除数据delete from student where studentName=张一凡删除student表中学生姓名为张一凡的一行数据向student表修改数据update student set department=经贸系where studentID=100421205对student表中学生学号为100421205的系部名称改为“经贸系”(5)创建表lent_librarycreate table lent_library( lentID number(20) primary key, bookID number(20), borrowbookID number(20), borr

11、owDate varchar2(50), borrowState varchar2(30), foreign key(bookID) references library(bookID)tablespace student_library;向lent_library表插入数据insert into lent_libraryvalues(12345,100421,1360,2012-3-15,还未归还)insert into lent_libraryvalues(21350,100422,2035,2012-1-6,已经归还)insert into lent_libraryvalues(1305

12、6,100423,1503,2012-3-22,还未归还)向lent_library表删除数据delete from lent_library where bookID=100423删除lent_library表中书籍编号为100423的一行数据向lent_library表查询数据select lentID,bookID,borrowbookID,borrowState from lent_library where bookID=10042%查询lent_library表中的lentID,bookID,borrowbookID,borrowState数据根据书籍编号前五位数位10042进行排

13、序(6)创建表booktypecreate table booktype( Leibienumber number (20) primary key, booktype varchar2 (20)tablespace student_library;向booktype表插入数据insert into booktypevalues(4630,文学类)insert into booktypevalues(1623,科普类)insert into booktypevalues(2412,理工科类)向booktype表删除数据delete from booktype where leibienumbe

14、r=1623删除booktype表中类别编号为1623的一行数据向booktype表修改数据update booktype set booktype=计算机类where leibienumber=2412对booktype表中类别编号为2412的书籍类型改为“计算机类”4、（1）创建索引【1】create index AdministratorID _index on Administrator (AdministratorID)tablespace users; 在Administrator表的AdministratorID列创建一个名为Aid_index的索引【2】create bitma

15、p index studentName_index on student(studentName)tablespace users;在student表的学生姓名列上创建位图索引（2）创建视图create or replace view view_library as select bookID,bookName,author,press,publishDate,price from library;创建视图view_library，查询library表中的bookID，bookName，author，press，publishDate，price5、（1）创建匿名块setserveroutpu

16、tondeclare out_textvarchar2(50);begin out_text:=程序块示例; dbms_output.put_line(out_text);exception whenothersthen dbms_output.put_line(捕获一个异常); end;（2）创建存储过程create or replace procedure InsertAdministrator asbegin insert into Administrator(AdministratorID,administratorName,sex) values(100421105,李凡,男);ex

17、ception when dup_val_on_index then dbms_output.put_line(重复的编号); when others then dbms_output.put_line(发生其他错误!);end InsertAdministrator;创建一个存储过程InsertAdministrator，向Administrator表的AdministratorID，administratorName和sex列分别插入数据100421105,李凡和男三个值。（3）创建函数create or replace function Factorial(n in number) re

18、turn numberas result number: =1;begin for i in 1.n loop result: =result*i; end loop; return(result);end Factorial;（4）创建触发器create table student_log( studentID number(20), studentName varchar2(50), sex varchar2(2), department varchar2(10);创建一个日志表，记录对student表所做的修改，日志表名为student_logcreate or replace trig

19、ger insertlog_trigger after insert on studentbegin insert into student_log values(100421230,路奇, 女, 园林系);end;在student表上创建一个语句级Insert触发器6、用户权限和角色管理(1) 创建用户user_studentcreate user user_studentidentified by studentdefault tablespace student_library(2) 为用户user_student设置系统权限grant create session to user_student;（3）为用户user_student撤销系统权限 revoke create session from user_student;（4）创建角色user_library create role user_libraryidentified by student(5)为角色user_library授予权限 grant user_library to public;（6）启用角色user_library set role user_libraryidentified by student-