Oracle 避免在查询中使用函数

来源：互联网发布：阿里云打印域名证书编辑：程序博客网时间：2024/06/05 21:06

-- 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 eWHEREd.Department_Id = e.Department_Id;

-- 更多参见：Oracle SQL 优化精萃

-- 声明：转载请注明出处

-- Last edited on 2015-06-29

-- Created by ShangBo on 2015-06-29

-- End

0 0