15-1.변수

1.SQL 확장 언어

SQL은 오로지 질의만을 위해 만든 언어이다. 어떤 값을 조사하라, 새로운 레코드를 삽입하라, 특정 정보를 삭제하라 같은 단발적인 명령만 내릴 수 있다. 중간 결과를 저장하거나 조건을 판단하거나 비슷한 명령을 반복하는 제어 구조가 없어 구문이 중복되어 길고 난해하며 효율도 떨어진다.

대규모의 데이터를 체계적으로 처리하려면 흐름을 통제하는 제어 구문이 필수적이다. DBMS 제작사는 SQL에 변수와 조건문, 반복문 등의 문법을 추가하여 프로그래밍 언어 수준으로 확장하였다. 자바, C# 같은 언어와 문법은 비슷하되 수준은 단순해서 프로그래밍 경험자에게는 아주 쉽다.

이런 문법적 장치와 SQL을 같이 활용하면 복잡한 작업을 분해하여 단계별로 처리할 수 있고 대규모의 데이터 처리를 자동화할 수 있다. 주로 저장 프로시저로 코드를 작성하지만 쿼리툴에서 코드를 바로 사용할 수도 있다.

확장 SQL은 제조사마다 문법과 이름이 다르다. 오라클의 PL/SQL과 마이크로소프트의 TSQL은 개념과 구조는 유사하지만 서로 호환되지 않는다. 다만, 형식적인 차이가 있을 뿐 본질적인 목적은 같아 상호 대체는 가능하다. PL/SQL에 있는 기능은 TSQL에도 있고 반대도 마찬가지이다.

유사한 여러 언어를 한꺼번에 익히는 것은 혼란스러우니 오라클 PL/SQL 위주로 실습한다. PL/SQL로 문법적 구조와 활용 방법을 익힌 후 TSQL은 차이점 정도만 간략히 요약하기로 한다. 하나를 확실히 익히면 나머지는 금방 익숙해질 수 있다.

실무에서는 두 언어를 골고루 다 사용하므로 양쪽을 다 연구해 보는 것도 유용하지만 처음 배울 때는 오히려 헷갈리는 면이 있다. 당장은 오라클 문법만 필요하다면 TSQL이나 MariaDB의 문법은 건너 뛰어도 상관없으며 필요할 때 다시 공부하면 된다.

2.값의 출력

코드의 실행 결과를 확인하려면 화면에 출력해 봐야 한다. 주 실습툴인 SQL Developer는 SELECT 문의 실행 결과를 질의 결과창에 도표 형태로 예쁘게 잘 출력하지만 코드는 SQL이 아니어서 SELECT 문으로는 결과를 확인할 수 없다.

DBMS_OUTPUT 패키지의 PUT_LINE 프로시저로 문자열이나 변수를 스크립트창에 출력하며 다음 선언문으로 출력문을 쓰겠다는 선언부터 해야 한다. 이 옵션의 디폴트가 OFF여서 그냥은 변수값을 찍어볼 수 없다.

SET SERVEROUTPUT ON;

기본적인 출력문이 OFF인 이유는 원래 DBMS는 데이터만 처리할 뿐 사용자를 직접 대면하지 않기 때문이다. 개발할 때 옵션을 켜야 하는 불편함이 있지만 한 번 ON으로 바꿔 놓으면 세션 내에서는 설정을 계속 유지한다. 값을 출력할 때는 다음 명령을 사용한다.

DBMS_OUTPUT.PUT_LINE(출력할 내용);

단순한 문자열도 가능하고 변수나 연산식도 출력할 수 있다. 이 명령만 단독으로 쓸 수는 없으며 스크립트 안에서 호출해야 한다. PL/SQL의 스크립트 구조는 다음과 같다.

DECLARE

변수 선언

BEGIN

명령

EXCEPTION

END;

