Duritz's Fun Life

--- ○ Trigger
-- 트리거의 종류
-- 1. before trigger : DML이 발생하기 이전에 검사할때 사용
-- 2. after trigger (가장많이 쓰임) : DML이 발생한후 
-- 3. 문장 trigger
-- 4. 행 trigger (가장많이 쓰임)
create table workchart
(no number
,nalja date default sysdate
,work varchar2(300)
)tablespace users;
--// workchart테이블에 DML작업은 업무시간에만 가능하도록 Before & 문장 trigger를 작성
--   업무시간대란? 월-금, 09:00 - 16:00 이전까지
create or replace trigger btri_workchart
before
insert or update or delete on workchart
declare
    worknottime exception; -- exception : 사용자가 정의하는 오류메세지 임을 정의

begin
  if to_char(sysdate,'d') in ('1','7') or
   to_number(to_char(sysdate,'hh24')) < 9 or
   to_number(to_char(sysdate,'hh24')) > 15
  then raise worknottime; -- raise 오류내용 변수 : 사용자가 정의한 오류메세지를 띄운다.
  -- then 다음에 사용자가 정의하는 오류내용과 DML작업을 할 수 없게 한다.
  end if;
  exception
      when worknottime then
          raise_application_error
       (-20001,'월요일부터 금요일 오전9시부터 오후4시까지만 작업이 가능합니다.');
          -- 오류번호는 -20001~-20999까지만 사용가능하다.
  --insert or update or delete를 하기전에 if문 조건에 맞으면 해당 오류번호와 오류 메세지를 출력한다.
end;
/
select * from workchart;
insert into workchart(no,work) values(1,'일을 열심히 했음')
-- 위트리거 생성 조건에 맞으면 DML문이 실행이 안되고 아니면 실행이 된다.
alter trigger btri_workchart disable; -- 비활성화 시키면 트리거 조건에 상관없이 입력된다.
----------------------------------------------------------------------------------------------
--// After & 행 트리거
create table member
(id varchar2(20),
passwd varchar2(20) constraint member_passwd_nn not null,
name varchar2(20) constraint member_name_nn not null,
hp varchar2(20),
constraint member_id_pk primary key(id) using index tablespace indx
) tablespace users;

create table login
(id varchar2(20),
passwd varchar2(20),
constraint login_id_pk primary key(id) using index tablespace indx,
constraint login_id_fk foreign key(id)
     references member(id) on delete cascade
) tablespace users;

select * from member;
select * from login;
---- member 테이블 입력값중 특정행을  1:1 관계에 있는 login테이블에 자동으로 입력이 되도록
---- 하는 것을 after & 행 trigger라고 한다.
create or replace trigger atri_member_ins
after
insert on member -- 멤버 테이블에서 입력이 되어진 다음에 
for each row     -- 특정행을 잡아낸다. (행트리거일때 항시 사용)

begin
  insert into login values(:new.id, :new.passwd);
  -- trigger내에서 방금 입력된값은 :new에 저장이 되어진다.
end;
/
insert into member values('superman','pass123','홍길동','010-6647-6348');
insert into member values('eom','junghwa','엄정화','019-8897-1567');
-- member 테이블에 값을 입력하면 자동적으로 login테이블에 입력이 된다.
-- update = 기존date delete + 새로운date insert
create or replace trigger atri_member_upd
after
update of id,passwd on member -- update 다음에 변경되어질 컬럼을 항시 명시해 주어야 한다.
             -- 변경되어지기 이전값은 (:old)에 저장이 되어진다.
for each row
begin
  update login set id = :new.id, passwd = :new.passwd
  where id = :old.id;
end;
/
update member set id='batman', passwd='pass007'
where id='superman';

update member set passwd='pass007'
where id='superman';
commit;
rollback;

delete member
where id='superman';
-----------------------------------------------------------------------------------------
select * from DEPT;
select * from emp;
----- 1:다 관계의 두 테이블에서 1:1처럼 on delete cascade로 자동 삭제가 되지 않지만
--    트리거를 이용하여 참조하는 pk,fk를 자동으로 변경시켜줄 수 있다.
create or replace trigger aftri_upd
after  update of deptno on dept
for each row
begin
  update emp set deptno = :new.deptno
  where deptno = :old.deptno;
