[오라클로 배우는 데이터베이스 개론과 실습]데이터베이스 프로그래밍

티스토리 뷰

Database/Oracle

[오라클로 배우는 데이터베이스 개론과 실습]데이터베이스 프로그래밍

retto9522 2024. 9. 12. 14:07

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

데이터베이스 프로그래밍

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

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

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

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

일반적으로 기계어를 1세대, 어셈블리 언어를 2세대 그리고 절차지향 언어를 3세대, 4세대 언어인 4GL은 순차형 언어 이후의 프로그래밍 언어를 가리킵니다.

즉, 4GL이란 사용자 중심의 언어로 절차지향적인 틀에서 벗어난 언어를 이야기합니다. 예를 들어 Java와 C++ 같은 객체 지향의 언어가 포함됩니다.

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 ~ 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 라는 판매 가격을 받고 있습니다.

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