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}  ｅxpression] 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;