end;
/
update dept set deptno=11
where deptno = 10
--- 1:다 관계의 테이블에서 부모테이블의 pk가 변경되면
--    자식테이블의 fk가 변경되는 트리거를 만들어주면 좋다.
-----------------------------------------------------------------------------------------
select * from user_source
where type='TRIGGER' and name='AFTRI_UPD'; -- 유저가 가진 소스를 본다.
-----------------------------------------------------------------------------------------
select * from user_triggers; -- 유저가 가진 트리거에대한 내용을 볼수 있다.
-----------------------------------------------------------------------------------------

--- ○ Cursor
create or replace procedure p_empinfo4
(v_jik in emp.job%type)
is
  cursor cur_empinfo
  -- cursor 커서명 is 쿼리문(select문) : 쿼리문조건에 맞는 값을 커서명으로 정의한다.
  is
  select empno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
  from EMP
  where upper(job) = upper(v_jik);
 
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_job emp.job%type;
  v_yearpay number;
begin
  dbms_output.put_line('----------------------------');
  dbms_output.put_line('사원번호 사원명  직종  연봉');
  dbms_output.put_line('----------------------------');
 
  open cur_empinfo;
     loop
       fetch cur_empinfo into v_empno, v_ename, v_job, v_yearpay;
       exit when cur_empinfo%notfound;
       dbms_output.put_line(v_empno || ' ' ||
          v_ename || ' ' ||
         v_job   || ' ' ||
         v_yearpay);   
     end loop;
  close cur_empinfo;
  -- open커서명으로 커서를 열고 close커서명으로 커서를 닫는다.
  -- fetch 커서명 into 변수는 한행한행의 값을 변수에 기억시킨다
  -- loop로 반복해서 행값을 계속 변수에 기억만 하면 무한 loop에 빠지니
  -- exit when 조건, 으로 반복문에서 빠져나올 조건을 만든다.
  -- exit when 커서명%notfound는 커서명으로 정의한것의 값이 행이 찾아지지 않는
  -- 즉, 행이 끝나면 반복문을 나오게 된다.
end;
/
----------------------------------------------------------------------------------------
--- ○ For loop Cursor
create or replace procedure p_empinfo5
(v_jik in emp.job%type)
is
  cursor cur_empinfo
  is
  select empno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
  from EMP
  where upper(job) = upper(v_jik);
 
begin
  for result in cur_empinfo loop
    dbms_output.put_line(result.empno || ' ' ||
              result.ename || ' ' ||
              result.job   || ' ' ||
              result.yearpay);
  end loop;
  -- for loop cursor문은 해당 커서가 행이 끝날때까지 반복되어진다.
  -- 그냥 cursor처럼 open,end,fetch 할 필요가 없다. (프로시져가 간단해진다.)
end;
/
----------------------------------------------------------------------------------------
select * from sawon;
select * from v_sung;
---|| 연습문제 : exec p_sawon('남'); 를 실행하면 ('여'를 치면 여자만)
-- 남자의 (사원번호 사원명 현재나이 입사일자 정년일자)가 나오게 프로시져를 만들어라.
create or replace view v_sung
as
select sano, case when substr(Y.minbun,7,1) in ('1','3') then '남'
             else '여' end sex,
       name, age, hiredate,
    add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-age)) jung   
from (select sano, name, minbun,
     case when substr(minbun,7,1) in ('1','2')
       then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1899)
          when substr(minbun,7,1) in ('3','4')
    then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1999)
       end age,
             to_char(hiredate,'yyyy-mm-dd') hiredate
      from SAWON) Y;

create or replace procedure p_sex
(v_sung in varchar2)
is
  cursor cur_sexgubun
  is
  select sano, name, age, hiredate, jung
  from v_sung
  where sex = v_sung
begin
  dbms_output.put_line('----------------------------------');
  dbms_output.put_line('사원번호 사원명 나이 입사일 정년일');
  dbms_output.put_line('----------------------------------');
 
  for result in cur_sexgubun loop
  dbms_output.put_line(result.sano       || ' ' ||
          result.name       || ' ' ||
          result.age        || ' ' ||
          result.hiredate   || ' ' ||
          result.jung);
  end loop;
end;
/
exec p_sex('남')
----------------------------------------------------------------------------------------
--|| 위문제에서 '남', '여'가 아닌 0(남여모두), 1(남자), 2(여자) 이경우를 출력하여라
--  완 료 못 했 음 나 중 에 해 보 기 --
create or replace view v_sung
as
select sano, case when substr(Y.minbun,7,1) in ('1','3') then '남'
    else '여' end sex,
       name, age, hiredate,
    add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-age)) jung   
