PL/SQL
: Oracle's Procedural Language extension to SQL
: 기존의 ANSI-SQL + 절차 지향 언어 기능 추가(변수, 제어 흐름, 객체 정의 등)
프로시저, Procedure
: 메서드, 함수 등.. (같은 말)
: 순서가 있는 명령어들의 집합
: 모든 PL/SQL 구문은 프로시저내에서만 작성/동작이 가능하다.
: 프로시저 영역 <-> ANSI-SQL 영역
1. 익명 프로시저
: 1회용 코드 작성
2. 실명 프로시저
: 재사용
: 저장
: 데이터베이스 객체
PL/SQL 프로시저 구조
1. 4개의 블럭으로 구성
- DECLARE
- BEGIN
- EXCEPTION
- END
2. DECLARE
: 선언부
: 프로시저내에서 사용할 변수, 객체 등을 선언하는 영역
: 생략 가능
3. BEGIN ~ END
: 구현부
: 구현된 코드를 가지는 영역(메서드의 body 영역)
: 생략 불가능
: 구현된 코드? > ANSI-SQL + PL/SQL(연산, 제어 등)
4. EXCEPTION
: 예외처리부
: catch 역할
: 3번 > try 역할
: 생략 가능
[DECLARE
변수 선언;
객체 선언;]
BEGIN
업무 코드;
업무 코드;
업무 코드;
[EXCEPTION
예외처리 코드;]
END;
/ANSI-SQL vs PL/SQL
1. PL/SQL 자료형
: ANSI-SQL과 동일
2. 변수 선언하기
변수명 자료형(길이) [not null] [default 값];
3. 대입 연산자
- ANSI-SQL
update table set column = '값' - PL/SQL
변수 := 값;
- 실습 코드
set serveroutput on; -- 현재 세션에서만 유효 (로그아웃 하고 다시 로그인 해야함)
---set serveroutput off; -- 끄기
-- 익명 프로시저 선언하기
begin
dbms_output.put_line('Hello World!');
end;
/
declare
--- 변수명 자료형(길이) [not null] [default 값];
num number;
name varchar2(30);
today date;
begin
num := 10;
dbms_output.put_line(num);
name := '홍길동';
dbms_output.put_line(name);
today := sysdate;
dbms_output.put_line(today);
end;
/
📌 not null 제약 조건 주의 사항
declare
num1 number;
num2 number;
num3 number := 30;
num4 number default 40;
num5 number not null := 50; --declare 블럭에서 초기화를 해야 한다.
begin
dbms_output.put_line('num1' || num1); --null, 초기화 안해도 에러 안남
num2 := 20;
dbms_output.put_line(num2);
dbms_output.put_line(num3);
dbms_output.put_line(num4);
-- num5 := 50; 에러
-- num5 := null; 에러
dbms_output.put_line(num5); --ORA-06550: 줄 6, 열10:PLS-00218: NOT NULL로 정의된 변수는 초기치를 할당하여야 합니다
end;
/
📌 변수의 용도
select 결과를 담는 용도 > 비중 높음
select into 절(PL/SQL)
into 사용 시
1. 컬럼의 개수와 변수의 개수 일치
2. 컬럼의 순서와 변수의 순서 일치
3. 컬럼과 변수의 자료형 일치
=> 자료형 일치를 위해 타입 참조
타입 참조
%type
: 사용하는 테이블의 특정 컬럼의 스키마를 알아내서 변수에 적용
: 복사되는 정보
a. 자료형
b. 길이
(제약 사항은 복사가 안됨)%rowtype
: 행 전체 참조(여러개의 컬럼을 참조)
declare
vname tblInsa.name%type;
vbuseo tblInsa.buseo%type;
vjikwi tblInsa.jikwi%type;
vbasicpay tblInsa.basicpay%type;
begin
select
name, buseo, jikwi, basicpay
into vname, vbuseo, vjikwi, vbasicpay
from tblInsa where num = 1001;
dbms_output.put_line(vname);
dbms_output.put_line(vbuseo);
dbms_output.put_line(vjikwi);
dbms_output.put_line(vbasicpay);
end;
/
PL/SQL 제어문
1. 조건문
if문
if
elsif
else
end if;case 문
: ANSI-SQL의 case와 동일
: 자바 : switch문, 다중 if문
2. 반복문
loop
: 단순 반복
for loop
: loop 기반
: 횟수 반복 (자바 for)
while loop
: loop 기반
: 조건 반복 (자바 while)
- 실습 코드
declare
vnum number := 10;
begin
if vnum > 0 then
dbms_output.put_line('양수');
end if;
end;
/
declare
vnum number := -10;
begin
if vnum > 0 then
dbms_output.put_line('양수');
elsif vnum < 0 then -- else if, elsif, elseif 등..
dbms_output.put_line('음수');
else
dbms_output.put_line('0');
end if;
end;
/
📌 case 문
declare
vcontinent tblCountry.continent%type;
vresult varchar2(30);
begin
select continent into vcontinent from tblCountry where name = '대한민국';
case
when vcontinent = 'AS' then vresult := '아시아';
when vcontinent = 'EU' then vresult := '유럽';
when vcontinent = 'AF' then vresult := '아프리카';
else vresult := '기타';
end case;
dbms_output.put_line(vresult);
case vcontinent
when 'AS' then vresult := '아시아';
when 'EU' then vresult := '유럽';
when 'AF' then vresult := '아프리카';
else vresult := '기타';
end case;
dbms_output.put_line(vresult);
end;
/
📌 반복문
-- exit (루프탈출) > 안쓰면 무한 루프
declare
vnum number := 1;
begin
loop
dbms_output.put_line(vnum);
vnum := vnum + 1;
exit when vnum > 10;
end loop;
end;
/
create table tblGugudan (
dan number not null,
num number not null,
result number not null
);
alter table gugudan
add constraint tblgugudan_dan_num_pk primary key(dan,num); -- alter로 제약사항 만들기
-- 구구단 만들기
begin
for dan in 2..9 loop
for num in 1..9 loop
insert into tblGugudan (dan, num, result)
values (dan, num, dan * num);
end loop;
end loop;
end;
/
select * from tblGugudan;
declare
vnum number := 1;
begin
while vnum <= 10 loop
dbms_output.put_line(vnum);
vnum := vnum +1;
end loop;
end;
/
