DBMS_SQL,컬럼값을 가로행으로 출력,Unpivot, DESCRIBE_COLUMNS, DEFINE_COLUMN, COLUMN_VALUE,SQL교육,SQL학원,ORACLE교육학

오라클,PLSQL,DBMS_SQL,컬럼값을 가로행으로 출력 실습영상 입니다.

강의자료 : http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=732

먼저 예제에서 사용되는 몇가지 프로시저에 대해 이해를 하고 실습을 하겠습니다.

DBMS_SQL.DESCRIBE_COLUMNS 프로시저 : 컬럼의 정보를 DESC_REC 레코드 타입의 배열 형태인 DESC_TAB(OUT 타입) 변수에 담아두면 호출한 쪽에서 꺼내서 사용한다.

DBMS_SQL.DESCRIBE_COLUMNS (
c IN INTEGER,
col_cnt OUT INTEGER,
desc_t OUT DESC_TAB);
OPEN되고 파싱되는 커서의 컬럼을 정의. 컬럼의 정보를 배열에 담는다.

c : 커서, col_cnt : 컬럼 개수
desc_r : 컬럼의 정의를 담을 배열

DESC_TAB 타입은 DESC_REC를 담고 있는 오라클 테이블 타입(배열)

DESC_REC 에는 col_type, col_maxlen, col_name, col_name_len, col_schema_name, col_schema_name_len, col_precision, col_scale 등의 속성이 있다.





DBMS_SQL.DEFINE_COLUMN


[컬럼값의 타입이 문자열인 경우]
DBMS_SQL.DEFINE_COLUMN (
c IN INTEGER,
position IN INTEGER,
column IN VARCHAR2
CHARACTER SET ANY_CS,
column_size IN INTEGER);

[컬럼값의 타입이 문자열이 아닌 경우]
DBMS_SQL.DEFINE_COLUMN (
c IN INTEGER,
position IN INTEGER,
column IN datatype);


커서에서 추출되는 컬럼 값을 담을 변수를 정의. 컬럼의 위치는 SELECT 목록에서 상대적 위치로 구별된다.

c : 커서ID
position : 컬럼의 상태위치
column : 커서의 SELECT되는 컬럼값을 받을 변수
column_size : 컬럼 길이
DBMS_SQL.COLUMN_VALUE (
c IN INTEGER,
position IN INTEGER,
value OUT datatype
[,column_error OUT NUMBER]
[,actual_length OUT INTEGER]);


주어진 컬럼의 값을 리턴. FETCH_ROWS를 호출하여 가져온 데이터에 접근하는 데 사용된다.

c : 커서ID
position : 컬럼의 상태위치
value : 컬럼값을 담을 OUT 변수

[실습 : SELECT쿼리문을 입력 받아 DBMS_SQL을 이용하여 행만큼 루프를 돌면서 컬럼값을 가로행으로 출력]

CREATE OR REPLACE PROCEDURE unpivottable( p_sql IN VARCHAR2 )
AUTHID CURRENT_USER --프로시저 실행시 컴파일 사용자가 아닌 현재 접속사용자로 실행, 기본은 AUTHID DEFINER
IS
v_cursor INTEGER;
v_columnvalue VARCHAR2(500);
v_return INTEGER;
v_descrectable dbms_sql.desc_tab;
v_colcnt NUMBER;
BEGIN
-- SQL 실행을 위한 커서를 오픈, 커서ID 리턴
v_cursor := dbms_sql.open_cursor;

-- SQL문장 파싱
dbms_sql.parse(v_cursor, p_sql, dbms_sql.NATIVE);

-- 커서에 대한 컬럼정보를 DBMS_SQL.DESC_TAB 배열변수에 넣는다.
-- DBMS_SQL.DESC_TAB은 DESC_REC 레코드 타입의 배열이다.
dbms_sql.describe_columns ( v_cursor, v_colcnt, v_descrectable );

-- v_colCnt의 컬럼 개수만큼 루프를 돌며 컬럼을 정의.
-- 커서에서 추출되는 컬럼값을 담을 변수를 정의
FOR I IN 1..v_colcnt
LOOP
dbms_sql.define_column (v_cursor, I, v_columnvalue, 500);
END LOOP;

-- SQL실행, INSERT, UPDATE, DELETE의 경우 처리된 건수를 리턴받는다. 그 외는 무시
v_return := dbms_sql.EXECUTE(v_cursor);

-- 읽어들인 행의 수만큼 반복
WHILE ( dbms_sql.fetch_rows (v_cursor) &gt 0 )
LOOP
-- 컬럼 개수만큼 다시 루프를 돌면서 컬럼 값을 v_columnvalue 변수에 담는다
-- DBMS_SQL.DESC_TABdms DESC_REC 레코드 타입의 배열이고, DESC_REC 타입의 col_name은 컬럼 명칭 이다.
FOR I IN 1..v_colcnt
LOOP
-- FETCH후 컬럼값을 받아 옴
dbms_sql.COLUMN_VALUE ( v_cursor, I, v_columnvalue );
dbms_output.put_line ( rpad( v_descrectable(I).col_name, 20 ) || ': ' || v_columnvalue );
END LOOP;
-- 한행이 끝날때마다 출력
dbms_output.put_line( '--------------------------------------------' );
END LOOP;

dbms_sql.close_cursor (v_cursor);
END;

XEC UNPIVOTTABLE('select ename, sal, deptno from emp where deptno = 20');


ENAME : SMITH
SAL : 1100
DEPTNO : 20
--------------------------------------------
ENAME : JONES
SAL : 2975
DEPTNO : 20
--------------------------------------------
ENAME : SCOTT
SAL : 3000
DEPTNO : 20
--------------------------------------------
ENAME : ADAMS
SAL : 1100
DEPTNO : 20
--------------------------------------------
ENAME : FORD
SAL : 3000
DEPTNO : 20
--------------------------------------------