PL/SQL

[구디 아카데미]

✅ PL/SQL (Procedural language/SQL)

PL/SQL : 오라클에서 제공하는 프로그래밍 언어이며, 일반 프로그래밍 언어적인 요소를 다 가지고 있고
데이터베이스 업무를 처리하기 위한 최적화된 언어
-> SQL은 비절차적 언어이지만 PL/SQL은 SQL을 확장한 절차적(Procedural) 언어이다

• 익명 블록을 이용하기!
  -> BEGIN~ END : /구문 을 사용하는 것 (재사용 불가능)
• PROCEDUREM , FUNCTION 객체로 생성해서 이용
  -> OBJECT안에 작성된 PL/SQL * 생성된 OBJECT 명으로 재사용이 가능

✅ 프로시저 문법

✅ 익명블록

• PL/SQL 구문은 크게 3가지로 나뉨

(1) : [선언부] : DECLARE 약어를 사용하여 변수, 상수 선언
변수선언방법 : 변수명타입(기본타입,참조타입, ROWTYPE, TALBE, RECODE);
(2) : [실행부] : BEGIN 구문작성 END : 조건문, 반복문 등 실행할 내용에 대해 작성하는 구문
(3) : [예외처리부] = 처리할 예외 가 있을때 작성하는 구문

• [선언부] DECLARE 부분에서 사용되는 자료형의 종류

기본자료형 : 오라클에서 제공하는 TYPE들 (NUMBER, VARCHAR2, CHAR, DATE....)
상수 : CONSTANT 키워드 사용 ex) count CONSTANT NUMBER();
참조형자료형 : 테이블의 특정컬럼 에 설정된 타입을 불러와 사용
ROWTYPE : 테이블의 한개 ROW 를 저장할 수 있는 타입, 타입을 생성해서 사용

 emp_row EMPLOYEES%ROWTYPE; -> EMPLOYEES 테이블의 타입으로 저장가능
 emp_row.first_name, emp_row.last_name, emp_row.job_id

TABLETYPE : 자바의 배열 과 비슷한 타입 -> 인덱스 번호가 있고, 한개 타입만 저장이 가능
RECORDE : 자바의 클래스 와 비슷한 타입 -> 멤버변수가 있고, 다수 타입이 저장가능

	TYPE emp_type IS RECORD (first_name VARCHAR2(10),
    						last_name VARCHAR2(10),
                            job_id VARCHAR(10));
    emp_record emp_type;

DECLARE
    V_EMPNO VARCHAR2(20);
    V_EMPNAME VARCHAR2(15);
    V_AGE NUMBER := 19;       //  := 대입연산  
BEGIN    
    V_EMPNO := '010224-1234567';
    V_EMPNAME := '유병승';
    DBMS_OUTPUT.PUT_LINE(V_EMPNO);   // DBMS_OUTPUT.PUT_LINE = 자바의 System.out.print와 동일
    DBMS_OUTPUT.PUT_LINE(V_EMPNAME);
    DBMS_OUTPUT.PUT_LINE(V_AGE);
END;
/ 


// 참조형 자료형 이용하기
DECLARE
    V_EMPID EMPLOYEE.EMP_ID%TYPE;  // EMPLOYEE 테이블의 EMP_ID 컬럼의 자료형 타입을 참조해서 가져옴
    V_SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
    V_EMPID := '200';
    V_SALARY := 1000000;
    DBMS_OUTPUT.PUT_LINE(V_EMPID||' : '||V_SALARY);
    // SQL문과 연동하여 처리하기  (반드시 INTO를 넣어줘야함)
    SELECT EMP_ID, SALARY
    INTO V_EMPID, V_SALARY  
    FROM EMPLOYEE
    WHERE EMP_ID = '201';
    DBMS_OUTPUT.PUT_LINE(V_EMPID||' '||V_SALARY); 
END;
/


// ROWTYPE 
DECLARE
    V_EMP EMPLOYEE%ROWTYPE;  // ROWTYPE으로 변수 변수 선언 (선택한 테이블의 원하는 컬럼들을 접근 가능)
    V_DEPT DEPARTMENT%ROWTYPE;
BEGIN
    SELECT *
    INTO V_EMP
    FROM EMPLOYEE
    WHERE EMP_ID = '&사원번호';   // 사원번호 입력받기
    // ROWTYPE의 각 컬럼을 출력하려면 .연산자를 이용해서 컬럼명으로 접근한다.
    
    DBMS_OUTPUT.PUT_LINE(V_EMP.EMP_ID||' '||V_EMP.EMP_NAME||' '||V_EMP.SALARY||' '||V_EMP.BONUS);
    SELECT *
    INTO V_DEPT
    FROM DEPARTMENT
    WHERE DEPT_ID= V_EMP.DEPT_CODE;   // 컬럼변수를 통해서 다른 SELECT문에 넣을수 있다
    DBMS_OUTPUT.PUT_LINE(V_DEPT.DEPT_ID||' '||V_DEPT.DEPT_TITLE||' '||V_DEPT.LOCATION_ID);
