Programo90 코딩 기록저장소

 PL/SQL 이란 비절차적 언어인 SQL를 절차적 언어와 같이 동작하도록 하는 명령어들을 말한다.

 선언부와 시작/종료 부를 기본으로 포함하고 변수나 SQL을 포함하도록 설계한 후 필요시 호출하거나 특정 조건시 자동으로 실행된다.

PL/SQL 변수선언

PL/SQL에서 변수는 아래와 같이 단일변수, 로우변수(record type), 테이블변수(table type)이 있다.

   기본타입( number,date,varchar2 등 )의 단순 변수
   여러 값을 갖는 Row형식의 Record Type 변수
   여러 Row를 갖는 table 형식의 Table Type 변수

(Recorde type, Table type은 절차지향 언어에서 변수만 있는 클래스와 유사하다. )

Record type 변수 선언 
  type 타입변수명 is record( 컬럼명1 컬럼1타입, 컬럼명2 컬럼2타입, ... )

Table type 변수 선언
  type 타입변수명 is table of 다른테이블(혹은 위에서 정의한 recordtype) %rowtype   //단일컬럼도 가능은하다.

   * 위에서 선언한 Record type(Table type)은 타입만 만든것이고, 해당 타입으로 실제 사용할 변수를 만들어줘야 한다.
     ex) 타입명1  type_vari   -  이름이 타입명1 이라는 타입의 변수 type_vari 를 생성한다.

Scalar 변수
  sql에서 제공하는 기본 변수 타입을 정의하는 변수.

Reference 변수
   이미 변수타입, 크기가 정의되어 있는 컬럼에서 동일한 타입을 가져오는 변수.

      단일 컬럼의 타입을 가져오는 경우

          <뱃겨오려는 컬럼명> % type

      다중 컬럼의 타입들을 가져오려는 경우

            <뱃겨오려는 테이블> % type

Procedure

 프로시젼은 특정 동작을 반복적으로 사용하기 위해 정의하고 필요시마다 호출해 사용할 수 있는 구문이다.

 정의 기본형

    DECLARE [ OR REPLACE ] <프로시젼명> (<매개변수명> <in/out/inout> <매개변수타입>)

    is

       [변수 선언]

       begin

           <실행문>

       end;

 호출

   exec <프로시젼명>(in매개변수, :out매개변수)   -   out으로 받을 값엔 변수명 앞에 : 를 기입한다.

    Exception 처리
        exception when exception이름 then 실행문;

   if 조건문
        if( 조건 ) 

                then 실행문1
        else if(조건)
                then 실행문2
        end if;

   case 조건문

   loop 반복문 
        실행문 내용을 반복해서 실행하다가, exit; 가 호출되면 loop문을 빠져나간다.
        loop
              실행문
                if( ) then exit;
                end if;
        end loop;

   for 반복문  

        i가 n1부터 n2 번까지 반복해서 loop안에 값을 실행한다.

        for i in n1..n2  loop
            실행문
        end loop;

Cursor

   테이블에서 행을 가리키고 있는 객체, LS/SQL문에서 Cursor를 정의해서 사용한다.

  커서 정의
      선언부와 is begin 사이 정의구간에 아래와 같이 생성한다.
           cursor <커서명> is <서브쿼리> 
      서브쿼리가 반환하는 테이블을 가르키는 커서가 된다.

  커서 호출
     open <커서명>
          loop
                  fetch 커서명                          -     행을 읽고 다음행으로 커서를 옴긴다.
                  into 행을 받을 객체                 -     커서가 읽어올 테이블의 하나의 행
              exit when <커서명>%notfound      -     커서의 다음행이 없을때 loop를 나갈 exit 실행 
          end loop;
     close <커서명>;

Trigger

DML이 실행될때 자동으로 추가적인 작업을 하는 객체로 선언해놓은 동작 조건에 따라 저장해놓은 실행문을 자동 실행한다. 별도의 호출이 필요없이 바라보고 있는 테이블에 이벤트가 발생하면 자동으로 실행된다. 주의해야할 점은 트리거 실행중 오류가 발생하면 바라보고 있는 테이블에 트리거 실행전 작업은 남아있기 때문에 데이터 무결성이 훼손될 수 있기에 주의해야 한다.

    선언 기본형
       create trigger <트리거명 >
          after(before) <DML 중 한가지>
          on 적용할테이블명
              [for each row] - 한번의 쿼리로 여러 행이 개행될 경우 각 행마다 실행하는 옵션
       begin
             트리거로 작업할 실행문
       end;

   * 트리거 장점

      foreign key 로 묶여있는 데이터를 변경하려면 제약으로 인해 실행이 되지 않는다. 하지만 트리거로 제약으로 묶여있는 부모-자식 값을 동시에 처리하도록 정의해놓으면 DBMS가 이를 알아서 인식해 값을 변경할수 있도록 처리한다.

