1. 데이터베이스프로그래밍의개념 2. PL-SQL 3. 데이터베이스연동자바프로그래밍 4. 데이터베이스연동웹프로그래밍

Transcription

1 Chapter 05 데이터베이스프로그래밍... 오라클로배우는데이터베이스개론과실습

2 1. 데이터베이스프로그래밍의개념 2. PL-SQL 3. 데이터베이스연동자바프로그래밍 4. 데이터베이스연동웹프로그래밍

3 데이터베이스프로그래밍의개념을이해한다. PL-SQL의문법과사용방법을알아본다. 자바프로그램과데이터베이스를연동하는방법을알아본다. JSP 프로그램과데이터베이스를연동하는방법을알아본다.

4 01. 데이터베이스프로그래밍의개념 n 프로그래밍 이란프로그램을설계하고소스코드를작성하여디버깅하는과정 n 데이터베이스프로그래밍 : DBMS에데이터를정의하고저장된데이터를읽어와데이터를변경하는프로그램을작성하는과정. 일반프로그래밍과는데이터베이스언어인 SQL을포함한다는점이다름 SQL 전용프로그램 (SQL) 응용프로그램 (SQL + 자바 ) DBMS DBMS DB DB (a) SQL Interface (b) 삽입프로그래밍 그림 5-1 데이터베이스프로그래밍

5 01. 데이터베이스프로그래밍의개념 n 데이터베이스프로그래밍방법 Œ SQL 전용언어를사용하는방법 SQL 자체의기능을확장하여변수, 제어, 입출력등의기능을추가한새로운언어를사용하는방법 Oracle 은 PL/SQL 언어를사용하며, SQL Server 는 T-SQL 이라는언어를사용함 일반프로그래밍언어에 SQL 을삽입하여사용하는방법 자바, C, C++ 등일반프로그래밍언어에 SQL 삽입하여사용하는방법일반프로그래밍언어로작성된응용프로그램에서데이터베이스에저장된데이터를관리, 검색함삽입된 SQL문은 DBMS의컴파일러가처리함. Ž 웹프로그래밍언어에 SQL 을삽입하여사용하는방법 호스트언어가 JSP, ASP, PHP 등웹스크립트언어인경우 4GL(4th Generation Language) 데이터베이스관리기능과비주얼프로그래밍기능을갖춘 GUI 기반소프트웨어개발도구 를사용하여프 로그래밍하는방법. Delphi, Power Builder, Visual Basi 등이있음.

6 01. 데이터베이스프로그래밍의개념 그림 5-2 DBMS 플랫폼과데이터베이스프로그래밍의유형

7 01. 데이터베이스프로그래밍의개념 표 5-1 DBMS 의종류와특징 특징 Access SQL Server Oracle MySQL DB2 SQLite 제조사 마이크로소프트사 마이크로소프트사 오라클사오라클사 IBM 사 리처드힙 ( 오픈소스 ) 운영체제기반윈도우윈도우 윈도우, 유닉스, 리눅스 윈도우, 유닉스, 리눅스 유닉스 모바일 OS ( 안드로이드, ios 등 ) 용도 개인용 DBMS 윈도우기반기업용 DBMS 대용량데이터베이스를위한응용 소용량데이터베이스를위한응용 대용량데이터베이스를위한응용 모바일전용데이터베이스

8 02. PL/SQL n n n n 프로시저트리거사용자정의함수 PL/SQL 문법요약

9 02. PL/SQL n n n Procedural Language/Structured Query Language의줄임말로데이터베이스응용프로그램을작성하는데사용하는오라클의 SQL 전용언어 SQL 전용언어로 SQL 문에변수, 제어, 입출력등의프로그래밍기능을추가하여 SQL 만으로처리하기어려운문제를해결함 PL/SQL은 SQL Developer에서바로작성하고컴파일한후결과를실행함 프로그래머 오라클 PL/SQL 개발환경 5 DB 그림 5-3 PL/SQL 개발환경

10 1. 프로시저 v 실행버튼 y객체확인 u 프로시저정의 x실행결과 그림 5-4 프로시저를정의하는과정

11 1. 프로시저 n 프로시저를정의하려면 CREATE PROCEDURE 문을사용 n 정의방법 P L/SQL은선언부와실행부 (BEGIN-END) 로구성됨선언부에서는변수와매개변수를선언하고, 실행부에서는프로그램로직을구현 매개변수 (parameter) 는저장프로시저가호출될때그프로시저에전달되는값 변수 (variable) 는저장프로시저나트리거내에서사용되는값 소스코드에대한설명문은 /* 와 */ 사이에기술만약설명문이한줄이면이중대시 (--) 기호다음에기술해도됨

