[DB] Introduction to SQL Programming Techniques

SUbbb·2021년 11월 20일
Database
0

DataBase

목록 보기
10/15
post-thumbnail

Introduction to Database Programming

Database Applications

  • Host Language: DBA가 실행되기 위한 lang (JAVA, C/C++/C#, COBOL, etcs)
  • Data sublanguage: host lang에 탑재 (SQL)

Techniques and Issues

  • Interactive interface: DBMS에 직접 찾아 들어가야 하고, DBMS가 있는 서버에서만 가능하다. (sqlplus, mysql shell, psql, etcs)
  • Execute a file of commands: @<filename>.sql(in Oracle)

Approaches to Database Programming

1) Host language에 database commands를 내장

  • Compiler 또는 EXEC SQL 가 해당 명령어를 식별
  • Precompiler 또는 Preprocessor가 소스 코드를 스캔하면서 해당 statements를 식별하고, DBMS의 처리를 위해 추출
  • Compile time에 SQL statements의 error를 발견할 수 있다.
  • embedded SQL

  • EXEC SQL ~ EXEC SQL END DECLARE SECTION 까지는 변수 선언 part
    • 이때, int SQLCODE : SQL code의 state를 return받는 변수
  • : : binding을 위해 사용

2) Database functions library 사용

  • 함수의 library는 Application Programming Interface(API)로 불린다.
  • 각 library는 DBMS에 접근하는 여러 functions를 포함한다.
    • ODBC (Open DataBase Connectivity):
      • C언어용
    • JDBC (Java DataBase Connectivity):
      • Java SE 9에 내장 (JDK에 import됨)
      • JAVA용으로, Java-compliant object database를 사용함으로써 impedance mismatch를 많이 줄였다.

JDBC Driver Manager: DB와의 연결을 관리

3) Brand-new language 설계

  • Database Programming Language에 loops나 conditional statements같은 추가적인 프로그래밍 구조를 추가
    • 완전한 프로그래밍 언어로 변환하기 위함
    • Programming 관점에서는 가장 빠른 성능을 보인다.
  • DBPL example: Oracle's PL/SQL, SQL Server's T-SQL

JDBC: SQL Class Library for Java Programming

JDBC (Java Database Connectivity)

DBMS connection/operation을 위한 Java-based API들의 집합

  • driver로 알려져 있다.
  • 다양한 DBMS에 접근할 수 있도록 설계
    • Main advantage: 하나의 Java Program으로 여러 DBMS에 접근하여 작업을 수행할 수 있다.
    • DBMS에 독립적이지만, 선택한 DBMS와 연결된 적절한 드라이버를 명시적으로 load해야 한다. →\rarr Driver만 알면 Portable하다.

Relationship among Java Objects for Database Connection (in JDBC)

  • DriverManager Object
    : DBMS와의 Connection 관리 (메모리자원을 소비하는 것이므로 무작정 많은 연결을 해선 안된다.)
  • Connection Object
    : DBMS와의 통신 채널
  • Statement Object
    : 해당 객체로 SQL 실행
  • ResultSet Object
    : SQL query의 결과에 접근하는 통로

  • DriverManager - url : IP와 Port # 정보
  • void close() : resource이기 때문에 사용 후 release가 필요
  • PreparedStatement : Code template이 고정됨, 매번 Compile할 필요가 없이 파라미터만 수정해주면 되는 형태
  • CallableStatement : SQL function call을 위해 사용
  • ResultSet - getInt(), getString() : SQL query 결과의 column의 datatype에 맞는 function을 호출해야 함

TestJDBC.java

Connection

import java.sql.*; // import JDBC package

public class TestJDBC {
	public static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    	// IP: localhost, Port #: 1521
	public static final String USER_UNIVERSITY ="university";
    	// 연결할 USER
	public static final String USER_PASSWD ="comp322";
    	// USER의 PWD
	
	public static void main(String[] args) throws IOException, SQLException {
		Connection conn = null;
		Statement stmt = null;
		String sql = "";
        
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
            	// Oracle DBMS를 위해 필요한 JDBC driver load
			System.out.println("Driver Loading: Success!");
		} catch(ClassNotFoundException e) {
			System.err.println("error = " + e.getMessage());
			System.exit(1);
		}
        
