■ PL/SQL 서브프로그램(Subprogram)
- 반복적으로 호출할 수 있는 이름이 있는 PL/SQL 블록이다.
- PL/SQL 블록 또는 다른 서브프로그램 내에서 서브프로그램을 선언하고 정의할 수 있다.
- 서브프로그램은 프로시저, 함수, 패키지를 통칭해서 말한다.
- 프로시저는 SELECT, DML, COMMIT, ROLLBACK, SAVEPOINT 업무에 작업을 수행하는 ㅡ주프로그램
- 함수는 값을 계산하고 반환할 때 사용하는 서브프로그램.
- 패키지는 프로시저, 함수를 그룹화할 수 있는 서브프로그램이다.(DBMS_OUTPUT, SUM, ... 등 패키지안에 구성되어있다)

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;
BEGIN
    DBMS_OUTPUT.PUT_LINE(to_char(v_a,'YYYY-MM-DD HH24:MI:SS'));
END;
/

 

/* 기존 테이블 삭제 */
DROP TABLE hr.emp PURGE;
DROP TABLE hr.dept PURGE;

/* 테이블 생성 */
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_fk 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');
# 신규 부서 정보를 입력하는 프로그램 작성

-- 마지막 부서번호 다음 번호 패턴
SELECT max(department_id) +10 FROM hr.dept;
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
    SELECT max(department_id) +10
    INTO v_max
    FROM hr.dept;

    INSERT INTO hr.dept(department_id, department_name, manager_id, location_id)
    VALUES(v_max, p_name, p_mgr, p_loc);
END;
/

SELECT * FROM hr.dept;

BEGIN
    hr.add_dept('경영지원',100,1800);   -- Transaction 실행
    hr.add_dept('데이터분석',99,1800);   -- 오류 >> 강제종료 + ROLLBACK
    hr.add_dept('DA',101,1800);    -- 미실행
END;
/
-- ORA-02291: integrity constraint (HR.DEPT_MGR_ID_FK) violated - parent key not found
-- 매니저번호는 사원번호를 참조하여 생성가능 -> 99번 없는 번호 = 오류
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
    SELECT max(department_id) +10
    INTO v_max
    FROM hr.dept;

    INSERT INTO hr.dept(department_id, department_name, manager_id, location_id)
    VALUES(v_max, p_name, p_mgr, p_loc);
END;
/

SELECT * FROM hr.dept;

BEGIN
    hr.add_dept('경영지원',100,1800);   -- Transaction 실행 >> 저장
    hr.add_dept('데이터분석',99,1800);   -- 오류 >> EXCEPTION + Transaction  종료 + 미저장
    hr.add_dept('DA',101,1800);    -- 미실행

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
    SELECT max(department_id) +10
    INTO v_max
    FROM hr.dept;

    INSERT INTO hr.dept(department_id, department_name, manager_id, location_id)
    VALUES(v_max, p_name, p_mgr, p_loc);
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

SELECT * FROM hr.dept;

BEGIN
    hr.add_dept('경영지원',100,1800);    -- Transaction  실행 >> 저장
    hr.add_dept('데이터분석',99,1800);   -- 오류 >> EXCEPTION +  Transaction 종료 + 미저장
    hr.add_dept('DA',101,1800);        -- 실행 >> 저장
END;
/

 

/* hr session : 프로시저 생성 */
CREATE OR REPLACE PROCEDURE hr.query_emp(p_id number)
IS
    v_rec hr.employees%rowtype;
    
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = p_id;
    
    DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' works on '||v_rec.job_id||', earns $'||
                            v_rec.salary||' per month.');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다.');
    
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp;
/
/* dba session  : scott 유저 생성 */
CREATE USER scott IDENTIFIED BY oracle;

GRANT CREATE SESSION TO scott;

SELECT *
FROM dba_sys_privs
WHERE grantee = 'SCOTT';
/* scott session  : 부여받은 권한 확인 */
SELECT * FROM session_privs;
SELECT * FROM user_tab_privs;
/* hr session  : scott한테 hr.query_emp의 실행 권한 부여 */
GRANT EXECUTE ON hr.query_emp TO scott;     -- EXECUTE : 실행
SELECT * FROM user_tab_privs;
/* scott session  : 부여받은 권한 확인 */
SELECT * FROM user_tab_privs;