12 1.1 삽입작업을하는프로시저 n 프로시저로데이터를삽입작업을하면좀더복잡한조건의삽입작업을인자값만바꾸어수 행할수도있고, 저장해두었다가필요할때마다호출하여사용가능 예제 5-1 Book 테이블에한개의투플을삽입하는프로시저 (InsertBook) 01 CREATE OR REPLACE PROCEDURE InsertBook( 02 mybookid IN NUMBER, 03 mybookname IN VARCHAR2, 04 mypublisher IN VARCHAR2, 05 myprice IN NUMBER) 06 AS 07 BEGIN 08 INSERT INTO Book(bookid, bookname, publisher, price) 09 VALUES(myBookID, mybookname, mypublisher, myprice); 10 END; A /* 프로시저 InsertBook 을테스트하는부분 */ B EXEC InsertBook(13, ' 스포츠과학 ', ' 마당과학서적 ', 25000); C SELECT * FROM Book;

13 1.1 삽입작업을하는프로시저 그림 5-5 InsertBook 프로시저를실행한후 Book 테이블

14 1.2 제어문을사용하는프로시저 n PL/SQL 의제어문은어떤조건에서어떤코드가실행되어야하는지를제어하기위 한문법으로, 절차적언어의구성요소를포함함 표 5-2 PL/SQL 의제어문 구문의미문법 BEGIN-END IF-ELSE FOR WHILE RETURN PL/SQL 문을블록화시킴 중첩가능 조건의검사결과에따라문장을선택적으로수행 counter 값이범위내에있을경우 FOR 문의블록을실행 조건이참일경우 WHILE 문의블록을실행 프로시저를종료 상태값을정수로반환가능 BEGIN { SQL 문 } END IF < 조건 > SQL 문 [ ELSE SQL 문 ] END IF; FOR counter IN < 범위 > {SQL 문 } END LOOP WHILE < 조건 > { SQL 문 BREAK CONTINUE } END LOOP RETURN [ < 정수 > ]

15 1.2 제어문을사용하는프로시저 예제 5-2 동일한도서가있는지점검한후삽입하는프로시저 (BookInsertOrUpdate) 01 CREATE OR REPLACE PROCEDURE BookInsertOrUpdate( 02 mybookid NUMBER, 03 mybookname VARCHAR2, 04 mypublisher VARCHAR2, 05 myprice INT) 06 AS 07 mycount NUMBER; 08 BEGIN 09 SELECT COUNT(*) INTO mycount FROM Book 10 WHERE bookname LIKE mybookname; 11 IF mycount!=0 THEN 12 UPDATE Book SET price = myprice 13 WHERE bookname LIKE mybookname; 14 ELSE 15 INSERT INTO Book(bookid, bookname, publisher, price) 16 VALUES(myBookID, mybookname, mypublisher, myprice); 17 END IF; 18 END; A /* BookInsertOrUpdate 프로시저를실행하여테스트하는부분 */ B EXEC BookInsertOrUpdate(15, ' 스포츠즐거움 ', ' 마당과학서적 ', 25000); C SELECT * FROM Book; /* 15 번투플삽입결과확인 */ D /* BookInsertOrUpdate 프로시저를실행하여테스트하는부분 */ E EXEC BookInsertOrUpdate(15, ' 스포츠즐거움 ', ' 마당과학서적 ', 20000); F SELECT * FROM Book; /* 15 번투플가격변경확인 */

16 1.2 제어문을사용하는프로시저 그림 5-6 BookInsertOrUpdate 프로시저를실행한후 Book 테이블

17 1.3 결과를반환하는프로시저 예제 5-3 Book 테이블에저장된도서의평균가격을반환하는프로시저 (AveragePrice) 01 CREATE OR REPLACE PROCEDURE AveragePrice( 02 AverageVal OUT NUMBER) 03 AS 04 BEGIN 05 SELECT AVG(price) INTO AverageVal FROM Book WHERE price IS NOT 06 NULL; 07 END; A /* 프로시저 AveragePrice를테스트하는부분 */ B SET SERVEROUTPUT ON ; C DECLARE D AverageVal NUMBER; E BEGIN F AveragePrice(AverageVal); G DBMS_OUTPUT.PUT_LINE(' 책값평균 : ' AverageVal); H END; 그림 5-7 AveragePrice 프로시저를실행한결과

