SQL + PL/SQL

MoonDD·2023년 8월 8일
PL/SQLsql
0

웹프로그래밍 기초다지기

목록 보기
4/4

SQL

1. 개요

SQL

  • Structed Query Language → 구조화된 질의 언어
  • DBMS에서 데이터를 조작, 관리하기 위한 프로그램 언어
  • 집합적 언어
    • 데이터를 특정 집합 단위로 분류해 단위별로 처리하는 언어
    • 절차적 언어 → 프로그래밍 순서대로 로직 처리

DDL

Data Definition Language

데이터베이스 객체를 관리

  • CREATE
  • DROP
  • ALTER
  • TRUNCATE

DML

Data Manipulation Language

데이터를 조작하는 언어

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • COMMIT
  • ROLLBACK

PL/SQL

  • SQL을 절차적으로 사용하는 언어
  • 변수 할당, 예외처리, 함수 생성 가능
  • DB 서버에서 컴파일 된다는 특징이 있다.

2. 데이터베이스의 객체

데이터베이스에 존재하는 논리적인 저장 구조

데이터베이스 객체의 종류

(1) 테이블

데이터를 담고 있는 객체

행과 열로 구성되어 있다.

데이터 타입

  • 문자 데이터 타입
    • CHAR : 고정
    • VARCHAR2 : 가변
  • 숫자 데이터 타입
  • 날짜 데이터 타입
    • DATE
    • TIMESTAMP
  • LOB 데이터 타입
  • NULL

제약조건

  • NOT NULL
  • UNIQUE
  • 기본키
  • 외래키 테이블 간의 참조 데이터 무결성을 위한 제약조건
  • CHECK 컬럼에 입력되는 데이터를 특정 조건에 맞는 지 확인

(2) 뷰

하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있다.

테이블, 뷰 참조하여 새로운 뷰 생성 가능

실제로 만들어진 테이블은 아니지만 테이블처럼 사용가능하다.

데이터 보안 측면에서 유리

(3) 인덱스

여러가지 인덱스 종류가 있지만 B-tree 인덱스가 가장 일반적이다

특정 컬럼들을 기준으로 정렬해 해당 컬럼으로 조회하는 기능의 성능을 높이려는 목적

하지만 데이터 동기화로 인해 오히려 너무 많이 만들면 오히려 과부화로 성능이 낮아질 수 있다.

(4) 시노님

동의어, 별명 같은 기능

  • Public : 모든 사용자가 접근 가능
  • Private : 특정 사용자에게만 참

사용하는 이유

  • 데이터 베이스 투명성 제공을 위해 사용다른 사용자의 객체를 참조할 때 많이 사용
  • 시노님이 참조하던 객체의 이름이 바뀌어도 이전에 작성한 SQL문 수정할 필요가 없다.
  • 별칭으로 사용되기에 원 객체를 숨길 수 있다.

(5) 시퀀스

자동 순번을 반환하는 데이터베이스 객체

(6) 파티션 테이블

논리적으로 1개 테이블이지만 물리적으로 분할한 만큼 파티션이 만들어저 컬럽 값에 따라 데이터가 분리되서 저장된다.

대용량 데이터테이블 조회 시 효율성과 성능을 높여준다.

3. SQL 문장

  1. SELECT

  2. INSERT

  3. UPDATE

  4. MERGE

    조건을 비교해 조건에 맞는 데이터가 없으면 → INSERT / 있으면 → UPDATE

  5. DELETE

  6. COMMIT

    변경한 데이터를 반영하는 역할

  7. ROLLBACK

    반영한 데이터를 이전 상태롤 되돌리는 역할

  8. TRUNCATE

    DELETE의 역할과 동일하지만 COMMIT을 하지않아도 반영이 되고 ROLLBACK X

  9. 의사컬럼

    테이블의 컬럼처럼 동작하지만 실제로 테이블레 저장 X

  10. 연산자

  11. 표현식

    1개 이상의 값과 연산자, SQL 함수로 결합된 식

    • CASE ~ WHEN ~ THEN / ELSE / END

  12. 조건식

    • 비교 조건식 논리 연산자 혹은 ANY, SOME, ALL 키워드로 비교
    • 논리 조건식
    • NULL
    • BETWEEN AND
    • IN
    • EXIST
    • LIKE

4. SQL 함수

(1) 숫자 함수

  • ABS(n)
  • CEIL(n), FLOOR(n)
  • ROUND(n, i), TRUNC(n1,n2)
  • POWER(n2, n1), SORT(n)
  • MOD(n2,n1), REMAINDER(n2,n1)
  • EXP(n), LN(n), LOG(n2,n1)

