데이터베이스 프로그래밍

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

-프로그래밍: 프로그램을 설계하고 소스코드를 작성하여 디버깅하는 과정

 

-데이터베이스 프로그래밍: DBMS에 데이터를 정의하고 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정, 데이터베이스 언어인 SQL을 포함한다

 

-SQL단독으로 프로그래밍하는 것은 절차가 필요한 복잡한 로직을 구현하기 어렵고, GUI(Graphic User Interface)응용을 구현할 수 없는 등 일반 사용자가 사용하기에 한계가 있다. →일반 프로그래밍 언어에 SQL문을 삽입하여 각 언어의 장점을 살린다 =삽입 프로그래밍(embedded) , 여기에 사용되는 프로그래밍 언어를 호스트 언어(host language)라고함.

 

1.SQL 전용 언어를 사용하는 방법

-SQL 자체의 기능을 확장하여 변수, 제어, 입출력 등의 기능을 추가한 새로운 언어를 사용하는 방법

-MYSQL은 저장 프로그램, 오라클은 PL/SQL, SQL Server는 T-SQL

-SQL 전용 언어는 데이터베이스를 다루는 능력이 뛰어나고 다루는 방법이 쉽다.

-사용자 인터페이스(GUI)를 구축하는 기능이 없어 독립적으로 사용하기보다 프로시저나 함수 등으로 구현하여 다른 프로그램에서 호출해 사용

 

2.일반 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법

-자바, C, C++등 일반 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법.

-삽입된 SQL문은 DBMS의 컴파일러가 처리.

-일반 프로그래밍 언어로 작성된 응용프로그램에서 데이터를 관리,검색

-SQL 단독으로 사용할 때보다 복잡한 로직의 구현이 용이

 

3.웹 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법

-JSP, PHP, ASP 등 웹 스크립트 언어에 SQL을 삽입하여 사용하는 방법

-웹 프로그래밍 언어로 작성된 프로그램에서 데이터를 관리,검색

-SQL문의 결과는 웹 브라우저에서 확인하며

-아파치(apache)와 같은 웹 서버가 데이터베이스 연동을 지원

 

4.4GL(4th generation language)

-데이터베이스 관리기능과 비주얼 프로그래밍 기능을 갖춘'GUI 기반 소프트웨어 개발도구'

-개발도구로는 Delphi, Power Builder, Visual Basic 등이 있음

 

 

-데이터베이스 응용 시스템을 1.하드웨어-2.운영체제-3.DBMS-4.프로그램 환경

으로 계층화시키고 층간에 관계를 표현한 것.

 

-맨 아래의 하드웨어 계층과 운영체제 계층은 DBMS가 운영되는 기반 구조 즉, 플랫폼

 

※저장 프로그램(Stored Program)

-데이터베이스 응용 프로그램을 작성하는 데 사용하는 MySQL의 SQL전용 언어

-SQL전용 언어는 SQl문에 변수, 제어, 입출력 등의 프로그래밍 기능을 추가하여 SQL만으로 처리하기 어려운 문제를 해결

 

1.저장 프로그램

-저장 프로그램은 프로그램 로직을 프로시저(procedure)로 구현하여 객체 형태로 사용한다.

-일반 프로그래밍 언어에서 사용하는 함수와 비슷한 개념으로,작업순서가 정해진 독립된 프로그램의 수행 단위를 말하며, 정의된 다음 MYSQL(DBMS)에 저장되므로 저장 프로그램이라고 한다.

-저장 루틴(routine),트리거(trigger),이벤트(event)로 구성되며, 저장 루틴은 프로시저(procedure)와 함수(function)로 나눔

 

-트리거(trigger)란 데이터의 변경(INSERT, DELETE, UPDATE) 문이 실행될 때 자동으로 같이 실행되는 프로시저를 말함

 

-MySQL에서 저자아 프로그램을 정의 하는 과정

1)SQL 편집기에서 프로그램을 정의

2)스크립트 실행

3)실행 결과가 결과 화면 창에 나타남

4)개체 탐색기의 Stored Procedures/Functions 폴더의 객체가 만들어짐

 