from (select sano, name, minbun,
     case when substr(minbun,7,1) in ('1','2')
       then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1899)
          when substr(minbun,7,1) in ('3','4')
    then extract(year from sysdate) - (to_number(substr(minbun,1,2)) + 1999)
       end age,
             to_char(hiredate,'yyyy-mm-dd') hiredate
      from SAWON) Y;

create or replace procedure p_sex
(v_sung in number)
is
  cursor cur_sexgubun
  is
  select sano, name, age, hiredate, jung
  from v_sung
  where sex = v_sung;
    
begin
  dbms_output.put_line('----------------------------------');
  dbms_output.put_line('사원번호 사원명 나이 입사일 정년일');
  dbms_output.put_line('----------------------------------');
 
  for result in cur_sexgubun loop
  dbms_output.put_line(result.sano       || ' ' ||
          result.name       || ' ' ||
          result.age        || ' ' ||
          result.hiredate   || ' ' ||
          result.jung);
  end loop;
end;
/
-------------------------------------------------------------------------------------------------
select *
from user_source;  -- 유저가 만든 피로시져, 함수등의 소스를 보여준다.
-------------------------------------------------------------------------------------------------

--- ○ 배 열 (테이블 타입형 변수 - oracle에서)
--|| 배열을 쓰기위해서는 출력값이 저장될 변수를 테이블 타입형 변수로 정의를 해주어야 한다.
--|| 반복문 안에 들어갈 쿼리문을 미리 view로 생성해주면 좋다.
create or replace view v_emppay
as
select empno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
from EMP;

create or replace procedure p_empinfo2
(v_jik in varchar2)
is
  type empnotbltype is table of emp.empno%type index by binary_integer;
  -- type 테이블타입형변수명 is table of 데이타타입지정
  -- index by binary_integer는 쉽게 말해 변수에 들어갈 값들의 방번호를 의미한다.
  type enametbltype is table of emp.ename%type index by binary_integer;
  type jobtbltype is table of emp.job%type index by binary_integer;
  type yearpaytbltype is table of number index by binary_integer;
 
  v_empno empnotbltype;
  v_ename enametbltype;
  v_job jobtbltype;
  v_yearpay yearpaytbltype;
 
  i binary_integer := 0; -- 방번호로 사용할 변수지정 및 시작값 지정
begin
  for result in(select * from v_emppay where upper(job) = upper(v_jik)) loop
     -- for 변수 in다음에는 쿼리문을 넣을수 있고 이것은 해당 쿼리문의 결과값 row가 없으면
  -- for 문을 빠져나오게 된다.
  -- result(for문에 사용되는 변수)에 저장되는 값은 수식이 들어올수 없으므로
  -- 쿼리문을 넣을때는 항시 alias 처리를 해주어야 한다.
  i := i + 1;  --- 오라클에서 방시작은 1부터시작되기때문에 1로 맞추어 주어야 한다.
  v_empno(i) := result.empno;
  v_ename(i) := result.ename;
  v_job(i) := result.job;
  v_yearpay(i) := result.yearpay;
  -- 테이블타입형 변수에 값이 들어가기위해서는 방번호를 입력해야하는데
  -- 방번호 또한 변수가 되기때문에 위에서 방번호로 지정해준 binary_integ로
  -- 지정해주고 테이블타입형 변수()안에 넣어주면 된다.
     end loop;
    
     dbms_output.put_line('-------------------------------');
     dbms_output.put_line('사원번호   사원명   직종   연봉');
     dbms_output.put_line('-------------------------------');
    
     for s in 1..i loop
       dbms_output.put_line(v_empno(s) || '  ' || v_ename(s) || '  ' ||
              v_job(s)   || '  ' || v_yearpay(s));
     end loop;
end;
/
/* exec p_empinfo2('salesman') */
/* 이러한 프로시져를 사용하는것은 오라클서버에 부하를 좀더 줄이기 위해 사용한다. */
----------------------------------------------------------------------------------------
-----|| 연습문제 : exec p_empinfo3(부서명); 을 입력하면
--      부서번호  사원명  직급  연봉  이 출력되는 프로시져를 생성하시오.
create or replace view v_empjoin
as
select E.deptno, ename, job, coalesce(sal*12+comm, sal*12, 0) "YEARPAY"
from EMP E join dept D
on E.deptno = D.deptno;

