[PL/SQL]익명 블록, PROCEDURE, FUNCTION

EUNJI LEE·2023년 4월 14일

sql

SQL

목록 보기

13/14

PL/SQL 구문

오라클 내에 있는 절차적 언어로 단일 명령어만 사용해서 데이터를 구분, 처리하는 SQL의 단점을 보완하여 SQL 내에서 변수의 정의, 조건 처리, 반복 처리 등을 지원한다.

PL/SQL을 이용해서 필요한 기능은 데이터베이스 내부에 구현하고 필요할 때 PL/SQL을 호출해서 사용할 수 있다. 결과를 다른 테이블에 다시 저장하거나 갱신하는 일련의 처리를 할 때 주로 사용한다.

PL/SQL 사용 방법

익명 블록

BEGIN ~ END;/ 구문을 사용하는 것으로 재사용이 불가능하다. 명칭을 호출해서 계속 사용할 수 없다. BEGIN~END;/는 자바에서의 중괄호와 같은 역할로 안에 로직을 작성하면 된다.
PROCEDURE, FUNCTION 객체로 생성

객체 안에 생성된 PL/SQL 구문으로 재사용이 가능하다. 명칭으로 호출해서 사용할 수 있다.

익명 블록

선언부 : DECLARE 예약어를 사용해서 변수, 상수를 선언한다. 변수명 타입(기본타입, 참조타입, ROW타입, TABLE, RECODE);
실행부 : 조건문, 반복문 등 실행할 내용에 대해서 작성한다. BEGIN 구문작성 END;/
예외처리부 : 처리할 예외가 존재하는 경우 작성하는 구문. 예외가 없는 경우 생략 가능하다.

--변수, 예외처리가 없는 단순 익명 블록
BEGIN
    DBMS_OUTPUT.PUT_LINE('첫 PL/SQL구문');
END;
/

💡 DBMS_OUTPUT.PUT_LINE()는 스크립트에 출력을 실행해주는 구문이다. 스크립트 출력창에 문구를 출력하기 위해서는 SET SERVEROUTPUT ON;를 먼저 선언해줘야 한다. 매 세션마다 실행해주는 명령어.

익명 블록의 자료형 종류에 따른 활용

기본 자료형

오라클에서 제공하는 타입들을 포함한다.

--기본자료형 선언과 이용
DECLARE
    V_EMPNO VARCHAR2(20);
    V_EMPNAME VARCHAR2(15);
    V_AGE NUMBER :=20;
    --> := 대입연산자
		-->변수를 선언하면서 값을 바로 대입할 수 있다.
BEGIN
    V_EMPNO:='230411_1';
    V_EMPNAME:='LEEJI';
    DBMS_OUTPUT.PUT_LINE(V_EMPNO);
    DBMS_OUTPUT.PUT_LINE(V_EMPNAME);
    DBMS_OUTPUT.PUT_LINE(V_AGE);
END;
/

참조 자료형

테이블에서 특정 컬럼에 설정된 타입을 불러와서 사용한다. 해당 테이블의 타입을 변경하면 불러와서 사용하는 타입도 그대로 변경된 채 가져오기 때문에 동적 타입이 된다.

--참조형 자료형 이용
DECLARE
    V_EMPID EMPLOYEE.EMP_ID%TYPE;
    V_SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
    V_EMPID:='200';
    V_SALARY:=1000000;
    DBMS_OUTPUT.PUT_LINE(V_EMPID||' : '||V_SALARY);

    --SQL문과 연동해서 처리
    SELECT EMP_ID, SALARY
    INTO V_EMPID, V_SALARY
    FROM EMPLOYEE
    WHERE EMP_ID=V_EMPID;

    DBMS_OUTPUT.PUT_LINE(V_EMPID||' '||V_SALARY);
END;
/

💡 이 때 SELECT문에 INTO절을 작성하지 않으면 오류가 발생한다. 데이터를 가져오기만 하고 처리 방법을 지정하지 않은 상태로 뒀기 때문이다.
⚠️ORA-06550: 줄 8, 열5:PLS-00428: 해당 SELECT 문에 INTO 절이 필요합니다.

ROWTYPE

테이블의 한 개 ROW를 저장할 수 있는 타입. 타입을 생성해서 활용한다.

DECLARE
    V_EMP EMPLOYEE%ROWTYPE;
    V_DEPT DEPARTMENT%ROWTYPE;