		try {
			conn = DriverManager.getConnection(URL, USER_UNIVERSITY, USER_PASSWD); 
			// Connection을 위해 필요한 정보를 전달해주면서 생성
			System.out.println("Oracle Connected.");
		} catch(SQLException ex) {
			ex.printStackTrace();
			System.err.println("Cannot get a connection: " + ex.getLocalizedMessage());
			System.err.println("Cannot get a connection: " + ex.getMessage());
			System.exit(1);
		}

CREATE TABLE

try {
	conn.setAutoCommit(false);
	// 자동 commit 기능 끄기
	stmt = conn.createStatement();
	// Connection object로부터 Statement object 받아오기

	sql = "DROP TABLE " + TABLE_NAME + " CASCADE CONSTRAINT";
	int res = stmt.executeUpdate(sql);
	// sql문 실행 (SELECT 이외의 모든 sql은 executeUpdate로 수행)
	if (res == 0) System.out.println("Table was successfully dropped.");
	StringBuffer sb = new StringBuffer();
	sb.append("CREATE TABLE " + TABLE_NAME + " (Id INT, ");
	sb.append("	Name VARCHAR2(10), ");
	sb.append("	Address VARCHAR2(20))");
	sql = sb.toString();
	// TABLE 생성을 위한 query문 작성
	res = stmt.executeUpdate(sql);
	if (res == 0) System.out.println("Table was successfully created.");
	conn.commit();
	// 자동 commit 기능을 껐으므로 수동으로 commit
} catch(SQLException ex) {
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}

INSERT

try {
	sql = "INSERT INTO TEST VALUES (10, 'BAEK', 'Daegu')";
	int res = stmt.executeUpdate(sql);
	if (res == 0) System.out.println(res + "개의 행이 삽입되었습니다.");
	sql = "INSERT INTO TEST VALUES (20, 'KIM', 'Seoul')";
	stmt.addBatch(sql);
	// 여러 개의 SQL을 한 번에 수행하기 위해 batch에 추가
	sql = "INSERT INTO TEST VALUES (30, 'CHOI', 'Busan')";		
	stmt.addBatch(sql);
	sql = "INSERT INTO TEST VALUES (40, 'LEE', 'Jeju')";
	stmt.addBatch(sql);
	int[] count = stmt.executeBatch();
	// batch를 한 번에 수행
	System.out.println(count.length + "개의 행이 삽입되었습니다.");
	conn.commit();
	// 자동 commit 기능을 껐으므로 수동으로 commit
} catch(SQLException ex) {
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}
  • batch를 사용해 여러 query 구문을 메모리에 올려두었다가, executeBatch() 를 만나면 한 번에 처리한다.
  • 하지만 batch의 size에 대한 고려가 필요하다.

Query

try {
	sql = "SELECT * from TEST";
	ResultSet rs = stmt.executeQuery(sql);
	// SELECT는 executeQuery로 수행하고, return되는 결과를 ResultSet에 저장
	while (rs.next()) {
		// no impedance match in JDBC, rs == null 이면 return된 tuple이 없다는 의미
		int id = rs.getInt(1);
		String name = rs.getString(2);
		String addr = rs.getString(3);
		// column index를 전달하여 해당 위치의 value를 return
		System.out.println("ID = " + id + ", Name = " + name + ", Address = " + addr);
	}
	conn.commit();
	// 자동 commit 기능을 껐으므로 수동으로 commit
} catch(SQLException ex) {
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}

UPDATE/DELETE

try {
	sql = "UPDATE TEST SET Name = 'OH' WHERE Id = 40";
	int res = stmt.executeUpdate(sql);
	System.out.println(res + "개의 행이 수정되었습니다.");
	sql = "DELETE FROM TEST WHERE Id = 30";
	stmt.addBatch(sql);
	int[] count = stmt.executeBatch();
	System.out.println(count.length + "개의 행이 삭제되었습니다.");
	// 여러 개의 행이 삭제될 수도 있다.
	conn.commit();
	// 자동 commit 기능을 껐으므로 수동으로 commit
} catch(SQLException ex) {
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}

QUERY - PreparedStatement

try {
	sql = "SELECT * from TEST WHERE Id = ?";
	// ? : parameter를 전달할 부분
	PreparedStatement pstmt = conn.prepareStatement(sql);
	// query 형태는 고정적이고, 특정 parameter만 변경될 query
	pstmt.setInt(1, 40);
	// Parameter Setting, 1 : parameter index
	ResultSet rs = pstmt.executeQuery();
	while (rs.next()) {
		// no impedance match in JDBC, rs == null 이면 return된 tuple이 없다는 의미
		int id = rs.getInt(1);
		String name = rs.getString(2);
		String addr = rs.getString(3);
		// column index를 전달하여 해당 위치의 value를 return
		System.out.println("ID = " + id + ", Name = " + name + ", Address = " + addr);
	}
	
	conn.commit();
	// 자동 commit 기능을 껐으므로 수동으로 commit
    
	rs.close();
	pstmt.close();
   	stmt.close();
   	conn.close();
	// 사용했던 자원 release 필수
} catch(SQLException ex) {
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}

Oracle PL/SQL

Procedural Language/Structured Query Language (PL/SQL)

