避免SQL中的函数调用

最新推荐文章于 2021-07-31 20:24:06 发布

cmff98425

最新推荐文章于 2021-07-31 20:24:06 发布

阅读量167

点赞数

在SQL编码中，尽量避免在SQL中进行函数调用，否则会产生大量的递归调用而影响性能。

如果用表连接来代替函数调用，往往会更高效。

点击(此处)折叠或打开

DROP TABLE people;
DROP TABLE sex;
CREATE TABLE people (first_name VARCHAR2(200), last_name VARCHAR2(200), sex_id NUMBER);
CREATE TABLE sex(NAME VARCHAR2(20), sex_id NUMBER);
INSERT INTO people(first_name, last_name, sex_id) SELECT object_name, object_type, 1 FROM dba_objects;
INSERT INTO sex(name, sex_id) VALUES(\'男\' ,1);
INSERT INTO sex(name, sex_id) VALUES(\'女\' ,2);
INSERT INTO sex(name, sex_id) VALUES(\'不详\' ,3);
COMMIT;
CREATE OR REPLACE FUNCTION get_sex_name(p_id sex.sex_id%TYPE) RETURN sex.name%TYPE
IS
v_name sex.name%TYPE;
BEGIN
SELECT NAME INTO v_name FROM sex WHERE sex_id = p_id;
RETURN v_name;
END;
/
--查询people表的信息,同时通过sex表,获取人员的性别信息
SET autotrace traceonly
SET timing ON
SELECT sex_id, first_name || \' \' || last_name AS full_name, get_sex_name(sex_id) AS gender FROM people;
75559 rows selected.
Elapsed: 00:00:04.68
Execution Plan
----------------------------------------------------------
Plan hash value: 2528372185

点击(此处)折叠或打开

点击(此处)折叠或打开

--验证函数调用的次数,就是执行计划中递归调用的次数
--在函数中增加 dbms_application_info.set_client_info(userenv(\'client_info\')+1)。
CREATE OR REPLACE FUNCTION get_sex_name(p_id sex.sex_id%TYPE) RETURN sex.name%TYPE IS
v_name sex.name%TYPE;
BEGIN
SELECT NAME INTO v_name FROM sex WHERE sex_id = p_id;
dbms_application_info.set_client_info(userenv(\'client_info\') + 1);
RETURN v_name;
END;
exec dbms_application_info.set_client_info(\'0\');
SET autotrace traceonly
SELECT sex_id, first_name || \' \' || last_name AS full_name, get_sex_name(sex_id) AS gender FROM people;
75559 rows selected.
Elapsed: 00:00:05.62
Execution Plan
----------------------------------------------------------
Plan hash value: 2528372185