231101 PL/SQL 프로시저 컴파일, FORMAL PARLACE 형식매개변수, IN MODE, OUT MODE, IN-OUT MODE

[문제] 사원번호를 입력값으로 받아서 그 사원의 정보를 출력하는 프로그램을 작성해 주세요. 

 

execute :b_id :=100;
print :b_id;

DECLARE
    v_id NUMBER := :b_id;
    v_rec hr.employees%ROWTYPE;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
END;
/

 

# 바인드변수 프로그램외 사용가능+ 익명블록구조 내에서만 사용

var b_id NUMBER; 
var b_sal NUMBER;
execute :b_id :=100;
print :b_id;

DECLARE
    v_id NUMBER := :b_id;
    v_rec hr.employees%ROWTYPE;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
    
    -- 프로그램외(BEGIN:실행) 사용가능(bind 변수)
    :b_sal := v_rec.salary;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
END;
/

print :b_sal;

# 시스템 권한 확인

SELECT * FROM session_privs;

익명 블록	서브프로그램(프로시저, 함수, 패키지) 객체프로그램(object)
이름이 없는 PL/SQL 블록	이름이 있는 PL/SQL 블록
매번 컴파일해야 한다.(단점)	한번만 컴파일 한다.
데이터베이스에 저장되지 않는다.	데이터베이스에 저장된다.
다른 응용프로그램에서 호출 X	다른 응용프로그램에서 호출 O
parameter 를 사용할 수 없다.	parameter 를 사용할 수 있다.
권한이 있다면, 실행 가능	CREATE PROCEDURE( 프로시저, 함수, 패키지 객체를 생성할 수 있는 권한 ) 권한  O

# 프로시저 컴파일

CREATE OR REPLACE PROCEDURE emp_proc( v_id NUMBER )
IS -- declare 대신
    v_rec hr.employees%ROWTYPE;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
        
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
END;
/

SELECT *
FROM user_source
WHERE name = 'EMP_PROC'
ORDER BY line;

# 프로시저명() 출력

(); 콜론은 생략가능

execute emp_proc(100); -- actual parameter(실제 매개변수)
execute emp_proc(200);
execute emp_proc(101)
execute emp_proc(102)

BEGIN
    emp_proc(100);
END;
/

 

# 오류발생: IN 모드로 선언된 형식매개 변수에 실제값을 입력하지 X

execute emp_proc()

 

■ FORAML PARLACE 형식매개변수
- 형식 매개변수를 선언시에 size를 명시하면 안 된다.

PARAMETER MODE
IN(기본값) parameter	입력값 처리 상수값을 호출 환경에서 프로시저로 전달. in 모드로 선언된 형식매개변수는 프로그램안내에서 상수로 동작한다. default 값 설정 가능
OUT parameter	프로시저 안에서 '어떤 값'(선언된 )을 호출환경으로 전달한다.
IN OUT parameter	값을 호출환경에서 프로시저로 전달하고 동일한 매개변수에 대해서 다른 값을 변경한 값을 호출환경으로 전달한다.

# 오류발생 
- 형식매개변수는 한번 입력받은 값만 사용해야 한다. 

CREATE OR REPLACE PROCEDURE emp_proc( v_id IN NUMBER )
IS -- declare 대신
    v_rec hr.employees%ROWTYPE;
BEGIN
    v_id := 200; 
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = v_id;
    
    dbms_output.put_line(v_rec.employee_id||' '||v_rec.last_name);
        
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line(v_id||' 사원은 존재하지 않습니다.');
END;
/

# IN, OUT 모드 같이 fetch 하기

CREATE OR REPLACE PROCEDURE emp_proc( p_id IN NUMBER, p_name OUT VARCHAR2, p_sal OUT NUMBER )
IS -- declare 대신 + IS (선언하지 않는다면 바로) BEGIN 절 
BEGIN
    SELECT last_name, salary
    INTO p_name, p_sal
    FROM hr.employees
    WHERE employee_id = p_id;
    
    dbms_output.put_line(p_id||' '||upper(p_name)||' '||to_char(p_sal, 'L999,999'));
        
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line(p_id||' 사원은 존재하지 않습니다.');
END;
/

# source 확인

SELECT * 
FROM user_source 
WHERE name = 'EMP_PROC'
ORDER BY line;

# 컴파일 실패시 오류 확인하는 방법
- 오류가 없다면, 아무것도 출력되지 않음.

show error

 

# 프로시저 형식매개변수 구조 확인

desc emp_proc