DECLARE에서 변수를 선언하고 BEGIN ~ END 사이에 명령을 작성한다. 예외를 처리 하려면 EXCEPTION 블록에 코드를 작성하되 필요 없으면 생략한다. 뭐라도 출력해 보려면 최소한 BEGIN ~ END 블록은 구성해야 한다. 간단한 상수 문자열을 스크립트 출력란에 찍어 보자.

BEGIN

DBMS_OUTPUT.PUT_LINE('안녕하세요');

END;

문자열이 나타나지 않으면 SERVEROUTPUT 옵션을 ON으로 켰는지 확인해 보자. 옵션을 켜 놨더라도 재시작하면 다시 OFF되어 버리므로 매번 다시 켜 줘야 한다. 여러 가지 이유로 에러가 발생할 소지도 많다. 다음 구문은 에러이다.

BEGIN

DBMS_OUTPUT.PUT_LINE('안녕하세요')

END

PL/SQL은 형식이 엄격해 오타에 유의해야 한다. 출력문은 세미콜론으로 끝나며 END 다음에도 블럭의 끝임을 분명히 표시하기 위해 세미콜론을 찍는다. 간단한 연산이나 함수 호출도 가능하다.

BEGIN

DBMS_OUTPUT.PUT_LINE(2 + 3 * 4);

DBMS_OUTPUT.PUT_LINE(POWER(2, 3));

END;

다항식 정도는 정확히 계산해 내며 POWER 함수로 2의 3승을 구했다. 각각 14와 8을 출력한다. 괄호 안에 변수를 적으면 현재값을 출력한다.

3.변수의 선언

변수(Variable)는 작업 중의 임시 결과를 저장하는 장치이다. 쿼리 결과를 잠시 저장해 놓고 재사용하거나 필드의 값을 읽어 조작할 때 사용한다. 사용하기 전에 선언부터 하는데 DECLARE 블록에서 이름과 타입을 밝힌다.

변수명 타입 [ := 초기값 ]

변수끼리 구분하고 이후 변수를 칭하려면 고유한 이름이 있어야 한다. 대상을 잘 상징하는 이름을 자유롭게 붙이되 명칭 규칙을 지켜야 한다. SQL 키워드는 쓸 수 없으며 중간에 공백이 들어가서도 안된다. 오라클은 관습적으로 변수명에 v_ 접두를, 상수명에 c_ 접두를 붙여 명칭만으로 자격을 쉽게 구분한다.

변수에 값을 대입할 때는 := 연산자를 사용한다. =은 같다는 뜻의 비교 연산자이지 대입 연산자가 아님을 주의하자. 습관적으로 : = 식으로 띄우는 실수를 많이 하는데 한 연산자여서 :=로 붙여 쓴다. 선언할 때 미리 초기값을 지정할 수도 있고 코드 블록에서 값을 대입할 수도 있다. 초기값도 없고 대입도 하지 않으면 NULL값을 가진다.

DECLARE

v_price INT := 1000;

v_num INT := 5;

v_total INT;

BEGIN

v_total := v_price * v_num;

DBMS_OUTPUT.PUT_LINE(v_total);

END;

가격을 저장하는 v_price 변수와 개수를 저장하는 v_num 변수를 정수형으로 선언하고 각각 1000과 5로 초기화했다. 한 번에 하나씩 변수를 선언하며 설사 타입이 같아도 한꺼번에 두 변수를 선언할 수는 없다. v_total은 초기값 없이 선언했다가 BEGIN 블록에서 v_price와 v_num을 곱한 값을 대입했다. 이 값을 출력하면 총 가격은 5000이다.

오라클 코드는 문법은 엄격하지만 형식은 자유롭다. 매 줄마다 개행해야 하는 것은 아니며 들여쓰기도 강제하지 않는다. 세미콜론으로 각 줄을 구분하므로 한 줄에 여러 명령을 적어도 상관없다. DECLARE와 변수 선언문을 같은 줄에 적을 수 있고 BEGN~END까지 한 줄에 다 써도 상관없다.

DECLARE v_price INT := 1000;v_num INT := 5;v_total INT;

