[ORACLE]오라클종합

--nvl(comm,0) --> comm이 널이면 0으로 대체
--nvl2(comm, 수식1, 수식2) comm이 널이 아니면 수식1을, 널이면 수식2를 반환
--coalesce(수식1, 수식2, 수식3, ...) 수식1이 널이 아닐 경우에 반환하고 널이면 수식2를, 수식2도 널이면 수식3을.... 반환
--|| --> 두 컬럼을 한컬럼으로 출력 (문자/숫자 구분없이 결합함)
--distinct --> 중복열을 제거하여 오름정렬 후 출력
--chr(65) --> 아스키65값을 문자A로 표시
--ascii('A') --> 문자 A 를 아스키값 65로 표시]
 
alter session set nls_date_format=   -- sssss : 오늘 하루를 초로 변환하여 나타냄...
'yyyy-mm-dd hh:mi:ss sssss am';      -- hh : 12시간제, hh24 : 24시간제로 나타냄... (현재 세션에서만 적용됨)

select value from v$parameter where name='db_block_size'; --환경정보확인(블럭사이즈)

show parameter db_block_size; -- 기본환경정보 확인 (블럭사이즈)
 
***문자처리함수***
--upper(' ') --> 대문자로 변환
--lower(' ') --> 소문자로 변환
--initcap(' ') --> 첫 문자만 대문자로 변환, 나머지는 소문자로 변환
--to_char(sysdate, 'yyyy-mm-dd') --> 날짜를 포맷형식으로 출력하라...
--sysdate --> 현재 시간/날짜
--current_date --> sysdate와 같다...
--like에서 _는 글자 하나, %는 모든것..
--substr('abcde',3,2) --> 문자열중 3번째부터 2글자 출력 (한글도 1을 1글자로 인식)
--substr('abcde',3) --> 문자열 중에서 3번째부터 끝까지...
--concat('ab','cd') --> 두 문자열을 결합함...
--length('aa') 또는 langthb('aa') --> 문자열의 길이 또는 바이트 수 구하기
--instr('abcdabcd','c',1,2) --> 문자열에서 c가 첫문자부터 세어서 두번째 나오는 위치값 출력
--lpad('korea', 10, '*') --> 전체 10자리로 맞추고 빈칸은 왼쪽으로 *로 채운다.
--rpad('korea', 10, '-') --> 전체 10자리로 맞추고 빈칸은 오른쪽으로 -로 채운다.
--ltrim('aaaabaaba','a') --> 왼쪽에서 반복되는 문자열을 지워라... (최초로 반복되는 문자열까지만...) 지울 문자열을 생략하면 공백을 지운다.
--rtrim('aaaabaaba','a') --> 오른쪽에서 반복되는 문자열을 지워라... (최초로 반복되는 문자열까지만...) 지울 문자열을 생략하면 공백을 지운다.
--translate('hallp','ap','eo') --> a를 e로, p를 o로 변환하여 hello를 출력
--replace('orahome','ora','오라') --> ora를 오라로 변환 대치하여 출력...
--reverse('oracle') --> 거꾸로 출력함....
 
***숫자처리함수***
--floor(2.9) --> 실수에서 작은 정수값 취함
--ceil(2.9) ---> 실수에서 큰 정수값 취함
--mod(5, 2) ---> 5/2에서 나머지값을 취함 
--round(321.123, 2) --> 소수 3자리에서 반올림하여 2자리로 표시함
--truncate(12.34, 1) --> 소수 첫째자리만 나타냄(버림)
--power(2,3) --> 2의 3승 값
--sqrt(3) --> 루트3의 값

***날짜처리함수***
--months_between(sysdate, sysdate) --> 날짜 차이를 월단위로 구함...
--add_months(sysdate, 4) --> 4개월을 더한 날짜를 출력...
--next_day(sysdate,'월요일') --> 현재날짜로부터 가장 빠른 월요일 날짜를 구하기...
--last_day(sysdate) --> 현재달의 마지막 날을 구함...
--to_char(sysdate,'yyyy') --> 연도 네자리만 출력함 mm, dd, hh, mi, ss 등 가능함...
--to_date('2005-12-12', 'yyyy-mm-dd') 문자열을 날짜및 시간형식으로 변환
--extract(year from sysdate) --> sysdate에서 년도/월/일 만 추출함...
--extract(month from sysdate)
--extract(day from sysdate)
***to_char 변환옵션들...***
 q : 분기표시 1 2 3 4
 ww: 올해 1월1일부터 계산해서 몇주째인지..
 w : 현재 달에서 몇주째인지..
 d : 현재 몇요일인지 일(1), 월(2), ... , 토(7)
 dd: 현재 달에서 몇일인지...
ddd: 올해 1월1일부터 현재 몇일째인지..
day: 몇요일인지...
 dy: 몇요일인지.. 약자로..
