2024.12.30 PL/SQL 8일차

복습) 사원번호를 입력값으로 받아서 그 사원의 정보를 출력하는 프로그램

var b_id number

declare
    v_rec hr.employees%rowtype;
begin
    select *
    into v_rec
    from hr.employees
    where employee_id = :b_id;
    dbms_output.put_line(v_rec.employee_id||'번 사원 '||v_rec.first_name);
exception
    when no_data_found then
    dbms_output.put_line(:b_id||'번 사원은 존재하지 않습니다.');
    
    when others then
    dbms_output.put_line(sqlerrm);
end;
/

출력

▶ 하지만 이 코드는 혼자 쓰긴 편하지만 많은 고객들과 쓰는 프로그램이다 가정하면 좋은 코드는 아니다.

 

■ 명명된(이름이 있는) PL/SQL 블록

   - PL/SQL 서브 프로그램(subprogram)이라고 한다.

   - 익명블록(anonymous block)과 유사한 블록구조를 가지고 있다.

   - 선언부분, 실행부분, 예외처리부분

 

 # 익명블록(anonymous block)

  - 명명되지 않은 블록 (이름이 없는 블록)

  - 매번 컴파일해야 한다.

  - 데이터베이스에 저장되지 않는다.

  - 다른 응용 프로그램에서 호출할 수 없다.

  - 값을 반환하지 않는다. (return문을 이용해서 값을 반환하지 않는다.)

  - 파라미터를 사용할 수 없다.

 

 # 서브 프로그램(subprogram)

   - 명명된 블록 (이름이 있는 블록)

   - 한 번만 컴파일한다.

   - 데이터베이스에 저장된다.

   - 권한이 있어야지 서브 프로그램을 만들 수 있다.

   - 다른 응용 프로그램에서 호출할 수 있다.

   - 함수를 이용해서 값을 반환할 수 있다. (return문을 이용해서 값을 반환할 수 있다.)

   - 파라미터를 사용할 수 있다.

   - 프로시저(procedure), 함수(function), 패키지(package)

   - create procedure(내부적으로 프로시저, 함수, 패키지를 생성할 수 있는 권한) 시스템 권한이 필요하다.

   - 서브 프로그램에서는 bind 변수를 사용할 수 없다. 그 이유는 파라미터를 제공하기 때문이다.

그림으로 이해하기

■ 프로시저(procedure)

   - 특정 작업을 수행하는 서브 프로그램이다.

   - DB에 스키마 객체로 저장할 수도 있고, 안 할 수도 있다.

   - 모듈화, 확장성, 재사용성, 유지 관리용이성 증대된다.

   

 # 서브 프로그램 구조

create or replace procedure procedure_name(사용자 지정)
    (parameter [mode] datatype, -- mode : 입력할 건지, return할 건지 결정
    .
    ..
    ...
     parameter [mode] datatype) -- parameter는 생략이 가능하다. (옵션)
     is[as]
        변수, 상수, 명시적커서, 사용자가 정의한 예외사항
begin
exception
end;
/

 

■ parameter 

  - 데이터 값을 호출 환경에서 프로시저(서브 프로그램) 또는 프로시저에서 호출 환경으로 전송하는 데 사용

    1. formal parameter (형식 매개변수)

       - 형식 파라미터 : 서브 프로그램 사양의 파라미터 리스트에 선언된 로컬 변수 (데이터 타입만 사용해야 함(데이터유형, %type, %rowtype))

  - in mode : 바인드 변수와 다르게 입력값을 처리하는 것, 상수로 동작하기 때문에 입력값으로 받은 거 외에는 사용할 수 없다, 호출환경에서 값을 프로시저로 전달한다.

       1) mode의 default값은 in이므로 in을 생력해도 무방하다.

  - out mode : 프로시저에서 값을 호출환경으로 전달한다.

  - in/out mode : 호출환경에서 값을 프로시저로 전달하고 동일한 파라미터를 사용하여 프로시저에서 값을 다시 호출환경으로 전달한다.

 

ex) in mode

