-------------------自定义函数:把一些功能/操作封装起来,存在数据库里面,供用户多次使用,不同于sp的是,自定义函数必须要返回一个值(return)
FUN一般用来计算用户的指标
--创建自定义函数的语法结构:
CREATE OR REPLACE FUNCTION 函数名[(参数1 [参数类型] 数据类型[,参数2 参数类型 数据类型......])]
RETURN 返回的数据类型
IS / AS
--声明部分
BEGIN
--逻辑体
--必须有一个 RETURN 子句;
END;
--开发规范:函数名以 FUN_ 开头
--需要注意的是:
1、“RETURN 返回的数据类型” 指的就是 逻辑体里面 RETURN 子句里面的值的类型
2、逻辑体里面必须要有一个RETURN 子句(意思就是:里面必须有一个 RETURN 值/变量)
3、所有的 RETURN 子句 里面的值/变量 的 类型跟 “RETURN 返回的数据类型” 要一致
4、“RETURN 返回的数据类型” 也是只需要定义类型,不能定义长度
--比如:创建一个自定义函数,比较两个数字的大小,并返回较大的数字
CREATE OR REPLACE FUNCTION FUN_BIGGER(X NUMBER,Y NUMBER)
RETURN NUMBER
IS
BEGIN
IF X > Y THEN
RETURN X;
ELSE
RETURN Y;
END IF;
END;
--调用自定义函数:跟调用内置函数的语法一样
SELECT FUN_BIGGER(1,3) BIGGER FROM DUAL;
--小练习一把:创建一个自定义函数,计算员工表中的员工的平均工资,并保留整数
CREATE OR REPLACE FUNCTION FUN_AVG
RETURN NUMBER
IS
V_AVG NUMBER;
BEGIN
SELECT ROUND(AVG(SAL))
INTO V_AVG
FROM EMP;
RETURN V_AVG;
END;
--调用
SELECT EMPN FROM emp;
---几点:
1、上面的这个自定义函数是针对于某一张特定的表来拿到结果
那么,这个时候,无论在哪个表中查询,那么返回的都是EMP表中的平均工资取整
2、自定义函数在创建的时候可以借用一切能用的东西(比如:内置函数)来达到目的(简化代码,提高效率)
3、当创建自定义函数没有带参数的时候,在调用的时候可以不带括号。但是最好还是带上,有助于区别于这是个函数,而不是一个列名
---小练习一把:创建自定义函数,根据员工的入职日期,返回该员工缴纳社保的月份(年-月 varchar2)
--社保缴纳规则:15号之前的当月缴纳,15号之后次月缴纳
CREATE OR REPLACE FUNCTION FUN_SBJN(P_DATE DATE)
RETURN VARCHAR2
IS
V_SBJN VARCHAR2(20);
BEGIN
V_SBJN := TO_CHAR(ROUND(P_DATE,'MM'),'YYYY-MM');
RETURN V_SBJN;
END;
--测试一下
SELECT FUN_SBJN(SYSDATE) FROM DUAL;
SELECT E.*,FUN_SBJN(E.HIREDATE) 社保缴纳月份
FROM EMP E;
---fun跟sp的相同跟不同
--相同的地方:
1、语法结构上相似
2、都是起到封装的作用
3、都是将一些功能/操作/逻辑封装起来,存在数据库里面,便于后续的多次使用
--不同的地方:
1、sp不需要返回值
fun必须要返回一个值
2、sp主要用于数据同步
fun主要用于计算指标
3、sp在其他的代码块里调用
fun跟内置函数一样的调用,放在select后面