1. sqlplus 사용자/관리자 설정

-- sys 계정 접속
cmd>sqlplus sys/암호 as sysdba;
sql>select * from all_users; -- 전체 사용자 확인

-- scott lock 설정
sql>alter user scott account lock;

-- scott 계정
sql>conn scott/java301$; -- 에러 : lock 걸림

-- soctt lock 해제
sql>alter user scott account unlock;

-- sys 계정으로 scott 암호 변경
sql>alter user scott identified by 암호;

-- sys 암호 변경
cmd>sqlplus / as sysdba;
sql>alter user sys identified by 암호;
sql>conn sys/암호 as sysdba;

-- 오라클 서버 shutdown
sql>conn sys/암호 as sysdba;
sql>shutdown immediate;

-- sys 계정으로 connect 했을때 다음과 같은 메시지가 출력 된 경우
-- 휴지 인스턴스에 접속되었습니다.
-- 데이터베이스가 close 된 상태이므로 sys 계정에서 startup

-- 오라클 서버 startup
cmd>sqlplus /nolog;
sql>conn sys/암호 as sysdba;
sql>startup;

-- 사용자 확인(sys 계정)
select username, password, created from dba_users;
select username, password, created from dba_users
where username='scott'; --scott 계정만 확인

-- 사용자 추가
create user 사용자명 identified by 비밀번호;

-- 사용자 제거
drop user 사용자명 cascade;

-- conn, 테이블 스페이스 권한 설정(sys 계정)
grant connect, resource to 사용자명;

-- 휴지통 비우기
purge recyclebin;

-- 테이블 컬럼 확인
select * from user_tab_columns where table_name='테이블명';
select * from all_tab_columns where owner ='디비계정' and table_name = '테이블명';

-- 테이블 목록 확인
sql>select * from tab;

-- 테이블 구조 확인
sql>desc insa;

2. 기본 쿼리 함수

-- isql*plus 실행(사용자)
http://컴퓨터명:5560/isqlplus/

-- em 실행(관리자)
http://컴퓨터명:1158/em/

-- tnsnames.ora 에 정의된 connect_identifier 으로 접속
cmd>sqlplus scott/tiger@ora111
-- ora111 : tnsnames.ora 에 정의된 connect_identifier

-- sql 에서 cmd 창으로 잠시 빠져 나가야 하는 경우
sql>host
cmd>exit -- 복귀

-- sqlplus 에서 *.sql 파일 실행
-- 파일이 존재하는 경로에서 sqlplus를 실행하고,
-- "@파일명" 명령을 실행
sql>@ex.sql

-- 경로 지정 실행
sql>@d:\sql\ex.sql

-- sqlplus 에서 sql 작업 내용 자동 저장
sql>spool d:\sql\test.txt
sql>select * from insa; -- d:\sql\test.txt 파일 생성
sql>spool off -- 스풀 정지

-- 산술연산자
+ : 덧셈 - : 뺄셈 * : 곱셈 / : 나눗셈 ( ) : 괄호

-- 비교연산자
= : 같다. > : 크다. >= : 크거나 같다.
< : 적다. <= : 작거나 같다. <> : 같지 않다. != : 같지 않다.

-- 논리 연산자
and : 논리 곱 or : 논리 합 not : 부정

-- sql 연산자
in(값, 값, ...) : 피연산자가 식 목록 중 하나와 동일한 경우 true → OR
between ~ and: 피연산자가 범위 안에 있는 경우 true
like : 피연산자가 패턴과 일치하는 경우 true

-- 문자열 연결 연산자 : ||
select name || ' 님' as 이름, city 출신도 from insa;

-- 자료 검색
-- 형식 : select 필드명, 필드명 ..... from 테이블

-- 기술 순서
from -> where조건 -> group by -> select -> order by

-- 전체 자료 출력
sql>select * from insa;

-- 이름, 전화번호, 부서 출력
select name, tel, buseo from insa;

-- 필드명 변경 : 필드명 as 별칭 / 필드명 as "별 칭" / 필드명 별칭...
select name as 이름, tel as "전화번호", buseo 부서 from insa;

-- 연산 : 이름과 기본급을 10,000으로 나눈 값 출력
select name, basicpay/10000 기본급 from insa;

-- 조건 검색 : 출신도 서울인 사람 출력(이름, 출신도)
select name, city from insa where city='서울';

-- 조건 검색 : 출신도 서울, 기본급 200만원 이상 출력(이름, 출신도, 기본급)
select name, city, basicpay from insa
where city='서울' and basicpay>=2000000;

-- 기본급+수당이 200만원 이상이고 서울사람(이름, 기본급+수당 출신도)
select name as 이름, basicpay+sudang as "급여",city as 출신도 from insa
where basicpay+sudang>=2000000 and city='서울';

-- dual
-- dual 테이블은 데이터 딕셔너리와 함께 oracle에 의해 자동으로 생성되는
-- 테이블로 사용자 sys의 스키마에 존재하며, 모든 사용자가 사용 가능하다.
select sysdate from dual; -- 시스템내 현재 날짜 표시

-- 스칼라 함수 : 단일데이터를 반환
-- substr (char, m [,n]) : 문자열 추출
select substr('seoul korea', 7, 3) from dual; -- 'kor' → 앞기준, 7번째부터 3번 이동한 값 출력
select substr('seoul korea', -5, 3) from dual; -- 'kor' → 뒤기준, 5번째부터 3번 이동한 값 출력
select substr('seoul korea', 7) from dual; -- 'korea' → 앞기준, 7번째부터 모두 출력

-- 출신도 서울이고 주민번호 8번째의 첫번째 숫자가 1 또는 3인 사람 출력(이름, 주민번호, 출신도)
select name, ssn, city from insa
where city='서울' and
(substr(ssn, 8, 1)='1' or substr(ssn, 8, 1)='3');

-- 위와 같으나 in 연산자를 이용하여 간결하게
select name, ssn, city from insa
where city='서울' and
(substr(ssn, 8, 1) in ('1', '3'));

-- 생년월일이 78~82년생만 출력(이름, 주민번호)
select name, ssn from insa
where substr(ssn, 1, 2) >= 78 and substr(ssn, 1, 2) <= 82;
-- 주민번호 1번째부터 2번째의 값이 78보다 크거나 같고 82보다 작거나 같은 조건

-- 서울사람이면서 김씨만 출력(이름, 출신도)
select name, city from insa where city='서울' and substr(name, 1, 1)='김';
-- 이름의 첫번째 값이 '김' 과 같은 조건

-- length (column | expression) : 문자열 길이
select length('대한민국') from dual; -- 4

-- lengthb (column | expression) : 문자열 바이트수
select lengthb('대한민국') from dual; -- 8

-- instr (column | expression, 'string' [,m] [,n])
-- 문자의 위치 반환. m값은 시작위치, n값은 발생 횟수이며 m과 n의 기본 값은 1
select instr('korea','e') from dual; -- 4

-- lpad (column | expression, n, ['string']), rpad (column | expression, n, ['string'])
-- expression의 문자열을 제외한 공간에 문자열을 왼쪽(오른쪽)에 채운다.
select lpad ('korea', 12, '*') from dual; -- '*******korea'
select lpad('*', 0, '*') from dual; -- null

-- replace (text, search_string [, replacement_string]) : 문자열 치환
select replace('seoul korea', 'seoul', 'busan') from dual;
select name, replace(buseo, '부', '팀') as "부서" from insa;

-- rtrim(char [,set]), ltrim(char [,set])
trim (leading | trailing | both trim_character from trim_source)
공백을 제거하거나 오른쪽(왼쪽)의 문자열을 'set'문자열에 포함된 문자를 삭제, 없는 문자를 만나면 정지

select rtrim('우리나라 ') from dual;
select rtrim('우리나라대한', '대한') from dual; -- 오른쪽의 '대한' 절삭
select trim(' 우리 ') from dual;
select name, rtrim(buseo, '부') || '팀' as "부서" from insa;
select ltrim('aabbbbcdad', 'ba') from dual; -- 결과 : cdad(ba중 한문자라도 첫부분에 포함되어 있으면 제거)

-- mod(m, n) : m을 n으로 나눈 나머지
select mod(11, 4) "나머지" from dual;

-- round(n [,m]) : 반올림 → 미입력시 기본값은 0이며 1은 소수점 둘째가 반올림되어 첫째자리가 표시되고 -는 반대
select round(15.193, 1) "반올림" from dual; -- 15.2
select round(15.193) from dual; -- 15
select round(15.193, 0) from dual; -- 15
select round(15.193, -1) from dual; -- 20
select trunc(15.79, -1) "절삭" from dual; -- 10

-- trunc(n [,m]) : 절삭 → round와 형식은 같다.
select trunc(15.79, 1) "truncate" from dual; -- 15.7
select trunc(15.79, -1) "truncate" from dual; -- 10

select name, trunc(basicpay/10000) 만원권 from insa;

-- 기본급 10만원당 * 출력
select name, basicpay,
lpad('*', trunc(basicpay/100000), '*') 그래프 from insa;
-- trunc(basicpay/100000) 결과가 1보다 적으면 null 출력

-- 암시적 형 변환
값을 할당할 때 오라클 서버는 다음과 같이 자동으로 값을 변환할 수 있다.

varchar2, char → number
varchar2, char → date
number → varchar2
date → varchar2

-- to_char(label [, fmt]) : mlslabel datatype을 varchar2 타입으로 변환
-- to_char(n [, fmt [, 'nlsparams'] ]) : 숫자를 문자로 변환(varchar2 타입)
-- to_char(d [, fmt [, 'nlsparams'] ]) : 날짜를 문자로 변환(varchar2 타입)

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') now from dual;
select sysdate, to_char(sysdate, 'yyyy-mm-dd day') from dual;
select sysdate, to_char(sysdate, 'yyyy"년" mm"월" dd"일"') from dual;
select sysdate, to_char(sysdate, 'dd "of" month') from dual; -- 07/04/05 05 of april
select to_char(-1234, '99999mi') from dual; -- 1234-
select to_char(-1234, '9999pr') from dual; -- <1234>
select to_char(1234.345, '9.999eeee') from dual; -- 1.234e+03
select to_char(1234, '999999v9999') from dual; -- 12340000
select to_char(1234, 'l9,999,999') from dual; -- ￦1,234

select name, basicpay+sudang 급여 from insa;
select name, to_char(basicpay+sudang, 'L9,999,999') 급여 from insa; -- L : 통화기호
select name, to_char(ibsadate, 'yyyy"년" mm"월" dd"일" day') 입사일 from insa;
select name, ibsadate from insa where to_char(ibsadate,'yyyy') = '2000'; -- 입사날짜의 형식을 yyyy로 바꿔 2000과 같은 값 출력

-- 날짜형식 변경(기본:rr/mm/dd)
alter session set nls_date_format='yyyy-mm-dd';

-- rr : 1900년대의 년도를 기준으로 하여 이전 50년도에서 이후 49년까지는 기준년도와
-- 가까운 1850년도에서 1949년도까지의 값으로 표현하고, 이 범위를 벗아 날 경우 다시
-- 2000년을 기준으로 이전 50년도에서 이후 49년까지의 값을 출력한다. 하지만 yy는
-- 무조건 system 상의 년도를 따른다.

-- to_number(char [,fmt [, 'nlsparams'] ]) : 문자를 숫자로 변환 (그닥 잘 사용될일 없음)

-- to_date(char [, fmt [, 'nlsparams'] ]) : 문자를 날짜로 변환 (그닥 잘 사용될일 없음)

-- 살아온 날 수 구하기
select trunc(sysdate - to_date('2000/10/10', 'yyyy/mm/dd')) "살아온날수" from dual;

-- 근무년수 계산하기 : trunc
select name, trunc((sysdate-ibsadate)/365) 근무년수 from insa;

-- 월을 한국 또는 영어권 스타일로 출력
select to_char(sysdate, 'mon dd day', 'nls_date_language = american') as en,
to_char(sysdate, 'mon dd day', 'nls_date_language = korean') as ko from dual;

-- sysdate : 시스템에 저장된 현재 날짜를 반환
select sysdate from dual; -- 07/04/05
select name, trunc(sysdate-ibsadate) 근무일수 from insa;

-- current_date : 현재 session의 날짜 정보를 반환
select current_date from dual; -- 07/04/05

-- add_months(d, n) : 해당 날짜에 n만큼의 달수를 더한다.
select current_date "today", add_months(current_date,1) "next month" from dual;

-- last_day(d) : 정의된 날짜의 달에서 마지막일이 몇일인지 돌려준다.
select sysdate, last_day(sysdate) "last day", last_day(sysdate) - sysdate "days left" from dual;

-- 입사월의 마지막 날짜는 ??(30, 31, 29, 28 등)
select name, ibsadate, last_day(ibsadate) from insa;

-- months_between(d1, d2) : 정의된 두 날짜사이의 차이(d1 - d2)를 월로 돌려준다.
select months_between(to_date('2007-05-20', 'yyyy-mm-dd'),
to_date('2007-04-10', 'yyyy-mm-dd')) from dual; -- 1.32258065

-- 근무년수 계산하기 : trunc, months_between
select name, ibsadate, trunc(months_between(sysdate, ibsadate)/12) 근무년수 from insa;

-- 현재 날짜에서 주민번호상의 날짜의 차로 나이 구하기(1900년대 생이라는 전제하에)
select ssn, trunc(months_between(sysdate, to_date('19'||substr(ssn,1,6), 'yyyymmdd'))/12)
나이 from insa;

-- round(d [,fmt]) : 정해진 날짜를 fmt를 기준으로 반올림
select round(to_date('2007-07-10', 'yyyy-mm-dd'),'year') "first of the year"
from dual; -- 08/01/01(년 반올림. 7월 1일부터 반올림)

select name, ibsadate, round(ibsadate, 'month') from insa; -- 월 반올림(16일 기준)

-- trunc(d,[fmt]) : 정해진 날짜를 fmt를 기준으로 버림
select trunc(to_date('27-08-04','dd-mm-yy'), 'year') from dual; -- 04/01/01
select name, ibsadate, trunc(ibsadate, 'month') from insa; -- 모두 출력

-- next_day(d, char)
명시된 요일(char)이 돌아오는 날짜를 계산. 요일은 숫자로 표현 가능(sunday : 1, monday : 2, ...)
select next_day(to_date('07/05/01', 'yy/mm/dd'), 6) from dual; -- 07/05/04

-- 오늘을 기준으로 가장 가까운 수요일은 언제 ??
select sysdate, next_day(sysdate, '수요일') from dual;

-- 이번주 일요일, 토요일 알아내기
select sysdate,
case
when to_char(sysdate, 'day') = '일요일' then sysdate
else next_day(sysdate,1)-7
end as 주시작일,
case
when to_char(sysdate, 'day') = '토요일' then sysdate
else next_day(sysdate, '토요일')
end 주마지막일,
from dual;

-- 날짜에 산술 연산을 사용하는 경우
날짜 + 숫자 → 날짜 : 날짜에 일수를 더하여 날짜 계산
날짜 - 숫자 → 날짜 : 날짜에 일수를 감하여 날짜 계산
날짜 + 숫자/24 → 날짜 : 날짜에 시간을 더하여 날짜 계산
날짜 - 날짜 → 일수 : 날짜에 날짜를 감하여 일수 계산

-- null : 값이 존재하지 않은 상태
is null : null 이면 참
not is null : null 이 아니면 참

-- null 관련 함수
nvl(expr1, expr2) : expr1이 null 이면 expr2 반환
nvl2(expr1, expr2, expr3) : expr1이 null이 아니면 expr2를 반환하고, null이면 expr3를 반환
nullif(expr1, expr2) : 두 표현식을 비교해서 같으면 null 반환하고, 같지 않으면 expr1을 반환
coalesce(expr1, expr2, ……, exprn) : 표현식 목록에서 첫 번째로 null이 아닌 것을 반환한다.
즉, expr1이 널이 아니면 expr1을 반환하며, expr1이 널이고 expr2가 널이 아니면 expr2를 반환한다.
lnnvl(조건) : 조건이 거짓이거나 값이 존재하지 않으면 true를 리턴하며, 조건이 거짓이면 false를 리턴 한다.
select 리스트에서는 사용할 수 없으며 where 절에서만 사용 가능 하다.

select name, tel from insa where tel is null;
select name, tel from insa where tel is not null;
select name, nvl(tel, '0000-0000') tel from insa;
select name, nvl(tel, '0000-0000') tel from insa where tel is null;
select name, nvl2(tel, tel, '0000-0000') tel from insa;

-- order by : 정렬
-- asc : 오름차순, desc : 내림차순
select name, city, basicpay+sudang pay from insa
order by basicpay+sudang asc;

select name, city, basicpay+sudang pay from insa
order by basicpay+sudang desc;

select name, city, basicpay+sudang pay from insa
order by city desc, basicpay+sudan;

select name, city, basicpay+sudang pay from insa
order by city desc, basicpay+sudan desc;

select name, city, basicpay+sudang pay from insa
where substr(ssn, 8, 1) in ('1', '3')
order by city desc, basicpay+sudan desc;

-- 서울 사람중에서 기본급+수당 내림차순으로 정렬
이름, 출신도, 기본급+수당
select name, city, basicpay+sudang pay from insa
where city='서울'
order by pay desc; -- 필드의 별명은 order by에 사용 가능

-- 여자중 부서오름차순으로 정렬하고 부서가 같으면 기본급 내림차순 정렬
이름, 주민번호, 부서, 기본급
select name, ssn, buseo, basicpay from insa
where sustr(ssn, 8, 1) in (2, 4)
order by buseo, basicpay desc;

-- distinct : 중복 배제(반대 : all -- 기본)
select buseo from insa;

select distinct buseo from insa;
select distinct buseo, jikwi from insa;

-- in
in(값, 값, ...) : 피연산자가 식 목록 중 하나와 동일한 경우 true

select name, city from insa where city = '서울' or
city = '인천' or city = '광주' or city = '부산'
select name, city from insa where city in ('서울', '인천', '광주', 부산')

-- between ~ and : 피연산자가 범위 안에 있는 경우 참
select name, basicpay from insa
where basicpay >=1500000 and basicpay <=2000000;

select name, basicpay from insa
where basicpay between 1500000 and 2000000;

select name, ibsadate from insa
where ibsadate between '2000-01-01' and '2000-12-31';

-- like : 주어진 문자열이 패턴과 일치하는지 여부 확인
-- % : 여러문자 공통
-- _ : 한문자 공통

select name from insa where name like '김%';
select name from insa where name not like '김%';
select tel from insa where tel like '%3%';
select tel from insa where tel like '%3%' or tel like '%5%';
select ssn from insa where ssn like '_0%';
select tel from insa where tel like '%%';

-- decode : 각각의 조건에 맞는 값에 대한 처리를 결과를 리턴
select name, ssn,
decode(substr(ssn, 8,1),'1','남자','3','남자','2','여자','4','여자') 성별
from insa;

select name, ssn,
decode(substr(ssn, 8,1), '1', '남자') 성별 from insa;

-- 이름, 성별, 나이 출력
-- 성별 : 주민번호(ssn의 8번째)가 1,3->남자 2,4->여자
-- 나이 : 주민번호를 이용
select name,
decode(substr(ssn,8,1), '1','남자','2','여자','3','남자','4','여자') 성별,
trunc(months_between(sysdate, to_date('19'||substr(ssn,1,6),'yyyymmdd'))/12) 나이
from insa;

select name,
decode(mod(substr(ssn,8,1), 2),1,'남자',0,'여자') 성별,
trunc(months_between(sysdate, to_date('19'||substr(ssn,1,6),'yyyymmdd'))/12) 나이
from insa;

-- case ~ end : 조건에 따라 다른 결과를 리턴해야 하는 경우(decode 보다 성능 우수)
select name, basicpay+sudang pay,
case
when (basicpay+sudang) >= 2500000 then
round((basicpay+sudang) * 0.03, -1)
when (basicpay+sudang) >= 2000000 then
round((basicpay+sudang) * 0.02, -1)
else 0
end as tax
from insa;

-- 집합 연산자
-- union : 첫 번째 sql문의 결과와 두 번째 sql문의 결과 중 중복된 데이터를 제거한다.
-- union all : 첫 번째 sql문의 결과와 두 번째 sql문의 결과를 모두 출력한다.
-- minus : 차집합. 첫 번째 sql문 결과에는 있고, 두 번째 sql문의 결과에는 없는 데이터를 출력한다.
-- intersect : 인터섹트는 두 번째 sql문의 결과와 첫 번째 sql문의 결과에 중복된 행만 출력(교집합)

-- 합집합 연산자 : union -> 중복 데이터 한번만 출력
select name, city, buseo from insa where buseo='개발부'
union
select name, city, buseo from insa where city='인천';

-- 합집합 연산자 : union all -> 중복 데이터도 출력
select name, city, buseo from insa where buseo='개발부'
union all
select name, city, buseo from insa where city='인천' order by city;
-- order by 는 마지막에 한번만

-- 합집합 연산자 : union
-- 필드의 개수와 자료형이 일치하면 필드가 달라도 가능
select name, city, buseo,basicpay from insa where buseo='개발부'
union
select name, city, buseo,sudang from insa where city='인천';

-- 차집합
select name, city, buseo from insa where buseo='개발부'
minus
select name, city, buseo from insa where city='인천';

-- 교집합
select name, city, buseo from insa where buseo='개발부'
intersect
select name, city, buseo from insa where city='인천';

3. 그룹함수

-- 집계함수(count, sum, avg, max, min) 등
count : 행의 개수를 리턴
avg : null값을 제외한 행의 평균값을 출력한다.
sum : null값을 제외한 합계를 출력한다.
stddev : null값을 제외한 표준편차를 출력한다.
variance : null값을 제외한 분산을 출력한다.
max : 최대값을 출력한다.
min : 최소값을 출력한다.

-- 집계함수는 where 절에 사용불가, group by를 사용하지 않는 경우 일반 필드와 사용불가

-- count : 자료의 개수 구하기
select count(num) from insa;
select count(*) from insa;
select count(tel) from insa;
select count(nvl(tel, '0')) from insa;

select count(decode(substr(ssn, 8, 1), '1', '1', '3', '1')) 남자,
count(decode(substr(ssn, 8, 1), '2', '1', '4', '1')) 여자
from insa;

select count(decode(mod(substr(ssn, 8, 1), 2), '1', '1')) 남자,
count(decodemod(substr(ssn, 8, 1), 2), '0', '1')) 여자
from insa;

select count(*) from insa where city='서울';
select count(decode(substr(ssn, 8, 1), '1', '1', '3', '1')) 서울남자
from insa where city='서울';

select count(distinct buseo) from insa;

-- sum, avg, max, min
select sum(basicpay), avg(basicpay), max(basicpay),
min(basicpay) from insa;

select name, sum(basicpay) from insa;
-- 오류(일반 필드와 집계함수는 같이 사용 불가)

-- group by(그룹별 통계)
select sum(basicpay) from insa
group by buseo;
select buseo, sum(basicpay) from insa
group by buseo;

select buseo, jikwi, count(*) from insa
group by buseo, jikwi
order by buseo;

select buseo, count(*) 인원수 from insa
where substr(ssn, 8, 1) in ('2', '4')
group by buseo;

select buseo,
count(decode(substr(ssn, 8, 1), '1', '1', '3', '1')) 남자,
count(decode(substr(ssn, 8, 1), '2', '1', '4', '1')) 여자
from insa
group by buseo;

select count(*) from insa where buseo='개발부';

- having
그룹 또는 집계에 대한 검색 조건을 지정한다. having은 select 문하고만 사용될 수 있다.

select buseo, count(*) from insa
group by buseo
having count(*) >= 7;

-- 부서별 인원수가 가장 많은 부서(rownum는 두개 이상의 부서가
-- 가장 많은 인원수를 가진 경우 부서 하나만 출력 된다.)
select * from (select buseo, count(*) 인원수 from insa group by buseo
order by 인원수 desc) where rownum =1;