create or replace procedure emp_proc(p_id in 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.employee_id||'번 사원 '||v_rec.first_name);
exception
    when no_data_found then
    dbms_output.put_line(p_id||'번 사원은 존재하지 않습니다.');
    
    when others then
    dbms_output.put_line(sqlerrm);
end;
/

출력

▶ DB 안에 소스가 저장되어 있고, Parse된 소스를 저장한다.

▶ is 대신 as로 써도 된다.

 

# 내가 만든 프로시저 소스를 보는 방법 (호출환경)

select text
from user_source
where name = 'EMP_PROC'
order by line;

출력

 

# 서브 프로그램을 실행하고 싶을 경우 (호출환경)

  - ACTUAL PARAMETER (실제 매개변수)

     1) 실제 파라미터를 실제 인수(argument)라고도 한다.

     2) 호출 서브 프로그램의 파라미터 리스트에 사용되는 리터럴값, 변수, 표현식

     3) 프로시저를 호출할 때 값을 넣어주지 않으면 에러가 발생한다. ex) emp_proc()

execute emp_proc(100)

출력

출력

▶ 디벨로퍼에서 execute 해놓으면 어디서든 확인이 가능하다.

 

# 다른 프로그램에서 프로시저를 호출할 경우

begin
	emp_proc(100);
end;
/

출력

 

※ 개발을 하다 보면 함수로 개발할 건지 프로시저로 개발할지 헷갈리는 경우가 많다. 그럴 경우엔 호출을 어떻게 할 건지 물어보면 된다. SQL문장에서도 사용하게 할 거면 함수로 개발해야 한다.

 

# 내가 만든 프로시저 이름을 확인할 수 있다.

EMP_PROC 프로시저 생성확인

 

# 서브 프로그램 파라미터 확인

desc emp_proc

출력

 

 

문제) 사원번호, 급여 인상 비율을 입력값으로 받아서 급여 수정하는 프로그램

create or replace procedure hr.raise_salary
(p_id in hr.employees.employee_id%type,
 p_percent in number)
as -- 생략할 수 없다. 무조건 작성해줘야 함
begin
    update hr.employees
    set salary = salary * (1 + p_percent / 100)
    where employee_id = p_id;
end raise_salary; -- end절 뒤에 프로시저 이름을 쓰는 습관을 가지는 게 좋다.
/

출력

desc hr.raise_salary

출력

select employee_id, salary
from hr.employees
where employee_id = 100;

출력

exec hr.raise_salary(100,10) -- 100번 사원을 10% 급여 인상하는 것

select employee_id, salary
from hr.employees
where employee_id = 100;

출력

▶ 이 프로그램은 트랜잭션이 발생하기 때문에 꼭 마지막에 롤백을 할지, 커밋을 할지 정해서 써줘야 한다.

 

※ sqlplus에서 에러가 어디서 났는지 확인하는 법 : show error

 

# 프로시저 삭제

drop procedure 삭제할 프로시저명;

 

※ or replace를 쓰는 습관을 가지는 게 좋다. 그 이유는 동일한 프로시저명으로 개발할 경우가 있는데 or replace를 쓰지 않으면 drop하고 또 create 해야 하는데 권한을 다시 줘야 하는 수고가 있기 때문이다.

 

ex) out mode 

create or replace procedure hr.emp_proc
(p_id in number, p_name out varchar2, p_sal out number)
is
begin
    select last_name, salary
    into p_name, p_sal
    from hr.employees
    where employee_id = p_id;
    --dbms_output.put_line(p_name||' '||p_sal);
exception
    when no_data_found then
        dbms_output.put_line(p_id||'번 사원은 존재하지 않습니다.');
end emp_proc;
/

desc hr.emp_proc

출력

 # 호출환경

   - ID는 in mode이기 때문에 예를 들어 100번을 입력해서 프로시저에 값을 전달하는 것이고, p_name, p_sal은 out mode이기 때문에 호출환경에서 변수 처리해서 값을 받는다.

