Trigger (트리거)

: 테이블이 변경되면 이를 이벤트로 다른 테이블이 자동으로(연쇄적으로) 변경되도록 하기 위해서 사용된다.

특정 동작을 이벤트로 그로 인해서만 실행되는 프로시저의 일종이다.

-- 트리거 사용 방법
create trigger trigger_name
timing[before | after] event[insert | update | delete]
on table_name
[for each row] -- 튜플 단위로 변경할 때 사용
[when conditions] -- 조건을 줄 수 있음
begin
statement
end;

before 타이밍은 문장이 실행될 때, 실행되기 전에 가지고 있는 begin ~ end 사이의 문장 실행

after 타이밍은 문장이 실행되고 난 후 begin ~ end 사이의 문장 실행

for each row 사용 시 행 레벨 트리거

사용하지 않을 시 문장 레벨 트리거

• 상품분류 테이블에 새로운 데이터가 들어오면 ＇상품분류가 추가되었습니다.'란 메시지를 출력도록 문장 레벨 트리거를 작성하기

-- 상품분류 테이블에 새로운 데이터가 들어오면 ＇상품분류가 추가되었습니다.'란 메시지를 출력도록 문장 레벨 트리거를 작성하기
-- 1. 다음 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력하시오.(파일이름:TRIG01.SQL)
create or replace trigger tg_lprod_id
after insert
on lprod
begin
dbms_output.put_line('상품분류가 추가되었습니다.');
end;
/

-- 2. 만들어진 트리거 확인
select trigger_name from user_triggers;

-- 3. 상품분류 테이블에 로우를 추가
set serveroutput on;

insert into lprod(lprod_id, lprod_gu, lprod_nm) values (
    (select max(lprod_id) + 1 from lprod),
    (select 'P' || (substr(max(lprod_gu), 2) + 1) from lprod),
    '트리거 추가 값1');

select * from lprod;

• 사원 테이블에 새로운 데이터가 들어오면(즉, 신입 사원이 들어오면) 급여 테이블에 새로운 데이터(즉 신입 사원의 급여 정보)를 자동으로 생성하도록 하기 위해서 사원 테이블에 트리거를 작성해 봅시다. (신입사원의 급여는 일괄적으로 200으로 합니다.)

-- 사원 테이블에 새로운 데이터가 들어오면(즉, 신입 사원이 들어오면) 급여 테이블에 새로운 데이터(즉 신입 사원의 급여 정보)를 자동으로 생성하도록 하기 위해서 사원 테이블에 트리거를 작성해 봅시다. (신입사원의 급여는 일괄적으로 200으로 합니다.)
-- 1. 직원을 저장할 테이블 생성
create table emp01 (
    empno number(4) primary key, -- 이럴 경우 key name이 존재하지 않음
    empname varchar2(45),
    empjob varchar2(60)
);

-- 2. 급여를 저장할 테이블 생성
create table sal01 (
    salno number(4) primary key,
    sal number(7, 2),
    empno number(4) references emp01 (empno) -- 사원 테이블의 사원 번호를 연결해서 사용하겠다는 뜻
);

-- 3. 급여번호를 자동 생성하는 시퀀스를 정의하고 이 시퀀스로부터 일련번호를 얻어 급여번호를 부여
-- 초기값 지정하지 않을 시 1부터 진행됨
create sequence sal01_salno_seq;

-- 4. 급여 테이블 입력
-- 사원 테이블에서 insert 된 후에 trg_02가 돌아감
create or replace trigger trg_02
after insert
on emp01
for each row -- 행 단위로 돌아감
begin
-- 급여 테이블을 자동으로 삽입 해줌
insert into sal01 values(sal01_salno_seq.nextval, 200, :new.empno); -- :new.mepno : 입력되는 번호가 들어감.
end;
/

-- 5. 신입사원 입사후 데이터 생성
insert into emp01 values(1, '전수빈', '프로그래머'); -- 이 경우 new.empno에 들어가는 값은 1임

-- 6. 결과 확인
select * from emp01;
select * from sal01;

• 사원이 삭제되면 그 사원의 급여 정보도 자동 삭제되는 트리거

-- 사원이 삭제되면 그 사원의 급여 정보도 자동 삭제되는 트리거
-- 참조하고 있기에 사원번호 2를 삭제가 불가능함
-- 삭제를 하기 위해선 사원의 급여 정보도 삭제되어야 함.
create or replace trigger trg_03
after delete on emp01
for each row
begin
delete from sal01 where empno = :old.empno; -- 삭제된 것을 표현하기 위해 old.empno로 사용해야 한다.
end;
/

-- 삭제
delete from emp01 where empno = 2;

-- 확인
select * from emp01 where empno = 2;
select * from sal01;

• 입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거를 작성

-- 입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거를 작성
-- 1. 상품 테이블 생성
create table product (
    prod_id varchar2(6) primary key,    -- 상품코드
    prod_name varchar2(12) not null,    -- 상품명
    prod_jejo varchar(12),              -- 제조사
    prod_sale number(8),                -- 소비자가격
    prod_jaego number default 0         -- 재고수량
);

-- 2. 입고 테이블 생성
create table ipgo(
    ipgo_id number(6) primary key,      -- 입고 번호
    prod_id varchar2(6) references -- product(prod_id)를 참조
                    product(prod_id),   -- 상품코드
    ipgo_day date default sysdate,      -- 입고일자
    ipgo_qty number(6),                 -- 입고수량
    ipgo_cost number(8),                -- 입고단가
    ipgo_amount number(8)               -- 입고금액
);

