chapter 10. SQL 프로그래밍 기술 소개

데이터베이스 프로그래밍 소개

데이터베이스 애플리케이션

호스트 언어

JAVA, C/C++/C#, COBOL 및 다른 프로그래밍 언어

데이터 하위 언어

기술 및 이슈

대화형 인터페이스: SQL 명령을 직접 모니터에 입력

예: sqlplus (또는 SQL Developer), mysql shell, psql, db2 등

명령 파일 실행: @<filename>.sql (Oracle에서)
응용 프로그램 또는 데이터베이스 애플리케이션

사용자 프로그램 또는 웹 인터페이스를 통해 데이터베이스에 액세스

데이터베이스 프로그래밍의 3가지 접근 방식

데이터베이스 명령을 일반 목적 프로그래밍 언어에 포함 (일반적)

데이터베이스 문장은 호스트 프로그래밍 언어에 포함됩니다.

이러한 문장은 특수 접두사 또는 EXEC SQL에 의해 식별됩니다.

전처리기 또는 프리프로세서가 소스 프로그램 코드를 스캔하여 데이터베이스 문장을 식별하고 DBMS에서 처리하기 위해 추출합니다. 이 기술은 일반적으로 "내장 SQL"로 알려져 있습니다.

데이터베이스 함수 라이브러리 사용 (가장 일반적)

호스트 프로그래밍 언어 (예: JAVA 또는 C(++))에서 사용 가능한 함수 라이브러리를 사용합니다. 이것은 응용 프로그래밍 인터페이스(API)라고도 합니다.

이 라이브러리에는 DBMS에 액세스하는 다양한 함수가 포함되어 있습니다.

예시:

ODBC (Open DataBase Connectivity): 2016년 6월 현재 버전 4

C 언어용

JDBC (Java DataBase Connectivity): 2017년 9월 현재 버전 4.3

Java SE 9에 포함
JAVA 언어용; Java 호환 객체 데이터베이스 사용으로 임피던스 미스매치가 크게 줄어듦

새로운 언어 설계 (덜 일반적)

"데이터베이스 전용" 프로그래밍 언어 (DBPL)를 처음부터 설계합니다. 이 언어는 데이터베이스 모델과 쿼리 언어와 호환될 수 있도록 설계됩니다.

데이터베이스 액세스가 집중적인 애플리케이션에 더 적합합니다.

데이터베이스 프로그래밍 언어에 루프 및 조건문과 같은 추가 프로그래밍 구조가 "추가"됩니다. 왜냐하면 이를 완전한 프로그래밍 언어로 변환하기 위함입니다.

클래식 DBPL 예제:

Oracle의 PL/SQL
SQL Server의 T-SQL
이미 PL/SQL로 작성된 PSM을 랩 #6에서 경험했습니다.

DBMS 플랫폼 및 DB 프로그래밍 유형

Chapter 10.3.2

JDBC: Java 프로그래밍을 위한 SQL 클래스 라이브러리

(2) 데이터베이스 함수 라이브러리 접근 방식에 해당합니다.

JDBC (Java Database Connectivity)

DBMS 연결/작업을 위한 Java 기반 API 세트

드라이버로도 알려져 있음

다양한 DBMS(Oracle, DB2, SQL Server, MySQL, PostgreSQL, JavaDB, Sqlite, SAP HANA 등)에 접근하기 위해 설계됨

주요 이점: 단일 (Java) 프로그램이 "다중" 데이터베이스에 연결하고 그 위에서 작업할 수 있음

DBMS에 독립적이지만, 먼저 선택한 DBMS와 관련된 적절한 드라이버를 명시적으로 로드해야 함

JDBC 프로그래밍 환경

사용된 소프트웨어 버전 (이 실험에서 사용됨)

이후 슬라이드에서 Oracle JDBC 드라이버를 설정할 것임

항목

시스템/프로그램

DBMS

Oracle DBMS 19c (23c/21c/12c/11g)

Java 컴파일러 (javac)

JDK 8 (또는 9)

JDBC 드라이버

Oracle JDBC 라이브러리: ojdbc10.jar

Oracle DBMS 릴리스에 특화된 JDBC/JDK 버전

Oracle 릴리스

JDBC Jar 파일

21c/23c

JDK11/17에서 인증된 ojdbc11.jar; JDBC 4.3 구현
JDK8, 9, 11에서 ojdbc8.jar; JDBC 4.2 구현