  • DB application programs에 사용되는 Oracle-exclusive language
  • SQL statements를 하나의 block으로 만들어서 전송, 처리 →\rarr 성능 향상
  • general-purpose language (GPL)처럼 Variables, loops, control structures 지원
  • Exception handling 지원
  • 특정 기능을 수행하기 위한 procedure이나 function을 생성하는 기능을 제공

SQL은 procedural하지 않았기때문에 위의 특징들이 지원되지 않는다.

Block in PL/SQL

모두 block 단위로 처리

Variable and Types in PL/SQL

  • GPL와 동일
  • DECLARE section에 명시

Example

SET SERVEROUTPUT ON;
DECLARE
    vempssn EMPLOYEE.Ssn%TYPE;
    vefname EMPLOYEE.Fname%TYPE;
    velname EMPLOYEE.Lname%TYPE;
    // EMPLOYEE.ATTR와 같은 datatype을 가지도록 설정
    // vemp EMPLOYEE%ROWTYPE; : table의 datatype을 가지도록 설정, 즉 table 자체를 참조. 이후 vemp.Ssn, vemp.Lname과 같이 접근 가능
BEGIN
    SELECT Ssn, Fname, Lname
    INTO vempssn, vefname, velname <- 할당
    FROM EMPLOYEE
    WHERE Ssn = '888665555';
    DBMS_OUTPUT.PUT_LINE(vempssn || ', ' || vefname || ', ' || velname );
END;
/ // PL/SQL statement의 실행을 위해서는 필수

Control Flow

IF Statement Family

DECLARE
    vdname DEPARTMENT.dname%TYPE;
    vempssn EMPLOYEE.ssn%TYPE;
    vename EMPLOYEE.lname%TYPE;
    vsal EMPLOYEE.salary%TYPE;
    vlabel VARCHAR2(10);
BEGIN
    SELECT dname, ssn, fname || ' ' || lname as ename, salary
    INTO vdname, vempssn, vename, vsal
    FROM EMPLOYEE e, DEPARTMENT d
    WHERE e.dno = d.dnumber and e.ssn = '888665555';
    
    IF (vsal < 20000) THEN
    	vlabel:='LOW';
    ELSIF (vsal < 40000) THEN
    	vlabel:='MEDIUM 1';
    ELSIF (vsal < 60000) THEN
    	vlabel:='MEDIUM 2';
    ELSE
    	vlabel:='HIGH';
    END IF;
    DBMS_OUTPUT.PUT_LINE(vdname || ', ' || vempssn || ', ' || vsal || '=>' vlabel);
END;
/ // PL/SQL statement의 실행을 위해서는 필수

CASE-WHEN Statement

LOOP Statement

WHILE Statement

FOR Statement

COUNTER: 증감하는 변수, REVERSE: 순서 반대로

Cursor

