[PLSQL-3] PL/SQL고급

1. 테스트할 테이블 생성 (20만건이 넘는 emp테이블 재생성)

SQL>

drop table emp  PURGE;

CREATE TABLE emp AS
SELECT empno, sal, deptno,
       LPAD(ename, 5, ename) AS ename,
       LPAD(addr, 5, addr) AS addr
FROM (
    SELECT LEVEL + 10000000 AS empno,
           MOD(LEVEL, 1000) + 10000 AS sal,
           CASE WHEN MOD(LEVEL, 4) = 0 THEN 10
                WHEN MOD(LEVEL, 4) = 1 THEN 20
                WHEN MOD(LEVEL, 4) = 2 THEN 30
                WHEN MOD(LEVEL, 4) = 3 THEN 40
           END AS deptno,
           CHR(97 + MOD(LEVEL, 26)) AS ename,
           CHR(65 + MOD(LEVEL, 26)) AS addr
    FROM DUAL
    CONNECT BY LEVEL <= 200000
)
WHERE deptno IN (10, 20, 30, 40); ---부서번호 10,20,30,40

select deptno, count(*)
     from  emp
      group by deptno;

/*
					DEPTNO   COUNT(*)
					---------- ----------
					        20      50000
					        30      50000
					        40      50000
					        10      50000

				데이터들이 5만건씩 균등하게 들어가있음       */

2. for loop 문을 사용해서 update 를 수행하는 프로시져 생성하기

CREATE OR REPLACE PROCEDURE for_update_deptno
IS

///* 두개의 중첩 테이블 변수 만들기 *///

	   TYPE trd_emp IS RECORD (deptno emp.deptno%TYPE, sal emp.sal%TYPE);
1)   TYPE tnt_emp IS TABLE OF trd_emp;
		  v_emp tnt_emp;

			-- v_emp라는 중첩테이블 변수 만든 것
			-- key, deptno, sal이 컬럼으로 들어감
      -- 레코드 타입을 생성하고, 중첩테이블 타입을 생성함 즉, 타입 2번 생성
			-- 값이 비워져있는 테이블변수형태

2)  TYPE tnt_deptno IS TABLE OF emp.deptno%TYPE;
	   v_deptno tnt_deptno := tnt_deptno(10, 20, 30, 40);

		-- emp테이블의 부서번호만 담는 중첩테이블 생성
		-- key와 depno가 컬럼으로 들어감
		-- 1)중첩테이블과는 다르게 할당연산자로 중첩테이블의 값을 할당해줌
		-- 그말은 즉 초기화 해주고 값이 넣어져있는 중첩테이블 변수 만드는


BEGIN
    FOR i IN v_deptno.FIRST .. v_deptno.LAST LOOP
       update /*+ FOR */ emp
       set sal = sal * 1.2
       WHERE deptno = v_deptno(i) --해당 key 의 value를 가져옴
       RETURNING  max(deptno), SUM(sal) BULK COLLECT INTO v_emp;

--부서번호를 v_emp에 담아줘야하는데, max를 쓰지 않으면 부서번호가 여러개가 되어
--MAX를 사용하게 되는 것
--갱신된 상태의 토탈월급으로 V_EMP라는 중첩테이블에 데이터를 넣고 싶은데
--MAX를 사용하지 않으면,20만건의 부서번호가 넣어지게됨.

--SELECT MAX(DEPTNO), SUM(SAL) FROM EMP GOURP BY DEPTNO;의 값을 한번에 V_EMP에
--넣게 되는것 => 부서번호 10번의 MAX는 10, 20번의 MAX는 20이기 때문
				/*  MAX(DEPTNO)   SUM(SAL)
						----------- ----------
						         20  524950000
						         30  525000000
						         40  525050000
							       10  524900000  */
--MAX를 넣어주게 되어여 GROUP BY 한 결과처럼 부서번호가 4개가 되어 매칭시킴
--즉, 각 부서번호에 각 부서의 토탈월급을 V_EMP에 업데이트 해주는 것
      
      FOR j IN 1 .. v_emp.COUNT LOOP
         DBMS_OUTPUT.put_line(v_emp(j).deptno || chr(9) || v_emp(j).sal);
      END LOOP;

   END LOOP;    --이중루프구조로 사용하여 갱신할 때마다 매번 출력이 되게 함