var b_name VARCHAR2(30);
var b_sal NUMBER;
execute emp_proc(100, :b_name, :b_sal);

print :b_name :b_sal;

DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    emp_proc(100, v_name, v_sal);
    dbms_output.put_line(v_name||' '||v_sal);
END;
/

 

# in-out 모드 

CREATE OR REPLACE PROCEDURE format_phone( p_phone_no IN OUT VARCHAR2 )
IS
BEGIN
    p_phone_no := substr(p_phone_no, 1, 3)||'-'||substr(p_phone_no, 4, 4)||'-'|| substr(p_phone_no, 8);
END;
/

-- 변수 
var b_phone VARCHAR2;
EXECUTE :b_phone := '01098088154';

execute format_phone(:b_phone);
print :b_phone;

# 익명블록 구조

DECLARE 
    v_phone varchar2(20) := '01098088154';
BEGIN 
    format_phone(v_phone);
    dbms_output.put_line(v_phone);
END;
/

 

# in 모드, out 모드, in-out 모드 

CREATE OR REPLACE PROCEDURE sp_comm
(   p_id in hr.employees.employee_id%TYPE , 
    p_name out hr.employees.last_name%TYPE , 
    p_sal out hr.employees.salary%TYPE , 
    p_comm in out hr.employees.commission_pct%TYPE 
)
IS
    v_comm hr.employees.commission_pct%TYPE;
BEGIN
    SELECT last_name, salary, nvl(commission_pct, 0)
    INTO p_name, p_sal, v_comm
    FROM hr.employees
    WHERE employee_id = p_id;
    
    p_comm := p_comm + v_comm;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise_application_error(-20000, sqlerrm);
    WHEN OTHERS THEN
        raise_application_error(-20001, sqlerrm);
END;
/

 

# sp_comm(100, 변수, 변수, 초기값이 있는 변수) 

-  형식 매개변수 순서대로 

 

# 익명블록 구조 

DECLARE 
    v_name VARCHAR2(30); 
    v_sal NUMBER;
    v_comm NUMBER(3,2) := 0.1; --초기값 설정
BEGIN 
    sp_comm(100, v_name, v_sal, v_comm);
    dbms_output.put_line(v_name||' '||v_sal||' '||v_comm);
    
    sp_comm(1000, v_name, v_sal, v_comm);
END;
/

 

# TEST table 생성 

CREATE TABLE sawon (
    id      NUMBER,
    name    VARCHAR2(30),
    day     DATE,
    deptno  NUMBER
);

 

# 익명블록 구조

CREATE OR REPLACE PROCEDURE sawon_in_proc (
    p_id      IN  NUMBER,
    p_name    IN  VARCHAR2,
    p_day     IN  DATE DEFAULT sysdate,
    p_deptno  IN  NUMBER := 0
) 
IS 
BEGIN 
    INSERT INTO hr.sawon( id, name, day, deptno )
    VALUES ( p_id, p_name, p_day, p_deptno );
END sawon_in_proc;
/

 

# 프로시저 구조 확인 

desc sawon_in_proc

 

# 형식매개변수에 실제값이 위치적으로 대응해서 수행하는 방법.
- insert 문의 기능과 동일, transaction 진행중.

EXECUTE sawon_in_proc(1, '홍길동', to_date('2023-01-01', 'yyyy-mm-dd'), 10);

SELECT * FROM hr.sawon;

 

# 형식매개변수에 실제값이 이름 지정방식으로 대응해서 수행하는 방법. 

- 문법: "프로시저명 => 실제값" 

- 이름지정방식에 경우에는 작성 순서는 상관없다.

EXECUTE sawon_in_proc(p_id => 1, p_name => '홍길동', p_deptno=> 10);
EXECUTE sawon_in_proc(p_id => 3, p_deptno=> 30, p_name=> '박찬호');
SELECT * FROM hr.sawon;

 

# 혼합방식 : 위치지정방식 + 이름지정방식

- 위치지정방식 뒤에 이름지정방식 문제없이 사용가능 + 순서상관없다.

EXECUTE sawon_in_proc(2, p_name => '손흥민', p_deptno=> 10);
EXECUTE sawon_in_proc(2, p_deptno=> 10,  p_name => '손흥민');

SELECT * FROM hr.sawon;

 

# 혼합방식 사용시, 주의사항

- 오류: a positional parameter association may not follow a named association

- 이름지정방식 뒤에서 위치지정 방식을 사용할 수없다.

EXECUTE sawon_in_proc(p_id => 3, '박찬호', p_deptno=> 30);

SELECT * FROM hr.sawon;