codemon

DELETE FROM AA FROM AA aa INNER JOIN BB bb ON aa.key = bb.key WHERE ~

mssql에서 delete와 inner join 을 같이 쓸 때 특이점은 from이 두 번 나온다는 것이다.

출처: https://3months.tistory.com/193 [Deep Play]

statement 와 preparedStatement 가 왜 두개가 있는지부터 생각해보아야 할꺼에요?
먼저 statement 는 동적으로 변하지 않는 쿼리를 날리고자 하는 경우에 사용되고요
preparedStatement 는 동적으로 쿼리를 변경해서 날릴 때 사용하지요.
그렇기 때문에 in parameter 만 다른 많은 쿼리를 날리고자 할 때는
preparedStatement 를 사용하는 것이 매우 효과적입니다. 왜냐하면 statement 로 날릴 경우에는 매번 dbms 에서 컴파일을 하고 실행하는데 반해 preparedStatement 는 한번 컴파일해놓고 in parameter 만 변경해서 실행하거든요 하지만 모든 쿼리를 preparedStatement 로 사용할 경우에는 문제가 생길 수 있습니다. dbms 자원이 낭비되거든요 모든 쿼리를 메모리에 가지고 있을려니 힘들죠 그리고 필요하지도 않는 쿼리가 메모리에 있으니 메모리가 꽉차면 매번 제거하는 작업도 해야하구..
jdbc programming in oracle 라는 책이던가.. 거기서는 65번 이상 반복적으로 쿼리를 실행할 경우에는 preparedStatement 를 사용하라고 합니다. oracle 이외에는 어떤게 적정수치라고 할 수 없지만 ...
암튼 결론을 내리자면 반복적인 실행이 필요없는 쿼리이고 자주 사용되지 않는 쿼리라면 꼭 statement 를 사용하세요. 어떤 회사에는 preparedStatement 가 좋다고 모든 쿼리를 이걸로 작성하는데 이는 분명 잘못된겁니다. 

출처 : http://uclee1124.tistory.com/m/28

프로시저는 PL/SQL을 통해 만들어지고 특정 작업을 수행하는 서브 프로그램이다. 자주 사용되는 SQL문을 DB 객체로 생성해서 저장한 후 사용시에 프로시저명을 호출해서 사용한다. PL/SQL에서 FUNCTION 은 리턴값을 반환하는데 반해 프로시저는 지정된 작업을 수행 후에 결과값을 반환할 수도 있고 반화나지 않을 수 있다. FUNCTION과 프로시저의 차이점은 FUNCTION은 SQL문 내부에서 사용할 수 있지만, 프로시저는 EXEC 또는 EXECUTE의 실행문을 통해서 사용된다.

출처: http://kkiuk.tistory.com/16 [평범한 개발자의 이야기]

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

관계형 데이터베이스에서 SQL을 이용해 관계를 맺는 것을 '조인'이라고 합니다. 테이블 간의 연결 고리로 관계를 맺고 데이터를 추출하는 방법입니다. 테이블의 기본키와 외래키가 연결고리가 됩니다.

조인의 종류에 대해 알아보겠습니다. 조인은 크게 내부 조인과 외부 조인으로 구분이 됩니다. 

내부 조인에는 동등 조인, 세미 조인, 안티 조인, 셀프 조인이 있습니다. 

하나 하나 알아보겠습니다. 

동등 조인 

WHERE절에서 등호 연산자를 사용해 2개 이상의 테이블이나 뷰를 연결하는 조인입니다. 

세미 조인

서브 쿼리를 사용해 서브 쿼리에 존재하는 데이터만 메인 쿼리에서 추출하는 조인입니다. 

세미 조인은 최종 반환되는 메인 쿼리 데이터에 중복되는 건이 없습니다.

안티 조인

서브 쿼리의 B 테이블에는 없는 메인 쿼리의 A 테이블의 데이터만 추출하는 조인입니다.

셀프 조인

동일한 한 테이블을 사용해 조인하는 방법입니다. 

외부 조인

조인 조건에 만족하는 데이터 뿐만 아니라, 어느 한 쪽 테이블에 조인 조건에 명시된 컬럼에 값이 없거나 (NULL이라도) 해당 로우가 아예 없더라도 데이터를 모두 추출하는 것입니다.  데이터가 없는 테이블의 컬럼에 (+) 표시를 붙이는 방식이고 조인 조건이 여러 개일 때 모든 조건에 (+) 표시를 붙여야 합니다.

카타시안 조인

FROM절에는 2개 이상의 테이블이 명시되어 있지만 WHERE절에는 조인 조건이 없는 조인입니다.  결과는 두 테이블의 건수의 곱만큼 나오게 됩니다.

ANSI 조인

ANSI SQL 문법을 사용한 조인으로 조인 조건이 WHERE절이 아닌 FROM절에 위치하는 것입니다.

ANSI 내부 조인

ANSI 외부조인 

다음 코드를 보면 데이터가 더 많은 쪽이 왼쪽에 쓰여진 테이블(employees)이기 때문에 LEFT [OUTER] JOIN이라고 합니다.

