03.

숫자와 관련된 함수들

 

1) 절대값 : abs(정수)

select abs(-30) from dual;

 

2) 부호(양수(1), 음수(-1), 0)를 반환해 주는 함수 : sign(정수)

select sign(10), sign(-15), sign(0) from dual;

3) 반올림 : round(숫자, 반올림 위치(선택))

반올림 위치를 음수로 지정할 경우 자연수 쪽으로 한자리 위로 반올림.

select round(1234.5678) from dual; --> 1235

select round(0.12345, 4) from dual; --> 0.1235

select round(2.3423557, 5) from dual; --> 2.34236

select round(1234.5678, -1) from dual; --> 1230 

 

4) 소수점 이하 자리수를 잘라내는 함수 : trunc(숫자, 버릴 위치)

select trunc(1234.1234567, 0) from dual; --> 1234

select trunc(1234.1234567, 4) from dual; --> 1234.1234

select trunc(1234.1234567, -3) from dual; --> 1000

 

5) 무조건 올림: ceil()

select ceil(22.8594) from dual; --> 23

 

6) 지정된 숫자보다 작은 정수 중에서 가장 큰 수를 선택: floor()

select floor(-3.14) from dual; --> -4

 

7) 제곱: power()

select power(4, 3) from dual; --> 4^3

 

8) 나머지를 구해주는 함수: mod(나눗셈 될 숫자, 나눌 숫자)

select mod(7,4) from dual; --> 3

 

 

**VIEW**

물리적인 테이블에 근거한 논리적인 가상 테이블.

실질적으로 데이터를 저장하고 있지 않음.

View를 만들면 DB에 질의 시 실제 테이블에 접근하여 데이터를 불러오게 됨.

주로 데이터를 조회할 때 가장 많이 사용됨.

테이블과 유사하며, 테이블처럼 사용이 가능함.

테이블에 저장하기 위한 물리적 공간이 필요 없음.

테이블과 마찬가지로 insert, update, delete, select 명령 가능.

 

View를 사용하는 이유?

1) 보안 관리를 위해 사용 (중요)

==> 보안 등급에 맞추어 조회가능한 컬럼의 범위 설정 가능.

2) 사용자의 편의성 제공

 

View 생성 방식

create view 뷰이름 

as

쿼리문;

 

예)

create view emp_HR

as

select empno, ename, job, mgr, hiredate, deptno 

from emp;

 

create view emp_View

as 

select *

from emp

with read only; ==> 읽기 전용으로 view 생성 (insert, update, delete 쿼리 불가능)

 

create or replace view emp_annualSal(empno, ename, annual_salary) --이런식으로 view 컬럼의 이름 설정 가능

--같은 이름의 view가 있는 경우 삭제하고 새 view 생성

as

select empno, ename, (sal+nvl(comm, 0))*12

from emp;

 

create or replace veiw emp_view2

as

select * from emp

where deptno = 1;

==> 이런식으로 그 컬럼에 존재하지 않는 값을 조건으로 주면 데이터 없이 컬럼이름만 있는 view 생성가능.

 

*담당업무가 salesman인 사원의 사번, 이름, 담당업무, 입사일, 부서번호를 컬럼으로 하는 emp_sale이라는 View를 생성

create or replace view emp_sale

as

select empno, ename, job, hiredate, deptno

where job = 'SALESMAN'

with read only;

 

 

**********

컬럼 속성 (제약 조건)

-테이블에 부적합한 자료가 입력되는 것을 방지하기 위해서 테이블 생성시 각 컬럼에 부여하는 여러 규칙.

1) not null

2) unique

3) primary key

4) foreign key

5) check

 

1) not null

- null 값이 입력되지 못하게 하는 제약 조건.

- 특정 열에 데이터 중복 여부와 관계 없이 null 값을 허용하지 않는 제약 조건.

create table null_test(

col1 varcher2(10) not null,

col2 varchar2(10)

);

 

insert into null_test(col1) values ('') --> 에러

 

2) unique 제약 조건

-열에 저장할 데이터의 중복을 허용하지 않고자 할 때 사용하는 조건.

-null 은 허용됨.

create table unique_test (

col1 varchar2(10) unique,

col2 varchar2(10) not null

);

insert into unique_test values('aa', 'bb');

insert into unique_test values('aa1', 'bb1');

update unique_test set col2 = 'bb' where col1 = 'aa1'; --> unique 위배로 인한 에러

 

3) primary key

- 테이블에 하나만 존재해야 하는 제약 조건.

- 보통 주민번호나 사원번호 등이 pk의 조건이 됨.

 

4) foreign key 제약 조건

- 다른 테이블의 필드(컬럼)을 참조해서 무결성을 검토하는 조건

- PK가 있는 테이블을 부모 테이블이라 칭함.

- FK가 있는 테이블을 자식 테이블이라 칭함.

- 부모 테이블의 PK 컬럼에 존재하는 데이터만 자식테이블에 입력가능.

- 참조하는 데이터 컬럼과 데이터 타입이 반드시 일치해야 함.

- references : 참조할 부모 테이블과 부모 테이블에 있는 컬럼을 정의한다.

- on delete cascade : 부모 테이블의 행이 지워지면 자식 테이블의 행도 같이 지워진다.

- on delete set null : 부모 테이블의 행이 지워지면 자식 테이블의 행은 null 값으로 설정된다.

- 참조되는 컬럼은 PK이거나 Unique만 가능하다.

 

예시)

--부모 테이블 생성

