oracle - PL/SQL Oracle 对未初始化组合的引用

Question

CREATE OR REPLACE TYPE gma_trg_eval AS OBJECT (
v_id NUMBER, 
v_year NUMBER, 
v_max_id NUMBER, 
v_min_id NUMBER, 
v_max_year NUMBER, 
v_min_year NUMBER);

CREATE OR REPLACE TYPE gma_trg_eval_table AS TABLE OF gma_trg_eval;

CREATE OR REPLACE FUNCTION gma_trg_test (v_p NUMBER, v_y NUMBER)
    RETURN gma_trg_eval_table PIPELINED 
IS
    v_r gma_trg_eval;
BEGIN
    SELECT MAX (id), MIN (id)
      INTO v_r.v_max_id, v_r.v_min_id
      FROM gma_trg_time_periods
     WHERE year_id = v_y;

    SELECT MAX (year_id), MIN (year_id)
      INTO v_r.v_max_year, v_r.v_min_year
      FROM gma_trg_time_periods;

    SELECT year_id, id
      INTO v_r.v_year, v_r.v_id
      FROM gma_trg_time_periods
     WHERE period = v_p AND year_id = v_y;

    IF v_r.v_year = v_r.v_min_year AND v_r.v_id = v_r.v_min_id
    THEN
        gma_msg.exit_with_message ('GMA-02006');
    END IF;

    DBMS_OUTPUT.put_line ('Min = ' || v_r.v_min_id);
    DBMS_OUTPUT.put_line ('Max = ' || v_r.v_max_id);
    DBMS_OUTPUT.put_line ('Current year = ' || v_r.v_year);
    DBMS_OUTPUT.put_line ('Current period = ' || v_r.v_id);
    DBMS_OUTPUT.put_line ('Min year = ' || v_r.v_min_year);
    DBMS_OUTPUT.put_line ('Max year = ' || v_r.v_max_year);

    IF v_r.v_year >= v_r.v_min_year AND v_r.v_year <= v_r.v_max_year
    THEN
        IF v_r.v_id = v_r.v_min_id
        THEN
            v_r.v_year := v_r.v_year - 1;
            DBMS_OUTPUT.put_line ('Year changed to:' || ' ' || v_r.v_year);
            v_r.v_id := v_r.v_max_id;
            PIPE ROW (v_r);
        ELSE
            v_r.v_id := v_r.v_id - 1;
            PIPE ROW (v_r);
        END IF;

        DBMS_OUTPUT.put_line ('Period changed to:' || ' ' || v_r.v_id);

        IF v_r.v_id = v_r.v_min_id AND v_r.v_year = v_r.v_min_year
        THEN
            DBMS_OUTPUT.put_line ('Button is not active');
            PIPE ROW (v_r);
        END IF;
        PIPE ROW (v_r);
    END IF;
    RETURN;
END;

当我执行这个函数时，

select * from table(gma_trg_test(2, 2013));

我收到以下错误。

line 64: ORA-06530: Reference to uninitialized composite
ORA-06512: at "GAMMA_OWNER.GMA_TRG_TEST", line 12

Answer 1

正如消息所述，您有一个初始化的组合(在本例中为对象)。您的 v_r 变量实际上是对对象的引用。您需要先创建对象本身，然后才能引用其字段:

CREATE OR REPLACE FUNCTION gma_trg_test (v_p NUMBER, v_y NUMBER)
    RETURN gma_trg_eval_table PIPELINED 
IS
    v_r gma_trg_eval;
BEGIN
    v_r := NEW gma_trg_eval(null, null, null, null, null, null);
    SELECT MAX (id), MIN (id)
      INTO v_r.v_max_id, v_r.v_min_id
      FROM gma_trg_time_periods
     WHERE year_id = v_y;
...

SQL Fiddle .

需要 null 值，因为您正在调用默认构造函数 - 当然您可以使用其他值，但 null 在这里看起来很合适。您也许可以通过添加自己的不带参数的构造函数来稍微简化一下，但这只会使实例化看起来更整洁，无论如何，这是一个观点问题。