SQL - SELECT, INSERT, UPDATE 사용

SELECT문의 사용

기본 사용법

DECLARE
	v_cnt NUMBER;
BEGIN
	SELECT COUNT(*)
    	INTO v_cnt
   		FROM emp;
    DBMS_OUTPUT_PUT_LINE('COUNT(*) = ' || v_cnt);
END;
    

SELECT문의 특징은 쿼리를 실행하여 결과값을 가져오는 것이다.

SELECT문이 실행되어 COUNT(*)의 결과가 계산되면 그 결과는 INTO 다음에 나타나는 PL/SQL 출력 변수인 v_cnt에 저장된다. SELECT절에 여러 개의 칼럼이 조회되는 경우는 밑의 코드와 같이 칼럼목록을 콤마로 분리하여 나열한다.

SELECT되는 칼럼의 수와 동일한 개수의 출력 변수가 INTO절에 나열되는 것에 주의하라.

DECLARE
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_deptno emp.deptno%TYPE;
    v_job   emp.job%TYPE;
BEGIN
    SELECT EMPNO, ENAME, DEPTNO, JOB
    INTO v_empno, v_ename, v_deptno, v_job  --출력 변수의 개수는 SELECT되는 칼럼의 수와 같다.
    FROM emp
    WHERE empno = 7788;
END;

배열 처리 시에는 INTO 앞에 BULK COLLECT를 추가하여 BULK COLLECT INTO 형태로 사용한다. 

 

PL/SQL 입력 변수의 사용

INTO절에는 SELECT문의 결과를 저장하는 출력변수만 사용할 수 있다.

많은 경우에 SQL문의 실행 시 동적인 값을 입력으로 제공하기 위한 입력변수를 사용할 필요도 생긴다.

또한 SELECT절의 칼럼에도 변수가 표현식으로 사용될 수 있다.

-- SELECT문에서 PL/SQL 입력변수의 사용
DECLARE
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_rate number := 1.1;
    v_sal number;
BEGIN
    v_empno := 7788;
    SELECT ename
        , (sal+comm)*v_rate -- SELECT 칼럼에 입력변수 v_rate 사용
        INTO v_ename, v_sal -- 출력변수
        FROM emp
    WHERE empno = v_empno;  -- 리터럴을 입력변수 v_empno로 대체
END;

%ROWTYPE을 사용하면 변수선언과 INTO절을 훨씬 간단하게 할 수 있다.

-- SELECT문에서 %ROWTYPE의 사용
DECLARE
    v_emprec emp%ROWTYPE;   -- 레코드 변수 선언
BEGIN    
    v_emprec.empno := 7788;
    SELECT *
        INTO v_emprec   -- 레코드 변수 사용
        FROM emp
    WHERE empno = v_emprec.empno;
    DBMS.OUTPUT_PUT_LINE('이름 : ' ||v_emprec.ename);
    DBMS.OUTPUT_PUT_LINE('부서번호 : ' ||v_emprec.deptno);
END;
	

SELECT문의 결과는 항상 INTO절에 지정된 변수에 저장된다.

SELECT절에 변수를 지정하면 SELECT 결과가 그 변수에 저장되는 것이 아니라 변수가 가진 값이 INTO절에 지정된 변수로 반환된다.

--SELECT절에 변수를 사용하면 결과를 변수에 저장하는 게 아니라 변수의 값을 반환한다
SCOTT> SELECT ename
		FROM emp
       WHERE empno = 7788;
       
ENAME
-------------
SCOTT

SCOTT> DECLARE
		v_name	emp.ename%TYPE;
        v_ename emp.ename%TYPE;
      BEGIN
      	v_name := 'TIGER';
        SELECT v_name
        INTO  v_ename
        FROM emp
       WHERE empno = 7788;
       DBMS_OUTPUT_PUT_LINE('이름: '||v_ename);
 	END;;
	/
    

위 코드는 SELECT문에 변수 v_name을 사용하면 사번 7788의 이름인 SCOTT이 변수 v_name에 저장되는 게 아니라 변수 v_name에 저장된 값인 'TIGER'가 INTO절의 변수 v_ename에 저장되는 것을 보여준다.

 

INSERT문의 사용

INSERT문은 지정된 값을 테이블에 삽입하고 결과행을 반환하지 않는 SQL문이다.

-- 가장 간단한 INSERT문
BEGIN
	INSERT INTO emp(empno, ename, hiredate, deptno)
    VALUES			(9000, '홍길동', SYSDATE, 30);
    DBMS_OUTPUT_PUT_LINE('INSERT 건수: '||SQL%ROWCOUNT); --변경된 건수 출력
    COMMIT;
END;

INSERT문에서도 SELECT문과 마찬가지로 PL/SQL 변수를 사용할 수 있다.

--INSERT문에서 PL/SQL 입력변수 사용
REM 앞에서 삽입한 로우 삭제
DELETE FROM emp WHERE empno = 9000;

DECLARE
	v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_deptno emp.deptno%TYPE;
