避免SQL中的函式呼叫

在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

點選(此處)摺疊或開啟

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1072037/，如需轉載，請註明出處，否則將追究法律責任。

相關文章