DB 프로그래밍 기법
- 데이터 베이스 명령어를 프로그래밍 언어에 임베딩하기
- Host Programming Language에 데이터베이스 구문이 Embed 된다.
- Precompiler, Preprocessor가 소스코드를 스캔, 데이터베이스 구문을 뽑아내 DBMS에서 실행한다.
- 임베드 SQL로도 부른다.
- SQL만 수정해도 코드 전체를 새로 컴파일해야 해, 변경이 쉽지 않다.
- 동적 쿼리의 생성이 불가능해진다.
EXEC로 시작하는 부분
- 데이터베이스 기능을 담은 라이브러리를 사용하기
- 프로그래밍 언어에 사용 가능한 라이브러리를 사용하는 것이다.
- 런타임에 쿼리가 생성되어 좀더 프로그램이 복잡하다.
- 런타임에 문법 확인이 이루어지기에 에러가 발생할 가능성이 있다.
예시
- ODBC : Open DataBase Connectivity
- JDBC : Java DataBase Connectivity
- 새로운 프로그래밍 언어를 만들기
- 데이터베이스 모델과 쿼리 언어에 호환되는 데이터베이스 전용 프로그래밍 언어
- 루프와 조건문을 포함함
- 새로이 배워야 한다는 부담스러움이 있다.
예시
- PL/SQL : Oracle Database
- T-SQL : SQL-server
JDBC
- DBMS와 연결/작동 하기 위한 API 집합
- 여러 DBMS와도 동작할수 있도록 설계되어 있다.
- 단 DBMS와 관련된 올바른 드라이버를 미리 명시적으로 로딩 해야 한다.
DBMS 드라이버 로딩
JDBC 구조
단어
Connection
- 특정 데이터베이스와 연결 정보를 가지는 Interface
- DriverManager에서 Connection 객체를 가지고 온다.
Statement
- SQL Query 를 DB에 전송하는 방식을 정의한 Interface
- Connection에서 가져온다.
ResultSet
- Select문의 실행 결과를 조회하는 방법을 정의한 Interface
PreparedStatement
- Statement의 하위 Interface
- SQL을 미리 컴파일해 실행속도를 높힘
CallableStatement
- PreparedStatement의 하위 Interface
- DBMS의 Stored Procedure를 호출합니다.
JDBC 작동 예시
Class.forName("oracle.jdbc.driver.OracleDriver")
// 오라클 데이터베이스의 JDBC 드라이버를 로딩하는것
DriverManager.getConnection("JDBC URL", "ID", "PW")
// 데이터베이스와 연결하기
conn.setAutoCommit(false)
// 자동으로 커밋 되는것 방지
stmt = conn.createStatement());
// SQL Queray 전송 위한 Statement의 생성
stmt.executeUpdate(SQL문자열);
// 해당 SQL DML을 실행한다, 결과로 영향을 받은 튜플의 갯수를 리턴한다.
// 위 경우는 테이블을 생성하는 것이기에 어떤 튜플도 영향을 받지 않아 0을 리턴한다.
conn.commit();
// 변경사항을 저장한다.
stmt.addBatch(SQL 문자열)
// 한번에 여러 DML을 실행하기 위해 SQL을 쌓아둔다.
stmt.executeBatch()
// 여러 DML을 한번에 실행한다, 결과는 여러 DML들에 대한 변경된 튜플의 갯수들을 배열로 리턴한다.
stmt.executeQuery(SQL문)
// 쿼리 실행 및 결과로 ResultSet을 받는다.
/*
ResultSet 메소드
1. first() - 첫번째 튜플을 가져온다.
2. next() - 다음 튜플을 가져온다, 튜플이 없으면 false가 리턴된다.
3. getInt(int) - 가리키는 Column의 값을 가져온다, 첫번째 Column은 1부터 시작
4. getString(int) - 가리키는 Column의 문자열을 가져온다.
*/
PreparedStatement ps = conn.prepareStatement(sql 문자열)
// 미리 컴파일 할 SQL문자열을 가져온다.
// 문자열에 ?는 차후 치환될수 있는 부분이다.
ps.setInt(순서, 값) or ps.setString(순서, 값)
// n번째 순서의 ?를 값으로 치환한다.
// 순서는 1부터 시작한다.
/*
ps.executeQuery() 로 실행한다.
ps도 close()를 통해 닫아야 한다.
*/
PL/SQL
- 오라클 데이터베이스에서만 사용하는 DB 프로그램
- SQL Statement 몇개를 단일 블럭으로 그룹화 해 데이터베이스 서버에 보내 전체 블럭을 한번에 실행시킨다.
- 변수, 반복, 조건문을 지원한다.
- 프로시저나 함수를 통해 특별한 기능을 지원한다.
- DBMS에 바로 생성 및 실행되어 JDBC같은것보다 훨씬 빠르다.
- 예외처리가 가능하다.
Block
- PL/SQL 에서 한번에 실행되는 단위
- BEGIN 으로 시작해 END; 로 끝난다.
- 블록 내에서 프로시저를 호출할때엔 EXEC 가 필요 없다.
Variable, Type
- 블럭 내부에서 변수의 선언은 반드시 DECLARE 문장 뒤에 와야 한다.
- {변수 이름} {상수 여부/CONSTANT} {데이터타입} {NULL여부/NOT NULL} {:= 기본값}
- 여러개의 변수를 선언하는 예시
- EMPLOYEE.Ssn%TYPE과 같은 경우는 EMPLOYEE의 SSN과 같은 타입이라는 의미이다.
- 변수명은 v를 앞에 붙힌다.
- SELECT 뒤에 INTO로 SELECT로 Projection 된 칼럼을 변수에 넣을수 있다.
ROWTYPE
- 전체 ROW를 전부 가져올때 사용한다.
조건문 IF
- ELSIF 로 else if 를 표기한다.
- 반드시 END IF; 로 끝을 내야 한다.
조건문 CASE-WHEN
- 특정 변수의 값에 따라 값이 바뀐다.
- vgrade의 값에 따라 vmsg가 바뀌는 예시
LOOP STATEMENT
- LOOP 로 시작해 END LOOP 로 끝난다.
- EXIT 혹은 EXIT WHEN 조건 으로 빠져나간다.
WHILE STATEMENT
- LOOP 앞에 사용해 조건을 명시하기 위해 사용한다.
FOR LOOP
- FOR 문과 동일
- REVERSE가 있으면 FOR문이 거꾸로 돌아간다.
CURSOR
- SQL 문이 실행 될때마다 DBMS는 특별한 메모리 공간에 Statement의 실행 결과를 저장한다.
IMPLICIT CURSOR
- 가장 최근에 실행된 SQL Statement의 정보를 담고 있다.
EXPLICIT CURSOR
- 유저가 직접 선언한다.
- CURSOR C1 선언 후, OPEN으로 쿼리를 실행 및 결과를 저장하고 FETCH로 튜플을 하나하나씩 가져온다.
- EXIT WHEN C1%NOTFOUND; 는 더이상 값이 없으면 LOOP에서 빠져 나오겠다는 의미이다.
- 혹은 FOR LOOP를 사용할수도 있다.
STORED PROCEDURE
- PROCEDURE, FUNCTION, TRIGGER 가 있다.
- 한번 정의되면 DBMS에 저장된다.
- 결과를 리턴하거나 리턴하지 않을수 있다.
PROCEDURE
- 프로시저 선언하기
- 괄호 안에 패러미터를 선언한다.
- IS 뒤에 사용할 변수를 선언한다.
프로시저 실행하기
- EXEC 프로시저이름(프로시저 Argument)
기타 동작
단일 값을 리턴하는 프로시저의 예시
FUNCTION
- 값을 계산해 리턴한다.
- RETURN 이 있고 BEGIN 전에 AS가 없다.
- BEGIN-END 블럭 안에 리턴을 해야한다.
TRIGGER
- DML이 시작하기 전(BEFORE)이나 대신(INSTEAD OF) 혹은 이후(AFTER)에 자동으로 실행된다.
- 기본값을 제공하거나, 값의 제한을 두거나, View Modification 혹은 Referential integrity violation check 에 쓰인다.
- 기본적으로 TRIGGER는 전체 테이블을 검사하는데 FOR EACH ROW를 넣어 단일 ROW별로 검사하게 할수 있다.
- 변수 선언 전에 DECLARE를 사용한다.
- 맨 뒤에 백슬래시를 붙혀 끝을 표시해야 한다.