Oracle - SQL

Oracle (feat. docker)

※ Oracle Database 11g Express Edition 중심으로

자바에서 Oracle DB를 사용할 때 JDBC 라이브러리를 사용하게 되는데 이때 JDBC를 사용하기 위해 몇가지 필요한 정보가 있다. 즉 JDBC를 통해 Oracle DB에 connection하기 위해 몇 가지 필요한 정보가 있다. 대표적으로 Oracle 사용자 계정에 대한 id, password... 그 중에서 Oracle database에 대한 url 정보가 있다. 대체로 다음과 같은 형태이다.

jdbc:oracle:thin:@localhost:1521:xet

이것이 무엇을 의미하는지에 대해서 살펴본다. 이에 대한 프로토타입은 다음과 같다.

jdbc:oracle:driver_type:[username/password]@[//]host_name[:port][/XE]

위의 프로토타입으로부터 아래 정보를 해석해 보면 다음과 같다.

jdbc:oracle:thin:@localhost:1521:xe

-. jdbc:oracle:thin은 사용하는 JDBC드라이버가 thin 타입을 의미한다. 자바용 오라클 JDBC드라이버는 크게 두가지가 있는데 하나는 Java JDBC THIN 드라이버고, 다른 하나는 OCI기반의 드라이버라고 한다.

-. username/password은 option이다. [ ]안에 있는 정보는 반드시 명기할 필요는 없다는 뜻이다.

-. :port 번호도 option이다. 다만 Oracle의 listener port인 1521을 사용하지 않을 경우는 이 값을 명기해 줘야 된다. 예를 들어서 jdbc:oracle:thin:hr/hr@//localhost:1522

-. localhost는 Oracle DB가 설치되어 있는 서버의 IP인데 위 경우는 로컬에 설치되어 있다는 뜻이다.

-. 1521 은 오라클 listener의 포트번호이다.

-. /XE는 Oracle database client의 고유한 service name이다. 디폴트로 XE를 사용하므로 이 정보도 option이다. 이에 대한 설정 정보는 Oracle이 설치된 폴더 아래의 app\oracle\product\11.2.0\server\network\ADMIN\listener.ora 파일에 다음과 같이 표시되어 있다

 

SQLPLUS 를 docker 에서 접속

docker exec -it oracle12c sqlplus

 

계정 변경

conn kh/1234

 

SQLPLUS 계정 수정하기

alter user hr account unlock; ==> hr유저의 상태를 unlock

 

USER hr의 비밀번호를 1234로 지정

alter user hr identified by 1234;

 

화면 clear

cl scr;

 

계정 만들기 (id = kh , pwd = 1234 )

create user kh identified by 1234 default table space users;

 

권한 주기

grant connect to kh;

 

로그아웃

disconnect

 

현 사용자 확인

show user

 

username , account_status 확인

select username , account_status from dba_users

 

sql 파일 실행하기

start 파일경로

 

접속시 오류 및 해결 방법

SQL> connect username/password

Error accessing PRODUCT_USER_PROFILE

Warning: Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.

SQL>

 

Oracle User 접속시 Profile 을 액세스 하는 오류입니다.

SYSTEM User 계정으로 PUPBLD.SQL 실행이 필요합니다.

 

SQL> connect system/password Connected. SQL> @?/sqlplus/admin/pupbld.sql

 

일반관리자 , 슈퍼 관리자

  1. sys (슈퍼 관리자) - 데이터베이스 생성/삭제하는 권한 있음

    데이터베이스 관련 모든 문제를 처리 가능

    로그인시 SYSDBA룰로 접속해야함

  2. system (일반 관리자) - 데이터베이스 생성/삭제 권한 X

 

no privileges on tablespace 'USERS' , 테이블 스페이스 권한 주기

grant create session, create table, create view,resource to (User_Name);

 

테이블 스페이스 권한 주기

예제

show user;

--kh가 가진 테이블 조회 select * from tab;

--하나씩 조회 select * from department; select * from employee; select * from job; select * from location; select * from nation; select * from sal_grade;

--일반관리자, 슈퍼관리자 -- 1. sys(슈퍼관리자) : -- 데이터베이스를 생성/삭제하는 권한 있음 -- 데이터베이스 관련 모든 문제를 처리 가능 -- 로그인 시 SYSDBA로 접속해야함

-- 2. system(일반관리자) : -- 데이터베이스 생성/삭제 권한이 없음 -- 데이터베이스를 유지보수하는 관리자

/*

Table

  • 데이터를 담고 있는 객체
  • 데이터에 대해서 생성, 조회, 수정, 삭제(CRUD)작업을 하는 대상
  • 행과 열로 이루어져 있다.

Table(Eneity, Relation) : Employee 테이블과 테이블을 붙여서(조인) = Relation Column(Filed, Attribute : Emp_Id 200 201 202... Row(Record, Tuple) : 행, 200 선동일 621225-1985634 sun_di@kh.or.kr 01099546325 D9 J1 S1 8000000 0.3 90/02/06 N Domain : 하나의 컬럼(속성_이 취할 수 있는 같은 타입으로 이루어진 값의 집합 ​ gender : 남, 여 (여기서 남, 여를 도메인이라고 함) */

--하나의 테이블 정보조회 desc employee; --desc : 내림차순 --null여부 (nullable) --1. not null : 필수요소 --2. null : 선택요소

/* Data type

  1. 문자형 CHAR : 고정길이 문자형(최대 2000byte) VARCHAR2 : 가변길이의 문자형(최대 4000byte)

NCHAR : char와 동일하나 유니코드 문자개수로 처리 (고정형), 1000개까지 됨. 유니코드(2byte라서) NVARCHAR2 : varchar2와 동일하나 유니코드 문자개수로 처리 (가변형), 2000개까지 됨.

char(10) 고정형으로 문자열 10byte 'korea' => 실제로는 5byte지만, 저장시에는 10byte를 모두 사용한다. 빈공백으로 잔여크기 채움 >>> 고정형 '대한민국' => 글자당 2byte지만, 저장시에는 8byte가 아닌 10byte를 모두 사용 '대한민국 서울시' => 글자2, 공백1 : 15byte지만 최대크기가 10byte로 지정되어있어 에러가 발생된다.

varchar(20) 가변형으로 문자열 20byte까지 저장가능 'korea' : 실제 5byte, 저장도 5byte '대한민국' : 실제 8byte, 저장도 8byte '대한민국 서울시' : 실제 15byte, 저장도 15byte 물론 20byte보다 크면 오류

nchar(10) 고정형, 문자열개수가 10개까지 허용 nvarchar2(10) 가변형, 문자열개수가 10개까지 허용

long : 최대 2GB까지 작성 가능한 가변형 문자타입

*/

create table tb_chartype( ​ col1 char(8), ​ col2 varchar2(8), ​ col3 nchar(6), ​ col4 nvarchar2(6) );

--데이터입력(create) Insert into tb_chartype values('abc', 'abc', 'abc', 'abc');

Insert into tb_chartype values('가다', '하다', '호호나', '푸호나'); --오라클에서 한글처리는 2byte지만, 11g xe버전은 3byte로 처리된다.

select * from tb_chartype;

--메모리에서 작업인것을 취소 rollback;

--메모리에서 작업한 내용을 실제 파일에 적용 commit;

/*

  1. 숫자형 number([p,s]) -- precision : 표현할 수 있는 전체 숫자 자리수(1~38) -- scale : 소수점이하 자리수(-84 ~ 127)

데이터입력값이 1234 678 일때,

데이터 타입 저장되는 값

number(7,3) 1234.678 number(7) 1235 number 1234.678 number(7,1) 1234.7 (반올림) number(5, -2) 1200 (마이너스는 소수점을 기준으로 왼쪽, 0으로 처리)

  1. 날짜형 : date 일자(년/월일) 및 시간(시/분/초) 정보를 관리 기본적으로는 화면에 년/월/일만 표시됨 (물론 시분초도 저장되어있음) 날짜의 연산 또는 비교가 가능함

연산 결과타입 설명

날짜+-숫자 date 하루를 1로 계산해서 처리함 날짜+-날짜 number 두 날짜의 차이를 리턴(하루가 1기준) */

select sysdate from dual; select sysdate-1 from dual; select sysdate+1 from dual;

select to_char(sysdate, 'yyyy/mm/dd hh:Mi:ss')from dual;

-- 정확한 시각정보(밀리초 이하)를 표현하기 위한 timestamp타입도 있다 select systimestamp from dual; /*

  1. LOB형 clob : 최대 4GB 허용가능한 문자타입 */

--실습문제 : today라는 회사의 회원테이블을 작성할 때, -- 다음 필드에 적절한 db데이터 타입을 설정해서 tb_today_member 라는 테이블을 생성하세요. /*

  1. id : 8자리에서 15자리
  2. pw : 8자리 이상 15자리
  3. name
  4. phone : -없이 11자리
  5. ssn : 주민번호 -없이 13자리
  6. mileage : 회원 마일리지
  7. reg_date : 가입일 */

create table tb_today_member( ​ id varchar2(15) NOT NULL,

pw char(15) NOT NULL,

name varchar2(100) NOT NULL,

phone char(11) NOT NULL,

ssn char(13) NOT NULL,

mileage number,

reg_date date

);

 


--## SQL

/*

Structed Query Language : 구조화된 질의 언어

dbms사용되는 SQL문법이 조금씩 다름

DDL (Data Definition Language) 데이터베이스의 구조, 객체에 대한 언어 ex) create , drop , alter

DML (Data Manipulation Language) 저장된 데이터의 조작 ex) insert, update , delete, select ==> CRUD

DCL (Data Control Language) 사용자권한, 설정 ex) grant , revoke

TCL (Transaction Control Language) ex) commit , rollback, savepoint

*/

 

--##DQL

/*

데이터를 검색(추출)하기 위한 언어

DML로 분류

데이터를 조회한 결과를 ResultSet(결과집합)

-> 0개 이상의 행이 포함

-> 특정 기준에 의해서 정렬될 수 있음

 

  1. select : 조회하고자 하는 컬럼명을 기술

  2. from : 조회하고자 하는 테이블명 기술

  3. where : 특정 행을 선출하는 조건을 기술

  4. group by :

  5. having :

  6. order by : 특정 컬럼을 대상으로 오름, 내림 차순 지정 asc , desc

    처리 되는 순서 From -> where -> group by -> having -> select -> order by (중요 앙앙)

*/

select * from employee;

select emp_id, emp_name,salary from employee;

select emp_id, emp_name,salary,job_code from employee where job_code='J5' order by emp_name asc;

select emp_id,emp_name,salary,job_code -- 3

from employee --1

where job_code='J5' -- 2

order by emp_name; -- 4

 

—1. job 테이블에서 job_name 컬럼만 출력

select job_name

from job;

--2. department테이블의 전체 컬럼을 출력

select *

from department;

--3 employee테이블에서 이름,이메일, 전화번호, 입사일을 출력

select emp_name,email,phone,hire_date

from employee;

—4. employee테이블에서 월급이 2,500,000원 이상인 사람만 출력

select *

from employee

where salary >= 2500000;

—5. employee테이블에서 월급이 350만원 이상이면서 job_code가 'J3 '인 사람의 이름

select emp_name

from employee

where salary >= 3500000 and job_code = 'J3';

--6 산술연산

— 연봉 : 월급 * 12 + (월급보너스) * 12

select emp_name,

salary,

bonus,

salary * 12,

(salary +(salary * nvl(bonus,0))) *12

from employee;

 

— null처리 함수 : nvl(col, val) ==> 값을 리턴

— col의 값이 null인 경우 val을 리턴

— col의 값이 null이 아닌 경우 col의 값을 리턴

select nvl(7,3), nvl(null,3), nvl('이순신','거북선'), nvl(null,'거북선')

from dual;

select nvl(bonus, 0), bonus

from employee;

— 컬럼명 별칭(alias)지정

— as "별칭" ===> as 와 "" 생략 가능

-- as는 항상 생략이 가능하지만 ""을 반드시 써야하는 경우가 있음 : 공백이 포함된 경우, 숫자로 시작되는 경우

 

— 컬럼에 리터럴(literal)지정

— 리터럴은 resultset의 모든 행에 반복 표시

select emp_name, salary, '원' as 단위 , 12345

from employee;

 

— 중복값 제거 distinct

— select절에 한번만 사용 가능, 여러 컬럼을 사용하면, 여러 컬럼을 합친 것을 고유값으로 간주

select distinct job_code

from employee;

 

— 연결 연산자 ||

— 문자열연결, 기타값과 문자열연결에 +연산자가 아닌 || 연산자 사용

— 문자는 왼쪽 정렬, 정수형은 오른쪽 정렬

select emp_name, salary || '원' as "연봉"

from employee;

 

— where절에 사용하는 연산자

/*

-1. 논리연산자

and

or

not : 해당 결과를 반전 (null 제외)

-2. 비교 연산자

=

< , >

<= , >=

<> , ^= , != : 같지 않다

between 값1 and 값2 : 값1 이상 값2 이하

like , not like : 문자 패턴비교

is null , is not null : null여부 비교 -> where bonus is not null

in , not in : 비교값 목록에 포함되었는지 여부

*/

 

— 부서코드 D6이고, 급여를 2000000원보다 많이 받는 사람의 이름, 부서, 월급을 출력

select emp_name,dept_code,salary

from employee

where dept_code = 'D6' and salary > 2000000;

 

— 직급코드가 J1이 아닌 사원들의 월급등급(sal_level)을 중복없이 출력

select distinct sal_level

from employee

where job_code != 'J1';

 

— 급여를 3500000원 이상, 6000000 이하 의 이름과 연봉 출력

select emp_name,salary

from employee

where salary >= 3500000 and salary <= 6000000;

 

— 숫자 , 날짜에 대해서 사용가능

— 입사일이 90/01/01 ~ 01/01/01인 사원을 조회

— 기본 date 형식을 사용하면 됨

select *

from employee

where hire_date between '90/01/01' and '01/01/01';

 

— 문자열 패턴비교 like

— 비교하려는 값이 지정한 문자패턴을 만족시키면 true를 리턴함

— 와일드카드 % _를 사용함

— % : 글자가 없든지, 있으면 true

-- _ : 임의의 문자 1개를 의미

 

select emp_name,salary

from employee

where emp_name like '';

 

--email에서 _의 앞자리가 3자리인 직원을 조회 --이스케이핑처리 : 임의의 문자를 이스케이프 문자로 사용하고, 이를 명시 image

 

create table tb_watch(

watchname varchar2(40),

description varchar2(200)

);

insert into tb_watch values ('금시계','순금 99.99% 함유 고급시계');

insert into tb_watch valuse('은시계','고객만족도 99.99점을 획득한 고급시계');

commit;

 

실습문제

1

select emp_name as "이름" , (salary * 12) || '원' as "연봉" , ​ (salary + (salary * nvl(bonus,0))) * 12 as "총수령액", ​ ((salary + (salary * nvl(bonus,0))) * 12) - (salary * 0.03) as "실수령액" ​ from employee;

 

2

select emp_name as "이름",

trunc(sysdate - hire_date) as "근무 일수"

from employee;

 

3

select emp_name as "이름",

salary as "월급",

nvl(bonus,0) as "보너스율"

from employee

where trunc(sysdate - hire_date) >= (365 * 20);

 

4

select emp_name as "이름"

from employee

where emp_name like '%연';

 

5

6

 

비교연산자 in

비교 하려는 값 목록에 일치하는 것이 있는가? 있으면 true, 없으면 false

D6 , D8 부서인 사원들을 조회하라

 

D6, D8 부서가 아닌 사원들을 조회하라

 

연산자 우선순위

  1. 산술 연산자
  2. 연결연산자 ||
  3. 비교연산자
  4. IS NULL / IS NOT NULL, LIKE , IN / NOT IN
  5. BETWEEN AND / NOT BETWEEN AND
  6. NOT
  7. AND
  8. OR

 

부서코드가 J7 또는 J2이고, 급여가 2000000원 이상인 사원 조회

select *

from employee

where job_code in ('J7','J2')

and salary >= 2000000;

 

order by

select한 컬럼에 대해서 정렬기준을 제시

order by 컬럼명 | 별칭 | 컬럼순서 {정렬방식 (asc , desc)][NULLS FIRST | LAST]

 

 

FUNCTION

일련의 처리과정을 반복적으로 사용하기 위해서 작성해둔 서브프로그램

호출시 값을 전달하면, 수행결과를 리턴하는 방식 (반드시 값을 리턴 , void X)

 

함수의 유형

  1. 단일행 처리 함수

    • 문자처리함수
    • 숫자처리함수
    • 날짜처리함수
    • 형변환함수
    • 기타함수
  2. 그룹 함수

    • group by
    • having

 

단일행 처리 함수

  1. 문자 처리 함수

    length : 문자 개수를 리턴

lengthb : 문자열의 byte를 리턴

instr : 찾는 문자열이 대상문자열에서 지정한 위치부터 지정한 회수번째에 나타난 위치를 반환

instr(대상문자열, 찾고자하는 문자열[,시작 인덱스 [, 몇번째]])

오라클에서 인덱스는 0기반이 아닌 1기반이다.

 

연습문제

email컬럼에서 @의 위치인덱스값을 출력

select email,

instr(email,'@')

from employee;

lpad / rpad : lpad(string,byte[,paddingString])

byte공간에 string문자열을 쓰고, 남은 공간 왼쪽/오른쪽에 paddingString으로 채워라

ltrim / rtrim

ltrim / rtrim(string, str)

trim : 양쪽에서 지정한 문자를 제거

 

연습문제

1231233546356542hello48763987649387461

substr

substr(string, position ,[, length])

lower / upper / initcap

concat : 문자 연결함수이지만 , 2개의 문자만 처리가능 , 3개 이상연결에는 ||사용할 것

replace : replace(대상문자열,fromString, toString)

 

연습문제

  1. 사원명에서 성만 중복없이 출력(성은 한글자라고 가정)

  2. employee테이블에서 남자만 사원번호,사원명,주민번호 출력(주민 뒷자리 *출력)

  1. 다음 tb_files테이블에서 파일명만 출력

 

숫자 처리 함수

  1. abs : 절대값

  2. mod : 나머지 구하는 함수

    %가 아닌 mod함수를 사용

  3. round : 반올림

    round(nunber, [, position])

  4. ceil

    12.3456을 소수점 둘째자리까지 올림처리해서 표현하기

  5. floor

  6. trunk : trunc(number , position)

날짜처리함수

  1. sysdate

 

  1. add_months : add_months(date, number);

  2. months_between : months_between(date1, date2)

    date1 과 date2의 날짜 차이를 month단위로 리턴

    1이 리턴

연습문제

근무개월수 구하기

군복무 1년 6개월, 전역일, 전역일까지 먹어야 할 짬밥의 수

  1. next_day : 인자로 전달받은 요일데이터를 토대로 가장 빠른 해당요일을 리턴

    next_day(date, string | number)

  1. last_day : 해당일자를 기준으로 해당 월의 말일을 리턴

  2. extract : 특정일에서 년월일 정보를 추출

    extract(year|month|day from date)

  3. trunc : trunc(date) : 시분초 정보를 00:00:00으로 처리

// docker 의 컨테이너로 진입

// /etc/localtime 링크

 

 

형변환 함수

 

 

 

 

날짜에서 앞에 0 지우기

 

 

sysdate 연산

1이 1일이다

 

 

to_char 포맷

 

 

to_number

to_number(character[, format])

1,000,000,000 - 50,000,000

 

자동 형변환

 

nvl

null처리 함수 nvl(컬럼, null일 경우 값)

nvl2(col,val1,val2)

col의 값이 null이 아니면 val1, null이면 val2

보너스가 있는 사원은 보너스있음, 없으면 보너스없음으로 표시하세요,

 

기타함수

  1. greatest , least

    숫자,문자,날짜 비교

  2. decode

    선택함수

    switch문과 흡사함

    decode(표현식,조건1,결과1,조건2,결과2) - 표현식은 반드시 값이여야 함

    사원명과 직급코드에 따른 직급명으로 출력

    J1 대표 , J2 부사장 , J3 부장 , J4 차장, J5 과장, J6 대리 , J7 사원

  3. case

    case when 조건1 then 결과1

    when 조건2 then 결과2

    ...

    else 결과

    end

     

    case 조건

    when 조건1 then 결과1

    when 조건2 then 결과2

    ...

    else 결과

    end

    사원 나이를 조회 , 사원명, 주민번호 , 성별 , 나이 (현재년도 - 출생년도 + 1)

날짜 포맷 (rr을 사용할 시)

현재년도 입력년도 처리년도

00~49 00~49 현재세기(2000년도)

50~99 전세기(1900년도)

50~99 00~49 다음세기(2100년도)

50~99 현재세기(2000년도)

ex)

