Duritz's Fun Life

---- 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 내용을 보여준다. 

--- ○ View
create or replace view v_sawonexpire
as
select Y.*,
       add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-Y.현재나이)) "정년일",
       trunc(add_months(to_char(sysdate,'yyyy') || '-02-05', 12*(60-Y.현재나이)) - sysdate,0) "남은일수",
       trunc(sysdate - to_date(Y.입사일,'yyyy-mm-dd'),0) "근무일"
from (select 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 "현재나이",
             to_char(hiredate,'yyyy-mm-dd') "입사일"
      from SAWON) Y;
--- create or replace view 뷰명 as (문장) : 해당문장을 지정한 뷰명으로 만들되
--                                          해당 뷰명이 있으면 replace(교체) 하고 없으면
--                                          새롭게 생성한다.
select * from v_sawonexpire;

select *
from v_sawonexpire
where extract(year from to_date(정년일,'yyyy-mm-dd')) >= 2050; -- 뷰 사용한 예제

create or replace view v_emp10
as
select empno, ename, sal
from emp
where deptno = 10;

select * from v_emp10;

create or replace view v_emp10
as
select empno, ename, sal, comm, coalesce(sal*12+comm, sal*12, 0) "YearPay"
    -- 원래 테이블에 있는 순수한 컬럼이 아니고 수식을 컬럼처럼 출력할때는 별칭을 해주어야한다.
from emp
where deptno = 10;

create or replace view v_emp10(사원번호, 사원명, 연봉) -- 뷰명 옆에 직접 별칭을 줄수 있다.
as
select empno, ename, coalesce(sal*12+comm, sal*12, 0)
from emp
where deptno = 10;

select 사원명  --- view를 사용한 예시문
from v_emp10
where 연봉 >= 2000
--// view와 view, 테이블과 view 모두 join이 되어진다.
--------------------------------------------------------------------------------------------------
-- view의 원본소스를 보자.
select * from tab; -- 해당유저의 테이블과 뷰를 모두 보여준다.

select *
from user_views
where view_name = 'V_SAWONEXPIRE'; -- 해당 뷰의 정보를 나타낸다.

select text
from user_views
where view_name='V_SAWONEXPIRE'; -- 해당 뷰의 텍스트 정보를 나타낸다.

----- 컬럼의 추가 및 제거하기 -----
--1. 컬럼추가
alter table sawon
add email varchar2(20);

select * from sawon;
--2. 컬럼삭제
alter table sawon
drop column email;

alter table sawon
add email varchar2(20) default ' ' constraint sawon_email_nn not null;
  -- 빈테이블이라면 상관이 없지만 데이타가 있는 상태에서 컬럼추가시에는 값이 null이기 때문에
  -- not null 조건을 주게되면 실행되어지지 않는다.
  -- 그래도 not null로 하고 싶으면 dafault값을 넣어서 실행하면된다.
--3. 컬럼의 데이타타입 변경
alter table sawon
modify (sano number(4));
 
----- 테이블명 변경하기 -----
rename sawon to jikwon; -- 테이블명을 변경한다.

select * from sawon; -- 기존에 쓰던 이름은 사용이 안된다.
select * from jikwon;

----- 컬럼명 변경하기 -----
alter table sawon
rename column jik to job;

alter table sawon
rename column job to jik;

----- 제약조건명 바꾸기 -----
alter table sawon
rename constraint sawon_jik_ck to sawon_jik_check; -- 제약조건명 변경 완료

----- 테이블에 주석문 달기 -----
comment on table sawon
is '우리회사 사원정보테이블';

select * from user_tab_comments; -- 해당 유저가 사용하는 테이블의 comment(주석문)을 확인할수 있다.

----- 컬럼명에 주석문 달기 ------
comment on column sawon.mgr is '관리자 사원번호';

select *
from user_col_comments
where table_name='SAWON' -- 유저가 사용하는 테이블중 원하는 테이블의 컬럼 내용확인

----- 행 삭제하기 -----
select * from buseo;

delete buseo where buno=104; -- 참조되어진 자식테이블에 해당하는 fk값이 없으므로 삭제가 된다.
delete buseo where buno=101; -- 그러나 101은 자식테이블에서 fk에 값이 있으므로 삭제가 안된다.
                             -- 삭제를 하려면 제약조건을 삭제 또는 비활성화 해야한다.

/*//// 생성한 salesman으로 접속하여 실행 ////*/
-- 테이블 생성하기 --

--- 1 : 다 관계의 테이블에서 먼저 1(부모) 테이블을 생성해야한다.
drop table buseo;