hr.emp_proc(값(in), 변수(out), 변수(out))

var b_name varchar2(30)
var b_sal number
exec hr.emp_proc(100, :b_name, :b_sal)
print b_name b_sal

출력

declare
    v_name varchar2(30);
    v_sal number;
begin
    hr.emp_proc(100, :b_id, :b_sal)
    dbms_output.put_line(v_name||' '||v_sal);
end;
/

 

# 형식 out mode를 사용하고 싶지 않을 경우

   - is 다음에 로컬변수를 선언해서 into절에 사용하면 된다.

create or replace procedure hr.emp_proc
(p_id in number)
is
    v_name varchar2(30);
    v_sal number;
begin
    select last_name, salary
    into v_name, v_sal
    from hr.employees
    where employee_id = p_id;
    dbms_output.put_line(v_name||' '||v_sal);
exception
    when no_data_found then
        dbms_output.put_line(p_id||'번 사원은 존재하지 않습니다.');
end emp_proc;
/

▶ 호출할 때 in mode만 호출하면 된다.

 

문제) 사원번호를 입력값으로 받아서 그 사원의 이름, 급여, 부서이름을 출력하는 프로시저를 생성하세요. 단, 100번 사원이 입력값으로 들어오면 프로그램은 종료할 수 있도록 작성해 주세요.

create or replace procedure hr.query_emp
(p_id in number)
as 
    v_name varchar2(30);
    v_sal number;
    v_dept_name varchar2(30);
    
    eos exception; -- 이셉션 선언
begin
    if p_id = 100 then
        raise eos;
    else
        select e.last_name||' '||e.first_name, e.salary, d.department_name
        into v_name, v_sal, v_dept_name
        from hr.employees e, hr.departments d
        where e.department_id = d.department_id
        and e.employee_id = p_id;
    
    dbms_output.put_line(v_name||' '||v_sal||' '||v_dept_name);
    end if;
exception
    when no_data_found then
        dbms_output.put_line(p_id||'번 사원은 존재하지 않습니다.');
    when eos then
        null; -- 아무 액션을 주지 않을 땐 null 사용
end query_emp; 
/

exec hr.query_emp(101)

출력

exec hr.query_emp(100)

▶ 컴파일은 되지만 출력이 나오진 않는다.

 

# 또 다른 방법 (return 이용)

   - 이셉션을 선언이나, raise문도 안 써도 된다. 이셉션에 따로 코드를 작성하지 않고 if문에 return; 해주면 된다.

   1) 프로시저 return문을 수행하면 프로그램 정상적인 종료

create or replace procedure hr.query_emp
(p_id in number)
as 
    v_name varchar2(30);
    v_sal number;
    v_dept_name varchar2(30);
begin
    if p_id = 100 then
       return;
    else
        select e.last_name||' '||e.first_name, e.salary, d.department_name
        into v_name, v_sal, v_dept_name
        from hr.employees e, hr.departments d
        where e.department_id = d.department_id
        and e.employee_id = p_id;
    
    dbms_output.put_line(v_name||' '||v_sal||' '||v_dept_name);
    end if;
exception
    when no_data_found then
        dbms_output.put_line(p_id||'번 사원은 존재하지 않습니다.');
end query_emp; 
/

 

# 익명 블록에서 return문을 사용할 경우

begin
    dbms_output.put_line('main block 1');
        begin
            dbms_output.put_line('sub block');
            return;
        end;
    dbms_output.put_line('main block 2');
end;
/

출력

▶ 익명 블록에서 RETURN문을 만나는 순간 모든 블록 종료, 즉 프로그램 정상적인 종료

 

ex) 01012345678 -> 010-1234-5678

select substr('01012345678',1,3)||'-'||substr('01012345678',4,4)||'-'||substr('01012345678',8) 전화번호
from dual;

출력

ex) in/out mode

create or replace procedure hr.format_phone(p_no in out varchar2)
is
begin
    p_no := substr(p_no,1,3)||'-'||substr(p_no,4,4)||'-'||substr(p_no,8); -- 함수이기 때문에 가능  