CROSS 조인은 기존 카타시안 조인을 ANSI 조인에서 쓴 것입니다.

FULL OUTER 조인은 외부 조인의 하나로 ANSI 조인에서만 제공하는 것입니다. 두 테이블 모두 기준이 되어 부족한 것들까지 다 나오도록 하는 방식입니다. 

서브 쿼리

SQL 문장 안에서 보조로 사용되는 또 다른 SELECT문을 의미합니다. 서브 쿼리는 여러 개를 사용할 수 있습니다. 서브 쿼리를 구분해보면 다음과 같이 나눌 수 있습니다.

- 연관성이 없는 서브쿼리

  : 메인 쿼리와 조인 조건이 걸리지 않는 서브쿼리로 단지 순차적으로 처리 되는 경우

- 연관성이 있는 서브쿼리

  : 메인 테이블과 조인 조건이 걸린 서브쿼리

- 일반 서브쿼리(SELECT절)

- 인라인 뷰(FROM절)

- 중첩쿼리(WHERE절)

[출처] DB - 조인과 서브 쿼리|작성자 DeveloperBee

SQL을 작성하다 보면 테이블에 저장된 데이터를 원하는 목적에 맞게 집계해서 데이터를 추출해야 할 때가 많은데 이때 사용하는 것이 그룹 쿼리입니다.

그룹 쿼리란, 특정 항목별로 그룹을 지어 정보를 추출할 때 사용하는 SQL 구문으로 집계 함수와 GROUP BY절의 결합이라고 할 수 있습니다.

먼저 기본 집계 함수에 대해 알아보겠습니다.

우선 집계 함수란, 대상 데이터를 특정 그룹으로 묶은 다음 이 그룹에 대해 총합, 평균, 최댓값, 최솟값 등을 구하는 함수를 말합니다.

COUNT(expr)

쿼리 결과 건수, 즉 전체 로우 수를 반환하는 함수입니다. expr에 * 또는 컬럼명이 올 수 있고 NULL이 아닌 것만을 체크하게 됩니다.

DISTINCT

중복을 제거할 때 사용하여 유일한 값만을 조회하게 됩니다. 중복된 값을 제거하지만 NULL인 것도 하나로 체크하게 됩니다.

SUM(expr)

expr의 전체 합계를 반환합니다. expr은 숫자형만 올 수 있습니다.

AVG(expr)

expr의 평균 값을 반환합니다.

MIN(expr) / MAX(expr)

최솟값과 최댓값을 반환합니다.

VARIANCE(expr) / STDDEV(expr)

분산과 표준편차를 반환합니다. 분산이란 개별 값과 평균 값의 차이인 편차를 구해 이를 제곱하여 평균한 값을 의미합니다. 표준 편차는 이 분산 값의 제곱근을 의미합니다.

다음으로 GROUP BY절과 HAVING절에 대해 알아보겠습니다.

GROUP BY절이란, 

특정 그룹을 묶어 데이터를 집계하는 것을 말합니다. WHERE와 ORDER BY절 사이에 위치하며 그룹으로 묶을 컬럼명이나 표현식을 명시하게 됩니다. 집계함수가 아닌 컬럼과 집계함수를 같이 조회하는 경우 반드시 일반 컬럼은 GROUP BY절에 명시가 되어야 합니다.

HAVING절은 GROUP BY절 다음에 위치해서 GROUP BY한 결과를 대상으로 다시 필터를 거는 역할을 합니다.

GROUP BY절에서 그룹별 소계를 추가로 보여주는 역할을 하는 ROLLUP과 CUBE절이 있습니다. 이 절들에는 집계 함수를 제외한 컬럼 등의 표현식이 올 수 있습니다.

ROLLUP(expr1, expr2,...)은 명시한 표현식 수와 순서(오른쪽 -> 왼쪽)에 따라 레벨별로 집계한 결과를 반환합니다.

CUBE(expr1, expr2,...)는 명시한 표현식 개수에 따라 가능한 모든 조합별로 집계한 결과를 반환합니다.

추가적으로 분할(partial) ROLLUP과 분할 CUBE도 있습니다.

마지막으로 집합 연산자에 대해 알아보겠습니다.

집합 연산자란,

데이터 집합(쿼리 조건에 맞아 조회된 일련의 데이터)을 대상으로 연산을 수행하는 연산자로 여러 SELECT문을 연결해 또 다른 하나의 쿼리를 만드는 역할을 합니다.

UNION

합집합과 같은 의미로 각 집합 원소 (SELECT 결과)를 모두 포함한 결과를 반환합니다. 여기서 중복되는 값은 한번만 조회됩니다.

UNION ALL

UNION과 비슷하지만 중복되는 값을 각각 하나로 조회한 결과를 반환합니다.

INTERSECT

교집합과 같은 의미로 데이터 집합에서 공통된 항목만 추출한 결과를 반환합니다.

MINUS