BEGIN v_total := v_price * v_num;DBMS_OUTPUT.PUT_LINE(v_total);END;

그러나 이렇게 쓰면 어디까지가 한 명령인지 잘 보이지 않아 가독성이 떨어지고 편집하기도 어렵다. 가급적 줄당 한 명령만 쓰고 들여쓰기를 잘 해 두는 것이 관리에 유리하다. 상수를 선언할 때는 CONSTANT 키워드를 붙이며 선언시 반드시 초기값을 주어야 한다.

DECLARE

c_mile CONSTANT NUMBER := 1.609;

v_kilo INT;

BEGIN

v_kilo := 400;

DBMS_OUTPUT.PUT_LINE(v_kilo * c_mile);

END;

c_mile은 1마일이 1.609킬로미터라는 뜻이며 변하지 않는 값이다. 반복적으로 사용할 수치에 이름을 붙여 상수로 정의해 두면 외우기 쉽고 일괄 수정도 용이하다. 상수는 초기식에서만 값을 지정할 수 있어 초기식을 생략하면 이후 값을 대입할 방법이 없어 에러이다. 초기화 후에는 읽기만 해야 하며 BEGIN 블록에서 c_mile := 1.5 식으로 다른 값을 대입할 수 없다.

특정 테이블의 필드와 같은 타입의 변수를 선언하려면 타입 자리에 테이블.필드%TYPE 으로 쓴다. 필드의 값을 대입받는 변수라면 선언할 때부터 필드와 같은 타입으로 선언해 두는 것이 편리하다. 필드 타입을 일일이 조사할 필요 없고 차후 필드 타입을 변경하면 변수의 타입도 자동으로 같이 바뀐다.

DECLARE

v_price tItem.price%TYPE := 1000;

v_num v_price%TYPE := 5;

v_total v_price%TYPE;

BEGIN

v_total := v_price * v_num;

DBMS_OUTPUT.PUT_LINE(v_total);

END;

v_price를 tItem 테이블의 price 필드와 같은 타입으로 선언한다. 이 필드가 INT로 정의되어 있어 v_price도 INT 타입이 된다. 필드뿐만 아니라 다른 변수와 같은 타입으로 선언할 때도 변수명%TYPE 식으로 타입을 밝힌다. v_num과 v_total은 v_price와 같은 타입이다.

오라클은 원래 진위형인 BOOLEAN 타입이 없다. 이 점은 다른 DBMS도 마찬가지여서 CHAR(1) 타입에 Y/N 또는 T/F 등으로 진위형을 저장한다. 그러나 코드에서는 BOOLEAN 타입이 있으며 TRUE, FALSE, NULL 중 하나의 값을 저장한다. 필드에는 쓸 수 없는 BOOLEAN 타입이 코드에만 존재하는 이유는 조건식이 있기 때문이다.

DECLARE

v_isOk BOOLEAN;

BEGIN

v_isOk := (1 = 1);

IF v_isOK THEN

DBMS_OUTPUT.PUT_LINE('OK');

END IF;

END;

1 = 1 비교 연산문의 평가 결과를 v_isOk에 대입해 두면 IF 문에서 조건식 대신 사용할 수 있다. 조건식이 길거나 여러 번 사용한다면 별도의 BOOLEAN 변수에 대입해 두고 계속 사용하는 것이 유리하다.

연습 문제

1.시, 분, 초를 변수로 선언한 후 모두 곱하여 하루는 몇 초인지 계산하여 출력하라.

4.필드값 대입

SQL의 변수는 주로 테이블의 필드값을 잠시 저장하는 용도로 사용한다. 테이블의 필드값을 읽어 변수에 대입할 때는 SELECT INTO 구문을 사용하며 INTO 다음에 변수를 적는다. 물론 이 변수는 필드와 호환되는 타입이어야 한다.

