오라클 DBMS 정리 12

DECLARE
    CURSOR 커서명 IS
     SELECT ... ;
...
BEGIN
...
    FOR 레코드변수명 IN 커서명 
    LOOP
        레코드변수명.컬럼명으로 사용
    END LOOP;
...
END;
/

-- 사원번호, 사원명, 입사일, 연봉을 조회
-- 입사일은 년-월-일 분기의 형태
SET serveroutput ON
 
DECLARE
    -- 1.선언
    CURSOR cur_emp IS
     SELECT empno, ename,
            TO_CHAR(hiredate,'yyyy-mm-dd q') hiredate, sal
     FROM   emp;
BEGIN
 
    -- FOR가 시작되며 커서 열리고 자동으로 FETCH, CLOSE까지 수행                     
 
    -- 2. FOR를 사용한 인출
    -- (FETCH를 사용하지 않는다.)
    FOR emp_data IN cur_emp LOOP
        DBMS_OUTPUT.PUT_LINE(emp_data.empno||' '
                             ||emp_data.ename||' '
                             ||emp_data.hiredate||' '
                             ||emp_data.sal);    
    END LOOP;
    -- LOOP가 끝나면서 커서 닫힘
 
END;
/

BEGIN
...
    FOR 레코드변수명 IN (SELECT ... ) LOOP
 
        레코드변수명 : 조회된 결과를 얻을 수 있다
 
    END LOOP;
...
END;

-- 부서 번호를 입력받아 해당 부서에 따른
-- 사원번호, 사원명, 입사일, 연봉을 조회
-- 입사일은 년-월-일 분기의 형태   
-- 부서번호가 10,20,30이 아니라면
-- 10번 부서로 조회하여 출력할 것
 
SET serveroutput ON
SET verify OFF
 
ACCEPT deptno PROMPT '부서번호 : '
 
DECLARE         
-- 입력값에 대한 IF문을 사용한 제약설정(값 검증)은
-- DECLARE-BEGIN사이에서 할 수 없다
    i_deptno NUMBER(2) := &deptno;
BEGIN                     
 
    IF &deptno NOT IN (10, 20, 30) THEN
      i_deptno := 10;
    END IF;
    
    FOR emp_data IN 
      (SELECT empno, ename,
          TO_CHAR(hiredate,'yyyy-mm-dd q') hiredate, sal
      FROM   emp
      WHERE  deptno = i_deptno)
    LOOP                                 
        DBMS_OUTPUT.PUT_LINE(emp_data.empno||' '
             ||emp_data.ename||' '||emp_data.hiredate||' '
             ||emp_data.sal);    
    END LOOP;            
    
END;
/     

DECLARE
    커서명 SYS_REFCURSOR;
BEGIN
    OPEN 커서명 FOR SELECT쿼리.. ;
...
    LOOP
        FETCH 커서명 INTO 변수명, ...
    END LOOP;
 
    CLOSE 커서명;
END;

-- 사원테이블에서 매니저번호를 입력받아 매너지가 관리하는
-- 사원의 사원번호, 사원명, 입사일, 부서번호, 연봉을 조회
-- 입력된 매니저번호가 emp테이블에 존재하는 매니저 번호일때만
-- 검색을 수행한다. (7839, 7782, 7698, 7902, 7566, 7788)
 
SET serveroutput ON
SET verify OFF
 
    ACCEPT mgr PROMPT '매니저번호 : '
 
DECLARE
    i_mgr NUMBER;
    
    -- 1. SYS_REFCURSOR 선언 : 커서명 SYS_REFCURSOR;
    -- 데이터형처럼 선언
    cur_mgr SYS_REFCURSOR;
    
    -- 조회결과를 저장할 레코드형 선언
    TYPE rec_emp IS RECORD (
      empno emp.empno%TYPE,
      ename emp.ename%TYPE,
      hiredate emp.hiredate%TYPE,
      deptno emp.deptno%TYPE,
      sal emp.sal%TYPE
    );    
    
    emp_rec rec_emp;
BEGIN
    -- 입력된 매니저 번호가 존재하는지 검색
    -- 커서 돌리면 받아오는 값은 레코드 변수!
    -- '.'를 사용해서 컬럼명을 참조한다.**
    FOR temp_rec IN (SELECT DISTINCT mgr
                     FROM emp
                     WHERE mgr = &mgr)
    LOOP
        i_mgr := temp_rec.mgr;
    END LOOP;
 
 
    IF i_mgr IS NOT NULL THEN
        
        -- 2. 커서 열기 : 조회 쿼리문 작성
        OPEN cur_mgr FOR SELECT empno, ename, hiredate, 
                                deptno, sal
                         FROM emp
                         WHERE mgr = i_mgr;
       
 
        LOOP
            -- 3. 인출
            FETCH cur_mgr INTO emp_rec;        
            EXIT WHEN cur_mgr%NOTFOUND;
            
            DBMS_OUTPUT.PUT_LINE(emp_rec.empno||' '
                ||emp_rec.ename||' '
                ||TO_CHAR(emp_rec.hiredate,'yyyy-mm-dd')||' '
                ||emp_rec.deptno||' '||emp_rec.sal);
        END LOOP;
        
        IF cur_mgr%ISOPEN THEN
            -- 4. 닫기
            CLOSE cur_mgr;
            DBMS_OUTPUT.PUT_LINE('커서를 닫습니다..');
        END IF;  
              
    ELSE
        DBMS_OUTPUT.PUT_LINe('&mgr 은 없는 매니저번호입니다.');
  END IF;