select buseo, 인원수 from (select buseo, count(*) 인원수,
rank() over(order by count(*) desc) r from insa
group by buseo ) i where i.r = 1;

-- 여자 인원수가 가장 많은 부서
select buseo, 인원수 from (select buseo, count(*) 인원수,
rank() over(order by count(*) desc) r from insa
where substr(ssn, 8, 1) in ('2', '4')
group by buseo ) i where i.r = 1;

-- 출신도별 성별 인원수
select city,count(decode(substr(ssn,8, 1),1,1,3,1)) 남자,
count(decode(substr(ssn,8,1),2,1,4,1)) 여자
from insa group by city;

select city,
decode(substr(ssn,8, 1),1,'남자',2,'여자',3,'남자',4,'여자') 성별
,count(*) 인원수 from insa group by city,
decode(substr(ssn,8, 1),1,'남자',2,'여자',3,'남자',4,'여자')
order by city;

-- 입사년도별 인원수 구하기
select to_char(ibsadate, 'yyyy') 입사년도, count(*) 인원수
from insa
group by to_char(ibsadate, 'yyyy')
order by 입사년도;

-- 기본급이 평균 이하인 사원출력(이름, 기본급)
select avg(basicpay) from insa;
select name, basicpay
from insa
where basicpay <=
(select avg(basicpay) from insa);

-- 부서별 남자인원수가 5명 이상인 부서와 남자인원수
select buseo, count(*) 남자인원수
from insa where substr(ssn,8,1) in (1, 3)
group by buseo;

select buseo, count(*) 남자인원수
from insa where substr(ssn,8,1) in (1, 3)
group by buseo
having count(*)>=5;

-- 부서별 여자인원수가 3명이상인 부서와 여자인원수출력
select buseo, count(*) from insa
where substr(ssn, 8, 1) in (2, 4)
group by buseo
having count(*) >= 3;

-- 기본급+수당이 가장 많은 사람의 이름, 기본급+수당 출력
select name, (basicpay+sudang) 급여
from insa
where (basicpay+sudang) =
(select max(basicpay+sudang) from insa);

-- 입사월별 인원수 구하기(월, 인원수), 월 오름차순
select to_char(ibsadate, 'mm') 월, count(*) 인원수
from insa
group by to_char(ibsadate, 'mm')
order by 월 ;

select to_number(to_char(ibsadate, 'mm')) || '월' as 월,
count(*)||'명' 인원수
from insa
group by to_char(ibsadate, 'mm')
order by to_char(ibsadate, 'mm');

-- rollup : 그룹화 하고 그룹에 대한 부분합
select buseo, jikwi, sum(basicpay) from insa
group by rollup(buseo, jikwi);