BEGIN
		--사원번호를 입력 받아서 일치하는 사원번호의 ROW를 V_EMP에 저장
    SELECT *
    INTO V_EMP
    FROM EMPLOYEE
    WHERE EMP_ID='&사원번호';
    --ROWTYPE의 각 컬럼 출력하기 위해 접근연산자 . 이용
		--&은 값을 입력받게 해주는 연산자
    -->실행순서가 SELECT>INTO로 간다. 조회된 데이터를 INTO에 해당하는 곳에 넣어줌
    DBMS_OUTPUT.PUT_LINE(V_EMP.EMP_ID||' '||V_EMP.EMP_NAME
					||' '||V_EMP.SALARY||' '||V_EMP.BONUS);

		--부서번호를 받아서 일치하는 부서번호의 ROW를 V_DEPT에 저장
    SELECT *
    INTO V_DEPT
    FROM DEPARTMENT
    WHERE DEPT_ID='&부서번호';
    DBMS_OUTPUT.PUT_LINE(V_DEPT.DEPT_ID||' '||V_DEPT.DEPT_TITLE
					||' '||V_DEPT.LOCATION_ID);
END;
/

TABLE TYPE

자바의 배열과 비슷한 타입으로 인덱스가 존재하고 하나의 타입만 저장할 수 있다. 인덱스 번호가 존재하기 때문에 인덱스 번호도 부여해야 한다.

DECLARE
    --타입 생성
    TYPE EMP_ID_TABLE IS TABLE OF EMPLOYEE.EMP_ID%TYPE
    INDEX BY BINARY_INTEGER;
    --변수명 타입;
    MYTABLE_ID EMP_ID_TABLE;
    I BINARY_INTEGER:=0;
BEGIN
    MYTABLE_ID(1):='100';
    MYTABLE_ID(2):='200';
    MYTABLE_ID(3):='300';
    DBMS_OUTPUT.PUT_LINE(MYTABLE_ID(1));
    DBMS_OUTPUT.PUT_LINE(MYTABLE_ID(2));
    DBMS_OUTPUT.PUT_LINE(MYTABLE_ID(3));
    --반복문 활용이 가능하다.
    FOR K IN (SELECT EMP_ID FROM EMPLOYEE) LOOP
--> K에 서브 쿼리를 이용해서 가져온 EMP_ID를 하나씩 넣는 반복문
        I:=I+1;
				-->LOOP가 돌 때 마다 INDEX를 증가
        MYTABLE_ID(I):=K.EMP_ID;
				-->MYTABLE_ID에 K에 저장한 EMP_ID를 하나씩 대입
    END LOOP;
    FOR J IN 1..I LOOP
-->J가 1부터 시작해서 I가 될 때까지 반복하는 구문
        DBMS_OUTPUT.PUT_LINE(MYTABLE_ID(J));
				-->해당 루프가 돌 때마다 인덱스번호 순서대로 MYTABLE_ID를 출력
    END LOOP;
END;
/

RECORD

자바의 클래스와 비슷한 타입으로 멤버 변수가 있고 다수의 타입을 저장할 수 있다.

DECLARE
--다양한 타입의 변수를 갖는 테이블을 RECODE 타입으로 선언해서 사용
    TYPE MYRECORD IS RECORD(
        ID EMPLOYEE.EMP_ID%TYPE,
        NAME EMPLOYEE.EMP_NAME%TYPE,
        DEPTTITLE DEPARTMENT.DEPT_TITLE%TYPE,
        JOBNAME JOB.JOB_NAME%TYPE
    );
    MYDATA MYRECORD;
BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
    INTO MYDATA
    FROM EMPLOYEE
        JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
        JOIN JOB USING(JOB_CODE)
    WHERE EMP_NAME='&사원명';
    DBMS_OUTPUT.PUT_LINE(MYDATA.ID||MYDATA.NAME||MYDATA.DEPTTITLE||MYDATA.JOBNAME);
END;
/

조건문

IF문

IF 조건식 THEN 조건식이 TRUE일 때 실행할 문장 END IF;

IF THEN TRUE인 경우 처리 구문 ELSE FALSE인 경우 처리 구문 END IF;

IF THEN ELSIF THEN ELSE END IF;

--월급에 따라 테이블에 나눠서 저장하는 기능
CREATE TABLE HIGH_SAL(
    EMP_ID VARCHAR2(20) REFERENCES EMPLOYEE(EMP_ID),
    SALARY NUMBER
);
CREATE TABLE LOW_SAL(
    EMP_ID VARCHAR2(20) REFERENCES EMPLOYEE(EMP_ID),
    SALARY NUMBER
);

DECLARE
    EMPID EMPLOYEE.EMP_ID%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT EMP_ID, SALARY
    INTO EMPID, SALARY
    FROM EMPLOYEE
    WHERE EMP_NAME='&사원명';
    
    IF SALARY>3000000
        THEN
        INSERT INTO HIGH_SAL VALUES(EMPID, SALARY);
        ELSE
        INSERT INTO LOW_SAL VALUES(EMPID, SALARY);
    END IF;
    COMMIT;
END;
/

--직급 코드에 따라 해당하는 직급명칭 나타내기
CREATE TABLE MSGTEST(
    EMP_ID VARCHAR2(20) REFERENCES EMPLOYEE(EMP_ID),
    MSG VARCHAR2(100)
);

