Oracle 数据库脚本实例

l5430p958z

--用户: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;
/