BEGIN
	v_empno := 9000;
    v_ename := '홍길동';
    v_deptno := 30;
    
    INSERT INTO emp(empno, ename, hiredate, deptno)
    VALUES			(v_empno, v_ename, SYSDATE, v_deptno);	-- PL/SQL 변수 사용
    DBMS_OUTPUT_PUT_LINE('INSERT 건수: ' ||SQL%ROWCOUNT);	-- 변경된 건수 출력
    COMMIT;
END;

INSERT문도 %ROWTYPE을 사용하여 레코드변수를 선언하면 프로그램을 훨씬 간단하게 작성할 수 있다.

--INSERT문에서 %ROWTYPE의 사용
REM 앞에서 삽입한 로우 삭제
DELETE FROM emp WHERE empno = 9000;

DECLARE
	v_emprec emp%ROWTYPE;	-- 레코드 변수 선언
BEGIN
	v_emprec.empno		:= 9000;
    v_emprec.ename		:= '홍길동';
    v_emprec.deptno		:= 30;
    v_emprec.hiredate	:= SYSDATE;
    
    INSERT INTO emp
    VALUES		v_emprec;
    DBMS_OUTPUT.PUT_LINE('INSERT 건수: '||SQL%ROWCOUNT); -- 변경된 건수 출력
    COMMIT;
 END;

예제 스키마 계정 scott의 테이블 emp는 모두 여덟 개의 컬럼을 가지고 있기 때문에 위 코드의 레코드 변수 v_emp도 여덟 개의 필드를 가졌다. 위 코드에서는 그 중 네 개의 필드만 값을 지정했다. 값을 지정하지 않은 나머지 네 개의 필드는 값이 NULL이다. 따라서 INSERT할 떼 이에 대응되는 네 개의 칼럼에는 NULL이 저장된다.

 

INSERT문에서 테이블명이나 INSERT 대상으로 나열되는 컬럼명은 변수로 사용할 수 없다.

정적SQL은 컴파일 시에 문법과 의미검사를 수행하는데 테이블명이나 칼럼명에 변수를 허용하면 문법검사와 의미검사 모두 불가능해지므로 이를 허용하는 것은 PL/SQL 언어의 기본 철학에 위배된다.

 

INSERT문에 배열 처리를 사용하기 위해서는 FORALL문을 사용해야한다. FORALL은 다음에 알아본다.

 

UPDATE문 사용

INSERT문과 마찬가지로 지정된 값을 사용하여 테이블의 로우를 변경하고 결과 행을 반환하지 않는 SQL문.

-- 가장 간단한 UPDATE문
BEGIN
	UPDATE emp
    SET	deptno = 40
    WHERE empno = 9000;
    DBMS_OUTPUT_PUT_LINE('UPDATE 건수: '||SQL%ROWCOUNT); -- 변경된 건수 출력
    COMMIT;
END;

UPDATE문도 PL/SQL 변수 사용 가능하다.

DECLARE
	v_empno	emp.empno%TYPE := 9000;
    v_deptno emp.deptno%TYPE := 40;
BEGIN
	UPDATE emp
    SET deptno = v_deptno -- 입력변수
    WHERE empno = v_empno -- 입력변수
    DBMS_OUTPUT_PUT_LINE('UPDATE 건수: ' ||SQL%ROWCOUNT); --변경된 건수 출력
    COMMIT;
END;

--UPDATE문에서 %ROWTYPE 사용
DECLARE
	v_emprec	emp%ROWTYPE;	-- 레코드변수 선언
BEGIN
	v_emprec.empno := 9000;
    
    -- 원래의 값을 조회
    SELECT * 
    INTO v_emprec
    FROM emp
    WHERE empno = v_emprec.empno;
    
    --이름과 부서번호 필드값을 변경
    v_emprec.ename := '홍길동';
    v_emprec.deptno := 40;
    
    --변경된 값을 테이블에 반영
    UPDATE emp
    SET ROW = v_emprec	-- 레코드 변수를 사용한 UPDATE
    WHERE empno = v_emprec.empno;
    COMMIT;
END;

INSERT문에서 설명한 것과 동일한 이유로 레코드 변수읭 특정필드에만 값을 지정하고 UPDATE문을 수행하면 값을 지정하지 않은 필드에 해당하는 컬럼의 값은 NULL이 된다. 이렇게 하면 의도치 않은 컬럼의 값이 NULL로 변경될 수 있으므로 위 프로그램과 같이 먼저 모든 컬럼의 값을 SELECT한 후 특정 컬럼만 변경하는 것이 좋다.

 

UPDATE문에서 레코드변수는 18번 줄과 같이 SET ROW 다음에만 사용할 수 있으며, 레코드변수는 단 한 개만 사용할 수 있다. UPDATE문도 역시 키워드(UPDATE, SET, ROW, WHERE 등)나 테이블명, SET의 대상이 되는 컬럼명에는 변수를 사용할 수 없다. UPDATE문에서 배열 처리를 사용하기 위해서는 INSERT문처럼 FORALL문을 사용해야 한다.