PLSQL Language Reference-PL/SQL子程式-PL/SQL函式結果快取-開啟函式結果快取

--函式結果快取

CREATE OR REPLACE PACKAGE department_pkg IS

    TYPE dept_info_record IS RECORD (

         dept_name  departments.department_name%TYPE,

         mgr_name   employees.last_name%TYPE,

         dept_size  PLS_INTEGER);

    -- 對結果進行快取的函式

    FUNCTION get_dept_info (dept_id PLS_INTEGER)

    RETURN dept_info_record

    RESULT_CACHE;

END department_pkg;

/

CREATE OR REPLACE PACKAGE BODY department_pkg IS

    -- 函式定義

    FUNCTION get_dept_info (dept_id PLS_INTEGER)

    RETURN dept_info_record

    --結果集依賴於DEPARTMENTS和EMPLOYEES表,對錶的任何改變都會使快取結果無效

    RESULT_CACHE RELIES_ON (DEPARTMENTS, EMPLOYEES)

    IS

        rec  dept_info_record;

    BEGIN

        SELECT department_name INTO rec.dept_name

        FROM departments

        WHERE department_id = dept_id;

        SELECT e.last_name INTO rec.mgr_name

        FROM departments d, employees e

        WHERE d.department_id = dept_id

        AND d.manager_id = e.employee_id;

        SELECT COUNT(*) INTO rec.dept_size

        FROM EMPLOYEES

        WHERE department_id = dept_id;

        RETURN rec;

    END get_dept_info;

END department_pkg;

/