-- 3. 상품 테이블에 샘플 데이터 입력
insert into product(prod_id, prod_name, prod_jejo, prod_sale)
    values('A00001', '세탁기', 'LG', 500);
insert into product(prod_id, prod_name, prod_jejo, prod_sale)
    values('A00002', '컴퓨터', 'LG', 700);
insert into product(prod_id, prod_name, prod_jejo, prod_sale)
    values('A00003', '냉장고', '삼성', 600);

-- 4. 입고 트리거
create or replace trigger trg_04
after insert
on ipgo
for each row
begin
update product
set prod_jaego = prod_jaego + :new.ipgo_qty -- insert 이기에 new 사용
where prod_id = :new.prod_id;
end;
/

-- 5. 입고 테이블에 행 추가
insert into ipgo(ipgo_id, prod_id, ipgo_qty, ipgo_cost, ipgo_amount)
    values(11, 'A00001', 5, 320, 1600);
insert into ipgo(ipgo_id, prod_id, ipgo_qty, ipgo_cost, ipgo_amount)
    values(2, 'A00001', 2, 320, 640);
insert into ipgo(ipgo_id, prod_id, ipgo_qty, ipgo_cost, ipgo_amount)
    values(3, 'A00002', 100, 200, 20000);

-- 6. 입고 수량 변경 시 재고 수량 변경 트리거
create or replace trigger trg_05
after update on ipgo
for each row
begin
update product
set prod_jaego = prod_jaego + (-:old.ipgo_qty + :new.ipgo_qty)
where prod_id = :new.prod_id;
end;
/

update ipgo set ipgo_qty=200, ipgo_amount=40000
where ipgo_id=3;

-- 7. 삭제 트리거
create or replace trigger trg_06
after delete on ipgo
for each row
begin
update product
set prod_jaego = prod_jaego + -:old.ipgo_qty
where prod_id = :old.prod_id;
end;
/

delete ipgo where ipgo_id=3;

• 분류테이블에 추가되거나, 변경될 때 분류코드를 항상 대문자로 처리하는 트리거

-- 트리거 생성
create or replace trigger tg_lprod_upper
    before insert or update
    on lprod
    for each row
begin
    :new.lprod_gu :=upper(:new.lprod_gu);
end;
/

-- 시퀀스 사용하기 위해 시퀀스 생성
create sequence lprod_seq minvalue 12;

-- 데이터 삽입
insert into lprod
    values (lprod_seq.nextval, 'tt07', '트리거 테스트 ');

• 장바구니 테이블에 입력 발생시 재고수 테이블에 출고, 현재고를 변경하는 트리거

-- 장바구니 테이블에 입력 발생시 재고수 테이블에 출고, 현재고를 변경하는 트리거
create or replace trigger tg_cart_qty_change
    after insert or update or delete on cart
    for each row
declare
    v_qty number;
    v_prod varchar2(20);
begin
    if inserting then -- 입력(주문 시)
        v_qty := nvl(:new.cart_qty, 0);
        v_prod := :new.cart_prod;
    elsif updating then -- 수정 (주문 수량 변경시)
        v_qty := nvl(:new.cart_qty, 0) - nvl(:old.cart_qty, 0);
        v_prod := :new.cart_prod;
    elsif deleting then -- 삭제 (주문 취소 시)
        v_qty := -(nvl(:old.cart_qty, 0));
        v_prod := :old.cart_prod;
    end if;
update remain set 
    remain_o = remain_o + v_qty, -- 출고
    remain_j_99 = remain_j_99 - v_qty -- 재고
    where remain_year = '2020' and remain_prod = v_prod;
    dbms_output.put_line('수량 : ' || v_qty);
exception
    when others then
        dbms_output.put_line('예외 발생 : ' || sqlerrm);
end;
/

-- 확인
select * from remain where remain_prod = 'P101000001';

• 한 줄씩 해석

CREATE or REPLACE TRIGGER tg_cart_qty_change
-- tg_cart_qty_change 이름의 트리거 생성 또는 갱신
      AFTER  insert or update or delete    ON  cart
-- cart 테이블에서 삽입, 수정, 삭제가 이루어진 후에 트리거 발생하도록 함      
      FOR EACH ROW
-- 각 행(=ROW=RECORD)이 변경될 때마다 트리거를 발생시키는 방법, 없을때는 단 한번만 트리거를 발생시킬 때 사용(문장레밸트리거)
DECLARE 
-- PL/SQL 구조로 선언부문-변수, 상수, 사용자예외 등
       v_qty NUMBER;
-- v_qty 정수숫자타입 변수 선언        
       v_prod VARCHAR2(20);
-- v_prod 가변문자타입 20바이트 크기로 변수 선언       
 BEGIN
-- PL/SQL 구조로 시작부문-실행부문 시작
    IF INSERTING THEN
-- 만약 트리거 문장이 삽입이면(즉, 상품이 팔리면)    
         v_qty := NVL(:NEW.cart_qty,0);
-- v_qty에 데이터가 삽입될 때 들어온 새로운 값(상품이 팔린 수량) 저장
-- 이 값(NEW.cart_qty)이 NULL이 아니면 자신값이고 NULL이면 0으로 대체
         v_prod := :NEW.cart_prod;
