데이터베이스 프로그래밍

1. 데이터베이스 프로그래밍의 개념

---

[ 데이터베이스 프로그래밍]

• 프로그래밍: 프로그램을 설계하고 소스코드를 작성하여 디버깅하는 과정
• 데이터베이스 프로그래밍 : DBMS에 데이터를 정의하고 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정. 일반 프로그래밍과는 데이터베이스 언어인 SQL을 포함한다는 점이 다르다

데이터베이스 프로그래밍 중에서도 SQL Interface 와 Embedded Programming을 사용하는 경우가 있습니다.

[ 데이터베이스 프로그래밍 방법 ]

• SQL 전용 언어를 사용하는 방법
• 일반 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법
• 웹 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법
• 4GL (4th Generation Language)

일반적으로 기계어를 1세대, 어셈블리 언어를 2세대 그리고 절차지향 언어를 3세대 그리고 4세대 언어인 4GL은 순차형 언어 이후의 프로그래밍 언어를 가리킵니다. 즉, 4GL이란 사용자 중심의 언어로 절차지향적인 틀에서 벗어난 언어 예를 들어 JAVA 와 C++같은 객체 지향의 언어가 포함됩니다.

 

2. PL/SQL

---

[ PL/SQL이란? ]

• Procedural Language/Structured Query Language로 응용 프로그램을 작성하는데 사용하는 오라클 전용 SQL 언어
• SQL 전용 언어로 SQL문에 변수, 제어, 입출력 등의 프로그래밍 기능을 추가하여 SQL만으로 처리하기 어려운 문제 해결
• PL/SQL 은 SQL Developer에서 바로 작성하고 컴파일한 후 결과를 실행함
• PL/SQL에는 프로시저, 트리거, 사용자 정의 함수 등이 있다.

[ 프로시저 ]

• 프로시저를 정의하려면 CREATE PROCEDURE 문을 사용해야 한다.
• PL/SQL은 선언부(BEGIN) 와 실행부(END)로 구성된다.
• 선언부에서는 변수와 매개변수를 선언하고, 실행부에서는 프로그램 로직을 구현한다.
• 매개변수(parameter) 는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값이다.
• 변수 (Variable)는 저장 프로시저나 트리거 내에서 사용되는 값임
• 주석은 /*와 * / 사이 또는 -- 다음에 기술하면 된다.

프로시저는 프로그래밍에 함수와 유사합니다. 프로시저를 실행하는데 필요한 매개변수를 받고, 프로시저의 구현에 필요한 변수는 선언부에 선언을 하고 로직은 실행부에 구성을 합니다. 그렇기에 아래의 프로시저처럼 Insert와 같이 여러번 자주 실행해야 하는 SQL문들을 프로시저로 구현해두면 상당한 편리성을 얻을 수 있습니다.

동일한 값이 있는지를 검사하기 위한 프로시저는 아래와 같이 구현할 수 있습니다. 예를 들어 같은 책 제목을 가진 도서가 있는지를 검사하는 프로시저를 작성하고자 할 때, mycount라는 변수를 두어 내가 원하는 책 제목을 가진 책들이 도서목록에 몇 개 있나 개수를 세주어야 합니다. 그러므로 Begin(선언부)에 mycount라는 변수를 선언해두고 End(실행부)에서는 If/else와 같은 제어문을 사용하여 mycount==0인 경우와 그렇제 않은 경우를 나누어 처리해주면 됩니다.

우리는 프로시저가 한 번에 한 행씩 처리하기를 원하는 경우가 있는데 이러한 경우에 커서(Cursor)를 활용하여 테이블의 행을 순서대로 가리키도록 하고, 필요한 경우에 그 행의 데이터를 추출할 수 있습니다. 커서와 관련된 키워드는 아래와 같습니다.

키워드	역할
CURSOR <cursor 이름> IS <커서 정의>	커서를 생성
OPEN <cursor 이름>	커서의 사용을 시작
FETCH <cursor 이름> INTO <변수>	행 데이터를 가져옴
CLOSE <cursor 이름>	커서의 사용을 끝냄

판매 도서 목록에 대한 이윤을 계산하는 프로시저를 작성한다고 할 때, 우리는 행마다 값을 확인하여 처리를 해주어야 합니다. 그래서 프로시저 선언부에서 Cursor - IS - 를 활용하여 커서를 만들어주고, Open -를 활용하여 커서를 열어주고 Fetch - INTO - 하여 데이터를 추출한 후에 반복문이 종료되면 Close 함수를 이용하여 커서의 사용을 끝내면 됩니다. 그리고 이를 전체 프로시저로 작성하면 아래와 같습니다.

[ 트리거 ]

• 트리거(Trigger)는 데이터의 변경(INSERT, DELETE, UPDATE)문이 실행될 때 자동으로 실행되는 프로시저를 의미한다.
• 트리거에는 BEFORE 트리거와 AFTER 트리거가 있다.

BEFORE 트리거란 해당 프로시저의 실행 이전에 자동으로 먼저 실행되는 프로시저이며, AFTER 트리거란 해당 프로시저가 실행된 후에 자동으로 실행되는 프로시저입니다.

 

아래의 그림에서는 새로운 도서가 Book 테이블에 삽입된 후 백업을 위해 Book_log 테이블에 같은 내용을 삽입하는 트리거를 보여주고 있습니다. AFTER INSERT ON Book FOR EACH ROW 구문은 Book 테이블에 값이 추가되면 실행하는 트리거임을 명시해주고 있고, 실행부(END)에서는 Book_log 테이블에 데이터를 백업해두고 있습니다.

[ 사용자 정의 함수 ]

• 사용자 정의 함수는 수학의 함수와 마찬가지로 입력된 값을 가공하여 결과를 돌려준다.

아래의 함수는 판매된 도서에 대한 이익을 계산하는 함수 입니다. RETURN 값이 INT 형임과 그 값이 사용자가 선언한 변수임을 적어주고 있고, 인자로는 price라는 판매 가격을 받고 있습니다.

[ 프로시저, 트리거, 사용자 정의 함수 비교 ]

	프로시저	트리거	사용자 정의 함수
공통점	저장 프로시저임, 오라클의 경우 PL/SQL로 작성
정의 방법	CREATE PROCEDURE 문	CREATE TRIGGER 문	CREATE FUNCTION 문
호출 방법	EXEC 문으로 직접 호출	INSERT, DELETE, UPDATE 문이 실행될 때 자동으로 실행됨	SELECT 문으로 호출
기능의 차이	SQL 문으로 할 수 없는 복잡한 로직을 수행	기본 값 제공, 데이터 제약 준수, SQL 뷰의 수정, 참조무결성 작업 등을 수행	속성 값을 가공하여 반환, SQL 문에 직접 사용