Oracle 数据库脚本实例

--用户:scott密码:tiger。如果没有这个用户自己创建一个。保存文档成sql。文件命令行下> @C:/名字.sql 执行这个脚本
-- Author : 划破思念
-- Created : 2010-1-3 16:42:52
-- Purpose : 不写数据库脚本，总忘，mark下
create table STAFF
(
STAFF_ID NUMBER(10) not null,
STAFF_NAME VARCHAR2(20),
STAFF_AGE NUMBER(3),
STAFF_SEX VARCHAR2(6),
STAFF_ADDR VARCHAR2(50),
STAFF_TEL VARCHAR2(20),
STAFF_JOB VARCHAR2(10),
constraint STAFF primary key (STAFF_ID)
);
INSERT INTO STAFF VALUES(199701,'张静',38,'女','大连市开发区×××','13478657854','馆长');
INSERT INTO STAFF VALUES(200402,'李爽',20,'女','大连市开发区×××','13478657577','计算机');
INSERT INTO STAFF VALUES(200404,'李媛',24,'女','大连市开发区×××','13478657127','计算机');
INSERT INTO STAFF VALUES(200501,'王璐',25,'女','大连市开发区×××','13478673344','医学');
INSERT INTO STAFF VALUES(200502,'刘新',25,'男','大连市开发区×××','13478672455','外语');
INSERT INTO STAFF VALUES(200503,'王浩',21,'男','大连市开发区×××','13778672455','外语');
INSERT INTO STAFF VALUES(200504,'李璐',25,'女','大连市开发区×××','13478673341','文学');
CREATE table Borrower (
Borrower_id NUMBER,
Borrower_name VARCHAR2(20),
Borrower_age NUMBER,
Borrower_sex VARCHAR2(6),
Borrower_addr VARCHAR2(50),
Borrower_tel VARCHAR2(20),
Borrower_limit NUMBER,
Borrower_pwd VARCHAR2(20),
constraint Borrower_PK primary key (Borrower_id)
);
INSERT INTO BORROWER VALUES(200793001,'刘新新',21,'男','大连理工大学软件学院3舍702','13478672455',6,'123456');
INSERT INTO BORROWER VALUES(200793002,'李新一',22,'男','大连理工大学软件学院3舍524','13478672433',6,'123456');
INSERT INTO BORROWER VALUES(200793003,'王笑笑',20,'女','大连理工大学软件学院1舍225','13478672457',6,'123456');
INSERT INTO BORROWER VALUES(200793004,'孙红雨',19,'男','大连理工大学软件学院3舍512','13478672434',6,'123456');
INSERT INTO BORROWER VALUES(200793168,'孙小璐',19,'女','大连理工大学软件学院1舍622','13478672284',6,'123456');
INSERT INTO BORROWER VALUES(200793195,'张钰静',19,'女','大连理工大学软件学院1舍312','13478672104',6,'123456');
CREATE table Press (
Press_name VARCHAR2(50),
Press_addr VARCHAR2(50),
Press_tel VARCHAR2(20),
constraint Press_PK primary key (Press_name)
);
INSERT INTO PRESS VALUES('大连理工大学出版社','大连市陵水路39号','0411-42547547');
INSERT INTO PRESS VALUES('清华大学出版社','北京市×','010-78445275');
INSERT INTO PRESS VALUES('北京大学出版社','北京市×','010-47875714');
INSERT INTO PRESS VALUES('机械工业出版','北京市×','010-47875715');
INSERT INTO PRESS VALUES('人民邮电出版社','北京市×','010-47875715');

