第十一章 Oracle 函数与存储过程

第一节：Oracle 自定义函数

Create function 函数名称 return 返回值类型 as
Begin
…
End 函数名称;

create function getBookCount return number as
begin
  declare book_count number;
  begin
    select count(*) into book_count from t_book;
    return book_count;
  end;
end getBookCount;


set serveroutput on;
begin
  dbms_output.put_line('表t_book有'|| getBookCount() ||'条数据');
end;

create function getTableCount(table_name varchar2) return number as
begin
  declare recore_count number;
  query_sql varchar2(300);
  begin
    query_sql:='select count(*) from ' || table_name;
    execute immediate query_sql into recore_count;
    return recore_count;
  end;
end getTableCount;


begin
  dbms_output.put_line('表有'|| getTableCount('t_bookType') ||'条数据');
end;

---

第二节：Oracle 存储过程

Create procedure 存储过程名称 as
Begin
…
End 存储过程名称；

In 只进不出
Out 只出不进
In out 可进可出

create procedure addBook(bookName in varchar2,typeId in number) as
begin
  declare maxId number;
  begin
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bookName,typeId);
    commit;
  end;
end addBook;

//调用语句
execute addBook('java好东西',1);

create procedure addBook2(bN in varchar2,typeId in number) as
begin
  declare maxId number;
  n number;
  begin
    select count(*) into n from t_book where bookName=bN;
    if(n>0) then
     return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bN,typeId);
    commit;
  end;
end addBook2;

//调用语句
execute addBook2('java好东西33',1);

create procedure addBook3(bN in varchar2,typeId in number,n1 out number,n2 out number) as
begin
  declare maxId number;
  n number;
  begin
    select count(*) into n1 from t_book;
    select count(*) into n from t_book where bookName=bN;
    if(n>0) then
     return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bN,typeId);
    select count(*) into n2 from t_book;
    commit;
  end;
end addBook3;

//执行语句
declare n1 number;
        n2 number;
begin
  addBook3('喝喝33223',2,n1,n2);
  dbms_output.put_line('n1='||n1);
  dbms_output.put_line('n2='||n2);
end;

---

第三节：程序包

引入的目的，是为了有效的管理函数和存储过程，当项目模块很多的时候，用程序包管理就很有效了。
语法：
Create or replace package 包名 as
变量名称 1 数据类型 1；
变量名称 2 数据类型 2；
…
…
Function 函数名称 1(参数列表) return 数据类型 1；
Function 函数名称 2(参数列表) return 数据类型 2；
…
…
Procedure 存储过程名称 1(参数列表)；
Procedure 存储过程名称 2(参数列表)；
…
…
End 包名；

create package pkg_book as
   function getbookcount return number;
   function getTableCount(table_name varchar2) return number;
   procedure addBook(bookName in varchar2,typeId in number);
end pkg_book;

create package body pkg_book as

       function getBookCount return number as
          begin
            declare book_count number;
            begin
              select count(*) into book_count from t_book;
              return book_count;
            end;
        end getBookCount;


        function getTableCount(table_name varchar2) return number as
           begin
              declare recore_count number;
              query_sql varchar2(300);
              begin
                query_sql:='select count(*) from ' || table_name;
                execute immediate query_sql into recore_count;
                return recore_count;
              end;
          end getTableCount;


          procedure addBook(bookName in varchar2,typeId in number) as
            begin
              declare maxId number;
              begin
                select max(id) into maxId from t_book;
                insert into t_book values(maxId+1,bookName,typeId);
                commit;
              end;
            end addBook;


end pkg_book;


//调用语句
set serveroutput on;
begin
  dbms_output.put_line('表t_book有'|| pkg_book.getBookCount() ||'条数据');
end;

---