하위^^

트리거는 사전적 의미로는 방아쇠를 의미한다

말 그대로 트리거는 방아쇠를 당겼을때 총이 나가는 것 처럼

트리거를 실행하면 안에 담겨있는 실행문이 그대로 실행되는 것이다.

VIEW에 대해서는 트리거를 이용할 수 없고 INSERT, UPDATE, DELETE문만 사용가능하다.

CREATE OR REPLACE TRIGGER 트리거 이름

[BEFORE|AFTER]

[INSERT|UPDATE|DELETE] ON 테이블 이름

[FOR EACH ROW] …

Pl/sql 실행문

와 같은 익숙한 구조로 만들어진다.

안의 옵션의 설명은

[BEFORE|AFTER] : 문장이 실행 되기 전/후에 실행된다.

[INSERT|UPDATE|DELETE] ON 테이블 이름 : 작동할 구문

[FOR EACH ROW] : 이 옵션은 행 트리거가 된다.

행/문장 트리거

행 Trigger     : 컬럼의 각 행의 데이터 변화가 생길 때마다 실행 (데이터 행 제어 O)

문장 Trigger  : 트리거 사건에 의해 단 한번만 실행(데이터 행 제어 X)

SET SERVEROUTPUT ON;

-- 사용 테이블 2개 생성

CREATE TABLE emp01(
    empno NUMBER(4) PRIMARY KEY,
    ename VARCHAR2(20),
    job VARCHAR2(20)
);

CREATE TABLE emp02(
    empno NUMBER(4) PRIMARY KEY,
    ename VARCHAR2(20),
    job VARCHAR2(20)
);

-- INSERT TRIGGER
-- emp01에 데이터가 들어가면 emp02에도 들어간다.
CREATE OR REPLACE TRIGGER tri_insert
    AFTER INSERT ON emp01
-- emp01에서 insert가 일어난 후에 작동하여라
    FOR EACH ROW
-- 각 행의 데이터를 변화를 감시, 감지되면 아래 구문을 실행
    BEGIN
        -- :NEW 는 새로 들어온 데이터를 의미
        INSERT INTO emp02 VALUES(:NEW.empno, :NEW.ename, :NEW.job);
    END;
/

-- 데이터 삽입
INSERT INTO emp01 VALUES(1, 'kim', '개발자');
INSERT INTO emp01 VALUES(2, 'lee', '디자이너');
INSERT INTO emp01 VALUES(3, 'park', '기획자');
-- 트리거 결과 확인
SELECT * FROM emp01;
SELECT * FROM emp02;



-- UPDATE TRIGGER
-- emp01이 수정되면 emp02도 수정된다.
CREATE OR REPLACE TRIGGER tri_update
    AFTER UPDATE ON emp01
    FOR EACH ROW
    BEGIN
        UPDATE emp02 SET ename = :NEW.ename, job = :NEW.job WHERE empno = :NEW.empno;
    END;
/

-- 데이터 변경
UPDATE emp01 SET ename = 'oh', job = '개발자' WHERE empno = 2;

-- 트리거 결과 확인
SELECT * FROM emp01;
SELECT * FROM emp02;




-- DELETE TRIGGER
-- emp01이 삭제되면 emp02도 삭제된다.
CREATE OR REPLACE TRIGGER tri_del
    AFTER DELETE ON emp01
    FOR EACH ROW
    BEGIN
        -- OLD는 이전 데이터를 의미하지만 DELETE문에서는 삭제할 자료를 의미
        DELETE FROM emp02 WHERE empno = :OLD.empno;
    END;
/

-- 데이터 삭제
DELETE FROM emp01 WHERE empno = 2;

-- 트리거 결과 확인
SELECT * FROM emp01;
SELECT * FROM emp02;


-- 트리거 조회
SELECT * FROM USER_TRIGGERS;
-- 트리거 소스 확인
SELECT * FROM USER_SOURCE WHERE type = 'TRIGGER' AND name = 'TRI_INSERT';
-- 트리거 삭제
DROP TRIGGER tri_insert;