(2) 문자 함수

  • INITCAP(char), LOWER(char), UPPER(char)
  • CONCAT(char1, char2), SUBSTR(char, pos, len), SUBSTRB(char, pos, len)
  • LTRIM(char, set), RTRIM(char, set)
  • LPAD(expr1, n, expr2), RPAD(expr1, n, expr2)
  • REPLACE(char, search_str, replace_str), TRANSLATE(expr, from_str, to_str)
  • INSTR(str, substr, pos, occure), LENGTH(chr), LENGTH(chr)

(3) 날짜 함수

  • SYSDATE, SYSTIMESTAMP
  • ADD_MONTHS(date, integer)
  • MONTHS_BETWEEN(date1, date2)
  • LAST_DAY(date)
  • ROUND(date,format), TRUNC(date, format)

(4) 변환 함수

  • TO_CHAR(숫자 혹은 날짜, format)
  • TO_NUMBER(expr, format)
  • TO_DATE(char, format)

(5) NULL 관련 함수

  • NVL(expr1, expr2), NVL2(expr1,expr2,expr3)
  • COALESCE(expr1, expr2, …)
  • LNNVL(조건식)

(6) 기타 함수

  • GREATEST(expr1, expr2, …), LEAST(expr1, expr2, …)
  • DECODE(expr, search1, result1 ..)

5. 그룹 쿼리와 집합 연산자

(1) 기본 집계 함수

(2) GROUP BY절, HAVING 절

(3) ROLL UP절, CUBE 절

(4) 집합 연산자

  1. ROLL UP절 / CUBE

6. 조인과 서브 쿼리

내부조인 & 외부조인

(1) 내부 조인

  • 동등 조인 WHERE 사용
  • 세미 조인 IN, EXIST 사용하여 서브쿼리 이용
  • 안티 조인 NOT IN, NOT EXIST
  • 셀프 조인

(2) 외부 조인

조인 조건 모두에 (+)를 붙여야한다.

(3) 카타시안 조인

WHERE X

ANSI 조인

(1) ANSI 내부 조인

INNER JOIN + ON / USING + 테이블명.컬럼명 / 컬럼명

(2) ANSI 외부 조인

LEFT/RIGHT [OUTER] JOIN + ON

(3) CROSS 조인

= 카타시안 조인

(4) FULL OUTER 조인

서브쿼리

서브쿼리

SQL 문장안에서 보조로 사용되는 SELECT문

메인 쿼리를 제외한 나머지 모든 SELECT 문

조건식에 주로 사용된다.

인라인 뷰

FROM 절에 사용된느 서브 쿼리를 인라인 뷰라고 한다.

7. 고급 쿼리

계층형 구조

테이블에 저장된 데이터를 계층형 구조로 반환하는 쿼리를 계층형 쿼리라고 한다.

  • 최상위 계층 → START WITH
  • 계층형 구조 조건 → CONNECT BY

계층형 쿼리 활용

  • ORDERY BY
  • CONNECT_BY_ROOT
  • CONNECT_BY_ISLEAF
  • SYS_CONNECT_BY_PATH
  • CONNECT_BY_ISCYCLE

WITH 절

WITH 별칭1 AS (SELECT 문).
		 별칭2 AS (SELECT 문)
…
SELECT
FROM 별칭1, 별칭 2

별칭으로 사용하는 SELECT문에서도 별칭 참조가 가능하다

순환 서브 쿼리

WITH절을 활용해서 계층형 쿼리를 만들 수 있다.

조회의 출력 순서를 자식 혹은 형제 로우를 기준으로 출력할 지 정할 수 있다.

  • DEPTH FIRSTY BY
  • BREADTH FIRST BY

분석함수

테이블의 로우에 대해 특정 그룹별로 집계 값을 산출할 때 사용한다.

  • ROW_NUMBER()
  • RANK(), DENSE_RANK()
  • CUME_DIST, PERCENT_RANK()
  • NTILE
  • LAG LEAD

Window 절

파티션으로 분할된 그룹에 대해 범위를 정해 다시 부분 집합을 만든다.

{ ROWS | RANGE }
	{ BETWEEN { UNBOUNDED PRECENDING
							| CURRENT ROW
							| value_expr{ PRECEDING | FOLLOWING}
						}
		AND { UNBOUNDED FOLLOWING
					| CURRENT ROW
					| value_expr{ PRECEDING | FOLLOWING }
				}
	| { UNBOUNDED PRECEDING
			| CURRENT ROW
			| value_expr PRECEDING}
}

