[데이터베이스] Pl/Sql
Pl/Sql
pl/sql(Procedure Language extension to sql) 은 sql을 확장한 절차적인 언어(Procedure Language)로써 종류로는 함수,저장프로시저,트리거 로 구성된다.
오라클에서는 계정이 DB이자 Schema이고, 계정내부에 사용자가 정의한 함수,프로시저,트리거 등이 저장된다.
그리고 사용자는 이를 호출하여 모듈화된 로직을 그대로 사용하면서 server단에서 로직을 수행시킬수있고 이를통해 보안향상,네트워크 전송량감소라는 장점이 있지만 서버내의 부하가 증가한다는 단점이 있다.
또한 만들어진 프로시저는 트리거와 연계되어 사용가능하며 예외처리가 가능하다.
함수,저장프로시저,트리거 모두 DDL(Data Definition Language)로 create/alter/drop/truncate 등을 이용하여 테이블을 조작할수 있다.
Function
create or replace function 함수이름(매개변수1 타입,매개변수2 타입) return 타입
As
선언부
Begin
구현부 ---Sql문 작성 불가능
return 값;
End 함수이름;
위와같은 형식으로 함수는 만들어진다.
함수는 매개변수가 R-Value이다. 따라서 매개변수로써 변수,함수,값,상수 모두 할당가능하며 call by value형식이기 때문에 할당된 매개변수의 값을 함수내부에서 변경할수 없다.(L-value는 항의 좌측에오는 변수, R-value는 항의 우측에오는 값을 말한다. => L-value= R-value)
또한 리턴타입이 존재하고 반드시 내부에서 정의된 리턴타입에 따른 값을 리턴해주어야 한다.
함수의 내부에서는 Sql문을 사용할수 없으며, Sql문 내부에서는 함수이름을 호출하여 간단한 식처럼 사용할수 있다.
매개변수가 없으면 함수이름의 우측에 ()는 생략할수있다.
만들어진 함수는 계정내의 함수 dictionary내부에 생성된다.
함수의 응용한것이 파이프라인된 함수인데, 이는 임시테이블을 정의하고 임시테이블에 pipe row(튜플타입) 을통해 Loop를 돌면서 튜플을 하나씩 삽입한뒤 return;을 통해 임시테이블을 반환하는 함수를 만들수 있다.
이를 사용하기 위해서는 먼저 테이블에 사용될 튜플타입과, 튜플타입을 사용하는 테이블타입을 선언하여야 한다.
create or replace type 튜플타입 As Object(매개변수1 타입,매개변수2 타입);
위의 DDL로 튜플타입을 정의한후,
create or replace type 테이블타입 As Table of 튜플타입;
위의 DDL로 만들어진 튜플타입을 타입으로하는 테이블타입을 정의한다.
create or replace function 임시테이블반환함수(매개변수1 타입,매개변수2 타입) return 테이블타입 pipelined
As
선언부;
Begin
Loop
구현부;
pipe row(튜플타입); ---튜플을 하나씩 loop문 돌면서 pipe row()를통해 임시테이블에 튜플삽입
End Loop;
return; ---만들어진 임시테이블을 반환함
End 임시테이블반환함수;
위와같은 형식으로 테이블타입을 반환하는 파이프라인된 함수를 정의하고 sql질의문내에서 from절에 table(임시테이블반환함수(매개변수1,매개변수2)) 형식으로 호출하여 사용하면 임시테이블로 부터 값을 조회할수 있다.
select * from Table(임시테이블반환함수(매개변수1,매개변수2))
저장 프로시저
저장프로시저는 함수와달리 리턴값이 없고 매개변수의 타입이 In,Out,In Out 세가지로 구분된다. 또한 프로시저 내부에 Sql문을 사용할수 있지만 Sql문 내부에 프로시저를 호출할수는 없다.
In은 R-value에 해당하며 call by reference형식이고, Out은 L-value에 해당하고 call by value 형식이다. In Out은 L-value와 R-value 두가지 모두로 사용가능하다.
구현형식은 다음과같다.
create or replace procedure 프로시저이름(매개변수1 In 타입,매개변수2 Out 타입,매개변수3 In Out 타입)
As
선언부;
Begin
구현부; ---Sql문 작성가능
End 프로시저이름;
만들어진 프로시저는 계정내부의 프로시저 dictionary에 생성된다.
또한 함수와 프로시저는 prompt 내부에서는 execute 함수/프로시저이름(매개변수) 형식으로 호출한다.
프로시저 내부에서는 sql문을 사용할수 있기 때문에 정적방식이 아닌 동적 sql문을 작성하여 사용할수도 있다.
create or replace procedure 프로시저이름(매개변수1 out 타입,매개변수2 in 타입,매개변수3 in 타입)
As
sql_str varchar2(100); ---동적질의문을 담아둘 프로시저 지역 변수생성
Begin
sql_str := 'select ' ||매개변수2|| ' As "결과물" from 테이블명 where 속성 = ''' ||매개변수3|| '''' ; ---질의문을 문자열형식으로 담아서
execute immediate sql_str into 매개변수1; --- execute immediate 로 수행시키고 반환된 값을 매개변수에 into로 넣음
end 프로시저이름;
sql문 자체를 문자열 형태로 정의하고 execute immediate하여 실행시키면 동적sql문을 작성할 수있다.
매개변수가 out타입이므로 매개변수1에 into를 통해 값을 집어넣어 call by reference로 사용하며, 매개변수3과 어떤속성이 일치하는 매개변수2를 select하는 질의문을 동적방식으로 작성이 가능하다.
또한 테이블이름 역시 매개변수로 받아와서 from절에 사용하면 테이블로 인식하기 때문에 테이블도 동적으로 입력가능하다.
sql_str := 'select ' ||매개변수2|| ' As "결과물" from' ||매개변수4|| ' where 속성 = ''' ||매개변수3|| '''' ; ---질의문을 문자열형식으로 담아서
유의할점은 동적sql문이 아니라 정적sql로 수행한다면 매개변수4 라는 변수이름자체를 테이블이름으로 인식하기때문에 오류가 발생할수 있다.
create or replace procedure 프로시저이름(매개변수1 out 타입)
As
선언부;
Begin
select * from 매개변수1; ---매개변수1 이라는 변수이름 자체를 테이블명으로 인식하기 때문에 테이블이존재하지 않는다는 오류발생
end 프로시저이름;
예외처리(Exception Handling)
함수와 프로시저의 장점으로써 예외처리가 가능하다는점이 있다.
예외처리를 하는 방법은 4가지이다.
- 단순하게 Exception 에서 해당예외 처리하기
- 사용자 정의 예외처리
- Raise 예외변수 로 예외 강제로 발생시켜 Exception 단에서 해당예외 처리하기
- Raise_Application_Error 로 직접만든 예외코드,예외문구로 예외발생시켜 Exception 단에서 해당예외 처리하기
단순하게 Exception 에서 해당예외 처리
create or replace function 함수명(매개변수1 타입) return 리턴타입
As
선언부;
Begin
구현부;
Exception when Too_Many_Rows then DBMS_OUTPUT.PUT_LINE('너무 데이터가 많습니다.');
when No_Data_Found then DBMS_OUTPUT.PUT_LINE('데이터가 존재하지 않습니다.');
End 함수명;
위는 단순하게 Exception에서 예외코드 혹은 발생예외명 혹은 예외변수명이 발생하였을때(when) 어떠한 로직을 수행하겠다(then)으로 구현한다.
사용자 정의 예외처리
create or replace function 함수명(매개변수1 타입) return 리턴타입
As
예외변수 exception;
pragma exception_init(예외변수,예외코드); --- 예외코드는 반드시 존재하는 예외코드를 작성해야함.
Begin
구현부;
Exception when Too_Many_Rows then DBMS_OUTPUT.PUT_LINE('너무 데이터가 많습니다.');
when No_Data_Found then DBMS_OUTPUT.PUT_LINE('데이터가 존재하지 않습니다.');
when 예외변수 then DBMS_OUTPUT.PUT_LINE('예외변수 의 예외발생');
End 함수명;
예외변수를 exception타입으로 선언하고 해당예외를 초기화하기 위해 pragma exception_init()을 사용하여 초기화한다. 이때 반드시 만들어진 예외변수와 존재하는 예외코드를 매개변수로 할당하여야 한다.
Raise 예외변수 로 예외 강제로 발생시켜 Exception 단에서 해당예외 처리
create or replace function 함수명(매개변수1 타입) return 리턴타입
As
예외변수 exception;
pragma exception_init(예외변수,예외코드); --- 예외코드는 반드시 존재하는 예외코드를 작성해야함.
Begin
select * from 테이블;
if sql%notfound then
raise 예외변수;
End if;
Exception when Too_Many_Rows then DBMS_OUTPUT.PUT_LINE('너무 데이터가 많습니다.');
when No_Data_Found then DBMS_OUTPUT.PUT_LINE('데이터가 존재하지 않습니다.');
when 예외변수 then DBMS_OUTPUT.PUT_LINE('예외변수 의 예외발생');
End 함수명;
raise 예외변수 를 해당조건식에 부합하였을때 수행하도록 하여 예외를 강제로 발생시켜 처리하는 방법도 있다.
Raise_Application_Error 로 직접만든 예외코드,예외문구로 예외발생시켜 Exception 단에서 해당예외 처리
create or replace function 함수명(매개변수1 타입) return 리턴타입
As
예외변수 exception;
pragma exception_init(예외변수,예외코드1); --- 아래에서 만들어진 예외코드를 사용하여 초기화하고 예외처리
Begin
select * from 테이블;
if sql%notfound then
raise_application_error(예외코드1,'예외문구');
End if;
Exception when Too_Many_Rows then DBMS_OUTPUT.PUT_LINE('너무 데이터가 많습니다.');
when No_Data_Found then DBMS_OUTPUT.PUT_LINE('데이터가 존재하지 않습니다.');
when 예외변수 then DBMS_OUTPUT.PUT_LINE('예외변수 의 예외발생');
End 함수명;
raise_application_error는 예외를 만드는 함수라고 생각하면 된다. 예외코드의 범위는 20001~20999로 예외문구와 함께 직접만들어 발생시킨뒤 예외변수를 만들어진 예외의 코드로 초기화하고 예외처리를 하는 방법으로 사용한다.
또는 위와같이 예외를 처리하지않고 그냥 예외만 발생시켜 컴파일단계에서 종료되면서 에러가 뜨게도 사용할수있다. ( 트리거에서는 예외를 강제로 발생시켜 해당트리거가 등록된 변경연산의 수행을 취소시키는 방법으로 사용된다.)
Cursor
create or replace procedure 프로시저이름(매개변수1 out 타입,매개변수2 in 타입,매개변수3 in 타입)
As
sql_str varchar2(100); ---동적질의문을 담아둘 프로시저 지역 변수생성
Begin
sql_str := 'select ' ||매개변수2|| ' As "결과물" from 테이블명 where 속성 = ''' ||매개변수3|| '''' ; ---질의문을 문자열형식으로 담아서
execute immediate sql_str into 매개변수1; --- execute immediate 로 수행시키고 반환된 값을 매개변수에 into로 넣음
end 프로시저이름;
위의 프로시저에서 사용했던 예시를 가져왔다.
만약 위의 동적질의문의 결과가 매개변수1에 담길때 여러개의 튜플의 값이면 어떻게 될까?
오류가 발생한다. 왜냐하면 into뒤의 변수는 항상 하나의 값만을 저장할수 있기 때문이다.
이를위해 oracle에는 varray(길이)의 배열타입이나 nested table로써 table타입 또는 associated table(hash map구조)로써의 table타입을 지원하고 있다.
하지만 이세가지 타입들은 loop문을 돌리면서 각각의 인덱스에 값을 하나씩 할당하는 방법으로 사용하지 실질적으로 into 뒤에 배열변수같은것을 집어넣는다고 여러개를 한번에 넣어주지는 않는다.
그럼어떻게 처리하는가? 방법은 두가지다.
첫번째는 예외로 처리하는것이다. into 뒤에 여러개의 튜플값이 들어가야 한다면 Too_Many_Rows 라는 예외가 발생하고 이를 exception에서 예외처리하는 방법이 있다.
물론 이는 근본적으로 여러개의 행을 어떠한 변수에 집어넣어 사용할수있도록은 할수없다.
두번째로 Cursor를 사용하여 처리하는 것이다. Cursor는 여러개의 튜플로 반환되는 것을 하나씩의 튜플을 Loop문을 돌리면서 가져와서 처리하도록 사용된다.
즉, 위와같은 문제점을 해결하기 위한 근본적인 방법으로써 만들어진 해결책이다.
create or replace procedure 프로시저이름(매개변수1 out 타입,매개변수2 out 타입)
As
Cursor C Is select 학생.학번,학생.이름 from 학생테이블; --- 여러개의 튜플을 반환하는 질의문을 커서변수에 정의
tuple_학번 varchar2(10); --- 반환되는 학번을 저장할 변수
tuple_이름 varchar2(10); --- 반환되는 학생이름을 저장할 변수
Begin
open c; --- 커서를 사용하기위해 열어야함.
Loop
fetch C into tuple_학번,tuple_이름;
exit when C%Notfound; --- 더이상 루프문을 수행할 튜플행이 존재하지 않는다면 반복문을 빠져나옴 (exit)
구현부;
End Loop;
close c;
end 프로시저이름;
커서는 커서변수할당후 커서를 열고 무한루프를 돌면서 각각의 튜플행을 fetch하여 가져와서 처리한후 모든행에 대해 로직을 수행했으면 exit하고 커서를 닫고 종료시키는 방식으로 사용한다.
패키지
패키지는 관련되는 함수,저장프로시저,변수,커서 등을 모두 저장시켜놓고 묶음으로써 사용하는 객체이다.
패키지는 header와 body로 구분되며 header는 선언부, body에는 구현부로 구성된다.
또한 패키지내의 구성요소를 사용하기 위해서는 패키지.구성요소 로 호출하여 사용한다.
create or replace package 패키지이름
As
변수명 타입;
Function 함수이름(매개변수1 타입,매개변수2 타입) return 리턴타입;
Procedure 프로시저이름(매개변수1 In 타입,매개변수2 Out 타입);
End 패키지이름;
선언부는 위와같이 변수,함수,프로시저 외에 커서 등등을 package 의 선언부인 as부분에 작성한다.
create or replace package body 패키지이름
As
Function 함수이름(매개변수1 타입,매개변수2 타입) return 리턴타입
As
선언부;
Begin
구현부;
End 함수이름;
Procedure 프로시저이름(매개변수1 In 타입,매개변수2 Out 타입)
As
선언부;
Begin
구현부;
End 프로시저이름;
End 패키지이름;
패키지의 body는 package body키워드로 만들며 반드시 header의 패키지이름과 동일하여야만 한다. 그리고 구현부를 작성해주면 된다.
트리거
단일속성에 적용되는 단순제약조건의 경우 DDL 또는 GUI를통해 테이블을 생성할때 제약조건을 다양한 방법으로 설정해줄수있다.
하지만 특정 속성이아닌 복합적으로 하나의 테이블에 대한 복합제약조건으로써 논리적 제약조건을 작성하기 위해서는 트리거를 만들어야 한다.
함수나 저장프로시저와는 달리 트리거는 특정테이블 또는 특정테이블의 특정속성의 변경연산이 발생하면 background process에 의해 또한 묵시적으로 호출하여 사용하는 것이 아니라 자동으로 만들어진 트리거의 로직을 수행하도록 만든다.
따라서 여러개의 트리거가 만들어지면 그만큼 적용되는 로직을 수행해야 하므로 CPU의 부하가 커지게 되므로 사용하지 않는 트리거는 반드시 삭제해주는 것이 바람직하다.
하나의 테이블 혹은 테이블의속성에 여러개의 트리거가 등록되는 다중트리거와 트리거의 구현부에서 다른테이블의 변경연산을 수행하여 그테이블의 트리거를 발생시키는 형태의 연쇄트리거를 사용할수도 있다.
트리거 작성예는 다음과같다.
create or replace trigger 트리거이름
before/after insert/update/delete [of 속성] --- 속성의경우 지정하지않으면 생략가능
on 테이블
for each row --- 한번의 트리거수행이 여러개의 튜플들을 처리하는경우 각행에 대해 처리하고싶으면 for each row를 넣어주어야한다.
[declare
선언부;]
Begin
구현부;
End 트리거이름;
특정 테이블 혹은 특정테이블의 특정속성의 변경연산이 발생했을때 구현부의 내용이 자동으로 수행되며, 지역변수가 필요없는경우 선언부는 생략가능하다.
또한, 테이블이아닌 뷰에도 트리거를 등록할수있다.
뷰의 경우 변경연산이 수행되면 뷰가 참조하는 테이블의 내용도 변경시켜 준다.
하지만, 뷰가 두개의 테이블을 join연산하여 만들어 졌거나 pk가 포함되지않은경우, 집계함수로 파생되는 속성이 있는경우, unique를 만족하지않는 속성이 포함된경우 원본테이블에 변경연산을 적용할수 없으며 오류가 발생한다.
이럴때는 뷰에 트리거를 등록하여 해당 질의문을 db에 반영시키지 않고 트리거를 수행하여 트리거내에 구성된 질의문을 수행하도록 하여 원하는 질의문을 수행하도록 할수있다.
create or replace view 뷰이름 as select * from 테이블1 inner join 테이블2 on 테이블1.pk=테이블2.pk;
insert into 뷰이름 values (테이블1.속성1,테이블1.속성2,테이블2.속성1,테이블2.속성2);
위와같이 insert 연산을 수행하면 오류가발생한다.
create or replace trigger 트리거이름
instead of insert [of 속성] --- 속성의경우 지정하지않으면 생략가능
on 뷰
for each row --- 한번의 트리거수행이 여러개의 튜플들을 처리하는경우 각행에 대해 처리하고싶으면 for each row를 넣어주어야한다.
Begin
insert into 테이블1 values(속성1,속성2);
insert into 테이블2 values(속성1,속성2);
End 트리거이름;
위의 트리거를 작성하여 뷰의 insert문이 수행할때 트리거의 내부 insert문 두개가 수행하도록 하여 오류를 해결할수있다.
여기서 중요한점은 두개의 insert문의 수행순서이다.
이두개의 테이블은 join연산으로 구성되어져 있기때문에 한테이블의 pk를 다른테이블의 fk가 참조하고 있을것이다.
그런데 Fk의 테이블을 먼저 insert해버리면 참조무결성 제약조건 위반(참조키는 null 혹은 존재하는 pk를 참조하여야만 한다.)이 되므로 반드시 pk가있는 테이블을 insert한뒤에 fk가있는 테이블을 insert하는 순서로 진행되어야만 한다.
또한 제약조건 설정시 delete의 경우에는 no action이 default이지만 cascade와 set null을 oracle에서 지원해준다.
하지만 update는 no action만을 지원해주기 때문에 cascade를 수행시키기 위해 트리거로 구현하여야 한다.
create or replace trigger 트리거이름
after update [of 속성] --- 속성의경우 지정하지않으면 생략가능
on 테이블1
for each row --- 한번의 트리거수행이 여러개의 튜플들을 처리하는경우 각행에 대해 처리하고싶으면 for each row를 넣어주어야한다.
Begin
update 테이블2 set 속성= :New.속성;
End 트리거이름;
테이블1의 update연산이 수행되면 테이블1을 참조하는 테이블2의 속성값에도 update를 해주면 된다.
그런데 만약, 테이블1의 pk속성을 변경한다면 테이블2의 fk가 참조하는 값이 없어지므로 참조무결성 제약조건위반이 발생한다.
이러할때는 트리거 내에서 테이블2의 fk속성값을 테이블1의 pk값으로 바꾸어주면 된다.
여기서 중요한 점이 몇가지 발생한다.
- 원본테이블의 내용을 참조하기 위해 트리거내에서 사용할수있는 임시테이블인 Old와 New를 지원한다.
- 트리거의 수행시점과 Old , New는 관계가 없다.
- 트리거내에서는 적용테이블의 내용에 변경연산을 수행할수 없다. 다른테이블은 가능하다.
- 반드시 트리거가 수행된 후 무결성 제약조건의 check가 수행된다.
위의 예시에서보면 :New.속성이 있다. 이는 트리거가 적용되는 테이블에 대해 참조가능한 임시테이블을 제공하는 방법이다.
insert의 경우 삽입된후인 New만존재하며, delete는 삭제되기전인 Old만존재하고, update는 Old와 New 모두 존재한다.
insert에서 old를참조하거나 delete에서 new를 참조할때 값이 없으면 null이 나오게되지 오류가 발생하지는 않는다.
또한 이들은 after/before와 같이 트리거의 수행시점과는 전혀 관계가 없다. before insert라고해서 new가 없지 않다는것이다.
트리거내에서는 적용하는 테이블에 대한 변경연산을 수행할수 없다. 이는 무한루프의 가능성이 발생할수 있기 때문이다.
트리거는 변경연산이 발생하면 수행되는데, 트리거내에서 또 적용테이블에 변경연산을 수행하면 또다시 트리거를 수행하는 … 무한루프가 발생할 여지가 있기 때문이다.
트리거는 before/after/instead of 이렇게 세가지의 수행시점으로 나뉘는데 이는 반드시 개체혹은 참조무결성 제약조건과 같은 constraints들을 수행하기 전에 먼저 트리거가 수행된다.
즉, 트리거가 수행된후 제약조건을 check 하기때문에 내부의 로직에따른 제약조건위반을 걱정할 필요는 없다.
댓글남기기