현재년도 입력년도 rr yy

1994 95 1995 1995

04 2004 1994

4자리인 경우엔 차이가 없구요. 2자리인경우엔 차이가 있습니다. yy 는 무조건 '20'이 앞에 붙습니다. rr 은 50년을 기준으로 작으면 '20' 크거나 같으면 '19'가 붙습니다. TO_DATE('00','yy') ==> 2000 TO_DATE('50','yy') ==> 2050 TO_DATE('00','rr') ==> 2000 TO_DATE('50','rr') ==> 1950

그룹함수

하나의 행이 아니라, 여러행을 그룹으로 묶어서 종합, 평균 등의 값을 리턴하는 함수

  1. sum

  2. avg

  3. count

  4. min , max

2번 pdf

 

group by

세부적인 그룹을 지정해서 그룹함수를 사용할 수 있다.

부서별 salary의 합계

 

Having

그룹함수 값을 구해야하는 그룹에 조건은 Having절에 기술함

그룹함수를 사용한 where은 쓸수 없다

 

소계, 누계를 처리하기 : rollup, cube

rollup : 가장 먼저 지정한 그룹별 합계와 총 합계를 출력

cube : 그룹으로 지정된 모든 그룹에 대한 합계와 총 합계를 출력

그룹지정한 컬럼이 하나인 경우 , rollup 과 cube의 결과는 같다

 

