ORA-01861

Oracle/기본문법 2013. 6. 13. 13:50

보통 테이블 내 컬럼의 데이터 타입과 SQL구문 내에서 쓴 데이터타입이 일치하지 않는 경우 나타나는 에러인것 같음..


오늘 TOAD 설치 후 한글이 깨지는 문제가 발생해서 여차저차 수정하고 이전에 문제없이 사용했던 인서트 구문을 실행하니

갑자기 떡하니 나오는 01861...-ㅅ-


문제 없던 인서트 구문은 테이블내 날짜 타입 컬럼에 '2000-10-10' 이라는 데이터를 적어놨는데

이전에는 자동으로 날짜타입으로 형변환하여 인서트해서 문제가 없었지만.. 위에 쓴 한글깨지는 오류를 해결하고 나니 인서트가 안됨...ㅡ.ㅡ;;;


연습할때는 상관없겠지만 어차피 묵시적인 형변환보다는 실무에서는 명시적인 형변환을 사용하는 것이 주가 되므로

기존 문제 없던 구문을 아래와 같이 변경함.



INSERT INTO insa (num, name, ssn, ibsaDate, city, tel, buseo, jikwi, basicPay, sudang) VALUES

  (1001, '홍길동', '771212-1022432', to_date('1998-10-11', 'yyyy-mm-dd'), '서울', '011-2356-4528', '기획부', 

   '부장', 2610000, 200000);


여기서 ibsaDate는 당근 날짜타입의 컬럼 ㅇㅇ


되도록 옵티마이저를 믿기보다는 명시적으로 쿼리를 짤 수 있는 버릇을 기르자!


WRITTEN BY
빨강꼬마

,

procedure


1. PL/SQL을 미리 등록해서 일괄적으로 처리할 수 있는 프로그램 집합체.

2. 형식

CREATE OR REPLACE PROCEDURE

(

매개변수 리스트 -- 자료형 지정시 크기지정 불가

)

IS

내부변수 리스트;

BEGIN

실행문;

END;



-- 두개 이상의 테이블에 자료를 분산 저장하는 프로시저

-- ID, PW만 저장하는 전용 테이블. 로그인할 때 주로 사용.

CREATE TABLE test18 (

id varchar2(10)

,pw varchar2(10)

);

ALTER TABLE test18

ADD CONSTRAINT test18_id_pk PRIMARY KEY (id);


INSERT INTO test18(id, pw) VALUES ('test', '1234');

INSERT INTO test18(id, pw) VALUES ('hong', '1234');


COMMIT;


-- 개인정보가 저장된 테이블. 회원 정보 출력시에만 사용.

CREATE TABLE test19 (

id varchar2(10)

,name varchar2(10)

,tel varchar2(10)

,email varchar2(10)

);

ALTER TABLE test19

ADD CONSTRAINT test19_id_fk FOREIGN KEY (id) REFERENCES test18(id);

ALTER TABLE test19

ADD CONSTRAINT test19_id_pk PRIMARY KEY (id)


INSERT INTO test19(id, name, tel, email) VALUES ('test', '테스트', '111-1111' , 'test@nate');

INSERT INTO test19(id, name, tel, email) VALUES ('hong', '홍길동', '222-2222' , 'hong@nate' );


commit;



-- 회원 가입하는 경우(id, pw, name, tel, email을 동시에 입력 받는다.)

INSERT INTO test18(id, pw) VALUES ('kim', '1234');

INSERT INTO test19(id, name, tel, email) VALUES ('kim', '김길동', '333-3333' , 'kim@nate');

COMMIT;



--> INSERT 쿼리를 두개 이상 동시 실행하려면 프로시저로 처리한다.


--프로시저 생성

CREATE OR REPLACE PROCEDURE proInsertMember

(

vid VARCHAR2

,vpw VARCHAR2

,vname VARCHAR2

,vtel VARCHAR2

,vemail VARCHAR2

)

IS


BEGIN

INSERT INTO test18(id, pw) VALUES (vid, vpw);

INSERT INTO test19(id, name, tel, email) VALUES (vid, vname, vtel, vemail);

COMMIT;

END;



-- 프로시저 실행

EXECUTE proInsertMember('park', '1234', '박길동', '444-4444', 'park@nate');


select t18.id, t18.pw, t19.name, t19.tel, t19.email

from test18 t18, test19 t19

where t18.id = t19.id;



-- 전화번호 수정 프로시저 작성. ID, PW가 일치하는 경우만 수정 가능.

UPDATE test19

SET TEL='새로운전화번호'

WHERE id='hong' and pw='1234'; --X


CREATE OR REPLACE PROCEDURE proUpdateTel

(

vid VARCHAR2

,vpw VARCHAR2

,vtel VARCHAR2

)

IS


BEGIN

UPDATE (select t1.id, t1.pw, t2.tel

from test18 t1, test19 t2

where t1.id = t2.id) tt

SET tt.tel=vtel

WHERE tt.id=vid and tt.pw=vpw;


COMMIT;

END;



EXECUTE proUpdateTel('hong', '1234', '새로운전화번호');  -- O



계정(sqluser, 1234) 새로 만들고, emp.sql 실행

-> city, buseo, jikwi, emp 테이블 생성, 제약조건 등록, 시퀀스 등록, 자료 입력됨.


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

emp.sql 내용 실행

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


문제) emp 테이블에서 신규 자료 입력하는 프로시저 작성.



EXECUTE proEmpInsert('김신애', '810809-2111111', '2001-10-10', '서울', '011-4151-4444', '개발부', '사원', 900000 , 102000);


INSERT INTO emp (emp_id, name, ssn, ibsadate, city_id, tel, buseo_id, jikwi_id, basicpay, sudang) 

VALUES (empSeq.nextval

, '김신애'

, '810809-2111111'

, '2001-10-10'

, '서울'  --'서울'이 아니라 city_id가 입력되어야 함

, '011-4151-4444'

, '개발부' --'개발부'가 아니라 buseo_id가 입력되어야 함

, '사원' --'사원'이 아니라 jikwi_id가 입력되어야 함

, 900000 

, 102000); --X



INSERT INTO emp (emp_id

, name

, ssn

, ibsadate

, city_id

, tel

, buseo_id

, jikwi_id

, basicpay

, sudang) 

VALUES (empSeq.nextval

, '김신애'

, '810809-2111111'

, '2001-10-10'

, (select city_id from city where city='서울')

, '011-4151-4444'

, (select buseo_id from buseo where buseo='개발부')

, (select jikwi_id from jikwi where jikwi='사원')

, 900000 

, 102000); --O

COMMIT;


-- 프로시저 생성

CREATE OR REPLACE PROCEDURE proEmpInsert

(

vname VARCHAR2

, vssn VARCHAR2

, vibsadate DATE  

, vcity VARCHAR2 --city명

, vtel VARCHAR2 

, vbuseo VARCHAR2 --buseo명

, vjikwi VARCHAR2 --jikwi명

, vbasicpay  NUMBER   

, vsudang  NUMBER   

)

IS

BEGIN

INSERT INTO emp (emp_id

, name

, ssn

, ibsadate

, city_id

, tel

, buseo_id

, jikwi_id

, basicpay

, sudang) 

VALUES (empSeq.nextval

, vname

, vssn

, vibsadate

, (SELECT city_id FROM city WHERE city=vcity)

, vtel

, (SELECT buseo_id FROM buseo WHERE buseo=vbuseo)

, (SELECT jikwi_id FROM jikwi WHERE jikwi=vjikwi)

, vbasicpay

, vsudang);

COMMIT;

END;






문제) 상품, 입고, 출고 테이블에서 입고 테이블에 데이터 입력시 상품 테이블의 재고 수량이 변동되는 프로시저 작성.

CREATE TABLE 상품 (

상품코드 VARCHAR2(4)

,상품명 VARCHAR2(30)

,소비자가격 NUMBER

,재고수량 NUMBER DEFAULT 0 -- 자동 입력

);

CREATE TABLE 입고 (

입고번호 NUMBER

,상품코드 VARCHAR2(4)

,입고일자 DATE

,입고수량 NUMBER

,입고단가 NUMBER

);

-- 상품 테이블의 상품코드를 PK로 지정

ALTER TABLE 상품

  ADD CONSTRAINT 상품_상품코드_PK PRIMARY KEY(상품코드);


-- 입고 테이블의 입고번호를 PK로 지정

ALTER TABLE 입고

  ADD CONSTRAINT 입고_입고번호_PK PRIMARY KEY(입고번호);


-- 입고 테이블의 상품코드는 상품 테이블의 상품코드를 참조(FK)하는 키 지정

ALTER TABLE 입고

  ADD CONSTRAINT 입고_상품코드_FK FOREIGN KEY(상품코드)

      REFERENCES 상품(상품코드);


-- 상품 테이블의 상품 정보 입력

INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('T001', '스마트TV40인치', 1000000);

INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('T002', '스마트TV50인치', 1500000);

INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('T003', '스마트TV60인치', 2000000);

INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('R001', '냉장고200리터', 1500000);

INSERT INTO 상품 (상품코드, 상품명, 소비자가격) VALUES ('R002', '냉장고250리터', 2000000);

COMMIT;


SELECT * FROM 상품;



-- 입고 테이블에서 입고 절차 진행시 상품 테이블의 재고수량 변동하는 프로시저 작성. 입고 번호는 자동 증가 처리(시퀀스가 아닌 프로시저 내에서 해결).



insert into 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가) values ((SELECT NVL(MAX(입고번호), 0)+1 FROM 입고), 'R002', SYSDATE, 10, 1800000);

UPDATE 상품

SET 재고수량 = 재고수량 + 10

WHERE 상품코드 = 'R002';

COMMIT;

insert into 입고 (입고번호, 상품코드, 입고일자, 입고수량, 입고단가) values ((SELECT NVL(MAX(입고번호), 0)+1 FROM 입고), 'R002', SYSDATE, 10, 1800000);

UPDATE 상품

SET 재고수량 = 재고수량 + 10

WHERE 상품코드 = 'R002';

COMMIT;


SELECT NVL(MAX(입고번호), 0)+1 FROM 입고;   -- 시퀀스를 대신할 수 있는 쿼리를 통한 번호 얻기



-- 프로시저 생성


CREATE OR REPLACE PROCEDURE ibgoInsert

(

v상품코드 VARCHAR2

,v입고수량 NUMBER

,v입고단가 NUMBER

)

IS

BEGIN

INSERT INTO 입고(입고번호, 상품코드, 입고일자, 입고수량, 입고단가) 

VALUES ((SELECT NVL(MAX(입고번호), 0)+1 FROM 입고), v상품코드, SYSDATE, v입고수량, v입고단가);


UPDATE 상품

SET 재고수량 = 재고수량 + v입고수량

WHERE 상품코드 = v상품코드;


COMMIT;

END;


******** 정답

CREATE OR REPLACE PROCEDURE ibgoInsert

(

v상품코드 VARCHAR2

,v입고수량 NUMBER

,v입고단가 NUMBER

)

IS

BEGIN

INSERT INTO 입고(입고번호, 상품코드, 입고일자, 입고수량, 입고단가) 

VALUES ((SELECT NVL(MAX(입고번호), 0)+1 FROM 입고), v상품코드, SYSDATE, v입고수량, v입고단가);


UPDATE 상품

SET 재고수량 = 재고수량 + v입고수량

WHERE 상품코드 = v상품코드;


COMMIT;

END;





문제2) 입고 테이블에서 입고수량을 수정하는 프로시저 작성


--입고번호, 상품코드, 이전수량, 이후수량

--> 입고 기록과 상품 재고가 변동됨.

EXECUTE ibgoUpdate(2, 'R002', 10, 20);


--입고 기록은 10에서 20으로 수정되어야 하고,

--재고 수량은 현재 재고수량에서 +10(이후수량-이전수량)으로 수정되어야 함.


CREATE OR REPLACE PROCEDURE ibdoUpdate

(

v입고번호 NUMBER

,v상품코드 VARCHAR2

,v이전수량  NUMBER

,v이후수량  NUMBER

)

IS

BEGIN

UPDATE 입고

SET 입고수량 = v이후수량

WHERE 입고번호 = v입고번호;


IF v이전수량 < v이후수량 THEN

UPDATE 상품

SET 재고수량 = 재고수량 + (v이후수량-v이전수량)

WHERE 상품코드 = v상품코드;

else

UPDATE 상품

SET 재고수량 = 재고수량 - (v이전수량-v이후수량)

