p202페이지
트랜잭션 Transaction : 분리되어 수행될수 없는 논리적인 하나의 작업 단위
Unit of work
예1] 계좌이체 : A로부터 출금 - > B에게 입금 (update, update)
원자성 : 전체 작업이 완료되거나 혹은 전체 작업이 수행 전으로 취소되어야 합니다.
예2] 쇼핑몰 구매 : 결제 (결제 테이블에 insert)
구매상품, 고객정보, 배송지 주소(배송 테이블 insert)
구매된 제품에 대한 재고 수량 변경(물품 테이블 update)
고객의 구매 history 정보 추가(고객의 구매테이블 insert)
select문 트랜잭션 처리 안함
데이터베이스는 DML문에 대해서 내부적으로 트랜잭션을 시작합니다.
트랜잭션의 종료: commit; rollback; 세션 종료(정상종료, 비정상종료), DDL, DCL문장을 수행
commit으로 종료한 경우 데이터베이스의 disk상에 변경 내용이 저장되면서 영속성을 갖는다
DDL : create, alter, drop, truncate...은 수행 즉시 auto commit됩니다.
conn hr2/hr2
select * from dept;
delete from dept; --> 내부적으로 트랜잭션 시작
select * from dept;
rollback; --> 명시적으로 트랜잭션을 종료
select * from dept;
insert into dept
values (77, 'AppDev', '경기');
select * from dept;
update dept
set loc = '대전'
where deptno = 77;
select * from dept;
command창을 새로 열고..
sqlplus hr2/hr2
select * from dept;
==> 데이터베이스에 접속한 사용자는 동일하지만 세션이 다릅니다.
==> 세션별로 트랜잭션은 격리되어서 수행됩니다.
==> 다른 세션에서의 변경은 commmit이 되어야만 다른 모든 세션에서 볼수 있다.
>>>>developer에서
commit;
select * from dept;
>>>>command창에서 hr2사용자로
select * from dept;
update emp
set sal = 0;
select * from emp;
>>>>developer에서
select sal from emp;
update emp
set sal = 1; ===>? 명령어가 완료되지 못하고 waiting됨 , 서로 다른 세션에서 동일한 데이터에 대해서 동시 변경은 불가능하도록 트랜잭션은 고립되어 수행됩니다.
>>>>command창에서 hr2사용자로
rollback;
>>>>developer에서
select sal from emp;
rollback;
drop table t_dept purge;
create table t_dept
as select * from dept
where 1=2; ===>autocommit;
insert into t_dept
select * from dept;
select * from t_dept;
==>세션을 비정상종료 합니다.
==>developer를 다시 시작시켜서 hr2로 데이터베이스에 connect
select * from t_dept;
==>세션을 비정상종료했기 때문에 진행중이던 트랜잭션은 rollback;
insert into t_dep
select * from dept;
exit; ==>세션을 정상 종료합니다.
세션을 다시 시작해서 select * from t_dept;
drop table book purge;
create table book
(isbn varchar2(10) ,
title varchar2(20),
author varchar2(20)
);
desc book;
insert into book
values (1234, 'java', 'sun micro');
savepoint a; ==> 취소할 수 있는 제어점 설정
insert into book
values (1235, 'oracle11g', 'oracle corp');
savepoint b; ==> 취소할 수 있는 제어점 설정
insert into book
values (1236, 'android', 'google');
select * from book;
rollback to a;
select * from book;
========================================
p242
emp테이블에서 전체사원의 평균 월급과
부서별 사원들의 월급의 평균과
부서별로 직무를 그룹핑한 사원들의 월급의 평균을 단일 결과로 생성하시오
select to_number(null), to_char(null), avg(sal)
from emp
union all
select deptno, to_char(null), avg(sal)
from emp
group by deptno
union all
select deptno, job, avg(sal)
from emp
group by deptno, job;
==> 각각의 select문을 실행하기 위해서 emp테이블을 3번 Access, 2번은 group by을 수행하기 위해서 hash 함수로 그룹핑 연산을 수행합니다.
select deptno, job, avg(sal)
from emp
group by rollup(deptno, job);
==>1번 대상 테이블에 Access해서 전체 그룹핑 결과와 서브 그룹핑 결과를 생성
group by rollup(A, B) => group by (A, B), group by(A), group by ()
group by rollup(A, B, C) => group by (A, B, C), group by (A, B), group by (A), group by ()
전체 그룹핑 집계와 서브그룹핑 집계를 생성
emp테이블에서 전체사원의 평균 월급과
부서별 사원들의 월급의 평균과
부서별로 직무를 그룹핑한 사원들의 월급의 평균,
직무별로만 그룹핑한 사원들의 월급의 평균을 단일 결과로 생성하시오
select to_number(null), to_char(null), avg(sal)
from emp
union all
select deptno, to_char(null), avg(sal)
from emp
group by deptno
union all
select to_number(null), job, avg(sal)
from emp
group by job
union all
select deptno, job, avg(sal)
from emp
group by deptno, job;
==>4번 테이블 Access, 3번 hash함수로 그룹핑 처리...
select deptno, job, avg(sal)
from emp
group by cube(deptno, job);
group by cube(A, B) ==> group by(A, B), group by(A), group by (B), group by ()
group by cube(A, B, C) ==> group by(A, B, C), group by(A, B), group by (B, C), group by(A, C), group by(A), group by (B), group by (C), group by ()
select deptno, job, avg(sal), grouping(deptno), grouping(job)
from emp
group by cube(deptno, job);
select deptno, job, avg(sal), , grouping(deptno), grouping(job)
from emp
group by rollup(deptno, job);
drop table 테이블 purge; ==>10g버전부터 오라클만 지원하는 옵션
==>논리적으로 테이블을 삭제하고, 테이블 이름을 rename(BIN$.....)해서 recycle bin에 남아 있으므로 조회 됨...
==>purge옵션을 주어야 recyclebin을 by pass하고 물리적으로 완전 삭제됨
drop table 테이블; 표준sql, oracle 9i까지는 물리적으로 테이블의 메타 data, 레코드가 모두 완전 삭제, 복구 불가능
drop table 테이블이름 cascade constraint;
자식테이블에서 참조하는 부모테이블은 삭제 불가이므로
삭제하려면 자식테이블에서 참조 무결성 제약조건인 foreign key를 삭제하면 부모 테이블을 삭제할 수 있습니다.
테이블에 PK, UK 제약조건이 선언된 컬럼에는 인덱스가 자동으로 생성되지만,
자주 검색에 사용되는 컬럼에 인덱스가 필요할 경우
해당 컬럼에 인덱스를 생성할 수 있습니다.
인덱스는 테이블과 따로 저장됩니다.
create index 인덱스이름 on 테이블( 컬럼 [, 컬럼,...]);
drop index 인덱스이름;
인덱스 생성해야 할 컬럼>>
where절에 자주 검색 조건으로 사용되는 컬럼
join 컬럼
group by, order by절에 자주 사용되는 컬럼
위의 후보컬럼중에서 컬럼값의 분포도가 넓은 컬럼이어야 합니다.
테이블의 row 많아서 인덱스를 사용해서 검색했을 때 전체 테이블의 2~5%이내의 행이 리턴
update등으로 컬럼값이 자주 변경되는 컬럼에는 생성하면 안됩니다.
인덱스 생성해서는 안되는 컬럼>>>
테이블의 레코드가 적은 경우
update등으로 컬럼값이 자주 변경되는 컬럼
where절,join , group by, order by절에 자주 사용되지 않는 컬럼
대상 테이블에 생성된 인덱스 정보를 조회하려면
select *
from user_indexes
where table_name = 'EMP';
View는 논리적인 테이블
table은 자신의 data가 존재하지만, View는 data가 존재하지 않습니다.
View는 테이블에 대한 window역할을 합니다.
View는 하나 이상의 테이블로부터 데이터를 조회하는 select으로 정의됩니다.
회원 ( 고객ID,주소,... )....
conn sys/oracle as sysdba
grant create view to HR2
conn HR2/HR2
create or replace view emp30_v
as select empno, ename, job, deptno
from emp
where deptno = 30;
===> alter view 구문이 없습니다.
==> 동일한 이름의 view를 생성할 경우 오류 발생하므로
==> create or replace는 존재하지 않는 view는 새로 생성하고
이미 존재하는 view에 대해서는 definition을 변경합니다.
desc emp30_v
select * from emp30_v
select *
from user_views
where view_name = 'EMP30_V';
drop view emp30_v;
==>뷰를 삭제하면 data dictionary에서 view에 대한 definition만 삭제되며
원본 테이블에는 영향을 주지 않는다.
뷰의 definition에 해당하는 select문에 group by, having, order by, 그룹함수, 수식, 조인 모두 사용 가능합니다. => 복합 뷰는 select만 가능합니다.
뷰의 definition으로 하나의 테이블, not null컬럼을 포함하는 view는 SimpleView이며, SimpleView에 insert, update, delete를 수행하면 원본 테이블에도 insert,update, delete가 수행됩니다.
시퀀스 객체 =>자동 number 생성기
게시판, 주문테이블의 경우 각각의 row를 식별할 수 있는 Primary key컬럼으로 글번호, 혹은 주문번호 컬럼을 추가합니다.
추가된 컬럼에 insert할때마다 자동으로 정수값이 증가되어 추가되도록 하려면
create sequence emp_seq;
[start with n]
[increment by n]
[maxvalue n|unlimited]
[minvalue n|unlimited]
[cycle|nocycle]
[cache|nocache]
select * from user_sequences;
시퀀스는 생성하면 자동으로 컬럼 2개 추가됩니다. nextval, currval
현재 시퀀스값 조회>>>
select emp_seq.currval from dual;
==> 에러
시퀀스 생성후 반드시 nextval먼저 호출하고 currval을 호출할 수 있습니다.
select emp_seq.nextval from dual;
select emp_seq.currval from dual;
select empno
from emp;
insert into emp (empno, ename)
values (emp_seq.nextval, '홍길동');
select empno, ename
from emp;
alter sequenece ~~;
drop sequence ~~~;
==================================================
declare
...//선언문
begin
...//실행문 (SQL문, 제어문, 반복문)
exception
...//예외처리
end;
/
p326페이지
LOOP
반복 수행 문장;
EXIT WHEN 조건 ;
END LOOP;
>>1~10사이의 홀수만 역순으로 출력하세요 BASIC LOOP
DECLARE
cnt number(2) := 10;
BEGIN
LOOP
if mod(cnt, 2) = 1 then
DBMS_OUTPUT.put_line(cnt);
end if;
cnt := cnt-1;
EXIT WHEN cnt = 0 ;
END LOOP;
END;
/
====================================================
WHILE 조건 LOOP
반복 수행 문장;
END LOOP;
>> 1~10까지의 누적합을 구하시오
DECLARE
cnt number(2) := 1;
hap number(2) := 0;
BEGIN
WHILE cnt <= 10 LOOP
hap := hap+cnt;
cnt := cnt+1;
END LOOP;
DBMS_OUTPUT.put_line('1~10까지의 누적합은 '||hap);
END;
/
FOR 카운트 변수 IN 하한값..상한값 LOOP
반복 수행 문장;
END LOOP;
FOR 카운트 변수 IN REVERSE 하한값..상한값 LOOP
반복 수행 문장;
END LOOP;
>>1~10사이의 짝수를 역순으로 출력하시오 (FOR LOOP문 사용)
BEGIN
FOR cnt IN REVERSE 1..10 LOOP
if mod(cnt , 2) = 0 then
dbms_output.put_line(cnt);
end if;
END LOOP;
end;
/
>>구구단 7단 출력하시오 (FOR LOOP문 사용)
BEGIN
FOR i IN 1..1 LOOP
dbms_output.put_line(7 || '*'|| i ||'='||(7*i));
END LOOP;
end;
/
>>구구단 출력하시오 (FOR LOOP문 사용)
BEGIN
FOR i IN 1..9 LOOP
FOR j IN 2..9 LOOP
dbms_output.put(j || ' * '|| i ||' = '||(j*i)||' ');
END LOOP;
dbms_output.new_line();
end loop;
end;
/
create or replace procedure 프로시저이름 (num in|out|inout number, ..)
[is]
로컬변수;//
begin
실행문장;
exception
예외처리 문장
end 프로시저이름;
/
create table patient(
patient_id varchar2(6)
, body_temp_deg_c number(4, 1)
, body_temp_deg_f number(4,1)
, insurance varchar2(1)
);
select * from patient;
create or replace procedure changeCF(patientId in varchar2, temp_c binary_double)
is
temp_f binary_double := 0.0 ;
begin
temp_f := (9.0/5.0)* temp_c +32.0;
insert into patient ( patient_id, body_temp_deg_c , body_temp_deg_f)
values (patientId, temp_c, temp_f);
end;
/
execute changeCF ('YN0001', 40.0)
execute changeCF('YN0002', 41.0)
select * from patient;
=================================================================
conn hr2/hr2
drop table t_emp purge;
create table t_emp
( deptno number(4),
sum_sal number(8, 2),
avg_sal number(8, 2),
avg_tax number(8, 2),
avg_comm number(8, 2)
);
>>부서번호를 입력받으면 해당 부서번호 소속 사원의 월급합계, 월급 평균,
커미션 평균, 평균 세금을 계산해서 t_emp테이블에 insert를 수행하는 프로시저를 만드세요..
커미션이 없으면 0으로 처리합니다.
세금은 부서별 평균이 1000이상이면 5%, 2000이상이면 10% , 3000이상이면 15%, 4000이상이면 20%로 계산합니다.
create or replace procedure dept_all_avg(p_deptno number)
is
v_sum_sal emp.sal%type :=0;
v_avg_sal emp.sal%type :=0;
v_avg_comm emp.comm%type :=0;
v_avg_tax emp.sal%type :=0;
begin
select sum(sal), avg(sal) , avg(nvl(comm, 0))
into v_sum_sal, v_avg_sal, v_avg_comm
from emp
where deptno = p_deptno;
v_avg_tax := case when v_avg_sal > 1000 then v_avg_sal*0.05
when v_avg_sal > 2000 then v_avg_sal*0.1
when v_avg_sal > 3000 then v_avg_sal*0.15
when v_avg_sal > 4000 then v_avg_sal*0.2
end ;
insert into t_emp (deptno,sum_sal , avg_sal, avg_tax, avg_comm )
values (p_deptno, v_sum_sal, v_avg_sal, v_avg_tax, v_avg_comm);
end;
/
execute dept_all_avg(10)
execute dept_all_avg(30)
select * from t_emp;
함수 : 반드시 하나의 값을 리턴
create or replace function 함수이름 (파라미터...)
return 리턴타입
is
로컬 변수 선언 및 초기화
begin
실행문장;
return 값;
end;
/
>>사원번호를 입력 받아서 해당 사원의 세금을 계산리턴
월급이 1000이상이면 5%, 2000이상이면 10% , 3000이상이면 15%, 4000이상이면 20%로 계산합니다.
create or replace function emp_tax(p_empno number)
return number
is
v_tax emp.sal%type := 0;
v_sal emp.sal%type := 0;
begin
select sal into v_sal
from emp
where empno = p_empno;
v_tax := case when v_sal > 4000 then v_sal*0.2
when v_sal > 3000 then v_sal*0.15
when v_sal > 2000 then v_sal*0.1
when v_sal > 1000 then v_sal*0.05
end ;
return v_tax;
end;
/
select empno, sal, emp_tax(empno)
from emp;
conn scott/tiger
create or replace function member_sex (p_registno varchar2)
return varchar2
is
v_sex varchar2(10) := '남자';
v_substr char(1) := '0';
begin
select substr(regist_no, 8, 1) into v_substr
from ec_member
where regist_no = p_registrno;
if v_substr = '2' then v_sex := '여자'
end if;
return v_sex;
end;
/
select userid, name, regist_no, member_sex(regist_no) "성별"
from ec_member;
create or replace function member_yymm (p_userid ec_member.userid%type)
return varchar2
is
v_duration varchar2(15) ;
v_year number(2) :=0;
v_month number(3) := 0;
begin
select ceil(ceil(months_between(sysdate, timestamp))/12) ,
mod(ceil(months_between(sysdate, timestamp)), 12)
into v_year, v_month
from ec_member
where userid = p_userid;
v_duration := v_year ||'년 '||v_month||'개월';
return v_duration;
end;
/
select userid, name, timestamp, member_yymm(userid) "가입기간"
from ec_member;