grouping함수

group by절에 의해서 산출된 컬럼은 0을 리턴

rollup, cube절에 의해서 산출된 컬럼은 1을 리턴

 

Join

Join : 가로합침, 컬럼과 컬럼을 합치는 것

union : 세로합침, 행과 행을 합치는 것

여러테이블의 레코드를 조합하여 하나의 행으로 표현한 가상 테이블을 생성

연관성이 있는 컬럼을 기준으로 조합

 

join 두가지 종류

  1. equal-join : 동등조건으로 조인하는 경우 , = 을 이용
  2. non equal-join : 동등조건이 아닌 between and , is null , is not nul , in등을 이용한 조인

 

join 두가지 문법

  1. ANSI 표준문법 : DBMS와 상관없이 공통적으로 사용하는 표준 SQL
  2. Oracle 표준문법 (벤더사 제공하는 문법) : Oracle에서 사용하는 구문 , 콤마(오라콤마)

 

equl - Join

  1. 조인하는 컬럼명이 다른 경우

 

  1. 조인하는 컬럼명이 같을 경우

 

eqal-join 종류

  1. inner join : 보통 교집합 생각하면됨

    기본 조인 : (inner) join

  2. outer join : 합집합

    left (outer) join : 왼쪽 외부 조인

    right (outer) join : 오른쪽 외부 조인

    full (outer) join : 완전 외부 조인

  1. cross join
  2. self join
  3. multiple join

