Function一般是Tools性的东西。

Stored Procedures一般是一些操作。

一、Oracle 自定义函数

Syntax：

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

Example：统计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;

输出结果：

带参数的函数

Example：

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 存储过程

Syntax：

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

参数In 只进不出

Example：向t_book中添加Data。

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;

Stored Procedures调用

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;

调用Stored Procedures

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

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

Out 只出不进

Example1：

Return添加记录前和添加记录后的总条数

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;

ExecuteStored Procedures：

execute用于Execute单据。这里调用Stored Procedures，需要单独调用：

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、查看参数

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

四、程序包

引入的目的，是为了有效的管理Function和Stored Procedures，当项目模块很多的时候，用程序包管理就很有效了。

Syntax：

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

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

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;

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

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.。。)；

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

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);

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;

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

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