END;
/


// 테이블 타입
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)); // 100
    DBMS_OUTPUT.PUT_LINE(MYTABLE_ID(2)); // 200
    DBMS_OUTPUT.PUT_LINE(MYTABLE_ID(3)); // 300
    
    FOR K IN (SELECT EMP_ID FROM EMPLOYEE) LOOP
        I:=I+1;
        MYTABLE_ID(I):=K.EMP_ID;
    END LOOP;
    FOR J IN 1..I LOOP
        DBMS_OUTPUT.PUT_LINE(MYTABLE_ID(J)); // EMPLOYEE 테이블의 모든 회원들의 ID 출력
    END LOOP;
END;
/


// RECODE 타입 활용하기
// 클래스와 유사
DECLARE
    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 조건식 
//  THEN : 조건식이 TRUE일때 THEN에 있는 구문이 실행됨. 
// END IF; 
DECLARE
    V_SALARY EMPLOYEE.SALARY%TYPE; // V_SALARY의 자료형은 EMPLOYEE테이블의 SALARY 컬럼 자료형으로 지정
BEGIN
    SELECT SALARY
    INTO V_SALARY
    FROM EMPLOYEE
    WHERE EMP_ID = '&사원번호'; // 입력한 사원번호의 연봉을 V_SALARY에 저장
    
    IF V_SALARY > 3000000 
        THEN DBMS_OUTPUT.PUT_LINE('많이 받으시네요!');
    END IF;    
END;
/


// IF 조건식
//  THEN 실행구문
//  ELSE 실행구문
// END IF;
CREATE TABLE HIGH_SAL(  // HIGH_SAL 테이블 생성
    EMP_ID VARCHAR2(20) REFERENCES EMPLOYEE(EMP_ID),
    SALARY NUMBER 
);