END;            --튜닝 전 sql임.. 수업을 위해 만들다보니 이렇게 된 것
/               --즉 그냥 update문장이 4번 수행된다는 것을 보여주기 위함
								--부서번호 10일 때 전체 결과 출력~마지막 부서버호 40일 때 값을 우리에게 보여주는 

SQL> set timing on

SQL> exec for_update_deptno;
			10      629880000
			20      629940000
			30      630000000
			40      630060000

			PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> 

col sql_text for a20

SELECT sql_id, sql_text, executions, program_line#
  FROM v$sql
 WHERE sql_text LIKE 'UPDATE /*+ FOR */%';

SQL_ID                       SQL_TEXT                   EXECUTIONS  PROGRAM_LINE#
-------------------------- --------------------          ----------     -------------
69jnr6bfugbyw              UPDATE /*+ FOR_ALL *          4            12
                           / EMP SET SAL = SAL
                           * 1.2 WHERE DEPTNO =
                            :B1 RETURNING MAX(D
                           EPTNO), SUM(SAL) INT
                           O :O0 ,:O1

//* 만약 EXCUTIONS 즉 수행이 4번되었다가 아니라 
	UPDATE를 많이 수행해버려서 잘못 나오면 아래와 같이 입력후 다시 하면 됨*/

SQL> drop procedure update_deptno

SQL> alter SYSTEM FLUSH SHARED_POOL;

3. 다시 emp 테이블을 재생성 합니다.

SQL>

drop table emp  PURGE;

CREATE TABLE emp AS
SELECT empno, sal, deptno,
       LPAD(ename, 5, ename) AS ename,
       LPAD(addr, 5, addr) AS addr
FROM (
    SELECT LEVEL + 10000000 AS empno,
           MOD(LEVEL, 1000) + 10000 AS sal,
           CASE WHEN MOD(LEVEL, 4) = 0 THEN 10
                WHEN MOD(LEVEL, 4) = 1 THEN 20
                WHEN MOD(LEVEL, 4) = 2 THEN 30
                WHEN MOD(LEVEL, 4) = 3 THEN 40
           END AS deptno,
           CHR(97 + MOD(LEVEL, 26)) AS ename,
           CHR(65 + MOD(LEVEL, 26)) AS addr
    FROM DUAL
    CONNECT BY LEVEL <= 200000
)
WHERE deptno IN (10, 20, 30, 40);

select deptno, count(*)
     from  emp
      group by deptno;

						DEPTNO   COUNT(*)
						---------- ----------
						        20      50000
						        30      50000
						        40      50000
						        10      50000


4. forall  문을 사용해서 update 하는 프로시져 생성하기

SQL>
CREATE OR REPLACE PROCEDURE forall_update_deptno
IS
     
   type trd_emp  is  record(deptno emp.deptno%type, sal emp.sal%type);
   type tnt_emp  is table of trd_emp;
   v_emp   tnt_emp;

  type  tnt_deptno is  table of  emp.deptno%type;
  v_deptno  tnt_deptno := tnt_deptno( 10, 20, 30, 40 );

BEGIN 

   FORALL i  in   v_deptno.first .. v_deptno.last 
       update /*+ FOR_ALL */ emp
       set sal = sal * 1.2
       WHERE  deptno = v_deptno(i)
       RETURNING  max(deptno), sum(sal) BULK COLLECT INTO  v_emp; 

/* FORALL문은 FOR LOOP문과 다르게 업데이트가 한 번만 수행됨 */
 
   FOR  i IN 1 .. v_emp.COUNT LOOP 
      DBMS_OUTPUT.put_line (v_emp(i).deptno ||chr(9) || v_emp(i).sal ); 
   END LOOP; 

END;
/

SQL> exec forall_update_deptno;

SQL> 

col sql_text for a20

SELECT sql_id, sql_text, executions, program_line#
  FROM v$sql
 WHERE sql_text LIKE 'UPDATE /*+ FOR_ALL */%';

SQL_ID                            SQL_TEXT               EXECUTIONS PROGRAM_LINE#
--------------------------      --------------------      ---------- -------------
69jnr6bfugbyw              UPDATE /*+ FOR_ALL *          1            12
                           / EMP SET SAL = SAL
                           * 1.2 WHERE DEPTNO =
                            :B1 RETURNING MAX(D
                           EPTNO), SUM(SAL) INT
                           O :O0 ,:O1


UNDO도 많이 차지하기 때문에 경과시간이 크게 차이나지는 않음