WHERE 상품코드 = v상품코드;

END IF;

END;






문제3) 입고 테이블에서 입고수량을 삭제하는 프로시저 작성


--입고번호, 상품코드

--> 입고 기록과 상품 재고가 변동됨.

EXECUTE ibgoDelete(2, 'R002');


--입고 기록은 삭제되어야 하고,

--재고 수량은 현재 재고수량에서 삭제된 입고수량만큼 줄어야 함.



CREATE OR REPLACE PROCEDURE ibgoDelete

(

v입고번호 NUMBER

,v상품코드 VARCHAR2

)

IS

BEGIN


UPDATE 상품

SET 재고수량 = 재고수량 - (select 입고수량 from 입고 where 입고번호 = v입고번호)

WHERE 상품코드 = v상품코드;


DELETE 입고

WHERE 입고번호 = v입고번호;


END;





권한 관리



1. 권한은 시스템 권한과 객체권한이 있다.


2. 시스템 권한


- 오라클 관리자 암호 변경 (O/S Admin 권한 사용자)

명령프롬프트>sqlpuls  /  as sysdba

sql> alter user sys identified by 새로운암호;


- 사용자 암호 변경 (by 오라클 관리자)

sql> alter user 사용자계정이름 identified by 새로운암호;


- 사용자 암호 변경 (by 사용자)

SQL> ALTER USER 자신의계정이름 IDENTIFIED BY 새로운암호;


--사용자 암호 변경 (타사용자)

SQL> ALTER USER 타사용자계정이름 IDENTIFIED BY 새로운암호;  -- 관리자가 아니므로 불가


--사용자 암호 사용 기간 만료 (관리자)

-->로그인할때는 예전 암호, 로그인하면 새로운 암호 입력하도록 함.

SQL> ALTER USER 사용자계정명 IDENTIFIED BY 임시암호;

SQL> ALTER USER 사용자계정명 PASSWORD EXPIRE; -- 암호를 만료시켜 로그인한 후 암호를 강제적으로 변경하도록 함 (by 관리자)


--사용자 계정 잠금 (관리자)

SQL>ALTER USER 잠글계정명 ACCOUNT LOCK;


--사용자 계정 잠금 해제 (관리자)

SQL>SQL>ALTER USER 해제할계정명 ACCOUNT UNLOCK;


--사용자 객체(테이블) 생성 시도 

SQL>CREATE TABLE 테이블명(con1 number, col2 varchar2(10)); --생성 권한 부족


--사용자 객체 생성 권한 부여 (관리자)

SQL>GRANT CREATE TABLE TO 권한을줄계정명;


--사용자 삭제 (소유한 객체 없는 경우, 관리자)

SQL>DROP USER 계정명;


--사용자 삭제 (소유한 객체 있는 경우, 관리자)

SQL>DROP USER 계정명 CASCADE;


--사용자 삭제 (타사용자)

SQL>DROP USER 타계정명; -- 관리자가 아니므로 불가


--사용자 계정에 관리자 권한 부여 (관리자)

SQL>GRANT DBA TO 권한을줄계정명; -- DBA: 롤명


--계정 연습용 Cuser 사용자 생성 (사용자-관리자 권한 부여)

SQL> 가능


--사용자 암호 변경 (사용자-관리자 권한 부여)

SQL> 가능


--사용자 암호 사용 기간 만료 (사용자-관리자 권한 부여)

SQL> 가능


--사용자 계정 잠금 (사용자-관리자 권한 부여)

SQL> 가능


--사용자 삭제 (사용자-관리자 권한 부여)

SQL> 가능


--사용자 계정에 부여된 관리자 권한 취소 (관리자)

SQL> REVOKE DBA FROM 권한을회수할계정명 -- DBA: 롤명 // DBA 대신 다른 권한을 기재해도 됨.


--계정 연습용 Cuser 사용자 생성 (사용자-관리자 권한 취소)

SQL> 불가


--사용자 암호 변경 (사용자-관리자 권한 취소)

SQL> 불가


--사용자 암호 사용 기간 만료 (사용자-관리자 권한 취소)

SQL> 불가


--사용자 계정 잠금 (사용자-관리자 권한 취소)

SQL> 불가


--사용자 삭제 (사용자-관리자 권한 취소)

SQL> 불가



3. 객체 권한(SQLDeveloper)



scott 계정에서 hr 계정내 테이블에 접근(select 문) 시도



--객체 접근 시도 (관리자)

select * from 테이블을소유한계정명.테이블명; -- 가능


--객체 접근 시도 (타사용자)

select * from 테이블을소유한계정명.테이블명; -- 불가


--객체 접근 권한 부여 (소유자) -- 예를 기준으로 HR 계정만 실행가능 (같은 테이블 스페이스 내에 있을 경우)

grant select on employees to scott; -- SELECT: 객체명


--객체 접근 시도 (타사용자-권한 부여)

select * from 테이블을소유한계정명.테이블명; -- 가능


--특정 정보만 제한하는 뷰 생성 (소유자)

--SALARY와 커미션퍼센트만 보여주기 싫을때 뷰생성해서 뷰 권한을 부여해줌.

CREATE OR REPLACE VIEW employeesView

AS

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, MANAGER_ID, DEPARTMENT_ID

FROM employees;


--제한된 뷰 접근 권한 부여 (소유자)

grant select on employeesView to scott;


--객체 접근 시도 (제한된 뷰 사용, 타사용자-권한 부여)

select * from hr.employeesView;


--객체 접근 권한 취소 (소유자)

revoke select on employees from scott;


--부여한 객체 권한 확인(소유자) -- HR입장

select * from user_tab_privs_made;


--부여된 객체 권한 확인(타사용자) --scott 입장

select * from user_tab_privs_recd;


--객체 수정, 삭제 권한 부여 (소유자)

GRANT SELECT, INSERT, UPDATE, DELETE ON 객체 TO 사용자명;


--객체 수정, 삭제 시도 (타사용자-권한 부여)

--가능


--객체 수정, 삭제 권한 취소 (소유자)

REVOKE SELECT, INSERT, UPDATE, DELETE ON 객체 FROM 사용자명;


WRITTEN BY
빨강꼬마

,

view



1. 뷰는 테이블에서 사용자가 얻기를 원하는 데이터들만을 정확하고 편하게 가져오기 위하여

사전에 원하는 컬럼들만을 모아서 만들어 놓은 가상의 테이블



2. 형식

CREATE OR REPLACE VIEW 뷰이름

AS

SELECT 구문;


서브쿼리로 예를 들면,

SELECT * FROM (서브쿼리 구문);

서브쿼리 구문이 복잡하더라도 결국은 단순한 쿼리문으로 표현할 수 있다.

서브쿼리는 객체로서 등록하지는 못한다.


뷰로 예를 들면,

CREATE OR REPLACE VIEW 뷰이름

AS

서브쿼리 구문;

서브쿼리는 복잡하더라도 결국은 단순한 뷰 구문(SELECT * FROM 뷰이름)으로 표현할 수 있다.

뷰는 객체로서 등록하지는 못한다.



-- 제약조건 확인용 뷰 생성

-- 원본SELECT 쿼리

SELECT uc.owner                         --소유자

, uc.constraint_name            --제약명

, uc.table_name AS table_name   --테이블명

, uc.constraint_type            --제약조건 종류(P, R, U, C)

, uc.search_condition           --CHECK 제약 조건식

, ucc.column_name               --지정된 컬럼

, (SELECT table_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_table_name --참조하는 테이블

, (SELECT column_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_conlumn_name --참조하는 컬럼

FROM user_constraints uc, user_cons_columns ucc

WHERE uc.constraint_name = ucc.constraint_name

AND uc.table_name='테이블명'; -- 외부로 부터 받는 인자는 지우고 추후 추가 where 절을 통해 개별지정.


--뷰생성

CREATE OR REPLACE VIEW constraint_check

AS

SELECT uc.owner                         --소유자

, uc.constraint_name            --제약명

, uc.table_name AS table_name   --테이블명

, uc.constraint_type            --제약조건 종류(P, R, U, C)

, uc.search_condition           --CHECK 제약 조건식

, ucc.column_name               --지정된 컬럼

, (SELECT table_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_table_name --참조하는 테이블

, (SELECT column_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_conlumn_name --참조하는 컬럼

FROM user_constraints uc, user_cons_columns ucc

WHERE uc.constraint_name = ucc.constraint_name;


--뷰 실행

select * from constraint_check

where table_name = 테이블명;



--뷰의 구조 확인

DESC 뷰이름;


--뷰의 소스 확인


SELECT view_name, text

from user_views

where view_name = '뷰 이름';



문제) insa 테이블에서 아래의 결과를 얻는 뷰 작성.

이름, 부서, 출신도, 기본급, 수당, 기본급+수당(총급여), 세금, 실수령액 출력

세금 계산 -> 총급여가 250만원 이상이면 2% 200만원이상이면 1%, 나머지는 0%

실수령액 계산 -> 총급여 - 세금








인라인 뷰


1. 쿼리문에서 from절에 서브쿼리를 사용하여 생성한 임시 뷰이다.

인라인 뷰는 객체로서 저장되지 않는다.


2. 형식

SELECT 컬럼리스트

FROM (서브쿼리);


UPDATE (서브쿼리)

SET 컬럼 = 신규자료, ...

WHERE 조건식;


DELETE (서브쿼리)

WHERE 조건식;



--employees 테이블에서 입사일(hire_date)가 빠른 순으로 5순위까지만 출력


SELECT *

FROM (SELECT first_name, last_name, RANK() OVER(order by hire_date asc) as rank

FROM EMPLOYEES)

where rank <= 5;




--employees 테이블에서 jobs.job_title이 'Shipping Clerk'인 직원의 employees.salary를 10% 인상.


update employees

set salary = salary * 1.1

where job_id = (select job_id

from jobs

where job_title = 'Shipping Clerk');



update (select first_name, last_name, job_title, salary

from employees e, jobs j

where e.job_id = j.job_id) e

set esalary = e.salary * 1.1

where e.job_title = 'Shipping Clerk';



--employees 테이블에서 jobs.job_title이 'Shipping Clerk'인 직원 삭제.


delete (select first_name, last_name, job_title, salary

from employees e, jobs j

where e.job_id = j.job_id) e

where e.job_title = 'Shipping Clerk'



문제) employees 테이블에서 employees.salary를 각 부서 이름별(department_name)로 다른 인상율 적용합니다. 인라인뷰 이용.


Finance -> 10%

Executive -> 15%

Accounting -> 20%

나머지 부서 -> 5%


update(select e.first_name, e.last_name, d.department_name, e.salary

from employees e, departments d) t

set t.salary = case when t.department_name='Finance' then t.salary * 1.1

   when t.department_name='Executive' then t.salary * 1.15

   when t.department_name='Accounting' then t.salary * 1.2

   else t.salary * 1.05 end







PL/SQL (Procedural Language extension to SQL)


1. PL/SQL 은 프로그래밍 언어의 특성을 가지는 SQL의 확장.


2. PL/SQL은 블록 구조로 되어 있으며 선언부, 실행부, 예외처리부로 구성됨


3. 형식

DECLARE

-- 선언문

BEGIN

-- 실행문


[EXCEPTION

-- 예외처리문]


END;



4. PL/SQL 은 변수, 연산자(대입문), 제어문(조건문, 반복문) 등을 지원한다.


--메세지 출력

SET SERVEROUTPUT ON;

DECLARE

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello, Oracle World!');

END;



--변수 선언 및 초기값 대입

SET SERVEROUTPUT ON;

DECLARE

str VARCHAR2(100) := 'Hello, Oracle World!';

BEGIN

DBMS_OUTPUT.PUT_LINE(str);

END;



--산술연산

SET SERVEROUTPUT ON;

DECLARE

n1 NUMBER := 10;

n2 NUMBER := 20;

BEGIN

DBMS_OUTPUT.PUT_LINE('n1 = ' || n1);

DBMS_OUTPUT.PUT_LINE('n2 = ' || n2);

DBMS_OUTPUT.PUT_LINE('n1 + n2 = ' || (n1 + n2));

DBMS_OUTPUT.PUT_LINE('n1 * n2 = ' || (n1 * n2));

END;



--IF문

형식

IF(조건식) THEN 실행문;

elsif 조건식 then 실행문;3


else 실행문

END IF;



형식2

IF(조건식) THEN

실행문;

ELSE

실행문;

END IF;



형식3

IF(조건식) THEN

실행문;

ELSIF(조건식2) THEN

실행문;

[ELSIF(조건식n THEN)

실행문;]

ELSE

실행문;

END IF;




SET SERVEROUTPUT ON;

DECLARE

grade NUMBER := 100;

BEGIN

if(grade >= 90 and grade <= 100)  then

DBMS_OUTPUT.PUT_LINE('A등급');

else

DBMS_OUTPUT.PUT_LINE('B등급');

end if;

END;




SET SERVEROUTPUT ON;

DECLARE

grade NUMBER := 100;

BEGIN

if(grade >= 90 and grade <= 100)  then

DBMS_OUTPUT.PUT_LINE('A등급');

elsif (grade >= 80 and grade <= 90) then

DBMS_OUTPUT.PUT_LINE('B등급');

elsif (grade >= 70 and grade <= 80) then

DBMS_OUTPUT.PUT_LINE('C등급');

elsif (grade >= 60 and grade <= 70) then

DBMS_OUTPUT.PUT_LINE('D등급');

else

DBMS_OUTPUT.PUT_LINE('F등급');

end if;

END;




-- CASE ~ END CASE 구문

CASE 변수

WHEN 값1 THEN 실행문;

WHEN 값2 THEN 실행문;

...

ELSE 실행문;

END CASE;



문제) 점수를 분석해서 A등급에서 F등급까지 출력하는 PL/SQL 작성. CASE ~ END CASE 구문 이용.


SET SERVEROUTPUT ON;

DECLARE

grade NUMBER := 100;

BEGIN

CASE trunc(grade/10)

WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('A등급');

WHEN 9 THEN DBMS_OUTPUT.PUT_LINE('A등급');

WHEN 8 THEN DBMS_OUTPUT.PUT_LINE('B등급');

WHEN 7 THEN DBMS_OUTPUT.PUT_LINE('C등급');

WHEN 6 THEN DBMS_OUTPUT.PUT_LINE('D등급');

ELSE DBMS_OUTPUT.PUT_LINE('F등급');

END CASE;

END;




-- WHILE ~ END LOOP 구문

형식1

WHILE 조건식 LOOP -- 조건이 참이면 반복실행

실행문;

END LOOP;



형식2 (무한실행)

LOOP

EXIT WHEN 조건식; -- 조건이 참이면 블럭탈출

실행문;

END LOOP;


-- 1부터 10까지 출력.


SET SERVEROUTPUT ON;

DECLARE

n NUMBER; 

BEGIN

n := 0;

while n < 10 loop

n := n + 1;

DBMS_OUTPUT.PUT_LINE(n);

end loop;

END;




SET SERVEROUTPUT ON;

DECLARE

n NUMBER;

BEGIN

n := 0;

loop

exit when n = 11;

n := n + 1;

DBMS_OUTOUT.PUT_LINE(n);

end loop;

END;




-- FOR ~ END LOOP 문

FOR 변수 IN 시작수..끝수 LOOP

실행문;

END LOOP;



-- 1부터 10까지 출력.


SET SERVEROUTPUT ON;

DECLARE

n NUMBER;

BEGIN

FOR n IN 1..10 LOOP -- 이 FOR문에서 n 즉 변수는 별도의 선언이 없어도 자동선언되므로, DECLARE 에서 별도로 선언하지 않아도 됨.

DBMS_OUTPUT.PUT_LINE(n);

END LOOP;

END;




문제) 구구단 출력을 WHILE, LOOP, FOR 반복문으로 출력. 5단으로 한정.


