Yi

The following J2EE Applications have been deployed and are accessible at the URLs listed below.

iSQL*Plus URL:
http://localhost.localdomain:5560/isqlplus

iSQL*Plus DBA URL:
http://localhost.localdomain:5560/isqlplus/dba

Oracle10g 에서 테이블을 삭제하면 9i 이전 버전의 경우 바로 삭제 된다.
하지만 Oracle 10g의 경우 윈도우의 휴지통과 같은 기능을  제공한다.

테이블을 DROP 하면 BIN$xxxxxxx 과 같이 테이블이 생성된다.
이 테이블은 언제든지 되살릴 수 있으며 쿼리도 모두 된다.
DESC "BIN$xxxxxxx";
SELECT * FROM "BIN$xxxxxxx";

이 BIN 테이블을 완전히 삭제하기 위해서

SQL> purge recyclebin;

--emp_process 프로시져 만들고
--SQL> set serveroutput on
--SQL> exec emp_process

--emp_process2 프로시져 만들고 (부서별 조회)
--SQL> exec emp_process2

--emp_process3 프로시져를 만들고 (업데이트)
--SQL> exec emp_process3

--test4,test5,
--페키지 emp_comm

--dbms_job 패키지
--일정 시간 간격으로 DB작업을 수행
--1. submit : 새로운 job를 등록하는 프로시져
--2. run : 등록된 job을 실행하는 프로시져
--3. remove : 등록된 job을 제거하는 프로시져

create table job_table(
seq number(5),
job_date date
);
--프로시저 만들기 JOB_TEST
--SQL> var job number;
--SQL> exec dbms_job.submit(:job,'job_test;' , sysdate, 'sysdate+10/36000';
--SQL> exec dbms_job.run(:job);
--SQL> exec dbms_job.remove(:job);

--트리거(Trigger)
--테이블의 변경을 자동 감지해서 다른 테이블을 작업을 수행
--예를 들어 게시물이 등록되면 게시물테이블에 데이터가 입력되고,
--게시물카운트테이블에도 데이터가 입력되게 하겠다.
--로깅, 보안, 감사에 많이 쓰임

--트리거의 구성요소
--1.트리거의 유형
--1)문장레벨의 트리거 : 테이블에 update, insert, delete가 일어나면
--   단한번만 트리거를 발생시킴(트리거의 수 = 1)
--2)행레벨의 트리거 : 테이블에 update,insert,delete가 일어나면
--   모든행에 대해 트리거를 발생시킴(트리거의 수 = 행의 수)
--2. 트리거의 타이밍
--1) before : 테이블에 update, insert, delete 가 일어나기 전
--2) after : 테이블에 update, insert, delete 가 일어난 후
--3. 트리거의 이벤트
--1) update
--2) insert
--3) delete
--test1_trigger
--sql)update emp set sal = sal * 1.0;

create table dept_same (
deptno number(2),
dname varchar2(14),
loc varchar2(10),
o_deptno number(2),
o_dname varchar2(14),
o_loc varchar2(10),
gubun varchar2(10),
chk date
);

--test2_trigger 생성
--SQL> insert into dept values(99,'CCC','SEOUL');
--update dept set loc = 'busan' where deptno=99;
--delete from dept where deptno=99;

--트리거의 상태 변경
--1. 트리거 활성화/비활성화
--alter tirgger 트리거 이름 enable
--alter tirgger 트리거 이름 disable
--2. 테이블에 관련된 모든 트리들을 활성화/비활성화
--alter table 테이블명 enable all triggers;
--alter table 테이블명 disable all triggers;
--3. 트리거 재컴파일
--all trigger 트리거명 compile;
--4. 트리거 제거
--drop trigger 트리거명;

--데이터 사전
--사용자의 객체 정보 : user_objects
select object_name, object_id, object_type, created, timestamp, status
from user_objects;
select object_name from user_objects where object_type='PROCEDURE';

-- 소스코드 : user_source
select name, type, line, text
from user_source where name='TAX';
select name, type, line, text
from  user_source where name='TEST5';

-- 사용자의 에러 정보 : user_errors
select * from user_errors;

--사용자의 트리거 정보 : user_triggers
select trigger_name, trigger_type, triggering_event, table_name, status from

user_triggers;

--사용자 객체들의 상호참조 정보 : user_dependencies
select * from user_dependencies where referenced_name = 'EMP';
--사용자의 테이블 정보
select * from user_tables;

