[DB]03-01.Oracle DB PLSQL BLOCK, IN, OUT, IN OUT

PL/SQL은 Procedual Language SQL의 약자로

프로시저라고 부른다.

PL은 SQL에서 변수 정의, 조건문, 제어문 등 프로그래밍 언어에서 사용하던 비슷한 기능, 함수들을 사용할 수 있다.

장점은 SQL문을 한번에 DB에 보내 처리하여 속도가 향상된다.

단점으로는 실행 주기 설정이 어렵고 SQL과 PL/SQL 형태만 실행 가능, DB 이외의 프로그램을 실행 할 수 없다.

실행할 작업과 이름을 지정한 부분을 PL/SQL BLOCK이라고 한다.

함수와 똑같이 매개변수를 받고 재사용 할 수 있는 BLOCK이다.

대개 반복 실행, 구현이 복잡한 트랜잭션을 실행하기 위해 생성한다.

CREATE OR REPLACE PROCEDURE [임의의 이름]

    IS

 -- 초기 변수 선언

        [변수 이름] [변수 타입] := [초기값];

    BEGIN

        [실행 명령어];

    END;

/

/, 슬래시로 닫아 마무리하여 PL/SQL BLOCK을 만든다.

가끔 프로시저는 주석문을 위 아래 같은줄 어디든 영향받을 때가 있는거 같은데 왜 그런지는 정확히 모르겠다.

가장 큰 특징으로는 Return 값이 따로 없기 때문에 UPDATE같은 구문으로 수정을 바로 해야함

그 외의 실행이라던지 접속 연결이라던지는 다음 코드에서 설명한다.

-- 1) 매개변수가 없는 프로시저
CREATE OR REPLACE PROCEDURE hello
    IS
        msg VARCHAR2(20) := 'Hello, world';
 -- 초기 변수 선언
    BEGIN
        DBMS_OUTPUT.PUT_LINE(msg);
    END;
/

-- 프로시저 종료
-- DBMS_OUTPUT 출력을 위한 구문(WEB_USER 연결시 딱 한 번만 설정 해주면 접속 종료할 때 까지 유지됨)
SET SERVEROUTPUT ON;

-- 프로시저 실행
-- EXEC [프로시저 이름]
EXEC hello;


-- 2) IN 매개변수가 있는 경우
-- IN 매개변수 : 인자값을 받는 변수, 변수형에는 크기를 따로 입력하지 않음.
-- 사원번호를 받아 특정 사원의 급여를 10% 올려주는 프로시저 생성
SELECT * FROM employees;
DESC employees;

SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE upSalary(eno IN NUMBER)
    IS
    BEGIN
        UPDATE employees SET salary = salary * 1.1 WHERE emp_no = eno;
        COMMIT;
    END;
/

EXEC upSalary(118);



-- 3) OUT 매개변수
-- 프로시저에는 반환(Return 값)이 없기 때문에 OUT 매개변수를 활용한다.
-- 사원 번호를 넣으면 사원명과 급여를 반환
-- 115번의 이름과 급여 출력
CREATE OR REPLACE PROCEDURE find_emp(v_eno IN NUMBER, v_fname OUT NVARCHAR2, v_sal OUT NUMBER)
    IS
    BEGIN
        SELECT first_name, salary
        INTO v_fname, v_sal
        FROM employees 
        WHERE emp_no = v_eno;
    END;
/
-- SELECT 인자들에 맞춰 INTO에 빼낼 값들을 타입과 자리를 맞춰서 기입
-- 실행은 기존과 다름
-- OUT 매개변수는 실행 전 받아 줄 변수(BIND, 바인드 변수)를 선언해야 한다.
-- 바인드 변수 선언 시 실제 컬럼 속성 보다 조금 더 여유있게 선언하는게 좋다.
VARIABLE v_fname NVARCHAR2(10);
VARIABLE v_sal NUMBER;
-- 실행
EXEC find_emp(115, :v_fname, :v_sal);

PRINT v_fname;
PRINT v_sal;



-- IN/OUT 매개변수
-- 매개변수로 시작하지만 종료시 OUT 변수 기능을 함
-- 일반적으로 프로시저가 다른 프로시저 호출 후 반환값을 사용 할 경우 활용
-- 사원번호를 넣으면 해당 사원의 급여 출력
CREATE OR REPLACE PROCEDURE find_sal(v_eno IN OUT NUMBER)
    IS
    BEGIN
        SELECT salary INTO v_eno FROM employees WHERE emp_no = v_eno;
    END;
/
-- 익명 프로시저, 일회용 프로시저, 저장이 안되고 한번 쓰고 사라짐
-- 위에 선언한 find_sal을 다시 이용
DECLARE 
    v_eno NUMBER := 115;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('eno = '||v_eno);
        find_sal(v_eno);
        DBMS_OUTPUT.PUT_LINE('salary = '||v_eno);
    END;
/

-- 프로시저 조회
SELECT object_name,object_type FROM USER_PROCEDURES;

-- 프로시저 삭제
DROP PROCEDURE hello;