while 문사용 ) --


SET SERVEROUTPUT ON;

DECLARE

n NUMBER := 5;

m NUMBER := 1;

BEGIN

WHILE(m <= 9) LOOP

DBMS_OUTPUT.PUT_LINE(n || '*' || m ||' = ' || (n*m));

m := m + 1;

END LOOP;

END;




LOOP 문 사용 )--


SET SERVEROUTPUT ON;

DECLARE

n NUMBER := 5;

m NUMBER := 1;

BEGIN

LOOP

EXIT WHEN m = 9;

DBMS_OUTPUT.PUT_LINE(n || '*' || m ||' = ' || (n*m));

m := m+1;

END LOOP;

END;




FOR 문 사용 )--


SET SERVEROUTPUT ON;

DECLARE

n NUMBER := 5;

BEGIN

for m in 1..9 loop

DBMS_OUTPUT.PUT_LINE(n || '*' || m ||' = ' || (n*m));

end loop;

END;








PL/SQL 외부 입력 처리


1. ACCEPT 변수 PROMPT '메시지'


2. 외부 변수에서 받은 데이터를 내부 변수에 전달할 때는 &외부변수명 형태로 연결.


ACCEPT n1 PROMPT '숫자1?';

ACCEPT n2 PROMPT '숫자2?';

SET SERVEROUTPUT ON;

DECLARE

num1 NUMBER := &n1;

num2 NUMBER := &n2;

BEGIN

DBMS_OUTPUT.PUT_LINE('n1 = ' || n1);

DBMS_OUTPUT.PUT_LINE('n2 = ' || n2);

DBMS_OUTPUT.PUT_LINE('n1 + n2 = ' || (n1 + n2));

END;



문제) 구구단 출력을 사용자가 원하는 단수로 출력. ACCEPT, FOR문 사용.

실행 예)

구구단 단수? 5

5 * 1 = 5

....

5 * 9 = 45



ACCEPT n1 PROMPT '구구단 단수?';

SET SERVEROUTPUT ON;

DECLARE

n NUMBER := &n1;

BEGIN

for m in 1..9 loop

DBMS_OUTPUT.PUT_LINE(n || '*' || m ||' = ' || (n*m));

end loop;

END;






FUNCTION (함수)


1. 내장함수, 사용자 정의 함수. 외부 데이터 입력시 단일 결과 리턴하는 PL/SQL 구문의 집합체.


2. 형식

CREATE OR REPLACE FUNCTION 함수이름

(

매개변수 리스트 --자료형 지정시 크기 지정 불가

)

RETURN 리턴자료형 --자료형 지정시 크기 지정 불가

IS

내부변수 리스트;

BEGIN

실행문;

RETURN 값;

END;


--주민번호 입력시 성별 출력하는 함수 작성.

--> fnGender('주민번호')


--사용자 정의 함수 작성

CREATE OR REPLACE FUNCTION fnGender

(

vssn VARCHAR2 --'123456-1234567', 1 또는 2만으로 구분한다고 가정

)

RETURN NVARCHAR2 --'남자', '여자'

IS

gender NVARCHAR2(2);

BEGIN

IF (SUBSTR(vssn, 8, 1) = '1') THEN

gender := '남자';

ELSE

gender := '여자';

END IF;

RETURN gender;

END;


--사용자 정의 함수 실행

SELECT fnGender('123456-1234567') AS fnGender FROM dual; --'남자'

SELECT fnGender('123456-2345678') AS fnGender FROM dual; --'여자'


SELECT num, name, ssn, fnGender(ssn) AS fnGender

  FROM insa;

  

SELECT fnGender(ssn), COUNT(*)

  FROM insa

  GROUP BY fnGender(ssn);



--주민번호(VARCHAR2) 입력시 생년월일(DATE, YYYY-MM-DD) 리턴하는 함수 작성

--> fnBirthDay('주민번호')

CREATE OR REPLACE FUNCTION fnBirthDay

(

vssn VARCHAR2

)

RETURN date

IS

birthday DATE;

BEGIN

birthday := TO_DATE(SUBSTR(vssn, 1, 6)); --YY/MM/DD

RETURN birthday;

END;



SELECT fnBirthDay('800101-1234567') AS fnBirthDay FROM dual;


SELECT TO_CHAR(fnBirthDay('800101-1234567'), 'YYYY-MM-DD') AS fnBirthDay FROM dual;


SELECT num, name, ssn, fnBirthDay(ssn) AS fnBirthDay FROM insa;



문제) 날짜 데이터를 입력 받아서 년도, 월, 일을 따로 출력하는 함수 작성.

반환 자료형은 NUMBER.

fnYear(날짜 데이터), fnMonth(날짜데이터), fnDay(날짜데이터)


--사용 예

SELECT num, name, ibsadate 

FROM insa

WHERE fnMonth(ibsadate) = 5;



--날짜 데이터에서 '년' 얻는 함수

CREATE OR REPLACE FUNCTION fnYear

(

vdate DATE

)

RETURN NUMBER

IS

BEGIN

RETURN TO_NUMBER(TO_CHAR(vdate, 'YYYY'));

END;


SELECT fnYear(SYSDATE) FROM dual;



--날짜 데이터에서 '월' 얻는 함수

CREATE OR REPLACE FUNCTION fnMonth

(

vdate DATE

)

RETURN NUMBER

IS

BEGIN

RETURN TO_NUMBER(TO_CHAR(vdate, 'MM'));

END;


SELECT fnMonth(SYSDATE) FROM dual;



--날짜 데이터에서 '일' 얻는 함수

CREATE OR REPLACE FUNCTION fnDay

(

vdate DATE

)

RETURN NUMBER

IS

BEGIN

RETURN TO_NUMBER(TO_CHAR(vdate, 'DD'));

END;


SELECT fnDay(SYSDATE) FROM dual;



WRITTEN BY
빨강꼬마

,

시퀀스 (Sequence)



1. 자동 번호 부여하는 기능을 가진 객체. nextVal 명령에 의해서 자동으로 일련번호를 리턴받는다. 번호는 기본적인 상태에서는 1부터 1씩 증가한다.

- 한번 사용할때마다 반환되는 숫자는 유니크하므로 생성한 시퀀스를 다른 목적으로 사용한 경우 그때 리턴된 번호 다음 번호부터 사용할 수 있다.



2. 형식

CREATE SEQUENCE 시퀀스명; -- 1부터 1씩 증가하는 일련번호


CREATE SEQUENCE 시퀀스명

START WITH 시작번호; -- 시작번호부터 1씩 증가하는 일련번호


CREATE SEQUENCE 시퀀스명

INCREMENT BY 증가분 

START WITH 시작번호; -- 시작번호부터 증가분만큼씩 증가하는 일련번호


DROP SEQUENCE 시퀀스명; -- 시퀀스 삭제


select 시퀀스명.currVal from dual; -- 현재 사용할 수 있는 번호 확인 명령. (nextVal 명령대신 currVal 을 사용한다)


Sequence 예제)

create sequence test15Seq;


CREATE TABLE test15 (

col1 NUMBER

,col2 varchar2(10)

);


alter table test15

add constraint test15_col1_pk primary key (col1);



insert into test15 (col1, col2) values (test15Seq.nextVal, 'kim'); -- 1, kim

insert into test15 (col1, col2) values (test15Seq.nextVal, 'park'); -- 2, park

insert into test15 (col1, col2) values (test15Seq.nextVal, 'choi'); -- 3, choi



문제) city, buseo, jikwi 테이블에서 기본 테이블을 입력한다. 시퀀스 사용.









자료 수정 (UPDATE), 삭제(DELETE)



1. 형식

UPDATE 테이블명 SET 컬럼 = 신규자료, 컬럼 = 신규자료,....

WHERE 조건식;


UPDATE 테이블명 SET 컬럼 = (서브쿼리의 결과값), 컬럼 = 신규자료,....

WHERE 조건식(조건식대신 서브쿼리 이용가능);


DELETE 테이블명

WHERE 조건식(조건식대신 서브쿼리 이용가능);


※ 조건식이 없으면 모든 행이 대상으로 되어 모든 행이 변경되므로 반드시 주의할것~



2. INSERT, UPDATE, DELETE  실행 후 영구적인 적용은 COMMIT;

취소하려면 ROLLBACK;



3. 참조당하는 테이터(PK 지정된 컬럼이면서 FK에 의해 참조 당하는 컬럼)인 경우 수정, 삭제가 안될 수 있다.



자료수정 및 삭제 예제)

******************


create table test17 (

col1 number

,col2 varchar2(10)

);


insert into test17 (col1, col2) values (1, 'kim');

insert into test17 (col1, col2) values (2, 'choi');


select * from test17;


commit;


update test17 set col2 ='kang' where col1 = 2;


select * from test17;


commit;


delete test17 where col1 = 2;


select * from test17;


commit;





서브쿼리를 이용한 DML 예제)


-- EMPLOYEES 테이블 직원들의 SALARY 를 10% 인상합니다. 단, 부서명(department_name)이 'IT'인 경우만 실행.


update employees set salary = (salary * 1.1)

