PL/SQL

PL/SQL이란?

SQL을 확장한 순차적 처리 언어
Procedural language extension to Structured Query Language

데이터베이스 질의어인 SQL과 일반 프로그래밍 언어의 특성을 결합한 언어

PL/SQL의 기본 구조

PL/SQL의 기본 단위는 블록(block)

• 선언부(declarative part)
  사용할 변수나 상수를 선언(선언부에만 변수와 상수 선언 가능)
• 실행부(executable part)
  실제 처리할 로직을 담당하는 부분
• 예외처리부(exception-building part)
  실행부에서 로직을 처리하던 중 발생할 수 있는 각종
  오류들에 대해 처리

변수와 상수

• 변수 선언

  emp_num1 number(9);
  grade char(2);
  emp_num2 integer := 1;
  

• 상수 선언

  nYear constant integer := 30; (o)
  nYear constant integer; (x)

• %TYPE

  %TYPE : 참조할 테이블에 있는 컬럼의 데이터 타입을 자동으로 가져옴.
  [구문형식]
  변수명 테이블명.컬럼명%TYPE
  
  nSal emp.sal%type;

콜렉션

varray

varray는 variable array의 약자로 고정 길이(fixed number)를 가진 배열

TYPE 타입명 IS { VARRAY | VARYING ARRAY } (크기) OF 요소데이터타입 [NOT NULL];

declare
type varray_test is varray(3) of integer;
varray1 varray_test; -- 위에서 선언한 varray_test 타입 변수
begin
varray1 := varray_test(10,20,30);
dbms_output.put_line(varray1(1));
end;

10

중첩 테이블

중첩 테이블은 varray와 흡사하지만 중첩 테이블은 선언 시에 전체 크기를 명시할 필요가 없음

TYPE 타입명 IS TABLE OF 요소데이터 타입 [NOT NULL];

declare
type nested_test is table of varchar2(10);
nested1 nested_test; -- 위에서 선언한 nested_test 타입 변수
begin
nested1 := nested_test('A','B','C','D');
dbms_output.put_line(nested1(2));
end;

B

Associative array(index-by table)

연관배열
키와 값의 쌍으로 구성

declare
 --키는 pls_integer형이며,값은 number형인 요소들로 구성
 --(주의)키에 자료형을 명시할 때 integer로 명시하면 오류 발생
 type assoc_array_num_type is table of number index by pls_integer;
 assoc1 assoc_array_num_type;
begin
 assoc1(3) := 33; -- 키는 3, 값은 33
 
 dbms_output.put_line(assoc1(3));
end;

33

레코드

테이블의 컬럼들이 서로 다른 유형의 데이터 타입으로 구성되듯이 레코드 역시 해당 필드(레코드에서는 요소란 말 대신 필드란 용어를 사용)들이 각기 다른 데이터 타입을 가질 수 있음

declare
  --type으로 선언한 레코드
  type record1 is record(deptno number not null:=50,
                         dname varchar2(14),
                         loc varchar2(13));
  rec1 record1;                       
begin
  --record1 레코드 변수 rec1의 dname 필드에 값 할당
  rec1.dname := 'RECORD';
  --record1 레코드 변수 rec1의 loc 필드에 값 할당
  rec1.loc := 'SEOUL';
  
  --rec1 레코드 값을 dept 테이블에 insert
  INSERT INTO dept VALUES rec1;

조건문

IF문

declare
 grade char(1);
begin
 grade := 'B';
 
 if grade = 'A' then
   dbms_output.put_line('Excellent');
 elsif grade = 'B' then
   dbms_output.put_line('Good');
 elsif grade = 'C' then
   dbms_output.put_line('Fair');
 elsif grade = 'D' then
   dbms_output.put_line('Poor');
 end if;
end;

Good

case문

declare
 grade char(1);
begin
 grade := 'C';
 
 case grade 
 when 'A' then
   dbms_output.put_line('Excellent');
 when 'B' then
   dbms_output.put_line('Good');
 when 'C'then
   dbms_output.put_line('Fair');
 when 'D' then
   dbms_output.put_line('Poor');
 else
   dbms_output.put_line('Not Found');
 end case;
end;

Fair

LOOP문

declare
 test_number integer;
 result_num integer;
begin
 test_number := 1;
 
 loop
  result_num := 2 * test_number;
  if result_num > 20 then
   exit; --블록 종료
  else
   dbms_output.put_line(result_num);
  end if; 
   test_number := test_number + 1;
 end loop;
end;

2
4
6
8
10
12
14
16
18
20

WHILE-LOOP문

declare
 test_number integer;
 result_num integer;
begin
 test_number := 1;
 result_num := 0;
 
 while result_num < 20 loop
  result_num := 2 * test_number;
  dbms_output.put_line(result_num);
  test_number := test_number + 1;
 end loop;
end;

2
4
6
8
10
12
14
16
18
20

FOR...LOOP문

declare
 test_number integer;
 result_num integer;
begin
 test_number := 1;
 result_num := 0;
 
 for test_number in 1..10 loop
  result_num := 2 * test_number;
  dbms_output.put_line(result_num);
 end loop;
end;

2
4
6
8
10
12
14
16
18
20

커서

SELECT 문장을 실행하면 결과가 추출되는데 그것을 결과집합
(RESULT SET)이라고 부르고 결과집합에 접근해서 정보를 얻으려면 커서(CURSOR)를 사용함

declare
 cursor emp_csr is
 SELECT empno
 FROM emp
 WHERE deptno = 10;
 
 emp_no emp.empno%type;
begin
 open emp_csr;
 
 loop
   fetch emp_csr into emp_no;
   --%notfound : 커서에서만 사용 가능한 속성
   --더 이상 패치(할당)할 로우가 없음을 의미
   exit when emp_csr%notfound;
   
   dbms_output.put_line(emp_no);
 end loop;
 
 close emp_csr;
end;

7782
7839
7934

PL/SQL 서브프로그램

PL/SQL 서브프로그램은 파라미터와 고유의 이름을 가진 PL/SQL 블록을 말하며, 데이터베이스 객체로 존재한다. 즉 필요할 때마다 호출해서 사용할 수 있다는 의미이다. 이러한 서브프로그램에는 내장 프로시저(stored procedure)와 함수(function)가 있다.

함수

[구문형식]
CREATE OR REPLACE FUNCTION 함수명(파라미터1 데이터타입,
파라미터2 데이터타입,
…)
RETURN 데이터타입 IS [AS]
변수 선언…;
BEGIN
처리내용…;
RETURN 리턴값;
END;

입력받은 값으로부터 10%의 세율을 얻는 함수

create or replace function tax(p_value in number)
  return number
is
begin
  return(p_value * 0.1);
end;

SELECT TAX(100) FROM dual;