프로시저 Procedure 정의 및 변수종류 (1)

프로시저 Procedure

-넓은 의미는 어떤 업무를 처리하기 위한 절차.

-결과값 반환없이 특정 로직을 처리.

-질의의 집합으로 어떤 동작을 일괄처리.

- 테이블에서 데이터 추출 및 조작, 결과를 다른 테이블에 저장하거나 갱신.

CREATE OR REPLACE PROCEDURE 프로시저명
(
	매개변수명1 [IN | OUT | IN OUT] 데이터 타입,
	매개변수명2 [IN | OUT | IN OUT] 데이터 타입
)
IS | AS 
	변수 및 상수 선언
BEGIN
	실행 문장
    EXCEPTION 문장
END;

프로시저 실행
EXECUTE 프로시저명();
EXEC 프로시저명();

IN 프로시저에서 입력으로 사용

OUT  프로시저에서 밖으로 나감

IN OUT 둘다

---

프로시저 변수유형

#일반 변수
COUNT NUMBER();
EMP_NAME VARCHAR2(10);

#상수: CONSTANT (변경불가)
COUNT CONSTANT NUMBER();
EMP_NAME VARCHAR2(10);

# %TYPE% 테이블 열 데이터 형식과 동일
EMP_NAME EMPLOYEES.EMPLOYEE_ID%TYPE
EMP_EMAIL EMPLOYEES.EMAIL%TYPE


# %ROWTYPE 테이블 전체 열의 데이터 형식과 동일
EMP EMPLOYEES%ROWTYPE
DEPT DEPTARTMENTS%ROWTYPE


# 레코드: 여러개의 열의 데이터 형식을 지정
TYPE user_type IS RECORD ( name VARCHAR2(10), email VARCHAR2(20));
user user_type;


# 컬렉션(Collection) 배열과 유사, VARRAY, 중첩 테이블(Nested Table), 연관 배열(Associative Array) 등.
TYPE v_array_type IS VARRAY(5) OF NUMBER(10);
v_array varray_type;
TYPE nest_tbl_type IS TABLE OF VARCHAR2(10);
nest_tbl nest_tbl_type;
TYPE a_array_type IS TABLE OF NUMBER(10) INDEX BY VARCHAR2(10);
a_array a_array_type;

 

---

 

첫번째 직원 출력 프로시저

CREATE OR REPLACE PROCEDURE frist_emp AS
	emp_name VARCHAR2(20);
BEGIN
	SELECT first_name || ' ' || last_name INTO emp_name;  // emp_name에 넣어라
    FROM employees 
    WHERE employee_id = 1000;
    DBMS_OUTPUT.PUT_LINE(emp_name); //출력문
END;

서버 출력 허용 

SET SERVEROUTPUT ON;

프로시저 실행

EXECUTE first_emp();

 

직원 정보 출력 프로시저 (IN 파라미터 사용)

CREATE OR REPLACE PROCEDURE print_emp(
	emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
) AS
	emp_name VARCHAR(20);
BEGIN
	SELECT first_name || '' || last_name INTO emp_name
	FROM employees WHERE employee_id = emp_id;
        DBMS_OUTPUT.PUT_LINE(emp_name);
END;

 

---

직원 평균 salary 출력 프로시저 (OUT)

CREATE OR REPLACE PROCEDURE emp_avg_salary(
	avg_salary OUT NUMBER
) AS
BEGIN
	SELECT AVG(salary) INTO avg_salary
    	FROM employees;
END

OUT 프로시저 실행

DECLARE
	avg_salary NUMBER;
BEGIN
	emp_avg_salary(avg_salary);
	DBMS_OUTPUT.PUT_LINE(avg_salary);
END;

---

IF ELSE 프로시저 

CREATE OR REPLACE PROCEDURE if_salary(
	salary IN NUMBER
) AS
	avg_salary NUMBER;
BEGIN
	SELECT AVG(salary) INTO avg_salary FROM employees;
    
    IF salary >= avg_salary THEN
    	DBMS_OUTPUT.PUT_LINE('평균 이상');
    ELSE
    	DBMS_OUTPUT.PUT_LINE('평균 미만');
    END IF;
END;

---

CASE 문 프로시저

CREATE OR REPLACE PROCEDURE case_hire_date(
	emp_email IN EMPLOYEES.EMAIL%TYPE
) AS
	hire_year NCHAR(2);
    text_msg VARCHAR2(20);
BEGIN
	SELECT TO_CHAR(hire_date, 'YY') INTO hire_year
    FROM employees
    WHERE email = emp_email;
    
    CASE
    	WHEN (hire_year = '01') THEN text_msg :='01년도 입사';
    	WHEN (hire_year = '03') THEN text_msg :='03년도 입사';
    	WHEN (hire_year = '05') THEN text_msg :='05년도 입사';
    END	CASE;
    DBMS_OUTPUT.PUT_LINE(text_msg);
END;

 

프로시저 반복문  (while, for문)

CREATE OR REPLACE PROCEDURE while_print AS
	str VARCHAR(100);
        i NUMBER;
BEGIN
	i := 1;
    WHILE(1 <= 10) LOOP
    	str := '반복 횟수 :' || ' (' || i ||')';
        DBMS_OUTPUT.PUT_LINE(str);
    	i := i +1;
    END LOOP;
END;

---

예외처리 사용 프로시저

Exception 에 ORA- 에러 메시지를 넣어준다.( null 등)

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;

---

IN OUT 파라미터 사용 프로시저

CREATE OR REPLACE PROCEDURE in_out_emp(
	emp_name IN OUT VARCHAR2
) AS
BEGIN
	SELECT first_name || '' || last_name INTO emp_name
    FROM employees
    WHERE first_name = emp_name OR last_name = emp_name;
    
	emp_name := '직원' || emp_name;
    EXCEPTION
    	WHEN NO_DATA_FOUND THEN
        	emp_name := '직원: 없음';
END;

실행

DECLARE
	emp_name VARCHAR2(30) := 'LISA';
BEGIN
	in_out_emp(emp_name);
        DBMS_OUTPUT.PUT_LINE(emp_name);
END;