[데이터베이스] #12 SQL 확장

목차

1. SQL 절차적 확장
   1. 외부 언어 함수/프로시저
   2. 저장 프로시저
2. SQL: 1999 함수 및 프로시저
   1. 테이블 함수
   2. SQL 프로시저
   3. 절차 생성자
3. PL/SQL
   1. 선언 및 예외
   2. 선언부
   3. 조건 로직
   4. 루프
   5. 사용자 정의 예외
   6. 커서
   7. 프로시저
   8. 함수
   9. 패키지

 

---

1. SQL 절차적 확장

사용자는 SQL/PSM 또는 외부 프로그래밍 언어를 이용하여 함수와 프로시저를 개발할 수 있음

ex)

• 이미지 데이터 타입에 대한 중첩 판정 기능
• 이미지 유사성 계산 기능

1. 외부 언어 함수/프로시저

Create procedure deptCountProc
					(in deptName varchar(20), out count integer)
language C
external name '/usr/shlee/bin/deptCountProc';

Create procedure deptCountProc2(deptName varchar(20))
returns integer
language C
external name '/usr/shlee/bin/deptCount';

external name에 명시되는 것은 C 프로그램의 목적 코드

장단점

• 외부 언어로 개발 시 보안 문제(메모리 문제)
  • C#, JAVA 등 안전한 언어로 작성
  • 독립적인 프로세스로 사용자 함수를 실행

2. 저장 프로시저

데이터베이스 객체로 관리 됨

호출 문장 실행 만으로 다수개의 SQL 문장 묶음이 서버에서 실행 → 네트워크 부하 감소

저장 프로시저 호출 권한, 저장 프로시저가 접근하는 데이터 권한이 분리되어 관리됨

사용자에게 저장 프로시저 권한을 부여하여 사용자가 권한이 없는 데이터 접근도 가능

 

---

2. SQL: 1999 함수 및 프로시저

Create function profC(deptName varchar(20)) returns integer
begin
	Declare pCount integer;
	Select count(*) into pCount
	from professor
	where professor.deptName = profC.deptName;
	Return pCount;
end;

입력으로 주어지는 학과(이름)의 교수 수를 반환하는 함수

테이블 함수

Create function myProf(deptName varchar(20))
	returns table(pID char(5),
								name varchar(20),
								deptName varchar(20),
								salary numeric(10,2))
	return table(	select piD, name, deptName, salary
								from professor
								where professor.deptName = myProf.deptName);

Select * from table(myProf('CS'));

• returns 함수 선언 head에 나오는 키워드
• return 함수 body에 나오는 키워드

SQL 프로시저

SQL 함수와는 다르게 인자에 대하여 입력 및 출력 명시

출력 인자가 2개 이상인 경우에 프로시저 사용

인자 개수, 타입으로 서로 다른 프로시저 구분

절차 생성자

Declare noMoreSeats condition;
Declare exit handler for noMoreSeats;
begin
	...
	signal noMoreSeats; // signaling noMoreSeats
end

exit 처리자에 대한 예제

 

---

3. PL/SQL

선언 및 예외

[Declare
	declaration _ statment]
Begin
	executable _ statments
[Exception
	exception _ handling _ statments]
End;

선언부

sName  student.name%type;
dName  student.deptName%type;
myStudent  student%rowtype;

• 앞에 명시한 속성과 같은 타입을 지정
• 앞에 명시한 테이블과 같은 테이블을 지정

조건 로직

If condition1 then
	statement1
elseif condition2 then
	statement2
else
	statement3
endif;

루프

Loop
	statements
end loop

• exit 또는 exit when으로 루프 연산 중지

While condition loop
	statements
end loop

For loop_var in [reverse] low_bound..upper_bound loop
	statements
end loop

사용자 정의 예외

정상적인 실행 상태로 인식

1. 정의 블록에서 선언
2. 실행 블록에서 raise 문을 통하여 예외 발생
3. 예외처리 블록에서 이를 처리

Declare
	invalid_deptno exception;
Begin
	Update dept
		set dname = 'engineer'
		where deptno = '123':
	If SQL%NOTFOUND then
		raise invalid_deptno;
	end if;
	dbms_output.put_line('update was successful');
Exception
	when invalid_deptno then
		dbms_output.put_line('No such deptno');
	when others then
		dbms_output.put_line('An error occurs');
end;
/

커서

select 문장이 하나 이상의 터플을 반환하는 경우 커서 사용

1. Select 문장이 반환하는 속성을 저장하는 변수 선언
2. 커서 declare
3. 커서 open
4. 커서로부터 터플을 fatch
5. 커서 close

프로시저

Create [or replace] procedure proc_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
begin
	procedure body
end proc_name;
Call proc_name(argnment1, ...);

• Curly bracket는 반드시 두 개중 하나는 존재해야함
• {IS | AS}와 begin 사이에 프로시저에서 사용할 변수 선언
• 예외처리 명세는 end proc_name 바로 전에 함

함수

Create [or replace] function function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
return type
{IS | AS}
begin
	procedure function_body
end function_name;

시스템이 제공하는 built-in 함수를 호출하는 방식으로 사용자가 정의한 함수를 호출

패키지

• 선언: specification, body
• specification 관련 함수 및 프로시저 이름과 매개변수 명시
• body 실제 코드 명시
• 호출: ‘패키지명.함수명’