--사용자의 뷰 정보
select * from user_views;

--사용자 인덱스 정보
select * from user_indexes;

--사용자의 제약조건 정보
select * from user_constraints;

--바인드 관련 자료
--http://blog.naver.com/nkmin80?Redirect=Log&logNo=140032147

20090220-lsj403.sql

emp_comm-lsj403.sql

emp_process-lsj403.sql

emp_process2-lsj403.sql

emp_process3-lsj403.sql

job_test-lsj403.sql

test1_trigger-lsj403.sql

test2_trigger-lsj403.sql

test4-lsj403.sql

text5-lsj403.sql

-- PL/SQL의 종류
-- 1. Anonymous Procedure (이름없는 프로시져)
-- 수행할때마다 컴파일해서 연산/결과를 출력
-- 2. Stored Procedure (이름있는 프로시져)
-- 컴파일하면 오라클에 PCODE(컴파일된코드) 형태로 저장되었다가
-- 호출하면 연산/결과를 출력
-- 3. Stored Function (이름있는 함수)
-- 오라클에 내장되어 있는 내장함수들과 사용자가 만든 사용자정의함수로 분류
-- 오라클에 PCODE(컴파일된코드) 형태로 저장되었다가 호출하면 연산하고
-- 결과를 반환
-- 4. Package
-- 비슷한 기능을 하는 함수/프로시져를 묶음
-- 선언부와 구현부로 나누어서 작성
-- 5. Trigger
-- 테이블에 데이터의 입출력을 감지해서 다른 테이블에 입출력하는 기능

-- Stored Procedure
create table log_table (
userid varchar2(10),
log_date date
);

select * from log_table;

-- 매개변수의 모드(mode)
-- in : 값을 프로시져 외부에서 받아오는 매개변수 (기본값)
-- out : 프로시져의 연산결과를 외부로 보내는 매개변수
-- in out : 값을 받아와서 외부르 보내는 매개변수

create sequence s_emp_id
start with 25;

create table top_dogs (
name varchar2(10),
salary number(7,2)
);

-- Stored Function (저장함수)
-- 1. 오라클 내장함수 : 오라클에 내장되어 있는 함수
-- 2. 사용자 정의함수 : 사용자가 생성한 함수

-- 실습
-- 1. 핸드폰번호를 입력하면 010-1234-1234 형식으로 출력해주는 프로시져 hp_proc
-- 실행 : exec hp_proc('01012341234');
-- 결과 : 010-1234-1234
-- 2. 문자열을 입력하면 문자열의 길이를 리턴하는 함수 str_func
-- 실행 : select str_func('안녕하세요!') from dual;
-- 결과 : 6
-- 3. 학생정보 입력/삭제
create table student(stdno number, stdname varchar2(10));
-- * record타입으로 학생정보 저장
-- 입력 : 프로시져 registStudent - 학생번호(number), 학생명(varchar2)
--   실행 : exec registStudent(10, '홍길동')
--   결과 : 홍길동 학생이 등록되었습니다. or 10번 학생이 있습니다.
-- 삭제 : 프로시져 deleteStudent - 학생번호(number)
--   실행 : exec deleteStudent(10)
--   결과 : 10번 학생이 삭제되었습니다. or 10번 학생은 없습니다.
-- 수정 : 프로시져 updateStudent - 학생번호(number), 학생명(varchar2)
--        (학생명 수정되도록)
--   실행 : exec updateStudent(10, '강감찬')
--   결과 : 10번 학생이 수정되었습니다. or 10번 학생은 없습니다.
-- 목록 : 프로시져 listStudent (커서 이용)
--   실행 : exec listStudent
--   결과 : 학생리스트가 보여진다.

0219_procedure-lsj403.sql

20090219-lsj403.sql

20090219_2-lsj403.sql

add_one-lsj403.sql

deletestudent-lsj403.sql

f_tax-lsj403.sql

gugudan-lsj403.sql

hire_emp-lsj403.sql

hp_proc-lsj403.sql

log_execution-lsj403.sql

p_hap-lsj403.sql

query_emp-lsj403.sql

registerstudent-lsj403.sql

str_func-lsj403.sql

tax-lsj403.sql

test2-lsj403.sql

updatestudent-lsj403.sql

select employee_id, first_name, last_name
from employees
where employee_id = :emp;

create table score(
score number);

