2014/5/16 - 오라클 - Function, join, create, constraint

Character Function

substr(s,m,n) - 문자를 찾고 몇번째까지 가져온다, like랑 속도차이가 10배난다.

select * from emp where substr(ename,0,1)='S';

select * from emp where substr(ename,length(ename),1)='S';

replace(s,p,r) - 문자 위치 바꾸기

select replace(ename,'S','*') from emp; 

S자를 *로 바꾼다.

ascii(s) - 아스키코드값 으로 변환해준다.

select ascii('a') from emp;

select chr(79)||chr(114)||chr(97)||chr(99)||chr(108)||chr(101) from dual;  - 아스키코드값을 문자로

Oracle

select ascii('o'),ascii('r'),ascii('a') from dual;   -  문자를 아스키코드값

111  114   97

select substr('Oracle DBMS',2,4) from dual;  -  2번째문자에서 4개를 가져온다.

racl

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

Date Function

add_Months(d,n) - 오늘 날짜에 월을 더한다.

select add_months(sysdate,21) from dual;

last_day(d) - 이번달의 마지막날이 몇일인지 알수 있다.

select last_day(sysdate) from dual;

months_between(d1,d2) - 개월수를 구할수 있다.

select months_between(sysdate,'87/12/30') from dual;

round(d,fmt) - 날짜를 기준으로 반올림계산하여 가까운 년도 1월1일을 출력한다. YEAR,MONTH를 fmt자리에 넣어서 사용가능하다.

select round(sysdate,'YEAR') from dual;

to_date() - 문자를 날짜에 맞게 변환해준다.

round(to_date('87/12/30'),'YEAR')

select to_char(new_time(to_date('03:10:30','hh24:mi:ss'),'est','gmt'),'hh24:mi:ss') from dual;

08:10:30

Conversion Function

to_char(d,fmt)

RR Format

날짜형식 YY가 아닌  RR을 사용한다.

RR포맷은 99년을 입력하면 1999년을 찾고 YY는 2099년을 찾는다.

YY는 세기를 기준으로 찾고 RR은 가까운 쪽의 년도를 찾는다.

select to_char(to_date('80/01/01','yy/mm/dd'),'yyyy') from dual;

결과값 : 2080

select to_char(to_date('80/01/01','rr/mm/dd'),'yyyy') from dual;

결과값 : 1980

Misellaneous Function

greatest(expr,.....) - 가장큰값을 보여준다.

select greatest(10,20,30) from dual;

결과값 : 30

select greatest(nvl(comm,0),sal) from emp;

least(expr,...) - 가장작은값을 보여준다.

select least(10,20,30) from dual;

결과값 : 10

decode(expr,search,reslut,...,default)  -  선택적으로 다른 출력값으로 보여주고 싶을때

select decode(deptno,10,'10번 부서',20,'20번부서') from emp;

deptno의 값이 10이면 10번부서, 20이면 20번 부서로 출력

nvl2(expr1,expr2,expr3) - null값대신 다른값을 대체해준다.

select nvl2(comm,1,2) from emp;

comm이 널이면 1, 아니면 2가 출력된다.

예)select nvl2(mgr,'대표','사원') from emp;

Join

select * from emp, dept where emp.deptno= dept.deptno;

deptno로 연결된 테이블을 보여준다./

select emp.ename, dept.dname from emp, dept where emp.deptno= dept.deptno;

DALLAS에 살고 있는 사람의 정보를 보여줘라

select *

from dept d, emp e

where d.deptno=e.deptno and loc='DALLAS';

800만원을 받는 사람의 지역은 어디인가?

select d.loc

from dept d, emp e

where d.deptno=e.deptno and sal=800;

Equijoin

매칭이 되는 값만 보여준다.

훨씬 많이 쓴다.

select d.name, e.last_name from s_dept d, s_emp e where d.id=e.dept_id and d.id=31;

Non-equijoin

select * from emp e, salgrade s where e.sal between s.losal and s.hisal;

Outer join

매칭이 되는 값이 없으면 null로 표시되서 보여준다. 

일반적

select e.ename, d.dname

from emp e,dept d

where e.deptno = d.deptno;

select e.ename, d.loc

from emp e,dept d

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

(+) outer join  

하나의 select문 안에서 하나의 table에만 사용할 수 있을때 쓰인다.

(+)연산자는 오라클에서만 사용가능 한 연산자이며 조인 조건의 필요한 곳에 붙이도록 한다. 어느쪽에 붙이느냐에 따라 의미가 변하므로 null이 나올가능성이 있는 곳에 붙여야 한다.

왼쪽에 붙으면 left outer join이라고 부르고 오른쪽에 붙으면 rigth outer join이라고 부른다.

SELF Join

하나의 테이블 안에서 모든 작업이 일어난다.

select e1.ename,e2.ename,e1.mgr,e2.mgr

from emp e1,emp e2

where e1.mgr = e2.mgr;

같은 테이블에서 작업이 일어나기 때문에 alias를 써서 구분해줘야 한다.

Create Table - 테이블 생성

create table Student(

SNo char(10),

SName varchar2(20),

SBirthDay date

);

create table 테이블이름(

속성이름   속성타입[(크기)]

);

create table test(

name char(10),

JoinDate date default sysdate

)

insert into test values('홍길동','14/05/16');

insert into test(name) values('전지현');   

name값만 넣어도  JoinDate date default sysdate 때문에 날짜가 자동으로 들어간다.

CONSTRAINT

create table student(

sno char(10) constraint STU_SNO_PK primary key,   // 기본키

sname char(20) constraint STU_SNAME_NN not null,  //공백

shp char(11) constraint STU_SHP_UK unique   // 중복값

)

constraint를 정의할때 이름을 정해주지 않으면 oracle이 SYS-Cn의 형식으로 생성해준다.

user가 해당  constraint에 위배되는 명령을 요청했을때 , oracle은 constraint이름과 함께 error를 내보낸다.

값이 숫자 값이지만 속성타입을 char(10)로 할지 number로 할지는 

create table student(

sCode char(10) constraint STU_CODE_PK primary key,

sName char(20) constraint STU_NAME_NN not null

mCode number constraint STU_MCODE_FK references Major(mCode),

cCode number constraint STU_CCODE_FK references Circle(cCode) on delete set null

sScore char(1) constraint STU_SCORE_CK check(sSCore in('a','b','c','f'))

)

delete from major where name=1;

CHECK Constraint

설정해논 값중에서만 insert 할수 있다.

Create tables by Subquery

기존의 테이블을 통해서 새로운 테이블을 만든다.

create table의 column수와 subquery의 select_list 수가 같아야한다.

subquery에 있는 기존 table에서 새로 만들어지는 table로 not null제약조건만 상속된다.

create table NewEmp

as 

select *

from emp

where deptno =10;