/* 프로시저 호출 */
EXECUTE hr.query_emp(100)
>>> scott은 hr.query_emp에 대한 권한만 있어도 hr.employees 간접 엑세스 가능

 

# DROP PROCEDURE hr.query_emp;
>> 삭제하면서 해당 권한도 자동 회수를 한다.

# OR REPLACE 옵션으로 삭제, 생성 할 경우 권한이 그대로 살아 있다.

 

 

# RETURN 문 : 프로시저 종료

# 특정사원 조회 방지 추가
CREATE OR REPLACE PROCEDURE hr.query_emp(p_id number)
IS
    v_rec hr.employees%rowtype;
BEGIN
    IF p_id IN (100,101,102) THEN
        RETURN;     -- 강제종료
    ELSE
        SELECT *
        INTO v_rec
        FROM hr.employees
        WHERE employee_id = p_id;
    
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' works on '||v_rec.job_id||', earns $'||
                                v_rec.salary||' per month.');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다.');
    
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp;
/

EXECUTE hr.query_emp(100)
EXECUTE hr.query_emp(200)
# 특정사원 조회 시 에러추가
CREATE OR REPLACE PROCEDURE hr.query_emp(p_id number)
IS
    v_rec hr.employees%rowtype;
    e_error EXCEPTION;
BEGIN
    IF p_id IN (100,101,102) THEN
        RAISE e_error;
    ELSE
        SELECT *
        INTO v_rec
        FROM hr.employees
        WHERE employee_id = p_id;
    
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' works on '||v_rec.job_id||', earns $'||
                                v_rec.salary||' per month.');
    END IF;
EXCEPTION
    WHEN e_error THEN
        DBMS_OUTPUT.PUT_LINE('조회할 수 없습니다.');
        
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다.');
    
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp;
/

EXECUTE hr.query_emp(100)
EXECUTE hr.query_emp(200)
# 특정사원 조회 시 사용자에러 발생
CREATE OR REPLACE PROCEDURE hr.query_emp(p_id number)
IS
    v_rec hr.employees%rowtype;
    e_error EXCEPTION;
BEGIN
    IF p_id IN (100,101,102) THEN
        RAISE_APPLICATION_ERROR(-20000,'조회할 수 없습니다.');
    ELSE
        SELECT *
        INTO v_rec
        FROM hr.employees
        WHERE employee_id = p_id;
    
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' works on '||v_rec.job_id||', earns $'||
                                v_rec.salary||' per month.');
    END IF;
EXCEPTION
    WHEN e_error THEN
        DBMS_OUTPUT.PUT_LINE('조회할 수 없습니다.');
        
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다.');
    
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END query_emp;
/

EXECUTE hr.query_emp(100)
EXECUTE hr.query_emp(200)

 

[문제]사원번호를 입력값으로 받아서 그 사원의 근무 연수를 출력하고 근무 연수가
20년 이상이면 급여를 20% 인상한 급여로 수정, 
20년 보다 작고 17년 보다 크거나 같으면  10%인상한 급여로 수정,
17년 미만인 근무자는 아무 작업을 수행하지 않는 프로그램을 작성하세요.
테스트가 끝나면 rollback 합니다.
CREATE OR REPLACE PROCEDURE sal_update_proc(p_id number)
IS
    v_months number;
    v_sal number;       -- BEFORE
    v_sal_up number;    -- AFTER
BEGIN
    SELECT salary, trunc(months_between(sysdate,hire_date)/12)
    INTO v_sal, v_months
    FROM hr.employees
    WHERE employee_id = p_id;
    
    IF v_months >= 20 THEN
        UPDATE hr.employees
        SET salary = salary * 1.2
        WHERE employee_id = p_id;
        
        SELECT salary
        INTO v_sal_up
        FROM hr.employees
        WHERE employee_id = p_id;

        DBMS_OUTPUT.PUT_LINE(p_id||' 사원은 근무 연수가 '||v_months||'년 입니다. 이전 급여는 '||v_sal||
                                ' 수정된 급여는 '||v_sal_up||' 입니다.');
    ELSIF v_months >= 17 then
        UPDATE hr.employees
        SET salary = salary * 1.1
        WHERE employee_id = p_id;
        
        SELECT salary
        INTO v_sal_up
        FROM hr.employees
        WHERE employee_id = p_id;
        
        DBMS_OUTPUT.PUT_LINE(p_id||' 사원은 근무 연수가 '||v_months||'년 입니다. 이전 급여는 '||v_sal||
                                ' 수정된 급여는 '||v_sal_up||' 입니다.');
    else  
        DBMS_OUTPUT.PUT_LINE(p_id||' 사원은 근무 연수가 '||v_months||'년 입니다. '||v_sal||
                                ' 급여는 수정 안됩니다.');
    end if;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE(p_id||' 사원번호가 없습니다.');
        
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
end sal_update_proc;
/