insert into score values(100);
insert into score values(0);
insert into score values(null);

select avg(score), count(*) from score;
select avg(nvl(score, 0)), count(*) from score;

select department_id, sum(salary) from employees
group by department_id
order by department_id;

--------------------------------------------------
--scott
-------------------------------------------------
--built-in function (오라클 내장함수)
---------------------------문자함수
--ascii 코드값에 해당하는 문자
select chr(75) from dual;

-- 문자열 접합
select concat(concat(ename, ' is a '), job) from emp;

-- 첫글자들을 대문자로
select initcap('i am a boy') from dual;

-- 소문자로
select lower('Mr, Smith') from dual;

--대문자로
select upper('Mr, Smith') from dual;

--왼쪽채우기 (전체 15자리 왼쪽 나머지 공간은 *)
select lpad('Page 1', 15, '*') from dual;

--오른쪽채우기 (전체 15자리 오른쪽 나머지 공간은 *)
select rpad('Page 1', 15, '*') from dual;

--왼쪽 지우기 (왼쪽에 x나 y가 나오면 제거-안나올때까지)
select ltrim('xyxXxyLast Word', 'xy') from dual;

--문자열 대체
select replace ('JACK and JUE', 'J', 'BL') from dual;

--부분 문자열 추출 (인덱스 3에서 2개-인덱스는 1부터 시작)
select substr('abcdefg',3,2) from dual;
select substr('abcdefg',3) from dual;
select substr('abcdefg',-3,2) from dual;

--문자의 유니코드값
select ascii('뷁') from dual;

--첫번째인자문자열에서 두번째 문자열이
--세번째인자문자열에서 네번째인자번째나온 곳의 인덱스
select instr('CORPORATE FLOOR','OR',3,2) from dual;
select instr('CORPORATE FLOOR','OR',-3,2) from dual;

--문자열의 길이
select length('안녕하세요!') from dual;
select lengthb('안녕하세요!') from dual;

--가장큰것(각각의 문자를 비교하여 각각 큰거 & 작은거 비교)
select greatest('HARRY', 'HARIOT', 'HALORD') from dual;

--가장 작은것
select least('HARRY', 'HARIOT', 'HALORD') from dual;

-- null 대체
--nvl : 첫번째 인자가 null이면 두번째 인자, 아니면 첫번째 인자.
select nvl(sal,0), nvl(ename,'*'), nvl(hiredate, '02/11/11') from emp;

----------------------------------시스템 함수
select sysdate from dual;
select systimestamp from dual;
select user from dual;

----------------------수학함수
--절대값
select abs(-15) from dual;

--올림값
select ceil(15.7) from dual;

--내림값
select floor (15.7) from dual;

--버림값
select trunc(15.7) from dual;

--cosine
select cos(180*3.14/180) from dual;

--e의 몇 승
select exp(4) from dual;

--log
select log(10, 100) from dual;

--나머지
select mod(11,4) from dual;

--승
select power(3,2) from dual;

--반올림
select round(15.193, 1) from dual;
select round (15.193, -1) from dual;

-------------------------------날짜함수
--월더하기
select hiredate, add_months(hiredate, 1)
from emp where empno=7782;

--해당 월의 마지막날
select hiredate, last_day(hiredate) from emp;

--시간대 변경
select hiredate, new_time(hiredate, 'GMT', 'PDT') from emp;

--개월의 차
select hiredate, months_between(sysdate, hiredate) from emp;

------------------------------변환 함수

--MM:월
--MON:세글자 영문 월
--MONTH:전체글자 영문 월
--D:그주의 일 수(1:일요일)
--DD:날짜
--DY:요일을 세글자 영문
--DAY:요일의 전체 이름
--YYYY:네자리 표현한 연도
--YY:두자리로 표현한 연도
--HH:12시간으로 표시, HH24:24시간으로 표시
--MI:분
--SS:초
select sysdate, to_char(sysdate, 'D') from dual;
--2009/02/18 12:10
select sysdate, to_char(sysdate, 'YYYY/MM/DD HH24:MI') from dual;
--02일 ==> 2일 (앞에 0제거)
select ename, to_char(hiredate, 'fmDD-MM-YY') from emp;

--문자열을 숫자로 변환
select to_number('100')+200 from dual;
select '100'+200 from dual;

