2011년 6월 1일 수요일

Chapter 6 PL SQL(Procedural Language/SQL) - 7

PLT 6.7 매개변수와 CURSOR
CURSOR가 열릴 때 CURSOR로 매개변수 값을 전달하고, CURSOR가 실행될 때 질의에서 그 값이 사용될 수 있습니다. 이것은 각 경우마다 다른 활성 셋(set)을 생성하는 블록에서 여러 번 명시적 CURSOR를 열고 닫을 수 있음을 의미합니다. CURSOR선언 시 각각 형식적인(formal) 매개변수는 OPEN문장에서 실제 해당 매개변수를 가져야 합니다. 매개변수 데이터형은 스칼라 변수의 데이터형과 동일하지만 크기는 주지 않습니다. 매개변수 명은 CURSOR의 질의 표현식에서 참조하기 위한 것입니다.
CURSOR  cursor_name  [(parameter_name1  datatype, . . . .)]
IS
select_statement;

cursor_name : 앞에 선언된 CURSOR대한 PL/SQL식별자 입니다.
parameter_name : 매개변수 이름입니다. 매개변수는 아래의 구문을 따름니다.


Cursor_parameter_name  [IN]  datatype  [{ := | DEFAULT}  expression]

Datatype : 매개변수의 스칼라 데이터형 입니다.
select_statement : INTO절이 없는 SELECT 문장을 기술합니다.


매개변수 표기법은 더 많은 기능성을 제공하지 않습니다, 단순히 입력 값을 명확하고 쉽게 지정할 수 있도록 해 줍니다. 이것은 동일한 CURSOR가 반복적으로 참조될 때 특히 유용합니다.
DECLARE
                 CURSOR dept_cursor (v_deptno NUMBER) IS
                                  SELECT *
                                                    FROM dept
                                                    WHERE deptno = v_deptno;
BEGIN
                 DBMS_OUTPUT.PUT_LINE('부서번호               ');
                 DBMS_OUTPUT.PUT_LINE('--------  -------------  ------------');
                 FOR dept_record IN dept_cursor(10) LOOP
                                  DBMS_OUTPUT.PUT_LINE(LPAD(dept_record.deptno,2) || '        '
                || RPAD(dept_record.dname,15) || RPAD(dept_record.loc,12));
                 END LOOP;
                 FOR dept_record IN dept_cursor(20) LOOP
                                  DBMS_OUTPUT.PUT_LINE(LPAD(dept_record.deptno,2) || '        '
                                  || RPAD(dept_record.dname,15) || RPAD(dept_record.loc,12));
                 END LOOP;
END;


ACCEPT  p_deptno PROMPT ' 부서번호를 입력하시오 : '
ACCEPT  p_job    PROMPT ' 담당업무를 입력하시오 : '
DECLARE
                 v_sal_total               NUMBER(10,2) := 0;
                 CURSOR emp_cursor(v_deptno emp.deptno%TYPE,
                                                      v_job VARCHAR2) IS
                                  SELECT empno,ename,sal
                                                    FROM emp
                                                    WHERE deptno = v_deptno AND job = v_job
                                                    ORDER BY empno;
BEGIN
                 DBMS_OUTPUT.PUT_LINE('사번                ');
                 DBMS_OUTPUT.PUT_LINE('----  ----------  ----------------');
                 FOR emp_record IN emp_cursor(&p_deptno,UPPER('&p_job')) LOOP
                                  v_sal_total := v_sal_total + emp_record.sal;
                                  DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.empno,6) ||
                                                    RPAD(emp_record.ename,12) ||
                                                    LPAD(TO_CHAR(emp_record.sal,'$99,999,990.00'),16));
                 END LOOP;
                 DBMS_OUTPUT.PUT_LINE('----------------------------------');
                 DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(&p_deptno),2) || ' 부서의   ' ||
                                  LPAD(TO_CHAR(v_sal_total,'$99,999,990.00'),16));
END;


