数组TABLE和RECORD的综合例子

-- Create table
create table SPS_TELESEG
(
  teleseg  VARCHAR2(20),
  provcode VARCHAR2(10),
  areacode VARCHAR2(10)
);
/
CREATE OR REPLACE PACKAGE cache_teleseg IS

    -- Author  : rongxr
    -- Created : 2015-7-28 21:07:48
    -- Purpose :
    TYPE teleseg_record IS RECORD(
        teleseg  sps_teleseg.teleseg%TYPE,
        provcode sps_teleseg.provcode%TYPE,
        areacode sps_teleseg.areacode%TYPE);

    TYPE teleseg_table IS TABLE OF teleseg_record INDEX BY PLS_INTEGER;
    -- Public type declarations
    FUNCTION with_sql(teleseg_varchar_in IN sps_teleseg.teleseg%TYPE) RETURN teleseg_record;
    FUNCTION from_cache(teleseg_varchar_in IN sps_teleseg.teleseg%TYPE) RETURN teleseg_record;
END;
/
CREATE OR REPLACE PACKAGE BODY cache_teleseg IS
    g_cache teleseg_table;

    FUNCTION with_sql(teleseg_varchar_in IN sps_teleseg.teleseg%TYPE) RETURN teleseg_record IS
        l_row teleseg_record;
    BEGIN
        SELECT teleseg,
               provcode,
               areacode
        INTO l_row
        FROM sps_teleseg
        WHERE teleseg = teleseg_varchar_in;
        RETURN l_row;
    END;

    FUNCTION from_cache(teleseg_varchar_in IN sps_teleseg.teleseg%TYPE) RETURN teleseg_record IS
    BEGIN
        RETURN g_cache(teleseg_varchar_in);
    END;

BEGIN
    FOR teleseg_rec IN (SELECT teleseg,
                               provcode,
                               areacode
                        FROM sps_teleseg) LOOP
        BEGIN
            g_cache(teleseg_rec.teleseg) := teleseg_rec;
        END;
    END LOOP;
END;
/

CREATE OR REPLACE FUNCTION getareacode_cache(paramusernumber IN VARCHAR2)
    RETURN VARCHAR2 IS
    area_code   VARCHAR2(32);
    user_number VARCHAR2(32);
BEGIN
    area_code := '0';
    IF (LENGTH(paramusernumber) != 11 AND LENGTH(paramusernumber) != 13) THEN
        RETURN area_code;
    END IF;

    IF LENGTH(paramusernumber) = 11 THEN
        user_number := '86' || paramusernumber;
    ELSE
        user_number := paramusernumber;
    END IF;
    BEGIN
        area_code := cache_teleseg.from_cache(SUBSTR(user_number, 1, 9))
                    .areacode;
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
    RETURN area_code;
END;
/

CREATE OR REPLACE FUNCTION getprovcode_cache(paramusernumber IN VARCHAR2)
    RETURN VARCHAR2 IS
    area_code   VARCHAR2(32);
    user_number VARCHAR2(32);
BEGIN
    area_code := '0';
    IF (LENGTH(paramusernumber) != 11 AND LENGTH(paramusernumber) != 13) THEN
        RETURN area_code;
    END IF;

    IF LENGTH(paramusernumber) = 11 THEN
        user_number := '86' || paramusernumber;
    ELSE
        user_number := paramusernumber;
    END IF;
    BEGIN
        area_code := cache_teleseg.from_cache(SUBSTR(user_number, 1, 9))
                    .provcode;
    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;
    RETURN area_code;
END;
/