18 1.4 커서를사용하는프로시저 n 커서 (cursor) 는실행결과테이블을한번에한행씩처리하기위하여테이블의행 을순서대로가리키는데사용함 표 5-3 커서와관련된키워드

19 1.4 커서를사용하는프로시저 예제 5-4 Orders 테이블의판매도서에대한이익을계산하는프로시저 (Interest) 01 CREATE OR REPLACE PROCEDURE Interest 02 AS 03 myinterest NUMERIC; 04 Price NUMERIC; 05 CURSOR InterestCursor IS SELECT saleprice FROM Orders; 06 BEGIN 07 myinterest := 0.0; 08 OPEN InterestCursor; 09 LOOP 10 FETCH InterestCursor INTO Price; 11 EXIT WHEN InterestCursor%NOTFOUND; 12 IF Price >= THEN 13 myinterest := myinterest + Price * 0.1; 14 ELSE 15 myinterest := myinterest + Price * 0.05; 16 END IF; 17 END LOOP; 18 CLOSE InterestCursor; 19 DBMS_OUTPUT.PUT_LINE(' 전체이익금액 = ' myinterest); 20 END; A /* Interest 프로시저를실행하여판매된도서에대한이익금을계산 */ B SET SERVEROUTPUT ON; C EXEC Interest;

20 1.4 커서를사용하는프로시저 그림 5-8 Interest 프로시저를실행한결과

21 2. 트리거 n 트리거 (trigger) 는데이터의변경 (INSERT, DELETE, UPDATE) 문이실행될때자동 으로따라서실행되는프로시저 BEFORE 트리거 DATA 변경문 (INSERT, DELETE, UPDATE) AFTER 트리거 그림 5-9 데이터변경과트리거의수행

22 2. 트리거 예제 5-5 새로운도서를삽입한후자동으로 Book_log 테이블에삽입한내용을기록하는트리거 A /* 실습을위한 Book_log 테이블생성 */ B CREATE TABLE Book_log( C bookid_l NUMBER, D bookname_l VARCHAR2(40), E publisher_l VARCHAR2(40), F price_l NUMBER); /* 파일명 : AfterInsertBook.sql */ 02 CREATE OR REPLACE TRIGGER AfterInsertBook 03 AFTER INSERT ON Book FOR EACH ROW 04 DECLARE 05 average NUMBER; 06 BEGIN 07 INSERT INTO Book_log 08 VALUES(:new.bookid, :new.bookname, :new.publisher, :new.price); 09 DBMS_OUTPUT.PUT_LINE(' 삽입투플을 Book_log 테이블에백업..'); 10 END; G /* 삽입한내용을기록하는트리거확인 */ H INSERT INTO Book VALUES(14, ' 스포츠과학 1', ' 이상미디어 ', 25000); I SELECT * FROM Book WHERE bookid='14'; J SELECT * FROM Book_log WHERE bookid_l='14'; /* 결과확인 */

23 2. 트리거 G /* 삽입한내용을기록하는트리거확인 */ H INSERT INTO Book VALUES(14, ' 스포츠과학 1', ' 이상미디어 ', 25000); I SELECT * FROM Book WHERE bookid='14'; J SELECT * FROM Book_log WHERE bookid_l='14'; /* 결과확인 */ 그림 5-10 Book 테이블에투플을삽입하여트리거가실행된결과

24 3. 사용자정의함수 n 사용자정의함수는수학의함수와마찬가지로입력된값을가공하여결과값을되 돌려줌 예제 5-6 판매된도서에대한이익을계산하는함수 (fnc_interest) 01 CREATE OR REPLACE FUNCTION fnc_interest( 02 price NUMBER) RETURN INT 03 IS 04 myinterest NUMBER; 05 BEGIN 06 /* 가격이 30,000원이상이면 10%, 30,000원미만이면 5% */ 07 IF Price >= THEN myinterest := Price * 0.1; 08 ELSE myinterest := Price * 0.05; 09 END IF; 10 RETURN myinterest; 11 END; A /* Orders 테이블에서각주문에대한이익을출력 */ B SELECT custid, orderid, saleprice, fnc_interest(saleprice) interest C FROM Orders;

25 3. 사용자정의함수 그림 5-11 Orders 테이블의건별이익금계산

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

