오라클,PLSQL,DBMS_SQL,컬럼값을 가로행으로 출력,Unpivot, DESCRIBE_COLUMNS, DEFINE_COLUMN, COLUMN_VALUE, 자바학원, 오라클학원, SQL학원, 닷넷학원

 오라클,PLSQL,DBMS_SQL,컬럼값을 가로행으로 출력,Unpivot, DESCRIBE_COLUMNS, DEFINE_COLUMN, COLUMN_VALUE, 자바학원, 오라클학원, SQL학원, 닷넷학원

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=731 

오라클,PLSQL,DBMS_SQL.DESCRIBE_COLUMNS, 컬럼정보출력, DESC_TAB, DESC_REC, 오라클교육, SQL교육, 자바교육, 오

오라클,PLSQL,DBMS_SQL.DESCRIBE_COLUMNS, 컬럼정보출력, DESC_TAB, DESC_REC, 오라클교육, SQL교육, 자바교육, 오라클학원, SQL학원, 자바학원오라클 PL/SQLDBMS_SQL.DESCRIBE_COLUMNS(컬럼정보 출력)실습DBMS_SQL.DESCRIBE_COLUM

ojc.asia

https://www.youtube.com/watch?v=6Dj4un2o9bQ&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=44 

컬럼값을 가로행으로 출력

(Unpivot)

https://www.youtube.com/watch?v=0CYrvAWrBjk&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=43

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

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 등의 속성이 있다.

https://www.youtube.com/watch?v=oR_UKxk_HO4&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=39 

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) > 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; EXEC 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 --------------------------------------------

https://www.youtube.com/watch?v=XJOkRPZYSYo&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=42
#오라클,#PLSQL,#DBMS_SQL,#Unpivot,#DESCRIBE_COLUMNS,#DEFINE_COLUMN, #COLUMN_VALUE, #ORACLE교육, #ORACLE학원, #SQL교육, #SQL학원, #JAVA교육, #JAVA학원, 오라클,PLSQL,DBMS_SQL,Unpivot,DESCRIBE_COLUMNS,DEFINE_COLUMN, COLUMN_VALUE, ORACLE교육, ORACLE학원, SQL교육, SQL학원, JAVA교육, JAVA학원

https://www.youtube.com/watch?v=XbRQfXxbyng&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=41 

https://www.youtube.com/watch?v=tO4rkJAC7FM&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=40 

오라클,PL/SQL,DBMS_SQL,동적SQL실습 테이블 생성, 멀티행 입력,BIND_ARRAY, EXECUTE, 오라클교육, 오라클학원, 오라클동영상,자바교육,자바학원,SQL교육, SQL학원

 오라클,PL/SQL,DBMS_SQL,동적SQL실습  테이블 생성, 멀티행 입력,BIND_ARRAY, EXECUTE, 오라클교육, 오라클학원, 오라클동영상,자바교육,자바학원,SQL교육, SQL학원

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=730 

오라클,PL/SQL,DBMS_SQL,동적SQL실습 테이블 생성, 멀티행 입력,BIND_ARRAY, EXECUTE, 오라클교육, 오라클학

오라클,PL/SQL,DBMS_SQL,동적SQL실습 테이블 생성, 멀티행 입력,BIND_ARRAY, EXECUTE, 오라클교육, 오라클학원, 오라클동영상,자바교육,자바학원,SQL교육, SQL학원오라클 PL/SQLDBMS_SQL동적SQL실습테이블 생성,

ojc.asia

https://www.youtube.com/watch?v=XbRQfXxbyng&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=42 

https://www.youtube.com/watch?v=XbRQfXxbyng&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=42 

https://www.youtube.com/watch?v=tO4rkJAC7FM&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=40 

DBMS_SQL

동적SQL실습

테이블 생성, 멀티행 입력

실습

DBMS_SQL을 이용하여 DDL문(CREATE, DROP)을 실행하고 생성된 테이블에 5개의 행을 오라클의 TABLE TYPE(배열)에 데이터를 넣은 후 멀티 행을 바인딩 하여 INSERT를 한번에 실행(EXECUTE)하는 예제를 실습하면서 DBMS_SQL 패키지 사용법에 대해 이해합니다.

