oracle学习实例

业务环境
  图书借阅与归还，一次借阅可借多本图书。

数据结构：
  图书：图书ID，图书名，初始数量，外借数量，在库数量
  借阅记录：借阅ID，借阅人，借阅时间，归还时间，状态(已借，已还)
  借阅明细：明细ID，借阅ID，图书ID

数据装备：
增加3-5条以上的图书信息，初始数量都默认为10，在库数量也是10

要求：
函数：负责对图书的库存数进行处理（包括增、减）

存储过程：（在处理借阅图书时，使用游标方式进行）
        借阅：可以实现增加借阅信息的新增，新增后，借阅的状态默认为已借，借阅的图书外借数量相应+1，在库数量-1。
            注：借阅至少指定3本图书。
        提示：借阅记录相关信息可利用参数传入；借阅的图书可以参数形式传入，以固定格式，处理时，从给定格式中解析出来
     
        归还：可以实现对借阅的图书进行归还，每次归还必须针对一次借阅，归还后，借阅的图书的外借数量相应-1，在库数量+1。

         

/*图书表*/
create table Book(
Bookid int primary key,
Bookname varchar2(20),  /*图书名称*/
Initialnum number default(10),  /*初始数量*/
Loanamount number default(5),  /*外借数量*/
Innum number default(10)       /*在库数量*/
)

/*借阅记录表*/
create table Borrowrecords(
Borrowid int primary key,  /*借阅人编号*/
Borrowperson varchar2(20),  /*借阅人*/
Borrowtime date,   /*借阅时间*/
ReturnTime date,   /*归还时间*/
State varchar2(10) check(State = '已借' or State = '已还')   /*图书状态(已借，已还)*/
)

/*借阅明细表*/
create table Loandetails(
Loanid int primary key,   /*明细ID*/
Borrowid int  references Borrowrecords(Borrowid),  /*借阅ID*/
Bookid int  references Book(Bookid)   /*图书ID*/
)

insert into Book values(1,'往事',15,0,15);
insert into Book values(2,'你好',12,0,12);
insert into Book values(3,'中联',13,0,13);
insert into Book values(4,'信息',10,0,10);
insert into Book values(5,'产业',23,0,23);

insert into Borrowrecords values(101,'邓合靖',sysdate,sysdate+1/24,'已还');
insert into Borrowrecords values(102,'潘锡侯',sysdate,sysdate+2/24,'已还');
insert into Borrowrecords values(103,'高宇果',sysdate,sysdate+3/24,'已还');
insert into Borrowrecords values(104,'杨祖应',sysdate,sysdate+5/24,'已还');

insert into Loandetails values(001,101,1);
insert into Loandetails values(002,102,2);
insert into Loandetails values(003,103,3);
insert into Loandetails values(004,104,4);

select Bookid as 图书编号,Bookname as 图书名称,Initialnum as 初始数量, Loanamount as 外借数量,Innum as 在库数量  from book
select Borrowid as 借阅人编号,Borrowperson as 借阅人,Borrowtime as 借阅时间,ReturnTime as 归还时间,State as 借阅状态 from Borrowrecords
select Loanid as 明细id,Borrowid as 借阅id,Bookid as 图书id  from Loandetails



/*借阅图书的存储过程*/

/*

首先创建需要创建两个序列

一：图书借阅ID 

二：图书借阅明细ID

*/

create or replace  procedure p_InsertToBorrowrecords(
Borrowperson_In Varchar2 := null,
Bookid1_In int,
Bookid2_In int,
Bookid3_In int
) 
as 
 v_图书借阅ID int;
 Type Cursor_Type Is Ref Cursor;
 c_图书明细 Cursor_Type;
 
 v_借阅图书ID int;
Begin 

--将图书借阅ID序列中值赋给已定义的v_借阅图书ID
  v_图书借阅ID := 图书借阅ID.NEXTVAL;
  --插入借阅信息
  Insert into Borrowrecords(Borrowid,Borrowperson,Borrowtime,State) values(v_图书借阅ID,Borrowperson_In,sysdate,'已借');
  --插入借阅明细
  If nvl(Bookid1_In,0) > 0 Then 
    Insert into Loandetails(Loanid,Borrowid,Bookid) Values (图书借阅明细id.nextval,v_图书借阅ID,Bookid1_In);
  End If;
  If nvl(Bookid2_In,0) > 0 Then 
    Insert into Loandetails(Loanid,Borrowid,Bookid) Values (图书借阅明细id.nextval,v_图书借阅ID,Bookid2_In);  
  End If;
  If nvl(Bookid3_In,0) > 0 Then 
    Insert into Loandetails(Loanid,Borrowid,Bookid) Values (图书借阅明细id.nextval,v_图书借阅ID,Bookid3_In);
  End If;
  --使用游标统一处理相关图书库存
  Open c_图书明细 For 
     Select Bookid From Loandetails l Where l.borrowid=v_图书借阅ID;
  Loop
    Fetch c_图书明细 into v_借阅图书ID;
    Exit When c_图书明细%NotFound;
    --进行图书库存
    Update Book Set Loanamount=Loanamount+1,Innum=Innum-1 Where bookid = v_借阅图书ID;
  End Loop;

close c_图书明细;
End p_InsertToBorrowrecords;