create table buseo
(buno number(3),
buname varchar2(20) constraint buseo_buname_nn not null
-- 컬럼레벨 constraint : 컬럼안에 주어지는 제약조건을 말한다.
-- 그냥 not null 이라해도 되지만 차후 유지 관리를 위해서 이름을 지어서 not null을 해준다.
,addr varchar2(100)
,constraint buseo_buno_pk primary key(buno)); -- constraint 제약 조건
-- Table level Constraint : 컬럼다음에 오는 제약조건을 말한다.

-- constraint(제약조건)의 종류 --
-- 1. primary key
-- 2. unique
-- 3. foreign key
-- 4. check
-- 5. not null

----- 제약조건을 조회한다. -----
select *
from user_constraints;

----- buseo 테이블에 데이터입력 -----
insert into buseo values(101,'총무부','서울');
insert into buseo values(102,'연구부','서울');
insert into buseo values(103,'영업부','수원');
insert into buseo values(104,'생산부','인천');

select *
from buseo;
commit;
insert into buseo values(104,'생산부','인천'); -- buno는 이미 pk로 지정했기때문에 같은 번호가 올 수 없다.
insert into buseo values(105,null,'인천'); -- buname은 not null로 지정했기 때문에 null값이 될수가 없다.
insert into buseo values(105,'인사부',null); -- addr는 null 값이 가능하다.
rollback;
insert into buseo values(105,'인사부'); -- 컬럼수가 맞지않아서 자료추가가 안된다.
insert into buseo(buno,buname) values(105,'인사부');
      -- 이와같이 두개의 컬럼만 지정하면 두개컬럼값만 지정할 수 있다.
rollback;

--- 사원 테이블을 생성하자 (자식테이블)
create table sawon
(
sano number(4) constraint sawon_sano_nn not null,
      -- pk로 지정하면 굳이 not null을 안해도 되지만 해주는것을 권장한다.
      -- 테이블을 복사하면 not null제약만을 제외하고
                -- 나머지 모든 제약조건과 default 값은 복사가 안 되어진다.
                -- 그래서 not null을 붙여주는것을 권장한다.
saname varchar2(10) constraint sawon_saname_nn not null,
jubun varchar2(13) constraint sawon_jubun_nn not null,
jik varchar2(10) default '사원', -- default 값을 넣어주면 값이 지정되지 않으면 해당 default값이 된다.
sal number(4),
comm number(4),
hiredate varchar2(10) default to_char(sysdate,'yyyy-mm-dd'),
buno number(3), -- 부모테이블에서의 fk로 작용하기 때문에 부모테이블의 pk와 동일(number(4))해야한다.
mgr number(4), -- sawon 테이블의 sano의 fk로 작용하기때문에 sano와 동일하게 해야한다.
constraint sawon_sano_pk primary key(sano),
constraint sawon_jubun_uk unique(jubun),
       -- unique key는 자동 not null이 되지 않기 때문에 컬럼생성시 not null 조건을 넣어야한다.
constraint sawon_jubun_ck check(to_number(jubun) > 0 and
                 substr(jubun,7,1) in ('1','2','3','4') and length(jubun)=13),
constraint sawon_jik_ck check(jik in ('사장','부장','과장','대리','사원')),
constraint sawon_sal_ck check(sal between 1000 and 5000 and sal > comm),
constraint sawon_comm_ck check(comm between 0 and 2000),
constraint sawon_hiredate_ck check(extract(year from to_date(hiredate,'yyyy-mm-dd')) >= 1980),
constraint sawon_buno_fk foreign key(buno) references buseo(buno),
       -- references에서 정해준 테이블의 컬럼값을 현재테이블 컬럼에 fk로 지정한다.
constraint sawon_mgr_fk foreign key(mgr) references sawon(sano)
);

select *
from sawon

insert into sawon values
(1001,'백승만','7910121234567','사장',5000,1000,'1980-01-05',101,null);

insert into sawon values
(1002,'윤봉길','8102201234567',default,3000,default,default,103,1001);

insert into sawon(sano,saname,jubun,sal,buno,mgr) values
(1003,'엄정화','7410102234567',2000,103,1001);
--/// 테이블네임다음에 컬럼명을 명시해 주지 않으면 모든 컬럼값을 넣어줘야 하는데
--    해당 컬럼값에 default를 명시해 주었으면 default값이 들어오고
--    모든 컬럼값을 넣기 힘들면 원하는 컬럼만 정해주고 넣어도 된다.

--|| default로 정의되어진 값을 변경하고자 한다.
select column_name, data_default
from user_tab_cols
where table_name='SAWON';  -- 사원테이블의 각 컬럼별 default값을 보여준다.
 