image

 

inner join

교집합, 기존되는 컬럼에서 null이나 해당되는 값이 없다면 제외

 

outer join

left outer join : 좌측 table 모두 반환, 우측 테이블 조건 컬럼에 일치하는 row만 반환

right outer join : 우측 table 모두 반환, 좌측 테이블 조건 컬럼에 일치하는 row만 반환

full outer join : 조건 컬럼에 일치하지않아도 , 우측 table , 좌측 table 모두 row 반환

 

조인 결과 : employee(23row) , department(9row)

  • inner join : 21
  • left outer join : 23 = 21 + 2
  • right outer join : 24 = 21 + 3
  • full outer join : 26 = 21 + 2 + 3

3번 pdf

 

cross join

좌측 테이블 * 오른쪽 테이블

모든 경우의 수를 구하고, 행의 수는 (좌측테이블 행 개수 * 우측테이블 행 개수)

cartesian product : 카테시안의 곱

 

self join

같은 테이블을 조인해서 결과셋을 얻고자 할 때 사용

 

multiple join(다중 조인)

 

Oracle 전용 문법

join키워드 대신 ,(콤마)를 사용

on절 대신 where절을 사용

outer join

where절에 (+)이 없는 쪽을 우선 다출력

left : (+) 우항에 작성

right : (+) 좌항에 작성

 

