오늘, 내일은 PL/SQL에 대해 알아본다.
PL/SQL이란 ?
Oracle's Procedural Language extension to SQL로, 오라클에서 SQL을 확장하여 사용하는 프로그래밍 언어이다.
절차적 프로그래밍 언어로 일반적인 SQL언어와는 차이가 있다.
SQL은 select, update, insert 등 데이터를 조작하는 쿼리만 가능하지만,
PL/SQL은 기본 sql외에도 declare(변수), exception(조건처리), loop(반복문) 등 절차적 프로그래밍이 가능한 것이 특징이다.
PL/SQL의 유형
- 익명블럭
- PL/SQL객체 - Procedure, Function, Trigger, Job, Scheduler
익명블럭의 구조
declare
--(선택) 변수 선언부
begin
--(필수) 실행부
exception
--(선택) 예외처리부
end;
/
PL/SQL 자료형과 변수
자료형 - sql의 자료형을 모두 포함하고, 확장된 형태의 자료형도 제공함.
- 기본 자료형
- 문자형 : varchar2, char, long, clob, ....
- 숫자형 : number, binary_integer, pls_integer
- 날짜형 : date, timestamp
- 논리형 : boolean(true, false, null)
- 복합 자료형
- record
- cursor
- collection : varray(배열) , nested table (자바 List), associative array(자바 Map)
변수 종류
- 스칼라 변수 : 단일 값 처리
- 참조 변수 : 다른 테이블의 컬럼 타입을 참조할 수 있음
- 상수 : 한번 값이 정해지면 바꿀 수 없음
일단 콘솔 출력이 가능하도록 오라클에서 설정하는 코드
set serveroutput on;
을 입력하고 Ctrl+Enter.
declare
n:=10+20;
user_name varchar2(20) := '홍길동';
n number;
today date := sysdate;
bool boolean;
age constant number :=20; --값변경불가능.
begin
dbms_output.put_line(user_name);
dbms_output.put_line(n);
dbms_output.put_line(today);
-- dbms_output.put_line(bool); --출력불가 'PUT_LINE'에 bool type 처리하는 함수가 없음.
dbms_output.put_line(age); --20
-- age :=age+1; --PLS-00363: expression 'AGE' cannot be used as an assignment target
bool := age>30;
if bool then
dbms_output.put_line('참참참');
else
dbms_output.put_line('거짓거짓거짓');
end if;
end;
/선언부에 bool을 사용하면 PUT_LINE 에러가 발생
상수값은 정해져있는 값이므로 age에 +1을 하면 --PLS-00363: expression 'AGE' cannot be used as an assignment target 에러가 발생
:= 는 JAVA에서 = 와 같은 대입연산자이다.
참조변수 %tpye
기존 테이블을 컬럼단위로 참조
desc employee;
declare
v_emp_name employee.emp_name%type;
v_phone employee.phone%type;
begin
select
emp_name,phone
into
v_emp_name,v_phone
from
employee
where
emp_id='&사번';
dbms_output.put_line('사원명 : ' || v_emp_name);
dbms_output.put_line('전화번호 : ' || v_phone);
end;
/
참조변수 %rowtype
기존테이블을 행단위로 참조
declare
erow employee%rowtype;
begin
select
*
into
erow
from
employee
where
emp_id = '&사번';
dbms_output.put_line('사원명 ' || erow.emp_name);
dbms_output.put_line('이메일 ' || erow.email);
dbms_output.put_line('급여 ' || erow.salary || '원');
end;
/
참조변수타입 record
declare
-- 자료형 생성
type myemptype is record(
v_emp_name employee.emp_name%type,
v_dept_title department.dept_title%type,
v_job_name job.job_name%type
);
-- 변수명 자료형
myemprow myemptype; begin
select
emp_name,
(select dept_title from department where dept_id = e.dept_code) dept_title,
(select job_name from job where job_code = e.job_code) job_name
into
myemprow
from
employee e
where
emp_id = '&사번';
dbms_output.put_line('사원명 : ' || myemprow.v_emp_name);
dbms_output.put_line('부서명 : ' || myemprow.v_dept_title);
dbms_output.put_line('직급명 : ' || myemprow.v_job_name);
end;
/
DQL | DML
DQL select절의 조회결과가 into절의 변수에 순서대로 대입되는 과정 추가
DML dml실행 후 commit처리까지 함께 진행할 것. (pl/sql을 하나의 트랜잭션으로 지정)
begin
insert into
member
values(
'sejong', '1234', '세종대왕', 'sejong@naver.com' , 'M', 1000, default
);
commit;
end;
/ex ) employee_ex에 사원정보를 1행 추가하는 익명블럭을 작성.
emp_id는 마지막 emp_id + 1한 값을 자동으로 부여
emp_name, emp_no, email, phone, dept_code, job_code, sal_level은 사용자입력을 받아 처리할 것.
insert into employee_ex (
select * from employee
);
commit;
declare
v_emp_id number;
begin
-- 신입 사번 조회
select
max(emp_id) + 1
into
v_emp_id
from
employee;
-- 사원입력 & 트랜잭션처리
insert into
employee_ex (emp_id, emp_name, emp_no, email, phone, dept_code, job_code, sal_level)
values (
v_emp_id, '&사원명', '&주민번호', '&이메일', '&전화번호', '&부서코드', '&직급코드', '&급여레벨'
);
commit;
end;
/
실제로 224에 추가된 것을 확인할 수 있다.
제어문
if문
|
if문
|
if else문
|
if else if 문
|
|
if 조건식 then
처리구문 end if ; |
if 조건식 then
true 처리구문 else false 처리구문 end if; |
if 조건식1 then
처리구문1 elsif 조건식2 then 처리구문2 elsif 조건식3 then 처리구문3 else 기본처리문 end if; |
이런식으로 사용된다.
ex ) 사번을 입력받고, 해당사원의 급여와 평균급여의 비교결과를 조회
declare
v_salary employee.salary%type;
avg_salary employee.salary%type;
begin
select
salary
into
v_salary
from
employee
where
emp_id = '&사번';
select
trunc(avg(salary))
into
avg_salary
from
employee;
dbms_output.put_line(v_salary || ', ' || avg_salary);
if v_salary > avg_salary then
dbms_output.put_line('평균보다 급여를 많이 받습니다.');
elsif v_salary < avg_salary then
dbms_output.put_line('평균보다 급여를 적게 받습니다.');
else
dbms_output.put_line('평균급여를 받습니다.');
end if;
end;
/
case분기문
|
유형1
|
유형2
|
|
case 표현식
when 값1 then 처리구문1; when 값2 then 처리구문2; .... [else 기본처리구문] end case; |
case
when 조건식1 then 처리구문 1; when 조건식2 then 처리구문 2; .... [else 기본처리구문] end case; |
accept 가위바위보 prompt '가위1 바위2 보3 중에 하나를 입력하세요'
declare
com number := trunc(dbms_random.value(1, 4)); -- 1.0이상 4.0미만의 실수를 반환
n number := &가위바위보;
begin
dbms_output.put_line('com : ' || com);
dbms_output.put_line('user : ' || n);
case
when n = com then
dbms_output.put_line('비겼습니다.');
when (n = 1 and com = 3) or (n = 2 and com = 1) or (n = 3 and com = 2) then
dbms_output.put_line('당신이 이겼습니다.');
else
dbms_output.put_line('당신이 졌습니다.');
end case;
end;
/
loop / while loop / for in loop
|
loop
|
while loop
|
for in loop
|
|
loop
-- 반복문 내용 exit when [ 반복문 탈출 조건 (필수) ] end loop; |
while [ 반복문 유지 조건 ] loop
-- 반복문 내용 end loop; |
for 변수 in (reverse) A .. B loop
-- 반복문 내용 end loop; A .. B 는 반복 범위의 숫자. 증감 변수를 미리 선언할 필요 없음. 증가값 1로 고정 (reverse사용시 -1 가능) |
ex ) 1~5까지 프린트
declare
n number := 1;
begin
loop
dbms_output.put_line(n);
n := n + 1;
exit when n > 5; -- 탈출구문 필수
end loop;
end;
/
declare
n number := 1;
begin
while n <= 5 loop
dbms_output.put_line(n);
n := n + 1;
end loop;
end;
/ex ) 반복문을 통해 사원 조회
declare
erow employee%rowtype;
begin
for n in 200..223 loop
select
*
into
erow
from
employee
where
emp_id = n;
dbms_output.put_line(erow.emp_id || ' ' || erow.emp_name || ' ' || erow.phone);
end loop;
end;
/