15. PL/SQL - Procedure(프로시저), Function(함수), Package(패키지), Trigger(트리거)

---

PL/SQL - Oracle Sub-Program

: Procedure(프로시저)

create [or replace] procedure procedure_name
[(  parameter1 [mode1] datatype1, 
    parameter2 [mode2] datatype2,
    ...)] 
is | as 
PL/SQL block; 

1) 사번을 입력받아 해당 사원의 급여를 5000으로 수정하시오(procedure).

CREATE PROCEDURE up_sal(vid employees.employee_id%type)
	IS 
		BEGIN
			UPDATE EMPLOYEES SET SALARY = 5000 WHERE EMPLOYEE_ID = vid;
		END;

		
		EXEC up_sal(206);
		SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=206;

2) 사번과 급여를 입력받아 해당 사원의 급여가 입력받은 급여로 수정되게 하시오. 

CREATE PROCEDURE up_sal2(vid employees.employee_id%type, vsal employees.salary%type)
	IS 
		BEGIN
			UPDATE EMPLOYEES SET SALARY = vsal WHERE EMPLOYEE_ID = vid;
		END;
		

		EXEC up_sal2(206, 7000);
		SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=206;

3) 부서 코드를 입력받아 부서명을 출력하시오.(departments 테이블 사용)

DROP PROCEDURE up_sal3;
CREATE PROCEDURE up_sal3(vid employees.department_id%type)
	IS 
		dname departments.department_name%type;
		BEGIN
			SELECT department_name INTO dname FROM DEPARTMENTS WHERE DEPARTMENT_ID = vid;
			dbms_output.put_line(dname);
		END;
		
		

EXEC up_sal3(80);

4) 급여 두 개를 입력받아 사원의 급여가 입력받은 두 개의 급여 사이인 직원의 이름, 급여를 출력하시오.

DROP PROCEDURE up_sal4;
CREATE PROCEDURE up_sal4(sal1 employees.salary%type, sal2 employees.salary%type)
	IS
		BEGIN
		FOR i IN (SELECT FIRST_NAME na, SALARY sa FROM EMPLOYEES WHERE SALARY BETWEEN sal1 AND sal2) LOOP
			dbms_output.put_line(i.na||'		'||i.sa);
			END LOOP;
		END;

4-2) Cursor를 사용해 문제 풀어보기.

CREATE or replace PROCEDURE up_sal4_2(sal1 employees.salary%type, sal2 employees.salary%type)
	IS
	cursor c1 is
		select first_name name, salary sal from employees where salary between sal1 and sal2;
		BEGIN
		for i in c1 LOOP
			dbms_output.put_line(i.name||'		'||i.sal);
			END LOOP;
		END;

EXEC up_sal4_2(5000, 8000) ;

5) 사번을 입력받아 성명, 급여, 연락처, 이메일, 부서명, 우편번호를 출력하시오.

DROP PROCEDURE up_sal5;
CREATE PROCEDURE up_sal5(vid employees.employee_id%type)
	IS
	na employees.first_name%type;
	sa employees.salary%type;
	ph employees.phone_number%type;
	em employees.email%type;
	dna departments.department_name%type;
	pos locations.postal_code%type;
		BEGIN
			SELECT e.first_name, e.SALARY, e.PHONE_NUMBER, e.EMAIL, d.department_name, l.postal_code
			into na, sa, ph, em, dna, pos 
			FROM EMPLOYEES e 
			JOIN DEPARTMENTS d ON e.department_id = d.department_id 
			JOIN LOCATIONS l ON d.location_id = l.location_id
			WHERE EMPLOYEE_ID = vid;
			dbms_output.put_line(na||'	'||sa||'	 	'||ph||'	 '||em||' 	'||dna||'	'||pos);
		END;

EXEC up_sal5(100) ;

5-2) Cursor를 사용해 문제 풀기.

CREATE or replace procedure emp_c(vid employees.employee_id%type)
	IS
	cursor c1 is
		select e.first_name na, e.SALARY sa, e.PHONE_NUMBER ph, e.EMAIL em, d.department_name dna, l.postal_code pos
		from employees e JOIN DEPARTMENTS d ON e.department_id = d.department_id 
			JOIN LOCATIONS l ON d.location_id = l.location_id
			WHERE EMPLOYEE_ID = vid;
		BEGIN
		for i in c1 LOOP
			dbms_output.put_line(i.na||'	'||i.sa||'	 	'||i.ph||'	 '||i.em||' 	'||i.dna||'	'||i.pos);
			END LOOP;
		END;

