Oracle 存储过程和函数

函数一般是工具性的东西。

存储过程一般是一些操作。

一、Oracle 自定义函数

语法：

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

示例：统计t_book记录数

create or replace function getbookCount return number as 
begin
  declare bookNum number;
  begin
    select count(*) into bookNum from t_book;
    return bookNum;
  end;
end getbookCount;

函数调用

set serveroutput on
begin
  dbms_output.put_line('t_book表有：' || getbookCount() || '条记录');
end;

输出结果：

带参数的函数

示例：

create or replace function getBookSum(table_name varchar2) return number as
begin
  declare sumCount number;
  count_sql varchar2(200);
  begin
    count_sql:='select count(*) from ' || table_name;
    execute immediate count_sql into sumCount;
    return sumCount;
  end;
end getBookSum;

带参函数调用

set serveroutput on
begin
  dbms_output.put_line('表有：' || getBookSum('t_booktype') || '条记录');
end;

输出结果：

二、Oracle 存储过程

语法：

Create procedure 存储过程名称  as
Begin
...
End 存储过程名称；
In 只进不出
Out 只出不进
In out 可进可出

参数In 只进不出

示例：向t_book中添加数据。

create or replace procedure addBook(bookname in varchar2, booktypeId in number,price in number) as
begin
  declare maxId number;
  begin 
    select max(id) into maxId from t_book;
    insert into t_book(id,bookname,type_id,price) values(maxId+1, bookname,booktypeId,price);
    commit;--自动提交
  end;
end addBook;

存储过程调用

execute 存储过程名称(参数1，参数2.。。)；

示例1：

execute addBook('代码简洁之道',2,66);

查询t_book结果：

示例2：

如果存在就不添加，没有记录才添加。

create or replace procedure addCheckBook(boookn in varchar2, booktypeId in number,price in number) as
begin
  declare maxId number;
  n number;
  begin 
    select count(*) into n from t_book where bookname=boookn;
    if (n>0) then
      return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book(id,bookname,type_id,price) values(maxId+1, boookn,booktypeId,price);
    commit;
  end;
end addCheckBook;

调用存储过程

execute addCheckBook('代码简洁之道2',2,66);

此时已经存在，不在向t_book中添加记录。

Out 只出不进

示例1：

返回添加记录前和添加记录后的总条数

create or replace procedure addCheckBookCount(boookn in varchar2, booktypeId in number,price in number, befern out number, aftern out number) as
begin
  declare maxId number;
  n number;
  begin 
    select count(*) into befern from t_book;
    select count(*) into n from t_book where bookname=boookn;
    if (n>0) then
      return;
    end if;
    select max(id) into maxId from t_book;
    insert into t_book(id,bookname,type_id,price) values(maxId+1, boookn,booktypeId,price);
    select count(*) into aftern from t_book;
    commit;
  end;
end addCheckBookCount;

执行存储过程：

execute用于执行单据。这里调用存储过程，需要单独调用：

declare befern number;
        aftern number;
begin
  addCheckBookCount('代码简洁之道3',2,66,befern,aftern);
  dbms_output.put_line('新增前条数：' || befern || '新增后条数：' ||aftern);
end;

输出结果：

三、存储过程Debug

1、打开调试，右键 Add debug information

2、test调试， Test，然后进入调试界面。

3、调试界面，输入参数

4、开始调试

5、查看参数

鼠标放到对应参数上，即可显示当前值。

四、程序包

引入的目的，是为了有效的管理函数和存储过程，当项目模块很多的时候，用程序包管理就很有效了。

语法：

Create or replace package 包名  as
变量名称1 数据类型1；
变量名称2 数据类型2；
...
...
Function 函数名称1(参数列表) return 数据类型1；
Function 函数名称2(参数列表) return 数据类型2；
...
...
Procedure 存储过程名称1(参数列表)；
Procedure 存储过程名称2(参数列表)；
...
...
End 包名；