select * from v_empjoin;
drop view v_empjoin;

create or replace procedure p_empinfo3
(v_buname in varchar2)
is
  type deptnotbltype is table of emp.deptno%type index by binary_integer;
  type enametbltype is table of emp.ename%type index by binary_integer;
  type jobtbltype is table of emp.job%type index by binary_integer;
  type yearpaytbltype is table of number index by binary_integer;
 
  v_deptno deptnotbltype;
  v_ename enametbltype;
  v_job jobtbltype;
  v_yearpay yearpaytbltype;
 
  i binary_integer := 0;
begin
  for result in(select * from v_empjoin
         where deptno = (select distinct D.deptno from emp E, dept D
                      where E.deptno = D.deptno and upper(D.dname) = upper(v_buname))) loop
   i := i + 1;
  v_deptno(i) := result.deptno;
  v_ename(i) := result.ename;
  v_job(i) := result.job;
  v_yearpay(i) := result.yearpay;
  end loop;
 
  dbms_output.put_line('-------------------------------');
     dbms_output.put_line('부서번호   사원명   직급   연봉');
     dbms_output.put_line('-------------------------------');

     for s in 1..i loop
       dbms_output.put_line(v_deptno(s) || '  ' || v_ename(s) || '  ' ||
              v_job(s)    || '  ' || v_yearpay(s));
     end loop; 
end;
/
/* exec p_empinfo3('sales') */
---↓ 선생님 풀이 ↓---
create or replace view v_empdname
as
select D.dname, D.deptno || '   ' ||
                rpad(E.ename,10) || ' ' ||
                rpad(E.job,10) || ' ' ||
                coalesce(E.sal*12+E.comm, E.sal*12, 0) sainfo
from emp E join dept D
on E.deptno=D.deptno;
    
select * from v_empdname;

create or replace procedure p_empinfo3
(v_dname in v_empdname.dname%type)
is
   type empinfotype
   is table of v_empdname.sainfo%type
   index by binary_integer;
  
   i binary_integer := 0;

   v_empinfo empinfotype;
begin

 for result in(select sainfo from v_empdname
               where upper(dname) = upper(v_dname)) loop
     i := i + 1;                  
     v_empinfo(i) := result.sainfo;                 
 end loop;                  

dbms_output.put_line('----------------------------');
dbms_output.put_line('부서번호 사원명  직종  연봉');
dbms_output.put_line('----------------------------');

for s in 1..i loop
  dbms_output.put_line(v_empinfo(s));
end loop;

end;
/
----------------------------------------------------------------------------------------

--- ○ 반복문
create table irumtbl
(no number
,name varchar2(10)
) tablespace users; ---  테이블 생성

--- 1. loop 반복문
create or replace procedure p_irumtblins
(v_name in varchar, v_cnt in number)
is
  v_no irumtbl.no%type := 0;  --- 변수지정에서 := 을하면 초기치값을 넣어줄 수 있다.
begin
  loop   -- 반복문 시작  (loop)
   v_no := v_no + 1;
   insert into irumtbl values(v_no, v_name||v_no);
  exit when v_cnt = v_no;  -- 해당 조건이 맞으면 반복문을 빠져 나온다. (exit when)
     end loop; -- 반복문 끝
end;
/
/* exec p_irumtblins('홍길동',10) */
select * from irumtbl;
---------------------------------------------------------------------------------------------
delete irumtbl;

--- 2. for loop 반복문
create or replace procedure p_irumtblins
(v_name in varchar, v_cnt in number)
is

begin
  for a in 1..v_cnt loop
  -- for 루프문 : for 변수 in 시작값..매개변수 loop
  --              시작값을 변수에 넣고 그 변수값이 1씩 증가시켜
  --              입력한 매개변수까지 해당 반복문 실행후 빠져나온다.
  -- 여기서 in 다음에 reverse를 입력하게되면(for a in reverse 1..v_cnt loop)
  -- 변수에 시작값부터 오는것이 아니라 매개변수값부터 넣어주고 1씩 감소시켜 실행한다.
   insert into irumtbl values(a, v_name || a);
  end loop;
