데이터베이스 PL/SQL 기본

절차형 SQL

우리가 C언어에 대해서 배울 때 절차지향형이라는 말을 들은 적 있을 것이다. SQL에서도 이런 절차 지향형 프로그램이 가능하며 이를 절차형 SQL이라고 말한다. 우리가 이번에 사용할 절차형 SQL은 PL / SQL이다. 이를 활용하여 오라클 데이터 조작을 프로그램처럼 할 수 있도록 해보자.

PL / SQL

PL이란 Procedural Language를 의미한다. 즉 절차형 언어라는 뜻이며, pl/sql로 작성하는 스크립트를 프로시저(?)라고 한다.

pl/sql은 블록 단위의 구조로 되어 있다. 그리고 블록 내부에 DML 문장이나, 쿼리문, 그리고 절차형 언어 등 트랜잭션 언어를 사용할 수 있다. 이렇게 sql문장들을 블록으로 묶어 한번에 서버로 보내기 때문에 통신량을 줄여 속도를 높일 수 있다.

게다가 블록을 활용하여 각 기능별로 모듈화 역시 가능하여 다양한 저장 모듈을 개발할 수 있다. 모듈은 사용자들 간에서 공유할 수 있는 sql 컴포넌트 프로그램이며 독립 실행 또는 다른 프로그램에서 실행될 수 있다.

pl/sql의 구조를 살펴보도록 하자. 일단 exception이라는 예외문을 제외하고 2가지로 구성된다. declare라는 변수에 대해서 정의하는 선언부와 begin~end라는 선언된 변수를 가지고 로직을 실행하는 실행부로 나누어져 있다.

이를 실제 스크립트 상에서 작성하면 다음과 같다.

여기서 serveroutput은 실행문이 cmd창에서 출력되도록 선언해준다.

그리고 아직 이름이 붙어 있지 않은 형태에 대해서 익명 프로시저라고 표현한다.

대체 변수 - &

절차형 sql에서 값을 대체하기 위해서 사용하는 것을 대체 변수라고 한다. 대체 변수는 ‘&’를  붙여 사용하며, 이에 대해서 알아보도록 하자.

간단한 pl/sql의 예시를 살펴보겠다. 데이터베이스에서 원하는 사원번호를 입력받아 해당 테이블을 출력하는 간단한 예시문이다. cmd 창에서 스크립트문을 실행하는 방법에 대해서는 이미 알고 있을 것이다. @와 해당 경로를 입력하면 스크립트문을 실행할 수 있다.

위에서 작성한 스크립트문을 출력해보도록 하겠다.

cmd창에서 프로그램처럼 값을 입력받고 우리가 원하는 테이블을 출력해주고 있다. 이 때, &p_empno라는 사원번호에 대한 대체 변수를 사용하고 있다.

그런데 이 때, 출력창에 구, 신이라는 표현이 함께 출력되어 대체 변수를 사용한 문장에 대해 같이 출력한다. 이를 제거 하기 위해서는 verify라는 기능을 off 해야한다.

set verify off / on

set verify를 off하고, 프로그램이 끝날 때 다시 on하기로 하자.

다음처럼 신, 구라는 verify 표현이 사라지고 우리가 원하는 테이블의 형태만을 출력한다.

간단한 예시문을 작성해보자. 이번에는 사원명을 입력받아 사원번호, 사원명, 급여, 부서번호를 출력하는 스크립트를 작성해보자.

이 때, 소문자 입력을 받기 위해서 lower 함수를 사용하고, 사원 이름은 사원 번호와 달리 문자열 표현이기 때문에 꼭 ‘ ‘를 붙이도록 하자.

사원번호로 출력받았던 데이터에 대해 이번엔 사원명으로 검색해보자. allen을 입력하면 위에서 출력했던 테이블이 그대로 검색되고 있다.

대체 변수는 값이 아닌 조건문에 대해서도 적용이 가능하다. 이번에는 값 뿐만 아니라 조건절의 내용 역시 대체변수로 작성해보겠다. 다음 예시문처럼 where 절에 대해 내용을 전부 대체변수로 받아 cmd창에서 입력을 받도록 하겠다.

이를 출력해보면, 다음처럼 칼럼 값을 입력받는 것 외에도 조건절에 대해 입력하도록 하고 있다. 그리고 원하는 조건절을 입력하면 다음처럼 해당 조건에 맞는 테이블을 출력한다.

프롬프트 메시지 - accept

이번에는 프롬프트 상에서 출력되는 메시지까지 우리가 임의대로 작성하고 싶다. 이 경우에는 어떻게 할까?

이를 위해서 accept문을 사용한다. 이 때 accept문 다음에 우리가 적용시키고 싶은 대체 변수를 넣고, 프롬프트 창에서 띄우고 싶은 메시지를 입력한다.

예시를 한번 살펴보자. p_empno에 대해서 대체변수를 사용하고 있다. 이 때 프롬프트 창에서 출력할 메시지를  ‘조회할 사원번호를 입력하세요 : ‘라는 메시지로 바꾸고 싶다. 따라서 accept문에서 p_empno에 대해 해당 문구를 입력한다.

다음처럼 우리가 원하는 메시지가 출력되고 테이블 역시 정상적으로 출력되고 있다.

문제

지금까지 배운 것들을 활용하여 간단하게 조인문 하나를 출력해보자. 사원명을 입력받아

사원번호, 사원명, 급여, 호봉을 출력하는 스크립트를 작성해보자.

일단 accept문을 이용해 원하는 메시지를 출력받고, 사원명 입력을 받기 위해 &p_ename이라는 대체변수를 ‘ ‘안에 넣어 문자열 입력을 받을 준비를 한다. 그리고 이제 호봉에 대한 정보를 가져오기 위해서 조인문을 작성한다. 사실상 이제까지 우리가 다뤘던 조인 문장에서 절차형 sql을 위해 대체변수 부분에 대한 조건을 추가한 것 뿐이다.

원하는대로 잘 출력되고 있음을 확인할 수 있다.

출력문

pl/sql에서 문자열 출력은 두 가지 방법이 존재한다. 다른 프로그래밍 언어에서도 마찬가지겠지만, 자동 개행이 되는 방법과 그렇지 않은 방법이 존재한다.

아래 스크립트 문을 살펴보자.

현재 put_line의 경우에는 자동 개행이 되는 출력방법이다. 반면에 put의 경우에는 개행이 없이 출력된다. 따라서 개행을 원하는 경우에는 put_line에 공백문자를 넣어 개행을 넣어주자.

출력창을 살펴보면 다음처럼 put_line의 경우 자동개행을 하고 있지만, put은 한 라인에 출력을 받고 있다.

프로시저 내 함수 사용

우리가 지금까지 배웠던 sql 함수 역시 프로시저에서 다 사용할 수 있다. 출력문에  문자열 합성 함수인 ‘||’와 concat()을 사용해보자. 그리고 sysdate 등의 값도 사용할 수 있다.

출력이 제대로 이루어지고 있다. 이제 이런 함수들을 이용하여 프로시저에서 sql문을 작성하도록 하자.