PL/SQL(2)

💡 PL/SQL문 내에서의 SQL문

• 암시적 커서

  SELECT문

  • 형식)

    SELECT select_list
    INTO variable_name | record_name
    FROM table
    WHERE condition;

  • 주의사항

    1. 반드시 하나의 행만 검색

    2. 검색되는 데이터행이 없거나 2개 이상일 경우 예외 발생

       • TOO_MANY_ROWS: 2개 이상의 데이터 행 추출시
       • NO_DATA_FOUND: 어떤 데이터도 추출하지 못할 때

    3. 다수 개의 데이터행을 검색할 때는 명시적 커서 사용

  INSERT문

  • 형식)

    DECLARE
        v_no dept.deptno%TYPE;
        v_name dept.dname%TYPE;
        v_loc dept.loc%TYPE;
    BEGIN
        INSERT INTO dept
        VALUES (50,'SALES','서울');
    
        SELECT deptno, dname,loc
        INTO v_no, v_name, v_loc
        FROM dept WHERE deptno=50;
    
        DBMS_OUTPUT.PUT_LINE('부서번호:'||TO_CHAR(v_no));
        DBMS_OUTPUT.PUT_LINE('부서이름:'||v_name);
        DBMS_OUTPUT.PUT_LINE('위치:'||v_loc);
    END;

  UPDATE문

  • 형식)

    DECLARE
        v_no dept.deptno%TYPE;
        v_name dept.dname%TYPE;
        v_loc dept.loc%TYPE;
    BEGIN
        UPDATE dept SET loc='수원' WHERE deptno=50;
    
        SELECT deptno, dname,loc
        INTO v_no, v_name, v_loc
        FROM dept WHERE deptno=50;
    
        DBMS_OUTPUT.PUT_LINE('부서번호:'||TO_CHAR(v_no));
        DBMS_OUTPUT.PUT_LINE('부서이름:'||v_name);
        DBMS_OUTPUT.PUT_LINE('위치:'||v_loc);
    END;

• DELETE문

  • 형식)

    BEGIN
        DELETE FROM dept
        WHERE deptno=50;
    END;

• 커서

  SQL 처리 결과가 저장된 작업 영역에 이름을 지정하고 저장된 정보를 접근할 수 있게 함

  • SQL명령을 실행시키면 서버는 명령을 파싱하고 실행하기 위한 메모리 영역을 오픈하는데 이 영역을 커서라고 부른다.

  • 커서의 종류

    • 암시적 커서(Implicit Cursor)

      : 모든 DML과 PL/SQL SELECT문에 암시적으로 PL/SQL이 선언

    • 명시적 커서(Explicit Cursor)

      : 프로그래머가 선언하고 명령하며 블럭의 실행 가능한 부분에서 특정 명령을 통해 조작

• 커서 : 암시적 커서

  SQL문장이 처리되는 곳에 대한 익명의 어드레스

  오라클 데이터 베이스에서 실행되는 모든 SQL문장 : 암시적 커서

  SQL문이 실행되는 순간 자동으로 열림과 닫힘 실행

  속성)

  속성	설명
  SQL%ROWCOUNT	해당 SQL문에 영향을 받는 행의 수
  SQL%FOUND	해당 SQL문의 영향을 받는 행의 수가 1개 이상일 경우 TRUE
  SQL%NOTFOUND	해당 SQL문에 영향을 받는 행의 수가 없을 경우 TRUE
  SQL%ISOPEN	암시적 커서가 열려 있는지의 여부 검색항상 FALSE(실행한 후 바로 커서를 닫기 때문)

  예)

  DECLARE
  	v_sal emp.sal%TYPE;	
  BEGIN
  	SELECT sal
  	INTO v_sal
  	FROM emp
  	WHERE empno = 7934;
  
  	IF SQL%FOUND THENN
  		DBMS_OUTPUT.PUT_LINE('데이터 존재');
  	END IF;
  	
  	UPDATE emp
  	SET sal = sal * 1.1
  	WHERE empno = 7934;
  
  	v_update_row := SQL%ROWCOUNT;
  	DBMS_OUTPUT.PUT_LINE('급여인상 사원수:'||TO_CHAR(v_update_row));
  END; 