JDK11/17에서 인증된 ojdbc11.jar; JDBC 4.3 구현
JDK11에서 ojdbc10.jar; JDBC 4.3 구현
JDK8, 9, 11에서 ojdbc8.jar; JDBC 4.2 구현

12cR2

JDK8/11에서 ojdbc8.jar

11gR2

JDK6/7/8에서 ojdbc6.jar

JDBC를 통한 데이터베이스 연결 시 Java 객체 간의 관계

JDBC와 관련된 주요 패키지들

Oracle PL/SQL

PL/SQL (Procedural Language/Structured Query Language)

DB 응용 프로그램에서 사용되는 Oracle 전용 언어

여러 SQL 문을 하나의 블록으로 그룹화하고 전체 블록을 단일 호출로 데이터베이스 서버에 보내므로 성능을 향상시킴

변수, 루프, 제어 구조는 일반 목적 언어(GPL)처럼 지원됨

따라서 SQL로만 처리할 수 없는 문제를 해결할 수 있음

실행 중 예외 처리 지원

전문 기능을 수행하기 위한 프로시저 또는 함수를 빌드하는 기능 제공

PL/SQL 프로그램은 모든 코드가 DBMS 내부에서 생성되고 처리되므로 GPL(예: JDBC와 유사한) 프로그램보다 훨씬 빠름

PL/SQL 블록

프로그램에서 논리적으로 분할할 수 있는 기본 단위

PL/SQL에서 처리의 최소 단위

구문


DECLARE -- 선언 섹션
(스칼라) 변수, 커서, 사용자 정의 예외
BEGIN -- 실행 섹션
SQL 문, PL/SQL 문
EXCEPTION -- 예외 섹션
오류 발생 시 수행할 작업
END;

PL/SQL에서의 변수와 유형

GPL의 개념과 동일

DECLARE 섹션에서 선언하고 실행 섹션에서 사용

제어 흐름: IF 문 패밀리

GPL에서의 IF (THEN ELSE) 문 사용과 유사

제어 흐름: IF 예제


IF condition THEN
   statement1;
ELSIF condition THEN
   statement2;
ELSE
   statement3;
END IF;

제어 흐름: CASE-WHEN 문

SQL의 CASE-WHEN과 유사

제어 흐름: LOOP 문

GPL의 do-while과 유사

제어 흐름: WHILE 문

GPL의 WHILE과 유사

제어 흐름: FOR 문

GPL의 FOR과 유사

구문


FOR COUNTER IN [REVERSE] start...end LOOP
   statement1;
   statement2;
   ...
END LOOP;

COUNTER: 암시적으로 증가 또는 감소

REVERSE: 순환 순서가 뒤바뀜

커서 (중요!)

SQL 문이 실행될 때마다 Oracle DBMS는 해당 문을 해석하고 처리한 결과를 저장하기 위한 전용 메모리 영역을 사용합니다.

이 메모리 영역을 가리키는 것이 커서입니다.

커서 유형

유형	설명
암시적 커서	- PL/SQL에서 자동으로 선언됩니다. SELECT 문이 하나의 행 또는 모든 DML 문에 반환됩니다. <br> - 지금까지 표시된 출력에 사용되었습니다.
명시적 커서	- 사용자에 의해 선언되며, 여러 행을 반환하는 SELECT 문에 사용됩니다.

암시적 커서의 속성

속성 이름	설명
SQL%ROWCOUNT	가장 최근 SQL 문에 영향을 미친 행의 수를 나타냅니다.
SQL%FOUND	가장 최근 SQL 문에 의해 영향을 받는 행이 있는 경우 TRUE를 반환합니다.
SQL%NOTFOUND	가장 최근 SQL 문에 의해 영향을 받는 행이 없는 경우 TRUE를 반환합니다.
SQL%ISOPEN	암시적 커서의 경우 항상 FALSE로 설정됩니다. 커서가 실행된 후 닫힙니다.

명시적 커서: 사용자에 의해 선언됨

명시적 커서는 사용자에 의해 선언되며, 여러 행을 반환하는 SELECT 문에 사용됩니다.

명시적 커서 사용

명시적 커서를 사용하려면 다음 단계를 따릅니다:

커서를 선언합니다.

커서를 열고 활성화합니다.

