Lecture 19 - PLSQL(3)

set serveroutput ON

begin
tel1;
end;
/

set serveroutput OFF

stored procedure

declare                create or replace procedure 프로시저명
                            is
변수 선언             변수선언
begin                  begin
end                     end
/

tel 1 프로시저

create or replace procedure tel1
is
v_tel varchar2(10) := '123456';
begin
v_tel := substr(v_tel, 1, 3) || '-' || substr(v_tel, 4);
dbms_output.put_line('전화번호 : ' || v_tel);
end;
/

tel_main

serveroutput을 tel1에서처럼 직접 치지 말고 템플릿에 적어두어 tel1만 begin에서 명령을 주었다. 왜냐하면 create or replace라는 것을 사용하여 저장시켰기 때문이다.(오라클에 저장이됨)

tel 2 값을 받아오는 프로시저

create or replace procedure tel2(
-- 파라미터 영역
p_tel in varchar2
)
is
-- 변수 선언
v_tel varchar2(10);
begin
v_tel := substr(p_tel, 1, 3) || '-' || substr(p_tel, 4);
dbms_output.put_line('전화번호 : ' || v_tel);
end;
/

tel_main_tel2

set serveroutput ON

begin
--tel1;
tel2(123456);
end;
/

set serveroutput OFF

입력받은 점수로 학점을 계산하는 프로시저 생성
hakjum_test

create or replace procedure hakjum_test(
-- 파라미터 영역
p_hakjum in varchar2
)
is
-- 변수 선언
v_hakjum varchar2(10);
begin
v_hakjum := p_hakjum;
if v_hakjum >= 90 then
dbms_output.put_line('학점은 : A');
elsif v_hakjum >= 80 then
dbms_output.put_line('학점은 : B');
elsif v_hakjum >= 70 then
dbms_output.put_line('학점은 : C');
elsif v_hakjum >= 60 then
dbms_output.put_line('학점은 : D');
else
dbms_output.put_line('학점은 : F');
end if;
end;
/

hakjum_main

set serveroutput ON

begin
hakjum_test(94);
end;
/

set serveroutput OFF

강사님이 짠거

create or replace procedure hakjum_test(
-- 파라미터 영역
v_hakjum in number
)
is
-- 변수 선언
begin
if v_hakjum >= 90 then
dbms_output.put_line('학점은 : A');
elsif v_hakjum >= 80 then
dbms_output.put_line('학점은 : B');
elsif v_hakjum >= 70 then
dbms_output.put_line('학점은 : C');
elsif v_hakjum >= 60 then
dbms_output.put_line('학점은 : D');
else
dbms_output.put_line('학점은 : F');
end if;
end;
/

tel 3

create or replace procedure tel3(
p_tel out varchar2
)
is
v_tel varchar2(10) := '123456';
begin
p_tel := substr(v_tel, 1, 3) || '-' || substr(v_tel, 4);
end;
/

간단한 구문은 위처럼 sqlplus에 직접 변수선언해서 명령한다

복잡한 구문은 위와같이 템플릿에 작성하여 명령한다.

단수를 입력받아서 구구단을 출력하는 gugudan procedure 생성
(단, 프로시저 내부에서는 dbms 구문 사용하지 말것)

단수 : 1
출력 : 1 X 1 = 1 ... 1 X 9 = 9

단수 : 2
출력 : 2 X 1 = 1 ... 2 X 9 = 18

gugudan

create or replace procedure gugudan(
p_dan in number,
v_result out varchar2
)
is
v_tot number;
begin
-- chr(10) : 엔터
-- chr(9) : 키
v_result := p_dan || ' 단 ' || chr(10);

for i_col in 1..9 loop
v_tot := p_dan * i_col;
v_result := v_result || p_dan || '*' || i_col || '=' || v_tot || chr(9);
end loop;
end;
/

