Oracle之实战（图书管理系统）

这是我oracle课程设计的作品的数据库脚本部分，应老师要求，尽量把学到的东西都应用上，所以涉及了包，序列，触发器，存储过程，建表，主外键，分页查询等

1、创建表空间（永久，临时，撤销），用户，授权 

create tablespace bookspace datafile 'E:\app\Administrator\oradata\orcl\bookspace.dbf'

 size 50M
 autoextend on
 next 5M
 maxsize 100M;
create temporary tablespace booktemp
tempfile 'E:\app\Administrator\oradata\orcl\boooktemp.dbf'
size 10M
autoextend on
next 2M
maxsize 20M;
create undo tablespace bookundo
datafile 'E:\app\Administrator\oradata\orcl\boookundo.dbf'
size 50M
autoextend on
next 5M
maxsize 100M;
create user lrz 
identified by  916520
default tablespace bookspace 
temporary tablespace booktemp;
alter user lrz quota unlimited on bookspace;
grant create session,create table,create procedure,create sequence,create trigger to lrz;

创建包定义成游标类型，select * from table...查询出多记录时，就用上场了

CREATE OR REPLACE PACKAGE PACKAGE_ORDER  AS
 TYPE CURSOR_ORDER IS REF CURSOR;
END PACKAGE_ORDER;
/

创建序列，用于实现自增长
CREATE SEQUENCE order_id    
MINVALUE 1    
START WITH 1    
INCREMENT BY 1    
CACHE 20;
/
commit;
CREATE SEQUENCE borrow_id    
MINVALUE 1    
START WITH 1    
INCREMENT BY 1    
CACHE 20;
/
commit;
CREATE SEQUENCE bookType_id    
MINVALUE 1    
START WITH 1    
INCREMENT BY 1    
CACHE 20;

创建表
create table tb_operater(
no varchar2(11) primary key,
name varchar2(30) not null,
pwd varchar2(30) not null,
sex char(2),
age number,
workdate timestamp not null,
tel char(11) null
);
insert into tb_operater values('2010324264','lrz','916520','男',23,sysdate,'15875952044');
commit; 
create table tb_bookType(
id number primary key,
name varchar2(30) not null unique,
days number not null,
fine number(7,2) not null
);
create table tb_bookInfo(
ISBN varchar2(13) primary key,
typeId number not null,
name varchar2(100) not null,
author varchar2(30) not null,
translator varchar2(30) null,
publisher varchar2(50) not null,
publishDate timestamp not null,
price number(7,2) null,
constraint fk_bkInfo_bkType foreign key(typeId) references tb_bookType(id)
); 
create table tb_reader(
no varchar2(11) primary key,
name varchar2(30) not null,
pwd varchar2(30) not null,
sex char(2),
age number,
career varchar2(30),
certificate varchar2(20),
certificateNo varchar2(30),
workdate timestamp not null,
lastdate timestamp not null,
maxAccount number not null,
tel char(11) null,
email varchar2(50) not null,
deposit number(7,2)   default  0.00
);
create table tb_order(
id number primary key,
bkISBN varchar2(13) not null,
operaterNo varchar2(11), 
account number not null,
orderDate timestamp not null,
checkAndAccept char(1) not null,
discount number not null,
constraint fk_order_bkInfo foreign key(bkISBN) references tb_bookInfo(ISBN)
);
create table tb_borrow(
id number primary key,
bkISBN  varchar2(13) not null,
operaterNo varchar2(11) null,
readerNo varchar2(11),
borrowDate timestamp not null,
backDate timestamp null,
back char(1) not null,
constraint fk_borrow_bkInfo foreign key(bkISBN) references tb_bookInfo(ISBN),
constraint fk_borrow_operater foreign key(operaterNo) references tb_operater(no),
constraint fk_borrow_reader foreign key(readerNo) references tb_reader(no)
);

创建触发器
create trigger delete_tb_booktype_trigger
before delete on tb_bookType
for each row
begin
delete  from tb_bookInfo where typeId=:old.id;
end delete_tb_booktype_trigger;
/

创建存储过程
CREATE OR REPLACE PROCEDURE PROCEDURE_ORDER_BKINFO(P_CURSOR OUT PACKAGE_ORDER.CURSOR_ORDER) IS 
BEGIN
    OPEN P_CURSOR FOR select a.*,b.* from tb_bookinfo a,tb_order b where a.isbn=b.bkisbn;
END PROCEDURE_ORDER_BKINFO;
/
CREATE OR REPLACE PROCEDURE PROCEDURE_ORDER_BKINFO_2(P_CURSOR OUT PACKAGE_ORDER.CURSOR_ORDER) IS 
BEGIN
    OPEN P_CURSOR FOR select a.* from tb_bookInfo a,tb_order b where a.ISBN!=b.bkISBN; 