create table dept(

dno vatchar2(2) primary key,

dname varchar2(10),

loc varchar2(8),

 

--자식 테이블 생성

create table emp(

eno varchar2(5) primary key,

ename varchar2(10),

job varchar2(10),

sal number,

dno varchar2(2) references dept (dno)

);

 

5) check 제약 조건

- 열에 저장할 수 있는 값의 범위 또는 패턴을 정의할 때 사용.

- 주여진 값만 허용.

create table check_test(

gender varchar2(10),

constraint gender_ch check(gender in('남자', '여자'))

);

inser into check_test values('여성'); --> 에러

 

 

*** 시퀀스 ***

연속적인 번호를 만들어 주는 기능

형식)

create sequence 시퀀스이름

start with n (시작 번호 설정 - 기본값 1)

increment by n (증가폭 설정 - 기본값 1)

maxvalue n (시퀀스 최대 번호 설정)

minvalue n (시퀀스 최소 번호 설정)

cache / nocache (시퀀스의 값을 빠르게 설정하기 위해 캐시 메모리 사용 여부 체크)

 

1) cache?

- 시퀀스를 빨리 제공하기 위해서 미리 캐시 메몰에 시퀀스를 넣어 두고 준비.

- 시퀀스 작업이 필요할 때 사용.

- 디폴트로 20개의 시퀀스를 캐시 메모리에 보관.

- 단, 시스템이 비정상적으로 종료 되거나 전원이 차단되어 시스템 종료가 되면 캐시 메모리에 남아 있던 시퀀스 번호는 증발.

 

*******서브 쿼리*******

하나의 쿼리문 안에 포함되어 있는 또 하나의 쿼리문을 말함.

쿼리문 안에 또 다른 쿼리문이 존재하는 것.

메인쿼리가 서브쿼리를 포함하는 종속적인 관계.

여러 번 쿼리를 실행해서 얻을 수 있는 결과를 하나의 중첩된 쿼리 문장으로 결과를 알 수 있음.

 

주의 사항

1) 서브쿼리는 괄호로 묶어서 사용해야 함.

2) 서브쿼리 안에서는 order by 절은 사용할 수 없음.

3) 연산자 오른쪽에 있어야 함.

 

사용 방법

우선 안쪽 쿼리문을 실행한 후 , 그 결과값을 가지고 바깥쪽 쿼리문을 실행함.

 

예시)

emp 테이블에서 이름이 'SCOTT'인 사원의 급여보다 더 많은 급여를 받는 사원의 사번, 이름, 담당업무, 급여를 화면에 출력.

 

select empno, ename, job, sal

from emp;

where sal > (select sal from emp where ename = 'SCOTT';)

 

emp 테이블에서 이름에 'T'를 포함하고 있는 사원들과 같은 부서에 근무하고 있는 사원의 사번과 이름, 부서번호를 출력

 

select empno, ename, deptno

from emp

where deptno in(select deptno from emp where ename like '%T%;);

==> 서브쿼리 안의 결과값이 2개 이상이므로 in()으로 처리해야 됨.

 

 

*** group by ***

특정 컬럼이나 값을 기준으로 해당 레코드를 묶어서 자료를 관리할 때 사용.

보통은 특정 컬럼을 기준으로 집계를 구하는데 많이 사용.

보통은 그룹함수와 함께 사용하면 효과적으로 활용 가능.

 

예)

emp 테이블에서 부서별로 그룹을 지어서 부서의 급여 합계와 부서별 인원수, 부서별 평균 급여, 부서별 최대 급여, 부서별 최소 급여를 구하여 출력. 단 급여 합계를 기준으로 내림차순 정렬.

select deptno, sum(sal), count(*), round(avg(sal)), max(sal), min(sal)

from emp

group by deptno

order by sum(sal) desc;

 

*** having ***

group by 절 다음에 나오는 조건절로 group by 절의 결과에 조건을 주어서 제한할 때 사용.

group by 절에는 where가 올 수 없음.

 

예)

select category_fk, count(*)

from products 

group by category_fk

having count(*) = 2

order by category_fk;

having 없이 출력

having으로 제한

 

***트랜잭션(transaction)***

데이터 처리의 한 단위.

오라클에서 발생하는 여러 개의 SQL 명령문들을 하나의 논리적인 작업 단위로 처리하는 것.

ALL or Nothing 방식으로 처리.

명령어 여러 개의 집합이 정상적으로 처리가 되면 종료.

여러 개의 명령어 중에서 하나의 명령어라도 잘못 처리되면 전체를 취소.

 

사용이유?

데이터의 일관성 유지 + 데이터 안정성 보장.

 

트랜잭션 제어 명령어

1) commit

모든 작업을 정상적으로 처리하겠다고 확정하는 명령어.

트랜잭션(insert, update, delte) 작업의 내용을 실제 DB에 반영.

이전에 있던 데이터에 update 현상이 발생함.

모든 사용자가 변경된 데이터의 결과를 확인할 수 있음.

 

2) rollback

작업 중에 문제가 발생했을 때 트랜잭션 처리 과정에서 발생한 변경 사항을 취소하여 이전 상태로 되돌리는 명령어.

트랜잭션(insert, update, delete) 작업 내용을 취소.

이전에 commit 한 곳까지만 복구됨.

 

3) savepoint

트랜잭션을 작게 분할하는 것.

사용자가 트랜잭션 중간 단계에서 포인트를 지정하여 트랜잭션 내의 특정 savepoint까지 rollback할 수 있게 함.