-- cube(rollup 결과에 group by 절의 조건에 따라 모든 가능한
-- 그룹핑 조합에 따른 결과 출력
select buseo, jikwi, sum(basicpay) from insa
group by cube(buseo, jikwi);

-- grouping 함수
grouping 함수는 rollup이나 cube 연산자와 함께 사용하여 grouping 함수에 기술된 컬럼이
그룹핑 시 즉, rollup이나 cube 연산 시 사용이 되었는지를 보여 주는 함수

select buseo, jikwi, grouping(buseo), grouping(jikwi), avg(basicpay) as 평균급여
from insa group by rollup (buseo, jikwi);

select buseo, jikwi, grouping(buseo), grouping(jikwi), avg(basicpay) as 평균급여
from insa group by cube (buseo, jikwi);

-- grouping sets 함수
grouping sets 함수는 group by 절에서 그룹 조건을 여러 개 지정할 수 있는 함수이다.
grouping sets 함수의 결과는 각 그룹 조건에 대해 별도로 group by한 결과를 union all한 결과와 동일하다.

select buseo , jikwi, null, avg(basicpay) from insa group by buseo , jikwi
union all
select null, jikwi, city, avg(basicpay) from insa group by jikwi, city;

select buseo , jikwi, city, avg(basicpay) from insa
group by grouping sets ((buseo , jikwi), (jikwi, city));

-- rownum : 쿼리의 결과로 나오는 각각의 행들에 대한 순서 값을 나타내는 의사 컬럼
select rownum, insa.name, basicpay from insa;

select rownum, name, basicpay from insa
order by basicpay desc; -- 사용하면 안됨..왜??

select rownum, data.* from (
select name, basicpay from insa
order by basicpay desc) data;

select rownum, data.* from (
select name, basicpay from insa
order by basicpay desc) data
where rownum >=10 and rownum <=15; -- 안됨

select * from (
select rownum rnum, data.* from (
select name, basicpay from insa
order by basicpay desc) data
) where rnum >=10 and rnum <=15;

-- 분석 함수
-- rank() over( )
-- 그룹 내에서 위치(순위)를 반환한다.
-- over : 순위를 부여하기 위한 집합의 정렬 기준과 분할 기준의 정의
-- partition by : value expression1을 기준으로 분할, 생략하면 전체 집합을 대상으로 순위부여
-- order by : 각 분할 내에서 데이터를 정렬하는 기준 컬럼 지정
-- nulls first | nulls last : 정렬 결과에서 null 값의 위치 지정

select name, basicpay,
rank() over(order by basicpay desc) 순위
from insa;

select name, basicpay, sudang,
rank() over(order by basicpay desc, sudang desc) 순위
from insa;

select name, basicpay, buseo,
rank() over(partition by buseo
order by basicpay desc) 순위
from insa;

-- 1~10등까지 출력(이름, 기본급, 순위)
select * from (
select name, basicpay,
rank() over(order by basicpay desc) as r
from insa) where r >= 1 and r <= 10;

select * from (
select name, basicpay from insa
order by basicpay desc
) where rownum <=10; -- 안됨

-- 기본급 상위 10%만 출력(이름,기본급)
select name, basicpay from (
select name, basicpay,
rank() over(order by basicpay desc) r
from insa
) where r <=
trunc((select count(*) from insa) * 0.1);

-- 부서별 기본급이 가장 높은 사람 출력(이름 부서 직위 급여)
select name, buseo, jikwi, basicpay from (
select name, buseo,basicpay, jikwi,
rank() over(partition by buseo
order by basicpay desc) r
from insa)
where r=1;

select name, buseo, jikwi, pay from (
select name, buseo,basicpay+sudang as pay, jikwi,
rank() over(partition by buseo
order by basicpay+sudang desc) r
from insa)
where r=1;

-- dense_rank() over( ) 함수
-- k 함수와 동일하지만 동일한 순위가 발생할 경우 연속적으로 순위를 부여한다.
-- 예를 들어 "100, 100, 90"의 순위를 desc 순으로 rank 함수로 부여하면 "1, 1, 3"이 되지만
-- dense_rank 함수는 "1, 1, 2"가 된다.

-- row_number() over( ) 함수
-- 분할별로 정렬된 결과에 대해 순위를 부여하는 기능이다. 분할은 전체 행을 특정 컬럼을 기준으로
-- 분리하는 기능으로 group by 절에서 그룹화 하는 방법과 같은 개념이다.
-- 동일한 값이 있는 경우 rank 함수는 1, 1, 3 형식으로 출력하지만 row_number는 1, 2, 3 순으로 출력

select name, basicpay,
row_number() over(order by basicpay desc) from insa;

-- count() over()
조건에 만족하는 행의 수 반환

-- 개발부 직원 중 이름, 기본급을 출력하고 각 직원의 급여보다 같거나 적게 받는 사람의 인원수를 출력
select name, basicpay,
count(*) over(order by basicpay)
from insa
where buseo='개발부';

select name, basicpay,
count(*) over(order by basicpay desc)
from insa
where buseo='개발부';

-- sum() over()
조건에 만족하는 행의 합 반환

select name, buseo, jikwi, basicpay,
sum(basicpay) over(order by buseo, jikwi),
sum(basicpay) over(partition by buseo
order by jikwi)
from insa;

-- avg() over()
조건에 만족하는 행의 평균 반환

select name, city, basicpay,
basicpay -
round(avg(basicpay) over(partition by city order by city))
"도시평균과차이",
basicpay -
(select round(avg(basicpay)) from insa)
"전체평균과차이"
from insa;

-- first_value() over()
정렬된 값 중에서 첫 번째 값을 구한다.

-- insa 테이블에서 전체 사원의 급여와, 각 부서의 최고 급여를 출력하고 비교하기
select name, buseo, basicpay,
first_value(basicpay) over(partition by buseo order by basicpay desc)
from insa;

-- 이름, 기본급, 최대기본급과의 차이
select name,basicpay,
first_value(basicpay) over(order by basicpay desc) - basicpay 차이
from insa;

-- first와 last
-- first와 last는 그 자체가 함수가 아니라 분석 함수에 사용되는 키워드이다.
-- 특정 그룹에 따라 최대값괴 최소값을 추출한다.

-- 형식
-- 집계합수 keep (dense_rank first[last] order by expr [asc | desc])
-- over(partition by expr ...)

-- 부서별 최대 급여를 받는 사람과 최대급여, 최소급여를 받는 사람과 최소 급여 출력
select buseo,
max(num || ' ' || name) keep (dense_rank first order by basicpay desc) 최대급여이름,
max(basicpay) 최대급여,
min(num || ' ' || name) keep (dense_rank last order by basicpay desc) 최소급여이름,
min(basicpay) 최소급여
from insa
group by busdeo;

4. 테이블작성/수정/삭제

-- 데이터 딕셔너리(data dictionary) : table과 view들의 집합으로 database에
-- 대한 정보를 제공하는 중요한 부분으로 database 생성시 sys schema 안의
-- 내부 table로 구성

select count(*) from dictionary;
select count(*) from user_tables;
select count(*) from tabs;
select * from tabs; -- user_tables 와 동일
select * from tab;

-- 컬럼명만 확인
select cname from col where tname='insa';

-- 컬럼명 및 자료형등 출력
select column_name, data_type, data_length,data_precision from user_tab_columns where table_name='insa';
select column_name, data_type, data_length,data_precision from cols where table_name='insa';

-- 제약조건 확인
select * from user_constraints where table_name='insa';

select constraint_name, table_name, constraint_type from user_constraints;

-- 사용자에게 주어진 권한
select * from user_sys_privs;

-- 자료형
-- 문자 데이터 타입
-- char(n) : 고정길이 문자 데이터를 저장하며 최대 길이는 2000자이고 길이를 명시하지 않으면 기본 길이는 1
-- nls(국가별 언어 집합)는 한글과 영문만 가능 하다. 남은 공간은 공백(space)로 채운다.

-- varchar2(n) : 가변 길이 문자 데이터를 저장하며 최대 길이는 4000자이고, 반드시 길이를 명시해야 한다.
-- nls(국가별 언어 집합)는 한글과 영문만 가능하다.
-- varchar 는 최대 2000개 문자를 저장하며 varchar2와는 다르게 varchar(10)로 선언하면
-- null을 채워 실제로는 10개의 공간을 사용한다. 하지만 varchar2(10)는 필요한 문자까지만 저장하는
-- variable length이며 최대 4000개 문자까지 저장할 수 있다.

-- nchar, nvarchar2 : 다양한 언어의 문자 값(국가별 언어 집합 : nls)을 저장하고 조회할 수 있다.
-- 바이트 단위가 아닌 문자 단위로 컬럼 길이를 지정한다. 예를 들어 nchar(7) 는 한문자가
-- 2byte인 경우 14byte가 사용된다.

-- 숫자 데이터 타입
-- number(p, s) : p(1~38)는 정밀도로 전체 자리수를 나타내며 기본 값이 38이고 s(-84~127)는
-- 소수점 이하의 자릿수이다. 정수나 실수 저장하기 위한 가변길이의 표준 내부 형식이다.
-- 사용 예
-- number : number(38, 0)과 동일
-- number(5) : 다섯 자리 정수
-- number(10, 3) : 1234567.6789 입력 시 1234567.679가 저장되며 12345678.678 입력 시 overflow 에러가 발생 한다.

-- 날짜 데이터 타입
-- date : 『년/월/일 시:분:초』까지 저장하며, 기본적으로 년/월/일 정보를 출력한다.

-- 테이블 작성
-- 아이디 숫자 7
-- 이름 문자 20
-- 나이 숫자 3
-- 전화번호 문자 25 (우편번호, 학번 .. : 문자)

-- 테이블 작성
create table test1 (
id number(7)
, name varchar2(20)
, age number(3)
, tel varchar2(25)
);

-- 테이블 목록 확인
select * from tab;
select table_name from tabs;

-- 테이블 구조 확인
desc test1;

-- 테이블에 필드 추가
alter table 테이블이름 add (컬럼명 datatype [default 값] [, 컬럼명 datatype]...);

alter table test1 add (birth date, bigo varchar2(255));
desc test1;

-- 테이블의 컬럼폭 및 자료형 변경
alter table 테이블명 modify (컬럼명 datatype [default 값] [, 컬럼명 datatype]...);

alter table test1 modify (bigo varchar2(200));
desc test1;

-- 테이블의 필드명 변경
alter table 테이블명 rename column 변경전컬럼명 to 새로운컬럼명;

alter table test1 rename column bigo to memo
desc test1;

-- 테이블의 필드 삭제
alter table 테이블명 drop column 컬럼명;

alter table test1 drop column age;
desc test1;

-- 테이블명 변경 : 테이블, 뷰, 시퀀스, 시너님의 이름을 변경
rename old_name to new_name;

rename test1 to exam1;
select * from tab;

-- 기존 테이블을 이용하여 테이블 작성
create table 테이블명 [컬럼명 (, 컬럼명) ,...] as subquery;

create table insa1 as select num, name, basicpay,
sudang, basicpay+sudang pay from insa;

select * from tab;
desc insa1
select * from insa1;

-- 테이블 구조만 복사(제약조건은 복사 되지 않는다)
create table insa2 as select * from insa where 1=0;

-- 테이블 삭제
drop table 테이블명 [cascade constraints] [purge];

-- 휴지통
drop table insa1; -- 휴지통에 삭제된 테이블 저장
select * from tab;

-- 휴지통 확인
select object_name, original_name, droptime, dropscn
from recyclebin;

-- 휴지통 테이블 내용 확인(휴지통 정보 확인 후 object_name 명으로 확인)
select * from "bin$lmg0n7nosncafq8lkjzm2q==$0" ;

-- 휴지통 복원(insa1 은 삭제하기 전의 원래 테이블명)
flashback table insa1 to before drop;
select * from tab;

-- 휴지통 복원(bin 이름(object_name)으로 되돌리기)
flashback table "bin$lmg0n7nosncafq8lkjzm2q==$0"
to before drop;

-- 휴지통 비우기
drop table insa2;
purge recyclebin;
select * from tab;

-- 휴지통에 버리지 않고 바로 테이블 삭제
drop table insa1 purge;
select * from tab;

5. 자료 추가/수정/삭제

-- 테이블에 데이터 추가
-- insert into 테이블명 [(컬럼명1, 컬럼명2, ...)] values (값1, 값2, ...)
-- 한개의 테이블에 하나의 행만 추가 할 수 있다.

select * from tab;
drop table test1 purge;
drop table insa1 purge;
drop table insa2 purge;

create table test1 (
num number(7) primary key
,name varchar2(20) not null
,birth date not null
,memo varchar2(255)
);

insert into test1 values (1, '홍길동', '2000-10-10', '테스트');
insert into test1 (num, name, birth, memo)
values (2, '이순신', '1999-01-01', '충무공');
insert into test1 values (3, '오라클', '1999-12-12'); --오류(필드개수와 값의 개수가 달라서)
insert into test1 values (3, '오라클', '1999-12-12', null);
insert into test1 (num, name, birth) values (4, '소녀시대', '1988-01-01');

select * from test1;

commit; -- 트랜잭션완료(데이터베이스에 저장)
rollback; -- 트랜잭션취소(데이터베이스에 저장되지 않음)

insert into test1 values (1, '호호호', '1999-10-10', '테스트');
-- 오류(제약조건 위반, 기본키는 중복을 허용하지 않는다.)

insert into test1 (num, name, memo) values (5, '이순신', '충무공');
-- 오류(birth는 not null 속성을 가지고 있는데 birth를 입력하지 않았으므로)

insert into test1 (num, name, memo, birth) values (5, '이순신', '충무공', '05/05/88');
-- 오류(날짜 형식이 맞지 않아서)

insert into test1 (num, name, memo, birth)
values (5, '이순신', '충무공',
to_date('05/05/88', 'mm/dd/yy'));
-- yy는 시스템시간을 기준으로하므로 2088 출력
commit;

-- 아래처럼 해야 1988
insert into test1 (num, name, memo, birth)
values (6, '이순신', '충무공',
to_date('05/05/88', 'mm/dd/rr'));
commit;
select * from test1

-- 여러행 추가
insert into 테이블명 [(컬럼명, 컬럼명, ...)] subquery;

create table test2 as select num, name, birth from test1 where 1=0;
-- 구조만복사(제약 조건은 복사 되지 않음)
desc test2 -- 확인

-- 제약조건 확인
select constraint_name, table_name, constraint_type from user_constraints;

-- 여러행 추가(기존 테이블의 자료를 추가)
insert into test2 select num, name, birth from test1;
commit;
select * from test2;

insert into test2(name, birth)
select name, birth from test1;
commit;
select * from test2;

-- 하나의 insert문을 이용 여러 테이블에 데이터 추가
insert all | first
[into 테이블_1 values (컬럼_1, 컬럼_2,...)]
[into 테이블_2 values (컬럼_1, 컬럼_2,...)]
.......
subquery;

-- all : 서브 쿼리의 결과 집합을 해당하는 insert 절에 모두 입력
-- first : 서브 쿼리의 결과 집합을 해당하는 첫 번째 insert 절에 입력

create table test3 as select * from test1 where 1=0;
create table test4 as select * from test1 where 1=0;

insert all
into test3 values(num, name, memo, birth)
into test4 (num, name) values (num, name)
select * from test1;
commit;

select * from test3;
select * from test4;

-- 조건에 만족하는 자료만 여러 테이블에 추가
insert all
when 조건절_1 then
into [테이블_1] values (컬럼_1, 컬럼_2, ...)
when 조건절_2 then
into [테이블_2] values (컬럼_1, 컬럼_2, ...)
........
else
into [테이블_3] values (컬럼_1, 컬럼_2, ...)
subquery;

create table test5 as select * from test1 where 1=0;
create table test6 as select * from test1 where 1=0;

insert all
when num >3 then
into test5 values(num, name, memo, birth)
when num <=3 then
into test6 (num, name) values (num, name)
select * from test1;
commit;

select * from test5;
select * from test6;

-- 수정
-- update 테이블명 set 컬럼명= 변경할값[, 컬럼명= 변경할값, ...] [where 조건];

select * from test1;

update test1 set birth='2010-10-10', memo='수정';
-- 절대로 이렇게 하면 안된다. 이렇게 수정하면 쫓겨난다.(전체가 다 수정되므로)
-- where조건 추가해야 한다.
select * from test1;
rollback;

update test1 set birth='2010-10-10', memo='수정'
where num=1;
commit;
select * from test1;

update test1 set num=111 where num=1;
-- 기본키는 nc 속성을 갖지 않는다. 즉 변경 가능 하다.
commit;
select * from test1;

update test1 set num=2 where num=111;
-- 오류(기본키 중복)

create table score (
num number(7) primary key
,name varchar2(20) not null
,com number(3)
,excel number(3)
,word number(3)
);

insert into score(num, name)
select num, name from insa where num<=1007;
commit;

select * from score;

update score set com=35, excel=95, word=100 where num=1001;
update score set com=85, excel=80, word=80 where num=1002;
update score set com=45, excel=65, word=55 where num=1003;
update score set com=75, excel=75, word=60 where num=1004;
update score set com=85, excel=85, word=80 where num=1005;
update score set com=85, excel=65, word=85 where num=1006;
update score set com=65, excel=90, word=80 where num=1007;
commit;

select num, name, com, excel, word, com+excel+word as tot,
round((com+excel+word)/3, 1) as ave,
case
when com>=40 and excel>=40 and word>=40 and
(com+excel+word)/3 >= 60 then '합격'
when (com+excel+word)/3 < 60 then '불합격'
else '과락'
end as pan
from score;

-- 여자의 excel 점수에 + 5(insa 테이블 참조)
update score set excel=excel+5
where num in (select num from insa
where substr(ssn, 8, 1) in (2, 4));
commit;

-- 자료 삭제
delete [from] 테이블명 [where 조건];

select * from tab;
select * from test1;

-- 모든 자료 삭제(테이블 구조는 지워지지 않는다.)
delete from test1;
commit;
select * from test1;
drop table test1 purge;

-- 조건에 만족하는 데이터 삭제
select * from test2;
delete from test2 where num=1; -- from은 생략 가능
commit;
select * from test2;

-- 모든 자료 삭제
truncate table test2;
-- 모든 자료를 삭제하는 경우 delete보다 빠름
-- rollback 불가(자동 commit)

select * from test2;
drop table test2 purge

-- score 테이블의 여자만 삭제
select * from score;
delete from score where num in
(select num from insa where substr(ssn, 8, 1) in (2, 4));
select * from score;
commit;

select * from tab;

drop table test2 purge;
drop table test3 purge;
drop table test4 purge;
drop table test5 purge;
drop table test6 purge;

select * from tab;

-- merge(병합)
merge into 테이블_명 별칭
using 대상테이블/뷰 별칭
on 조인조건
when matched then
update set
컬럼_1=값_1
,컬럼_2=값_2
when not matched then
insert (컬럼_1, 컬럼_2, ...)
values(값_1, 값_2, ...);

create table test1(
id number primary key
,name varchar2(20)
,pay number
,sudang number
);

create table test2(
id number primary key
,sudang number
);

insert into test1(id, name, pay, sudang) values
(1, 'a', 100, 10);
insert into test1(id, name, pay, sudang) values
(2, 'b', 150, 20);
insert into test1(id, name, pay, sudang) values
(3, 'c', 130, 0);

insert into test2(id, sudang) values (2, 5);
insert into test2(id, sudang) values (3, 15);
insert into test2(id, sudang) values (4, 10);
commit;

merge into test2 s
using (select id, sudang from test1) t
on (s.id = t.id)
when matched then
update set s.sudang=s.sudang+t.sudang
when not matched then
insert (s.id, s.sudang) values (t.id, t.sudang);

select * from test1;
select * from test2;

merge into test2 s
using test1 t
on (s.id = t.id)
when matched then
update set s.sudang=s.sudang+t.sudang
when not matched then
insert (s.id, s.sudang) values (t.id, t.sudang);

drop table test1 purge;
drop table test2 purge;

6. 제약조건

-- 제약조건 확인
desc user_constraints; -- user_constraints 구조 확인

select * from user_constraints where table_name='insa'; -- 어떤 컬럼에 제약조건이 부여되었는지 확인 불가
-- p : 기본키, c : not null 등, u : unique, r : 참조키 등

select constraint_name, table_name, constraint_type from user_constraints;

-- 현재 user가 가지고 있는 column에 할당된 제약조건에 대한 정보
select * from user_cons_columns; -- 어떤 컬럼에 기본키가 부여되었는지 확인 가능

-- 개체 무결성(primary key, unique)
릴레이션에 저장되는 튜플(tuple)의 유일성을 보장하기 위한 제약조건

-- 참조 무결성(foreign key)
참조 무결성은 릴레이션 간의 데이터의 일관성을 보장하기 위한 제약조건이다

-- 컬럼 레벨 기본키 지정
컬럼 정의 시 해당 컬럼별로 지정하는 무결성 제약조건이다.

create table 테이블명 (
컬럼명 데이터타입 [constraint 제약조건명] primary key
);

create table test1 (
id number primary key
,name varchar2(20)
,tel varchar2(25)
);

-- 제약 조건 확인
select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

-- 현재 user가 가지고 있는 column에 할당된 제약조건에 대한 정보
select * from user_cons_columns;

-- 제약 조건 이름 부여
create table test2 (
id number constraint pk_test2_id primary key
,name varchar2(20)
,tel varchar2(25)
);

select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

-- 테이블 레벨 기본키
하나 이상의 컬럼을 참조하거나 하나의 컬럼에 두 개 이상의 제약조건을 지정하는 경우 사용한다.

create table 테이블명 (
컬럼명 데이터타입
,constraint 제약조건명 primary key(컬럼명 [,컬럼명])
);

create table test3 (
id number
,name varchar2(20)
,tel varchar2(25)
,constraint pk_test3_id primary key (id)
);

create table test4 (
id number(4)
,code varchar2(4)
,name varchar2(20)
,constraint pk_test4_id primary key (id, code)
);

select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

-- 자료 입력
insert into test4(id, code, name) values (1, '1111', 'a');
insert into test4(id, code, name) values (1, '2222', 'b');
insert into test4(id, code, name) values (1, '2222', 'c');
-- 오류 : 제약조건 위반
insert into test4(id, code, name) values (1, null, 'c');
-- 오류 : 기본키는 null이 될수 없다.
commit;

select * from test4;

-- 기본키 삭제
alter table 테이블명 drop primary key;
alter table 테이블명 drop constraint 제약조건이름;

alter table test4 drop primary key;

select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;
where table_name='TEST4'; -- 조건은 대소문자 비교

-- 이미 존재하는 테이블에 기본키 추가
alter table 테이블명 add constraint 제약조건이름 primary key(필드[, 필드]);

alter table test4 add constraint pk_test4_id primary key(id);
-- 오류 : test4 테이블의 id에 유일성을 위반하는 데이터가 있으므로

alter table test4 add constraint pk_test4_id primary key(id, code);

-- unique
-- 테이블에서 지정한 컬럼의 데이터가 중복되지 않고 유일하다.
-- null을 허용할 경우 null은 중복이 가능하다.

-- 컬럼 레벨
create table 테이블명 (
컬럼명 데이터타입 constraint 제약조건명 unique
);

-- 테이블 레벨
create table 테이블명 (
컬럼명 데이터타입
,constraint 제약조건명 unique(컬럼명)
);

create table test5(
id number not null
,ssn varchar2(14)
-- ,ssn varchar2(14) constraint uk_test5_ssn unique
,tel varchar2(20)
,constraint pk_test5_id primary key (id)
,constraint uk_test5_ssn unique (ssn)
);

select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

insert into test5 (id) values (1);
-- ssn이 not null이 아니므로 null 허용
insert into test5 (id) values (2);
-- unique에서 null은 중복 허용
insert into test5 (id, ssn) values (3, '1');
insert into test5 (id, ssn) values (4, '1');
-- 오류 : ssn이 중복적인 데이터 이므로
commit;

select * from test5;

-- 컬럼 레벨
create table 테이블명 (
컬럼명 데이터타입 constraint 제약조건명 check(조건)
);
create table 테이블명 (
컬럼명 데이터타입 check(조건)
);

-- 테이블 레벨
create table 테이블명 (
컬럼명 데이터타입
,constraint 제약조건명 check(조건)
);

-- gender 컬럼에 남자와 여자만 입력을 허용 할 경우
gender varchar2(4) check(gender check(gender in ('남자', '여자'))

-- score 컬럼에 0~100 점만 입력을 허용 할 경우
score number(3) check (score between 0 and 100)

-- 컬럼 레벨
create table test2 (
id number
,name varchar2(20)
,score number(3) check (score between 0 and 100)
,constraint pk_test2_id primary key(id)
);

또는
-- 테이블 레벨
create table test2 (
id number
,name varchar2(20)
,score number(3)
,constraint pk_test2_id primary key(id)
,constraint ck_test2_score
check (score between 0 and 100)
);

또는
-- 컬럼 레벨(제약조건명 지정)
create table test2 (
id number
,name varchar2(20)
,score number(3) constraint ck_test2_score
check (score between 0 and 100)
,constraint pk_test2_id primary key(id)
);

select constraint_name, table_name,
constraint_type, search_condition
from user_constraints;

-- 존재하는 필드에 check 제약 추가
alter table 테이블명 add constraint 제약조건명 check (조건);

alter table test1 add constraint ck_test1_score
check (score between 0 and 100);

-- 제약 조건 확인
select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

insert into test1 (id, ssn, name, score)
values (4, '888', 'bbb', 80);
commit;

insert into test1 (id, ssn, name, score)
values (5, '999', 'hhh', 101);
-- 오류 : check 제약 위반

-- not null 추가
-- 테이블에서 지정한 컬럼의 데이터가 null(또는 '') 값을 갖지 못한다. ' ' 처럼 공백이 있는 경우는 null이 아니다.

-- 테이블 생성시
create table 테이블명 (
필드명 자료형 not null,
);

-- 존재하는 테이블에 not null 추가
alter table test2 modify 컬럼명 not null;
alter table test2 modify (컬럼명 자료형 not null);
alter table 테이블명 constraint 제약조건명 check(컬럼명 is not null);

alter table test2 modify name not null;
또는
alter table test2 modify (name varchar2(20) not null);

-- default(초기값)
-- insert할 때 값을 입력하지 않은 경우 default에서 설정한 값으로 입력
-- insert와 update 문에서 특정 값이 아닌 디폴트값을 입력할 수 도 있다.

create table 테이블명 (
필드명 자료형 default 값,
);

create table ex1(
no number(6) not null
,sal number(8, 2)
,nowdate date default sysdate
);

insert into ex1 values(1111, 30000, '');
insert into ex1 values(2222, 40000, default);
insert into ex1 (no, sal) values (2222, 40000);
commit;
select * from ex1;

update ex1 set nowdate=default where no=1111;

select * from ex1;

-- default 제거
alter table 테이블명 modify 컬럼명 default null;

alter table ex1 modify nowdate default null;

-- 확인
select column_name, data_type, data_precision, data_length, nullable, data_default
from user_tab_columns where table_name='ex1';

-- 테이블 삭제
drop table ex1 purge;

-- 제약조건 제거
select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

-- 기본키 제거
alter table 테이블명 drop primary key;
alter table 테이블명 drop constraint 제약조건이름;

-- not null 제거
alter table 테이블명 modify 컬러명 null;
alter table 테이블명 drop constraint 제약조건이름;

-- 기타 제약조건 제거
alter table 테이블명 drop constraint 제약조건이름;

-- 참조(외래, foreign)키
-- 두 테이블의 데이터 간 연결을 설정하고 강제 적용하는 데 사용되는 열이다. 한 테이블의 기본 키 값이 있는 열을
-- 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다. 이 때 두 번째 테이블에 추가되는 열이 외래 키가 된다.
-- 부모 테이블이 먼저 생성된 후 자식 테이블(foreign key를 포함하는 테이블)이 생성되어야 한다.
-- foreign key는 부모 테이블의 primary key, unique만 참조할 수 있고, 컬럼의 값과 일치하거나 null 값이어야 한다.
-- 부모테이블의 컬럼명과 자식테이블의 컬럼명은 일치하지 않아도 되지만 자료형은 일치해야 한다.
-- 참조 무결성 제약조건에서 부모 테이블의 참조 키 컬럼에 존재하지 않는 값을 자식 테이블에 입력하면 오류가 발생한다.
-- on delete set null은 자식 테이블이 참조하는 부모 테이블의 값이 삭제되면 자식 테이블의 값을 null 값으로 변경시킨다.

create table 테이블명 (
컬럼명 자료형
,constraint 제약조건이름 foreign key(컬럼명 [,컬럼명])
references 참조할테이블(컬럼명 [,컬럼명])
[on delete cascade]
);

create table exam1 (
code number primary key
,name varchar2(20)
);

create table exam2 (
id number primary key
,name varchar2(100)
,code number
,constraint fk_exam2_code foreign key(code)
references exam1(code)
);

-- exam2(참조 테이블에 자료 먼저 추가)
insert into exam2(id, name, code) values(1, '홍길동', 1);
-- 오류(참조키 위반, 참조당하는 테이블에 없는 코드를 입력했으므로)

-- exam1에 추가
insert into exam1(code, name) values(100, '신발');
insert into exam1(code, name) values(200, '가방');

insert into exam2(id, name, code) values(1, '홍길동', 100);
insert into exam2(id, name, code) values(1, '홍길동', 100); -- 오류(기본키 중복)
insert into exam2(id, name, code) values(2, '홍길동', 100);

update exam1 set code=300 where code=100;
-- 오류(100번이 참조 당하고 있으므로)

update exam1 set code=400 where code=200;
-- 참조당하고 있지 않으므로 수정 가능

delete from exam1 where code=100;
-- 오류(exam2 테이블에서 100을 참조하고 있으므로)

delete from exam1 where code=200;
-- 삭제가능(exam2에서 참조하고 있는것이 없으므로)

drop table exam1 purge;
-- 삭제 불가(참조 당하고 있으므로)

-- 참조키 삭제
alter table 테이블명 drop constraint 참조키제약조건명;

alter table exam2 drop constraint fk_exam2_code;

-- 존재하는 테이블에 참조키 추가
alter table 테이블명 add
constraint 제약조건명 foreign key(컬럼명[, 컬럼명])
references 참조할테이블명(컬럼명[, 컬럼명]);

alter table exam2 add
constraint fk_exam2_code foreign key(code)
references exam1(code);

drop table exam2 purge;
drop table exam1 purge;

create table exam1 (
code number primary key
,name varchar2(20)
);

create table exam2 (
id number primary key
,name varchar2(100)
,code number
,constraint fk_exam2_code foreign key(code)
references exam1(code)
on delete cascade
);

insert into exam1(code, name) values (100, '신발');
insert into exam1(code, name) values (200, '가방');
insert into exam1(code, name) values (300, '옷');

insert into exam2(id, name, code)
values(1, '홍길동', 100);
insert into exam2(id, name, code)
values(2, '이기자', 100);
insert into exam2(id, name, code)
values(3, '홍길동', 200);
insert into exam2(id, name, code)
values(4, '심심해', 100);

select * from exam1;
select * from exam2;

-- 자료 삭제(exam1을 삭제하면 참조된 exam2 자료도 삭제)
-- on delete cascade 때문에
delete from exam1 where code=100;
select * from exam1;
select * from exam2;

drop table exam1; -- 오류
drop table exam1 cascade constraints;
-- 테이블과 제약조건도 삭제

select * from tab;

select * from exam2; -- 참조한 컬럼의 데이터는 null로 변경

select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

drop table exam2 recyclebin;

-- 제약 조건 활성화/비활성화
create table exam1 (
id number primary key
,name varchar2(20)
);

select constraint_name, table_name, status,
constraint_type from user_constraints;

-- 기본키 비활성화
alter table exam1 disable primary key cascade;

select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

-- name에 not null 제약설정
alter table exam1 add constraint exam1_name_nn
check(name is not null);

select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

insert into exam1(id, name) values (1, null);
-- 오류(name : not null)
insert into exam1(id, name) values (1, '홍길동');
insert into exam1(id, name) values (1, '이순신');
-- 가능 : 기본키가 비활성화 된 상태이므로 중복 허용

-- not null 비활성화
alter table exam1 disable constraint exam1_name_nn cascade;

select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

insert into exam1(id, name) values (3, null);
select * from exam1;

-- 기본키활성화
alter table exam1 enable constraint sys_c005440;
-- 오류 : 유일성을 위반하는 자료가 있으므로

delete from exam1 where name='이순신';

alter table exam1 enable constraint sys_c005440;
-- 가능 : 유일성을 위반하는 자료가 없으므로

select constraint_name, table_name, r_constraint_name, constraint_type, search_condition
from user_constraints;

drop table exam1 purge;

7. 조인

-- equi 조인
-- 두 개 이상의 테이블에 관계되는 컬럼들의 값들이 일치하는 경우에 사용하는 가장 일반적인 join 형태로
-- where 절에 '='(등호)를 사용한다.
-- equi join은 단순 join 또는 내부 join이라고도 한다.

select * from book;
select * from danga;

-- 형식-1
select 테이블명1.컬럼명, 테이블명2.컬럼명 from 테이블명1, 테이블명2 [, 테이블명3 ]
where 테이블명1.컬럼명1=테이블명2.컬럼명1 [and 조건];

select b.b_id, title, c_name, price
from book b, danga d where b.b_id = d.b_id;

-- 형식-2
select 테이블명1.컬럼명, 테이블명2.컬럼명 from 테이블명1
join 테이블명2 using (컬럼명1)
join 테이블명3 using (컬럼명2);

select b_id, title, c_name, price
from book join danga using(b_id);

-- 형식-3
select 테이블명1.컬럼명, 테이블명2.컬럼_명 from 테이블명1
join 테이블명2 on 테이블명1.컬럼명1=테이블명2.컬럼명1;

select b.b_id, title, c_name, price
from book b join danga d
on b.b_id = d.b_id;

-- 형식-4
select 테이블명1.컬럼명, 테이블명2.컬럼명 from 테이블명1
natural join 테이블명2;

-- 만약 match되는 컬럼이 여러 개 일 경우, natural join에서는 특정 컬럼을 지정하지 않으므로
-- match되는 컬럼 모두를 연결 조건으로 사용한다.
-- natural join의 경우 match되는 컬럼이 여러 개일 경우 모두 join조건으로 사용하므로,
-- 실행속도나 performance면에서 좋지 않다.

select b_id, title, c_name, price
from book natural join danga;

-- 판매 실적
-- 책코드, 책명, 수량, 단가, 고객명, 금액
select b.b_id, title, p_su, price, g_name, p_su*price 금액
from book b join danga d on b.b_id=d.b_id
join panmai p on b.b_id=p.b_id
join gogaek g on p.g_id=g.g_id;

select b.b_id, title, p_su, price, g_name, p_su*price 금액
from book b ,danga d, panmai p, gogaek g
where b.b_id=d.b_id
and b.b_id=p.b_id
and p.g_id=g.g_id;

-- 책코드, 책명, 전체판매권수(판매된 책의)
select b.b_id, title, p_su from book b join panmai p
on b.b_id=p.b_id;
select b.b_id, title, sum(p_su) 권수 from book b join panmai p
on b.b_id=p.b_id
group by b.b_id, title;

-- 책코드, 책명, 단가, 전체판매권수, 금액(판매된 책의)
select b.b_id, title, price, sum(p_su) 권수,
sum(p_su*price) 금액
from book b join panmai p on b.b_id=p.b_id
join danga d on b.b_id=d.b_id
group by b.b_id, title, price;

-- 판매된 전체권수가 10권 이상인 책만출력
-- (책코드, 책명, 수량)
select b.b_id, title, sum(p_su) 권수 from
book b join panmai p on b.b_id=p.b_id
group by b.b_id, title
having sum(p_su)>=10;

-- 판매된 책코드와 책이름 출력(중복 배제)
select distinct b.b_id, title from
book b join panmai p on b.b_id=p.b_id;

-- 가장 판매 권수가 많은 책코드와 책이름
select b_id, title from (
select b_id, title, 권수, rank() over(order by 권수 desc) r from (
select b.b_id, title, sum(p_su) 권수 from book b
join panmai p on b.b_id=p.b_id
group by b.b_id, title
)
) where r=1;

select b_id, title from (
select b.b_id, title, sum(p_su) 권수,
rank() over(order by sum(p_su) desc) r
from book b
join panmai p on b.b_id=p.b_id
group by b.b_id, title
) where r=1;

-- 고객별 판매금액(고객코드, 고객명, 판매금액)
select g.g_id, g_name, (p_su*price) 금액
from panmai p, danga d, gogaek g
where p.b_id=d.b_id and g.g_id = p.g_id;

select g.g_id, g_name, sum(p_su*price) 금액
from panmai p, danga d, gogaek g
where p.b_id=d.b_id and g.g_id = p.g_id
group by g.g_id, g_name;

-- outer join(외부 조인)
-- join 조건을 만족하지 않는 행을 보기 위한 추가적인 join의 형태이다.
-- 일반적인 join으로 얻을 수 없는 데이터를 구하고 싶을 때 사용하며, '+' 연산자를 사용한다.
-- outer join은 조인 조건의 양측 컬럼 값 중에서 하나가 null인 경우에도 조인 결과를
-- 출력할 수 있는 방법이다.
-- equi join은 조인 조건에서 양측 컬럼 값 중에서 하나라도 null이면 '=' 비교 결과가
-- 거짓이 되어 조인 결과로 출력되지 않는다.

select b.b_id, title, p_su from book b
join panmai p on b.b_id=p.b_id; --equi 조인

-- left outer join
-- 왼쪽 테이블의 데이터는 모두 출력하고 오른쪽 테이블은 조건에 맞는 데이터만 출력

select 테이블1명.컬럼명, 테이블2명.컬럼명 from 테이블1명, 테이블2명
where 테이블1명.컬럼명=테이블2명.컬럼명(+);

또는

select 테이블1명.컬럼명, 테이블2명.컬럼명 from 테이블1명
left outer join 테이블2명
on 테이블1명.컬럼명=테이블2명.컬럼명;

select b.b_id, title, p_su from book b
left outer join panmai p on b.b_id=p.b_id;

select b.b_id, title, p_su from book b, panmai p
where b.b_id=p.b_id(+);

-- right outer join
오른쪽 테이블의 데이터는 모두 출력하고 왼쪽 테이블은 조건에 맞는 데이터만 출력

select 테이블1명.컬럼명, 테이블2명.컬럼명 from 테이블1명, 테이블2명
where 테이블1명.컬럼명(+)=테이블2명.컬럼명;

또는

select 테이블1명.컬럼명, 테이블2명.컬럼명 from 테이블1명
right outer join 테이블2명
on 테이블1명.컬럼명=테이블2명.컬럼명;

select b.b_id, title, p_su from panmai p
right outer join book b on b.b_id=p.b_id;

select b.b_id, title, p_su from panmai p, book b
where p.b_id(+) = b.b_id;
-- b.b_id(+) = p.b_id; -- 안됨

select b.b_id, title, price, p_su, p_su*price amt from book b
left outer join danga d on b.b_id=d.b_id
left outer join panmai p on b.b_id=p.b_id;

select b.b_id, title, price, nvl(p_su, 0) p_su,
nvl(p_su, 0)*price amt from book b
left outer join danga d on b.b_id=d.b_id
left outer join panmai p on b.b_id=p.b_id;

-- 한권도 판매되지 않은 책
select b.b_id, title, p_su from book b
left outer join panmai p on b.b_id=p.b_id;
select b.b_id, title from book b
left outer join panmai p on b.b_id=p.b_id
where p_su is null;

-- full outer join(left와 right outer join 결합형태)

select 테이블1명.컬럼명, 테이블2명.컬럼명
from 테이블1명 full outer join 테이블2명 on 테이블1명.컬럼명=테이블2명.컬럼명;

select g.g_id, g_name, b_id from panmai p
full outer join gogaek g on p.g_id=g.g_id;

select a.num, b.num, a.name,b.name,
a.buseo, b.buseo,a.city, b.city from
(
select num, name, buseo, city from insa
where city='인천'
) a
full outer join
(
select num, name, buseo, city from insa
where buseo='개발부'
) b
on a.num=b.num;

-- non-equi 조인
-- 두테이블에 관련성있는 컬럼이 없고 한테이블 컬럼의 값이 다른 테이블 컬럼에
-- 포함된 경우 사용(= 대신 >, between 사용)

select b.b_id, title, price from book b
join danga d on b.b_id = d.b_id;

select b.b_id, title, price from book b
join danga d on b.b_id between d.b_id and d.b_id;

-- cross join
상호 조인은 조인에 포함된 테이블의 카티션 곱(cartisian product)을 반환한다.

select b.b_id, title, price from book b
cross join danga d;

-- self join
자신의 테이블을 alias를 사용하여 마치 두 개의 테이블처럼 join하는 형태이다.

select a1.b_id, a1.name, a2.name from au_book a1
join au_book a2 on a1.b_id = a2.b_id
order by a1.b_id

select a1.b_id, a1.name, a2.name from au_book a1
join au_book a2 on a1.b_id = a2.b_id and a1.name > a2.name
order by a1.b_id

8. 서브쿼리

-- 서브쿼리(subquery)
-- 하위 쿼리는 단일 값을 반환하며 select, insert, update, delete 문이나 다른
-- 하위 쿼리 내부에 중첩된 select 쿼리 이다.
-- 하위 쿼리는 식이 허용되는 모든 위치에서 사용할 수 있으며 단독으로 실행 가능 하다.

--서점코드 서점명 판매금액합 비율
select g.g_id, g_name, sum(p_su*price) amt,
round(sum(p_su*price) / (
select sum(p_su*price) from panmai p1, danga d1
where p1.b_id=d1.b_id
) * 100, 1) 비율
from gogaek g join panmai p on g.g_id=p.g_id
join danga d on p.b_id=d.b_id
group by g.g_id, g_name;

-- 1. 년도별 판매현황 출력
-- (년도 판매금액)
select to_char(p_date, 'yyyy') 년도, sum(p_su*price) amt
from danga d join panmai p on p.b_id=d.b_id
group by to_char(p_date, 'yyyy');

-- 2. 년도별 서점 판매현황 출력
-- (년도 서점코드 서점명 판매금액)
select to_char(p_date, 'yyyy') 년도, g.g_id, g_name,
sum(p_su*price) amt
from gogaek g join panmai p on g.g_id=p.g_id
join danga d on p.b_id=d.b_id
group by to_char(p_date, 'yyyy'),
g.g_id, g_name
order by 년도;

-- 3. 년도별 서점 판매현황이 가장 많은 자료 출력
-- (년도 서점코드 서점명 판매금액)
select 년도, g_id, g_name, amt from (
select 년도, g_id, g_name, amt,
rank() over(partition by 년도 order by amt desc) r
from (
select to_char(p_date, 'yyyy') 년도, g.g_id, g_name,
sum(p_su*price) amt
from gogaek g join panmai p on g.g_id=p.g_id
join danga d on p.b_id=d.b_id
group by to_char(p_date, 'yyyy'),
g.g_id, g_name
order by 년도
)
) where r=1;

-- with
-- 서브쿼리를 미리 블럭으로 정한후 사용(여러번 사용될 경우 간결)

with year_panmai as (
select to_char(p_date, 'yyyy') 년도, g.g_id, g_name,
sum(p_su*price) amt
from gogaek g join panmai p on g.g_id=p.g_id
join danga d on p.b_id=d.b_id
group by to_char(p_date, 'yyyy'),
g.g_id, g_name
order by 년도
)
select 년도, g_id, g_name, amt from (
select 년도, g_id, g_name, amt,
rank() over(partition by 년도 order by amt desc) r
from year_panmai
) where r=1;

-- 판매된 책코드, 책이름
select distinct book.b_id, title from book join panmai
on book.b_id = panmai.b_id;
select b_id, title from book
where b_id in (select distinct b_id from panmai);
-- any : 비교함수중하나가참이면참
select b_id, title from book
where b_id = any (select distinct b_id from panmai);
-- some : 비교함수중일부가참이면참
select b_id, title from book
where b_id = some (select distinct b_id from panmai);

-- 판매되지 않은 책코드, 책이름
select b_id, title from book
where not b_id in (select b_id from panmai);
select book.b_id, title from book left outer join panmai
on book.b_id = panmai.b_id
where p_su is null;

-- exists : 하나라도 존재하면 참

select title from book where
exists (select * from panmai where p_su > 10 and substr(b_id, 1, 1) = 'a');

-- 아래와 동일

select title from book ;

-- 상관 하위 부질의
-- 상호 연관 서브 쿼리(correlated sub query)는 sub query가 main query의 값을 이용하고,
-- 그렇게 구해진 sub query의 값을 다시 main query가 다시 이용하게 된다.

-- 서점별로 1회 주문 수량이 가장 많은 책(서점명, 책이름, 수량)
-- rank() over() 함수로도 구할 수 있음.
select g1.g_id, g1.g_name, title, p_su
from book b1 join panmai p1 on b1.b_id=p1.b_id
join gogaek g1 on p1.g_id=g1.g_id
where p_su =
(select max(p_su) from panmai p2
where p2.g_id = p1.g_id)
order by g1.g_id;

-- 기본급을 기준으로 순위 부여
select name, basicpay,
(select count(i2.basicpay) + 1 from insa i2
where i2.basicpay > i1.basicpay) 순위
from insa i1;

9. 뷰 시퀀스

-- VIEW
뷰란 이미 특정한 데이터베이스 내에 존재하는 하나 이상의 테이블에서
사용자가 얻기 원하는 데이터들만을 정확하고 편하게 가져오기 위하여
사전에 원하는 컬럼들 만을 모아서 만들어 놓은 가상의 테이블로
편리성 및 보안에 목적이 있다.

CREATE [OR REPLACE] VIEW 뷰이름
AS subquery

-- 사용자 권한 확인
-- 사용자 계정
SQL>SELECT * FROM USER_SYS_PRIVS;

-- 사용자 권한 부여(RESOURCE롤의 권한 만으로는 뷰를 생성할 수 없다.)
-- SYS 계정
-- 서버에 있는 모든 시스템 권한 출력
CMD>sqlplus sys/암호 as sysdba
SQL>SELECT * FROM system_privilege_map;

-- 사용자에게 뷰를 만들 수 있는 권한 부여
SQL>GRANT CREATE VIEW TO 사용자;

-- 사용자 계정
-- 권한 확인
SQL>CONN 사용자/암호
SQL>SELECT * FROM USER_SYS_PRIVS;

-- 책코드, 책이름, 단가, 고객코드, 고객명, 판매날짜, 판매수량
SELECT b.b_id, title, price, g.g_id, g_name, p_date, p_su
FROM BOOK b
JOIN PANMAI p ON b.b_id=p.b_id
JOIN danga d ON b.b_id = d.b_id
JOIN gogaek g ON g.g_id = p.g_id;

-- 뷰 작성
CREATE VIEW viewPan
AS
SELECT b.b_id, title, price, g.g_id, g_name, p_date, p_su
FROM BOOK b
JOIN PANMAI p ON b.b_id=p.b_id
JOIN danga d ON b.b_id = d.b_id
JOIN gogaek g ON g.g_id = p.g_id;

SELECT * FROM viewPan;

-- 뷰 수정
CREATE OR REPLACE VIEW viewPan
AS
SELECT b.b_id, title, price, g.g_id, g_name, p_date, p_su
, p_su*price amt
FROM BOOK b
JOIN PANMAI p ON b.b_id=p.b_id
JOIN danga d ON b.b_id = d.b_id
JOIN gogaek g ON g.g_id = p.g_id;

SELECT * FROM viewPan;

CREATE OR REPLACE VIEW yearPan
AS
SELECT TO_CHAR(p_date, 'YYYY') 년도, g.g_id, g_name,
SUM(p_su*price) amt
FROM GOGAEK g JOIN PANMAI p ON g.g_id=p.g_id
JOIN DANGA d ON p.b_id=d.b_id
GROUP BY TO_CHAR(p_date, 'YYYY'),
g.g_id, g_name
ORDER BY 년도 DESC;

SELECT * FROM yearPan;

-- 뷰 정보 확인
DESC viewPAN;
SELECT view_name, text FROM user_views;

-- 뷰 삭제
DROP VIEW yearPan;
SELECT view_name, text FROM user_views;

-- 뷰를 이용한 자료 입력 및 수정, 삭제
CREATE TABLE TestA(
a_id NUMBER(4) PRIMARY KEY NOT NULL
,name VARCHAR2(20) NOT NULL
,city VARCHAR2(20) NOT NULL
,memo VARCHAR2(100)
);

CREATE TABLE TestB(
b_id NUMBER(4) PRIMARY KEY NOT NULL
,a_id NUMBER(4) CONSTRAINT FK_TestB_id
REFERENCES TestA(a_id)
ON DELETE CASCADE
,score NUMBER(3)
);

INSERT INTO TestA(a_id, name, city, memo)
VALUES (1, '홍길동', '서울', NULL);
INSERT INTO TestA(a_id, name, city, memo)
VALUES (2, '이순신', '부산', NULL);
INSERT INTO TestA(a_id, name, city, memo)
VALUES (3, '어려워', '서울', 'C/C++');
INSERT INTO TestA(a_id, name, city, memo)
VALUES (4, '이기자', '인천', NULL);

INSERT INTO TestB(b_id, a_id, score) VALUES (1, 1, 80);
INSERT INTO TestB(b_id, a_id, score) VALUES (2, 2, 70);
INSERT INTO TestB(b_id, a_id, score) VALUES (3, 3, 90);
INSERT INTO TestB(b_id, a_id, score) VALUES (4, 4, 60);

COMMIT;

SELECT * FROM TestA;
SELECT * FROM TestB;

-- testView1 뷰
-- a_id, b_id, name, memo, score
CREATE OR REPLACE VIEW testView1
AS
SELECT a_id, b_id, name, memo, score
FROM TestA a JOIN TestB b USING(a_id);

-- testView2 뷰
-- a_id, name, city
CREATE OR REPLACE VIEW testView2
AS
SELECT a_id, name, city FROM TestA;

-- 자료 입력
INSERT INTO testView1 (a_id, b_id, name, memo, score)
VALUES (5, 5, '입력될까', NULL, 90);
-- 오류 : 한 번에 두 개의 테이블에 입력할 수 없다.
-- (단순 뷰가 아닌 경우 추가 불가)

INSERT INTO testView2 (a_id, name) VALUES (5, '입력될까');
-- 오류 : TestA 테이블의 city는 NULL 될 수 없다.

INSERT INTO testView2 (a_id, name, city)
VALUES (5, '입력될까', '궁금');
-- TestA 테이블에 입력

SELECT * FROM TestA

UPDATE testView1 SET name='재밌다',
score=100 WHERE a_id = 1;
-- 오류 : 한 번에 두 개의 테이블을 수정할 수 없다.
-- (단순 뷰가 아닌 경우 수정 불가)

-- 단순 뷰는 수정 가능 하다.
UPDATE testView2 SET name='재밌다' WHERE a_id = 1;
SELECT * FROM TestA;

-- WITH CHECK OPTION
뷰를 통해 참조 무결성(reference integrity)을 검사할 수 있고 DB 레벨에서의 constraint 적용이 가능하다. 즉, WITH CHECK OPTION 절을 사용한 뷰에서 INSERT와 UPDATE를 수행하면 에러가 발생한다.

-- testView2 뷰 를 city 가 '서울'인 자료만 갖는 뷰로 수정
CREATE OR REPLACE VIEW testView2
AS
SELECT a_id, name, city FROM TestA WHERE city = '서울';

SELECT * FROM testView2;

-- testView2 뷰를 이용하여 a_id가 1인 자료의 city 를 '부산'으로 수정
UPDATE testView2 SET city='부산' WHERE a_id = 1; -- 수정 됨.

-- testView2 뷰 에 WITH CHECK OPTION을 부여하여 수정한다.
CREATE OR REPLACE VIEW testView2
AS
SELECT a_id, name, city FROM TestA WHERE city = '서울'
WITH CHECK OPTION CONSTRAINT ch_testView2;

-- testView2 뷰를 이용하여 a_id가 3인 자료의 city 를 '부산'으로 수정한다.
UPDATE testView2 SET city='부산' WHERE a_id = 3; -- 오류

- testView1, testView2 뷰 및 테이블 삭제
DROP VIEW testView1;
DROP VIEW testView2;
DROP TABLE testB PURGE;
DROP TABLE testA PURGE;

-- 머트리얼라이즈 뷰(MATERIALIZED VIEW)
/*
실제 데이터(통계정보)를 자신이 가지고 있으며, master 테이블이라고 불리는 기준이 되는 테이블에 INSERT, UPDATE, DELETE가 발생하면 새로운 데이터를 MATERIALIZED 뷰에 반영

- SUM, MIN, MAX, AVG, COUNT 등 그룹함수를 미리 계산해 놓을 때 사용
- USER_SEGMENTS 에서 확인 가능

-- 옵션
- BUILD IMMEDIATE : MView 생성과 동시에 데이터들도 생성
- BUILD DEFERRED : MView를 생성은 하지만, 그 안의 데이터는 추후에 생성
- REFRESH : MView의 데이터를 새로 고치는 시기와 방법을 결정

1) 시기

1) ON COMMIT - 기초 테이블에 Commit 이 일어날 때 Refresh 가 일어나는 방안이며,
이는 1 개의 테이블에 COUNT(*), SUM(*)과 같은 집합 함수를 사용하거나,
MView에 조인만이 있는 경우, Group By 절에 사용된 컬럼에 대해 COUNT(col) 함수가 기술된 경우만 사용이 가능

2) ON DEMAND - 사용자가 DBMS_MVIEW 패키지 (REFRESH, REFRESH_ALL_MVIEWS,
REFRESH_DEPENDENT) 를 실행 한 경우 Refresh 되는 경우

2) 방법

1) COMPLETE - MView의 정의에 따라 MView의 데이터 전체가 Refresh 되는 것으로
ATOMIC_REFRESH=TRUE와 COMPLETE으로 설정한 경우

2) FAST - 새로운 데이터가 삽입될 때마다 점진적으로 Refresh 되는 방안

3) FORCE - 이 경우 먼저 Fast Refresh가 가능한지 점검 후 가능하면 이를 적용하고,
아니면 Complete Refresh를 적용(디폴트)

4) NEVER - Refresh를 쓰지 않는다.

- ENABLE QUERY REWRITE : MView 생성시 이 옵션을 주어야만 임의의 SQL문장을 처리시
Query Rewrite를 고려한다. 이는 쿼리 재작성의 기능이다. 이전의 쿼리를 수정 하지 않고 재작성이 가능 한 기능
*/

-- 사용자에게 권한 설정
-- QUERY REWRITE : 쿼리 재작성 권한
-- SYS 사용자(sqlplus sys/암호 as sysdba)
GRANT QUERY REWRITE TO 사용자;
GRANT CREATE MATERIALIZED VIEW TO 사용자;

-- 사용자
SELECT * FROM USER_SYS_PRIVS;

SELECT * FROM USER_SEGMENTS;

CREATE MATERIALIZED VIEW panmview
BUILD IMMEDIATE
REFRESH
COMPLETE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT TO_CHAR(p_date, 'YYYY') 년도,
TO_CHAR(p_date, 'MM') 월,
SUM(p_su*price) 금액
FROM PANMAI JOIN DANGA d ON p.b_id=d.b_id
GROUP BY TO_CHAR(p_date, 'YYYY'),
TO_CHAR(p_date, 'MM') ;

SELECT * FROM TAB;
SELECT * FROM panmview;

INSERT INTO panmai (id, g_id, b_id, p_date, p_su)
VALUES (11, 6, 'b-1', '2010-05-05', 20);
COMMIT;

SELECT * FROM panmview;
-- 추가된 자료가 반영되지 않음(ON DEMAND 때문에)

-- 수동으로 재실행
EXECUTE dbms_mview.refresh('panmview');
SELECT * FROM panmview;

DROP MATERIALIZED VIEW panmview;

DELETE FROM PANMAI WHERE id=11;
COMMIT;

CREATE MATERIALIZED VIEW panmview
BUILD IMMEDIATE
REFRESH
COMPLETE
ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT TO_CHAR(p_date, 'YYYY') 년도,
TO_CHAR(p_date, 'MM') 월,
SUM(p_su*price) 금액
FROM PANMAI JOIN DANGA d ON p.b_id=d.b_id
GROUP BY TO_CHAR(p_date, 'YYYY'),
TO_CHAR(p_date, 'MM')
;

SELECT * FROM panmview;

INSERT INTO panmai (id, g_id, b_id, p_date, p_su)
VALUES (11, 6, 'b-1', '2010-05-05', 20);
COMMIT;

SELECT * FROM panmview;
-- 바로 반영(ON COMMIT)

DELETE FROM PANMAI WHERE id=11;
COMMIT;

DROP MATERIALIZED VIEW panmview;

-- SEQUENCE
특정 컬럼에 대해 유일한 값을 연속적으로 생성시키기 위해 사용하는 방법으로
오라클에서 제공하는 것이 sequence라는 객체이다.

-- 사용자에게 시퀀스를 만들 수 있는 권한이 없는 경우 권한 부여(RESOURCE 롤에
기본적으로 시퀀스를 만들수있는 권한 있음)
-- SYS 계정
CMD>sqlplus sys/암호 as sysdba
SQL>GRANT CREATE SEQUENCE TO 사용자;

-- 부여된 권한 확인
SQL>CONN 사용자/암호
SQL>SELECT * FROM USER_SYS_PRIVS;

-- 100부터 1씩 증가하는 시퀀스 작성
CREATE SEQUENCE seq1 INCREMENT BY 1
START WITH 100
MINVALUE 1
MAXVALUE 10000;

-- 3부터 999 까지 3씩 증가하는 시퀀스(미리 5개씩 캐시)
CREATE SEQUENCE seq2 INCREMENT BY 3
MINVALUE 3
MAXVALUE 999
CACHE 5;

-- 최대값에 도달하면 처음부터 다시 시작
CREATE SEQUENCE seq3 INCREMENT BY 3
START WITH 9
MINVALUE 3
MAXVALUE 12
CYCLE
CACHE 2;

-- 1부터 증가하는 가장 간단한 시퀀스
CREATE SEQUENCE seq4;

-- 시퀀스 목록 확인
SELECT * FROM seq;
SELECT * FROM user_sequences;

-- 시퀀스 활용
CREATE TABLE seqtest (
num1 NUMBER
,num2 NUMBER
,num3 NUMBER
,num4 NUMBER
);

INSERT INTO seqtest VALUES (seq1.NEXTVAL, SEQ2.NEXTVAL,
SEQ3.NEXTVAL, seq4.nextval);
SELECT * FROM seqtest;

DELETE FROM seqtest;

INSERT INTO seqtest VALUES (seq1.NEXTVAL, SEQ2.NEXTVAL,
SEQ3.NEXTVAL, seq4.nextval);
SELECT * FROM seqtest;

INSERT INTO seqtest VALUES (seq1.currVAL, SEQ2.currVAL,
SEQ3.currVAL, seq4.currval);
SELECT * FROM seqtest;

-- 시퀀스 삭제
DROP SEQUENCE 시퀀스명;

DROP SEQUENCE seq1;
DROP SEQUENCE seq2;
DROP SEQUENCE seq3;
DROP SEQUENCE seq4;

SELECT * FROM seq;
DROP TABLE seqtest PURGE;

10. 계층적 질의

-- 계층적 질의
오라클에서는 계층적인 데이터를 저장한 컬럼으로부터 데이터를 검색하여
계층적으로 출력할 수 있는 기능을 제공한다.
SELECT 문에서 START WITH와 CONNECT BY 절을 이용하여 데이터를 계층적인 형태로 출력할 수 있다.

-- START WITH : 절 계층적인 출력 형식을 표현하기 위한 최상위 행

-- CONNECT BY : 절 계층관계의 데이터를 지정하는 컬럼

-- PRIOR 연산자 : CONNECT BY는 PRIOR 연산자와 함께 사용하여 부모 행을 확인할 수 있다.
PRIOR 연산자의 위치에 따라 top-down 방식인지 bottom up 방식인지를 결정한다.
PRIOR 연산자가 붙은 쪽의 컬럼이 부모 행이 된다.

-- WHERE 절 : where 절이 JOIN을 포함하고 있을 경우 CONNECT BY 절을 처리하기 전에 JOIN 조건부를
적용하여 처리하고, JOIN을 포함하고 있지 않을 경우 CONNECT BY 절을 처리한 후에 WHERE 절의 조건을 처리한다.

-- LEVEL : 계층적 질의문에서 검색된 결과에 대해 계층별로 레벨 번호 표시, 루트 노드는 1,
하위 레벨로 갈수록 1씩 증가

-- 예제

CREATE TABLE exam(
num NUMBER PRIMARY KEY
,dname VARCHAR2(50) NOT NULL
,loc VARCHAR2(50)
,parent NUMBER
);

INSERT INTO exam VALUES(10, '공과대학', NULL, NULL);
INSERT INTO exam VALUES(100, '정보미디어학부', NULL, 10);
INSERT INTO exam VALUES(200, '메카트로닉스학부', NULL, 10);
INSERT INTO exam VALUES(101, '컴퓨터공학과', '1호관', 100);
INSERT INTO exam VALUES(102, '멀티미디어학과', '2호관', 100);
INSERT INTO exam VALUES(201, '전자공학과', '3호관', 200);
INSERT INTO exam VALUES(202, '기계공학과', '4호관', 200);

SELECT num, dname, loc, LEVEL, parent FROM exam
START WITH num=10 -- 출력 시작할 최상위행
CONNECT BY PRIOR num=parent; -- 계층관계지정
-- PRIOR 연산자가 붙은쪽이 부모

SELECT LPAD(' ', (LEVEL-1)*4) || dname AS 조직도 FROM exam
START WITH num=10
CONNECT BY PRIOR num=parent;

SELECT num, dname, loc, LEVEL, parent FROM exam
START WITH num=102
CONNECT BY PRIOR parent=num;

-- 정보미디어 학부만 출력 하지 않음
SELECT num, dname, loc, LEVEL, parent FROM exam
WHERE num != 100
START WITH num=10
CONNECT BY PRIOR num=parent;

-- 정보미디어학부 및 정보미디어학부의 과도 출력하지 않음
SELECT num, dname, loc, LEVEL, parent FROM exam
START WITH num=10
CONNECT BY PRIOR num=parent AND num != 100;

11. 트랜잭션

-- 트랜잭션
트랜잭션(transaction)은 하나의 논리적 작업 단위로 수행되는 일련의 작업으로 테이블에
INSERT, UPDATE, DELETE 문으로 지시하고, COMMIT을 실행하면 모든 변경 사항이 영구히 데이터베이스에
저장되며, ROLLBACK은 마지막 COMMIT이나 ROLLBACK 이후의 변경 사항을 취소하고 데이터베이스를
원래의 상태로 되돌려 준다. 즉, 트랜잭션에서 데이터베이스를 변경하라는 명령이 COMMIT이고,
COMMIT 전까지 변경된 것을 되돌리는 것이 ROLLBACK이다.

-- COMMIT
-- ROLLBACK
-- SAVEPOINT
-- transaction 내의 한 시점을 표시한다.
-- ROLLBACK TO SAVEPOINT 명령어로 표시 지점까지 ROLLBACK하는데 쓰인다.

-- 예제

CREATE TABLE test AS SELECT num, name, city FROM insa;

SELECT * FROM test;

INSERT INTO test VALUES(2000, 'test', '서울');
SELECT * FROM test;

SAVEPOINT a;

UPDATE test SET city='울산' WHERE num=1001;
SELECT * FROM test;

ROLLBACK TO a; -- UPDATE는 롤백되고 INSERT는 롤백되지 않음

SELECT * FROM test;

UPDATE test SET city='부산' WHERE num=1001;
SELECT * FROM test;

ROLLBACK;

SELECT * FROM test;
INSERT INTO test VALUES(2000, '테스트', '인천');
COMMIT;

SELECT * FROM test;
ROLLBACK; -- COMMIT가 된 상태에서는 롤백되지 않음

SELECT * FROM test;

DROP TABLE test PURGE;



-- SET TRANSACTION : 다양한 트랜잭션 속성을 지정
-- 커낵션 1
CREATE TABLE test (
id NUMBER(3) PRIMARY KEY,
value VARCHAR2(30),
score NUMBER(3)
);

INSERT INTO test VALUES (1, 'a', 80);
INSERT INTO test VALUES (2, 'b', 80);
SELECT * FROM test; -- 확인

-- 커낵션 2
SELECT * FROM test;
-- 추가된 결과를 확인 할 수 없다.

-- 커낵션 1
COMMIT;

-- 커낵션 2
SELECT * FROM test;

-- 커낵션 1
SET TRANSACTION READ ONLY; -- INSERT, UPDATE, DELETE가 되지 않고 SELECT만 가능
DELETE FROM test; -- 오류

ROLLBACK;
SET TRANSACTION READ WRITE;



-- SET AUTOCOMMIT
SQL>show autocommit -- 상태확인
autocommit OFF
SQL>set autocommit on
SQL>show autocommit
autocommit IMMEDIATE
SQL>set autocommit 3
SQL>show autocommit
모든 3 DML문에 AUTOCOMMIT ON
SQL>set autocommit off

-- LOCK : 현재의 트랜잭션이 사용하고 있는 데이터에 대해 다른 트랜잭션의 검색이나 변경을 막아 여러
트랜잭션이 동시에 같은 데이터를 사용 할 수 있도록 하는 것

-- 커낵션 1
SELECT * FROM test;
UPDATE test SET score = 200
WHERE id = 1;
SET TIME ON;

-- 커낵션 2
SELECT * FROM test
FOR UPDATE WAIT 5;
-- 5초 후 오류발생 메시지

-- 커낵션 1
ROLLBACK;

LOCK TABLE test IN EXCLUSIVE MODE;
-- EXCLUSIVE : 잠긴 테이블에 쿼리만 허용
-- NOWAIT : 다른 사용자가 이미 lock 하였더라도 바로 자신에게 제어권이 넘겨 오도록 함.

DELETE FROM test;

-- 커낵션 2
UPDATE test SET score = 100 WHERE id = 1;
-- 커넥션 1이 COMMIT 또는 ROLLBACK 할 때 까지 멈춰 있음.

-- 커낵션 1
ROLLBACK;

12. PL/SQL


-- PLSQL
PL/SQL(Procedural Language extensions to SQL)은 프로그래밍언어의 특성을 가지는 SQL의 확장이며,
데이터 조작과 질의 문장은 PL/SQL의 절차적 코드 안에 포함된다. 또한 PL/SQL을 사용하면
SQL로 할 수 없는 절차적 작업이 가능하다. 여기에서 절차적이란 어떤 것이 어떻게 완료되는지 그 방법을
정확하게 코드에 기술 한다는 것을 의미한다.

-- SQLGATE도 확인 가능

-- SQLPLUS에서 DBMS_OUTPUT.PUT_LINE('값'); 으로 화면에 출력
SET SERVEROUTPUT ON

-- DBMS_OUTPUT.PUT_LINE('값');
출력 후 라인 넘김

-- DBMS_OUTPUT.PUT('값');
출력 후 라인 넘기지 않음

-- DBMS_OUTPUT.NEW_LINE();
라인 넘김

-- 대입문 형식
변수 := 값;

-- %TYPE
-- 테이블 컬럼 자료형을 참조하는 자료형

DECLARE
vname insa.name%TYPE;
vpay NUMBER;
BEGIN
SELECT name, basicpay+sudang INTO vname, vpay
FROM INSA WHERE num=1001;
DBMS_OUTPUT.PUT_LINE('이름 : ' || vname);
DBMS_OUTPUT.PUT_LINE('급여 : ' || vpay);
END;
/ -- 실행

-- %ROWTYPE
-- 테이블 스키마와 같은 구조체변수

DECLARE
vdata insa%ROWTYPE;
BEGIN
SELECT name, basicpay INTO vdata.name, vdata.basicpay
FROM INSA WHERE num=1001;
DBMS_OUTPUT.PUT_LINE('이름 : ' || vdata.name);
DBMS_OUTPUT.PUT_LINE('급여 : ' || vdata.basicpay);
END;
/

-- 사용자 정의 구조체 변수

DECLARE
TYPE myData IS RECORD
(
vname insa.name%TYPE
,vpay NUMBER
);
vdata myData;
BEGIN
SELECT name, basicpay+sudang INTO
vdata.vname, vdata.vpay
FROM INSA WHERE num=1001;
DBMS_OUTPUT.PUT_LINE('이름 : ' || vdata.vname);
DBMS_OUTPUT.PUT_LINE('급여 : ' || vdata.vpay);
END;
/

-- IF 조건 THEN ~ ELSIF 조건 THEN ~ ELSE ~ END IF 문
TRUE면 THEN과 ELSE사이의 문장을 수행하고 FALSE나 NULL이면 ELSE와 END IF사이의 문장을 수행한다.

DECLARE
vpay NUMBER;
vname insa.name%TYPE;
vtax NUMBER;
BEGIN
SELECT name, basicpay+sudang INTO vname, vpay
FROM INSA WHERE num=1001;
IF vpay >= 2500000 THEN
vtax := vpay * 0.03;
ELSIF vpay >= 2000000 THEN
vtax := vpay * 0.02;
ELSE
vtax := 0;
END IF;
DBMS_OUTPUT.PUT_LINE('이름 : ' || vname);
DBMS_OUTPUT.PUT_LINE('급여 : ' || vpay);
DBMS_OUTPUT.PUT_LINE('세금 : ' || vtax);
END;
/

-- WHILE 조건 LOOP ~ END LOOP
제어 조건이 TRUE인 동안만 일련의 문장을 반복하기 위해 WHILE LOOP문장을 사용한다.

-- EXIT [WHEN condition];
EXIT 문을 이용하면 END LOOP 문 다음 문으로 제어를 보내기 때문에 루프를 종료할 수 있다.

-- LOOP ~ END LOOP
반복문으로 "EXIT WHEN 조건"이 실행문 앞과 뒤 어느 곳이나 위치할 수 있으며 "EXIT WHEN 조건"에서 조건을 만족하면 빠져 나간다.

DECLARE
n NUMBER := 0;
s NUMBER := 0;
BEGIN
WHILE n < 100 LOOP
n := n + 1;
s := s + n;
END LOOP;
DBMS_OUTPUT.PUT_LINE('결과 : ' || s);
END;
/

------------------------------------------------------------------
DECLARE
n NUMBER := 1;
s NUMBER := 0;
BEGIN
WHILE n <= 100 LOOP
s := s + n;
n := n + 2;
END LOOP;
DBMS_OUTPUT.PUT_LINE('결과 : ' || s);
END;

------------------------------------------------------------------
DECLARE
n NUMBER := 0;
s NUMBER := 0;
BEGIN
WHILE n < 100 LOOP
n := n + 1;
IF MOD(n, 2) = 1 THEN
s := s + n;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('결과 : ' || s);
END;
/

------------------------------------------------------------------
DECLARE
n NUMBER := 1;
m NUMBER := 0;
g NUMBER;
BEGIN
WHILE n < 9 LOOP
n := n + 1;
DBMS_OUTPUT.PUT_LINE(n || '단');
m := 0;
WHILE m < 9 LOOP
m := m + 1;
g := n * m;
DBMS_OUTPUT.PUT_LINE(n || '*' || m || '='|| g);
END LOOP;
END LOOP;
END;
/

------------------------------------------------------------------
DECLARE
n NUMBER := 0;
s NUMBER := 0;
BEGIN
LOOP
n := n + 1;
s := s + n;
EXIT WHEN n=100;
END LOOP;
DBMS_OUTPUT.PUT_LINE('결과 : ' || s);
END;
/

-- FOR ~ LOOP
FOR 에서 사용되는 변수는 자동 선언되므로 따로 선언하지 않아도 됨
"시작수"에서 1씩 증가하여 "끝날 수"가 될 때까지 반복 수행하며, FOR문에 사용되는 변수는 자동 선언되므로 따로 선언할 필요가 없다.

-- FOR문
FOR counter IN [REVERSE] 시작수 .. 끝낼수 LOOP
실행문;
END LOOP;

REVERSE : "끝날수"에서 "시작수"까지 반복함으로써 인덱스가 1씩 감소되도록 한다.

------------------------------------------------------------------
DECLARE
s NUMBER := 0;
BEGIN
FOR n IN 1 .. 100 LOOP
s := s + n;
END LOOP;
DBMS_OUTPUT.PUT_LINE('결과 : ' || s);
END;
/

------------------------------------------------------------------
DECLARE
BEGIN
FOR n IN REVERSE 10 .. 1 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n));
END LOOP;
END; -- 결과 안나옴
/