end;
/
/* exec p_irumtblins('이순신',10) */
select * from irumtbl;
---------------------------------------------------------------------------------------------
delete irumtbl;

--- 3. while loop 반복문
create or replace procedure p_irumtblins
(v_name in varchar, v_cnt in number)
is
  v_no irumtbl.no%type := 0;
begin
  while v_cnt > v_no loop
  -- while 조건 loop : 조건이 참이면 해당 반복문을 계속 실행하고 거짓이면 반복문을 나온다.
   v_no := v_no + 1;
   insert into irumtbl values(v_no, v_name || v_no);
     end loop;
end;
/
/* exec p_irumtblins('엄정화',10) */
select * from irumtbl;

---- Function (함수생성)
create or replace function f_tax
(v_sano in emp.empno%type)
return number     --- 결과물 값을 해당 문자 타입으로 return시킨다.
is
  v_yearpay number(6);
  v_result number;
begin
  select coalesce(sal*12+comm,sal*12,0) into v_yearpay
  from emp
  where empno = v_sano;
                                -- 일반적인 수식에서는 = 를쓰나 
  if v_yearpay >= 40000 then v_result := v_yearpay*0.1;      -- 해당 변수에 값을 대입할때는
  elsif v_yearpay >= 30000 then v_result := v_yearpay*0.08;  -- ':=' 를 쓴다.
  elsif v_yearpay >= 20000 then v_result := v_yearpay*0.05;
  else v_result := v_yearpay*0.03;
  end if;
 
  return v_result;
end;
/

select empno, ename, f_tax(empno) tax
from emp
where f_tax(empno) > 1000;
--///////////////////////////////////////////////////////////////////////////////////////////--
--///////////////////////////////////////////////////////////////////////////////////////////--
/* 프로시저와 함수는 만드는 방법은 비슷하나 프로시저는 함수처럼 쿼리문 안에 넣을수 없고
   결과값만 바로 출력해주는것이다. 함수는 쿼리문안에 넣어서 사용할 수 있다.           */

---- ★ PL(Procedure Language)/SQL
--- Prosedure
create or replace procedure p_empinfo
(v_sano in number) -- number타입의 매개변수를 v_empno(지정한 변수)에 입력 받는다.
is -- is 다음에는 사용할 변수를 정의해 준다.
   v_empno number(4);    -- ┐
   v_ename varchar2(20); -- │ 하드코딩 데이타 타입 (데이타가 변경되서 자리수를 넘으면 곤란해짐)
   v_job varchar2(20);   -- │ 잘 사용 안하는 타입이다.
   v_yearpay number(6);  -- ┘
begin   -- begin절 속에는 DDL문과 DCL문은 사용할 수 없다. 즉, select와 DML, TCL문만 사용가능하다.
  -- 그런데 select문은 반드시 into를 사용한 변수로 처리를 해야한다.
 select empno, ename, job, coalesce(sal*12+comm,sal*12,0)
     into v_empno, v_ename, v_job, v_yearpay
 from emp
 where empno = v_sano;

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_empno || ' ' ||
       v_ename || ' ' ||
       v_job   || ' ' ||
       v_yearpay);
 -- dbms_output.put_line은 C언어에서 사용하는 print문같은 오라클에서 사용하는 print문이다.
end;
/
/* 명령프롬프트에서 프로시져생성시 오류가 나게되면 SQL> show errors로 오류를 확인후 수정하면된다. */
/* 명령프롱프트에서 출력값을 보려면 set serveroutput on을해서 활성화 시켜줘야한다. */
----------------------------------------------------------------------------------------------
execute p_empinfo(7499);   -- 입력한 변수를 출력하는 문 execute(또는 exec) 프로시져네임(변수)
---|| 연습문제 : p_empinfo2(사원번호)를 입력하면 부서명, 사원명, 직종, 연봉이 출력되는 Procedure를 생성
create or replace procedure p_empinfo2
(v_sano in number)
is
   v_dname varchar2(20);
   v_ename varchar2(20);
   v_job varchar2(20);
   v_yearpay number(6);
begin
 select D.dname, E.ename, E.job, coalesce(E.sal*12+comm,E.sal*12,0)
  into v_dname, v_ename, v_job, v_yearpay
 from emp E, dept D
 where E.deptno = D.deptno and E.empno = v_sano;

 dbms_output.put_line('--------------------------');
 dbms_output.put_line('부서명  사원명  직종  연봉');
 dbms_output.put_line('--------------------------');
 dbms_output.put_line(v_dname || ' ' ||
          v_ename || ' ' ||
                v_job   || ' ' ||
                v_yearpay);
