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

第一节：Oracle 自定义函数

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

create function getBookCount return number asbegin  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 asbegin  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) asbegin  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) asbegin  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) asbegin  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;

---