닮슬아빠의 그냥노트

* oracle 시스템 데이터 Dictionary에 들어 있는 정보

1. 테이블과 뷰, 스냅샷, 인덱스, 동의어, 프로시저, 함수를 포함한 모든 데이터베이스 오브젝트의 이름

2. 오라클 데이터베이스 사용자의 이름

3. 각 사용자에게 주어진 특권과 역할

4. 무결성 제한의 명세

5. 열을 위한 초기 값

6. 스페이스 소비와 할당

7. 정보 오디팅(auditing)

* oracle 시스템 데이터 Dictionary의 필수적인 두 컴포넌트

1. 베이스 테이블

2. 사용자가 액세스 할 수 있는 뷰

* 데이터 Dictionary는 sys 사용자의 소유이다.

* 오라클 데이터베이스 논리구조 특징 정의

1. 테이블스페이스

2. 세그먼트

3. 영역(extents)

4. data block

* 테이블 스페이스를 사용하여 관리자가 하는 일

1. 데이터 베이스에 의한 물리적 디스크 스페이스 사용을 컨트롤 한다.

2. 특정 디스크 쿼타를 개별 사용자에게 할당한다.

3. 여러 물리적 저장장치에 걸쳐 데이터 저장을 할당한다.

* 테이블 스페이스를 나누는 잇점

1. 시스템 데이타와 사용자 데이터를 구분한다.

2. 애플리케이션들을 서로 구분할 수 있다.

3. 각 테이블스페이스를 하나는 온라인으로 다른 것은 오프라인으로 할 수 있는 기회를 제공한다..

4. 퍼포먼스를 개선하고 데이터 회선을 줄이기 위하여 서로 다른 테이블스페이스를 별도의 디스크 드라이브에 저장할 수 있는 능력

* 시스템 테이블 스페이스에 포함되는 것들

1. 프로시저 함수, 트리거, 패키지와 같은 PL/SQL, 롤백 세그먼트(rollback segment)

* 시스템 테이블 스페이스는 항상 온라인으로 있어야 한다.

* 테이블 스페이스가 오프라인일 필요가 있는 경우

1. 데이터베이스에서 유지 또는 백업을 수행하기 위해

2. 애플리케이션에의 업그레이드나 수정 중에 이용할 수 없는 테이블 그룹을 만들기 위해

3. 데이타 베이스가 온라인으로 있을때 일부를 이용할 수 없도록 만들때

* 테이블 스페이스 만들기

create tablespace dbt_user_01

datafile '<directory>/datafile_01' size 10m

default storage (

initial 50k

next 50k

minextents 10

maxextents 50

pctincrease 0)

offline;

* 기존의 테이블 스페이스에 새로운 데이터 파일 추가

alter tablespace dbt_user_01

add datafile '<directory>/new_file_name_2' size 5m

* 테이블 스페이스의 정보 보기

select tablespace_name, initial_extent, next_extent, max_extents, pct_increase,

from sys.dba_tablespace;

* segment

종류: 테이블, 인덱스, 롤백, 템포러리

* 테이블 세그먼트

테이블, 클러스터, 스냅샷으로부터의 데이터를 저장한다.

pctfree명령은 세그먼트가 가지는 프리스페이스의 양을 모니터링한다.

* 인덱스 세그먼트

인덱스 데이터를 수용한다.

* 롤백 세그먼트

원하면 롤백 하여야 할 sql트랜잭션의 활동을 기록하는 데이타베이스의 일부분이다

변경된 데이타를 위한 블록 id와 파일명, 마지막 트랜잭션 이전에 존재하였던 데이타를 포함

* Temporary 세그먼트

sql트랜잭션의 중간 결과를 수용

* Extents(영역)

연속적인 블록 번호들로 이루어지는 논리적 저장구조

* 데이터 블록

데이터를 저장하기 위하여 오라클이 사용하는 최소 저장단위

헤더,테이블디렉토리,로우디렉토리,로우데이타,프리스페이스를 포함한다.

* pctfree와 pctused

pctfree와 pctused 는 개발자가 데이터 블록 프리스페이스의 사용을 콘트롤 하는데 이용할 수 있다. 이 2개의 파라미터는 테이블과 클러스터를 만들거나 변경할 때에만 지정할 수 있다.

pctfree 35 : create와 함께 사용되며, 그 테이블을 위하여 사용되는 데이터 테이블의 35%를 가능한 장래의 갱신을 위한 프리로 남겨두라는 것

pctused 40 : create와 함께 사용되어 오라클로 하여금 그 블록의 40% 이상이 프리 일경우에만 데이타를 삽입하라는 것.

* 데이터 타입

값의 범위를 제한해줌

* char type

1부터 255의 범위 문자 숫자 데이타를 저장한다.데이타 길이는 고정된다.

실제 들어가는 데이타가 할당된 것보다 적으면 나머지 부분엔 공백이 들어가고 그 반대의 경우엔 에러가 난다.

* varchar2

1부터 2000바이트의 범위(variable length)

* long

2기가 바이트까지의 정보를 포함하는 문자 데이터(variable length)

* row, long row

2진 데이타를 위한 것으로 이 데이타들은 멀티미디어 데이타나 2진 데이타를 위해 적절하다.

raw는 varchar2와 비슷하고 long law는 long과 비슷하다.

* row id

클러스터되지 않은 오라클 테이블에서 행의 물리적 주소에 해당

다음과 같이 사용 가능하다.

select rowid, ename from emp

row id EMP_NAME

--------------------------

000c0b7.0000.0001 'WHITE'

000c0b7 : 데이터 블록

0000 : 행 시퀀스 번호

0001 : 데이터 파일 번호

* row id의 특징

1. 가장 빠른 행 액세스 속도

2. 테이블이 어떻게 조직되는지 보는데 사용할 수 있다.

3. 주어진 테이블에 있는 행들을 위한 고유한 식별자. rowid는 인덱스 구축에 사용됨

* mlslabel 데이타 타입

Trusted Oracle을 위해 사용되며 255바이트의 최대 크기를 갖는다.

* 테이블 만들기

create table emp (

empno number(15) primary key,

first_name varchar2(20) not null,

last_name varchar2(40) not null,

deptno varchar2(12),

hire_date date not null)

pctfree 20

pctused 40

tablespace emp_01

storage (initial 50k

next 50k

maxextense 15

pctincrease 25);

empno는 주키로 사용된다.

pctfree는 장래의 확장을 위하여 프리 스페이스로서 예비된 각 데이터블럭의 퍼센티지를 나타내다. 20%가 예비된다.

pctused는 새로운 레코드를 삽입하기 위해 남아 있어야 할 양이다.

pctfree 값을 크게 하면 사용되지 않는 공간을 증가시키지만 sql명령의 효율을 증가시킨다.

pctused값을 크게 하면 미사용저장 공간을 줄이긴 하지만 insert, update실행의 퍼포먼스를 저하시킨다.

* 테이블 코멘트

테이블이나 열에 대한 코멘트를 데이타 사전에 추가하려면 comment명령을 사용한다.

comment on column emp.hire_date IS '무라무라';

코멘트를 제거하려면

comment on column emp.hire_date IS ''

* 테이블 제작중에 테이블 로드하기

앞의 테이블 작성 예에 다음을 추가한다.

as select empid, f_name, l_name, dept, hire_date from uk_emp;

* 테이블 클러스터 만들기

클러스터는 테이블 테이터를 저장하는 선택적인 방법. 자주 함께 액세스되는 테이블은 동일한 데이터 블럭에 물리적으로 함께 저장된다.

create cluster emp_dept ( deptno number(5))

pstused 80

pctfree 5;

* 클러스터 인덱스 만들기

create index i_emp_dept

on cluster emp_dept;

* 테이블 변경

alter table dept

add (dept_mgr varchar2(35));

alter table emp

modify (sal not null);

alter table emp

add constraint s_dept_man_id_fk foreign key (deptno) references dept;

change dept to emp

alter table dept

drop constraint s_dept_man_id_fk

alter table emp

* 테이블 잘라내기

truncate table명령 사용

장점 : 롤백세그먼트를 사용하지 않는다.실행즉시 테이블스페이스를 해제한다.

단점 : 롤백되지 않으므로 사용에 신중해야 한다.

* 시노님(Synonym)

오라클 오브젝트를 위한 앨리어스(alias)이다.실제적인 오라클 오브젝트가 아니라 오브젝트에 대한 직접적인 참조이다. 이 오브젝트는 테이블, 뷰, 스냅샷, 시퀀스, 프로시저, 함수, 패키지 또는 다른 시노님이 될 수 있다.

제약: 프로시저 안에 포함될 수 없다.

* 시노님 사용 이유

1. 데이터 사전에 대한 정의 외에는 어떤 스토리지도 요구하지 않는다.

2. sql코딩을 단순화한다.

3. 데이터베이스 보안을 개선한다.

4. 오라클 오브젝트에의 퍼블릭 액세스를 제공한다.

* 시노님 만들기

create public synonym emp FOR emp_01.emply;

create synonym emp for emp_01.emply

public을 쓰지 않으면 전용 시노님이 된다.(반대: 공용)

리모트의 데이타베이스를 참조하는 시노님은 다음과 같이 만든다.

create synonym part for master_part.part_number@corp;

* 시노님 사용하기

select * from emp;

시노님을 사용하지 않는다면

select * from emp_01.emply;

* 시노님 이름 바꾸기

rename dept to bus_unit;

(Indexs and Sequence)

* 인덱스 생성 지침

1. 테이블에서 행의 15%이하만을 검색하는 경우가 많을때

2. 인덱스 열은 다수의 테이블 조인을 개선하기 위한 연결에 이용될때

3. 행이 몇 안되는 작은 테이블은 인덱스 않는다.

4. 주요키와 고유키는 자동적으로 인덱스를 갖지만 대개는 모든 외부 키에도 인덱스를 만드는 것이 좋다

5. 열에 널 값이 없는것이 좋다

6. 테이블이 읽기 전용이면 인덱스가 많을수록 편리 하고 갱신이 많을경우엔 인덱스가 적을수록 좋다

예.emp테이블에서 phone열에 고유한 인덱스를 만든다.

create unique index i_emp_phone_uk on emp(phone);

또한 복수 열에 대해서 인덱스를 만들수도 있다.

예. create index i_emp_city_zip

on emp(city, zip);

=> 이런 경우 가장 많이 쓰이는 열을 우선 놓는다. 모두 16개까지 열을 넣을 수 있다.

* 인덱스 생성시의 파라메터

1. tablespace : 인덱스가 생성되는 테이블 스페이스를 지정할 수 있다.

2. cluster : 클러스터를 위하여 인덱스된 클러스터 키의 이름을 제공

3. pctfree : table의 pctfree와 같은 용도

4. initrans : 인덱스된 세그먼트의 데이터 블록에 트랜잭션 엔트리를 위하여 초기에 얼마나 많은 스페이스를 할당될 수 있는지 정의

5. maxtrans : 인덱스된 데이터 블록에 트랜잭션 엔트리를 위하여 초기에 할당할 수 있는 최대 스페이스 수를 정의

6. storage : 스토리지 파라미터로서 작은 영역을 많이 갖는것 보다 많은 영역을 적게 갖는게 좋다.

* 인덱스 없애기

drop index i_emp_city_zip;

* 클러스터된 인덱스

클러스터된 테이블이 만들어지고 나면 데이타 입력 전에 우선 그 크럴스터에 대한 인덱스부터 만들어야 한다.

* 시퀀스 만들기

create sequence s_empno

increment by 1

start with 1

nomaxvalue

nocycle

cache 15;

=> s_empno라는 시퀀스를 만들고 번호들의 시퀀스 리스트는 1의 값부터 시작되며 연속되는 번호마다 1씩 증가한다. 이 시퀀스에는 최대값이 없으므로

그 시퀀스는 초기값으로부터 순환하지 않으며 서버의 퍼포먼드를 위해 메모리에 15개까지의 시퀀스를 미리 만들어둔다

* 시퀀스 확인

select sequence_name, min_value, max_value, increment_by, last_number

from user_sequences;

* 시퀀스 변경하기

alter sequence s_empno

maxvalue 50000

cache 20

cycle;

* 시퀀스의 사용

insert into emp (empno, first_name, last_name)

values (s_empno.nextval, 'thomas', 'smith');

* 시퀀스의 .nextval, .currval등의 의사열을 쓸 수 있는 상황

1. insert문에서 value절

2. select 문의 선택 리스트

3. update명령의 set 절

* 시퀀스의 .nextval, .currval등의 의사열을 쓸 수 있는 상황

1. distinct 절이 있는 select문

2. order by또는 group by절이 있는 select문

3. 연산자 union, minus또는 intersect에서 사용되는 select 문

4. 서브쿼리, 뷰 쿼리, 스냅샷 쿼리

* 뷰(view)를 사용하는 이유

1. 테이블의 사전 정의된 행과 열 세트에 대해서 액세스를 제한함으로써 데이터베이스 보안의 추가적인 단계를 제공

2. 기저 조인과 테이블 구조를 숨김으로써 사용자들에게 데이터 프리젠테이션을 간략하게 제공

3. 기본 테이블의 데이터와는 다른 재생으로 데이터를 표현한다.

* 뷰 만들기

create view emp_view as

select empno, f_name, l_name, deptno

from emp

where deptno=12

with check option constraint emp_cnst;

emp_view뷰는 deptno가 12인 행만을 인용하며 with check option은 뷰가 선택될 수 없는 행의 뷰가 만들어지거나 산출되도록 허용되지 않는 상황에서 insert, update문 같은 제한을 사용해서 뷰를 제작하게 된다. 따라서 deptno가 12인 행만이 뷰로 삽입될 수 있다.

또한 뷰를 만들때 여러 테이블을 조인하여 만들 수도 있다.

제약: 1. 행이 조인을 포함하는 쿼리를 사용해서 정의된 뷰로 삽입되거나 갱신되지 못하기 때문에 위의 예에서는 with check option이 상술되지 못한다.

2. for update절이나 order by절을 사용하지 못한다.

create view force는 테이블이 존재하지 않거나 사용자가 테이블에 대한 권한이 없어도 뷰를 만들게 한다.

반대로 create view noforce는 권한이 있거나 테이블이 존재 하여야만 뷰를 만들수 있다.

* 뷰 교체하기

뷰의 정의를 바꾸려면 뷰를 교체 해야 한다.

create or replace view emp_dept_name as

select f_name, l_name, empno, dept.deptno

from emp, dept

where emp.deptno = 15;

효과:

1. 데이터 사전에 있는 뷰 정의가 갱신된다. 모든 기저 오브젝트는 뷰 교체에 의한 영향을 받지 않는다

2. 원래의 뷰 정의에 있다가 교체된것에 정의되어 있지 않은 것은 드롭된다.

3. 교체된 뷰에 종속된 모든 뷰와 PL/SQL프로그램은 무효로 된다.

* 뷰 사용하기

select f_name, l_name, empno from emp_dept_name;

* 뷰가 사용가능한 절

select, insert, update, comment, lock table

* 뷰의 제한

1. 뷰의 쿼리가 join operation이나 set또는 distinct연산자, group by절, 또는 group함수를 포함하고 있을 때 insert, update 또는 delete연산을 수행하는데 뷰를 사용할 수 없다.

2. with check option 절을 이용하여 뷰를 정의할 경우에는 그 뷰를 사용하여 기저 테이블의 행을 삽입하거나 갱신할 수 없다.

3. default value절 없이 not null열이 정의될 때에는 뷰를 사용 하여 베이스 테이블에 행을 삽입할 수 없다.

4. 뷰가 decode표현식을 사용하여 만들어졌을 경우에는 그 뷰를 사용하여 행을 테이블에 삽입할 수 없다.

5. 뷰의 쿼리는 nextval, currval의사열을 참조할 수 없다.

6. 추가 프로세싱 타임으로 서버의 반응시간을 약간 감소시킨다.

* 뷰 재컴파일

alter view emp_dept compile;

* 스냅샷

마스터 테이블이라고 하는 하나 이상의 테이블 쿼리의 결과를 포함하는 테이블 또는 전형적으로 리모트 노드에 위치하는 뷰. 본질적으로 리모트 데이터베이스의 로컬 카피라고 볼 수 있다. 읽기 전용이며. 갱신이 발생되지 않는다.

일단 스냅샷에 테이터가 캡춰되면 재생 사이클에 의해 재생될때까지 데이터는 변하지 않는다.

또한 내부적으로 스냅샷은 스키마 오브젝트의 집합이다.

* 스냅샷 제작 중에 만들어 지는 오브젝트

1. 스냅샷 쿼리의 결과를 수용하기 위해 리모트 노드에서 베이스 테이블이 만들어짐

2. 베이스 테이블에 대하여 쿼리가 주어질 때 사용될 읽기 전용 뷰가 이 베이스 테이블에서 만들어진다.

3. 주기적인 스냅샷 재생 프로세스를 위하여 로컬 노드에서 마스터 테이블에 뷰가 만들어진다

* 스냅샷 사용 이유

네트웍 통신량을 줄이고 어플리케이션 퍼포먼스를 개선할 수 있다.

* 스냅샷이 많이 사용되는 경우

1. 마스터 테이블이 잘 갱신되지 않지만 많이 액세스될대

2. 많은 리모트 사용자에 의하여 마스터 테이블이 액세스될때

* 스냅샷 설정시 필요한일

스냅샷의 제작, 사용, 재생, 올바른 특권설정

* 스냅샷 종류

1. 단순 스냅샷

단 하나의 테이블을 쿼리

2. 복합 스냅샷

복수의 테이블과 뷰를 쿼리할 수 있다.

* 스냅샷 준비

환경설정 스크립트 실행(catsnap.sql, dbmssnap.sql)

마스터 테이블을 수용하는 데이터베이스와 스냅샷을 수용하는 곳 모두에서 실행해야 함

* 스냅샷 만들기

create snapshot UK_emp

pctfree 15

pctused 55

tablespace user_01

storage(initial 50k

next 50k

pctincrease 10)

refresh fast

start with sysdate

next sysdate +1

as select * from emp@uk

* 스냅샷을 만들기 위해 필요한 특권

create snapshop, create table, create view

* 단순스냅샷은 다음을 포함하면 안된다.

group by, connect by, sub query, join, set 연산자

* 스냅샷의 장단점

단순 스냅샷: 재생이 발생할때 효율적, 쿼리 실행중엔 느려짐

복합 스냅샷: 쿼리 실행중에 효율적, 재생이 발생할때 비효율적

* 스토리지 파라미터 설정

복합 스냅샷이 재생중에 최대의 효과를 내기 위해서 pctfree는 0으로 설정되어야 하고 pctused는 100으로 설정해야 한다.

* 스냅샷 이용하기

select deptno, dept_name from dept

또한 스냅샷에 기초하여 시노님을 만들수도 있다.

create synonym emp for UK_emp;

* 한계

스냅샷은 읽기 전용이므로 update, insert, delete등은 불가능하다.

* 스냅샷 로그

스냅샷에서 재생을 수행하는데 필요한 프로세스의 양과 시간을 줄이는데 사용.

스냅샷 로그로부터 스냅샷을 재생하는 프로세스는 속성재생이라 한다.

스냅샷 로그를 사용하지 않고 스냅샷을 재생하는 것은 완전한 재생이라고 한다.

* 스냅샷 로그 만들기

create snapshot log on UK_emp

tablespace emp_01

storage (initial 20k next 20k pctincrease 25);

* 스냅샷 로그를 만들기 위한 조건

create table권한과 create trigger시스템 특권이 있어야 한다.

* 스냅샷 로그 Truncate하기( 로그가 너무 커지는것 방지)

execute dbms_snapshot.purge_log('master_table',3,'DELETE');

* 스냅샷 인덱스 하기

create index i_uk_emp on snap$UK_emp (deptno);

* 스냅샷 재생하기

오라클이 자동으로 스냅샷을 재생하게 만들려면 다음의 단계를 거치면 된다.

1. create snapshot문에 start with와 next파라미터를 지정한다. start with절은 첫번째 자동 재생 사이클 시간을 설정한다. next절은 자동 재생 사이의 간격을 지정한다.

2. 오라클은 start with와 next파라미터를 평가하여 이 정보를 데이터 사전에 저장한다. 이 파라미터는 장래의 한 시점으로 설정하여야 하며 그 렇지 않으면 에러난다.

create snapshot master_emp

pctfree 15

pctused 25

tablespace user_03

storage(initial 50k next 50k pctincrease 10)

refreshh start with round(sysdate+1) + 9/24

fast next trunc(sysdate, 'MONDAY') + 3/24

as select * from emp

스냅샷이 다음날 오전 9시와 그 후 매주 월요일 오전 3시에 재생될 것이라고 지정한다

* 재생 프로세스의 시간과 모드 변경

alter snapshot part_master mode fast;

* 빠른 스냅샷 재생 조건

1. 스냅샷이 단순 스냅샷이다.

2. 마스터 테이블에 스냅샷 로그가 있다.

3. 스냅샷이 마직막으로 재생되거나 만들어지기 전에 스냅샷 로그가 만들어 졌다.

* 스냅샷과 스냅샷 로그 DROP

drop snapshot UK_emp;

drop snapshot log on master_emp;

스냅샷 드롭위한 권한-> drop any table과 drop any trigger시스템 특권

* 커서란?

커서는 나중에 액세스하기 위하여 특정 문장을 수용할 전용인 메모리 구역을 사용자가 명명할 수 있게 해주는 구조

* implicit cursor

insert, delete, update문등이 사용

* explicit

사용자가 선언하며 복수의 행을 리턴하는 쿼리결과를 처리

* 커서 사용 이유

커서는 복수 행 결과 세트를 한 번에 한 행씩 처리하는데 사용되고 커서는 어떤 행이 현재 액세스되고 있는지 추적하며 이것은 활성세트(쿼리로부터 리턴된 복수행)를 반복해서 처리할 수 있게 해준다

* 커서 사용 단계

1. 커서를 선언

2. 커서를 연다

3. 데이터를 커서에 불러온다

4. 커서를 닫는다

* 명시적(explicit) 커서 선언

선언하기 위해 커서의 이름을 붙이고, 커서를 쿼리와 결합한다.

declare

cursor c_parts is

select * from master_parts;

* 파라미터를 이용하여 선언하기

declare

cursor c_parts (v_part_qty number) is

select * from master_parts where parts_qty > v_part_qty

* 명시적 커서 열기

open c_parts

-> 커서는 그 활성 세트의 첫 행 바로 전으로 초기화 된다.

* 명시적 커서 속성

커서에 대한 유익한 정보를 얻기 위하여 사용

1. %isopen : 커서가 열려 있는지에 대한 불린 값 리턴

begin 
   if c_cursor%isopen then 
       fetch c_cursor into v_order_qty, v_order_price; 
   else 
      open c_cursor; 
   end if; 
end; 

2. %notfound : 가장 최근의 fetch실행에 의해 아무 행도 리턴되지 않으면 true

loop 
   fetch c_cursor into v_ord_qty, v_ord_price; 
   exit when c_cursor%notfound 
end loop; 
end; 

3. %found : 가장 최근의 fetch가 행을 리턴하지 않을 때까지 True로 평가한다.

%notfound의 논리적 반대이다.

4. %rowcount : fetch에 의하여 리턴된 행의 총수가 되는 숫자 속성

* 명시적 커서 사용하기

커서는 이미 불러온 fetch정보를 다시 볼수 없기 때문에 다시 읽으려면 커서를 닫고 나서 처음부터 fetch를 다시 해야 한다.

* 명시적 커서 닫기

close c_orders;

* 암시적 커서(implicit cursor)

모든 sql문은 암시적 커서를 자동적으로 만든다.

* 패키지 커서

오라클은 커서의 명세가 패키지에 저장될 수 있도록 해준다. 이 명세에서는 러턴절이 사용되어야 한다. 커서에 대한 이 어프로치는 커서 명세를 바꾸지 않고도 커서를 바꿀수 있게 해준다.

create package emp_monthly as

cursor c_emp_sal return emp%rowtype;

emp emp_monthly;

create package body emp_monthly as

cursor c_emp_sal return emp%rowtype;

select emp_id, emp_salary from master_emp where hire_date > '01-JAN-1986';

end emp_monthly;

* 커서 for 루프

커서 for루프는 다음을 수행함으로써 코드화 작업을 단순화 할 수 있다.

1. 루프가 초기화될때 암시적 open이 실행된다.

2. 루프의 각 반복을 위하여 암시적 fetch가 실행된다.

3. 루프가 중지될 때 암시적 close가 실행된다.

begin 
  for order_rec in c_cursor loop 
      v_order_total := v_order_total+ ((order_rec.qty * order_rec.qty)*.95); 
      insert into master_order (part_id, order_total) values 
         (order_rec.part_id, v_order_total); 
  end loop; 
  commit 
end; 

* 커서 앨리어스

쿼리 문이 표현식을 갖고 있을때 참조할때 문제가 생기는 경우 해결 방안

* 커서 퍼포먼스

커서 내부에서 실행 호출의 속도는 파리미터 cursor_space_for_time의 값을 조정하여 개선한다.

* 데이터 무결성

데이터 무결성은 테이블에 있는 열에 대하여 유효한 값을 제한하는 규칙을 정의한다

* 데이터 무결성 사용 이유

데이터 무결성 제한은 데이터베이스 테이블에 무효한 데이터 입력을 막는데 사용

* 데이터 무결성 이용 방법

데이터베이스의 테이블에서 정의됨

뷰와 테이블 시노님은 기저 베이스 테이블에서 정의된 무결성 제한에 따른다.

* 무결성 사용하는 곳

어플리케이션 보다 데이터 베이스 차원에서 하는 것이 효과적이다.

* 무결성 제한의 종류

1. 널형

2. 고유형

3. 주키 형

4. 참조형

5. 점검형

무결성 제한을 설정하기 위하여 create table 이나 alter table절에서 constraint절을 포함시킬 수 있다. 구문 포맷은 다음과 같다.

constraint constraint_name constraint_type

* 참조형 무결성 제한

서로 다른 테이블의 열 사이의관계를 지배하는 규칙을 정한다

alter table address

add constraint fk_state_abbr

foreign key (state_abbr)

references state_abbr (state_abbr)

* 외부 키의 삭제를 캐스케이드하기

on delete cascade는 자식 테이블에종속 행을 갖고 있는 부모 테이블에서 참조된 키 값을 삭제할 수 있게 해주며, 부모 테이블의 삭제가 발생할때 오라클은 자동적으로 그 자식 테이블에 있는 해당 행들을 삭제한다.

* 점검형 무결성 제한

check 무결성 제한은 참이 되어야 하는 조건을 명시적으로 정의한다.

제약: 서브쿼리를 포함할 수 없다. sysdate, uid, user, userenv함수를 포함불가능 curral, netval, leve, rownum의사열을 사용할 수 없다.

create table office_location

