Oracle学习----高级篇（五）-----Oracle函数与存储过程，程序包

Oracle函数与存储过程，程序包

Oracle自定义函数

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

--创建不带参数函数，返回t_book中书的数量
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);--定义sql语句
  begin
    query_sql:='select count(*) from '||table_name;
    --execute immediate：立即执行该SQL语句
    execute immediate query_sql into recore_count;
    return recore_count;
  end;
end getTableCount;

--函数调用
set serveroutput on;
begin
  dbms_output.put_line('表中有'||getTableCount('t_book_log')||'条数据');
end;

Oracle存储过程

Create procedure 存储过程名称 as
Begin
···
End 存储过程名称;

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

--创建存储过程，在t_book表中插入数据，且判断插入的数据是否已经存在
create procedure addBook(bName in varchar2,typeid in number) as
begin
  declare maxId number;
  n number;
  begin
    select count(*) into n from t_book where t_book.bookname=bName;
    if(n>0) then
      return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bName,typeid);
    commit;
  end;
end addBook;

--调用存储过程
execute addBook('vs',1);

--创建存储过程，在t_book表中插入数据，输出插入前后数据条数
create procedure addBook(bName in varchar2,typeid in number,n1 out number,n2 out number) as
begin
  declare maxId number;
  n number;
  begin
    --n1存储执行前数据条数
    select count(*) into n1 from t_book;
    select count(*) into n from t_book where t_book.bookname=bName;
    if(n>0) then
      select count(*) into n2 from t_book;
      return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bName,typeid);
    --n2存储执行后数据条数
    select count(*) into n2 from t_book;
    commit;
  end;
end addBook;

--返回存储执行前后数据条数
declare n1 number;
        n2 number;
begin
  addBook('c+++',1,n1,n2);
  dbms_output.put_line('n1='||n1||',n2='||n2);
end;

程序包

• 引入目的：为了有效的管理函数和存储过程，当项目模块很多的时候，用程序包管理就很有效了。
• 语法
  create or replace 包名 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;
       procedure addBook(bName in varchar2,typeid in number,n1 out number,n2 out 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;

procedure addBook(bName in varchar2,typeid in number,n1 out number,n2 out number) as
begin
  declare maxId number;
  n number;
  begin
    --n1存储执行前数据条数
    select count(*) into n1 from t_book;
    select count(*) into n from t_book where t_book.bookname=bName;
    if(n>0) then
      select count(*) into n2 from t_book;
      return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book values(maxId+1,bName,typeid);
    --n2存储执行后数据条数
    select count(*) into n2 from t_book;
    commit;
  end;
end addBook;
end pkg_book;


--调用
begin
  dbms_output.put_line('表中有'||pkg_book.getBookCount||'条记录');
end;