-- v_prod에 데이터가 삽입될 때 들어온 새로운 값(팔린 상품번호) 저장,          
    ELSIF UPDATING THEN
         v_qty :=  NVL(:NEW.cart_qty,0) - NVL(:OLD.cart_qty,0);
-- v_qty에 데이터가 수정될 때 들어온 수량에서 기존의 수량을 빼서 저장(이 값을 변경사항에 고려)
         v_prod := :NEW.cart_prod;
-- v_prod에 데이터가 수정될 때 들어온 상품번호 저장,           
    ELSIF DELETING THEN
         v_qty :=  -(NVL(:OLD.cart_qty,0));
         v_prod := :OLD.cart_prod;
    END IF;    
       UPDATE remain SET remain_o = remain_o + v_qty,  
                                    remain_j_99 = remain_j_99 - v_qty
        WHERE remain_year = '2023' AND remain_prod = v_prod;
    
       DBMS_OUTPUT.PUT_LINE('수량 :' || v_qty);
EXCEPTION 
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('예외 발생:' || SQLERRM);     
END;
-- PL/SQL 구조로 끝부문-실행부문 끝
/
-- Ctrl+Enter 실행시 마감처리


SET SERVEROUTPUT ON;

-- INSERTING
-- 주문테이블에 P101000001상품 20개 주문하기(20개팔림) => 
INSERT INTO cart VALUES ('a001','2023041000001','P101000001',20);
-- 출고수량에서 + 20개, 재고수량에서 - 20개 되어짐

-- 결과확인
SELECT * FROM remain
    WHERE REMAIN_YEAR = '2023' AND REMAIN_PROD = 'P101000001';

INSERT INTO cart VALUES ('a001','2023041000001','P101000001',100);
INSERT INTO cart VALUES ('a001','2023041000002','P101000001',10);

-- 출고수량에서 + 10개, 재고수량에서 - 10개 되어짐

SELECT * FROM CART
ORDER BY  CART_NO DESC;
SELECT * FROM MEMBER;
-- UPDATING
-- 주문테이블에서 판매된 P101000001상품에서 판매수량이 변경됨(10개에서 5개로 수정) => 출고에서는 -5개, 재고에서는 +5개로 적용도어야 함 
UPDATE cart SET CART_QTY=5
    WHERE CART_MEMBER='a001' AND CART_NO = '2023041000002';

-- DELETING
-- 주문테이블에서 판매번호 2023041000001로 판매된 P101000001상품에서 판매수량이 취소됨(20개 취소) => 출고에서는 -20개, 재고에서는 +20개로 적용도어야 함

• 주문 순량 변경 테스트

-- 요구사항2
-- 회원번호 : c001
-- 주문번호 : 2020080100001
-- 상품번호 : P101000003
-- 주문수량 : 입력할때 20
--          수정할 때 10 (20 => 10)
--          삭제할때 (해당수량 취소함)

select * from cart where cart_prod='P101000003'; -- P101000003 상품의 판매 내역
select * from member where mem_id = 'c001'; -- 회원번호 c001의 정보
select * from prod where prod_id = 'P101000003'; -- P101000003의 상품 정보
select * from remain where remain_prod='P101000003'; -- P101000003 상품 재고 수량
select * from cart where cart_no='2020080100001'; -- 2020080100001 주문 번호의 판매 내역

-- 주문 수량 20개
insert into cart values('c001', '2020080100001', 'P101000003', 20);

select * from cart; -- 주문 내역이 쌓임
select * from remain; -- 재고 테이블
select * from remain
    where remain_prod='P101000003';

-- 주문 수량 10개로 수정
update cart set cart_qty=10
    where cart_member='c001' and cart_no='2020080100001';

-- 주문 내역 삭제
delete from cart
    where cart_member='c001' and cart_no='2020080100001';

• 해당 회원의 마일리지도 변경되는 트리거

-- 해당 회원의 마일리지도 변경되는 트리거
create or replace trigger tg_mem_mileage_chage
    after insert or update or delete on cart
    for each row
declare
    v_qty number(9);
    v_prod varchar2(10);
    v_member varchar2(15);
    v_prod_sale prod.prod_sale%type;
    v_sum number := 10;
begin
    if inserting then
        v_qty := nvl(:new.cart_qty, 0);
        v_prod := :new.cart_prod;
        v_member := :new.cart_member;
    elsif updating then
        v_qty := nvl(:new.cart_qty, 0) - nvl(:old.cart_qty, 0);
        v_prod := :new.cart_prod;
        v_member := :new.cart_member;
    elsif deleting then
        v_qty := -(nvl(:old.cart_qty, 0));
        v_prod := :old.cart_prod;
        v_member := :old.cart_member;
    end if;
    select prod_sale into v_prod_sale from prod where prod_id = v_prod;
    v_sum := (v_prod_sale*v_qty)*0.01;
    
    update member set mem_mileage = mem_mileage + v_sum
        where mem_id = v_member;
    dbms_output.put_line('변경 마일리지 ==> ' || v_sum);
exception
    when others then
        dbms_output.put_line('예외 발생: ' || sqlerrm);
end;
/

select * from member where mem_id = 'a001';

-- 삽입
insert into cart values ('a001', '2020100100001', 'P201000001', 3);

