절차형 SQL의 구조

절차형 SQL(procedural SQL)은 SQL 문장에 변수와 조건문·반복문을 더해, 여러 문장을 블록 하나로 묶어 차례대로 실행하는 언어입니다. 저장 프로시저·사용자 정의 함수·트리거가 모두 이 블록 꼴로 만들어집니다.

SELECT·INSERT 같은 SQL 한 문장은 한 가지 일만 합니다. 앞 문장의 결과를 보고 다음 할 일을 골라야 하면, 응용 프로그램이 결과를 받아 판단한 뒤 다음 문장을 다시 보내야 합니다. 같은 처리를 여러 번 되풀이할 때도 그렇습니다. 오가는 횟수만큼 느려지고, 같은 업무 규칙이 프로그램마다 흩어집니다. 절차형 SQL은 그 판단과 되풀이를 데이터베이스 안에 두고 한 번에 실행합니다. 아래 그림은 학년별 인원을 세어 더하는 블록이 위에서 아래로 실행되는 장면입니다.

DECLARE  v_cnt NUMBER;  v_sum NUMBER := 0;BEGIN  FOR i IN 1..3 LOOP    SELECT COUNT(*) INTO v_cnt    FROM 학생    WHERE 학년 = i;    v_sum := v_sum + v_cnt;  END LOOP;  DBMS_OUTPUT.PUT_LINE(v_sum);END;

7

DECLARE  v_cnt NUMBER;  v_sum NUMBER := 0;BEGIN  FOR i IN 1..3 LOOP    SELECT COUNT(*) INTO v_cnt    FROM 학생    WHERE 학년 = i;    v_sum := v_sum + v_cnt;  END LOOP;  DBMS_OUTPUT.PUT_LINE(v_sum);END;

DECLARE  v_cnt NUMBER;  v_sum NUMBER := 0;BEGIN  FOR i IN 1..3 LOOP    SELECT COUNT(*) INTO v_cnt    FROM 학생    WHERE 학년 = i;    v_sum := v_sum + v_cnt;  END LOOP;  DBMS_OUTPUT.PUT_LINE(v_sum);END;

DECLARE  v_cnt NUMBER;  v_sum NUMBER := 0;BEGIN  FOR i IN 1..3 LOOP↑ 되돌아옴    SELECT COUNT(*) INTO v_cnt    FROM 학생    WHERE 학년 = i;    v_sum := v_sum + v_cnt;  END LOOP;  DBMS_OUTPUT.PUT_LINE(v_sum);END;

DECLARE  v_cnt NUMBER;  v_sum NUMBER := 0;BEGIN  FOR i IN 1..3 LOOP↑ 되돌아옴    SELECT COUNT(*) INTO v_cnt    FROM 학생    WHERE 학년 = i;    v_sum := v_sum + v_cnt;  END LOOP;  DBMS_OUTPUT.PUT_LINE(v_sum);END;

DECLARE  v_cnt NUMBER;  v_sum NUMBER := 0;BEGIN  FOR i IN 1..3 LOOP    SELECT COUNT(*) INTO v_cnt    FROM 학생    WHERE 학년 = i;    v_sum := v_sum + v_cnt;  END LOOP;  DBMS_OUTPUT.PUT_LINE(v_sum);END;

1블록의 네 부분

절차형 SQL은 문장 하나가 아니라 블록(block) 하나를 단위로 씁니다. 블록은 네 개의 키워드로 자리가 나뉩니다.

DECLARE
  -- 선언부: 쓸 변수를 만든다
BEGIN
  -- 실행부: SQL과 제어문을 쓴다
EXCEPTION
  -- 예외 처리부: 오류를 받는다
END;

• 실행부만 있으면 블록이 됩니다. BEGIN으로 시작해 END;로 끝나는 블록도 돌아갑니다.
• 선언부에서 만든 변수는 그 블록 안에서만 살아 있습니다. END;를 지나면 사라집니다.
• 예외 처리부는 이 단원 마지막 레슨에서 따로 다룹니다.
• 블록 끝의 /는 블록을 여기까지 받아 실행하라는 도구의 신호입니다. 블록 자체의 문법은 아닙니다.

2변수와 SELECT INTO