(office id number(5),

city varchar2(50)

constraint check_city

check(city in ("Dallas","Denver","Indianapolis"),

state varchar2(50));

* 무결성 제한 디스플레이하기

테이블:USER_CONSTRAINTS, USER_CONS_COLUMN

select constraint_name, constraint_type

from user_constraints

where table_name='mst_parts';

* 무결성 제한 유효화,무효화하기

alter table mst_parts

disable constraint s_parts_code_fk;

특히 Loader등을 통해 데이터를 로딩하는 동안과 프로세스를 임포트하고 엑스포트하는 동안이다.

* 무결성 제한의 시스템 뷰

1. all_constraints : 액세스 가능한 모든 테이블을 위한 제한 정의

2. all_cons_columns : 제한 정의에 있는 액세스 가능한 열에 대한 정보

3. user_constraints : 사용자의 테이블에 대한 제한 정의

4. user_cons_columns : 사용자가 소유하는 제한 정의에 있는 열에 대한 정보

5. dba_constraints : 데이타베이스의 모든 테이블에 대한 제한 정의

6. dba_cons_columns : 제한 정의에 있는 모든 열에 대한 정보

* 무결성 제한 drop하기

alter table mst_parts

drop constraints s_parts_code_라;

* 트랜잭션 이란?

하나의 논리적 작업 단위를 구성하는 하나 이상의 sql문

* 트랜잭션의 효과를 데이타베이스에 확립시킴=> commit

* 트랜잭션의 효과를 데이타베이스에서 제거=> rollback

* 트랜잭션 사용 이유

사용자와 오라클 서버, 애플리케이션 개발자, 데이터베이스 개발자에게 데이터 일치성과 데이터 동시발생을 보장

* set transaction

1. 트랜잭션을 읽기 전용 트랜잭션이나 읽기-쓰기 트랜잭션으로 설정한다.

2. 현재 읽기-쓰기 트랜잭션을 지정된 롤백 세그먼트에 대입한다.

* 트랜잭션 커밋(commit)하기

커밋 하기 전까지 트랜잭션은 다음과 같은 특징을 갖는다.

1. DML은 데이터 베이스 버퍼에만 영향을 미친다. 따라서 변경사항이 백아웃 될수있다.

2. 롤백 세그먼트 버퍼가 서버에 만들어 진다.

3. 트랜잭션의 소유자는 select문을 사용하여 트랜잭션의 효과를 볼 수 있다.

4. 데이터베이스의 다른 용법으로는 트랜잭션의 효과를 볼 수 없다.

5. 영향을 받은 행들을 lock되며 다른 사용자들들 영향을 받은 행 안에 있는 데이터를 변경할 수 없다.

커밋이 실행되면 다음의 단계들이 발생한다.

1. 영향받은 행에 수용된 lock들이 풀린다.

2. 트랜잭션이 완료된 것으로 표시된다.

3. 서버의 내부 트랜잭션 테이블이 시스템 변경 번호를 생성하고, 이 번호를 트랜잭션에 대입하고, 번호들을 모두 테이블에 저장한다.

* commit과 함께 comment절을 사용하여 텍스트 스트링을 트랜잭션 ID와 함께 데이터 사전에 넣을수 있다.

* Two-Phase Commits

분산 commit을 수행(분산 데이타베이스 환경에서)

* 트랜잭션 롤백하기

트랜잭션을 롤백한다는 것은 현재 트랜잭션이 행한 모든 변경을 해제한다는 것을 의미 전체 트랜잭션의 롤백을 실행하려면 sqlplus상에서 rollback명령을 실행한다.

다른 방법으로 rollback to savepoint를 사용하여 일부만 rollback할 수 있다.

* 트랜잭션 전체 롤백할때의 단계

1. 현재 트랜잭션의 의하여 이루어진 모든 변경은 해당 롤백 세그먼트를 사용하여 해제할 수 있다.

2. 트랜잭션에 의하여 야기된 행에 있는 모든 lock이 풀린다.

3. 트랜잭션이 끝난다.

* 트랜잭션을 savepoint에 롤백할 때에는 다음의 단계가 발생한다.

1. 마지막 savepoint가 롤백된 후, sql문만 실행된다.

2. 롤백 명령에서 지정된 마지막 저장점(savepoint)이 보존되지만 그 저장점 이후의 다른 모든 저장점은 그 데이타 베이스로부터 제거된다.

3. 지정된 저장점 이후에 설정된 모든 lock은 풀린다.

4. 트랜잭션이 아직 활동 중이며 계속 될 수 있다.

* 저장점(savepoint) 만들기

savepoint master_credit;

* 저장점을 이용해 롤백하기

rollback to savepoint master_credit;

rollback to master_credit;

* 롤백 세그먼트

set transaction

use rollback segment rbs_mst_credit;

* 롤백 세그먼트의 크기는 롤백 세그먼트의 스토리지 파라미터에 의해정의됨

만약 애플리케이션이 매우 짧은 트랜잭션을 실행하고 있다면 롤백세그먼트는 항상 메모리에 캐시될 수 있을 정도로 작아야 할 것이다. 이렇게 되면 퍼포먼스가 향상된다.

* 롤백 세그먼트를 위해서는 높은 maxextents값을 설정해야 한다.

* 데이터 locking

2가지 레벨: 독점, 공유

* 이산 트랜잭션 관리(Discrete Transaction Management)

* 패키지란?

관련된 스키마 오브젝트의 캡슐화된 집합. 이 오브젝트는 프로시저와 함수, 변수 상수, 커서, 그리고 예외(exception)를 포함할 수 있다.

* 패키지 구성 컴포넌트

1. 명세: 타입과 변수, 상수, 예외, 사용할 수 있는 서브프로그램을 선언

2. 본체: 커서와 함수, 그리고 프로시저를 완전히 정의하고 그 명세를 구현한다

* 패키지 사용 잇점

1. 어플리케이션 개발을 효율적으로 모듈에 조직하는 능력.

2. 패키지는 특권(privileage)를 보다 효율적으로 허가할 수 있게 해준다.

3. 패키지의 퍼블릭 변수와 커서는 그 세션동안 지속된다. 그러므로 이 환경을 실행하는 모든 커서와 프로시저가 패키지의 퍼블릭 변수와 커서들을 공유할수 있다.

4. 패키지는 프로시저와 함수에서의 오버로딩을 수행할 수 있게 해준다.

5. 패키지는 한 번에 여러 오브젝트를 로드함으로써 퍼포먼스를 개선한다.

* 패키지 사용법

SQL*DBA나 SQL*PLUS를 사용

* 패키지에 필요한 힌트

1. 기존의 오라클함수와 중복되는 패키지를 쓰는 것을 피한다.

2. 재사용에 대비해 간단하고 범용적으로 만든다.

3. 어플리케이션을 설계한 후 패키지 본체를 설계한다. 모든 사용자가 볼 수 있도록 하고 싶은 오브젝트만 패키지 명세에 넣는다.

4. 너무 많은 아이템을, 특히 컴파일링을 필요로 하는 아이템을 패키지 명세에 넣는 것을 피한다.

* 패키지 명세

패키지 명세에는 패키지 이름의 퍼블릭 선언과 모든 인수의 데이터 타입과 이름이 포함된다.

* 패키지 명세 선언

create package inv_pck_spec as

function inv_count(qty number, part_nbr varchar2(15))

return number;

procedure inv_adjust(qty number);

end inv_pck_spec

* 본체가 없는 패키지 선언

create package inv_constings is

type inv_rec is record

(part_name varchar2(30),

part_price number,

part_cost number);

price number;

qty number;

no_cost exception;

cost_or exception;

END inv_costings;

* 패키지 만들기

1. 명세 만들기

create or replace package inv_pcg_spec as

function inv_count(qty integer, part_nbr varchar2(15))

return integer;

procedure inv_adjust(qty integer);

end inv_pck_spec;

위의 예에서 create or replace를 썼는데 이렇게 하면 기존 패키지와 관련된 허가권한은 그대로 유지된다.

2. 본체 만들기

create or replace package body inv_control is

function inv_count

(qty integer,

part_nbr varchar2(15))

return integer is;

new_qty:= qty*6;

insert into mst_inv values

(new_qty, part_nbr);

return(new_qty);

end inv_count;

procedure inv_adjust(qty integer);

begin

delete from user_01.mst_inv

where inv_qty<10000;

end;

begin -- package initialization begins here

insert into inv_audit values

(sysdate, user);

end inv_control;

* 패키지 서브프로그램 호출

1. 오라클이 사용자 액세스를 확인한다.

2. 프로시저 유효성을 확인한다.

3. 패키지 서브프로그램이 실행된다.

* 패키지 서브프로그램 참조

package_name.type_name

package_name.object_name

package_name.sunprogram_name

* 패키지를 위한 시노님

1. 패키지의 이름과 소유자의 정체를 감출수 있게 해준다.

2. 리모트에 저장된 패키지를 위하여 위치의 투명성을 제공해 준다.

* 패키지 재컴파일

본체

alter package inventory_pkg compile body;

alter package inventory_pkg compile package;

모든 패키지는 오라클 유틸리티 dbms_utility를 사용하여 재컴파일할 수 있다.

execute dbms_utility,compile_all;

* 패키지 drop하기

drop package body inventory;

drop package inventory;

* 전용패키지 오브젝트와 공용 패키지 오브젝트

패키지 본체 안에서 선언된 오브젝트는 그 패키지 안에서만 사용할 수 있다.

* 패키지 상태

유효 아니면 무효상태이다.

* 오버로딩

오라클은 동일한 패키지에 있는 여러 프로시저가 동일한 이름을 갖는 것을 허용한다

* 패키지 정보 리스팅

all_errors

all_source

dba_errors

dba_object_size

dba_source

user_errors

user_source

user_object_size

* 트리거란?

결합된 테이블에 대하여 insert, update, delete명령이 발생될때 implicit하게 생행되는 프로시저

트리거는 하나의 단위로서 실행되는 sql과 pl/sql문을 포함할 수 있으며 다른 프로시저와 트리거를 호출할 수 있다.

트리거는 insert, update, delete명령 전후에 호출되며, 제작자는 DML문과 관련하여 언제 실행될 것인지를 지정한다

트리거는 테이블의 뷰나 시노님에서가 아니라 테이블에서 정의되지만 뷰의 베이스 테이블에 있는 그 뷰에 대하여 DML문이 발생될 때도 실행된다

* 트리거 사용 이유

1. 파생 열 값을 자동적으로 생성한다.

2. 보안 허가와 제한을 집행한다.

3. 투명 이벤트 로깅을 제공한다.

4. 테이블 액세스에 대한 통계를 수집한다.

5. 오라클 내부 오디팅 시스템을 보충하는데도 사용할 수 있다.

6. 분산 데이타베이스의 서로 다른 노드에 위치한 복제 테이블을 동기적으로 유지하는 데에도 사용 할 수 있다. 스냅샷에 이런 방식으로 트리거를 사용하는 것이 한 예이다.

* 트리거를 위한 지침

1. 미리 정의된 다른 연산이 실행될 때마다 특정 연산을 수행하고 싶을 때에만 트리거를 사용한다.

2. 트리거링 문을 위하여 실행되어야 할 중앙화된 글로벌 연산을 위하여 트리거를 사용한다.

3. 반복적인 트리거를 만들지 않도록 조심하여야 한다.

4. 코드를 되도록 작게 하기 위하여 트리거를 만드는 것을 삼가야 한다.

5. sql문이 실행될때 프로세스되는 행의 순서에 의존하여 트리거를 만들면 안된다

* 트리거 파트(요소)

1. 트리거 문: 어떤 sql문이 오라클로 하여금 트리거를 지시하게 만드는지 지정한다. 이 트리거문은 insert, update, delete문이 될 수 있다.

2. 트리거 제한: 지시할 트리거를 위하여 참이 되어야 할 조건을 지정. 이 조건은 pl/sql 조건이 아니라 sql조건이어야 한다. 그 조건은 when절에 상주할 수 있다.

3. 트리거 활동: 이것은 트리거가 지시될 때 오라클 싱행하는 pl/sql블록을 지정한다

* 트리거 타입

트리거 활동이 실행되는 횟수를 지정한다.

1. 행 트리거: 트리거 문에 의하여 테이블이 영향을 받을 때마다 실행된다.

예를 들어 update문이 실행되고 30행을 갱신할 경우 이 트리거가 30회 지시된다.

2. 문장 트리거: 트리거 문을 위하여 단 한번 지시된다.

예를 들어 update문이 실행되고 30행을 갱신할 경우 이 트리거는 단 1번 실행된다

* 트리거 타이밍

언제 시작할 지를 지정한다.

before트리거는 트리거링 문 앞에서 실행된다. 다음을 위해 실행된다.

1. insert나 update문의 트리거링을 끝내기 전에 열 값을 끌어내기 위하여

2. 트리거 활동을 실행해야 할 것인지 여부를 결정하기 위하여

after트리거는 트리거 문이 실행된 후 트리거 활동을 실행한다. 다음을 위해 시행됨

1. 트리거 활동 이전에 트리거링 문을 끝내고 싶을때

2. before트리거 활동 외에 추가의논리를 시행하기 위하여

* 트리거 만들기

create trigger 'trigger command'

trigger command: before, after, delete, insert, update, of, on, for each row

when, or replace

create trigger parts_delete

after delete on mst_parts

for each row

when(part_price < 100)

declare

.

.

.

end;

* 트리거 유.무효화 하기

alter trigger t_inv_count disable

* 트리거 실행

sql문은 트리거를 4개까지 실행할 수 있다. 이 4개의 트리거는 before row, after row, before statement, after statement트리거 이다.

* 트리거 변경

트리거를 명시적으로 변경할 수는 없다.

create trigger or replace를 사용하여야 한다.

또는 트리거를 drop했다가 다시 만들어야 한다.

* 트리거 재컴파일하기

기존 트리거의 재컴파일을 집행하려면 alter trigger명령을 사용한다. 트리거는 재컴파일될 때 유효화될 수도 있고 무효화될 수도 있다. alter triggfer명령은 기존 트리거의 정의를 변경하지 않는다.

* 스냅샷 로그 트리거

테이블을 위하여 스냅샷 로그가 만들어질때 오라클은 그 테이블에 after row트리거를 명시적으로 만든다. 그러므로 사용자가 정의한 after row 트리거는 이 동일한 테이블에서는 만들어질 수 없다.

* 내장 프로시저(stored procedure)

특정 작업을 수행하는 논리적으로 그룹지어진 sql과 pl/sql문의 셋이다.

선언파트와 실행파트로 구성되어 있다.

* 내장 프로시저 사용 이유

1. pl/sql은 특정 요구에 맞도록 프로시저를 맞출수 있게 해준다.

2. 이 프로시저들은 모듈적이다. 하나의 프로그램을 관리할 수 있는 잘 정의된 유니트로 구성되어 있다.

3. 프로시저들은 데이타베이스에 내장되기 때문에 재사용할 수 있다. 일단 유효화되면, 네트워크를 가로질러 재컴파일하거나 분포하지 않고도 반복해서 사용할 수 있다.

4. 데이타의 보완성을 개선한다.

5. 공유메모리 자원을 활용함으로써 메모리를 개선할 수 있다.

* 내장 프로시저 사용 필요조건

데이터베이스 관리자는 catproc.sql스크립트를 실행할 책임이 있다.

* 내장 프로시저 만들기

create procedure명령은 독립형 프로시저를 만든다.

* create문 에서 사용할 수 있는 파라미터의 리스트

1. in : 호출되는 서브 프로그램에 값을 패스해야 한다고 지정한다.

2. out: 호출하는 프로시저에 값을 리턴한다는 것을 지정한다.

3. inout : 위의 둘다

4. or release : 프로시저가 존재할 경우 다시 만든다.

create procedure user_o1.parts (part_id number, qty number)

as begin

update journal

set journal.qty = journal.qty + qty

where journal_id = part_id;

end;

* return문

프로시저에서 return문은 표현식을 포함할 수 없다.

* 내장프로시저 재컴파일하기

alter procedure로 가능하며, 독립형 내장 프로시저에서만 사용해야 하며 패키지의 일부인 프로시저에서 사용해서는 안된다.

* 내장 프로시저 호출하기

parts_sum(qty, wip_nbr);

execute parts_sum(qty, wip_nbr);

* 임포트, 익스포트를 사용하는 이유

1. 데이터 보관하기

2. 오라클의 새로운 릴리즈로업그레이드하기

3. 오라클 데이터베이스 간에 데이터 이동하기

4. 테이블스페이스 drop하기

5. 데이터베이스 defragment하기

* 익스포트

모드: 테이블 모드, 사용자 모드

테이블 모드

1. 테이블 데이터

2. 테이블 정의

3. 소유자의 테이블 허가

4. 테이블 제한

5. 테이블 트리거

사용자모드: 테이블 모드를 포함하여 다음의 것들

1. 뷰

2. 스냅샷

3. 클러스터

4. 데이타베이스 링크

5. 시퀀스

6. 전용 시노님

7. 공용 시노님

* 익스포트 준비

1. 저장공간 확인

2. 오라클 데이타베이스에 대한 create session시스템 특권이 있어야 한다

* 실행

exp를 타이핑 하고 유틸리티를 구동한다.

* 사용방법 3가지

1. 명령라인에서 그 파라미터 파일의 이름과 함께 exp명령을 입력

exp username/password parfile=filename

2. 명령라인에서 exp명령과 함께 그 뒤에 익스포트의 실행을 콘트롤하는 파리미터를 쓴다.

exp username/password tables=(emp,master_dept) grants=y

3. 명령라인에서 사용자명과 패스워드를 입력한다. (대화형으로 익스포트)

exp username/password

* 익스포트 파라미터

1. buffer : 데이터 행들을 불러오는데 사용되는 버퍼의 크기

정확한 버퍼크기 계산 공식: rows_in_arrary*maximin_row_size

2. file : 출력파일의 이름 초기값은 expdat.dmp

3. grants : 허가를 익스포트 할 것인지를 나타낸다. (y/n) 디폴트는 y

4. indexes : 인덱스도 익스포트할 건가?

5. rows : 테이블로부터 데이터 행을 익스포트 할 건인지..

6. constraints : 제한을 익스포트할 건가?

7. compress: 압축할건가?

8. full : 완전한 풀 익스포트인가?

9. owner: 익스포트할 사용자명. 초기값은 없다.

10. tables: 익스포트할 테이블 이름.

11. recordlength: 출력파일의 바이트 길이

12. inctype : 증분의 타입: complete, cumulative, incremental: 초기값 없음

13. parfile : 파라미터 파일의 이름

14. log : 로그파일의 파일명

15. statistics : 데이터베이스 옵티마이저 통계의 타입 - estimate, compute, none 초기값은 none

16. consistent : 이 파라미터는 익스포트 중에 다른 애플리케이션이 데이터베이스를 갱신하고 있을때 편리하다

* 익스포트 시간을 아낄려면

1. 필요한 테이블만 익스포트한다.

2. 일치된 것으로 남아 있을 필요가 있는 테이블들을 함께 익스포트한다.

* 익스포트 시퀀스

export나 import 중에는 시퀀스 번호를 액세스 하지 않는 것이 좋다.

* 익스포트 온라인 헬프

오라클은 온라인 헬프를 제공한다.

exp help=y

* 임포트 할때 수행 되는 순서

1. 테이블 정의 - 우선 테이블이 만들어 진다.

2. 테이블 데이터 - 새로 만들어진 테이블에 데이터가 로드된다.

3. 테이블 인덱스 - 데이터가 로드된 후 인덱스가 만들어 진다.

4. 통합된 제한과 트리거- 마지막으로 트리거가 임포트 되고 제한이 유효화 된다.

* 스냅샷 익스포트/임포트

스냅샷은 마스터 테이블과 옵션 스냅샷 로그, 마스터 테이블 트리거, 스냅샷 자체

다음과 같은 규칙에 따라서 임포트/익스포트 된다.

1. 마스터 테이블이 이미 존재하고 있을 경우 임포트 데이터는 그 스냅샷로그에 기록된다. 만약 존재하지 않으면 새로운 마스터 테이블이 임포트된다.

2. 오라클은 마스터 테이블이 만들어지고 로드된 후 모든 트리거를 임포트한다

3. 임포트 후에 스냅샷 로그는 비게된다. 그러므로 속성 재생을 시도하기전에 완전한 재생을 실행하여야 한다.

* 내장된 프로시저와 함수 그리고 패키지의 익스포트/임포트

내장된 프로시저와 함수 그리고 패키지가 임포트될 때 그것들은 그 본래의 시간 스탬프를 유지한다.

* 임포트 파라미터

buffer, file, grants, indexes, rows, full, tables, recordlength, inctype, parfile

log, destroy, commit, show(임포트파일과 파일내용 리스트 여부)

* 임포트 유틸리티 실행

1. imp username/password parfile=filename

2. imp username/password tables=(emp, master_dept) grants=y

3. imp username/password

* 임포트 온라인 헬프

imp help=y

* SQL*Loader란?

외부 파일로부터 오라클 데이타베이스 테이블에 데이터를 로드하는 오라클 툴이다. 다양한 포맷의 파일을 로드할 수 있고, 필터링도 수행할 수 있다.

복수의 테이블에 로드할 수 있으며 자세한 로그를 남긴다.

* SQL*Loader를 사용하는 이유

1. 서로다른 데이터 타입의 여러 데이터 파일로부터 데이터를 여러 오라클 테이블에 자동으로 로드한다

2. 선별해서 특정 레코드를 로드할 수 있도록 데이터를 필터링한다.

3. 특정 열을 위하여 자동적으로 고유 시퀀스 번호를 생성할 수 있다.

4. 에러 교정등을 제공한다.

5. 데이터를 데이타베이스에 넣기 전에 sql함수와 함께 입력 데이타를 조작할 수 있다.

* SQL*Loader 사용 단계

1. 입력 데이터 소스를 결정한다.

2. 콘트롤 파일을 만든다.

3. 로딩 프로세스를 결정한다

4. 결과를 확증한다.

* 입력 데이타

1. varchar

2. date

3. integer external

4. float external

5. decimal external

6. zoned

7. double

8. graphic

9. raw

10. vargraphic

11. smallint

12. byteint

* 콘트롤 파일이 제공하는 정보

1. 데이터가 로드될 위치(외부 파일 이름이나 실제 데이터 자체)

2. 해당 오라클 테이블 열에 입력 데이터의 매핑. 이때 어떤 데이터를 어떤 테이블의 어떤 열에 넣을것인가 결정

3. 데이터를 로드하기 위한 명세. 특정 테이블 열을 위한 데이터를 필터링하고 포맷하고 만들 논리를 포함

예1)

LOAD DATA

INFILE filename

INSERT

INTO TABLE table_name

(field_name1 POSITION(start:end) CHAR,

field_name2 POSITION(start:end) CHAR)

실예1)

LOAD DATA

INFILE 'DISK1:EMP_DEPT.DAT'

INSERT

INTO TABLE EMP

(EMP_ID SEQUENCE (MAX,1),

F_NAME POSITION(01:35) CHAR,

L_NAME POSITION(36:75) CHAR,

EMP_NBR POSITION(76:85) CHAR,

START_DATE SYSDATE)

INTO TABLE DEPT

WHEN DEPT !=' '

(EMP_NBR POSITION(76:85) CHAR,

DEPT POSITION(86:95) INTEGER EXTERNAL)

* SQL*Loader에서 SQL연산자

insert문의 values절에서 다양한 sql연산자가 사용가능하다.

sql문자열 뒤에 날짜 마스크가 평가된다.

field1 DATE 'mm-dd-yyyy' "RTRIM(:field1)"

job_title필드를 대문자로 변환한다.

job_title POSITION (34:55) CHAR "UPPER(:job_title)"

* SQL*Loader실행

sqlload contro=case1.ctl userid=userid/password

* 인수들

log : 로그 파일이 로딩 프로세스에 대한 정보를 저장하도록 지명

bad : sql*loader가 만든 불량 파일이 로딩 중에 야기된 레코드를 저장하도록 지명

discard : sql*loader가 만든 옵션 로그 폐기파일이 테이블에 삽입되었지만 에러를 포함하지 않은 레코드를 저장하도록 지명

discardmax : 로드를 종결하기 전에 허용된 discard레코드의 수를 지정

errors : 로드를 종결하기 전에 허용된 삽입 에러의 수를 지정

skip : 로드되어서는 안될 파일의 처음부터 논리적 레코드의 수를 지정

rows : 바인드 배열에 있는 행의 수를 지정한다.

* SQL*Loader출력파일

1. 로그파일

2. 실패 레코드 파일(bad record file)

3. 폐기 파일(discard file)

* SQL*DBA란? 사용 이유는?

오라클 데이타베이스의 운영을 효과적이고 효율적으로 관리할 수 있게 해주는 유틸리티

사용이유

1. 데이터베이스 인스턴스를 기동하고 중지함

2. 데이터베이스를 위한 새로운 사용자를 만듬

3. sql과 pl/sql문을 실행

4. 데이터베이스 퍼포먼스 통계를 모니터링

* 인터페이스

메뉴중심 대화형 모드

sqldba mode=screen

대화형 라인모드

sqldba mode=line

* sql*dba 명령들

1. archive log : 온라인 redo로그파일의 보관을 기동하고 중지함

파라미터: list, start, stop, netx, all, to

archive log parameter

2. connect : 지정된 사용자명을 사용하여 데이타베이스에 접근

connect username/password instance/database

3. describe : 테이블이나 내장프로시저, 또는 함수를 기술

4. disconnect : 사용자의 접속을 끊는다

5. execute : pl/sql문을 실행한다.

excute PL/SQL statement

6. exit : 무조건 현재 sql*dba세션을 떠난다.

7. host : 호스트의 명령을 사용한다.

8. print : 변수값을 프린트 한다.

9. recover : 하나 이상의 테이블스페이스나 데이터베이스파일, 또는 전체 데이터베이스에서 미디어 복구를 수행한다.

파라미터: database:테이터베이스 전체 복구(until time절 사용가능)

tablespace tablespace_name : 복구할 테이블 스페이스를 지정

datafile : 복구할 데이터 파일의 이름을 지정

10. remark : 스크립트 파일에 코멘트를 만드는데 사용

11. set : sql*dba세션의 특징을 설명하거나 변경

12. shutdown : 실행중인 오라클 인스턴스를 셧다운 시킨다.

파라미터:

abort: 가능한 가장 빠른 방법으로 셧다운과 함께 처리된다.이것은 인스턴스를 셧다운하지만 트랜잭션의 완료를 기다리지 않는다. 또한 데이터베이스를 닫거나 디스마운트한다.

immediate: 현재 호출이 완료되기를 기다리지 않고 데이터베이스를 닫고 디스마운트한다.

normal: 모든 호출이 완료되기를 기다리고 정상적인 방법으로 그 인스턴스를 닫느다.

13. spool : 모든 출력이 지정된 파일에 이코되게 만든다.

14. startup : 오라클 인스턴스를 기동한다.

파라미터:

exclusive : 그 데이터베이스가 현재 인스턴스에 의해서만 마운트되고 열릴수 있음을 의미

force : 현재 실행중인 인스턴스가 있을 경우엔 그 인스턴스를 재기동하기 전에 폐기(abort)옵션을 사용하여 현재 인스턴스를 셧다운 시킨다.

mount : 데이터베이스를 마운트하지만 열지는 않는다

15. variable : 현재 세션에서 사용할 바인드 변수를 선언한다.

* 사용자 만들기

사용자를 만들때 지정해야 할 것들

1. 사용자명

2. 초기값 테이블 스페이스

3. 임시 세그먼트 테이블 스페이스

4. 테이블스페이스 쿼터

5. 프로파일

create user user_01

identified by D82KLW9

default tablespace ts_101

temporary tablespace ts_temp

quota 5m on ts_101

quota 5m on ts_temp

quota 3m system

profile clerk;

이렇게 만들어진 사용자일지라도 connect 권한이 주어지지 않으면 데이타베이스에 연결할 수 없다.

암호 바꾸기

alter user tom_01

identified by 2JAW3HZ;

* 사용자 자원 관리하기

create profile을 통하여 사용자 자원을 관리하게된다.

프로파일을 만들때 다음의 자원 제한을 명시적으로 설정한다.

1. session_per_user : 사용자를 위한 동시발생 수를 제한

2. cpu_per_session : 하나의 세션을 위한 CPU시간 1/100 단위

3. cpu_per_call : 하나의 호출을 위한 cpu시간을 제한

