18. System Aspects of SQL

How SQL fits into a complete programming environment?

SQL/Host Language Interface(Embedded SQL)

• 프로그래밍 언어에 SQL을 다루는 문법을 추가한 방식

• Pro*C(Oracle)

• ecpg(PostgreSQL)

• 최근에는 많이 사용되지는 않음

Call-Level Interface

• 프로그래밍 언어 수준에서 DB와 커넥션하기 위한 API를 표준화한 방식

• SQL/CLI(ANSI SQL)

• JDBC(Sun)

• ADO.NET(ODBC, ADO, OLEDB) (Microsoft)

Persistent Stored Modules

• PL/pgSQL(PostgreSQL)

• PL/SQL(Oracle)

• Transact-SQL(Microsoft)

• 표준화가 잘 안 되어있음

SQL in a Programming Environment

→ 대부분의 SQL 문은 소프트웨어의 큰 부분 중 하나임

• C와 같은 기존의 호스트 언어의 프로그램에 있음

• 하지만 이 프로그램의 단계중 일부는 SQL 문임

내장 SQL과 Call-Level Interface(CLI)

• 호스트 언어(C 같은)와 내장 SQL로 작성된 코드는 전처리기에서 호스트 언어와 함수로 모두 변경됨

• 그리고 코드를 컴파일한 후 SQL 라이브러리를 링크하여 코드가 생성됨

The Impedance Mismatch Problem

• SQL의 데이터 모델은 다른 언어의 모델과 많이 다름

• SQL은 관계 데이터 모델을 핵심으로 사용함
  • SQL은 직접 포인터, 배열과 같은 프로그래밍 언어의 구조를 사용하지 않음

• C같은 다른 프로그래밍 언어들은 정수, 문자, 포인터, 배열 등의 데이터 모델을 사용함
  • 집합 개념은 C와 같은 언어들에선 직접적으로 표현하지 않음

SQL2 내장 SQL

• C와 같은 프로그래밍 언어에서 SQL 연산을 표현하기 위한 statement을 추가함

• 각 내장 SQL statement는 EXEC SQL 로 시작됨

• 전처리기는 C와 SQL 문을 C언어로 변환함

공유 변수의 선언

• EXEC 문으로 변수를 감쌈(Bracket)

  EXEC SQL BEGIN DECLARE SECTION;
  	char studioName[50];
  EXEC SQL END DECLARE SECTION;

• 변수는 일반적으로 다음과 같이 사용함

  printf("%s\n", studioName);

• SQL에서 변수에 값을 저장하려면 콜론(:)을 사용함

  EXEC SQL select name into :studioName from Studio;

에러 핸들링

• SQL 문을 실행한 후에 데이터를 사용하기 전에 에러가 없는지 프로그램에서 체크해야 함

• 다음 방법으로 체크할 수 있음
  • SQLCA 필드 명세를 디스플레이함
  • 애플리케이션 프로그램의 문에 WHENEVER 키워드 사용
  • 특정 값에 SQLCODE나 SQLSTATE 테스트함

sqlca 사용

• sqlca.sqlcode

  

• sqlca.sqlstate

  

WHENEVER 사용

• 이벤트 발생 시에 특정 동작을 하도록 하는 키워드

• 문을 실행한 후부터 적용됨

EXEC SQL WHENEVER SQLERROR STOP; // 에러가 발생하면
STOP EXEC SQL WHENEVER SQLWARNING SQLPRINT; // 경고가 발생하면
SQLPRINT EXEC SQL WHENEVER NOT FOUND DO BREAK: // NOT FOUND라면 BREAK

sqlca를 사용한 Single Row Select Statement

• 하나의 행 결과를 받는 문

#include <stdio.h>
#include <string.h>

EXEC SQL INCLUDE sqlca;