where department_id = (select department_id

from departments

where department_name = 'IT'

      );




문제) employees 테이블에서 jobs.job_title이 'Sales Manager' 인 직원들의 salary를 해당 직무의 최고 급여(jobs.max_salary)로 수정.

단, 입사일(employees.hire_date)가 2005년 이전(해당 년도 포함) 입사자만 적용.


update employees set salary = ( select max_salary from jobs where jobs_title = 'Sales Manager')

where to_char(hire_date, 'yyyy') <= '2005';



데이터베이스 모델링 작업


1. ERD


2. CREATE TABLE


3. 제약조건


4. 기초코드입력


5. 뷰 생성

-- 조인 기반 쿼리문 작성시 뷰로 생성하면 편리하게 작업이 가능.


6. SQL 파일 작성


WRITTEN BY
빨강꼬마

,

DQL(Data Query Lenguage) - 데이터 질의어, SELECT

-> 

DML (Data Manipulation Language) - 데이터 조작어, INSERT, UPDATE, DELETE


DDL 정의

DCL 컨트롤





INSERT


1. INSERT 문은 테이블에 새 행(row)을 추가하는데 이용하며, single table insert 나 multi table insert를 수행할 수 있다.


single table insert : 오직 하나의 테이블이나 뷰에 오직 하나의 행(row)의 값들을 삽입할 수 있다.

multi table insert : 하나 이상의 테이블로부터 스브쿼리로 얻은 여러 행(row)을 삽입하는 경우이다.


2.

insert into 테이블명 (컬럼명1, 컬럼명2, ...) values (값1, 값2, ...);

-> 컬럼명과 값은 1:1 매칭이 되어야 한다. 자료형, 순서, 갯수.



insert into 테이블 values (값1, 값2, ...);

-> 테이블에 있는 모든 컬럼을 대상으로 값을 입력하는 경우 컬럼명 부분을 생략할 수 있다.








자료형


1. VARCHAR2, NVARCHAR2 : 문자열형. 길이 지정. 최대 4000자.

2. NUMBER : 숫자형(정수, 실수). 길이 지정. 최대 38자리

3. DATE : 날짜형. 날짜, 시간이 저장됨.






NUMBER 자료형 자료 입력 테스트


CREATE TABLE test1 (

col1 number -- 38자리까지 입력가능

,col2 number(3) -- 3자리까지만 입력가능

,col3 number(5, 2) -- 5자리인데, 소수이하 2자리 입력가능. (소수이하 자리 포함해서 5자리)

);


insert into test1 (col1, col2, col3) values (123, 123, 123); -- 123, 123, 123

insert into test1 (col1, col2, col3) values (123.45, 123.45, 123.45); -- 123.45, 123, 123.45

insert into test1 (col1, col2, col3) values (123.456, 123.456, 123.456); -- 123.456, 123, 123.46

insert into test1 (col1, col2, col3) values (1234, 1234, 1234); -- 입력시도하는 정수가 크므로 아예 입력 불가





DATE 자료형 자료 입력 테스트


CREATE TABLE test2 (

col1 date --날짜, 시간 저장

);


insert into test1 (col1) values (SYSDATE); -- 2012-10-25 10:05:30

insert into test1 (col1) values ('20121025'); -- 2012-10-25 00:00:00

insert into test1 (col1) values ('2012-10-25'); -- 2012-10-25 00:00:00

insert into test1 (col1) values ('2012/10/25'); -- 2012-10-25 00:00:00





VARCHAR2, NVARCHAR2 자료형 자료 입력 테스트


CREATE TABLE test3 (

col1 VARCHAR2(1) -- 1글자만 허용.

,col2 VARCHAR2(10) -- 10글자까지 허용. 영어는 1글자, 한글은 한글자당 3글자 차지

,col3 NVARCHAR2(10) -- 10글자까지 허용. 영어, 한글 상관없이 한글자당 1글자 차지

);


insert into test3(col1, col2, col3) values ('A', 'B', 'C'); --A, B, C

SELECT * FROM test3;

insert into test3(col1, col2, col3) values ('TEST', 'TEST', 'TEST'); -- 허용 글자수 오버로 입력 불가.


insert into test3(col2, col3) values ('ORACLEDATA', 'ORACLEDATA'); -- 널, ORACLEDATA, ORACLEDATA

insert into test3(col2, col3) values ('ORACLEDATABASE', 'ORACLEDATABASE'); -- 허용 글자수 오버로 입력 불가.


insert into test3(col2, col3) values ('오라클', '오라클'); -- 널, 오라클, 오라클,

insert into test3(col2, col3) values ('오라클데이터베이스', '오라클데이터베이스'); -- 허용 글자수 오버로 입력 불가.


insert into test3(col3) values ('오라클데이터베이스'); -- 널, 널, 오라클데이터베이스

insert into test3(col2) values ('오라클데'); -- 한글 하나당 3자리 차지하므로 입력 불가.






테이블 객체 관리


1. 새 테이블 생성

create table 테이블명 (

컬럼명 자료형 [제약조건]

,...

);



2. 기존 테이블 구조 확인

desc 테이블명;



3. 기존 테이블 구조 복사해서 새 테이블 생성

create table 새테이블명

as

select 컬럼리스트 from 기존테이블명 where 1=0;



4. 기존 테이블 구조 및 자료 복사해서 새 테이블 생성

create table 새테이블명

as

select 컬럼리스트 from 기존테이블명 [where 조건식]; -- 조건식이있을 경우 조건식에 만족하는 행만 복사



-- insa 테이블 복사해서 test4 테이블 생성. 구조와 자료 전체 복사.

create table test4 as 

select * from insa;



5. 기존 테이블에 새 열(column) 추가

alter table 기존테이블명

add (열이름 자료형, ....);



6. 기존 테이블에 기존 열(column) 자료형 변경

alter table 기존테이블명

modify 열이름 새로운자료형;



7. 기존 테이블에 기존 열(column) 이름 변경

alter table 기존테이블명

rename column 기존열이름 to 새열이름;



8. 기존 테이블에 기존 열(column) 삭제

alter table 기존테이블명

drop column (열이름, ....);


9. 기존 테이블의 이름 변경

rename 기존테이블명 to 새로운테이블명;


10. 기존 테이블 삭제 (휴지통 기능)

drop table 테이블명; -- 휴지통으로 이동한 상태


-- 휴지통에 있는 객체 확인.

select *

from recyclebin;


-- 휴지통에 있는 객체 복원.

flashback table 테이블명 to before drop;


-- 휴지통 비우기

purge recyclebin;


-- 완전 삭제(휴지통을 거치지 않고 바로 삭제)

drop table 테이블명 PURGE;







제약 조건


1. Primary Key(PK), Unique(UK), NOT NULL, Check(CK), Foreign Key(FK)


2. 무결성 유지를 위한 조건 추가. 개체 무결성, 참조 무결성, 도메인 무결성.


3. 제약조건 지정방법

- 컬럼레벨

CREATE TABLE 테이블명 (

컬럼명 자료형 [CONSTRAINT 제약명] 제약조건명  // [] 은 제약조건명이 제약명을 가진 객체로 오라클이 가지고 있게 하여, 추후 수정할때 사용함.

,...

);



- 테이블레벨 (권장)

CREATE TABLE 테이블명 (

컬럼명 자료형 

,...

,CONSTRAINT 제약명 제약조건명 (컬럼지정)

);



4. 제약조건 확인

select * from user_constraints;


-- 특정 테이블에 있는 제약조건 확인 (제약 종류만 확인)

select * from user_constraints 

where table_name = 'TEST7'; -- 테이블명 대문자로 표기



-- 특정 테이블에 있는 제약조건이 지정된 컬럼 정보 확인 (제약조건 걸린 컬럼명만 확인)

select *

from user_cons_columns

where table_name = 'TEST7';



-- 제약종류와 컬럼 정보를 동시에 확인. constraint_name 컬럼을 기준으로 join


select uc.owner, uc.table_name, column_name, constraint_type, uc.constraint_name

from user_constraints uc, user_cons_columns ucc

where uc.constraint_name = ucc.constraint_name AND uc.table_name = '테이블명';


****

-> 자주 사용하는 select 쿼리는 view 객체로 등록


create view constraint_check

as

select uc.owner, uc.table_name as table_name, column_name, constraint_type, uc.constraint_name

from user_constraints uc, user_cons_columns ucc

where uc.constraint_name = ucc.constraint_name;


-> view 객체를 이용한 제약조건 확인


select * from constraint_check

where table_name ='테이블명';



-- 특정 테이블의 NOT NULL 제약 여부 확인


DESC 테이블명;




-- DEFAULT 제약 확인 

SELECT *

FROM user_tab_columns

where table_name = '테이블명';



-- 제약 확인용 VIEW 객체 수정 (CHECK 제약 확인 추가)


create or replace view constraint_check

as

select uc.owner, uc.table_name as table_name, column_name, constraint_type, uc.constraint_name, uc.search_condition

from user_constraints uc, user_cons_columns ucc

where uc.constraint_name = ucc.constraint_name;






◆ Primary Key(PK)

 * 특성

- PK가 지정된 컬럼에는 중복된 데이터 입력 불가.

- 테이블에는 PK를 한번만 지정가능.

- PK가 지정된 컬럼에는 NULL 값을 허용하지 않는다.

- PK가 지정된 컬럼에는 UNIQUE INDEX가 자동 생성된다.

- PK가 지정된 컬럼이 두 개 이상인 경우는 묶어서 하나로 처리해야 한다.

-- 컬럼지정시 컬럼을 다중으로 기재하면 복합키 처리가 됨.


 * 형식

CREATE TABLE 테이블명 (

컬럼명 자료형 

,...

,CONSTRAINT 제약명 PRIMARY KEY (컬럼지정)

);



--PK 지정전

CREATE TABLE test6 (

col1 NUMBER

,col2 VARCHAR2(10)

);


INSERT INTO test6(col1, col2) values (1, 'hong'); -- 1, hong

INSERT INTO test6(col1, col2) values (2, 'kim'); -- 2, kim

INSERT INTO test6(col1, col2) values (2, 'park'); -- 2, park

INSERT INTO test6(col2) values ('choi'); -- NULL, choi

INSERT INTO test6(col1, col2) values (NULL, NULL); -- NULL, NULL



--PK 지정후

CREATE TABLE test7 (

col1 NUMBER

,col2 VARCHAR2(10)

,CONSTRAINT test7_col1_pk PRIMARY KEY (col1)

);


INSERT INTO test7(col1, col2) values (1, 'hong'); -- O

INSERT INTO test7(col1, col2) values (2, 'kim'); -- O

INSERT INTO test7(col1, col2) values (2, 'park'); -- X

INSERT INTO test7(col2) values ('choi'); -- X

INSERT INTO test7(col1, col2) values (NULL, NULL); -- X






◆ Unique(UK)

 * 특성

- UK가 지정된 컬럼에는 중복된 데이터 입력 불가.

- 테이블에 UK를 여러번 지정 가능.

- UK가 지정된 컬럼은 NULL 값을 허용한다.


 * 형식

CREATE TABLE 테이블명 (

컬럼명 자료형 

,...

,CONSTRAINT 제약명 UNIQUE (컬럼지정)

);


-- UK 지정전

CREATE TABLE test8 (

col1 NUMBER -- PK 지정대상

,col2 VARCHAR2(10)

,col3 VARCHAR2(20) -- UK 지정대상

);


INSERT INTO test8 (col1, col2, col3) VALUES (1, 'kim', 'test@nate.com'); - 입력 가능

INSERT INTO test8 (col1, col2, col3) VALUES (2, 'park', 'test@nate.com'); - 입력 가능



--UK 지정 후

CREATE TABLE test9 (

col1 NUMBER

,col2 VARCHAR2(10)

,col3 VARCHAR2(20)

,CONSTRAINT test9_col1_pk PRIMARY KEY (col1)

,CONSTRAINT test9_col2_uk UNIQUE (col2)

);


INSERT INTO test8 (col1, col2, col3) VALUES (1, 'kim', 'test@nate.com'); - 입력 가능

INSERT INTO test8 (col1, col2, col3) VALUES (2, 'park', 'test@nate.com'); - 입력 불가






◆ NOT NULL 키워드


1. 특정 컬럼이 NULL 값이 가지지 못하도록 막는다. PK가 지정된 컬럼은 자동으로 NOT NULL 특성을 가진다.


2. 형식