차집합과 같은 의미로 한 데이터 집합을 기준으로 다른 데이터 집합과 공통된 항목을 제외한 결과만 추출하여 반환합니다.

집합 연산자를 사용할 때 제한 사항은 다음과 같습니다.

  - 집합 연산자로 연결되는 각 SELECT문의 SELECT 리스트의 개수와 데이터 타입 일치

  - 집합 연산자로 SELECT문을 연결할 때 ORDER BY절은 맨 마지막 문장에서만 사용 가능

  - BLOB, CLOB, BFILE 타입의 컬럼에 대해서는 집합 연산자를 사용할 수 없음

  - UNION, INTERSECT, MINUS 연산자는 LONG형 컬럼에는 사용할 수 없음

그 외 GROUPING SETS절이 있는데 이는 GROUP BY절에서 사용되고 UNION ALL 개념이 셨여 있다고 할 수 있습니다. 간단히 설명하면 GROUPING SETS(expr1, expr2, expr3)이면 세 표현식 별로 각각 집계가 이루어진다는 것입니다.

이유는 Statement 객체에서 사용한 createStatement()라는 메소드 때문입니다. 이것을 사용할 경우 사용자의 입력 값을 미리 만들어 놓은 sql문에 적용한 후 컴파일을 하기 때문에 사용자의 입력 값에 따라 쿼리문의 형태가 바뀔 수 있어 보안에 취약합니다. 사용자가 입력 값과 함께 'OR 1=1'과 같은 코드를 함께 전달할 경우 모든 사용자의 정보 등이 노출될 수 있기 때문입니다.

따라서 해결책으로 Statement 객체의 preparedStatement(query) 메소드를 사용하였습니다. 이것은 미리 개발자가 작성한 쿼리문을 컴파일 하고 ?로 처리한 부분에 사용자의 입력 값을 넣기 때문에 쿼리문의 형태가 바뀌지 않아 보안성을 높일 수 있습니다.

PreparedStatement 와 Statement

* PreparedStatement 와 Statement의 가장 큰 차이점은 캐시(cache) 사용여부이다.

1) 쿼리 문장 분석

2) 컴파일

3) 실행

Statement를 사용하면 매번 쿼리를 수행할 때마다 1) ~ 3) 단계를 거치게 되고, PreparedStatement는 처음 한 번만 세 단계를 거친 후 캐시에 담아 재사용을 한다는 것이다. 만약 동일한 쿼리를 반복적으로 수행한다면 PreparedStatment가 DB에 훨씬 적은 부하를 주며, 성능도 좋다.

1. Statement 

String sqlstr = "SELECT name, memo FROM TABLE WHERE num = " + num 

Statement stmt = conn.credateStatement(); 

ResultSet rst = stmt.executeQuerey(sqlstr); 

sqlstr를 실행시 결과값을 생성

Statement  executeQuery() 나 executeUpdate() 를 실행하는 시점에 파라미터로 SQL문을 전달하는데, 이 때 전달되는 SQL 문은 완성된 형태로 한눈에 무슨 SQL 문인지 파악하기 쉽다. 하지만, 이 녀석은 SQL문을 수행하는 과정에서 매번 컴파일을 하기 때문에 성능상 이슈가 있다. 

2. PreparedStatement 

String sqlstr = "SELECT name, memo FROM TABLE WHERE num = ? " 

PreparedStatement stmt = conn.prepareStatement(sqlstr); 

pstmt.setInt(1, num);

ResultSet rst = pstmt.executeQuerey(); 

sqlstr 은 생성시에 실행

PreparedStatement 은 이름에서부터 알 수 있듯이 준비된 Statement 이다. 이 준비는 컴파일(Parsing) 을 이야기하며, 컴파일이 미리 되어있는 녀석이기에 Statement 에 비해 성능상 이점이 있다. 요 녀석은 보통 조건절과 함께 사용되며 재사용이 되는데, ? 부분에만 변화를 주어 지속적으로 SQL을 수행하기 때문에 한눈에 무슨 SQL 문인지 파악하기는 어렵다.

2.와 같이 이용할 경우 해당 인자만 받아서 처리하는 구조로 갈 수 있는것입니다.내부적으로 상세하게 뜯어 보지는 않았지만, 2.는 생성시 메모리에 올라가게 되므로 동일한 쿼리의 경우 인자만 달라지게 되므로, 매번 컴파일 되지 않아도 된다는 결론이 날듯 합니다. 

3. API

(1) Preparedstatement

public interface PreparedStatement extends Statement 

프리컴파일 된 SQL 문을 나타내는 오브젝트입니다. PreparedStatement 는 Statement를 상속받고 있습니다. 

SQL 문은 프리컴파일 되어 PreparedStatement 오브젝트에 저장됩니다. 거기서, 이 오브젝트는 이 문장을 여러 차례 효율적으로 실행하는 목적으로 사용할 수 있습니다. 

(2) Statement

public interface Statement 

정적 SQL 문을 실행해, 작성된 결과를 돌려주기 위해서(때문에) 사용되는 오브젝트입니다. 