https://www.youtube.com/watch?v=oR_UKxk_HO4&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=39

[실습1 : DBMS_SQL 패키지를 이용하여 테이블 생성 및 멀티행 바인딩 후 한번에 입력 실습]

CREATE OR REPLACE PROCEDURE CREATE_INSERT_TEST

AS

    V_CURSOR        NUMBER := DBMS_SQL.OPEN_CURSOR();

    V_CNT           NUMBER;    

    V_ID_ARR        DBMS_SQL.NUMBER_TABLE;

    V_NAME_ARR      DBMS_SQL.VARCHAR2_TABLE;    

    V_SQL           VARCHAR2(200);

    V_AFFECTED_ROWS NUMBER;

BEGIN    

    -- TEST 테이블이 생성되어 있는지 딕셔너리 뷰에서 확인

    SELECT COUNT(*) INTO V_CNT FROM USER_TABLES WHERE TABLE_NAME = 'TEST';

    -- 이미 생성되어 있다면 삭제 후 생성

    IF V_CNT > 0 THEN

       DBMS_SQL.PARSE(V_CURSOR, 'drop table test', DBMS_SQL.NATIVE);

       DBMS_SQL.PARSE(V_CURSOR, 'create table test ( id number, name varchar2(20)) ', DBMS_SQL.NATIVE);

       

       DBMS_OUTPUT.PUT_LINE('##### 삭제 후 생성');

    -- 생성되어 있지 않은 경우 그냥 생성

    ELSE

       DBMS_SQL.PARSE(V_CURSOR, 'create table test ( id number, name varchar2(20)) ', DBMS_SQL.NATIVE);

       DBMS_OUTPUT.PUT_LINE('##### 생성');

    END IF;   

    

    -- 5건 데이터 입력 위해 배열에 데이터 생성

    FOR i IN 1..5 LOOP

        V_ID_ARR(i) := i;

        V_NAME_ARR(i) := i || '길동';

    END LOOP;

    

    -- SQL문장을 변수에 담기

    V_SQL := 'insert into test (id, name) values (:id, :name)';

    -- SQL구문 파싱

    DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);

    -- 바인드 변수 연결, 한건씩 연결시 BIND_VARIABLE 사용

    DBMS_SQL.BIND_ARRAY(V_CURSOR, 'id', V_ID_ARR);

    DBMS_SQL.BIND_ARRAY(V_CURSOR, 'name', V_NAME_ARR);

    -- SQL실행, 한번에 5건이 입력됨

    V_AFFECTED_ROWS := DBMS_SQL.EXECUTE(V_CURSOR);

    -- 입력건수 츨력

    DBMS_OUTPUT.PUT_LINE('INSERT 건수 : ' || V_AFFECTED_ROWS);

    

    DBMS_SQL.CLOSE_CURSOR(V_CURSOR);

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLERRM);

    IF DBMS_SQL.IS_OPEN(V_CURSOR) THEN

       DBMS_SQL.CLOSE_CURSOR(V_CURSOR);

    END IF;

END;

exec CREATE_INSERT_TEST;

select * from test;

#오라클,#PLSQL,#DBMS_SQL,#동적SQL,#BIND_ARRAY, #오라클교육, #오라클학원, #오라클동영상,#자바교육,#자바학원,#SQL교육, #SQL학원,오라클,PLSQL,DBMS_SQL,동적SQL,BIND_ARRAY, 오라클교육, 오라클학원, 오라클동영상,자바교육,자바학원,SQL교육, SQL학원

오라클PLSQL DBMS_SQL 참조커서(REFCURSOR) 실습, 동적SQL,TO_REFCURSOR,오라클교육,SQL교육,JAVA교육,오라클학원,SQL학원,SQL동영상

 오라클PLSQL DBMS_SQL 참조커서(REFCURSOR) 실습, 동적SQL,TO_REFCURSOR,오라클교육,SQL교육,JAVA교육,오라클학원,SQL학원,SQL동영상

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=729 

PLSQL DBMS_SQL 참조커서(REFCURSOR) 실습, 동적SQL,TO_REFCURSOR,오라클교육,SQL교육,JAVA교육,오라클학원,SQL