-- 확인
select * from cart;
select * from remain;
select * from prod;

select * from cart where cart_no = '2020090100001';
select * from remain where remain_prod='P201000001';
select * from prod where prod_id='P201000001';
select * from member where mem_id = 'a001'; -- 삽입전 마일리지 1000

Package (패키지)

: 꾸러미, 작은 잠, 포장한 상품이라는 의미로 쓰인다.

업무적으로 관련 있는 것을 하나로 묶어서 사용하며 여러 변수, 커서, 함수, 프로시저, 예외를 묶어 캡슐화 한다.

- 장점

모듈화로 어플리케이션 개발이 용이해짐

공유하여 사용 가능

 cursor 문

select 문에서 생성된 결과 집합에 대해 개별적인 행 단위 작업을 가능하게 한다.

query 결과를 읽거나 수정, 삭제할 수 있도록 해준다.

select 문의 결과를 정의한 후 사용해야 한다.

-- corsor 실행
-- 2020년도 및 상품별 총 입고수량을 출력하는 커서
declare
    v_prod varchar2(30);
    v_qty number(10, 0);
    
    cursor UpRemain_cur is
    -- UpRemain_cur이 buy_prod, sum(buy_qty)의 컬럼을 가지고 있음
        select buy_prod, sum(buy_qty) from buyprod
        where extract(year from buy_date) = 2020
        group by buy_prod order by buy_prod asc;
begin
    open UpRemain_cur;
    fetch UpRemain_cur into v_prod, v_qty;
    while(UpRemain_cur%found) loop -- 데이터가 있을 때 반복
        dbms_output.put_line(UpRemain_cur%rowcount || '번째 상품 = ' 
        || v_prod || ' 입고수량 = ' || v_qty || '입니다.');
        fetch UpRemain_cur into v_prod, v_qty; -- cursor에 저장되어 있는 다음 것을 찾아 저장 > 이후 while문을 돌면 다음 것을 읽음.
    end loop;
    close UpRemain_cur; -- 메모리에 올라온 것들을 끊어줘야함.
end;
/

-- 직업을 변수로 받아 이름 회원명과 마일리지를 출력하는 커서
declare
    v_name varchar2(30);
    v_mileage number(10);
    
    cursor member_cur (v_job varchar2) is
        select mem_name, mem_mileage from member
        where mem_job = v_job
        order by mem_name asc;
begin
     open member_cur('주부');
     loop
        fetch member_cur into v_name, v_mileage;
        exit when member_cur%notfound;
        dbms_output.put_line(member_cur%rowcount || '번째 ' || v_name || ', ' || v_mileage);
     end loop;
     close member_cur;
end;
/

for loop 문

• FOR LOOP를 이용하는 CURSOR

각 반복하는 동안 커서를 자동으로 open하고 모든 행이 처리되면 자동으로 커서를 close 한다. 

-- record_name 은 묵시적으로 자동 생성되므로 선언하지 않는다.
FOR record_name IN cursor_name LOOP 
    statement;
END LOOP;

• Subquery를 이용한 FOR LOOP

서브쿼리를 사용하여 커서를 선언하지 않아도 동일한 결과를 보여준다.

FOR record_name IN (subquery) LOOP

-- 직업을 입력받아서 FOR LOOP를 이용하는 CURSOR 
accept p_job prompt '직업을 입력하세요 :'

declare 
    v_name varchar2(30);
    v_mileage  number(10);
    cursor member_cur  is
        select mem_name, mem_mileage 
            from member
            where mem_job = '&p_job' 
            order by mem_name asc;
begin   
    for mem_rec in member_cur loop    
        dbms_output.put_line( member_cur%rowcount || '번째 ' 
        || mem_rec.mem_name || ', ' || mem_rec.mem_mileage );
    end loop;
end;
/

-- Subquery를 이용한 FOR LOOP
begin   
    for mem_rec in (select mem_id, mem_name, mem_mileage
        from member order by mem_name asc)  loop    
            dbms_output.put_line( mem_rec.mem_id || ', ' 
            ||  mem_rec.mem_name || ', ' 
            || mem_rec.mem_mileage );  
    end loop;
end;
/

저장 프로시져 (Stored Procedure)

: DB 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합을 이야기 한다.

수행될 때 문법을 검사하고 컴파일 되며 컴파일된 버전은 서버에 저장된다.

어떤 클라이언트든 쓸 수 있다는 장점이 있다. => 캡슐화

- 장점

클라이언트든 쓸 수 있음 (캡슐화)

view와 동일한 개념으로 데이터베이스 내부를 구조 보안함

서버 보호

자료 무결성 권한을 구현

Query 속도가 향상됨

Network Traffic 감소

create [ or replace ] procedure procedure_name
    [( argument  [mode] [ {:= | default} expression] 
    , …)]  
    { is | as }
begin
    pl/sql_block; 
end;

-- Stored Procedure
-- 상품 코드를 매게변수(parameter)로 하여 재고수량 add

-- 만들거나 수정시키는 procedure, 프로시져 이름 : usp_prod_totalstock_update (총 재고수량 업데이트 프로그램)
create or replace procedure usp_prod_totalstock_update
    (v_prod_id in prod.prod_id%type, -- in은 외부로부터 데이터를 받는다는 뜻
    v_qty in prod.prod_totalstock%type) -- prod_totalstock와 똑같은 타입으로 받아 v_qty 이름으로 만듦