------------------------------------------------------------------
DECLARE
BEGIN
FOR n IN REVERSE 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n));
END LOOP;
END;


-- FOR 를 이용한 SELECT 문
FOR 레코드이름 IN SELECT문 LOOP
실행문;
END LOOP;

------------------------------------------------------------------
DECLARE
BEGIN
FOR rec IN (SELECT name, buseo, basicpay FROM insa) LOOP
DBMS_OUTPUT.PUT_LINE(rec.name || ' ' ||
rec.buseo || ' ' || rec.basicpay);
END LOOP;
END;

13-1. 프로시져

-- 프로시져(Stored Procedure)
PL/SQL에서 가장 대표적인 구조인 스토어드 프로시저는 개발자가 자주 실행해야 하는 업무 흐름을 미리 작성하여 데이터베이스 내에 저장해 두었다가 필요할 때마다 호출하여 실행할 수 있다.


-- 테이블의 프로시저, 함수, 패키지 등 상호 참조되는 관계 확인
SELECT * FROM user_dependencies;

-- 사용자에게 프로시져를 만들 수 있는 권한이 없는 경우 권한 설정
(RESOURCE 롤에 기본적으로 프로시져를 만들수있는 권한 있음)
-- SYS 계정
-- 사용자에게 프로시저를 만들 수 있는 권한이 없는 경우 부여
GRANT CREATE PROCEDURE TO 사용자명;