4. connect_time : 한 세션의 총 소요시간을 제한

5. idle_time : 비 활성 최대 지속시간을 정의

6. logical_read_per_session : 한 세션에서 읽어들이는 데이터블록의 수를 제한

7. logical_reads_per_session : sql문을 처리하기 위한 호출에서 읽어들이는 데이터 블록수 제한

8. private_sga : 하나의 세션이 시스템 글로벌 구역에 예비할 수 있는 전용 스페이스의 양을 제한

9. composite_limit : 세션당 총 자원 코스트를 제한 이것은 sessions_per_user, connect_time, logical_reads_per_session, private_sga등을 제한한다. 이들 자원과 결합된 코스트를 정의하려면 sql명령 alter resource cost를 사용한다.

* 프로파일 만들기 예

create profile clerk limit

sessions_per_user 1

connect_time 560

cpu_per_call unlimited

idle_time 15;

alter문을 통하여 변경도 간능하다.

* 사용자 관리를 위한 데이터사전 뷰

포함되는 정보들

1. 데이터벵이스에 있는 사용자들의 리스트

2. 테이블과 크러스터, 그리고 인덱스를 위한 각 사용자의 초기 테이블스페이스

3. 각 현재 세션을 위한 메모리 사용

4. 임시 세그먼트를 위한 각 사용자의 테이블스페이스

5. 각 사용자를 위한 스페이스 쿼터

6. 각 사용자에 대입된 프로파일과 자원 제한

7. 각 애플리케이션 시스템 자원에 할당된 코스트

데이터사전뷰

all_users

user_users

dba_user

user_ts_quotas

dba_ts_quotas

user_resource_limits

dba_profiles

resource_cost

* 라이센싱

license_max_sessions

license_max_warning

license_max_users

데이터사전 뷰

v$license

select sessions_max s_max,

session_warning s_warn,

session_current s_curr,

session_highwater s_high,

users_max u_max

from v$license;

* 롤(Roles)이란?

롤은 개별 사용자나 다른 롤에 주어진 관련 권한 지명 그룹이다.

* 롤을 사용하는 이유

1. 단순한 특권관리

2. 개별적 동적 특권관리

롤의 특권이 수정될때 이 변경은 자동적으로 그 롤이 주어진 사용자에게 주어진다.

3. 애플리케이션이 지정하는 보안

* 롤 만들기

create role clerk

identified by Y8DS20J;

* 시스템이 정의하는 롤

오라클은 다섯개의 미리 정의된 롤을 오라클 서버에 제공한다

1. connect: alter session, create cluster, create database link,

create sequence, create session, create synonym, create table,

create view

2. Resource: create cluster, create procedure, create sequence,

create table, create trigger

3. DBA: admin옵션과 함께 모든 시스템 특권

4. Exp_full_database : 테이블 SYS.INCVID와 SYS.INCFIL그리고

SYS.INCEXP에서 select any table, back up_any_table, insert, delete, update

5. Imp_full_database : become user, writedown(Trusted 오라클 서버에서만)

* 롤 변경하기

롤을 유효화하는데 필요한 암호, 롤이 유효화될 때 그 롤을 확인할 것인지 여부.

alter role clerk identified by JL10C3Q

* 사용자의 롤 바꾸기

alter user tom_01

default role all except dba;

* 롤 drop

drop role clerk;

롤 부여하기

grant manager to NT9K3BH with admin option;

-> admin옵션을 가진 사용자 NT9K3BH에게 롤 manager를 부여한다.

* 롤 취소하기

revoke clerk from tom_01;

* 운영체계 허가와 취소

세션이 만들어질때 데이터베이스가 운영체계를 이용하여 사용자의 역할을 관리하려면 초기화변수 os_roles를 True로 설정하고 그 인스턴스를 재기동 하여야 한다.

특권(Privileges)

* 특권(Privileges)이란?

활동을 실행하거나 다른 사용자의 오브젝트에 액세스하는 것을 허가하는 것

특권엔 시스템 특권과 오브젝트 특권의 두가지.

시스템특권은 사용자가 일정한 타입의 오브젝트에서 활동을 수행할 수 있게 해주는반면 오브젝트 특권은 사용자가 특정 오브젝트에서 활동을 수행하도록 허가한다

* 특권 사용 이유

데이터베이스 보안의 가장 기초적인 통제 가운데 하나.

특권은 데이터베이스 관리자로 하여금 적당한 레벨의 보안을 데이터베이스 오브젝트와 사용자에게 할당할 수 있게 해준다.

* 특권을 사용하는 법

1. 시스템 특권

80여가지가 있다.

분석(모든 테이블, 인덱스 또는 클러스터)

. analyze any

오디트(데이터베이스에 있는 모든 스키마)

. audit any

. audit system

클러스터

. create cluster

. create any cluster

. alter any cluster

. drop any cluster

데이터베이스

. alter database

데이터베이스 링크

. create database link

인덱스

. create any index

. alter any index

. drop any index

기타 등등

* 시스템 특권 허가하기

grant create session to kabin, finance;

위 문장은 시스템 특권을 사용자 kabin과 finance롤에게 준다

* 시스템 특권 취소하기

revoke all from robert_c

* 오브젝트 특권

오브젝트 특권은 테이블이나 패키지 또는 뷰와 같은 특정 오브젝트에서 활동을 수행할 수 있는 허가이다.

* Public 사용자 그룹

사용자 그룹 public에 부여된 모든 롤은 모든 데이터베이스 사용자가 액세스할 수 있다.

* 특권정보 리스트

all_col_privs

user_col_privs

dba_col_privs

dba_sys_privs

all_col_privs_made

all_col_privs_recd

all_tab_privs

all_tab_privs_made

all_tab_privs_recd

user_role_privs

user_sys_privs

column_privileges

session_privs

* 데이터베이스 오디팅(auditing)이란?

선택된 데이터베이스 사용자의 활동을 감시하는 프로세스

일반적으로 세타입의 데이터베이스활동에서 수행된다.

1. 문장

2. 오브젝트

3. 특권

오디팅은 오디팅된 오퍼레이션, 그 운영을 수행하는 사용자, 그 운영 일시, 그리고 그 운영의 성패 표시와 같은 정보들을 기록한다. 이 정보는 오라클 오디트 트레일 테이블이나 운영체계 오디트 파일에 저장된다.

* 데이터베이스를 오디트 하는 이유는?

1. 데이터베이스 자원과 활동에 대한 데이터와 통계를 수집하기 위하여(IO현황,동시발생 사용자의 수등)

2. 의문스러운 활동이나 사용자를 감시하기 위해.

* 오디트 할때 주의사항

오라클의 오디트 기능과 운영체계의 오디트 기능을 사용한다.

오라클이 오디트 기능을 수행하면 테이블에 오디트 정보들이 저장된다.

오디트 할때는 일반적인 오디트 기능을 일단 설정해 차츰 필요한 부분을 추가한다

* 문장과 오브젝트 그리고 특권의 오디팅

문장 오디팅은 관련된 특정 문장 그룹을 오디팅한다. 문장 오디팅은 일반적으로 범위가 넓으며, 모든 사용자 또는 선택된 사용자 그룹의 활동을 오디트한다. 특권 오디팅은 선택된 시스템 특권과 그와 결합된 문장을 오디트 하는 것이다. 예를 들어 create user시스템 특권을 오디트하는 것은 이 특권의 실행을 요구하는 모든 사용자의 문장을 오디트한다.

* 오디트 구성하기

오디트 내용은 다음을 포함한다.

1. 오디트된 문장을 실행된 사람의 사용자명

2. 사용자에 의하여 실행된 오디트된 문장을 나타내는 활동코드

3. 오디트된 문장에서 참조된 오브젝트(들)

4. 오디트된 문장이 실행된 일시

* 오디트 3가지 타입

1. 문장: sql문의 타입에 기초하여 오디트

2. 특권: 특정 시스템 특권을 오디트

3. 오브젝트: 특정 오브젝트에 있는 특정 문장을 오디트

* 문장 오디트 옵션

alter, cluster, database link, index, not exists, procedure, public database link, public synonym, role, rollback, sequence, sesstion, synonym, system audit, system grant, table, tablespace, trigger, user, view

* 예

사용자 kabin을 위한 모든 접속활동의 오디트를 지정하려면..

audit session

by kabin;

* 오디트 옵션 키워드

1. connect : session

2. resource : alter system, cluster, database link, procedure, rollback segment, sequence, synonym, table, tablespace, view 포함

3. dba : system audit, public database link, public synonym, role, system, grant, user

4. all 1,2,3의 모든 옵션

예) audit dba kabin

* 최종 오디트 옵션

alter sequence, alter table, comment table, delete table, execute procedure, grant procedure, grant sequence, grant table, insert table, lock table, select sequence, select table, update table

* 오브젝트 오디트 옵션

alter, audit, comment, delete, execute, grant, index, insert, lock, rename, select update

* 특권 오디트 옵션

audit alter user

by access;

* 오디트 유효화 하기와 무효화하기

예) audit role whenever successful;

-> 성공적인 role문만 오디트 한다. 그러나 실제로 오디트 레코드를 생성하지는 않는다

데이타베이스 오디팅은 데이터베이스의 파라미터 파일에 있는 audit_trail 초기화

파라미터에 의하여 유효화되고 무효화 된다.

audit_trail파라미터

1. db : 데이타베이스 오디팅을 유효화 하고 모든 오디트 레코드를 데이터베이스 오디트 트레일로 보낸다

2. os : 데이타베이스 오디팅을 유효화 하고 모든 오디트 레코드를 운영체계 오디트 트레일로 보낸다.

3. none : 오디팅을 무효화한다.

파라미터

1. by session : 동일한 세션에서 발행된 동일한 타입의 모든 sql문을 위하여 그 오디트가 하나의 레코드를 쓰도록 만든다

2. by access : 오라클이 오디트된 각 문장을 위하여 하나의 레코드를 쓰게한다.

3. whenever successful : 성공적으로 완료되는 sql문을 위해서만 오디팅을 선택한다

4. whenever not successful : 실패하거나 에러로 결과되는 sql문을 위해서만 오디팅을 선택한다.

* 오디트 트레일 뷰

stmt_audit_option_map, audit_actions, all_def_audit_opts, dba_stmt_audit_opts

dba_priv_audit_opts, dba_obj_audit_opts, user_obj_audit_opts, user_audit_trail

user_audit_session, user_audit_statement, user_audit_object

* 오디트 트레일 관리 지침

1. 절대적으로 필요한 오디트 옵션만 켠다.

2. 필요에 따라서 데이터베이스를 유효화하거나 무효화한다.

3. 오브젝트 오디팅을 제한한다. 왜냐하면 문장 오디팅보다 더 많은 정보를 생성하기 쉽기 때문이다. 그럴려면 audit any시스템 특권을 소유하는 사람을 제한해야 한다.

* 모든 레코드의 오디트 트레일을 없애려면?

1. delete from sys.aud$;

2. delete from sys.aud$

where obj$name='mst_parts';

* 트리거를 이용하여 오디트하기

trigger를 이용한 오디트는 DML문장만을 다룬다. 대신 훨씬 자세한 정보를 제공할 수 있다. 트리거를 사용하여 추가의 오디팅 정보를 생성할 때에는 after트리거를 사용하여야 한다.

after트리거를 사용함으로써 무결성 제한 때문에 실패하는 문장의 불필요한 오디팅을 피할 수 있다.

* 백업과 복구의 구조를 위한 정의

1. 프로세스 실패: 사용자, 서버, 또는 백그라운드 프로세스에 있어서의 실패 이 예로는 비정상적인 접속단절이 있다.

2. 네트워크 실패: 네트워크 컴포넌트에 있어서의 실패

3. 인스턴스 실패: 하드웨어 실패나 운영체계 문제의 결과로서 인스턴스 실패

4. 미디어 실패: 일기/쓰기 시도의 실패로 인한 실패

5. 리두로그(redo log): 이것은 오라클 데이터베이스에서 이루어진 모든 변경을 기록

6. 롤백 세그먼트: 현재 트랜잭션에 의하여 변경된 데이터의 변경된 값이나 이전 값을 저장한다.

* 백업을 위한 지침

1. 적당한 백업 프로시저를 계획하고 구현

2. 백업을 테스트 할 것.

3. 정기적으로 백업을 수행할것

4. restore중에 데이터베이스를 보호할것

5. 테이블스페이스를 백업할 것.

6. 백업을 유지할 것

7. 분산 데이타베이스를 보호할것. 분산데이타베이스는 동일한 백업 계획의 일부가 되어야 한다. 데이터베이스가 noarchivelog모드에서 운영되는 경우에는 동시에 백업이 수행되어야 한다.. 보관 모드가 유효화될 경우에는 동기화할 필요가 없다.

* 백업의 종류

오라클은 두 종류의 백업을 제공한다.

부분 백업과 완전백업

* 백업 모드

아카이브 모드 : 데이터베이스 실패의 마지막 순간까지 완전한 백업이 이루어지도록 보장한다. 이것은 온라인 리두 로그를 다른 테이프나 디스크 드라이브에 보관함으로써 이루어진다.

온라인이나 오프라인 백업을 이용하여 특정 시점에 완전복구를 할 수 있게 해준다. 데이터를 잃어버리면 안되는 중요한 애플리케이션을 실행하고 있는 고객을 위해서는 이 모드를 추천한다.

노아카이브 모드: 온라인 리두 로그를 보관하지 않는다.

이 타입의 백업은 미디어 실패가 아니라 인스턴스 실패로부터 보호해 준다.

* 백업 수행하기

어떤 백업도 수행하기 전에 백업할 파일의 리스트를 만든다.

v$logfile 뷰는 온라인 리두 로그 파일의 리스팅을 포함한다.

데이터 베이스의 현재 콘토롤 파일의 리스트를 얻기

show parameter control_file

* 완전 백업 프로세스

1. 정상적인 우선 순위에 의하여 데이터베이스를 셧다운 시킨다

2. 데이ㅌ터베이스에 의하여 사용된 모든 파일의 백업을 수행한다.

3. 데이터베이스를 재기동한다.

* 콘트롤 파일의 백업

alter database backup controlfile cf_user_01 reuse;

* 복구를 위한 지침

1. 복구 프로시저를 테스트 할것

2. 올바른 복구 방안을 마련한다.

* 버퍼와, 리두 로그, 롤백 세그먼트

리두 로그로 롤하려면 alter database명령을 쓴다. 다음은 arch0021이라는 redo log로 롤한다.

alter database

recover logfile 'tape_3:arch0021.arc';

redo log는 그 변경이 commit 되었는지 여부에 관계없이 인덱스와 데이터 그리고 롤백 세그먼트를 포함하여 그 데이터베이스에 가해진 모든 변경을 기록하는 일련의 운영체계 파일이다.

* 완전 복구 프로세스

1. abort절과 함께 shutdown

2. 데이터베이스의 모든 데이터파일과 온라인 리두 로그파일 그리고 파라미터 파일을 복구한다.

3. 복구된 파라미터 파일을, 만약 가능하다면 새로운 디스크의 위치를 반영하도록 편집한다.

4. 복구되고 편집된 파라미터 파일을 사용하여 그 인스턴스를 재기동한다.

5. 데이터베이스를 마운트한다.

6. 복구된 데이터파일과 온라인 리두로그 파일의 새로운 위치를 기록한다.

7. 데이터베이스를 다시 연다.

* 부분복구

특정 데이터베이스 복구 중에 부분적 파일 복구가 필요할 수도 있다. 복구할 필요가 있는 파일을 디스플레이하려면 v$recover_file 파일을 사용한다.

다음의 문장은 개별 파일을 복구하는데 필요한 정보를 생성한다.

select file#, online, error

from v$recover_file;

* 인스턴스 복구

인스턴스 복구 프로세스는 그 데이터베이스를 인스턴스 실패 바로 전의 트랜잭션 일치 상태로 복구한다. 인스턴스 복구는 다음과 같이 이루어진다.

1.데이터파일에 아직 저장되지 않은 데이터를 복구하기 위하여 리두로그를 롤하기. 이것은 alter database명령을 recover절과 함께 사용하면 된다.

2. 커미트되지 않은 트랜잭션을 롤백 세그먼트가 지정한 대로 롤백하기. sql롤백 명령은 커미트 되지 않은 트랜잭션을 롤백한다.

3. DDL테이블 락과 같은 모든 자원 락을 풀어주기. 트랜잭션이 커미트 되거나 롤백될 때까지 락은 제 위치에 남는다.

4. 유예된 분포 트랜잭션으로부터 양면 커미트를 해결하기. 양면 커미트는 커미트나 롤백이 실행될 때 분산 데이터베이스의 모든 노드에서 트랜잭션이 유효화되도록 보장한다.

* SQL 문을 튜닝하는 이유

1. 오라클 데이타베이스 애플리케이션의 퍼포먼드를 개선한다.

2. 특정 SQL문의 퍼포먼스를 개선한다.

3. 오라클 내부에서 메모리 할당을 개선한다.

4. 사용자 회선과 디스크 I/O를 감소시킨다.

* SQL문 튜닝

인덱스, 클러스터, 해시 클러스터, 힌트 등을 사용한다.

* 인덱스 사용하기

1. where절에서 자주 사용되는 열을 인덱스한다.

2. sql문에서 테이블을 연결하기 위하여 자주 사용되는 열을 인덱스한다.

3. 그 행의 값이 크게 다를 경우 인덱스를 위한 열로 선택한다.

4. 행의 값이 자주 바뀌는 열은 인덱스 하지 말아야 한다. 이런 열을 인덱스하면 갱신과 삽입프로세스의 속도가 떨어질 것이다.

5. where절이 함수를 참조할 경우 그 where절의 열은 인덱스 하면 안된다.

6. 참조 무결성 제한의 외부 키를 구성하는 열에서 인덱스를 사용한다

7. 둘 이상의 열로 구성된 복합 인덱스의 사용을 고려해 본다.

* 복합 인덱스의 사용을 고려할때 참작해야 할 것

1. where절에서 함께 사용된 열에 대하여 복합 인덱스를 사용한다.

2. 여러 쿼리가 하나 이상의 열 값의 특정 값에 기초항여 동일한 열 세트를 선택할 경우 이 열들에 대하여 복합 인덱스를 만든다.

3. create index문에서의 선택도에 따라 열들을 정렬한다. 선택도가 높은 열을 처음, 그리고, 선택도가 낮은 것을 뒤에 나타나게 한다.

* 인덱스 피하기

널 값을 인덱스 문자열에 연결하여 where조건을 건다.

select *

from employee

where emp_dept || '' = 'ABCDEFG';

* 클러스터 사용하기

1. join문에서 자주 액세스되는 클러스터된 테이블을 사용한다. 만약 그 연결이 자주 사용되지 않으면 그 테이블들을 클러스터하면 안된다.

2. 클러스터된 테이블 가운데 한 테이블에서만 완전한 테이블 스캔이 수행되면 테이블들을 클러스터하지 말아야 한다.

3. 클러스터된 테이블에 있는 데이터의 크기가 오라클 데이터 블록 둘 보다 크면 테이블들을 클러스터하지 말아야 한다.

* 해시 클러스터 사용하기

해시 클러스터를 위한 행 검색이 해시 함수에 따른다.

1. 동일한 열이나 열의 조합을 사용하는 상등 조건을 포함하는 where절에 의하여 자주 액세스되는 테이블을 위하여 해시 클러스터를 사용한다.

2. 클러스터 키 값의 모든 행을 수용하는데 필요한 스페이스를 경정할 수 있을 경우 해시 클러스터를 사용한다.

3. 데이터베이스 스토리지를 낮게 실행하고 있다면 해시 클러스터를 사용하지 말아야 한다.

4. 테이블이 계속 커가고 있고 보다 큰 새로운 해시 클러스터를 만드는 것이 실용적이지 못할 경우에는 해시 클러스터링을 이용해서는 안된다.

5. 해시 클러스터 키 값이 자주 수정될 경우에는 해시 클러스터를 사용해서는 안된다.

* 힌트 사용하기

1. sql문을 위한 최적화 어프로치

2. sql문을 위한 코스트 중심 어프로치의 목표

3. 테이블을 위한 액세스 경로

4. join문을 위한 연결 순서

5. join문에서 연결 오퍼레이션

오라클은 코스트 중심 어프로치를 사용할 때에만 힌트를 인식한다.

* 여러가지 힌트들

all_row : 최대 처리량을 위하여 sql을 최적화한다.

end_qual : 지정된 테이블에서 인덱스 머지를 이용한다.

cluster : 지정된 테이블에서 클러스터 스캔을 이용한다.

cost : 코스트 중심 옵티마이저를 이용한다.

first_row : 최선의 응답시간을 위하여 sql을 최적화한다.

이 힌트는 옵티마이저가 다음의 선택을 하도록 만든다.

1. 가능하다면, 완전한 테이블 스캔보다 인덱스 스캔을 이용한다.

2. 인덱스 스캔이 가능하다면 소트-머지 연결 전에 내장 루프가 사용된다

3. order by절에 의하여 인덱스 스캔이 가능하다면, 그 옵티마이저는 언제나 소트 작업을 피하도록 선택한다.

4. 이 힌트는 group by, for update, distinct, set operators중 어떤 것이건 포함하는 delete와 update문을 위해서는 무시된다. 모든 행이 이 문장들을 위하여 검색되어야 하기 때문에 이 문장들은 최적화 할 수 없다.

full : 지정된 테이블에서 완전한 테이블 스캔을 이용한다.

hash : 지정된 테이블에서 해시 탐색을 이용한다.

index : 지정된 테이블에서 지정된 스캔을 이용한다.

index_asc : 지정된 테이블에서 지정된 인덱스 스캔을 오름차순으로 이용한다.

index_desc : 지정된 테이블에서 지정된 인덱스 스캔을 내림차순으로 이용한다.

ordered : from절 join순서를 이용한다. join문에서 ordered절을 생략할 경우 오라클이 그 열들의 순서를 결정한다.

rowid : rowid액세스 방법을 사용한다.

rule : 규칙 중심 최적화를 한다. 이것은 또한 옵티마이저가 이 문장 블록에 나타나는 다른 힌트는 무시하도록 한다.

user_merge : 지정된 테이블에서 소트-머지 테크닉을 사용한다.

use_nl : 지정된 테이블에서 내장 루프 연결 테크닉을 사용한다.

* TKPROF유틸리티

tkprof은 트레이스 파일을 읽을 수 있는 포맷으로 번역하는데 이용된다.

* 트레이스 파일에 들어 있는 정보

1. 파싱과 불러오기, 실행의 횟수

2. cpu와 소요시간

3. 물리적 시간과 논리적 시간

4. 프로세스된 행의 수

5. 라이브러리 캐시에서 누락의 수

tkprof infile outfile

tkprof명령 라인에 sort, print, explain절을 포함시킬 수 있다. sort절은 트레이스된 sql문들을 지정된 소트 옵션에 따라서 내림차수으로 소트한다.

print절은 지정된 수의 sqlㅁ누들만 출력 파일에 리스트한다. 여러분은 이것을 이용하여 가장 퍼포먼스가 좋지 않은 문장들에의 출력의 양을 제한하고 싶을지도 모른다. 때로는 이 파일이 다소 커질 때가 있다.

explain절은 tkprof로 하여금 explain plan명령을 실행하도록 지시하는데 상용된다 explain plan출력은 tkprof통계가 프린트된 직후 디스플렝이된다.

다음의 지침은 tkprof유틸리티로부터 생성된 통계를 이용하는데 도움이 될 수 있을 것이다.

1. 논리적 I/O의 수가 리턴된 행의 크기보다 훨씬 크면 문장이 그 인덱스를 이용할 수 있도록 만드는 것을 고려해 본다.

2. 수행된 불러오기의 수가 검색된 행의 수와 같으면 배열 인터페이스의 사용을 고려해 본다.

3. 문장이 실행되는 만큼 자주 파싱되면 라이브러리 캐시가 퍼포먼스를 개선 할 수도 있다.

4. 물리적 I/O의 수가 논리적 I/O의 수와 가까우면 보다 큰 데이타베이스 버퍼 캐시가 퍼포먼스를 개선할 수도 있다.

* 이산트랜잭션(Discrete Transactions)

begin_discrete_transaction프로시저를 사용하여 여러분의 짧은 비분포 SQL문의 퍼포먼스를 개선할 수 있다. 이 프로시저는 트랜잭션 프로세싱을 간소화해짐으로 짧은 트랜잭션이 보다 빨리 실행될 수 있다.

* 트랜잭션이 사용될 수 있는 조건들.

1. 소수의 데이터베이스 블록만 수정한다.

2. 트랜잭션마다 개별 데이터베이스 블록을 한 번 이상 변경하는 일이 거의 없다

3. 오래 끄는 쿼리에 필요할 것 같은 데이터를 수정하지 않는다

4. 데이터를 수정한 후에 그 데이터의 새로운 값을 볼 필요가 없다.

5. 긴 값을 포함하는 테이블을 수정하지 않는다.

* 이산 트랜잭션의 특징

1. 모든 데이터에 가해진 변경은 트랜잭션이 커미트될 때까지 연기된다.

2. 리두 정보가 생성되지만 메모리에서 별도의 위치에 저장된다.

3. 언두 정보는 생성되지 않는다.

4. 트랜잭션은 그 자체의 변경을 알 수 없다.

5. 행들은 이산 트랜잭션이 지속되는 동안 select/for update문을 사용하여 락을 걸 수 있다.

6. discrete transaction중에 만나는 모든 에러는 discrete_transaction_failed 예외가 제기되게 만든다.

다음의 예는 이산트랜잭션 프로시저의 용례이다.

declare 
  begin 
     begin_discrete_transaction; 
  exit; 
  exception 
    when discrete_transaction_failed 
    then 
    rollback; 
  end; 
end; 

* SQL*DBA로부터의 통계

SQL*DBA에서 모니터 기능을 이용하면 다음의 sql튜닝 통계를 디스플레이할 수 있다

모니터 명령을 위한 구문은 다음과 같다.

monitor keyword

예를 들어 디스크 소트의 수를 모니터 할때

monitor sorts(disk);

파라메터

cluster key scans

cluster key scan block gets

table fetch by rowid

table fetch continued row

table scan rows gotten

table scan blocks gotten

table scans(긴 테이블)

table scans(짧은 테이블)

parse count

parse time CPU

parse time elapsed

sorts(디스크)

sorts(행)

sorts(메모리)

* 어플리케이션 튜닝이란?

코드의 각 라인, 오라클 서버와의 인터페이스, 어플리케이션이 실행되는 환경 튜닝

* 어플리케이션 튜닝하는 이유?

오라클 어플리케이션과 sql문, 그리고 pl/sql블록의 퍼포먼스를 개선한다는 최종목표

* PL/SQL 튜닝하기

* 프로시저 코드를 어플리케이션에 넣어야 할때

1. 입력 필드로부터의 값을 유효화 하고자 할때

2. 단 하나의 유일한 어플리케이션에 지정된 활동을 수행하고 있을때

3. 에러처리 루틴을 커스터마이즈해야 할때

* 프로시저 코드를 서버에 넣어야 할때

1. 코드가 트랜잭션에서 여러테이블을 갱신한다.

2. 여러 어플리케이션이 프로시저 코드를 공유한다.

3. 참조 무결성 제한을 보장하는데 그 코드가 사용된다.

4. 그 코드가 테이블의 갱신이나 삽입 또는 삭제시에 발생하도록 활동을 지정한다

* 프리컴파일러 프로그램의 퍼포먼스의 질을 떨어뜨리는 많은 요인

1. 클라이언트 프로그램과 오라클 사이에 광범위한 호출. 이것은 특히 그 호출이 네트웍을 가로질러 이루어질 때 그렇다