BEGIN
  sal_update_proc(100);
  rollback;
END;
/
# CASE 문
    CASE
        WHEN v_months >= 20 THEN
            UPDATE hr.employees
            SET salary = salary * 1.2
            WHERE employee_id = p_id;
        
            SELECT salary
            INTO v_sal_up
            FROM hr.employees
            WHERE employee_id = p_id;

            DBMS_OUTPUT.PUT_LINE(p_id||' 사원은 근무 연수가 '||v_months||'년 입니다. 이전 급여는 '||v_sal||
                                    ' 수정된 급여는 '||v_sal_up||' 입니다.');
        WHEN v_months >= 17 then
            UPDATE hr.employees
            SET salary = salary * 1.1
            WHERE employee_id = p_id;
        
            SELECT salary
            INTO v_sal_up
            FROM hr.employees
            WHERE employee_id = p_id;
        
            DBMS_OUTPUT.PUT_LINE(p_id||' 사원은 근무 연수가 '||v_months||'년 입니다. 이전 급여는 '||v_sal||
                                    ' 수정된 급여는 '||v_sal_up||' 입니다.');
        ELSE  
            DBMS_OUTPUT.PUT_LINE(p_id||' 사원은 근무 연수가 '||v_months||'년 입니다. '||v_sal||
                                    ' 급여는 수정 안됩니다.');
    END CASE;

 


■ FUNCTION 함수
- 함수는 값을 반환할 수 있는 이름이 있는 PL/SQL 블록이다.
- 함수는 값을 계산할 때 많이 사용하는 서브프로그램이다.

/* 프로시저 생성 */
CREATE OR REPLACE PROCEDURE get_sal(p_id IN number, p_sal OUT number)
IS
BEGIN
    SELECT salary
    INTO p_sal
    FROM hr.employees
    WHERE employee_id = p_id;
EXCEPTION
    WHEN no_data_found THEN
        NULL;
END get_sal;
/

/* 호출 */
DECLARE
    v_sal number;
BEGIN
    get_sal(100,v_sal);
    DBMS_OUTPUT.PUT_LINE(v_sal);
END;
/
↓ 함수로 구현
/* 함수 생성 */
CREATE OR REPLACE FUNCTION get_sal_func(p_id IN number)  -- IN 모드만 가능
    RETURN number       -- 리턴 1개 타입명만 작성, 새미콜론(;)사용 안함
IS
    v_sal number := 0;  -- 입력값이 없다면 0 출력하기위해 초기화값 설정
BEGIN
    SELECT salary
    INTO v_sal
    FROM hr.employees
    WHERE employee_id = p_id;
    
    RETURN v_sal;       -- 값 리턴
    
EXCEPTION
    WHEN no_data_found THEN
        RETURN v_sal;   -- 값이 없다면 초기값으로 리턴
END get_sal_func;
/

/* 호출 */
BEGIN
    DBMS_OUTPUT.PUT_LINE(get_sal_func(100));    -- 함수 호출
END;
/

DECLARE
    v_sal number;
BEGIN
    v_sal := get_sal_func(100);
    DBMS_OUTPUT.PUT_LINE(v_sal);
END;
/

/* 호출 */
SELECT employee_id, get_sal_func(employee_id)
FROM hr.employees;

 

/* 함수 생성 */
CREATE OR REPLACE FUNCTION hr.today
    RETURN varchar2
IS
BEGIN
    RETURN to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
END;
/


/* 호출 */
SELECT hr.today
FROM dual;

 

[문제] 급여에 3.3%를 계산하는 tax 함수를 생성해주세요.
/* 함수 생성 */
CREATE OR REPLACE FUNCTION tax(f_sal number)
    RETURN number
IS
BEGIN
    RETURN (f_sal * 0.033);
END;
/

/* 호출 */
SELECT employee_id, salary, tax(salary)
FROM hr.employees;


/* 코드 조회 */
SELECT *
FROM user_source
WHERE name = 'TAX';

 

# 함수 삭제
DROP FUNCTION tax;

 

