Oracle课程设计

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;