커서에서 현재 행을 변수에 로드합니다.

다음 행으로 이동합니다.

커서를 닫습니다.

커서 FOR LOOP 문

구문


FOR record_name IN cursor_name LOOP
   Statement1;
   Statement2;
   ...
END LOOP;

이 구문은 훨씬 더 편리하고 단순화된 방식으로 명시적 커서를 사용합니다.

저장 프로시저 (Stored Procedure)

프로그램 로직을 구현합니다 (PL/SQL로).

객체로 존재하며 DBMS에서 사용됩니다.

GPL의 함수와 유사합니다.

실행에 대한 지정된 작업 순서가 있는 독립 프로그램을 나타냅니다.

PROCEDURE, FUNCTION 및 TRIGGER를 포함합니다.

정의한 후 DBMS에 저장됩니다.

이것이 저장 프로시저 또는 지속적 저장 모듈로도 불리는 이유입니다.

재 컴파일 할 필요 없음; 여러 번 호출 가능

결과를 반환할 수 있거나 (RETURN을 통해) 반환 값을 갖지 않을 수 있습니다 (항상 반환 값을 갖는 DBMS 함수와 대조적).

다음은 주어진 텍스트를 마크다운 형식으로 해석한 내용입니다:

(1) CREATE PROCEDURE 문

프로시저를 정의합니다.

프로시저를 어떻게 정의하나요?

PL/SQL에서 프로시저는 BEGIN-END로 구성됩니다.
BEGIN에서 변수와 매개변수를 선언합니다.
END에서는 특정한 프로그램 로직 (절차적 동작)이 구현됩니다.

특정 매개변수는 프로시저를 호출할 때 (저장된) 프로시저에 전달할 값입니다.

변수는 저장된 프로시저나 트리거 내에서 사용됩니다.

주석은 /*와 */ 사이에 지정할 수 있으며 SQL에서처럼 '--' 뒤에 한 줄 주석을 지정할 수 있습니다.

구문


CREATE [OR REPLACE] PROCEDURE procedure_name (
   param_name mode [IN|OUT|IN OUT] data_type,
   param_name mode [IN|OUT|IN OUT] data_type,
   ...
) IS
[Variable;]
AS
BEGIN
   ...
END;

INSERT를 위한 프로시저 – 실행

구문: EXEC stored_procedure_name ( ... )

INSERT를 위한 프로시저 – 삭제

구문: DROP PROCEDURE stored_procedure_name

(2) CREATE FUNCTION 문

사용자 정의 함수를 정의합니다.

수학 함수처럼 몸체에서 값을 계산하고 반환합니다.

일반적으로 SQL 문이나 다른 프로시저에서 호출됩니다.

구문


CREATE [OR REPLACE] FUNCTION function_name (
   param_name data_type,
   param_name data_type,
   ...
) RETURN data_type IS
[Variable; ...]
BEGIN
   ...
   RETURN return_value
END;

예시: PL/SQL에서 정의된 함수를 통해 스칼라 값 반환하기

함수 정의:

함수 호출:

(3) CREATE TRIGGER 문

관련 DML (Insert, Delete 및 Update) 문이 실행될 때 자동으로 호출되는 프로시저를 정의합니다.

일반적으로 DML 문이 실행될 때 트리거가 작동합니다.

BEFORE, 2) INSTEAD OF 및 3) AFTER의 세 가지 시점에서 트리거가 작동합니다.

INSTEAD OF 트리거는 DML 문을 건너뛰고 다른 문을 실행합니다.

트리거는 주로 (i) 기본값 제공, (ii) 데이터 제약 조건 준수, (iii) 뷰 수정 및 (iv) 참조 무결성 위반 검사와 관련이 있습니다.

구문


CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE [| AFTER | INSTEAD OF] INSERT [| DELETE | UPDATE OF [Column, ...] ] table_name
[FOR EACH ROW] -- 행 수준 트리거 (기본값: 테이블 수준)
DECLARE
[Variable; ...]
BEGIN
   ... -- PL/SQL 블록
END;

트리거 예제 (1/3): 책 상점 로그

테이블과 해당 로그 테이블을 만듭니다.

삽입 로그를 위한 트리거를 생성합니다.

트리거 예제 (3/3): 확인!

Oracle에서 프로시저, 트리거 및 함수의 비교

