[Day 25] Database 0429 - 내용 정리

Doyeon Kim·2022년 5월 1일

Database

목록 보기

11/12

🌞PL/SQL 제어문

<기본문법>

if 조건식 then
else
end if;

예제) 도서번호, 도서명, 출판사, 가격을 매개변수로 전달받아 동일한 도서명의 도서가 이미 있으면 가격을 수정하고 그렇지 않으면 도서를 등록하는 프로시저를 만들고 호출해보자.

create or replace procedure
BookInsertOrUpdate(
	p_bookid in number,
	p_bookname in varchar2,
	p_publisher in varchar2,
	p_price in number
)
as
	mycount number; //도서의 수를 담는다.
begin
	select count(*) into mycount from book 
	where bookname = p_bookname; //도서테이블로부터 bookname을 p_bookname에 넣고 그것이 있으면 count(*)의 수가 올라간다. 그것을 mycount에 넣기
						
	if mycount != 0 then //mycount가 0이 아닌가요? ==> 책이 이미 있다는 것
		update book set price = p_price where //이미 있다면 도서의 가격을 입력받은 p_price로 수정하자 !
		bookname = p_bookname;
	else
		insert into book values(p_bookid, p_bookname, p_publisher, p_price); //mycount가 0이라면 도서를 등록하자 !
	end if;
end;
/

==> 같은 도서명이 있으면 그 도서명에 대한 가격을 변경해준다.
==> 같은 도서명이 없다면 그 도서를 등록해준다.

🌞cursor를 사용한 프로시저

예제) orders 테이블에 주문내역에 대하여 전체이익금을 계산하여 출력하는 프로시저를 만들어보자. 이익금은 판매가격이 30,000원 이상이면 10%, 그렇지 않으면 5%가 이익금이다.

create or replace procedure
interest //매개변수가 필요없으면 괄호를 안 해도 된다.
as
	price number; //가격에 대한 변수
	total number; //전체 이익금을 계산하기 위한 변수
	cursor c is select saleprice from orders; //이 쿼리문의 레코드는 여러 건이기 때문에 cursor를 만들어줘야 한다.
begin
	total := 0; //total의 초기값을 0으로 준다.
	open c; //cursor에 있는 sql 쿼리가 동작한다.
	loop //레코드의 값이 여러 건이기 때문에 루프를 돈다.
		fetch c into price; //c는 cursor에 saleprice를 가리킴. 그것을 price 변수에 저장한다.
		exit when c%NOTFOUND; //cursor에 더 이상 레코드가 없으면 탈출해라!
		if price >= 30000 then //price가 30000원보다 크거나 같냐?
			total := total + price * 0.1; //30000원이면 total에 10% 곱한 값을 누적
		else
			total := total + price * 0.05; //30000원이 아니면 total에 5% 곱한 값을 누적
		end if;
	end loop;
	close c; //사용했던 cursor를 닫아줌.
	dbms_output.put_line('전체 이익 금액 = ' || total); //출력하기
end;
/

==> PL/SQL의 출력에서 ||는 자바에서의 = 와 같은 의미
==> dbms_output을 통하여 출력하려면 다음의 명령어를 먼저 실행해야 한다.

set serveroutput on;
exec interest();

🌞함수(function)

select 절에 사용할 수 있는 프로시저
오라클이 제공하는 함수들이 많이 있다. sum, max, min, count, ...
이러한 함수들은 모두 select절에 사용할 수 있다.
이것들 처럼 사용자가 select절에 사용할 수 있는 사용자가 필요한 함수를 만들 수 있다.
==> 사용자 정의함수(function)
==> select 절에 사용되어야 하니 반드시 변환 값이 있어야 한다!

<기본문법>

create or replace function 함수이름(매개변수명 자료형) return 자료형
is
	변수선언
begin
	함수가 해야할 문장(들)
	return 값;
end;
/

예시) 판매금액을 매개변수로 전달받아 이익금을 반환하는 함수를 만들어보자. 판매금액이 30000원이상이면 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;

🌞데이터모델링

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