시퀀스(Sequence)

시퀀스란 시스템에서 자동으로 생성해내는 숫자의 배열을 말한다.

시퀀스 생성

   create sqeuence 시퀀스이름        시퀀스를 생성한다.
   start with number1                    시퀀스의 시작값 설정, 미설정시 1부터 시작 
   increment by number2               시퀀스의 다음값으로 패턴 설정(증감값), 미설정시 1씩 증가 
   max(min)value number3              시퀀스의 최대/최소값 설정 미설정시 거의 무한 
   cycle(nocycle)                           시퀀스의 최대/최소값 초과시 반복할지 설정. 
   cashe number4  (no cashe)     시퀀스의 값은 캐시에 설정해놓은 갯수만큼 한번에 미리 만들어놓고 사용하는데, 시스템의 비정상적 종료 등으로 캐시에 미리 만들어놓은 시퀀스값이 사라질수가 있다. 이를 대비해 캐시에 미리 만들어놓은 갯수를 설정할수 있다.

시퀀스 사용
   시퀀스이름.nextval 을 통해서 지정해놓은 패턴을 따라 다음 값을 반환한다.
   시퀀스이름.currval 을 통해서 현재 값을 반환한다.

인덱스(Index) 

DB에서 검색에 사용할 Index를 테이블에 특정 컬럼을 기준으로 설정 할 수 있다.
index를 추가하면 기준이된 컬럼 값을 기준으로 새로운 tree가 만들어지기 때문에 특정 값 검색속도가 비약적으로 상승할 수 있다. 

 하지만 인덱스생성시 많은 작업량이 요구되고 별도의 저장공간이 추가로 필요하며 지나치게 많은 인덱스를 오히려 성능저하를 가져오기 때문에 꼭 필요한 인덱스만 생성하는게 유리하다. 인덱스 생성에 유리한 조건 Unique한 값, 적절한 분포도 등을 고려해야 한다.

인덱스 생성

  create index 인덱스명 on 테이블명(기준컬럼명);

View는 일종의 가상의 테이블이다.
내부 구조는 실제 테이블 형태로 저장되어 있는 것이 아니라 쿼리문으로 저장되어 있다. 하지만 뷰에서 데이터를 변경하는 경우, 실제 테이블에도 반영된다. 결국 가상 테이블이지만 실제 테이블과 연결되어 있는것. 

뷰를 사용해서 얻을 수 있는 장점으로는

1. 자주 사용하는 뷰를 생성해놓고 사용하면 매번 복잡한 쿼리문을 사용하지 않고도 쉽게 가상 테이블을 사용할수 있다.
2. 뷰를 미리 생성해놓구, 사용자에게 테이블에 직접적인 접근이 아닌 뷰에 대한 필요한 접근권한만을 부여함으로써 보안성과 안정성을 확보할수 있다.


뷰 생성 명령
create view 뷰이름
as
   서브쿼리

이와같이 정의함으로써 서브쿼리의 결과를 뷰로 만들수 있다.

뷰 생성에 추가적인 조건으로
   with read only         해당 뷰로 read만 가능하다.
   with check option     뷰를 통해 볼 수 있는 테이블의 일부만, 뷰를 통해 변경이 가능하도록 제한한다.
                               뷰의 일부를 선택하는 '조건' 이 된 컬럼값을 변경할수 없도록 하는것.
   ex) view_chk30 이라는 뷰가 deptno=30 인 로우로만 이뤄진 뷰일경우,  이 뷰에 있는 deptno컬럼의 값을 다른 값으로 임의 변경이 불가능하다.

Transaction
SQL로 작업을 처리하는 단위를 말한다.
SQL로 작업을 할때 일정 단위별로 작업내용을 DB에 적용하거나, 적용하지 않고 되돌릴 수 있는 기능을 제공한다.
하나의 트랜잭션은 그 안에 작업한 내용을 전부 수행하거나, 하나라도 처리되지 않을 경우 전체를 되돌리는
All or Noting 의 방식으로 처리된다. 이를 통해 데이터의 안정성과 일관성을 확보할수 있다.

