PL/SQL

1. PL/SQL작성법

복잡한 구조의 질의를 하려고, 관련있는 동작을 연속으로 실행하여
결과를 변수에 담거나 출력할 수 있다

-3종류 구문이 있음
DECLARE //변수 선언
BEGIN //실행하는 부분
EXCEPTION //예외발생 시 실행하는 부분

example)

declare //변수선언
   vename employees.last_name%type;
   vempno employees.employee_id%type;
   vjob_id employees.job_id%type;
   vdeptname departments.department_name%type;
begin //현재 실행하고싶은 부분
   select employee_id, last_name, job_id, department_name
   into vempno, vename, vjob_id, vdeptname
   from employees e, departments d
   where e.department_id=d.department_id
   and employee_id=145;
   DBMS_OUTPUT.PUT_LINE('emp no:' ||  vempno);
   DBMS_OUTPUT.PUT_LINE('name:' ||  vename);
   DBMS_OUTPUT.PUT_LINE('job:' ||  vjob_id);
   DBMS_OUTPUT.PUT_LINE('department:' ||  vdeptname);
end;

-제어문(프로그램의 흐름을 제어) & 선택문(조건에 따른 실행내용 선택)
1) if(조건) then ~ end if
example)

declare
   vscore number(3);
begin
   vscore := 50;
   if(vscore>=60)
   then DBMS_OUTPUT.PUT_LINE('합격');
   end if;
   end;

2) if(조건) then ~ else ~end if

declare
   vscore number(3);
begin
   vscore := 50;
   if(vscore>=60)
   then DBMS_OUTPUT.PUT_LINE('합격');
   else DBMS_OUTPUT.PUT_LINE('불합격');
   end if;
   end;
   /

3) if(조건1) then ~ elsif(조건2) ~ else~end if

declare
   x number(1);
begin
   x := 3;
   if(x=1)
   then DBMS_OUTPUT.PUT_LINE('하나');
   elsif(x=2) 
   then DBMS_OUTPUT.PUT_LINE('둘');
   elsif(x=3) 
   then DBMS_OUTPUT.PUT_LINE('셋');
   elsif(x=4) 
   then DBMS_OUTPUT.PUT_LINE('넷');
   else
   DBMS_OUTPUT.PUT_LINE('범위 밖의 값');
   end if;
   DBMS_OUTPUT.PUT_LINE('if밖');
   end;
   /

-반복문
1)loop
example)

declare
        x number :=1;
        y number :=0;
    begin
        loop
            y:=y+x;
            x:=x+1;
            if(x>100)
                then exit;
            end if;
        end loop;
        DBMS_OUTPUT.PUT_LINE(y);
    end;
    /

2)forloop
example)

declare
        dan number(1);
        x number:=1;
    begin
        dan:=&dan;        
        for x in 1..9 loop
            DBMS_OUTPUT.PUT_LINE(dan || ' * ' || x || ' = ' || dan*x);
        end loop; 
    end;
    / 

3) while loop
example)

declare
    x number:=1;
begin
    while x<10 loop
        DBMS_OUTPUT.PUT_LINE(x);
        x:=x+1;
    end loop;
end;

-테이블 타입(여러줄을 담을 수 있는 타입)
1)타입정의
type 타입명 is table of 테이블명.컬럼명%type
index by binary_integer;
2)정의한 타입으로 변수 선언
3)루프를 돌며 값을 하나씩 꺼내어 사용

example)

declare
    type ename_arr is table of employees.last_name%type
    index by binary_integer;
    type job_arr is table of employees.job_id%type
    index by binary_integer;
    
    names ename_arr;
    jobs job_arr;
    
    i binary_integer:=0; 
begin
    for k in(select last_name, job_id from employees) loop
        i:=i+1;
        names(i):=k.last_name;
        jobs(i):=k.job_id;
    end loop;
    
    for j in 1..i loop
        DBMS_OUTPUT.PUT_LINE(names(j) || ' / ' || jobs(j));
    end loop;
end;

-레코드 타입
%rowtype은 지정한 테이블의 모든 컬럼을 포함
record 타입은 지정한 테이블의 원하는 컴럼만 포함

타입 정의: type 타입명 is record(컬럼 리스트);
==>컬럼 리스트는 컬럼명 타입으로 정의

example)

declare
    type emp_record is record(
    vnum employees.employee_id%type,
    vname employees.last_name%type,
    vsalary employees.salary%type,
    vjob employees.job_id%type);
    
    vemp_rec emp_record;
begin
    select employee_id, last_name, salary, job_id
    into vemp_rec 
    from employees
    where employee_id=100;
    DBMS_OUTPUT.PUT_LINE(vemp_rec.vnum || ' / ' || 
    vemp_rec.vsalary || ' / ' || vemp_rec.vjob || ' / ' || 
    vemp_rec.vname);
end;
/

2. Procedure

(주의사항:
set serveroutput on;
위 문장을 실행해야 프로시져, 함수 등 안에 구현해둔 print문 확인가능)

procedure을 사용하는 목적

sql file에 작성해서 저장해놓은 여러 라인의 SQL문장을 block설정해서 실행할 수도 있지만
필요할때마다 or 프로그램 내 코드 사이에 호출해서 하기는 어렵다.