alter table sawon       -- alter table 테이블명 modify 컬럼명 default 원하는값;
modify comm default 0;  -- 위와같이 실행하면 해당컬럼의 default값이 변경된다.

---- 생성되어진 sawon테이블의 재약조건을 조회 ----
select *
from user_constraints
where table_name='SAWON'

select *
from user_cons_columns
where table_name='SAWON' -- 컬럼의 제약조건을 보여준다.

select B.constraint_name, A.column_name,B.constraint_type, B.search_condition, B.status
from user_cons_columns A join user_constraints B
on A.constraint_name = B.constraint_name
where A.table_name = 'SAWON';  -- 해당 테이블의 제약조건 리스트를 보여준다.

---- 제약조건을 제거하거나 수정하기 ----
--//. 제약조건을 수정하려면 먼저 해당 제약조건을 제거하고 새로이 추가해야한다.
alter table sawon
drop constraint sawon_jik_ck; -- 원하는 제약조건 삭제

alter table sawon
add constraint sawon_jik_ck check(jik in('사장','부장','과장','대리','주임','사원')); --제약조건추가

---- not null 제약 제거 및 추가하기 ----
alter table sawon
drop constraint sawon_saname_nn; -- not null 제약을 삭제함으로 제약조건 삭제
---- 또는
alter table sawon
modify saname null; -- null 값을 허용해서 not null 제약을 없앤다.
--- 다시 추가하기
alter table sawon
add constraint sawon_saname_nn saname not null;  -- 오류 문법이다.

alter table sawon
modify saname constraint sawon_saname_nn not null; -- not null 제약은 항시 이렇게 추가해야한다.

----- 제약조건의 활성화/비활성화 -----
--1. 비활성화
alter table sawon
disable constraint sawon_saname_nn; -- 해당 제약조건을 비활성화 한다. (존재는 하나 사용 안하게된다.)
--2. 활성화
alter table sawon
enable constraint sawon_saname_nn; -- 해당 제약조건을 다시 활성화 시킨다.

alter table buseo
disable constraint buseo_buno_pk; -- 해당 문법은 실행이 되지 않는다.

select B.constraint_name, A.column_name,B.constraint_type, B.search_condition, B.status
from user_cons_columns A join user_constraints B
on A.constraint_name = B.constraint_name
where A.table_name = 'BUSEO';

select *
from user_constraints
where table_name = 'SAWON' and constraint_type='R';

select *
from user_cons_columns
where constraint_name = 'BUSEO_BUNO_PK'
------ PK 제약조건은 FK와 참조되어지기 때문에 그냥은 지워지지 않는다.
alter table buseo
disable constraint buseo_buno_pk cascade; -- cascade를 해주면 PK,FK둘다 비활성화가 되어진다.

alter table buseo
enable constraint buseo_buno_pk; -- cascade를 넣지않고 enable 하면 다시 활성화 되어진다.
alter table sawon
enable constraint sawon_buno_fk; -- 부모테이블에 pk를 먼저 활성화 한뒤 자식테이블의 fk를 활성화 한다.

/* SYS로 접속하여 실행 */
--- TABLE CREATE ---
-- 1. 테이블 스페이스 생성
create tablespace develtbs
datafile 'c:\data\develtbs01.dbf' size 2048k -- size는 64kb의 배수로 하는것이 좋다.
extent management local
segment space management auto;
--|| 생성되어진 tablespace 조회
select *
from dba_data_files;

-- 2. user create
create user orauser1 identified by pass123 --user만들기
default tablespace develtbs -- 위에서 생성한 tablespace사용
temporary tablespace temp; -- temp는 유저가 oder by를 할때 사용할 공간을 설정해주는 것이다.
--|| 연결권한을 줘야 접속이 가능하다.
grant create session to orauser1;
--|| 새로만들어진 계정으로 접속하기
create table testtbl
(no number, name varchar2(10)); -- 권한이 없어서 테이블 생성이 안된다.
--|| orauser1 자신에게 부여되어진 system 권한을 조회
select *
from user_sys_privs;
--|| sys로 다시 접속하여 테이블 생성 권한을 준다.
grant create table to orauser1;
--|| 새로운 유저로 접속하여 권한을 조회하면 권한이 되있다
--|| 그리고 테이블을 생성하면 할당량이 주어지지 않아서 또다시 테이블 생성이 안된다.
select *
from user_users; -- 테이블 저장하는 테이블 스페이스 조회