--숫자를 형식에 맞게 문자로 변환
--숫자혀익요소 9(숫자하나), 0(자리가비면 0으로), $(달러).(소수점),(콤마)
select to_char(12506, '$9099,99') from dual;
select '\'||to_char(1234567, '90999,99') from dual;

--날짜의 형식을 변경
select sysdate, to_date('09/07/01', 'DD-MM-YY') from dual;

-- 9i버젼에서 추가된 함수
--nvl2(첫번째인자가 null이면 세번째 인자 null이 아니면 두번째 인자)
select empno, ename, nvl2(comm, comm*1.1, 0) from emp;
--nullif(같으면 null, 다르면 첫번째 인자)
select empno, ename, nullif(comm,0) from emp;
--coalesce(null이 아닌 첫번째 인자)
select coalesce(comm, sal) from emp;
select coalesce(null,null,null,3,null) from dual;
--trim (8i에 추가)
select ename, trim(LEADING 'A' FROM ename) as trim
from emp
where ename like 'A%';

select ename, trim(TRAILING 'N' FROM ename) as trim
from emp
where ename like '%N';

select ename, trim(BOTH 'A' FROM ename) as trim
from emp
where ename like '%';

--------------------------------------복수행 함수
select count(*) from emp;
select count(empno) from emp;
select sum(sal) from emp;

select avg(sal) from emp;
select avg(nvl(sal,0)) from emp;

select max(sal), min(sal) from emp;

--입사일이 가장 늦은 사람과 이른 사람의 일수 차
select max(hiredate)-min(hiredate) from emp;

--부서별 평균 급여
select avg(nvl(sal,0)), deptno
from emp
group by deptno;

--그룹에 조건을 줄때는 having
--그룹에 속하는 사원수가 2보다 크다라는 조건
select deptno, count(*), sum(sal)
from emp
group by deptno
having count(*)>2;

--decode
--decode(expr, a, r1,b,r2,default)
--expr을 평가해서 a이면 r1, b이면 r2, 나머지 모든 경우 default 반환
select job, sal, decode(job, 'ANALYST', sal*1.10,  'CLERK', sal*1.15,
  'MANAGER',sal*1.20, sal) "인상된 급여"
from emp;

--1월 부터 6월 까지 월별로 입사한 사원의 수와 1~6월 전체 입사한 사원의 수
select
count(decode(extract(month from hiredate), '1', 1)) "1월",
count(decode(to_char(hiredate, 'MM'), '02', 1)) "2월",
count(decode(to_char(hiredate, 'MM'), '03', 1)) "3월",
count(decode(to_char(hiredate, 'MM'), '04', 1)) "4월",
count(decode(to_char(hiredate, 'MM'), '05', 1)) "5월",
count(decode(to_char(hiredate, 'MM'), '06', 1)) "6월", count(*) "1월~6월"
from emp
where to_char(hiredate,'MM')>='01' and to_char(hiredate,'MM')<='06';

--case
select job, sal,
  case
    when job='ANALYST' then sal*.10
    when job='CLERK' then sal*1.15
    when job='MANAGER' then sal*1.20
    else sal
  end
from emp;

--사원들의 입사일의 요일을 한자로
select ename, hiredate,
  case
    when to_char(hiredate,'DY')='월' then '月'
    when to_char(hiredate,'DY')='화' then '火'
    when to_char(hiredate,'DY')='수' then '水'
    when to_char(hiredate,'DY')='목' then '木'
    when to_char(hiredate,'DY')='금' then '金'
    when to_char(hiredate,'DY')='토' then '土'
    when to_char(hiredate,'DY')='일' then '日'
  end
from emp;

20090218-lsj403.sql

20090218_home-lsj403.sql

-- 오라클 객체
-- 1. table
-- 데이터를 저장하는 객체
-- 2. index
-- 데이터 검색속도 증가를 위한 객체
-- 3. view
-- 테이블(들)의 데이터를 보호할 목적으로 사용되는 객체
-- 4. sequence
-- 연속적인 일련번호 생성을 위한 객체
-- 5. synonym
-- 객체의 이름을 간략하게 하기위한 객체

-- Data Dictionary (데이터사전)
-- 데이터에 대한 데이터(MetaData)를 조회할 목적의 뷰들
-- dictionary 조회
select * from dictionary;
-- 사용자의 테이블들
select * from user_tables;
-- 사용자의 인덱스들
select * from user_indexes;
-- 사용자의 뷰들
select * from user_views;
-- 사용자의 제약조건들
select * from user_constraints;

