저장 프로시저

프로시저(Procedure)

데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템에 저장한 것으로 영구저장모듈(Persistent Storage Module)이라고도 불립니다.

보통 저장 프로시저를 프로시저라고 부르며, 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다.

---저장 프로시저 만들기-----
create or replace procedure sp_getFirstName(v_empid in employees.employee_id%type,v_fname in employees.first_name%type )

is


begin

    select first_name
    into v_fname
    from employees
    where employee_id = v_empid;
    dbms_output.put_line('직원번호:' || v_empid);
    dbms_output.put_line('직원 fname ' || v_fname);
    

end;
/
desc user_source;
select * from user_source;


------- 직원번호가 들어가면 이름과 급여를 OUT 하기
create or replace procedure sp_emp (v_empid in employees.employee_id%type ,
                                        v_sal out employees.salary%type,
                                           v_fname out employees.first_name%type )
is 
begin 
    select first_name,salary
    into v_fname,v_sal
    from employees 
    where employee_id = v_empid;

end;
/
variable a number;
exec :a :=200;
variable b number;
variable c varchar2(50);


execute sp_emp (200, :b , :c);
print a;
print b;
print c;
-----------------------PL/SQL 로 함수 만들기----------------------------------
SELECT UPPER('oracle'),f_bonus(105)
FROM DUAL;

select employee_id,salary , f_bonus(employee_id) , salary*200
from employees;

--직원 번호가 들어가면 직원급여 * 200 return 한다.
create or replace function f_bonus(v_empid in employees.employee_id%type)
    return number
is
    v_sal employees.salary%type;
begin
    select salary
    into v_sal
    from employees
    where employee_id = v_empid;
    return v_sal*200;
end;
/
----procedure : 미리 컴파일된 프로그램 ,오래걸리는 작업을 수행해야 한다면 자바 application 에서 SQL 문을 DB 에 보내는것보다 빠르다.
----------------직원번호가 들어오면 직원이 근무하는 부서의 이름과 근무도시를 out하는 PROCEDURE 를 작성하시오
create or replace procedure SP_EMP3 (v_empid in employees.employee_id%type, 
                                    v_deptname out departments.department_name%type,
                                    v_city out locations.city%type
)
is
begin 
    select department_name , city
    into v_deptname,v_city
    from  employees 
    join departments using (department_id)
    join locations using(location_id)
    where employee_id = 100;

end;
/   
variable v_empid number;
exec :v_empid: = 105;
variable v_deptname varchar2(50);
variable v_city varchar2(50);

exec SP_EMP3(:v_empid,:v_deptname, :v_city);
print v_empid;
print v_deptname;
print v_city

------------------------직원의 급여는 salary + salary * commission_pct 입니다. 급여를 ㅈ주면 실수령액이 return 되는 function을 작성하시오
create or replace function f_salary(v_empid in employees.employee_id%type)
return number
is
    v_salary employees.salary%type;
begin
    select salary + salary * nvl(commission_pct,0)
    into v_salary
    from employees
    where employee_id = v_empid;
    return v_salary;
end;
/
select employee_id , salary,commission_pct,f_salary(employee_id),lower(first_name)
from employees;