CREATE TABLE LOW_SAL(  // 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;
/


// IF 조건식 
//	THEN 실행구문
// ELSIF 조건식 
//  THEN 실행구문
// ELSE 실행구문
// END IF
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:='임원';
    ELSE MSG:='사원';
    END IF;
    INSERT INTO MSGTEST VALUES(V_EMP_ID, MSG);
    COMMIT;
END;
/


// CASE문 이용하기
DECLARE
    NUM NUMBER;
BEGIN
    NUM:='&수';  // 숫자 입력받아서 CASE문 ㅓ리
    CASE 
        WHEN NUM >10
            THEN DBMS_OUTPUT.PUT_LINE('10초과');
        WHEN NUM > 5
            THEN DBMS_OUTPUT.PUT_LINE('10~5사이 값');
        ELSE DBMS_OUTPUT.PUT_LINE('5미만');
    END CASE;
END;
/



// 기본반복문 LOOP예약어를 이용
// FOR, WHILE문이 있음
DECLARE
    NUM NUMBER := 1;
    RNDNUM NUMBER;
BEGIN
    LOOP // 반복문 시작
        DBMS_OUTPUT.PUT_LINE(NUM);
        // 오라클에서 랜덤값 출력하기
        RNDNUM := FLOOR(DBMS_RANDOM.VALUE(1,10)); // 1~10 사이의 난수 출력
        DBMS_OUTPUT.PUT_LINE(RNDNUM);
        INSERT INTO BOARD VALUES(SEQ_BASIC.NEXTVAL,'제목'||RNDNUM,'CONTENT'||RNDNUM,'작성자'||RNDNUM,SYSDATE);
        NUM:=NUM+1;  // NUM과 1씩 증가
        IF NUM>100  // NUM이 100보다 커지면 반복문 멈춤
            THEN EXIT;  // BREAK문과 동일
        END IF;
    END LOOP; // 여기까지가 반복문 구문
    COMMIT;
END;
/


// WHILE문
// WHILE 조건문 LOOP
// 실행구문
// END LOOP;
// /
DECLARE 
    NUM NUMBER := 1;
BEGIN
    WHILE NUM <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE(NUM); // 1 ~ 10 까지 출력
        NUM:=NUM+1;
    END LOOP;
END;
/


// FOR 변수 IN 범위(시작..끝) LOOP
// END LOOP;
BEGIN 
    FOR N IN 1..10 LOOP // 범위 1 ~ 10
    DBMS_OUTPUT.PUT_LINE(N);
    END LOOP;
END;
/

//FOR 변수 IN (SELECT문) LOOP
//END LOOP;
BEGIN
    FOR EMP IN (SELECT * FROM EMPLOYEE) LOOP
        DBMS_OUTPUT.PUT_LINE(EMP.EMP_ID||EMP.EMP_NAME||EMP.SALARY||EMP.DEPT_CODE||EMP.JOB_CODE);
        IF EMP.SALARY > 3000000 // 모든 사람을 조회해서 각 사람의 연봉에 따라 각 테이블로 삽입
            THEN INSERT INTO HIGH_SAL VALUES(EMP.EMP_ID,EMP.SALARY);
        ELSE INSERT INTO LOW_SAL VALUES(EMP.EMP_ID,EMP.SALARY); 
        END IF;
        COMMIT;
    END LOOP;
END;
/

✅ PROCEDURE, FUNCTION

CREATE OR REPLACE PROCEDURE out_emp(
    emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    out_str OUT VARCHAR2
) AS
    emp_name VARHCAR2(20);
BEGIN
    SELECT first_name || ' ' || last_name INTO emp_name
    FROM employees WHERE employee_id = emp_id;
    IF emp_id = NULL THEN
        out_str := '직원: 없음';
    ELSE
        out_str := '직원: ' || emp_name;
    END IF;
END;


DECLARE 
    out_str VARCHAR2(30);
BEGIN   
    out_emp(100, out_str);
    DBMS_OUTPUT.PUT_LINE(out_str);
END;

위 코드에서 예외처리 추가한 SQL

CREATE OR REPLACE PROCEDURE out_emp(
    emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    out_str OUT VARCHAR2
) AS
    emp_name VARCHAR2(20);
BEGIN
    SELECT first_name || ' ' || last_name INTO emp_name
    FROM employees WHERE employee_id = emp_id;

    out_str := '직원: ' || emp_name;
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            out_str := '직원: 없음';
END;

CREATE TABLE EMP_DEL
AS SELECT * FROM EMPLOYEE;  // EMP_DEL 테이블 하나 생성

CREATE OR REPLACE PROCEDURE EMP_DEL_PRO // EMP_DEL_PRO 함수 정의 (프로시저 저장)
IS
BEGIN
    DELETE FROM EMP_DEL; // EMP_DEL 테이블 삭제
    COMMIT;
END;
/

EXEC EMP_DEL_PRO; // 함수 호출   (EXEC 프로시저명) -> 저장된 프로시저 실행하기
SELECT * FROM EMP_DEL; // 비어있는 것을 확인할 수 있다


CREATE OR REPLACE PROCEDURE EMP_INSERT  // EMP_INSERT 함수 정의 (프로시저 저장)
IS
BEGIN
    FOR EMP IN (SELECT * FROM EMPLOYEE) LOOP
        INSERT INTO EMP_DEL  // EMP_DEL 테이블에 모든 데이터 저장
        VALUES(EMP.EMP_ID, EMP.EMP_NAME, EMP.EMP_NO, EMP.EMAIL, EMP.PHONE,
                EMP.DEPT_CODE, EMP.JOB_CODE, EMP.SAL_LEVEL, EMP.SALARY, EMP.BONUS,
                EMP.MANAGER_ID, EMP.HIRE_DATE,EMP.ENT_DATE, EMP.ENT_YN); 
    END LOOP;
    COMMIT;
END;
/
// 프로시저 정의로 한 문장으로 데이터들을 쉽게 수정할 수 있다!
EXEC EMP_INSERT;  // 모든 데이터 삽입
EXEC EMP_DEL_PRO; // 모든 데이터 삭제


// 프로시저의 매개변수 활용하기
// IN 매개변수 : 프로시저 실행시에 필요한 데이터를 받는 매개변수 * 일반적인 매개변수
// OUT 매개변수 : 호출한곳에서 지정한 변수에 데이터를 대입해주는 매개변수를 의미 (RETURN 값)
CREATE OR REPLACE 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;
/

EXEC PRO_SELECT_EMP(201,:EMP_NAME);  // 201 (매개변수) , EMP_NAME : 리턴된값
PRINT EMP_NAME;

// FUNCTION 오브젝트
// 매개변수로 받은 문자열의 길이를 반환해주는 함수
CREATE OR REPLACE FUNCTION MYFUNC(V_STR VARCHAR2)
RETURN NUMBER
IS
    V_RESULT NUMBER;
BEGIN
    SELECT LENGTH(V_STR)
    INTO V_RESULT
    FROM DUAL;
    RETURN V_RESULT;
END;
/

SELECT MYFUNC('유병승') // 3
FROM DUAL;
SELECT MYFUNC(EMAIL) // 모든 회원들의 이메일 길이만큼 출력
FROM EMPLOYEE;


// 매개변수로 EMP_ID를 받아서 연봉을 계산해주는 함수 만들기
CREATE OR REPLACE 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; // EMP_ID가 200인 회원의 연봉 출력됨

프로시저와 함수 차이