-프로시저를 정의하려면 CREATE PROCEDURE문을 사용

 

-정의 방법

1)프로시저는 선언부와 실행부(BEGIN-END)로 구성된다. 선언부에서는 변수와 매개변수를 선언하고 실행부에서는 프로그램 로직을 구현

2)매개변수는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값이다.

3)변수는 저장 프로시저나 트리거 내에서 사용되는 값이다.

4)소스코드에 대한 설명문은 /*와 */사이에 기술한다. 만약 설명문이 한 줄이면 이중 대시(--)기호 다음에 기술해도 됨

 

delimiter //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
     SET @x =0;
     REPEAT SET @x = @x + 1; UNTIL @x> END REPEAT;
END //
delimiter ;

call dorepeat(1000);

 

※프로시저의 의미와 유용성

1.삽입 작업을 하는 프로시저

-INSERT문을 사용하는 것보다 좀 더 복잡한 조건의 삽입 작업을 인자 값만 바꾸어 수행

-저장해두었다가 필요할 때마다 호출하여 사용

 

ex)

01 use madang;

02 delimiter //

03 CREATE PROCEDURE InsertBook(

04    IN myBookID INTEGER,

05    IN myBookName VARCHAR(40),

06    IN myPublisher VARCHAR(40),

07    IN myPrice INTEGER)

08   BEGIN

09    INSERT INTO Book(bookid, bookname, publisher, price)

10        VALUES(myBookID, myBookName, myPublisher, myPrice);

11   END;

12    //

13    delimiter ;

 

A    /* 프로시저 InsertBook을 테스트하는 부분 */

B    CALL InsertBook(13, '스포츠과학', '마당과학서적',25000);

C    SELECT * FROM Book;

 

02~13 프로시저 정의문은 CREATE PROCEDURE-BEGIN-END 형식.

프로시저 뒤에 인자를 정의하였고, BEGIN-END에는 INSERT 문을 작성

 

-01행은 madang 데이터베이스 사용을 정하는 명령

-02행은 프로시저 종료가 //에서 끝남을 알리는 delimiter

-03행은 프로시저를 정의하는 부분으로 CREATE 문이 프로시저를 정의하는 명령

-04~07행은 프로시저의 매개변수를 정의하는 문장

-프로시저를 작성한 후 [실행]을 클릭하면 정으된 프로시저 InsertBook이 개체 탐색기 madang 데이터베이스  'stored prcedures'항목에 생성된다.

 

A~C 정의된 프로시저를 CALL문으로 호출하는 구문이다. B행에서 InsertBook 프로시저가 호출되어 실행되면 Book 테이블에 bookid가 13인 새로운 투플 한 개가 추가된다.

 

-생성된 InsertBook 프로시저를 삭제하는 문장은 'DROP PROCEDURE InsertBook;'

 

 

2.제어문을 사용하는 프로시저

-저장 프로그램의 제어문은 어떤 조건에서 어떤 코드가 실행되어야 하는지를 제어하기 위한 문법으로, 절차적 언어의 구성요소를 포함

 

-이 구성요소는 BEGIN-END문, IF-ELSE문, WHILE문, RETURN 문 등과 같이 코드 블록에 연산을 하기 위한 구문들을 포함

1)BEGIN-END문은 하나 이상의 문장이 수행될 수 있또록 블록을 정의

2)IF-ELSE문은 조건을 검사하고 검사 결과에 따라 코드의 어느 부분이 사용되는지를 결정

3)WHILE문은 조건이 참인 동안 코드의 일정 부분을 반복

4)RETURN 문은 코드 구조(저장 프로시저나 트리거)에 상관없이 블록을 빠져나와 저장 프로그램을 종료, 이 경우 제어는 DBMS로 반환

 

 

ex)

