-- Start
一个查询可能要搜索上百万行数据,在查询中使用函数就可能被调用上百万次,这会严重影响性能,下面是一个简单的例子。
-- 创建表
CREATE TABLE Department
(
Department_Id NUMBER(9,0),
Department_Name VARCHAR2(40)
);
CREATE TABLE Employee
(
Employee_id NUMBER(9,0),
Employee_Name VARCHAR2(40),
Department_Id NUMBER(9,0)
);
-- 定义函数
CREATE OR REPLACE FUNCTION getDepartmentNameById(
DepartmentId number
)
RETURN varchar2
AS
DepartmentName VARCHAR2(40);
BEGIN
select Department_Name into DepartmentName from Department where Department_Id = DepartmentId;
return DepartmentName;
END;
-- 查询 SQL -- 使用函数
SELECT getDepartmentNameById(Department_Id) DepartmentName, Employee_Name FROM Employee;
-- 查询 SQL -- 使用表连接
SELECT
d.Department_Name,
e.Employee_Name
FROM
Department d,
Employee e
WHERE
d.Department_Id = e.Department_Id;
-- 更多参见:Oracle SQL 优化精萃
-- 声明:转载请注明出处
-- Last edited on 2015-06-29
-- Created by ShangBo on 2015-06-29
-- End