함수와 함께 사용될 수 있다.

  • FIRST_VALUE(), LAST_VALUE()
  • NTH_VALUE()
  • ….

다중 테이블 INSERT

INSERT ALL | FIRST
WHEN 조건 1 then
	INTO [스키마. ]테이블명(컬럼1, 컬럼2 .. ) VALUES(값1, 값2, ..)
..
ELSE
	INTO INTO [스키마. ]테이블명(컬럼1, 컬럼2 .. ) VALUES(값1, 값2, ..)
SELECT 문;

8. PL/SQL 구조와 구성요소

PL/SQL 기본 구조

블록

프로그램 소스의 기본 단위

구조

이름부 
IS(AS)
	선언부
BEGIN
	실행부
EXCEPTION
	예외 처리부
END;
  • 이름부
    • 이름 x : 익명블럭
    • 이름 o : 함수, 프로시저, 패키지

PL/SQL 구성요소

변수 선언

변수명 데이터 타입 := 초깃값;

상수 선언

상수명 **CONSTANT** 데이터 타입 := 상수값:

연산자

주석

  • -- 한줄 주석
  • / 여러 줄 주석 /

DML문

SELECT문에서 INTO절을 사용해서 변수에 할당해준다.

%TYPE

변수의 데이터 타입을 컬럼 타입으로 가져온다.

9. PL/SQL 함수, 프로시저

PL/SQL 제어문

  • IF문
  • CASE문
  • LOOP문
  • WHILE문
  • FOR문
  • CONTINUE문
  • GOTO문
  • NULL문

PL/SQL 사용자 정의 함수

함수생성

CREATE OR REPLACE FUNCTION 함수이름 (매개변수1, 매개변수2, ...)
RETURN 데이터타입;
IS[AS]
	변수, 상수 선언
BEGIN
	실행부
	...
	RETURN 반환값;
[EXCEPTION
	예외 처리부]
END [함수 이름];

프로시저

함수와 달리 특정한 로직만 처리하고 반환값 X

CREATE OR REPLACE PROCEDURE 프로시저 이름 
	(매개변수1[IN |OUT|IN OUT ] 데이터타입[:= 디폴트값],
		매개변수1[IN |OUT|IN OUT ] 데이터타입[:= 디폴트값] ,
		...
	)
IS[AS]
	변수, 상수 선언
BEGIN
	실행부
	...
	
[EXCEPTION
	예외 처리부]
END [함수 이름];

익명블록에서 함수, 프로시저, 패키지를 호출할때는 EXEC, EXECUTE를 붙이지 않는다.

10. 예외처리와 트랜젝션

예외처리

EXCEPTION WHEN 예외명1 THEN 예외처리 구문1
WHEN 예외명2 THEN 예외처리 구문2
...
WHEN OTHERS THEN 예외처리 구문n;
  • OTHERS는 맨 마지막 구문에 배치한다.
  • 사용자 정의 예외는 예외 발생 시 로그를 남기고 관리하기 위해 모듈화하는 것을 권장한다.

예외정보 참조

  • SQLCODE, SQLERRM

사용자 정의 예외

  1. 선언부에 사용자 정의 예외 선언
  2. PRAGAMA EXCEPTION_INIT(예외명, 코드) 이용해서 연결
  3. RAISE, RAISE_APPLICATION_ERROR를 이용해 예외 발생
  4. EXCEPTION WHEN ..을 이용해 예외 처리

트랜잭션

COMMIT

COMMIT [WORK];

테이블에 최종적으로 반영한다.

ROLLBACK

ROLLBACK [WORK] [TO [SAVEPOINT] 세이브포인트명];

변경사항 취소

SAVEPOINT

SAVEPOINT 세이브포인트명;

해당 savepoint 이전까지는 commit 된다.

11. 커서, 레코드, 컬렉션

커서

SQL문장을 처리한 결과값의 위치를 가리키는 일종의 포인터 역할을 한다.

  • 묵시적 커서 → 오라클 내부에서 자동 생성되어 사용
  • 명시적 커서 → 사용자가 직접 정의해서 사용

명시적커서의 사용단계

  1. 커서 정의

    CURSOR 커서명 [(매개변수1, 매개변수2, ...)]
IS
SELECT 문장;

  2. 커서 열기

    OPEN 커서명 [(매개변수1, 매개변수2, ...)]

  3. 패치

    1. LOOP 사용

      LOOP
	FETCH 커서명 INTO 변수1, 변수2, ...;
	EXIT HEN 커서명%NOTFOUND;