27 4. PL/SQL 문법요약 표 5-5 PL/SQL 의기본문법 - 구분 Data Definition Language ( 데이터정의어 ) Data Manipulation Language ( 데이터조작어 ) Data Types( 데이터타입 ) Variables( 변수 ) CREATE TABLE CREATE PROCEDURE CREATE FUNCTION CREATE TRIGGER ALTER, DROP SELECT DELETE NUMBER(n), VARCHAR2(n), DATE DECLARE 문으로선언치환 ( := 사용 ) 명령어 INSERT UPDATE Operator( 연산자 ) 산술연산자 (+, -, *, /) 비교연산자 (=, <, >, >=, <=, <>) 문자열연산자 ( ) 논리연산자 (NOT, AND, OR) Language Element( 주석 ) - -, /* */ Built-in Function( 내장함수 ) 숫자함수 (ABS, CEIL, FLOOR, POWER 등 ) 집계함수 (AVG, COUNT, MAX, MIN, SUM) 날짜함수 (SYSDATE, NEXT_DAY, TO_CHAR 등 ) 문자함수 (CHR, LENGTH, LOWER, SUBSTR 등 ) Control of Flow( 제어문 ) Data Control Language ( 데이터제어어 ) BEGIN-END IF-THEN-ELSE FOR LOOP END LOOP WHILE LOOP-END LOOP, EXIT GRANT REVOKE

28 03. 데이터베이스연동자바프로그래밍 n n 소스코드설명 프로그램실습

29 03. 데이터베이스연동자바프로그래밍 표 5-6 데이터베이스연동자바프로그래밍실습환경 항목 프로그램 데이터베이스프로그램 오라클 11g r2 자바컴파일러 JDK 버전 7 데이터베이스와자바를연결하는드라이버 JDBC 드라이버 ( 파일이름 ojdbc6.jar)

30 1. 소스코드설명 표 5-7 데이터베이스접속자바클래스 (java.sql) 클래스구분클래스혹은인터페이스주요메소드이름메소드설명 java.lang Class Class forname(< 클래스이름 >) < 클래스이름 > 의 JDBC 드라이버를로딩 DriverManager Connection getconnection (url, user, password) 데이터베이스 Connection 객체를생성 Connection Statement createstatement() void close() SQL 문을실행하는 Statement 객체를생성 Connection 객체연결을종료 java.sql Statement ResultSet executequery (String sql) ResultSet executeupdate (String sql) SQL 문을실행해서 ResultSet 객체를생성 INSERT/DELETE/UPDATE 문을실행해서 ResultSet 객체를생성 boolean first() 결과테이블에서커서가처음투플을가리킴 ResultSet boolean next() int getint(<int>) 결과테이블에서커서가다음투플을가리킴 <int> 가가리키는열값을정수로반환 String getstring(<int>) <int> 가가리키는열값을문자열로반환

31 1. 소스코드설명 오라클 DBMS Driver Manager 객체 Connection 객체 Statement 객체 ResultSet 객체 데이터베이스 (Madang) 자바프로그램 자바프로그램 (a) 자바의데이터베이스연동객체 ResultSet SQL Statement Driver manager Connection DB 처리 ResultSet SQL Statement Connection DB 처리 (b) 객체간의호출순서 그림 5-12 데이터베이스연결자바객체들의호출관계

32 2. 프로그램실습 표 5-8 자바프로그램실습단계 단계세부단계프로그램참조 [1 단계 ] DBMS 설치및환경설정 1 오라클 11g r2 설치 2 오라클접속을위한사용자 (madang) 생성 오라클 11g 부록 A.1~A.3 부록 B.3 [2 단계 ] 데이터베이스준비 1 마당서점데이터베이스준비 (demo_madang.sql) 부록 B.3 1 자바컴파일러설치 (A) 명령프롬프트이 용 2 JDBC 드라이버설치 3 자바프로그램준비 (booklist.java) JDK JDBC 부록 C.1~C.3 [3 단계 ] 4 컴파일및실행 자바실행 (B) 이클립스이용 1 자바와이클립스개발도구설치 2 JDBC 드라이버설치 3 자바프로그램준비 (booklist.java) 4 컴파일및실행 JDK Eclipse JDBC 부록 C.1~C.4

33 2. 프로그램실습 1 단계 - 2 오라클사용자인증 (madang, madang) 1 단계 -3 3 단계 -4 3 단계 -3 PORT 1521 번 자바 CLASS 자바프로그램 1 단계 -1 오라클 DBMS 2 단계 - 2 데이터베이스 (Madang) JDBC Library + 자바컴파일러 3 단계 -2 3 단계 -1 그림 5-14 데이터베이스연동자바프로그램의실행흐름도

