[Cloud Wave] PL/SQL & 프로시저 & 사용자함수 & 트리거

PL/SQL

1. 오라클에서 만든 문법, 프로그래밍 처리가 가능

2. 형식

[DECLARE 변수 선언]
BEGIN
	수행할 PL/SQL
END;

3. 변수에 저장할 값을 출력하기 위해 서버 출력을 on 해야함

SET SERVEROUTPUT ON;

변수선언

1. 대입연산자 (:=) 사용
2. 종류
   • 스칼라 변수 → 타입을 직접 지정
   • 참조변수 → 특정 칼럼의 타입 사용
   • 레코드 변수 → 2개 이상의 칼럼을 합쳐서 하나의 타입으로 지정
   • 행 변수 → 행 전체 데이터 저장

SET SERVEROUTPUT ON;

--서버출력확인
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
END;

--

--스칼라 변수 사용
DECLARE NAME VARCHAR2(10 BYTE);
        GRADE NUMBER(3);
BEGIN
        NAME := '홍길동';
        GRADE := 4;
        DBMS_OUTPUT.put_line('이름은' || NAME || '입니다');
        DBMS_OUTPUT.put_line('학년은' || GRADE || '입니다');
END;

--참조변수 사용
DECLARE 
    SNAME STUDENT.NAME%TYPE;
    SGRADE STUDENT.GRADE%TYPE;
    SHEIGHT STUDENT.HEIGHT%TYPE;
BEGIN
    SELECT NAME, GRADE, HEIGHT
        INTO SNAME, SGRADE, SHEIGHT
        FROM STUDENT
        WHERE STUDNO = 9611;
        DBMS_OUTPUT.PUT_LINE(SNAME || '은' || SGRADE || '학년이고, 키는' || SHEIGHT || 'CM');
END;

 

프로시저(Procedure)

1. 하나의 프로시저에 여러개 쿼리문 작성해서 처리할 수 있음

2. 여러개 쿼리문이 필요한 서비스를 프로시저로 작성해서 호출하여 사용

3. 형식

CREATE [OR REPLACE] PROCEDURE 프로시저명 [(매개변수)]

IS 또는 AS

변수 선언

BEGIN

본문

[예외처리]

END;

4. 작성된 프로시저는 다음과 같은 형식으로 실행

EXECUTE 프로시저();

 

 

-- IN 입력 매개변수 OUT 출력 매개변수
CREATE OR REPLACE PROCEDURE PROC1 (S_STUDNO IN STUDENT.STUDNO%TYPE)
IS
    S_NAME STUDENT.NAME%TYPE;
    S_GRADE STUDENT.GRADE%TYPE;
    S_HEIGHT STUDENT.HEIGHT%TYPE;
BEGIN
    SELECT NAME, GRADE, HEIGHT
        INTO S_NAME, S_GRADE, S_HEIGHT
        FROM STUDENT
        WHERE STUDNO = S_STUDNO;
        DBMS_OUTPUT.PUT_LINE(S_NAME || '은' || S_GRADE || '학년이고, 키는' || S_HEIGHT || 'CM');
END;

EXECUTE PROC1 (9611);

 

사용자 함수

1. 어떤 값을 반환할 때 사용하는 데이터베이스 객체
2. 실제로 함수를 만들어서 사용하는 개념
3. RETURN 값의 존재
4. 함수의 결과값을 확인할 수 있도록 SELECT 문 많이 사용
5. 형식

CREATE OR REPLACE FUNCTION 함수명[(매개변수)]
RETURN 반환타입
IS 변수 선언
BEGIN
	함수본문
	예외처리
END;

 

EX)

CREATE OR REPLACE FUNCTION FUNC1
RETURN VARCHAR -- 반환타입에 크기를 명시하지 않음
IS
BEGIN
    RETURN 'HELLO WORLD';
END;

SELECT FUNC1 () FROM DUAL;

 

트리거 

1. DML(INSERT, UPDATE, DELETE) 작업 전/후 자동으로 실행되는 데이터베이스 객체
2. 행(ROW) 단위로 트리거 동작
3. 종류
   1. 비포 트리거:DML 동작 전 수행
   2. 애프터 트리거: DML 동작 후 수행
4. 형식

CREATE OR REPLACE TRIGGER 트리거명
BEFORE | AFTER
INSERT OR UPDATE OR DELETE -- 3개 다에 대해서 하고 싶으면 다음과 같이 쓰면 됨 아니면 그냥 하나만 써도 됨
ON 테이블 명
FOR EACH ROW
BEGIN
	본문 --트리거시 작동시키고자 하는 행동들 작성
END;

 

EX)

CREATE OR REPLACE TRIGGER TRIG1
AFTER
UPDATE
ON GOGAK
FOR EACH ROW
BEGIN 
    DBMS_OUTPUT.PUT_LINE('DATA UPDATED');
END;
/

UPDATE GOGAK SET POINT = 0 WHERE GNO = 20110001;