Accessing SQL from a programming language
SQL의 쿼리로 표현할 수 있는 문법에 한계가 있기 때문에 SQL은 general perpose programming과 병행할 수 있어야 한다.
SQL 기능 접근은 주로 API 함수 호출 또는 내장 SQL을 통해 이뤄진다.
JDBC
SQL에 접근할 수 있는 JAVA의 API이다.
JDBC를 활용하려면 우선 응답 성공 여부를 체크하고, 그 다음에 코드를 작성하면 된다.
Prepared statement
DB에 쿼리를 미리 준비해두고 여러 번 수행할 수 있는 함수 같은 객체이다.
이 경우 쿼리와 입력값을 구분하므로 SQL injection 을 확실하게 방지할 수 있다.
한 번에 여러 값을 가진 객체를 DB에 올리려면 values()를 활용한다.
transaction control
각 SQL이 하나의 트랜잭션으로 구성되어있다. update, insert등의 기능을 사용하면 자동으로 트랜잭션이 생긴다.
실제로 트랜잭션을 만들 때는 우선 자동 커밋(automatic commit)을 끄고, 코드를 작성한 다음 conn.commit() 을 통해 커밋 타이밍을 조절할 수 있다.
자동 커밋은 conn.setAutoCommit()의 값을 false로 설정해서 끄고 true로 설정해서 켤 수 있다.
ODBC
ODBC 는 open database connectivity의 약자로, db에 접근하기 위한 api의 표준이다.
데이터베이스는 각자 개성도 다르고 통신 규격도 다르다. 이때 ODBC가 중재자 역할을 하여 DB의 종류에 상관 없이 접속할 수 있도록 돕는다.
각 DBMS가 ODBC에 맞춰 제작한 드라이버를 ODBC Driver 라고 부른다.
또한 이 드라이버들을 관리하는 라이브러리를 Driver Manager 라고 한다.
Embedded SQL
다른 프로그래밍 언어에 내장된 SQL을 임베디드 SQL이라고 부른다.
이 경우 SQL을 지원하는 언어를 host 언어라고 한다. 호스트 언어로는 C/C++, JAVA, 포트란 등이 있다.
external language routines
SQL 외의 다른 언어로 만든 함수 또는 프로시저이다.
이 경우 SQL만으로는 어려운 복잡한 작업이 가능해진다. 다양한 알고리즘에 최적화된 코드를 이용해서 성능 향상도 기대할 수 있다.
Function과 Procedure
함수와 프로시저 모두 특정 작업을 수행하기 위한 코드 블록이다. 둘 다 파라미터를 가진다.
하지만 함수는 값을 반환하고 프로시저는 작업을 수행한다는 점이 다르다.
예를 들어 특정 연산을 수행한 수 결과를 알고 싶다면 함수를 쓰고, 데이터를 여러 개 집어넣는 일련의 작업을 하고 싶다면 프로시저를 써야 한다.
그래서 프로시저가 더 복잡한 작업을 수행할 수 있다.
function
create function 으로 함수를 만들 수 있다.
다른 코딩 언어랑 비슷한 구조이다.
create function func_name (paramter)
returns integer
begin
...
return a;
end함수를 이용해서 테이블을 반환하게 할 수도 있다. 이런 함수를 table function이라고 부른다.
create function table_function()
returns tableTrigger
트리거는 특정 이벤트가 발생했을 때 자동으로 수행되는 스크립트이다.
트리거를 만들기 위해서는
- 트리거가 수행될 상황을 특정한다.
- 트리거가 수행할 액션을 특정한다.
트리거가 반응하는 이벤트는 insert, delete, update가 있다.
예를 들어 새로운 고객이 회원가입할 때마다 가입 시간을 타임 테이블에 자동으로 기록하는 식으로 트리거를 이용할 수 있다.
트리거는 데이터 변경 전에 실행하는 before와 후에 실행하는 after 로 나뉜다.
예시)
after update of takes on grade
트리거를 사용하면 안 되는 순간들
데이터 요약본(평균 등) 유지, 데이터 복제를 할 때는 트리거를 쓰지 않는 것이 좋다.
보통 이런 기능들은 DB 상에서 자체 기능을 제공하기 때문에 내장으로 캡슐화된 기능을 쓰자.
만약 트리거를 잘못 쓴다면 수행되기 전에 취소되는 등의 상황을 겪을지도 모른다.
Recursion
with recrsive rec(parameter) as (select~)함수처럼 쓸 수 있는 재귀 sql문.
oracle에서는 recursive 키워드를 쓰지 않고, union 대신 all을 쓴다.
advanced aggregation function
rank
rank() over ()
항목에 순위를 매기는 함수, dense_rank()를 쓰면 순위에 간격을 남기지 않는다.
예를 들어 1등이 두 명이라면, 원래는 다음 순위는 3등이 되어야 하지만 dense 키워드가 붙은 경우 2등으로 표시한다.
over안에는 순위 기준과 partition을 지정해줄 수 있다.
- order by : 랭킹의 기준을 정한다.
- partition : group by와 비슷하게 기준을 두고 partition을 생성한다.
A분야에서 1,2,3등, B분야에서 1,2,3등 식으로... - null first/null last : null값의 위치를 정의한다.
Windowing
행과 행 사이의 관계를 정의하는 함수이다.
