[DB][SQL][국비교육] Day 31

➰ PL/SQL 변수의 데이터타입

• 스칼라 변수
  SQL자료형을 이용하여 선언한 변수
  VARCHAR2(30), DATE, NUMBER...

• 래퍼런스 변수
  테이블의 특정 컬럼의 타입을 사용하여 선언한 변수
  emp.empno%TYPE, dept.dname%TYPE
emp%ROWTYPE : 테이블의 모든 컬럼을 이용한 변수타입

DECLARE
    v_deptrow dept%ROWTYPE;
BEGIN
    SELECT deptno, dname, loc
    INTO v_deptrow
    FROM dept
    WHERE deptno = 30;
    
    DBMS_OUTPUT.PUT(v_deptrow.deptno || chr(9));
    DBMS_OUTPUT.PUT(v_deptrow.dname || chr(9));
    DBMS_OUTPUT.PUT_LINE(v_deptrow.loc);
    
END;
/

• 복합타입 변수
  변수들을 임의로 조합하여 사용하는 변수

  • RECORD TYPE
    서로 다른 자료형을 하나로 모아서 사용할 때 적용하는 타입 ➡ 약간 class, map 같은 느낌
    - 변수 선언코드를 이용할 때 레코드타입을 적용할 수 있음

  • TABLE TYPE
    List처럼 동일 타입의 데이터를 여러개 사용할 때 적용하는 타입 👉🏻 컬렉션과 비슷한 구조
    - 변수선언 할 때 테이블 타입을 적용하여 사용

--복합타입 변수 / RECORD TYPE
DECLARE
    TYPE dept_type IS RECORD (
        deptno dept.deptno%TYPE
        , dname dept.dname%TYPE
        , loc dept.loc%TYPE
    );
    
    -- 둘이 갖고있는 데이터의 타입이 전부 동일
    v_dept dept_type;   --레코드타입 변수 선언
    v_deptrow dept%ROWTYPE;     --래퍼런스 타입 변수
BEGIN
    SELECT *
    INTO v_dept
    FROM dept
    WHERE deptno = 10;
    
    DBMS_OUTPUT.PUT(v_dept.deptno || chr(9));
    DBMS_OUTPUT.PUT(v_dept.dname || chr(9));
    DBMS_OUTPUT.PUT_LINE(v_dept.loc);    
END;
/


--복합타입 변수 / TABLE TYPE
DECLARE
    TYPE empno_type IS TABLE OF emp.empno%TYPE
    INDEX BY BINARY_INTEGER;
    
    TYPE ename_type IS TABLE OF emp.ename%TYPE
    INDEX BY BINARY_INTEGER;
    
    empno_list empno_type;
    ename_list ename_type;
    
    i BINARY_INTEGER := 0;
BEGIN
    -- 에러, SELECT하고 empno_list의 한 공간에 모든 empno이 들어감
--    SELECT empno
--    INTO empno_list
--    FROM emp;
    FOR emp_result IN (
        SELECT empno, ename
        FROM emp
        ORDER BY empno
    )
    LOOP
        i := i + 1;
        empno_list(i) := emp_result.empno;
        ename_list(i) := emp_result.ename;
    END LOOP;
    
    FOR idx IN 1..i
    LOOP
        DBMS_OUTPUT.PUT(empno_list(idx) || chr(9));
        DBMS_OUTPUT.PUT_LINE(ename_list(idx));
    END LOOP;
END;
/

--  테이블타입 변수의 데이터타입으로 래퍼런스 변수
DECLARE
    TYPE emp_type IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;
    
    emp_list emp_type;
    
    i BINARY_INTEGER := 0;
BEGIN
    -- 변수에 데이터 저장
    FOR emp_result IN (
        SELECT empno, ename
        FROM emp
        ORDER BY empno
    )
    LOOP
        i := i + 1;
        emp_list(i).empno := emp_result.empno;
        emp_list(i).ename := emp_result.ename;
    END LOOP;
    
    -- 조회결과 출력
    FOR idx IN 1..i
    LOOP
        DBMS_OUTPUT.PUT(emp_list(idx).empno || chr(9));
        DBMS_OUTPUT.PUT_LINE(emp_list(idx).ename);        
    END LOOP;