-- 사용자 계정
-- 부여된 권한 확인
SELECT * FROM USER_SYS_PRIVS;

-- 형식
CREATE [OR REPLACE] PROCEDURE 프로시저이름
[(
매개변수 IN 자료형, // 입력용, 인수에서는 자료 형에서는 크기를 지정하면 안 된다.
매개변수 OUT 자료형, // 출력용
매개변수 IN OUT 자료형 // 입/출력용
)]
IS
[변수의 선언]
BEGIN
.............
[EXCEPTION]
.............
END;



IN 파라미터 : 호출자에 의해 프로시저로 전달되는 파라미터이며,
'읽기' 전용의 값으로 프로시저는 이 파라미터의 값을 변경할 수 없다.(디폴트 모드)

OUT 파라미터 : 프로시저에서 값을 변경할 수 있고, '쓰기' 기능으로 프로시저가 정보를 호출자에게 돌려주는 기능이다.
OUT 파라미터는 디폴트값을 지정할 수 없다.

IN OUT 파라미터 : 프로시저가 읽고 쓰는 작업을 동시에 할 수 있는 파라미터이다.

-- PROCEDURE
SELECT * FROM TAB;
DROP TABLE EXAM PURGE;
DROP TABLE EXAM1 PURGE;

SELECT * FROM seq;

-- 시퀀스 작성
CREATE SEQUENCE seq1 START WITH 1;
SELECT * FROM seq;

-- 테이블 작성
CREATE TABLE EXAM1 (
id NUMBER PRIMARY KEY
,name VARCHAR2(20)
,score NUMBER
);

-- 테이블에 자료를 추가하는 프로시저 작성
CREATE OR REPLACE PROCEDURE proInsExam1
IS
BEGIN
INSERT INTO EXAM1(id, name, score) VALUES (SEQ1.NEXTVAL, '홍길동', 80);
COMMIT;
END;


-- 소스확인
SELECT TEXT FROM user_source;


-- 프로시저 목록 확인
SELECT object_name FROM user_procedures;


-- 프로시저 실행
EXECUTE proInsExam1;
SELECT * FROM EXAM1;

-- 프로시저 수정
-- IN
CREATE OR REPLACE PROCEDURE proInsExam1
(
pname IN exam1.name%TYPE
,pscore IN exam1.score%TYPE
)
IS
BEGIN
INSERT INTO EXAM1(id, name, score) VALUES (
SEQ1.NEXTVAL, pname, pscore);
COMMIT;
END;

EXECUTE proInsExam1('이순신', 100);
SELECT * FROM EXAM1;

------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE proViewExam1
(
pid IN NUMBER
)
IS
vname exam1.name%TYPE;
vscore exam1.score%TYPE;
BEGIN
SELECT name, SCORE INTO vname, vscore FROM EXAM1 WHERE id = pid;
DBMS_OUTPUT.PUT_LINE(vname || ' ' || vscore);
DBMS_OUTPUT.PUT_LINE('유저 : ' || USER);
END;
EXECUTE proViewExam1(1);

------------------------------------------------------------------
-- 1. exam1 테이블에 pan varchar2(10) 필드 추가
ALTER TABLE exam1 ADD (pan varchar2(10));

-- 2. 점수가 입력되면 pan 필드에 다음의 조건에 따라 값을 대입하도록
-- proInsExam1 프로시저 수정
-- 80점이상 우수, 60점이상 보통, 60미만 불합격
-- 실행 예
-- EXECUTE proInsExam1('이기자', 80);
-- SELECT * FROM exam1
-- 1 이기자 80 우수

CREATE OR REPLACE PROCEDURE proInsExam1
(
pname IN exam1.NAME%TYPE
,pscore IN exam1.SCORE%TYPE
)
IS
vpan VARCHAR2(10);
BEGIN
IF pscore >= 80 THEN
vpan := '우수';
ELSIF pscore >= 60 THEN
vpan := '보통';
ELSE
vpan := '불합격';
END IF;

INSERT INTO exam1 (id, name, score, pan) VALUES
(SEQ1.NEXTVAL, pname, pscore, vpan);
COMMIT;
END;

EXECUTE proInsExam1('이이이', 80);
SELECT * FROM EXAM1;

-- 3. 자료 수정 프로시저 작성
-- 실행예
-- EXECUTE proUpdateExam1('이기자', 60, 1);
-- SELECT * FROM exam1
-- 1 이기자 60 보통

CREATE OR REPLACE PROCEDURE proUpdateExam1
(
pname IN exam1.NAME%TYPE
,pscore IN exam1.SCORE%TYPE
,pid IN exam1.id%TYPE
)
IS
vpan VARCHAR2(10);
BEGIN
IF pscore >= 80 THEN
vpan := '우수';
ELSIF pscore >= 60 THEN
vpan := '보통';
ELSE
vpan := '불합격';
END IF;

UPDATE EXAM1 SET name=pname, score=pscore
, pan=vpan WHERE id=pid;
COMMIT;
END;

EXECUTE proUpdateExam1('이기자', 60, 1);
SELECT * FROM EXAM1;

-- 삭제 프로시저 작성
CREATE OR REPLACE PROCEDURE proDeleteExam1
(
pid IN exam1.id%TYPE
)
IS
BEGIN
DELETE FROM EXAM1 WHERE id=pid;
COMMIT;
END;

EXECUTE proDeleteExam1(1);
SELECT * FROM EXAM1;

-- 프로시저 실습

------------------------------------------------------------------
-- 테이블이 존재하면 테이블 삭제
DROP TABLE mem2 PURGE;
DROP TABLE mem1 PURGE;

------------------------------------------------------------------
CREATE TABLE mem1 (
id VARCHAR2(20) NOT NULL PRIMARY KEY
,pwd VARCHAR2(20) NOT NULL
,name VARCHAR2(20) NOT NULL
);

CREATE TABLE mem2 (
id VARCHAR2(20) NOT NULL PRIMARY KEY
,birth DATE
,tel VARCHAR2(20)
,CONSTRAINT fk_mem2_id FOREIGN KEY(id)
REFERENCES mem1(id)
);

------------------------------------------------------------------
SELECT constraint_name, table_name,
r_constraint_name, constraint_type
FROM user_constraints;

-- 출력용 View 작성
CREATE OR REPLACE VIEW memberView
AS
SELECT mem1.id, pwd, name, birth, tel
FROM mem1 LEFT OUTER JOIN mem2 ON mem1.id=mem2.id;

SELECT * FROM memberView;

------------------------------------------------------------------
-- 추가 프로시져
CREATE OR REPLACE PROCEDURE proInsMember
(
pid IN mem1.id%TYPE
,ppwd IN mem1.pwd%TYPE
,pname IN mem1.name%TYPE
,pbirth IN mem2.birth%TYPE
,ptel IN mem2.tel%TYPE
)
IS
BEGIN
INSERT INTO mem1 (id, pwd, name) VALUES (pid, ppwd, pname);

IF pbirth IS NOT NULL OR ptel IS NOT NULL THEN
INSERT INTO mem2 (id, birth, tel) VALUES (pid, pbirth, ptel);
END IF;
COMMIT;
END;

------------------------------------------------------------------
-- 테스트
EXECUTE proInsMember('1111', '1111', '홍길동', NULL, NULL);
EXECUTE proInsMember('2222', '2222', '홍길동', '', '');
EXECUTE proInsMember('3333', '3333', '이순신', NULL, '');
EXECUTE proInsMember('4444', '4444', '이순신', '2000-10-10', '');
EXECUTE proInsMember('1111', '4444', '이순신', '2000-10-10', '111-111');
-- 오류
EXECUTE proInsMember('5555', '6666', '허허허', '2000-10-10', '111-111');
EXECUTE proInsMember('6666', '6666', '후후후후', NULL, '111-111');

SELECT * FROM mem1;
SELECT * FROM mem2;

EXECUTE proInsMember('4444', '4444', '이순신', '2000-10-10', '');

------------------------------------------------------------------
-- 수정 프로시져 - 1
CREATE OR REPLACE PROCEDURE proUpMember
(
pid IN mem1.id%TYPE
,ppwd IN mem1.pwd%TYPE
,pname IN mem1.name%TYPE
,pbirth IN mem2.birth%TYPE
,ptel IN mem2.tel%TYPE
)
IS
vCount NUMBER;
BEGIN
SELECT COUNT(*) INTO vCount FROM mem2 WHERE id=pid;

UPDATE mem1 SET pwd=ppwd, name=pname WHERE id=pid;

IF pbirth IS NOT NULL OR ptel IS NOT NULL THEN
IF vCount=1 THEN
UPDATE mem2 SET birth=pbirth, tel=ptel WHERE id=pid;
ELSE
INSERT INTO mem2 (id, birth, tel) VALUES (pid, pbirth, ptel);
END IF;
ELSE
DELETE FROM mem2 WHERE id=pid;
END IF;

COMMIT;
END;

------------------------------------------------------------------
-- 수정 테스트
EXECUTE proUpMember('5555', '4444', '이순신', '', '');
EXECUTE proUpMember('3333', '7777', '이이이', '2010-10-10', '');

------------------------------------------------------------------
-- 수정 프로시져 - 2
CREATE OR REPLACE PROCEDURE proUpMember
(
pid IN mem1.id%TYPE
,ppwd IN mem1.pwd%TYPE
,pname IN mem1.name%TYPE
,pbirth IN mem2.birth%TYPE
,ptel IN mem2.tel%TYPE
)
IS
vid mem1.id%TYPE;

CURSOR mem_list IS SELECT id FROM mem2 WHERE id = pid;
BEGIN
UPDATE mem1 SET pwd=ppwd, name=pname WHERE id=pid;

OPEN mem_list;
FETCH mem_list INTO vid;

IF pbirth IS NOT NULL OR ptel IS NOT NULL THEN
IF mem_list%FOUND THEN
UPDATE mem2 SET birth=pbirth, tel=ptel WHERE id=pid;
ELSE
INSERT INTO mem2 (id, birth, tel) VALUES (pid, pbirth, ptel);
END IF;
ELSE
DELETE FROM mem2 WHERE id=pid;
END IF;

COMMIT;
END;

------------------------------------------------------------------
-- 삭제 프로시져
CREATE OR REPLACE PROCEDURE proDelMember
(
pid IN mem1.id%TYPE
)
IS
BEGIN
DELETE FROM mem2 WHERE id=pid;
DELETE FROM mem1 WHERE id=pid;
COMMIT;
END;

EXECUTE proDelMember('3333');

13-2. 프로시져 문제

-- 테이블 작성
-- 엔티티명 : mem1, 엔티티성격 : 테이블
-- 컬럼
userId 문자(10) NOT NULL
userPwd 문자(10) NOT NULL
userName 문자(20) NOT NULL
userId는 기본키

CREATE TABLE mem1(
userId VARCHAR2(10) NOT NULL
,userPwd VARCHAR2(10) NOT NULL
,userName VARCHAR2(20) NOT NULL
,CONSTRAINT pk_mem1_userId PRIMARY KEY (userId)
);

-- 엔티티명 : mem2, 엔티티성격 : 테이블
userId 문자(10) NOT NULL
userSsn 문자(14)
userTel 문자(20)
userBirth 날짜
userId 는 기본키이며 mem1 테이블의 userId의 참조키
userSsn는 유니크

CREATE TABLE mem2(
userId VARCHAR2(7) NOT NULL
,userSsn VARCHAR2(14)
,userTel VARCHAR2(20)
,userBirth DATE
,CONSTRAINT pk_mem2_userId PRIMARY KEY (userId)
,CONSTRAINT uk_mem2_userSsn UNIQUE(userSsn)
,CONSTRAINT fk_mem2_userId FOREIGN KEY(userId)
REFERENCES mem1(userId)
);

-- 두 테이블에 추가하고 COMMIT 하고 SELECT 해볼것
INSERT INTO mem1(userId, userPwd, userName) VALUES('11', '11', '자바');
INSERT INTO mem1(userId, userPwd, userName) VALUES('22', '22', '오라클');

INSERT INTO mem2(userId, userSSn, userTel, userBirth) VALUES('22', '', '010-111-1111', '2010-11-10');
COMMIT;

SELECT * FROM mem1;
SELECT * FROM mem2;

-- mem1과 mem2를 조인(OUTER JOIN)하여 모든 컬럼을 출력하는 뷰를 작성한다.(memView)
CREATE OR REPLACE VIEW memView
AS
SELECT mem1.userId, userName, userPwd, userSsn,
userBirth, userTel FROM mem1
LEFT OUTER JOIN mem2 ON mem1.userId=mem2.userId;

SELECT * FROM memView;

-- mem1과 mem2에 데이터를 추가하는 프로시져를 작성한다.
(프로시져명 : memInsert)
단, userSsn, userTel, userBirth 모두가 NULL인 경우에는 mem2 테이블에는 자료를 추가하지 않도록 한다.
IF pUserSSn IS NOT NULL OR ..... THEN
END IF;

이런식으로 하면 된다.

CREATE OR REPLACE PROCEDURE memInsert
(
pUserId IN mem1.userId%TYPE
,pUserPwd IN mem1.userPwd%TYPE
,pUserName IN mem1.userName%TYPE
,pUserSsn IN mem2.userSsn%TYPE
,pUserTel IN mem2.userTel%TYPE
,pUserBirth IN mem2.userBirth%TYPE
)
IS
BEGIN
INSERT INTO mem1 (userId, userPwd, userName) VALUES (pUserId, pUserPwd, pUserName);

IF pUserSsn IS NOT NULL OR pUserBirth IS NOT NULL OR pUserTel IS NOT NULL THEN
INSERT INTO mem2 (userId, userSsn, userBirth, userTel) VALUES
(pUserId, pUserSsn, pUserBirth, pUserTel);
END IF;
COMMIT;
END;

EXEC memInsert('111','111','김병태',null,null,'200-10-10');
EXEC memInsert('222','222','공현일','','','');
EXEC memInsert('333','333','최연지','111-111','010','2010-10-10');

SELECT * FROM memView;

-- mem1과 mem2에 데이터를 삭제하는 프로시져를 작성한다.
(프로시져명 : memDelete)

CREATE OR REPLACE PROCEDURE memDelete
(
pUserId IN mem1.userId%TYPE
)
IS
BEGIN
DELETE FROM mem2 WHERE userId=pUserId;
DELETE FROM mem1 WHERE userId=pUserId;
COMMIT;
END;

EXEC memDelete('22');

SELECT * FROM memView;

-- mem1과 mem2에 데이터를 수정하는 프로시져를 작성한다.
(프로시져명 : memUpdate)
단, userSsn, userTel, userBirth 모두가 NULL이면서 mem2테이블에 자료가 존재하면 mem2테이블의 자료를 삭제하고 userSsn, userTel, userBirth 중 하나라도 NULL이 아니면서 mem2테이블에 자료가 존재하면 수정하고 존재하지 않으면 추가한다.

CREATE OR REPLACE PROCEDURE memUpdate
(
pUserId IN mem1.userId%TYPE
,pUserPwd IN mem1.userPwd%TYPE
,pUserName IN mem1.userName%TYPE
,pUserSsn IN mem2.userSsn%TYPE
,pUserTel IN mem2.userTel%TYPE
,pUserBirth IN mem2.userBirth%TYPE
)
IS
vCount NUMBER;

BEGIN
SELECT COUNT(*) INTO vCount FROM mem2 WHERE userId = pUserId;

UPDATE mem1 SET userPwd=pUserPwd, userName=puserName WHERE userId=pUserId;

IF pUserSsn IS NOT NULL OR pUserBirth IS NOT NULL OR pUserTel IS NOT NULL THEN
IF vCount!=0 THEN
UPDATE mem2 SET userSsn=pUserSsn, userBirth=pUserBirth, userTel=pUserTel WHERE userId=pUserId;
ELSE
INSERT INTO mem2 (userId, userSsn, userBirth, userTel) VALUES (pUserId, pUserSsn, pUserBirth, pUserTel);
END IF;
ELSE
DELETE FROM mem2 WHERE userId=pUserId;
END IF;

COMMIT;
END;

EXEC memUpdate('11','111','프로시져',null,null,'200-10-10');

SELECT * FROM memView;

14. 사용자 정의 함수

-- Stored Function(사용자 함수)
스토어드 함수는 시스템 함수처럼 쿼리에서 호출하거나 저장 프로시저처럼 EXECUTE 문을 통해 실행할 수 있다.

-- 형식(인수나 RETURN 에서는 자료형의 크기를 명시하지 않는다.)
CREATE [OR REPLACE] FUNCTION 함수이름
[(
매개변수1 IN 자료형,
매개변수2 IN 자료형;
)]
RETURN datatype;
IS
[변수의 선언]
BEGIN
.............
RETURN (값);
[EXCEPTION]
.............
END;

-- 함수 목록 확인
SELECT object_name FROM user_procedures;

-- 합을 구하는 함수 만들기
CREATE OR REPLACE FUNCTION fnSum
(
n IN NUMBER
)
RETURN NUMBER -- 인수나 RETURN 에서는 자료형의 크기를 명시하지 않는다.
IS
s NUMBER := 0;
BEGIN
FOR i IN 1..n LOOP
s := s + i;
END LOOP;
RETURN (s);
END;

SELECT fnSUM(100) FROM DUAL;

-----------------------------------------------------
-- fnPow(10, 2) : 10의 2승
CREATE OR REPLACE FUNCTION fnPow
(
n1 IN NUMBER
,n2 IN NUMBER
)
RETURN NUMBER
IS
s NUMBER := 1;
BEGIN
IF n2 > 0 THEN
FOR i IN 1..n2 LOOP
s := s * n1;
END LOOP;
ELSE
FOR i IN 1..(-n2) LOOP
s := s / n1;
END LOOP;
END IF;
RETURN s;
END;

SELECT fnPow(2, 10), fnPow(2, -2) FROM DUAL;

-- 주민번호가지고 성별 구하기 또는 생일, 나이등 계산

-- 성별
CREATE OR REPLACE FUNCTION fnSex
(
ssn IN VARCHAR2
)
RETURN VARCHAR2
IS
s VARCHAR2(4) := '여자';
BEGIN
IF MOD(SUBSTR(ssn, 8, 1), 2)=1 THEN
s := '남자';
END IF;
RETURN s;
END;

-- 생일
CREATE OR REPLACE FUNCTION fnBirth
(
ssn IN VARCHAR2
)
RETURN DATE
IS
s VARCHAR2(8);
BEGIN
s := '19' || SUBSTR(ssn, 1, 6);
IF SUBSTR(ssn, 8, 1) IN ('3', '4') THEN
s := '20' || SUBSTR(ssn, 1, 6);
END IF;
RETURN TO_DATE(s, 'YYYYMMDD');
END;