디폴트에서는 Statement 오브젝트 마다 1 개의 ResultSet 오브젝트만이 동시에 오픈할 수 있습니다. 따라서, 1 개의 ResultSet 오브젝트의 read가, 다른 read에 의해 끼어들어지면(자), 각각은 다른 Statement 오브젝트에 의해 생성된 것이 됩니다. Statement 인터페이스의 모든 execution 메소드는 문장의 현재의 ResultSet 오브젝트로 오픈되고 있는 것이 존재하면, 그것을 암묵에 클로우즈 합니다. 

그리고 FOR 문등을 통하여 동일한 SELECT 를 여러번 실행해야 하는 경우에는, 그 사용성에 볼때 2번이 훨씬 효과적이라고 볼 수 있습니다. 

(3) 예제

1) Statement 

String sqlstr = null; 

Statement stmt = null; 

ResultSet rst = null; 

FOR(int i=0; i< 100 ; i++){ 

sqlstr = "SELECT name, memo FROM TABLE WHERE num = " + i 

stmt = conn.credateStatement(); 

rst = stmt.executeQuerey(sqlstr); 

} 

2) PreparedStatement

String sqlstr = null; 

PreparedStatement pstmt = null; 

ResultSet rst = null; 

sqlstr = "SELECT name, memo FROM TABLE WHERE num = ? " 

pstmt = conn.prepareStatement(sqlstr); 

FOR(int i=0; i< 100 ; i++){ 

pstmt.setInt(1, i); 

rst = pstmt.executeQuerey(); 

}

4. PreparedStatement를 사용해야 하는 경우

(1) 사용자 입력값으로 쿼리를 생성하는 경우 

사용자에의해 입력되는 값을 가지고 SQL 작업을 할 경우 statement를 사용한다면 다음과 같이 될 것이다.

String content = request.getParameter("content");

stmt= conn.createStatement();

stmt.executeUpdate("INSERT INTO TEST_TABLE (CONTENT) VALUES('"+content+"');

사용자가 제대로 입력 하였다면 상관 없지만 content값에 "AA'AA"를 입력하였다면?

stmt.executeUpdate("INSERT INTO TEST_TABLE (CONTENT) VALUES('"+content+"'); 에서 에러가 발생할 것이다.

즉 SQL문은 다음과 같이 되는 것이다. INSERT INTO TEST_TABLE (CONTENT) VALUES('AA'AA');

이를 다음과 같이 수정한다면 위와같은 에러나 장애를 원천적으로 봉쇄할 수 있다

pstmt = conn.preapreStatement("SELECT * FROM TEST_TABLE WHERE CONTENT = :content");

pstmt.setString(1, content);

pstmt.executeUpdate();

이는 content값이 "'"가 들어왔다 하더라도 알아서 파싱 해주기 때문이다.

고로 사용자 입력 값으로 쿼리를 바인딩 할 경우에는 필히 pstmt를 사용하도록 하자! :)

(2) 쿼리 반복수행 작업일 경우

일반적으로 반복 수행 작업을 할 경우 아래와 같이 코딩 하게 된다.

1) Statement 사용

for (int i = 0; i < 100000; i++) {

stmt.executeUpdate("INSERT INTO TEST_TABLE VALUES('"+content+"');

}

2) PreparedStatement 사용

pstmt = conn.preapreStatement("INSERT INTO TEST_TABLE VALUES(?)"); <--- ⓐ

for (int i = 0; i < 10000; i++) {

pstmt.setString(1, content+i);

pstmt.executeUpdate();

}

종종 실수로 ⓐ번 문장이 for문으로 들어가는것을 보게된다! 주의!

cf.) DB의 종류에 따라 상황이 달라진다.

일반적으로 위와같은 코딩을 할 경우 2)경우가 1)보다 더 나은 성능을 보인다고 알려져 있다. 즉 자바의 PreparedStatement의 사용은 오라클 DB에서 bind변수를 사용하도록 함으로 해서 DB서버에 미리 준비된 SQL을 사용하게 되고 파싱과정을 생략하기 때문에 결국 DB리소스를 효율적으로 사용하도록 하는 방법이 된다.

하지만 이것이 DB서버에 따라 다르다. MySql같은 경우는 1)과2)의 성능차이가 거의 나지 않는다.

--> 적당한 PreparedStatement의 사용

위와같은 이유로 PreparedStatement가 좋다! 모든 쿼리를 PreparedStatement로 하자! 만약 이와 같이 된다면 또다른 문제가 생긴다. 각 DB마다 SQL캐싱할 수 있는 한계가 있기 때문에 정작 성능상 캐싱되어야 할 쿼리가 그렇지 않은 쿼리 때문에 캐싱이 안 될 수 있기때문에 꼭 필요한 문장만 PreparedStatement를 쓰는것을 권고한다.

5. Statement를 받드시 사용해야 하는 경우

(1) Dynamic SQL을 사용할 경우