self join

multiple join (oracle전용구문의 다중조인에서는 테이블 순서가 중요하지 않다.)

 

 

non - equal join

동등조건이 아닌 기준으로 조인

 

집합연산자(Set Operator)

여러개의 질의결과를 가로로 연결하여 합치거나 뺀 후, 하나로 결합하는 방식

조건1. slect절의 컬럼수가 동일해야됨

조건2. select절의 동일위치계 존재하는 컬럼의 데이터타입이 상호 호환 가능해야함

 

  1. union 합집합
  2. union all 합집합
  3. intersect 교집합
  4. minus 차집합

A = {1,2,3,4,5};

B = {3,5,7,9};

union --- A U B = {1,2,3,4,5,7,9}; ==> 중복 제거 O , 첫번째 컬럼기준으로 오름차순

union all --- A U B = {1,2,3,4,5,3,5,7,9}; == > 중복 제거 X , 그냥 연결

intersect --- A n B = {3,5};

minus --- A - B = {1,2,4};

 

grouping sets

group by + union all 을 한번에 처리

 

SubQuery

하나의 SQL문안에 포함된 또 다른 SQL문

메인쿼리에 종속된 서브쿼리가 존재함

존재하지 않는 조건에 근거한 값들을 검색 할 때

 

서브쿼리는 반드시 소괄호로 묶을 것

서브쿼리는 연산자의 오른쪽 위치 할 것.

 

서브쿼리의 유형

  1. 단일행 서브쿼리(단일컬럼)
  2. 다중행 서브쿼리(단일컬럼)
  3. 다중열 서브쿼리(단일행)
  4. 다중행 다중열 서브쿼리
  5. 상관 서브쿼리
  6. 스칼라 서브쿼리
  7. 인라인 뷰

 

1.단일행 서브쿼리

 

2. 다중행 서브쿼리

IN, NOT IN, ANY , ALL , EXISTS

실습문제

 

3. 다중열 서브쿼리

하나의 컬럼이 아닌 여러 컬럼

 

상관서브쿼리

상호연관 서브쿼리

메인쿼리의 값을 서브쿼리에 전달하고, 서브쿼리를 수행한 다음, 그 결과를 다시 메인쿼리로 리턴

 

스칼라 서브쿼리

결과값이 1개(단일행, 단일컬럼)인 상관서브쿼리

select : 주로 select절 스칼라서브쿼리를 일컫음

where

order by

 

인라인 뷰

실제테이블에 근거한 논리적 가상테이블(result set)

  1. inline view : 1회용
  2. stored view : db에 저장해서 영구적 사용 가능

view를 사용하면 복잡한 쿼리문을 가독성 좋게 만들 있음

 

실습문제

 

DML

Data Manipulation Language

CRUD - 데이터를 이용하려는 사용자와 Database서버 사이의

인터페이스를 직접적으로 제공하는 언어

C : insert into

R : select ~ from

U : update set

D : delete from

 

insert

테이블에 새로운 을 추가

 

update

테이블에 이미 기록된 행에 대해서 컬럼의 값을 수정

작업 후 행의 개수에는 변화가 없다

 

delete

테이블의 행을 삭제

where절을 반드시 사용 , 안할 시 테이블 내용 다 삭제

 

DDL

Data Definition Language 데이터 정의 언어

데이터베이스객체에 대해서 , 생성 create , 수정 alter , 삭제 drop하는 명령어 모음

rollback / commit 등의 tcl사용이 불가 - 자동 커밋(auto commit)

 

데이터베이스 객체

  1. table
  2. user
  3. sequence : numbering을 담당
  4. index : 컬럼에 인덱스를 넣어, 조회할 때 속도향상 B Tree + 가 기본
  5. view : 가상테이블
  6. package
  7. procedure
  8. function
  9. trigger
  10. synonym

 

create

 

1. NOT NULL

필수항목인 컬럼에 추가하는 제약조건

 

2. Unique

컬럼 입력값에 대해서 중복을 제한하는 제약조건

 

3. primary key

not null + unique 기능을 하지만, 테이블당 한개만 선언가능하다

테이블에서 한행의 정보를 구분하기 위한 고유식별자(identifier)역할을 하기 위해

 

4. foreign key

결성을 유지하기 위한 제약조건

참조하는 컬럼(자식)과 참조되는 컬럼(부모)간의 부모자식테이블관계를 형성

 

5. check

컬럼 도메인의 값을 제한

 