Commit     트랜잭션의 작업 내용을 DB에 적용하는 명령어. commit의 단위로 하나의 트랜잭션이 나뉜다.
                 DML이 아닌 DDL(createm alter drop truncate) 가 실행되면 자동으로 commit이 실행 된다.

Rollback    트랜잭션 작업내용을 모두 되돌려 이전 commit 지점으로 되돌린다.

Savepoint  하나의 트랜잭션에서 rollback으로 돌아갈 지점을 이전 commit이 아닌 특정 지점을 지정한다.
    savepoint a1; 식으로 지점을 선언할수 있고, rollback a1; 식으로 지정한 지점으로 되돌릴수 있다.

*하나의 트랜잭션에서 변경, 사용중인 data는 lock이 걸려 다른 쿼리문으로 접근할 수 없고, commit이나 rollback으로 작업을 완료해야 lock이 풀린다.

Lock
DML로 특정 데이터를 변경했을때, 트랜잭션을 commit, rollback으로 완료하지 않았을 경우 해당 데이터는 lock이 걸려 다른 쿼리문으론 접근할수 없게해 데이터의 안정성과 일관성을 유지한다.

lock이 걸려 있는 데이터에 접근 할 경우, 먼저 lock을 얻어 작업중인 내용이 완료 될때까지 접근을 요청한 쿼리문은 무한대기상태에서 기다리게 된다.
 * 서로 다른 쿼리문이 교차적으로 데이터에 락을 걸어 deadlock이 발생할 경우, 먼저 lock을 얻은 사용자측에  DBMS가 자동으로 deadlock detected message를 출력한다.

제약조건 Constraint 
 DB에 일관성을 유지하기 위해 테이블에 컬럼에 제약조건을 정의할 수 있다.
제약조건은 한 테이블에서 하나의 컬럼에만 적용되거나,

한 테이블에서 여러 컬럼을 묶여서 적용되거나(복합키),
다른 테이블과 연관시켜 컬럼에 제약조건을 걸 수 있다.(외래키)

제약조건 타입
Primary key 기본키 설정(중복불가, 널 입력 불가)

   primary key (지정할 컬럼명)

Not null 널 입력 불가

   not null (지정할 컬럼명)

Unique 중복값 입력 불가

  unique (지정할 컬럼명)

Check ( )안에 넣은 값으로 컬럼에 입력 가능한 값을 제한한다

   check( 조건 )

Foreign key 다른 테이블 특정 컬럼 값 내에서만 값을 설정할수 있도록 관계지은 키.

    foreign key (지정할 칼럼명) references 참고할 테이블명( 참고할 컬럼명 )
   (지정할 칼럼명) 은 테이블단위 제약조건 정의의 경우만 사용한다.

제약조건 정의 단위
  컬럼단위 제약조건 정의
  create절이에서 컬럼 정의 바로 뒤에 이어서 정의하는 경우. 컬럼당 제약조건을 주기 때문에 복합키설정은 불가능.
       ex)   create table emp(   sal number(7,2) constraint emp_sal_ck check(sal>1000 and sal<5000)    );

  테이블단위 제약조건 정의
   create 절에서 컬럼 정의를 다 하고나서, 아래 부분에 따로 제약 조건을 정의하는 경우.
     ex)  create table emp( empno number(5),  sal number, ename varchar2(20)
         , constraint emp_deptno_fk foreign key(deptno) references dept(deptno) )
     복합키는 테이블단위로만 정의가 가능하다. 아래와 같이 복합키로 사용할 컬럼을 나열해준다.
     ex) constraint emp_empno_ename_pk primary key(empno, ename);

기존 컬럼에 제약조건 추가, 삭제
   alter table 테이블명 add constraint 제약자명 키종류( 대상이 될 컬럼명 )

    제약조건 추가는 추가하려는 제약조건과 이미 입력되어 있는 값간에  충돌이 없어야 한다.

      ex) 중복이 있는 컬럼에 unique 제약조건 불가.
   * not null의 경우는 add가 아닌 modify로 컬럼을 재정의 하면서 constraint를 추가해줘야 한다.

   alter table 테이블명 drop constraint 제약자명

    * 제약조건 제거는 외래키로 묶여있는 경우 부모 테이블과의 관계를 유의해야 한다.