다음 코드는 주문 테이블에서 1번 주문을 한 사람의 이름을 조사한다. tOrder 테이블의 member를 읽어 대입하기 위한 변수이므로 선언할 때부터 tOrder.member 필드와 같은 타입으로 선언했다. 이 필드가 VARCHAR(20)으로 정의되어 있어 v_member도 같은 타입이 된다.

DECLARE

v_member tOrder.member%TYPE;

BEGIN

SELECT member INTO v_member FROM tOrder WHERE orderID = 1;

DBMS_OUTPUT.PUT_LINE(v_member);

END;

첫 주문을 한 춘향이를 조사하여 출력한다. 두 개 이상의 필드값을 한꺼번에 조사할 때는 SELECT a, b INTO v_a, v_b 식으로 필드명과 변수를 콤마로 구분하여 나열한다. 필드와 변수를 순서대로 대응시켜 대입한다.

DECLARE

v_member tOrder.member%TYPE;

v_item VARCHAR(20);

BEGIN

SELECT member, item INTO v_member, v_item FROM tOrder WHERE orderID = 1;

DBMS_OUTPUT.PUT_LINE(v_member || '의 ' || v_item || ' 주문');

END;

첫 주문을 한 사람과 상품을 조사하여 문자열로 출력했다. "춘향의 청바지 주문"을 출력한다. 변수는 하나의 값만 저장할 수 있으므로 SELECT 문으로 조회하는 값도 반드시 하나여야 한다. 없거나 여러 개이면 예외가 발생하며 이때는 예외 처리 구문으로 특수한 처리를 해야 한다.

필드 하나가 아닌 레코드 전체를 읽을 때는 테이블%ROWTYPE 타입의 변수를 선언하며 레코드 한 행을 통째로 저장한다. SELECT *으로 읽은 행을 이 변수에 대입해 놓고 변수.필드 식으로 필드값을 하나씩 읽는다. 다음은 tCity의 부산 레코드를 변수로 읽어 출력한다.

DECLARE

v_row tCity%ROWTYPE;

BEGIN

SELECT * INTO v_row FROM tCity WHERE name = '부산';

DBMS_OUTPUT.PUT_LINE(TRIM(v_row.name) || ', ' || v_row.area || ', ' || v_row.popu);

END;

v_row를 tCity의 레코드 타입으로 선언하고 SELECT * INTO v_row 명령으로 한 행 전체를 대입했다. 이후 v_row.name, v_row.area 식으로 행 내부의 필드값을 읽는다. 부산의 이름과 면적, 인구수를 구해 출력했다.

부산, 765, 342

한 행 전체를 한꺼번에 대입받아야 하므로 보통 SELECT * INTO 구문으로 대입한다. 일부 필드만 대입받으려면 INTO 구문에도 필드명을 일일이 적어 주어야 한다. 다음 코드는 도시의 이름과 소속 지역만 조사한다.

DECLARE

v_row tCity%ROWTYPE;

BEGIN

SELECT name, region INTO v_row.name, v_row.region FROM tCity WHERE name = '춘천';

DBMS_OUTPUT.PUT_LINE(v_row.region || '도의 ' || v_row.name);

END;

"강원도의 춘천"을 출력한다. 변수 하나로는 한 행만 대입받을 수 있으니 결과 레코드는 반드시 하나여야 한다. 테이블 전체를 읽으려면 커서와 루프를 사용하여 한 행씩 순서대로 읽어야 한다.

레코드 타입은 복수 개의 열로 구성된 하나의 레코드를 표현한다. RECORD 키워드 다음의 괄호 안에 필드를 나열하여 정의한다. 다음 코드의 cap 타입은 면적과 인구 두 개의 필드를 가지는 레코드이다. 이 타입의 변수 city를 선언하면 두 개의 변수를 한꺼번에 대입받을 수 있다.

DECLARE

TYPE cap IS RECORD(area INT, popu INT);

city cap;

BEGIN

SELECT area, popu INTO city FROM tCity WHERE name = '부산';

DBMS_OUTPUT.PUT_LINE(city.area || ',' || city.popu);

END;

