231102 PL/SQL 서브프로그램(Sub Program), 프로시저, ERROR

■ PL/SQL 서브프로그램(Sub program)

1. 반복적으로 호출 할 수 있는 이름이 있는 PL/SQL 블록이다. 

2. PL/SQL 블록 또는 다른 서브 프로그램 내에서 서브프로그램을 선언하고 정의할 수 있다.

3. 서브 프로그램은 프로시저, 함수, 패키지를 통칭해서 말한다. 

 

1) 프로시저 : SELECT문, DML, COMMIT, ROLLBACK, SAVEPOTIN 업무작업 수행 → 함수 사용시 변수+EXECUTE 이용

2) 함수 : 값을 계산하고 반환할 때 사용. 기능 프로그램. (SQL 문장에 가능하면 X → 문제 가능성 ↑)

3)  패키지 : 프로시저+함수 : 프로시저, 함수를 '그룹화' 할 수 있음.

▷ IN(값, 상수) ,OUT(변수) ,IN OUT() 모드 != SIZE X

 

# 표현식

CREATE OR REPLACE PROCEDURE HR.PROC_DATE
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
END;
/

 

# 프로시저 호출

EXEC HR.PROC_DATE;

BEGIN 
    HR.PROC_DATE;
END;
/

 

# 프로시저와 함수는 호출방식이 다르다

# 함수 호출 방식(SELECT, DML 표현식에서 사용)

DECLARE
    v_a date := sysdate;
    -- v_b VARCHAR2(50) := HR.proc_date; : 오류 및 규칙
BEGIN 
    DBMS_OUTPUT.PUT_LINE(to_char(v_a, 'yyyy-mm-dd hh24:mi:ss')); -- 표현식
    -- DBMS_OUTPUT.PUT_LINE(to_char(v_b, 'yyyy-mm-dd hh24:mi:ss')); -- 표현식
END;
/

 

# 연습 TABLE

CREATE TABLE hr.emp
AS 
SELECT * 
FROM hr.employees;

CREATE TABLE hr.dept
AS 
SELECT * 
FROM hr.departments;

 

# 제약조건  추가

ALTER TABLE hr.emp ADD CONSTRAINT empid_pk PRIMARY KEY(employee_id);
ALTER TABLE hr.dept ADD CONSTRAINT deptid_pk PRIMARY KEY(department_id);
ALTER TABLE hr.dept ADD CONSTRAINT dept_mgr_id_pk FOREIGN KEY(manager_id) REFERENCES hr.emp(employee_id);

 

# 확인

SELECT * 
FROM user_constraints
WHERE table_name IN ('EMP', 'DEPT');

SELECT * 
FROM user_cons_columns
WHERE table_name IN ('EMP', 'DEPT');

 

@ 부서번호가 10씩 증가, 신규 부서 정보 입력하는 프로그램 작성 

방법1) 

CREATE OR REPLACE Procedure hr.add_dept (
    p_name  IN VARCHAR2,
    p_mgr   IN NUMBER,
    p_loc   IN NUMBER 
)
IS
    v_max number;
BEGIN
    -- 부서번호 마지막번호+10
    SELECT max(department_id) +10 
    INTO v_max
    FROM hr.dept;
    
    -- data 로드
    INSERT INTO hr.dept(department_id, department_name, manager_id, location_id)
    VALUES (v_max, p_name, p_mgr, p_loc);
    -- transaction 언어 쓰지 말것
END add_dept;
/

 

# hr.dept 확인

SELECT * FROM hr.dept;

 

# 오류 발생 

- integrity constraint (HR.DEPT_MGR_ID_PK) violated - parent key not found

: 부모키가 없는 상태에서 insert를 실행하면 무결성 제약 조건의 위배

BEGIN 
    hr.add_dept('경영지원', 100, 1800);  -- 2. ROLLBACK 발생
    hr.add_dept('데이터분석', 99, 1800); -- 1. 오류 발생
    hr.add_dept('DA', 101, 1800);
END;
/

 

방법2) 

CREATE OR REPLACE Procedure hr.add_dept (
    p_name  IN VARCHAR2,
    p_mgr   IN NUMBER,
    p_loc   IN NUMBER 
)
IS
    v_max number;
BEGIN
    -- 부서번호 마지막번호+10
    SELECT max(department_id) +10 
    INTO v_max
    FROM hr.dept;
    
    -- data 로드
    INSERT INTO hr.dept(department_id, department_name, manager_id, location_id)
    VALUES (v_max, p_name, p_mgr, p_loc);
    -- transaction 언어 쓰지 말것
END add_dept;
/

# hr.dept 확인

SELECT * FROM hr.dept;

# 호출 및 예외처리

- 예외처리로 오류방지를 했으나, 마지막 data는 수행이 되지 않고 END로 끝나버린다.

BEGIN 
    hr.add_dept('경영지원', 100, 1800);  -- 수행
    hr.add_dept('데이터분석', 99, 1800); -- 무결성 제약조건으로 오류발생
    hr.add_dept('DA', 101, 1800); -- 수행X
EXCEPTION -- 예외처리
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END;
/

 

방법3)

- 예외처리를 프로시저 생성하면서 처리하는 방법

CREATE OR REPLACE Procedure hr.add_dept (
    p_name  IN VARCHAR2,
    p_mgr   IN NUMBER,
    p_loc   IN NUMBER 
)
IS
    v_max number;
BEGIN
    -- 부서번호 마지막번호+10
    SELECT max(department_id) +10 
    INTO v_max
    FROM hr.dept;
    
    -- data 로드
    INSERT INTO hr.dept(department_id, department_name, manager_id, location_id)
    VALUES (v_max, p_name, p_mgr, p_loc);
    -- transaction 언어 쓰지 말것
EXCEPTION -- 예외처리
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END add_dept;
/

# hr.dept 확인

SELECT * FROM hr.dept;

# 예외처리 프로시저로 변경

- 코드의 간결화.

BEGIN 
    hr.add_dept('경영지원', 100, 1800);  
    hr.add_dept('데이터분석', 99, 1800); 
    hr.add_dept('DA', 101, 1800); 
END;
/