end format_phone;
/

var b_no varchar2(30)
exec :b_no := '01033793560'
print :b_no

exec hr.format_phone(:b_no) -- 초기값이 있는 변수로 설정해야 함
print :b_no

 

출력

 

# 익명 블록에서 실행할 경우

declare
    v_no varchar2(30) := '010123456789';
begin
    hr.format_phone(v_no);
    dbms_output.put_line(v_no);
end;
/

출력

 

# in/out 일 경우

출력

▶ 초기값으로 설정되어 있는 변수를 사용해야 한다.

▶ 값만 쓸 경우 에러가 발생하니까 주의해야 한다. (IN / OUT 다 작용해야 하는데 하나만 작용하기 때문이다.)

 

ex 1) 테이블 생성

CREATE TABLE hr.sawon(id number, name varchar2(30), day date, deptno number);

 

ex 2) 데이터 넣을 때 입력변수로 처리하고 싶을 경우

create or replace procedure hr.sawon_insert
(p_id in number, p_name in varchar2, p_day in date default sysdate, p_deptno in number := 0) -- in mode만 가능하다.
is
begin
    insert into hr.sawon(id, name, day, deptno)
    values(p_id, p_name, p_day, p_deptno);
end sawon_insert;
/

desc sawon_insert

출력

출력

▶ 디벨로퍼에서는 default가 생략되어서 보인다.

 

select text
from user_source
where name = 'SAWON_INSERT'
order by line;

출력

# 호출환경

  - 실제값을 형식매개변수에 위치지정 방식으로 입력

exec hr.sawon_insert(1,'홍길동', to_date('2024-12-30','yyyy-mm-dd'), 10) -- 형식 매개변수 순서대로 입력해야 함
select * from hr.sawon;

출력

 

# 실제값을 형식매개변수에 이름지정 방식 (=>)으로 입력

exec hr.sawon_insert(p_id =>2, p_name=> '박찬호', p_deptno => 20)
select * from hr.sawon_insert;

출력

# 실제값을 형식매개변수에 혼합 방식으로 입력

exec hr.sawon_insert(3,'제임스', p_day => to_date('2024-01-01','yyyy-mm-dd'))
select * from hr.sawon;

# 혼합 방식 시 주의할 점

exec hr.sawon_insert(4, p_name => '손흥민', to_date('2020-01-01','yyyy-mm-dd'), 30);

출력

▶ 이름지정 방식 후 포지셔널( to_date('2020-01-01','yyyy-mm-dd'))하게 쓰면 에러 발생한다. 뒤에도 똑같이 이름지정 방식이 와야 한다.

 

ex)

출력

 

 # 아이디로 접속 후 본인이 받은 권한 확인하기

출력

# hr에서 생성한 query_emp프로시저 문을 anna가 사용할 수 있도록 권한 부여

grant execute on hr.query_emp to anna; -- 간접 엑세스

 

# 본인이 받은 객체 권한 확인하기

select * from user_tab_privs;

 

# anna에서 실행하면 뜨는 걸 볼 수 있다.

exec hr.query_emp(101)

 

# 사원테이블, sawon_insert 프로시저 권한 주기

grant select on hr.sawon to anna; -- 직접 엑세스
grant execute on hr.sawon_insert to anna; -- 간접 엑세스

출력

▶ 이런 작업을 간접 엑세스라고 한다. (현장에서도 많이 사용함)

▶ 프로그램을 통해서 권한을 주는 것을 간접 엑세스라고 한다.

▶ grant execute on hr.sawon_insert to anna를 통해 간접 엑세스를 하는 이유는 select insert하면 편할 수 있겠지만 필요한 데이터, 필요 없는 데이터를 다 입력해야 한다. 하지만 execute on을 씀으로써 내가 작성한 hr.sawon_insert에 작성해서 만든 프로시저문을 통해 내가 원하는 값을 입력할 수 있기 때문이다

 

# HR에서 실행하면 똑같은 결과가 나오는 걸 볼 수 있다.

출력