Dynamic SQL을 사용한다면 매번 조건절이 틀려지게 됨으로 statement가 낫다. 즉 캐싱의 장점을 잃어버립니다. 또한 Dynamic SQL일 경우 코딩도 Statement가 훨신 편하다.

출처: http://devbox.tistory.com/entry/Comporison?category=577782 [장인개발자를 꿈꾸는 :: 기록하는 공간]

출처 : https://12bme.tistory.com/247

   http://kikook.tistory.com/499

일단 pro-c 를 사용하는 이유에 대해 알아봅시다.

현업자들의 Comment

Comment (1) : DB처리가 가장 빠른 건 PL/SQL입니다. 그다음이 pro*c고요
속도만이 문제였다면 아마 PL/SQL을 쓰겠죠. 
pro*c를 쓰는 이유는 PL/SQL이 단점을 가지고 있기 때문입니다. 부하분산이 어렵다는..
보통 pro*c를 쓸땐 TP-모니터를 같이 씁니다. 턱시도같은...
AP서버 여러대를 써서 부하분산을 합니다. 일반적인 쓰리티어 구조죠.

Comment (2) : 사실 자바시스템도 마찬가지긴 합니다. WAS가 트랜잭션처리와 UI처리를 같이 해주는 거 뿐이죠. DB처리 속도도 JDBC가 꾸준히 업그레이드되면서 많이 좋아졌습니다. 다만 미션크리티컬한 사이트에서는 자바의 트랜잭션 처리에 의구심이 강합니다. 은행같은 금융이 그렇죠. 자바초기에 사건이 많았었기 때문에.. ^^ WAS는 UI구성만 맡기고 졸트로 TP-모니터에 연결해 트랜잭션 처리는 pro*c에 맡깁니다. 비용은 많이 들겠지만 더 신뢰할 수 있는 시스템을 원한다면 그렇게 해야겠죠.

Comment (3) : pro*c를 쓰는 가장 큰 이유는 가장 안정적이기 때문입니다. 또한 배치 프로그램도 java 처럼 힙메모리 제한이 없어 대용량 데이터 처리에 안정적이고 알맞기 때문에 pro*c로 개발합니다. pro*c로 개발했다고 해서 java보다 DB처리 속도에 대한 이득은 없습니다.

pro-c 개요

Pro-c란 Oracle rdbms에 준비된 pro-c 툴은, sql문을 포함한 c 프로그램을 oracle 데이터베이스내에 있는 데이터에 접근과 조작을 할 수 있는 c 프로그램으로 변환하기 위한 것이다.

pro-c는 프리컴파일러이기 때문에 입력 파일 내에 있는 exec sql 문을 적당한 oracle 콜로 변환해서 출력 파일을 작성한다. 그 다음 이 출력 파일을 c프로그램에 대한 통상의 방법으로 컴파일하고 링크해서 실행모듈을 작성한다. 

SQL 문은 절차형 언어가 아닙니다. 그래서 오라클을 포함한 많은 데이터베이스는 PL/SQL이라는 절차형 언어를 제공합니다. PL/SQL은 오라클 내부에서 실행되는 프로그램으로서 오라클 내부라는 한정된 공간에서 실행되는 도구입니다.

DBMS의 버전이 높아지면서 이전관 다르게 PL/SQL에도 다양한 기능이 추가 되었습니다. 예를 들면, TCP/IP, HTTP, FILE I/O 등과 같은 기능이 추가되어 다양한 방식의 프로그래밍이 가능해졌습니다. 하지만 오라클 외부 프로그램과의 연동 등에 있어서는 많은 제약이 있습니다. 예를 들어, TCP/IP 통신을 하는 PL/SQL 문장을 작성하여 수행하는 중에 메모리 할당 등 예기치 않은 오류로 프로그램이 종료되는 상황이 발생할 수 있습니다. 프로그램이 오류로 인해 종료되는 부분은 프로그램을 수정하여 해결이 가능합니다. 하지만 이로 인해 오라클 엔진에 부하가 가해진다면 이는 돌이킬 수 없는 장애로 이어질 수 있습니다. 이렇듯 내부 PL/SQL을 통한 프로그램의 경우 오라클이라는 내부 울타리에서만 수행되는 특징이 큰 제약으로 작용합니다.

이런 고민을 해결하기 위해서 대부분의 DBMS 벤더는 외부 C 프로그램과 결합할 수 있는 선행 컴파일러를 제공하고 있으며 오라클에서는 이를 Pro*C라고 합니다. Pro*C는 PL/SQL과 같이 절차적 프로그래밍이 가능한 프로그램 도구로서 PL/SQL처럼 오라클 내부에서 수행되는 프로그램이 아니라 실행 가능한 외부 프로그램으로 작성이 되어 관련 작업을 수행할 수 있게 해 주는 도구입니다.