-- 테이블 생성 기본문법
create table dept1 (
deptno number(2),
dname varchar2(13),
loc varchar2(14)
);
-- 테이블 복사
create table dept2
as
select * from dept1;
-- 테이블 목록
select * from tab;
-- 테이블의 컬럼 추가
alter table dept1 add (bigo varchar2(15));
desc dept1;
-- 테이블의 컬럼 변경
alter table dept1 modify (bigo varchar2(30));
-- 테이블의 데이터 삭제 (delete + commit)
truncate table dept1;
-- 테이블명 변경
rename dept2 to dept3;
select * from tab;
-- 테이블 제거
drop table dept3;
-- 주석 달기
comment on table dept1 is '부서정보 테이블입니다!';
select * from user_tab_comments where table_name='DEPT1';
comment on column dept1.deptno is '부서코드';
select * from user_col_comments where table_name='DEPT1';

-- 제약조건 (constraints)
-- 데이터 무결성(integrity)을 지킬 목적으로 컬럼레벨이나 테이블레벨에
-- 부여하는 제약 사항

-- 제약조건들
-- 1. primary key : not null + unique
-- 2. foreign key : 다른테이블의 primary key컬럼의 값을 참조하는 컬럼
-- 3. unique : 컬럼의 값이 유일, 단 null은 여러개 입력될 수 있음
select 1 from dual where null=null;
select 1 from dual where null is null;
-- 4. not null : 컬럼에 값이 null이 입력될 수 없음
-- 5. check : 컬럼에 입력되는 값에 대해 체크
-- 6. default : 컬럼에 입력되는 기본값

-- 제약조건의 특징
-- 사용자에 의해 발생한 잘못된 DML문을 수행되지 않도록 할 목적
-- 제약조건에 대한 모든 정보는 dictionary에 저장된다.
-- 제약조건을 활성화(enable), 비활성화(disable)할 수 있다.
-- 하나의 컬럼에 여러 제약조건을 부여할 수 있다.
-- 여러개의 컬럼에 하나의 제약조건을 부여할 수 있다.
-- 제약조건은 오라클 서버가 관리한다.

-- 제약조건의 유형
-- 1. column level constraints (컬럼레벨제약)
-- 하나의 컬럼에 제약 조건을 정의할 때 사용
-- create, alter 문장으로 컬럼을 정의할 때 데이터 타입 뒤에 정의
-- 위에 있는 모든 제약조건을 컬럼레벨로 모두 부여할 수 있음
-- 2. table level constraints (테이블레벨제약)
-- 하나의 컬럼에 여러개의 제약조건을 부여할때 주로 사용
-- not null제약조건은 table level로 부여할 수 없음

create table dept9 (
deptno number(2) constraint dept9_deptno_pk primary key,
dname varchar2(15),
loc varchar2(1));

drop table dept9;

-- 제약조건명 검색
select constraint_name from user_constraints;
-- 제약조건 추가
alter table dept9 add (constraint dept9_deptno_nn primary key(deptno));

create table dept10 (
deptno number(2) constraint dept10_deptno_pk primary key,
dname varchar2(15),
loc varchar2(1));

create table emp10 (
empno number(4) constraint emp10_empno_pk primary key,
ename varchar2(15),
deptno number(2) constraint emp10_deptno_fk references dept10(deptno));

create table emp11 (
empno number(4) constraint emp11_empno_pk primary key,
ename varchar2(15) constraint emp11_ename_uq unique,
deptno number(2)
);

alter table emp11 add (constraint emp11_deptno_fk foreign key(deptno)
references dept10(deptno));

alter table emp11 add (constraint emp11_deptno_uq unique(deptno));

create table dept12 (
deptno number(2),
dname varchar2(15),
loc char(1) constraint dept12_loc_ck check (loc in ('1','2'))
);

alter table dept12 add (constraint dept12_loc_ck2 check (loc in ('1','2')));

create table dept13 (
deptno number(2),
dname varchar2(15) not null,
loc char(1)
);

select * from user_constraints;

-- 제약조건 삭제
alter table dept12 drop constraint dept12_loc_ck2;
-- 제약조건 비활성화
alter table dept12 modify constraint dept12_loc_ck disable;
-- 제약조건 활성화
alter table dept12 modify constraint dept12_loc_ck enable;