34 2.1 [1 단계 ] DBMS 설치및환경설정 Œ 오라클 11g 설치 오라클접속을위한사용자 (madang) 설정

35 2.2 [2 단계 ] 데이터베이스준비 Œ 마당서점데이터베이스준비 (demo_madang.sql) 마당서점데이터베이스의샘플데이터는이미 3 장에서설치하였다. 이책의순서대로실습을 진행하지않았다면부록 B.3 을참고하여설치함.

36 2.3 [3 단계 (A)] 자바실행 명령프롬프트를이용하는방법 Œ 자바컴파일러설치부록 C.2 를참고하여설치 JDBC 드라이버설치부록 C.3 을참고하여설치 Ž 자바프로그램준비 (booklist.java) booklist.java 프로그램의소스코드는앞에서설명. booklist.java 파일은메모장에서작성하거나 예제소스폴더의 booklist.java 를가져와사용 ➍ 컴파일및실행

37 2.4 [3 단계 (B)] 자바실행 이클립스를이용하는방법 Œ 이클립스개발도구설치부록 C.4 를참고하여설치 JDBC 드라이버설치부록 C.3 을참고하여설치 Ž ➍ 자바프로그램준비 (booklist.java) 285~286 쪽참고하여설치 컴파일및실행 그림 5-23 이클립스에서 booklist.java 실행결과화면

38 04. 데이터베이스연동웹프로그래밍 n n 소스코드설명 프로그램실습

39 04. 데이터베이스연동웹프로그래밍 표 5-9 데이터베이스연동웹프로그래밍실습환경 항목 프로그램 데이터베이스프로그램 오라클 11g r2 자바컴파일러 JDK 버전 7 웹서버 데이터베이스와자바를연결하는드라이버 톰캣 JDBC 드라이버 ( 파일이름 ojdbc6.jar)

40 1. 소스코드설명 n JSP 프로그램은 HTML 태그에 JSP 스크립트를끼워넣어작성하는데, JSP 스크립 트부분은 <%... %> 에넣어서실행시킴. booklist.jsp bookview.jsp 웹서버 ( 톰캣 ) booklist.jsp 프로그램 bookview.jsp 프로그램 그림 5-24 booklist.jsp 와 bookview.jsp 의호출관계와웹에서실행된화면

41 2. 프로그램실습 표 5-10 JSP 프로그램실습단계 단계세부단계프로그램참조 [1 단계 ] DBMS 설치및환경설정 1 오라클 11g r2 설치 2 오라클접속을위한사용자 (madang) 생성 오라클 11g 부록.1~A.3 부록 B.3 [2 단계 ] 데이터베이스준비 1 마당서점데이터베이스준비 (demo_madang.sql) 부록 B.3 [3 단계 ] JSP 실행 1 자바컴파일러, 톰캣설치 2 JDBC 드라이버설치 3 JSP 프로그램준비 (booklist.jsp, bookview.jsp) 4 실행 J 아 톰캣 JDBC 부록 C.1~C.3 부록 C.5

42 2. 프로그램실습 1 단계 - 2 오라클사용자인증 (madang / madang) 1 단계 -3 3 단계 단계 - 4 PORT 1521 번 JSP 프로그램 웹브라우저 1 단계 -1 오라클 DBMS 1 단계 - 2 데이터베이스 (Madang) JDBC Library + 톰캣 ( 웹서버 ) 3 단계 -2 3 단계 -1 그림 5-21 데이터베이스연동 JSP 프로그램의실행흐름도

43 2. 프로그램실습 n [1 단계 ] DBMS 설치및환경설정 n [2 단계 ] 데이터베이스준비 n Œ [3 단계 ] JSP 실행 자바컴파일러, JDBC 드라이버설치 톰캣설치부록 C.5 를참고하여설치

44 2. 프로그램실습 Ž JSP 프로그램준비 (booklist.jsp, bookview.jsp) booklist.jsp 파일과 bookview.jsp 파일을예제소스에서가져와사용 작성된프로그램은톰캣기본폴더에 booklist 폴더를생성하고저장 그림 5-26 booklist.jsp, bookview.jsp 파일저장

45 2. 프로그램실습 실행 그림 5-23 booklist.jsp 실행화면 그림 5-24 bookview.jsp 실행화면

46 요약 1. 데이터베이스프로그래밍 2. 삽입프로그래밍 3. PL/SQL(Procedural Language/Structured Query Language) 4. 저장프로시저 5. 커서 6. 트리거 7. 연동 8. JDBC(Java Database Connectivity)