exec emp_c(100);

번외) ANSI타입이 아닌 ORACLE타입으로 join해보기. 

-join이 빠지고 콤마(',')로 묶인다. 

-on절 대신 where절이 온다. 

create or replace procedure emp_c(vid employees.employee_id%type)
	is
	cursor c1 is
		select e.first_name na, e.SALARY sa, e.PHONE_NUMBER ph, e.EMAIL em, d.department_name dna, l.postal_code pos
		from employees e, DEPARTMENTS d, locations l where e.department_id = d.department_id 
			and d.location_id = l.location_id and EMPLOYEE_ID = vid;
		begin
		for i in c1 LOOP
			dbms_output.put_line(i.na||'	'||i.sa||'	 	'||i.ph||'	 '||i.em||' 	'||i.dna||'	'||i.pos);
			end LOOP;
		end;

PL/SQL - Oracle Sub-Program

: 2. Function(함수)

기본 골격

create [or replace] function function_name
[(parameter1 [mode1] datatyp1,
    parameter2 [mode2] datatyp2,
    ...)] return datatype
    is | as
    pl/sql block;   

create or replace function 함수이름
    return number --타입 ㅇㅇ...
is
begin

    return 변수명;
end;

* cf. 프로시저 - 사용되고 끝

---

1) 부서 코드를 전달받아 해당 부서의 최대급여를 반환하는 함수 max_sal을 작성하시오. 

create or replace function max_sal(vdept employees.department_id%type)
	return number --타입 ㅇㅇ...
is
	--변수 선언
	maxsal number ;
begin
	select max(salary) into maxsal from employees where department_id = vdept;
	return maxsal;
end;

select max_sal(80) from dual;

2) 부서코드를 입력받아 해당 부서의 직원 수를 반환하는 함수 empCount를 작성하시오.

create or replace function empCount(vdept employees.department_id%type)
	return number
is
	cnt number;
begin
	select count(first_name) into cnt from employees where department_id = vdept;
	return cnt;
end;

select empCount(80) from dual;

3) 부서코드를 입력받아 해당 부서의 우편번호를 반환하는 함수 getPostal을 작성하시오

create or replace function getPostal(vdept departments.department_id%type)
	return locations.postal_code%type
is
	pCode locations.postal_code%type;
begin
	select l.postal_code into pCode 
	from departments d 	join locations l on d.location_id = l.location_id where d.department_id = vdept;
	return pCode;
end;

select getPostal(110) from dual;

4) 사번을 입력받아 해당 직원의 매니저 이름을 반환하는 함수 getManager를 작성하시오. 

create or replace function getPostal(vdept employees.department_id%type)
	return locations.postal_code%type
is
	pCode locations.postal_code%type;
begin
	select l.postal_code into pCode 
	from employees e join departments d on e.department_id = d.department_id 
	join locations l on d.location_id = l.location_id where e.department_id = vdept;
	return pCode;
end;

select getManager(120) from dual;

---

PL/SQL - Oracle Sub-Program

: 3. Package

①패키지 선언부 생성

create [or replace] package package_name
is | as
    public type and item declarations
    subprogram specifications
end pacakge_name;

②패키지 몸체부(Package Body) 생성

create [or replace] package body package_name
    is | as
    private type and item declarations
    subprogram bodies
end pacakge_name;

---

1) function의 1번~4번 문제를 package로 묶어보자. 

-패키지 선언부 생성.

create or replace package pck_emp
	as 	function max_sal(vdept employees.department_id%type) return number;
			function empCount(vdept employees.department_id%type) return number;
			procedure up_sal(vid employees.employee_id%type);
			procedure up_sal3(vid employees.department_id%type);
	end pck_emp;

-패키지 몸체부(Package Body) 생성.