--subquery를 이용한 create table --employee테이블의 특정컬럼 emp_id,emp_name,salary,dept_title,job_name --의 결과셋을 특정테이블로 생성하고 싶다면..

create table emp_copy as select emp_id,emp_name,salary,dept_title,job_name from employee ​ left join department on dept_code = dept_id ​ left join job using (job_code);
select * from emp_copy;

--제약조건 조회 --create table [table명] as로 생성된 테이블에는 not null제약조건만 복사. --생성된 테이블에 제약조건 추가는 alter문을 통해서 사용해야함. select constraint_name,UC.owner,UC.table_name,UC.constraint_type, UC.search_condition from user_constraints UC join user_cons_columns UCC using (constraint_name) where UC.table_name = 'EMP_COPY';

select constraint_name,UC.owner,UC.table_name,UC.constraint_type, UC.search_condition from user_constraints UC join user_cons_columns UCC using (constraint_name) where UC.table_name = 'EMPLOYEE';

 

Alter

 

drop

데이터베이스 객체 삭제시 사용

 

DCL

Data Control Language

grant(권한부여), revoke(권한회수), commit(실행), rollback(복구)

 

2. revoke

권한 회수해보리기

 

Database Object

 

DD - Data Dictionary

자원(데이터베이스 객체)을 효율적으로 관리하기 위해서 다양한 메타정보를 담고 있는 테이블

다양한 메타정보를 담고 있는 테이블

사용자가 DB에 직접적으로 작업하지 않음

사용자가 테이블 등에 작업을 하게되면, 자동으로 DD에 반영됨.

사용자는 조회만 가능하다

  1. user_xxx : 자신의 계정이 소유한 객체 조회
  2. all_xxx : 자신에게 부여받은 객체 조회
  3. dba_xxx : 관리자가 조회하는 모든 객 조회

 

VIEW

stored view

inline view(일회용)과 다르게 저장된 가상테이블

뷰를 사용해서 특정사용자가 원본테이블에 제한적으로 접근하게 함.

view를 생성하기 위해서는 권한을 부여받아야함 , resource에는 포함 안됨

 

 

SEQUENCE

순차적으로 정수값을 자동으로 생성하는 객체, 채번기

 

INDEX

색인 -> 처리속도 향상을 위해서 컬럼에 대하여 생성하는 객체

key = value

key = 해당컬럼값

value = 행이 저장된 주소값

단점 : 덱스에 대한 추가저장공간이 필요, 인덱스생성시간이 소요

데이터변경(insert / update / delete)가 자주 일어나는 컬럼에는 오버헤드가 오히려 더크다

 

어떤 컬럼에 인덱스를 만들어야 될까?

선택도가 좋은 컬럼을 가지고 인덱스를 만들어야 한다.

고유한 값을 많이 가지는 컬럼이 선택도가 좋다고 할 수 있다

선택도가 좋은 컬럼 : 주민번호 > 성명

선택도가 나쁜 컬럼 : 성별

 

효율적으로 인덱스를 사용하려면

  • where절에 자주 사용되는 컬럼을 인덱스를 만들것
  • 전체데이터에서 10~15%에 해당하는 컬럼
  • 조인시에 조건절로 사용되는 컬럼
  • 데이터변경이 적은 컬럼
  • 테이블의 데이터가 아주 많은 컬럼(최소 20만건)

 

복합 인덱스

2개 이상의 컬럼으로 이루어진 인덱스

선행이 무엇이냐가 아주 중요하다

 

PL/SQL

Oracle's Procedural Language Extension to SQL

오라클이 제공하는 절차적 언어 확장

기존 sql에 변수, 조건문, 반복처리등을 지원

 

종류

  1. 익명블럭 : 1회용
  2. 프로시져 : 특정작업처리를 위한 서브프로그램, 단독으로 실행하거나, 다른 프로시저를 호출해서 실행
  3. 함수 : 거의 프로시저와 유사하나 반드시 리턴값이 있다

구조

DECLEAR(선택) : 선언부

 

BEGIN(필수) : 실행부

 

EXCEPTION(선택) : 예외처리부

 

END;(필수)

/ -> pl/sql 선언 끝 및 실행

 

문제

 

--양모자 씌우기 --리턴절에 세미클론 금지, 파라미터와 리턴절의 데이터 타입의 크기 지정 금지 ->컴파일 에러 create or replace function myfunc( ​ P_str varchar2 ) return varchar2 is ​ result varchar2(1000); --리턴될 변수 begin ​ dbms_output.put_line('p_str = ' || P_str ); ​ result := 'd' || P_str || 'b'; ​ return result; end; / --확인 --user_functions(X) --user_procedures에서 object_type = 'FUNCTION'로 검색할 것 select * from user_procedures where object_type='FUNCTION';

--실행 --1. exec명령문 실행 var result varchar2; exec :result :=myfunc('김민우'); print result;

--2. 쿼리문에서 실행 select myfunc('&이름') 양모자 from dual;

--3. 익명블럭 begin ​ dbms_output.put_line(myfunc('&이름')); end; /

--사번을 입력 받아서 해당 사원의 연봉을 계산해서 리턴하는 저장 함수 작성 create or replace function fn_bonus_calc( ​ p_emp_id employee.emp_id%type ) return number is ​ result number; ​ v_sal employee.salary%type; ​ v_bonus employee.bonus%type; begin ​ select salary, nvl(bonus,0) ​ into v_sal, v_bonus ​ from employee ​ where emp_id = p_emp_id;
​ result := trunc((v_sal+(v_salv_bonus))12); ​ return result; end; /

select * from user_procedures where object_type = 'FUNCTION';

--실행 var annualPay number; exec :annualPay := fn_bonus_calc('&emp_id'); print annualPay;

select emp_id, ​ emp_name, ​ fn_bonus_calc(emp_id) 연봉 from employee;