end;
/
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
--// 하드코딩 데이타타입을 대신할 방법 //--
-- 1. 해당테이블 데이타타입을 참조하게 한다.
create or replace procedure p_empinfo
(v_sano in number)
is
   v_empno emp.empno%type; -- 해당테이블 데이타타입을 참조하게 한다.
   v_ename emp.ename%type;
   v_job emp.job%type;
   v_yearpay number(6);    -- 컬럼이 아닌경우는 타입값을 넉넉히 주어야 한다.
begin
 select empno, ename, job, coalesce(sal*12+comm,sal*12,0)
     into v_empno, v_ename, v_job, v_yearpay
 from emp
 where empno = v_sano;

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_empno || ' ' ||
       v_ename || ' ' ||
       v_job   || ' ' ||
       v_yearpay);
end;
/
-----------------------------------------------------------------------------------------------------
-- 2. view를 생성하여 만든다.
create or replace view v_empinfo 
as
select empno, ename, job, coalesce(sal*12+comm,sal*12,0) yearpay
from emp;           -- view 생성

select * from v_empinfo; 

create or replace procedure p_empinfo
(v_sano in number)
is
  v_result v_empinfo%rowtype;  -- 해당 뷰에 있는 모든 행의 데이타 타입을 받아온다.
begin
 select * into v_result
 from v_empinfo
 where empno = v_sano;

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_result.empno || ' ' ||
       v_result.ename || ' ' ||
       v_result.job   || ' ' ||
       v_result.yearpay);
end;
/
-----------------------------------------------------------------------------------------------------
-- 3. 새로운 데이타 타입을 선언
create or replace procedure p_empinfo
(v_sano in number)
is
  type emprcd                 -- 새로운 데이타 타입을 선언해준다.
  is record(a emp.empno%type,
      b emp.ename%type,
      c emp.job%type,
      d number(6)
      );
  v_emprcd emprcd;  -- v_emprcd는 emprcd 라는 변수를 사용하는 데이타 타입임을 명시
begin
 select empno, ename, job, coalesce(sal*12+comm,sal*12,0)
     into v_emprcd
 from emp
 where empno = v_sano;

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_emprcd.a || ' ' ||
       v_emprcd.b || ' ' ||
       v_emprcd.c || ' ' ||
       v_emprcd.d);
end;
/
-----------------------------------------------------------------------------------------------------
--|| 위에 배운것을 이용하여 매개변수를 문자로 받아본다.
create or replace procedure p_empinfo2
(v_jikjong in varchar2)
is
  v_result v_empinfo%rowtype;
begin
 select * into v_result
 from v_empinfo
 where upper(job) = upper(v_jikjong); -- 실제 데이타 입력값이 대문자 혹은 소문자로 들어올수 있는데
                -- 오라클은 대소문자 구분을 하기때문에 upper로 모두 대문자로 변경

 dbms_output.put_line('-------------------------');
 dbms_output.put_line('사원번호 사원명 직종 연봉');
 dbms_output.put_line('-------------------------');
 dbms_output.put_line(v_result.empno || ' ' ||
       v_result.ename || ' ' ||
       v_result.job   || ' ' ||
       v_result.yearpay);
end;
/
/*지금까지 한것은 하나의 변수여서 레코드 값이 하나만 나오기때문에 값이 두개 이상인 결과물이 나오게 되면
  이것은 프로시져 생성시는 문제가 없으나 결과값에서 오류가 난다. 이럴경우 반복문을 사용하여야한다.*/

--- ○ Sequence
create table sunbun
(no number
,name varchar2(10)
) tablespace users;

create sequence seq_2
start with 3            -- minvalue의 값은 start의 값보다 같거나 작아야 한다.
increment by 2
maxvalue 10
minvalue 1
cycle                   -- max값이 넘으면 반복을한다.
      -- nocyle은 max값이 초과되면 정지한다.
nocache;     -- 메모리에 기억해 둘것인지 여부를 명시 (cache)

select * from sunbun;

