oracle은 sql에서 확장된 pl(procedural language)/sql을 사용한다. pl/sql 프로그램의 종류는 크게 프로시저(procedure), 함수(function), 트리거(trigger)가 있다. 프로시저와 함수는 비슷하지만 다른 점이 있다. 프로시저는 return이 없지만 함수는 return이 있다는 점이다.
프로시저는 프로시저명을 정하여 저장한 뒤 나중에도 계속해서 호출해서 사용할 수 있는 형태지만, 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 : 말 그대로 끝을 알려주는 예약어
프로시저는 앞서 말한 것처럼 저장한 뒤 계속 호출해서 사용할 수 있는 형태다. 또한 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 : 프로시저 작성을 닫는 예약어
프로시저 실행
select * from user_source; : 현재 저장된 프로시저들을 확인할 수 있는 명령어다.
함수도 위에서 설명한 declare, 프로시저와 굉장히 비슷하다. 비슷한 부분의 설명은 생략하고 다른 점만 설명한다.
CREATE OR REPLACE FUNCTION 함수명
(파라미터명 파라미터타입)
RETURN -- 이 부분이 프로시저와 다른 부분
return타입
IS
변수명 변수타입:= 변수값;
BEGIN
작업1;
작업2;
RETURN return할값; -- 이 부분이 프로시저와 다른 부분
END;
함수 구조
CREATE OR REPLACE TRIGGER 트리거명
BEFORE/AFTER 트리거를실행시킬작업 ON 테이블명
FOR EACH ROW
WHEN 조건
BEGIN
작업1;
작업2;
END;
트리거 구조
마찬가지로 반복되는 부분은 설명 생략한다.
개인적으로 dbeaver에서 오라클을 사용하면서 생각대로 되지 않는 부분들이 꽤 많아서 고생을 많이 했다. 혹시나 도움이 될 수도 있으니 적어둔다.
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;
위에 정리해놓은 함수형식을 이용하면 sqlplus에서는 문제없이 돌아가지만 dbeaver에서는 하나의 함수가 begin예약어를 기준으로 2개로 나뉘어 인식되는 문제생겨 제대로 된 함수 선언이 불가하다. 아직 왜 그런지 모르겠다.??????
해결방법 : 완벽한 해결은 아닌것 같지만 일단 실행방법을 찾았다. 커서를 함수 자체에 올리고 ctrl+enter를 입력하는 방식을 썼을 때는 에러가 생기지만, 함수 전체에 드래그를 한 다음 ctrl+enter를 입력하니 문제없이 실행되었다.