이와같이 데이터의 변경을 감지 할 수 있으므로 백업같은 작업에 잘 사용한다.

암시적 커서

암시적 커서는 SQL문장이 처리되는 곳에 대한 익명의 주소

실행되는 모든 SQL문은 암시적인 커서가 생성되고, 그대로 이용 할 수 있다.

SQL%ROWCOUNT, 해당 SQL문에 영향을 받는 행의 수

SQL%FOUND, 해당 SQL문에 영향을 받는 행의 수가 한 개 이상일 때 TRUE

SQL%NOTFOUNT, 해당 SQL문에 영향을 받는 행의 수가 없을 때 TRUE

SQL%ISOPEN, 항상 FALSE, 암시적 커서가 열려있는지 여부

커서는 사용하고 꼭 닫아줘야 한다.

JAVA의 몇몇 클래스들 처럼...

결론으로 커서는 쿼리문을 주소로 담고있는것.

명시적 커서

명시적 커서는 직접 커서를 만들어 사용하는 것.

커서에는 한개 이상의 데이터가 담기게 되고 데이터를 OPEN, PATCH, CLOSE 등을 사용하여 다룬다.

CURSOR 커서이름 IS 실행 할 문장

CURSOR test_cursor IS SELECT * FROM dept

와 같은 형식으로 사용

SET SERVEROUTPUT ON;

-- 1) 암시적(묵시적) 커서
-- SQL%ROWCOUNT : 쿼리를 만족하는 갯수 추출
-- SQL%FOUND    : 쿼리를 만족하는 갯수가 있는가? T/F
-- SQL%NOTFOUND : 쿼리를 만족하는 갯수가 없는가? T/F

DECLARE
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT salary INTO v_emp.salary FROM employees WHERE emp_no = 115;
        IF SQL%FOUND THEN
        -- FOUND - 해당 문장 값이 존재하는가
            DBMS_OUTPUT.PUT_LINE('검색한 값이 있습니다.');
            DBMS_OUTPUT.PUT_LINE('115번 사원의 급여 : '||v_emp.salary);
            DBMS_OUTPUT.PUT_LINE('115번 사원의 수 : '||SQL%ROWCOUNT);
        ELSE
            DBMS_OUTPUT.PUT_LINE('검색한 값이 없습니다.');
        END IF;
    END;
/


SELECT * FROM dept;
-- 2) 명시적 커서
DECLARE
    CURSOR cur IS SELECT * FROM dept;
    v_dept dept%ROWTYPE;
    BEGIN
        OPEN cur;
        LOOP
            FETCH cur INTO v_dept.deptno, v_dept.deptname, v_dept.loc;
            DBMS_OUTPUT.PUT_LINE('부서번호 : '||v_dept.deptno);
            DBMS_OUTPUT.PUT_LINE('부서명 : '||v_dept.deptname);
            DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
            DBMS_OUTPUT.PUT_LINE('Count : '||cur%ROWCOUNT);
            EXIT WHEN cur%ROWCOUNT >= 5;
        END LOOP;
        CLOSE cur;
    END;
/

-- FOR IN LOOP
-- CURSOR의 OPEN,PATCH,CLOSE 가 필요 없다.
DECLARE
    CURSOR cur IS SELECT * FROM dept;
    v_dept dept%ROWTYPE;
    BEGIN
        FOR v_dept IN cur LOOP
            DBMS_OUTPUT.PUT_LINE('부서번호 : '||v_dept.deptno);
            DBMS_OUTPUT.PUT_LINE('부서명 : '||v_dept.deptname);
            DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
        END LOOP;
    END;
/

다른 언어의 기본적인 문법 중 반복문 역시 사용 가능하다.

LOOP, WHILE, FORLOOP 가 있다.

차이점은 따로 없으며 예제를 통해 간단히 확인하자

LOOP