전통적인 언어의 구조체에 해당하며 임의 타입의 변수 여러 개를 모아 놓은 타입이다. 테이블의 필드 중 일부 몇 개만 읽고 싶을 때 필요한 필드만 모아 레코드 타입을 선언한다. cap 타입은 도시의 인구와 면적을 한꺼번에 읽어 들이는 타입이며 다음과 같이 선언할 수도 있다.

TYPE cap IS RECORD(area tCity.area%TYPE, popu tCity.popu%TYPE);

어차피 읽을 대상이 정해져 있으니 아예 해당 필드와 타입을 맞추어 두면 관리하기 좋다. 부산의 면적과 인구를 조사하여 그 결과를 cap 타입의 변수 city에 바로 대입할 수 있다. 실행 결과는 765,342이다.

5.변수의 활용

값을 저장하는 변수의 능력은 활용할 곳이 많다. UPDATE나 INSERT 문으로 변수값을 필드에 저장할 수 있고 WHERE 절의 조건문에도 사용한다. 타입만 일치하면 필드가 올 수 있는 모든 곳에 변수를 사용할 수 있다.

변수에 중간값을 잠시 저장해 두면 서브쿼리를 두 개의 문장으로 분리하여 순서대로 실행할 수 있다. 다음 코드는 인구가 가장 많은 도시를 조사한다.

DECLARE

v_maxPopu INT;

v_cityName VARCHAR(10);

BEGIN

SELECT MAX(popu) INTO v_maxPopu FROM tCity;

SELECT name INTO v_cityName FROM tCity WHERE popu = v_maxPopu;

DBMS_OUTPUT.PUT_LINE(v_cityName);

END;

v_maxPopu에 최대 인구수를 조사해 놓고 다음 쿼리문에서 이 변수를 조건절에 사용하여 최대 인구수를 가진 도시명을 v_cityName 변수에 대입하여 출력하였다. 두 질문을 한 번에 처리하는 서브쿼리를 두고 이런 코드를 굳이 쓸 필요는 없지만 조사한 값을 여러 번 쓴다면 변수가 더 효율적이다.

필드의 값을 읽어 모종의 계산을 한 후 다시 필드에 대입할 때도 변수가 유용하다. 변수는 저장 장치가 아닌 메모리 내부에 있어 어떤 복잡한 연산이든 초고속으로 처리한다. 다음 코드는 서울의 인구를 2배로 만든다. v_popu에 인구수를 조사하여 2배로 만든 후 다시 대입한다.

DECLARE

v_popu INT;

BEGIN

SELECT popu INTO v_popu FROM tCity WHERE name = '서울';

v_popu := v_popu * 2;

UPDATE tCity SET popu = v_popu WHERE name = '서울';

END;

이 정도 처리는 UPDATE 문으로 SET num = num * 2하는 것이 더 간단하다. 그러나 일단 값을 변수로 대입받았으면 이 변수를 마음대로 가공할 수 있다는 면에서 쿼리문과는 수준이 다르다. 복잡한 수식을 적용할 수 있고 함수를 호출하여 미리 정의한 규칙대로 값을 조작할 수도 있으며 조건에 따라 다른 처리를 할 수도 있다.

코드의 쿼리문도 실행 결과를 테이블에 직접 반영하지 않고 임시 영역에 저장한다. 따라서 값을 변경한 후 커밋은 따로 해 줘야 한다. 업데이트 결과를 더 확인할 것도 없다면 아예 코드 내에 COMMIT; 명령을 같이 포함시켜 두면 된다.

연습 문제

2.tStaff 테이블에서 월급이 가장 많은 직원은 어느 부서의 누구인지 조사하여 출력하라.

6.컬렉션

배열은 같은 타입의 변수 집합이다. VARRAY 배열은 선언할 때 크기와 타입을 밝히며 생성자로 초기화한다. 처음 선언한 크기까지만 사용할 수 있다. 첨자는 1부터 시작하는 정수이며 변수(첨자) 형식으로 읽고 쓴다.

DECLARE

