Oracle的自定义函数

发表于 | 分类于 ， |
| 字数统计: 603

在Oracle数据库中，为了实现特定的功能，可以自定义函数，就像C/C++语言，除了系统的库函数，程序员还会编写很多自定义的函数。

创建函数

语法格式：

1
2
3
4
5
6
7
8
9
10
11
12
create or replace function 函数名(参数1 模式 数据类型,......) return 数据类型
as
  -- 定义局部变量。
  变量1 数据类型;
  ......
begin
  -- 实现函数功能的PL/SQL代码。
  ......
  exception
  -- 异常处理的PL/SQL代码。
  ......
end;

  1. 参数的模式有三种：
    • in：只读模式，在函数中，参数只能被引用/读取，不能改变它的值。
    • out：只写模式，参数只能被赋值，不能被引用/读取。
    • in out：可读可写。
      参数的模式可以不写，缺省为in，out和in out两种模式极少使用。
  2. as/is二选一，在这里没有区别。
  3. 可以不定义局部变量。
  4. 可以没有异常（exception）处理代码段。

如下是一个示例：

1
2
3
4
5
6
7
8
9
10
11
12
function JudgeExist(p_budgetid in varchar2) return number 
is
    v_count number(2);
begin
    select count(1)
    into v_count
    from  project_budget
    where budgetid = p_budgetid
    and isdel = 0;

    return v_count;
end;

函数的调用

自定义函数的调用与Oracle数据库自带的函数调用的方法相同。

1
select JudgeExist('123') from dual;

在存储过程中也可以调用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
procedure AddProjectBuget(p_returnValue out number,
                            p_budgetid    in varchar2, --项目预算表数据的唯一id
                            p_projectid   in varchar2, --项目id
                            p_projectname in varchar2, --项目名称                        
                            p_expendtime  in varchar2, --支出日期
                            p_usage       in varchar2, --用途
                            p_totalbudget in number, --预算金额(元)
                            p_spendbudget in number, --支出金额(元)                                
                            p_applicant   in varchar2, --申请人
                            p_addperson   in varchar2) --创建人
  
is
    v_count number(2);
begin
    v_count := JudgeExist(p_budgetid);
    if v_count = 0 then
    insert into project_budget
        (projectid,
        projectname,
        createtime,
        expendtime,
        totalbudget,
        spendbudget,
        applicant,
        remainbudget,
        usage,
        addperson,
        isdel)
    values
        (p_projectid,
        p_projectname,
        sysdate,
        to_date(p_expendtime, 'yyyy-MM-dd'),
        p_totalbudget,
        p_spendbudget,
        p_applicant,
        p_totalbudget - p_spendbudget, --剩余金额(元)=预算金额(元)-支出金额(元)
        p_usage,
        p_addperson,
        0);
    p_returnValue := 1; --如果添加成功，返回1
    else
    p_returnValue := -2; --如果添加失败，返回-2
    end if;

    commit;
exception
    when others then
    begin
        raise_application_error(-20999,
                                '程序出错，错误代码:' || sqlcode || '错误信息:' ||
                                sqlerrm);
        p_returnValue := -1;
        rollback;
    end;
end;

删除自定义函数

语法格式：

1
drop function 函数名;

例如：

1
drop function JudgeExist;

------ 本文完 ------