Knowhow | DBMS_SQL 패키지를 이용해 컬럼 값을 세로로 출력하기
이미 여러 차례 언급했듯이 DBMS_SQL 패키지는 사용법이 복잡하고 이로 인해 코드가 길어지는 단점이 있다. 하지만 이런 단점을 상쇄할 정도로 훌륭한 기능을 수행하는 함수와 프로시저를 내장하고 있다. 이번 현장 노하우에서는 가로로 길게 늘어져 있는 컬럼 값을 세로로 일목요연하게 출력해 주는 방법을 소개하고자 한다.
컬럼 개수가 많은 테이블의 데이터를 보려면 마우스로 수평 스크롤바를 끊임없이 눌러야 한다. 보통 테이블의 키에 해당하는 컬럼은 앞쪽(왼쪽)에 위치해 있고 뒤쪽(오른쪽) 컬럼 값을 보다가 다시 앞쪽으로 왔다갔다 해야 하는데 여간 귀찮은 일이 아니다. 그래서 한 로우를 기준으로 컬럼 값을 세로로 보여주는 기능을 지원하는 툴이 있긴 하지만 모든 툴에서 이런 기능을 제공하지는 않는다. 그런데 DBMS_SQL 패키지를 사용하면 컬럼 값을 보기 좋게 세로로 출력할 수 있다.
우선 이 기능을 수행하는 프로시저를 만들어 보자.
입력
CREATE OR REPLACE PROCEDURE print_table( p_query IN VARCHAR2 )
IS
l_theCursor INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
l_columnValue VARCHAR2(4000);
l_status INTEGER;
l_descTbl DBMS_SQL.DESC_TAB;
l_colCnt NUMBER;
BEGIN
-- 쿼리 구문 자체를 p_query 매개변수에 받아 온다
-- 받아온 쿼리를 파싱한다
DBMS_SQL.PARSE( l_theCursor, p_query, DBMS_SQL.NATIVE );
-- DESCRIBE_COLUMN 프로시저 : 커서에 대한 컬럼 정보를 DBMS_SQL.DESC_TAB형 변수에 넣는다
DBMS_SQL.DESCRIBE_COLUMNS ( l_theCursor, l_colCnt, l_descTbl );
-- 선택된 컬럼 개수만큼 루프를 돌며 DEFINE_COLUMN 프로시저를 호출해 컬럼을 정의한다
FOR i IN 1..l_colCnt
LOOP
DBMS_SQL.DEFINE_COLUMN (l_theCursor, i, l_columnValue, 4000);
END LOOP;
-- 실행
l_status := DBMS_SQL.EXECUTE(l_theCursor);
WHILE ( DBMS_SQL.FETCH_ROWS (l_theCursor) > 0 )
LOOP
-- 컬럼 개수만큼 다시 루프를 돌면서 컬럼 값을 l_columnValue 변수에 담는다
-- DBMS_SQL.DESC_TAB 형 변수인 l_descTbl.COL_NAME은 컬럼 명칭이 있다
-- l_columnValue에는 컬럼 값이 들어 있다
FOR i IN 1..l_colCnt
LOOP
DBMS_SQL.COLUMN_VALUE ( l_theCursor, i, l_columnValue );
DBMS_OUTPUT.PUT_LINE ( rpad( l_descTbl(i).COL_NAME, 30 ) || ': ' || l_columnValue );
END LOOP;
DBMS_OUTPUT.PUT_LINE( '-----------------' );
END LOOP;
DBMS_SQL.CLOSE_CURSOR (l_theCursor);
END;
결과
PROCEDURE PRINT_TABLE이(가) 컴파일되었습니다
print_table 프로시저의 소스에서 한 가지 부분만 제외하면 나머지 내용은 지금까지 배운 내용을 토대로 쉽게 이해할 수 있을 것이다. 이 프로시저의 역할은 쿼리가 수행된 결과의 컬럼 값을 세로로 출력하는 것인데, 이를 위해서는 먼저 컬럼의 명칭이 필요하다. 컬럼 값이야 어차피 쿼리 결과로 반환되니까 DEFINE_COLUMN, COLUMN_VALUE 프로시저로 값을 가져올 수 있지만, 도대체 컬럼 명칭은 어떻게 가져와야 할까?
위 소스에서 굵게 표시한 부분이 바로 컬럼 명칭을 가져오는 부분이다. 이들에 대해 하나씩 알아 보자.
● DESC_TAB
: DESC_REC 레코드 타입을 요소로 하는 연관 배열
( TYPEdesc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER )
● DESC_REC
: 동적 쿼리의 단일 컬럼 정보를 담고 있는 레코드 타입으로, DESCRIBE_COLUMNS 프로시저의 OUT 매개변수 타입인 DESC_TAB의 요소 타입이다.
TYPE desc_rec IS RECORD (
col_type BINARY_INTEGER := 0, -- 데이터 타입
col_max_len BINARY_INTEGER := 0, -- 최대 길이
col_name VARCHAR2(32) := '', -- 컬럼명
col_name_len BINARY_INTEGER := 0, -- 컬럼명 길이
col_schema_name VARCHAR2(32) := '', -- 컬럼 스키마명
col_schema_name_len BINARY_INTEGER := 0, -- 컬럼 스키마명 길이
col_precision BINARY_INTEGER := 0, -- 전체 자리 수
col_scale BINARY_INTEGER := 0, -- 소수점 이하 자리 수
col_charsetid BINARY_INTEGER := 0, -- 컬럼 문자 설정 ID
col_charsetform BINARY_INTEGER := 0, -- 컬럼 문자 설정 폼
col_null_ok BOOLEAN := TRUE -- NULL 여부
);
● DBMS_SQL.DESCRIBE_COLUMNS (
c IN INTEGER,
col_cnt OUT INTEGER,
desc_t OUT DESC_TAB);
• DBMS\_SQL 패키지가 오픈하고 파싱한 커서의
컬럼 정보를 가져온다
• c: 커서 ID
• col\_cnt: 해당 커서에 대한 쿼리의 전체 컬럼
수를 가진 출력 매개변수
• desc\_t: DESC\_TAB 컬렉션 타입으로 컬럼
정보를 가진 출력 매개변수
정리하면 DESCRIBE_COLUMNS 프로시저의 첫 번째 매개변수로 커서 ID를 전달해 실행하면, 해당 쿼리에서 명시한 컬럼 수는 col_cnt에, 그리고 나머지 컬럼에 대한 정보는 desc_t라는 DESC_TAB 타입 변수에 들어 간다. 특히, DESC_TAB은 연관 배열 컬렉션 타입으로 컬럼 상세 정보가 있는 DESC_REC라는 레코드 타입을 요소로 하고 있다. 결국 col_cnt와 desc_t는 출력 변수이므로 프로시저 호출 후 이 두 변수를 참조하면 컬럼 정보를 얻어 올 수 있다. print_table 프로시저에서는 DESC_REC 레코드 항목 중 COL_NAME을 참조해서 컬럼명을 가져오고 있다.
자, 그러면 실행해서 제대로 결과를 출력해 내는지 알아 보자. 이 프로시저의 매개변수로는 동적 쿼리문이 들어가므로 ch13_physicist 테이블을 조회하는 구문을 넣어 보자.
입력
EXEC print_table ( 'SELECT * FROM ch13_physicist');
결과
IDS : 1
NAMES : Albert Einstein
BIRTH_DT : 1564-02-15
-----------------
IDS : 2
NAMES : Galileo Galilei
BIRTH_DT : 1643-01-04
-----------------
IDS : 3
NAMES : Isaac Newton
BIRTH_DT : 1858-04-23
-----------------
IDS : 4
NAMES : Max Plank
BIRTH_DT : 1879-03-14
-----------------
테이블에 담긴 데이터가 세로로 출력되었다. 사실 이 프로시저는 필자가 만든 것이 아니라 PL/SQL의 전설적인 대가인 Tom Kyte의 블로그에 올라와 있는 프로시저다. 관련 정보는 “https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958”에 있다.
print_table 프로시저를 응용하면 다양한 기능을 구현할 수 있다. 다음과 같은 상황을 가정해 보자. 30개의 컬럼이 있는 tab1이란 테이블을 사용하고 있는데, 업무 로직이 변경되어 컬럼을 2개 추가해야 한다. 그래서 tab1_new란 테이블을 신규로 만들었다. 문제는 추가된 컬럼이 맨 끝에 붙은 것이 아니라 중간에 띄엄띄엄 들어간 것이다. 따라서 신규 테이블로 데이터를 이관하기 위해 다음과 같은 쿼리를 사용할 수 없게 되었다.
입력
INSERT INTO tab1_new
SELECT *
FROM tab1;
tab1_new에는 tab1보다 2개의 컬럼이 더 있고 추가된 두 컬럼은 중간에 들어가 있어 컬럼 수와 순서가 맞지 않아 위 구문을 사용할 수는 없고, 대신 일일이 컬럼명을 기술해야 한다. 하지만 32개나 되는 컬럼을 일일이 기술하는 것도 그렇고 컬럼 순서까지 달라져서 INSERT~SELECT 구문을 작성하는 작업이 만만치 않다. 이런 상황에서는 tab1_new 기준으로 컬럼을 쭉 나열한 뒤, 새로 추가된 두 컬럼만 제거하면 문제는 말끔히 해결된다.
print_table 프로시저를 약간만 변경하면 INSERT 구문을 자동으로 만들어 낼 수 있다. 즉 일일이 모든 컬럼을 직접 명시할 필요가 없다는 말이다. DESCRIBE_COLUMNS 프로시저가 쿼리로 선택된 컬럼 명칭을 가져오기 때문에 이렇게 할 수 있는 것이다. 그럼 필자가 만든 insert_ddl 프로시저를 살펴 보자.
입력
CREATE OR REPLACE PROCEDURE insert_ddl ( p_table IN VARCHAR2 )
IS
l_theCursor INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
l_status INTEGER;
l_descTbl DBMS_SQL.DESC_TAB;
l_colCnt NUMBER;
v_sel_sql VARCHAR2(1000); -- SELECT 문장
v_ins_sql VARCHAR2(1000); -- INSERT 문장
BEGIN
-- 입력받은 테이블명으로 SELECT 쿼리를 만들기
v_sel_sql := 'SELECT * FROM ' || p_table || ' WHERE ROWNUM = 1';
-- 받아온 쿼리를 파싱하기
DBMS_SQL.PARSE( l_theCursor, v_sel_sql, DBMS_SQL.NATIVE );
-- DESCRIBE_COLUMN 프로시저 : 커서에 대한 컬럼 정보를 DBMS_SQL.DESC_TAB형 변수에 넣기
DBMS_SQL.DESCRIBE_COLUMNS ( l_theCursor, l_colCnt, l_descTbl );
-- INSERT문 쿼리를 만들기
v_ins_sql := 'INSERT INTO ' || p_table || ' ( ';
-- 루프를 돌며 컬럼명을 하나씩 읽어와 INSERT 구문을 완성시키기
FOR i IN 1..l_colCnt
LOOP
-- 맨 마지막 컬럼에 오면 끝에 괄호를 붙이기
IF i = l_colCnt THEN
v_ins_sql := v_ins_sql || l_descTbl(i).COL_NAME || ' )';
ELSE -- 루프를 돌며 '컬럼명,' 형태로 만들기
v_ins_sql := v_ins_sql || l_descTbl(i).COL_NAME || ', ';
END IF;
END LOOP;
-- INSERT 문장 출력
DBMS_OUTPUT.PUT_LINE ( v_ins_sql );
-- 커서 닫기
DBMS_SQL.CLOSE_CURSOR (l_theCursor);
END;
결과
PROCEDURE INSERT_DDL이(가) 컴파일되었습니다.
print_table 프로시저의 동작 원리를 완벽히 이해했다면 이 프로시저도 어렵지 않을 것이다. insert_ddl 프로시저는 매개변수로 테이블명을 전달받는데, 이를 토대로 SELECT 구문을 만들어 파싱한 다음, 컬럼 정보를 가져와 컬럼 개수만큼 루프를 돌면서 INSERT 구문을 만들어 내고 있다. 이 프로시저의 독특한 점은 실제 쿼리를 수행하는 것이 아니라 컬럼명만 가져와 INSERT 구문을 만드는 것이 목적이므로 동적 쿼리 문장인 SELECT문을 실행(EXECUTE 함수 호출) 하지 않았다는 점이다. 그럼 insert_ddl 프로시저를 실행해 보자.
입력
EXEC insert_ddl ( 'ch13_physicist');
결과
INSERT INTO ch13_physicist ( IDS, NAMES, BIRTH_DT )
성공적이다. 이번에는 컬럼 수가 좀더 많은 테이블을 선택해 보자.
입력
EXEC insert_ddl ( 'CUSTOMERS');
결과
INSERT INTO CUSTOMERS ( CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH, CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY, CUST_CITY_ID, CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_SRC_ID, CUST_EFF_FROM, CUST_EFF_TO, CUST_VALID )
여러분도 “이런 기능이 있으면 좋겠다”라는 생각이 든다면 DBMS_SQL 패키지를 이용해 다양하고 독특한 기능을 수행하는 함수나 프로시저를 만들어 보길 바란다.