-- 나이
CREATE OR REPLACE FUNCTION fnAge
(
ssn IN VARCHAR2
)
RETURN NUMBER
IS
s VARCHAR2(8);
age NUMBER;
BEGIN
s := '19' || SUBSTR(ssn, 1, 6);
IF SUBSTR(ssn, 8, 1) IN ('3', '4') THEN
s := '20' || SUBSTR(ssn, 1, 6);
END IF;
age := TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE(s, 'YYYYMMDD')) /12);
RETURN age;
END;

SELECT name, fnSex(ssn), fnBirth(ssn), fnAge(ssn) FROM INSA;

15. 커서와 동적 SQL

-- 커서(Cursor)
-- 하나의 레코드가 아닌 여러 레코드로 구성된 작업영역에서 SQL문을 실행하고 그 과정에 생긴 정보를 저장하기 위해서 CURSOR를 사용

1. 암시적인 커서
암시적인 커서는 오라클이나 PL/SQL실행 메커니즘에 의해 처리되는 SQL문장이 처리되는 곳에 대한 익명의 address로 오라클 데이터베이스에서 실행되는 모든 SQL문장은 암시적인 커서이며, 암시적인 커서 속성이 사용될 수 있다.

-- 암시적 커서의 속성
SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수
SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE
SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE
SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색

2. 명시적(explicit) 커서
명시적 커서는 프로그래머에 의해 선언되며 이름이 있는 커서로 여러 row를 다룰 수 있다.

-- 작업 순서
CURSOR 선언 → 커서 OPEN → FETCH → 커서 CLOSE

-- 커서 선언
CURSOR 커서이름 IS [SELECT 문];

실행하고자 하는 SELECT문을 작성 한다.

-- 커서 OPEN
OPEN 커서이름;

OPEN은 커서에서 선언된 SELECT문의 실행을 의미 한다.

-- FETCH
LOOP
FETCH 커서이름 INTO variable1, variable2 ;
EXIT WHEN [조건];
END LOOP;

OPEN된 SELECT 문에 의해 검색된 하나의 행 정보를 읽어 OUT 변수에 대입한다. 만약 리턴 되는
결과가 여러 개인 경우 LOOP ~ END LOOP와 같은 반복문을 이용하여 마지막 행이 읽혀질 때까지 계속 읽게 된다.

-- 커서 CLOSE
CLOSE 커서이름;

선언된 SELECT문의 선언을 해제 한다.

3. FOR문에서 커서 사용(Cursor FOR Loops)
FOR문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생하므로 따로 기술할 필요가 없고 레코드
이름도 자동 선언되므로 따로 선언할 필요가 없다.

-- 형식
FOR 레코드이름 IN 커서이름 LOOP
문장;
......
END LOOP;

4. WHERE CURRENT OF
FETCH문에 의해 가장 최근에 처리된 행을 참조하기 위해서 "WHERE CURRENT OF 커서이름 " 절로 DELETE나 UPDATE문 작성이 가능하다. 이 절을 사용하기 위해서는 참조하는 커서가 있어야 하며, FOR UPDATE절이 커서 선언 query문장 안에 있어야 한다.

-----------------------------------------------------
DECLARE
vName insa.name%TYPE;
vBasicPay insa.basicpay%TYPE;

CURSOR insa_list IS
SELECT name, basicpay FROM insa;

BEGIN
OPEN insa_list;
LOOP
FETCH insa_list INTO vName, vBasicpay;
-- EXIT WHEN insa_list%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vName || ' ' || vbasicpay);
EXIT WHEN vName = '이기자';
END LOOP;

CLOSE insa_list;
END;
/

-----------------------------------------------------
CREATE OR REPLACE PROCEDURE curTest
IS
vName insa.name%TYPE;
vBasicPay insa.basicpay%TYPE;
CURSOR insa_list IS
SELECT name, basicpay FROM insa;
BEGIN
OPEN insa_list;
LOOP
FETCH insa_list INTO vName, vBasicpay;
EXIT WHEN insa_list%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vName || ' ' || vbasicpay);
END LOOP;
CLOSE insa_list;
END;
/
EXECUTE curTest

-----------------------------------------------------
-- 조건 만족 데이터 가져오기
CREATE OR REPLACE PROCEDURE curTest
(
pcity insa.city%TYPE
)
IS
vName insa.name%TYPE;
vBasicPay insa.basicpay%TYPE;
vCity insa.city%TYPE;
CURSOR insa_list IS
SELECT name, basicpay,city FROM insa WHERE city=pcity;
BEGIN
OPEN insa_list;
LOOP
FETCH insa_list INTO vName, vBasicpay, vCity;
EXIT WHEN insa_list%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vName || ' ' || vbasicpay || ' ' vcity);
END LOOP;
CLOSE insa_list;
END;
/
EXECUTE curTest('서울');

-----------------------------------------------------
-- 커서에서 FOR : 자동 OPEN, 자동 CLOSE
CREATE OR REPLACE PROCEDURE curTest
IS
CURSOR insa_list IS
SELECT * FROM insa;
BEGIN
FOR rec IN insa_list LOOP
DBMS_OUTPUT.PUT_LINE(rec.Name || ' ' || rec.basicpay);
END LOOP;
END;
EXECUTE curTest

-----------------------------------------------------
-- 파라미터가 있는 커서
CREATE OR REPLACE PROCEDURE CURTEST
(
pcity INSA.CITY%TYPE
)
IS
vName insa.name%TYPE;
vBasicPay insa.basicpay%TYPE;

CURSOR insa_list(ppcity INSA.CITY%TYPE) IS
SELECT name, basicpay FROM INSA WHERE city=ppcity;

BEGIN
OPEN insa_list(pcity);
LOOP
FETCH insa_list INTO vName, vBasicpay;
EXIT WHEN insa_list%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vName || ' ' || vbasicpay);
END LOOP;
CLOSE insa_list;
END;

EXECUTE CURTEST('서울');

-- SYS_REFCURSOR
-- REF CURSOR : 9i 이전
-- SYS_REFCURSOR : 9i 이후

-- 테이블의 여러 로우를 반복적으로 조회하기 위해 레퍼런스 커서(reference cursor)를 사용한다.

-----------------------------------------------------
-- 테이블 작성
CREATE TABLE score(
num NUMBER(7) PRIMARY KEY NOT NULL
,name VARCHAR2(20)
,birth DATE
,kor NUMBER(3)
,eng NUMBER(3)
,mat NUMBER(3)
);
INSERT INTO score (num, name, birth, kor, eng, mat)
VALUES (1, '홍길동', '2000-10-10', 80, 80, 80);
INSERT INTO score (num, name, birth, kor, eng, mat)
VALUES (2, '이기자', '2000-11-11', 90, 50, 50);
COMMIT;

-----------------------------------------------------
-- 프로시져 - 1
CREATE OR REPLACE PROCEDURE select_score
(
pRc IN sys_refcursor
)
IS
varNum SCORE.num%TYPE;
varName SCORE.name%TYPE;
varBirth SCORE.birth%TYPE;
varKor SCORE.kor%TYPE;
varEng SCORE.eng%TYPE;
varMat SCORE.mat%TYPE;
BEGIN
LOOP
FETCH pRc INTO varNum, varName, varBirth,
varKor, varEng, varMat;
EXIT WHEN pRc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(varNum || ' ' ||
varName || ' ' || (varKor+varEng+varMat));
END LOOP;
END;

-----------------------------------------------------
-- 프로시져 - 2
CREATE OR REPLACE PROCEDURE list_score
IS
pResult SYS_REFCURSOR;
BEGIN
OPEN pResult FOR SELECT num, name,
birth, kor, eng, mat FROM score;
select_score(pResult);
CLOSE pResult;
END;

-- 실행
EXEC list_score;

-- 동적 SQL
응용 프로그램 개발 시 자주 실행되는 SQL문이 존재할 때 정적 SQL문을 이용하여 구현하는 경우 여러 번 반복 작성 실행해야 한다.

-- 주의
RESOURCE 롤은 테이블을 생성할 수 있지만 동적 SQL을 이용하여 테이블을 생성할수 는 없다. 동적 SQL로 테이블을 생성하기 위해서는 CREATE TABLE 권한을 설정 해 주어야 한다.

-- SYS 계정
cmd>sqlplus sys/"암호" AS sysdba;
sql>GRANT CREATE TABLE TO 사용자;

------------------------------------------------------------
EXECUTE IMMEDIATE dynamic_sql_string
[INTO {define_var1 [, define_var2] ... | plsql_record }]
[USING [IN | OUT | IN OUT] bind_arg1 [,
[IN | OUT | IN OUT] bind_arg2] ...];

------------------------------------------------------------
-- 동적으로 테이블 생성
CREATE OR REPLACE PROCEDURE dynTest1
(
pTname VARCHAR2
)
IS
sql_stmt VARCHAR2(4000);
BEGIN
sql_stmt:='CREATE TABLE ' || pTname;
sql_stmt:=sql_stmt || '(num NUMBER PRIMARY KEY,';
sql_stmt:=sql_stmt || ' name VARCHAR2(20) )';

-- 동적으로 쿼리 실행
FOR temp IN (SELECT TNAME FROM TAB WHERE TNAME = UPPER(pTname)) LOOP
EXECUTE IMMEDIATE 'DROP TABLE '||pTname||' PURGE';
DBMS_OUTPUT.PUT_LINE('테이블 삭제........');
EXIT;
END LOOP;
EXECUTE IMMEDIATE sql_stmt;
DBMS_OUTPUT.PUT_LINE('테이블 생성........');
END;

EXEC dynTest1('xyz');

------------------------------------------------------------
-- 두번째 예제
CREATE OR REPLACE PROCEDURE dynTest2
IS
sql_stmt VARCHAR2(200);
v_id NUMBER;
a_id NUMBER;
v_memo VARCHAR2(20);

TYPE MyType IS RECORD
(
t_id NUMBER,
t_memo VARCHAR2(20)
);
rec MyType;

BEGIN
-- 테이블이 존재하면 삭제
FOR temp IN (SELECT TNAME FROM TAB WHERE TNAME = 'TEST') LOOP
EXECUTE IMMEDIATE 'DROP TABLE TEST';
EXIT;
END LOOP;

------------------------------------------------------------
-- 테이블 작성
EXECUTE IMMEDIATE 'CREATE TABLE Test (t_id NUMBER, t_memo VARCHAR2(20))';

------------------------------------------------------------
-- 자료 입력
sql_stmt := 'INSERT INTO test VALUES(:1, :2)';
FOR n IN 1..5 LOOP
v_id := n;
v_memo := CHR(n+96);

EXECUTE IMMEDIATE sql_stmt USING v_id, v_memo;
END LOOP;
COMMIT;

------------------------------------------------------------
-- 자료 출력
sql_stmt := 'SELECT * FROM Test WHERE t_id = :id';
a_id := 1;
EXECUTE IMMEDIATE sql_stmt INTO rec USING a_id;

DBMS_OUTPUT.PUT_LINE(rec.t_id || ' ' || rec.t_memo);

------------------------------------------------------------
-- 자료 수정(변경 값을 a_id에 가져옴)
v_id := 1;
sql_stmt := 'UPDATE Test SET t_id = 100 WHERE t_id = :1 RETURNING t_id INTO :2';
EXECUTE IMMEDIATE sql_stmt USING v_id RETURNING INTO a_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE(a_id || ' 으로 ID 변경');

------------------------------------------------------------
-- 자료 삭제
v_id := 3;
sql_stmt := 'DELETE FROM Test WHERE t_id = :id';
EXECUTE IMMEDIATE sql_stmt USING v_id;
COMMIT;
END;

EXEC dynTest2;

16-1. 트리거

트리거(TRIGGER)
TRIGGER란 DML 작업 즉, INSERT, DELETE, UPDATE 작업이 일어날 때 자동으로 실행되는 객체로 특히 이런 TRIGGER를 DML TRIGGER라 한다. TRIGGER는 데이터의 무결성 뿐만 아니라 다음과 같은 작업에도 사용된다.
트리거 내에서는 COMMIT, ROLLBACK 문을 사용할 수 없다.

-- 문장 트리거(문장당 한번만 실행)
트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 오직 한번만 트리거를 발생시키는 방법

-- 행 트리거
조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법으로 [FOR EACH ROW WHEN 조건]절 정의된다.

-- 형식
CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE | AFTER]
이벤트-1 [OR 이벤트-2 OR 이벤트-3] ON 테이블명
[FOR EACH ROW [WHEN TRIGGER 조건]]
DECLARE
선언문
BEGIN
PL/SQL 코드
END;

-- 이벤트
INSERT, UPDATE, DELETE

-- BEFORE : 구문을 실행하기 전에 트리거를 시작
-- AFTER : 구문을 실행한 후에 트리거를 시작
-- FOR EACH ROW : 행 트리거임을 알림
-- WHEN 조건 : 사용자의 트리거 이벤트 중에 조건에 만족하는 데이터만 트리거 한다.
-- REFERENCING : 영향 받는 행의 값을 참조
-- :OLD : 참조 전 열의 값(INSERT : 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 자료)
-- :NEW : 참조 후 열의 값(INSERT : 입력할 자료, UPDATE : 수정할 자료)

ROW 트리거에서 컬럼의 실제 데이터 값을 제어하는데 사용하는 연산자는 :OLD와 :NEW 의사 레코드이다. 이 연산자와 함께 컬럼 명을 함께 기술한다. 예를 들어, 컬럼명이 sal이라고 하면, 변경전의 값은 :OLD.sal이고 변경 후의 값은 :NEW.sal 처럼 표기한다.

문장 트리거에서는 :NEW, :OLD 를 참조 할 수 없다.

-- 트리거 상태 확인
SQL>SELECT trigger_name, trigger_type, table_name FROM user_triggers;

-- 트리거 삭제
DROP TRIGGER 트리거명;


-- 권한설정
-- SYS 계정
-- 사용자에게 트리거를 만들 수 있는 권한 부여
GRANT CREATE TRIGGER TO 사용자명;

-- 사용자 계정
-- 부여된 권한 확인
SELECT * FROM USER_SYS_PRIVS;

-- 문장 트리거 예제
SELECT * FROM tab;

-- EXAM1 테이블이 존재하면 삭제
DROP TABLE EXAM1 PURGE;

-- 문장 트리거
CREATE TABLE EXAM1 (
id NUMBER PRIMARY KEY
,name VARCHAR2(20)
);

CREATE TABLE EXAM2 (
memo VARCHAR2(100)
,ilja DATE DEFAULT SYSDATE
);

------------------------------------------------------------
-- exam1 테이블에 추가, 수정, 삭제가되면 시간과 상태 저장 트리거
-- 문장, AFTER 트리거
CREATE OR REPLACE TRIGGER trgExam
AFTER DELETE OR INSERT OR UPDATE ON EXAM1
BEGIN
IF INSERTING THEN
INSERT INTO EXAM2(memo) VALUES ('추가');
ELSIF UPDATING THEN
INSERT INTO EXAM2(memo) VALUES ('수정');
ELSIF DELETING THEN
INSERT INTO EXAM2(memo) VALUES ('삭제');
END IF;
END;

------------------------------------------------------------
-- 트리거 확인
SELECT trigger_name, trigger_type, table_name
FROM user_triggers;
SELECT TEXT FROM user_source;

INSERT INTO EXAM1(id, name) VALUES(1, 'aaa');
COMMIT;
SELECT * FROM EXAM1;
SELECT * FROM exam2;

-- 테이블의 프로시저, 함수, 패키지 등 상호 참조되는 관계 확인
SELECT * FROM user_dependencies;

-- 날짜 표현 형식 변경
ALTER SESSION SET NLS_DATE_LANGUAGE='KOREAN';

-- BEFORE 트리거
CREATE OR REPLACE TRIGGER trgExam1
BEFORE DELETE OR INSERT OR UPDATE ON EXAM1
BEGIN
IF TO_CHAR(SYSDATE, 'DAY') IN ('토요일', '일요일')
OR (TO_CHAR(SYSDATE, 'hh24') >= 12 AND
TO_CHAR(SYSDATE, 'hh24') <= 13) THEN
raise_application_error(-20007, '지금은 일못해');
END IF;
END;

-- raise_application_error
-- 표준화되지 않은 에러 코드와 에러 메시지를 리턴하기 위해 raise_application_error 프로시저 사용
-- 코드값은 -20000~-20999사이의 수 사용

INSERT INTO EXAM1(id, name) VALUES(5, 'bbb');
SELECT * FROM EXAM1;
SELECT * FROM exam2;

-- 위 트리거 수정
CREATE OR REPLACE TRIGGER trgExam1
BEFORE DELETE OR INSERT OR UPDATE ON EXAM1
BEGIN
IF TO_CHAR(SYSDATE, 'DAY') IN ('토요일', '일요일')
OR TO_CHAR(SYSDATE, 'hh24') < 9 OR
TO_CHAR(SYSDATE, 'hh24') > 18 THEN
raise_application_error(-20007, '지금은 일못해');
END IF;
END;

-- 테이블을 삭제하면 트리거도 자동적으로 지워진다.

-- 행트리거
-- :NEW -> 참조후 열의 값
-- INSERT에 의해 새로 추가된(할) 레코드
-- UPDATE에 의해 수정된(할) 레코드
-- :OLD -> 참조전 열의 값
-- UPDATE에 의해 수정되기 전 레코드
-- DELETE에 의해 삭제된(할) 레코드

------------------------------------------------------------
CREATE TABLE demo1(
id NUMBER,
data VARCHAR2(20)
);

CREATE OR REPLACE TRIGGER demo_trigger
BEFORE INSERT ON demo1 FOR EACH ROW
BEGIN
SELECT TO_CHAR(SYSDATE, 'YYYY') INTO :new.id FROM dual;
END;
/
-- after 트리거는 :NEW를 수정 할 수 없다.

INSERT INTO demo1(id, data) VALUES(5, '우리');
COMMIT;

------------------------------------------------------------
CREATE TABLE exama(
hak VARCHAR2(20) PRIMARY KEY
,name VARCHAR2(20)
,kor NUMBER
,eng NUMBER
,mat NUMBER
);

CREATE TABLE examb(
hak VARCHAR2(20) PRIMARY KEY
,tot NUMBER
,ave NUMBER(5, 1)
,CONSTRAINT fk_examb_hak FOREIGN KEY(hak)
REFERENCES exama(hak)
);

-- 행 트리거(AFTER) : 추가
CREATE OR REPLACE TRIGGER triInsExama
AFTER INSERT ON exama
FOR EACH ROW
DECLARE
vtot NUMBER;
vave NUMBER(5, 1);
BEGIN
vtot := :NEW.kor + :NEW.eng + :NEW.mat;
vave := ROUND(vTOt/3, 1);

INSERT INTO examb(hak, tot, ave)
VALUES(:NEW.hak, vtot, vave);
END;

INSERT INTO exama(hak, name, kor, eng, mat) VALUES
('1111', '홍길동', 80, 80, 80);
COMMIT;
SELECT * FROM exama;
SELECT * FROM examb;

-- 행 트리거(BEFORE) : 삭제
CREATE OR REPLACE TRIGGER triDelExama
BEFORE DELETE ON exama
FOR EACH ROW
BEGIN
DELETE FROM examb WHERE hak = :OLD.hak;
END;

DELETE FROM exama WHERE hak = '1111';
COMMIT;
SELECT * FROM exama;
SELECT * FROM examb;

-- 행 트리거(AFTER) : 수정
CREATE OR REPLACE TRIGGER triUpdateExama
AFTER UPDATE ON exama
FOR EACH ROW
DECLARE
vtot NUMBER;
vave NUMBER(5, 1);
BEGIN
vtot := :NEW.kor + :NEW.eng + :NEW.mat;
vave := ROUND(vtot/3, 1);

UPDATE examb SET tot=vtot, ave=vave
WHERE hak = :NEW.hak; -- :OLD.hak
END;

16-2. 트리거 실습

-- 상품 테이블 작성
CREATE TABLE 상품 (
상품코드 VARCHAR2(6) NOT NULL PRIMARY KEY
,상품명 VARCHAR2(30) NOT NULL
,제조사 VARCHAR2(30) NOT NULL
,소비자가격 NUMBER
,재고수량 NUMBER DEFAULT 0
);

-- 입고 테이블 작성
CREATE TABLE 입고 (
입고번호 NUMBER PRIMARY KEY
,상품코드 VARCHAR2(6) NOT NULL CONSTRAINT FK_ibgo_no
REFERENCES 상품(상품코드)
,입고일자 DATE
,입고수량 NUMBER
,입고단가 NUMBER
);

-- 판매 테이블 작성
CREATE TABLE 판매 (
판매번호 NUMBER PRIMARY KEY
,상품코드 VARCHAR2(6) NOT NULL CONSTRAINT FK_pan_no
REFERENCES 상품(상품코드)
,판매일자 DATE
,판매수량 NUMBER
,판매단가 NUMBER
);

-- 상품 테이블에 자료 추가
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
('AAAAAA', '디카', '삼싱', 100000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
('BBBBBB', '컴퓨터', '엘디', 1500000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
('CCCCCC', '모니터', '삼싱', 600000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
('DDDDDD', '핸드폰', '다우', 500000);
INSERT INTO 상품(상품코드, 상품명, 제조사, 소비자가격) VALUES
('EEEEEE', '프린터', '삼싱', 200000);
COMMIT;
SELECT * FROM 상품;

------------------------------------------------------------
-- 3. 트리거 작성
-- 1) 입고 테이블에 INSERT 트리거를 작성 한다.
-- [입고] 테이블에 자료가 추가 되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER insTrg_Ipgo
AFTER INSERT ON 입고
FOR EACH ROW

BEGIN
UPDATE 상품 SET 재고수량 = 재고수량 + :NEW.입고수량
WHERE 상품코드 = :NEW.상품코드;
END;
/

-- 입고 테이블에 데이터 입력
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
VALUES (1, 'AAAAAA', '2004-10-10', 5, 50000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
VALUES (2, 'BBBBBB', '2004-10-10', 15, 700000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
VALUES (3, 'AAAAAA', '2004-10-11', 15, 52000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
VALUES (4, 'CCCCCC', '2004-10-14', 15, 250000);
INSERT INTO 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가)
VALUES (5, 'BBBBBB', '2004-10-16', 25, 700000);
COMMIT;

SELECT * FROM 상품;
SELECT * FROM 입고;

-- 2) 입고 테이블에 UPDATE 트리거를 작성 한다.
-- [입고] 테이블의 자료가 변경 되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER upTrg_Ipgo
AFTER UPDATE ON 입고
FOR EACH ROW

BEGIN
UPDATE 상품 SET 재고수량 = 재고수량 - :OLD.입고수량 + :NEW.입고수량
WHERE 상품코드 = :NEW.상품코드;
END;
/

-- UPDATE 테스트
UPDATE 입고 SET 입고수량 = 30 WHERE 입고번호 = 5;
COMMIT;
SELECT * FROM 상품;
SELECT * FROM 입고;

-- 3) 입고 테이블에 DELETE 트리거를 작성 한다.
-- [입고] 테이블의 자료가 삭제되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER delTrg_Ipgo
AFTER DELETE ON 입고
FOR EACH ROW

BEGIN
UPDATE 상품 SET 재고수량 = 재고수량 - :OLD.입고수량
WHERE 상품코드 = :OLD.상품코드;
END;
/

-- DELETE 테스트
DELETE FROM 입고 WHERE 입고번호 = 5;
COMMIT;
SELECT * FROM 상품;
SELECT * FROM 입고;

-- 입고 테이블의 재고 수량 수정 및 삭제는 상품 테이블의 재고 수량이 적거나 없으면 할 수 없으므로 UPDATE 및 DELETE 트리거를 BEFORE 트리거로 수정하야 상품 테이블의 재고 수량에 따라 수정 또는 삭제를 할수 없도록 수정한다.

-- 4) 판매 테이블에 INSERT 트리거를 작성한다.(BEFORE 트리거로 작성)
-- [판매] 테이블에 자료가 추가 되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER insTrg_Pan
BEFORE INSERT ON 판매
FOR EACH ROW

DECLARE
j_qty NUMBER;

BEGIN

SELECT 재고수량 INTO j_qty FROM 상품 WHERE 상품코드 = :NEW.상품코드;
IF :NEW.판매수량 > j_qty THEN
raise_application_error(-20007, '판매 오류');
ELSE
UPDATE 상품 SET 재고수량 = 재고수량 - :NEW.판매수량
WHERE 상품코드 = :NEW.상품코드;
END IF;
END;
/

-- 판매 테이블에 데이터 입력
INSERT INTO 판매 (판매번호, 상품코드, 판매일자, 판매수량, 판매단가) VALUES
(1, 'AAAAAA', '2004-11-10', 5, 1000000);
COMMIT;
SELECT * FROM 상품;
SELECT * FROM 판매;

INSERT INTO 판매 (판매번호, 상품코드, 판매일자, 판매수량, 판매단가) VALUES
(1, 'AAAAAA', '2004-11-10', 50, 1000000);
COMMIT;
SELECT * FROM 상품;
SELECT * FROM 판매;

-- 5) 판매 테이블에 UPDATE 트리거를 작성한다.(BEFORE 트리거로 작성)
-- [판매] 테이블의 자료가 변경 되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER upTrg_Pan
BEFORE UPDATE ON 판매
FOR EACH ROW