ACCEPT  p_deptno PROMPT ' 부서번호를 입력하시오 : '
ACCEPT  p_job    PROMPT ' 담당업무를 입력하시오 : '
DECLARE
                 TYPE emp_record_type IS RECORD(
                                  v_empno                   emp.empno%TYPE,
                                  v_ename                    emp.ename%TYPE,
                                  v_sal                          emp.sal%TYPE);
                 emp_record              emp_record_type;
                 v_sal_total               NUMBER(10,2) := 0;
                 CURSOR emp_cursor(v_deptno             emp.deptno%TYPE,
                                                      v_job                     VARCHAR2) IS
                                  SELECT empno,ename,sal
                                                    FROM emp
                                                    WHERE deptno = v_deptno AND job = v_job
                                                    ORDER BY empno;
BEGIN
                 DBMS_OUTPUT.PUT_LINE('사번                ');
                 DBMS_OUTPUT.PUT_LINE('----  ----------  ----------------');
                 OPEN emp_cursor(&p_deptno,UPPER('&p_job'));
                 LOOP
                                  FETCH emp_cursor INTO emp_record;
                                  EXIT WHEN emp_cursor%NOTFOUND;
                                  v_sal_total := v_sal_total + emp_record.v_sal;
                                  DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.v_empno,6) ||
                                                    RPAD(emp_record.v_ename,12) ||
                                                    LPAD(TO_CHAR(emp_record.v_sal,'$99,999,990.00'),16));
                 END LOOP;
                 DBMS_OUTPUT.PUT_LINE('----------------------------------');
                 DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(&p_deptno),2) || ' 부서의    ' ||
                                  LPAD(TO_CHAR(v_sal_total,'$99,999,990.00'),16));
                 CLOSE emp_cursor;
END;



FOR UPDATE절
행을 갱신하거나 삭제하기 전에 행을 잠글 수 있습니다. CURSOR가 열릴 때 영향을 미치는 행을 잠그기 위해 CURSOR질의에서 FOR UPDATE절을 추가합니다. ORACLE SERVER은TRANSACTION이 종료할 때 잠금(locking)을 해제하기 때문에 FOR UPDATE가 사용된다면, 명시적 CURSOR에서 인출(fetch)한 후에 바로 COMMIT해서는 안됩니다. FOR UPDATE절은, ORDER BY절이 있다 해도, SELECT문자에서 마지막 절이 됩니다. 다중 테이블을 질의할 때, 특정 테이블에 대해서만 행을 잠그기 위해 FOR UPDATE절을 사용할 수 있습니다. 테이블의 행은 FOR UPDATE절이 그 테이블의 열을 참조할 때만 잠겨집니다. 독점적인(exclusive) 행 잠금(locking)은 FOR UPDATE절이 사용될 때, OPEN하기 전에 결과 셋(set)에 행해집니다.
CURSOR  cursor_name IS
            SELECT . . . . .
                          FOR UPDATE [NOWAIT] [OF column1[,column2,. . . .]];


ORACLE SERVER은 SELECT FOR UPDATE에서 필요로 하는 행의 잠금을 얻을 수 없다면, 막연하게 기다립니다. SELECT FOR UPDATE문장에서 NOWAIT절을 사용할 수 있고 루프에서 잠금을 얻는데 실패하여 생기는 오류 코드를 테스트할 수 있습니다. 그러므로 PL/SQL블록을 종료하기 전에 CURSOR OPEN을 n번 다시 시도할 수 있습니다. 대형 테이블이라면, 테이블의 모든 행을 잠그기 위해 LOCK TABLE문장을 사용함으로써 더 나은 성능을 얻을 수도 있습니다. 그러나LOCK TABLE을 사용할 때, WHERE CURRENT OF절을 사용할 수 없고 WHERE column = identifier를 사용해야 합니다. FOR UPDATE OF절이 열을 참조 하는 것이 필수적 이지는 않지만 이것은 더 쉽게 읽고 유지할 수 있게 하기 위해 추천됩니다.