2. 비효율적인 sql문은 인덱스와 같은 데이터베이스 자원을 활용하지 않는다.

3. 내장 sql문의 불필요한 재파싱과 재바인딩

* 커서의 관리에 도움을 주는 프리컴파일러 옵션

1. maxopencursors :프리컴파일러가 캐시해 두려고 하는 열린 커서의 최대수를 지정

2. hold_cursor : 프로그램 커서와 커서 캐시 간에 링크를 유지해야 할 것인지 여부지정

3. release_cursor : 캐시 엔트리와 콘텍스트 구역 간에 링크를 유지해야 할 것인지 여부를 지정

PL/SQL코드를 되도록이면 SQL코드가 아니라 애플리케이션에서 사용하려고 해야 한다.

이것은 어플리케이션이 PL/SQL의 블록 성격을 활용하도록 만든다. 내장 블록은 전체가 한꺼번에 네트워크를 통하여 오라클 서버로 보내어진다. 이것은 네트워크를 통하여 개별적으로 보내지는 각 SQL문과는 대조가 된다.

* 배열

가능하면 배열 프로세싱을 실현하라. 배열 프로세싱은 어플리케이션으로부터 오라클의 호출 수를 줄임으로써 퍼포먼스를 개선할 수 있다.

* 배열의 잇점을 활용할 수 있는 어플리케이션의 전형적인 타입

1. 고도의 퍼포먼스가 필수적인, 많은 행을 프로세스하는 모든 배치형 시스템

2. 한 번에 많은 레코드의 삽입과 갱신을 수행하는 모든 어플리케이션

3. 네트워크를 통하여 많은 양의 데이터를 이동시키는 클라이언트/서버 어플리케이션

4. 둘 이상의 레코드를 프로세스해야 하는 모든 SQL문 일반적으로 오라클 툴들은 자동적으로 배열을 사용한다.

다음은 배열의 크기를 조절하는 명령이다.

set arraysize 200

* 메모리 할당 튜닝하기

메모리의 적절한 할당의 잇점

1. 캐시 퍼포먼스가 개선된다.

2. SQL문과 PL/SQL블록의 파싱이 감소한다.

3. 페이징과 스왑핑이 감소

* 메모리 할당 튜닝에 포함되는 단계들

1. 운영체계를 튜닝한다.

2. 전용 SQL과 PL/SQL구역을 튜닝한다. 불필요한 파스 호출을 식별하거나 가능 하다면 그 수를 줄인다. TJPROF 유틸리티는 파스호출의 수를 식별하는데 도움이 될 수 있다.

3. 공유 풀을 튜닝한다. v$librarycache테이블에 리스트된 라이브러리 캐시 미스의 수를 평가 하는 일부터 시작한다. 미스는 실행단계에서 라이브러리 캐시 미스의 수를 나타냄

실행된 단계의 수에 대한 미스의 비율은 1%미만이어야 함.

select * from emp;

select * from Emp;

이렇게 대소문자를 다르게 하면 공유풀을 쓰지 않게 된다.

4. 버퍼캐시를 튜닝한다.

버퍼캐시 히트율을 결정하려면 v$sysstat테이블에서 select를 수행하여 버퍼의 데이터베이스 블록 gets과 일치gets, 그리고 물리적read의 수를 얻는다.

hit ratio=1-(physical reads/(database block gets + consistent gets))

만약 이 비율이 75% 미만이라면 문제가 있는 것이다. 이때 캐시에서 버퍼의 수를 늘린다.

* I/O 튜닝하기

1. 디스크 경쟁을 줄인다

2. 데이터 블록을 위하여 적당한 스페이스를 할당한다.

3. 동적 스페이스 할당을 삼가한다.

v$datefile테이블은 그 특정 데이타베이스 파일을 위한 물리적인 읽기와 쓰기에 대한 정보를 제공한다. 현재 초당 40이상의 액세스로 실행하고 있다면 그 디스크는 과용되고 있는 것이다.

* 디스크 경쟁은 주로 롤백 세그먼트와 MTS서버에서 잘 일어난다.

* 롤백 세그먼트 경쟁

롤백 세그먼트는 액세스 대기의 수가 전체 데이터베이스 액세스 수의 1%를 넘으면 문제를 갖게 된다. 다음은 그 데이터베이스를 위한 대기수를 디스플리이한다.

select class, count from v$waitstat;

* 만약 멀티 쓰레드형 서버 디스패처가 50%이상의 시간 동안 바쁘다면 새로운 디스패처를 추가해야 한다.

select network, busy

from v$dispatcher;

* 공유 서버 프로세스를 얻는데 필요한 평균 대기시간

select (wait/totalq) "Average Wait Time"

from v$queue

where type = 'COMMON';

* 공유 서버의 수 출력

select count(*) 'NBR of shared servers"

from v$shared-servers

where status !='QUIT;

* lock의 종류

데이터 lock

DML lock이라고도 하며, 테이블 데이터를 보호하고 데이터 무결성을 보장하는데 사용

사전 lock

데이터 정의 lock이라고도 하며 트랜잭션 중에 스키마 오브젝트가 바뀌지 않도록 보호

내부 lock

데이터베이스와 메모리의 내부 컴포넌트를 보호한다.

분포 lock

데이타와 기타 자원들이 여러 인스턴스에 걸쳐 일치 되도록 하기 위하여 만들어 진다.

병렬 cache lock

버퍼 구역에서 하나 이상의 데이터 블록을 보호하는 분포lock이다.

매뉴얼 lock

* SQL*DBA는 현재 설정된 lock을 볼 수 있도록 lock모니터를 제공

* 데이타베이스 LINK 사용법

데이타베이스 link는 데이터베이스간에 경로를 정의한다.

create public database link node_01.corp.dist;

이렇게 링크를 만든 다음에 다음과 같은 쿼리를 할 수 있다.

select * from user_03.parts@node_01.corp.dist;

* 데이타베이스 LINK의 종류

1. 전용링크: 특정 사용자를 위해 만들어 진다.

2. 공용링크: public이라는 특수한 사용자 그룹을 위하여 만들어 진다. 그 데이타베이스의 모든 사용자가 이 링크에 대한 액세스를 갖는다.

3. 네트워크 링크: 이 링크는 네트워크 도메인 서비스에 의하여 만들어지고 관리된다. 그 네트워크에 있는 어떤 데이타베이스의 어떤 사용자라도 SQL문에서 글로벌 오브젝트 이름을 지정하면 네트워크 링크를 사용할 수 있다.

* 데이타베이스 LINK제거

drop database link user_03.parts@node_01.corp.dist

* 분산 데이타베이스 제한

분산 데이타베이스에서 성능에 도움이 되는 몇가지 제한

1. 노드당 분산 트랜잭션의 수를 제한

2. 각 인스턴스를 위하여 커미트 포인트 스트랭스를 설정

* 양면 commit(Two-Phase Commits)

오라클은 자동적으로 글로벌 데이터베이스를 감시하고 분산 트랜잭션의 글로벌 커밋과 롤백을 콘트롤한다. 양면 commit은 오라클이 분포 커밋과 롤백을 감시 하는 방법이다. 데이터 무결성을 유지하기 위함.

* 분산 어플리케이션 개발시 고려사항

1. 시퀀스 : 다음 두 사항이 참일 경우 리모트 시퀀스는 참조될 수 있다.

. 문장에서 참조된 시퀀스와 갱신된 테이블, 갱신 테이블을 위한 선택, 긴열, 그리고 lock된 테이블은 모두 그 시퀀스와 동일한 노드에 위치한다.

. 최소한 하나의 참조된 테이블이 시퀀스와 동일한 노드에 있다.

예)

select intro mst_part@inventory_01

select sq_1.nextal@inventory_01, part_id, part_name, part_price

from routings@inventory_01;

2. 참조무결성: 오라클은 분산 데이터베이스의 노드들에 참조 무결성이 선언되는것을 허용하지 않는다. 여러분은 리모트 테이블에서 주요 키나 고유키를 참조하는 외부키를 지정하는 하나의 테이블에 대한 참조무결성 제한을 가질 수 없지만 부모자식 관계는 분산 데이터베이스들 간에 허용된다. 이 관계는 트리거를 사용해야 유지될 수 있다.

3. 분산 쿼리: 트리거나 stored procedure를 이용하여 분산 쿼리를 만들수 있다.

4. 스냅샷: 오라클은 무제한의 스냅샷에 의하여 마스터 테이블을 다른 노드에 복제하는 것을 허용한다.스냅샷은 마스터 테이블은 이용할 수 없게 되더라도 그 데이터를 다른 노드에서 계속 이용할 수 있게 해준다.

* 옵티마이저란?

모든 DML문을 실행하는 가장 효율적인 방법을 결정하는 오라클 서버의 일부분이다. 옵티마이저는 rules base 최적화와 cost base최적화의 두가지 경로를 통해 최적화된 검색 경로를 찾아간다.

* 옵티마이저를 사용하는 이유

오라클 옵티마이저는 실행 계획을 생성하는데 이 실행계획은 문장을 실행하기 위하여 오라클이 취하는 일련의 단계이다. 때로는 사용자가 옵티마이저보다 어플리케이션을 더 잘 알기 때문에 걁길행계획을 사용자가 지정하기도 하는데 이를 힌트라고 한다. 그러나 이렇게 지정 한다고 해서 이것이 directive(지시)를 뜻하지는 않기 때문에 옵티마이저는 다만 사용자가 주는 힌트를 제안으로 참고한다.

힌트에는 sql문을 최적화 하는데 사용되는 정보의 범위를 제한하는 힌트가 있는 반면에 전반적인 방침을 정하는 힌트들도 있다.

* 힌트가 하는 일

1. 참조된 각 테이블을 위한 액세스 경로

2. 접속을 위한 접속 순서

3. 테이블을 접속하는데 사용하는 방법

4. sql문을 위한 cost base approach

5. sql문을 위한 최적화 approach

* 실행계획

실행 계획은 오라클에 의해 만들어지며 오라클이 명령문을 실행하기 위하여 취하는 단계들을 정의한다. explain plan명령을 사용하여 문장의 실행계획을 검사할 수 있다.

출력예)

OPERATION OPTION OBJECT_NAME

--------------------------------------

select statement

table access by rowid dept

index range scan dpt_indx

* explain plan

explain plan사용의 첫 단계는 출력을 담을 테이블을 만드는 것이다. 이것을 위해 utxplan.sql 스크립트를 사용하여 사용자의 스키마에 테이블을 만들거나 수동으로 테이블을 만들 수 있다.

explain plan명령을 사용해 보자. 다음과 같다.

explain plan

set statement_id = 'part_cost'

select part_name, part_nbr, part_id, part_cost

from mst_part.part_nbr = inventory.part_nbr

and not exists

(select * from obs_parts

where part_code > min_cost);

다음과 같은 결과를 얻을 수 있다.

OPERATION OPTIONS OBJ_NAME ID PARENT_ID POS.

--------------------------------------------------

SELECT STMT 0 5

FILLTER 1 0 0

NESTED LOOP 2 1 1

TABEL ACCESS FULL MST_PART 3 2 1

TABLE ACCESS FULL INVENTORY 4 2 2

TABLE ACCESS FULL OBS_PARTS 5 1 3

이 예에서 옵티마이저가 3번의 완전 테이블 스캔을 수행하여 결과세트를 생성한다.

* 두가지 최적화 방법

1. Rule-based

오라클은 활용가능한 액세스에 기초를 둔 실행 계획을 선택하고 이들 경로로 랭크를 할당한다. 오라클은 항상 고 랭크에 대해서 저 랭크를 가진 경로를 선택한다.

2. Cost-based:

오라클은 활용가능한 액세스 경로를 고려하고 어떤 경로가 가장 효율적인지의 문제의 의해서 액세스되는 테이블, 테이블과 관련된 클러스터와 인덱스에 대한 데이터 사전의 상황에 기초를 두고 결정하게 된다. 다음의 과정을 거친다.

가) 여러 대안적인 실행계획이 활용가능한 경로와 조언에 기초를 두고 제시된다.

나) 옵티마이저는 각각의 실행 계획의 비용을 측정한다.

나) 옵티마이저는 가장 적은 비용의 실행 계획을 선택한다.

* SQL 최적화를 위한 표현식과 조건의 평가

1. 표현식과 조건은 옵티마이저가 우선적으로 평가하는 아이템이다. 다음의 첫 번째 문장은 두 번째 문장으로 옵티마이저에 의해 변형된다.

dozen=12000/12

dozen=1000

2. 조건을 디자인 할 때에는 표현식을 가진 조건을 열에 비교하지 말고 가능하면 상수를 열에 비고해야 한다.

price > cost*1.25;

3. 그 밖의 옵티마이저가 변환시키는 것들

month in ('JAN','MAR','MAY')

는 다음과 같이 바뀐다.

month='JAN' or month='MAR' or month = 'MAY'

part_nbr > (:new_part, :old_part);

는 다음과 같이 바뀐다.

part_nbr > any(:new_part, :old_part);

* 문장 변형

옵티마이저는 하나의 문장을 동일한 결과를 낳는 다른 문장으로 변형시킬때가 많다.

1. 조건 가운데 하나가 완전 테이블 스캔을 요구하지 않는 한 OR를 포함하는 문장이 union all을 포함하는 문장으로 변형된다.

2. 복합문이 연결문으로 변형된다. 복합문은 가능하면 등호 연결문으로 변경된다.

예를 들면

select chemical_name

from master_chemical

where chemical_id in

(select chemical_id from chemical_archive)

는 다음과 같이 변형된다.

select chemical_name

from master_chemical

where master_chemical.chemical_id=chemical_archive.chemical_id;

* 뷰 머징(View Merging)

뷰를 포함하는 문장 최적화시에는 다음중 하나가 실행된다.

1. 문장을 등가문(equivalent statement)으로 변형된다.

2. 뷰 쿼리를 만든 다음 그 결과를 마치 테이블인 것처럼 본래의 문장을 이용하여 처리한다.

예)

create view parts

as select part_nbr, part_name

from mst_parts

where mfg_pt='45J';

select part_name

from parts

where part_nbr like '95-%'

는 다음과 같이 바뀐다.

select part_name

from mst_parts

where part_nbr like '95-%'

and mfg_pt = '45J';

* 최적화 어프로치(approach) 결정

1. 고려할 사항

. 초기화 파라미터 optimizer_mode값

. alter session명령에 있는 optimier_mode파라미터

. 데이터 사전에 있는 통계

. 문장에 있는 힌트

2. optimizer_mode파라미터는 다음의 값들 가운데 하나를 가질 수 있다.

. cost: cost-based을 선택하도록 한다.

. rule: rule-based를 선택하도록 한다.

3. optimization_goal파라미터는 다음의 값들 가운데 하나와 같을 수 있다.

. choose : cost-based든 rule-based든 하나를 고르도록 강요한다.

. all_rows : 코스트 중심 어프로치로 현재 목표를 달성하도록 강요한다.

. fist_row : 코스트 중심 어프로치로 최선의 응답시간을 얻도록 강요한다.

. rule : 옵티마이저가 rule-based 어프로치를 선택하도록 강요한다.

* 액세스 경로 선정

1. 데이터와 테이블을 액세스하는 여러 가지 방법

. 완전테이블 스캔

. rowid에 의한 테이블 스캔

. 클러스터 스캔 : 동일한 클러스터 키를 가진 행들이 검색된다.

. 해시 스캔 : 동일한 해시 값을 가진 모든 행이 검색된다.

. 인덱스된 스캔 : 인덱스의 한 행 이상의 값에 기초하여 인덱스로부터 데이터가 검색된다.

2. 액세스 경로의 순위

(1) rowid에 의한 단일 행

(2) 클러스터 연결에 의한 단일 행

(3) 고유한 이름이나 주요 키에 의한 해시클러스터 키에 의한 단일 행

(4) 고유한 이름이나 중 키에 의한 단일 행

(5) 클러스터 연결

(6) 해시 클러스터 키

(7) 인덱스된 클러스터 키

(8) 복합 인덱스

(9) 단일 열 인덱스

(10) 인덱스된 열에서 바운드된 범위 선택

(11) 인덱스된 열에서 바운드되지 않은 범위 선택

(12) 소트-머지 연결

(13) 인덱스된 열의 최대와 최소

(14) 인덱스된 열의 의한 순서

(15) 완전 테이블 스캔

* 연결 순서의 선택

rule-based approach 실행 단계

1. 옵티마이저는 서로다른 테이블에서 첫 테이블을 사용하여 일련의 접속을 만든다. 첫 테이블 뒤에 테이블의 순서를 메꾸기 위해 옵티마이저는 전항에서 제시된 순위에 의해 최상의 액세스 경로를 산출하는 테이블의 결합을 선택한다. 일단 테이블의 순서가 결정되면 그 앞의 테이블을 액세스 하기 위한 각 테이블의 액세스 방법이 결정된다.

2. 실행 가능한 실행 계획들이 평가되고 하나가 선택된다. 이 선택 프로세스에서 옵티마이저의 목표는 인덱스 스캔을 이용하여 그 이너(inner)테이블이 액세스되는 중첩된 연결 연산의 수를 극대화하는 실행 플랜을 찾는 것이다.

cost-based approach 실행 단계

1. 가능한(available) 연결 순서와 연결 연산, 그리고 이용가능한 액세스 경로에 기초하여 일련의 가능한 실행 계획이 생성된다.

2. 그 다음, 옵티마이저는 가능한 각 실행 플랜을 위한 코스트를 계산한다.

이 코스트는 다음에 기초한다.

. 중첩된 루프의 코스트는 아우터 조인의 선택된 까 행과 그와 매치하는 이너테이블의 각 행을 메모리에 읽어 들이는 코스트에 기초한다.

. 소트-머지 연결의 코스트는 각 소스를 메모리에 읽어들이고 그것을 소트하는 코스트에 주로 기초한다.

. 소트 크기와 인덱싱, 복수 블록 리드, 그리고 시퀀스 리드와 같은 요인들이 모두가 코스트 산정에 들어가는 요인이다.

코스트 중심 어프로치에서, 연결 실행의 순서는 정렬된 힌트를 이용하여 사용자가 정의할 수 있다.

* 연결 연산의 선택

두 행 소스를 연결하려면 오라클이 다음의 연산 가운데 하나를 수행하여야 한다.

. 내장 루프

. 소트-머지

. 클러스터

1. 내장 루프 연결 단계

(1) 드라이빙 테이블 또는 주요 테이블로서 하나의 테이블이 선정되고 다른 테이블들은 이너(inner)테이블이라고 부른다.

(2) 주요 테이블에 있는 각 해을 위하여 연결 조건을 만족시키는 모든 행을 이너 테이블에서 찾는다.

(3) 연결 조건을 만족시키는 각 쌍의 행으로부터의 데이터가 결합되어 결과행을 리턴한다.

2. 소트-머지 연결 단계

(1) 연결할 각 행을 그 연결에 사용된 열에서 소트한다.

(2) 한 소스에 있는 각 행이 그 연결 열을 위한 매칭 값을 포함하는 다른 소스의 각 행과 결합 되도록 두 소스가 머지된다.

3. 클러스터 연결 주의사항

오라클은 동일한 클러스터에 내장된 두 테이블의 클러스터 키 열과 동등한 등호 연결을 위해서만 클러스터 연결을 수행할 수 있다. 클러스터 연결은 간단히 말해서 하나의 클러스터에 함께 내장된 두 테이블을 포함하는 중첩된 루프이다.

* SQL문의 종류

오라클은 다음과 같은 종류의 SQL문들을 최적화 한다.

insert나 update와 같은 단순문, 단순 쿼리, 연결(둘 이상의 테이블로부터 데이터 선택), 등호 연결, 비 등호 연결, 아우터 조인, 카르테시안 프로덕트, 복합문, 혼합쿼리, 분산문

* 혼합 쿼리

혼합쿼리는 옵티마이저에 의하여 여러 컴포넌트로 나누어진다. 그 다음, 각 컴포넌트에 실행 계획이 주어지고 각 컴포넌트의 결과는 union, intersection, minus연산을 이용하여 조합된다.

union all 연산자를 위해서는 각 테이블에서 완전 테이블 스캔이 일어난다. 또한 그 결과는 조합되어 결과 선정 세트를 만들게 된다.

union 연산자는 union all과 비슷한데 다만, sort-unique연산이 조합된 결과에서 실행되어 결과 선전 세트가 만들어 진다는 것이다.

intersection연산자는 각 테이블이 완전 스캔이 일어나고 각 개별 완전 테이블 스캔에서 sort-unique연산을 수행한다. 양 sort-unique결과세트에 모두 나타나는 행만 최종 선정 세트에 조합되도록 sort-unique연산의 결과들이 조합된다.

* 분산문(Distributed Statement) 고려사항

만약 sql문을 위한 테이블들이 여러 리모트 노드에 분포되어 있다면 오라클은 그 문장을 단일 데이터베이스를 액세스하는 단편들로 분해한다. 이 단편들은 실행을 위하여 리모트 데이터베이스로 보내진다. 리모트 데이터베이스는 실행계획을 생성하고 결과 선정세트를 그 호출 데이터베이스에 리턴한다. 로컬 호출 데이터베이스는 그 다음 필요하다면 추가의 프로세싱을 수행한다.

(1) 개요
   1) 사용자가 임의로 Shell파일을 만들어 필요시 마다 그 파일을 호출하여 실행한다.
   2) Shell은 보통언어가 가지고 있는 기본적인 특성을 가지므로 Shell을 이용하여 사용자 환경에
     맞도록 프로그래밍을 할 수 있다.
   3) Shell파일을 이용한 프로그램을 Shell프로그램 또는 Shell스크립트라고 한다.
   4) Shell프로그램의 수행방법
     ㄱ. chmod를 이용하여 수행하고자 하는 Shell파일에 실행권한을 부여한다.
     ㄴ. Shell파일에 인수를 주어 sh명령을 준다.
(2) 쉘스크립트 생성하는 법
   1) 첫라인에 사용할 쉘을 명시한다. bash쉘을 사용할 경우 보통 첫줄에 다음과 같이 표기한다.
     #!/bin/bash
   2) 스크립트를 실행 가능한 파일로 만든다.
(3) 실행예
   1) 예1
     ㄱ. [posein@www posein]$ cat sample1
         echo "sample1"
           => vi 편집기를 이용하여 입력한다.
         [posein@www posein]$ ls -l sample1
         -rw-rw-r--    1 posein   posein         15 Jun 25 02:49 sample1
           => 현재 실행권한이 없으므로 실행시킬 수가 없다.
         [posein@www posein]$ chmod 755 sample1
           => 실행권한을 부여하였다.
         [posein@www posein]$ ./sample1
         sample1
           => 현재디렉토리에 경로로 설정이 안되어 있으므로 './'를 붙여서 실행시켜야 한다.
     ㄴ. [posein@www posein]$ sh sample1
         sample1
          => 실행권한이 없더라도 앞에 'sh'를 붙여서 실행시킬 수 있다.
   2) 예2 : #!/bin/bash를 명기

(1) 리다이렉션과 파이프
   1) 다중명령어 사용
    ㄱ. 한 라인에 여러 개의 명령을 입력하면 여러 명령을 수행한다.
    ㄴ. 순차적으로 명령들을 수행할 때 명령들의 구분자는 ';'이다.
    ㄷ. 동시에 명령들을 수행할 때 명령들의 구분자는 '&'이다.
    ㄹ. 사용예
      a. [posein@www posein]$ date; ls -l; whoami
           => 순서대로 실행된다.
      b. [posein@www posein]$ date& ls -l& whoami
           => 동시에 실행된다.
   2) 입출력방향의 재지정
    ㄱ. 표준입출력
       -표준입력(stdin) : 내용입력, 0으로 표기
       -표준출력(stdout) : 내용출력, 1으로 표기
       -표준에러(stderr) : 에러메시지, 2로 표기
    ㄴ. 리다이렉션심볼
       < : 존재하는 파일로 부터 표준입력으로 읽음 (program < input)
       > : 표준 출력으로 파일에 씀 (program > output)
       >> : 존재하는 파일에 표준출력으로 추가함 ( program >> output)
    ㄷ. 입력재지정
      a. 설명: 키보드로부터 명령을 읽는 대신 파일에서 명령을 읽을 때 사용한다.
      b. 입력재지정 기호 : "<"
      c. 사용법
        command < file
      d. 사용예
        [posein@www posein]$ cat red
        ps
        ls
        [posein@www posein]$ sh < red
          PID TTY          TIME CMD
        25289 pts/1    00:00:00 bash
        25581 pts/1    00:00:00 sh
        25582 pts/1    00:00:00 ps
        Desktop  bbb
    ㄹ. 출력재지정
     a. 설명: 명령의 출력을 터미널에 연결하는 대신 파일로 저장할 때 사용한다.
     b. 출력재지정기호 : ">"
     c. 사용법
       command > file
     d. 사용예
       [posein@www posein]$ ls > red2
       [posein@www posein]$ cat red2
       Desktop/
       aaa*
       aaa.sh*
    ㅁ. 출력재지정 추가
     a. 설명: 출력을 파일의 끝에 추가하고자 할 때 사용한다.
     b. 출력재지정기호 : ">>"
     c. 사용법
       command >> file
     d. 사용예
       [posein@www posein]$ ls -l >> red2
    ㅁ. 표준에러 재지정
     a. 설명: 에러메시지를 터미널에 출력하지 않고 파일로 저장할 때 사용한다.
     b. bashd인 경우
       command 2 > errfile
        => 표준에러를 errfile에 저장한다.
       (command > file) 2 > errfile
        => 표준출력은 file에, 에러는 errfile에 저장한다.
     c. C shell인 경우
       command >& errfile
        => 표준에러를 errfile에 저장한다.
       (command > file) >& errfile
        => 표준출력은 file에, 에러는 errfile에 저장한다.
     d. 사용예
       1. [posein@www posein]$ ls -z
          ls: invalid option -- z
          Try `ls --help' for more information.
          [posein@www posein]$ ls -z 2>errfile
          [posein@www posein]$ cat errfile
          ls: invalid option -- z
          Try `ls --help' for more information.
       2. [posein@www posein]$ (ls -z > file) 2>errfile2
          [posein@www posein]$ cat file
          [posein@www posein]$ cat errfile2
          ls: invalid option -- z
          Try `ls --help' for more information.
       3. C Shell에서의 표준에러지정
          [posein@www ~]$ ls -z >& errfile
          [posein@www ~]$ cat errfile
          ls: 부적절한 옵션 -- z
          더 많은 정보를 얻으러면 `ls --help'명령을 하십시오.
       4. C Shell에서 표준출력과 표준에러지정
          [posein@www ~]$ ( ls -z > file2) > & errfile3
   3) 파이프
    ㄱ. 파이프를 사용하면 여러 프로세스를 함께 사용할 수 있다.
    ㄴ. 파이프로 연결된 프로세스는 동시에 수행될 수 있으며, 이 프로세스들 사이의 데이터 흐름은
       자동적으로 다시 계획된다.
    ㄷ. 사용법
       program1 | program2 | .....
    ㄹ. 사용예
       1. [posein@www posein]$ ps | sort | more
            PID TTY          TIME CMD
          25289 pts/1    00:00:00 bash
          25649 pts/1    00:00:00 ps
          25650 pts/1    00:00:00 bash
          25651 pts/1    00:00:00 bash
       2. [posein@www posein]$ ps | sort | more > ps.txt
            => 리다이렉션과 함께 사용하여 파일로 저장할 수도 있다.