--@실습문제 : 주민번호를 입력 받아서 성별을 리턴하는 저장함수 fn_get_gender를 생성하고 --사번, 사원명, 성별을 조회하는데 사용할 것 create or replace function fn_get_gender( ​ p_emp_no employee.emp_no%type ) return varchar2 is ​ result varchar2(10); ​ gender varchar2(10); begin ​ select case when substr(emp_no,8,1) in('1','3') then '남' ​ else '여' end 성별 ​ into gender
​ from employee ​ where emp_no = p_emp_no;
​ result := gender; ​ return result; end; / drop function fn_get_gender;

var empGender varchar2; exec :empGender := fn_get_gender('&emp_no'); print empGender;

select emp_id 사번, emp_name 사원명, fn_get_gender(emp_no) 성별 from employee where emp_no = '&emp_no';

select * from employee;

--@실습문제 : 사원번호를 받아서 직급명을 리턴하는 저장 함수 fn_get_job_name create or replace function fn_get_job_name( ​ v_emp_id employee.emp_id%type ) return job.job_name%type is ​ result varchar2(10); ​ jn varchar2(10); begin ​ select J.job_name ​ into jn ​ from employee E join job J using(job_code) ​ where emp_id = v_emp_id;
​ result := jn; ​ return result; end; /

var emp_job_name varchar2; exec :emp_job_name := fn_get_job_name('&emp_id'); print :emp_job_name;

select emp_id, emp_name, fn_get_job_name(emp_id) from employee where emp_id = '&emp_id';


--## PROCEDURE

--Stored Procedure --함수와 달리 반환값이 없을 수도 있다. /* create [or replace] procedure 프로시저명( ​ 매개변수명1 [IN | OUT] 자료형, ​ 매개변수명2 [IN | OUT] 자료형, ​ ... ) is ​ 변수선언부; begin ​ 실행부; end; / */ --1. 매개변수 없는 프로시저 : 정기적 실행하는 프로그램시 주로 사용 select * from emp_copy; --emp_copy 테이블의 모든 데이터를 삭제하는 프로시저 create or replace procedure proc_del_all_emp is begin ​ delete from emp_copy; ​ commit; end; / select * from user_procedures;

--실행 exec proc_del_all_emp; select * from emp_copy;

--2. 매개변수 있는 프로시저 insert into emp_copy (select emp_id, emp_name, salary, dept_title, job_name from employee E join department D on E.dept_code = D.dept_id ​ join job J using(job_code)); commit; select * from emp_copy;

--emp_id를 입력 받아서, emp_copy테이블에서 제거하는 프로시저 --IN : 프로시저 입장에서 받는 것(default) create procedure proc_del_emp( ​ p_emp_id IN emp_copy.emp_id%type ) is

begin ​ delete from emp_copy ​ where emp_id = p_emp_id; ​ commit; ​ dbms_output.put_line(p_emp_id || '번 사원을 삭제했습니다!'); end; / exec proc_del_emp('&emp_id');--201삭제 select * from emp_copy where emp_id = '201';

--매개변수 OUT --사번을 입력 받아서 사원명, 급여, 보너스를 OUT하는 프로시저 create or replace procedure proc_select_emp( ​ p_emp_id IN employee.emp_id%type, ​ p_emp_name OUT employee.emp_name%type, ​ p_salary OUT employee.salary%type, ​ p_bonus OUT employee.bonus%type ) is begin ​ select emp_name, salary, nvl(bonus,0) ​ into p_emp_name, p_salary, p_bonus ​ from employee ​ where emp_id = p_emp_id;
end; / --실행 --바인드 변수 선언 var b_emp_name varchar2(30); var b_salary number; var b_bonus number; --프로시저 호출 exec proc_select_emp('&emp_id', :b_emp_name, :b_salary, :b_bonus); --IN과 OUT을 한번에 입력 print :b_emp_name; print :b_salary; print :b_bonus; --exec 명령 후 바인드 변수 자동출력 해주는 옵션 set autoprint on;

--프로시저 활용 예제 : UPSERT --insert + update : 기존에 데이터가 존재하면 update 존재하지 않으면 insert --직급코드 관리 create table job_copy as select * from job;

select* from job_copy; --제약조건 조회 select constraint_name, UC.owner, UC.table_name, UCC.column_name, UC.constraint_type, UC.search_condition from user_constraints UC join user_cons_columns UCC using(constraint_name) where UC.table_name = 'JOB_COPY';

--제약조건 추가 alter table job_copy add constraint pk_job_code primary key(job_code) modify job_name not null; --프로시저 생성 create or replace procedure proc_upsert_job_copy( ​ p_job_code job_copy.job_code%type, ​ p_job_name job_copy.job_name%type ) is ​ v_cnt number; begin ​ --0. null여부 검사 ​ if p_job_name is null then ​ dbms_output.put_line('ERROR: null값 대입 불가'); ​ return;
​ end if; ​ --1. 데이터가 존재하는지 확인 ​ select count(*) ​ into v_cnt ​ from job_copy ​ where job_code = p_job_code;
​ dbms_output.put_line('v_cnt = ' || v_cnt); ​ --2. insert or update ​ if v_cnt = 0 then insert into job_copy values(p_job_code, p_job_name);
​ else update job_copy set job_name = p_job_name where job_code = p_job_code;
​ end if;
​ commit;

-- insert into job_copy values(p_job_code, p_job_name); -- commit; end; / --실행 exec proc_upsert_job_copy('J8', '인턴'); select * from job_copy;

exec proc_upsert_job_copy('J8', '수습'); --ORA-00001: unique constraint (KH.PK_JOB_CODE) violated exec proc_upsert_job_copy('J9', ''); --에러 메세지 보여주기

--2018-10-25 @실습문제

