Oracle - 참조자에서 특정 열 선택

Oracle - 참조자에서 특정 열 선택

내 상황:

• 표 1이라는 이름의 테이블이 있습니다.열이 많이 있습니다. 그 중 하나가 열 1입니다.저는 다른 칸은 모릅니다, 가끔 바뀔 수도 있습니다.
• 테이블 1% 행 유형인 cur_Table1을 반환하는 강력한 유형의 참조자 유형이 있습니다.
• SP1이라는 저장 프로시저가 있는데, 이 저장 프로시저의 out 매개 변수는 cur_Table1 유형입니다.테이블이나 형식 자체가 아닌 이 저장 프로시저만 볼 수 있는 다른 데이터베이스에서 이 SP1 저장 프로시저를 호출합니다.

반환된 커서에서 열 1만 선택하려면 어떻게 해야 합니까?

레코드로 가져올 수 있거나 커서에 열이 있는 변수의 수만큼 가져올 수 있다는 것은 알고 있지만 열 하나만 알고 있으므로 전체 레코드나 올바른 변수 수를 선언할 수 없습니다.

사용할 수 있습니다.DBMS_SQL하지만 예쁘지는 않습니다.

표 및 표본 데이터(Column 1은 숫자 1 - 10):

create table table1(column1 number, column2 date, column3 varchar2(1000), column4 clob);

insert into table1
select level, sysdate, level, level from dual connect by level <= 10;
commit;

참조를 열고 모든 항목을 선택하는 절차가 포함된 패키지:

create or replace package test_pkg is
    type cur_Table1 is ref cursor return table1%rowtype;
    procedure sp1(p_cursor in out cur_table1);
end;
/

create or replace package body test_pkg is
    procedure sp1(p_cursor in out cur_table1) is
    begin
        open p_cursor for select column1, column2, column3, column4 from table1;
    end;
end;
/

참조자에서 COLUMN1 데이터를 읽는 PL/SQL 블록:

--Basic steps are: call procedure, convert cursor, describe and find columns,
--then fetch rows and retrieve column values.
--
--Each possible data type for COLUMN1 needs to be added here.
--Currently only NUMBER is supported.
declare
    v_cursor sys_refcursor;
    v_cursor_number number;

    v_columns number;
    v_desc_tab dbms_sql.desc_tab;
    v_position number;
    v_typecode number;
    v_number_value number;
begin
    --Call procedure to open cursor
    test_pkg.sp1(v_cursor);
    --Convert cursor to DBMS_SQL cursor
    v_cursor_number := dbms_sql.to_cursor_number(rc => v_cursor);
    --Get information on the columns
    dbms_sql.describe_columns(v_cursor_number, v_columns, v_desc_tab);

    --Loop through all the columns, find COLUMN1 position and type
    for i in 1 .. v_desc_tab.count loop
        if v_desc_tab(i).col_name = 'COLUMN1' then
            v_position := i;
            v_typecode := v_desc_tab(i).col_type;

            --Pick COLUMN1 to be selected.
            if v_typecode = dbms_types.typecode_number then
                dbms_sql.define_column(v_cursor_number, i, v_number_value);
            --...repeat for every possible type.
            end if;
        end if;
    end loop;

    --Fetch all the rows, then get the relevant column value and print it
    while dbms_sql.fetch_rows(v_cursor_number) > 0 loop
        if v_typecode = dbms_types.typecode_number then
            dbms_sql.column_value(v_cursor_number, v_position, v_number_value);
            dbms_output.put_line('Value: '||v_number_value);
        --...repeat for every possible type
        end if;
    end loop;   
end;
/

원래 문제를 봤을 때 조너선의 대답은 여전히 정확하기 때문에 그렇게 표시해 두겠지만, 저는 완전히 다른, 훨씬 더 좋은 일을 하게 되었습니다.

문제는 SP1의 데이터베이스를 제어할 수 없기 때문에 타사 클라이언트로 다른 곳에서 호출해야 한다는 것입니다.이제 SP 뿐만 아니라 커서의 종류도 볼 수 있게 되었습니다.저는 여전히 테이블을 보지 못했지만 이제 훨씬 더 깨끗한 해결책이 있습니다.

다른 데이터베이스에서 이 유형을 볼 수 있는 권한이 부여되었습니다.

type cur_Table1 is ref cursor return Table1%rowtype;

이제 데이터베이스에서 다음과 같은 작업을 수행할 수 있습니다.

mycursor OtherDB.cur_Table1;
myrecord mycursor%rowtype;
...
OtherDB.SP1(mycursor);
fetch mycursor into myrecord;
dbms_output.put_line(myrecord.Column1);

저는 여전히 테이블에 접근할 필요가 없어요. 커서만 보여요.핵심은 마법의 %행 유형이 테이블뿐만 아니라 커서에도 작동한다는 것입니다.sys_refcursor에서는 작동하지 않지만 강력한 유형의 sys_refcursor에서는 작동합니다.이 코드를 고려할 때, 저는 다른 쪽에서 변경되는 사항이 있더라도 상관하지 않고 모든 열 또는 기록을 정의할 필요가 없으며, 관심 있는 열 하나만 지정합니다.

저는 Oracle에 대한 OOP 태도가 정말 마음에 듭니다.

옵션인지 아닌지는 모르겠지만, 원하는 특정 값을 반환하는 기능을 만드는 것이 더 나은 해결책이 아닐까요?이렇게 하면 추가 데이터 전송에 따른 오버헤드를 방지할 수 있습니다.또는 양쪽 모두가 알고 있는 알려진 필드 집합으로 커서를 정의할 수 있습니다.

언급URL : https://stackoverflow.com/questions/10321571/oracle-select-a-specific-column-from-a-ref-cursor