create or replace package body pck_emp
as
	function max_sal(vdept employees.department_id%type)
		return number
	is
		maxsal number ;
	begin
		select max(salary) into maxsal from employees where department_id = vdept;
		return maxsal;
	end max_sal;
	
	function empCount(vdept employees.department_id%type)
		return number
	is
		cnt number;
	begin
		select count(first_name) into cnt from employees where department_id = vdept;
		return cnt;
	end empCount;
	
	PROCEDURE up_sal(vid employees.employee_id%type)
	is
	begin
		update EMPLOYEES set SALARY = 5000 where EMPLOYEE_ID = vid;
	end up_sal;
	
	PROCEDURE up_sal3(vid employees.department_id%type)
	IS 
		dname departments.department_name%type;
	BEGIN
		SELECT department_name INTO dname FROM DEPARTMENTS WHERE DEPARTMENT_ID = vid;
		dbms_output.put_line(dname);
	end up_sal3;
end pck_emp;

- 패키지에서 펑션(함수) 실행 

select pck_emp.empCount(80) from dual;
select pck_emp.max_sal(80) from dual;

           

- 패키지에서 프로시저 실행 

exec pck_emp.up_sal(110);
exec pck_emp.up_sal3(110);

---

PL/SQL - Oracle Sub-Program

: 4. Trigger

create [or replace] trigger trigger_name
timing 
    event1 [or event2 or event3 ...] 
on {table_name|view_name|schema|database}
[referencing old as old | new as new]
[for each row [when(condition)]] 
    trigger_body

1) 허용된 시간이 아니라면 입력되지 않도록 trigger를 사용해 작업하시오. 

create or replace trigger t_order3 
before insert on emp
begin
	if(to_char(sysdate, 'hh24:mi') not between '12:45' and '12:50') then
	raise_application_error(-20100, '허용시간이 아님...'); end if;
end;

insert into emp values('이이이', 'gomei', 2000, 82);

2) 데이터가 입력되면 입력 확인 메세지를 출력하는 trigger를 생성하시오.

create or replace trigger tOrderComfirm
after insert on emp
for each row
begin
	dbms_output.put_line(:new.first_name||'의 데이터가 입력됨.');
end;

insert into emp values('이이이', 'gomei', 2000, 82);
select * from emp;

3) 입고테이블에 데이터가 추가되면 재고테이블의 입고수량을 증가시키고

    출고테이블에 데이터가 추가되면 재고테이블에 출고 수량만큼 수량이 감소되는 트리거를 작성하시오.

-a. 입고, 재고, 출고 테이블 생성

create table imp(code 	varchar2(10), ea 	number(4,0));
create table sto(code 	varchar2(10), ea 	number(4,0));
create table expo(code 	varchar2(10), ea 	number(4,0));

-b. 입고 테이블에 자료가 입력되면 재고 테이블에 재고양이 증가되는 트리거 생성

create or replace trigger t_imp 
after insert on imp
for each row
declare
	mEa number(4,0); 
begin
	select count(ea) into mEa from sto where :new.code = code;
	if (mEa =0) then 
		insert into sto values(:new.code, :new.ea);
	else
		update sto set ea = ea + :new.ea where :new.code = code; end if;
end;

insert into imp values('A', 100);
insert into imp values('C', 999);
drop table sto;
select * from sto;
select * from imp;
select * from expo;

-c. 출고 테이블(expo)에 데이터가 추가되면 재고 테이블(stock)에 입력된 데이터 수량만큼 감소하도록 작성 

create or replace trigger t_expo
before insert on expo for each row
declare
	mEa number(4,0); 
begin
	select nvl(ea, 0) into mEa from sto where code = :new.code;
	if (mEa>=:new.ea) then 
		update sto set ea = ea - :new.ea where :new.code = code;
	else
		raise_application_error(-20200, '재고가 없거나 입력 수량이 가진 수량보다 큽니다.'); end if;
end;

insert into expo values('C', 333);
commit;

4) insert, update, delete 문장이 실행되면 해당 내용에 적절한 조치를 하기 위한 이벤트 분리

create or replace trigger t_event
after insert or update or delete on emp for each row
declare
	msg varchar2(100);
begin
	if inserting then
	--emp 테이블에 데이터가 추가되었을 때 조치 내용
	msg:='데이터가 추가됨...';
	elsif updating then
	--emp 테이블의 내용이 수정되었을 때 조치 내용
	msg:='데이터가 수정됨...';
	elsif deleting then 
	--emp 테이블의 데이터가 삭제되었을 때 조치 내용
	msg:='데이터가 삭제됨...';
	end if;
	raise_application_error(-20200, msg);
end; 

insert into emp values('나', 'gomdi', 20000, 608);