PL/SQL

• 오라클 안에서도 프로그램을 만들 수 있다. 오라클 전용 프로그래밍 언어 PL/SQL 이다.
  (Procedural Language Structured Query Language)

 

PL/SQL 로 만들 수 있는 것

• procedure : 자바의 메소드와 유사
• function : select 절에 사용할 수 있다.
• trigger : 이벤트(insert, update, delete)가 발생했을 때 연쇄하여 동작

프로시저 만들기(메소드를 만든 것이나 다름없다)

create or replace procedure 프로시저이름(매개변수명 모드 자료형,...)
as
	지역변수(들)
begin
	프로시저가 해야할 sql 명령어(들)
end;
/

 

모드의 종류

• in : 입력용. 프로시저가 일을 하기 위해 값을 전달 받기 위한 모드(기본)
• out : 출력용. 프로시저가 일을 한 결과를 돌려 주기 위한 모드

-- 도서번호, 도서명, 출판사, 가격을 매개변수로 전달받아 도서를 추가하는 프로시저를 만들고 프로시저를 호출하여 insert 를 수행해본다.
create or replace procedure insertBook(
    myBookID in book.bookid%TYPE, -- book테이블에 있는 bookid 와 같은 타입이란 뜻
    myBookName in book.bookname%TYPE,
    myPublisher in book.publisher%TYPE,
    myPrice in book.price%TYPE
)
as
    --지역변수
begin
    --프로시저가 해야할 일
    insert into book values(myBookID, myBookName, myPublisher, myPric);
end;
/

show errors; --에러를 알려줌


create or replace procedure insertBook(
    myBookID in book.bookid%TYPE, -- book테이블에 있는 bookid 와 같은 타입이란 뜻
    myBookName in book.bookname%TYPE,
    myPrice in book.price%TYPE,
    myPublisher in book.publisher%TYPE
)
as
    --지역변수
begin
    --프로시저가 해야할 일
    insert into book(bookid, bookname, price, publisher) values(myBookID, myBookName, myPrice, myPublisher);
end;
/

 

프로시저 실행하기

exec 프로시저명(값1, 값2, ...)

exec insertBook(1001, '월요병 치료법', 300000, '이상미디어');

 

• 예제 코드

-- 도서번호와 가격을 매개변수로 전달받아 해당 도서의 가격을 수정하는 프로시저를 만들고 호출해본다.
create or replace procedure updateBookPrice(
    myBookId book.bookid%TYPE,
    myBookPrice book.price%TYPE
)
as
begin
    update book set price = mybookPrice where bookid = myBookId;
end;
/

exec updateBookPrice(1, 10000);

select * from book where bookid = 1;

--도서번호를 매개변수로 전달받아 해당 도서를 삭제하는 프로시저를만들고 호출
create or replace procedure deleteBook(
    delBookid book.bookid%TYPE
)as

begin
    delete book where bookid = delBookid;
end;
/

exec deleteBook(13);

select * from book where bookid = 13;

 

선택문 사용하기

• 조건에 따라 실행할 sql 문을 선택하도록 할 수 있다.
• 형식

if 조건식 then
	참일 때 실행시킬 SQL
else
	거짓일 떄 실행시킬 SQL 
end if;

-- 도서번호, 도서명, 출판사, 가격을 매개변수로 전달받아
-- 해당 도서가 이미 있다면 도서의 가격을 수정하고
-- 그렇지 않다면 도서를 추가하는 프로시저를 생성하고 호출한다.

create or replace procedure BookInsertOrUpdate(
    myBookId book.bookid%TYPE,
    myBookName book.bookname%TYPE,
    myPublisher book.publisher%TYPE,
    myPrice book.price%TYPE
)
as
    mycount number;
begin
    select count(*) into mycount from book where bookid = myBookID;
    if mycount = 0 then
        insert into book(bookid, bookname, publisher, price) values(myBookId, myBookName, myPublisher, myPrice);
    else
        update book set price = myPrice;
    end if;
end;
/