01 use madang;
02 delimiter //
03 CREATE PROCEDURE BookInsertOrUpdate(
04    myBookID INTEGER,
05    myBookName VARCHAR(40),
06    myPublisher VARCHAR(40),
07    myPrice INTEGER)
08 BEGIN
09    DECLARE mycount INTEGER;
10    SELECT count(*) INTO mycount FROM Book
11      WHERE bookname LIKE myBookName;
12    IF mycount!=0 THEN
13      SET SQL_SAFE_UPDATES=0; /* DELETE, UPDATE 연산에 필요한 설정문 */
14      UPDATE Book SET price = myPrice
15        WHERE bookname LIKE myBookName;
16    ELSE
17        INSERT INTO Book(bookid, bookname, publisher, price)
18          VALUES(myBookID, myBookName, myPublisher, myPrice);
19    END IF;
20   END;
21   //
22  delimiter ;

A -- BookInsertOrUpdate
B CALL BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 25000);
C SELECT * FROM Book;
D -- BookInsertOrUpdate
E CALL BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 20000);
F SELECT * FROM Book; --

 

0~22 프로시저 정의문은 CREATE PROCEDURE-BEGIN-END 형식

 

-프로시저 이름 뒤에 인자를 정의, BEGIN-END에는 책의 중복 여부를 찾아 수정 혹은 삽입 작업을 하는 SQL문

-01행은 madang 데이터베이스 사용

-02행은 프로시저 종료가 //에서 끝남을 알리는 delimiter

-03~07행은 프로시저 이름과 매개변수를 정의하는 문장

-IN/OUT을 생략하면 기본값은 IN으로 설정

-09행은 지역 변수 mycount를 선언하는 문장

-10~11행은 myBookName과 같은 이름의 책이 몇 권 있는지 확인하여 mycount 변수에 값을 저장하는 문장

-12~19행은 IF 조건문으로 삽입하려는 도서이름(myBookName)과 같은 도서가 있는지 확인하여 있으면 가격(price)만 새-로운 가격(myprice)으로 수정하고 아니면 투플 전체를 새로 삽입

 

A~F 정의된 프로시저를 CALL

 

-B행 호출 결과 Book 테이블에 bookid가 15인 새로운 투플 한 개가 추가된다.

-E행 호출 결과 이미 같은 책 제목이 있으므로 가격만 변경된다.

 

3.결과를 반환하는 프로시저

-저장 프로시저는 주어진 작업을 수행하고 작업을 완료하기도 하지만 함수와 같이 계산된 결과를 반환할 수도 있다.

 

ex)

01 delimiter //

02 CREATE PROCEDURE AveragePrice(

03  OUT AverageVal INTEGER)

04 BEGIN

05  SELECT AVG(price) INTO AverageVal

06  FROM Book WHERE price IS NOT NULL;

07 END;

08 //

09 delimiter ;

 

A /* 프로시저 AveragePrice를 테스트 하는 부분 */

B CALL AveragePrice(@myValue);

C SELECT @myValue;

 

코드의 01~09행은 AveragePrice 프로시저를 정의하는 부분이고, A~C행은 AveragePrice 프로시저를 실행하는 부분

 

-01~09 프로시저 정의문은 CREATE PROCEDURE-BEGIN-END형식

-프로시저 이름 뒤에 인자를 정의하였고, BEGIN-END에는 책의 평균값을 계산하여 출력 변수에 저장하는 SQL문

-02~03행은 프로시저 이름과 매개변수를 정의하는 문장

-OUT은 출력 매개변수라는 의미

-05~06행은 책의 평균값을 계산하여 매개변수 AverageVal에 저장하는 문장

-INTO 문은 변수에 값을 저장할 때 사용

 

A~C 정의된 프로시저를 테스트하기 위하여 만든 프로시저

 

-B행은 변수 myValue를 정의하였고 AveragePrice를 호출하엿따.

-호출 후 결과는 매개변수 myValue에 저장

-C행은 myValue 값을 출력하는 문장

 

4.커서를 사용하는 프로시저

-SQL 문의 실행 결과가 다중행 또는 다중열일 경우 프로그램에서는 한 행씩 처리

-커서(cursor)는 실행 결과 테이블을 한번에 한 행씩 처리하기 위하여 테이블의 행을 순서대로 가리키는데 사용

-커서 관련 키워드로는 CURSOR, OPEN, FETCH, CLOSE 등이 있다.

 

CURSOR<cursor 이름>IS<커서 정의>

