패키지

관련있는 프로시저나 함수를 묶어 관리, 운영하는 기능

명세부

• Create [Or Replace] Package 패키지 명
  IS
   Procedure 프로시저 명
   Functino 함수 명
  ......
  END;

몸체부

• Create [Or Replace] Package Body 패키지 명
  IS
   Procedure 프로시저 명
  -프로시저 생성
  
   Functino 함수 명
  - 함수 생성
  ......
  END;

실행

• EXECUTE [패키지명].[프로시저/함수 명];

ex)

<명세부>

CREATE OR REPLACE PACKAGE PACK1 is
procedure test2
(v_stu_no in student.stu_no%type,
v_stu_grade in student.stu_grade%type);
function test6
(v_enr_grade in number)
return char;
end;

<몸체부>

CREATE OR REPLACE PACKAGE BODY PACK1 IS
    PROCEDURE test2 (
        v_stu_no IN student.stu_no%TYPE,
        v_stu_grade IN student.stu_grade%TYPE
    ) IS
    BEGIN
        UPDATE student
        SET stu_grade = v_stu_grade
        WHERE stu_no = v_stu_no;
    END test2;

    FUNCTION test6 (
        v_enr_grade IN NUMBER
    ) RETURN char IS
        enr_score char;
    BEGIN
        IF v_enr_grade >= 90 THEN
            enr_score := 'A';
        ELSIF v_enr_grade >= 80 THEN
            enr_score := 'B';
        ELSIF v_enr_grade >= 70 THEN
            enr_score := 'C';
        ELSIF v_enr_grade >= 60 THEN
            enr_score := 'D';
        ELSE
            enr_score := 'F';
        END IF;
        RETURN enr_score;
    END test6;
END pack1;

select pack1.test6(85) from dual;

ONE-TIME ONLY 프로시저와 오버로딩

• ONE-TIME ONLY 프로시저
  • 전역변수의 초기화나 기본적으로 처리하여야 할 로직
  • 패키지가 실행될 때 무조건 한번만 실행됨
  • 패키지 몸체부 가장 마지막 부분에 BEGIN절과 함께 정의됨
• 오버로딩
  • 같은 이름의 여러개 프로시저 사용
  • 매개변수의 수가 다르거나, 순서를 달리하여 사용

<명세부>

• CREATE OR REPLACE package C202144098.PACK2 is
  g_stu_dept varchar2(20);
  procedure test12 
  (v_stu_no in student.stu_no%type);
  procedure test12     -오버로딩-
  (v_stu_name student.stu_name%type);
  End;

<몸체부>

• CREATE OR REPLACE PACKAGE BODY C202144098.PACK2 IS
      PROCEDURE test12 (v_stu_no IN student.stu_no%TYPE) IS
          v_stu_name student.stu_name%TYPE;
      BEGIN
          SELECT stu_name
          INTO v_stu_name
          FROM student
          WHERE stu_no = v_stu_no AND stu_dept = g_stu_dept;
          DBMS_OUTPUT.PUT_LINE(v_stu_name);
      EXCEPTION
          WHEN no_data_found THEN
              DBMS_OUTPUT.PUT_LINE('컴퓨터정보과에 학생이 없습니다.');
      END test12;
  
      PROCEDURE test12 (v_stu_name IN student.stu_name%TYPE) IS
          v_stu_no student.stu_no%TYPE;
      BEGIN
          SELECT stu_no
          INTO v_stu_no
          FROM student
          WHERE stu_name = v_stu_name AND stu_dept = g_stu_dept;
          DBMS_OUTPUT.PUT_LINE(v_stu_no);
      EXCEPTION
          WHEN no_data_found THEN
              DBMS_OUTPUT.PUT_LINE('컴퓨터정보과에 학생이 없습니다.');
      END test12;
  
  BEGIN
      g_stu_dept := '컴퓨터정보';
  END;             -ONE-TIME ONLY 프로시저

Execute pack2.test12(20191001);

Execute pack2.test12('김종헌');

트리거

어떤 조건에 맞는 이벤트가 일어나면 후속 이벤트를 발생시킴

트리거의 5 요소

• 트리거 유형 : DML 단위의 트리거, 변경된 레코드 단위의 트리거
• 트리거 동작 시점 : 트리거 동작 시점은 Before, After
• 트리거 이벤트 : 트리거를 발생시킬 것 인지를 셜정
• 트리거 조건 : 레코드 단위의 트리거에서만 설정할 수 있는 UPDATE 작업 시 특정 조건을 명시하여 조건에 맞는 레코드의 변셩에서만 트리거 이벤트를 발생
• 트리거 몸체 : BEGIN ~ END절로 동작 정의

트리거의 형식

CREATE [OR REPLACE] TRIGGER 트리거 명

BEFORE | AFTER 이벤트 ON 테이블 명

[FOR EACH ROW]

[WHEN CONDITION]

BEGIN

...................

END;

트리거의 활성화 / 비활성화 / 삭제

ALTER TRIGGER 트리거 명 DISABLE / ENABLE

ALTER TABLE 테이블 명 DISABLE | ENABLE ALL TRIGGER

DROP TRIGGER 트리거 

프로시저

처리 후 반환 값이 여러 개

프로시저 생성

Create [Or Replace] Procedure 프로시저 명

(매개변수 명 [모드] 자료형,

.........................................)

IS

  자체변수 명 - 자료형,

...................................

BEGIN

- 실행부

EXCEPTION

- 예외처리

END;

프로시저 삭제

Drop Procedure 프로시저 명

ex) 학년을 압력으로 학생의 이름을 검색