procedure로 저장해두면
(java에서 method 호출하듯이) procedure을 필요할때에 호출해서 사용할 수 있음.

실행방법

exec로 호출하되,
in, out parameter는 실행하는 환경에서 변수 선언 및 주어져야함.

example)

create or replace PROCEDURE PROC1 
(
  EMP_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
  emp_name out employees.last_name%type,
  emp_salary out employees.salary%type
) AS 
BEGIN
  select last_name, salary into emp_name, emp_salary 
  from employees 
  where employee_id=emp_id;
END PROC1;

실행 example)

var emp_name varchar2(25);
var emp_salary number;
exec proc1(206, :emp_name, :emp_salary); 
/*procedure_name(input 변수; :반환받을 변수1,:반환받을 변수2))*/
print emp_name;
print emp_salary;

여기서 변수 앞에 : 는 binding을 의미한다.
반환 받는 값을 외부에서도 사용 할 수 있도록 지정하는 목적.

procedure을 통해 얻을 수 있는 OUT paramter는 여러 행을 포함하는 형태를 가질 수 있다 <-- cursor을 사용하면 가능!

cursor는 JDBC의 ResultSet과 동일한 역할을 한다. 여러줄의 검색결과가 cursor객체에 들어가고, cursur를 통해 결과를 가져올 수 있다.

example)

create or replace PROCEDURE PRODUCT_LIST AS 
    cursor c is select * from product order by sno; 
    r product%rowtype; 
    /*product테이블의 한줄을 담을 수 있는 type */
BEGIN
  open c; 
  /*cursor open한 후 사용*/
    loop
        fetch c into r; 
        /*c 한개씩 fetch해서 r에 담아라*/
        exit when c%NOTFOUND; 
        /*더이상 c에서 읽을것이 없을때 exit*/
        DBMS_OUTPUT.PUT_LINE('sno:'||r.sno||'/name:'||
                r.name||'/company:'||r.company||'/p_date:'||r.p_date||
                '/price:'||r.price||'/quantity:'||r.quantity);
    end loop;
    close c; 
    /*cursor 사용완료 후 close*/
END PRODUCT_LIST;

실행 example)

exec product_list(); 

3. Function (함수)

함수를 사용하는 목적
SQL의 내장함수 (MIN, AVG, MAX, etc)와 같은 함수들대신
내가 직접 함수를 구현해서 사용하는 것.

실행방법
exec로 호출할수도 있고,
또는 SQL query문 안에서 함수자체를(반환되는 값을) 사용할 수 있음.

실행 example - query문 안에서 호출)

select make_name(first_name, last_name) name
from employees;

실행 example- exec로 호출)

exec get_product_by_name('신라면');

아래 예시와 같이 exception문구를 작성하여, 예외가 발생하여도 함수가 항상 값을 반환한다.

create or replace FUNCTION MAKE_GRADE 
(
  SCORE IN NUMBER 
) RETURN VARCHAR2 AS 
    grade varchar2(20):='잘못된 점수';
    score_range_exception exception;
BEGIN
    if(score>100 or score < 0)
        then raise score_range_exception;--예외를 강제로 발생
        else 
            if(score>=90) then grade:='A';
            elsif(score>=80) then grade:='B';
            elsif(score>=70) then grade:='C';
            elsif(score>=60) then grade:='D';
            else grade:='F';
            end if;
    end if;
    RETURN grade;
    exception 
        when score_range_exception 
            then DBMS_OUTPUT.PUT_LINE('score_range_exception 발생'); 
                    RETURN grade;
END MAKE_GRADE;

4. Trigger

TRIGGER 사용 목적
테이블에 사건 발생 전/후 상태를 저장/관리

실행방법
사건이 발생한 이력을 담을 테이블A를 생성하고
다른 테이블B에 사건이 발생하면 (insert, update, delete)
trigger내 구현문안에서 정의한대로 테이블A에 사건 이력이 담긴다.
trigger는 사건 before/after로 두종류가 있음

example)

CREATE OR REPLACE TRIGGER TRIGGER1 
after DELETE OR INSERT OR UPDATE /*after:사건(delete,insert,update) 발생 후 */
ON emp
for each row
BEGIN /*사건의 종류별 실행 사항 나열*/
/*여기 쓰이는 old(사건 전), new(사건 후)는 정해진 binding keyword*/
  if inserting then insert into emp_tmp 
    values(:new.employee_id, :new.last_name, :new.job_id,
    :new.salary, 'i'); /*마지막은 동작 종류*/
  elsif updating then insert into emp_tmp 
    values(:old.employee_id, :old.last_name, :old.job_id,
    :old.salary, 'u');
  elsif deleting then insert into emp_tmp 
    values(:old.employee_id, :old.last_name, :old.job_id,
    :old.salary, 'd');
  end if;
END;

실행 example)

create table emp_temp 
as select employee_id, last_name, job_id, salary
from employees where 1=0;

alter table emp_temp
add (action varchar2(1));

insert into emp values(206, 'aaa', 'bbb', 'emaila', '111.222.3333', 
sysdate, 'MK_MAN', 13000, null, 100, 20);

update emp set salary=15000 where employee_id=206;
delete emp where employee_id=206;

select * from emp_temp;