mon:
month:
year:
 

***변환형함수***
----------------------------------------|
--     to_date <------ to_char          |
--날짜 <-------> 문자 <--------> 숫자 |
--     to_char -------> to_number       |
----------------------------------------|
--to_char(hiredate, 'fmyyyy*mmfm*dd') --> fm~fm: 숫자앞에0을 빼버림
--to_char(hiredate, 'year-mmsp-ddspth') --> 모두 영문으로 표기함..
--to_char(sal, 'L9,999') -->통화기호 및 컴마 표시
--to_dsinterval('007 14:30:10') --> 일 시:분:초 (sysdate와의 계산식에 사용)
--to_yminterval('01-03') --> 년-월 ( " )
 
***rownum***
결과에 로우컬럼을 추가하여 하나씩 증가시킴...

***case***
select case 3 when 5-2 then 'a'
        when 2-2 then 'b'
        else '몰라'
  end "case 연습"
from dual;

***case 부등호 조건 쓰기***
case when sal > 5000 then .....

***decode***
select decode(2, 5-2, '5-2',
        2-2, '2-2',
       '몰라')
  from dual;
***inline view*****
select T.*      -- select 문 자체를 뷰화 하여 쿼리함..
from (select * from emp) T;

*** ***
select deptno, job, sum(sal)
from emp
group by rollup(deptno, job)

--rank() over(order by sal desc) --> sal컬럼에 대한 석차구하기 (공동등수일때 다음 등수 건너뛰기)
--rank() over(partition by deptno order by sal desc) --> deptno에 따라서 sal컬럼에 대한 석차 구하기
--dense_rank() over(order by sal desc) --> 공동석차일 경우 다음 순위에 공동순위만큼 등수 건너뛰지 않음

***조인***
--> 조인시 from절에서 로우값이 많은 테이블을 먼저 쓸것.
--> from에서 가장 끝에 기술 된 테이블이 구동테이블로 잡히고 무조건 테이블 full-scan되어진다.
    (로우값이 작은 테이블을 구동시키고 로우가 큰 테이블을 참조시킴)
--> 한쪽만 인덱스가 있다면 from절에서 위치 상관 없이 인덱스 없는쪽이 자동으로 구동테이블이 된다.
(기본 블럭사이즈 = 8KB)
block -> extent -> segment(table/index) -> tablespace -> database -> dataware-house

--equi join
inner join과 같음.... 일반적인 쪼인....
 
--non equi join
select E.ename, E.sal, G.grade, G.losal, G.hisal
from emp E, salgrade G
where E.sal between G.losal and G.hisal;

--left outer join
select D.department_id, E.first_name, E.last_name, E.salary
from hr.employees E, hr.departments D
where E.department_id=D.department_id(+);

--right outer join
select D.department_id, E.first_name, E.last_name, E.salary
from hr.employees E right join hr.departments D
on E.department_id = D.department_id;

--full outer join
select D.department_id, E.first_name, E.last_name, E.salary
from hr.employees E full join hr.departments D
on E.department_id = D.department_id;

--self join
select A.empno, A.ename, A.job, A.mgr, B.ename, B.job
from emp A, emp B
where A.mgr = B.empno(+);

***union / intersect / minus***
--union : 두 쿼리문을 같은 컬럼으로 로우붙이기 하여 이어서 보여줌...
select * from jumunold
union
select * from jumun;
--차이점           ( union / union all )
--첫번째컬럼으로 자동 asc sort ( O / X )
--결합시 중복되는 로우는 제거  ( O / X )

--intersect : 교집합
--minus     : 차집합

*** rank (row number) ***

*** 쿼리출력을 새로운 테이블로...***
--oracle
create table jumunold
as select * from jumun

--ms sql
select * into jumunold
from jumun
 
*** exists & 상관서브쿼리 ***
in()을 사용하는것 보다 처리 속도가 빠름...

------------------------------------
------------------------------------
------------------------------------
select * from user_users --계정정보(디폴트테이블스페이스)
select * from user_tablespaces --테이블스페이스정보
select * from USER_SYS_PRIVS  --시스템 권한 부여 확인
select * from USER_TABLES  --해당 계정 테이블에 대한 정보 확인
select * from USER_ROLE_PRiVS --자기 롤 확인
select * from USER_TS_QUOTAS  --자기 테이블스페이스 쿼타 확인
select * from user_free_space --사용 할 수 있는 빈 공간 테이블 스페이스 크기
select * FROM user_SEGMENTS --자기 세그먼트들을 확인(테이블/인덱스)
SELECT * FROM USER_CONS_COLUMNS --제약조건들 목록을 확인
SELECT * FROM USER_INDEXES -- 자기 인덱스 확인하기....
SELECT * FROM USER_IND_COLUMNS -- 자기 인덱스 확인하기....
SELECT * from user_tab_columns --테이블 컬럼 구조 확인 ..
SELECT * FROM USER_CONSTRAINTS --제약조건 검색 
select * from user_tab_comments --현재 계정의 모든 테이블에 주석문을 보여준다...
select * from user_col_comments --컬럼 주석문 검색하기...
select * from role_sys_privs --현재 사용자에게 적용된 시스템 role 검색
select * from dba_roles --시스템 기본 role 검색하기....
select * from dba_data_files --sys계정이 database files 정보 확인하기
select * from dict_columns where table_name = 'DBA_DATA_FILES' --dba_data_files 테이블의 정보 검색
select * from user_catalog --자신의 소유의 존재하는 테이블 및 뷰 검색하기....
SELECT * FROM SYSTEM_PRIVILEGE_MAP --SYSTEM PRIVILEGE 종류 검색....
SELECT * FROM TABLE_PRIVILEGE_MAP --TABLE PRIVILEGE 종류 검색...
--오브젝트 검색할 수 있는 테이블 ---> user_tables < user_catalog = tab = cat < user_objects

--현재 사용중인 테이블스페이스 파일 공간 확인하기....
select a.tablespace_name,
    a.file_name,
    a.bytes,
    b."free byte",
    (a.bytes - b."free byte") "사용중인 공간"
from dba_data_files a, (select tablespace_name, sum(bytes) "free byte"
          from dba_free_space
      group by tablespace_name
      ) b
where a.tablespace_name = b.tablespace_name
---------------------
--제약조건 추가.....
---------------------
--제약조건 검색하기...
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='BUSEO'

--디폴트옵션도 조회하여 모두 복사함....
SELECT COLUMN_NAME, DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'BUSEO'

-- primary key 부여하기....
ALTER TABLE BUSEO
ADD CONSTRAINT BUSEO_BUNO_PK PRIMARY KEY(BUNO)

--참조키 해제하기...
ALTER TABLE SAWON  --테이블을 드롭하기 위하여 참조키를 해제함
DROP CONSTRAINT SAWON_DEPTNO_FK
 
-- NOT NULL 해제하기....
ALTER TABLE BUSEO
DROP CONSTRAINT SYS_C003051 --> constraint 이름
--또는
ALTER TABLE BUSEO
MODIFY ZIPCODE VARCHAR2(7) NULL
 

-- DEFAULT 부여하기....
ALTER TABLE BUSEO
MODIFY ZIPCODE DEFAULT ' '

-- NOT NULL 부여하기
ALTER TABLE BUSEO
MODIFY BUNO CONSTRAINT BUSEO_BUNO_NN NOT NULL

--컬럼 추가하기...
alter table sawon    --8i 이상에서는 이렇게 쉽게 가능함..
add email varchar2(30);
--또는
ALTER TABLE BUSEO  --컬럼 추가하기 ( NOT NULL 옵션 동시에 걸어주기.)....
ADD ZIPCODE VARCHAR2(7) DEFAULT ' ' CONSTRAINT BUSEO_ZIPCODE_NN NOT NULL

--컬럼 이름 변경하기....
alter table sawon
rename column addr to juso

--컬럼 삭제하기....
alter table sawon
drop (email)

--컬럼 수정하기....
alter table  high_tax
modify ( tax number(10,2)  ) ;


--컬럼 UNUSED 하기
ALTER TABLE SAWON SET UNUSED(EMAIL)
--또는
ALTER TABLE SAWON SET UNUSED COLUMN EMAIL

--UNUSED상태에 있는 컬럼명 조회
SELECT *
FROM USER_UNUSED_COL_TABS

--실제로 UNUSED 상태의 컬럼을 삭제하기.....
ALTER TABLE SAWON
DROP UNUSED COLUMNS

--테이블 이름 변경
RENAME BUSEOCP TO BUSEO --BUSEOCP --> BUSEO 이름 변경

--주석문 달기
comment on table sawon is '우리회사 사원들의 테이부르'
comment on column buseo.jitel is '지역전화번호'
select * from user_tab_comments
select * from user_col_comments

--dept 테이블을 다른 스페이스(users)로 옮기기
ALTER TABLE SCOTT.DEPT MOVE TABLESPACE USERS --하나하나 옮기기
-- 일반 사용자가 만든 세그먼트들 검색....
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE 
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'SYSTEM'
   AND OWNER IN ( SELECT USERNAME --USER_ID가 60이상이면 일반사용자임...
             FROM DBA_USERS
      WHERE USER_ID > 60 OR USERNAME = 'SCOTT' )
 
--------------------------------------

--권한부여의 종류와 특정 사용자에게 권한 부여하기
select * from table_privilege_map
grant select on sawon to scott

-----------------------------------------------------------
--사용자 계정 생성하기.....
--계정생성 순서
-> 계정생성 -> 세션(로긴)허용 -> 해당테이블스페이스 쿼터 할당 
-----------------------------------------------------------
CREATE USER ORAUSER2 IDENTIFIED BY TPDLF
DEFAULT TABLESPACE SALESTBS
TEMPORARY TABLESPACE TEMP
QUOTA 5M ON SALESTBS  --SALESTBS 테이블스페이스는 5MB 할당
QUOTA UNLIMITED ON TEMP  --임시 테이블스페이스는 모두 할당

GRANT CREATE SESSION TO ORAUSER2  --로긴 권한 부여
GRANT CREATE TABLE TO ORAUSER2  --테이블 생성 권한 부여

----------------------------------------------------------------------
--사용자 계정 삭제하기...(해당 계정소유의 오브젝트가 있다면 바로 삭제가 불가능함..)
----------------------------------------------------------------------
DROP USER ORAUSER1
DESC DBA_OBJECTS

SELECT *    --삭제할 계정소유의 오브젝트 확인하기...
FROM DBA_OBJECTS
WHERE OWNER='ORAUSER1'

DROP TABLE ORAUSER1.MYTAB --삭제할 계정소유의 테이블 먼저 삭제함...

--또는 삭제할 계정을 삭제할 때 그 소유의 오브젝트를 모두 지우고 삭제함..
DROP USER ORAUSER1 CASCADE

-----------------------------
--뷰생성 --view---
-----------------------------
create or replace view v_emp10 --새로 생성하거나 기존의 뷰를 재정의 할 때 쓰여짐.....
as
select empno, ename, sal, deptno
from emp
where deptno = 10

--create or replace trigger
--create or replace procedure

--truncate 는 DDL문이므로 rollback 이 불가함.... 
truncate table sawon --> 사원테이블의 모든 입력값을 지우고 high water mark 까지도 초기화함...
delete table_name -----> 이 역시 모든 입력값이 지워지지만 H.W.M 는 초기화되지 못한다.

** commit 은 DML문에서만 가능하고 그 이외의 명령문은 auto commit 이다.
** DML이외의 명령문이 샐행 되어지면 commit이 실행되므로 그 이전에 dml문까지도 commit이 실행되어져 rollback 불가함

------------------------------------------------------------------
**MS-SQL에서는 BEGIN TRAN <-----> ROLLBACK TRAN 사이에 넣은 문들은
  DML,DDL,DCL등 관계없이 모두 ROLLBACK 되어짐......
------------------------------------------------------------------

--암호재정의
alter user user_ identified by password_
 
 

-----------------------------
--external table 생성하기....
-----------------------------

--My-Sql 에서 데이타베이스 내용 추출하기...
C:\> bcp pubs.dbo.stores out c:\ext_data\stores.txt -c -t"," -r\n -Usa -P****** -Sclass-b108
 
--오라클에서 external table로 사용 할 디렉토리 정의
create or replace directory stores_dir AS 'c:\ext_data\'

--external table 생성
--------------------------------------------------------------------------------
create table scott.stores_ext     --external 테이블 정의
    ( stor_id varchar2(4)     --external 테이블 정의
    , stor_name varchar2(40)    --external 테이블 정의
    , stor_address varchar2(40)   --external 테이블 정의
    , city varchar2(20)     --external 테이블 정의
    , state varchar2(2)     --external 테이블 정의
    , zip varchar2(5)      --external 테이블 정의
    )    
organization external          --위에서 정의 한 테이블을 external로 정의
( type oracle_loader
 default directory stores_dir    --생성되는 external 테이블 디렉토리
 access parameters
         ( records delimited by newline --개행문자'\n'에 의한 레코드 구분 정의
          badfile 'stores_bad.bad' --데이타형식이 틀려서 입력 안된 로우가 기록되는 파일
          logfile 'stores_log.log' --기본 로그파일 정의
          fields terminated by ',' --컬럼 구분자를 정의
                   ( stor_id char  -- 컬럼 구조 정의
                   , stor_name char  -- 컬럼 구조 정의
                   , stor_address char -- 컬럼 구조 정의
                   , city char   -- 컬럼 구조 정의
                   , state char   -- 컬럼 구조 정의
                   , zip char)   -- 컬럼 구조 정의
               )
 location('stores.txt') --외부에서 추출한 데이타파일
 )
reject limit unlimited --bad파일 로우 제한 설정
---------------------------------------------------------------------------------
desc scott.stores_ext
select * from scott.stores_ext
SELECT * FROM DICT WHERE UPPER(COMMENTS) LIKE '%EXTERNAL%'
USER_EXTERNAL_TABLES
USER_EXTERNAL_LOCATIONS