Вопрос по plsql, sys-refcursor, oracle, stored-procedures, rowtype – Как объявить% ROWTYPE переменной со слабым типом SYS_REFCURSOR?

8

Приведенный ниже код W.r.t Я не могу объявить тип выборки в переменную в качестве базовой таблицы s ROWTYPE, поскольку SYS_REFCURSOR находится в элементе выбора, который объединяет две таблицы, а также выбирает несколько функций, вызываемых для атрибутов двух базовых таблиц; то есть я не могу объявить как L_RECORD T% ROWTYPE

---
DECLARE
  P_RS SYS_REFCURSOR;
  L_RECORD P_RS%ROWTYPE;
BEGIN
  CAPITALEXTRACT(
    P_RS => P_RS
  );
    OPEN P_RS;
    LOOP
      BEGIN
        FETCH P_RS INTO L_RECORD;
        EXIT WHEN P_RS%NOTFOUND;
        ...
      EXCEPTION
        WHEN OTHERS THEN
        ...
      END;
    END LOOP;
    CLOSE P_RS;
END;
--------
CREATE or REPLACE PROCEDURE CAPITALEXTRACT
(
    p_rs OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_rs for 
     select t.*,tminusone.*, f(t.cash), g(t.cash) FROM T t, TMINUSONE tminusone
    where t.ticket=tminusone.ticket;
END CAPITALEXTRACT;

Конечно, я не хочу определять статическую таблицу R со столбцами, как возвращено в SYS_REFCURSOR, а затем объявлять как L_RECORD R% ROWTYPE.

И отсюда вопрос: Как объявить% ROWTYPE переменной со слабым типом SYS_REFCURSOR?

Ваш Ответ

1   ответ
14

вы не можете. Вам необходимо определить переменную для каждого столбца, который будет возвращен.

DECLARE
    P_RS SYS_REFCURSOR;
    L_T_COL1 T.COL1%TYPE;
    L_T_COL1 T.COL2%TYPE;
    ...

А затем загрузить список столбцов:

FETCH P_RS INTO L_T_COL1, L_T_COL2, ... ;

Это болезненно, но выполнимо, если вы знаете, чего ожидаете в курсоре ссылки. С помощьюT.* однако, в вашей процедуре это становится хрупким, поскольку добавление столбца в таблицу нарушит код, который считает, что он знает, какие столбцы есть и в каком порядке они находятся. (Вы также можете разбить его между средами, если таблицы не построены). последовательно - я видел места, где порядок столбцов различен в разных средах). Вы, вероятно, захотите убедиться, что вы в любом случае выбираете только те столбцы, которые вам действительно интересны, чтобы избежать необходимости определять переменные для вещей, которые вы никогда не прочитаете.

С 11 г вы можете использоватьDBMS_SQL пакет для преобразования вашегоsys_refcursor вDBMS_SQL курсор, и вы можете запросить это, чтобы определить столбцы. В качестве примера того, что вы можете сделать, будет напечатано значение каждого столбца в каждой строке с именем столбца:

DECLARE
    P_RS SYS_REFCURSOR;
    L_COLS NUMBER;
    L_DESC DBMS_SQL.DESC_TAB;
    L_CURS INTEGER;
    L_VARCHAR VARCHAR2(4000);
BEGIN
    CAPITALEXTRACT(P_RS => P_RS);
    L_CURS := DBMS_SQL.TO_CURSOR_NUMBER(P_RS);
    DBMS_SQL.DESCRIBE_COLUMNS(C => L_CURS, COL_CNT => L_COLS,
        DESC_T => L_DESC);

    FOR i IN 1..L_COLS LOOP
        DBMS_SQL.DEFINE_COLUMN(L_CURS, i, L_VARCHAR, 4000);
    END LOOP;

    WHILE DBMS_SQL.FETCH_ROWS(L_CURS) > 0 LOOP
        FOR i IN 1..L_COLS LOOP
            DBMS_SQL.COLUMN_VALUE(L_CURS, i, L_VARCHAR);
            DBMS_OUTPUT.PUT_LINE('Row ' || DBMS_SQL.LAST_ROW_COUNT
                || ': ' || l_desc(i).col_name
                || ' = ' || L_VARCHAR);
        END LOOP;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(L_CURS);
END;
/

Это не имеет большого практического применения, и для краткости я рассматриваю каждое значение как строку, поскольку я все равно хочу его напечатать. Посмотрите на документы и найдите примеры для более практического применения.

Если вы хотите только несколько столбцов из вашего курсора ссылки, вы можете, я думаю, обойтиl_desc и запишите позицию, гдеcolumn_name это то, что вас интересует, как числовая переменная; затем вы можете обратиться к столбцу по этой переменной позже, где вы обычно используете имя в цикле курсора. Зависит от того, что вы делаете с данными.

Но если вы неexpecting не знать порядок столбцов, который вы возвращаете, что маловероятно, поскольку вы, кажется, управляете процедурой - и при условии, что вы избавляетесь от.*s - вам, вероятно, гораздо лучше уменьшить количество возвращаемых столбцов до минимума, которое вам нужно, и просто объявить их все по отдельности.

Удивительный человек, я целый год искал, как это сделать, и это объяснило это лучше всего. Примечание. Мне не нужен CAPITALEXTRACT (P_RS = & gt; P_RS); линия. (на самом деле он допустил ошибку, не был уверен, что он сделал, поэтому я закомментировал, и мой pl / sql прославился)
@ Armyofda12mnkeys - рад, что это помогло. CAPITALEXTRACT был функцией, специфичной для этого вопроса, а не чем-то присущим решению, так что не беспокойтесь об этом.

Похожие вопросы