[Lecture] DataBase - Day 10~11

🖥 DataBase - Day 10

👉🏻 [Chapter 13] 뷰(View)

[01] 뷰의 개념

• 뷰
  • 가상 테이블(virtual table)
  • select 문을 저장한 객체
• 뷰의 사용 목적
  • 편의성 : select 문의 복잡도 완화
  • 보안성 : 테이블의 일부 데이터만 노출

[02] 뷰 생성

create [or replace][force | noforce] view 뷰이름 as sub-query
[with check option [constraint 제약조건]]
[with read only]

• 뷰의 생성
  • or replace : 같은 이름의 뷰가 있을 경우 삭제 후 다시 생성
  • force : 기본 테이블의 존재 여부에 상관없이 뷰 생성
  • noforce : 기본 테이블이 존재할 경우에만 뷰 생성, 기본 값
  • with check option : 주어진 제약조건에 맞는 데이터만 입력 및 수정 허용
  • with read only : select 만 가능한 읽기 전용 뷰 생성

grant create view to [account_name];

• 계정에 뷰 생성 권한 부여

[03] 뷰 삭제

drop view 뷰;

• 뷰 삭제 쿼리

👉🏻 [Chapter 16] PL/SQL 기초

[01] PL/SQL 구조

• 블록(block)
  • PL/SQL 프로그램의 기본 단위
• PL/SQL 사용 시 주의사항
  • declare, begin, exception 키워드에는 ; 사용하지 않음
  • PL/SQL 블록의 각 부분에서 실행해야 하는 문장 끝에는 ; 사용
  • PL/SQL 문 내부에서 한 줄 주석과 여러 줄 주석 사용 가능
  • PL/SQL 문 작성을 마치고 실행하기 위해 마지막에 / 사용
• PL/SQL 주석
  • - : 한 줄 주석
  • /* ... */ : 여러 줄 주석

declare
    [실행에 필요한 여러 요소 선언];
begin
    [작업을 위해 실제 실행하는 명령어];
exception
    [PL/SQL 수행 도중 발생하는 오류 처리];
end;

• declare : 선택
  • 실행에 사용될 변수, 상수, 커서 등 선언
• begin : 필수
  • 조건문, 반복문, select, DML, 함수 등 정의
• exception : 선택
  • PL/SQL 실행 도중 발생하는 오류(예외 상황) 처리

set serveroutput on;

• PL/SQL 결과를 화면에 출력하기 위한 사전작업 필요
  • 기본적으로 PL/SQL 실행결과를 화면에 출력하지 않음
  • 위의 쿼리를 적용하면 화면 출력 기능이 활성화됨

[02] 변수와 상수

변수명 자료형 := 값 또는 표현식
v_empno number(4) := 7788;

• 변수 선언
• 변수명 정하기
  • 같은 블록 안에서 식별자는 고유해야 함
  • 대소문자 구별하지 않음
  • 테이블명 규칙을 따름
• 변수의 자료형
  • 스칼라형(scalar type)
    • 숫자, 문자, 날짜 등과 같이 오라클 기본 자료형
  • 참조형(reference type)
    • 테이블 열의 자료형이나 하나의 행 구조를 참조하는 자료형
    • 열 참조 : %type
      • 변수명 테이블명.열이름%type
    • 행 참조 : %rowtype
      • 변수명 테이블명%rowtype
  • 복합형(composite type)
    • 컬렉션, 레코드
  • LOB형(Large Object type)
    • 대용량의 텍스트, 이미지, 동영상 등

변수명 constant 자료형 := 값 또는 표현식
v_tax constant number(1) := 3;

• 상수 선언

변수명 자료형 default 값 또는 표현식
v_deptno number(2) default 10;

• 변수의 기본값 지정

변수명 자료형 not null := 또는 default 값 또는 표현식
v_deptno number(2) not null := 10;
v_deptno number(2) not null default 10;

• 변수에 null 값 저장 제한

[03] 조건 제어문

1.
if <condition> then
    {command}
end if;

2.
if <condition> then
    {command}
else
    {command}
end if;

3.
if <condition> then
    {command}
elsif <condition>
    {command}
...
else
    {command}
end if;

• if 조건문

case 비교기준
    when 값1 then
        {command};
    ...
    when 값n then
        {command};
end case;

• 단순 case 문

case
    when 값1 then
        {command};
    ...
    when 값n then
        {command};
