Oracle学习笔记-函数

---------基本语法---------
1.函数
CREATE OR REPLACE FUNCTION 函数名（参数列表）
RETURN 数据类型
IS | AS
PL/SQL

1.1 函数和存储过程的区别
1、函数只能有一个返回值，存储过程可以有0到任意多个。
2、函数可以嵌套在SQL语句中，存储过程是不行。执行本质是一样的。
3、函数中临时表不能用。
4、存储过程功能强大，能实现比较复杂的业务。
5、【是否可以独立执行】函数只能作为查询语句的一部分，存储过程是可以的。

 

---------举例---------
1、根据部门编号获取部门的平均水平
 

CREATE OR REPLACE FUNCTION f_avgSalByDept(dept_no NUMBER)
RETURN 	NUMBER
IS
	avgSal NUMBER;
BEGIN 
	SELECT AVG(sal) INTO avgSal FROM emp WHERE deptno = dept_no;
	RETURN avgSal;
	EXCEPTION
		WHEN no_data_found THEN
			raise_application_error(-20001,'无此部门');
END;

--调用
 

SELECT f_avgSalByDept(20)
FROM emp
GROUP BY deptno;

2、根据部门编号返回该部门下工资大于部门平均工资的人数
 

CREATE OR REPLACE FUNCTION f_moreSalThenAvgByDept(dept_no NUMBER)
RETURN 	NUMBER
IS
	e_count NUMBER;
BEGIN 
	SELECT COUNT(*) INTO e_count 
	FROM emp t
	WHERE t.sal >= (SELECT AVG(sal) FROM emp e WHERE e.deptno = dept_no)
	AND t.deptno = dept_no;
	RETURN e_count;
END;

--调用

SELECT f_moreSalThenAvgByDept(20)
FROM emp
GROUP BY deptno;