DECLARE
    V_EMP_ID EMPLOYEE.EMP_ID%TYPE;
    V_JOBCODE EMPLOYEE.JOB_CODE%TYPE;
    MSG VARCHAR2(100);
BEGIN
    SELECT EMP_ID, JOB_CODE
    INTO V_EMP_ID, V_JOBCODE
    FROM EMPLOYEE
    WHERE EMP_ID='&사원번호';
    
    IF V_JOBCODE='J1'
        THEN MSG:='대표이사';
    ELSIF V_JOBCODE IN ('J2','J3','J4')
        THEN MSG:='임원';
		-->IF 조건을 만족하지 않고 ELSIF 조건을 만족할 때 임원 출력
    ELSE MSG:='사원';
		-->위 조건에 해당하지 않는 경우 전부 사원으로 출력
    END IF;
    INSERT INTO MSGTEST VALUES(V_EMP_ID, MSG);
    COMMIT;
END;
/

CASE

CASE WHEN 조건문 THEN TRUE일 때 실행 구문 [ELSE 조건문에 전부 해당하지 않는 경우] END CASE;

DECLARE
    NUM NUMBER;
BEGIN
    NUM:='&숫자';
    CASE
        WHEN NUM>10
            THEN DBMS_OUTPUT.PUT_LINE('10초과');
        WHEN NUM>5
            THEN DBMS_OUTPUT.PUT_LINE('10~6사이값');
        ELSE DBMS_OUTPUT.PUT_LINE('5이하 값');
    END CASE;
END;
/

WHILE

WHILE 조건문 LOOP 실행구문 END LOOP;

DECLARE
    NUM NUMBER:=1;
BEGIN
    WHILE NUM<=10 LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        NUM:=NUM+1;
    END LOOP;
END;
/

PROCEDURE

CREATE PROCEDURE 프로시저명 IS [변수선언] BEGIN 실행로직 END;/ 구문을 작성해서 프로시저를 생성할 수 있다. 저장된 프로시저 실행은 EXEC 프로시저명;으로 한다.

--DELETE 실행 후 바로 COMMIT하는 프로시저 생성
CREATE TABLE EMP_DEL
AS SELECT * FROM EMPLOYEE;

CREATE PROCEDURE EMP_DEL_PRO
IS
BEGIN
    DELETE FROM EMP_DEL;
    COMMIT;
END;
/

EXEC EMP_DEL_PRO;
SELECT * FROM EMP_DEL; -->프로시저 실행 후 조회하면 테이블의 데이터가 전부 삭제됨
-->DELETE 선언 후 COMMIT하지 않아도 PROCEDURE 사용만으로 한 번에 완료된다.

ROCEDURE의 매개변수

매개변수 IN : 프로시저 실행 시 필요한 데이터를 받는 매개변수
매개변수 OUT : 호출한 곳에서 지정한 변수에 데이터를 대입해주는 매개변수(리턴값과 비슷)

CREATE PROCEDURE PRO_SELECT_EMP(V_EMPID IN EMPLOYEE.EMP_ID%TYPE,
                            V_EMPNAME OUT EMPLOYEE.EMP_NAME%TYPE)
IS
    TEST VARCHAR2(20);
BEGIN
    SELECT EMP_NAME
    INTO V_EMPNAME
    FROM EMPLOYEE
    WHERE EMP_ID=V_EMPID;
END;
/

--전역변수 등록 후 매개변수로 받아온 값을 저장
VAR EMP_NAME VARCHAR2(20);
PRINT EMP_NAME;
EXEC PRO_SELECT_EMP(201,:EMP_NAME); 
-- : 바인드 연산
-->호출하는 쪽에서 바인드 연산자를 사용해서 값을 받는 매개변수를 지정해줘야한다.

FUNCTION

매개변수와 리턴값을 갖는다. SELECT문 내부에서 실행하고 출력값에 따라서 INSERT, UPDATE문에서도 사용 가능하다.
CREATE FUNCTION 함수명([매개변수선언]) RETURN 리턴타입 IS [변수] BEGIN ~ END;/

--매개변수로 EMP_ID를 받아서 연봉을 계산해주는 함수
CREATE FUNCTION SAL_YEAR(V_EMPID EMPLOYEE.EMP_ID%TYPE)
RETURN NUMBER
IS
    V_RESULT NUMBER;
BEGIN
    SELECT SALARY*12
    INTO V_RESULT
    FROM EMPLOYEE
    WHERE EMP_ID=V_EMPID;
    RETURN V_RESULT;
END;
/
SELECT SAL_YEAR(200) FROM DUAL;
SELECT EMP_NAME, SALARY, BONUS, SAL_YEAR(EMP_ID) AS 연봉
FROM EMPLOYEE;

EUNJI LEE

천천히 기록해보는 비비로그

이전 포스트

TRIGGER

다음 포스트