---------------------------------------------------------
-- index
---------------------------------------------------------
--인덱스는 테이블 검색속도를 향상시키기 위한 객체
--주로 B*Tree인덱스를 사용
--- 인덱스를 사용해야 할 때
--1. where절에 자주 등장하는 컬럼
--2. 검색결과가 전체 행의 10%미만인 경우
--- 인덱스를 사용하지 말아야 할 때
--1. 데이터가 적은 (보통 행이 몇천개 이하) 경우
--2. where절에 자주 등장하지 않는 컬럼
--3. 테이블이 자주 수정,삽입,삭제될때
--- 인덱스의 종류
--1. single index : 단일컬럼으로 만들어지는 인덱스
--create index i_emp_ename on emp(ename)
--2. concanated index : 복합컬럼(두개 이상의 컬럼)으로 만들어지는 인덱스
--create index i_emp_empno_ename on emp(empno, ename)
--3. unique index : 인덱스를 생성할 컬럼의 값이 중복되지 않는 경우
--create unique index i_emp_empno on emp(empno)
--4. non-unique index : 인덱스를 생성할 컬럼의 값이 중복되는 경우
--create index i_emp_ename on emp(ename)
--5. function-based index : 컬럼들의 연산결과를 토대로 만든 인덱스
--create index i_emp_sal_comm on emp(sal-comm)

-- 인덱스 생성
create index i_emp_ename on emp(ename);
create unique index i_emp_empno on emp(empno);

-- 인덱스 확인
select * from user_indexes;

-- 인덱스 제거
drop index i_emp_ename;

---------------------------------------------------
-- view
---------------------------------------------------
-- 테이블 데이터를 보호할 목적으로 테이블(들)의 일부데이터를
-- 추출해서 만든 가상 테이블
-- materialized view를 제외하면 view는 물리적으로 데이터를
-- 저장하지 않음. 간단히 말해서 view는 테이블(들)의 select문
select e.empno, e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;

-- 뷰 생성
create or replace view view_emp
as
select e.empno, e.ename, d.dname, d.deptno
from emp e, dept d
where e.deptno = d.deptno;

-- 사용자 뷰 확인
select * from user_views;

-- view를 통한 테이블 select
select * from view_emp;

-- force : 테이블이 없어도 뷰를 생성 가능
create or replace force view emp_chk
as
select * from employees
where deptno=10;

-- with check option : 뷰를 생성할때 제약조건을 부여
-- 뷰 생성시 부여된 조건을 뷰의 결과가 계속 유지되도록 하겠다.
create or replace view emp_chk1
as
select e.empno, e.ename, d.dname, d.deptno
from emp e, dept d
where e.deptno = d.deptno and e.deptno=30
with check option constraint emp_v_chk1;

select * from user_constraints;
select * from emp_chk1;

-- with check option을 가진 뷰에 변경이 일어나게 되므로 수행 안됨
--update emp_chk1 set deptno=20 where deptno=30;
--update emp_chk1 set deptno=30 where deptno=20;

-- 읽기 전용 뷰
create or replace view emp_chk2
as
select e.empno, e.ename, d.dname, d.deptno
from emp e, dept d
where e.deptno = d.deptno and e.deptno=30
with read only;

-- update emp_chk2 set deptno=20 where deptno=30;
-- delete from emp_chk2;

-- 뷰 변경 : 없음 => create or replace를 통해 재생성

-- 뷰 제거
drop view emp_chk2;

-- inline view (인라인뷰:from절내에 있는 서브쿼리)
select a.empno, a.ename, a.sal, a.hiredate
from emp a,  (select deptno, max(hiredate) maxdate
              from emp
              group by deptno) b
where a.deptno = b.deptno;

-- top-n query
select rownum, a.empno, a.ename, a.sal, a.hiredate
from emp a,  (select rownum rn, deptno, hiredate from emp) b
where a.deptno = b.deptno and b.rn<5;

--------------------------------------------
-- sequence
--------------------------------------------
-- 연속적인 일련번호 생성
create table seq_test (
seq number,
name varchar2(20)
);
-- 시퀀스 생성
create sequence seq_test_seq
increment by 1
start with 1000
maxvalue 10000
nocache
nocycle;
-- 시퀀스 확인
select * from user_sequences;
-- 시퀀스 사용
insert into seq_test(seq,name)
values (seq_test_seq.nextval,'홍길동');
select * from seq_test;
-- 시퀀스 현재값
select seq_test_seq.currval from dual;
-- 시퀀스 제거
drop sequence seq_test_seq;

