[PL/SQL]PL/SQL 내의 SQL문의 기초

PL/SQL 내의 SQL문의 기초

기본적으로 

PL/SQL에서는 다음과 같은 명령어가 사용가능하다.

BEGIN
 SELECT
 INSERT, UPDATE, DELETE, MERGE
 COMMIT, ROLLBACK, SAVEPOINT
END;
/

다른 구문도 사용 가능하나, 이경우에는 다른 명령어가 추가 되므로, 다음에 알아보도록 하자.

SELECT문

SELECT의 경우 반드시 INTO로 Return을 해야하는 변수가 존재해야 한다.

즉 반드시 INTO절을 가져야 한다.

또한 PL/SQL안의 SELECT문은 반드시 하나의 행만 검색되어야한다.

구문은 다음과 같이 작성한다.

SELECT select_list
INTO {variable_name[, variable_name]...
| record_name}
FROM table
[WHERE condition];

다음의 예시를 참고하자.

DECLARE
  V_ENAME   EMP.ENAME%TYPE;
  V_SAL     EMP.SAL%TYPE;
BEGIN
  SELECT ENAME, SAL INTO V_ENAME, V_SAL
  FROM EMP
  WHERE EMPNO = 7788;
  
  DBMS_OUTPUT.PUT_LINE(V_ENAME);
  DBMS_OUTPUT.PUT_LINE(V_SAL);
END;
/

다음의 예시는 ERROR가 발생하는 예시이다.

DECLARE
  V_ENAME   EMP.ENAME%TYPE;
  V_SAL     EMP.SAL%TYPE;
BEGIN
  SELECT ENAME, SAL INTO V_ENAME, V_SAL
  FROM EMP
  WHERE DEPTNO = 10;
END;
/
 
--ERROR가 나는 이유는 스칼라변수는 단일값을 가지고 있기 때문에 여러 값을 반환 할 수 없다.

다음의 예시도 ERROR가 발생하는 예시이다.

DECLARE
  V_ENAME   EMP.ENAME%TYPE;
  V_SAL     EMP.SAL%TYPE;
BEGIN
  SELECT ENAME, SAL INTO V_ENAME, V_SAL
  FROM EMP
  WHERE DEPTNO = 50;
END;
/
 
--ERROR가 나는 이유는 스칼라변수에는 특별한 예외처리가 있지 않는 이상, NULL값이 들어갈 수는 없기 때문에 발생한다.

*추가내용

만약 반환값이 여러개라서 ERROR가 발생할 경우 우리는 어떤 조치를 취해야 할까??

여러가지가 있지만 그중에 하나는 CURSOR를 이용하는 것이다. 

다음 예시를 참고하도록 하자.

DECLARE 
  CURSOR CUR_EMP IS 
   SELECT ENAME, SAL 
   FROM EMP 
   WHERE DEPTNO = 10 ; 
 
  V_ENAME   EMP.ENAME%TYPE ; 
  V_SAL     EMP.SAL%TYPE ;
BEGIN 
  OPEN CUR_EMP ; 
  FETCH CUR_EMP INTO V_ENAME, V_SAL ; 
  DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||V_SAL) ; 
  FETCH CUR_EMP INTO V_ENAME, V_SAL ; 
  DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||V_SAL) ; 
  FETCH CUR_EMP INTO V_ENAME, V_SAL ; 
  DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||V_SAL) ; 
  CLOSE CUR_EMP ; 
END ; 
/

MERGE문

시작하기 앞서 MERGE라는 것대 대해 간단하게 설명하면

COPY_EMP테이블을 생성한다.

INSERT INTO COPY_EMP 
SELECT * 
FROM EMP 
WHERE EMPNO NOT IN (SELECT EMPNO FROM COPY_EMP) ; 

다음예시를 보자

UPDATE COPY_EMP C 
SET SAL = (SELECT SAL 
           FROM EMP 
           WHERE EMPNO = C.EMPNO) ; 
 
