PL/SQL의 구성

• PL/SQL은 SQL에 절차적 언어의 요소를 더한 기능
• 절차적 언어 요소
  • 선택처리(IF)
  • 반복처리(LOOP, WHILE, FOR)
• 향상된 데이터 처리 능력을 부여함

PL/SQL의 종류

• PROCEDURE : 프로시저 처리 후 반환. 값의 제한이 없음
• FUNCTION : 함수 처리 후 반환. 하나의 값 반환
• PACKAGE : 프로시저나 함수의 그룹
• TRIGGER : 조건에 맞는 이벤트가 일어나면 후속 이벤트가 발생

변수

• 매개변수, 자체변수
• IS 로 구분됨
• IS 다음의 자체 변수는 mode 생략
• 매개변수는 역할에 따라
  • IN : 프로시저가 호출될 때 전달되는 값을 처리하기 위한 변수
  • OUT :  프로시저가 처리된 후 반환되는 값을 처리하기 위한 변수
  • INOUT :  두가지 기능을 갖는 변수

변수 타입

• 스칼라(scalar) 타입
  • NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN 등
• 참조(reference) 타입
  • %TYPE
  • %ROWTYPE

선택처리문 (IF ~ ENDIF)

declare - 선언부
v_condition number := 2;
begin
IF v_condition < 1 then
dbms_output.put_line(‘대한’);
ELSIF v_condition = 1 then
dbms_output.put_line(‘민국’);
ELSE
dbms_output.put_line(‘만세’);
END IF;
end; 

반복 처리문 

Loop ~ END LOOP

declare

v_cnt number := 0;

v_sum number := 0;

 begin

  LOOP

   v_cnt := v_cnt + 1;

   v_sum := v_sum + v_cnt;

   dbms_output.put_line(v_cnt||" " ||v_sum);

   EXIT WHEN v_cnt = 10;

  END LOOP

end;

FOR ~ END LOOP

declare

v_cnt number := 0;

v_sum number := 0;

 begin

FOR v_cnt 1..10 LOOP

 v_sum := v_sum + v_cnt;

 dbms_output.put_line(v_cnt||" " ||v_sum);

END LOOP;

end;

WHILE ~ END LOOP

declare

v_cnt number := 0;

v_sum number := 0;

 begin

 WHILE v_cnt <= 10 LOOP

  v_cnt := v_cnt + 1;

  v_sum  := v_sum + v_cnt;

  dbms_output.put_line(v_cnt||" "||v_sum);

 END LOOP;

end;

데이터 정의어

• 데이터베이스의 3층 스키마를 정의
• 데이터베이스 여러 개체 기술

개체

•  Table : 행과 열로 구성된 2차원 테이블로 데이터를 저장하는 개체
• View : 하나 이상의 테이블로부터 유도된 데이터의 부분집합 개체
• Index : 빠른 검색을 위해 사용하는 개체
• Sequence : 순차적인 숫자 값을 생성하는 개체

테이블 (Table)

• 테이블은 데이터를 저장할 수 있는 개체
• 테이블 생성 : Create Table
  • CREATE TABLE table‐name
    ( column‐name1 data‐type default‐value,
    column‐name2 data‐type default‐value,
    ┉┉┉┉┉┉┉┉┉┉┉┉┉┉┉┉┉┉┉
    column‐name data‐type default‐value );
  • CREATE TABLE table‐name
    AS sub‐query;

• 테이블 변경 : Alter Talble
  • 새로운 열 추가
  • ALTER TABLE table‐name
    ADD (column‐name1 data‐type,
    column‐name2 data‐type,
    ┉┉ );
  • 열 구조 변화
  • ALTER TABLE table‐name
    MODIFY (column‐name1 data‐type,
    column‐name2 data‐type,
    ┉┉ );
  • 열 삭제
  • ALTER TABLE table‐name
    DROP ( column‐name1 , column‐name2 ┉┉ );
• 테이블 삭제 : Drop Table
  • Drop Table table-name;
• 테이블 이름 변경
  • Rename old_table_name To new_table_name;
• 테이블 내의 데이터 삭제
  • Truncate Table table-name;

제약 조건