다만 일반 프로그램 언어들과 다른 점이 있다면, Pro*C만으로는 실행 파일을 만들어 낼 수 없다는 것입니다. 그래서 Pro*C를 Pre Compiler(설행 컴파일러)라고 합니다. Pro*C는 선행 컴파일러이기 때문에 선행 컴파일을 통해서 실행 파일이 아닌 C 컴파일러가 인식할 수 있는 출력 파일(xxx.c)을 생성합니다. 선행 컴파일러를 통해 생성된 C 프로그램은 C 프로그램의 통상적인 방법으로 컴파일되고, 오라클 라이브러리와 결합(링크)되어 실행 모듈이 만들어집니다.

Pro*C 프로그래밍을 위해서는 Pro*C 고유의 문법을 숙지하고 있어야 하지만 C 프로그래밍에 대한 기본 지식도 있어야 합니다. 특히 메모리와 관련된 부분이나 포인터 등과 같은 C의 고급 기능을 사용하기 위해서는 C 프로그래밍에 대한 지식을 갖추고 있어야 합니다. 그리고 C 프로그램은 기본적으로 OS의 특성에도 많은 영향을 받으므로 OS의 고유한 특성도 공부해야 합니다.

Pro*C를 처음 접하는 처음 접하는 분들에게 너무 많은 부담감을 드린 듯하지만 대부분의 개발자가 작성하는 일반적인 배치 프로그램이나 미들웨어 연동 프로그램의 경우에는 C에 대해 많이 알고 있지 않아도 정상적인 개발과 운영이 가능합니다. 물론 조금 더 심도 있는 프로그래밍을 위해서는 C의 고급 기능이나 OS의 고유한 특성을 자세히 알고 있어야 합니다.

그러나 이 책을 공부함에 있어서 C에 대해 많이 알고 있지 않아도 무방합니다. 기초 단계를 넘어 고급 단계로 가기 위해서 무엇이 필요한지 미리 알아본 것이므로 크게 부담감을 갖지 않아도 됩니다.

 프로그램 작성 방식

4GL 프로그램으로 DB 핸들링 프로그램을 작성하기 위해서 가장 먼저 할 일은 DB 접속 방식을 선택하는 것입니다. ODBC 방식으로 할 것인지, OLE-DB 방식으로 진행할 것인지를 선택한 후에 그에 맞는 프로그램 방식을 결정해야 합니다.

Pro*C 역시 다르지 않습니다. GUI 방식의 4GL 프로그램처럼 마우스 클릭을 통해 설정할 수 있는 것이 아니라 코딩 시점에 Header 파일 선정, 프로그램 방식의 선정, 컴파일 옵션의 변경을 통해 작성 방식을 선택하여 사용할 수 있습니다. Pro*C에서 프로그램 작성 방식은 두 가지로 나누어지며, 하나는 내장 SQL 방식이고, 다른 하나는 OCI(Oracle Call Interface) 방식입니다.

(1) 내장 SQL 방식

내장 SQL 방식이란 C 프로그램 내부에서 'EXEC SQL'이라는 접두사 뒤에 SQL 문장을 직접 기술하는 방식입니다. 내장 SQL 방식은 통상적으로 가장 많이 사용됩니다. 우리에게 익숙한 윈도우 환경의 개발툴과 비교해 본다면, OLE나 ODBC를 통해 데이터베이스와 연결하여 작업하는 방식의 프로그램과 비슷하다고 할 수 있습니다. 

이 책에서도 가장 대중적인 방식인 내장 SQL 방식을 기준으로 실습 예제를 만들고 설명할 것입니다. 내장 SQL 문장에는 일반 SQL 문장뿐만 아니라 오라클을 이용한 다양한 형식의 내부 문장을 사용할 수 있으며, 미리 생성되어 있는 Stored Procedure, Package 또는 개발자가 임의로 작성한 PL/SQL도 사용할 수 있습니다. 즉, 오라클 데이터베이스에서 사용하는 모든 문장, 즉 DML, DDL, DCL, PL/SQL, 일반 SQL 문장을 내장 SQL 문장에 사용할 수 있습니다.

내장 SQL 문장에 사용할 수 있는 명려어는 아래 표(내장 SQL 문장 구문 방식의 프로그램 구문)와 같습니다.

(2) OCI 방식

OCI(Oracle Call Interface) 방식이란 OCI 라이브러리를 통해서 오라클 SQL 문장을 직접 호출하여 사용하는 방식입니다. 내장 SQL 방식에 비해서 조금 더 하위 레벨에 해당하는 프로그래밍 방식으로서 OCI를 통해서 DB 핸들링 작업을 실행하기 때문에 데이터베이스 서버의 자원을 효율적으로 관리하고 SQL 문장 수행의 각 단계를 직접 제어할 수 있다는 장점이 있습니다.

그러나 프로그래밍 방법에 있어 내장 SQL 프로그램에 비해 복잡하기 때문에 개발자의 숙련도가 요구된다는 단점이 있습니다. Unix 환경의 C 프로그램을 대부분의 개발자들은 쉽게 생각하기보다는 어렵게 생각하고 있습니다. 이는 대부분의 제어를 개발자가 직접 기술해야 하기 때문입니다. 반면에 GUI 프로그램은 마우스 클릭 몇번만으로 대부분의 설정이 가능해집니다. 이렇듯 OCI 프로그램에서는 모든 부분의 직접 제어나 기술이 내장 SQL 프로그램에 비해서 어렵게 여겨지는 것입니다. 물론 대부분의 프로그램에는 특정한 패턴이 있으므로 그 패턴을 익히기만 하면 어떤 프로그램 방식이 더 쉽다, 혹은 더 어렵다고 단정지을 수는 없습니다.