is
begin
    update prod
        set prod_totalstock = prod_totalstock + v_qty -- 현재 재고에다가 지금 들어온 수량을 더하여 현재 재고에 넣음
    where prod_id = v_prod_id;
    dbms_output.put_line('정상적으로 업데이트 되었습니다.');
    commit;
    
exception -- 에러가 났을 때
    when others then -- others 오라클 에서 에러가 났을 경우 (others)
        dbms_output.put_line('예외 발생: ' || sqlerrm);
        rollback;
end;
/

위와 같이 실행할 경우 제대로 저장되었는지 확인할 수 없다. => exec 또는 execute를 사용한다.

exec 또는 execute  procedule_name ( 매개변수, …);

-- execute 사용
-- 조회
select prod_id, prod_totalstock
    from prod
    where prod_id = 'P102000006';

-- 업데이트 후 조회하면 500개가 상승된다.
execute usp_prod_totalstock_update('P102000006', 500);

create or replace procedure usp_MemberID
    (p_mem_id in member.mem_id%type,
    p_mem_name out member.mem_name%type, -- 밖으로 mem_name을 보냄
    p_mem_like out member.mem_like%type)
is
begin
    select mem_name, mem_like
    into p_mem_name, p_mem_like
    from member
    where mem_id = p_mem_id;
end;

var mem_name varchar2(20);
var mem_like varchar2(20);
execute usp_memberID('a001', :mem_name, :mem_like);
print mem_name;
print mem_like;

create or replace procedure usp_memberCartTop
    (p_year in varchar2,
    p_amt out number, -- 금액
    p_mem_name out member.mem_name%type)
is
    v_year varchar2(5); -- 프로시저는 is와 begin 사이에서 변수선언을 함
begin
    v_year := (p_year || '%'); -- ex) 2020%
    select mem_name, mem_amt into p_mem_name, p_amt
    from (select mem_name, sum(prod_price * cart_qty) mem_amt
        from member, cart, prod
        where cart_no like v_year -- cart_no(구매번호)가 2020% 인것들 조회 -> 2020년에 결제한 내역 조회
        and cart_member = mem_id
        and cart_prod = prod_id
        group by mem_name
        order by sum(prod_price * cart_qty) desc)
    where rownum <= 1;
end;
/

var send_member varchar2;
var send_amt number;
exec usp_membercarttop('2020', :send_amt, :send_member);
print send_member;
print send_amt;

create or replace procedure usp_prod_info
    (v_prod_id  in prod.prod_id%type,
    p_yyyymm    in varchar2,
    p_inqty     out number,
    p_outqty    out number)
is
    v_yyyymm varchar2(7);
begin
    v_yyyymm := (p_yyyymm || '%');
    
    select nvl(sum(buy_qty), 0) into p_inqty
        from buyprod
        where to_char(buy_date, 'YYYYMMDD') like v_yyyymm and buy_prod = v_prod_id
        group by buy_prod;
----
    select nvl(sum(cart_qty), 0) into p_outqty
        from cart
        where cart_no like v_yyyymm and cart_prod = v_prod_id
        group by cart_prod;
end;

-- 실행
var send_inqty number;
var send_outqty number;
exec usp_prod_info('P101000001', '202004', :send_inqty, :send_outqty);
print send_inqty;
print send_outqty;

-- 환경설정 변수 변경. 매 세션마다 초기화 되므로 매번 재설정 해야 함
set serveroutput on

declare
    send_inqty number;
    send_outqty number;
    
begin
    usp_prod_info('P101000001', '202004', send_inqty, send_outqty);
    dbms_output.put_line('*입고량 => ' || send_inqty);
    dbms_output.put_line('*출고량 => ' || send_outqty);
end;

User Defined 함수

Function은 Procedure가 갖는 장점과 동일하지만 반환값이 있어 오라클 내장함수처럼 사용할 수 있다.

자주 반복되는 subquery, 복잡한 계산식을 사용자가 만들어 일반 함수처럼 사용할 수 있다.

반환할 데이터 타입을 return 으로 선언해야 하며 실행영역에서 return 문이 있어야 한다.

create [ or replace ] function function_name
    [( argument  [mode] [ {:= | default} expression] 
    , …)]
return data_type
{ is | as }
begin
    pl/sql_block; 
end;

-- function
-- 회원 아이디를 받으면 해당 이름을 리턴하는 함수 만들기
create or replace function fn_memName
    (p_mem_id in varchar2)
    return varchar2 -- 필수! 타입으로 줘야함.
is
    r_name varchar2(30);
begin
    select mem_name into r_name from member
        where mem_id = p_mem_id;
    return r_name;
exception
    when others then
        dbms_output.put_line('예외 발생: ' || sqlerrm);
        return null;
end;
/

-- fn_memName 실행 테스트 방법1
var m_name varchar2;
execute : m_name := fn_memName('a001');
print m_name;

-- fn_memName 실행 테스트 방법2
-- 실제 함수처럼 sql 구문에서 실행
select cart_no, cart_prod, cart_member, fn_memName(cart_member)
    from cart
    where cart_no = '2020040100001';

-- fn_memName 실행 테스트 방법3
declare
    m_name varchar2(20);
begin
    m_name := fn_memName('a001');
    dbms_output.put_line('* 이름 => ' || m_name);
end;