END PROCEDURE_ORDER_BKINFO_2;
/
CREATE OR REPLACE PROCEDURE PROCEDURE_ORDER_1(ORDER_ISBN IN VARCHAR2,ORDER_OPERRATERNO IN VARCHAR2,ORDER_ACCOUNT IN NUMBER,ORDER_DATE IN TIMESTAMP,ORDER_DISCOUNT IN NUMBER) 
IS 
BEGIN
    insert into tb_order(id,bkISBN,operaterNo,account,orderDate,checkAndAccept,discount)  
    values(order_id.nextval,ORDER_ISBN,ORDER_OPERRATERNO,ORDER_ACCOUNT,ORDER_DATE,'0',ORDER_DISCOUNT);
END PROCEDURE_ORDER_1;
/
CREATE OR REPLACE PROCEDURE PROCEDURE_ORDER_2(ORDER_ID IN NUMBER,ORDER_RESULT OUT NUMBER) 
IS 
BEGIN
   update tb_order set checkAndAccept='1' where id=ORDER_ID;
   ORDER_RESULT := sql%rowcount;
END PROCEDURE_ORDER_2;
/
CREATE OR REPLACE PROCEDURE PROCEDURE_READER_1(reader tb_reader%rowtype) 
IS 
BEGIN
   insert into tb_reader(no,name,pwd,sex,age,career,certificate,certificateNo,workdate,lastdate,maxAccount,tel,email,deposit) values(reader.no,reader.name,reader.pwd,reader.sex,reader.age,reader.career,reader.certificate,reader.certificateNo,reader.workdate,reader.lastdate,reader.maxAccount,reader.tel,reader.email,reader.deposit);
END PROCEDURE_READER_1;
/

初始化数据：