대부분의 경우라고 할 수는 없지만 적지 않은 개발자들이 내장 SQL 문장 방식을 사용하고 있습니다. 이 책에서도 내장 SQL 문장 방식을 사용하며, OCI 방식 프로그램에 대해서는 다루지 않습니다. OCI 방식의 프로그램 방법을 더 자세히 알고 싶으면 오라클 사이트에 관련 예제를 어렵지 않게 구할 수 있으므로 이를 참고하면 됩니다.

1-4. Pro*C의 데이터 형

Pro*C에서 사용하는 데이터 형은 C 프로그램에서 사용하는 일반적인 데이터 형과 Pro*C에서만 사용할 수 있는 고유한 형태의 데이터 형으로 구성되어 있습니다. 아래의 내용은 Pro*C 프로그램에서 사용하는 데이터 형을 설명한 것입니다.

• 기본적인 데이터 형의 일차원 배열
• CHAR 데이터 형과 VARCHAR 데이터 형의 이차원 배열
• 출력 호스트 변수로 VARCHAR 변수를 사용할 때 오라클은 구조체의 멤버 길이를 설정하지만 배열을 NULL-terminate(\0)시키지는 않습니다. 그러므로 출력하기 전에 출력 호스트 변수를 NULL-terminate 시키십시오. CHAR은 자동 NULL-terminate입니다.
• 기본적인 데이터 형에 대한 포인터
• 사용자 정의의 typedef
• 구조체
• 배열의 구조체
• 구조체에 대한 포인터
• 구조체 배열

아래의 [표 1-3]에서는 C 프로그램에서 일반적으로 사용할 수 있는 데이터 형의 종류와 각 데이터 형의 세부 내용을 설명하였습니다. Pro*C 역시 C 프로그램이기 때문에 C의 일반적인 데이터 형을 사용할 수 있습니다.

오라클 데이터베이스의 데이터 형과 C 프로그램의 데이터 형은 호환됩니다. 그 내용을 아래 표와 같이 정리할 수 있습니다.

오라클의 char을 C 프로그램의 char로 치환하고, number을 in, long, short로 치환해서 사용할 수 있습니다. 그러므로 오라클 데이터베이스와 연결해서 프로그래밍을 함에 있어서 큰 제약이 없습니다.

VARCHAR 형의 경우에는 데이터 형의 특성상 C 프로그램의 char 데이터 형과는 다른 특이한 형태를 가지고 있습니다. Pro*C 프로그램에서 VARCHAR uid[20];로 선언하면 실제로는 아래와 같은 형태로 생성됩니다.

오라클 데이터베이스에서 VARCHAR2 데이터 형의 선언은 가변 길이 문자열로, 데이터형 선언시 정의된 데이터 길이 내에서 다양한 길이의 데이터를 가질 수 있다는 것입니다. 좀더 쉽게 설명하자면 'VARCHAR2 CODE[40]'로 선언하고 '1'이라는 데이터를 할당하면 전체 40바이트의 길이 중 1바이트만 사용이 되고 나머지 39바이트는 빈 공간으로 남게되어 공간의 효율적 활용이 가능해지며 이러한 것을 가변 길이 문자열이라고 하는 것입니다. 

이와 같이 Pro*C의 VARCHAR 형도 오라클 데이터베이스의 VARCHAR2와 동일하게 가변 길이로 할당이 되어 사용된다는 것입니다. 다만 Pro*C만의 특징이 있다면, Pro*C에서 VARCHAR로 선언하는 것은, 내부적으로는 가변적 데이터 할당이 가능한 문자형 구조체로 선언하는 것과 같습니다.

구조체로 선언하지 않더라도 구조체의 특성을 가지므로 변수에 값을 할당하는 방법도 일반 char 변수와 다르게 이루어집니다. 뿐만 아니라 명시적으로 포인터로 선언하지 않더라도 VARCHAR 형은 데이터를 포인터로만 전달할 수 있기 때문에 데이터 할당과 출력에 있어 일반 데이터 형과 같은 형태가 아닌 포인터의 처리 프로세스를 따릅니다. 

char 형으로 선언하고 할당하려면 아래와 같이 진행하면 됩니다. 이 형식은 C 프로그램과 동일하며, 사용법에 있어서도 전혀 차이가 없음을 알 수 있습니다.

char 형과 달리 VARCHAR형의 경우에는 다음과 같이 선언하고 할당 합니다.

VARCHAR 형은 선언과 동시에 len과 arr이라는 멤버를 갖는 구조체로 선언됩니다. 구조체이지기 때문에 앞서 확인한 char 형의 변수 데이터 할당 방법과 동일하게 사용할 수 ㅇ벗습니다.