외래키 관계
  외래키로 설정하기 위해선 부모테이블(참조값을 주는 테이블)의 참조 컬럼은 PK나 UK 설정이 되어있어야 한다.
 부모테이블의 값이나 제약조건을 제거, 변경하기 위해선 참조중인 자식테이블과의 충돌이 없어야 한다.

 Cascade  외래키로 묶여있는 제약조건을 해제할때 사용한다.

  ex)  alter table 테이블명 drop constraint 제약자명
  위와 같이 제약조건을 삭제하려 할때, 해당 제약자가 pk나 uk로 다른 컬럼이 외래키의 참조값으로 사용중인 경우, 삭제가 되지 않는데, 이때 뒤에 cascade를 붙여주면 강제로 외래키 관계를 끊으면서 부모테이블의 제약조건을 삭제한다.

외래키 설정시 미리 references( 컬럼명 ) on delete cascade;   이와같이 on delete cascade 설정을 해놓은 경우,
delete 로 관계된 값을 삭제하더라도 삭제가 가능하고, 이경우 부모테이블에서 삭제된 외래키 값은 자녀테이블에서도
삭제된다. ( *외래키 관계가 삭제되는게 아니라 참조로 연결되어 있는 값들이 삭제되는것 )

이 조건을 잘못 사용할 경우 원치 않게 다른 테이블의 데이터까지 삭제하기 쉽기 때문에 유의해야 한다.

테이블의 구성을 정의하고 변경하는 명령어
DDL 명령어들은 실행시 바로 commit 처리된다.
따라서 트랜잭션 관리에 유의해야한다.

Create
테이블이나 뷰를 생성하는 명령어로 테이블명, 컬럼명, 컬럼의속성을 정의한다.
컬럼을 정의하고 바로 옆에 constraint(제약조건) 정의가 가능하다.
create table 테이블명
( 컬럼명, 컬럼속성(크기)
 , 컬럼명2, 컬럼속성(크기)
 , 컬럼명3, 컬럼속성(크기) )

carete as 다른 테이블의 컬럼과 값을 가져와 테이블을 생성하는 일종의 복사.
select 가져올 컬럼
from 가져올 테이블
where 가져올 값 제한. 1=0 으로 정의할 경우 값은 전혀 가져오지 않으므로 스키마(테이블형태)만 가져온다.

Alter
테이블의 구조를 변경하는 명령어.
alter table 테이블명
변경명령어

변경명렁어
   add 컬럼명 컬럼속성(크기)
   modify 컬럼명 컬럼속성(크기)
   rename column 컬럼명 to 변경할컬럼명
   drop column 컬럼명
   *그냥 Drop은 테이블삭제, alter - drop은 컬럼삭제, delete는 행삭제.

Drop
테이블을 삭제함
drop table 테이블명
purge 를 뒤에 붙이면 쓰래기통에 가지 않고 바로삭제.
   flashback recyclebin 으로 쓰래기통에 잇는 모든 테이블을 복구하거나,
   flashback table 테이블명 to before drop 으로 특정 테이블만 복구가 가능하다.

Truncate 쓰래기통(recyclebin)에 가지않고 바로 완전삭제하는 명령어 drop보다 처리속도가 빠르지만 복원이 불가하다.

Insert
테이블에 행(row)를 추가한다. 특정 컬럼만 추가하거나 전체 컬럼값으 전부 입력할수 있다.
insert into 테이블명(컬럼명1,컬럼명2 ...) values(컬럼1 값, 컬럼2값 ...)
* 테이블명 뒤에 컬럼명을 명시해주지 않을경우, values 안에 모든 컬럼값(null일경우 null로 명시)을 넣어줘야한다.
   insert all
            서브쿼리를 이용해서 다른 테이블의 값을 테이블에 넣어주는 경우.
이경우 행을 입력할 테이블을 여러 테이블로 설정할수 있고, 서브쿼리 값을 when - then문으로 받을수도 있다.
insert all
   when 서브쿼리 조건1 then into 받는 테이블명1 values(각 컬럼값)
   when 서브쿼리 조건2 then into 받는 테이블명2 values(각 컬럼값)
select 서브쿼리문
 이 구성을 통해서 하나의 테이블을 서브쿼리문으로 읽어오고, 여러 테이블에 나눠서 값을 넣을 수 있다.