insert into sunbun values(seq_1.nextval,'홍길동');
insert into sunbun values(seq_1.nextval,'이순신');
insert into sunbun values(seq_1.nextval,'엄정화');
insert into sunbun values(seq_1.nextval,'김정화'); --- 여기까지는 순차대로 입력이 된다.
insert into sunbun values(seq_1.nextval,'이정화'); --- 여기는 max값이 넘으므로 다시 cycle되서
                    

--- min값이 된다.
drop table sunbun    -- 테이블 삭제
drop sequence seq_1  -- sequence 삭제

create table sunbun1
(no number
,name varchar2(10)
) tablespace users;

create table sunbun2
(no number
,name varchar2(10)
) tablespace users;

create sequence seq_no
start with 1
increment by 1
nomaxvalue              -- 1E27까지 증가 생성된다. (max값을 주지 않는다)
nocycle                 -- nocycle을 하면 minvalue를 써줄 필요가 없다.
nocache;                -- min값이 없으면 자동으로 -1E27까지 생성가능하다.

insert into sunbun1 values(seq_no.nextval,'일정화');
insert into sunbun1 values(seq_no.nextval,'이정화');
insert into sunbun1 values(seq_no.nextval,'삼정화');
    -- 값을 3개 입력하였으니 1,2,3 으로 입력이 되는데
select * from sunbun1;

insert into sunbun2 values(seq_no.nextval,'사정화');
insert into sunbun2 values(seq_no.nextval,'오정화');
    -- 앞에서 sequence를 사용했기때문에 테이블이 달라져도 계속 적용이되서 순번이 4,5가 나온다.
select * from sunbun2;

insert into sunbun1 values(seq_no.nextval,'육정화');
insert into sunbun1 values(seq_no.nextval,'칠정화');
    -- 다시 테이블을 바꿔 입력해도 계속 순차데로 입력이 되어진다.
select *
from user_sequences
where sequence_name='SEQ_NO'; -- 시퀀스의 내용을 보여준다.

select seq_no.currval from dual; -- 현재까지 사용한 seq 숫자를 보여준다.
/* 테이블값이 삭제되고 다시 생성한 seq로 다시 테이블 값을 넣으면 시작값부터 시작하는것이 아니라
   소모된 seq다음의 값부터 시작된다. 그러므로 게시판 게시글 순번같은 곳에는 사용을 권장하지 않는다. */

alter sequence seq_no   -- alter에는 start를 넣을 수 없다.
increment by 2          -- 증가치를 바꾸려면 alter를 써서 변경하면된다.
nomaxvalue              -- 하지만 start값을 바꾸려면 seq를 지우고 다시 만들어야 한다.
nocycle                
nocache;

select *
from user_sequences
where sequence_name='SEQ_NO';  -- seq내용을 확인해보면 증가치가 변경된것을 볼 수 있다.

