[DB]Oracle

신동혁·2022년 8월 12일

DB

목록 보기

4/6

Oracle

1. PL/SQL

oracle은 sql에서 확장된 pl(procedural language)/sql을 사용한다. pl/sql 프로그램의 종류는 크게 프로시저(procedure), 함수(function), 트리거(trigger)가 있다. 프로시저와 함수는 비슷하지만 다른 점이 있다. 프로시저는 return이 없지만 함수는 return이 있다는 점이다.

2. 프로시저( Procedure )

2.1 Declare

프로시저는 프로시저명을 정하여 저장한 뒤 나중에도 계속해서 호출해서 사용할 수 있는 형태지만, declare는 한번 실행 후 소멸한다. 일회용 프로시저라고 생각할 수 있다. 그런 declaare에 관해 먼저 알아보자.


declare
	변수명 변수타입,사이즈 := 변수값;
begin
	작업1 ;
    작업2 ;
exception
    when 조건 then
        작업3 ;
    작업4 ;
end;

Declare 구조

declare : 변수 선언부로 변수를 선언할 수 있고 다른 부분에서는 변수를 선언할 수 없다. 변수값을 초기화시키고 싶으면 ':='을 이용한다. 값의 초기화는 필수가 아니다.
- %type : 변수의 타입을 지정하는 부분에서 특정 테이블의 칼럼(필드)과 같은 타입을 가지고 싶다면, '%type'을 이용하면 된다.
```
ex)        
변수명 테이블명.칼럼명%type := 변수초기화값;  마찬가지로 초기화는 필수가 아님.
```
- %rowtype : 위에서 설명한 %type과 비슷하지만 다른점은 %type은 한 가지 변수에 관한 한 가지 데이터타입을 받는 형식이라면, %rowtype은 여러가지 변수에 관한 여러 데이터타입을 받는 형식이다.
```
ex)        
변수명 테이블명%rowtype; : 테이블에 속한 칼럼들의 칼럼타입들을 똑같이 가지는 변수를 생성. 즉, 이 변수는 여러 칼럼에 대한 정보를 가지고 있다.
```
- 치환변수( &변수명 ): 실행 시마다 입력값을 받아 반영하는 동적 데이터 변수를 만들 수 있다. 현재 sqlplus에서는 문제없이 작동하지만 dbeaver에서는 치환변수를 사용할 수 없는 것으로 보인다. 이 부분에 대해서는 더 알아봐야겠다
```
ex)        
변수명 변수타입:= &동적변수명; : 동적변수를 받아 바로 선언된 변수를 초기화하는 코드다.     
```

begin : 변수를 초기화시키거나 새로운 값을 넣을 수도 있고, 원하는 내용을 출력할 수도 있다. 이외에도 핵심작업이 이루어지는 부분이다.

dbms_output.put_line( 출력내용 ); : 원하는 내용을 출력할 수 있는 명령어다. 출력내용 안에 들어가는 내용은 '||'를 이용해서 붙여 출력한다.

ex)        
dbms_output.put_line( 변수1,변수2 ); 이런식으로 작성하면 에러발생         
dbms_output.put_line( 변수1 || 변수2 ); 이런식으로 작성해야 변수1의 변수값과 변수2의 변수값이 이어져서 출력된다.

select into from where문 : declare에서 선언한 변수에 특정 테이블, 칼럼, 조건에 해당하는 값을 가져와 대입하고 싶다면 다음과 같이 sql문을 작성할 수 있다.
```
ex)       
SELECT 테이블칼럼명 INTO declare선언변수명 FROM 테이블명 WHERE 조건;
```

if문 : 조건에 따라 작업을 달리 할 수 있는 if문을 작성할 수 있다.

ex)      
if (조건1) then 작업1;      
			elsif (조건2) then 작업2;       
			else 작업3;      
end if;

반복문 : for, while, loop 등 반복문을 실행하는 방법은 여러가지 존재한다.

ex)      
-- loop사용
loop
    작업;
    exit when 탈출조건;
end loop;

-- while사용
while 반복조건 loop
    작업;
end loop;

-- for사용
for 변수명 in 시작숫자..끝숫자 loop
    작업;
end loop;

exception : 에러가 발생했을 때 프로그램이 멈추지 않게 하기 위한 장치다.
end : 말 그대로 끝을 알려주는 예약어

2.2 (저장)프로시저

프로시저는 앞서 말한 것처럼 저장한 뒤 계속 호출해서 사용할 수 있는 형태다. 또한 declare의 구조와 매우 흡사하다.(declare에서 설명한 부분과 같은 부분들은 생략했다)

create or replace procedure 프로시저명
( 파라미터명 in/out선택 파라미터타입)
is
    변수명 변수타입:= 변수값;