선언부는 이름 자료형 꼴로 변수를 만듭니다. 뒤에 := 값을 붙이면 처음 값까지 정합니다. 실행부에서 값을 담을 때도 :=를 씁니다. 등호 하나(=)는 값이 같은지 묻는 비교라서, 담는 자리에는 쓰지 않습니다.

v_sum NUMBER := 0;
v_sum := v_sum + v_cnt;

첫 줄은 선언하면서 0을 담았고, 둘째 줄은 오른쪽을 먼저 계산해 그 값을 다시 v_sum에 담았습니다.

SELECT 결과를 변수에 담을 때는 SELECT와 FROM 사이에 INTO 절을 넣습니다. 그림의 반복문 안에서 인원을 세는 문장이 이 꼴입니다.

SELECT COUNT(*) INTO v_cnt
FROM 학생
WHERE 학년 = i;

INTO가 받을 수 있는 것은 행 하나뿐입니다. 찾은 행이 없거나 둘 이상이면 오류가 납니다. 집계 함수는 언제나 한 행을 돌려주므로 INTO와 짝이 잘 맞습니다.

3조건문과 반복문

실행부에는 SQL 말고도 흐름을 바꾸는 문장을 씁니다. 조건에 따라 갈라지는 조건문과 같은 자리를 되도는 반복문입니다. 둘은 3과목 프로그래밍에서 자세히 배우고, 여기서는 블록 안에서 어떤 꼴로 쓰이는지만 봅니다.

IF 조건 THEN
  실행할 문장;
ELSE
  아니면 실행할 문장;
END IF;

FOR i IN 1..3 LOOP
  되풀이할 문장;
END LOOP;

• 조건문은 IF로 시작해 END IF;로 닫습니다. 조건을 더 두려면 사이에 ELSIF 조건 THEN을 넣습니다.
• FOR i IN 1..3 LOOP는 i에 1·2·3을 차례로 담아 세 바퀴를 돕니다. 그림에서 지금 줄이 아래로 내려가다 FOR 줄로 되올라가는 것이 한 바퀴의 끝입니다.
• 반복 변수 i는 따로 선언하지 않습니다. 반복문이 만들고 반복이 끝나면 없앱니다.
• 횟수가 정해지지 않은 되풀이에는 WHILE 조건 LOOP를 씁니다.

4이름을 붙인 블록 — 프로시저·함수·트리거

지금까지 본 블록은 이름이 없습니다. 적어서 보내면 그 자리에서 한 번 돌고 사라집니다. 이런 블록을 익명 블록(anonymous block)이라 합니다. 블록에 이름을 붙여 데이터베이스에 저장해 두면 언제든 다시 부를 수 있습니다. 저장해 둔 블록이 절차형 SQL의 세 가지입니다.

세 가지 모두 블록의 네 부분을 그대로 씁니다. 머리에 CREATE PROCEDURE·CREATE FUNCTION·CREATE TRIGGER가 붙고 DECLARE 자리에 그 머리가 들어서는 것만 다릅니다. 이어지는 레슨에서 하나씩 봅니다.

예제

익명 블록 셋을 돌려 봅니다. 블록 하나가 실습 하나이고, 블록 끝의 / 뒤에서 실행됩니다. SET SERVEROUTPUT ON은 DBMS_OUTPUT이 찍은 줄을 화면에 보이라는 도구 설정입니다. 블록마다 처음 데이터에서 시작합니다 — 앞 블록이 만들거나 바꾼 것은 남아 있지 않습니다.

선언부가 없는 블록

실행부만으로 블록이 됩니다.

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE(
      '블록 하나');
END;
/

블록 하나

변수와 조건문

103번 학생의 네트워크(NW) 점수를 변수에 담고, 60점을 기준으로 갈라 찍습니다.

SET SERVEROUTPUT ON
DECLARE
  v_점수 NUMBER;
  v_판정 VARCHAR2(10);
BEGIN
  SELECT 점수 INTO v_점수
  FROM 수강
  WHERE 학번 = 103
    AND 과목코드 = 'NW';
  IF v_점수 >= 60 THEN
    v_판정 := '통과';
  ELSE
    v_판정 := '재수강';
  END IF;
  DBMS_OUTPUT.PUT_LINE(v_판정);
