✔️ PL/SQL이란?
PL/SQL : SQL의 확장된 개념, 절차적 프로그래밍 언어
PL/SQL 프로그램의 종류는 Procedure, Function, Trigger 의 세가지로 나뉜다.
프로시저와 함수, 트리거의 기본 생성 문법과 생성 구조에 대해 알아보자.
- 프로시저
CREATE [OR REPLACE] PROCEDURE 프로시저명
( 매개변수명1 [ IN || OUT || INOUT ] 데이터타입,
매개변수명2 [ IN || OUT || INOUT ] 데이터타입 ... )
IS||AS
변수, 상수 등 선언 ( 선언부 )
BEGIN
실행 문장 ( 실행부 )
EXCEPTION 문장 //필수아님
END;- 함수
CREATE [OR REPLACE] FUNCTION 함수명 ( 매개변수명1 매개변수1타입,
매개변수명2 매개변수2타입 ... )
RETURN 데이터타입
IS||AS
변수, 상수 등 선언 ( 선언부 )
BEGIN
실행 문장 ( 실행부 )
RETURN 반환값 //필수
EXCEPTION 문장 //필수아님
END;- 트리거
CREATE [OR REPLACE] TRIGGER 트리거 이름
BEFORE||AFTER
event 1[ or event2 or ...]
ON
table_name || view_name
[referencing old or new]
[for each row] //이 옵션이 있으면 행 트리거
trigger body(PL/SQL 문장)선언 문법을 비교해보면 함수와 프로시저의 구조는 매우 비슷한 것을 확인할 수 있다.
( 참고 : [ Oracle ] 프로시저와 함수의 차이 )
- Procedure(프로시저)
CREATE [OR REPLACE] PROCEDURE 프로시저명
IS
변수 선언부
BEGIN
실행부
END;
/ -> sqlplus에서 실행시 필요프로시저는 user_source라는 오라클의 사전 테이블에 정보가 등록되며 select * from user_source; 문장을 통해 확인할 수 있다.
프로시저에 이름을 부여해서 DB에 사용자 정의 기능을 등록하고, 필요한 때에 사용 가능한 것을 저장 프로시저라고 한다.
학습용으로 제공하는 emp 와 dept table을 이용하여 프로시저를 만들어보자.
emp 의 정보를 받아온 emp01 table의 부서 번호가 20인 모든 사원의 직업을 의사(Doctor)로 바꿔보자.
CREATE OR REPLACE PROCEDURE updatejob
IS
BEGIN
UPDATE emp01 SET job='Doctor' WHERE deptno=20;
COMMIT;
END;프로시저 생성 후 select * from user_source; 를 통해 검색하면 목록중 updatejob이 있는 것을 확인할 수 있다.
이제 작성한 프로시저를 실행해보자.
- sqlplus에서 프로시저 실행
EXECUTE 프로시저명;
- DBeaver에서 프로시저 실행
CALL 프로시저명();emp01 의 정보를 다시 확인하면 부서번호가 20인 모든 사원의 직업이 Doctor로 변경된 것을 볼 수 있다.
이번에는 parameter를 활용하여 사번을 ( )로 입력받으면 해당 사원의 직업을 간호사(Nurse)로 변경하는, 동적 데이터를 반영하는 프로시저를 작성해보자.
//프로시저 작성
CREATE OR REPLACE PROCEDURE updatejob
IS
BEGIN
UPDATE emp01 SET job='Doctor' WHERE deptno=20;
COMMIT;
END;
//DBeaver에서 프로시저 실행
CALL updatejob2(7369);
//변경된 정보 조회
SELECT * FROM EMP01 WHERE EMPNO = 7369;사원번호가 7369인 사원 SMITH의 직업이 Nurse로 변경된 것을 확인할 수 있다.
프로시저에는 매개변수인 IN , OUT , INOUT , RETURN 4가지가 존재한다.
- IN : procedure 내에서 사용될 데이터의 변수
- OUT : procedure 내에서 발생된 데이터를 호출한 곳으로 제공(반환) 하는 데이터의 변수
- INOUT : procedure 내에서 사용될수도 있고, 호출한 곳으로 결과값을 반환도 가능한 변수
- RETURN : 프로시저의 강제 종료
매개변수를 사용하여 사원번호를 입력하면, 이름과 급여를 선언한 변수에 반환하는 기능의 프로시저를 작성해보자.
variable myname varchar2(10); //이름 반환할 변수
variable mysal number; //급여 반환할 변수
CREATE OR REPLACE PROCEDURE show_empinfo(
myempno IN emp.empno%TYPE,
myname OUT emp.ename%TYPE,
mysal OUT emp.sal%type)
IS
BEGIN
SELECT ename, sal
INTO myname, mysal
FROM emp
WHERE empno = myempno;
END;
CALL show_empinfo(7369, :myname, :mysal);사원번호가 7369인 사원의 이름과 급여 정보를 myname 과 mysal 에 반환했으니 해당 변수를 출력하여 결과를 확인해보자.
print myname; //SMITH
print mysal; //800- Function(함수)
CREATE [OR REPLACE] FUNCTION 함수명()
RETURN 반환타입
IS
변수 선언부
BEGIN
실행부
RETURN 값;
END;
/ -> sqlplus에서 실행시 필요오라클은 count , avg 와 같은 함수를 지원하는데 기본으로 지원하는 함수 외에 사용자 정의 함수를 만들어서 사용할 수 있다.
사용자 정의 함수도 이름을 부여하여 저장 후 이름으로 호출하여 사용할 수 있는데 이것을 저장 함수라고 한다.
단, 절대 기존 함수명과 중복될 수 없다!
select * from user_source; 문장을 통해 함수 목록 확인이 가능하다.
emp 테이블을 이용하여 사원 번호를 받으면 사원의 이름을 반환하는 함수를 만들어보자.
CREATE OR REPLACE FUNCTION searchname(num NUMBER)
RETURN varchar2
IS
newename emp.ename%TYPE;
BEGIN
SELECT ename
INTO newename
FROM EMP
WHERE empno = num;
RETURN newename;
END;
함수 호출은 SELECT 함수명() FROM 테이블명; 으로 할 수 있다.
그럼 위에서 작성한 searchname 함수 호출 시에는 어떻게 작성을 해야할까?
SELECT searchname(7369) FROM emp;위와 같이 함수를 호출하면 결과는 다음과 같다.
왜 이렇게 출력되는 것일까?
emp 테이블의 직원 수만큼 함수를 호출했기 때문에 논리적인 오류가 발생하여 다음과 같은 결과가 출력된 것이다.
Oracl에서는 함수 내부에 table명이 명시되어 있는 경우 논리적인 오류가 발생하기 때문에, dual 이라는 이름의 잉여 테이블(dummy table)을 함수 호출시 from 뒤에 함께 사용한다.
(오라클은 from 생략 불가이기 때문에 문법 오류를 피하기 위해 사용한다)
SELECT searchname(7369) FROM dual;다음과 같이 함수를 호출하면 정상적으로 실행된다.
- TRIGGER(트리거)
CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE
실행부
ON
테이블명
[referencing old or new]
[for each row]
PL/SQL문장트리거에는 문장 트리거와 행 트리거가 있다.
문장 트리거 : 컬럼 자체의 변화를 감지하여 실행
행 트리거 : 특정 데이터의 영향을 받는 행에 한해서만 트리거 실행
[for each row] 가 있으면 행 트리거가 된다.
- for each row : DML의 영향을 받는 모든 행
- EX ) 기존 row값을 새로운 table의 row로 이관 작업시에도 설정
-:OLD/:NEW사용
- 데이터 구조
1.:OLD: trigger가 처리한 레코드의 원래 값을 저장
2.:NEW: 새값 포함
트리거를 이용하기 전에, 관리자 계정(admin)으로 트리거 생성/변경/삭제 권한을 부여해야한다.
SCOTT 계정에 해당 권한을 부여해주었다.
grant create trigger to SCOTT;
grant alter any trigger to SCOTT;
grant drop any trigger to SCOTT;주문 가능 시간을 정해두고 해당 시간 안에만 주문이 가능하게 하는 문장 트리거를 작성해보자.
먼저 주문 정보 테이블을 새로 생성했다.
drop table order_table;
create table order_table(
no number,
ord_code varchar2(10),
ord_date date
);지금이 주문 가능한 시간인지 알아보기 위해, 현재 시간을 초 단위로 보여주는 SYSDATE 를 문자열로 반환하는 기능의 TO_CHAR 로 가공해보았다.
select sysdate from dual; -- 2022-06-07 21:56:26.000
select to_char(sysdate, 'HH24:MI') from dual; -- 21:56현재 시간이 24시간 단위로 표기되는 것을 확인할 수 있다.
주문 가능한 시간을 벗어나면 "주문 가능한 시간이 아닙니다." 라는 메시지를 출력하게 하려면 RAISE_APPLICATION_ERROR(고유번호, '메세지') 를 이용하면 된다.
CREATE OR REPLACE TRIGGER order_time
BEFORE INSERT
ON order_table
BEGIN
IF (to_char(sysdate, 'HH24:MI') NOT BETWEEN '9:00' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20100, '주문 가능한 시간이 아닙니다.');
END IF;
END;
//트리거 확인
insert into order_table values(1, '피자', sysdate); order_table 에 주문번호 1, 주문코드 '피자', 현재 시간을 입력하면 주문 가능한 시간이 아니라는 메시지가 나타난다.
주문 가능 시간을 0시 ~ 24시로 변경 후 다시 실행하면 정상적으로 주문이 실행된다.
이번에는 주문시, 주문 코드가 '피자'가 아니면 에러가 발생하는 행 트리거를 작성해보자.
CREATE OR REPLACE TRIGGER order_check
BEFORE INSERT
ON
order_table
FOR EACH ROW
BEGIN
IF (:NEW.ord_code) NOT IN ('피자') THEN
RAISE_APPLICATION_ERROR(-20300, '지금은 피자만 주문하실 수 있습니다.');
END IF;
END;
//트리거 확인
insert into order_table values(1, '피자', sysdate); //정상
insert into order_table values(1, '치킨', sysdate); //지금은 피자만 주문하실 수 있습니다.FOR EACH ROW 를 사용함으로써 insert되는 모든 row마다 검증 요청이 되게 설정이 가능하다.
이번에는 기존 테이블의 데이터가 업데이트 되는 경우, 백업 테이블에 기존 데이터를 이관하는 기능의 트리거를 작성해보자.
먼저 주문 정보 백업 테이블을 생성하고, 기존의 주문 정보 테이블에 3가지 주문 정보를 입력했다.
create table order_backup(
no number,
ord_code varchar2(10),
ord_date date
);
insert into order_table values(1, '피자', sysdate);
insert into order_table values(2, '피자', sysdate);
insert into order_table values(3, '피자', sysdate);백업 테이블로 정보를 옮기는 트리거 작성 후 확인
CREATE OR REPLACE TRIGGER backup_trigger
BEFORE UPDATE
ON order_table
FOR EACH ROW
BEGIN
INSERT INTO backup_order
VALUES (:OLD.no, :OLD.ord_code, :OLD.ord_date);
END;
//기존 테이블 정보 확인
select * from order_backup;
select * from order_table;
//주문번호가 1번인 목록의 주문 코드 업데이트 실행
update order_table set ord_code='치킨' where no=1;
//변경된 테이블 정보 확인
select * from order_backup;
select * from order_table;