学习笔记

/*  创建用户并授权*/
create user LibManager identified by lib;
grant connect,resource to LibManager;
conn LibManager/lib;

/* 建表*/

/*1 用户表 --用户名，密码，用户等级（学生，管理员）*/
Drop table UserInfo;
Create table UserInfo(
userid varchar2(20) not null,
username varchar2(20) not null,
password varchar2(6) not null,
userlevel varchar2(6) not null,
primary key(userid));

insert into UserInfo values('00001','zhangsan','123','学生');
insert into UserInfo values('00002','lisi','123','管理员');

/*2 读者信息表
--学号（职工编号）/*yyyyddddmmccid  yyyy是入学年份，dddd是所属学院的编号，mm是系别编号，cc是班编号，id是班内学号
--姓名，学院，系别，图书借阅最大数量（本），归还期限（月）*/
Drop table ReaderInfo;
Create table ReaderInfo (
readerid varchar2(20) not null,
readertype varchar2(6) not null,/*学生,教师*/
readername varchar2(20) not null,
readerdept varchar2(10) not null,
readermajor varchar2(10) not null,
borrmaxsize number(2) not null,
lendlimit number(2) not null,
primary key(readerid)
);
insert into ReaderInfo values('20080001010101','学生','李四','信息学院','计算机',5,2);

/*3 图书信息表--图书编号，图书所属类别，图书数量（本），图书名称，图书价格，出版社编号，作者姓名*/
Drop table BookInfo;
Create table BookInfo(
bookid varchar2(10) not null,
booktype varchar2(5) not null,
booksize number(2) not null,
bookname varchar2(100) not null,
bookprice number(5,2) not null,
publisherid varchar2(5) not null,
author varchar2(20) not null,
primary key(bookid,booktype));
insert into BookInfo values('00222','0001',5,'MVC模式编程',25.00,'00011','abc');

/*4 图书状态表--图书编号，图书在馆本数,图书状态（在馆，全部借出）*/
drop table BookStatus;
Create Table BookStatus(
bookid  varchar2(10) not null,
bookstatus varchar2(10) not null,
booksize number(2) not null,
primary key(bookid));
insert into BookStatus values('0001','在馆',5);

/*5 出版社信息表--出版社编号,出版社名称,出版社所在地*/
Drop table PublisherInfo;
Create table PublisherInfo(
publisherid varchar2(5) not null,
publishername varchar2(30) not null,
publishloc varchar2(10),
primary key(publisherid));
insert into PublisherInfo values('00011','清华大学出版社','北京');

/*6 图书类别表--图书类别编号,类别名称*/
Drop table BookType;
Create Table BookType(
booktype varchar2(5) not null,
typename varchar2(20) not null,
primary key(booktype));

insert into BookType values('0001','计算机');
insert into BookType values('0002','电子技术');

/*7 借阅记录表--借阅流水号,读者编号,图书编号,借出时间,应归还时间,借阅记录状态（未还，已还）*/
Drop table BorrowRecord;
Create table BorrowRecord(
borrserials number(10) not null,
readerid varchar2(20) not null,
bookid  varchar2(10) not null,
borrdate  Date not null,
beback   Date not null,
recordstatus varchar2(5) not null,
primary key(borrserials)
);
Create sequence BorrowRecord_seq;
--不可有的insert into BorrowREcord values(2223,'20080001010101','0001',sysdate,sysdate+5,'未还');
/*创建存储过程*/

/* 1 过程名称：addReader
输入参数：id,type,name,dept,major,size,limit
输出参数：password
*/
create or replace procedure addReader(
id ReaderInfo.readerid%type,
type ReaderInfo.readertype%type,
name ReaderInfo.readername%type,
dept ReaderInfo.readerdept%type,
major ReaderInfo.readermajor%type,
maxsize ReaderInfo.borrmaxsize%type,
limit ReaderInfo.lendlimit%type,
password out UserInfo.password%type)
as
begin
  /*Reader 表中插入数据*/
   insert into ReaderInfo values(id,type,name,dept,major,maxsize,limit);
  /*随机生成６位密码*/
  SELECT DBMS_RANDOM.STRING('A', 6) into password FROM DUAL;
   insert into UserInfo values(id,name,password,type);
   commit;
exception
when others then
  password:=null;
  rollback;
end;
/

declare
result varchar2(6);
begin
addReader('20080001010102','教师','李四','信息学院','计算机',3,2,result);
dbms_output.put_line('注册成功，密码:'||result);
end;
/

/* 2 过程名称：userLogin
功能：
输入参数：id,password
输出参数：result boolean ; ErrMsg
*/
create or replace procedure userLogin(
id ReaderInfo.readerid%type,
password UserInfo.password%type,
result out boolean,
ErrMsg out varchar2)
as
psw UserInfo.password%type;
begin
    select password into psw from UserInfo where userid =id;
    if psw = password then
result:=true;
ErrMsg:='登陆成功';
    else