gugudan_main
set serveroutput ON
DECLARE
v_result varchar2(2000);
begin
--tel1;
--tel2(123456);
--hakjum_test(94);
--tel3(p_tel => g_tel);
--dbms_output.put_line('결과 : ' || g_tel);
gugudan(2, v_result => v_result);
dbms_output.put_line(v_result);

end;
/

set serveroutput OFF

 

사원명을 입력받아서 급여 / 부서이름을 출력하는 프로시저
dname_sal_disp

create or replace procedure dname_sal_disp
(
v_ename in emp.ename%type,
v_dname out dept.dname%type,
v_sal out emp.sal%type
)
is
v_deptno emp.deptno%type;
begin
select sal, deptno
into v_sal, v_deptno
from emp
where lower(ename) = v_ename;

select dname
into v_dname
from dept
where deptno = v_deptno;
exception
when no_data_found then
dbms_output.put_line('입력한 사원은 없습니다.');
when too_many_rows then
dbms_output.put_line('자료가 2건 이상입니다.');
when others then
dbms_output.put_line('기타 에러입니다.');
end;
/
dname_sal_disp_main
set serveroutput on

declare
v_dname varchar2(14);
v_sal number;
begin
dname_sal_disp(
'smith',
v_dname => v_dname,
v_sal => v_sal
);
dbms_output.put_line('v_dname = ' || v_dname);
dbms_output.put_line('v_sal = ' || v_sal);
end;
/

set serveroutput off

ftel1 함수
-- 리턴(계산 결과)

create or replace function ftel1
--return 자료형
return varchar2
is 
--변수 선언
v_tel varchar2(10) := '123456';
begin
-- 처리로직
v_tel := substr(v_tel, 1, 3) || '-' || substr(v_tel, 4);
-- return 값
return v_tel;
end;
/

 

ftel 2 값을 받아오는 함수

create or replace function ftel2(
p_tel in varchar2
)
return varchar2
is 
v_tel varchar2(10);
begin
v_tel := substr(p_tel, 1, 3) || '-' || substr(p_tel, 4);
return v_tel;
end;
/

함수이기 때문에 기존 데이터와 병합이 가능

substr1

create or replace function substr1(
p_str in varchar2
)
return varchar2
is 
v_str varchar2(10);
begin
v_str := substr(p_str, 1, 2);
return v_str;
end;
/

dept_count
부서번호를 입력받아 부서의 총원을 출력하는 함수 dept_count를 생성하라

create or replace function dept_count(
p_deptno in number

)
return varchar2
is 
v_count number;
begin
select count(*)
into v_count
from emp
where deptno = p_deptno;

return v_count; 
end;
/

급여를 입력받아서 호봉(X 등급)을 출력하는 sal_grade 함수
sal_grade

create or replace function sal_grade(
p_sal in number

)
return varchar2
is 
v_grade varchar2(10);
begin
select grade || '등급'
into v_grade
from salgrade
where p_sal between losal and hisal;

return v_grade; 
end;
/

부서번호를 입력받아 다음과 같이 내용을 출력하는 dept_info 함수
총원, 최고급여, 최소급여

create or replace function dept_info(
v_deptno in emp.deptno%type
)
return varchar2
is 
v_count number;
v_maxsal number;
v_minsal number;

begin
select count(*), nvl(max(sal), 0), nvl(min(sal), 0)
into v_count, v_maxsal, v_minsal
from emp
where deptno = v_deptno;

return v_count || ' : ' || v_maxsal || ' : ' || v_minsal; 
end;
/

패키지