(1) 주석
   1) 설명: 주석은 '#'을 이용하며 해당라인 끝까지 처리된다. 이 부분은 쉘실행시 무시되며, 보통
           프로그램에 대한 이해를 돕기위해 사용된다.
   2) 사용예
     #!/bin/sh
     # Auth: posein
     # Work : hello world 를 출력한다.
     echo "hello world"   # 문자열을 출력한다
(2) 변수
   1) 설명: 변수는 모든 프로그래밍언어의 가장 기본이 되는 요소로 어떠한 데이터를 저장하는 공간
           이다. 쉘에서 변수의 타입은 문자열(string)만을 가지면 C언어처럼 변수타입선언이 필요
           없다. 참고로 perl, python, php등도 변수타입선언이 불필요하다.
   2) 사용법
     var=value
      => 변수를 사용할 때는 변수명앞에 $을 붙인다. 또한 변수에 데이터를 저장할 때는 대입연산자인
        '='를 사용하며 대입연산자, 연산자/피연산자 사이에는 공백이 존재해서는 안된다. 또한 변수
        명은 -를 제외한 특수문자와 숫자로 시작해서는 안된다.
   3) 기타변수 대응법
     ㄱ. ${name} : name이라는 변수에 들어있는 값으로 치환한다.(변수명 다음에 다른 문자가 연이
                  어 나올때 유용)
     ㄴ. ${name:=value} : name이 null이면 value로 할당하여 저장하고, 아니면 그냥 기존의 값으로
                         치환한다.(기본값을 지정할 때 유용)
     ㄷ. ${name:+value} : 기존의 name이 null이 아니라면 value를 사용하지만 name에 저장하지는
                         않는다.
     ㄹ. ${name:-value} : 기존에 name값이 있다면 value를 기본값으로 하고  없다면 value반환.
                         name에 value값을 저장하지는 않는다.
     ㅁ. ${name:?value} : 기존에 name값이 있다면 기본값으로 하고 없으면 error를 내면서 value
                         값을 보여준다.
     ㅂ. ${#name} : name의 문자열 길이를 반환
     ㅅ. ${name:offset} : name값에서 offset만큼 삭제한 후에 값을 돌려준다.
     ㅇ. ${name:offset:length} : name값에서 offset만큼 삭제한 후에 length만큼 센 뒤 돌려준다.
   4) 환경변수
    ㄱ. $HOME: 사용자의 홈디렉토리
    ㄴ. $PATH: 명령어 찾기에 사용되는 콜론(:)으로 구분되는 디렉토리 목록
    ㄷ. $LOGNAME: 사용자의 로그인명($USER)
    ㄹ. $MAIL or $MAILPATH: 메일도착을 알고자 할때 사용되는데 둘 중 하나만 지정, $MAIL이 지정
                           되면 메일이 도착할 파일명이 지정되어야 하고 $MAILPATH가 지정되면
                           콜론(:)으로 분리된 메일 파일의 리스트이여야 한다. 메일프로그램이
                           메일을 어디에 놓아야 하는지를 지정하지는 않지만 새로운 메일이 도착
                           하면 Shell이 어디를 찾아야 하는지를 지정한다.
    ㅁ. $MAILCHECK: 메일 파일을 검사하는 초단위 설정
    ㅂ. $SHELL: 로그인 Shell명
    ㅅ. $PS1: Shell이 사용할 첫번째 프롬프트 스트링, 보통은 $
    ㅇ. $PS2: 두번째 프롬프트 스트링, 명령이 완전히 끝나지 않고 Shell이 또 다른 입력행을 요구
             할 경우 사용, 보통 >
    ㅈ. $TERM: 사용자의 터미널형을 포함, vi같은 어떤 명령은 정확한 결과를 나타내기 위해 사용
              중인 터미널의 종류를 나타냄.
    ㅊ. $IFS: 입력필드 구분자, Shell상에서 입력을 읽어들일 때 글자를 구분하기 위한 목적으로
             사용되는 문자 목록, 보통은 스페이스, 탭, 개행문자임.
   5) 아규먼트 변수: 특별한 내장변수로 '위치 매개변수(positional parameter)'라고도 한다. 이
                    변수는 매개변수를 불러올 때 스크립트의 명령행 인자를 담당한다. 위치 매개
                    변수는 그 이름이 1, 2, 3등으로 되어 있고 그 값을 각각 $1, $2, $3등으로
                    표시하고 0은 스크립트 파일명을 뜻한다.
    ㄱ. $0 : 실행된 쉘 스크립트이름
    ㄴ. $1 : 스크립트에 넘겨진 첫번째 아규먼트
    ㄷ. $2 : 스크립트에 넘겨진 두번째 아규먼트
    ㄹ. $# : 스크립트에 넘겨진 아규먼트의 개수
    ㅁ. $$ : 쉘스크립트의 프로세스ID
    ㅂ. $* : 스크립트에 전달된 인자 전체를 하나의 변수에 저장하며 IFS환경변수의 첫번째 문자로
            구분
    ㅅ. $@ : $*와 동일(다른점은 IFS환경변수를 사용하지 않음)
    ㅇ. $? : 실행한 뒤의 반환(return)값, 즉 참이면 0, 거짓이면 1이 반환됨
    ㅈ. $- : 현재 Shell이 호출될 때 사용한 옵션들
   6) 사용예
    ㄱ. test.sh
       #!/bin/sh
       # 변수에 값을 할당한다.
       a="hello world"
       # 이제 a라는 변수에 등록된 값을 화면에 출력한다.
       echo "a is : $a"
        => 실행파일로 만들어서 실행시키면 'a is : hello world'가 출력된다.
    ㄴ. test2.sh
       num=2
       echo "this is the $numnd"
         => 원하는 값이 "this is the 2nd" 이다. 그러나 위의 예제를 실행시키면 "this is the "
           라는 문자열이 출력된다. 왜냐면 쉘은 "numnd"를 하나의 변수명으로 생각하기 때문이다
           만약 원하는 값을 출력하려면 아래와 같이 바꿔야 한다.
    ㄷ. test3.sh
       num=2
       echo "this is the ${num}nd"
        => 'this is the 2nd' 라고 출력된다.
    ㄹ. [posein@www posein]$ cat var
        var=
        ${var:=ls}
        echo $var
         => var가 null이므로 값에 ls가 들어가면서 해당 명령이 실행된다.
    ㅁ. [posein@www posein]$ echo $DIR
        [posein@www posein]$ echo ${DIR:-temp}
        temp
        [posein@www posein]$ echo $DIR
    ㅂ. [posein@www posein]$ DIR=tmp
        [posein@www posein]$ echo ${DIR:+temp}
        temp
        [posein@www posein]$ echo $DIR
        tmp
    ㅅ. [posein@linux67 posein]$ string1=linux
        [posein@linux67 posein]$ string2=window
        [posein@linux67 posein]$ test $string1 = $string2
        [posein@linux67 posein]$ echo $?
        1
         => $?은 값이 참이면 0, 거짓이면 1을 출력한다.
    ㅇ. [posein@www posein]$ cat test4.sh
         #!/bin/bash
         echo "This script file $0"
         echo "Argument Count is $#"
         echo "Process ID is $$"
         echo "Argument List \$* : $*"
         echo "Argument List \$@ : $@"
         echo "Argument 1: $1"
         echo "Argument 2: $2"
         echo "Argument 3: $3"
         [posein@www posein]$ ./test4.sh a b c
         This script file ./test4.sh
         Argument Count is 3
         Process ID is 31779
         Argument List $* : a b c
         Argument List $@ : a b c
         Argument 1: a
         Argument 2: b
         Argument 3: c
    ㅈ. $@와 $*의 차이점
       [posein@www posein]$ cat ii1
       #!/bin/bash
       IFS=,
       echo "$@"
       [posein@www posein]$ cat ii2
       #!/bin/bash
       IFS=,
       echo "$*"
       [posein@www posein]$ ./ii1 a b c
       a b c
       [posein@www posein]$ ./ii2 a b c
       a,b,c
        => $*는 IFS를 사용하고 $@는 IFS를 사용하지 않는다.
    ㅊ. [posein@www posein]$ count=Ilovebash
        [posein@www posein]$ echo ${count:5}
        bash
        [posein@www posein]$ echo ${count:5:2}
        ba
   7) Shell변수의 출력
    ㄱ. set: 현재 정의되어 있는 모든 변수와 그 값을 출력
    ㄴ. env: export된 변수의 값만 출력
    ㄷ. exoprt:
      a. 설명: 특정변수의 범위를 환경 데이터 공간으로 전송하여 자식프로세스에서도 그 특정변수
              를 사용 가능하게 함.
      b. 특징
        - Shell프로그램이 호출될 때 export를 사용한 모든 변수에 대한 복사가 만들어져 호출된
          프로그램으로 전달되게 하여 변수의 범위를 확대시킨다.
        - 인자가 없는 export명령은 export된 변수의 리스트를 얻을 수 있다.
        - 서브 Shell에서 수정된 환경변수값은 부모 Shell에 영향을 주지 못한다.
        - 서브 Shell에서 변수를 export하면 export된 변수는 그 다음 서브 Shell에 영향을 준다.
    ㄹ. 예제
          [posein@www posein]$ cat main
          echo shell programming
          name=test
          city=seoul
          echo name is $name
          echo city is $city
          export name
          ./sub
          echo stop main
          [posein@www posein]$ cat sub
          echo start sub
          echo name is $name
          echo city is $city
          echo stop sub
          [posein@www posein]$ ./main
          shell programming
          name is test
          city is seoul
          start sub
          name is test
          city is
          stop sub
          stop main
   8) 정의된 변수의 제거
    ㄱ. unset 변수명
       => 변수의 값을 널(Null)로 만드는 것이 아니라, 존재하지 않게 한다.
    ㄴ. readonly 변수명
       => 읽기전용 변수로 되어 삭제할 수가 없다.(로그아웃할 때까지 적용된다.)
    ㄷ. 예
       1. [posein@www posein]$ str1=test1
          [posein@www posein]$ echo $str1
          test1
          [posein@www posein]$ unset str1
          [posein@www posein]$ echo $str1
       2. [posein@www posein]$ str2=test2
          [posein@www posein]$ readonly str2
          [posein@www posein]$ echo $str2
          test2
          [posein@www posein]$ str2=test3
          bash: str2: readonly variable
          [posein@www posein]$ echo $str2
          test2
   9) echo문과 escape문자
    ㄱ. 설명: echo문은 라인을 제어하는 명령해석기이며 -e옵션과 같이 \으로 시작하는 escape
             특수문자를 사용할 수 있다.
    ㄴ. 종류
       \b : Backspace
       \c : 새로운 라인의 생성을 억제(bash에서는 안됨)
       \f : Formfeed
       \n : 새로운 라인을 생성
       \r : Carrage Return
       \t : Tab문자
       \\ : Backslash
    ㄷ. 사용예
       [posein@www posein]$ echo "Hi\nHello"
       Hi\nHello
       [posein@www posein]$ echo -e "Hi\nHello"
       Hi
       Hello
   10) 특별구문: expr
    ㄱ. expr은 명령라인에 있는 표현식을 평가한다.
    ㄴ. 수학적인 표현식(+, -, *, /)을 계산하고 표준출력에 결과값을 출력한다.
    ㄷ. 결과값은 역인용부호(`)에 의해 다른 변수에 저장가능하다.
    ㄹ. expr명령은 수식의 연산자 앞 뒤에 반드시 공백이 필요하다.
    ㅁ. 정확한 정수 연산시 사용되고 소수점 계산은 불가능
    ㅂ. 예제
      a. [posein@www posein]$ expr 1 + 2
         3
      b. [posein@www posein]$ int=100
         [posein@www posein]$ expr 100 + $int + 10
         210
      c. [posein@www posein]$ int=0
         [posein@www posein]$ result='expr $int + 5'
         [posein@www posein]$ echo $result
         expr $int + 5
         [posein@www posein]$ result=`expr $int + 5`
         [posein@www posein]$ echo $result
         5
          => 단일 인용부호(' ')를 사용하면 둘러싸인 전체가 변수에 저장되며 역인용부호(` `)를
            사용하면 연산결과가 변수에 저장된다.
      d. [posein@www posein]$ expr 3 * 5
         expr: syntax error
         [posein@www posein]$ expr 3 \* 5
         15
           => *만 사용하면 특수문자로 인식하여 에러가 발생한다. 따라서 *앞에 \를 사용하여 특수
             문자의 의미를 제거하거나 인용부호인 " "나 ' '를 사용해야 한다.
      e. [posein@www posein]$ expr 10 / 3
         3
          => /는 나누어서 몫을 구한다.
      f. [posein@www posein]$ expr 10 % 3
         1
          => %는 나누어서 나머지를 구한다.
   11) 변수타입지정: decalre 와 typeset
    ㄱ. 설명: 이 두 명령은 내장명령으로 동의어이다. 역할은 변수의 특성을 제한한다. declare
             명령어는 bash 버전 2이후부터 가능하고 typeset명령은 ksh스크립트에서도 가능하다.
             declare명령을 아무 옵션없이 실행하면 모든 변수값을 출력한다.
    ㄴ. 사용법
       declare [option] [변수=변수값]
    ㄷ. option
       -r : 읽기전용으로 취급한다. readonly로 선언하는 것과 같다.
       -i : 정수로 취급한다.
       -a : 배열로 취급한다.
       -f : 정의된 함수의 목록을 출력한다.
       -F : 정의된 함수의 이름만을 출력한다.
       -x : export시켜서 외부환경에서도 이 변수를 사용할 수 있게 해준다.
    ㄹ. 사용예
     a. [posein@www posein]$ declare
          => 정의된 변수와 함수 등을 보여준다.
     b. [posein@www posein]$ declare -f
        declare -f popd ()
        {
          DIR_STACK=${DIR_STACK#* };
          cd ${DIR_STACK%% *};
          echo "$PWD"
        }
        declare -f pushd ()
        {
          dirname=$1;
          DIR_STACK="$dirname ${DIR_STACK:-$PWD''}";
          cd ${dirname:?"missing directory name."};
          echo "$DIR_STACK"
        }
          => 정의된 함수명과 함수를 출력한다. -f옵션뒤에 함수명을 명기하면 해당 함수에 대한
            정보만을 출력한다.
     c. [posein@www posein]$ declare -F
        declare -f popd
        declare -f pushd
     d. [posein@www posein]$ val1=3 val2=5
        [posein@www posein]$ result1=val1*val2
        [posein@www posein]$ echo $result1
        val1*val2
        [posein@www posein]$ declare -i val3=3 val4=5
        [posein@www posein]$ declare -i result2
        [posein@www posein]$ result2=val3*val4
        [posein@www posein]$ echo $result2
        15
(3) 인용부호
   1) 설명: 쉘에는 특별한 의미를 가지고 있는 특수문자들이 있는데, 이를 메타문자(meta character
           )라고도 한다. 이러한 특수문자를 문자그대로 사용하고자 할 때 즉, 문자나 단어의 특별
           한 의미를 없앨 때 인용부호를 사용할 수 있다.
   2) 종류
     ㄱ. single quotes(' '): 문자열로 표기(변수명 자체를 문자열로 표기)
     ㄴ. double qutoes(" "): 문자열로 표기(변수값을 하나의 인자로 인식, 즉 $, `, \는 문자열에서
                            제외됨)
     ㄷ. backslash(\) : 문자의 특수한 기능을 제거
     ㄹ. back quotes(` `) : 명령어로 인식
   3) 사용예
     ㄱ. [posein@www posein]$ echo 'My Home Directory is $HOME'
         My Home Directory is $HOME
          => $HOME을 그냥 문자열로 취급한다.
     ㄴ. [posein@www posein]$ echo "My Home Directory is $HOME"
         My Home Directory is /home/posein
          => $HOME의 값을 출력한다. (" "는 $, `, \의 특수한 기능을 인정)
     ㄷ. [posein@www posein]$ echo "My Home Directory is \$HOME"
         My Home Directory is $HOME
          => \의 특수한 기능을 제거하여 $를 문자열화 하여 $HOME가 출력된다.
     ㄹ. [posein@www posein]$ echo 'My Home Directory is \$HOME'
         My Home Directory is \$HOME
          => ' '는 모든 특수문자를 무조건 문자열처리하므로 그대로 출력된다.
     ㅁ. [posein@www posein]$ echo "Current Directory is `pwd`"
         Current Directory is /home/posein
          => ` `는 명령어가지고 있는 기능을 인정한다. 역시 " "입장에서 봤을 때 ` `의 특수한
            기능을 인정한다.
     ㅅ. [posein@www posein]$ name=posein
         [posein@www posein]$ echo $name
         posein
          => name의 값인 posein을 출력한다.
         [posein@www posein]$ echo \$name
         $name
          => \는 바로 다음 문자의 특수한 기능을 문자열로 전환시킨다.
         [posein@www posein]$ echo \\$name
         \posein
          => 역시 \는 바로 다음에 나오는 \의 특수한기능을 없앤다.
(4) 간단한 조건문
   1) 설명: test명령을 기본으로 보통 if문과 같이 사용된다. 보통 test라고 입력하지만 보통 쉘
           스크립트상에서는 test라는 명령어를 생략하고 [ ] 로 한다. 이러한 테스트 조건들은
           '['와 ']'사이에 쓰면되고, '['과 ']'사이에는 반드시 공백문자가 들어가야 한다.
   2) 사용법
     test 표현식
     [ 표현식 ]
   3) 표현식
     ㄱ. 문자열비교
        a. [ string ] : string이 빈 문자열이 아니라면 참
        b. [ string1 = string2 ] : 두 문자열이 같다면 참
        c. [ string1 != string2 ] : 두 문자열이 다르면 참
        d. [ -n string ] : 문자열이 null(빈문자열)이 아니라면 참
        e. [ -z string ] : 문자열이 null(빈문자열)이면 참
     ㄴ. 산술비교
        a. [ expr1 -eq expr2 ] : 두 표현식 값이 같다면 참(Equal)
        b. [ expr1 -ne expr2 ] : 두 표현식 값이 같지 않다면 참(Not Equal)
        c. [ expr1 -gt expr2 ] : expr1 > expr2 이면 참(Greater Then)
        d. [ expr1 -ge expr2 ] : expr1 >= expr2 이면 참(Greater Equal)
        e. [ expr1 -lt expr2 ] : expr1 < expr2 이면 참(Less Then)
        f. [ expr1 -le expr2 ] : expr1 <= expr2이면 참(Less Equal)
        h. [ ! expr ] : expr이 참이면 거짓, 거짓이면 참
        i. [ expr1 -a expr2 ] : expr1 AND expr2의 결과, 즉 둘 다 참이면 참
        j. [ expr1 -o expr2 ] : expr1 OR expr2의 결과, 즉 둘 중 하나만 참이면 참
     ㄷ. 파일조건
        a. [ -b FILE ] : FILE이 블럭디바이스이면 참
        b. [ -c FILE ] : FILE이 문자디바이스이면 참
        c. [ -d FILE ] : FILE이 디렉토리이면 참
        d. [ -e FILE ] : FILE이 존재하면 참
        e. [ -f FILE ] : FILE이 존재하고 정규파일이면 참(호환성 문제로 -e보다 -f가 주로 사용)
        f. [ -g FILE ] : FILE에 SGID가 있으며 참
        g. [ -k FILE ] : FILE에 Sticky bit가 있으면 참
        h. [ -L FILE ] : FILE이 심볼릭링크이면 참
        i. [ -p FILE ] : Named pipe이면 참
        j. [ -r FILE ] : 현재 사용자가 읽을 수 있는 파일이면 참
        k. [ -s FILE ] : 파일이 비어있지 않으면 참
        l. [ -S FILE ] : 소켓디바이스이면 참
        m. [ -t FD ] : FD(File Descriptor)가 열려진 터미널이면 참
        n. [ -u FILE ] : FILE에 SUID가 있으면 참
        o. [ -w FILE ] : 현재사용자가 쓸 수 있는 파일(writable file)이면 참
        p. [ -x FILE ] : 현재사용자가 실행할 수 있는 파일(Excute file)이면 참
        q. [ -O FILE ] : FILE의 소유자가 현재 사용자이면 참
        r. [ -G FILE ] : FILE의 그룹이 현재 사용자의 그룹과 같으면 참
        s. [ FILE1 -nt FILE2 ] : FILE1이 FILE2보다 새로운 파일(최근의 파일)이면 참
        t. [ FILE1 -ot FILE2 ] : FILE1이 FILE2보다 오래된 파일이면 참
        u. [ FILE1 -ef FILE2 ] : FILE1이 FILE2의 하드링크, 즉 I-node값이 같으면 참
   4) 사용예
    ㄱ. [posein@www posein]$ cat test5.sh
        if [ -d data ]
        then
        cd data
        echo "data's File List"
        ls
        fi
         => 현재 디렉토리에 data라는 디렉토리가 존재할 경우 data's File LIst라는 문자열과
            함께 파일의 리스트를 보여준다.
    ㄴ. [posein@www posein]$ cat test6.sh
        [ "$LOGNAME" = "root" ]
        echo $?
        [posein@www posein]$ ./test6.sh
        1
         => 로그인한 사용자가 root가 아니므로 반환값이 1, 즉 거짓이다.
    ㄷ. [posein@www posein]$ cat test7.sh
        [ "$LOGNAME" = "posein" ]
        echo $?
        [posein@www posein]$ ./test7.sh
        0
         => 로그인한 사용자와 일치하므로 반환값이 0, 즉 참이다.
    ㄹ. [posein@linux67 posein]$ test 20 -gt 30
        [posein@linux67 posein]$ echo $?
        1
        [posein@linux67 posein]$ test 20 -lt 30
        [posein@linux67 posein]$ echo $?
        0
(5) 목록
   1) 설명: 여러 명령을 실행할 때 앞의 명령의 결과에 의해서 다음행도 결정되어야 할 경우가 있
           다. 이런 경우에 AND나 OR조건을 사용해서 한번에 처리할 수 있다. 이것은 쉘스크립트
           뿐만아니라 명령행에서도 사용가능하다. if문을 사용하는 것보다 간결하다.
   2) 종류
    ㄱ. &&
      a. 설명: AND를 의미한다. &&는 왼쪽 문장이 참이면 오른문장을 실행한다. 수행도중에 결과
              가 거짓이 되면 그 이후의 명령은 수행되지 않는다.
      b. 사용법
        문장1 && 문장2 && 문장3 && .....
      c. 사용예
        [ -f "/etc/shadow" ] && echo "This computer uses shadow passwords"
          => 왼쪽문장이 참이면 오른쪽 문장을 실행시킨다. 즉 /etc/shadow가 존재하면
            "This computer uses shadow passwords"라는 문자열을 출력한다.
    ㄴ. ||
      a. 설명: OR를 의미한다. 각 문장들이 거짓이 나오는 동안에 계속 실행된다. 즉 참이 나오면
              실행을 멈춘다.
      b. 사용법
        문장1 || 문장2 || 문장3 || ...
      c. 사용예
        [posein@icf04 posein]$ cat mail.sh
        #!/bin/sh
        mailfolder=/var/spool/mail/posein
        [ -r "$mailfolder" ] || { echo "Can not read $mailfolder"; exit 1;}
        echo "$mailfolder has mail from : "
        grep "^From " $mailfolder
         => posein이라는 사용자의 메일파일을 검사하여 파일을 읽을 수 없으면 에러메시지와
           함께 종료하고 그렇지 않으면 grep명령을 이용하여 누구한테 메일이 왔는지를 보여
           주는 스크립트이다.
        [posein@icf04 posein]$ ./mail.sh
        /var/spool/mail/posein has mail from :
        From posein  Wed Jun 26 21:50:15 2002
    ㄷ. &&과 ||의 혼용
      a. 설명: &&와 ||는 혼용이 가능하다.
      b. 사용법
        1. && 문장1 || 문장2
         => 조건이 참이면 문장1을 수행하고 조건이 거짓이면 문장2를 수행한다.
        2. && {
               문장1
               문장2
               문장3
              }
           ||
              {
               문장4
               문장5
               문장6
             }
          => 참이냐 거짓이냐에 따라 여러개의 문장을 수행하고 싶을 때는 { }을 사용한다.
      c. 사용예
       [posein@www posein]$ cat test8.sh
       #!/bin/bash
       [ -f /etc/shadow ] && echo "/etc/shadow is existed" || echo "/etc/shadow is not
        existed"
       [posein@www posein]$ ./test8.sh
       /etc/shadow is existed
        => /etc/shadow파일이 존재하면 "/etc/shadow is existed"라는 문자열이 출력되고 존재
         하지 않으면 "/etc/shadow is not existed"가 출력된다. 참고로 root권한자에서 pwunconv
         명령을 내린뒤에 다시 수행시켜보도록 한다.
(6) 조건문
   1) if
    ㄱ. 설명: 참인지 거짓인지를 판단할 때 사용된다. 참이라면 then부분을 실행하고 그렇지 않으
             면 else부분을 실행한다.
    ㄴ. 사용법
      - 형식1(단일 if문)
       
        if [ 조건 ]
        then
         실행문장
        fi
     - 형식2(if ~ else)
       if [ 조건 ]
        then
          실행문장1
        else
          실행문장2
        fi
     - 형식3(if ~ elif) : elif는 else if의 약자로 이 구문은 여러 개 사용해도 무방하다.
       if [ 조건1 ]
        then
           실행문장1
        elif [ 조건2 ]
           실행문장2
        else
           실행문장3
        fi
    ㄷ. 사용예
      a. [posein@icf04 posein]$ cat test.sh
         #!/bin/sh
         if [ "$SHELL" = "/bin/bash" ]
         then
              echo "your login shell is the bash (bourne again shell)"
         else
              echo "your login shell is not bash but $SHELL"
         fi
         [posein@icf04 posein]$ ./test.sh
         your login shell is the bash (bourne again shell)
      b. [posein@linux67 posein]$ cat tt
         if [ "$1" = "rose" ]
         then
          echo ROSE
         else echo FLOWER
         fi
         [posein@linux67 posein]$ ./tt rose
         ROSE
         [posein@linux67 posein]$ ./tt lily
         FLOWER
          => 첫번째 아규먼트값으로 rose가 오면 ROSE가 출력되고 그 외의 경우에는 FLOWER가
            출력된다.
     c. [posein@linux67 posein]$ cat tt2
        if [ "$1" = "rose" ]
        then
          echo ROSE
        elif [ "$1" = "lily" ]
        then
          echo LILY
        else
          echo FLOWER
        fi
        [posein@linux67 posein]$ ./tt2 rose
        ROSE
        [posein@linux67 posein]$ ./tt2 lily
        LILY
        [posein@linux67 posein]$ ./tt2 tulip
        FLOWER
         => 아규먼트에 rose가 오면 ROSE, lily가 오면 LILY, 그 외의 값이 오면 FLOWER가 출력
           된다.
     d. [posein@linux67 posein]$ cat i1
        #!/bin/bash
        echo "읽어들인 파일의 이름은?";read var
        if [ -f $var ]
         then
          echo file
        else
           if [ -d $var ]
             then
               echo directory
           else
               echo "not found"
           fi
        fi
        [posein@linux67 posein]$ ./i1
        읽어들인 파일의 이름은?
        /etc/shadow
        file
     e. [posein@www posein]$ cat i2
        #!/bin/bash
        if test -z "$1"
        then
            echo usage:i2 filename
            exit 1
        fi
        file_size=`wc -c < $1`
        echo the file size $file_size characters
        if test $file_size -le 5120
        then
            echo the file $1 is a small file
        else
            echo the file $1 is a large file
        fi
        [posein@www posein]$ ./i2 /etc/passwd
        the file size 3875 characters
        the file /etc/passwd is a small file
     f. [posein@www posein]$ cat el1
        #!/bin/bash
        echo "Are you man? (y/n)";read var
        if [ "$var" = "y" ]
        then
            echo "You are man"
        elif [ "$var" = "n" ]
        then
            echo "You are woman"
        else
            echo "enter y or n"
        fi
        [posein@www posein]$ ./el1
        Are you man? (y/n)
        y
        You are man
   2) case문
    ㄱ. 설명: 문자열과 일치하는 정규식부분을 찾아 해당하는 정규식 다음에 있는 명령어를 실행시
             킨다. 아래로 계속 내려가면서 문자열에 해당하는 부분을 찾고 보통 맨 마지막에
             나열한 정규식에 해당하지 않는 모든 경우에 해당하는 부분은 *)로 표기한다.
    ㄴ. 사용법
       case 문자열
       in
         정규식1) 명령어1;;
         정규식2) 명령어2;;
       ...
       esac
        => 오른쪽 괄호와 세미콜론은 반드시 사용해야 한다.
    ㄷ. 사용예
     a. [posein@linux67 posein]$ cat tt3
        case $1
        in
          0) echo zero;;
          1) echo one;;
          2) echo two;;
          3) echo three;;
          *) echo greater than four
        esac
        [posein@linux67 posein]$ ./tt3 2
        two
          => 아규먼트와 동일한 정규식을 찾아 해당 명령어를 수행한다.
     b. [posein@www posein]$ cat ca1
        #!/bin/bash
        echo "*********************************************"
        echo "*              보         기                *"
        echo "*********************************************"
        echo "1. who    2. date    3. pwd    4. ls -l     *"
        echo "*********************************************"
        echo "수행하고자 하는 명령어는?(번호를 입력하시오) "
        read number;
        case $number in
          1) who;;
          2) date;;
          3) pwd;;
          4) ls -l;;
          *) echo "없는 번호입니다."
        esac
        [posein@www posein]$ ./ca1
        *********************************************
        *              보         기                *
        *********************************************
        1. who    2. date    3. pwd    4. ls -l     *
        *********************************************
        수행하고자 하는 명령어는?(번호를 입력하시오)
        3
        /home/posein
     c. [posein@www posein]$ cat ca2
        #!/bin/bash
        echo "is it morning? (yes/no)"
        read ans;
        case "$ans" in
             yes | y | Yes | YES)           echo "good morning";;
             no  | n | No | NO)             echo "good afternoon";;
             *) echo "answer not recognize";;
        esac
        [posein@www posein]$ ./ca2
        is it morning? (yes/no)
        n
        good afternoon
   3) select문
    ㄱ. 설명: 이 구문은 다른 쉘에는 없고 콘쉘과 배시쉘에만 존재하는 구문으로 문법이 간결하다.
             구조는 for문과 유사하다.
    ㄴ. 사용법
       select 변수 in 값1, 값2,...
       do
          실행문장
       done
        => in 다음에 나오는 값들이 항목으로 된 메뉴로 생성된다. 이 값들이 생략되면 기본값은
          "$@"가 된다. 형식은 번호를 선택하게 되어 있으며, 사용자에게 번호를 표시해준다.
          "변수"에 선택된 내용을 저장하고 선택된 번호는 내장변수인 REPLY에 저장하고 "실행문장"
           이 실행된다.
    ㄷ.사용예
       [posein@www posein]$ cat sele1
       #!/bin/bash
       echo "What is your favourite OS ?"
       select var in "Linux" "Free BSD" "Windows" "Solaris" "Other"
          do
                break
          done
       echo "You have selected $var"
       [posein@www posein]$ ./sele1
       What is your favourite OS ?
       1) Linux
       2) Free BSD
       3) Windows
       4) Solaris
       5) Other
       #? 1
       You have selected Linux
(7) 반복문: 루프는 지정된 숫자나 몇가지 조건을 만날 때까지 일련의 명령들을 반복적으로 수행할
            수 있게 한다.
   1) for
    ㄱ. 설명: for문은 지정된 범위 안에서 루프를 수행한다. 범위는 어떤 집합도 가능하다. in뒤의
             값1, 값2, ...가 순서대로 지정된 변수에 배정되면서 do이하의 문장을 수행한다. 즉 매
             루프를 돌 때마다 변수의 값은 in이후의 값으로 대체된다.
    ㄴ. 사용법
      for 변수 in 값1, 값2
      do
         실행문장
      done
    ㄷ. 사용예
     a. [posein@linux67 posein]$ cat tt4
        for flower in Rose Tulip Lily
        do
          echo $flower
        done
        [posein@linux67 posein]$ ./tt4
        Rose
        Tulip
        Lily
         => flower라는 변수에 Rose, Tulip, Lily라는 값이 루프를 돌면서 들어가 출력된다.
     b. [posein@linux67 posein]$ cat tt6
        for file in $(ls chap[345].txt)
        do
          echo "-----$file-----" >> book.txt
        cat $file >>book.txt
        done
        [posein@linux67 posein]$ ./tt6
        [posein@linux67 posein]$ cat book.txt
        -----chap3.txt-----
        chap3 Page
        -----chap4.txt-----
        chap4 Page
        -----chap5.txt-----
        chap5 Page
         => chap3.txt, chap4.txt, chap5.txt파일을 book.txt라는 파일에 붙여넣는다.
     c. [posein@www posein]$ cat for1
        #!/bin/bash
        for var in *
        do
          file_size=`wc -c<$var`
          echo $var contains $file_size characters
          if test $file_size -le 5120
          then
               echo the file $var is a small file
          else
               echo the file $var is a large file
          fi
        done
         => for문에서 *는 현재디렉토리에 있는 모든 파일들을 값으로 받아들여 출력한다.
   2) while
    ㄱ. 설명: while뒤의 조건문 동안 do이하의 문장을 실행한다. for구문과 같이 실행 횟수를 지정
              하지 않아도 된다.
    ㄴ. 사용법
       while 조건문
       do
         실행문장
       done
    ㄷ. 사용예
     a. [posein@linux67 posein]$ cat tt7
        var=1
        while [ "$var" -le 5 ]
        do
           echo $var
           var=`expr $var + 1`
       done
       [posein@linux67 posein]$ ./tt7
       1
       2
       3
       4
       5
         => var이 5보다 작거나 같으면 var에 1씩 더한다. 결과는 1,2,3,4,5가 차례로 출력된다.
           참고로 expr은 연산할 때 사용한다.
     b. [posein@linux67 posein]$ cat tt8
        echo "Enter Password: "
        read password1
        echo "Retype Password: "
        read password2
        while [ "$password1" != "$password2" ]
        do
           echo "Password miss match Try again"
           echo "Retype Password: "
        read password
        done
        echo "OK Password Match complete"
          => 패스워드를 두 번 입력하여 두 값이 일치하지 않을 경우, 다시 입력을 요구한다. (물
            론 패스워드로 입력한 문자는 화면에 그대로 출력된다.)
   3) until
    ㄱ. 설명: until은 while문과 동일한 효과를 내지만 조건이 반대이다. 즉, while문은 조건이 참
             일 동안 루프를 수행하지만, until은 거짓일 동안 루프를 수행한다.
    ㄴ. 사용법
       until 조건문
       do
         실행문장
       done
    ㄷ. 사용예
       [posein@linux67 posein]$ cat tt9
       COUNTER=20
       until [ $COUNTER -lt 10 ]
       do
          echo COUNTER is $COUNTER
          COUNTER=`expr $COUNTER - 1`
       done
       [posein@linux67 posein]$ ./tt9
       COUNTER is 20
       COUNTER is 19
       COUNTER is 18
       COUNTER is 17
       COUNTER is 16
       COUNTER is 15
       COUNTER is 14
       COUNTER is 13
       COUNTER is 12
       COUNTER is 11
       COUNTER is 10
         => 20에서 부터 10까지 차례로 출력된다.
(8) 함수
   1) 설명
    ㄱ. 함수란 일종의 '스크립트안의 스크립트'라고 할 수 있다.
    ㄴ. 명령어 그룹을 메모리내에 정의하여 약어의 기능으로 사용할 수 있다. 메모리에 함수가 존재
       함으로서 수행속도를 향상시킨다.
    ㄷ. Shell 스크립트의 크기를 적절히 조절할 경우에 사용하고 코드를 구조화하는데 유리하다.
   2) 사용법
    ㄱ. 함수이름()
        {
           command
        }
    ㄴ. function 함수이름
        {
           command
        }
   3) 사용예
     [posein@www posein]$ cat fun
     #!/bin/bash
     user_print()
     {
       echo "shell programming"
     }
     echo "start user_print"
     user_print
     echo "end user_print"
     [posein@www posein]$ ./fun
     start user_print
     shell programming
     end user_print
   4) 참고할만 명령어
    ㄱ. type
     a. 설명: 주어진 인자가 쉘 스크립트, 함수, 앨리어스인지를 검사하여 출력한다. 기본적으로
             함수이면 정의된 내용까지 모두 출력한다.
     b. 사용법
       type [option] 인자
     c. option
       -all : 해당 인자이름으로 정의된 모든 사항을 출력한다.
       -path : 실행파일이나 쉘 스크립트에 국한되어 결과를 출력한다.
       -type : 여러결과는 인자이름과 일치하는 키워드 하나만을 출력한다.
     d. 사용예
       1. [posein@www shell]$ type pushd
          pushd is a function
          pushd ()
          {
              dirname=$1;
              DIR_STACK="$dirname ${DIR_STACK:-$PWD''}";
              cd ${dirname:?"missing directory name."};
              echo "$DIR_STACK"
          }
       2. [posein@www shell]$ type -all pushd
          pushd is a function
          pushd ()
          {
              dirname=$1;
              DIR_STACK="$dirname ${DIR_STACK:-$PWD''}";
              cd ${dirname:?"missing directory name."};
              echo "$DIR_STACK"
          }
          pushd is a shell builtin
       3. [posein@www shell]$ type -type pushd
          function
       4. [posein@www shell]$ type -all -type pushd
          function
          builtin
(9) 명령실행
   1) 산술확장
    ㄱ. 설명: 산술연산을 하는 expr명령은 실행이 매우 느리기 때문에 사용한다.
    ㄴ. 사용법
       $((...))
    ㄷ. 사용예
       [posein@www posein]$ cat ex1
       #!/bin/bash
       var=0
       while [ "$var" -ne 10 ]
       do
           echo $var
           var=$((var+1))
       done
       exit 0
      [posein@www posein]$ ./ex1
      0
      1
      2
      3
      4
      5
      6
      7
      8
      9
   2) 패턴과 패턴비교
    ㄱ. 설명: 일종의 문자열을 연산하는 것으로 특정한 패턴(pattern)을 놓고 변수의 문자열 값이
             일부분이라도 이 패턴과 일치하는지 검사할 때 쓰인다. 이 패턴에는 와일드카드문자
             (*, ?, [ ]를 포함한 문자세트)를 포함해도 된다.
    ㄴ. 종류
       ${variable#pattern} : 처음부터 pattern과 맞는 variable의 부분을 찾아 이 중 가장 작은
                            부분을 제거하고 나머지를 반환
       ${variable##wpattern} : 처음부터 pattern과 맞는 variable의 부분을 찾아 가장 큰 부분을
                              제거하고 나머지 부분을 반환
       ${variable%pattern} : 끝에서부터 pattern과 일치하는 variable의 최소부분을 제거하고
                            나머지를 반환
       ${variable%%pattern} : 끝에서부터 pattern과 일치하는 variable의 최대부분을 제거하고
                             나머지를 반환
    ㄷ. 사용예
      [posein@www posein]$ cat ex3
      #!/bin/bash
      unset var1
      echo ${var1:-string2}
      var1=string1
      echo ${var1:-string2}
      var1=/etc/sysconfig/network
      echo ${var1#*/}
      echo ${var1##*/}
      var2=/etc/sysconfig/network-script/ifcfg-lo
      echo ${var2%sysconfig*}
      echo ${var2%%sysconfig*}
      exit 0
      [posein@www posein]$ ./ex3
      string2
      string1
      etc/sysconfig/network
      network
      /etc/
      /etc/
(10) 기타관련명령어
   1) read
    ㄱ. 설명: 임의의 자료를 키보드로부터 입력받는 명령어로 read를 사용하며, 연속으로 여러 자료
             를 받을 수도 있다. 아규먼트는 차례대로 1,2,3,...순으로 매겨지고, 실행시킨 스크립
             트 파일 이름의 자체 아규먼트는 0이다.
    ㄴ. 사용법
       read 변수1 [변수2, ....]
    ㄷ. 사용예
       a. [posein@linux67 posein]$ read flower
          rose
           => read flower라고 입력하면 입력받을 수 있는 대기상태가 된다. 그 상태에서 값을 입력
             한다.
          [posein@linux67 posein]$ echo $flower
          rose
           => echo문으로 값을 확인한다.
       b. [posein@linux67 posein]$ read var1 var2
          aaa bbb
          [posein@linux67 posein]$ echo $var2 $var1
          bbb aaa
           => read문을 이용하여 연속적으로 입력받을 수 있다.
       c. [posein@www posein]$ cat read1
          #!/bin/bash
          echo "두 자연수를 입력받아 뺄셈을 하는 프로그램"
          echo "첫번째 자연수는?"
          read int1
          echo "두번째 자연수는?"
          read int2
          if [ $int1 -ge $int2 ]
          then
              expr $int1 - $int2
          else
              expr $int2 - $int1
          fi
          [posein@www posein]$ ./read1
          두 자연수를 입력받아 뺄셈을 하는 프로그램
          첫번째 자연수는?
          6
          두번째 자연수는?
          2
          4
       d. [posein@linux67 posein]$ cat read2
          #!/bin/bash
          echo "당신의 이름을 입력하세요?"
          read str
          echo $str
          [posein@linux67 posein]$ ./read2
          당신의 이름을 입력하세요?
          posein
          posein
   2) echo
    ㄱ. 설명: 해당문자열을 표준 출력에 표시하고 행바꿈을 한다. 만일 문자열이 없으면 행바꿈만
             한다.
    ㄴ. 사용법
       echo [option] [문자열]
    ㄷ. option
      -n : 출력결과에서 행바꿈을 하지 않는다.
      -e : escape문자를 사용할 수 있게 해준다.
    ㄹ. 사용예
     a. [posein@linux67 posein]$ echo HI!
        HI!
     b. [posein@www posein]$ echo -e "Yes\nNo"
        Yes
        No
     c. [posein@www shell]$ cat file6
        #!/bin/bash
        echo -n "ENTER A : "
        read A
        echo -n "ENTER B : "
        read B
       result1=`expr $A + $B`
        result2=`expr $A - $B`
        result3=`expr $A \* $B`
        result4=`expr $A / $B`
        result5=`expr $A % $B`
       echo "A + B = $result1"
        echo "A - B = $result2"
        echo "A * B = $result3"
        echo "A / B = $result4"
        echo "A % B = $result5"
        [posein@www shell]$ ./file6
        ENTER A : 6
        ENTER B : 2
        A + B = 8
        A - B = 4
        A * B = 12
        A / B = 3
        A % B = 0
   3) break
    ㄱ. 설명: for, while, until등의 순환문에서 빠져 나온다.
    ㄴ. 사용법
       break [n]
        => 순환문의 n번째루프에서 빠져나온다.
    ㄷ. 사용예
       [posein@www posein]$ cat br1
       #!/bin/bash
       rm -rf file*
       echo > file1
       echo > file2
       mkdir file3
       echo > file4
       for var in file*
       do
         if [ -d "$var" ]
         then
           break;
         fi
       done
       echo "directory name : $var"
       exit 0
       [posein@www posein]$ ./br1
       directory name : file3
   4) continue
    ㄱ. 설명: for, while, until등의 순환문을 계속해서 실행한다.
    ㄴ. 사용법
       continue [n]
         => n번째 루프를 나타내는 부분은 생략할 수 있다.
    ㄷ. 사용예
       [posein@www posein]$ cat co1
       #!/bin/bash
       rm -rf file*
       echo > file1
       echo > file2
       mkdir file3
       echo > file4
       for var in file*
       do
           if [ -d "$var" ]
           then
               continue
           fi
           echo "file name : $var"
       done
       exit 0
       [posein@www posein]$ ./co1
       file name : file1
       file name : file2
       file name : file4
   5) exit
    ㄱ. 설명: 상태 코드 n가 함께 스크립트를 종료한다. n은 0(성공)이나 0이 아닌 숫자(실패)가
             될 수 있다. 만일 n이 주어지지 않으면 가장 최근에 사용된 명령의 상태코드를 사용
             한다.
    ㄴ. 사용법
       exit [n]
   6) function
    ㄱ. 설명: 함수를 정의한다. 위치 매개변수($1, $2, -)가 명령어부분에서 사용가능하다.
    ㄴ. 사용법
       function 함수명
       {
         명령어
       }
    ㄷ. 사용예
       [posein@linux67 posein]$ cat myfunc
       # function myfunc
       {
         echo "parameter is $1"
       }
       [posein@linux67 posein]$ ./myfunc love
       parameter is love
   7) getopts
    ㄱ. 설명: 명령행 인자를 처리해 올바른지 점검한다. getopts명령은 주로 쉘스크립트 순환문에서
             사용되며 명령행 옵션이 표준 형식에 맞는지를 확인한다. 문자열에는 옵션을 나타내는
             문자들이 포함되며 스크립트가 실행될 때 getopts에 의해 처리한다. 적합한 옵션들은
             하나씩 처리되어 지정된 변수명에 저장된다.
    ㄴ. 사용법
      getopts 문자열 변수명
   8) return
    ㄱ. 설명: 함수정의에서 사용되며 함수가 종료될 때 상태코드 n을 반환한다. 만일 n이 생략되면
             직전에 실행된 명령 상태코드를 반환한다.
    ㄴ. 사용법
       return [n]
   9) set
    ㄱ. 설명: Shell에서 매개인자를 설정한다. 문자열을 공백으로 나누어진 필드로 출력할 때 유용
             하다.
    ㄴ. 사용법
       set 문자열
    ㄷ. 사용예
      a. [posein@www posein]$ set a b c
         [posein@www posein]$ echo $1 $2
         a b
      b. [posein@www posein]$ cat da1
         #!/bin/bash
         echo Date: $(date)
         set $(date)
         echo Date: $6년 $2 $3$1 $4
         exit 0
         [posein@www posein]$ ./da1
         Date: Thu Jul 4 23:36:22 KST 2002
         Date: 2002년 Jul 4Thu 23:36:22
   10) shift
    ㄱ. 설명
     a. 위치 매개변수를 n자리만큼 당긴다.
     b. 만일 n이 생략되면 기본적으로 1이 적용된다. 즉, $2는 $1으로 $3은 $2로 이동한다.
     c. 보통 매개 변수를 처음부터 끝까지 검색하는데 자주 사용한다.
    ㄴ. 사용법
       shift [n]
    ㄷ. 사용예
     a. [posein@www shiftdir]$ cat sh1
        #!/bin/bash
       while [ $# -gt 0 ]
        do
             echo "$# : $*"
             shift
        done
        [posein@www shiftdir]$ ./sh1 a b c
        3 : a b c
        2 : b c
        1 : c
     b. [posein@www shiftdir]$ cat sh2
        #!/bin/bash
        echo $#
        echo $1
        echo $2
        echo $3
        echo $4
       echo "____________________________"
        shift 2
       echo $#
        echo $1
        echo $2
        echo $3
        echo $4
        [posein@www shiftdir]$ ./sh2 a b c d
        4
        a
        b
        c
        d
        ____________________________
        2
        c
        d
     c. [posein@www posein]$ cat sh3
        #!/bin/bash
        echo Date: $(date)
        set $(date)
        while [ "$1" != "" ]
        do
          echo $1
        shift
        done
        exit 0
        [posein@www posein]$ ./sh3
        Date: Thu Jul 4 23:25:24 KST 2002
        Thu
        Jul
        4
        23:25:24
        KST
        2002
   11) source
    ㄱ. 설명: 파일을 읽고 실행한다. 파일이 반드시 실행가능할 필요는 없지만 PATH에 등록되어
             있어야 한다. 점(.)이 source와 같은 기능을 한다.
    ㄴ. 사용법
      source 파일 [인수]
      . 파일 [인수]
   12) eval!
    ㄱ. 설명: 인자의 값을 구하는데 사용
    ㄴ. 사용예
       [posein@www posein]$ cat ev1
       #!/bin/bash
       var1=10
       var2=var1
       eval! result='$'$var2
       echo $result
       [posein@www posein]$ ./ev1
       10
   13) expr
    ㄱ. 설명: 인자로 사용되는 표현식의 값을 구함
    ㄴ. 사용예
       var2=`expr $var +1`
   14) prinft
    ㄱ. 설명: 가장 최근의 shell에서만 사용가능한 명령어로 형식화된 출력을 할 때 사용한다.
    ㄴ. 사용법
       printf "format string" parameter1 parameter2
    ㄷ. format string의 변환식별자
       d : 십진수 출력
       c : 한문자 출력
       s : 문장 출력
       % : %문자 출력
    ㄹ. 사용예
     a. [posein@www posein]$ printf "%s\n" shell
        shell
     b. [posein@www posein]$ printf "%s %d\t%s\n" "shell programming" 100 gogo
        shell programming 100   gogo
   15) true
    ㄱ. 설명: 쉘 상에서 참의 의미를 가지는 0을 반환한다.
    ㄴ. 사용법
       true
    ㄷ. 특징
     a. 쉘 스크립트상에서 무한 루프를 만들 때 등의 경우 사용될 수 있다.
     b. Bourne 쉘 상에서 콜론문자(:)로 같은 효과를 얻을 수 있다. 보통 의미없는 널(Null).명령
    ㄹ. 사용예
     a. [posein@www posein]$ cat tr1
        #!/bin/bash
        while true
        do
          echo "over and over"
        done
     b. [posein@www posein]$ cat tr2
        #!/bin/bash
        rm -f file
        if [ -f file ]
        then
            :
        else
            echo "file did not exist"
        fi
        exit 0
        [posein@www posein]$ ./tr2
        file did not exist
(11) 쉘프로그래밍 기타사항
   1) 종료코드
     0          : 성공
     1 ~ 125    : 에러
     126        : 파일이 실행가능하지 않음
     128 ~ 255  : 시그널이 발생
      => Shell프로그래밍에서는 전역에러코드 변수없이 125개의 사용자 정의 에러코드를 사용할 수
        있다.
   2) 우선순위
    ㄱ. 앨리어스
    ㄴ. function, if, for같은 키워드
    ㄷ. 함수
    ㄹ. cd나 type과 같은 내장 명령
    ㅁ. 스크립트와 프로그램. PATH환경 변수에 들어있는 디렉토리를 쉘이 확인하여 실행한다.

PLSQL 이란?
PL/SQL 이란 ?
 - PL/SQL 은 Oracle's Procedural Language extension to SQL. 의 약자 입니다.
 - SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,
   오라클 자체에 내장되어 있는 Procedure Language입니다
 - DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항입니다.
 - PL/SQL 문은 블록 구조로 되어 있고 PL/SQL 자신이 컴파일 엔진을 가지고 있습니다.
PL/SQL의 장점
 - PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB 로 보내서 처리하므로
   수행속도를 향상 시킬수 있습니다.
 - PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.
 - 보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있습니다.
 - Variable, Constant, Cursor, Exception을 정의하고, SQL문장과 Procedural 문장에서
   사용합니다. .
 - 단순, 복잡한 데이터형태의 변수를 선언합니다.
 - 테이블의 데이터 구조와 DataBase의 컬럼럼에 준하여 동적으로 변수를 선언 할 수 있습니다.
 - Exception 처리 루틴을 이용하여 Oracle Server Error를 처리합니다.
 - 사용자 정의 에러를 선언하고 Exception 처리 루틴으로 처리 가능 합니다.
--------------------------------------------------------------------------------
PL/SQL Block 구조
PL/SQL Block Structure
 - PL/SQL은 프로그램을 논리적인 블록으로 나누는 구조화된 블록 언어 입니다.
 - PL/SQL 블록은 선언부(선택적), 실행부(필수적),예외 처리부(선택적)로 구성되어 있고,
   BEGIN과 END 키워드는 반드시 기술해 주어야 합니다.
 - PL/SQL 블록에서 사용하는 변수는 블록에 대해 논리적으로 선언할 수 있고 사용할 수 있습니다.
 ● DECLARE
    - Optional
    - Variables, cursors, user-defined exceptions
● BEGIN
    - Mandatory
    - SQL Statements
    - PL/SQL Statements
● EXCEPTION
    - Actions to perform when errors occur
● END;
    - Mandatory
  ◈ Declarative Section(선언부)
   -  변수, 상수, CURSOR, USER_DEFINE Exception선언
  ◈ Executable Section(실행부)
   -  SQL, 반복분, 조건문실행
   -  실행부는 BEGIN으로 시작하고 END 로 끝납니다.
   -  실행문은 프로그램 내용이 들어가는 부분으로서 필수적으로 사용되어야 합니다.
  ◈ Exception Handling Section(예외처리)
    - 예외에 대한 처리.
    - 일반적으로 오류를 정의하고 처리하는 부분으로 선택 사항입니다.
PL/SQL 프로그램의 작성 요령
  - PL/SQL 블록내에서는 한 문장이 종료할 때마다 세미콜론(;)을 사용합니다. .
  - END뒤에 ;을 사용하여 하나의 블록이 끝났다는 것을 명시 합니다.
  - PL/SQL 블록의 작성은 편집기를 통해 파일로 작성할 수도 있고,
    SQL프롬프트에서 바로 작성할 수도 있습니다.
  - SLQ*PLUS환경에서는 DELCLARE나 BEGIN이라는 키워드로 PL/SQL블럭이 시작하는 것을
    알 수 있습니다.
 - 단일행 주석 : --
 - 여러행 주석 : /* */
 - PL/SQL 블록은 행에 / 가있으면 종결 됩니다.
--------------------------------------------------------------------------------
PL/SQL 블럭의 유형
Block Type(PL/SQL 블럭의 유형)
[ Anonymous ]
[ Procedure ]
[ Function ]
 ◈ Anonymous Block(익명 블록)
    이름이 없는 블록을 의미 합니다.
    실행하기 위해 프로그램 안에서 선언되고 실행 시에 실행을 위해 PL/SQL 엔진으로 전달됩니다.
    선행 컴파일러 프로그램과 SQL*Plus 또는 서버 관리자에서 익명의 블록을 내장할 수 있습니다.
 ◈ Procedure(프로시저)
    특정 작업을 수행할수 있는 이름이 있는 PL/SQL 블록으로서.
    매개 변수를 받을수 있고.. 반복적으로 사용할수 있는거죠..
    보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을
    데이터 베이스에 저장하기 위해 생성합니다.
 ◈ Function(함수)
    보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용 합니다.
    대부분 구성이 프로시저와 유사하지만 IN 파라미터만 사용 할 수 있고,
    반드시 반환될 값의 데이터 타입을 RETURN문에 선언해야 합니다.
    또한 PL/SQL블록 내에서 RETURN문을 통해서 반드시 값을 반환해야 합니다.
--------------------------------------------------------------------------------
프로시저(PROCEDURE)
프로시져란..
특정 작업을 수행할수 있고, 이름이 있는 PL/SQL 블록으로서. 매개 변수를 받을수
있고.. 반복적으로 사용할수 있는거죠.. 보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는
PL/SQL블록을 데이터 베이스에 저장하기 위해 생성합니다.
⊙ CREATE OR REPLACE 구문을 사용하여 생성합니다.
⊙ IS 로 PL/SQL의 블록을 시작합니다.
⊙ LOCAL 변수는 IS 와 BEGIN 사이에 선언합니다.
[Syntax]
CREATE OR REPLACE procedure name
   IN argument
   OUT argument
   IN OUT argument
IS
   [변수의 선언]
BEGIN   --> 필수
   [PL/SQL Block]
   -- SQL문장, PL/SQL제어 문장
   [EXCEPTION]  --> 선택
  -- error가 발생할 때 수행하는 문장
END;   --> 필수
프로시저 작성 예제
SQL>CREATE OR REPLACE PROCEDURE update_sal
        /* IN  Parameter */
        (v_empno         IN    NUMBER)
       IS
       BEGIN
       UPDATE emp
       SET sal = sal  * 1.1
       WHERE empno = v_empno;
       COMMIT;
       END update_sal;
       /
 프로시져가 생성되었습니다.
설명..
프로시저의 이름은 update_sal이고..
프로시저 update_sal은 사번(v_empno)를 입력받아서 급여를 update시켜주는 sql문입니다.
프로시저를 끝마칠때에는 항상 "/"를 지정 합니다.
프로시저의 실행
EXECUTE 문을 이용해 프로시저를 실행합니다.
SQL> execute update_sal(7369);
PL/SQL 처리가 정상적으로 완료되었습니다.
7369번 사원의 급여가 10% 인상됐습니다.
SELECT 문을 실행시켜보면 데이터가 수정된 것을 확인할수 있습니다.
Parameter란
 ⊙ 실행 환경과 program사이에 값을 주고 받는 역할을 합니다.
 ⊙ 블록 안에서의 변수와 똑같이 일시적으로 값을 저장하는 역할을 합니다.
 ⊙ function에서는 IN만 있고, OUT과, INOUT는 사용하지 않습니다.
 ⊙ Parameter의 타입
    - IN :  실행환경에서 program으로 값을 전달
    - OUT : program에서 실행환경으로 값을 전달
    - INOUT : 실행환경에서 program으로 값을 전달하고,
                  다시 program에서 실행환경으로 변경된 값을 전달
.
--------------------------------------------------------------------------------
함수(FUNCTION)
 ◈ 함수(Function)
 - 보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용 합니다.
 - 대부분 구성이 프로시저와 유사 하지만 IN 파라미터만 사용 할 수 있습니다.
 - 반드시 반환될 값의 데이터 타입을 RETURN문에 선언해야 합니다.
 - 또한 PL/SQL블록 내에서 RETURN문을 통해서 반드시 값을 반환해야 합니다.
[Syntax]
CREATE OR REPLACE FUNCTION function name
  [(argument...)]
  RETURN  datatype
    -- Datatype은 반환되는 값의 datatype입니다.
IS
   [변수 선언 부분]
BEGIN
  [PL/SQL Block]
    -- PL/SQL 블록에는 적어도 한 개의 RETURN 문이 있어야 합니다.
    -- PL/SQL Block은 함수가 수행할 내용을 정의한 몸체부분입니다.
END;
SQL> CREATE OR REPLACE FUNCTION FC_update_sal
         (v_empno         IN    NUMBER)
          -- 리턴되는 변수의 데이터타입을 꼭 정의해야 합니다
          RETURN NUMBER  .
        IS
        v_sal  emp.sal%type;
        BEGIN
        UPDATE emp
        SET sal  = sal  * 1.1
        WHERE empno  = v_empno;
        COMMIT;
        SELECT sal
        INTO v_sal
        FROM emp
        WHERE empno = v_empno;
        -- 리턴문이 꼭 존재해야 합니다
        RETURN v_sal;
       END;
함수가 생성되었습니다.
설명..
이 함수에는 v_sal이라는 %type 변수가 사용되고 있습니다.
스칼라 데이터 타입을 참고하세요.
프로지저와 마찬가지로 세미콜론(;)으로 블록을 종료한 뒤 "/"를 붙여 코드를 끝마칩니다.
함수의 실행
먼저 함수의 반환값을 저장할 변수를 선언합니다.
SQL> VAR salary NUMBER;
EXECUTE 문을 이용해 함수를 실행합니다.
SQL>EXECUTE :salary := FC_update_sal(7900);
PL/SQL 처리가 정상적으로 완료되었습니다.
오라클 SQL에서 선언된 변수의 출력은 PRINT문을 사용합니다.
PRINT문으로 함수의 반환값을 저장한 salary의 값을 확인하면 됩니다.
SQL>PRINT salary;
    SALARY
----------
      1045
결과가 이렇게 나옵니다.
--------------------------------------------------------------------------------
스칼라 데이터 타입
변수 선언 방법
⊙ Identifier의 이름은 sql의 object명과 동일한 규칙을 따릅니다.
⊙ Identifier를 상수로 지정하고 싶은 경우는 CONSTANT라는 KEYWORD를 명시하고
    반드시 초기화를 할당합니다.
⊙ NOT NULL이 정의되어 있으면 초기값을 반드시 지정하고,
    정의되어 있지 않을 때는 생략 가능합니다.
⊙ 초기값은 할당 연산자(:=)를 사용하여 정의 합니다.
⊙ 초기값을 정의하지 않으면 Identifier는 NULL값을 가지게 됩니다.
⊙ 일반적으로 한줄에 한 개의 Identifier를 정의 합니다.
※ 스칼라 데이터 타입은 단수 데이터형으로 한가지의 데이터 값만 가집니다.
 BINARY_INTEGER
 -2147483647에서 2147483647 사이의 정수
NUMBER[(P, S)]
 고정 및 부동 소숫점 수에 대한 기본 유형
CHAR[(최대길이)]
 32767바이트까지의 고정길이 문자 데이터에 대한 기본 유형으로 최대길이를 명시하지 않으면 기본적으로 길이는 1로 설정
LONG
 32760바이트까지의 가변길이 문자 데이타
VARCHAR2(최대길이)
 32767바이트까지의 가변길이 문자 데이타
DATE
 날짜와 시각에 대한 기본 유형
BOOLEAN
 논리연산에 사용되는 세 가지 값(TRUE, FALSE, NULL) 중 하나를 저장 하는 기본 유형
선언 예제
v_price CONTANT NUMBER(4,2) := 12.34 ;     -- 상수 숫자 선언(변할 수 없다)
v_name VARCHAR22(20) ;
v_Bir_Type   CHAR(1) ;
v_flag   BOOLEAN  NOT NULL := TRUE ;      -- NOT NULL 값 TRUE로 초기화
v_birthday DATE;
%TYPE 데이터형
⊙ %TYPE 데이터형은 기술한 데이터베이스 테이블의 컬럼 데이터 타입을 모를 경우 사용할 수 있고,
⊙ 또. 코딩이후 데이터베이스 컬럼의 데이터 타입이 변경될 경우 다시 수정할 필요가 없습니다.
⊙ 이미 선언된 다른 변수나 데이터베이스 컬럼의 데이터 타입을 이용하여 선언합니다.
⊙  데이터 베이스 테이블과 컬럼 그리고 이미 선언한 변수명이 %TYPE앞에 올수 있습니다.
%TYPE 속성을 이용하여 얻을 수 있는 장점
   - 기술한 DB column definition을 정확히 알지 못하는 경우에 사용할 수 있습니다.
   - 기술한 DB column definition이 변경 되어도 다시 PL/SQL을 고칠 필요가 없습니다.
예제
v_empno  emp.empno%TYPE := 7900 ;
v_ename emp.ename%TYPE;
예제 프로시져..
SQL>CREATE OR REPLACE PROCEDURE Emp_Info
      -- IN Parameter
      ( p_empno IN emp.empno%TYPE )
        IS
        -- %TYPE 데이터형 변수 선언
        v_empno emp.empno%TYPE;
        v_ename emp.ename%TYPE;
        v_sal   emp.sal%TYPE;
        BEGIN
        DBMS_OUTPUT.ENABLE;
        -- %TYPE 데이터형 변수 사용
        SELECT empno, ename, sal
        INTO v_empno, v_ename, v_sal
        FROM emp
        WHERE empno = p_empno ;
        -- 결과값 출력
        DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || v_empno );
        DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || v_ename );
        DBMS_OUTPUT.PUT_LINE( '사원급여 : ' || v_sal );
        END;
       /
프로시져가 생성되었습니다.
SQL>SET SERVEROUTPUT ON;     --  DBMS_OUTPUT 결과값을 화면에 출력 하기위해
실행 결과
SQL> EXECUTE Emp_Info(7369);
사원번호 : 7369
사원이름 : SMITH
사원급여 : 880
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
%ROWTYPE
하나 이상의 데이터값을 갖는 데이터 타입으로 배열과 비슷한 역할을 하고 재사용이 가능합니다.
%ROWTYPE데이터 형과, PL/SQL테이블과 레코드가 복합 데이터 타입에 속합니다.
%ROWTYPE
⊙  테이블이나 뷰 내부의 컬럼 데이터형, 크기, 속석등을 그대로 사용할수 있습니다.
⊙  %ROWTYPE 앞에 오는 것은 데이터 베이스 테이블 이름입니다.
⊙  지정된 테이블의 구조와 동일한 구조를 갖는 변수를 선언할수 있습니다.
⊙  데이터베이스 컬럼들의 수나 DATATYPE을 알지 못할 때 편리 합니다.
⊙  테이블의 데이터 컬럼의 DATATYPE이 변경될 경우 프로그램을 재수정할 필요가 없습니다.
%ROWTYPE 예제 프로시져..
SQL>CREATE OR REPLACE PROCEDURE RowType_Test
        ( p_empno IN emp.empno%TYPE )
    IS
        -- %ROWTYPE 변수 선언
        v_emp   emp%ROWTYPE ;
   BEGIN
        DBMS_OUTPUT.ENABLE;
        -- %ROWTYPE 변수 사용
        SELECT empno, ename, hiredate
        INTO v_emp.empno, v_emp.ename, v_emp.hiredate
        FROM emp
        WHERE empno = p_empno;
       DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || v_emp.empno );
       DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || v_emp.ename );
       DBMS_OUTPUT.PUT_LINE( '입 사 일 : ' || v_emp.hiredate );
   END;
        /
   프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ;  -- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL> EXECUTE RowType_Test(7900);
사원번호 : 7900
사원이름 : JAMES
입 사 일 : 81/12/03
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
PL/SQL 테이블
  PL/SQL 에서의 테이블은 오라클 SQL에서의 테이블과는 다릅니다. PL/SQL에서의 테이블은
일종의 일차원 배열이라고 생각하시면 이해하기 쉬울겁니다.
⊙ 테이블은 크기에 제한이 없으면 그 ROW의 수는 데이터가 들어옴에 따라 자동 증가 합니다.
⊙ BINARY_INTEGER 타입의인덱스 번호로 순서가 정해집니다.
⊙ 하나의 테이블에 한 개의 컬럼 데이터를 저장 합니다.
예제
TYPE prdname_table IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
-- prdname_table 테이블타입으로 prdname_tab변수를 선언해서 사용
prdname_tab   prdname_table ;
PL/SQL 테이블 예제 프로시져..
SQL>CREATE OR REPLACE PROCEDURE Table_Test
    (v_deptno IN emp.deptno%TYPE)
    IS
         -- 각 컬럼에서 사용할 테이블의 선언
         TYPE empno_table IS TABLE OF emp.empno%TYPE
         INDEX BY BINARY_INTEGER;
         TYPE ename_table IS TABLE OF emp.ename%TYPE
         INDEX BY BINARY_INTEGER;
         TYPE sal_table IS TABLE OF emp.sal%TYPE
         INDEX BY BINARY_INTEGER;
         -- 테이블타입으로 변수를 선언해서 사용
         empno_tab  empno_table ;
         ename_tab  ename_table ;
         sal_tab    sal_table;
         i BINARY_INTEGER := 0;
   BEGIN
         DBMS_OUTPUT.ENABLE;
         FOR emp_list IN(SELECT empno, ename, sal FROM emp WHERE deptno = v_deptno) LOOP
          /* emp_list는 자동선언되는 BINARY_INTEGER형 변수로 1씩 증가합니다.
             emp_list대신 다른 문자열 사용가능 */
                i := i + 1;
               -- 테이블 변수에 검색된 결과를 넣습니다
                empno_tab(i) := emp_list.empno ;
                ename_tab(i) := emp_list.ename ;
                sal_tab(i)   := emp_list.sal ;
          END LOOP;
          -- 1부터 i까지 FOR 문을 실행
          FOR cnt IN 1..i LOOP
             -- TABLE변수에 넣은 값을 뿌려줌
             DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || empno_tab(cnt) );
             DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || ename_tab(cnt) );
             DBMS_OUTPUT.PUT_LINE( '사원급여 : ' || sal_tab(cnt) );
          END LOOP;
  END;
  /
프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE Table_Test(10);
사원번호 : 7782
사원이름 : CLARK
사원급여 : 2450
사원번호 : 7839
사원이름 : KING
사원급여 : 5000
사원번호 : 7934
사원이름 : MILLER
사원급여 : 1300
PL/SQL 처리가 정상적으로 완료되었습니다.
 emp 테이블에 있는 데이터의 입력한 부서에 해당하는 사원번호, 사원이름, 사원급여를
 뿌려주는 프로시져 입니다
--------------------------------------------------------------------------------
PLSQL 레코드
여러개의 데이터 타입을 갖는 변수들의 집합입니다.
⊙ 스칼라, RECORD, 또는 PL/SQL TABLE datatype중 하나 이상의 요소로 구성됩니다.
⊙ 논리적 단위로서 필드 집합을 처리할 수 있도록 해 줍니다.
⊙ PL/SQL 테이블과 다르게 개별 필드의 이름을 부여할 수 있고, 선언시 초기화가 가능합니다.
 예제
  TYPE record_test IS RECORD
    ( record_empno   NUMBER,
      record_ename   VARCHAR2(30),
      record_sal        NUMBER);
      prd_record    record_test;
PL/SQL RECORD 예제 프로시져..
SQL> CREATE OR REPLACE PROCEDURE Record_Test
      ( p_empno IN emp.empno%TYPE )
     IS
         -- 하나의 레코드의 세가지의 변수타입 선언
         TYPE emp_record IS RECORD
         (v_empno    NUMBER,
          v_ename    VARCHAR2(30),
          v_hiredate  DATE );
         emp_rec   emp_record ;
   BEGIN
         DBMS_OUTPUT.ENABLE;
         -- 레코드의 사용
         SELECT empno, ename, hiredate
         INTO emp_rec.v_empno, emp_rec.v_ename, emp_rec.v_hiredate
         FROM emp
         WHERE empno = p_empno;
        DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || emp_rec.v_empno );
        DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || emp_rec.v_ename );
        DBMS_OUTPUT.PUT_LINE( '입 사 일 : ' || emp_rec.v_hiredate );
   END;
 /
프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ;  -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE Record_Test(7369);
사원번호 : 7369
사원이름 : SMITH
입 사 일 : 80/12/17
PL/SQL 처리가 정상적으로 완료되었습니다.
 %ROWTYPE예제와 비교해 보세요
--------------------------------------------------------------------------------
PL/SQL Table of Record
⊙ PL/SQL TABLE변수 선언과 비슷하며 데이터타입을 %ROWTYPE으로 선언하면 됩니다.
⊙ PL/SQL TABLE과 RECORD의 복합 기능을 합니다.
DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
-- Each element of dept_table  is a record
dept_table   dept_table_type ;
PL/SQL TABLE OF RECORD 예제 프로시져..
CREATE OR REPLACE PROCEDURE Table_Test
IS
        i BINARY_INTEGER := 0;
     -- PL/SQL Table of Record의 선언
    TYPE dept_table_type IS TABLE OF dept%ROWTYPE
     INDEX BY BINARY_INTEGER;
     dept_table dept_table_type;
BEGIN
    FOR dept_list IN (SELECT * FROM dept) LOOP
                i:= i+1;
        -- TABLE OF RECORD에 데이터 보관
        dept_table(i).deptno := dept_list.deptno ;
        dept_table(i).dname := dept_list.dname ;
        dept_table(i).loc   := dept_list.loc ;
    END LOOP;
    FOR cnt IN 1..i LOOP
     -- 데이터 출력
     DBMS_OUTPUT.PUT_LINE( '부서번호 : ' || dept_table(cnt).deptno ||
                                          '부서명 : ' ||  dept_table(cnt).dname ||
                                          '위치 : ' || dept_table(cnt).loc );
    END LOOP;
 END;
/
SQL>set serveroutput on;
SQL>exec Table_test;
부서번호 : 10부서명 : ACCOUNTING위치 : NEW_YORK
부서번호 : 20부서명 : RESEARCH위치 : DALLAS
부서번호 : 30부서명 : 인사과위치 : CHICAGO
부서번호 : 40부서명 : OPERATIONS위치 : BOS%TON
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
INSERT
PL/SQL에서의 INSERT 문은 SQL과 비슷합니다.
사원 등록 예제 프로시져..
SQL> CREATE OR REPLACE PROCEDURE Insert_Test
        ( v_empno  IN emp.empno%TYPE,
          v_ename  IN emp.ename%TYPE,
          v_deptno IN emp.deptno%TYPE )
        IS
      BEGIN
          DBMS_OUTPUT.ENABLE;
          INSERT INTO emp(empno, ename, hiredate, deptno)
          VALUES(v_empno, v_ename, sysdate, v_deptno);
          DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || v_empno );
          DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || v_ename );
          DBMS_OUTPUT.PUT_LINE( '사원부서 : ' || v_deptno );
          DBMS_OUTPUT.PUT_LINE( '데이터 입력 성공 ' );
       END ;
      /
프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ;  -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL>  EXECUTE Insert_Test(1000, 'brave', 20);
사원번호 : 1000
사원이름 : brave
사원부서 : 20
데이터 입력 성공
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
UPDATE
상품 수정 예제 프로시저..
※ 특정 사원의 급여를 일정%센트 인상/인하하는 프로시져
SQL>CREATE OR REPLACE PROCEDURE Update_Test
       ( v_empno IN    emp.empno%TYPE,       -- 급여를 수정한 사원의 사번
         v_rate     IN    NUMBER )                     -- 급여의 인상/인하율
         IS
         -- 수정 데이터를 확인하기 위한 변수 선언
         v_emp  emp%ROWTYPE ;
         BEGIN
         DBMS_OUTPUT.ENABLE;
         UPDATE emp
         SET sal = sal+(sal * (v_rate/100))   -- 급여를 계산
         WHERE empno = v_empno ;
         DBMS_OUTPUT.PUT_LINE( '데이터 수정 성공 ' );
         -- 수정된 데이터 확인하기 위해 검색
         SELECT empno, ename, sal
         INTO v_emp.empno, v_emp.ename, v_emp.sal
         FROM emp
         WHERE empno = v_empno ;
         DBMS_OUTPUT.PUT_LINE( ' **** 수 정 확 인 **** ');
         DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || v_emp.empno );
         DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || v_emp.ename );
         DBMS_OUTPUT.PUT_LINE( '사원급여 : ' || v_emp.sal );
         END ;
         /
프로시저가 생성되었습니다.
프로시저 실행
SQL> SET SERVEROUTPUT ON ;  -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE Update_Test(7900, -10);
데이터 수정 성공
**** 수 정 확 인 ****
사원번호 : 7900
사원이름 : JAMES
사원급여 : 855
PL/SQL 처리가 정상적으로 완료되었습니다.
7900번 사원의 급여를 10% 인하했습니다.
--------------------------------------------------------------------------------
DELETE
사원 삭제 예제 프로시저..
SQL> CREATE OR REPLACE PROCEDURE Delete_Test
    ( p_empno IN  emp.empno%TYPE )
        IS
        -- 삭제 데이터를 확인하기 레코드 선언
        TYPE del_record IS  RECORD
        ( v_empno      emp.empno%TYPE,
          v_ename      emp.ename%TYPE,
          v_hiredate    emp.hiredate%TYPE) ;
          v_emp  del_record ;
        BEGIN
        DBMS_OUTPUT.ENABLE;
         -- 삭제된 데이터 확인용 쿼리
         SELECT empno, ename, hiredate
         INTO v_emp.v_empno, v_emp.v_ename, v_emp.v_hiredate
         FROM emp
         WHERE empno = p_empno ;
        DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || v_emp.v_empno );
        DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || v_emp.v_ename );
        DBMS_OUTPUT.PUT_LINE( '입 사 일 : ' || v_emp.v_hiredate );
        -- 삭제 쿼리
        DELETE
        FROM emp
        WHERE empno = p_empno ;
        DBMS_OUTPUT.PUT_LINE( '데이터 삭제 성공 ' );
       END;
 /
프로시저가 생성되었습니다.
프로시저 실행 (결과화면)
SQL> SET SERVEROUTPUT ON ;  -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE Delete_Test(7900);
사원번호 : 7900
사원이름 : JAMES
입 사 일 : 81/12/03
데이터 삭제 성공
PL/SQL 처리가 정상적으로 완료되었습니다.
※ 7900사원을 삭제했습니다.
--------------------------------------------------------------------------------
FOR LOOP
FOR LOOP문
  -  index 는 자동 선언되는 binary_integer 형 변수이고. 1씩 증가합니다.
  -  reverse 옵션이 사용될 경우 index 는 upper_bound에서 lower_bound로 1씩 감소합니다.
  -  IN 다음에는 coursor나 select 문이 올수 있습니다.
FOR문 예제
DECLARE
-- 사원 이름을 출력하기 위한 PL/SQL 테이블 선언
TYPE ename_table IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
-- 사원 급여를 출력하기 위한 PL/SQL 테이블 선언
TYPE sal_table IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;
ename_tab    ename_table;
sal_tab      sal_table;
i BINARY_INTEGER := 0;
BEGIN
DBMS_OUTPUT.ENABLE;
FOR emp_list IN  (SELECT ename, sal FROM emp WHERE deptno = 10) LOOP
   i := i +1 ;
   ename_tab(i) := emp_list.ename;     -- 테이블에 상품 이름을 저장
   sal_tab(i)      := emp_list.sal;          -- 테이블에 상품 가격을 저장
END LOOP;
FOR cnt IN   1..i   LOOP         --  화면에 출력
   DBMS_OUTPUT.PUT_LINE('사원이름 : ' || ename_tab(cnt));
   DBMS_OUTPUT.PUT_LINE('사원급여 : ' || sal_tab(cnt));
END LOOP;
END;
/
사원이름 : CLARK
사원급여 : 2450
사원이름 : KING
사원급여 : 5000
사원이름 : MILLER
사원급여 : 1300
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
LOOP문, WHILE문
EXIT 문이 사용되었을 경우, 무조건 LOOP문을 빠져나갑니다,
EXITH WHEN 이 사용될 경우  WHEN 절에 LOOP를 빠져 나가는 조건을 제어할수 있습니다.
LOOP 문 예제
SQL> SET SERVEROUTPUT ON ;  -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL>  DECLARE
          v_cnt number(3) := 100;
          BEGIN
          DBMS_OUTPUT.ENABLE ;
          LOOP
              INSERT INTO emp(empno, ename , hiredate)
              VALUES(v_cnt, 'test'||to_char(v_cnt),  sysdate);
              v_cnt := v_cnt+1;
              EXIT WHEN v_cnt > 110;
          END LOOP;
          DBMS_OUTPUT.PUT_LINE('데이터 입력 완료');
          DBMS_OUTPUT.PUT_LINE(v_cnt-100 || '개의 데이터가 입력되었습니다');
          END;
         /
데이터 입력 완료
11개의 데이터가 입력되었습니다
PL/SQL 처리가 정상적으로 완료되었습니다.
WHILE LOOP 문
WHILE LOOP문은 FOR 문과 비슷하며 조건이 TRUE일 경우만 반복되는 LOOP문 입니다.
예제
WHILE cnt < 10 LOOP
   INSERT INTO emp(empno, ename , hiredate)
   VALUES(emp_seq.nextval, 'test',  sysdate);
cnt := cnt + 1 ;
END LOOP ;
cnt가 10이면 반복 While Loop를 탈출
EXIT WHEN 조건 => 조건이 만족할 때 반복 loop를 탈출합니다. .
--------------------------------------------------------------------------------
조건제어(IF)
IF문 예제 프로시저..
SQL>CREATE OR REPLACE PROCEDURE Dept_Search
(p_empno IN emp.empno%TYPE )
IS
v_deptno emp.deptno%type ;
BEGIN
DBMS_OUTPUT.ENABLE;
SELECT deptno
INTO v_deptno
FROM emp
WHERE empno = p_empno ;
IF v_deptno = 10 THEN
DBMS_OUTPUT.PUT_LINE( ' ACCOUNTING 부서 사원입니다. ' );
ELSIF v_deptno = 20 THEN
DBMS_OUTPUT.PUT_LINE( ' RESEARCH 부서 사원입니다. ' );
ELSIF v_deptno = 20 THEN
DBMS_OUTPUT.PUT_LINE( ' SALES 부서 사원입니다. ' );
ELSIF v_deptno = 20 THEN
DBMS_OUTPUT.PUT_LINE( ' OPERATIONS 부서 사원입니다. ' );
ELSE
DBMS_OUTPUT.PUT_LINE( ' 부서가 없네요... ' );
END IF ;
END ;
/
프로시저가 생성되었습니다.
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
프로시저 실행
SQL> EXECUTE Dept_Search(7900);
부서가 없네요...
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> EXECUTE Dept_Search(7369);
RESEARCH 부서 사원입니다.
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
암시적 커서(Implicit Cursor)
  암시적인 커서는 오라클이나 PL/SQL실행 메커니즘에 의해 처리되는 SQL문장이 처리되는 곳에 대한
  익명의 에드레스입니다. 오라클 데이터 베이스에서 실행되는 모든 SQL문장은 암시적인 커서이며
  그것들과 함께 모든 암시적인 커서 속성이 사용될 수 있습니다.
 -암시적 커서의 속성
◈ SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수
◈ SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE
◈ SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE
◈ SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색
(암시적 커서는 SQL 문이 실행되는 순간 자동으로 열림과 닫힘 실행)
암시적 커서 예제
CREATE OR REPLACE PROCEDURE Implicit_Cursor
(p_empno emp.empno%TYPE)
is
v_sal  emp.sal%TYPE;
v_update_row NUMBER;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = p_empno ;
-- 검색된 데이터가 있을경우
IF  SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재합니다 : '||v_sal);
END IF;
UPDATE emp
SET sal = sal*1.1
WHERE empno = p_empno;
-- 수정한 데이터의 카운트를 변수에 저장
v_update_row := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원 수 : '|| v_update_row);
END;
프로시저가 생성되었습니다.
SQL> SET SERVEROUTPUT ON ;  -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE Implicit_Cursor(7369);
검색한 데이터가 존재합니다 : 880
급여가 인상된 사원 수 : 1
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
Explicit Cursor
▣ 커서란 무엇인가?
◈커서는 Private SQL의 작업영역 입니다.
◈오라클 서버에 의해 실행되는 모든 SQL문은 연관된 각각의 커서를 소유하고 있습니다.
◈ 커서의 종류
   - 암시적 커서 : 모든 DML과 PL/SQL SELECT문에 대해 선언됩니다.
   - 명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서입니다.
▣ Explicit Cursor의 흐름도?
▣ 문법(Syntax)
▣ 커서 열기(OPEN)
◈커서의 열기는 OPEN문을 사용합니다.
◈커서안의 검색이 실행되며 아무런 데이터행을 추출하지 못해도 에러가 발생하지 않습니다.
    OPEN   cursor_name;
▣ 커서 패치(FETCH)
◈커서의 FETCH는 현재 데이터 행을 OUTPUT변수에 반환합니다.
◈커서의 SELECT문의 컬럼의 수와 OUTPUT변수의 수가 동일해야 합니다.
◈커서 컬럼의 변수의 타입과 OUTPUT변수의 데이터 타입도 동일해야 합니다.
◈커서는 한 라인씩 데이터를 패치 합니다.
    FETCH   cursor_name INTO variable1, variable2 ;
▣ 커서 닫기(CLOSE)
◈사용을 맞친 커서는 반드시 닫아 주어야 합니다.
◈필요하다면 커서를 다시 열 수 있습니다.
◈커서를 닫은 상태에서 FETCH를 할 수 없습니다.
    CLOSE   cursor_name;
 Explicit Cursor 예제
특정 부서의 평균급여와 사원수를 출력..
--------------------------------------------------------------------------------
FOR문에서 커서 사용(Cursor FOR Loops)
◈ FOR문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생하므로 따로 기술할
    필요가 없습니다
◈ 레코드 이름도 자동 선언되므로 따로 선언할 필요가 없습니다.
 FOR문에서 커서 사용 예제
부서별 사원수와 급여 합계를 구하는 프로시저입니다.
SQL> CREATE OR REPLACE PROCEDURE ForCursor_Test
        IS
        CURSOR dept_sum IS
        SELECT b.dname, COUNT(a.empno) cnt, SUM(a.sal) salary
        FROM emp a, dept b
        WHERE a.deptno = b.deptno
        GROUP BY b.dname;
     BEGIN
       -- Cursor를 FOR문에서 실행시킨다
       FOR emp_list IN dept_sum LOOP
          DBMS_OUTPUT.PUT_LINE('부서명 : ' || emp_list.dname);
          DBMS_OUTPUT.PUT_LINE('사원수 : ' || emp_list.cnt);
          DBMS_OUTPUT.PUT_LINE('급여합계 : ' || emp_list.salary);
       END LOOP;
   EXCEPTION
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
   END;
   /
프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE ForCursor_Test;
부서명 : ACCOUNTING
사원수 : 3
급여합계 : 8750
부서명 : RESEARCH
사원수 : 6
급여합계 : 10875
부서명 : SALES
사원수 : 6
급여합계 : 9305
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
명시적 커서의 속성(Explicit Cursor Attributes)
▣ Explicit Cursor 속성
◈%ISOPEN
      - 커서가 OPEN되어 있으면 TRUE
      -  %ISOPEN속성을 이용하여 커서가 열려있는지 알 수 있습니다.
◈%NOTFOUND
      - 패치한 데이터가 행을 반환하지 않으면 TRUE
      -  %NOTFOUND속성을 이용하여 루프를 종료할 시점을 찾습니다.
◈%FOUND
      - 패치한 데이터가 행을 반환하면 TRUE
◈ %ROWCOUNT
     - 현재까지 반환된 모든 데이터 행의 수
      - %ROWCOUNT속성을 이용하여 정확한 숫자만큼의 행을 추출합니다.
커서의 속성 예제
SQL>CREATE OR REPLACE PROCEDURE AttrCursor_Test
       IS
       v_empno     emp.empno%TYPE;
       v_ename     emp.ename%TYPE;
       v_sal          emp.sal%TYPE;
      CURSOR emp_list IS
         SELECT empno, ename, sal
         FROM emp;
    BEGIN
      DBMS_OUTPUT.ENABLE;
      OPEN emp_list;
      LOOP
        FETCH emp_list INTO v_empno, v_ename, v_sal;
           -- 데이터를 찾지 못하면 빠져 나갑니다
           EXIT WHEN emp_list%NOTFOUND;
      END LOOP;
       DBMS_OUTPUT.PUT_LINE('전체데이터 수 ' || emp_list%ROWCOUNT);
     CLOSE emp_list;
     EXCEPTION
       WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
    END;
   /
프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE AttrCursor_Test;
전체데이터 수 15
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
파라미터가 있는 커서(Cursors with Parameters)
◈ 커서가 열리고 질의가 실행되면 매개 변수 값을 커서에 전달한다.
◈ 다른 active set을 원할때 마다 explicit커서를 따로 선언해야 한다
▣ 문법(Syntax)
파라미터가 있는 커서 예제
SQL> CREATE OR REPLACE PROCEDURE ParamCursor_Test
        (param_deptno   emp.deptno%TYPE)
        IS
         v_ename     emp.ename%TYPE;
       -- Parameter가 있는 커서의 선언
        CURSOR emp_list(v_deptno emp.deptno%TYPE) IS
        SELECT ename
        FROM emp
        WHERE deptno = v_deptno;
       BEGIN
        DBMS_OUTPUT.ENABLE;
        DBMS_OUTPUT.PUT_LINE(' ****** 입력한 부서에 해당하는 사람들 ****** ');
       -- Parameter변수의 값을 전달(OPEN될 때 값을 전달한다)
        FOR emplst IN emp_list(param_deptno) LOOP
          DBMS_OUTPUT.PUT_LINE('이름 : ' || emplst.ename);
        END LOOP;
        EXCEPTION
          WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
        END;
        /
프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE ParamCursor_Test(10);
****** 입력한 부서에 해당하는 사람들 ******
이름 : CLARK
이름 : KING
이름 : MILLER
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
The WHERE CURRENT OF Clause
◆ WHERE CURRENT OF
   - ROWID를 이용하지 않고도 현재 참조하는 행을 갱신하고 삭제할 수 있게 합니다.
   - 추가적으로 FETCH문에 의해 가장 최근에 처리된 행을 참조하기 위해서
      "WHERE CURRENT OF 커서이름 "    절로 DELETE나 UPDATE문 작성이 가능합니다..
   - 이 절을 사용할 때 참조하는 커서가 있어야 하며,
      FOR UPDATE절이 커서 선언 query문장 안에 있어야 합니다.
      그렇지 않으면 error가 발생합니다..
 WHERE CURRENT OF 예제
SQL> SET SERVEROUTPUT ON ;  -- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SQL>CREATE OR REPLACE PROCEDURE where_current
         IS
        CURSOR emp_list IS
             SELECT empno
             FROM emp
             WHERE empno = 7934
             FOR UPDATE;
      BEGIN
        --DBMS_OUTPUT.PUT_LINE명령을 사용하기 위해서
        DBMS_OUTPUT.ENABLE;
        FOR emplst IN emp_list LOOP
              --emp_list커서에 해당하는 사람의 직업을  SALESMAN으로 업데이트 시킵니다.
              UPDATE emp
              SET job = 'SALESMAN'
             WHERE CURRENT OF emp_list;
            DBMS_OUTPUT.PUT_LINE('수정 성공');
        END LOOP;
        EXCEPTION
           WHEN OTHERS THEN
                -- 에러 발생시 에러 메시지 출력
                DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END;
--먼저 데이터를 확인해 보세용
SQL> SELECT job FROM emp WHERE empno = 7934;
JOB
---------
CLERK
 --PLSQL을 실행시키고..
SQL> EXECUTE where_current;
수정 성공  --DBMS_OUTPUT.PUT_LINE명령으로 출력한거..
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 다시 데이터를 확인하면 변경된 것을 볼 수 있습니다.
SQL>  SELECT job FROM emp WHERE empno = 7934;
JOB
---------
SALESMAN
--------------------------------------------------------------------------------
예외(Exception)
▣ 예외(Exception)란?
◈ 오라클 PL/SQL의 오류를 예외라고 부릅니다.
◈ 오류는 PL/SQL을 컴파일 할때 문법적인 오류로 발생하는 컴파일 타임 오류와,
    프로그램을 실행할때 발생하는 실행타임 오류로 구분할수 있습니다.
▣ PL/SQL오류의 종류
 예  외
  설  명
  처  리
   미리 정의된 오라클 서버 오류
(Predefined Oracle Server)
   PL/SQL에서 자주 발생하는
  약20개의 오류
   선언할 필요도 없고, 발생시에
예외 절로 자동 트랩(Trap)
된다.
  미리 정의되지 않은 오라클
  서버 오류
  (Non-Predefined Oracle   Server)
   미리 정의된 오라클 서버
오류를 제외한
모든 오류
   선언부에서 선언해야 하고 발생시
  자동 트랩된다.
  사용자 정의 오류
  (User-Defined)
   개발자가 정한 조건에
만족하지 않을경우
발생하는 오류
    선언부에서 선언하고 실행부에서
   RAISE문을 사용하여 발생시켜야
   한다
▣ Execption 문법(Syntax)
◈ WHEN OTHERS절은 맨 마지막에 옵니다.
◈ 예외 처리절은 EXCEPTION부터 시작합니다.
◈ 허용합니다.
◈ 예외가 발생하면 여러 개의 예외 처리부 중에 하나의 예외 처리부에 트랩(Trap)됩니다.
--------------------------------------------------------------------------------
미리 정의된 예외(Predefined Exceptions)
  ◈ 오라클 PL/SQL은 자주 일어나는 몇가지 예외를 미리 정의해 놓았으며,
      이러한 예외는 개발자가 따로 선언할 필요가 없습니다
▣ 미리 정의된 예외의 종류?
◈ NO_DATA_FOUND : SELECT문이 아무런 데이터 행을 반환하지 못할때
◈ TOO_MANY_ROWS : 하나만 리턴해야하는 SELECT문이 하나 이상의 행을 반환할 때
◈ INVALID_CURSOR : 잘못된 커서 연산
◈ ZERO_DIVIDE : 0으로 나눌때
◈ DUP_VAL_ON_INDEX : UNIQUE 제약을 갖는 컬럼에 중복되는 데이터가 INSERT될때
   이 외에도 몇 개가 더 있습니다.
미리 정의된 예외 예제
SQL> CREATE OR REPLACE PROCEDURE PreException_test
         (v_deptno  IN emp.empno%TYPE)
   IS
       v_emp   emp%ROWTYPE;
   BEGIN
      DBMS_OUTPUT.ENABLE;
      SELECT empno, ename, deptno
      INTO v_emp.empno, v_emp.ename, v_emp.deptno
      FROM emp
      WHERE deptno = v_deptno ;
      DBMS_OUTPUT.PUT_LINE('사번 : ' || v_emp.empno);
      DBMS_OUTPUT.PUT_LINE('이름 : ' || v_emp.ename);
      DBMS_OUTPUT.PUT_LINE('부서번호 : ' || v_emp.deptno);
   EXCEPTION
      WHEN   DUP_VAL_ON_INDEX   THEN
          DBMS_OUTPUT.PUT_LINE('데이터가 존재 합니다.');
          DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러 발생');
      WHEN   TOO_MANY_ROWS   THEN
        DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS에러 발생');
      WHEN   NO_DATA_FOUND   THEN
        DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND에러 발생');
      WHEN   OTHERS   THEN
        DBMS_OUTPUT.PUT_LINE('기타 에러 발생');
  END;
  /
프로시저가 생성되었습니다.
프로시저 실행
SQL> SET SERVEROUTPUT ON ;  -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE PreException_Test(20);
TOO_MANY_ROWS에러 발생
PL/SQL 처리가 정상적으로 완료되었습니다.
※ TOO_MANY_ROWS에러를 타는 이유?
 - SELECT문의 결과가 1개 이상의 행을 리턴하기 때문이다..
 - TOO_MANY_ROWS를 피하기 위해서는 FOR문이나 LOOP문으로 SELECT문을 처리해야 합니다.
아래와 같이 바꾸면 에러가 발생하지 않습니다.
     FOR  emp_list  IN
         (SELECT empno, ename, deptno
         FROM emp
         WHERE deptno = v_deptno)   LOOP
         DBMS_OUTPUT.PUT_LINE('사번 : ' || emp_list.empno);
         DBMS_OUTPUT.PUT_LINE('이름 : ' || emp_list.ename);
         DBMS_OUTPUT.PUT_LINE('부서번호 : ' || emp_list.deptno);
     END LOOP;
--------------------------------------------------------------------------------
미리 정의되지 않은 예외(Non-Predefined Exception)
◈ STEP 1 : 예외의 이름을 선언(선언절)
◈ STEP 2 : PRAGMA EXCEPTION_INIT문장으로 예외의 이름과 오라클 서버
                  오류 번호를 결합(선언절)
◈ STEP 3 : 예외가 발생할 경우 해당 예외를 참조한다(예외절)
 미리 정의되지 않은 예외 예제
SQL> CREATE OR REPLACE PROCEDURE NonPreException_Test
       IS
          not_null_test EXCEPTION;    -- STEP 1
          /* not_null_test는 선언된 예외 이름
             -1400 Error 처리번호는 표준 Oracle7 Server Error 번호 */
          PRAGMA EXCEPTION_INIT(not_null_test, -1400);       -- STEP 2
        BEGIN
          DBMS_OUTPUT.ENABLE;
        -- empno를 입력하지 않아서 NOT NULL 에러 발생
        INSERT INTO emp(ename, deptno)
        VALUES('tiger', 30);
        EXCEPTION
        WHEN not_null_test THEN    -- STEP 3
            DBMS_OUTPUT.PUT_LINE('not null 에러 발생 ');
       END;
        /
프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ;    -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE NonPreException_Test;
not null 에러 발생
PL/SQL 처리가 정상적으로 완료되었습니다.
--------------------------------------------------------------------------------
사용자 정의 예외(User-Defined Exceptions)
◈ 오라클 저장함수 RAISE_APPLICATION_ERROR를 사용하여 오류코드 -20000부터
   -20999의 범위 내에서 사용자 정의 예외를 만들수 있습니다.
◈ STEP 1 : 예외의 이름을 선언(선언절)
◈ STEP 2 : RAISE문을 사용하여 직접적으로 예외를 발생시킨다(실행절)
◈ STEP 3 : 예외가 발생할 경우 해당 예외를 참조한다(예외절)
사용자 정의 예외 예제 Procedure
입력한 부서의 사원이 5명보다 적으면 사용자 정의 예외가 발생하는 예제 입니다.
SQL>CREATE OR REPLACE PROCEDURE User_Exception
        (v_deptno IN emp.deptno%type )
      IS
       -- 예외의 이름을 선언
       user_define_error EXCEPTION;     -- STEP 1
       cnt     NUMBER;
     BEGIN
       DBMS_OUTPUT.ENABLE;
       SELECT COUNT(empno)
       INTO cnt
       FROM emp
       WHERE deptno = v_deptno;
       IF cnt < 5 THEN
         -- RAISE문을 사용하여 직접적으로 예외를 발생시킨다
          RAISE user_define_error;         -- STEP 2
       END IF;
      EXCEPTION
        -- 예외가 발생할 경우 해당 예외를 참조한다.
       WHEN user_define_error THEN      -- STEP 3
           RAISE_APPLICATION_ERROR(-20001, '부서에 사원이 몇명 안되네요..');
   END;
  /
프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE user_exception(10);
BEGIN user_exception(10); END;
 *
1행에 오류:
ORA-20001: 부서에 사원이 몇명 안되네요..
ORA-06512: "SCOTT.USER_EXCEPTION", 줄 17에서
ORA-06512: 줄 1에서
 10부서의 사원이 5보다 적기 때문에 사용자 정의 예외가 발생했습니다.
SQL> EXECUTE user_exception(20);
PL/SQL 처리가 정상적으로 완료되었습니다.
20부서로 실행을 하면 에러가 발생하지 않는 것 을 알 수 있습니다..
--------------------------------------------------------------------------------
사용자 정의 예외(User-Defined Exceptions)
◈ 오라클 저장함수 RAISE_APPLICATION_ERROR를 사용하여 오류코드 -20000부터
   -20999의 범위 내에서 사용자 정의 예외를 만들수 있습니다.
◈ STEP 1 : 예외의 이름을 선언(선언절)
◈ STEP 2 : RAISE문을 사용하여 직접적으로 예외를 발생시킨다(실행절)
◈ STEP 3 : 예외가 발생할 경우 해당 예외를 참조한다(예외절)
사용자 정의 예외 예제 Procedure
입력한 부서의 사원이 5명보다 적으면 사용자 정의 예외가 발생하는 예제 입니다.
SQL>CREATE OR REPLACE PROCEDURE User_Exception
        (v_deptno IN emp.deptno%type )
      IS
       -- 예외의 이름을 선언
       user_define_error EXCEPTION;     -- STEP 1
       cnt     NUMBER;
     BEGIN
       DBMS_OUTPUT.ENABLE;
       SELECT COUNT(empno)
       INTO cnt
       FROM emp
       WHERE deptno = v_deptno;
       IF cnt < 5 THEN
         -- RAISE문을 사용하여 직접적으로 예외를 발생시킨다
          RAISE user_define_error;         -- STEP 2
       END IF;
      EXCEPTION
        -- 예외가 발생할 경우 해당 예외를 참조한다.
       WHEN user_define_error THEN      -- STEP 3
           RAISE_APPLICATION_ERROR(-20001, '부서에 사원이 몇명 안되네요..');
   END;
  /
프로시져가 생성되었습니다.
실행 결과
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
SQL> EXECUTE user_exception(10);
BEGIN user_exception(10); END;
 *
1행에 오류:
ORA-20001: 부서에 사원이 몇명 안되네요..
ORA-06512: "SCOTT.USER_EXCEPTION", 줄 17에서
ORA-06512: 줄 1에서
 10부서의 사원이 5보다 적기 때문에 사용자 정의 예외가 발생했습니다.
SQL> EXECUTE user_exception(20);
PL/SQL 처리가 정상적으로 완료되었습니다.
20부서로 실행을 하면 에러가 발생하지 않는 것 을 알 수 있습니다..
--------------------------------------------------------------------------------
Package(패키지)
package?
  ◆ 패키지(package)는 오라클 데이터베이스에 저장되어 있는 서로 관련있는 PL/SQL
      프로지져와 함수들의 집합 입니다
  ◆ 패키지는 선언부와 본문 두 부분으로 나누어 집니다.
패키지 선언부
- 선언절은 패키지에 포함될 PL/SQL 프로시저나, 함수, 커서, 변수, 예외절을 선언 합니다.
- 패키지 선언부에서 선언한 모든 요소들은 패키지 전체에 적용됩니다.
- 즉 선언부에서 선언한 변수는 PUBLIC 변수로 사용 됩니다.
패키지 본문
- 패키지 본문은 패키지에서 선언된 부분의 실행을 정의 합니다.
- 즉 실재 프로시져나 함수의 내용에 해당하는 부분이 옵니다.
아주 간단한 패키지 예제입니다.
4개의 프로시저가 존재하고 있습니다.
프로시저명
 프로시저 기능
all_emp_info
   모든 사원의  사원 정보 (사번, 성명, 입사일)
all_sal_info
   모든 사원의  급여 정보 (평균급여, 최고급여, 최소급여)
dept_emp_info
   특정 부서의  사원 정보 (사번, 성명, 입사일)
dept_sql_info
   특정 부서의  급여 정보 (평균급여, 최고급여, 최소급여)
위 4개의 프로시저를 가지고 패키지를 생성하겠습니다.
all_sal_info
   1. 모든 사원의  사원 정보(사번, 성명, 입사일)를 보여 주는 프로시져
SQL> CREATE OR REPLACE PROCEDURE all_emp_info
          IS
            CURSOR emp_cursor IS
            SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
            FROM emp
            ORDER BY hiredate;
          BEGIN
            FOR  aa  IN emp_cursor LOOP
                DBMS_OUTPUT.PUT_LINE('사번 : ' || aa.empno);
                DBMS_OUTPUT.PUT_LINE('성명 : ' || aa.ename);
                DBMS_OUTPUT.PUT_LINE('입사일 : ' || aa.hiredate);
            END LOOP;
          EXCEPTION
                WHEN OTHERS THEN
                        DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
          END;
Procedure created.
all_sal_info
   2. 모든 사원의  급여 정보 (평균급여, 최고급여, 최소급여)
SQL>CREATE OR REPLACE PROCEDURE all_sal_info
        IS
            CURSOR emp_cursor IS
            SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
            FROM emp;
        BEGIN
            FOR  aa  IN emp_cursor LOOP
                DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || aa.avg_sal);
                DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || aa.max_sal);
                DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || aa.min_sal);
            END LOOP;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
        END;
/
Procedure created.
dept_emp_info
   3. 특정 부서의  사원 정보 (사번, 성명, 입사일)
SQL> CREATE OR REPLACE PROCEDURE dept_emp_info
          (v_deptno IN  NUMBER)
          IS
                CURSOR emp_cursor IS
                SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
                FROM emp
                WHERE deptno = v_deptno
                ORDER BY hiredate;
          BEGIN
            FOR  aa  IN emp_cursor LOOP
                DBMS_OUTPUT.PUT_LINE('사번 : ' || aa.empno);
                DBMS_OUTPUT.PUT_LINE('성명 : ' || aa.ename);
                DBMS_OUTPUT.PUT_LINE('입사일 : ' || aa.hiredate);
            END LOOP;
          EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
          END;
dept_sql_info
   4. 특정 부서의  급여 정보 (평균급여, 최고급여, 최소급여)
SQL>CREATE OR REPLACE PROCEDURE dept_sal_info
          (v_deptno IN  NUMBER)
        IS
            CURSOR emp_cursor IS
            SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
            FROM emp
            WHERE deptno = v_deptno;
        BEGIN
            FOR  aa  IN emp_cursor LOOP
                DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || aa.avg_sal);
                DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || aa.max_sal);
                DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || aa.min_sal);
            END LOOP;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
        END;
선언부를 먼저 생성 합니다.
package 예제 (선언부)
SQL>CREATE OR REPLACE PACKAGE emp_info AS
            PROCEDURE all_emp_info;                                           -- 모든 사원의  사원 정보
            PROCEDURE all_sal_info;                                             -- 모든 사원의  급여 정보
            PROCEDURE dept_emp_info (v_deptno IN  NUMBER) ;     -- 특정 부서의  사원 정보
            PROCEDURE dept_sal_info (v_deptno IN  NUMBER) ;       -- 특정 부서의  급여 정보
        END emp_info;
Package created.
선언부를 생성 하고 나서 본문 부분을 생성 합니다.
package 예제 (본문)
SQL>CREATE OR REPLACE PACKAGE BODY emp_info AS
                -- 모든 사원의  사원 정보
                PROCEDURE all_emp_info
                IS
                        CURSOR emp_cursor IS
                        SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
                        FROM emp
                        ORDER BY hiredate;
                BEGIN
                        FOR  aa  IN emp_cursor LOOP
                                DBMS_OUTPUT.PUT_LINE('사번 : ' || aa.empno);
                                DBMS_OUTPUT.PUT_LINE('성명 : ' || aa.ename);
                                DBMS_OUTPUT.PUT_LINE('입사일 : ' || aa.hiredate);
                        END LOOP;
                EXCEPTION
                        WHEN OTHERS THEN
                                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
                END all_emp_info;
                 -- 모든 사원의  급여 정보
                PROCEDURE all_sal_info
                IS
                        CURSOR emp_cursor IS
                        SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
                        FROM emp;
                BEGIN
                        FOR  aa  IN emp_cursor LOOP
                                DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || aa.avg_sal);
                                DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || aa.max_sal);
                                DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || aa.min_sal);
                        END LOOP;
                EXCEPTION
                        WHEN OTHERS THEN
                                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
                END all_sal_info;
                --특정 부서의  사원 정보
                PROCEDURE dept_emp_info (v_deptno IN  NUMBER)
                IS
                        CURSOR emp_cursor IS
                        SELECT empno, ename, to_char(hiredate, 'RRRR/MM/DD') hiredate
                        FROM emp
                        WHERE deptno = v_deptno
                        ORDER BY hiredate;
                BEGIN
                        FOR  aa  IN emp_cursor LOOP
                                DBMS_OUTPUT.PUT_LINE('사번 : ' || aa.empno);
                                DBMS_OUTPUT.PUT_LINE('성명 : ' || aa.ename);
                                DBMS_OUTPUT.PUT_LINE('입사일 : ' || aa.hiredate);
                        END LOOP;
                EXCEPTION
                        WHEN OTHERS THEN
                                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
                END dept_emp_info;
                --특정 부서의  급여 정보
                PROCEDURE dept_sal_info (v_deptno IN  NUMBER)
                IS
                        CURSOR emp_cursor IS
                        SELECT round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
                        FROM emp
                        WHERE deptno = v_deptno;
                BEGIN
                        FOR  aa  IN emp_cursor LOOP
                                DBMS_OUTPUT.PUT_LINE('전체급여평균 : ' || aa.avg_sal);
                                DBMS_OUTPUT.PUT_LINE('최대급여금액 : ' || aa.max_sal);
                                DBMS_OUTPUT.PUT_LINE('최소급여금액 : ' || aa.min_sal);
                        END LOOP;
                EXCEPTION
                        WHEN OTHERS THEN
                                DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
                END dept_sal_info;
        END emp_info;
         /
 Package body created.
패키지의 실행
패키지의 실행은 패키지 명 다음에 .을 찍고 프로시저냐 함수 명을 써 줍니다.
먼저 set serveroutput on을 실행한후..
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
다음 명령들을 실행해 보세요..
SQL> exec emp_info.all_emp_info;
SQL> exec emp_info.all_sal_info;
SQL> exec emp_info.dept_emp_info(10);
SQL> exec emp_info.dept_sal_info(10);
--------------------------------------------------------------------------------
Trigger(트리거)
트리거란?
◆ INSERT, UPDATE, DELETE문이 TABLE에 대해 행해질 때
묵시적으로 수행되는 PROCEDURE 입니다.
◆ Trigger는 TABLE과는 별도로 DATABASE에 저장됩니다.
◆ Trigger는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의될 수 있습니다.
- BEFORE : INSERT, UPDATE, DELETE문이 실행되기 전에 트리거가 실행됩니다.
- AFTER : INSERT, UPDATE, DELETE문이 실행된 후 트리거가 실행됩니다.
- trigger_event : INSERT, UPDATE, DELETE 중에서 한 개 이상 올 수 있습니다.
- FOR EACH ROW : 이 옵션이 있으면 행 트리거가 됩니다.
-- 행 트리거 : 컬럼의 각각의 행의 데이터 행 변화가 생길때마다 실행되며,
그 데이터 행의 실제값을 제어할수 있습니다.
-- 문장 트리거 : 트리거 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어할수 없습니다.
간단한 행 트리거 예제
SQL>CREATE OR REPLACE TRIGGER triger_test
BEFORE
UPDATE ON dept
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값 : ' || : old.dname);
DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값 : ' || : new.dname);
END;
/
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
-- UPDATE문을 실행시키면..
SQL>UPDATE dept
SET dname = '총무부'
WHERE deptno = 30
-- 트리거가 자동 실행되어 결과가 출력됩니당.
변경 전 컬럼 값 : 인사과
변경 후 컬럼 값 : 총무부
1 행이 갱신되었습니다.
간단한 행 트리거 예제2 (PLSQL BLOCK이 있는 트리거)
SQL>CREATE OR REPLACE trigger sum_trigger
BEFORE
INSERT OR UPDATE ON emp
FOR EACH ROW
DECLARE
-- 변수를 선언할 때는 DECLARE문을 사용해야 합니다
avg_sal NUMBER;
BEGIN
SELECT ROUND(AVG(sal),3)
INTO avg_sal
FROM emp;
DBMS_OUTPUT.PUT_LINE('급여 평균 : ' || avg_sal);
END;
/
트리거가 생성되었습니다.
SQL> SET SERVEROUTPUT ON ; -- (DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용)
-- INSERT문을 실행합니다..
SQL> INSERT INTO EMP(EMPNO, ENAME, JOB, HIREDATE, SAL)
VALUES(1000, 'LION', 'SALES', SYSDATE, 5000);
-- INSERT문을 실행되기 전까지의 급여 평균이 출력됩니다.
급여 평균 : 2073.214
1 개의 행이 만들어졌습니다.