select *
from user_ts_quotas; -- ts(tablespace)의 공간조회
--|| sys로 할당량을 준다.
alter user orauser1
quota 1024k on develtbs
quota 1024k on temp;
--|| 다시 새로운 유저로 공간조회를 하면 공간이 주어지게된다. 그리고 테이블도 생성이 된다.
create table testtbl
(no number, name varchar2(10));  -- 이것은 아무이상없이 생성이 된다.

insert into testtbl values(1, '홍길동');
select * from testtbl; -- 자료를 넣어주고 select하면 테이블이 생성된것을 확인할수 있다.

create table testtbl2
(name varchar2(10)) tablespace develtbs;
  
create table testtbl3
(name varchar2(10)) tablespace users; -- 이것은 users에 테이블 생성이 가능하도록 주어지지 않았기때문에
                                      -- 생성이 되지 않는다. sys가 권한을 주어야 한다.
                                      -- tablespace users라고 입력을 하지 않게되면 해당 테이블은
           -- 처음유저 생성시 sys가 정해준

테이블스페이스에 저장이 되게 된다.
--|| sys로 users테이블스페이스에 무제한 할당량을 주어본다.        

  
alter user orauser1
quota unlimited on users;
--|| 다시 새로 생성한 유저로 와서 테이블을 생성해보자 
create table testtbl3
(name varchar2(10)) tablespace users;

select * from user_tables; -- 사용하는 테이블을 검색해보면 사용하는 테이블 수와
                           -- 테이블 스페이스도 확인 할 수 있다.
-------------------------------------------------------------------------------------------
=============================  개 발 자 용  U S E R 생 성

=================================
-------------------------------------------------------------------------------------------
/* sys로 접속하여 다음과 같이 생성을 한다. */
create user javauser1 identified by pass123
default tablespace develtbs
temporary tablespace temp;

grant connect, resource to javauser1;

select * from dba_roles;  -- 권한 그룹을 표시해 준다.

select *
from dba_sys_privs
where grantee in ('CONNECT','RESOURCE'); -- 해당 권한 그룹(ROLE)에서 가지고 있는 권한(PRIVS)을 보여준다.

/* 생성한 유저로 접속하여 다음과 같이 해보자 */
select * from user_sys_privs; -- 유저가 가진 권한을 보여준다.

create table mytab1
(name varchar2(10));  

create table mytab2
(addr varchar2(20)) tablespace users;   -- 위 두개의 테이블은 별도의 권한을 주지 않았는데도 생성이 된다.

select * from user_tables;

select * from user_role_privs;  -- 유저에게 부여된 role(권한그룹)을 보여준다.

select *
from role_sys_privs
where role in ('CONNECT','RESOURCE')  -- role안에 포함되어진 권한(privs)들을 보여준다.
--// 아무 무리 없이 테이블이 생성된다. 이러한 이유는 sys에서 유저 생성시 connect, resource라는
--   권한 그룹(ROLE)에 대하여 권한을 주었기 때문에 그 권한 그룹안에 포함되어진 권한(privs)들이 자동적으로
--   적용이 되어 일일이 하나씩 권한을 주지 않아도 된다.

/*/////// sys로 접속하여 실행 ///////*/
--- salesman이라는 사용자를 제거 ---
drop user salesman; -- 삭제가 안된다.  cascade를 하라고 나온다.
drop user salesman cascade; -- cascade를 넣어주면 유저 소유의 object를 삭제후 유저를 삭제한다.
             -- 단, 삭제할시엔 해당 유저를 접속종료한뒤 삭제한다.
             
-- Tablespace를 제거한다. --
select *
from dba_data_files; -- 현재 tablespace로 사용하는 파일들을 보여준다.

drop tablespace salestbs
including contents and datafiles; -- 원하는 tablespace 파일을 삭제한다. (절대 탐색기에서 임의로 지우지 말것)

--- ○ 데이터 조작어
---- insert, update, delete commit, rollback
---- merge 명령어 : A서버에서 B원격서버에 A서버의 내용을 동기화할때 사용한다.
update emp set ename='백승익'
where ename='KING'

select * from emp
where deptno = 10;
--// 다른 오라클 서버에 연결할 수 있도록 datebase link를 생성한다.
--- 1. 접속할 서버의 net service 명을 생성한다.
--- 2. database link를 생성한다.
create database link boaeoracle
connect to scott identified by lion
using 'FIREWALLBOAE' -- FIREWALLBOAE라는 넷서비스로 연결된 오라클서버에 대해 boaeoracle이라는 이름으로
                     -- 해당 서버에 scott계정에 접속하는 database link를 생성한다.