• 명시적 커서

  개념

  • DECLARE : 이름이 있는 SQL 영역 생성
  • OPEN : 커서 활성화
  • FETCH : 커서의 현재 데이터 행을 해당 변수에 넘김
  • EMPTY : 현재 데이터 행의 존재 여부 검사, 레코드가 없으면 FETCH하지 않음
  • CLOSE : 커서가 사용한 자원 해제

  커서 선언과 처리

  • 커서 선언

    DECLARE
    	CURSOR cursor_name IS
    		select문;

  • 커서 연결

    OPEN cursor_name;

    :커서 안의 검색이 실행

    :검색시 아무런 데이터 행을 추출하지 못할 경우 예외 발생

  • 커서로부터의 데이터 패치

    FETCH cursor_name INTO variable1, variable2, …;

    :현재 데이터행을 OUTPUT변수에 리턴

    :한 행씩 데이터를 패치

    :주의할 점

    →커서의 SELECT문의 컬럼의 수와 OUTPUT변수의 수가 동일해야 함

    →커서의 SELECT문의 컬럼 데이터 타입과 OUTPUT변수의 데이터 타입이 동일해야함

  • 커서 닫기

    CLOSE cursor_name;

    :사용을 끝낸 커서는 반드시 닫아야함

    :필요하다면 커서를 OPEN을 통해 다시 열 수 있음

    :커서를 닫은 상태에서 패치 할 수 없음

  • 커서 선언과 처리 예)

    DECLARE
    	CURSOR dept_cnt IS
    		SELECT dname, loc
    		FROM dept WHERE deptno = 10;
    	
    	v_dname dept.dname%TYPE;
    	v_dloc dept.loc%TYPE;
    BEGIN
    	OPEN dept_cnt;
    	
    	FETCH dept_cnt INTO v_dname, v_dloc;
    	
    	DBMS_OUTPUT.PUT_LINE('부서명:'||v_dname);
    	DBMS_OUTPUT.PUT_LINE('부서위치:'||v_dloc);
    
    	CLOSE dept_cnt;
    END;

명시적 커서의 속성

• 속성

  속성	설명
  %ROWCOUNT	현재까지 반환된 모든 데이터 행의 수
  %FOUND	Fetch한 데이터가 행을 리턴하면 TRUE
  %NOTFOUND	Fetch한 데이터가 행을 리턴하지 않으면 TRUE
  %ISOPEN	커서가 열려 있으면 TRUE

  →%NOTFOUND : 루프 종료 시점을 검사할 때 사용

  →%ROWCOUNT : 정확한 숫자만큼의 행 추출에 사용

  →%ISOPEN : 커서가 열려있는지 검사할 때 사용

  • 예)

    DECLARE
    	v_empno emp.empno%TYPE;
    	v_ename emp.ename%TYPE;
    	
    	CURSOR emp_list IS
    		SELECT empno, ename
    		FROM emp;
    BEGIN
    	OPEN emp_list;
    
    	LOOP
    		FETCH emp_list INTO v_empno, v_ename;
    		EXIT WHEN emp_list%NOTFOUND;
    		DBMS_OPTPUT.PUT_LINE(v_ename);
    	END LOOP;
    
    	DBMS_OUTPUT.PUT_LINE
    		('전체데이터 수'||TO_CHAR(emp_list%ROWCOUNT));
    
    	CLOSE emp_list;
    END;

FOR문에서 커서 사용

• 특징

  : FOR문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생하므로 따로 기술할 필요 없음

  : 레코드 이름이 자동 선언되므로 따로 선언할 필요 없음
  

• 형식

  FOR record_name IN cursor_name LOOP
  	statement;
  	.....
  END LOOP

• 예시

  DECLARE
  	CURSOR dept_cnt IS
  		SELECT b.dname, COUNT(a.empno) cnt
  		FROM emp a, dept b
  		WHERE a.deptno = b.deptno
  		GROUP BY b.dname;
  BEGIN
  	FOR emp_list IN dept_cnt LOOP
  		DBMS_OUTPUT.PUT_LINE('부서명:'||emp_list.dname);
  		DBMS_OUTPUT.PUT_LINE('사원수:'||TO_CHAR(emp_list.cnt));
  	END LOOP;
  END;

파라미터가 있는 커서

• 특징 : 커서가 열리고 질의가 실행되면 파라미터 값을 커서에 전달
• 형식)

  CURSOR cursor_name [(parameter_name datatype, …)]
  IS
  	SELECT statement

• 예)

  DECLARE
  	CURSOR emp_list(v_deptno emp.deptno%TYPE) IS
  		SELECT ename
  		FROM emp
  		WHERE deptno = v_deptno;
  BEGIN
  	DBMS_OUTPUT.PUT_LINE('**입력한 부서 직원**');
  
  	--패러미터 변수의 값을 전달(OPEN될 때 값 전달)
  	FOR emplst IN emp_list(10) LOOP
  		DBMS_OUTPUT.PUT_LINE('부서번호:'||emplst.ename);
  	END LOOP;
  
  	FOR emplst IN emp_list(20) LOOP
  				DBMS_OUTPUT.PUT_LINE('부서번호 20:'||emplst.ename);
  	END LOOP;
  END;