END;
/

...
    ...
    EXCEPTION
    WHEN 예외처리객체명 THEN
        예외가 발생 시 동작할 코드;
    ...    
END;
/

-- 아이디, 이름, 비밀번호를 입력받아 table_primary
-- 테이블에 레코드를 삽입하는 PL/SQL 작성
SET serveroutput ON
SET verify OFF
    ACCEPT name PROMPT '이름 : '
    ACCEPT id PROMPT '아이디 : '
    ACCEPT pw PROMPT '비밀번호 : '
DECLARE
    name table_primary.name%TYPE;
    id table_primary.id%TYPE := '&id';
    pw table_primary.pw%TYPE := '&pw';
BEGIN
    -- 문자열을 입력받아 입력받은 문자열의 길이가
    -- 저장할 수 있는 크기가 아니라면 예외로는 처리할 수 없고
    -- 코드로 처리해야 됨
    IF LENGTH('&name') > 10 THEN
        DBMS_OUTPUT.PUT_LINE('입력가능한 글자수를 초과하였습니다');  
    ELSE
        name := '&name';
 
        INSERT INTO table_primary(name, id, pw)
        VALUES(name, id, pw);
    
        DBMS_OUTPUT.PUT_LINE('추가 성공했습니다.');
        COMMIT;
    END IF;
 
  EXCEPTION                                          
    -- 무결성  제약조건 위배 시(PK에 중복값 입력 시)
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('입력하신 '||id||'는 이미 사용중입니다.');
        -- 예외처리 객체가 제공하는 메시지 : sqlerrm
        DBMS_OUTPUT.PUT_LINE( sqlerrm );
        -- 예외처리 객체가 제공하는 에러코드 : sqlcode
        DBMS_OUTPUT.PUT_LINE( sqlcode );
END;
/

-- SELECT로 여러 행을 조회하거나 한 행을 조회할 때 예외처리
-- 사원번호를 입력받아 cp_emp3테이블에서 해당 사원의
-- 사원명, 부서번호, 직무, 연봉 조회하는 PL/SQL
 
SET serveroutput ON
SET verify OFF
 
    ACCEPT empno PROMPT '사원번호 : '
 
DECLARE
    i_empno number := &empno;
  
    TYPE rec_emp IS RECORD(
        ename cp_emp3.ename%TYPE,
        deptno cp_emp3.deptno%TYPE,
        job cp_emp3.job%TYPE,
        sal cp_emp3.sal%TYPE
    );
    
    emp_rec rec_emp;
BEGIN             
 
    SELECT ename, deptno, job, sal
    INTO emp_rec
    FROM cp_emp3
    WHERE empno = i_empno;
    
    DBMS_OUTPUT.PUT_LINE(emp_rec.ename||' '
                         ||emp_rec.deptno||' '
                         ||emp_rec.job||' '
                         ||emp_rec.sal);
                                                
    EXCEPTION
        WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE(i_empno||'번 사원이 한 건 이상 존재합니다.');
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE(i_empno||'번 사원이 존재하지 않습니다.'); 
        WHEN OTHERS THEN
        -- OTHERS에서 모든 예외를 다 처리할 수 있다.
        -- 최상위 예외이기 때문에 EXCEPTION에서 가장 먼저 정의하게되면
        -- 하위 예외처리 객체가 예외를 처리하기 전에 OTHERS가 처리
            DBMS_OUTPUT.PUT_LINE('인식하지 못한 예외');                                            
END;
/     

...
    EXCEPTION
        WHEN OTHERS THEN                                     
        -- OTHERS안에서 비교구문으로 
        -- sqlcode를 이용해서 에러처리도 가능!               
            IF sqlcode = 100 THEN
                DBMS_OUTPUT.PUT_LINE('사원정보가 조회되지 않습니다..');
            END IF;
        
            IF sqlcode = -1422 THEN
                DBMS_OUTPUT.PUT_LINE('사원번호로 여러명의 사원이 조회됩니다.');
            END IF;
        
...

RAISE_APPLICATION_ERROR(에러코드, '제공할 메시지');

    EXCEPTION
-- 예외가 발생했을 때 ORACLE에서 제공하는 형식의 예외메시지를 출력하고
-- 싶을 때 사용자 정의 예외처리를 한다.
-- RAISE_APPLICATION_ERROR(에러코드, '제공할 메세지');
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001, '사용자 정의 예외');
END; 

CREATE OR REPLACE FUNCTION 함수명(매개변수 ...)
RETURN 반환형
IS
    변수 선언
BEGIN
    코드 작성
    RETURN 결과;
END;
/

SQL> @경로\파일명.sql

-- SELECT에서 함수 사용
SELECT 함수명(값)
FROM   테이블명;
 
-- INSERT에서 함수 사용
INSERT 테이블명(컬럼명)
VALUES (함수명(값),..)