SET SERVEROUTPUT ON;
-- LOOP문은 빠져나갈 조건이 만족 될 때까지 반복된다.

DECLARE
    i NUMBER(1) := 0;
    BEGIN
        LOOP
            i := i + 1;
            DBMS_OUTPUT.PUT_LINE(i);
            EXIT WHEN i > 5;
        END LOOP;
    END;
/

-- dept에 있는 데이터를 하나씩 불러온다. (전체 5개))
SELECT deptno, deptname, loc FROM dept WHERE deptno = 1;

DECLARE
    i NUMBER(1) := 0;
    v_val dept%ROWTYPE;
    BEGIN
        LOOP
            i := i + 1;
            SELECT deptno, deptname, loc INTO v_val.deptno, v_val.deptname, v_val.loc FROM dept WHERE deptno = i;
            DBMS_OUTPUT.PUT_LINE(v_val.deptno||'/'||v_val.deptname||'/'||v_val.loc);
            EXIT WHEN i > 4;
        END LOOP;
    END;
/

WHILE

-- WHILE 문, 브레이크를 하나 더 만들어 주는 것.
DECLARE
    i NUMBER(1) := 0;
    v_val dept%ROWTYPE;
    BEGIN
        WHILE i <= 5 LOOP
            i := i + 1;
            SELECT deptno, deptname, loc INTO v_val.deptno, v_val.deptname, v_val.loc FROM dept WHERE deptno = i;
            DBMS_OUTPUT.PUT_LINE(v_val.deptno||'/'||v_val.deptname||'/'||v_val.loc);
            EXIT WHEN i >= 5;
        END LOOP;
    END;
/

FOR LOOP

FOR문은 향상된 FOR문처럼 데이터를 가져오는데

DB의 데이터를 반복문을 통해 한번에 가져올 때 씀

SET SERVEROUTPUT ON;
-- for loop은 JAVA의 향상된 FOR문과 비슷하다
-- 덩어리에서 하나씩 쪼개어 변수에 담는다.
-- FOR[변수] IN [덩어리]


-- 숫자를 통해 반복
DECLARE
    BEGIN
        FOR i IN 1..10
        LOOP
            DBMS_OUTPUT.PUT_LINE(i);
        END LOOP;
    END;
/

-- 반복으로 찾을 SELECT항목
SELECT ename, hiredate FROM emp WHERE hiredate > TO_DATE('15/01/02') ORDER BY hiredate;
-- SELECT 항목으로 반복으로 찾을 데이터
DECLARE
    BEGIN
        FOR emp_list IN (SELECT ename, hiredate FROM emp WHERE hiredate > TO_DATE('15/01/02') ORDER BY hiredate)
        LOOP
            DBMS_OUTPUT.PUT_LINE('사원이름 : '||emp_list.ename);
            DBMS_OUTPUT.PUT_LINE('입사일 : '||emp_list.hiredate);
        END LOOP;
    END;
/

IF문은 말그대로 JAVA같은 타 언어에서 사용하던 조건문이랑 똑같은 기능을 한다.

조건에 맞으면 작성한 코드를 실행하고

틀리다면 작성한 다른 코드를 실행하는 구문

데이터 결과랑 출력 구문인 DBMS_OUTPUT이랑 틀릴것입니다. 제가 ~~팀 입니다를 귀찮아서 안찾고 막 적었습니다.

SET SERVEROUTPUT ON;
SELECT * FROM dept;
-- 부서 번호를 통해 팀 이름 출력과 위치
SELECT deptname, loc FROM dept WHERE deptno = 1;