insert into tb_booktype  values(booktype_id.nextval,'哲学',10,0.3);
insert into tb_booktype  values(booktype_id.nextval,'文学',10,0.3);
insert into tb_booktype  values(booktype_id.nextval,'经济学',10,0.3);
insert into tb_booktype  values(booktype_id.nextval,'生物学',10,0.3);
insert into tb_booktype  values(booktype_id.nextval,'天文学',10,0.3);
insert into tb_booktype  values(booktype_id.nextval,'医学',10,0.3);
insert into tb_booktype  values(booktype_id.nextval,'工学',10,0.3);
commit;
insert into tb_bookinfo  values('2222222222221',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('2222222222222',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('2222222222223',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('2222222222224',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('2222222222225',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('2222222222226',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('2222222222227',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('2222222222228',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('2222222222229',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('2222222222220',2,'马克思主义新论','李日中','李日中','广东工业出版社',sysdate,100);
commit;
insert into tb_bookinfo  values('3333333333331',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('3333333333332',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('3333333333333',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('3333333333334',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('3333333333335',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('3333333333336',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('3333333333337',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('3333333333338',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('3333333333339',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('3333333333330',3,'该死的文学','李日中','李日中','广东工业出版社',sysdate,100);
commit;
insert into tb_bookinfo  values('4444444444441',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('4444444444442',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('4444444444443',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('4444444444444',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('4444444444445',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('4444444444446',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('4444444444447',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('4444444444448',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('4444444444449',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('4444444444440',4,'讨厌的经济学','李日中','李日中','广东工业出版社',sysdate,100);
commit;
insert into tb_bookinfo  values('5555555555551',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('5555555555552',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('5555555555553',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('5555555555554',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('5555555555555',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('5555555555556',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('5555555555557',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('5555555555558',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('5555555555559',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('5555555555550',5,'忽悠人的生物学','李日中','李日中','广东工业出版社',sysdate,100);
commit;
insert into tb_bookinfo  values('6666666666661',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('6666666666662',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('6666666666663',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('6666666666664',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('6666666666665',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('6666666666666',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('6666666666667',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('6666666666668',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('6666666666669',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('6666666666660',6,'神秘的天文学','李日中','李日中','广东工业出版社',sysdate,100);
commit;
insert into tb_bookinfo  values('7777777777771',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('7777777777772',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('7777777777773',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('7777777777774',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('7777777777775',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('7777777777776',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('7777777777777',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('7777777777778',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('7777777777779',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('7777777777770',7,'苦B的医学','李日中','李日中','广东工业出版社',sysdate,100);
commit;
insert into tb_bookinfo  values('8888888888881',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('8888888888882',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('8888888888883',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('8888888888884',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('8888888888885',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('8888888888886',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('8888888888887',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('8888888888888',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('8888888888889',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
insert into tb_bookinfo  values('8888888888880',8,'牛B的工学','李日中','李日中','广东工业出版社',sysdate,100);
commit;
insert into tb_operater values('2010324265','smp','916520','男',23,sysdate,'15875952044');
insert into tb_operater values('2010324266','http','916520','男',23,sysdate,'15875952044');
insert into tb_operater values('2010324267','ftp','916520','男',23,sysdate,'15875952044');
insert into tb_operater values('2010324268','www','916520','男',23,sysdate,'15875952044');
insert into tb_operater values('2010324269','smpt','916520','男',23,sysdate,'15875952044');
insert into tb_operater values('2010324260','pop3','916520','男',23,sysdate,'15875952044');
insert into tb_operater values('2010324261','imap','916520','男',23,sysdate,'15875952044');
insert into tb_operater values('2010324262','sendmail','916520','男',23,sysdate,'15875952044');
insert into tb_operater values('2010324263','sendbar','916520','男',23,sysdate,'15875952044');
commit;
insert into tb_reader values('20103242640','李日中','916520','男',23,'学生','学生证','2010324264',sysdate,sysdate,10,'15875952044','1130141391@qq.com',10);
insert into tb_reader values('20103242641','吴xx','916520','男',23,'学生','学生证','2010324264',sysdate,sysdate,10,'15875952044','1130141391@qq.com',10);
insert into tb_reader values('20103242642','顾xxx','916520','男',23,'学生','学生证','2010324264',sysdate,sysdate,10,'15875952044','1130141391@qq.com',10);
insert into tb_reader values('20103242644','陈xx','916520','男',23,'学生','学生证','2010324264',sysdate,sysdate,10,'15875952044','1130141391@qq.com',10);
insert into tb_reader values('20103242645','林xx','916520','男',23,'学生','学生证','2010324264',sysdate,sysdate,10,'15875952044','1130141391@qq.com',10);
insert into tb_reader values('20103242646','林xx','916520','男',23,'学生','学生证','2010324264',sysdate,sysdate,10,'15875952044','1130141391@qq.com',10);
insert into tb_reader values('20103242647','梁xx','916520','男',23,'学生','学生证','2010324264',sysdate,sysdate,10,'15875952044','1130141391@qq.com',10);
insert into tb_reader values('20103242648','李xx','916520','男',23,'学生','学生证','2010324264',sysdate,sysdate,10,'15875952044','1130141391@qq.com',10);
insert into tb_reader values('20103242649','李日中','916520','男',23,'学生','学生证','2010324264',sysdate,sysdate,10,'15875952044','1130141391@qq.com',10);
commit;
insert into tb_borrow values(borrow_id.nextval,'2222222222221','2010324264','20103242640',sysdate,sysdate,'0');
insert into tb_borrow values(borrow_id.nextval,'2222222222222','2010324264','20103242640',sysdate,sysdate,'0');
insert into tb_borrow values(borrow_id.nextval,'2222222222223','2010324264','20103242640',sysdate,sysdate,'0');
insert into tb_borrow values(borrow_id.nextval,'2222222222224','2010324264','20103242640',sysdate,sysdate,'0');
insert into tb_borrow values(borrow_id.nextval,'2222222222225','2010324264','20103242640',sysdate,sysdate,'0');
insert into tb_borrow values(borrow_id.nextval,'2222222222226','2010324264','20103242640',sysdate,sysdate,'0');
insert into tb_borrow values(borrow_id.nextval,'2222222222227','2010324264','20103242640',sysdate,sysdate,'0');
insert into tb_borrow values(borrow_id.nextval,'2222222222228','2010324264','20103242640',sysdate,sysdate,'0');
insert into tb_borrow values(borrow_id.nextval,'2222222222229','2010324264','20103242640',sysdate,sysdate,'0');
insert into tb_borrow values(borrow_id.nextval,'2222222222220','2010324264','20103242640',sysdate,sysdate,'0');
commit;
insert into tb_order values(order_id.nextval,'2222222222222','2010324264',10,sysdate,'0',8);
insert into tb_order values(order_id.nextval,'2222222222220','2010324264',10,sysdate,'0',8);
insert into tb_order values(order_id.nextval,'2222222222221','2010324264',10,sysdate,'0',8);
insert into tb_order values(order_id.nextval,'2222222222223','2010324264',10,sysdate,'0',8);
insert into tb_order values(order_id.nextval,'2222222222224','2010324264',10,sysdate,'0',8);
insert into tb_order values(order_id.nextval,'2222222222225','2010324264',10,sysdate,'0',8);
insert into tb_order values(order_id.nextval,'2222222222226','2010324264',10,sysdate,'0',8);
insert into tb_order values(order_id.nextval,'2222222222227','2010324264',10,sysdate,'0',8);
insert into tb_order values(order_id.nextval,'2222222222228','2010324264',10,sysdate,'0',8);
insert into tb_order values(order_id.nextval,'2222222222229','2010324264',10,sysdate,'0',8);
commit;

之后会总结下在java中使用什么api操作oracle，跟mysql还是有些不同的，在使用的方法上，特别注意java中的数据类型怎么与oracle的数据类型对应上，

mysql与oracle的分页功能有些不同。