END;
/

---

🔍 PL/SQL 객체 유형

1. 익명블록, Anonymous Block
   곧바로 실행할 수 있는 PL/SQL 블록
   이름을 따로 지정하지 않고 사용

--익명블록, Anonymous Block
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
    DBMS_OUTPUT.PUT_LINE('안녕하세요!!!');
END;
/

2. 저장 프로시저, Stored Procedure
   익명블록에 이름을 붙여 객체로 저장한 것
   실행과 결과처리에 필요한 매개변수가 존재

3. 함수, Function
   입력(매개변수)과 리턴이 있는 PL/SQL 블록
   단일행 함수처럼 사용할 수 있음 -> SQL 코드에서 사용

4. 트리거, Trigger
   특정 상황에 자동으로 수행되도록 작성해놓는 PL/SQL 블록
   테이블의 데이터 변화에 따라 수행되도록 작성할 수 있음
   INSERT, UPDATE, DELETE가 테이블에 적용될 때 트리거가 동작하도록 만들 수 있음

➰ 저장 프로시저, Stored Procedure

PL/SQL 블록에 이름을 붙여 객체로 저장해놓고 사용

• 실행시키는 명령어로 EXECUTE를 사용
• 다른 PL/SQL 블록 내에서 사용할 수 있음
• 실행하기 위해 필요한 매개변수를 설정할 수 있음
• 프로시저 매개변수의 형태(특성)
  • IN
    입력값(전달인자)을 저장할 매개변수로 선언
    매개변수를 읽기 전용으로 설정
    PL/SQL 블록에서 해당 매개변수의 값을 변경할 수 없음
    단, 매개변수를 선언할 때 초기값을 설정하는 것은 가능
    전달인자 값이 없을 경우 초기값을 이용하여 초기화됨

--  IN : 읽기 전용 매개변수
--  부서번호를 전달받아 부서정보 출력하는 기능
CREATE OR REPLACE PROCEDURE proc_get_dept (
    p_deptno IN dept.deptno%TYPE := 10
)
IS
    v_dept dept%ROWTYPE;
BEGIN
    SELECT *
    INTO v_dept
    FROM dept
    WHERE deptno = p_deptno;
    
    DBMS_OUTPUT.PUT(v_dept.deptno || chr(9));
    DBMS_OUTPUT.PUT(v_dept.dname || chr(9));
    DBMS_OUTPUT.PUT_LINE(v_dept.loc);
END;
/

--  실행
EXECUTE proc_get_dept(10);
EXEC proc_get_dept();   --초기값이 설정되어 있는 경우 괄호를 안쓰거나 비워놔도 됨
BEGIN
    proc_get_dept;
END;
/

• OUT
  리턴값으로 사용될 매개변수에 적용
  PL/SQL 블록의 끝(END)을 만나는 시점에 OUT 매개변수에 저장된 값이 리턴됨

--  OUT
--  부서번호(IN)를 매개변수로 전달받아 부서이름(OUT)을 리턴하도록 설정
CREATE OR REPLACE PROCEDURE proc_get_dname (
    p_deptno IN dept.deptno%TYPE
    , p_dname OUT dept.dname%TYPE   --OUT은 초기값 설정 불가
)
IS
BEGIN
    SELECT dname
    INTO p_dname    
    FROM dept
    WHERE deptno = p_deptno;
    
    DBMS_OUTPUT.PUT_LINE(p_dname);
END;
/

--  리턴받을 변수 선언 및 프로시저 사용
DECLARE
    result dept.dname%TYPE;
BEGIN
    proc_get_dname(10, result);
END;
/

• INOUT
  IN, OUT 두 기능이 합쳐진 유형
  매개변수의 전달값을 불러올 수 있고, 값을 저장하려 리턴으로 사용할 수도 있음

--  IN OUT
CREATE OR REPLACE PROCEDURE proc_inout_test (
    p_var1 IN VARCHAR2  --저장하는 용이 아니라서 어떤 용도로 쓸건지 표시한 하면 돼서 자릿수 지정 필요하지 않음
    , p_var2 OUT VARCHAR2
    , p_var3 IN OUT VARCHAR2
)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('p_var1 : ' || p_var1);
    DBMS_OUTPUT.PUT_LINE('p_var2 : ' || p_var2);
    DBMS_OUTPUT.PUT_LINE('p_var3 : ' || p_var3);
    