END LOOP;

    2. FOR문 사용

      FOR 레코드 IN 커서명(매개변수1, 매개변수2, ...)
LOOP
	처리문;
END LOOP;

  4. 커서 닫기

    CLOSE 커서명;

커서 변수

여러 쿼리에 재사용할 수 있는 커서 변수가 있다.

  1. 커서 변수 선언

    • 강한 커서 타입
      • 방법1
        TYPE 커서 타입명 IS REF CURSOR { RETURN 반환 타입};
커서_변수명 커서_타입명;
      • 방법 2 커서_변수명 REFCURSOR;
    • 약한 커서 타입
      • 방법1 TYPE 커서_변수명 IS REF CURSOR;
      • 방법2 커서_변수명 SYS_REFCURSOR;

  2. 커서 변수 사용하기

    OPEN 커서 변수명 FOR select 문;

  1. 커서 변수에서 결과 집합 가져오기
    • FETCH 커서 변수명 INTO 변수1, 변수2, …;
    • FETCH 커서 변수명 INTO 레코드명;

커서 표현식

컬럼 형태로 커서를 사용하는 것

CURSOR (서브 쿼리)

레코드

여러 개의 값을 가지고 각각 타입이 다른 복합형 구조이다.

테이블의 로우와 비슷한 형태이다.

테이블의 컬럼을 레코드에서는 필드라고 부른다.

사용자 정의형 레코드

TYPE 레코드명 IS RECORD (
	필드명1 필드1 타입 [[NOT NULL := 디폴트값],
	필드명2 필드2 타입 [[NOT NULL := 디폴트값],
	...
);
레코드변수명 레코드명;

테이블형 레코드

테이블의 모든 컬럼을 필드로 가지는 레코드

레코드 변수명 테이블명.%ROWTYPE;

커서형 레코드

커서를 레코드 변수로 받는 것

레코드 변수명 커서명%ROWTYPE;

컬렉션

같은 타입의 필드들이 나란히 연결되어 있는 로우가 여러개 모인 형태이다.

컬렉션의 종류

  1. 연관배열
    • 키-값
    • 사용자 정의 타입으로 사용 불가
    • TYPE 연관_배열명 IS TABLE OF 연관_배열_값타입 INDEX BY 인덱스타입;
  2. VARRY
    • 컬렉션에 들어갈 수 있는 요소의 개수에 제한이 있다.
    • 인덱스로 숫자만 가능하다
    • TYPE VARRY명 IS VARRAY(최대크기) OF 요소값 타입;
  3. 중첩 테이블
    • 요소 개수에 제한은 없다.
    • 생성자로 초기화가 가능하다
    • 숫자형 인덱스만 사용가능하다.
    • TYPE 중첩_테이블명 IS TABLE OF 값타입;

사용자 정의 데이터 타입

컬렉션을 선언하면 해당 블록의 실행이 완료되면 사라진다.

이때 해당 컬렉션을 다른 블록에서도 재사용하기위해 사용자 정의 데이터 타입을 사용하면 된다.

사용자 정의 타입으로 사용할 수 있는 타입

  • VARRY
  • 중첩 테이블
  • OBJECT

컬렉션 타입별 차이점과 활용법

  • 다차원 컬렉션 /컬렉션의 요소로 중첩 테이블, VARRY 타입을 사용하여 2차원 배열처럼 사용가능하다.
  • VARRY 중첩 테이블 사용자 정의 타입으로 정의를 해두면 데이터베이스의 객체로 저장되므로 일반 테이블의 컬럼 타입으로 사용가능하다.

12. 함수와 프로시저 관리, 패키지

패키지

논리적 연관성이 있는 항목들을 묶어둔 객체

패키지 구조

  • 패키지 선언부
    • 공용 항목
    • 데이터 : 변수, 상수, 예외 등 선언
    • 서브 프로그램 : 함수, 프로시저 명세
  • 패키지 본문
    • 내부 항목
    • 커서, 서브 프로그램 구현

패키지 사용

패키지명.변수명/상수명

패키지명.서브프로그램명

다른 프로그램에서도 변수, 서브 프로그램을 호출해서 사용할 수 있다.

참고도서

  • 오라클 SQL과 PL/SQL을 다루는 기술
profile
MoonDD
이전 포스트

Servlet & JSP

1개의 댓글

comment-user-thumbnail
happy
2023년 8월 8일

이런 유용한 정보를 나눠주셔서 감사합니다.

답글 달기