emp_info_header
-- emp_info_header.sql
create or replace package emp_info 
as
procedure all_emp_info;
procedure all_sal_info;
procedure dept_emp_info(v_deptno in number);
procedure dept_sal_info(v_deptno in number);
end emp_info;
/
emp_info_body
-- emp_info_body.sql
create or replace package body emp_info
as
procedure all_emp_info
is
cursor emp_cursor is
select empno, ename, to_char(hiredate, 'YYYY/MM/DD') hiredate
from emp
order by hiredate;
begin
for aa in emp_cursor loop
dbms_output.put_line('사번 : ' || aa.empno);
dbms_output.put_line('성명 : ' || aa.ename);
dbms_output.put_line('입사일 : ' || aa.hiredate);
end loop;
exception
when others then
dbms_output.put_line(sqlerrm || '에러 발생 ');
end all_emp_info;

procedure all_sal_info
is
cursor emp_cursor is
select round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
from emp;
begin
for aa in emp_cursor loop
dbms_output.put_line('전체급여평균 : ' || aa.avg_sal);
dbms_output.put_line('최대급여금액 : ' || aa.max_sal);
dbms_output.put_line('최소급여금액 : ' || aa.min_sal);
end loop;
exception 
when others then
dbms_output.put_line(sqlerrm || '에러 발생 ');
end all_sal_info;

procedure dept_emp_info(v_deptno in number)
is
cursor emp_cursor is
select empno, ename, to_char(hiredate, 'YYYY/MM/DD') hiredate
from emp
where deptno = v_deptno
order by hiredate;
begin
for aa in emp_cursor loop
dbms_output.put_line('사번 : ' || aa.empno);
dbms_output.put_line('성명 : ' || aa.ename);
dbms_output.put_line('입사일 : ' || aa.hiredate);
end loop;
exception 
when others then
dbms_output.put_line(sqlerrm || '에러 발생 ');
end dept_emp_info;

procedure dept_sal_info(v_deptno in number)
is
cursor emp_cursor is
select round(avg(sal),3) avg_sal, max(sal) max_sal, min(sal) min_sal
from emp
where deptno = v_deptno;
begin
for aa in emp_cursor loop
dbms_output.put_line('전체급여평균 : ' || aa.avg_sal);
dbms_output.put_line('최대급여금액 : ' || aa.max_sal);
dbms_output.put_line('최소급여금액 : ' || aa.min_sal);
end loop;
exception 
when others then
dbms_output.put_line(sqlerrm || '에러 발생 ');
end dept_sal_info;
end emp_info;
/

트리거

trigger1

create or replace trigger trigger1
before
update on dept2
for each row
begin
dbms_output.put_line('트리거 발생');
end;
/

 

trigger2

create or replace trigger trigger2
before
update on dept2
for each row
begin
dbms_output.put_line('변경 전 :' || :old.loc);
dbms_output.put_line('변경 후 :' || :new.loc);
end;
/
-----------------------------------------------------------------------------------------------------------------------
create or replace trigger trigger2
before
update on dept2
for each row
begin
--dbms_output.put_line('변경 전 :' || :old.loc);
--dbms_output.put_line('변경 후 :' || :new.loc);
insert into dept_history
values(:new.loc, :old.loc, sysdate);
end;
/

트리거 삭제

-----------------------------------------------------------------------------------------------------------------------

트리거 pdf
trigger 3

create sequence emp_audit_seq 
increment by 1 
start with 1 
maxvalue 999999 
minvalue 1 
nocycle 
nocache;

trigger 4

create table emp_audit (
id number(6) constraint emp_audit_pk primary key, 
name varchar2(30), 
gubun varchar2(10), 
wdate date 
);

trigger 5

create or replace trigger emp_audit_tr 
after insert or update or delete on emp2 
begin
if inserting then
insert into emp_audit values (emp_audit_seq.nextval, user, 'inserting', sysdate); 
elsif updating then 
insert into emp_audit values (emp_audit_seq.nextval, user, 'updating', sysdate); 
elsif deleting then 
insert into emp_audit values (emp_audit_seq.nextval, user, 'deleting', sysdate); 
end if; 
end; 
/

trigger 6

select * from emp_audit;
update emp2 set deptno = 20 where deptno = 10;
select * from emp_audit;