■ 문자형 함수

UPPER(문자값) 모든 문자를 대문자로 전환
LOWER(문자값) 모든 문자를 소문자로 전환
INITCAP(문자값) 문자를 단어별로 앞머리는 대문자 나머지는 소문자로 전환
CONCAT(문자값1, 문자값2) 두 문자열을 합성. || 연산자와 같은 용도로 사용
SUBSTR(문자값, a, b) 특정 문자열의 부분을 선택
a : 선택할 문자열의 시작 위치. 음수면 끝에서부터 시작
b : 선택할 문자열의 갯수. 생략할 경우문자열의 끝까지 선택
LENGTH(문자값1, a, 문자값2) 문자열의 길이를 구함
LPAD(문자값1, a, 문자값2) 왼쪽 문자 자리를 채움
a : 전체 채울 자리수
문자값2 : 채울 문자 (생략가능. 생략되면 공백 값)
RPAD(문자값1, a, 문자값2) 오른쪽 문자 자리를 채움
a : 전체 채울 자리수
문자값2 : 채울 문자 (생략가능. 생략되면 공백 값)
LTRIM(문자값1, 문자값2) 왼쪽 문자를 지움
문자값1에서 왼쪽에서부터 더이상 문자값2를 만나지 않을 때까지 지움
RTRIM(문자값1, 문자값2) 오른쪽 문자를 지움
문자값1에서 오른쪽에서부터 더이상 문자값2를 만나지 않을 때까지 지움
TRANSLATE(문자값, a, b) 특정 문자열을 대체
a : 대체하고 싶은 문자
b : 대체할 결과의 문자
REPLACE(문자값, a, b) 특정 문자열을 대신
a : 바꾸고 싶은 문자
b : 바꿀 결과의 문자

 

■ 숫자형 함수

ROUND(숫자값) 숫자를 반올림
TRUNC(숫자값, a) 숫자를 반내림
a : 숫자값을 반올림(버림)하여 a자리까지 돌려줌
(양수 : 소수이하자리, 음수 : 정수 부분 자리, 생략 : 0)
CEIL(숫자값) 지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환
FLOOR(숫자값) 지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환
MOD(숫자값, a) 나누기연산에서 나머지 구함
a 숫자값을 나누기 할 수 있음
POWER(숫자값1, 숫자값2) 거듭제곱
SQRT(숫자값) 제곱근
SIGN(숫자값) 양수인지 음수인지 0인지를 구별
CHR(숫자값) ASCII 값에 해당하는 문자를 구함
구분 설명
ABS 절댓값을 구함
COS COSINE 값을 반환
EXP e(2.71828183...)의 n승을 반환
FLOOR 소수점 아래를 잘라냄(버림)
LOG LOG 값을 반환
POWER(m, n) m의 n승을 반환
SIGN(n) n<0이면 -1, n=0이면 0, n>0이면 1을 반환
SIN SIN 값을 반환
TAN TANGENT 값을 반환
ROUND(대상, 자릿수) 특정 자릿수에서 반올림
TRUNC(대상, 자릿수) 특정 자릿수에서 잘라냄(버림)
MOD 입력 받은 수를 나눈 나머지 값을 반환

■ 날짜형 함수

SYSDATE 현재 시스템의 날짜 및 시간을 구함
LAST_DAY(날짜값) 지정한 날짜의 해당 월의 마지막 날짜를 구함
MONTHS_BETWEEN(날짜값1, 날짜값2) 두 날짜 사이의 개월 수를 구함
ADD_MONTHS(날짜값, 숫자값) 지정한 날짜로부터 몇 개월 후의 날짜를 구함
ROUND(날짜값, 자리수) 날짜에 대한 반올림
TRUNC(날짜값, 자리수) 날짜에 대한 버림

 

■ 반환형 함수