Update
테이블의 행의 값을 변경하는 것. (행에 값 하나하나를 변경할수 있다.)
update 테이블명
set 컬럼명1 = 변경할값, 컬럼명2 = 변경할값
where 조건

Delete
행을 삭제한다. 조건을 주면 해당 조건에 해당하는 행을 삭제한다. (행에서 하나의 값을 삭제하는게 아니다.)

delete from 테이블명
where 조건

Group Function
Group by
   특정 컬럼을 기준으로, 각 값끼리 그룹을 짓는다.
    group by를 이용해 그룹을 만들어 주면, select 절에선 그룹별 계산값을 보여주는 group function의 값이나,
    그룹의 기준이 된 컬럼만 보여줄 수 있다. (row의 각 값을 보여줄순 없다.)

  sum 각 그룹의 합계를 리턴
  avg 각 그룹 값의 평균을 리턴
  count 각 그룹의 row 갯수 세어서 리턴
  max 각 그룹내 최대 값 리턴
  min  각 그룹내 최소 값 리턴
*위의 function들은 null값을 아예 포함시키 않고 넘어간다.

having
   그룹의 조건을 적어준다.
   조건으로는 그룹을 나누는 컬럼에 값이나, 그룹 function으로 계산한 값 두가지 모두 사용할수 있다.

* 각 그룹은 그룹별 row로 리턴된다.
* row가 아니라 칼럼으로 그룹을 나눠주고 싶을경우에는 decode나 case 문을 이용해야한다.

Join
복수의 테이블을 연결해서 뷰를 만들어준다.
공통분모가 되는 컬럼은 where절에 정의해준다.
만약 여러 테이블의 같은 이름의 컬럼이 있다면, select 절에서 출력할 컬럼명 앞에 테이블명을 같이 적어줘야한다.

Equi join  복수의 테이블에서 공통되는 컬럼을 기준으로 뷰를 만든다.
Non equi join  한 테이블의 컬럼이 다른 테이블의 컬럼의 범위 안에 들어가도록 공통된 부분을 지정한다.
Self join 하나의 테이블의 두가지 컬럼을 이어서 뷰를 생성한다. (이때 보통 두 컬럼을 foreign키로 이어준다.)
Outer join  복수의 테이블에서 컬럼이 서로 대칭적이지 않을때, 한쪽에 null값이 있는 경우이다.
  이때 널값이 없는 쪽(값이 더 많은측)에 (+)를 해주면 널을 포함한 전체 값을 보여준다.

Ansi join
표준 sql로 만든 join 공통분모가 되는 컬럼은 from절 뒤 on에 정의해준다.
inner join = equi join
outer join = outer join 이지만 (+)로 null이 없는 쪽을 표시하는것이 아니라,
   right outer join, left outer join 형식으로 방향을 표시해준다.
   양쪽을 모두 포함할 경우 full outer join도 있음.

Sub query
하나의 쿼리문 안에서 다른 쿼리문을 사용하는 경우. 이때 메인쿼리(아우터쿼리), 서브쿼리(이너쿼리)로 구분한다.
select절, from절, where절 어디서든 사용이 가능한데, 이때 서브쿼리의 반환값이 단일값(단일 row)이냐 복수값
(복수row)에 따라서 처리를 달리 해야 한다.

단일행(single row) 서브쿼리
서브쿼리의 반환값이 하나의 row인 경우이다.
대부분의 단순 연산자들로 처리가 가능하다.

복수행(Multiple row) 서브쿼리
서브쿼리의 반환값이 복수의 row인 경우.
이경우 서브쿼리의 반환값을 처리할 다중행 연산자들로 처리해줘야 한다.
in
all
any, some
exist

상관 서브쿼리
서브쿼리에서 메인쿼리의 값을 참조하는 경우를 말한다.
원래 서브쿼리는 먼저 처리되고 메인쿼리가 수행되는 반면,
상관 서브쿼리는 메인쿼리를 실행하면서 서브쿼리문는 메인쿼리의 값을 가지고 가서(참조해서)
반복적으로 실행된다.
메인쿼리에서 가져가는 값의 갯수만큼 서브쿼리가 반복 수행해서 결과를 반환한다.
where절에 메인쿼리와 서브쿼리의 상관 관계를 정의해줘야 한다.