프로젝트기간 수업 D+2

PL/SQL

자주 사용하는 명령을 PL/SQL 구문을 이용하여 데이터베이스에 저장하고 필요할 때 호출하여 사용하는 방법

SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR) 등을 지원

 

* PL/SQL은 블록(BLOCK) 구조로 3부분으로 나눌 수 있음

- 한 문장이 종료할 때마다 세미콜론(;)을 사용함

DECLARE
	선언부 : 변수나 상수를 선언
BEGIN
	실행부 : SQL문, 제어문, 반복문, 커서
EXCEPTION
	예외 처리부
END;
/ --행에 /가 있어야 종료됨

 

'HELLO WORLD'를 출력하는 PL/SQL문 작성

set SERVEROUTPUT ON;

BEGIN
	DBMS_OUTPUT.PUT_LINE('HELLO WORLD'); --스크립트 출력할 수 있도록 만듦
END;

 

변수

구문	설명
identifier	변수의 이름
CONSTANT	변수의 값을 변경할 수 없도록 제약
datatype	자료형을 기술
NOT NULL	값을 반드시 포함하도록 하기 위해 변수를 제약
Expression	Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

{}와 같은 블록으로 묶지 않아도 됨

PL/SQL에서 변수를 선언할 때 사용하는 자료형은 SQL에서 사용하던 자료형과 거의 유사함.

* 숫자 : NUMBER

* 문자 : VARCHAR2

 

 

1) 변수 값 지정

변수 이름 := 값

변수 선언 및 할당을 하고 그 변수 값을 출력함

declare
  vempno number(4); --vempno 라는 변수는 숫자형임을 선언
  vename varchar2(10); --vename 라는 변수는 문자형임을 선언
  
begin
  vempno := 7788;
  vename := 'SCOTT';
  DBMS_OUTPUT.PUT_LINE('사 번 / 이 름');
  DBMS_OUTPUT.PUT_LINE('------------');
  DBMS_OUTPUT.PUT_LINE(vempno || '/' || vename); --vempno 와 vename 사이에 /를 붙이기 위해서 ||를 사용함
end;

 

2) 스칼라 변수 / 레퍼런스 변수

스칼라 변수 : SQL에서의 자료형 지정과 거의 동일함

레퍼런스 변수 : %TYPE 속성과 %ROWTYPE 속성을 사용함

- %TYPE : 이전에 선언된 다른 변수 or 컬럼에 맞춰 변수를 선언하기 위해 사용함. 원래 컬럼의 자료형과 크기를 그대로 사용

-%ROWTYPE : 행 단위로 참조함. 특정 테이블의 칼럼 개수와 데이터 형식을 몰라도 지정할 수 있음

 

3)PL/SQL에서 SQL문장

PL/SQL의 SELECT문은 INTO절이 필요함. INTO 절에는 데이터를 저장할 변수를 기술함

SELECT절에 있는 칼럼은 INTO절에 있는 변수와 1:1 대응을 하기에 개수, 데이터 형, 길이가 일치해야함

SELECT select_list
INTO {variable_name1 [,variable_name2,...] | record_name}
FROM table_name
WHERE condition;

구문	설명
select_list	열의 목록이며 행 함수, 그룹 함수, 표현식을 기술할 수 있음
variable_name	읽어들인 값을 저장하기 위한 스칼라 변수
record_name	읽어들인 값을 저장하기 위한 RECORD 변수
Condition	변수와 상수를 포함하며 열명, 표현식, 상수, 비교 연산자로 구성되며 오직 하나의 값을 RETURN 할 수 있는 조건이어야 함

declare
--다른 테이블의 컬럼을 가져올 땐 %type을 사용함
  vempno emp.empno%type; --데이터 타입을 변수에 저장
  vename emp.ename%type; --데이터 타입을 변수에 저장
  
begin  
  DBMS_OUTPUT.PUT_LINE('사 번 / 이 름');
  DBMS_OUTPUT.PUT_LINE('------------');
  select empno,ename into vempno,vename --각 컬럼을 변수에 저장함
  from emp
  where ename='SCOTT'; --이름이 'SCOTT'인 사람
  DBMS_OUTPUT.PUT_LINE(vempno || '/' || vename); --'SCOTT'의 사번과 이름 출력
end;

 

4) PL/SQL TABLE TYPE

PL/SQL TABLE TYPE : 한 행에 대하여 배열처럼 엑세스하기 위해 기본키를 사용함. 배열과 유사함

여러 행을 가져오기 위해서 사용함

TYPE table_type_name IS TABLE OF
	{ column_type | variable%TYPE | table.column%TYPE} {NOT NULL}
    [INDEX BY BINARY_INTEGER};
identifier table_type_name;

구문	설명
table_type_name	테이블 형의 이름
column_type	VARCHAR2, DATE, NUMBER와 같은 스칼라 데이터형
identifier	전체 PL/SQL 테이블을 나타내는 식별자 이름

 