DECLARE
j_qty NUMBER;

BEGIN

SELECT 재고수량 INTO j_qty FROM 상품 WHERE 상품코드 = :NEW.상품코드;
IF :NEW.판매수량 > (j_qty + :OLD.판매수량) THEN
raise_application_error(-20007, '판매량이 재고량보다 많을 수 없습니다.');
ELSE
UPDATE 상품 SET 재고수량 = 재고수량 + :OLD.판매수량 - :NEW.판매수량
WHERE 상품코드 = :NEW.상품코드;
END IF;
END;
/

-- UPDATE 테스트
UPDATE 판매 SET 판매수량 = 200 WHERE 판매번호 = 1;
UPDATE 판매 SET 판매수량 = 10 WHERE 판매번호 = 1;
COMMIT;
SELECT * FROM 상품;
SELECT * FROM 판매;

-- 6) 판매 테이블에 DELETE 트리거를 작성 한다.
-- [판매] 테이블에 자료가 삭제되는 경우 [상품] 테이블의 [재고수량]이 변경 되도록 트리거를 작성한다.

CREATE OR REPLACE TRIGGER delTrg_Pan
AFTER DELETE ON 판매
FOR EACH ROW

BEGIN
UPDATE 상품 SET 재고수량 = 재고수량 + :OLD.판매수량
WHERE 상품코드 = :OLD.상품코드;
END;
/

-- DELETE 테스트
DELETE 판매 WHERE 판매번호 = 1;
COMMIT;
SELECT * FROM 상품;
SELECT * FROM 판매;

-- 다음과 같은 방법을 이용하여 관련된 트리거는 하나의 트리거로 작성 할 수 있다.
-- IF INSERTING THEN
-- 추가할 때
-- ELSIF UPDATING THEN
-- 수정할 때
-- ELSIF DELETING THEN
-- 삭제할 때
-- END IF;

17. 예외처리


예외처리
-- PL/SQL 을 실행 하는 동안에 발생하는 에러 처리

-- 예외 트랩(trap)
만일 예외가 블록의 실행 가능한 섹션에서 발생한다면, 처리는 블록의 예외 섹션에서 해당 예외 처리기로 제어가 넘어 간다. PL/SQL 블록이 성공적으로 예외를 처리 한다면 이때 예외는 둘러싸는 블록이나 환경으로 전달 되지 않는다.

-- 예외 전달
예외를 처리하는 다른 방법은 실행 환경으로 예외를 전달하도록 하는 것이다. 예외가 블록의 실행부에서 발생하여 해당 예외 처리기가 없다면, PL/SQL 블록의 나머지 부분은 수행되지 못하고 종료된다.

-- 예외 검출
예외가 블록의 실행부에서 발생하면 블록의 예외부에 있는 해당 예외 처리부로 제어가 넘어간다.

-- 예외의 유형
실행 중에 ERROR가 발생하면 프로그램이 중단되지 않고 예외에 대한 프로그램을 할 수 있다.

정의된 ORACLE SERVER ERROR : PL/SQL코드에서 자주 발생하는 ERROR을 미리 정의함(선언할 수 없고 ORACLE SERVER이 암시적으로 발생)
정의되지 않은 ORACLE SERVER ERROR : 기타 표준 ORACLE SERVER ERROR(사용자가 선언하고 ORACLE SERVER이 그것을 암시적으로 발생)
사용자 정의 ERROR : 프로그래머가 정한 조건이 만족되지 않을 경우 발생(사용자가 선언하고 명시적으로 발생한다.)

-- 예외정의
EXCEPTION
WHEN exception1 [OR exception2, . . . .] THEN
statement1;
statement2;
. . . . . .
[WHEN exception2 [OR exception3, . . . .] THEN
statement3;
statement4;
. . . . . .]
[WHEN OTHERS THEN
statement5;
statement6;
. . . . . .]
END;

OTHERS : 명시적으로 선언되지 않은 모든 예외를 트랩하는 예외 처리 절

-- 정의된 오라클 서버 에러
NO_DATA_FOUND(ORA-01403) : 데이터를 RETURN하지 않는 SELECT문장
TOO_MANY_ROWS(ORA-01422) : 단일 행 SELECT는 하나 이상의 행을 RETURN

------------------------------------------------------------
DECLARE
TYPE insa_rec IS RECORD (
v_num insa.num%TYPE
,v_name insa.name%TYPE
,v_basicpay insa.basicpay%TYPE
);
rec insa_rec;

BEGIN
SELECT num, name, basicpay INTO rec
FROM INSA WHERE num=1001;

IF rec.v_name = '홍길동' THEN
UPDATE INSA SET name='김길동'
WHERE num=rec.v_num;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('데이터가 없습니다.');
WHEN TOO_MANY_ROWS THEN
-- 단일행 SELECT문은 하나의 행만 리턴해야 한다.
DBMS_OUTPUT.PUT_LINE('데이터가 여러개입니다.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');
END;

SELECT * FROM INSA;
ROLLBACK;

SELECT * FROM INSA;


-- 사용자 정의 예외
-- 선언 절차
1. 선언 섹션에서 사용자가 선언한다.
exception_name EXCEPTION;

exception_name : 예외 이름을 정의

2. 실행 섹션에서 명시적으로 예외를 발생하기 위해 RAISE문장을 사용한다.
RAISE exception_name;

exception_name : 앞에서 선언된 예외 이름을 기술한다.

3. 해당 예외 처리기 안에 선언된 예외를 참조한다.

------------------------------------------------------------
DECLARE
TYPE insa_rec IS RECORD (
v_num insa.num%TYPE
,v_name insa.name%TYPE
,v_basicpay insa.basicpay%TYPE
);
rec insa_rec;

insa_basicpay_check EXCEPTION;

BEGIN
SELECT num, name, basicpay INTO rec
FROM INSA WHERE num=1001;
IF rec.v_basicpay > 2500000 THEN
RAISE insa_basicpay_check;
ELSE
DBMS_OUTPUT.PUT_LINE(rec.v_name || ' ' || rec.v_basicpay);
END IF;
EXCEPTION
WHEN insa_basicpay_check THEN
DBMS_OUTPUT.PUT_LINE('기본급이 250만원 이상입니다.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');
END;

-- 예외 트래핑 함수
에러가 발생 했을 때 두 함수를 사용하여 관련된 에러 코드 또는 메시지를 확인할 수 있다.
코드 또는 메시지에 따라 에러에 대해 취할 작업을 정할 수 있다.

-- SQLCODE : 에러코드
-- SQLERRM : 에러 메시지

DECLARE
TYPE insa_rec IS RECORD (
v_num insa.num%TYPE
,v_name insa.name%TYPE
,v_basicpay insa.basicpay%TYPE
);
rec insa_rec;

BEGIN
SELECT num, name, basicpay INTO rec
FROM INSA WHERE num=8001;

IF rec.v_name = '홍길동' THEN
UPDATE INSA SET name='김길동' WHERE num=rec.v_num;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('에러 코드 : ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('에러 메시지 : ' || SQLERRM);
END;


-- raise_application_error
-- 표준화되지 않은 에러 코드와 에러 메시지를 리턴하기 위해 raise_application_error 프로시저 사용
-- 코드값은 -20000~-20999사이의 수 사용

------------------------------------------------------------
DECLARE
v_num insa.num%TYPE;
BEGIN
v_num := 8001;
DELETE FROM insa WHERE num=v_num;

IF SQL%NOTFOUND THEN
raise_application_error(-20100, '자료가 없음');
END IF;
END;

18. 패키지


-- 패키지
-- 타입, 프로그램객체, 프로시져, 함수등을 논리적으로 묶어 놓은것

-- 작성 방법
1. 패키지 명세
2. 패키지 몸체 구현

-----------------------------------------------------
-- 작성
-- 1. 패키지 명세
CREATE OR REPLACE PACKAGE pack_insa IS
FUNCTION fn_sex(ssn IN VARCHAR2)
RETURN VARCHAR2;
PROCEDURE list_insa(pNum IN NUMBER);
END pack_insa;

-- 2. 패키지 몸체 구현
CREATE OR REPLACE PACKAGE BODY pack_insa IS
FUNCTION fn_sex(ssn IN VARCHAR2)
RETURN VARCHAR2
IS
varSex VARCHAR2(10) := '여자';
BEGIN
IF SUBSTR(ssn, 8, 1) IN ('1', '3') THEN
varSex := '남자';
END IF;
RETURN varSex;
END;

PROCEDURE list_insa(pNum IN NUMBER)
IS
varName VARCHAR2(20);
varSsn VARCHAR2(20);
VarSex VARCHAR2(10);
BEGIN
SELECT name, ssn, fn_sex(ssn) sex INTO varName, varSsn, varSex FROM insa WHERE num=pNum;
DBMS_OUTPUT.PUT_LINE(varName || ' ' || varSsn || ' ' || varSex);
END;
END pack_insa;

EXEC pack_insa.list_insa(1001);

-- 오버로딩
CREATE OR REPLACE PACKAGE pack_insa IS
FUNCTION fn_sex(ssn IN VARCHAR2)
RETURN VARCHAR2;
PROCEDURE list_insa(pNum IN NUMBER);
PROCEDURE list_insa(pName IN VARCHAR2);
END pack_insa;

CREATE OR REPLACE PACKAGE BODY pack_insa IS
FUNCTION fn_sex(ssn IN VARCHAR2)
RETURN VARCHAR2
IS
varSex VARCHAR2(10) := '여자';
BEGIN
IF SUBSTR(ssn, 8, 1) IN ('1', '3') THEN
varSex := '남자';
END IF;
RETURN varSex;
END;

PROCEDURE list_insa(pNum IN NUMBER)
IS
varName VARCHAR2(20);
varSsn VARCHAR2(20);
VarSex VARCHAR2(10);
BEGIN
SELECT name, ssn, fn_sex(ssn) sex INTO varName, varSsn, varSex FROM insa WHERE num=pNum;
DBMS_OUTPUT.PUT_LINE(varName || ' ' || varSsn || ' ' || varSex);
END;

PROCEDURE list_insa(pName IN VARCHAR2)
IS
CURSOR cur_insa IS
SELECT name, ssn, fn_sex(ssn) sex FROM insa WHERE name LIKE '%' || pName || '%';
BEGIN
FOR rec IN cur_insa LOOP
DBMS_OUTPUT.PUT_LINE(rec.Name || ' ' || rec.Ssn || ' ' || rec.Sex);
END LOOP;
END;
END pack_insa;

EXEC pack_insa.list_insa(1001);
EXEC pack_insa.list_insa('김');

19. 테이블 스페이스


-- 테이블 스페이스
-- 시스템 테이블 스페이스 종류
-- SYSTEM : 오라클 서버가 사용하는 공간으로 자료사전 테이블, 뷰가 저장
-- UNDOTBS1 : 트랜잭션의 ROLLBACK문을 실행할 때 변경이전의 데이터로 복구하기 위한 데이터 저장 공간
-- SYSAUX : 기본적으로 만들어지는 시스템 관리용 테이블스페이스
-- TEMP : GROUP BY, ORDER BY, HAVING, START WITH와 같은 SQL 문에서 SORT 작업이 발생되는데, 이 때 사용되는 데이터베이스 내부의 임시 저장 공간
-- USERS : 사용자가 테스트 할 수 있는 기본적으로 만들어지는 공간

-- 참고
-- 1 block : 8KB
-- 8 block = 1 extent = 64 kb
-- 테이블 생성시 1 extent 생성
-- extent(table)이 모여서 segment
-- segment가 저장되는 공간 : 테이블 스페이스
-- 여러개의 테이블스페이스가 모여 데이터베이스

-- SYS 계정
-- dba_tablespaces 데이터 사전
SELECT tablespace_name, initial_extent, min_extlen
FROM dba_tablespaces;

-- dba_data_files 데이터 사전
SELECT file_name, tablespace_name FROM dba_data_files;

-- 테이블 스페이스 공간 확인
SELECT tablespace_name, SUM(bytes), MAX(bytes) FROM
DBA_FREE_SPACE
GROUP BY tablespace_name;

-- 테이블 스페이스 생성(크기 : 100M)
CREATE TABLESPACE sp_exam DATAFILE 'd:/tbl/exam_data.dbf'
SIZE 100M;
-- 용량은 짝수로 지정할것을 권장(디스크상에서 짝수로 용량이 늘어나므로 디스크용량낭비 방지)

SELECT * FROM v$tablespace;
SELECT tablespace_name FROM dba_tablespaces;

-- 사용자 계정
sp_exam 테이블 스페이스에 테이블 작성
CREATE TABLE sp1 (id NUMBER) TABLESPACE sp_exam;

-- 자신에게 부여된 테이블스페이스의 용량 확인
SELECT * FROM USER_TS_QUOTAS;

-- MAX_BYTES : -1 -> 무한대

-- 테이블 스페이스 용량 변경
-- SYS 계정
ALTER DATABASE DATAFILE 'd:/tbl/exam_data.dbf'
RESIZE 300M;

-- 테이블 스페이스 삭제
DROP TABLESPACE sp_exam INCLUDING CONTENTS;
-- INCLUDING CONTENTS :
-- 테이블스페이스에 테이블등 객체가 존재해도 삭제

SELECT tablespace_name, contents FROM dba_tablespaces;

-- 실제 파일을 탐색기로 제거해야 함

-- 테이블 스페이스 자동 확장
CREATE TABLESPACE sp_exam
DATAFILE 'd:/tbl/db_data.dbf' SIZE 20M
AUTOEXTEND ON NEXT 10M MAXSIZE 1000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
-- AUTOEXTEND ON : 자동확장

SELECT * FROM v$tablespace;

-- AUTOEXTEND ON NEXT 10M MAXSIZE 1000M
-- 20M 을 다 사용하면 10M씩 자동 증가(1000M 까지)
-- EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
-- 테이블스페이스의 각 extent size를 1M으로 지정
-- EXTENT : 일정한 수의 ORACLE 블럭(기본 1M)

-- 삭제
DROP TABLESPACE sp_exam INCLUDING CONTENTS;

-- 테이블 스페이스 자동 확장(용량을 지정하지 않으면 무한대)
-- (초기크기 : 20M)
CREATE TABLESPACE sp_exam
DATAFILE 'd:/tbl/db_data.dbf'
SIZE 20M AUTOEXTEND ON;

-- 삭제
DROP TABLESPACE sp_exam INCLUDING CONTENTS;

20. 사용자 관리

-- 주요 자료사전(data dictionary)

-- SYS 계정

-- 사용자 목록 확인
SELECT username, password, created FROM DBA_USERS;
SELECT username, password, created FROM DBA_USERS WHERE username='사용자명';

SELECT * FROM all_users; -- 전체 사용자 확인

-- 시스템 권한(CREATE TABLE, CREATE VIEW 등)
SELECT * FROM DBA_SYS_PRIVS; -- 부여된 모든 시스템 권한 조회
SELECT * FROM SYSTEM_PRIVILEGE_MAP; -- 전체 시스템 권한 목록

-- 롤 확인
SELECT * FROM DBA_ROLES;

-- 롤에 부여된 시스템 권한(CREATE TABLE, CREATE VIEW 등) 확인
SELECT * FROM role_sys_privs;
SELECT * FROM role_sys_privs WHERE role='CONNECT';
SELECT * FROM role_sys_privs WHERE role='RESOURCE';

-- 롤에 존재하는 롤 확인
SELECT * FROM role_role_privs;

-- 롤이 가지고 있는 객체 권한(INSERT, SELECT 등) 확인
SELECT * FROM ROLE_TAB_PRIVS

-- 테이블스페이스 할당량을 확인
SELECT username, tablespace_name, bytes/1024, max_bytes/1024 FROM dba_ts_quotas;


-- 사용자 계정

-- 사용자의 시스템 권한(CREATE TABLE, CREATE VIEW 등) 확인
SELECT * FROM USER_SYS_PRIVS;

-- 사용자에게 부여된 롤 확인
SELECT * FROM user_role_privs;

-- 가지고 있는 객체 권한 확인(INSERT, UPDATE, SELECT, DELETE 등)
SELECT * FROM USER_TAB_PRIVS;

-- 다른 사용자로 부터 받은 객체 권한 확인(INSERT, UPDATE, SELECT, DELETE 등)
SELECT * FROM USER_TAB_PRIVS_RECD;

-- 테이블 목록 확인
SELECT * FROM tab;

-- 테이블 컬럼 확인
SELECT * FROM USER_tab_columns WHERE table_name='테이블명';
SELECT * FROM ALL_TAB_COLUMNS WHERE owner ='디비계정' and table_name = '테이블명'

-- 제약조건 확인
DESC user_constraints; -- user_constraints 구조 확인

SELECT * FROM USER_constraints WHERE table_name='INSA';
-- 어떤 컬럼에 제약조건이 부여되었는지 확인 불가 P : 기본키, C : NOT NULL 등, U : UNIQUE, R : 참조키 등

SELECT constraint_name, table_name, constraint_type FROM user_constraints;

-- 현재 user가 가지고 있는 column에 할당된 제약조건에 대한 정보
SELECT * FROM USER_cons_columns; -- 어떤 컬럼에 기본키가 부여되었는지 확인 가능

-- procedure, function 목록 확인
SELECT object_name FROM user_procedures;

-- 테이블의 프로시저, 함수, 패키지 등 상호 참조되는 관계 확인
SELECT * FROM user_dependencies;

-- 트리거 확인
SELECT trigger_name, trigger_type, table_name FROM user_triggers;

-- 프로시저, 함수등의 소스 확인
SELECT TEXT FROM user_source;

-- 뷰 목록, 소스 확인
SELECT view_name, text FROM user_views;

-- 시퀀스 목록 확인
SELECT * FROM seq;
SELECT * FROM user_sequences;

-- 사용자 확인, 락 설정, 해제
-- SYS 계정
SQLPLUS sys/"암호" as sysdba
SELECT * FROM all_users; -- 전체 사용자 확인

-- soctt LOCK 설정
ALTER USER scott ACCOUNT LOCK;

-- SCOTT 계정
CONN scott/"암호"
-- 에러 : Lock이 설정되어 오라클 접속을 할수 없다.

-- SYS 계정
SQL>CONN sys/"암호" as sysdba

-- soctt LOCK 해제
SQL>ALTER USER scott ACCOUNT UNLOCK;


-- 사용자 관리 - 사용자 추가
-- SYS 계정
CREATE USER sky IDENTIFIED BY abc;
-- 로그온 권한이 없어서 CONN가 불가능

-- sky 사용자에게 데이터베이스에 접속할 수 있는 권한 부여
GRANT CREATE SESSION TO sky;

-- sky 계정
-- 테이블 작성
CREATE TABLE TEST(id NUMBER);
-- 테이블 스페이스 사용 권한이 없으므로 테이블을 작성할 수 없다.

--사용자 삭제
-- SYS 계정
-- sky 사용자가 CONN 되어 있는 상태에서는 삭제 불가
DROP USER sky; -- 테이블등이 존재하면 오류
-- DROP USER sky CASCADE; -- 테이블등이 존재하면 객체도 같이 삭제

-- 테이블스페이스를 사용할수 있는 권한을 부여하여 sky 사용자 추가
-- SYS 계정
CREATE USER sky IDENTIFIED BY abc
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS; -- 무한대
-- QUOTA 50M ON USERS;

GRANT CREATE SESSION TO sky; -- DB 접속 권한

-- 테이블 스페이스의 권한을 부여 했지만 테이블을 만들수 있는 권한이 없으므로 테이블 생성 불가

-- SYS 계정
-- 롤 권한 확인
SELECT * FROM role_sys_privs WHERE role='CONNECT';
SELECT * FROM role_sys_privs WHERE role='RESOURCE';

-- 테이블 생성 권한 부여
GRANT CREATE TABLE TO sky;
-- RESOURCE : 롤 -> 테이블스페이스, 테이블 생성, 자료형 생성등 몇가지 권한을 모아모은것
-- GRANT RESOURCE TO sky;

-- 사용자에 CONN, 테이블스페이스, 테이블작성등의 권한을 한번에 부여
-- GRANT CONNECT, RESOURCE TO sky;

DROP USER sky; -- 테이블이 존재하면 삭제 불가능
-- DROP USER sky CASCADE; -- 테이블 등의 개체가 존재해도 삭제

-- SYS 계정
CREATE USER sky IDENTIFIED BY abc;

-- 사용자에게 테이블 스페이스 권한 부여
ALTER USER sky
DEFAULT TABLESPACE USERS
QUOTA 50M ON USERS;

-- 테이블스페이스 할당량을 확인
SELECT * FROM dba_ts_quotas;
SELECT username, tablespace_name, bytes/1024,
max_bytes/1024 FROM dba_ts_quotas;

-- SYS 계정
-- 암호 관리
GRANT CREATE SESSION TO sky; -- DB 접속 권한

-- SYS에 의해 암호 변경
ALTER USER sky IDENTIFIED BY king;

-- sky 계정
-- sky 자신이 암호변경
ALTER USER sky IDENTIFIED BY king1;

-- SYS 계정
-- sky 사용자의 암호를 만료 시킴
ALTER USER sky PASSWORD EXPIRE;
-- sky 사용자로 CONN시 오류출력후 새로운 암호 입력 요구

DROP USER sky;

-- SYS 암호 변경
CMD>sqlplus "/AS sysdba"
-- CMD>sqlplus /nolog
SQL>ALTER USER sys IDENTIFIED BY 암호;
SQL>CONN sys/암호

-- 시스템 권한 : DB 작업, 연산을 수행하기 위해 필요한 권한
-- CREATE TABLE, CREATE USER, CREATE SESSION.....

-- SYS 계정
SELECT * FROM DBA_SYS_PRIVS; -- 부여된 모든 시스템 권한 조회
SELECT * FROM SYSTEM_PRIVILEGE_MAP; -- 전체 시스템 권한 목록

-- 사용자가 시스템 권한 확인
-- 사용자 계정
SELECT * FROM USER_SYS_PRIVS;

-- SYS 계정
CREATE USER sky1 IDENTIFIED BY king1;
CREATE USER sky2 IDENTIFIED BY king2;

-- DB 접근 권한 부여
GRANT CREATE SESSION TO sky1;
GRANT CREATE SESSION TO sky2;

-- sky1 계정
-- 부여된 시스템 권한 확인
SELECT * FROM USER_SYS_PRIVS;

-- SYS 계정
GRANT CREATE TABLE, CREATE VIEW TO sky1;
GRANT RESOURCE TO sky2;

-- sky1 계정
CREATE TABLE TEST(id NUMBER); -- 오류 : 테이블 스페이스 권한이 없어서

-- sky2
-- 사용자에게 부여된 롤 확인
SELECT * FROM user_role_privs;

CREATE TABLE TEST(id NUMBER);
-- RESOURCE 롤은 CREATE TABLE, 테이블스페이스등의 권한이 존재하므로

-- sky1 계정
GRANT CREATE VIEW TO sky2; -- ADMIN 권한을 가지고 있지 않으므로 다른 사용자에게 권한을 부여 하지 못함

-- SYS 계정
GRANT CREATE TABLE, CREATE VIEW TO sky1
WITH ADMIN OPTION; -- ADMIN 권한 부여

-- sky1 계정
GRANT CREATE VIEW TO sky2;

-- 권한 회수
-- SYS 계정
REVOKE CREATE TABLE, CREATE VIEW FROM sky1;

-- 사용자 제거
DROP USER sky1;
DROP USER sky2 CASCADE; -- 테이블등이 존재하면 오류가 발생하는데 CASCADE 옵션을 붙이면 테이블도 자동 삭제

-- 객체 권한(INSERT, UPDATE....... 등) 확인

-- 사용자 계정
-- 가지고 있는 객체 권한 확인
SELECT * FROM USER_TAB_PRIVS;

-- 다른 사용자로 부터 받은 객체 권한 확인
SELECT * FROM USER_TAB_PRIVS_RECD;

-- SYS 계정
-- 모든 테이블 관련 권한 확인
SELECT * FROM DBA_TAB_PRIVS;

-- SYS : 객체 권한
CREATE USER sky1 IDENTIFIED BY king1;
CREATE USER sky2 IDENTIFIED BY king2;

GRANT CONNECT, RESOURCE, CREATE VIEW TO sky1;
GRANT CONNECT, RESOURCE TO sky2;
-- CONNECT, RESOURCE : 롤

-- sky1
CREATE TABLE TEST(id NUMBER, name VARCHAR2(20));
INSERT INTO TEST (id, name) VALUES(1, 'aaa');
INSERT INTO TEST (id, name) VALUES(2, 'bbb');
COMMIT;

GRANT SELECT ON test TO sky2;
-- sky1이 sky2한테 test 테이블의 SELECT 권한 부여

-- sky2
SELECT * FROM sky1.test;

SELECT * FROM USER_TAB_PRIVS; -- 가지고 있는 객체 권한
SELECT * FROM USER_TAB_PRIVS_RECD; -- 받은 객체 권한 확인

INSERT INTO sky1.test (id, name) VALUES (3, 'ccc');
-- 오류 : INSERT 권한이 없으므로

-- sky1 계정
-- sky2 에게 sky1 테이블에 INSERT 권한 부여
GRANT INSERT(id, name) ON test TO sky2;
-- 또는 GRANT INSERT ON test TO sky2;

-- sky2 계정
INSERT INTO sky1.test (id, name) VALUES (3, 'ccc');
COMMIT;
SELECT * FROM sky1.test;

-- 권한 회수
-- sky1
REVOKE SELECT ON test FROM sky2;

-- sky2 계정
SELECT * FROM sky1.test;
-- 오류

-------------------------------------------
--SYS 계정
DROP USER sky1 CASCADE;
DROP USER sky2 CASCADE;

--ROLE : 관리의 효율성을 위해 서로 연관된 권한을 한데 묶은 그룹

------------------------------------------------------
-- SYS 계정
-- ROLE 확인
SELECT * FROM DBA_ROLES;

------------------------------------------------------
-- ROLE 생성과정
-- 롤생성 -> 롤에 권한 부여 -> 롤을 사용자에 부여

------------------------------------------------------
-- Role 생성
CREATE ROLE std_role;
SELECT * FROM DBA_ROLES;

------------------------------------------------------
-- 암호를 가진 롤 생성
CREATE ROLE std_role1 IDENTIFIED BY aaa;
SELECT * FROM DBA_ROLES;

------------------------------------------------------
-- 롤에 부여된 권한 확인
SELECT * FROM ROLE_TAB_PRIVS WHERE role='STD_ROLE';

------------------------------------------------------
-- 롤에 시스템 권한 부여
GRANT RESOURCE, CONNECT, CREATE VIEW TO std_role;
-- std_role 에 RESOURCE, CONNECT 롤, CREATE VIEW 시스템 권한 부여

SELECT * FROM ROLE_ROLE_PRIVS WHERE role='STD_ROLE';
-- 롤안의 롤 확인

SELECT * FROM ROLE_SYS_PRIVS WHERE role='STD_ROLE';
-- 시스템 권한 확인

------------------------------------------------------
-- 롤에 객체 권한 부여
-- 사용자
GRANT SELECT, INSERT, UPDATE ON insa TO std_role;

--sys 객체 권한 확인
SELECT * FROM ROLE_TAB_PRIVS WHERE role='STD_ROLE';

-- ROLE 변경
-- 롤의 암호 제거
ALTER ROLE std_role1 NOT IDENTIFIED;
SELECT * FROM dba_roles WHERE role='STD_ROLE1';

-- 사용자에게 ROLE 부여
CREATE USER sky1 IDENTIFIED BY king1;

-- 사용자에게 ROLE 적용
GRANT STD_ROLE TO sky1;

-- sky1 계정
SELECT * FROM scott.insa;

-- sky1이 권한 확인
SELECT * FROM USER_ROLE_PRIVS; -- 롤 확인
SELECT * FROM USER_TAB_PRIVS; -- 객체 권한 확인
SELECT * FROM USER_SYS_PRIVS; -- 시스템 권한 확인

-- ROLL의 권한은 시스템 계정에서 확인
-- SELECT * FROM ROLE_ROLE_PRIVS
-- SELECT * FROM ROLE_SYS_PRIVS
-- SELECT * FROM ROLE_TAB_PRIVS

-- ROLE 회수
-- SYS 계정
REVOKE std_role FROM sky1;

-- PUBLIC 롤 : 모든 사용자에게 자동으로 권한 부여

CREATE ROLE pub_role; -- 롤 생성
-- 롤에 권한 부여
GRANT RESOURCE, CREATE VIEW, CONNECT TO pub_role;
-- PUBLIC 롤
GRANT pub_role TO PUBLIC WITH ADMIN OPTION;

-- PUBLIC 롤 회수
REVOKE pub_role FROM PUBLIC;
-- REVOKE pub_role FROM sky1; -- 오류

-- sky1 CONN 오류

-- ROLE 삭제
DROP ROLE pub_role;
DROP ROLE std_role;
DROP ROLE std_role1;

21. 데이터 링크

-- 데이터 링크

-- 데이터 링크 권한 설정
-- SYS 계정
GRANT CREATE DATABASE LINK TO 사용자명;

-- 사용자 계정
CREATE DATABASE LINK link_test
CONNECT TO 사용자명 IDENTIFIED BY "암호"
USING
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=211.169.117.189)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORA)))';
-- ORA : 데이터베이스 명(SID)
-- 암호가 특수문자가 있는 경우에는 "" 안에 암호를 입력한다.