ㅤ	프로시저	트리거	함수
정의 및 목적	저장 프로시저로 분류되며 Oracle에서 PL/SQL로 구현됩니다.	DML 문 실행 시 자동으로 호출되며 PL/SQL로 구현됩니다.	사용자 정의 함수로 정의되며 특정 작업을 수행합니다.
호출 방법	CREATE PROCEDURE 문을 사용하여 정의하고, EXEC 문을 사용하여 실행합니다.	CREATE TRIGGER 문을 사용하여 정의하며, DML 문 실행 시 자동으로 작동합니다.	CREATE FUNCTION 문을 사용하여 정의하고, SQL 문이나 다른 프로시젝에서 호출합니다.
반환 값	반환 값을 갖지 않습니다.	반환 값을 갖지 않습니다.	값을 계산하고 반환합니다.
사용 사례	복잡한 로직을 처리합니다.	DML 문 실행 전후에 특정 작업을 수행합니다.	계산이 필요한 경우, SQL 문에서 함수를 호출하여 값을 반환합니다.

요약

세 가지 데이터베이스 프로그래밍 접근 방식 비교

세 가지 접근 방식 비교

1) 내장 SQL 접근 방식

장점:

쿼리 텍스트가 프로그램 소스 코드의 일부입니다.

컴파일 시점에서 구문 오류를 확인하고 데이터베이스 스키마와 유효성을 검사할 수 있습니다.

이로 인해 프로그램이 상당히 가독성이 좋아집니다.

쿼리가 소스 코드에서 즉시 볼 수 있기 때문입니다.

쿼리가 미리 알려져 있기 때문에 쿼리 결과를 보유할 프로그램 변수를 선택하는 것이 간단합니다.

따라서 응용 프로그램의 프로그래밍이 일반적으로 더 쉽습니다.

단점:

실행 중에 쿼리를 변경하는 유연성 손실

쿼리에 대한 모든 변경은 전체 재컴파일 프로세스를 거쳐야 합니다.

실행 시간에 쿼리를 동적으로 생성해야 하는 복잡한 응용 프로그램의 경우 함수 호출 접근 방식이 더 적합할 것입니다.

2) 클래스 및 함수 호출 라이브러리 접근 방식

장점:

유연성 제공: 필요한 경우 런타임에 쿼리를 생성할 수 있어 더 많은 유연성을 제공합니다.

단점:

복잡한 프로그래밍: 런타임 쿼리 생성은 프로그래밍을 더 복잡하게 만듭니다. 프로그램 변수가 미리 알려지지 않은 쿼리 결과의 컬럼과 일치해야 할 수도 있습니다.

컴파일 시점의 검사 미실시: 쿼리가 함수 호출 내의 문장 문자열로 전달되므로, 컴파일 시점에 검사가 이루어지지 않습니다.

런타임에 모든 문법 검사 및 쿼리 유효성 검사 필요: 쿼리를 준비함으로써 모든 문법 검사와 쿼리 유효성 검사가 런타임에 이루어져야 합니다.

프로그래머의 추가 런타임 오류 체크 및 대응 필요: 프로그래머는 프로그램 코드 내에서 발생할 수 있는 추가적인 런타임 오류를 체크하고 대응해야 합니다.

단점:

실행 시간에 쿼리 생성은 프로그램 변수가 쿼리 결과의 열과 일치하지 않을 수 있어 프로그래밍이 복잡해집니다.

컴파일 시간에 체크를 수행하지 않으며, 쿼리는 함수 호출 내에서 문장 문자열로 전달됩니다.

모든 구문 검사 및 쿼리 유효성 검사는 실행 시간에 쿼리를 준비하는 과정에서 수행되어야 합니다.

프로그램 코드 내에서 추가 실행 시간 오류를 확인하고 처리해야 합니다.

데이터베이스 프로그래밍 언어 접근 방식 장점:

프로그래밍 언어 데이터 형식이 DB 데이터 형식과 동일하기 때문에 임피던스 미스매치 문제가 없습니다.

단점:

프로그래머는 새로운 프로그래밍 언어를 배워야 합니다.

일부 DB 프로그래밍 언어 (예: PL/SQL, T-SQL 등)는 특정 벤더에 종속적이며, 일반 목적 언어 (예: Java, Python 등)는 여러 벤더의 시스템과 쉽게 작동할 수 있습니다.