result:=false;
ErrMsg:='密码不正确';
end if;
exception
when NO_DATA_FOUND then
result:=false;
ErrMsg:='用户ｉｄ无效，请确认';
when others then
result:=false;
ErrMsg:='未知故障';
end;
/

declare
result boolean;
resmsg varchar2(20);
begin
userLogin('2008000101010','qemwVe',result,resmsg);
    if result then
dbms_output.put_line(resmsg||',登录成功');
    else
dbms_output.put_line(resmsg||',登录不成功');
    end if;
end;
/

/* 3 过程名称:addBook
输入参数：id,typeid,bsize,bname,bprice,pubid,author
输出参数：result boolean ; ErrMsg */
create or replace procedure addBook(
id BookInfo.bookid%type,
typeid BookInfo.booktype%type,
bsize BookInfo.booksize%type,
bname BookInfo.bookname%type,
bprice BookInfo.bookprice%type,
pubid BookInfo.publisherid%type,
author BookInfo.author%type,
result out boolean,
errmsg out varchar2
) as
begin
    insert into BookInfo values(id,typeid,bsize,bname,bprice,
pubid,author);
    insert into BookStatus values(id,'在馆',bsize);
    commit;
    result:=true;
exception
when others then
    result:=false;
    errmsg:=SQLERRM;
end;
/

declare
result boolean;
resmsg varchar2(20);
begin
addBook('00223','0002',5,'Oracle数据库',25.00,'00088','迅腾国际',result,resmsg);
    if result then
dbms_output.put_line(resmsg||',添加成功');
    else
dbms_output.put_line(resmsg||',添加不成功');
    end if;
end;
/

/* 4 过程名称: alertBook
功能：图书信息修改（管理员权限）
输入参数：v_id,v_type,v_size,v_name,v_price,v_publisherid,v_author
输出参数：result out boolean,errmsg*/

create or replace procedure alterbook(
v_id in BookInfo.bookid%type,
v_type BookInfo.booktype%type,
v_size BookInfo.booksize%type,
v_name BookInfo.bookname%type,
v_price BookInfo.bookprice%type,
v_publisherid BookInfo.publisherid%type,
v_author BookInfo.author%type,
result out boolean,
errmsg out varchar2
)
as
begin
    alterBook(v_id,v_type,v_size,v_name,v_price,v_publisherid,v_author,result,errmsg);
    if result then
update BookInfo set bookid=v_id,booktype=v_type,
                            booksize=v_size,bookname=v_name,
                            bookprice=v_price,publisherid=v_publisherid,
                            author=v_author where bookid=v_id;
commit;
    end if;
exception
    when others then
        rollback;
errmsg:='未知异常';
result:=false;
end alterbook;
/



/* 5 过程名称: borrowBook
功能：借书（管理员权限）
输入参数：rid,bid
输出参数：result out boolean,resultmsg*/

create or replace procedure borrowBook(
rid BorrowRecord.readerid%type,
bid BorrowRecord.bookid%type,
result out boolean,
resultmsg out varchar2
) as
Max_Exception Exception;
No_Book_Borrow Exception;
v_borred number;
v_maxsize ReaderInfo.borrmaxsize%type;
v_limit ReaderInfo.lendlimit%type;
v_bookstatus BookStatus.bookstatus%type;
v_booknum BookStatus.booksize%type;
begin
--获取该用户已借图书数量
    select count(*) into v_borred from BorrowRecord where readerid=rid and recordstatus='未还';
--获取该用户的图书借阅最大数量
    select borrmaxsize,lendlimit into v_maxsize,v_limit from ReaderInfo where readerid=rid;

    if v_borred >= v_maxsize then/*达到最大值，抛出异常*/
raise Max_Exception;
    end if;

--获取该书状态
    select bookstatus into v_bookstatus from BookStatus where bookid = bid;
    if v_bookstatus = '全部借出' then
raise No_Book_Borrow;
    end if;
    /*更新借书记录*/
    insert into BorrowRecord
values(BorrowRecord_seq.nextval,rid,bid,sysdate,add_months(sysdate,v_limit),'未还');
    /*更新图书状态*/
    update Bookstatus set booksize = booksize-1 where bookid = bid;

    select booksize into v_booknum from Bookstatus where bookid = bid;
    if v_booknum = 0 then
update Bookstatus set bookstatus='全部借出' where bookid = bid;
    end if;
    commit;
    result:=true;
    resultmsg:='借书成功';
/*未达到最大值，判断该书状态是否在馆*/
exception
when Max_Exception then
    rollback;
    result:=false;
    resultmsg:='借书失败,借书数量达到最大值,将所借图书归还后才可继续借书';
when No_Book_Borrow then
    rollback;
    result:=false;
    resultmsg:='借书失败,该书已全部借出';
end;
/
------------------测试borrowBook
declare
result boolean;
resmsg varchar2(40);
begin
borrowBook('20080001010101','00222',result,resmsg);
    if result then
dbms_output.put_line(resmsg);
    else
dbms_output.put_line(resmsg);
    end if;
end;
/