Chapter 05 데이터베이스 프로그래밍
데이터베이스 프로그래밍의 개념
- ‘데이터베이스 프로그래밍’이란 DBMS에 데이터를 정의하고 저장된 데이터를 읽어와 데이터를 변경하는 프로그램을 작성하는 과정
대표적인 데이터베이스 프로그래밍
- SQL 전용 언어를 사용하는 방법
- SQL 자체의 기능을 확장하여 변수, 제어, 입출력 등의 기능을 추가한 새로운 언어를 사용하는 방법
- MySQL: 저장 프로그램, 오라클: PL/SQL, SQL Server: T-SQL이라는 용어 사용
- 장점: 데이터베이스를 다루는 능력이 뛰어나고 다루는 방법이 쉽다.
- 단점: 사용자 인터페이스(GUI)를 구축하는 기능이 없어 독립적으로 사용하기보다 프로시저나 함수 등으로 구현하여 다른 프로그램에서 호출해 사용한다.
- 일반 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법
- 자바, C, C++ 등 일반 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법
- 삽입된 SQL 문은 DBMS의 컴파일러가 처리
- 일반 프로그래밍 언어로 작성된 응용 프로그램에서 데이터를 관리 및 검색할 수 있음
- SQL 단독으로 사용할 때보다 복잡한 로직 구현이 용이
- 웹 프로그래밍 언어에 SQL을 삽입하여 사용하는 방법
- JSP, PHP, ASP 등 웹 스크립트 언어에 SQL을 삽입하여 사용하는 방법
- 웹 프로그래밍 언어로 작성된 프로그램에서 데이터를 관리 및 검색할 수 있음
- SQL 문의 결과는 웹 브라우저에서 확인
- 아파치와 같은 웹 서버가 데이터베이스 연동을 지원
- 4GL(4th Generation Language)
- 4세대 언어의 일종으로 데이터베이스 관리 기능과 비주얼 프로그래밍 기능을 갖춘 ‘GUI 기반 소프트웨어 개발 도구’를 사용하여 프로그래밍
- 개발도구로는 Delphi, Power Builder, Visual Basic 등이 있음
- 이 그림은 데이터베이스 응용 시스템을 ‘하드웨어 - 운영체제 - DBMS - 프로그램 환경’으로 계층화시키고, 층간에 관계를 표현한 것
- 맨 아래의 하드웨어 계층과 운영체제 계층은 DBMS가 운영되는 기반 구조 즉 플랫폼이다.
DBMS의 종류와 특징
| 특징 | Access | SQL Server | Oracle | MySQL | DB2 | SQLite |
|---|---|---|---|---|---|---|
| 제조사 | 마이크로소프트 | 마이크로소프트 | 오라클 | 오라클 | IBM | 리처드 힙 (오픈소스) |
| 운영체제 기반 |
윈도우 | 윈도우, 리눅스 | 윈도우, 유닉스, 리눅스 | 윈도우, 유닉스, 리눅스 | 유닉스 | 모바일 OS (안드로이드, IOS 등) |
| 특징 | 개인용 DBMS | 윈도우 기반 기업용 DBMS |
대용량 데이터베이스를 위한 응용 | 소용량 데이터 베이스를 위한 응용 | 대용량 데이터 베이스를 위한 응용 | 모바일 전용 데이터베이스 |
저장 프로그램
- MySQL 전용 언어
저장 프로그램에 대하여
- 저장 프로그램은 로직을 procedure(프로시저)로 구현하여 객체 형태로 사용
- 저장 프로그램은 일반 프로그래밍 언어에서 사용하는 함수와 비슷한 개념으로, 작업 순서가 정해진 독립된 프로그램의 수행 단위를 말하며 정의된 다음 MySQL(DBMS)에 저장되므로 저장 프로그램이라고 한다.
- 저장 프로그램은 저장 루틴(routine), 트리거(trigger), 이벤트(event)로 구성되며, 저장 루틴은 프로시저(procedure와 함수(function)로 나뉜다.
프로시저를 정의하려면 CREATE PROCEDURE 문을 사용한다.
정의 방법
- 프로시저는 선언부와 실행부(BEGIN-END)로 구성된다. 선언부에서는 변수와 매개변수를 선언하고 실행부에서는 프로그램 로직을 구현한다.
- 매개변수는 저장 프로시저가 호출될 때 그 프로시저에 전달되는 값이다.
- 변수는 저장 프로시저나 트리거 내에서 사용되는 값이다.
- 소스코드에 대한 설명문은
/* */사이에 기술한다.(한 줄이면--도 가능)
삽입 작업을 하는 프로시저
제어문을 사용하는 프로시저
- 저장 프로그램의 제어문은 어떤 조건에서 어떤 코드가 실행되어야 하는지를 제어하기 위한 문법
- 절차적 언어의 구성요소를 포함
- BEGIN-END 문, IF-ELSE 문, WHERE 문, RETURN 문 등의 코드 블록에 연산을 하기 위한 구문들을 포함
저장 프로그램의 제어문
| 구문 | 의미 | 문법 |
|---|---|---|
| DELIMITER | 구문 종료 기호 설정 | DELIMITER(기호) |
| BEGIN-END | 프로그램 문을 블록화시킴 중첩 가능 |
BEGIN {SQL 문} END |
| IF-ELSE | 조건의 검사 결과에 따라 문장을 선택적으로 수행 | IF <조건> THEN {SQL 문} [ELSE {SQL 문] END IF; |
| LOOP | LEAVE 문을 만나기 전까지 LOOP을 반복 | [label:]LOOP {SOL |
| WHILE | 조건이 참일 경우 WHILE 문의 블록을 실행 | WHILE <조건> DO {SQL 문 | BREAK | CONTINUE} END WHILE |
| REPEAT | 조건이 참일 경우 REPEAT 문의 블록을 실행 | [label:] REPEAT {SQL 문 | BREAK | CONTINUE} UNTIL <조건> END REPEAT [label:] |
| RETURN | 프로시저를 종료 상태값 반환 가능 |
RETURN[<식>] |
동일한 도서가 있는지 점검한 후 삽입하는 프로시저
- BookInsertOrUpdate 프로시저를 실행한 후 Book 테이블
CALL BookInsertOrUpdate(13, '스포츠과학', '마당과학서적', 25000); SELECT * FROM Book;
```sql
CALL BookInsertOrUpdate(13, '스포츠과학', '마당과학서적', 20000);
SELECT * FROM Book;
```
<img width="304" alt="스크린샷 2023-07-25 오후 4 07 16" src="https://github.com/Hoya324/2023-DBStudy/assets/96857599/70517273-fb17-4bfb-bdbf-4497f688b711">결과를 반환하는 프로시저
- 저장 프로시저는 주어진 작업을 수행하고 잡업을 완료하기도 하지만 함수와 같이 계산된 결과를 반환할 수도 있다.
- Book 테이블에 저장된 도서의 평균가격을 반환하는 프로시저
커서를 사용하는 프로시저
- SQL 문의 실행 결과가 다중행 또는 다중열일 경우 프로그램에서는 한 행씩 처리한다.
- 커서는 실행 결과 테이블을 한 번에 한 행씩 처리하기 위하여 테이블의 행을 순서대로 가리키는 데 사용한다.
- 커서에 관련된 키워드로는 CURSOR, OPEN, FETCH, CLOSE 등이 있다.
| 키워드 | 역할 |
| --- | --- |
| CURSOR <cursor 이름> IS <커서 정의>
DECLARE <cursor 이름> CURSOR FOR | 커서를 생성 |
| OPEN <cursor 이름> | 커서의 사용을 시작 |
| FETCH <cursor 이름> INTO <변수> | 행 데이터를 가져옴 |
| CLOSE <cursor 이름> | 커서의 사용을 끝냄 |- Orders 테이블의 판매 도서에 대한 이익를 계산하는 프로시저
트리거
- 트리거(trigger)는 데이터 변경(INSERT, DELETE, UPDATE) 문이 실행될 때 자동으로 같이 실행되는 프로시저를 말한다.
- 보통 트리거는 데이터의 변경문이 처리되는 세 가지 시점, 즉 BEFORE, INSTEAD OF, AFTER에 동작
- DBMS 제조사에 따라 트리거의 정의가 많이 다르다.
- 트리거는 데이터의 변경이 일어날 때 부수적으로 필요한 작업인 데이터의 기본값 제공, 데이터 제약 준수, SQL 뷰의 수정, 참조무결성 작업 등을 수행한다.
- 예) Book 테이블에 새로운 도서를 삽입할 때 삽입된 내용을 백업하기 위해 다른 테이블 Book_log에 삽입된 내용을 기록한다고 하자. Book 테이블에 INSERT 문을 수행하면서 같이 실행할 수도 있지만 사용자 입장에서는 번거롭기도 하고 보안상 백업을 감추어야할 경우도 있다.
- 이때 트리거를 사용하면 편하다.
- 신규 도서를 삽입한 후 자동으로 Book_log 테이블에 삽입한 내용을 기록하는 트리거
사용자 정의 함수
- 사용자 정의 함수는 수학의 함수와 마찬가지로 입력된 값을 가공하여 결과 값을 되돌려준다.
- 프로시저와 비슷해 보이지만 프로시저는 CALL 명령에 의해 실행되는 독립된 프로그램이고, 사용자 정의 함수는 SELECT 문이나 프로시저 내에 호출되어 SQL 문이나 프로시저에 그 값을 제공하는 용도로 사용된다.
- MySQL에서 작성할 수 있는 사용자 정의 함수는 단일 값을 돌려주는 스칼라 함수가 일반적
- 예) 판매된 도서의 이익을 계산하기 위해 각 주문 건별로 실제 판매가격인 saleprice를 입력받아 가격에 맞는 이익(30,000원 이상이면 10%, 30,000원 미만이면 5%)를 계산하여 반환하는 함수를 작성해보자
- 판매된 도서에 대한 이익을 계산하는 함수
프로시저, 트리거, 사용자 정의 함수의 공통점과 차이점
| 구분 | 프로시저 | 트리거 | 사용자 정의 함수 |
|---|---|---|---|
| 공통점 | 저장 프로시저 | 저장 프로시저 | 저장 프로시저 |
| 정의 방법 | CREATE | ||
| PROCEDURE 문 | CREATE TRIGGER 문 | CREATE FUNCTION 문 | |
| 호출 방법 | CALL 문으로 직접 호출 | INSERT, DELETE, UPDATE 문이 실행될 때 자동으로 실행됨 | SELECT 문에 포함 |
| 기능의 차이 | SQL 문으로 할 수 없는 복잡한 로직을 수행 | 기본값 제공, 데이터 제약 준수, SQL 뷰의 수정, 참조무결성 작업 등을 수행 | 속성 값을 가공하여 반환, SQL 문에서 직접 사용 |
저장 프로그램 문법 요약
| 구분 | 명령어 |
|---|---|
| 데이터 정의어 | CREATE TABLE CREATE PROCEDURE CREATE FUNCTION CREATE TRIGGER DROP |
| 데이터 조작어 | SELECT INSERT DELETE UPDATE |
| 데이터 타입 | INTEGER, VARCHAR(n), DATE |
| 변수 | DECLARE 문으로 선언 치환(SET, = 사용) |
| 연산자 | 산술연산자(+, -, *, /) 비교연산자(=, <,>.>=, <=, <>) 문자열연산자( |
| 주석 | —, /* */ |
| 내장 함수 | 숫자 함수(ABS, CEIL, FLOOR, POWER 등) 집계 함수(AVG, COUNT, MAX MIN, SUM) 날짜 함수(SYSDATE, DATE, DATNAME 등) 문자 함수(CHAR, LEFT, LOWER, SUBSTR 등) |
| 제어문 | BEGIN-END IF-THEN-ELSE WHILE, LOOP |
| 데이터 제어어 | GRANT REVOKE |
데이터베이스 연동 자바 프로그래밍
소스코드
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
// SQL 관련 클래스는 java.sql .*에 포함되어 있다.
public class BookList {
Connection con;
// 클래스 booklist를 선언한다. java.sql의 Connection 객체 con을 선언한다.
public BookList() {
String Driver="";
String url="jdbc:mysql://localhost:3306/madang?&serverTimezone=Asia/Seoul";
String userid="madang";
String pwd="Gh852852!";
// 접속변수를 초기화한다. url은 자바 드라이버 이름, 호스트명(localhost), 포트번호를 입력한다
// userid는 관리자(madang), pwd는 사용자의 비밀번호(madang)를 입력한다.
try { /* 드라이버를 찾는 과정 */
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("드라이버 로드 성공");
} catch(ClassNotFoundException e) {
e.printStackTrace();
}
// Class.forName()으로 드라이버를 로딩한다. 드라이버 이름을 Class.forName에 입력한다.
try { /* 데이터베이스를 연결하는 과정 */
System.out.println("데이터베이스 연결 준비...");
con= DriverManager.getConnection(url, userid, pwd);
System.out.println("데이터베이스 연결 성공");
} catch(SQLException e) {
e.printStackTrace();
}
}
// 접속 객체 con을 DriverManager.getConnection 함수로 생성한다.
// 접속이 성공하면 "데이터베이스 연결 성공"을 출력하도록 한다.
// 문자열 query에 수행할 SQL 문을 입력한다.
private void sqlRun() {
String query="SELECT * FROM Book"; /* SQL 문 */
try { /* 데이터베이스에 질의 결과를 가져오는 과정 */
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(query);
System.out.println(" BOOK NO \\tBOOK NAME \\t\\tPUBLISHER \\tPRICE ");
while(rs.next()) {
System.out.print("\\t"+rs.getInt(1));
System.out.print("\\t"+rs.getString(2));
System.out.print("\\t\\t"+rs.getString(3));
System.out.println("\\t"+rs.getInt(4));
}
con.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
public static void main(String args[]) {
BookList so = new BookList();
so.sqlRun();
}
}프로그램 실습
[1단계] DBMS 설치 및 환경설정
- MySQL 8.x 설치
- SQL 접속을 위한 사용자(madang)설정
[2단계] 데이터베이스 준비
- 마당서점 데이터베이스 준비(demo.madang.sql)
[3단계] 자바 실행(명령 프롬포트 이용)
- 자바 컴파일러 설치
- JDBC 드라이버 설치
- 자바 프로그램 준비(Booklist.java)
- 컴파일 및 실행
- 자바 실행(IntelliJ 이용)
- IntelliJ 개발도구 설치
- JDBC 드라이버 설치
- 자바 프로그램 준비(Booklist.java)
- 컴파일 및 실행
데이터베이스 연동 웹 프로그래밍
소스코드 설명
다음의 booklist.php는 Book 테이블에 저장된 도서를 읽어와 웹 브라우저에 출력하는 PHP 프로그램이다.
PHP 프로그램은 HTML 태그에 PHP 스크립트를 끼워 넣어 작성하는데, PHP 스크립트 부분은 에 넣어 실행한다.
bookview.php는 도서 한 권에 대한 상세 정보를 보는 프로그램이다.
booklist.php
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<h2><blockquote> 마당서점 도서목록 </blockquote></h2>
<?php
$conn = mysqli_connect('localhost', 'madang', 'madang', 'madang');
if (mysqli_connect_error($conn)){
echo 'Connection Error';
exit();
}
$sql = "SELECT * FROM Book";
$result = mysqli_query($conn, $sql);
echo '<table border=1><tr><td>BOOKNAME</td><td>PUBLISHER</td><td>PRICE</td></tr> ';
while($row = mysqli_fetch_array($result)) {
echo ('<tr><td><b>
<a href="bookview.php?id='.$row['bookid'].'">'
.$row['bookname'].'</a></b></td><td>'
.$row['publisher'].' </td><td> '
.$row['price'].'</td></tr>');
}
echo '</table><p>';
?>bookview.php
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<h2><blockquote> 마당서점 도서목록 </blockquote></h2>
<?php
$conn = mysqli_connect('localhost', 'madang', 'madang', 'madang');
if (mysqli_connect_error($conn)){
echo 'Connection Error';
exit();
}
$sql = "SELECT * FROM Book WHERE bookid='".$_GET['id']."'";
$result = mysqli_query($conn, $sql);
$id=$_GET['id'];
$row = mysqli_query($conn, $sql);
echo '<table border=1><tr><td>책번호</td><td>'.$id.'</td></tr>';
echo '<tr><td>책제목</td><td>'.$row['bookname'].'</td></tr>';
echo '<tr><td>출판사</td><td>'.$row['publisher'].'</td></tr>';
echo '<tr><td>가격</td><td>'.$row['price'].'</td></tr>';
echo '</table>';
echo '<a href="./booklist.php">목록보기</a>';
?>주요개념
연동이란?
- 어느 한 부분이 움직이면 다른 부분도 같이 움직인다는 의미로, 데이터베이스 응용에서는 일반 프로그램을 수행하여 DBMS를 동작시킨다는 의미이다.
- 연동은 자바 프로그램 혹은 웹 프로그램을 이용한다.
JDBC
- 자바는 객체지향 언어이기 때문에 객체를 호출하여 데이터베이스에 접속한다.
- 데이터베이스에 접속하는 API(Application Programming Interface)를 java.sql.*에서 제공한다.
- java.sql에 정의된 API는 각 DBMS 제조사에서 자신의 제품에 맞게 구현해서 제공하는데, 이를 JDBC(Java DataBase Connectivity) 드라이버라고 한다.
Reference
'Database' 카테고리의 다른 글
| DB | 정규화 (0) | 2023.08.28 |
|---|---|
| DB | 데이터 모델링 (1) | 2023.08.28 |
| DB | SQL 고급 (2) | 2023.08.27 |
| DB | SQL 기초 (0) | 2023.08.25 |
| DB | 관계 데이터 모델 (0) | 2023.08.25 |