[문제] 급여를 계산하는 get_annual_comp 함수를 생성해주세요. (nvl함수사용하지말고 조건제어문 활용)
/* 호출*/
SELECT 
        employee_id, 
        salary, 
        commission_pct, 
        ((salary*12)+(salary*12*commission_pct)) annual_salary_1,
        ((salary*12)+(salary*12*nvl(commission_pct,0))) annual_salary_2,
        get_annual_comp(salary, commission_pct) get_annual_comp
FROM hr.employees;
1)
CREATE OR REPLACE FUNCTION get_annual_comp(p_sal number, p_comm number)
    RETURN number
IS
BEGIN
    IF p_comm IS NULL THEN
        RETURN(((p_sal*12);
    ELSE
        RETURN(((p_sal*12)+(p_sal*12*p_comm)));
    END IF;
END;
/
2)
CREATE OR REPLACE FUNCTION get_annual_comp(p_sal number, p_comm number)
    RETURN number
IS
    num number := 0;
BEGIN
    IF p_comm IS NOT NULL THEN
        num := p_comm;
    END IF;
    
    RETURN((p_sal*12)+(p_sal*12*num));
END;
/

 

[문제] 사원번호를 입력값으로 받아서 그 사원의 근무년수를 출력하는 함수를 작성해주세요.
    단, 없는 사원번호가 입력값으로 들어오면 내가 만든 오류번호, 오류메시지를 출력해주세요.
/* 호출 */
SELECT employee_id, hire_date, get_year(employee_id)
FROM hr.employees;

EXECUTE DBMS_OUTPUT.PUT_LINE(get_year(100))

EXECUTE DBMS_OUTPUT.PUT_LINE(get_year(300))
CREATE OR REPLACE FUNCTION get_year(p_id hr.employees.employee_id%type)
    RETURN number
IS
    years number;
BEGIN
    SELECT trunc(months_between(sysdate,hire_date)/12)
    INTO years
    FROM hr.employees
    WHERE employee_id = p_id;

    RETURN years;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20000,'없는번호입니다.');
END get_year;
/

 

[문제] 우편번호=문자타입의 컬럼이지만 데이터는 숫자로만 입력되어있어야 한다.
    만약 숫자문자일경우 1, 문자와 NULL 이면 0으로 리턴하는 함수
/* 타입 체크 */
SELECT * FROM hr.locations;

desc hr.locations

/* 문자를 숫자형으로 변환 시 오류발생 */
SELECT to_number('d') from dual;    -- ORA-01722: invalid number

/* 호출 */
SELECT postal_code, as_number(postal_code)
FROM hr.locations;
CREATE OR REPLACE FUNCTION as_number(p_code varchar2)
    RETURN number
IS
    v_num number;
BEGIN
    v_num := to_number(p_code); -- 문자를 숫자형변수에 대입 >> 오류발생 >> EXCEPTION
    
    IF v_num IS NULL THEN
        RETURN 0;       -- NULL값일 경우
    ELSE
        RETURN 1;       -- 숫자값일 경우
    END IF;
EXCEPTION
    WHEN OTHERS THEN    -- 문자값일 경우
        RETURN 0;
END;
/

 

 

p_code varchar2
v_num number
v_num := to_number(p_code);
>> 문자를 숫자형변수에 대입 >> 오류발생  = 문자, 정상 = 숫자 또는 NULL
>> 10, 1O, 1,ㅣ, ... 등 숫자와 문자가 섞여있을 때 많이 사용

 

RAISE_APPLICATION_ERROR(-20000,'사용자정의')
>> 별도의 선언 없이 사용

 

FUNCTION 생성 시 변수 IN 모드만 가능
결과값은 RETURN 단일값

 

EXCEPTION 예외사항의 사용여부, 위치에 따라 작업 수행 또는 롤백 발생 주의

 

'Oracle SQL' 카테고리의 다른 글

22 PL/SQL PACKAGE, TRIGGER  (1) 2023.11.06
21 PL/SQL PACKAGE  (1) 2023.11.03
19 PL/SQL PROCEDURE  (0) 2023.11.01
18 PL/SQL EXCEPTION  (1) 2023.10.31
17 PL/SQL Nested Table, VARRAY, Explicit Cursor  (0) 2023.10.30
'Oracle SQL'의 다른글
  • 현재글20 PL/SQL PROCEDURE, FUNCTION

티스토리툴바