declare
  type ename_table_type is table of emp.ename%type
  index by binary_integer;
  type job_table_type is table of emp.job%type
  index by binary_integer;
  ename_table ename_table_type; --ename에 있는 데이터를 ename_table에 저장
  job_table job_table_type; --job에 있는 데이터를 job_table에 저장
  
  i binary_integer := 0;
  
begin  
  for k in (select ename, job from emp) loop
    i := i + 1;
    ename_table(i) := k.ename;
    job_table(i) := k.job;
  end loop;
  DBMS_OUTPUT.PUT_LINE(rpad('사번',7) || '/' ||rpad('이름',9));
  DBMS_OUTPUT.PUT_LINE('-------------------------------------');
  for j in 1..i loop
    DBMS_OUTPUT.PUT_LINE(rpad(ename_table(j),7) || '/' || rpad(job_table(j),9));
  end loop;
end;

 

5) PL/SQL RECORD TYPE

Record Type : 자바빈과 유사함. 필드(ITEM)들의 집합을 하나의 논리적 단위로 처리할 수 있게 해줌. 테이블의 행을 읽어올 때 편리함

declare
  type emp_record_type is record( --테이블에 꺼내온 데이터를 record_type에 저장(자바빈과 유사)
    v_empno emp.empno%type,
    v_ename emp.ename%type,
    v_job emp.job%type,
    v_deptno emp.deptno%type
  );
  
  emp_record emp_record_type;
begin
  select empno, ename,job, deptno into emp_record from emp
  where ename=upper('SCOTT');
  
  DBMS_OUTPUT.PUT_LINE('사원번호 :' || to_char(emp_record.v_empno));
  DBMS_OUTPUT.PUT_LINE('이   름 :' || emp_record.v_ename);
  DBMS_OUTPUT.PUT_LINE('담당업무 :' || emp_record.v_job);
  DBMS_OUTPUT.PUT_LINE('부서번호 :' || to_char(emp_record.v_deptno));
end;

 

제어문

1) IF ~ THEN ~ END IF

특정 조건을 만족하면 어떤 처리를 하고, 그렇지 않으면 아무 처리도 하지 않음

declare
  vemp emp%rowtype; --한 행의 전체를 vemp에 저장
  annsal number(7,2);
  
begin
  DBMS_OUTPUT.PUT_LINE('사번 / 이름 / 연봉');
  DBMS_OUTPUT.PUT_LINE('-----------------');
  select * into vemp from emp --'KING'이라는 사원에 대한 모든 정보 가져오기
  where ename = 'KING';
  
  if(vemp.comm is null) then --vemp의 comm이라는 데이터가 0이라면 null로 채우기
    vemp.comm := 0;
  end if;
  annsal := vemp.sal*12 + vemp.comm; --연봉 구하기
  DBMS_OUTPUT.PUT_LINE('사원번호:'||vemp.empno||'/사원명:'||vemp.ename||'/연봉:'||to_char(annsal,'$999,999'));
end;

 

2) IF ~ THEN ~ ELSE ~ END IF

조건을 만족할 때의 처리와 그렇지 않을 때의 처리. 두 가지 처리문 중에서 한 개를 선택해야 할 경우 사용

declare
  vemp emp%rowtype;
  annsal number(7,2);
begin
  DBMS_OUTPUT.PUT_LINE('사번 / 이름 / 연봉');
  DBMS_OUTPUT.PUT_LINE('-----------------');
  select * into vemp from emp
  where ename = 'SMITH';
  
  if(vemp.comm is null) then --comm이 null이라면 *12
    annsal := vemp.sal*12;
  else --comm이 null이 아니라면 *12 + comm
    annsal := vemp.sal*12 + vemp.comm;
  end if;
  
  DBMS_OUTPUT.PUT_LINE(vemp.empno||'/'||vemp.ename||'/'||annsal);
end;

 

 

3) IF ~ THEN ~ ELSIF ~ ELSE ~ END IF

여러 개 조건에 따라 처리도 여러 개일 대 사용하는 다중 IF문

 

declare
  vemp emp%rowtype;
  vdname dept.dname%type;
begin
  DBMS_OUTPUT.PUT_LINE('사번 / 이름 / 부서번호 / 부서명');
  DBMS_OUTPUT.PUT_LINE('-----------------------------');
  select * into vemp from emp
  where empno = 7934;
  
  if(vemp.deptno = 10) then
    vdname := 'ACCOUNTING';
  elsif(vemp.deptno = 20) then
    vdname := 'RESEARCH';
  elsif(vemp.deptno = 30) then
    vdname := 'SALES';    
  elsif(vemp.deptno = 40) then
    vdname := 'OPERATIONS';    
  end if;
  
  DBMS_OUTPUT.PUT_LINE(vemp.empno||'/'||vemp.ename||'/'||rpad(vemp.deptno,8)||'/'||vdname);
