Oracle自定义函数

目录

1.系统预定义函数

2.Oracle自定义函数语法

3.什么是编译

4.自定义函数练习题 

---

1.系统预定义函数

---聚合函数  sum  max  min count avg median wm_concat

---常用函数：数学函数  字符串函数  时间  trunc (date,'q')

---分析函数： over()

ratio_to_report()   NTILE()OVER()  LEAD() LAG() wm_concat() first_value()

row_number()  RANK()  dense_rank()

2.Oracle自定义函数语法

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

3.什么是编译

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

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

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

4.自定义函数练习题 

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

create or replace function fun_N(N number) return number
asf_sum number := 0; -- 存放临时累加的和
beginfor i in 1..Nloopf_sum := f_sum + i;end loop;return f_sum;-- 定义异常
exceptionwhen others thenDBMS_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
asf_power number := 1; -- 存放临时累加的和
beginfor i in 1..N2loopf_power := f_power * N1;end loop;return f_power;-- 定义异常
exceptionwhen others thenDBMS_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 -- 参数不能定义长度
asv_ename varchar2(10); -- 变量要定义长度
beginselect ename into v_ename from emp where EMPNO = P_EMPNO;return v_ename;-- 定义异常
exceptionwhen others thenDBMS_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 -- 参数不能定义长度
asv1 number := 1; -- 临时存放累加值
beginfor i in 1..P_Nloopv1 := v1 * i;end loop;return v1;-- 定义异常
exceptionwhen others thenDBMS_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
astemp number;
beginif M > N THENtemp := M;elsetemp := 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
asv_ct number;
beginselect count(1) into v_ct from emp where JOB = P_JOB;return v_ct;
end;select F_AMOUNT('MANAGER') from DUAL;