2022.04.29. 금요일 이공계 전문기술 연수사업 25일차

오늘 배운 것

pl/sql
오라클 전용 데이터베이스 프로그래밍 언어

종류
프로시저 자바의 메소드처럼 어떤 일처리를 위한 서로 관련있는 sql명령어들의 모임
트리거 어떤 테이블에서 dml이 일어났을 때 연쇄적(자동)으로 다른 테이블에 sql을 실행시키기 위한 프로시저
함수 select 절에 사용할 수 있는 프로시저

프로시저 만드는 방법
create or replace procedure 프로시저이름(매개변수이름 모드 자료형,...) as begin end;/

모드에는 in(입력용 - 프로시저 실행에 필요한 값을 전달),out(출력용-프로시저에 구해진 값을 돌려줄 용도)이 올 수 있음
as와 begin사이에 프로시저 실헹에 필요한 변수(지역변수)를 선언할 수 있음
begin과 end;사이에 프로시저가 해야 할 명령어를 써 줌

ex)부서번호, 부서명, 부서위치를 매개변수로 전달받아 새로운 부서를 등록하는 프로시저를 만들고 호출
create or replace procedure insertDept(
p_dno number,
p_dname varchar2,
p_dloc varchar2
)
as
begin
insert into dept(dno,dname,dloc) values(p_dno,p_dname,p_dloc);
end;
/

SQL> exec insertDept(50,'인사팀','강남');
SQL> @ff
SQL> select * from dept;

  DNO DNAME      DLOC
----- ---------- ----------
   10 총무팀     구로
   20 기획팀     서초동
   30 개발1팀    서교동
   40 개발2팀    판교
   50 인사팀     강남
   
제어문을 사용하는 프로시저 

if 조건식 then

else

end if;

ex)도서번호,도서명,출판사,가격을 매개변수로 전달받아
동일한 도서가 있는지 점검한 후 삽입하는 프로시저(있으면 가격만 수정)만든 후 호출
create or replace procedure book_insertorupdate(
p_bookid number,
p_bookname varchar2,
p_publisher varchar2,
p_price number
)
as
mycount number;
begin
select count(*) into mycount from book where bookname=p_bookname;
if mycount != 0 then
update book set price=p_price where bookname=p_bookname; 
else
insert into book values(p_bookid,p_bookname,p_publisher,p_price);
end if;
end;
/

*pl/sql은 :=가 대입연산자이고 ||는 문자열을 연결해달라는 의미임 이거 쓸 때 안 쓸 때 잘 생각해야 함!!!!!!!!!!!!

ex)orders테이블의 주문내역에 대하여 전체 이익금을 계산하여 출력하는 프로시저 생성
이익금은 판매가격이 삼만원이상이면 10%, 그렇지 않으면 5%
create or replace procedure interest
as
total number;
price number;
cursor c is select saleprice from orders;
begin
total:=0;
open c;
loop
fetch c into price;
exit when c%NOTFOUND;
if price >= 30000 then
total := total+price*0.1;
else
total := total+price*0.05;
end if;
end loop;
close c;
dbms_output.put_line('전체 이익금액 = ' || total);
end;
/

*dbms_output을 통하여 출력하려면
set serveroutput on;

---------------------------------------------------------------------------------------
트리거
데이터의 변경(insert/update/delete)이 실행될 때 자동으로 따라서 실행되는 프로시저 (꼭 똑같은 변경이 실행되지는 않음)
데이터 변경문 : insert,update,delete
시점 : before, after

트리거의 형식
create or replace trigger 트리거이름 
시점 명령어 on 테이블이름 for each row
declare
begin
end;
/

시점에는 before, after
명령어에는 insert,update,delete가 옴
declare와 begin사이에 변수를 선언
begin과 end 사이에 트리거가 해야 할 명령을 써 줌

*트리거에서 사용할 수 있는 키워드
insert,update가 되고 난 후의 레코드를 의미 => :new
delete,update가 되기 전의 레코드를 의미 => :old