end case;

• 검색 case 문

[04] 반복 제어문

loop
    반복수행작업;
end loop;

• 기본 loop
  • exit 사용하여 반복 종료

while 조건식 loop
    반복수행작업;
end loop;

• while loop

for i in 시작값..종료값 loop
    반복수행작업;
end loop;

• for loop

for i in 시작값..종료값 loop
    continue ...;
    반복수행작업;
end loop;

• continue 문, continue-when 문 포함 반복문
  • 오라클 11g부터 사용 가능

🖥 DataBase - Day 11

👉🏻 [Chapter 19] 저장 서브프로그램

[01] Stored Procedure : insert 문

create [or replace] procedure 프로시저이름
is|as
(
변수이름 데이터타입, -- 프로시저 내에서 사용할 변수 선언
변수이름 데이터타입,
...
)
begin
기능 구현;
end;

• 프로시저 생성 쿼리

exec 저장프로시저[(값1, 값2, ..., 값n)];

• 프로시저에 정보 추가 쿼리

create table dept_b
as select * from scott.dept;

select * from dept_b;

create or replace procedure insertdept(
    myDeptno in number,
    myDname in varchar2,
    myLoc in varchar2
)
as
begin
    insert into dept_b(deptno, dname, loc)
    values(myDeptno, myDname, myLoc);
end;

exec insertdept(50, '개발부', 'Seoul');

select * from dept_b;

• insert 문

[02] Stored Procedure : update 문

create or replace procedure updatedept(
    myDeptno in number,
    myDname in varchar2
)
as
begin
    update dept_b
    set dname = myDname
    where deptno = myDeptno;
end;

exec updatedept(50, '개발부-2');

• update 문

create or replace procedure insertorupdatedept(
    myDeptno in number,
    myDname in varchar2,
    myLoc in varchar2
)
as
    myCount number;
begin
    select count(*) into myCount
    from dept_b
    where deptno = myDeptno;

    if myCount!=0 then
        update dept_b set dname = myDname
        where deptno = myDeptno;
    else
        insert into dept_b(deptno, dname, loc)
        values(myDeptno, myDname, myLoc);
    end if;
end;

exec insertorupdatedept(50, '홍보부', 'Jeju');
exec insertorupdatedept(50, '개발부', 'Jeju');

• 조건에 의해 insert 혹은 update

[03] Stored Procedure : delete 문

create or replace procedure deletedept(
    myDeptno in number
)
as
begin
    delete from dept_b
    where deptno = myDeptno;
end;

exec deletedept(50);

• delete 문

set serveroutput on;

create or replace procedure deletedept2(
    myDeptno in number
)
as 
    myCount number;
begin
    select count(*) into myCount
    from dept_b where deptno = myDeptno;

    if myCount!=0 then
        dbms_output.put_line('부서번호가 '||myDeptno||'인 부서 정보가 삭제됨.');
        delete from dept_b
        where deptno = myDeptno;
    else
        dbms_output.put_line('삭제하려는 부서 정보가 없음');
    end if;
end;

exec deletedept2(70);
exec deletedept2(50);

• delete 문

[04] Stored Procedure

create table emp_t
as select * from scott.emp;

set serveroutput on;

create or replace procedure showcomm(
    myEmpno in number
)
as
    vComm number;
begin
    select comm into vComm
    from emp_t
    where empno = myEmpno;

    if vComm!=0 then
        dbms_output.put_line('직원번호가 '||myEmpno||'인 직원의 comm은 '||vComm);
    else
        dbms_output.put_line('직원번호가 '||myEmpno||'인 직원의 comm은 없음');
    end if;
end;

exec showcomm(7369);
exec showcomm(7499);

• 특정 직원의 comm 출력

create or replace procedure avgsal(
    myAvgsal out number
)
as
begin
    select avg(sal) into myAvgsal from emp_t;
end;

set serveroutput on;
declare
    vAvgsal number;
begin
    avgsal(vAvgsal);
    dbms_output.put_line('평균급여 '||vAvgsal);
end;

• 평균 급여 반환

create or replace procedure calcAvgsal
as
    vAvgsal number;
begin
    select avg(sal) into vAvgsal
    from emp_t;
    dbms_output.put_line('평균급여 : '||vAvgsal);
end;

exec calcAvgsal;

• 직원들의 평균 급여 출력