개발 참고자료

DECLARE
ls_Date VARCHAR2(500);
ln_cucode Number;
dw_Data SYS_REFCURSOR;

GetRow NUMBER;
ls_his_custno VARCHAR2(500);
ls_cusdname VARCHAR2(500);
ls_cuname VARCHAR2(500);
ls_telno VARCHAR2(500);
ls_telno1 VARCHAR2(500);
ls_mcreate VARCHAR2(500);

ls_Title VARCHAR2(2000);

l_colCnt NUMBER;
l_descTbl DBMS_SQL.DESC_TAB;

v_cursor_number NUMBER;
v_desc_tab DBMS_SQL.DESC_TAB;
v_column_count NUMBER;

v_temp_name VARCHAR2(30);
v_temp_object_id VARCHAR2(64);
v_temp_timestamp TIMESTAMP;

BEGIN
GetRow := 0;
DBMS_OUTPUT.ENABLE;
ls_Date := '20170101';
ln_cucode := 0 ;
SPDW_SAMPLE ( ls_Date, ln_cucode, dw_Data);
DBMS_OUTPUT.PUT_LINE('--------------------------------------------');

IF (dw_Data IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('-- 성공 --');
v_cursor_number := DBMS_SQL.TO_CURSOR_NUMBER(dw_Data);
DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_number, v_column_count, v_desc_tab);

WHILE (DBMS_SQL.FETCH_ROWS(v_cursor_number) > 0)
LOOP

GetRow := GetRow + 1;
DBMS_OUTPUT.PUT(to_char(GetRow));
DBMS_OUTPUT.PUT_LINE('+++++++++++++++++++');

FOR i IN 1..v_column_count
LOOP

v_temp_name := v_desc_tab(i).col_name;
DBMS_OUTPUT.PUT_LINE(to_char(i) || '=='||v_temp_name);

IF (v_temp_name like '%') THEN
DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, v_temp_object_id);
DBMS_OUTPUT.PUT(' ' || v_temp_name || '=' || v_temp_object_id);
ELSIF (v_temp_name = 'TRANSACTION_TIME') THEN
/* 날짜 타입 */
DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, v_temp_timestamp);
DBMS_OUTPUT.PUT(' ' || v_temp_name || '=' || NVL(TO_CHAR(v_temp_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
END IF;

END LOOP;
DBMS_OUTPUT.PUT_LINE('');

EXIT WHEN GetRow = 3;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor_number);
END IF;

/* 2222222222 S */
/* 타이틀이름 가져오기... */
-- v_cursor_number := DBMS_SQL.TO_CURSOR_NUMBER(dw_Data);
-- DBMS_SQL.DESCRIBE_COLUMNS
-- ( c => DBMS_SQL.TO_CURSOR_NUMBER(dw_Data),
-- col_cnt => l_colCnt,
-- desc_t => l_descTbl );
--
-- for i in 1 .. l_colCnt
-- loop
-- ls_Title := ls_Title || l_descTbl(i).col_name || ' | ';
-- --DBMS_OUTPUT.PUT_LINE( l_descTbl(i).col_name );
-- end loop;
-- DBMS_OUTPUT.ENABLE;
-- DBMS_OUTPUT.PUT_LINE( ls_Title );
/* 2222222222 E */

/* 111111111111 S */
-- IF (dw_Data IS NOT NULL)
-- THEN
-- DBMS_OUTPUT.PUT_LINE( v_cursor_number );
-- DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_number, v_column_count, v_desc_tab);
--
--
-- DBMS_OUTPUT.PUT_LINE(CHR(10));
-- DBMS_OUTPUT.PUT_LINE(CHR(10));
-- DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
-- LOOP
-- FETCH dw_Data INTO ln_cucode, ls_his_custno, ls_cusdname, ls_cuname, ls_telno, ls_telno1, ls_mcreate;
-- EXIT WHEN dw_Data%NOTFOUND;
--
-- DBMS_OUTPUT.PUT_LINE( to_char(dw_Data%ROWCOUNT) ||' | '|| ln_cucode ||' | '|| ls_his_custno ||' | '|| nvl(ls_cusdname, ls_cuname));
--
-- EXIT WHEN to_char(dw_Data%ROWCOUNT) = 3;
--
-- END LOOP;
--
-- DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
-- CLOSE dw_Data;
-- END IF;
/* 111111111111 E */
END;