create table Book (
Book_id NUMBER(10) not null,
Staff_id NUMBER(10) not null,
Book_name VARCHAR2(50),
Book_athor VARCHAR2(20),
Book_press VARCHAR2(50),
Book_price NUMBER(6),
Book_status VARCHAR2(10),
Book_size VARCHAR2(10),
Book_ISBN VARCHAR2(20),
Book_type VARCHAR2(20),
constraint PK_BOOK primary key (Book_id,Book_ISBN)
);
--序列
create sequence Blog_id start with 1 increment by 1;
create table BookBlog(
Blog_id number not null,
Blog_name varchar2(50) not null,
Blog_date DATE ,
constraint PK_BookBlog primary key (Blog_id)
);
alter table Book
add constraint FK_BOOK_RELATIONS_STAFF foreign key (Staff_id)
references Staff (Staff_id);
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'借出',700,'10001-1','计算机');
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'借出',700,'10001-2','计算机');
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'借出',700,'10001-3','计算机');
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'借出',700,'10001-4','计算机');
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'借出',700,'10001-5','计算机');
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'借出',700,'10001-6','计算机');
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'借出',700,'10001-7','计算机');
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'借出',700,'10001-8','计算机');
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'借出',700,'10001-9','计算机');
INSERT INTO BOOK VALUES(10001,200402,'C++ Primer','Stanley.Bl','人民邮电出版社',99,'可借',700,'10001-10','计算机');
INSERT INTO BOOK VALUES(10002,200402,'Java语言程序设计','YDL','机械工业出版',68,'可借',500,'10002-1','计算机');
INSERT INTO BOOK VALUES(10002,200402,'Java语言程序设计','YDL','机械工业出版',68,'可借',500,'10002-2','计算机');
INSERT INTO BOOK VALUES(10003,200402,'C程序设计教程','谭浩强','清华大学出版社',68,'可借',300,'10003-1','计算机');
INSERT INTO BOOK VALUES(10003,200402,'C程序设计教程','谭浩强','清华大学出版社',68,'可借',300,'10003-2','计算机');
INSERT INTO BOOK VALUES(10003,200402,'C程序设计教程','谭浩强','清华大学出版社',68,'可借',300,'10003-3','计算机');
INSERT INTO BOOK VALUES(10003,200402,'C程序设计教程','谭浩强','清华大学出版社',68,'可借',300,'10003-4','计算机');
INSERT INTO BOOK VALUES(10004,200503,'English','Patrick','清华大学出版社',68,'可借',300,'10004-1','外语');
INSERT INTO BOOK VALUES(10004,200503,'English','Patrick','清华大学出版社',68,'可借',300,'10004-2','外语');
INSERT INTO BOOK VALUES(10004,200503,'English','Patrick','清华大学出版社',68,'可借',300,'10004-3','外语');
INSERT INTO BOOK VALUES(10004,200503,'English','Patrick','清华大学出版社',68,'可借',300,'10004-4','外语');
INSERT INTO BOOK VALUES(10005,200503,'高级日本语','乔国钧','大连理工大学出版社',68,'可借',300,'10005-1','外语');
INSERT INTO BOOK VALUES(10005,200503,'高级日本语','乔国钧','大连理工大学出版社',68,'可借',300,'10005-2','外语');


create table Borrow(
Book_id NUMBER(10) not null,
Borrower_id NUMBER(10) not null,
Book_ISBN VARCHAR2(20) not null,
Borrow_fromdate DATE,
Borrow_todate DATE,
constraint PK_BORROW primary key (Book_id,Borrower_id,Book_ISBN)
);
alter table Borrow
add constraint FK_BORROW_BORROW_BORROWER foreign key (Borrower_id)
references Borrower (Borrower_id);
alter table Borrow
add constraint FK_BORROW_BORROW2_BOOK foreign key (Book_id,Book_ISBN)
references Book (Book_id,Book_ISBN);
INSERT INTO BORROW VALUES(10001,200793001,'10001-1',SYSDATE,SYSDATE+30);
INSERT INTO BORROW VALUES(10001,200793002,'10001-2',SYSDATE,SYSDATE+30);
INSERT INTO BORROW VALUES(10001,200793002,'10001-3',SYSDATE,SYSDATE+30);
INSERT INTO BORROW VALUES(10002,200793002,'10002-2',SYSDATE-31,SYSDATE-1);
INSERT INTO BORROW VALUES(10004,200793003,'10004-2',SYSDATE-31,SYSDATE-1);
INSERT INTO BORROW VALUES(10003,200793003,'10003-2',SYSDATE-31,SYSDATE-1);
--这个表中BOOk_ISBN属性是作为组合外键引用过来的，在表中无意义。
create table Buy (
Book_id NUMBER(10) not null,
Press_name VARCHAR2(50) not null,
Book_ISBN VARCHAR2(20) not null,
Buy_count NUMBER(5),
constraint PK_BUY primary key (Book_id,Press_name)
);
alter table Buy
add constraint FK_BUY_PRESS foreign key (Press_name)
references Press (Press_name);
alter table Buy
add constraint FK_BUY_BOOK foreign key (Book_id,Book_ISBN)
references Book (Book_id,Book_ISBN);
create or replace package LIBRARY is
procedure insert_book(
Book_id number,
Staff_id number,
Book_name varchar2,
Book_athor varchar2,
Book_press varchar2,
Book_price number,
Book_state varchar2,
Book_size varchar2,
Book_ISBN varchar2,
Book_type varchar2
);
end LIBRARY;
/
--添加图书的存储过程
create or replace package body LIBRARY is
procedure insert_book(
Book_id number,
Staff_id number,
Book_name varchar2,
Book_athor varchar2,
Book_press varchar2,
Book_price number,
Book_state varchar2,
Book_size varchar2,
Book_ISBN varchar2,
Book_type varchar2
) is
begin
insert into Book values(Book_id,Staff_id,Book_name,Book_athor,Book_press,Book_price,Book_state,Book_size,Book_ISBN,Book_type);
end insert_book;
end LIBRARY;
/
--触发器
create or replace trigger TRIGEER_BOOK_ADD
after insert on Book
for each row
begin
insert into BookBlog values(Blog_id.Nextval,:new.book_name,SYSDATE);
end TRIGEER_BOOK_ADD;
/