DECLARE<cursor 이름>CURSOR FOR→커서를 생성

 

OPEN<cursor 이름>→커서의 사용을 시작

 

FETCH<cursor 이름>INTO<변수> → 행 데이터를 가져옴

 

CLOSE<cursor 이름> → 커서의 사용을 끝냄

 

01 delimiter //

02 CREATE PROCEDURE Interest()

03 BEGIN

04   DECLARE myInterest INTEGER DEFAULT 0.0;

05   DECLARE price INTEGER;

06   DECLARE endOfRow Boolean DEFAULT FALSE;

07   DECLARE InterestCursor CURSOR FOR

08      SELECT saleprice FROM Orders;

09   DECLARE CONTINUE handler

10      FOR NOT FOUND SET endOfRow=TRUE;

11   OPEN InterestCursor;

12   cursor_loop: LOOP

13     FETCH InterestCursor INTO Price;

14     IF endOfRow THEN LEAVE cursor_loop;

15     END IF;

16     IF Price >= 30000 THEN

17       SET myInterest = myInterest + Price * 0.1;

18     ELSE

19       SET myInterest = myInterest + Price * 0.05;

20     END IF;

21   END LOOP cursor_loop;

22   CLOSE InterestCursor;

23   SELECT CONCAT(' 전체 이익 금액 = ', myInterest);

24   END;

25   //

26   delimiter ;

 

A   /* Interest 프로시저를 실행하여 판매된 도서에 대한 이익금을 계산 */

B   CALL Interest();

 

-01~26 프로시저 정의문은 CREATE PROCEDURE-BEGIN-END 형식이다. 프로시저를 선언하고 BEGIN-END에는 책의 이익금을 계산하여 추력하는 SQL 문을 작성

-02행은 프로시저를 선언하는 부분

-04~06행은 지역 변수를 선언하는 부분

-07~10행은 InterestCursor라는 이름의 CURSOR 변수를 선언

-InterestCursor는 SELECT 문의 결과 테이블을 차례대로 가리키는 포인터로 맨 처음에는 첫 번째 투플을 가리킨다.

-CURSOR 문 시작과 끝을 다루기 위해 endOfRow 변수를 선언

-11행은 커서의 사용을 알리는(OPEN)문장

-12~21행은 LOOP 반복문이며 14행의 IF문장에서 endOfRow 조건을 만족하면 빠져나옴

-13행은 다음 투플을 가져오는 FETCH문

-16~20행은 IF 조건에 따라 이익금을 myInterest에 저장하는 문장

-22행은 커서의 사용을 끝내는 CLOSE 문이고

-23행은 myInterest 결과 값을 출력하는 문장

 

A~B 정의된 프로시저를 CALL 문으로 호출하는 구문이다.

-매개변수 없이 바로 호출되며 실행결과는 도서의 판매이익금

 

※트리거(Trigger)

-데이터의 변경(INSERT,DELETE,UPDATE) 문이 실행될 때 자동으로 같이 실행되는 프로시저

-데이터의 변경문이 처리되는 세 가지 시점,즉 실행 전(BEFORE), 대신하여(INSTEAD OF), 실행 후에(AFTER) 동작

-DBMS제조사에 따라 트리거의 정의가 많이 다르다.

 

-데이터의 변경(삽입,삭제,수정)이 일어날 때 부수적으로 필요한 작업인 데이터의 기본값 제공, 데이터 제약 준수, SQL 뷰의 수정, 참조 무결성 작업 등을 수행한다.

 

※사용자 정의 함수

-수학의 함수와 마찬가지로 입력된 값을 가공하여 결과 값을 되돌려준다.

-사용자가 직접 필요한 기능을 함수로 만들어 사용한다.

-프로시저와 비슷해 보이지만 프로시저는 CALL 명령에 의해 실행되는 독립된 프로그램이고, 사용자 정의 함수는 SELECT 문이나 프로시저 내에서 호출되어 SQL 문이나 프로시저에 그 값을 제공하는 용도로 사용

-MySQL에서 작성할 수 있는 사용자 정의 함수는 단일 값을 돌려주는 스칼라 함수가 일반적이다.