-- 년도 및 상품코드를 입력 받으면 해당년도의 평균 판매 수량을 반환하는 함수
create or replace function fn_prodAvgQty
    (p_year in number default (extract(year from sysdate)),
    p_prod_id in varchar2)
return number
is
    r_qty number(10);
    v_year varchar2(5) := to_char(p_year) || '%';
begin
    select nvl(avg(cart_qty), 0) into r_qty from cart
        where cart_prod = p_prod_id and cart_no like v_year;
    return r_qty;
exception
    when others then
        dbms_output.put_line('예외 발생 : ' || sqlerrm);
        return 0;
end;
/

-- fn_prodAvgQty 실행 테스트 방법1
var qty number;
exec : qty := fn_prodAvgQty(2020, 'P201000018');
print qty;

-- fn_prodAvgQty 실행 테스트 방법2
-- 실제 함수처럼 sql 구문에서 실행
select prod_id, prod_name,
    fn_prodAvgQty(2004, prod_id) "2004년 평균 판매수량",
    fn_prodAvgQty(2020, prod_id) "2020년 평균 판매수량",
    fn_prodAvgQty(2024, prod_id) "2024년 평균 판매수량"
    from prod;

-- fn_prodAvgQty 실행 테스트 방법3
declare
m_year varchar2(5);
begin
    m_year := '2020';
    dbms_output.put_line('*연도 => ' || m_year);
end;

PL/SQL (Procedural Language/SQL)

: 서버에서 절차적인 처리를 위해 표준 SQL을 확장한 절차적 언어

블록(block) 구조로 여러 SQL문을 한번에 실행할 수 있다.

Anonymous block은 하단의 변수선언, if, case, while 문의 예제를 확인하면 된다.

변수 선언

-- 환경설정 변수 변경. 매 세션마다 초기화 되므로 매번 재설정 해야 함
set serveroutput on

declare
    v_i     number(9,2) := 0;
    v_name  varchar2(20);
    c_pi    constant number(8,6) := 93.141592;
    v_flag  boolean not null := true;
    v_date  varchar2(10) := to_char(sysdate, 'YYYY-MM-DD');
begin
    v_name := '홍길동';
    dbms_output.enable;
    dbms_output.put_line('v_i:' || v_i);
    dbms_output.put_line('v_name:' || v_name);
    dbms_output.put_line('c_pi:' || c_pi);
    dbms_output.put_line('v_date:' || v_date);
end;
/

IF 문

declare
    v_num number := 37;
begin
    dbms_output.enable;
    
    if mod(v_num, 2) = 0 then
        dbms_output.put_line(v_num || '는 짝수');
    else
        dbms_output.put_line(v_num || '는 홀수');
    end if;
end;
/

declare
    v_num   number := 67;
begin
    dbms_output.enable;
    if v_num > 90 then
        dbms_output.put_line('수');
    elsif v_num > 80 then
        dbms_output.put_line('우');
    elsif v_num > 70 then
        dbms_output.put_line('미');
    else
        dbms_output.put_line('분발합시다.');
    end if;
end;
/

declare
-- 테이블.컬럼명%type : 테이블의 컬럼의 타입과 동일하게 지정
    v_avg_sale  prod.prod_sale%type; -- number(10, 0)
    v_sale  number := 500000;
begin
    dbms_output.enable;
    
    select avg(prod_sale) into v_avg_sale from prod; -- avg(prod_sale)의 값을 v_avg_sale에 값을 넣음
    
    if  v_sale < v_avg_sale then
        dbms_output.put_line('평균 단가가 500000 초과입니다.');
    else
        dbms_output.put_line('평균 단가가 500000 이하입니다.');
    end if;
end;
/

declare
    v_mem_id    varchar2(10) := 'e001';
    v_mem_mileage   member.mem_mileage%type;
    v_mem_name  member.mem_name%type;
    v_mile  number := 5000;
begin
    dbms_output.enable;
    
    select mem_mileage, mem_name
    into v_mem_mileage, v_mem_name
    from member
    where mem_id = v_mem_id;
    
    if v_mile < v_mem_mileage then
        dbms_output.put_line('VIP 회원(' || v_mem_name || ', ' || v_mem_mileage || ')');
    else
        dbms_output.put_line('일반 회원(' || v_mem_name || ', ' || v_mem_mileage || ')');
    end if;
end;
/

CASE 문

declare
    v_num   number := 77;
begin
    v_num := trunc(v_num / 10);
    
    case v_num
        when 10 then
            dbms_output.put_line('수' || '(' || v_num || ')');
        when 9 then
            dbms_output.put_line('우' || '(' || v_num || ')');
        when 8 then
            dbms_output.put_line('미' || '(' || v_num || ')');
        when 7 then
            dbms_output.put_line('양' || '(' || v_num || ')');
        else
            dbms_output.put_line('분발합시다.');
    end case;
end;
/

WHILE 문

declare
    v_sum   number := 0;
    v_var   number := 1;
begin
    while v_var <= 10 loop
        v_sum := v_sum + v_var;
        v_var := v_var + 1;
    end loop;
    dbms_output.put_line('1부터 10까지의 합 = ' || v_sum);
end;
/

declare
    v_id    number := 1;
begin
    while v_id < 20 loop
        dbms_output.put_line(rpad('a', v_id, '*'));
        v_id := v_id + 2;
    end loop;
end;
/