--    p_var1 := 'A';    --IN매개변수에는 대입 불가
    p_var2 := 'B';
    p_var3 := 'C';    
END;
/

--  호출
DECLARE
    res_var1    VARCHAR2(30) := 'ALICE';
    res_var2    VARCHAR2(30) := 'BOB';
    res_var3    VARCHAR2(30) := 'CLARE';    
BEGIN
    proc_inout_test(res_var1, res_var2, res_var3);
    
    DBMS_OUTPUT.PUT_LINE('--- 프로시저 호출 후 ---');
    DBMS_OUTPUT.PUT_LINE('res_var1 : ' || res_var1);
    DBMS_OUTPUT.PUT_LINE('res_var2 : ' || res_var2);
    DBMS_OUTPUT.PUT_LINE('res_var3 : ' || res_var3);
END;
/

➰ 함수, Function

SQL구문에서도 사용할 수 있도록 만들어지는 PL/SQL 객체

• 단일 행 함수(Single Row Function) 형태로 호출해서 사용
• 함수를 호출한 자리에 리턴값을 되돌려 줌
• 매개변수 생략 가능 👉🏻 생략시 ()괄호까지 전부 생략
• RETURN은 생략 불가

--함수
CREATE OR REPLACE FUNCTION my_func
RETURN VARCHAR2
IS
BEGIN
    RETURN 'TEST';
END;
/
SELECT my_func() FROM dual;


CREATE OR REPLACE FUNCTION my_func2 (
    num NUMBER
    , data VARCHAR2
)
RETURN VARCHAR2
IS
    v_result NUMBER;    --지역변수
BEGIN
    v_result := num + 100;
    
    RETURN data || ' : ' || v_result;
END;
/

SELECT my_func2(1, 'a') FROM dual;
SELECT my_func2(1521, 'heool') FROM dual;

➰ 트리거, Trigger

테이블에 INSERT, UPDATE, DELETE가 수행될 때 같이 실행될 PL/SQL 객체

• BEFORE | AFTER : DML 코드 수행 이벤트가 발생했을 때 해당 코드 수행 전, 후에 트리거가 동작하도록 설정
• INSERT | UPDATE | DELETE : 트리거의 발동조건 이벤트 지정하기
  한 개 이상 설정해서 사용 👉🏻 OR를 이용하여 세 종류까지 선택 가능
• ON : 트리거 발동 조건을 감지할 테이블 설정
• FOR EACH ROW : 이벤트 발생한 행의 수만큼 트리거가 반복적으로 실행됨
  설정하지 않으면 테이블당 한번만 트리거가 실행됨

--트리거
CREATE OR REPLACE TRIGGER emp_trg
BEFORE INSERT ON emp
BEGIN
    DBMS_OUTPUT.PUT_LINE('Before INSERT INTO emp');
END;
/

INSERT INTO emp (empno, ename) VALUES (4105, 'TRIGGER');
INSERT INTO emp (empno, ename) VALUES (4104, 'TRIGGER');


--  emp테이블 INSERT 또는 UPDATE가 발생하면 동작하는 트리거 + FOR EACH ROW
CREATE OR REPLACE TRIGGER emp_trg2
BEFORE
    INSERT OR UPDATE
ON emp
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('TRIGGER!!');
END;
/

INSERT INTO emp (empno, ename) VALUES (4106, 'TRG');
UPDATE emp
SET ename = 'TRG DATA!'
WHERE empno BETWEEN 4000 AND 5000;

SELECT * FROM emp;


CREATE OR REPLACE TRIGGER update_emp_trg
BEFORE UPDATE ON emp
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE( :OLD.ename || ' : ' || :NEW.ename );
END;
/

UPDATE emp SET ename = 'UPDATE!!'
WHERE empno = 4101;

--  :OLD 키워드는 테이블의 기존 데이터 행     DELETE, UPDATE
--  :NEW 키워드는 테이블의 새로운 데이터 행    INSERT, UPDATE