CREATE TABLE 테이블명 (

컬럼명 자료형 NOT NULL

,...

);



-- NOT NULL 지정


CREATE TABLE test10 (

col1 VARCHAR2(10) NOT NULL --NULL 허용안함

,col2 VARCHAR2(10) -- NULL 허용

);


INSERT INTO test10 (col1, col2) VALUES ('test', 'test'); --O

INSERT INTO test10 (col1, col2) VALUES (NULL, NULL); --X









◆ DEFAULT 키워드


1. 입력시 미리 약속된 값을 자동 삽입 하는 경우. 단, 명시적으로 값을 입력하는 경우는 무시됨.


2. 형식

CREATE TABLE 테이블명 (

컬럼명 자료형 DEFAULT 값

,...

);




CREATE TABLE test11 (

col1 NUMBER

,col2 VARCHAR2(10)

,col3 DATE DEFAULT SYSDATE

);


INSERT INTO test11 (col1, col2, col3) values (1, 'hong', '20120101'); -- DEFAULT 값 무시됨

INSERT INTO test11 (col1, col2) values (2, 'kim');

INSERT INTO test11 (col1, col2) values (3, 'park', DEFAULT); -- DEFAULT값 자동 삽입.




-- DEFAULT 제약 확인 

SELECT *

FROM user_tab_columns

where table_name = '테이블명';







 CHECK 


1. 특정 컬럼에 입력되는 데이터에 대한 허용 범위를 지정할 때 사용.


2. 형식

CREATE TABLE 테이블명 (

컬럼명 자료형

,...

,CONSTRAINT 제약명 CHECK (조건식)

);


CREATE TABLE test12 (

col1 VARCHAR2(10)

,col2 NUMBER(3) --점수 입력 컬럼(0~100)

,CONSTRAINT test12_col2_ck CHECK (col2 between 0 and 100)

);



INSERT INTO test12 (col1, col2) values ('hong', 100); --O

INSERT INTO test12 (col1, col2) values ('park', 200); --X



-- 제약 확인용 VIEW 객체 수정 (CHECK 제약 확인 추가)


create or replace view constraint_check

as

select uc.owner, uc.table_name as table_name as table_name, column_name, constraint_type, uc.constraint_name, uc.search_condition

from user_constraints uc, user_cons_columns ucc

where uc.constraint_name = ucc.constraint_name;



-- NOT NULL 지정을 CHECK 제약으로 하는 방법


CREATE TABLE test13 (

col1 VARCHAR2(10) --NULL 허용안함

,col2 VARCHAR2(10) -- NULL 허용

,CONSTRAINT test13_col1_NN CHECK (col1 IS NOT NULL)

);


INSERT INTO test10 (col1, col2) VALUES ('test', 'test'); --O

INSERT INTO test10 (col1, col2) VALUES (NULL, NULL); --X







 FOREIGN KEY


1. 관계 설정시 사용하는 제약.

- FK가 지정된 컬럼에는 자료 입력이 참조를 하고 있는 PK 또는 UK 컬럼의 값들로 제한된다.

- FK가 지정된 컬럼에 자료입력하려면 먼저 PK 또는 UK컬럼에 해당 자료가 존재해야 한다.

- FK가 지정되고 나면 참조 당하는 PK 또는 UK 컬럼의 테이블 또는 자료는 삭제가 안된다.



2. 형식

CREATE TABLE 테이블명 (

컬럼명 자료명

,...

,CONSTRAINT 제약명 FOREIGN KEY (컬럼지정)

REFERENCES 참조테이블명(참조컬럼지정)

);


-- PK 제약 지정된 테이블

CREATE TABLE test14 (

col1 NUMBER -- PK

,col2 VARCHAR2(10)

,CONSTRAINT test14_col1_PK PRIMARY KEY (col1)

);


-- FK 제약 지정된 테이블

CREATE TABLE test15 (

col3 NUMBER -- PK

,col4 VARCHAR2(10)

,col1 NUMBER -- FK

,CONSTRAINT test15_col3_PK PRIMARY KEY (col3)

,CONSTRAINT test15_col1_FK FOREIGN KEY (col1) REFERENCES test14 (col1)

);



-- PK 자료 입력

INSERT INTO test14 (col1, col2) values (1, 'kim');

INSERT INTO test14 (col1, col2) values (2, 'park');

INSERT INTO test14 (col1, col2) values (3, 'hong');


-- FK 자료 입력

insert into test15 (col3, con4, col1) values (1, 'kim', 1);

insert into test15 (col3, con4, col1) values (2, 'park', 3);

insert into test15 (col3, con4, col1) values (3, 'hong', 4);



-- PK 자료 삭제 테스트

delete from test14

where col1 = 1;  --X



delete fron test14

where col1 = 3; --O








제약 조건 관리


1. 테이블 생성 후 제약 조건 추가

ALTER TABLE 테이블명

ADD (CONSTRAINT 제약명 제약조건 (컬럼지정)

, ...);


2. 제약 조건 삭제

ALTER TABLE 테이블명

DROP CONSTRAINT 제약명;


3. 제약 조건 확인

SELECT uc.owner                         --소유자

, uc.constraint_name            --제약명

, uc.table_name AS table_name   --테이블명

, uc.constraint_type            --제약조건 종류(P, R, U, C)

, uc.search_condition           --CHECK 제약 조건식

, ucc.column_name               --지정된 컬럼

, (SELECT table_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_table_name --참조하는 테이블

, (SELECT column_name 

FROM user_constraints 

WHERE constraint_name=uc.r_constraint_name) 

AS r_conlumn_name --참조하는 컬럼

FROM user_constraints uc, user_cons_columns ucc

WHERE uc.constraint_name = ucc.constraint_name


4. NOT NULL, DEFAULT 지정은 별도 확인

--NOT NULL 확인

DESC 테이블명;


--DEFAULT 확인

SELECT *

FROM user_tab_columns

WHERE table_name='테이블명';







문제) emp, city, buseo, jikwi 테이블에서 제약 조건 추가할 것.

아래 형태의 테이블 생성이 된 후에 제약 조건 추가 명령을 내린다고 가정함.


--city 테이블 생성

CREATE TABLE city (

city_id NUMBER --PK

, city VARCHAR2(10) --CK. 서울,충남,인천,부산,제주,전북,강원,경북,전남,경기 한정

);


--buseo 테이블 생성

CREATE TABLE buseo (

buseo_id NUMBER --PK

, buseo VARCHAR2(15) --CK. 총무부,개발부,영업부,기획부,인사부,자재부,홍보부 한정

);


--jikwi 테이블 생성

CREATE TABLE jikwi (

jikwi_id NUMBER --PK

, jikwi VARCHAR2(15) --CK. 과장,대리,부장,사원,사장,이사 한정

);


--emp 테이블 생성

CREATE TABLE emp (

num NUMBER --PK

, name VARCHAR2(20) -- NOT NULL

, ssn  VARCHAR2(14) -- NOT NULL

, ibsaDate DATE

, city_id NUMBER --FK

, tel   VARCHAR2(15)

, buseo_id NUMBER --FK

, jikwi_id NUMBER --FK

, basicPay NUMBER(10) --CK. 0 이상

, sudang NUMBER(10) --CK. 0 이상

);



WRITTEN BY
빨강꼬마

,

관계(Relationship)



1. 오라클 데이터베이스는 종류로 나눌때 관계형 데이터베이스로 분류한다.


2. 관계는 테이블(데이터저장소)간의 논리적으로 연결될 수 있는 부분에 대한 연결하는 고리.

예를들어, 학생과 강사 간에는 수업이라는 연결고리가 존재한다.


학생 - (수업) - 강사


수업이라는 행위가 관계가 된다.


3. 데이터베이스에서 관계는 데이터간의 공통점을 찾아서 연결시키는 것.

-> 데이터 중복 최소화의 이점이 있다.

-> 관계형 데이터베이스 구축


--원시데이터(회원테이블)

이름, 전화번호, 주소, 직장명

홍길동, 010-111-1111, 서울, KH정보교육원

김길동, 010-222-2222, 서울, KH정보교육원

박길동, 010-333-3333, 경기, KH정보교육원

--> 중복 최소화하기 위해서 테이블 분리 --> 관계설정



회원테이블 -----------

회원아이디(PK), 이름,   전화번호,     주소아이디(FK), 직장명아이디(FK)

1,              홍길동, 010-111-1111, 1,              1

2,              김길동, 010-222-2222, 1,              1

3,              박길동, 010-333-3333, 2,              1


주소테이블 -----------

주소아이디(PK), 주소

1,              서울

2,              경기


직장명테이블 ---------

직장명아이디(PK), 직장명

1,                KH정보교육원



4. HR 스키마의 경우


윈시데이터

first_name, last_name, department_name(중복데이터), job_title(중복데이터)

--> 관계형 데이터베이스 구축 필요.


-- 관계형 데이터베이스 구축 결과.

employee_id, first_name, last_name, department_id(FK), job_id(FK) -> employees 테이블


department_id(PK), department_name -> departments 테이블


job_id(PK), job_title -> jobs 테이블




5. insa 테이블의 경우


--원시데이터

num, name, ssn, ibsadate, city(중복), tel, buseo(중복), jikwi(중복), basicpay, sudang

--> 관계형 데이터베이스 구축 필요



-- 관계형 데이터베이스 구축 결과.

--member테이블

num(PK), name, ssn, ibsadate, city_id(FK), tel, buseo_id(FK), jikwi_id(FK), basicpay, sudang


--city테이블

city_id(PK), city


--buseo 테이블

buseo_id(PK), buseo


--jikwi 테이블

jikwi_id(PK), jikwi








JOIN


1. 두개 이상의 테이블에서 관련된 정보를 연결해서 출력하는 방법.


2. EQUI JOIN: 두개 이상의 테이블에서 관련된 정보(PK, FK)가 일치하는 경우에만 연결하는 경우.

   EQUI JOIN 형식


--오라클 형식

SELECT 컬럼리스트

FROM 테이블1 별칭1, 테이블2 별칭2

WHERE 별칭1.공통컬럼 = 별칭2.공통컬럼; <- EQUI JOIN 시 필수 조건절이며, = 을 사용한 경우 EQUI JOIN 이라고 칭할 수 있음.


--ANSI 형식(여러 SQL 에서 구동할 수 있도록 정한 표준 형식)

SELECT 컬럼리스트

FROM 테이블1 별칭1 JOIN 테이블2 별칭2

ON 별칭1.공통컬럼 = 별칭2.공통컬럼;    <- 위 구문과 동일한 표현



-- departments 테이블과 관련된 테이블 찾기

1. departments 테이블에서 PK 컬럼을 찾는다. -> departments.department_id

2. departments 테이블과 연결된 테이블을 찾는다. -> ERD에서 관계선을 찾는다. employees 테이블.

3. employees 테이블에서 FK 컬럼을 찾는다. -> employees.department_id

4. departments.department_id 컬럼과 employees.department_id 컬럼이 공통 컬럼.

5. JOIN QUERY 작성

SELECT first_name, last_name, department_name

FROM departments dep, employees emp

WHERE dep.department_id = emp.department_id; <- EQUI JOIN 시 필수 조건절이며, = 을 사용한 경우 EQUI JOIN 이라고 칭할 수 있음.



--  employees, departments 테이블에서 first_name이 'Nancy', last_name이 'Greenberg'인

직원의 부서명(department_name)을 출력.


select first_name, last_name, department_name

from departments d, employees e

where d.department_id = e.department_id and (first_name='Nancy' and last_name='Greenberg');




-- employees, jobs 테이블에서 first_name이 'Nancy', last_name이 'Greenberg'인 직원의 직무명(job_title)을 출력.


select first_name, last_name, job_title

from jobs j join employees e

on j.job_id = e.job_id and first_name='Nancy';



문제) employees, jobs 테이블에서 job_title이 'Finance Manager'인 직원 정보 출력.


select *

from jobs j, employees emp

where j.job_id = emp.job_id and job_title = 'Finance Manager';



문제) departments, employees 테이블에서 부서명(department_name), 부서장의 이름(first_name, last_name) 출력


select first_name, last_name, department_name

from departments dep, employees emp

where dep.manager_id = emp.manager_id;



문제) employees, jobs 테이블. 직위명(job_title)별 최소급여(min_salary)를 받는 직원 정보 출력.


select first_name, last_name, job_title, min_salary, salary

from jobs j , employees emp

where j.job_id = emp.job_id and (j.min_salary = emp.salary);