--|| 우편번호 데이타파일을 가지고 테이블에 저장하기 ||--
-- 1. 우편번호 데이타 파일을 다운받는다. (http://www.zipfinder.co.kr/ 참조)
/* 엑셀파일을 "," 형식의 파일로 바꿀려면 xx.csv 형태로 바꾸어주어야 한다. */
-- 2. 파일을 받았으면 파일내용에 맞게 테이블을 생성한다.
create table ziptbl
(zipcode varchar2(10)
,sido varchar2(30)
,gugun varchar2(200)
,dong varchar2(200)
,bunji varchar2(200)
) tablespace users;
-- 3. 이제 로드 할 수 있도록 ctl 파일을 생성한다 (생성은 상단 파일생성 참조)
-- 4. 명령프롬프트에서 명령을 실행하여 데이타를 로드한다. (상단 로드 명령어 참조)
-- 5. 테이블에 잘 입력되었나 확인해본다.
select * from ziptbl;

select *
from ziptbl
where dong='용흥동'; -- 원하는 동만 검색하나 풀검색을 하기 때문에 시간이 많이 소요된다.
--- index 생성
--// where 절에 자주 사용되어지는 컬럼에 생성한다.
--   단. 해당컬럼에 찾고자 하는 데이터가 전체 데이터에 10% - 15% 미만일 경우에만 생성한다.
--   10% - 15% 넘을 경우는 index를 생성하지 않는것이 성능에 좋다.
--   index가 많으면 유지 관리가 힘들고 DML처리속도가 느려지기  때문에 꼭 필요한 컬럼만 사용하는것이 좋다.
create index idx_ziptbl_dong -- index 생성
on ziptbl(dong);             -- dong 컬럼에 중복된 데이터값을 허용하는 것이다.

drop index idx_ziptbl_dong;  -- index 삭제

create table heowon
(id varchar2(10) not null
,name varchar2(10)
,jubun varchar2(13)
,addr varchar2(10)) tablespace users;

create unique index idx_heowon_id    -- 테이블 생성시 not null을 주고 unique index를 주면
               -- pk와 동일하게 작용한다.
on heowon(id);

create unique index idx_heowon_jubun
on heowon(jubun);

create index idx_heowon_name
on heowon(name);
--// index는 해당 유저의 default된 곳에 저장이 되어진다.
select *
from user_indexes
where table_name = 'HEOWON'; -- 해당 테이블의 인덱스 내용을 보여준다.

select *
from user_ind_columns
where table_name = 'HEOWON'; -- 해당 테이블에 인덱스된 컬럼내용을 보여준다.
/* 해당 테이블과 그 테이블에 해당하는 index는 서로 다른 tablespace에 있어야 물리적으로 좋다 */
create index idx_heowon_name  -- 이처럼 별도로 인덱스만 저장가능한 tablespace를 생성하여 저장한다.
on heowon(name)
tablespace indx;              -- indx는 별도로 생성해준 index 저장 tablespace
 
select * from heowon;

insert into heowon values('aaa','홍길동','8010101234567','서울');
insert into heowon values('aaa','김유신','9010101234567','서울');
insert into heowon values('bbb','김유신','8010101234567','서울');
---// 위 데이타를 입력해 보면 김유신은 데이타 입력이 안되게 된다.
--    이것은 id와 jubun에 unique index를 지정하여서 해당 컬럼이 PK로 작용하기 때문이다.
insert into heowon values('ccc','이순신',null,'서울');
insert into heowon values('ddd','엄정화',null,'서울');
--// 위 두개의 데이타를 입력해 보면 jubun에 null을 허용했기 때문에 null값이 들어온다.
--   또한 엄정화도 null을 주게되면 중복데이타기때문에 안들어 갈거 같지만.
--   index에서는 null을 제외해 버리기 때문에 오류없이 입력이 된다. 
insert into heowon values('eee','김유신','8210101234567','수원');
--// name 컬럼은 일반 index로 했기때문에 동일한 값이여도 입력이 가능하다.
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
/* 제약조건으로 primary key 와 unique 제약을 생성하면 자동적으로 unique한 index가 생성되어진다. */
/*//// salesman 으로 접속하여 실행 ////*/
select *
from user_indexes
where table_name='SAWON';  -- 테이블의 인덱스 내용을 보여줌

select *
from user_ind_columns
where table_name='SAWON';  -- 테이블의 해당 컬럼의 인덱스 내용을 보여줌

drop index sawon_jubun_uk; -- 직접 생성한 인덱스가 아니고 pk나 unique등 제약조건으로 생성된 index는
             -- drop index로는 지워지지가 않는다.
--- 제약조건으로 생긴 index를 지우려면 다음과 같이 실행해야한다.
alter table sawon
drop constraint sawon_jubun_uk; -- 1. 제약조건을 없애거나
-- 또는 
alter table sawon
enable constraint sawon_jubun_uk;  -- 2. 제약조건을 disable 시키면 인덱스가 사라진다.
                --    다시 enable 시키면 재 생성된다.
/* 테이블 생성시 pk,unique 제약조건을 줄시 생성되는 index는 테이블과 동일한 tablespace에
   위치하게 된다. 이것은 물리적으로 좋지 않기 때문에 테이블 생성시 나누어 주어야 한다. */
create table test01
(id varchar2(10)
,name varchar2(10)
,constraint test01_id_pk primary key(id)
) tablespace users;

select index_name, tablespace_name
from user_indexes
where table_name='TEST01';   -- 이렇게 생성하면 index가 테이블과 동일한 tablespace에 저장이 된다.

create table test02
(id varchar2(10)
,name varchar2(10)
,constraint test02_id_pk primary key(id) using index tablespace indx
) tablespace users;                      -- using index tablespace (저장할 테이블스페이스명)
              -- 이렇게 테이블을 생성해야만 다른 tablespace에 
              -- index가 저장이 되어진다.
select index_name, tablespace_name
from user_indexes
where table_name='TEST02';     -- 해당 테이블의 index 내용을 보여준다.