TO_CHAR 숫자나 날짜를 문자열로 변환
TO_NUMBER 문자를 숫자로 변환
TO_DATE 문자를 날짜로 변환

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--날짜 포맷
TO_CHAR(SYSDATE,'YYYYMMDD'),--20200607
TO_CHAR(SYSDATE,'YYYY-MM-DD'),--2020-06-07
TO_CHAR(SYSDATE,'YYYY/MM/DD'),--2020/06/07
TO_CHAR(SYSDATE,'YYYY'),--2020
TO_CHAR(SYSDATE,'YY'),--20
TO_CHAR(SYSDATE,'MM'),--06
TO_CHAR(SYSDATE,'MON'),--JUN
TO_CHAR(SYSDATE,'D'),--1 (주중의 일을 1~7로 표시(일요일 = 1)
TO_CHAR(SYSDATE,'DD'),--07
TO_CHAR(SYSDATE,'DDD'),--159 (1년기준 일 수)
TO_CHAR(SYSDATE,'DAY'),--SUNDAY (요일 표시)
TO_CHAR(SYSDATE,'DY'), --SUN (요일을 한자리로 표시)
TO_CHAR(SYSDATE,'WW'), -- 23 (일년 기준 주를 00 ~ 53 형태로 표시)
TO_CHAR(SYSDATE,'W'), -- 1 (한달 기준 몇번째 주)
TO_CHAR(SYSDATE,'DL'),--Sunday, June 07, 2020
 
--사간 포맷
TO_CHAR(SYSDATE,'AM'),--AM
TO_CHAR(SYSDATE,'PM'),--AM
TO_CHAR(SYSDATE,'HH'), --11 (시간을 0 ~ 12 형태로 표시)
TO_CHAR(SYSDATE,'HH24'), --11 (시간을 0 ~ 24 형태로 표시)
TO_CHAR(SYSDATE,'MI'), --15     (분을 00 ~ 59 형태로 표시)
TO_CHAR(SYSDATE,'SS'), --51     (초을 01 ~ 59 형태로 표시)
 
--세기 포맷
TO_CHAR(SYSDATE,'CC'), --21 (세기)
TO_CHAR(SYSDATE,'BC') --AD
 cs

 

 

■ 다중행 함수

COUNT(a) a의 행의 개수를 구함
AVG(a) a의 평균을 구함
SUM(a) a의 합계를 구함
MIN(a) a의 최소값을 구함
MAX(a) a의 최대값을 구함
STDDEV(a) a의 표준편차를 구함
VARIANCE(a) a의 분산을 구함

 

■ NULL 처리 함수 (단일행 함수)

NVL(값, 지정값) 값이 NULL인 경우 지정값을 출력
NVL2(값, 지정값1, 지정값2) 값이 NULL이 아닌 경우 지정값1을 출력하고 NULL인 경우 지정값2를 출력

 

■ 조건함수

DECODE(컬럼, 조건, TRUE 결과값, FALSE 결과값)

'프로그램 > DataBase' 카테고리의 다른 글

[Oracle] JOIN  (0) 2021.02.16
[Oracle] 프로시저, 함수의 이해  (0) 2021.02.16
[Oracle] 뷰(VIEW)의 이해  (0) 2021.02.16
[Oracle] 오라클 접속 및 PLSQL 사용법  (0) 2021.02.16
[Oracle] DDL (테이블 및 인덱스 생성)  (0) 2021.02.15

 

■ PL/SQL이란?

Procedural Language/SQL의 약자로 SQL만으로는 구현이 어렵거나 구현 불가능한 작업을 수행하기 위해 오라클에서 제공하는 절차적인 프로그래밍 언어. 

변수 · 조건 처리 · 반복 처리 등 다른 프로그래밍 언어에서 제공하는 다양한 기능을 사용할 수 있다.

 

데이터 트랜잭션 처리 능력이나 정보보호, 데이터에 대한 보안, 예외처리 기능, 객체 지향 등 데이터 베이스와 관련된 중요한 기능을 지원하는 데이터베이스 업무를 처리하기에 최적화된 언어이다.

 

■ PL/SQL 사용하는 이유

  1.  대용량 데이터를 연산해야 할 때, WAS등의 서버로 전송해서 처리하려면 네트워크에 부하가 많이 걸릴 수 있다. 이때 프로시저나 함수를 사용하여 데이터를 연산 가공한 수, 최종 결과만 서버에 전송하면 부담을 덜 수 있다.
  2.  로직을 수정하기 위해 서버를 셧다운 시키지 않아도 된다. 서버에서는 단순히 DB에 프로시저를 호출하여 사용하면 된다.
  3.  쿼리문을 직접 노출하지 않는 만큼 SQL Injection의 위험성이 줄어든다.
  4.  블록 단위로 유연하게 사용할 수 있다.

 

■ 단점

  1.  유지보수가 힘들다.
  2. 대용량 처리가 많을 경우, DB에 부하를 줄 수 있다.
  3. Git과 같은 형상관리를 사용할 수 없다.

■ PL/SQL의 기본 형식

1
2
3
4
5
6
7
DECLARE 블록
    [실행에 필요한 여러 요소 선언];
BEGIN
    [작업을 위해 실제 실행하는 명령어];
EXCEPTION
    [PL/SQL수행 도중 발생하는 오류 처리];
END;
 cs

 

블록 : PL/SQL의 기본단위. 선언부 · 실행부 · 예외 처리부로 구성

 

구성 키워드 필수/선택 설명
DECLARE (선언부) 선택 실행에 사용될 변수 · 상수 · 커서 등을 선언
BEGIN (실행부) 필수 조건문 · 반복문 · SELECT · DML · 함수 등을 정의
EXCEPTION (예외 처리부) 선택 PL/SQL 실행 도중 발생하는 예외 상황을 해결하는 문장 서술

 

■ PL/SQL 작성 주의사항

  1. PL/SQL 블록을 구성하는 DECLARE, BEGIN, EXCEPTION 키워드에는 세미콜론(;)을 사용하지 않는다.
  2. PL/SQL 블록의 각 부분에서 실행해야 하는 문장 끝에는 세미콜론(;)을 사용
  3. SQL에서와 마찬가지로 PL/SQL 내부에서도 주석 사용 가능(-- 주석내용 , /* 주석내용 */)
  4. PL/SQL문 작성을 마치고 실행하기 위해 마지막에 슬래시(/)를 사용 (SQLDeveloper에서는 생략 가능)

 

■ PL/SQL에서 간단한 문장 출력

1
2
3
4
5
6
--문장 출력
SET SERVEROUTPUT ON        -- 실행결과를 화면에 출력하기 위한 것
BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO, PL/SQL!');
END;
/    -- 실행하기 위해 사용 (SQLDeveloper에서는 생략 가능)
 cs

 

SQLPlus에서 PL/SQL 실행결과를 화면에 출력하려면 SERVEROUTPUT 환경변수 값을 ON으로 해야한다.

SQLPlus의 접속 계정이 변경되거나 접속이 끊어진 후 다시 접속했다면 SERVEROUTPUT을 다시 ON해줘야 결과를 확인할 수 있다.

PUT_LINE은 화면 출력을 할 때 사용한다. DEMS_OUTPUT패키지 안에 있다.

 

■ 변수

변수는 데이터를 일시적으로 저장하는 요소이다.

선언부(DECLARE)에서 작성하며 작성한 변수는 실행부(BEGIN)에서 활용한다.

 

1
[변수 이름] [자료형] := [값 OR 값을 도출하는 표현식]
 cs

 

 
1
2
3
4
5
6
7
8
9
DECLARE     -- 선언부
    V_EMPNO NUMBER(4) := 7788;    -- 변수 V_EMPNO에 7788할당. NUMBER(4)이므로 4자리 숫자
    V_ENAME VARCHAR(10);        -- 변수 V_ENAME을 VARCHAR(10)자료형으로 선언. 값은 지정하지 X
BEGIN         -- 실행부
    V_ENAME := 'SCOTT';            -- 변수 V_ENAME에 SCOTT을 저장
    DBMS_OUTPUT.PUT_LINE('V_EMPNO : ' || V_EMPNO);        -- 변수 V_EMPNO 출력    
    DBMS_OUTPUT.PUT_LINE('V_ENAME : ' || V_ENAME);        -- 변수 V_ENAME 출력    
END;        -- 현재 블록 종료
/            -- 작성한 PL/SQL문을 실행(SQLDeveloper에서는 생략 가능)
 cs

 

간단하게변수를 선언하고 값을 대입한 뒤 출력하는 예시이다.

문자를 연결할 때는 || 연산자를 사용한다.

 

 

■ 상수

1
[변수 이름] CONSTANT [자료형] := [값 OR 값을 도출하는 표현식]
 cs

 

■ 기본값

1
[변수 이름] [자료형] DEFAULT [값 OR 값을 도출하는 표현식]
 cs

'프로그램 > DataBase' 카테고리의 다른 글

[Oracle] JOIN  (0) 2021.02.16
[Oracle] 프로시저, 함수의 이해  (0) 2021.02.16
[Oracle] 뷰(VIEW)의 이해  (0) 2021.02.16
[Oracle] 쿼리문의 이해 및 자주 쓰는 내장 함수 이해  (0) 2021.02.16
[Oracle] DDL (테이블 및 인덱스 생성)  (0) 2021.02.15

DDL (Data Definition Language, 데이터 정의어)

DDL은 스키마, 도메인, 테이블 ,뷰, 인덱스를 정의하거나 변경 또는 제거할 때 사용하는 언어이다.

 

종류 역할
CREATE 데이터베이스, 테이블 등을 생성
ALTER  테이블을 수정
DROP 데이터베이스, 테이블을 삭제
TRUNCATE  테이블을 초기화

 

 

 

자료형

자료형

설명

CHAR

고정 길이의 문자 데이터

VARCHAR2

가변 길이의 문자 데이터

NUMBER

가변 길이의 숫자 데이터

DATE

날짜 및 시간 값

LONG

가변 길이의 문자 데이터 (최대 2GB)

LOB

이미지, 실행 파일 저장

 

 

■ CREATE

 

테이블 생성하기

 

1
2
3
4
5
6
7
8
9
CREATE TABLE 테이블명
       (속성명 데이터_타입 [NOT NULL], ...
       [, PRIMARY KEY (기본키_속성명, ...)]
       [, UNIQUE (대체키_속성명, ...)]
       [, FOREIGN KEY (외래키_속성명, ...)
              REFERENCES 참조테이블(기본키_속성명, ...)]
              [ON DELETE 옵션]
              [ON UPDATE 옵션]
       [, CONSTRAINT 제약조건명] [CHECK (조건식)]);

 

 

 

- NOT NULL : 지정한 열에 NULL을 허용하지 X (NULL을 제외한 데이터의 중복은 허용)

- PRIMARY KEY : 지정한 열이 유일한 값이면서 NULL을 허용하지 X (PRIMARY KEY는 테이블에 하나만 지정 가능)

- UNIQUE : 지정한 열의 값이 중복되지 않아야 함 (단, NULL은 값의 중복에서 제외)

- FOREIGN KEY : 외래키 속성과 참조 테이블에 관한 정보를 지정 한다. 외래키가 지정되면 참조 무결성의 CASCADE 법칙이 적용된다

- DEFAULT : DEFAULT 값을 정의 시 컬럼에 데이터 값이 입력되지 않은 경우 정의된 값이 자동 입력

- CHECK : 설정한 조건식을 만족하는 데이터만 입력 가능

- CONSTRAINT : 제약 조건의 이름을 지정한다

 

테이블 복사하기
CREATE TABLE 복사할 테이블 명 AS SELECT * FROM 기존 테이블 명

 

인덱스 생성하기

인덱스는 검색을 빠르게 하기 위해 만든 보조적인 데이터 구조이다.

1
2
3
4
CREATE [UNIQUE] INDEX 인덱스명
       ON 테이블명 (속성명 [ASC | DESC], ...)
      [CLUSTER]
 cs

- CLUSTER : 지정된 키에 다라 튜플들을 그룹으로 지정하기 위해 사용한다.

■ ALTER

 

테이블 구조 변경하기

1. 컬럼 추가하기

1
ALTER TABLE 테이블명 ADD (컬럼명 데이터타입);
 cs

 

2. 컬럼 제거하기

1
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
 cs

 

3. 테이블 구조 변경하기

1
ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입;
 cs

 

 

■ DROP

 

테이블까지 완전 삭제

1
DROP TABLE 테이블명 [CASCADE CONSTRAINT];
 cs

CASCADE CONSTRAINT는 해당 테이블과 관계가 있던 참조 제약조건도 삭제한다는 것을 의미

 

■ RENAME

 

이름 변경하기

1. 테이블 명 변경하기

1
RENAME OLD테이블명 TO NEW테이블명;
 cs

 

2. 컬럼 명 변경하기

1
ALTER TABLE 테이블명 RENAME COLUMN OLD컬럼명 TO NEW컬럼명;
 cs

 

■ TRUNCATE

테이블의 데이터만 삭제

1
TRUNCATE TABLE 테이블명;
 cs

 

 

제약조건

1. 제약조건 추가

1
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);
 cs

 

2. 제약조건 삭제

1
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
 cs

 

 

'프로그램 > DataBase' 카테고리의 다른 글

[Oracle] JOIN  (0) 2021.02.16
[Oracle] 프로시저, 함수의 이해  (0) 2021.02.16
[Oracle] 뷰(VIEW)의 이해  (0) 2021.02.16
[Oracle] 쿼리문의 이해 및 자주 쓰는 내장 함수 이해  (0) 2021.02.16
[Oracle] 오라클 접속 및 PLSQL 사용법  (0) 2021.02.16

+ Recent posts

티스토리툴바