-- lpad : 왼쪽에 블랭크를 부가하는 함수
select lpad('A', 2) from dual;
select lpad('A', 3, 'B') from dual;

-- rpad : 오른쪽에 블랭크를 부가하는 함수
select rpad('A', 2) from dual;
select rpad('A', 3, 'B') from dual;

declare
    v_id    number := 1;
    v_id2   number := 10;
begin
    dbms_output.put_line('');
    while v_id < 20 loop
        dbms_output.put(rpad('a', v_id2, ' '));
        dbms_output.put_line(rpad('b', v_id, '*'));
        v_id := v_id + 2;
        v_id2 := v_id2 - 1;
    end loop;
end;
/

declare -- 선언문
    v_id    number := 1; -- v_id 숫자변수 선언후 초기값 1저장
    v_id2   number := 10; -- v_id2 숫자변수 선언후 초기값 10저장
begin -- PL/SQL 실행문 시작
    dbms_output.put_line(''); -- dbms_output.put_line 초기인쇄 (에러방지)
    while v_id < 20 loop -- v_id가 초기값 1로 시작해서 20보다 작은 동안 while문을 수행한다.
        dbms_output.put(rpad(' ', v_id2, ' ')); -- v_id2 크기의 공간에서 왼쪽 ' '의 1자리를 제외한 자리수 만큼 오른쪽으로 공백(' ')을 채운다음 output한다.
        dbms_output.put_line(rpad('*', v_id, '*')); -- v_id 크기의 공간에서 왼쪽 '*'의 1자리를 제외한 자리수 만큼 오른쪽으로 공백('*')을 채운다음 output한다.
        v_id := v_id + 2; -- v_id의 초기값은 1이고 2씩 증가한다.
        v_id2 := v_id2 - 1; -- v_id2의 초기값은 10이고 1씩 감소한다.
    end loop; --while 종료문
end; -- PL/SQL 실행문 종료
/

-- 환경설정 변수 변경. 매 세션마다 초기화 되므로 매번 재설정 해야 함
set serveroutput on

-- 구구단 만들기
declare
    v_i number := 2;
    v_j number := 1;
begin
    while v_i < 10 loop
        dbms_output.put_line('===========');
        dbms_output.put_line(v_i || ' 단 ');
        dbms_output.put_line('===========');
        
        v_j := 1;
        
        while v_j < 10 loop
            dbms_output.put_line(v_i || ' X ' || v_j || ' = ' || v_i*v_j);
            
            v_j := v_j +1;
        end loop;
        
        v_i := v_i+1;
    end loop;
end;
/

커서

declare
    v_nm varchar2(20);
begin
    select lprod_nm into v_nm from lprod where lprod_gu = 'P201';
    if sql%found then
        dbms_output.put_line('받은 값 = ' || v_nm);
        dbms_output.put_line('행 수 = ' || sql%rowcount);
    end if;
end;
/

lprod의 lprod_id가 9기까지 있어야 아래 코드를 실행할 수 있다.

declare
    v_add   number(5) := 1000;
    v_code  char(4) := ' ';
    v_id    number(5);
begin
    select max(lprod_id) into v_id from lprod;
    while v_add <= 1005 loop
        v_add := v_add + 1;
        v_id := v_id + 1;
        v_code := 'T' || substr(to_char(v_add), -3);
        insert into lprod(lprod_id, lprod_gu, lprod_nm)
            values(v_id, v_code, 'loop test');
        if sql%found then
            dbms_output.put_line('신규코드' || v_code || '가 추가 되었음');
        end if;
    end loop;
end;
/

begin
    select max(lprod_id) into v_id from lprod;
    
    while v_add >= 1002 loop
        delete from lprod where lprod_id = v_id; -- v_id : 15, 14, 13, 12, 11,10
        
        v_add := v_add-1;
        v_id := v_id-1;
        v_code := 'T' || substr(to_char(v_add), -3);
        
        if sql%found then
            dbms_output.put_line('기존코드' || v_code || '가 삭제되었습니다.');
        end if;
    end loop;
end;
/

GOTO 문

: 실행처리를 임의의 지정으로 이동하는 문

declare
    v_sum   int := 0;
    v_var   int := 1;
    
begin
    <<mylabel>>
    v_sum := v_sum + v_var;
    v_var := v_var + 1;
    if v_var <= 10 then
        goto mylabel;
    end if;
    dbms_output.put_line(v_sum);
    dbms_output.put_line(v_var);
end;
/

LOOP 문

: 조건이 없는 단순한 무한 반복문

exit 문을 사용하여 반복문을 빠져나가게 해야 한다.

declare
    v_sum   number := 0;
    v_var   number := 1;
begin
    loop
        v_sum := v_sum + v_var;
        v_var := v_var + 1;
        if v_var > 10 then
            exit;
        end if;
    end loop;
    dbms_output.put_line('1부터 10 까지의 합 = ' || v_sum);
end;
/

EXIT 문

: 반복문을 빠져나가는 문.

when을 사용하여 조건에 따라서 빠져나간다.

declare
    v_sum   number := 0;
    v_var   number := 1;
begin
    loop
        v_sum := v_sum + v_var;
        v_var := v_var + 1;
        exit when v_var > 10;
    end loop;
    dbms_output.put_line('1부터 10까지의 합 = ' || v_sum);
end;
/

FOR 문