문제) jobs, employees 테이블. 직위명(job_title)별 직원의 수 출력


select job_title, count(*)

from jobs j, employees emp

where j.job_id = emp.job_id

group by job_title

order by count(*) desc;



문제) jobs, employees 테이블. 직위명(job_title)별 직원의 수 출력. 단 10명 이상인 경우만 출력


select job_title, count(*)

from jobs j, employees emp

where j.job_id = emp.job_id

group by job_title

having count(*) >= 10

order by count(*) desc;







세 개 이상의 테이블 JOIN


1. 형식


--오라클 형식

SELECT 컬럼리스트

FROM 테이블1 별칭1, 테이블2 별칭2, 테이블3, 별칭3

WHERE 별칭1.공통컬럼 = 별칭2.공통컬럼 AND 별칭2.공통컬럼 = 별칭3.공통컬럼;



-- jobs, employees, departments 테이블 join


select first_name, last_name, job_title, department_name

from jobs j, employees e, departments d

where j.job_id = e.job_id and e.department_id = d.department_id;



문제) employees, jobs, department 테이블에서 first_name이 'Nancy', last_name이 'Greenberg'인 직원의 직무명(job_title), 

부서명(department_name)을 출력.


select first_name, last_name, job_title, department_name

from jobs j, employees e, departments d

where j.job_id = e.job_id and e.department_id = d.department_id and (first_name = 'Nancy' and last_name = 'Greenberg')







SELF JOIN



1. 자기 자신(테이블)을 join의 대상으로 지정하는 것.


2. 형식

SELECT 컬럼리스트

FROM 테이블1 별칭1, 테이블1 별칭2  --> 테이블은 같고 별칭은 다르게 지정.

WHERE 별칭1.공통컬럼 = 별칭2.공통컬럼;



-- employees 테이블에서 first_name이 'Steven' last_name이 'King' 직원과 같은 부서 아이디(department_id)를 가진 직원 정보 출력


select e2.first_name, e2.last_name

from employees e1, employees e2

where e1.department_id = e2.department_id and e1.first_name = 'Steven' and e1.last_name = 'King';



-- employees 테이블에서 first_name이 'Alexander' last_name이 'Hunold' 직원과 같은 직위아이디(job_id)를 가진 직원 정보 출력


select e2.first_name, e2.last_name

from employees e1, employees e2

where e1.job_id = e2.job_id and e1.first_name = 'Alexander' and e1.last_name = 'Hunold';



문제) employees 테이블에서 first_name이 'Alexander' last_name이 'Hunold' 직원과 같은 급여(salary)를 가진 직원 정보 출력


select e2.first_name, e2.last_name

from employees e1, employees e2

where e1.salary = e2.salary and e1.first_name = 'Alexander' and e1.last_name = 'Hunold';



문제) employees 테이블에서 first_name이 'Steven' last_name이 'King' 직원의 직속 부하 직원 정보 출력








OUTER JOIN


1. 두개의 테이블을 JOIN한 상태에서 데이터 출력시, 

   양쪽에 동시에 존재하는 경우만 출력되는 경우는 EQUI JOIN 이지만,

   한쪽에만 존재하는 데이터를 출력하는 경우는 OUTER JOIN이라고 함.


예를 들어, 특정 과목에 대한 학생의 성적을 입력하는 과정에서 성적이 모두

입력되지 안은 상태라면, 학생 성적 출력시 EQUI JOIN을 사용하면 성적 처리가

된 학생들의 정보와 성적만 출력된다. OUTER JOIN을 사용하면 성적 처리가 된

학생들의 정보와 성적 외에 성적처리가 되지 않은 학생들의 정보가 같이 출력된다.



2. 형식

SELECT 컬럼리스트                                  -> 테이블1 정보 전체 출력 left outer join

FROM 테이블1 별칭1, 테이블2 별칭2

WHERE 별칭1.공통컬럼 = 별칭2.공통컬럼(+); 


SELECT 컬럼리스트                                  -> 테이블2 정보 전체 출력 right outer join

FROM 테이블1 별칭1, 테이블2 별칭2

WHERE 별칭1.공통컬럼(+) = 별칭2.공통컬럼; 



-- employees 테이블 전체 직원의 수


select count(*)

from employees; -- 107명


-- employees, departments 테이블 EQUI JOIN


select count(*)

from employees e, departments d

where e.department_id = d.department_id; -- 106명


select *

from employees

where department_id is null; -- 178, Kimberely


--LEFT OUTER JOIN(employees 테이블의 정보를 전체 출력)


select first_name, last_name, department_name

from employees e, departments d

where e.department_id = d.department_id(+);



--RIGHT OUTER JOIN(departments 테이블의 정보를 전체 출력)

select first_name, last_name, department_name

from employees e, departments d

where e.department_id(+) = d.department_id;



-- jobs, employees 테이블. left outer join

select job_name, first_name, last_name

from jobs j, employees e

where j.job_id = e.job_id(+);



문제) employees 테이블에서 부하 직원이 없는 직원 출력









데이터베이스 객체


1. 데이터베이스에 저장되는 항목들. 테이블, 뷰, 함수, 인덱스, 프로시져, 커서, 트리거, ........


2. 테이블 객체. 데이터 저장소 역할. 컬럼이라는 단위로 데이터를 구분해서 저장한다.

자료형, 제약조건 지정 필요.


데이터베이스 -> 테이블 스페이스 -> 테이블 -> 컬럼(자료형, 제약조건)


kor, eng, mat 컬럼이 있다면,

숫자를 저장해야 하므로, 자료형은 NUMBER 지정.

입력가능한 숫자 범위를 추가로 지정하려면 제약 조건(CHECK 제약)을 추가 지정.


자바에서는


class Record {    // -> 오라클에서는 이부분이 테이블 객체

private int kor, eng, mat; // -> 오라클에서는 이부분이 컬럼 및 자료형

public void serKor(){

if (kor >= 0 && kor <= 100) { // -> 오라클에서는 이부분이 제약조건

this.kor = kor;

}

}

}



오라클 에서는

CREATE TABLE Record ( 

kor NUMBER CONSTRAINT CHECK (kor >= 0 and kor <= 100)

,eng NUMBER CONSTRAINT CHECK (eng >= 0 and eng <= 100)

,mat NUMBER CONSTRAINT CHECK (mat >= 0 and mat <= 100)

);




3. 객제 관련 명령 (생성, 수정, 삭제)


생성 - CREATE 객체의종류 신규객체명 [객체의내용];

수정 - ALTER 객체의종류 기존객체명 [수정할내용];

삭제 - DROP 객체의종류 기존객체명;



4. 객체 확인(Data Dictionary)


DBA_XXXX : 관리자 전용 정보확인용

USER_XXX : 사용자 전용 정보확인용. 자기 자신이 소유한 객체의 정보만 출력.

ALL_XXXX : 사용자 전용 정보확인용. 범용 정보만 출력.



-- 관리자용 계정정보 확인

SELECT * FROM dba_users;


-- 사용자용 계정정보 확인

select * from all_users;


-- 사용자 전용 소유한 테이블 객체 확인

select * from user_tables;


-- 관리자 전용 테이블 객체 확인

select * from dba_tables;



5. 객체를 생성, 수정 삭제하려면 객체 관련 권한이 필요함.


GRANT 권한의종류 TO 계정명;

--> 권한의종류는 권한명 또는 ROLE 명으로 표기


GRANT CONNECT, RESOURCE TO scott;  -- ROLE

GRANT CREATE VIEW TO scott; -- 권한


select *

from dba_sys_privs

where grantee='RESOURCE';



6. 나의 권한 상태 확인.

select *

from user_sys_privs;







테이블 작성


1. 테이블은 관계형 데이터베이스에서 데이터 저장을 위해 이용되는 객체이며, 행(row)과 열(column)으로 구성된다.


예를 들어, 학생 정보 저장용 테이블이 있다면


column column    column

번호   이름     전화번호

  1   홍길동  010-111-1111  -> row

  2   김길동  010-222-2222  -> row



번호, 이름, 전화번호 항목 저장용 학생 정보 테이블을 생성한다면,


CREATE TABLE Students (

stid NUMBER

,name VARCHAR2(10)

,tel VARCHAR2(20)

);


INSERT INTO Students (stid, name, tel) VALUES (1, '홍길동', '010-111-1111');

INSERT INTO Students VALUES (2, '김길동', '010-222-2222');



2. 테이블 작성 순서

- 항목(컬럼) 결정 : 이름, 전화번호, .....

- 자료형: NUMBER, VARCHAR2,....

- 제약조건: PK, FK, CHECK, .....

- 관계: 



WRITTEN BY
빨강꼬마

,


오라클 계정생성



1. 관리자(sys)로 로그인


2. 계정생성

create user scott identified by tiger; (아이디: scott / 패스워드: tiger)


3. 테이블스페이스 지정

alter user scott default tablespace users;


4. 로그인 권한, 테이블 생성 권한 지정

grant connect, resource to scott;

grant create view to scott;


roll - 권한 여러개를 묶어서 처리하는 단위.


5. scott 으로 로그인



-- 계정확인

select * from dba_users;  <-뷰








연습용 예제인 INSA 테이블 생성하는 과정


1. scott 계정으로 로그인 후 작업할 것.

명령프롬프트 > sqlpuls scott

암호 입력


2. insa.sql 파일을 가지고 실행할 것.

sql>@c:\insa.sql


3. insa 테이블 생성 확인

select * from insa;




insa.sql


insa 테이블 관련 문제.txt







order by 구문


1. 정렬기준을 제시한다. 오름차순(Ascending), 내림차순 (Descending)


2. 


select 컬럼리스트

from 테이블명

where 조건식

group by 기준컬럼

having 조건식

order by 기준컬럼 [ASC|DESC], ....;



-- employees 테이블에서 first_name을 오름차순 정렬해서 출력.

select *

from employees

order by first_name asc; --asc는 생략가능



-- employees 테이블에서 salary를 내림차순 정렬해서 출력

select *

from employees

order by salary desc;



-- employees 테이블에서 Department_id를 기준으로 정렬하되,

   같은 부서인 경우 salary를 내림차순 정렬해서 출력.



문제) employees 테이블에서 입사년도 순으로 출력하되, 같은 년도면 급여(salary)가

      많은 순으로 출력.



문제) employees 테이블에서 입사월이 7월인 경우를 출력하되, 입사일이 빠른 순으로 출력.






group by 구문


1. 행들을 가지고 그룹을 만들때 사용. 동일한 데이터를 여러개 가지고 있는 컬럼을 대상으로 그룹형성할것.

그룹을 형성하게 되면 개인식별 정보는 출력되지 않는다. 집계함수(count, sum, avg...)의 결과만을 출력하게 된다.


2.

select 컬럼리스트

from 테이블

where 조건식

group by 기준컬럼

having 조건식

order by 기준컬럼;


-- employees 테이블에서 부서아이디(department_id)별로 그룹형성해서 출력 -> 인원수

select department_id , count(*)

from employees

group by department_id;



-- employees 테이블에서 부서아이디별로 인원수 출력하되, 인원수가 많은 순으로 출력.

select department_id , count(*) as 인원수

from employees

group by department_id

order by 인원수 desc;



-- employees 테이블에서 직무id(job_id)별로 그룹 형성해서 직원수 출력.

select job_id, count(*)

from employees

group by job_id;



-- employees 테이블에서 부서아이디별로 급여 평균을 출력하되, 급여 평균이 많은 순으로 출력.


select department_id, count(*) ,round(avg(salary))

from employees

group by department_id

order by round(avg(salary)) desc;



문제) employees 테이블에서 상급자아이디(manager_id)별 부하 직원수 출력. 부하직원이 많은 순으로 출력.


select manager_id, count(*)

from employees

group by manager_id

order by count(*) desc;




문제) employees 테이블에서 입사년도(hire_date)별 직원수 출력. 입사년도 순으로 출력

select to_char(hire_date, 'yyyy'), count(*)

from employees

group by to_char(hire_date, 'yyyy')

order by to_char(hire_date, 'yyyy');







having 구문


1. group by 구문에서의 결과(집계함수)를 가지고 조건을 지정하는 것.


2.

select 컬럼리스트

from 테이블명

where 조건식

group by 기준컬럼

having 조건식

order by 기준컬럼;


-- employees 테이블에서 직무id(job_id)별로 그룹 형성해서 직원수가 10명 이상인 경우만 출력.

select job_id, count(*)

from employees

group by job_id

having count(*) >= 10;