END;
/

재수강

103번의 NW 점수는 55점이라 ELSE 쪽이 실행됐습니다. SELECT 한 번과 판단 한 번이 블록 안에서 끝났습니다.

블록 안에서 데이터를 고친다

블록은 SELECT만 하는 것이 아닙니다. INSERT·UPDATE·DELETE와 COMMIT도 그대로 씁니다. 운영체제(OS) 과목 점수를 5점씩 올린 뒤 확인합니다.

BEGIN
  UPDATE 수강
  SET 점수 = 점수 + 5
  WHERE 과목코드 = 'OS';
  COMMIT;
END;
/
SELECT 학번,
  점수
FROM 수강
WHERE 과목코드 = 'OS'
ORDER BY 학번;

UPDATE 한 문장이 세 행을 한꺼번에 고쳤습니다. 블록이 하는 일은 이 UPDATE를 조건·반복과 함께 묶어 두는 것입니다.

시험 포인트

• 필기: 블록의 네 부분과 키워드를 짝짓는 문제가 나옵니다. DECLARE는 선언부, BEGIN은 실행부, EXCEPTION은 예외 처리부이고 END로 끝냅니다. 빠져도 되는 자리는 선언부와 예외 처리부이며, 실행부는 반드시 있어야 합니다. 값을 담는 기호는 :=입니다. SELECT 결과를 변수에 담는 절은 INTO이고 행 하나만 받습니다. 이름이 없어 저장되지 않는 블록은 익명 블록(anonymous block)입니다. 절차형 SQL의 종류로는 저장 프로시저·사용자 정의 함수·트리거 셋을 묻습니다. 셋 가운데 값을 돌려주는 것은 사용자 정의 함수, 저절로 실행되는 것은 트리거입니다.
• 실기: 이름을 쓰게 하면 절차형 SQL(Procedural SQL)과 블록의 구성 이름 셋 — 선언부(DECLARE)·실행부(BEGIN)·예외 처리부(EXCEPTION)입니다. 한글 이름을 물으면 선언부·실행부·예외 처리부로, 키워드를 물으면 대문자 DECLARE·BEGIN·EXCEPTION으로 씁니다. 블록 끝은 END;로 세미콜론까지 씁니다.

확인 문제

1. 다음은 과목 표에서 자료구조(DS)의 학점을 읽어 찍는 블록입니다. 읽을 행이 없을 때 나는 오류를 받아 처리하는 문장은 (가)~(라) 가운데 어디에 넣어야 할까요?

DECLARE
      ← (가)
  v_학점 NUMBER;
BEGIN
      ← (나)
  SELECT 학점 INTO v_학점
  FROM 과목
  WHERE 과목코드 = 'DS';
      ← (다)
EXCEPTION
      ← (라)
END;

• ① (가)
• ② (나)
• ③ (다)
• ④ (라)

2. 다음 (가)~(라)에 맞는 말을 쓰세요. (가)는 한글 이름과 영문으로 함께, (나)~(라)는 한글 이름과 그 자리를 여는 키워드로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) SQL 문장에 변수와 조건문·반복문을 더해, 여러 문장을 묶어 차례대로 실행하는 언어입니다.
• (나) 블록 안에서만 쓸 변수를 만드는 자리입니다. 없어도 블록은 돌아갑니다.
• (다) SQL 문과 흐름을 바꾸는 문장을 쓰는 자리입니다. 블록에 반드시 있어야 합니다.
• (라) 실행 도중 난 오류를 받는 자리입니다. 없어도 블록은 돌아갑니다.

3. 학년별 인원을 세어 더하는 블록을 다음처럼 바꿨습니다. 찍히는 줄을 위에서부터 차례로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요. 학생 표의 학년은 1학년 2명·2학년 2명·3학년 3명입니다.

SET SERVEROUTPUT ON
DECLARE
  v_cnt NUMBER;
  v_sum NUMBER := 0;
BEGIN
  FOR i IN 2..4 LOOP
    SELECT COUNT(*) INTO v_cnt
    FROM 학생
    WHERE 학년 = i;
    DBMS_OUTPUT.PUT_LINE(
        i || ':' || v_cnt);
    v_sum := v_sum + v_cnt;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_sum);
END;
/