PLSQL DBMS_SQL 참조커서(REFCURSOR) 실습, 동적SQL,TO_REFCURSOR,오라클교육,SQL교육,JAVA교육,오라클학원,SQL학원,SQL동영상오라클 PL/SQLDBMS_SQL일반커서를 참조커서로 변경리턴하기실습DBMS_SQL 패키지를 이용한

ojc.asia

https://www.youtube.com/watch?v=XJOkRPZYSYo&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=41 

https://www.youtube.com/watch?v=tO4rkJAC7FM&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=40 

https://www.youtube.com/watch?v=oR_UKxk_HO4&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=39 

https://www.youtube.com/watch?v=e9lZ0oZNZDo&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=38

https://www.youtube.com/watch?v=fYfTQruthLA&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=36 

DBMS_SQL 패키지를 이용한 참조커서(REF CURSOR)

• 기본적으로 DBMS_SQL 패키지는 동적 SQL을 사용하여 DML(데이터 조작 언어) 또는 DDL(데이터 정의 언어) 문을 구문 분석, 실행, 데이터 조작할 수 있는 인터페이스를 제공한다.

• DBMS_SQL 패키지의 PARSE 프로시저를 사용하여 SQL 구문을 파싱하며, DDL의 경우 별도의 EXECUTE 프로시저를 호출하지 않아도 된다.

• ORACLE 11g이후 DBMS_SQL의 TO_REFCURSOR를 이용하여 일반 커서를 참조커서(REF CURSOR)로  변경할 수 있다.

• 일반 커서는 정적이며 커서가 한번 SQL 영역을 가리키면 가리키는 곳을 변경 못한다. 하지만  참조커서(REF CURSOR)는 가능하다.

• 일반 커서가 가리키는 데이터는 함수나 프로시저를 호출한 곳으로 리턴 할 수 없지만 참조커서(REF CURSOR)가 가리키는 데이터는 리턴하는 것이 가능하다.

[실습 : DBMS_SQL로 일반커서를 참조커서로 변환하고 리턴]

-- 이번에는 REF CURSOR를 리턴해 보자.

-- 아래처럼 함수의 리턴타입을 SYS_REFCURSOR로 하고 v_rc 변수의 타입도 SYS_REFCURSOR로 주면 된다. 

CREATE OR REPLACE FUNCTION emplist (p_deptno emp.deptno%TYPE)

        RETURN SYS_REFCURSOR

    IS

           v_sql     CLOB;

           v_cursor  PLS_INTEGER;

           v_rc      SYS_REFCURSOR;  --REF CURSOR형

           v_execute PLS_INTEGER;

       BEGIN

          v_cursor := DBMS_SQL.OPEN_CURSOR;

          v_sql :='SELECT * FROM emp WHERE deptno = :d';

 

         -- Dynamic SQL문 파싱

         DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

 

         -- 바인드변수 바인딩

         DBMS_SQL.BIND_VARIABLE(v_cursor, 'd', p_deptno);

 

         -- SQL문 실행

         v_execute := DBMS_SQL.EXECUTE(v_cursor);

 

         -- REF CURSOR로 변환

         v_rc := DBMS_SQL.TO_REFCURSOR(v_cursor);

 

         return v_rc;

      END;

     /

함수가 생성되었습니다.

set autoprint on

variable refcur refcursor

exec :refcur := emplist(20);

     EMPNO ENAME      JOB          MGR   HIREDATE     SAL      COMM     DEPTNO

     -----------------------------------------------------------------------------------------------------

      7782   CLARK      MANAGER    7839     81/06/09     3404.4               10

      7839   KING       PRESIDENT             81/11/17     6946.8               10

      7934   MILLER     CLERK        7782     82/01/23     2257.5               10

 

#PLSQL, #DBMS_SQL, #참조커서, #REFCURSOR, #동적SQL, #TO_REFCURSOR,#오라클교육,#SQL교육,#JAVA교육,#오라클학원,#SQL학원,#SQL동영상, PLSQL, DBMS_SQL, 참조커서, REFCURSOR, 동적SQL, TO_REFCURSOR,오라클교육,SQL교육,JAVA교육,오라클학원,SQL학원,SQL동영상,