CREATE OR REPLACE PROCEDURE dept_find(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        SELECT loc INTO v_dept.loc
        FROM dept WHERE deptno = v_no;
        
        IF v_no = 1 THEN
            DBMS_OUTPUT.PUT('세일즈 팀');
        END IF;
        
        IF v_no = 2 THEN
            DBMS_OUTPUT.PUT('인사 팀');
        END IF;
        
        IF v_no = 3 THEN
            DBMS_OUTPUT.PUT('배송 팀');
        END IF;
        
        IF v_no = 4 THEN
            DBMS_OUTPUT.PUT('개발 1팀');
        END IF;
        
        IF v_no = 5 THEN
            DBMS_OUTPUT.PUT('개발 2팀');
        END IF;
        DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
    END;
/

EXEC dept_find(3);

IF ELSE 문

SET SERVEROUTPUT ON;

-- 부서 번호에 따라 세일즈 팀인지 아닌지를 출력
-- sales_find(v_no)
-- 테이블 : dept
SELECT * FROM dept;
CREATE OR REPLACE PROCEDURE sales_find(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        SELECT deptname INTO v_dept.deptname FROM dept WHERE deptno = v_no;
        
        IF v_dept.deptname = 'sales' THEN
            DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀 입니다.');
        ELSE IF
            DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀이 아닙니다.');
        END IF;
    END;
/
EXEC sales_find(2);



-- 조건에 없는 번호가 나왔을 경우 '해당 데이터가 없습니다.' 출력
-- JAVA에선 예외처리하면 되지만 DB에선??
CREATE OR REPLACE PROCEDURE sales_find2(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        IF v_no = 1 THEN
            DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀 입니다.');
        ELSE
            IF v_no >= 2 THEN
                IF v_no <= 5 THEN
                    DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀 아닙니다.');
                ELSE
                    DBMS_OUTPUT.PUT_LINE('올바른 숫자를 입력해주세요.');
                END IF;
            ELSE
                DBMS_OUTPUT.PUT_LINE('올바른 숫자를 입력해주세요.');
            END IF;
        END IF;
    END;
/
EXEC sales_find2(-1);
-- IF안에 IF를 넣는것도 가능

ELS IF 문

SET SERVEROUTPUT ON;
-- IF로 만든 프로시저를 ELSE IF로 변경
-- dept_find
SELECT name, text FROM USER_SOURCE WHERE name = 'DEPT_FIND';
-- 만들어 둔 프로시저 소스 확인

-- 부서 번호를 통해 팀 이름 출력과 위치
SELECT deptname, loc FROM dept WHERE deptno = 1;

CREATE OR REPLACE PROCEDURE dept_findels(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        SELECT loc INTO v_dept.loc
        FROM dept WHERE deptno = v_no;
        
        IF v_no = 1 THEN
            DBMS_OUTPUT.PUT('세일즈 팀');
        ELSIF v_no = 2 THEN
            DBMS_OUTPUT.PUT('인사 팀');
        ELSIF v_no = 3 THEN
            DBMS_OUTPUT.PUT('배송 팀');
        ELSIF v_no = 4 THEN
            DBMS_OUTPUT.PUT('개발 1팀');
        ELSIF v_no = 5 THEN
            DBMS_OUTPUT.PUT('개발 2팀');
        END IF;
        DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
    END;
/

EXEC dept_findels(2);

위의 코드

SELECT name, text FROM USER_SOURCE WHERE name = 'DEPT_FIND';

위와 같이 USER_SOURCE를 조회하여 확인할 수 있다.

또는

좌측에서 프로시저 항목을 보여주는데 클릭하면 위와같이 보여준다.

INSERT 예

SET SERVEROUTPUT ON;
-- 특정 사원 추가
SELECT * FROM emp;
-- insert_date(ename, job, deptno)
INSERT INTO emp(ename, job, deptno) VALUES('kim', 'staff', 3);

CREATE OR REPLACE PROCEDURE insert_date(v_ename IN emp.ename%TYPE, v_job IN emp.job%TYPE, v_deptno IN emp.deptno%TYPE)
    IS
    BEGIN
        INSERT INTO emp(ename, job, deptno) VALUES(v_ename, v_job, v_deptno);
        COMMIT;
        DBMS_OUTPUT.PUT_LINE(v_ename||'/'||v_job||'/'||v_deptno);
        DBMS_OUTPUT.PUT_LINE('데이터 입력 성공');
    END;
/

EXEC insert_date('kim', 'staff', 3);

UPDATE 예

-- 특정 사원의 급여 올리기
-- 급여가 인상된 직원의 데이터 출력(사원번호, 이름, 급여)
-- 테이블 : employees
SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE update_date(v_no IN NUMBER, v_rate IN NUMBER)
    IS
        v_emp employees%ROWTYPE;
    BEGIN
        UPDATE employees SET salary = salary + (salary * (v_rate / 100))WHERE emp_no = v_no;
        COMMIT;
        
        SELECT emp_no, family_name, first_name, salary
        INTO v_emp.emp_no, v_emp.family_name, v_emp.first_name, v_emp.salary
        FROM employees WHERE emp_no = v_no;
        
        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.family_name||''||v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('봉급 : '||v_emp.salary);
    END;
/
EXEC update_date(117, 10);

DELETE 예

-- 특정 사원 번호의 데이터 삭제 프로시저 제작
-- 대상 테이블 : employees
SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE delete_date(v_deptno IN employees.emp_no%TYPE)
    IS
    BEGIN
        DELETE FROM emp WHERE emp_no = v_deptno;
        COMMIT;
    END;
/

EXEC delete_date(127);

Function은 예전에 JAVA에서 사용하던 메소드, C에서 함수와 거의 비슷한 개념이다.

Procedure와 차이점은 Return값이 있다는 것으로 

반환값이 필요할 때 사용한다.

JAVA에서 메소드를 만들 때 처럼 역시 반환 타입을 반드시 Return문에 선언해주어야 한다, 생략할 수 없다.

또한 OUT, IN OUT을 쓸 수 없고 IN만 사용 가능하다.

예제를 볼 때 실행 부분에서 ':' 부분을 보면 msg 앞에도 붙어있는데

이것이 없으면 msg라는 함수를 찾으므로 msg가 변수임을 알리는 키워드이다.

나머지는 프로시져와 거의 비슷한 편

SET SERVEROUTPUT ON;
-- 함수 선언
CREATE OR REPLACE FUNCTION hello
    RETURN VARCHAR2
    IS
        msg VARCHAR2(20);
    BEGIN
        msg := 'hello, world';
        RETURN msg;
    END;
/

VARIABLE message VARCHAR2(20);

EXEC :message := hello;
PRINT message;


-- 사원번호를 받아서 해당 사원의 급여를 10% 올리고 출력 하기
CREATE OR REPLACE FUNCTION print1(emp_num IN employees.salary%TYPE)
    RETURN employees.salary%TYPE
    IS
        find_salary employees.salary%TYPE;
    BEGIN
        UPDATE employees SET salary = salary * 1.1 WHERE emp_no = emp_num;
        COMMIT;
        SELECT salary
        INTO find_salary
        FROM employees WHERE emp_no = emp_num;
        RETURN find_salary;
    END;
/

VARIABLE msg NUMBER;

EXEC :msg := print1(118);
PRINT msg;


-- Function 조회
SELECT object_name, object_type FROM USER_PROCEDURES WHERE object_type = 'FUNCTION';

-- Function 삭제
-- DROP FUNCTION [function name]
DROP FUNCTION hello;

%ROWTYPE은 전의 Scalar타입보다 약간 효율적인 데이터 타입 선언이다.

이 타입 선언문은 선언 시 지정한 테이블의 컬럼 선언 부분을 전부 가져와 사용한다.

예제에서 사용법을 확인하자.

-- 테이블의 모든 컬럼을 통채로 들고 온다.
-- 테이블명%ROWTYPE
SET SERVEROUTPUT ON;

DECLARE
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT emp_no, first_name, family_name, reg_date
        INTO v_emp.emp_no, v_emp.first_name, v_emp.family_name, v_emp.reg_date
        FROM employees WHERE emp_no = 118;
        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.family_name||''||v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('입사일 : '||v_emp.reg_date);
    END;
/

CREATE OR REPLACE PROCEDURE emp_row(ref_emp_no IN employees.emp_no)
    IS
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT emp_no, first_name, family_name, reg_date
        INTO v_emp.emp_no, v_emp.first_name, v_emp.family_name, v_emp.reg_date
        FROM employees WHERE emp_no = ref_emp_no;
        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.family_name||''||v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('입사일 : '||v_emp.reg_date);
    END;
/

EXEC emp_row(117);

스칼라 데이터 타입은 일반 단일 데이터타입 변수와 %TYPE 데이터형 변수가 있다.

일반 단일 데이터 타입은 타입을 직접 지정하고, 알고 있어야 하고, :=으로 초기값을 부여함

%TYPE 데이터형은 %TYPE만 사용하면 되고 데이터 타입을 몰라도 된다.

특정 테이블의 컬럼에 변수를 담는다.

SET SERVEROUTPUT ON;

-- 일반 단일 데이터 타입
-- 변수명 [CONSTANT] [DataType] [NOT NULL] := [초기값];

DECLARE
    var_emp_no NUMBER(3, 0);
    var_emp_name VARCHAR2(40);
    var_emp_grade CHAR(1) := 'A';
    BEGIN
        var_emp_no := 123;
        var_emp_name := 'kim';
        DBMS_OUTPUT.PUT_LINE('사원 번호 : '||var_emp_no);
        DBMS_OUTPUT.PUT_LINE('사원 이름 : '||var_emp_name);
        DBMS_OUTPUT.PUT_LINE('사원 등급 : '||var_emp_grade);
    END;
/
-- 초기 값을 생성 시 넣어도 되고 BEGIN, 시작하고 나서 넣어도 별 차이는 없다.

-- 사원번호 115에 대한 이름, 급여
DESC employees;
DECLARE
    var_emp_no NUMBER(3);
    var_emp_fn NVARCHAR2(8);
    var_emp_ln NVARCHAR2(2);
    var_emp_sal NUMBER;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees
        WHERE emp_no = 115;
        DBMS_OUTPUT.put_line('사원번호 : '||var_emp_no);
        DBMS_OUTPUT.put_line('이름 : '||var_emp_ln||''||var_emp_fn);
        DBMS_OUTPUT.put_line('급여 : '||var_emp_sal);
    END;
/






-- %TYPE 변수
-- [변수명] 테이블명.컬럼명%TYPE
-- 변수에 테이블, 컬럼을 입력해서 타입을 가져옴
DECLARE
    var_emp_no employees.emp_no%TYPE;
    var_emp_fn employees.first_name%TYPE;
    var_emp_ln employees.family_name%TYPE;
    var_emp_sal employees.salary%TYPE;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees
        WHERE emp_no = 115;
        DBMS_OUTPUT.put_line('사원번호 : '||var_emp_no);
        DBMS_OUTPUT.put_line('이름 : '||var_emp_ln||''||var_emp_fn);
        DBMS_OUTPUT.put_line('급여 : '||var_emp_sal);
    END;
/


-- %TYPE을 이용한 프로시저
CREATE OR REPLACE PROCEDURE emp_in(ref_emp_no IN employees.emp_no%TYPE)
    IS
    var_emp_no employees.emp_no%TYPE;
    var_emp_fn employees.first_name%TYPE;
    var_emp_ln employees.family_name%TYPE;
    var_emp_sal employees.salary%TYPE;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees
        WHERE emp_no = ref_emp_no;
        DBMS_OUTPUT.put_line('사원번호 : '||var_emp_no);
        DBMS_OUTPUT.put_line('이름 : '||var_emp_ln||''||var_emp_fn);
        DBMS_OUTPUT.put_line('급여 : '||var_emp_sal);
    END;
/

EXEC emp_in(120);

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;