• 도메인 제약 조건 : 각 열의 값은 반드시 해당 도메인에 속하는 원자
• 키 제약 조건 : 테이블에는 테이블의 각 레코드를 유일하게 실별할 수 있는 수단 즉, 최소한 하나의 기본키를 가지고 있어야 한다.
• 무결성 제약 조건 (integrity constraint)
  • 엔티티 무결성(entity integrity) : 기본키 속성들의 값은 어떠한 경우에도 널 값을 가질 수 없다.
  • 참조 무결성(referential integrity) : 한 테이블에 있는 레코드가 다른 테이블에 있는 레코드를 참조하려면 반드시 참도되는 레코드가 그 테이블 내에 존재해야 한다.(외래키)

제약 조건 선언

• Not Null - constraint 제약조건 이름 NOT NULL
• Unique Key - constraint 제약조건 이름 UNIQUE
• Primary Key - constraint 제약조건 이름 PRIMARY KEY(속성 명);
• Foreign Key - constraint 제약조건 이름 FOREIGN KEY (속성 명) REFERENCES 테이블명(속성 명);
• CHECK - constraint 제약조건 이름 CHECK (속성 명 - 조건);

제약 조건 확인

select * from user_constraints where 테이블 명 = '테이블 명';

제약 조건의 삭제

DROP CONSTRAINT 제약조건 명

• ALTER Table 테이블 명 drop constraint 제약조건 명

제약 조건의 활성화, 비활성화

Alter Table 테이블 명

Disalbe / Enable CONSTAINT 제약조건 명;

뷰 (VIEW)

• 외부단계
• 가상 테이블
• 질의 변형
• 장점 
  • 데이터베이스를 재구성하여 논리적 데이터 독립성 제공
  • 원하는 데이터 만을 조작함으로 데이터의 보완기능 강화

VIEW의 생성과 삭제

생성

Create View 뷰 이름

AS 부질의

삭제

Drop View 부 이름

VIEW의 종류

단순 뷰

단순 뷰는 단일 베이스 테이블로 부터 유도된 뷰이다.

Create or replace view v_student1

as select * from student where sty_dept='컴퓨터정보';

조인 뷰

조인뷰는 2개 이상의 베이스 테이블로부터 유도된 뷰이다.

Create or replace view v_enrol1

as select sub_name, a.sub_no, stu_no, enr_grade

from enrol a, subject b

where a.sub_no = b.sub_no;

인라인 뷰

인라인 뷰는 From절에 SELECT 문으로 정의된 뷰이다.

select stu_no, stu_name, a.stu_dept, stu_height

from student a, (select stu_dept, avg(stu_height) as avg_height

from student

group by stu_dept) b

where a.stu_dept = b.stu_dept

and a.stu_hegiht > b.avg_height;

TOP-N 뷰

최댓값 또는 최솟값을 가진 열에 몇 개의 레코드만 추출할 때 사용되는 기능

Select stu_no, stu_name, stu_height

from (select stu_no, stu_name, stu_height

from student

where stu_height is not null

order by stu_height desc)

where rownum <= 5;

인덱스 (INDEX)
빠른 검색을 위하여 정의되며, 한 개 이상의 열로 구성됨.
학생 테이블의 학번은 기본 키이므로 자동으로 인덱스 정의.
이름으로 검색하는 질의가 많을 경우 이름으로 인덱스를 정의.
학과로 검색하는 경우가 극히 적다면 학과를 인덱스로 정의할 경우 활용도가 떨어지며, 기억공간은 낭비된다.
데이터 사전의 USER_INDEXS, USER_ID_COLUMS

학생의 이름으로 인덱스 생성 - Create INDEX i_stu_name on student(stu_name);

학생의 학번과 이름을 합쳐서 인덱스를 생성 - Create INDEX i_stu_no on student(stu_no, stu_name);

유일한 값으로 인덱스 생성 - Create UNIQUE INDEX i_stu_name on student(stu_name);

함수나 수식을 이용하여 인덱스 생성 - Create INDEX i_stu_weight on student(stu_weight-5);


인덱스 생성 유무 및 상태 분석

Select * from user_indexes

where table_name='STUDENT';


인덱스 삭제

drop index i_stu_name;



시퀀스 (SEQUENCE)
어떤 연속적인 숫자 값을 자동적으로 증가하여 사용



시퀀스 생성

Create SEQUENCE 시퀀스 명

Increment by n

Start n

Maxvalue n | NoMaxvalue

Minvalue n | NoMinvalue

Cycle | NoCycle

Chache | NoCache;



시퀀스 삭제

Drop SEQUENCE 시퀀스 명