--/// boaeoracle이 database link 명이다.
select * from emp
where deptno = 10; -- 자신의 로칼 데이타베이스

select * from emp@boaeoracle -- 원격 오라클 서버이다.
where deptno = 10; -- 생성한 다른 오라클 서버 데이타베이스 자료 출력
------ 새로운 테이블 생성 ------
create table reservation
(reservno number, name varchar2(10)
,tel varchar2(13)
,constraint reservation_reservno_pk primary key(reservno)) tablespace users

select *from reservation;

select *from reservation@boaeoracle;
--- merge 사용하기
insert into reservation values(1,'백승익','010-6275-2735');

merge into reservation@boaeoracle R
using reservation L
on (L.reservno = R.reservno)  -- 로컬서버 자료와 원격서버 자료와 동일한게 있는지 여부 묻기
               -- 반드시 고유한 컬럼으로 정해야 한다.
when matched then                           -- 동일하다면 다음 자료로 업데이트 해라
  update set R.name=L.name, R.tel=L.tel
when not matched then                       -- 동일하지 않다면 다음 값을 넣어라
  insert(reservno, name, tel)
  values(L.reservno, L.name, L.tel);
--/// on 조건에 match되면 matched then 다음을 실행하고 (업데이트)
--              match가 안되면 not matched then 다음을 실행하게 된다. (새로운값 입력)
 
insert into reservation values(2,'이영애','011-6275-8699'); -- merge 실행

update reservation set name='이미자', tel='018-777-8974'
where reservno=2; -- merge 실행
-----------------------------------------------------------------------------------------
create table empcopy
tablespace users
as select * from emp;

select * from empcopy;
---- delete : 해당 부분을 삭제 한다.
delete empcopy where deptno=30;
---- rollback : commit한 이후로 실행되어진 DML(insert, update, delete, merge)의 명령을 취소한다.
---- commit : DML문장의 명령을 적용시킨다.
---- merge : DML문을 실행시키면 실행되어진 DML문의 해당행은 자동으로 Exclusive lock(배타적잠금)이 걸린다.
update empcopy set comm=120 where deptno=20;
    -- 현문장은 아직까지  commit이나 rollback을 하지 않은 상태이다. 그러므로 deptno가 20번인 행들은
    -- 배타적 잠금이 걸린다. 그래서 다른 세션에서는 deptno20번인 행들을 update하기전의 값으로
    -- select는 가능하지만 deptno 20번인 행들은 DML문을 할 수가 없다. 대기상태에 빠진다.
    -- 그러나 원래세션에서 update한 문장(모든DML)을 commit이나 rollback하면 대기상태에 빠진 다른
    -- 세션에서 실행한 DML문장은 대기상태에서 빠져나와 자동으로 실행되어진다.

 --// 그래서 잠금(lock)의 종류는 2가지가 있다. 첫번째가 방금해본 exclusive lock(배타적 잠금)이고
 --// 두번재가 share lock(공유잠금)이 있다.
 --// exclusive lock(배타적 잠금)는 DML문장을 했을때 DML문장을 실ㄹ행한 세션에서 Commit이나
 --// Rollback문을 해야만 다른 세션에서 잠금이 풀리어 또 다른 DMl문장을 할 수 있는 것이고
 --// share lock(공유잠금)은 select 문을 했을때 결과물 데이터가 완료되어 추출되기전까지
 --// 자동적으로 해당 테이블이 잠기는 것을 말한다. 하지만 select 결과물이 나오기만 하면
 --// 자동적으로 share lock(공유잠금)은 풀립니다.
 
 --// 그렇다면 select 되어진 데이터를 계속해서 잠금유지상태를 원하고자 한다면 어떻게 할까?
 --// 예를들어 은행에서 돈을 인출할때 조회후 인출과 같은 경우이다.
create table saving
(geno number, jango number, constraint saving_geno_pk primary key(geno)) tablespace users;

insert into saving values(1,2000);
insert into saving values(2,3000);

select * from saving;
----- 잔고가 얼마 있는지 조회후 인출을 하려고 한다.
----- 즉, 먼저 select를 한다음에 update를 하려고 한다.
select jango
from saving
where geno=1;

update saving set jango=jango-500     /* 다른곳에서 update saving set jango=jango-2000 where geno=1; */
where geno=1                          /* 을 했다면 돈이 빠지지 않게된다. */
------- 그럼 조회하면서도 잠금을 유지 하려면 다음과 같이 한다.
select jango
from saving
where geno=1
for update;   /* 이렇게 하게되면 commit이나 rollback을 하기전까진 다른곳에서 자료 변경이 안된다. */