TYPE int_array IS VARRAY(5) OF NUMBER;

ar int_array;

BEGIN

ar := int_array(8, 9, 0, 6, 2);

FOR idx IN 1..5

LOOP

DBMS_OUTPUT.PUT_LINE(ar(idx));

END LOOP;

END;

크기 5의 정수형 배열 타입을 int_array로 정의하고 이 타입의 변수 ar을 선언했다. 생성자에서 다섯 개의 정수로 초기화한다. FOR 문으로 1~5까지 루프를 돌며 ar(idx) 배열 요소를 순서대로 출력했다. 루프로 반복 처리가 가능하다는 면에서 일반 변수와는 수준이 다르다.

다차원 배열을 만들 때는 배열끼리 중첩한다. 배열 요소의 타입에 제한이 없어 배열을 다른 배열 안에 넣을 수 있다. 다음은 크기 3의 정수형 배열 세 개를 모아 정수형 2차원 배열을 선언한다. 두 배열 크기를 각각 다르게 지정할 수 있으며 총 요소 개수는 두 배열 크기의 곱이다.

DECLARE

TYPE int_array IS VARRAY(3) OF NUMBER;

TYPE int_array2 IS VARRAY(3) OF int_array;

ar int_array2;

BEGIN

ar := int_array2(int_array(1, 2, 3), int_array(4, 5, 6), int_array(7, 8, 9));

DBMS_OUTPUT.PUT_LINE(ar(2)(3));

END;

이차 배열의 요소를 칭할 때는 배열(배열첨자)(요소첨자) 식으로 밝힌다. 코드에서는 ar(2)(3)인 6만 출력해 봤는데 이중 루프를 돌면 모든 요소를 다 순회하며 출력하거나 연산에 사용할 수 있다. 배열을 세 번 이상 중첩할 수도 있다.

중첩 테이블(Nested Table)은 크기를 미리 밝히지 않는 배열이며 생성자에서 필요한 만큼 배열 요소를 나열한다. 선언할 때가 아닌 생성할 때 배열 크기를 결정한다. 테이블이라는 명칭은 데이터베이스의 테이블이라는 뜻이 아니라 룩업 테이블이라는 뜻이다.

DECLARE

TYPE int_table IS TABLE OF NUMBER;

ar int_table;

BEGIN

ar := int_table(1, 2, 3, 4, 5, 6);

DBMS_OUTPUT.PUT_LINE(ar(4));

END;

정수형의 룩업 테이블을 int_table 타입으로 정의하고 이 타입의 배열 ar을 선언했다. 요소 개수는 아직 결정하지 않았으며 생성자에서 크기 6으로 생성하면 6개의 정수를 저장하는 배열이 된다. 4번째 요소인 4를 출력했다.

연관 배열은 키와 값의 쌍이며 일반 언어의 맵 또는 사전과 같은 개념이다. 키는 정수 또는 문자열을 주로 사용하는데 PLS_INTEGER, STRING(길이) 형식으로 타입을 지정한다. NUMBER나 VARCHAR 같은 타입에 비해 32비트 길이의 기계적인 타입이어서 속도가 더 빠르다. 요소를 삭제하는 DELETE, 키의 존재 여부를 조사하는 EXIST, 요소 개수를 조사하는 COUNT 등의 메서드를 제공한다.

DECLARE

TYPE string_int_map IS TABLE OF NUMBER INDEX BY STRING(20);

score string_int_map;

BEGIN

score('홍길동') := 80;

score('황진이') := 90;

score('어우동') := 90;

score.DELETE('황진이');

DBMS_OUTPUT.PUT_LINE(score.COUNT());

DBMS_OUTPUT.PUT_LINE(score('홍길동'));

END;

문자열을 키로 하고 숫자를 값으로 하는 연관 배열을 선언하고 홍길동과 황진이, 어우동의 점수를 저장했다. 황진이는 다시 지우고 개수와 홍길동의 점수를 검색하여 출력했다. 2와 80을 출력한다.