구조체 변수에 대한 데이터 할당을 위해서는 구조체를 이루고 있는 각 멤버에 데이터를 할당해야 합니다. 물론 경우에 따라서는 구조체에 대한 할당만으로도 각 멤버에 대한 데이터 할당이 가능하기도 합니다. 이는 내부적으로 볼 때 자동적으로 각 멤버에 대한 할당이 이루어지는 것입니다. 대표적으로 Pro*C에서 데이터 추출 시 사용하는 FETCH 구문이 이에 해당됩니다.

VARCHAR 형도 구조체입니다. 그렇기 때문에 데이터를 할당하기 위해서는 구조체를 이루고 있는 각 멤버에 데이터를 할당해야만 합니다. 뿐만 아니라 데이터 전달이 포인터로만 가능하기 때문에 데이터 형에 대한 포인터로의 형 변환(Type Cast)도 필요합니다.

Pro*C의 VARCHAR형과 char 형은 사용 방법에서만 차이가 있는 것이 아닙니다. 저장 공간에 있어서도 차이가 있습니다. char 형은 Fixed Data Type으로 데이터 형의 길이만큼 저장 공간을 차지하고, VARCHAR 형은 데이터의 길이와 상관없이 할당받은 데이터 길이만큼의 저장 공간을 갖습니다. 사용의 편이성만 놓고 보자면 구조체로의 형변환도 안되고, 포인터로의 형번화도 필요없는 char형이 더 좋아보이지만 저장 공간, 즉 메모리 공간의 효율적 사용이라는 측면에서 본다면 VARCHAR 형은 char 형과 견주어 절대 뒤지지 않는 훌륭한 데이터형입니다.

이러한 장점이 작은 규모의 프로그래멩서는 큰 차이를 갖지 않지만, 사용자의 요청이 많은 프로그램이나 핸들링 해야 할 데이터가 많은 프로그램의 경우에는 성능 차이가 점점 크게 벌어지므로 각자 처한 상황에 맞게 충분히 고려한 후에 적절한 데이터 형을 사용해야 합니다.

Pro*C 프로그램에서 저장 공간과 처리 방법에 있어서 차이를 보이는 데이터 형은 char 형과 VARCHAR 형 두 개 밖에 없습니다. 다른 데이터 형은 C 프로그램에서 사용되는 일반 데이터 형과 동일합니다. 그러므로 두 데이터 형을 사용할 때는 주의하기 바랍니다.

left, right, inner, outer join에 대해서 알아보자.

위의 join 들을 검색했을 때 가장 많이 나오는 사진은 다음과 같다.

결과 부터 말하자면 INNER JOIN은 JOIN과 같은 말이고

LETF JOIN과 LETF OUTER JOIN도 같은 말이다.

쉽게 말해 INNER JOIN은 교집합 OUTER JOIN (LETF JOIN, RIGHT JOIN)은 여집합의 개념, FULL JOIN은 합집합이 되겠다.

NATURAL JOIN은 JOIN 조건이 '=' 일 때 동일한 속성이 두번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한번 만 표기하는 방법이다.

다음과 같은 예시를 통해 살펴보자.

학생 테이블

학과 테이블

성적 테이블

학생 테이블과 학과 테이블을 조인시켜서 LEFT JOIN, RIGHT JOIN, INNER JOIN에 대해서 알아보도록 하겠다.

1. LEFT JOIN

밑에 예시는 ms-sql 쿼리문에서 따온것이다.

oracle의 경우에는 (+)를 통해 outer조인이 형성된다.

oralce의 경우를 적어보겠다.

(oralce) select *from 학생,학과 where 학생.학과코드 = 학과.학과코드(+);

(mssql) select * from 학생 LEFT (OUTER) JOIN 학과 ON 학생.학과코드 = 학과.학과코드;

위와 같은 결과를 보면 INNER JOIN의 결과물에서 왼쪽 테이블의 조인조건에 일치하지 않는 행들도 모두 출력한다. (조인조건의 일치하지 않는 경우 오른쪽 테이블의 컬럼값은 존재하지 않으므로 null로 출력된다.

2.RIGHT JOIN

select * from 학생 RIGHT JOIN 학과 ON 학생.학과코드 = 학과.학과코드;

RIGHT JOIN은 딱히 다시 말 할 필요가 없는게 LETF JOIN과 같고 오른쪽 왼쪽만 바꾼거라 생각하면 된다.

3.INNER JOIN

select * from 학생 INNER JOIN 학과 ON 학생.학과코드 = 학과.학과코드;

INNER JOIN은 두 테이블을 JOIN 시킨 결과물이라고 생각하면 된다.

INNER JOIN과 JOIN은 같은 결과물을 볼 수 있다.

4.NATURAL JOIN

select * from 학생 NATURAL JOIN 학과;

NATURAL JOIN은 두 테이블을 JOIN 시킨 결과물에서 JOIN에 이용된 속성의 값을 빼 준 나머지값이 출력된다.