Oracle如何自定义函数

目录

• 1.系统预定义函数
• 2.oracle自定义函数语法
• 3.什么是编译
• 4.自定义函数练习题
• 总结

1.系统预定义函数

• ---聚合函数 sum max min count avg median wm_concat
• ---常用函数：数学函数 字符串函数 时间 trunc (dajste,'q')
• ---分析函数： over()

ratio_to_report() NTILE()OVER() LEAD() LAG() wm_concat() first_value()
row_number() RANK() dense_rank()

2.Oracle自定php义函数语法

CREATE OR REPLACE FUNCTION 函数名(参数1 数据类型，参数2 [IN | OUT] 数据类型……)
    RETURN 返回的数据类型 ---返回的如果是  VARCHAR2 类型，也不能给长度
    IS ---IS 或者 AS 随便写一个都可以
    声明变量 ---声明变量的时候记得给长度
BEGIN
    函数的具体逻辑;
    RETURN 声明变量;
    --里面必须要有一个RETURN子句
---异常处理
END;

3.什么是编译

编译，就是把函数在数据库里创建。

编译就是将写好的代码放在数据库里某个文件里存着，调用函数的时候，就会到数据库里执行存放的函数逻辑。

检查函数编译(创建)的时候有没有报错 选中函数的名称 > 鼠标右键 > view/edit

4.自定义函数练习题

示例：创建计算 1到 N 的和的函数

create orhttp://www.devze.com replace function fun_N(N number) return number
as
    f_sum number := 0; -- 存放临时累加的和
begin
    for i in 1..N
        loop
            f_sum := f_sum + i;
        end loop;
    return f_sum;
    -- 定义异常
exception
    when others then
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
    -- 使用 DBMS_OUTPUT.PUT_LINE 过程输出异常的错误消息。
    -- SQLERRM 是一个Oracle预定义的异常变量，它包含了最近一次数据库运行时错误的描述。
end;
select fun_N(10)
FROM DUAL;

示例：假如 Oracle没有 POWER 这个函数,需要人工自己开发函数POWER_A

create or replace function POWER_A(N1 number, N2 number) return number
as
    f_power number := 1; -- 存放临时累加的和
begin
    for i in 1..N2
        loop
            f_power := www.devze.comf_power * N1;
        end loop;
    return f_power;
    -- 定义异常
exception
    when others then
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;

select POWER_A(3,3) FROM DUAL;

第1次循环：v1 = 1 * 3 = 3

第2次循环：v1 = 3 * 3 = 9

第3次循环：v1 = 9 * 3 = 27 → 最终结果

示例：创建一个编程客栈函数，根据传递给函数的员工编号返回员工的姓名；

create or replace function F_ENAME(P_EMPNO number) 
return varchar2 -- 参数不能定义长度
as
    v_ename varchar2(10); -- 变量要定义长度
begin
    select ename into v_ename from emp where EMPNO = P_EMPNO;
    return v_ename;
    -- 定义异常
exception
    when others then
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
select F_ENAME(7369)
FROM DUAL;

开发自定义函数，返回 数字的阶乘，例如：SELECT F_factorial(4) FROM DUAL;

create or replace function F_factorial(P_N number) return number -- 参数不能定义长度
as
    v1 number := 1; -- 临时存放累加值
begin
    for i in 1..P_N
        loop
            v1 := v1 * i;
        end loop;
    return v1;
    -- 定义异常
exception
    when others then
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;

SELECT F_factorial(4) FROM DUAL;

开发自定义函数，比较两个数字的大小 P_MAX_MIN(M NUMBER,N NUMBER)返回比较大的值；

create or replace function P_MAX_MIN(M NUMBER, N NUMBER) return number
as
    temp number;
begin
    if M > N THEN
        temp := M;
    else
        temp := N;
    end if;
    return temp;
end;

select P_MAX_MIN(12, 9) from DUAL;

创建一个函数，函数的功能是根据传入的一个岗位名称，将这个岗位的员工数量返回

create or replace function F_AMOUNT(P_JOB VARCHAR2) return number
as
    v_ct number;
begin
    select count(1) into v_ct from emp where JOB = P_JOB;
    return v_ct;
end;

select F_AMOUNT('MANAGER') from DUAL;

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。