  • 매번 SQL statement가 실행될 때마다, Oracle DBMS는 특정 메모리 공간에 결과를 저장하는데, 이 메모리 공간에 접근하기 위해 Cursor를 사용한다.
  • 일종의 주소를 담고 있는 포인터

Cursor type

Implicit cursor

  • 하나의 row를 return하는 SELECT문에 대해 PL/SQL이 자동으로 선언

Explicit cursor

  • 여러 개의 rows를 return하는 SELECT문에 대해 사용자가 선언

Implicit Cursor

Explicit Cursor: Declared by a User

query statement의 result를 모두 저장fetch가 더 이상 진행되지 않는 경우 종료하고, CLOSE 단계에서 사용한 메모리 영역을 비움JDBC보다 더 빠른 속도를 보인다.

Cursor FOR LOOP Statement

Cursor와 For LOOP는 잘 결합된다.

Stored Procedure (저장 프로시저, PSM)

  • program logic을 구현
  • object로서 존재
  • GPL의 function과 유사
  • PROCEDURE, FUNCTION, TRIGGER 포함
  • RETURN 을 통해 결과를 return하거나, 하지 않을 수도 있다.

CREATE PROCEDURE Statement

  • procedure 정의
  • BEGIN-END 사용
  • 특정 parameter는 procedure가 호출될 때 전달되어야 한다.

EXEC stored_procedure_name(...); 로 procedure 호출
// JDBC-Callable Statement로 PSM 사용 가능

DROP PROCEDURE stored_procedure_name; 로 procedure 삭제

---

CREATE OR REPLACE PROCEDURE ComputeAvaSal (
    AvgSal OUT NUMBER)
AS
BEGIN
    SELECT AVG(Salary) INTO AvgSal
    FROM EMPLOYEE;
END;
/

DECLARE
    Avgsal NUMBER; -- return 값을 저장할 변수
BEGIN
    ComputeAvaSal(Avgsal); -- function처럼 사용 가능
    DBMS_OUTPUT.PUT_LINE('Avg. Salary: $' || AvgSal);
END;
/

CREATE FUNCTION Statement

  • user-defined function 정의
  • value를 계산하고, return
  • SQL statement or 다른 procedure에서 호출
  • Procedure 와 다른 점: 어떠한 return 값을 반환

Comparison of Procedure, Trigger, and Function in Oracle

  • Procedure
    : return 값이 무조건 존재하지 않고, SQL에 의해 처리될 수 없는 복잡한 logic을 수행
  • Trigger
    : INSERT, DELETE, UPDATE가 수행될 때 자동으로 실행, 제약조건 위반을 감지
  • Function
    : SELECT나 WHERE절에서 사용되고, return 값이 존재

Comparing the Three Apporoaches

1) Embedded SQL Approach

  • 장점
    • query text는 program source code의 일부가 된다.
      • compile time에 syntax error나 database schema의 유효성이 판단된다. (변경 시, recompile이 필요)
    • program의 가독성 증가
  • 단점
    • runtime에 query에 대한 수정이 불가능
      • query에 대한 수정이 있는 경우 recompile을 거쳐야 함
        • Debugging이 어려움
    • runtime에 query를 생성하거나 하는 복잡한 application의 경우 2) 접근법이 더 효과적

2) Library of Classes and Function Calls Approach

  • 장점
    • runtime에 query를 생성할 수 있어 1) 접근법보다 유연하고 사용이 쉽다.
  • 단점
    • Runtime query generation은 query 결과의 column가 일치하는 변수를 미리 알 수 없어서 결과적으로 더 복잡한 programming을 낳게 된다. (function call overhead)
    • 모든 syntax checking과 query validation은 runtime에 수행된다. (DBMS가 query의 에러를 알림)

3) Database Programming Language Approach

  • 장점
    • 완벽하게 data type이 매칭되므로 impedance mismatch problem이 없다.
  • 단점
    • 프로그래머가 새로운 programming language를 배워야 한다.
profile
SUbbb
배우고 정리하고 공유하기
이전 포스트

[DB] 8. The Relational Algebra and Relational Calculus

다음 포스트

[DB] Web Database Programming Techniques Using Tomcat + JSP

0개의 댓글