-------------------------------------
-- synonym
-------------------------------------
-- 객체의 이름이 길때 줄여쓰기 위해서
rename emp to employees;
select * from tab;
-- 시노님 생성
create public synonym emp for employees;
-- 시노님 확인

-- 시노님 제거
drop public synonym emp;

------------------------------------------
-- 사용자 관리
------------------------------------------
-- 사용자 생성
create user hong identified by pass;
-- 권한 부여
-- 접속, 자원사용 role(권한의 모음)을 hong에게 부여
grant connect,resource to hong;
-- dba롤 부여
grant dba to hong;
-- 전체사용자에게 권한 부여
grant connect,resource,dba to public;
-- 권한 제거
revoke dba from hong;
-- with admin option : 부여받은 권한을 부여할 수 있는 옵션
-- sys
grant create session to scott with admin option;
-- scott
grant create session to hong;
-- 객체 권한
rename employees to emp;
grant select,insert,update,delete on emp to hong;
revoke insert,update,delete on emp from hong;
-- role(롤)
create role conn;

---------------------------------------
-- join
---------------------------------------

-- 1. cross join (cartesion product)
-- 두 테이블에서 가능한 모든 행의 조합을 생성
select count(*) from emp;
select count(*) from dept;
select empno, ename
from emp cross join dept;

-- 2. inner join (natural join, equi join)
-- cross join의 결과 중 조인조건에 맞는 행들을 추출
select e.empno, e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;

-- 3. outter join
-- cross join의 결과 중 조인조건에 맞는 행들을 추출하고
-- 조인조건에 맞지 않는 행들도 추출
create table t1 (no number,name char(3));
create table t2 (no number,name char(3));
insert into t1 values (10,'AAA'); insert into t1 values (20,'BBB');
insert into t2 values (10,'CCC'); insert into t2 values (30,'DDD');
select * from t1; select * from t2;
-- left outer join
select t1.no, t1.name, t2.no, t2.name
from t1 left outer join t2
on t1.no = t2.no;
-- right outer join
select t1.no, t1.name, t2.no, t2.name
from t1 right outer join t2
on t1.no = t2.no;
-- full outer join
select t1.no, t1.name, t2.no, t2.name
from t1 full outer join t2
on t1.no = t2.no;

-- subquery
-- =, >, < 등의 비교연산일 경우는 서브쿼리의 결과가 한 개 나와야 함
select * from emp where job = (select job from emp where empno=7900);
-- 서브쿼리의 where절에는 그룹함수를 사용할 수 없습니다.
--select ename, sal
--from emp
--where sal = (select sal from emp where sal>avg(sal));

select e.ename,e.sal,e.deptno,d.dname
from emp e, dept d
where e.sal in (select max(sal) from emp group by deptno)
and e.deptno = d.deptno;

-- 직업이 MANAGER인 사람들과 같은 봉급을 받는 사원
select empno, ename, job, sal from emp
where sal = any (select sal from emp where job='MANAGER');

-- 직업이 MANAGER인 사람들의 최소봉급보다 작은 봉급을 받는 사원
select empno, ename, job, sal from emp
where sal < all (select sal from emp where job='MANAGER');

-- 서브쿼리의 결과가 존재하는지
select dname, deptno from dept
where exists (select * from emp where emp.deptno=10);

20090217-lsj403.sql

20090217_home-lsj403.sql

-----------------------------
-- hr
-----------------------------

-- 테이블 생성
create table temp_emp (
id number not null,
name varchar2(12),
primary key (id)
);

-- 테이블 검색
select * from tab where tname='TEMP_EMP';

-- 테이블 구조보기
desc temp_emp;

-- commit/rollback
insert into temp_emp(id, name) values(1,'홍길동');
select name from temp_emp where id=1;
commit;
update temp_emp set name='홍인영' where id=1;
insert into temp_emp(id, name) values(2,'홍판서');
select * from temp_emp;
rollback;
commit;
select name from temp_emp where id=1;
delete temp_emp;
rollback;
select name from temp_emp where id=1;

select * from temp_emp;
update temp_emp set name='홍만석' where id=1;
savepoint a;
insert into temp_emp(id,name) values(3,'강감찬');
savepoint b;
delete from temp_emp;
rollback to savepoint a;