// 에러나 경고 발생 시
SQLPRINT EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
int main(void) {
	// 변수 선언
    EXEC SQL GEGIN DECLARE SECTION;
		char theTitle[20];
		int theYear; 
		int theLength;
    EXEC SQL END DECLARE SECTION;
    
    // DB 연결
    EXEC SQL CONNECT TO fcdb@host USER user/xxx;
    
    // 변수에 값 할당
    strcpy(theTitle, "Gone With the Wind");
    theYear = 1939;
    
    // NOT FOUND가 발생하면 notfound로 goto
    EXEC SQL WHENEVER NOT FOUND GOTO notfound;
    
    // SQL 문 실행 후 theLength에 값 할당
    EXEC SQL SELECT length INTO :theLength
    FROM MOVIE
    WHERE title = :theTitle AND year = :theYear;
    
    // 에러가 안 나면 출력, 나면 Not Found
    if (!strcmp(sqlca.sqlstate, "00000"))
    	printf("%d\n", theLength);
    else 
    	printf("Not Found.\n");  
    EXEC SQL DISCONNECT; 
    return 0;
    
    notfound:
    printf("Not Found.\n");
    
    EXEC SQL DISCONNECT; 
    return 0;
}

Cursors를 사용한 Multi-Row Select Statements

EXEC SQL BEGIN DECLARE SECTION; 
	char theTitle[20];
	int  theYear; 
EXEC SQL END DECLARE SECTION; 

// 여러 행의 데이터를 c라는 CURSOR를 사용하여 받음
EXEC SQL DECLARE c CURSOR FOR 
	SELECT title, year, FROM Movie WHERE studioName = 'Disney'; 
    
// 그 데이터가 c라는 변수의 메모리에 올라감
EXEC SQL OPEN c;

EXEC SQL WHENEVER NOT FOUND DO break; 

// FETCH .. INTO를 통해 한 줄씩 읽어나감
// 끝까지 다 읽은 후엔 NOT FOUND가 발생하여 무한루프 탈출 
while(1) { 	
	EXEC SQL FETCH c INTO :theTitle, :theYear;
    printf("%s\n", theTitle);
}  

EXEC SQL CLOSE c;

Scrolling Cursors

• 기본적으로 FETCH를 통해 읽는 경우에는 전진만 가능함

• SCROLL 키워드를 사용하면 후진도 가능

• FETCH <row-selector> [FROM] <cursor> INTO <target-list>
  • row-selector:
    • NEXT, PRIOR
    • FIRST, LAST
    • RELATIVE <integer-value> - 현재 행에서 앞 뒤로 찾음
      • NEXT
      • PRIOR
    • ABSOLUTE <integer-value> - 맨 앞 데이터 기준으로 찾음
      • FIRST
      • LAST

예제

void changeWorth() {
	EXEC SQL BEGIN DECLARE SECTION; 
	int worth; 
	EXEC SQL BEGIN DECLARE SECTION; 
    EXEC SQL DECLARE eCursor SCROLL CURSOR FOR SELECT netWorth FROM MovieExec;
    EXEC SQL OPEN eCursor;  
    
    // 제일 마지막 데이터를 읽어와서 worth에 대입 
    EXEC SQL FETCH LAST eCursor INTO :worth; 
    EXEC SQL WHENEVER NOT FOUND DO break; 
    while(1) { 	
    	// PRIOR: 역순으로 읽어옴
        EXEC SQL FETCH PRIOR eCurrsor INTO :worth; 	
        printf("%d\n", worth);
    }
    EXEC SQL CLOSE eCursor;
}

Modifications by Cursor

• Cursor로 가져온 데이터를 수정한 뒤 DB에 반영하기 위해서는 FOR UPDATE 키워드 사용

void changeWorth() {
	EXEC SQL BEGIN DECLARE SECTION;
    int worth; 
    EXEC SQL BEGIN DECLARE SECTION;
    
    // FOR UPDATE 적용으로 DELETE, UPDATE 문 가능 
    EXEC SQL DECLARE eCursor CURSOR FOR SELECT netWorth FROM MovieExec FOR UPDATE; 
    
    EXEC SQL OPEN eCursor; 
    
    // 제일 마지막 데이터를 읽어와서 worth에 대입
    EXEC SQL FETCH LAST eCursor INTO :worth; 
    EXEC SQL WHENEVER NOT FOUND DO break; 
    
    while(1) { 	
    	EXEC SQL FETCH eCurrsor INTO :worth;
        
        // CURRENT OF eCursor를 통해 현재 행에 적용
        if (worth < 1000) 	
        	EXEC SQL DELETE FROM MovieExec WHERE CURRENT OF eCursor; 
        else 	
        	EXEC SQL UPDATE MovieExec SET netWorth = 2 * netWorth
            WHERE CURRENT OF eCursor; 
    } 
    EXEC SQL CLOSE eCursor;
}

