create or replace package body pkgbook
as
/*变量设置*/
/*查询模块*/
/*按照ISBN模块*/
function selisbn(selisbn varchar2) return selbook
is
sbook selbook;
exbook selbook;
begin
select * bulk collect into sbook from BookInfo where
ISBN like '%'||selisbn||'%';
return sbook;
exception
when no_data_found then
/*异常无法捕获因为返回值为空不是没有返回值!*/
raise_application_error(-20001,'该ISBN不存在!');
when others then
dbms_output.put_line(sqlcode||''||sqlerrm);
end selisbn;
/*按照图书名模块*/
function selname(selbookname varchar2) return selbook
is sbook selbook;
begin
select * bulk collect into sbook from BookInfo where
BookName like '%'||selbookname||'%';
return sbook;
exception
when no_data_found then
/*异常无法捕获因为返回值为空不是没有返回值!*/
raise_application_error(-20002,'该图书名不存在!');
when others then
dbms_output.put_line(sqlcode||''||sqlerrm);
end selname;
/*按照图书出版社模块*/
function selpub(selpublish varchar2) return selbook
is sbook selbook;
begin
select * bulk collect into sbook from BookInfo where
publisher like '%'||selpublish||'%';
return sbook;
exception
when no_data_found then
/*异常无法捕获因为返回值为空不是没有返回值!*/
raise_application_error(-20003,'该出版社不存在!');
when others then
dbms_output.put_line(sqlcode||''||sqlerrm);
end selpub;
/*修改设置*/
procedure upbookisbn(upisbn BookInfo.ISBN%type,upname varchar2,upinfo varchar2)
as
begin
case upname
when 'BookName' then
update BookInfo set BookName=upinfo where ISBN=upisbn;
if sql%notfound then
raise isbnnull;
end if;
when 'Writer' then
update BookInfo set Writer=upinfo where ISBN=upisbn;
if sql%notfound then
raise isbnnull;
end if;
when 'Type' then
select count(*) into tycount from BookType where
Type=upinfo;
if tycount>=1 then
update BookInfo set Type=upinfo where ISBN=upisbn;
if sql%notfound then
raise isbnnull;
end if;
else
dbms_output.put_line('不存在输入的类型！');
end if;
when 'publisher' then
update BookInfo set publisher=upinfo where ISBN=upisbn;
if sql%notfound then
raise isbnnull;
end if;
when 'Price' then
update BookInfo set Price=to_number(upinfo,'999.99')
where ISBN=upisbn;
if sql%notfound then
raise isbnnull;
end if;
when 'Total' then
update BookInfo set Total=to_number(upinfo,'99')
where ISBN=upisbn;
if sql%notfound then
raise isbnnull;
end if;
when 'commend' then
update BookInfo set commend=upinfo where ISBN=upisbn;
if sql%notfound then
raise isbnnull;
end if;
when 'newbook' then
update BookInfo set newbook=upinfo where ISBN=upisbn;
if sql%notfound then
raise isbnnull;
end if;
else
dbms_output.put_line('输入列名有错误！');
end case;
exception
when isbnnull then
dbms_output.put_line('输入的ISBN不存在!');
when no_data_found then
dbms_output.put_line('不能将字符转换成数字!');
when invalid_number then
dbms_output.put_line('不能将字符转换成数字!');
when value_error then
dbms_output.put_line('输入的变量长度超出预定义范围!');
end upbookisbn;
procedure upbookname(updatename BookInfo.BookName%type,upname varchar2,upinfo varchar2)
as
begin
case upname
when 'Writer' then
update BookInfo set Writer=upinfo where BookName=updatename;
if sql%notfound then
raise booknamenull;
end if;
when 'Type' then
update BookInfo set Type=upinfo where BookName=updatename;
if sql%notfound then
raise booknamenull;
end if;
when 'publisher' then
update BookInfo set publisher=upinfo where BookName=updatename;
if sql%notfound then
raise booknamenull;
end if;
when 'Price' then
update BookInfo set Price=upinfo where BookName=updatename;
if sql%notfound then
raise booknamenull;
end if;
when 'commend' then
update BookInfo set commend=upinfo where BookName=updatename;
if sql%notfound then
raise booknamenull;
end if;
when 'newbook' then
update BookInfo set newbook=upinfo where BookName=updatename;
if sql%notfound then
raise booknamenull;
end if;
else
dbms_output.put_line('输入列名有错误！');
end case;
exception
when booknamenull then
dbms_output.put_line('输入的图书名不存在!');
when no_data_found then
dbms_output.put_line('不能将字符转换成数字!');
when invalid_number then
dbms_output.put_line('不能将字符转换成数字!');
when value_error then
dbms_output.put_line('输入的变量长度超出预定义范围!');
end upbookname;
procedure delbookisbn(delisbn BookInfo.ISBN%type)
as
begin
delete from BookInfo where ISBN=delisbn;
if sql%notfound then
raise isbnnull;
end if;
exception
when isbnnull then
dbms_output.put_line('输入的ISBN不存在!');
end delbookisbn;
procedure delbookname(delname BookInfo.BookName%type)
as
begin
delete from BookInfo where BookName=delname;
if sql%notfound then
raise booknamenull;
end if;
exception
when booknamenull then
dbms_output.put_line('输入的图书名不存在!');
end delbookname;
/*修改设置*/
procedure addbook(book BookInfo%rowtype)
as
/*变量设置*/
begin
insert into BookInfo values book;
exception
when dup_val_on_index then
dbms_output.put_line('违反了主键的约束修改ISBN的值!');
when foreignkey then
dbms_output.put_line('违反了外键的约束修改Type的值!');
when setnum then
dbms_output.put_line('输入中数字型的值不匹配!');
when setcheck then
dbms_output.put_line('输入中违反了CHECK约束条件!');
end addbook;
/*统计模块*/
function ctypeinfo(ctype varchar2) return number
is
vcount number;
begin
select count(*) into vcount from BookInfo where
Type=ctype;
return vcount;
exception
when no_data_found then
dbms_output.put_line('输入的类型不存在!');
when others then
dbms_output.put_line('错误代码:'||sqlcode||'错误信息:'||sqlerrm);
end;
function cpubinfo(cpub varchar2) return number
is
vcount number;
begin
select count(*) into vcount from BookInfo where
publisher=cpub;
return vcount;
exception
when no_data_found then
dbms_output.put_line('输入的类型不存在!');
when others then
dbms_output.put_line('错误代码:'||sqlcode||'错误信息:'||sqlerrm);
end;
end pkgbook;