WHERE CURRENT OF절
명시적 CURSOR에서 현재 참조할 때 WHRE CURRENT OF절을 사용합니다. 이를 통해서 명시적으로 ROWID를 참조하지 않고 현재 처리 중인 행을 갱신하고 삭제할 수 있게 해 줍니다. 행을OPEN시에 잠기게 하기 위해서 CURSOR의 SELECT 문에서 FOR UPDATE절을 포함해야 합니다. CURSOR에서 일정 조건에 따라 행을 갱신 할 수 있고 또한 FETCH문장에 의해 가장 최근에 프로세스된 행을 참조하기 위해 WHERE CURRENT OF cursor_name절이 있는 DELETE 또는UPDATE문장을 쓸 수 있습니다. WHERE CURRENT OF절을 사용할 때 참조되는 CURSOR는CURSOR질의에서 FOR UPDATE절을 포함해야 하고 존재해야 합니다. 그렇지 않으면 에러를 일으키게 됩니다. 이 절은 ROWID pseudocolumn을 명시적으로 참조할 필요 없이 현재 처리된 행에 대해 갱신과 삭제를 할 수 있도록 해 줍니다.
DECLARE
                 . . . . .
                 CURSOR  cursor_name IS
                                   SELECT . . . . .
                                                    FOR UPDATE [NOWAIT] [OF column1[,column2,. . . .]];
BEGIN
                 OPEN cursor_name;
                 LOOP
                                  . . . . .
                                  UPDATE  . . . . . .
                                                    WHERE CURRENT OF cursor_name;
                                  . . . . .
                 END LOOP;
                 COMMIT;
                 CLOSE cursor_name;
END;


DECLARE
                 CURSOR emp_cursor IS
                                  SELECT sal,deptno
                                                    FROM emp
                                                    ORDER BY deptno
                                                    FOR UPDATE OF sal,deptno;
BEGIN
                 FOR emp_record IN emp_cursor LOOP
                                  IF emp_record.deptno = 10 THEN
                                                    UPDATE emp
                                                    SET sal = TRUNC(emp_record.sal * 1.25,-1),          deptno = 20
                                                                      WHERE CURRENT OF emp_cursor;
                                  ELSIF emp_record.deptno = 20 THEN
                                                    UPDATE emp
                                                    SET sal = TRUNC(emp_record.sal * 1.15,-1),          deptno = 30
                                                                      WHERE CURRENT OF emp_cursor;
                                  ELSIF emp_record.deptno = 30 THEN
                                                    UPDATE emp
                                                    SET sal = TRUNC(emp_record.sal * 1.20,-1),          deptno = 10
                                                                      WHERE CURRENT OF emp_cursor;
                                  END IF;
                 END LOOP;
                 COMMIT;
END;



SUBQUERY
SUBQUERY은 다른SQL데이터 조작 문장 속에 있는 질의(일반적으로 괄호로 둘러쌈)입니다. SUBQUERY는 수행되면 값 또는 값의 집합을 RETURN합니다. SUBQUERY은 SELECT문장의WHERE절에서 주로 사용됩니다. 또한 FROM절에서도 사용될 수 있습니다. SUBQUERY 또는 상호 관련 질의(correlated subquery)가 사용됩니다.
DECLARE
                 v_cnt       NUMBER;
                 CURSOR dept_cursor IS
                                  SELECT d.deptno,d.dname
                                                    FROM dept d
                                                    WHERE 5 <= (SELECT count(*)
                                                                      FROM emp
                                                                      WHERE deptno = d.deptno);
BEGIN
                 DBMS_OUTPUT.PUT_LINE('부서번호         인원수');
                 DBMS_OUTPUT.PUT_LINE('--------  -------------  ------');
                 FOR dept_record IN dept_cursor LOOP
                                  SELECT COUNT(*)
                                                    INTO v_cnt
                                                    FROM emp
                                                    WHERE deptno = dept_record.deptno;
                                  DBMS_OUTPUT.PUT_LINE(LPAD(dept_record.deptno,2) || '       ' ||
                                                    RPAD(dept_record.dname,15) || LPAD(v_cnt,4));
                 END LOOP;
END;

댓글 없음:

댓글 쓰기

ETL 솔루션 환경

ETL 솔루션 환경 하둡은 대용량 데이터를 값싸고 빠르게 분석할 수 있는 길을 만들어줬다. 통계분석 엔진인 “R”역시 하둡 못지 않게 관심을 받고 있다. 빅데이터 역시 데이터라는 점을 볼때 분산처리와 분석 그 이전에 데이터 품질 등 데이...