CREATE OR REPLACE PROCEDEURE p_1(

v_stu_no IN student.stu_no%TYPE,

v_stu_name OUT student.stu_name%TYPE)

IS

BEGIN

  Select stu_name

    into v_stu_name

    from student

    where stu_no=v_stu_no;

END p_1;

프로시저  실행

declare

variable d_stu_name varchar2(12);

begin

execute p_1(20213075, :d_stu_name); :d_stu_name = bind 변수

end;

print d_stu_name;

함수

처리 후 반환 값이 하나.

함수 생성

Create [Or Replace] Finction 함수 명

(매개변수 명 [모드] 자료형,

...........................................)

RETURN 자료형

is

  자체 변수 - 자료형

BEGIN

  - 실행부

  return 변수

EXCEPTION

  - 예외처리

END;

함수 삭제

Drop Function 함수 명

ex) 성적 환산 함수

CREATE OR REPLACE FUNCTION f_1(

v_enr_grade in number)

RETURN CHAR

IS

  enr_score char;

BEGIN

  IF v_enr_grade >= 90 THEN enr_score := 'A';

  ELSIF v_enr_grade >= 80 THEN enr_score := 'B';

  ELSIF v_enr_grade >= 70 THEN enr_score := 'C';

  ELSIF v_enr_grade >= 60 THEN enr_score := 'D';

  ELSE enr_score := 'F';

  END OF;

  RETURN (enr_socre);

END f_1;

execute :d_score := f_1(95);

커서, 페치

select 문의 실행 결과가 복수행을 가질 경우 사용함.

커서의 생성

CURSOR 커서 명

IS 

SELECT 질의

[FOR UPDATE];

페치

커서에서 행을 하나씩 가져올때 사용.

ex) 101번 과목을 수강하는 학생의 학번과 성적을 확인

CREATE OR REPLACE PROCEDURE p_2

IS 

  v_stu_no enrol.stu_no%TYPE;

  v_stu_no enrol.sub_no%TYPE;

  v_enr_grade enrol.enr_grade%TYPE;

  CURSOR t_cursor IS

    SELECT stu_no, sub_no, enr_grade

    FROM enrol

    WHERE sub_no = 101;

BEGIN

  OPEN t_cursor;

  LOOP

    FETCH t_cursor INTO v_stu_no, v_sub_no, v_enr_grade;

    EXIT WHEN t_cursor%NOTFOUND;

    dbms_output.put_line(v_stu_no||'='||v_sub_no||'='||v_enr_grade);

  END LOOP;

  CLOSE t_cursor;

END p_2;

프로시저 실행

execute p_2;

커서의 속성

• %ROWCOUNT 속성
  • dbms_output.put_line('현재 '|| %ROWCOUNT ||'번째 행을 처리합니다.');
• %FOUND 속성
  • if %FOUND then
    dbms_output.put_line('데이터를 읽었습니다.’);
    end if;
• %NOTFOUND 속성
  • if %NOTFOUND then
    dbms_output.put_line(＇더 이상 읽을 데이터가 없습니다.’);
    end if;
• %ISOPEN 속성
  • if %ISOPEN then
    fetch t_cursor into v_stu_no, v_sub_no, v_cour_grade;
    else
    open t_cursor;
    end if

예외처리

에러발생, 예외적인 상황 발생

ex) 학번에 의한 학생 이름 검색

CREATE OR REPLACE PROCEDURE p_3

(v_stu_no IN student.stu_no%TYPE)

IS

  v_stu_name student,stu_name%TYPE;

BEGIN

  Select stu_name

    Into v_stu_name

    From student

    Where stu_no = v_stu_no;

dbms_output.put_line(v_stu_name);

EXCEPTION

  When NO_DATA_FOUND Then

    dbms_output.put_line('해당 데이터가 없습니다.');

END p_3;

프로시저 실행

execute p_3(20213088);

execute p_3(20213089);

변수를 통한 예외처리

........

IS

  예외조건명 EXCEPTION;

.....

  IF 조건 then

    RAISE 예외조건명;

.....

  EXCEPTION

    When 예외조건명 Then

.....

END;

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 시퀀스 명

cross 조인

• 크로스 조인은 관게 대수가 가지는 8가지 연산 중 카티션 프로덕트를 구현함
• 2개 이상의 테이블을 조건 없이 실행하는 조인 연산

SELECT *
FROM STUDENT CROSS JOIN ENROL;

Equi 조인 

• 2개 이상의 테이블에 관련있는 공통 열의 값을 이용하여 논리적으로 결합하는 연산이 수행되는 조인
  • NATURAL JOIN
    • select stu_no, stu_name, stu_dept, stu_grade
      from student natural join enrol;
    • 두 테이블에 같은 열의 이름이 2쌍 이상 존재하면 사용 못함
  • Join ~ Using
    • select stu_no, stu_name, stu_dept, stu_grade
      from student join enrol using(stu_no)
  • Join ~ on
    • select stu_no, stu_name, stu_dept, stu_grade
      from student join enrol on student.stu_no = enrol.stu_no

Non-Equi 조인

• where 절에서 사용하는 '=' 이 아닌 연산자를 사용
•  select empno, ename, sal, grade
   from salgrade, emp
   where sal between losal and hisal

SELF 조인

• 같은 테이블 간의 조인, 테이블의 별칭을 사용함
• select a.empno as 사원번호, a.ename as 사원이름,b.empno as 상급자사원번호, b.ename as 상급자이름
  from emp a, emp b
  where a.mgr = b.empno; 
• join ~ on 만 사용 가능
• select a.empno as 사원번호, a.ename as 사원이름, b.empno as 상급자사원번호, b.ename as 상급자이름
  from emp a join emp b on a.mgr = b.empno;