ex)새로운 도서를 삽입한 후 자동으로 book_log테이블에 삽입한 내용을 기록하는 트리거 생성

create table book_log(
bookid number,
bookname varchar2(50),
publisher varchar2(50),
price number
);

create or replace trigger afterinsertbook
after insert on book for each row
begin
insert into book_log values(:new.bookid,:new.bookname,:new.publisher,:new.price);
dbms_output.put_line('삽입 튜플을 book_log에 백업하였습니다.');
end;
/

실습을 위해 다음과 같은 테이블 생성
create table 상품(
상품번호 number primary key,
상품명 varchar2(30),
가격 number,
재고수량 number
);

create table 입고(
입고번호 number primary key,
상품번호 number references 상품(상품번호),
입고수량 number,
입고일 date
);

insert into 상품 values(100,'색종이',500,0);
insert into 상품 values(200,'삼각자',700,0);
insert into 상품 values(300,'딱풀',600,0);

ex)입고가 되면 입고한 수량만큼 상품 테이블의 재고 수량을 증가시키는 트리거를 생성 후 호출
create or replace trigger pluscount
after insert on 입고 for each row
declare
begin
update 상품 set 재고수량=재고수량+:new.입고수량 where 상품번호=:new.상품번호;
dbms_output.put_line('재고수량을 더했습니다.');
end; 
/
SQL> insert into 입고 values(2,100,100,sysdate);
재고수량을 더했습니다.

1 개의 행이 만들어졌습니다.

SQL> select * from 상품;

  상품번호 상품명
---------- ------------------------------------------------------------
      가격   재고수량
---------- ----------
       100 색종이
       500        100

       200 삼각자
       700          0

       300 딱풀
       600          0
       
ex)입고테이블에서 레코드가 삭제되면 해당 상품의 재고수량을 감소시키는 트리거를 생성 후 호출
create or replace trigger deletecount
after delete on 입고 for each row
begin
update 상품 set 재고수량 = 재고수량-:old.입고수량 where 상품번호 = :old.상품번호;
end;
/

ex)입고테이블에 수정이 되면 수정된 입고수량만큼 상품테이블에도 재고수량도 수정되는 트리거 생성 후 실행
create or replace trigger updatecount
after update on 입고 for each row
begin
update 상품 set 재고수량=재고수량 + :new.입고수량-:old.입고수량 where 상품번호 = :old.상품번호;
end;
/

SQL> update 입고 set 입고수량=70 where 입고번호=2;

1 행이 업데이트되었습니다.

SQL> select * from 입고;

  입고번호   상품번호   입고수량 입고일
---------- ---------- ---------- --------
         2        100         70 22/04/29
         4        200          5 22/04/29

SQL> select * from 상품;

  상품번호 상품명
---------- ------------------------------------------------------------
      가격   재고수량
---------- ----------
       100 색종이
       500         70

       200 삼각자
       700          5

       300 딱풀
       600          0

실습을 위해 테이블을 하나 더 생성
create table 주문(
주문번호 number primary key,
상품번호 number references 상품(상품번호),
고객번호 number,
주문수량 number,
주문일 date
);

ex)주문이 일어나면 주문 수량만큼 상품의 재고수량을 감소시키는 트리거 생성 후 실행
create or replace trigger after주문
after insert on 주문 for each row
begin
update 상품 set 재고수량 = 재고수량 - :new.주문수량 where 상품번호=:new.상품번호;
end;
/

*시점이 반드시 before여야하는 경우?
자식테이블에 insert 이벤트가 발생되었는데 그 값이 부모테이블에 반드시 참조되는 컬럼인 경우
부모테이블에 없다면 추가할 수 없음
그 경우에 먼저 실행하여 부모테이블에 레코드를 추가해야 한다 이 때는 before 시점이 와야 함
//이게 뭔소리?

---------------------------------------------------------------------------------
사용자 정의 함수(function)