Read Only Cursors

• Cursor를 선언할 때 FOR READ ONLY 키워드를 맨 뒤에 붙이면 UPDATE나 DELETE를 하면 에러 발생

Dynamic SQL

• 내장 SQL은 컴파일 시 이미 SQL문이 다 정해져 있고 C언어 형태로 변경되어 있음

• Dynamic SQL은 프로그램 실행 중에 SQL을 입력받고 인터프리터 형식으로 처리함

예제

EXEC SQL BEGIN DECLARE SECTION;
char auery[MAX_QUERY_LENGTH]; 
EXEC SQL END DECLARE SECTION; 
while(1) { 
	printf("SQL> ");
    scanf("%s", query);
    
    // query에 입력된 쿼리를 받고 q에 담은 후 실행
    EXEC SQL PREPARE q FROM :query;
    EXEC SQL EXECUTE q;
}

Using Call-Level Interface(CLI)

• SQL/CLI

• ODBC(Open Database Connectivity)
  • MS 사에서 SQL/CLI를 가져와서 MS-DOS 에서 사용하기 위해 만든 인터페이스

• JDBC(Java Database Connectivity)

JDBC

1. 데이터베이스 드라이버를 로드함
   • DriverManager 객체를 생성

2. 데이터베이스와 연결을 설정함
   • Connection 객체는 DriverManager.getConnection()을 호출함으로써 생성됨

   Connection myCon = DriverManager.getConnection(<URL>, <name>, <password>);

JDBC에서 statement 생성

1. Creating Statement objects

   myCon.createStatement() -> statement 타입의 객체 stmt 생성
   myCon.prepareStatement(query) -> PreparedStatement 타입의 객체 pstmt 생성

2. 메서드 실행

   // 쿼리 실행
   stmt.executeQuery(query)
   pstmt.executeQuery()
   
   // update 실행. insert, delete도 같은 방식
   stmt.executeUpdate(non-query-statement)
   pstmt.executeUpdate()

• 예제 코드

  Connection conn = DriverManager.getConnection(
  	"jdbc:oracle:thin:@<hostname>:1512:<dbname>", "scott", "tiger");
  
  /* Statement */ 
  Statement stmt = conn.createStatement();
  ResultSet Worths = stmt.executeQuery("SELECT nentWorth FROM MovieExec");
  
  /* PreparedStatement */
  PreparedStatement pstmt = conn.prepareStatement(
  	"SELECT netWorth FROM MovieExec WHERE name LIKE ?");
      
  /* 파라미터를 넘길 때는 ?와 setString을 활용 */
  pstmt.setString(1, "M%");
  ResultSet Worths = pstmt.executeQuery();

JDBC에서 Cursor 연산

• ResultSet 클래스가 제공함

1. next(), prev()

2. getString(i), getInt(i), getFloat(i)

• 예제 코드

Statement stmt = conn.createStatement();
ResultSet Worths = stmt.executeQuery("SELECT netWorth FROM MovieExec"); 
while (Worths.next()) { 
	worth = Worths.getInt("netWorth"); 
}

전체적인 코드

import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
import static java.lang.System.out;
class TestJDBC {
	public static void main(String[] args) throws SQLException { 
    	OracleDataSource ds = new OracleDataSource();
        ds.setURL("jdbc:oracle:thin:@<hostname>:1521:<dbname>");
        ds.setUser("scott"); 
        ds.setPassword("tiger"); 
        Connection conn = ds.getConnection();
        Statement  stmt = conn.createStatement();
        REsultSet  rset = stmt.executeQuery("SELECT item_number, item_name FROM sales");  
        while(rset.next()) { 	
        	out.println(rset.getInt("item_number") + " " + rset.getString("item_name")); 	
        } 		
        rset.close(); 	
        stmt.close(); 	
        conn.close(); 
    }
}

SQLJ

• C언어의 내장 SQL과 같은 시도를 했었으나 개발자들의 선택을 받지 못하고 사장됨

 

Uploaded by Notion2Tistory v1.1.0