end;

 

반복문

* BASIC LOOP 문 : 조건 없이 반복 작업을 제공하기 위함

* FOR LOOP문 : 기본으로 작업의 반복 제어를 제공

* WHILE LOOP문 : 조건을 기본으로 작업의 반복 제어를 제공

* EXIT문 : LOOP를 종료하기 위함

 

1) BASIC LOOP문

실행 상의 흐름이 END LOOP에 도달할 때 마다 그와 짝을 이루는 LOOP문으로 제어가 되돌아감.

여기서 빠져나가기 위해 EXIT문 사용

--별 만들기
declare
  v_cnt number := 1;
  v_str varchar2(10) := null;
begin
  loop
    v_str := v_str||'*'; --기존 null값에 '*' 추가
    v_cnt := v_cnt+1; --cnt에 +1 
    DBMS_OUTPUT.PUT_LINE(v_str);
    if v_cnt > 5 then --반복문을 빠져나오기 위해 조건문 걸기
      exit;
    end if;
  end loop;
end;

 

2) FOR LOOP문

반복되는 횟수가 정해진 반복문을 처리함

이 반복문에서 사용되는 인덱스는 정수로 자동 선언되므로 따로 선언할 필요X

declare
  vdept dept%rowtype;
begin
  DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
  DBMS_OUTPUT.PUT_LINE('------------------------');
  for cnt in 1..4 loop --1부터 4까지 반복
    select * into vdept from dept
    where deptno = 10*cnt;
    
    DBMS_OUTPUT.PUT_LINE(vdept.deptno||'/'||vdept.dname||'/'||vdept.loc);
  end loop;
end;

 

3) WHILE LOOP문

제어 조건이 TRUE인 동안만 문장을 반복함. 

조건은 반복이 시작될 대 체크하게 됨

조건이 FALSE면 반복문장을 탈출함

--별 만들기
declare
  v_cnt number := 1;
  v_str varchar2(10) := null;
begin
  while v_cnt <= 5 loop --while문 사용
    v_str:=v_str||'*';
    DBMS_OUTPUT.PUT_LINE(v_str);
    v_cnt:=v_cnt+1;
  end loop;
end;

 

4) 커서

처리 결과가 여러 개의 행으로 구해지는 SELECT문을 처리하기 위함

 

* 커서의 상태

속성	의미
%NOTFOUND	커서 영역의 자료가 모두 FETCH 되었다면 TRUE
%FOUND	커서 영역에 FETCH 되지 않은 자료가 있다면 TRUE
%ISOPEN	커서가 OPEN된 상태이면 TRUE
%ROWCOUNT	커서가 얻어 온 레코드의 개수

 

DECLARE
	CURSOR cursor_name IS statement;	--커서 선언
BEGIN
    OPEN cursor_name; 	--커서 열기
    FECTCH cur_name INTO variable_name; 
    CLOSE cursor_name;	--커서 닫기
END;

FETCH문은 전체 결과에서 행 단위로 데이터를 읽어들임. => 현재 행에 대한 정보를 얻어와서 변수에 저장한 후, 다음 행으로 이동함

declare
  vdept dept%rowtype;
  cursor c1 --dept의 전체 데이터를 c1에 저장함
  is
  select * from dept;
begin
  DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
  DBMS_OUTPUT.PUT_LINE('------------------------');
  open c1; --커서가 오픈되어 데이터를 사용할 수 있음
  loop
    fetch c1 into vdept.deptno, vdept.dname, vdept.loc;
    exit when c1%notfound; --더이상 꺼내올 데이터가 없으면, 반복문 빠져나감
    DBMS_OUTPUT.PUT_LINE(vdept.deptno||'/'||vdept.dname||'/'||vdept.loc);
  end loop;
end;

 

5) 커서와 FOR LOOP(명시적으로 커서에서 행을 처리함)

LOOP에서 각 반복마다 커서를 열고 행을 인출(FETCH)하고 모든 행이 처리되면 자동으로 커서가 종료됨

declare
  vdept dept%rowtype;
  cursor f1 --file 데이터를 f1에 저장
  is
  select * from file_t;
begin
  DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
  DBMS_OUTPUT.PUT_LINE('------------------------');
  open c1; --커서가 오픈되어 데이터를 사용할 수 있음
  loop
    fetch c1 into vdept.deptno, vdept.dname, vdept.loc;
    exit when c1%notfound; --더이상 꺼내올 데이터가 없으면, 반복문 빠져나감
    DBMS_OUTPUT.PUT_LINE(vdept.deptno||'/'||vdept.dname||'/'||vdept.loc);
  end loop;
end;

 

 

- PL/SQL의 함수, 프로시저, 트리거 공부해보기 => 내일 공부함~