PL/SQL(Procedural Language extention to SQL)
아래와 같은 절차적 언어의 요소를 더하여 향상된 데이터 처리를 가능케 함.
- 선택처리(IF)
- 반복처리(LOOP, WHILE, FOR)
PL/SQL의 구성
DECLARE ~ BEGIN ~ EXCEPTION ~ END;
- 선언부 (DECLARE)
- 실행부 (BEGIN)
- 예외처리부 (EXCEPTION)
CREATE PROCEDURE/FUNCTION : 저장 프로시저 혹은 함수 생성
프로시저와 함수의 구분 : 모듈이 반환하는 값의 수에 의해 결정
함수 : 반환되는 값의 수가 하나
프로시저 : 반환되는 값의 수가 여러개
패키지 : 프로시저/함수의 그룹화
SQL> @---.sql -- 프로시저 생성 (에러가 없으면 생성 완료)
프로시저 디버깅은 DB TOOL을 사용하여 가능함 (SQL Developer)
SQL> execute 프로시저명(매개변수); -- 프로시저 실행
CREATE PROCEDURE TEST1
(v_sub_name in varchar2(30)
is
변수 선언
variable_name {mode} data_type
자체 사용 변수 선언 시, mode는 생략
IS 이전 변수 선언 : 매개변수 선언 (호출 시 입력받는 값)
IS 이후 변수 선언 : 모듈에서만 사용되는 변수 (자체 사용 변수)
mode
- IN : 프로시저가 호출될 때 전달되는 변수
- OUT : 프로시저 실행 후 반환되는 변수
- INOUT : 2개의 기능을 모두 갖는 변수
저장 프로시저의 경우, 테이블의 열의 값을 저장하게 됨으로 열의 데이터 타입을 참조하여 선언이 가능함
- %TYPE : 열의 데이터 타입을 참조
v_sub_no enrol.sub_no%type -- 열의 값들을 저장하는 변수 선언
- %ROWTYPE : 테이블의 모든 열의 데이터 타입을 참조
v_enrol enrol%rowtype -- 전체열을 모두 정의하는 변수 선언
ex) 열의 값 참조시
v_enrol.sub_no
실무에서는 스칼라타입(직접 데이터 타입 선언)의 변수보다는 참조타입 변수를 많이 사용함
선택 처리문
if 조건1 then
실행문1;
else if 조건문2 then
실행문2;
else
실행문
end if;
반복 처리문
1) LOOP ~ END LOOP (반복 횟수를 정할 수 없을 때)
반복을 중단하는 조건문 주의 (무한반복)
LOOP
실행문1;
EXIT WHEN 반복 중단 조건문;
END LOOP;
2) FOR ~ LOOP (반복 횟수 지정)
[REVERSE]로 증분값을 감소하는 방안으로 사용가능함
FOR 변수 IN 초기값..최종값 LOOP
실행문;
END LOOP;
예제는 아래와 같다.
FOR v_cnt IN 1..10 LOOP
.......
END LOOP
3)WHILE ~ END LOOP (조건이 만족되는 동안 반복실행)
WHILE 반복 조건
실행문;
END LOOP;
저장 프로시저 작성 (.sql)
CREATE [OR REPLACE] PROCEDURE 스키마.프로시저명
(
매개변수명 [MODE] DATA_TYPE,
..........
)
IS
변수명 DATA_TYPE,
..........
BEGIN
~
EXCEPTION
~
END;
/
예제)
CREATE OR REPLACE PROCEDURE test3
(v_stu_no in student.stu_no%type,
v_stu_name out student.stu_name%type)
IS
BEGIN
SELECT stu_name
INTO v_stu_name
FROM student
WHERE stu_no = v_stu_no;
END test3;
/
SQL> @test3.sql
SQL> variable d_stu_name varchar2(12); -- 바인드 변수 선언
SQL> excute test3(20153075, :d_stu_name); -- :d_stu_name
SQL> print d_stu_name;
저장 함수 작성
CREATE [OR REPLACE] FUNCTION 스키마.함수명
( 매개변수명1 [mode] data_type,
..............)
return data_type --- 반환 데이터의 자료형 정의
IS
variable data_type,
............
BEGIN
........
RETURN variable;
EXCEPTION
........
END;
저장 함수를 실행하고 돌아갈 때 하나의 값을 가지게 되며, 이 값은 함수의 이름에 저장됨
따라서, 함수의 이름에 반환 데이터를 저장하기 위한 데이터 타입이 필요함
return data_type;
예제)
CREATE OR REPLACE FUNCTION test6
(v_enr_grade in number)
return char
IS
enr_score char
BEGIN
IF v_enr_grade >= 90 then enr_score := 'A';
ELSE IF v_enr_grade >= 80 then enr_score := 'B';
ELSE IF v_enr_grade >= 70 then enr_score := 'C';
ELSE IF v_enr_grade >= 60 then enr_score := 'D';
ELSE enr_score := 'F';
END IF;
RETURN (enr_score);
END test6;
/
SQL> @test6.sql
SQL> variable d_score char;
SQL> excute :d_score := test6(95);
SQL> print d_score;
SQL> select enr_grade, test6(enr_grade) as score
from enrol
where stu_no = '20131001';