INSERT INTO COPY_EMP 
SELECT * 
FROM EMP 
WHERE EMPNO NOT IN (SELECT EMPNO FROM COPY_EMP) ; 

이 구문을 MERGE를 써서 나타내면

MERGE INTO COPY_EMP C
USING EMP E
   ON (C.EMPNO = E.EMPNO)
WHEN MATCHED THEN
UPDATE --위에 MERGE로 테이블이 정의되어있기 때문에 따로 테이블이름을 쓰지 않아도 된다.
SET C.SAL = E.SAL
   ,C.COMM = E.COMM
WHEN NOT MATCHED THEN
INSERT --위에 MERGE로 테이블이 정의되어있기 때문에 따로 테이블이름을 쓰지 않아도 된다.
VALUES (E.EMPNO, E.ENAME, E.JOB, E.MGR,
        E.HIREDATE, E.SAL, E.COMM, E.DEPTNO);

로 나타낼수 있다.

이 MERGE문은 SQL블록으로

BEGIN
MERGE INTO COPY_EMP C
USING EMP E
   ON (C.EMPNO = E.EMPNO)
WHEN MATCHED THEN
UPDATE --위에 MERGE로 테이블이 정의되어있기 때문에 따로 테이블이름을 쓰지 않아도 된다.
SET C.SAL = E.SAL
   ,C.COMM = E.COMM
WHEN NOT MATCHED THEN
INSERT --위에 MERGE로 테이블이 정의되어있기 때문에 따로 테이블이름을 쓰지 않아도 된다.
VALUES (E.EMPNO, E.ENAME, E.JOB, E.MGR,
        E.HIREDATE, E.SAL, E.COMM, E.DEPTNO);
END;
/

으로도 표현이 가능하다.

SQL CURSOR

- Oracle 서버에서 할당한 전용 메모리 영역에 대한 포인터

- SELECT 문의 결과 집합을 처리하는데 사용

>암시적커서

- Oracle서버에서 SQL문을 처리하기 위해 내부적으로 생성,관리

>명시적커서

- 프로그래머가 명시적 선언

BEGIN
 
  UPDATE COPY_EMP
  SET SAL = SAL * 1.2
  WHERE DEPTNO = 10;
  
  DELETE COPY_EMP
  WHERE SAL > 2500;
  
END;
/
--이 경우 각각의 업데이트가 몇번 행해졌는지는 나타나지 않는다.

그렇다면, 

몇개의 행이 업데이트 되었는지를 알고 싶다면

암시적 커서를 이용해야 한다.

다음 예시를 참고하자.

BEGIN
 
  UPDATE COPY_EMP
  SET SAL = SAL * 1.2
  WHERE DEPTNO = 10;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'row updated');
  
  DELETE COPY_EMP
  WHERE SAL > 2500;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'row updated');
  
  DELETE COPY_EMP
  WHERE SAL > 10000;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'row updated');
  
END;
/

명시적 커서의 경우 다음과 같이 사용한다.

DECLARE 
  CURSOR CUR_EMP IS 
   SELECT ENAME, SAL 
   FROM EMP 
   WHERE DEPTNO = 10 ; 
 
  V_ENAME   EMP.ENAME%TYPE ; 
  V_SAL     EMP.SAL%TYPE ;
BEGIN 
  OPEN CUR_EMP ; 
  FETCH CUR_EMP INTO V_ENAME, V_SAL ; 
  DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||V_SAL) ; 
  FETCH CUR_EMP INTO V_ENAME, V_SAL ; 
  DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||V_SAL) ; 
  FETCH CUR_EMP INTO V_ENAME, V_SAL ; 
  DBMS_OUTPUT.PUT_LINE(V_ENAME||' '||V_SAL) ; 
  CLOSE CUR_EMP ; 
END ; 

SQL을 배운지 얼마 되지 않아 잘못된 내용이 있을 수 있습니다. 틀린 내용이있다면, 댓글로 달아주세요.