exec BookInsertOrUpdate(1, '이것', '은?', 10300);
exec BookInsertOrUpdate(3000, '새로운 책', '이상미디어', 10300);
select * from book where bookid = 3000 or bookid = 1;

 

반환 값이 있는 프로시저 만들기

• 형식

create or replace procedure 프로시저 이름(
    매개변수 in 자료형,
    매개변수2 out 자료형,...
)
as
    select 칼럼1, 칼럼2, ... into 매개변수1, 매개변수 2..from ~~
end;
/

• 변수를 선언하고 프로시저를 호출하고 반환값을 확인(출력) 하기 위한 프로그램이 필요하다.
• 즉, PL/SQL 구문이 필요하다.

set serveroutput on; -- 화면에 결과를 출력하기 위해 필요한 설정. 콘솔을 닫기 전까지 유효

--pl/sql 의 기본 구조
declare
	변수명 자료형;
begin
	프로시저호출(변수명);		-- 프로시저에서 전달하는 변수에 값을 담아준다.
    dbms.output.put_line(변수명) -- 콘솔에 출력하는 명령
end;

-- 평균 도서 가격을 반환하는 프로시저를 호출해본다.
set serveroutput on;

declare
	result number;
begin
	getAvgPrice(result);
    dbms_output.put_line('책값 평균 : ' || result); -- || -> 문자열 연결을 뜻함
end;
/

--고객 번호를 매개변수로 전달받아 해당 고객의 총 구매건수와
-- 총 구매 금액을 반환하는 프로시저를 만들고 호출해본다.

--1. 프로시저 만들기
create or replace procedure getCustomer(
    myCustid in orders.custid%TYPE,
    myCnt out number,
    mySum out number
)
as
begin
    select count(*), sum(saleprice) into myCnt, mySum from orders where custid=myCustid;
end;
/

--2. 조회하기
declare
    cnt number;
    s number;
begin
    getCustomer(1, cnt, s);
    dbms_output.put_line('총 구매 건수 ' || cnt);
    dbms_output.put_line('총 구매 금액 ' || s);
end;
/

 

cursor를 사용하는 방법

create or replace procedure 프로시저이름
(
	매개변수(들)
)
as
	지역변수(들)
    cursor 커서이름 is select ~~ --여기 셀렉트 결과가 여러건일 때 미리 정의(실행 X)
begin
	open 커서이름;	--이 때 sql 이 동작한다.
    loop			--반복문 시작
    	fetch 커서이름 into 변수1, 변수2, .. -- select 한 칼럼의 값을 변수에 저장(위의 컬럼들 수에 맞춰야함)
        exit when 커서이름 %NOTFOUND; -- 더이상 레코드가 없으면 반복문 탈출
    end loop; --반복문 끝
    close 커서이름;
end;
/

-- 고객번호를 매개변수로 전달받아
-- 해당 고객이 구매한 모든 도서 명을 출력하는 프로시저를 만들고
-- 호출해본다.
create or replace procedure orderBook
(
    myCustid orders.custid%TYPE
)
as
    myBookname varchar2(30);
    cursor c is select bookname from book b, orders o
    where b.bookid = o.bookid and
    custid = myCustId;
begin
    open c;
    loop
        fetch c into myBookname;
        exit when c%NOTFOUND;
        dbms_output.put_line(myBookName);
    end loop;
    close c;
end;
/

exec orderBook(3);

--모든 주문에 대하여 총 이익금을 계산하여 출력하는 프로시저를 만들고
--호출해본다. 주문 가격이 30000원 이상이면 이익금은 10%
--그렇지 않으면 5%이다.
create or replace procedure princAllprofit
as
    total number;
    price number;
    profit number;
    cursor c is select saleprice from orders;
begin
    total := 0; -- 대입연산자
    open c;
    loop
        fetch c into price;
        IF price >= 3000 then 
            profit := price * 0.1;
        ELSE
            profit := price * 0.05;
        end if;
        exit when c %NOTFOUND;
        total := total + profit;        
    end loop;
    close c;
    dbms_output.put_line('총 이익금:'||total);
end;
/
exec princAllprofit();