메인● PL/SQL이란?
▶PL/SQL은 절차적 프로그래밍 언어로 SQL을 확장한 것.
▶데이터 조작과 질의문은 PL/SQL 블록안에 포함된 것.
▶PL/SQL은 별개의 Oracle 제품이 아니라 Oracle7 서버와 다른 Oracle 툴에 이용되고 있는 프로그래밍 언어이다.
● PL/SQL의 장점
SQL로는 얻을 수 없는 PL/SQL의 절차적 프로그래밍 기능이 있음.
◈ 모듈화된 프로그램 개발
º 블록 내에서 논리적으로 관련된 문장들의 그룹화
º 강력한 프로그램을 작성하기 위해 서브 블록들을 큰 블록에 포함
º 복잡한 문제에 대한 프로그래밍이 적절히 나뉘어진 모듈들의 집합으로 구성
◈ CURSOR, EXCEPTION
º 변수, 상수 등을 선언하고 SQL과 절차적인 프로그램에서 사용
º 데이터베이스의 테이블과 Record를 기반으로 하는 dynamic한 변수 선언이 가능
◈ 절차적 언어 구조로 된 프로그램 작성
º 조건에 따라 일련의 문장을 실행 (IF)
º 루프에서 반복적으로 일련의 문장을 실행 (LOOP)
º Explicit Cursor를 이용한 Multi-row 질의 처리
◈ ERROR 처리
º Exception 처리 루틴을 이용하여 오라클7 서버 에러를 처리함
º 사용자 정의 에러를 선언하고 Exception 처리 루틴으로
처리가능함.
● PL/SQL 블록 구조
▶DECLARE←Optional
-변수, 상수, Cursor
-user - defined exception
▶BEGIN←Mandatery
-SQL 문장
-PL/SQL 제어문
▶EXCEPTION ←Optional
-Error 발생시 수행할 내용
▷END; ←Mandatery
● SQL*Plus를 이용한 PL/SQL 블록 작성
▶DECLARE절
-선택사항(option) 절이다.
-PL/SQL에서 참조되는 모든 변수와 상수 선언.
☞ 변수와 상수선언:Syntax
Identifier [CONSTANT] Datatype [NOT NULL]
[:= | DEFAULT 수식];
-CONSTANT는 식별자의 값이 변경될 수 없도록 선언
-Naming Rule을 따름
-NOT NULL로 지정된 상수와 변수에 초기 값을 주어야 함
-지정 연산자(:=)나 DEFAULT 예약어를 사용하여 번수를 초기화 할수 있음
-한 행에는 하나의 변수만 선언할 수 있음.
☞ 변수와 상수선언: 예
Fax_NumberVARCHAR2(10);
Current_Used_ValueNUMBER(6,2) := 100.00;
StateVARCHAR2(2) := 'CA';
▶PL/SQL만의 변수 선언
-BOOLEAN;
True, False, Null 중에 하나의 값을 가짐.
-%TYPE; 이미 지정된 Identifier의 datatype을 사용함.
.데이터베이스 테이블의 column
.이미 선언한 변수명
Ex> T_EmpnoEmp.Empno%TYPE;
T_MgrT_empno%TYPE;
>%TYPE 속성을 이용하여 얻을 수 있는 장점
.기술한 데이터베이스 column definition을 정확히 알지 못하는 경우에도 쓸 수 있다.
.기술한 데이터베이스 column definition이 변경되어도 다시
PL/SQL을 고칠 필요 없다.
-%ROWTYPE; 테이블이나 View의 Column명과 datatype들을 선언한 Row의 변수로 취함.
Ex> t_emp_rowemp%ROWTYPE;
BEGIN
t_emp_row.empno := 7803;
t_emp_row.ename := 'RICHARDSON';
☞ 실습용 Table 생성
SQL> create table test_table (
record_number int,
current_date date);
● 반복(Loop) 제어 구조
▶ Basic LOOP
>Exit 조건이 True인 동안 문장을 Loop 반복.
>루프 안에 EXIT문이 없으면 루프를 무한히 반복하게 된다.
>EXIT문에 WHEN 조건절이 없으면 무조건 빠져나감.
SQL>DECLARE
max_records CONSTANT int := 10;
i int := 0;
BEGIN
LOOP
i := i + 1;
INSERT INTO test_table (record_number, current_date)
VALUES(i, sysdate);
EXIT WHEN i >= max_records;☜Exit 확인 지점
END LOOP;
COMMIT;
END;
▶ WHILE-LOOP
>While 조건이 TRUE인 동안에 문장을 반복.
SQL>DECLARE
max_records CONSTANT int := 10;
i int := 0;
BEGIN
WHILE i < 10 LOOP☜Exit 확인 지점
i := i + 1;
INSERT INTO test_table (record_number, current_date)
VALUES(i + 10, sysdate + 1);
END LOOP;
COMMIT;
END;
▶ FOR-LOOP
>FOR문의 하한값과 상한값 사이 조건일 동안만 Loop 반복.
>인덱스는 자동적으로 선언된다.
>인덱스는 상한에 도달할 때까지 루프를 반복할 때마다 자동적으로 1씩 증감하는 값
>REVERSE는 상한에서 하한까지 인덱스가 반복 때마다 감소
FOR 인덱스 IN [REVERSE] 하한..상한 LOOP
문장1;
문장2;
...
END LOOP;
SQL>DECLARE
max_rec CONSTANT int := 30;
start_rec int := 21;
BEGIN
FOR i IN start_rec..max_rec LOOP☜Exit 확인 지점
INSERT INTO test_table (record_number, current_date)
VALUES(i, SYSDATE + 2);
END LOOP;
COMMIT;
END;
♠ LOOP와 Label
>내부 블록의 값에 근거하여 외부 루프까지 종료할 때 사용.
>Label은 문장에서의 특정 위치를 지정할 때 사용. <<lebel_name>>
＜＜outer_loop＞＞
WHILE a>b LOOP
b:=b+1;
＜＜inner_loop＞＞
WHILE b>c LOOP
c:=c+2;
EXIT outer_loop WHEN c>200;
END LOOP inner_loop;
END LOOP outer_loop;
♠ GOTO문과 Label
>GOTO문을 사용하여 특정 Label로 빠져나간다.
WHILE a>b LOOP
b:=b+1;
WHILE b>c LOOP
c:=c+2;
if c>200 then
GOTO more_process;
END LOOP;
END LOOP;
<<more_process>>
● IF 제어 구조
>조건에 따라 선택적으로 작업을 수행한다.
IF조건 THEN
문장;
[ELSIF 조건 THEN☜반복 사용 가능
문장;]
ELSE
문장;
END IF;
SQL>DECLARE
max_rec CONSTANT int := 30;
BEGIN
FOR i IN 1..max_rec LOOP
IF i <= 10 THEN
update test_table set current_date = current_date + i
where record_number = i;
ELSIF i <= 20 THEN
update test_table set current_date = current_date - i
where record_number = i;
ELSE
NULL;
END IF;
END LOOP;
COMMIT;
END;
● Null문 사용
>어떠한 행동도 취하지 말아야 할 부분에 사용
● 주석 달기
> /* 와 */ 사이에 한 줄 이상 명시
>또는, -- 다음에 한 줄만 명시
● 검색된 내용 사용 및 보기
SQL> SET SERVEROUTPUT ON
SQL>DECLARE
max_rec CONSTANT int := 30;
sel_rowtest_table%rowtype;
output_linevarchar2(80);
BEGIN
DBMS_OUTPUT.ENABLE;
FOR i IN 1..max_rec LOOP
SELECT * INTO sel_row.record_number, sel_row.current_date
from test_table
where record_number = i;
output_line := to_char(sel_row.record_number, '99')|| ' '
|| to_char(sel_row.current_date,'yy/mm/dd');
dbms_output.put_line(output_line);
END LOOP;
END;
● 블록 내의 Procedure Call
>PL/SQL 블록 내에 별도의 Procedure를 선언해 놓고 Main
블록에서 Procedure call하여 사용
SQL>DECLARE
row_cnt CONSTANT int := 10;
PROCEDURE sel_out_proc (max_rec int) is
sel_rowtest_table%rowtype;
output_linevarchar2(80);
BEGIN
DBMS_OUTPUT.ENABLE;
FOR i IN 1..max_rec LOOP
SELECT * INTO sel_row.record_number, sel_row.current_date
from test_table
where record_number = i;
output_line := to_char(sel_row.record_number, '99')|| ' '
|| to_char(sel_row.current_date,'yy/mm/dd');
dbms_output.put_line(output_line);
END LOOP;
END;
BEGIN
sel_out_proc (row_cnt);
END;
● 블록 내의 Function Call
>PL/SQL 블록 내에 별도의 Function을 선언해 놓고 Main 블록에서 Function call하여 사용
>Function에서 미리 정의된 데이터형의 값만을 반환한다는
것을 제외하고는 Procedure Call과 동일
SQL>DECLARE
chk_char varchar2(30) := '%A%';
wild_chk_cnt varchar2(10);
FUNCTION sel_cnt_func (wild_chk varchar2) return int is
row_cntint;
BEGIN
SELECT count(*) INTO row_cnt
from student
where last_name like wild_chk;
return row_cnt;
END;
BEGIN
dbms_output.enable;
wild_chk_cnt := to_char(sel_cnt_func (chk_char), '999');
dbms_output.put_line('Last_name like ' || chk_char ||': '||wild_chk_cnt);
END;
Last_name like %A%: 15
PL/SQL 처리가 정상 완료되었습니다.
<실습> p267, [리스트 9.15]
특정 학과(department_id)의 모든 강좌(Course_id)에 대해서
부가수업료(additional_fees)가 가장 큰 강좌코드를 출력하는 PL/SQL 프로그램을
Function()을 사용하여 작성하라.
>SQL로 해결
SQL> select Course_ID, additional_fees
from Course A
where Department_ID = 'ECON'
and Additional_Fees =
(select max(Additional_Fees)
from Course B
where A.Department_ID = B.Department_id);
>PL/SQL로 해결
SQL> DECLARE
Course_ID Course.Course_ID%type;
FUNCTION Max_Additional_Fees (Dept_ID varchar2)
return varchar2 is
Additional_Fees Course.Additional_Fees%type;
Units Course.Units%type;
Course_ID Course.Course_ID%type;
BEGIN
select Course_ID into Course_ID
from Course
where Department_ID = Dept_ID
and Additional_Fees =
(select max(Additional_Fees)
from Course
where Department_ID = Dept_ID);
return Course_ID;
END;
-- Beginning of executable section of anonymous block.
BEGIN
dbms_output.enable;
Course_ID := Max_Additional_Fees ('ECON');
dbms_output.put_line('Course_ID: ' || Course_ID);
END;
<실습> p269 [리스트 9.16]
declare
this_arg1 number;
this_arg2 number;
this_arg3 number;
output_linevarchar2(80);
procedure different_arguments
(arg1in number,
arg2out number,
arg3in out number) is
begin
arg2 := arg1;
arg3 := arg3 + 1;
end;
begin
this_arg1 := 3.14159;
this_arg2 := 5;
this_arg3 := 10;
different_arguments (this_arg1, this_arg2, this_arg3);
dbms_output.enable;
output_line := to_char(this_arg1,'99.99') || ' '
|| to_char(this_arg2,'99.99') || ' ' || to_char(this_arg3,'99.99');
dbms_output.put_line(output_line);
end;
● 예외(EXCEPTION) 처리
>PL/SQL을 실행 동안에 발생하는 error에 대한 처리를 의미한다.
▣ 미리 정의된 예외 (System에서 제공)
▣ DUP_VAL_ON_INDEX ; 유일값 중복 Error
▣ INVALID_NUMBER ;Data type 불일치 Error
▣ NO_DATA_FOUND ;해당 자료가 없을 때
▣ TOO_MANY_ROWS ;반환된 값이 하나 이상일 때 (Select ~ into ~ 에서)
▣ VALUE_ERROR ;대입되는 값이 길이 초과 Error
<실습>
▣ DUP_VAL_ON_INDEX ; p313, [리스트 11.3]
▣ INVALID_NUMBER ;p314, [리스트 11.4]
▣ NO_DATA_FOUND ;p315, [리스트 11.6]
▣ TOO_MANY_ROWS ;p315, [리스트 11.7]
▣ VALUE_ERROR ;p316, [리스트
11.8]
<실습> Course 테이블에서 course_id=''777'인 행의 내용을 모두 조회하라.
▣ DUP_VAL_ON_INDEX ; p313,
[리스트 11.3]
SQL>declare
Course_Rec Course%ROWTYPE;
begin
dbms_output.enable;
select *
into Course_Rec from Course
where Course_ID = '777';
exception
when NO_DATA_FOUND then
dbms_output.put_line('No data returned');
when TOO_MANY_ROWS then
dbms_output.put_line('TOO_MANY_ROW returned');
when OTHERS then
NULL;
end;