-- employees 테이블에서 부서아이디별로 인원수 출력하되, 인원수가 10명 이상인 경우만 출력. 많은 순으로 정렬.

select department_id , count(*) as 인원수

from employees

group by department_id

having count(*) > = 10

order by 인원수 desc;



-- employees 테이블에서 직무id(job_id)별로 그룹 형성해서 직원수(count)출력 직워수가 10명 이상인 경우만 출력,

job_id가 'CLERK'으로 끝나는 경우는 제외


SELECT job_id, count(*)

FROM employees

where job_id not like '%CLERK'

group by job_id

having count(*) >= 10;



문제) employees 테이블에서 입사년도(hire_date)별 직원들의 평균(avg) 급여(salary) 출력.

단, 평균 급여가 10000 이상인 경우만 출력


select to_char(hire_date, 'yyyy'), avg(salary)

FROM employees

group by to_char(hire_date, 'yyyy')

having avg(salary) >= 10000;




문제) employees 테이블에서 직무아이디(job_id)별 직원의 평균 급여를 출력.

단, 평균 급여가 10000 이상이고, 직무가 'MAN'으로 끝나는 경우만 출력


select job_id, avg(salary)

from employees

where job_id like '%MAN'

group by job_id

having avg(salary) >= 10000;







case ~ end 구문


1. 자바에서는 switch ~ case 구문과 비슷한 역할


2. decode 함수와 유사한 역할. decode의 조건식은 = 연산만 가능하지만,

case ~ end 구문은 모든 형태의 조건식 사용 가능.


3.

CASE

WHEN 조건 THEN 결과

WHEN 조건 THEN 결과

...

[ELSE 결과]

END


-- DECODE() 사용

SELECT first_name, last_name

, DECODE(department_id, 90, 'Excutive', 60, 'IT', 50, 'Shipping')

FROM employees;


-- CASE 사용

SELECT first_name, last_name,

case department_id when 90 then 'Excutive' when 60 then 'IT' when 50 then 'Shipping' end

FROM employees;


또는


SELECT first_name, last_name,

case

when department_id = 90 then 'Excutive'

when department_id = 60 then 'IT'

when department_id = 50 then 'Shipping'

end

FROM employees;



--문제) employees 테이블에서 salary가 

10000 미만이 경우는 C등급,

10000 이상, 20000 미만인 경우 B등급,

20000 이상인 경우 A등급으로 출력.


SELECT first_name, last_name,

case

when TRUNC(salary/10000) = 0 then 'C등급'

when TRUNC(salary/10000) = 1 then 'B등급'

when TRUNC(salary/10000) >= 2 then 'A등급'

end AS salaryGrade

FROM employees;



문제) EMPLOYEES 테이블에서 급여(salary)의 수준에 따라 다른 세금 비율(tax_pct) 적용해서 

세금비율, 세금 액수(tax), 실수령액(pay)를 출력.

case~end 구문 이용


20000 이상 > 4%

10000~19999 사이 > 2 %

10000 미만 > 0%


select first_name, last_name,

case

when trunc(salary/10000) >= 2 then '4%'

when trunc(salary/10000) = 1 then '2%'

when trunc(salary/10000) = 0  then '0%'

end as "tax_pct",

case

when trunc(salary/10000) >= 2 then trunc(salary * (4/100))

when trunc(salary/10000) = 1 then trunc(salary * (2/100))

when trunc(salary/10000) = 0  then salary

end 

as "tax",

case

when trunc(salary/10000) >= 2 then trunc(salary - (salary * (4/100)))

when trunc(salary/10000) = 1 then trunc(salary - (salary * (2/100)))

when trunc(salary/10000) = 0  then salary

end 

as "pay"

from employees






Sub query (하위 질의)


1. 쿼리 내부에 쿼리가 포함된 상태.


2. 

SELECT (서브쿼리) -> 서브 쿼리 결과가 출력의 일부가 된다.

FROM (서브쿼리) -> 서브 쿼리 결과가 데이터소스가 된다.(인라인뷰)

WHERE (서브쿼리) -> 서브 쿼리 결과가 조건의 일부가 된다.

[GROUP BY 기준컬럼]

[HAVING 조건식]

[ORDER BY 기준컬럼];



-- employees 테이블에서 급여(salary)가 제일 많은(max 함수) 직원.

-> 1단계. 제일 많은 급여액을 확인.

select max(salary) from employees; -- 24000


-> 2단계. 해당 급여액을 받는 직원 확인.

select * from employees where salary = 24000;


-> 3단계. 서브쿼리 사용

select *

from employees

where salary = (

select max(salary)

from employees

);



-- employees 테이블에서 평균 급여보다 적은 급여를 받는 직원.


select avg(salary) from employees


select *

from employees

where salary < (select avg(salary) from employees);



-- employees 테이블에서 부서 id가 100번인 직원 출력


select *

from employees

where department_id= 100; -- 결과가 여러개인 경우


select *

from employees

where employee_id in (select employees_id

from employees

where department_id= 100);


--> 서브쿼리 결과가 항목은 한개이지만 테이터는 여러개 반환되는 경우

--> in연산자로 처리함.



-- 두개의 테이블에서 서로 관련된 정보 출력하는 경우


select first_name, last_name, dapartment_id

from employees;


select department_id, department_name

from departments;


--> 'Finance'부서에 속하는 직원 정보 출력


select first_name, last_name, dapartment_id

from employees

where department_id = (select department_id from departments where department_name = 'Finance');



-- 직무명(jobs.job_title)이 'Finance Manager'인 경우 직원 정보(employees.*) 출력.

select first_name, last_name

from employees

where job_id = (select job_id from jobs where job_title = 'Finance Manager');



문제) jobs.job_title이 'Finance Manager' 인 직무의 최고 급여액(jobs.max_salary) 보다 많은 급여(employees.salary)를 받는 직원의 정보 출력.


select *

from employees

where salary > (select max_salary from jobs where job_title = 'Finance Manager');



문제) employees 테이블에서 first_name이 'David'이고, last_name이 'Austin'인 직원과 같은

부서아이디(department_id), 같은 급여(salary)를 받는 직원 정보 출력

SELECT *

  FROM employees

  WHERE (department_id, salary) IN

        (SELECT department_id, salary

            FROM employees

            WHERE first_name='David' AND last_name='Austin');



--서브 쿼리가 FROM 구문에 오는 경우

SELECT first_name, last_name

, salary

, tax

, salary-tax AS pay

FROM (SELECT first_name, last_name

, salary

, salary*0.04 AS tax

FROM employees);



--서브 쿼리가 컬럼 리스트에 오는 경우

SELECT first_name, last_name, department_id

,(SELECT department_name 

FROM departments

WHERE department_id=em.department_id)

FROM employees em; --> 별칭 사용시 AS 키워드 사용하지 말것. em은 별칭임.







rank() over() 함수


1. 그룹 내에서 위치를 반환


2.

select 컬럼리스트, rank() over(정렬 기준) as rank

from 테이블;



-- employees 테이블에서 salary가 높은 순으로 정렬해서 출력. 순위 부여.


select first_name, last_name, salary, 

rank() over(order by salary desc) as rank

from employees;



-- 5등까지 출력 (서브쿼리 사용)



select *

from (select first_name, last_name, salary, rank() over(order by salary desc) as rank

from employees)

where rank <= 5;



-- 그룹별 정렬해서 순위부여. partition by 구문 추가


SELECT first_name, last_name, salary, department_id

, RANK() OVER(PARTITION BY department_id 

ORDER BY salary DESC) AS rank

FROM employees;


-- employees 테이블에서 부서아이디(department_id)별로 salary가 가장 높은 wlrdnjs cnffur

--> rank 계산. 순위 1인 경우만 출력


select *

from (SELECT first_name, last_name, salary, department_id

, RANK() OVER(PARTITION BY department_id  ORDER BY salary DESC) AS rank

FROM employees)

where rank = 1;



문제) employees 테이블에서 급여(salary) 액수 상위 10%만 출력.


select *

from (select first_name, last_name, salary,

          rank () over(order by salary desc) as rank

from employees)

where rank <= trunc((select count(*)

from employees)*0.1);







avg() over() 함수



1. 그룹별 평균 계산 전용.


-- GROUP BY 구문 사용시. 부서 id별 평균 급여 출력


select department_id, round(avg(salary))

from employees

group by department_id

order by department_id;

--> 개별 정보는 출력할 수 없다.



-- AVG() OVER() 사용시.  부서 id별 평균 급여 출력


select first_name, last_name, department_id,

avg(salary) over(partition by department_id order by department_id) as 부서별 평균

from employees

--> 개별 정보와 그룹 정보가 같이 출력된다.




-- employees 테이블에서 개인별 부서id 평균(avg함수) 급여(salary)와 개인별 급여와의 차이 출력.


select first_name, last_name, department_id, salary, salary - avgperdepartment_id as "개인별 급여와의 차이"

from (select first_name, last_name, department_id, salary,

avg(salary) over(partition by department_id order by department_id) as avgperdepartment_id

from employees);



-- employees 테이블에서 전체 급여 평균 (AVG함수)과  개인별 급여와의 차이 출력. AVG() OVER() 함수 이용.


select first_name, last_name, salary, avg(salary) over()

from employees









SUM() OVER() 함수


1. 그룹별 합 계산 전용.


-- group by 사용시 부서 ID별 급여 합 출력.

select department_id, sum(salary)

from employees

gorup by department_id;

-> 개별정보 출력불가


-- sum() over() 사용시 부서 ID별 급여 합 출력.


select first_name, last_name, department_id, sum(salary) over(partition by department_id order by department_id)

from employees;



문제) employees 테이블에서 직원 개개인의 급여와 직원 전체 급여 합과의 비중 (%) 출력


WRITTEN BY
빨강꼬마

,



다운로드


- Oracle 11g Express

- Oracle SQL Developer(jdk포함)





DQL(Data Query Lenguage)


1. SELECT 구문에 의해서 테이블의 자료를 검색하는 명령

2.

SELECT 컬럼리스트

FROM 테이블

WHERE 조건식

GROUP BY 기준컬럼

HAVING 조건식

ORDER BY 기준컬럼;


-- 특정 테이블의 자료 확인

select * from employees;



--특정 테이블의 자료 일부 출력.  행(column) 제한

select first_name, last_name, phone_number

from employees;


-- 특정 테이블의 자료 일부 출력. 열(Row) 제한.

select *

from employees

where job_id = 'IT_PROG';


-- 특정 테이블의 자료 일부 출력. 행(column), 열(Row) 제한.


select first_name, last_name, phone_number

from employees

where job_id = 'IT_PROG';






조건식(행 제한)


1. 

select 컬럼리스트

from 테이블

[where 조건식]



2. 연산자 종류

연산자 종류

비교연산자 : =, >, <, >=, <=, <>

논리연산자 : and, or, not

SQL 연산자 : IN(값, ...), BETWEEN ~ AND , LIKE



3. 자료형

-문자형 : CHAR2, VARCHAR2

-날짜형 : DATE

-숫자형 : NUMBER



4. 리터럴(상수)

문자형 : 작은 따옴표로 표기된 문자열. EX) '테스트' 

날짜형 : 날짜 서식을 가진 문자열. '년-월-일', '월/일/년' 형식을 가진 문자열. ex)'2012-10-22'

숫자형 : 정수 또는 실수로 표기된 숫자. ex) 10


올바른 자료형 비교 예.

select *

from employees

where job_id='IT_PROG'; -- 성공


틀린 자료형 비교 예.

select *

from employees

here job_id="IT_PROG";" -- 실패






날짜 데이터 검색하는 방법

-> 날짜 데이터를 문자열 형식으로 변환하는 과정 필요.

-> to_char(날짜데이터, '서식') 함수 이용


--employees 테이블에서 입사년도(hire_date)가 '2003'년인 경우 출력.

select *

from employees

where to_char(hire_date, 'yyyy') = '2003';



--employees 테이블에서 입사월(hire_date)이 '1'월인 경우 출력.

select *

from employees

where to_char(hire_date, 'mm') = '01';


--employees 테이블에서 입사년도(hire_date)가 '2005'년이고, 입사월이 '1'월인 경우 출력.

select *

from employees

where to_char(hire_date, 'yyyy-mm') = '2005-01'; 


-- 혹은

select *

from employees

where to_char(hire_date, 'yyyy') = '2005' and to_char(hire_date, 'mm') = '01';