SELECT name, basicPay FROM insa@link_test;
-- 테이블명@링크명

SELECT num, name, buseo, city, basicpay FROM insa@link_test WHERE buseo='개발부'
UNION ALL
SELECT num, name, buseo, city, basicpay FROM INSA WHERE city='인천';

DROP DATABASE LINK link_test;

-- tnsnames.ora 에 정의된 connect_identifier 으로 접속
CMD>sqlplus 사용자명/"암호"@ora111
-- ora111 : tnsnames.ora 에 정의된 connect_identifier

22. 암호화 및 복호화

-- 암호화 및 복호화
-- 암호화 기능을 이용하려면 DBMS_OBFUSCATION_TOOLKIT을 이용한다.

-- 이 패키지는 4개의 프로시져로 이루어져 있다.
- VARCHAR2 타입을 Encrypt/Decrypt할 수 있는 2개의 프로시져
- RAW 타입을 Encrypt/Decrypt할 수 있는 2개의 프로시져
-- (다른 타입은 지원하지 않으므로 number인 경우는 to_char 이용)

-- SYS 계정
SQL>@D:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/dbmsobtk.sql
SQL>@D:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/prvtobtk.plb

--------------------------------------------
-- 모든 사용자가 DBMS_OBFUSCATION_TOOLKIT 패키지의 프로시져를 사용 할 수 있는 권한 부여
-- SYS 계정
SQL>GRANT execute ON dbms_obfuscation_toolkit TO public;

-- 사용자 계정
-- 패키지 선언
CREATE OR REPLACE PACKAGE CryptIT AS
FUNCTION encrypt( str VARCHAR2,
hash VARCHAR2 ) RETURN VARCHAR2;
FUNCTION decrypt( xCrypt VARCHAR2,
hash VARCHAR2 ) RETURN VARCHAR2;
END CryptIT;
/

-- 패키지 몸체
CREATE OR REPLACE PACKAGE BODY CryptIT IS
crypted_string VARCHAR2(2000);

FUNCTION encrypt(str VARCHAR2, hash VARCHAR2)
RETURN VARCHAR2
IS
pieces_of_eight NUMBER := ((FLOOR(LENGTH(str)/8 + .9)) * 8);
BEGIN
dbms_obfuscation_toolkit.DESEncrypt(
input_string => RPAD(str, pieces_of_eight ),
key_string => RPAD(hash,8,'#'),
encrypted_string => crypted_string );

RETURN crypted_string;
END;

FUNCTION decrypt( xCrypt VARCHAR2, hash VARCHAR2 )
RETURN VARCHAR2 IS
BEGIN
dbms_obfuscation_toolkit.DESDecrypt(
input_string => xCrypt,
key_string => RPAD(hash,8,'#'),
decrypted_string => crypted_string );

RETURN TRIM(crypted_string);
END;
END CryptIT;

/

-- DESEncrypt, DESDecrypt 함수는 인수가 VARCHAR2, RAW인 프로시져가 있다.
-- raw : 2진데이터, UTL_RAW.CAST_TO_RAW : 문자열을 2진 데이터로
-- input_string VARCHAR2(32) := 'abcd';
-- raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
-- str VARCHAR2(32) := UTL_RAW.CAST_TO_VARCHAR2(raw_input));

--------------------------------------------------------
-- 테스트
CREATE TABLE exam(id NUMBER, pass VARCHAR2(20));

-- 입력
-- CryptIT.encrypt(비밀번호, 키값)
INSERT INTO exam VALUES(1, CryptIT.encrypt('12345', 'test'));

-- 데이터 조회
-- Decrypt 하지 않으면 결과값이 출력되지 않는다.
SELECT id, pass FROM exam WHERE pass = '12345';

SELECT id, pass FROM exam;
SELECT id, DUMP(pass) FROM exam; -- DUMP : oracle 내부 저장형식

SELECT id, CryptIT.decrypt(pass,'test') pass FROM exam;

23. SYS_RefCusor


-- SYS_REFCURSOR 예제
테이블의 여러 로우를 반복적으로 조회하기 위해 레퍼런스 커서(reference cursor)를 사용 한다.

-----------------------------------------------------
-- score 테이블 작성
CREATE TABLE score(
num NUMBER(7) PRIMARY KEY NOT NULL
,name VARCHAR2(20)
,birth DATE
,kor NUMBER(3)
,eng NUMBER(3)
,mat NUMBER(3)
);

-----------------------------------------------------
INSERT INTO score (num, name, birth, kor, eng, mat)
VALUES (1, '홍길동', '2000-10-10', 80, 80, 80);
INSERT INTO score (num, name, birth, kor, eng, mat)
VALUES (2, '이기자', '2000-11-11', 90, 50, 50);
COMMIT;

-----------------------------------------------------
CREATE OR REPLACE PROCEDURE select_score
(
pResult OUT SYS_REFCURSOR, pSQL IN VARCHAR2
)
IS
BEGIN
OPEN pResult FOR pSQL;
--OPEN pResult FOR SELECT * FROM score;
END;

-----------------------------------------------------
CREATE OR REPLACE PROCEDURE list_score
(
pSQL IN VARCHAR2
)
IS
result SYS_REFCURSOR;

varNum SCORE.num%TYPE;
varName SCORE.name%TYPE;
varBirth SCORE.birth%TYPE;
varKor SCORE.kor%TYPE;
varEng SCORE.eng%TYPE;
varMat SCORE.mat%TYPE;
BEGIN
select_score(result, pSQL);

LOOP
FETCH result INTO varNum, varName, varBirth,
varKor, varEng, varMat;
EXIT WHEN result%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(varNum || ' ' ||
varName || ' ' || (varKor+varEng+varMat));
END LOOP;
END;

-----------------------------------------------------
EXEC LIST_SCORE('SELECT * FROM score');

-- insa 테이블의 자료를 출력하는 프로시저 작성
-- (SYS_REFCURSOR 이용)
SELECT name, basicpay FROM insa WHERE name LIKE '김%';
SELECT name, basicpay FROM insa WHERE name LIKE '이%';

-----------------------------------------------------
CREATE OR REPLACE PROCEDURE select_insa
(
pResult OUT SYS_REFCURSOR, pVar IN VARCHAR2
)
IS
BEGIN
OPEN pResult FOR SELECT name, basicpay FROM insa
WHERE name LIKE '%' || pVar || '%';
END;

-----------------------------------------------------
CREATE OR REPLACE PROCEDURE list_insa
(
pVar IN VARCHAR2
)
IS
result SYS_REFCURSOR;

vName INSA.name%TYPE;
vBasicpay INSA.basicpay%TYPE;
BEGIN
select_insa(result, pVar);

LOOP
FETCH result INTO vName, vBasicpay;
EXIT WHEN result%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vName || ' ' || vBasicpay);
END LOOP;
END;

-----------------------------------------------------
EXEC list_insa('김');
EXEC list_insa('이');

24. 인덱스

-- 인덱스
-- 개요
index란 ROWID와 특정 컬럼의 정렬된 값을 set으로 하여 목록화한 것이다. 정렬해 놓은 컬럼의 각각의 값과 그에 해당하는 ROWID를 이용하여 곧 바로 data block에 접근하여 빠르게 해당 데이터를 찾아낸다. index는 자동으로 oracle에 의해 생성되기도 하고, 사용자가 직접 생성할 수 도 있다.

index는 데이터를 보다 빠르게 검색할 수 있도록 도와주는 객체이다.
DB의 성능을 향상시키고, 행(row)의 유일성을 보장하기 위해 사용된다.
잘못된 인덱스는 DB의 성능을 오히려 저하시킨다.

-- 특징
빠른 검색을 위해 B*Tree를 사용하여 디스크의 입출력 횟수를 감소
Oracle server가 자동으로 인덱스를 사용하고 유지 보수
테이블과는 논리적, 물리적으로 독립
생성, 삭제 시 다른 테이블이나 인덱스에 영향을 주지 않음
각 인덱스는 page로 구성된 pointer와 컬럼 값으로 구성

-- 생성
자동 생성 : Unique 인덱스로 primary key 또는 Unique key 제약조건 생성시 자동으로 인덱스가 생성된다.(중복 값이 존재하면 생성 불가)
수동 생성 : Non-Unique 인덱스로 access 시간을 향상시키기 위하여 사용자가 직접 생성한다. (중복 값이 존재 하는 경우)

-- 종류
1) LOGICAL 인덱스
Single 컬럼 인덱스 : 하나의 컬럼 만 인덱스에 존재
concatenated 인덱스(composite 인덱스) : 여러 컬럼을 결합하여 하나의 인덱스를 생성(32개의 컬럼 까지)
Unique 인덱스 : unique한 컬럼으로 인덱스
non-unique 인덱스 : unique하지 않은 여러 컬럼을 수동으로 인덱스
Function based(함수기반) 인덱스 : 연산자 또는 함수의 적용 결과에 따라 생성됨

2) PHYSICAL 인덱스
partitioned(분할) 인덱스 : 분할된 테이블을 생성하는 경우에 적용됨
non-partitioned(비 분할) 인덱스 : 인덱스를 분할하지 않음
non-partitioned(비 분할) B-Tree 인덱스 : balance tree 구조로 저장
non-partitioned(비 분할) Bitmap 인덱스 : bitmap 구조로 대용량인 경우
normal 인덱스
reverse 인덱스 : B-Tree 인덱스에서 key 컬럼의 값을 뒤집어서 배열

PARTITIONED INDEX는 테이블의 데이터 량이 너무 커서 분할된 테이블을 생성하는 경우에 적용되는 방법이다. 따라서 테이블 분할에 사용되었던 partition 기능을 사용하여 인덱스를 분할한다. 이렇게 분할된 인덱스는 관리하기도 편하고, 각 segment를 여러 개의 tablespace에 나누어 저장하므로 I/O의 경합을 줄이는 효과도 얻을 수 있다.

-- index 생성 시 유의사항
1) 컬럼이 where절 또는 join 조건에서 자주 사용될 때
2) 컬럼에 광범위한 값이 포함될 때
3) 컬럼에 많은 수의 NULL값을 포함할 때
4) 대형 테이블이고 대부분의 질의가 10~15% 이하로 읽어 들일 것으로 예상할 때

-- index를 생성하지 않는 경우 유의사항
1) 테이블에 자료의 양이 적을 때
2) 컬럼이 질의 조건으로 자주 사용되지 않을 때
3) 대부분의 질의들이 10~15%이상 읽어 들일 것으로 예상될 때
4) 테이블이 자주 갱신될 때

-- 형식
-- B*Tree 인덱스
CREATE INDEX 인덱스명 ON 테이블명(컬럼명, ...);

-- Bitmap 인덱스
CREATE BITMAP INDEX 인덱스명 ON 테이블명(컬럼명, ...);

-- 함수기반 인덱스
CREATE INDEX 인덱스명 ON 테이블명(함수식(컬럼명)| 산술식);

-- 역방향 인덱스
CREATE INDEX 인덱스명 ON 테이블명(컬럼명1,컬럼명2,...) REVERSE;

-- 내림차순 인덱스
CREATE INDEX 인덱스명 ON 테이블명(컬럼명1,컬럼명2,... DESC);

-- unique 옵션은 사용자가 직접 unique한 인덱스를 생성하고 할 때 사용한다. 디폴트는 non-unique 인덱스로 생성한다.
-- bitmap 옵션은 사용자가 직접 bitmap index를 생성하고 할 때 사용한다.

-- 인덱스 정보 조회
SELECT index_name, index_type, table_owner, table_name FROM user_indexes;
SELECT index_name, table_name, column_name FROM user_ind_columns;


-- 인덱스 생성
자신 소유의 스키마에서 이 문을 실행하려면 UNLIMITED TABLESPACE 시스템 권한이 있어야 하고,
다른 스키마인 경우 CREATE ANY INDEX 시스템 권한이 있어야 한다

--------------------------------------------
-- 인덱스 실습 테이블 작성
CREATE TABLE insa1 AS SELECT * FROM INSA;

INSERT INTO insa1 SELECT * FROM INSA;
INSERT INTO insa1 SELECT * FROM INSA;
INSERT INTO insa1 SELECT * FROM INSA;
INSERT INTO insa1 SELECT * FROM INSA;
INSERT INTO insa1 SELECT * FROM INSA;
-- 여러번 실행(많이)

-- 인덱스 실습용 자료 추가
INSERT INTO insa1 (num, name, ssn, ibsaDate, city, tel, buseo, jikwi, basicPay, sudang) VALUES
(9001, '자바다', '010101-1022432', SYSDATE, '서울', '011-2356-4528', '기획부', '사원', 1150000, 100000);

COMMIT;

-- 성능향상을 위해 시간을 표시
SET TIMING ON

-- 인텍스를 작성하지 않은 상태에서 검색
SELECT name, ssn FROM insa1 WHERE name='자바다';

-- B-Tree 인덱스
어떤 행에 대한 access 회수도 동일하게 하는 바이너리의 균형 탐색 tree이다. 찾으려는 행이 테이블 시작이나 끝 또는 중간에 있어도 거의 같은 횟수 내에 지정된 값을 access하는 효율적인 방법이다.

B-tree index는 binary search index의 약자이며, 일명 balence tree index라고도 부른다.

밸런스 트리 인덱스를 만드는 기준은 다음과 같다.
-- 인덱스를 만들어야 하는 열은 DML(insert, update, delete) 문의 WHERE 절에 자주 나오는 열이다.
-- 조인되는 테이블의 조인 열에는 인덱스를 만들어야 한다.
-- 인덱스에 NULL 값이 저장되지 않기 때문에, NULL 값이 많은 열에 인덱스를 만들면 유리하다.
-- DML 문장의 WHERE 절에 의해 검색되는 데이터의 테이블 전체의 10～15%의 범위에 속할 때 인덱스를 만들어야 한다.
-- 하나의 테이블에 인덱스를 많이 만들면 update, delete할 때 실행속도가 늦다.(하나의 테이블에 3~4개의 인덱스가 적당하다)

SELECT index_name, table_owner, table_name FROM user_indexes;
CREATE INDEX insa_name_idx ON insa1(name);
SELECT index_name, table_owner, table_name FROM user_indexes;

SELECT name, ssn FROM insa1 WHERE name='자바다';

-- 인덱스 삭제
DROP INDEX insa_name_idx;

-- Bitmap 인덱스
Bitmap 인덱스는 위에서 내려오는 것과는 달리 여러 단계를 거치지 않고 단번에 해당 block에 찾아가는 것을 의미한다. 즉, 소수의 다른 값을 가진 컬럼에 대해 적합한 타입의 인덱스인데, 컬럼에 대해 극소수의 다른 값이 존재하고 쿼리에서 제한조건(where절의 AND, OR연산)으로서 이 컬럼을 자주 사용하는 경우 컬럼에 대해 bitmap 인덱스 사용을 고려해야 한다.

-- 아주 큰 테이블에서 검색할 때 사용
-- UPDATE, INSERT, DELETE 문이 자주 실행되지 않는 테이블에 사용
-- 일반 인덱스의 크기가 너무 크거나 인덱스 생성 시간이 너무 많이 소요되는 경우에 사용

CREATE BITMAP INDEX insa_name_idx ON insa1(name);
SELECT index_name, table_owner, table_name FROM user_indexes;

SELECT name, ssn FROM insa1 WHERE name='자바다';

-- 인덱스 삭제
DROP INDEX insa_name_idx;

-- Function based index(함수 기반 인덱스)
-- 인덱스가 생성될 컬럼에는 함수나 산술식, 상수, 사용자 정의 함수를 적용할 수 있다.
-- 함수기반 인덱스를 생성하면 bitmap 인덱스가 생성된다(default).
-- 함수기반 인덱스 생성은 system 권한이 있어야 가능하다.

CREATE INDEX insa_ssn_idx ON insa1(SUBSTR(ssn, 8, 1));
SELECT index_name, table_owner, table_name FROM user_indexes;

SELECT name, ssn FROM insa1 WHERE SUBSTR(ssn, 8, 1) IN (1, 3);

-- 인덱스 삭제
DROP INDEX insa_ssn_idx;

-- 인덱스 재생성
인덱스가 만들어진 후 INSERT, UPDATE가 일어 난 경우 오라클이 자동으로 인덱스를 갱신하지만 DELETE는 논리적인 삭제 과정만 일어나고 인덱스는 그대로 남아 있다. 이런 경우 인덱스를 재 생성한다.

ALTER ONDEX 인덱스명 REBUILD;

-- 인덱스 삭제

SELECT index_name, table_owner, table_name FROM user_indexes;

DROP INDEX insa_ssn_idx;

SELECT index_name, table_owner, table_name FROM user_indexes;

DROP TABLE insa1 PURGE;

오라클 10일차 자료 정리 완료 -

자료: KH 정보교육원 한장희 교수님

html tag 작성 : Java 75기 엄기백

Mady by 엄기백