-- index는 1씩 증가
-- reverse 가 사용될 경우 1씩 감소
-- in 다음에 select 문, cursor 문이 올 수 있음

for index in [reverse] 최소값..최대값 loop
	처리문장들;
end loop;

begin
    for i in 1..10 loop
        dbms_output.put_line('i = ' || i);
    end loop;
end;
/

composite 데이터 타입

형태에 따라 콜렉션을 사용할 지 레코드를 사용할지 나뉜다.

콜렉션은 배열 형태의 타입을 말하며 varray 와 table로 구분된다.

varray : 고정길이를 가진 배열로, 첨자를 이용해 접근한다.

table : 가변길이를 가진 배열로, 첨자 또는 키로 접근한다.

레코드는 테이블 형태의 타입을 말하며 record나 %rowtype로 구분된다.

record : 여러 데이터 형이 조합된 구조체 형식이다.

%rowtype : 기존 테이블의 이름과 타입을 사용한다.

1. varray 사용

-- varray 의 사용
type 타입명 is {varray | varying array} (사이즈) of 데이터 타입 [not null];

declare
    type starcraft is varray(20) of varchar2(10);
    v_star starcraft;
begin
    v_star := starcraft('Terran', 'Protos');
    v_star.extend;
    v_star(3) := 'Zerg';
    dbms_output.put_line('스타크래프트 종족 : ' || v_star.count);
    for i in v_star.first..v_star.last loop
        dbms_output.put_line(i || '번째 종족 : ' || v_star(i));
    end loop;
end;
/

2. table 사용

-- table의 사용 : 가변적이기에 사이즈를 지정하지 않음.
-- 첨자 기반
type 타입명 is table of 데이터 타입 [not null];

-- 키 기반
type 타입명 is table of 데이터 타입 [not null]
index by [pls_integer | binary_integer | varchar2(n)];

declare
    type lprod_nm_table is table of varchar2(40)
        index by pls_integer;
    t_lprod_nm lprod_nm_table; -- t_lprod_nm 이름으로 사용할 수 있게 재정의함. 사용하기 위해서는 필수
begin
    for l_list in (select lprod_id, lprod_nm from lprod) loop -- l_list는 위에 선언하지 않았지만 for 안에 있을 때는 자동적으로 정의해줌.
        t_lprod_nm(l_list.lprod_id) := l_list.lprod_nm; -- lprod_id를 키로 lprod_nm를 value로 매칭함.
        dbms_output.put_line(l_list.lprod_id || '=' || l_list.lprod_nm);
    end loop;
    
    dbms_output.put_line('갯수 = ' || t_lprod_nm.count);
    for i in t_lprod_nm.first..t_lprod_nm.last loop
        if t_lprod_nm.exists(i) then -- 첫번째부터 마지막 까지 데이터가 있는지 확인
            dbms_output.put_line (i || ' ' || t_lprod_nm(i));
        end if;
    end loop;
end;
/

exception 처리

: error가 발생하면 exception을 발생시켜 해당 블록을 중지하고 예외처리 부분으로 이동하게 한다.

- 예외 유형

1. 정의된 Oracle Server Error

2. 정의 되지 않은 Oracle Server Error

3. 사용자 정의 Error

1. 정의된 Oracle Server Error

declare
    v_name  varchar2(20);
begin
    select lprod_nm into v_name from lprod where lprod_gu = 'P201';
    dbms_output.put_line('분류명 = ' || v_name);
exception
    when no_data_found then
        dbms_output.put_line('해당 정보가 없습니다.');
    when too_many_rows then
        dbms_output.put_line('한개 이상의 값이 나왔습니다.');
    when others then
        dbms_output.put_line('기타 에러 : ' || sqlerrm);
end;
/

2. 정의 되지 않은 Oracle Server Error

- 순서

2-1. 선언부에 예외의 이름을 지정한다.

2-2. pragma를 기술하고 exception_init으로 예외 이름과 에러번호를 등혹한다. (pragma는 실행될 때 처리되지 않는 명령문임을 알려주는 예약어)

2-3. exception 영역에 해당 예외 처리를 한다.

-- 정의되지 않은 예외
declare
    exp_reference exception;
    pragma exception_init(exp_reference, -2292); -- 예외 이름 exp_reference
begin
    delete from lprod where lprod_gu = 'P101';
    dbms_output.put_line('분류 삭제');
exception
    when exp_reference then
        dbms_output.put_line('자식 테이블에 데이터가 존재하여 삭제 불가 : ' || sqlerrm);
    when others then
        dbms_output.put_line(sqlcode || ' ' || sqlerrm);
end;
/

3. 사용자 정의 Error

- 순서

3-1. 선언부에 예외 이름을 지정한다.

3-2. 실행부에서 raise 문장을 사용한다.

3-3. exception 영역에 해당 예외 처리를 한다.

-- 사용자 정의 예외인 경우
accept p_lgu prompt '등록하려는 분류코드 입력 : '
declare
    exp_lprod_gu exception;
    v_lgu varchar2(10) := upper('&p_lgu');
begin
    if v_lgu in ('P101', 'P102', 'P201', 'P202' ) then raise exp_lprod_gu;
    end if;
    dbms_output.put_line(v_lgu || '는 등록 가능');
exception
    when exp_lprod_gu then
        dbms_outpur.put_line(v_lgu || '는 이미 등록된 코드 입니다.');
end;
/