begin   
    작업1;
    작업2;
end;

프로시저 구조

create : 프로시저명을 정하고 파라미터의 이름,타입, in/out을 정하는 부분

in/out : 파라미터는 프로시저를 호출 시에 같이 넣어주는 값으로 무조건 in의 형태지 않을까 싶지만 out의 형태도 있다.

variable out파라미터받아줄변수명 변수타입;  -- 먼저 out파라미터값을 넣어줄 변수를 선언해줘야 한다.

create or replace procedure 예제프로시저1
(   파라미터명1 in 타입,
    파라미터명2 out 타입)
    ...
end;

-- 다음과 같이 프로시저가 선언되었을 때, 프로시저를 호출하는 형식은 다음과 같다.      
call 예제프로시저1(파라미터명1에들어갈값, :out파라미터받아줄변수명);
```

is : declare와 같은 역할이라고 보면 쉽다.
begin : 프로시저의 내용을 작성하는 부분
end : 프로시저 작성을 닫는 예약어
프로시저 실행
- execute 프로시저명;
- call 프로시저명;
- begin
  프로시저명;
  end;
select * from user_source; : 현재 저장된 프로시저들을 확인할 수 있는 명령어다.

3. 함수 ( Function )

함수도 위에서 설명한 declare, 프로시저와 굉장히 비슷하다. 비슷한 부분의 설명은 생략하고 다른 점만 설명한다.

CREATE OR REPLACE FUNCTION 함수명
(파라미터명 파라미터타입)
RETURN  -- 이 부분이 프로시저와 다른 부분
	return타입
IS
	변수명 변수타입:= 변수값;
BEGIN
    작업1;
    작업2;
	RETURN return할값; -- 이 부분이 프로시저와 다른 부분
END;

함수 구조

create
return : 이 부분에서 return값의 타입을 미리 정해준다. 이때 타입만 작성해야 한다! 타입사이즈까지 정해 놓으면 에러가 생긴다!
is
begin
return : 이 부분에서 return값을 정해준다.(변수명을 넣어주거나 직접 값 넣어줌)
end

4. 트리거 ( Function )

CREATE OR REPLACE TRIGGER 트리거명
BEFORE/AFTER 트리거를실행시킬작업 ON 테이블명
FOR EACH ROW 
WHEN 조건
BEGIN 
	작업1;
    작업2;
END;

트리거 구조
마찬가지로 반복되는 부분은 설명 생략한다.

create
before/after .. on ..
ex)
예를 들어 A라는 테이블에 데이터를 update하고 난 뒤마다 트리거를 실행시키겠다.
--> after update on A
for each row : 행단위 트리거로 만약 insert를 이용해 3개의 values를 넣는다면 각 행마다 begin에 대한 내용을 실행한다는 뜻이다.
when : 조건
begin
end

Dbeaver에서 오라클 사용 시 에러가 났던 부분들

개인적으로 dbeaver에서 오라클을 사용하면서 생각대로 되지 않는 부분들이 꽤 많아서 고생을 많이 했다. 혹시나 도움이 될 수도 있으니 적어둔다.

sql문에 공백이 있을 시 에러가 발생되는 문제점 확인
예를 들어 프로시저를 선언할 때 공백을 만나기 전까지는 문제없는 sql문으로 취급되다 공백이 생기면 에러가 발생하는 경우가 있었다. 찾아보니 공백을 delimiter로 간주하는 설정때문에 일어나는 오류일 수도 있다고 해서 해당 설정을 변경했더니 에러가 사라졌다.

윈도우 탭 클릭 (Dbeaver 상단 탭들 중에서)
설정 클릭
편집기 클릭
sql편집기 클릭
sql실행 클릭
Delimiters에 관한 부분에 'Blank line is statement delimiter'라는 항목이 체크되어 있을 것이다. 체크를 풀어준다!

sqlplus에서 오라클을 통해 프로시저나 함수를 선언하고 나면 'end;' 명령어 다음에 '/' 다음과 같이 슬래쉬를 통해 입력을 다했다는 표현을 해야 하지만 dbeaver에서는 슬래쉬를 적으면 오히려 에러가 발생한다.
프로시저를 실행하는 예약어로 'execute'나 'exec'을 사용하려 했지만 에러가 발생해, 결국 'call'이라는 명령어로 대체했다.(begin 프로시저명; end; 로도 호출 가능!)

CREATE OR REPLACE FUNCTION newfunc
RETURN varchar(20)
IS
abcd SCOTT.emp.empno%type -- 여기서끊김
BEGIN
	RETURN '999';
END;

SELECT * FROM emp;


SELECT newfunc FROM dual;