오라클이 제공하는 함수들이 많이 있음
sum,max,min,count, ... ->이러한 함수들은 모두 select절에 사용할 수 있음
이처럼 사용자가 select절에 사용할 수 있는 사용자가 필요한 함수를 만들 수 있음
이것을 사용자 정의함수라고 하고 select 절에 사용해야하니까 반드시 반환값이 있어야 함

사용자 정의 함수(function) 생성 - 반드시 매개변수가 필요함
create or replace function 함수이름 (매개변수 자료형) return 반환하는 자료형
is
지역변수 선언
begin
함수가 해야할 명령어(들)
return 리턴해야하는 값;
end;
/ 

ex)판매 금액을 매개변수로 전달받아 이익금을 반환하는 함수 생성 후 실행
단, 판매 금액이 3만원 이상일 시 10프로 이익 그렇지 않으면 5프로 이익 
create or replace function margin (saleprice number) return number
is
result number;
begin
if(saleprice>=30000) then
result := saleprice*0.1;
else
result := saleprice*0.05;
end if;
return result;
end;
/
이제, 각 주문에 대한 주문번호,판매금액,이익금을 출력
select orderid,saleprice,margin(saleprice) from orders;

ex)오늘 주문에 대하여 주문번호,도서번호,도서명,고객명,이익금을 출력
select orderid,b.bookid,bookname,name,margin(saleprice) 
from customer c, book b, orders o 
where o.custid=c.custid and b.bookid=o.bookid and to_char(orderdate,'yyyymmdd')='20220429';

ex)고객번호를 매개변수로 전달받아 그 고객의 총 주문금액을 계산하여 주문 총액이 2만원이상이면 "우수"
그렇지 않으면 "보통"을 반환하는 grade함수 생성 후 호출

create or replace function grade(p_custid number) return varchar2
is
result varchar2(10);
f_sum number;
begin
select sum(saleprice) into f_sum from orders where p_custid=custid;
if(f_sum>=20000) then
result:='우수';
else
result:='보통';
end if;
return result;
end;
/

ex)고객번호를 매개변수로 전달받아 국내에 거주하면 국내거주,그렇지 않으면 국외거주를 반환하는 함수 생성 후 호출
create or replace function inkorea(f_custid number) return varchar2
is
result varchar2(20);
f_addr varchar2(30);
begin
select address into f_addr from customer where f_custid=custid;
if(f_addr like '대한민국 %') then
result := '국내거주';
else
result := '해외거주';
end if;
return result;
end;
/

select custid, name, inkorea(custid) from customer;
select custid, name, grade(custid), inkorea(custid) from customer;

--------------------------------------------------------------------------------------------
데이터 모델링
어플리케이션을 개발하기에 앞서 먼저 사용자와의 면접을 통하여 사용자의 요구사항을 파악하여 어플리케이션 시스템에 필요한
데이터베이스를 먼저 구축해야 함 이 때, 데이터베이스를 구축하기에 필요한 테이블들을 도출하고 각 테이블을 어떠한
속성으로 구성되는지 도출하고 또 각 테이블은 어떤 속성으로 관계가 있는지 설계하는 과정

데이터 모델링 결과의 산출물
테이블 명세서
인스턴스 명세서
ER다이어그램(테이블과 테이블 사이의 관계를 그림으로 나타냄)

데이터 모델링의 과정
요구사항 분석 -> 논리적 설계 -> 물리적 설계 -> 데이터베이스 구축

데이터 모델링 과정에서 결정되어야 할 사항
구조,연산,제약조건

개체 속성 관계 
인스턴스 = 레코드라고 생각해도 될돗? =튜플
도메인 = 컬럼에 값이 올 수 있는 종류

오늘 헷갈린 것

• :=의 사용! 지역변수에 할당할 때만! (pl/sql에서)

오늘 한 생각

너무 오래 작은 글씨 들여다보니 머리가 안돌아가는 느낌이다...

뭔가 brain fogg 쌓인 기분.. 이럴 땐 어떻게 해소해줘야하는 걸까? 과감하게 쉬기? 조금 쉬다가 다시 하기?....