--employees 테이블에서 입사년도(hire_date)가 '2000'년대인 경우 출력.

-> substr(원본데이터, 출발위치, 길이)


select *

from employees

where to_char(hire_date, 'yyyy') = '2000' or to_char(hire_date, 'yyyy') = '2001' or to_char(hire_date, 'yyyy') = '2002'

      or to_char(hire_date, 'yyyy') = '2003' or to_char(hire_date, 'yyyy') = '2004' or to_char(hire_date, 'yyyy') = '2005'

      or to_char(hire_date, 'yyyy') = '2006' or to_char(hire_date, 'yyyy') = '2007' or to_char(hire_date, 'yyyy') = '2008'

      or to_char(hire_date, 'yyyy') = '2009';

      

-- 혹은

select *

from employees

where substr(to_char(hire_date, 'yyyy'), 1, 2) = '20';


-- 혹은 (문자열도 크기 비교가 됨.)

select *

from employees

where to_char (hire_date, 'yyyy') >= '2000' and

         to_char(hire_date, 'yyyy') <= '2009';






숫자 데이터 검색하는 방법



-- employees 테이블에서 salary(급여)가 20000달러 이상인 경우 출력

select *

from employees

where salary >= 20000;


--employees 테이블에서 salary(급여)가 5000~10000번위인 경우 출력


and연산자 사용.








SQL 연산자


1. IN(값, ...): or 연산 역할. 괄호 안의 나열된 값중 하나와 일치하면 true


2. BETWEEN 값1 AND 값2: and 연산 역할. 값1~값2 범위 지정시


3. LIKE: 피연산자가 패턴(_, %)과 일치하는 경우 true;



--jobs 테이블에서 job_title이 'President' 이거나, 'Sales Manager'인 경우 출력.

select *

from jobs

where job_title='President' or job_title = 'Sales Manager';


-- 혹은

select *

from jobs

where job_title in ('President', 'Sales Manager');



--employees 테이블에서 salary(급여)가 5000~10000번위인 경우 출력


select *

from employees

where salary between 5000 and 10000;




--employees 테이블에서 전화번호가 4567로 끝나는 경우 출력

-> substr은 길이가 서로 다른 문자열에서는 일부 문자열을 얻기가 힘듬.

-> like 연산자 사용


select *

from employees

where phone_number like



--employees 테이블에서 전화번호가 국번이 123로 끝나는 경우 출력


select *

from employees

where phone_number like '%123_____';


select *

from employees

where phone_number like '___.123.____';








null 확인


1. 데이터베이스 내의 데이터 저장 공간을 확보한 후 데이터를 채우지 않으면

자동으로 NULL값이 저장된다.


2. is 로 확인.


-- employees 테이블에서 commission_pct 정보가 null인 경우 출력


select *

from employees

where commission_pct is null;









내장 함수


- 함수는 데이터를 입력시 , 단일 결과를 리턴하는 프로그램의 집합


1. 입력데이터가 없는 경우

select sysdate from dual; -- 시스템의 현재 날짜


select to_char(sysdate,'yyyy-mm-dd') from dual;


select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;






2. 입력 데이터가 있는 경우

TO_DATE('날짜문자열') : 문자열을 날짜 형식으로 변환


select sysdate - to_date('특정날짜') from dual;

-- 특정 날짜부터 특정 날짜 사이의 날짜수 계산



TO_CHAR(날짜자료형, '서식')


select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

--서식에 맞게 날짜, 시간 출력


ROUND(값)


select round (10.123) from dual; --10

select round (10.567) from dual; --11

select round (10.567, 2) from dual; --10.57

select round (10.567, 1) from dual; --10.6

-- 반올림 함수



TRUNC(값)


select trunc (10.123) from dual; --10

select trunc (10.567) from dual; --10

-- 절삭함수

ex) employees 테이블에서 salary가 10000이상이면서, 20000 미만인 경우 출력

select *

from employees

where trunc (salary/10000) = 1;



MOD(값1, 값2)


-- 나머지 연산 함수


LOWER('대문자') : 입력된 문자를 소문자로 변경


UPPER('소문자') : 입력된 문자를 대문자로 변경


ex)

select *

from employees

where first_name = 'ellen'; --X


select *

from employees

where first_name = 'Ellen'; --O


select *

from employees

where first_name = 'ELLEN'; --X


select *

from employees

where LOWER(first_name) = LOWER('ellen'); --O


select *

from employees

where UPPER(first_name) = UPPER('ellen'); --O



CONCAT(문자열1, 문자열2) : 입력된 문자열1과 문자열2를 하나의 컬럼으로 합쳐 출력


LENGTH(원본문자열) : 문자열 길이


SUBSTR(원본문자열, 위치, 길이) : 부분 문자열 리턴


INSTR(원본문자열, 검색문자열) : 문자열 검색.


REPLACE(원본문자열, 검색문자열, 대체문자열) : 문자열 대체


LPAD(식,길이,표시문자열) : 문자열 대체. 특정 문자열로 채워짐. (LIFT)

EX) 

select '801010-1234567' from dual;

select lpad((substr('801010-1234567', 7, 8), 14, '*') from dual;



RPAD(식,길이,표시문자열) : 문자열 대체. 특정 문자열로 채워짐. (RIGHT)

EX) 

select rpad(substr('801010-1234567', 1, 7), 14, '*') from dual;


NVL(식, 값) : 식의 결과값이 null 이면 값 리턴 (if구문만 있는 상태)


NVL2(식, 값1, 값2) : 식의 결과값이 null이면 값1 리턴 아니면 값2 리턴 (if~else 구문 상태)


DECODE(식, 조건, 값1, 조건2, 값2, ...) : 식의 조건이 참이면 값1을 리턴하고 조건2를 만족하면 값2를 리턴.. (if ~ else if~ else 인 상태)

ex)

select first_name, last_name, decode(department_id, 90, 'Excutive', 60, 'IT', 50, 'Shipping')

from employees;


ex)employees 테이블에서 salary가 10000미만인 경우는 C등급, 10000이상 20000미만인 경우는 B등급, 20000이상인 경우는 A등급으로 출력

select first_name || ' ' || last_name AS 이름, 

decode(trunc(salary/10000), 0, 'C등급', 1, 'B등급' , 2, 'A등급') as 급여등급

from employees;


WRITTEN BY
빨강꼬마

,

◆ view ◆

- view
  실제로 존재하지 않는 가상의 테이블을 의미함.
  즉, A, B, C, D라는 컬럼을 가진 실제 테이블이 있고,
  이 테이블에서 select의 함수등을 통해 연산처리된 추가적인 컬럼(E)를 만들수 있다면
  출력된 결과를 가상의 테이블, 즉 view로 만들어 사용할 수 있게 한다.


EX)
  jikwon 이라는 테이블의 정보(총 6개의 column)

 

  select 함수를 이용하여 현재나이, 정년퇴직일자, 연봉을 추가로 출력한 결과값

  해당 결과값을 뷰(가상테이블)로 생성

  create or replace view [생성할뷰이름] as

  가상테이블로 만들어진 데이터 확인

 

  이렇게 만들어진 뷰를 대상으로 예를 들어 [정년퇴직일이 2040년 이후인 직원중 연봉이 3000이상인 직원을 검색하라] 라는 문제를 보다 쉽게 해결할 수 있음. 또한, 뷰는 테이블의 정보를 변경하지 않기 때문에 테이블의 정보를 기반으로 구성된 쿼리나 프로그램에는 전혀 영향을 끼치지 않음.

 - 뷰는 sys계정에서 create view 권한을 주어야 테이블 생성 권한을 주는 것처럼 뷰를 생성할 수 있음.
 - create view [생성될뷰이름] as select ~~~ 로도 뷰 생성이 가능하나, 뷰내용의 교체(replace)는
          replace 명령 단독으로 실행되지 않기 때문에, create or replace view 로 사용하는 것을 권장함.
 - 뷰는 테이블과의 구분이 시각적으로 어렵기 때문에 파일명에서 차이를 두는 습관을 기르도록 권장함.
   (ex: tbl_abc / view_abc 등)
 - 뷰는 생성시 alias를 지정해야 정상적으로 생성된다. 다만, 컬럼명에서 함수등이 사용되지 않아 원 컬럼명 그대로
   사용된 경우 alias 지정을 하지 않아도 생성이 가능하다.
 - alias 지정시 각 컬럼명 뒤에 as "~~~"식으로도 가능하나,
   create or replace view (컬럼1, 컬럼2, .....) 식으로 순차 맵핑시켜 생성도 가능하다.


- select * from user_views;
  생성되어 있는 뷰의 소스를 확인할 수 있다.

- drop view [뷰이름]
  생성한 뷰를 삭제한다.

- revoke [회수할 권한] from [사용자명];
  sys 계정 명령어로 권한을 다시 회수할 때 사용하는 명령

- betweeb [X] and [Y]
  X 부터 Y 까지의 데이터.
  예를들어 1번부터 100번까지의 데이터가 있는데, 이중 30~39까지의 데이터를 뽑고싶다면 다음과 같이 사용할 수 있다.
  between 30 and 39  혹은  trunc(현재나이, -1) = 30  혹은  숫자>=30 and 숫자 <=39

 

◆ inline view ◆

 - inline view
   인라인 뷰란, 실제 뷰를 생성하지 않고 SQL 쿼리문 안에서 선언하여 쿼리문 수행시 임시적으로 view를 만들어
   사용하는데 이것을 inline view 라 칭함.

ex)
    inline view 로 사용할 소스

 

    작성한 소스 select 앞과 소스 가장 마지막을 (소스내용)["inline view명";] 를 사용하여 선언한다.

 

    선언한 inline view는 선언한 inline view를 사용할 소스의 from 절에 위치하도록 한다.

 

 

선언한 inline view의 컬럼명은 ["inline view명".inline view컬럼명] 으로 사용한다.
- 선언된 inline view를 사용하여 작성한 쿼리문 역시 다시 inline view를 사용하여 묶을 수 있다.

 

 

 


WRITTEN BY
빨강꼬마

,

- trunc
  소수점 이하 특정수치까지 표현할때 사용할수 있다. 소수있는 데이터를 소수없이 정수로만 표현할때도 사용한다.

 

  trunc(임의의데이터, [x])
  X = 소수점을 표현할 자릴수를 의미.
      0은 정수만 표현하며, 이는 X를 기입하지 않는 경우 trunc는 자동으로 0으로 인식한다.
      -1은 10의 자리까지만 보여주고 이하는 버리라는 의미. -2는 100의 자리까지 보여주라는 것.

 


- mod
  특정데이터를 나누었을때 남는 나머지값을 보여줌.

 

  mod(임의의데이터, [X])
  X = 임의의데이터를 나눌 값을 입력.
    2를 입력하면 임의의데이터를 2로 나눈후 나머지 값을 출력하고.
    5를 입력하면 임의의데이터를 5로 나눈후 나머지 값을 출력.

※ 시간에서 시간을 빼면 그 결과값은 일수로 출력된다.

 

- months_between
  입력된 두 날짜를 기준으로 몇개월차이나는지 확인.

 

 

  months_between([X], [Y])
  X, Y = 입력될 데이터는 날짜형식에 맞추어(ex: 2001-01-01) 입력하면 별도로 타입변형을 하지 않아도 됨.

 

- last_day
  해당 날짜가 속한 달의 매월 마지막날이 몇일인지 출력

 

 

 

- next_day
  지정된 돌아올 요일이 해당하는 날짜

 

 

  next_day(sysdate, '일')
  next_dat('2012-09-07', '월') -- 2012-09-07을 기준으로 월요일이 돌아올 날짜 -- 출력값은 12/09/10

 

 

 

 

 

 


복습 및 응용.

1. 1년 2개월 3일 4시간 5분 6초를 초로 환산하기.

2. 환산된 초를 년월일시간분초 형태로 나타내기

3. 경매번호, 경매물건, 경매종료시각으로 구성된 테이블에서
   현재로부터 남은기간은 얼마인지 나타내기

4. 26개월을 복무기간으로 입대한다. 제대까지 먹어야할 끼니는 몇번 먹을까.

5. 사용자 khman1에서 만든 jikwon 테이블을 기준으로 아래와 같이 나타내기.
   단, 정년퇴직일자는 해당사원의 나이(한국나이)로 60세가 되는 년도의 입사일자로 한다.
   ----------------------------------------------------
    사원명      현재나이     입사일자     정년퇴직일자
   ----------------------------------------------------


WRITTEN BY
빨강꼬마

,