• 예외 처리 예외 종류

  예외	설명	처리
  미리 정의된 오라클 서버 예외	PL/SQL에서 자주 발생하는 약 20개의 오류	선언할 필요도 없고, 발생시에 예외 절로 자동트랩
  미리 정의되지 않은 오라클 서버 예외	미리 정의된 오라클 서버 오류를 제외한 모든 오류	선언부에서 선언해야하고 발생시 자동트랩
  사용자 정의 예외	개발자가 정한 조건에 만족하지 않을 경우 발생하는 오류	선언부에서 선언하고 실행부에서 RAISE문을 사용하여 발생

  예외 처리 형식)

  EXCEPTION
  	WHEN exception1 [OR exception2 ...] THEN 
  		statement;
  		...
  	[WHEN exception3 [OR exception4 ...] THEN
  		statement;
  	]
  		...

  주의사항

  • WHEN OTHERS절은 맨 마지막에 위치

  • 예외 처리절은 EXCEPTION부터 시작

  • 여러 개의 예외 처리부 허용

  • 예외가 발생하면 여러 개의 예외 처리부 중 하나의 예외 처리부로 트랩(Trap)

    미리 정의된 예외

  • NO_DATA_FOUND: SELET문이 아무런 데이터 행을 반환하지 못할 때

  • TOO_MANY_ROWS: SELECT문이 2개 이상의 행을 반환할 떄

  • ZERO_DIVIDE: 0으로 나눌 때

  • ….

    미리 정의된 예외 예

    DECLARE
    	v_emp emp%ROWTYPE;
    BEGIN
    	SELECT empno, ename, deptno
    	INTO v_emp.empno, v_emp.ename, v_emp.deptno
    	FROM emp
    	WHERE deptno = 10;
    
    	DBMS_OUTPUT.PUT_LINE('사번:'||v_emp.empno;
    	DBMS_OUTPUT.PUT_LINE('이름:'||v_emp.ename;
    	DBMS_OUTPUT.PUT_LINE('부서번호:'||v_emp.deptno;
    	EXCEPTION
    		WHEN TOO_MANY_ROWS THEN
    			DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS에러 발생');
    		WHEN NO_DATA_FOUND THEN
    			DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND에러 발생');
    		WHEN OTHERS THEN
    			DBMS_OUTPUT.PUT_LINE('기타 에러 발생');
    END;

    미리 정의되지 않은 예외 처리방법

    1단계 : 예외의 이름을 선언(선언절)

    2단계 : PRAGMA EXCEPTION_INIT문장으로 예외의 이름과 오라클 서버 오류 번호를 결합(선언절)

    → PRAGMA : 컴파일러에게 직접 명령을 내리는 지시어

    → PRAGMA EXCEPTION_INIT : 예외 이름이 발생하면 오라클 서버 오류 번호로 컴파일러에게 지시

    3단계 : 예외가 발생할 경우 해당 예외를 참조(예외절)

    미리 정의되지 않은 예외 예)

    DECLARE
    	not_null_test EXCEPTION; --1단계
    
    	PRAGMA EXCEPTION_INIT(not_null_test, -1400); --2단계
    
    BEGIN
    	INSERT INTO emp(ename,deptno)
    	VALUES('tiger',30);
    
    	EXCEPTION
    		WHEN not_null_test THEN --3단계
    			DBMS_OUTPUT.PUT_LINE('not null 에러 발생');
    END;

    결과 : not null에러발생

    사용자 정의 예외 : 오라클 저장함수 RAISE_APPLICATION_ERROR를 사용하여 오류코드 -20000부터 -20999의 범위 내에서 사용자 정의 예외 생성 가능

    사용자 정의 예외 처리방법

    1단계 : 예외의 이름을 선언(선언절)

    2단계 : RAISE문을 사용하여 직접적으로 예외 발생(실행절)

    3단계 : 예외가 발생할 경우 해당 예외를 참조(예외절)

    사용자 정의 예외 예)

    DECLARE
    	-- 예외 이름 선언
    	user_define_error EXCEPTION;  --1단계
    	cnt NUMBER;
    BEGIN
    	SELECT COUNT(empno)
    	INTO cnt
    	FROM emp
    	WHERE deptno = 10;
    
    	IF cnt < 5 THEN
    		--RAISE문을 사용하여 직접적으로 예외 발생
    			RAISE user_define_error; --2단계
    	END IF;
    
    	EXCEPTION
    		--예외가 발생할 경우 해당 예외를 참조
    		WHEN user_definn_error THEN --3단계
    			RAISE_APPLICATION_ERROR(-20001,'사원 부족');
    END;

    SQLCODE, SQLERRM

  • WHEN OTHERS문으로 트랩되는 오류들의 실제 오류 코드와 설명을 볼 때 사용

  • SQLCODE : 실행된 프로그램이 성공적으로 종료하였을 때는 오류번호 0을 포함하며, 그렇지 못할 경우에는 해당 오류코드 번호 포함

  • SQLERRM : SQLCODE에 포함된 오라클 오류 번호에 해당하는 메시지를 가짐

    SQLCODE값	설명
    0	오류 없이 성공적으로 종료
    1	사용자 정의 예외 번호
    +100	NO_DATA_FOUND 예외 번호
    음수	위의 것을 제외한 오라클 서버 에러 번호