----------------------------
-- sys
----------------------------
-- 예약어
select keyword from v$reserved_words order by keyword;

----------------------------
-- hr
----------------------------
select * from departments;

----------------------------
-- scott
----------------------------

-- *** select

-- scott의 모든 테이블
select * from tab;
-- emp테이블의 모든 컬럼 조회
select * from emp;
-- emp테이블의 구조
desc emp;
-- emp테이블의 empno, ename 컬럼 조회
select empno, ename from emp;
-- emp테이블에 있는 job의 종류 조회
select distinct job from emp;
select job from emp;
-- emp테이블에 있는 모든 job
select all job from emp;
-- sal에 대해 정렬
select * from emp order by sal asc;
select * from emp order by sal;    
select * from emp order by sal desc;
-- 정렬시 null인 값은 마지막에
select * from emp order by comm;
-- 2차 정렬 (1차정렬이 완료된 후 2차정렬함)
select deptno, ename from emp order by deptno, ename;
-- alias (별칭)
select empno, sal 봉급, sal*0.1 보너스, sal+sal*0.1 총수령액 from emp;
-- emp테이블에서 deptno가 10
select empno, ename, deptno from emp
where deptno=10;
-- 현재 오라클이 인식하는 날짜 형식 알아내기
select value from nls_session_parameters
where parameter='NLS_DATE_FORMAT';
-- 사원들의 입사일
select hiredate from emp;
-- 1983년 이전에 입사한 사원들의 데이터
select * from emp where hiredate<'83/01/01';
-- 조건연산
select * from emp where deptno=10 and sal<3000;
-- not in 연산 (job이 SALESMAN이 아닌 데이터)
select * from emp where job not in 'SALESMAN';
-- in 연산 (job이 CLERK, MANAGER인 데이터)
select * from emp where job in ('CLERK','MANAGER');
select * from emp where job='CLERK' or job='MANAGER';
-- like 연산
-- ename에 M이 포함된 모든 데이터
select * from emp where ename like '%M%';
-- ename이 M으로 시작하는 모든 데이터
select * from emp where ename like 'M%';
-- ename이 M으로 끝나는 모든 데이터
select * from emp where ename like '%M';
-- 한글자는 _ (_자체를 표시하려면 \_)
select * from emp where ename like '_____';
-- between a and b (a, b를 포함해서 그 사이)
select empno, deptno from emp where deptno between 10 and 20;
select empno, deptno from emp where deptno>=10 and deptno<=20;
select * from emp where ename between 'A' AND 'K';
select * from emp where hiredate between '81/01/01' and '83/05/01';
-- null : 데이터 없음
select * from emp where comm is null;
select * from emp where comm is not null;
-- (X) select * from emp where comm=null;
-- 문자열 접합
select ename||'의 1년 연봉은'||sal||' 입니다!' as 연봉 from emp;
-- union (합집합)
select empno, ename from emp
union
select deptno, dname from dept;
-- union all (합집합 + 교집합)
select empno, ename from emp
union all
select deptno, dname from dept;
-- intersect (교집합)
select empno, ename from emp
intersect
select deptno, dname from dept;
-- minus (차집합)
select empno, ename from emp
minus
select deptno, dname from dept;
select deptno, dname from dept
minus
select empno, ename from emp;
-- projection : 열(컬럼) 추출
-- selection : 행(로) 추출

-- *** DML (Data Manipulation Language) - insert, update, delete
create table emp_dml(
sid number primary key,
name varchar2(12)
);
create table emp_dml2(
sid number primary key,
name varchar2(12)
);
-- insert
insert into emp_dml values (1111,'홍길동');
select * from emp_dml;
insert into emp_dml(sid, name) values(2222,'이순신');
-- null허용컬럼은 insert하지 않으면 null값 insert됨
insert into emp_dml(sid) values(3333);
-- subquery를 이용한 데이터 insert
insert into emp_dml2(sid, name) values(4444,'장화');
insert into emp_dml2(sid, name) values(5555,'홍련');
insert into emp_dml select * from emp_dml2;
-- update
update emp_dml set name='이순삼' where sid=2222;
-- subquery를 이용한 update
update emp_dml set name='박순삼'
where sid=(select sid from emp_dml2 where name='장화');
-- delete
delete from emp_dml where sid=1111;
delete from emp_dml where sid=(select sid from emp_dml2 where name='장화');
commit;

20090216-lsj403.sql

20090216_home-lsj403.sql