/*@실습문제

  1. 주민번호를 입력받아 나이를 리턴하는 저장함수 fn_get_age를 사용해서 사번, 이름, 성별, 연봉, 나이를 조회/ create or replace function fn_get_age( ​ p_emp_no employee.emp_no%type ) return number is
    ​ empAge number; begin ​ select extract(year from sysdate) - (case when substr(emp_no,8,1) in('1','2') then to_number(substr(emp_no,1,2)) +1900 ​ else to_number(substr(emp_no,1,2)) +2000 end) 나이
    ​ into empAge
    ​ from employee ​ where emp_no = p_emp_no;
    ​ return empAge; end; / select emp_id 사번, emp_name 사원명, fn_get_gender(emp_no) 성별,fn_bonus_calc(emp_id) 연봉, fn_get_age(emp_no) 나이 from employee where emp_no = '&emp_no'; --621225-1985634 select
    from employee;

/2. 사원에게 특별상여금(보너스)를 지급하려고 하는데, 입사일기준으로 차등 지급하려 한다. 입사일기준 10년이상이면 150%, 3년이상 10년미만이면 125%, 3년미만이면 50%를 지급함. 저장함수명 : FN_BONUS_CALC, FN_GET_WORKING_DAYS(HIRE_DATE) 조회컬럼 : 사번, 사원명, 입사일, 근무기간(~년 ~개월), 보너스금액/

--사원번호와 근속년수를 입력받아 상여금 계산 create function FN_BONUS_CALC2( ​ p_emp_id employee.emp_id%type, ​ p_workYear number ) return number is ​ result number; begin ​ select case when p_workYear >= 10 then salary1.5 ​ when p_workYear between 3 and 9 then salary1.25 ​ else salary*0.5 ​ end ​ into result
​ from employee ​ where emp_id = p_emp_id;
​ return result; end; / drop function FN_BONUS_CALC2;

--사번과 입사일을 입력하면 근속 년수를 계산하는 함수 create function FN_GET_WORKING_DAYS( ​ v_emp_id employee.emp_id%type ​ --v_hire_date employee.hire_date%type ) return number is ​ workYear number; begin ​ select to_number(trunc(months_between(sysdate, hire_date)/12)) 근속년수 ​ into workYear ​ from employee ​ where emp_id = v_emp_id;

end; / --정답! select emp_id 사번, emp_name 사원명, FN_GET_WORKING_DAYS(emp_id) 근속년수, ​ FN_BONUS_CALC2(emp_id, FN_GET_WORKING_DAYS(emp_id)) 상여금 from employee;

select emp_name, hire_date, to_number(trunc(months_between(sysdate, hire_date)/12))근속년수, round(mod(months_between(sysdate, hire_date), 12)) 개월 from employee order by 1;

/3. 기존부서테이블의 DEPT_ID, DEPT_TITLE만 복제한 DEPT_COPY를 생성한다. DEPT_ID 컬럼 PK추가. DEPT_ID 컬럼 변경 CHAR(3) DEPT_COPY를 관리하는 프로시져 PROC_MAN_DEPT_COPY를 생성한다. 첫번째 인자로 작동FLAG값 'UPSERT'/'DELETE'를 받는다. UPDATE시, 데이터가 존재하지 않으면 INSERT, 데이터가 존재하면 UPDATE DELETE시, 해당부서에 사원이 존재하는지(EMPLOYEE에서 FK로 참조하고있다고 가정)를 검사해서, 존재하면, 경고메세지와 함께 실행취소함. 그렇지 않으면, 삭제. 프로시저의 매개변수에 기본값을 지정하면, 생략가능함(매개변수명 mode 자료형 := 값)/ create table dept_copy as select dept_id, dept_title from department; select* from dept_copy;

alter table dept_copy add constraint pk_dept_id primary key(dept_id); alter table dept_copy modify dept_id char(3);

create or replace procedure PROC_MAN_DEPT_COPY( ​ flagNo IN number, ​ p_dept_id dept_copy.dept_id%type, ​ p_dept_title dept_copy.dept_title%type
) is ​ v_cnt number; ​ c_cnt2 number; begin ​ select count(*) ​ into v_cnt ​ from department ​ where dept_id = p_dept_id;


​ else

​ if c_cnt2 = 0 then delete from dept_copy where dept_id = p_dept_id; ​ else dbms_output.put_line('해당 직원이 있어 삭제 불가'); ​ return; ​ end if;


​ end if; ​ commit; end; /

 

 

select * from user_unique; --기록하기 commit; --commit 전까지 복구 --rollback;

select * from location; select * from employee; desc employee; select * from job; select * from department; select * from sal_grade;

--제약조건 조회 select constraint_name, UC.owner, UC.table_name, UCC.column_name, UC.constraint_type, UC.search_condition from user_constraints UC join user_cons_columns UCC using(constraint_name) where UC.table_name = 'EMPLOYEE'; /* constraint_type = 제약조건 유형 (user_constraints)

  • P : 기본키 (Primary Key)
  • R : 외래키 (Foreign Key) + r_constraint_name = 참조하고 있는 제약조건 명 (user_constraints)
  • U : 고유값 (Unique)
  • C : 체크 (Not Null, Check). search_condition 컬럼에서 내용 확인 가능합니다.

*/

 

CURSOR

sql문의 result set(결과집합)을 가리키는 참조변수(포인터)

하나 이상의 row에 대해서 순차적으로 접근할 수 있다.

--OPEN ~ FETCH ~ CLOSE

 

커서 종류

  1. 암시적 커서 : 모든 sql문이 실행됨과 동시에 암시적커서가 생성되서 처리됨

  2. 명시적 커서 : 쿼리 결과집합에 직접 접근해서 처리할 필요가 있을경우 커서를 선언해서 사용한다.

    (선언부)cursor

    (실행부)OPEN

    (실행부)fetch

    (실행부)close

 

 

TRIGGER

방아쇠, 연쇄반응

특정이벤트, ddl, dml문이 실행되었을 때

자동으로 어떤동작이 수행되도록 처리하는 객체

 

트리거 종류

  1. dml trigger

  2. ddl trigger

    logon/logoff trigger

회원테이블에 탈퇴가 발생하면, 탈퇴회원테이블에 추가하기

테이블변경이 있을때, 수정내역을 로그테이블에 추가하기

+ Recent posts