oracle第八天——程序包

--程序包

--1.目的:是为了有效的管理函数和存储过程，当项目模块很多的时候，用程序包管理就很有效了；

--2.语法：
  create or replace package 包名 as
         变量名1 函数类型1;
         变量名2 函数类型2;
         ......
         ......
         
         function 函数名称1(参数列表) return 数据类型1;
         function 函数名称2(参数列表) return 数据类型2;
         ......
         ......
         procedure 存储过程名1(参数列表);
         procedure 存储过程名2(参数列表);
         ......
         ......
  end 包名;
         
--3,示例

  --包头
  create package book_pkg as
         --函数
         function getBookCount return number;
         function getTableCount(tablename varchar2) return number;
         --存储过程
         procedure addBook(bookName in varchar2,type_id in number,bookPrice in number);
         procedure addBookNotExits(bookName in varchar2,type_id in number,bookPrice in number);
  end book_pkg;
  
  --包的具体实现
  create package body book_pkg as
         --函数1
         function getBookCount return number as
         begin
            declare bookCount number;
            begin
              select count(*) into bookCount from t_book;
              return bookCount;
            end;
         end getBookCount;
         --函数2
         function getTableCount(tablename varchar2) return number as
         begin
            declare recordCount number;
                    query_sql varchar2(300);
            begin
              query_sql := 'select count(*) from '||tablename;
              execute immediate query_sql into recordCount;
              return recordCount;
            end;
         end getTableCount;
         --......

         --存储过程1
         procedure addBook(bookName in varchar2,type_id in number,bookPrice in number) as
         begin
            declare maxId number;
            begin
              select max(id) into maxId from t_book;
              insert into t_book values(maxId+1,bookName,type_id,bookPrice);
            end;
         end addBook;
         --存储过程2
         procedure addBookNotExits(bookName in varchar2,type_id in number,bookPrice in number) as
         begin
            declare maxId number;
                    n number;
            begin
              select count(*) into n from t_book where book_name = bookName;
              if(n > 0) then
                   return;
              end if;
              select max(id) into maxId from t_book;
              insert into t_book values(maxId+1,bookName,type_id,bookPrice);
              commit;
            end;
         end addBookNotExits;
         --......
         
  end book_pkg;
  
  --包中函数调用
  set serveroutput on;
  begin
     dbms_output.put_line('表t_book有'||book_pkg.getBookCount()||'记录');
     dbms_output.put_line('表t_book_type有'||book_pkg.getTableCount('t_book_type')||'记录');
  end;
  --包中存储过程调用
  execute book_pkg.addBook('php',1,170);
  execute book_pkg.addBookNotExits('Ruly',1,130);