미처서의미처서만든블로그

①TO_CHAR(number\date,['fmt']) : 숫자 또는 날짜값을 fmt 형식 모델을 사용하여 VARCHAR2 문다스트링으로 바꾸어 줍니다. 

②TO_NUMBER(char) : 숫자를 포함하고 있는 문자 스트링을 숫자로 전환합니다. 

③TO_DATE(char,['fmt']) : 날짜를 나타내는 문자스트링을 명시한 fmt에 따라 날짜값으로 바꾸어 줍니다. fmt를 생략시 형식은 DD-MON-YY가 됩니다.

NVL(column, null인경우 값)

NVL2(column, null이아닌경우, null인경우)

NVLIF(비교1, 비교2) : 동일하면 null, 아니면 첫번째표현식

CASE     WHEN 조건1 THEN 조건만족시 값1

         WHEN 조건2 THEN 조건만족시 값2

         WHEN 조건3 THEN 조건만족시 값3

         ...

         ELSE 0

END 결과컬럼명 

DECODE(column, 조건1, 값1, 

               조건2, 값2,

      조건3, 값3) 결과컬럼명 

rowid : 주소로서 row가 실제로 저장되어있는 공간, 테이블내의 고유주소

rownum : 출력되어지는 순서 

      - 실제칼럼이 아닌 가상의 칼럼

      - 영구적으로 할당된 숫자가 아니다. 

      - 조건절 처리후 sort,집계 수행 이전에 할당, 할당후 증가 

첫번째row를 만났을때 rownum=5 라는 조건을 건다면 false 

//union

select employee_id, first_name, salary, '직원'

from employees

union

select department_id, department_name, 0, '부서'

from departments

order by 4

//rollup

select department_id, job_id, sum(salary)

from employees

group by rollup (department_id, job_id)

order by 1,2

//cube

select department_id, job_id, sum(salary)

from employees

group by cube (department_id, job_id)

order by 1,2

CREATE TABLE ORDER_LIST (

ORDER_DATE CHAR(8) NOT NULL,

PRODUCT VARCHAR2(10) NOT NULL,

QTY NUMBER NOT NULL,

AMOUNT NUMBER NOT NULL);

CREATE TABLE SALES_PER_DATE (

SALE_DATE CHAR(8) NOT NULL,

PRODUCT VARCHAR2(10) NOT NULL,

QTY NUMBER NOT NULL,

 AMOUNT NUMBER NOT NULL);

CREATE OR REPLACE Trigger SUMMARY_SALES

AFTER INSERT

 ON ORDER_LIST

 FOR EACH ROW

DECLARE

 o_date ORDER_LIST.order_date%TYPE;

 o_prod ORDER_LIST.product%TYPE;

 BEGIN

  o_date := :NEW.order_date;

  o_prod := :NEW.product;

  UPDATE SALES_PER_DATE

  SET qty = qty + :NEW.qty,

  amount = amount + :NEW.amount

  WHERE sale_date = o_date

   AND product = o_prod;

 if SQL%NOTFOUND then

   INSERT INTO SALES_PER_DATE

   VALUES(o_date, o_prod, :NEW.qty, :NEW.amount);

 end if;

 END;

  /

INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 300000);

commit;

INSERT INTO ORDER_LIST VALUES('20120901','MULTIPACK',10,300000);

select~~

rollback;

desc departments //칼럼정보보기

select first_name, hire_date, salary, employees.department_id, department_name

from employees, departments

where employees.department_id = departments.department_id

select first_name, hire_date, salary, department_id, department_name

from employees join departments using (department_id)

select e.first_name, e.hire_date, e.salary, e.department_id, d.department_name

from employees e join departments d on e.department_id = d.department_id

select e.ename, e.hiredate, e.sal, s.grade, s.losal, s.hisal

from emp e, salgrade s

where e.sal between s.losal and s.hisal

select e.first_name, e.hire_date, e.salary, j.job_id, j.job_title, j.min_salary, j.max_salary

from employees e, jobs j

where e.job_id = j. job_id

select first_name, hire_date, department_name

from employees e full join departments d

select e.first_name, e.hire_date, d.department_name

from employees e left join departments d

on e.department_id = d.department_id

select e.first_name, j.job_title

from employees e right join jobs j

on e.job_id=j.job_id

select * from jobs

select e.first_name, e.hire_date, nvl(d.department_name, '부서없음')

from employees e left join departments d

on e.department_id = d.department_id

select e.first_name, e.hire_date, nvl2(d.department_name, d.department_name||'**', '부서없음')

from employees e left join departments d

on e.department_id = d.department_id

//natural 조인 자동으로해줌

select emp.ename, dept.dname

from dept natural join emp

select emp.ename, dept.dname

from dept, emp

where dept.deptno = emp.deptno

//Cross 조인 다 곱해서 해줌

select e.first_name, d.department_name

from employees e, departments d

select e.first_name, d.department_name

from employees e cross join departments d

//Insert

insert into emp

values (2, '미처','CLERK', 7782, sysdate, 5000, 150, 30)

//select 이용해서 insert

insert into emp(empno, ename)

select employee_id, substr(first_name,1,10)

from employees

where department_id = 110

//update

update emp

set sal=600,

    deptno = 30

where empno in (205, 206)

rollback;

update emp

set sal = ( select avg(sal) from emp)

 where empno =1

commit

//Delete

delete from emp

where empno = 1

commit

delete from emp

where empno in (select employee_id from employees)

commit

Oracle 

SQL> conn system/root

Connected.

SQL> alter user hr

      identified by hr

      account unlock;

SQL>conn hr/hr

select sysdate from dual

select * from dual

alter session set nls_date_format='DD-MON-YY';

select (sysdate - to_date('02/25/1989'))/365 from dual // 몇년 살앗니

select months_between(sysdate,to_date('02/25/1989'))/12 from dual //몇달 살앗니

select add_months(sysdate,3) from dual //3달 더하기

select next_day(sysdate, 'mon') from dual //다가오는 월요일

select last_day(sysdate) from dual // 이번달 마지막

select round(sysdate, 'year') from dual //년도 반올림 6월이후기 때문에 15년이됨

select 

   round(sysdate, 'YYYY') 

               "년-6월이후",

   round(sysdate, 'MM') 

               "월-16일이후",

   round(sysdate, 'DD') 

               "일-12이후",

   round(sysdate, 'day') 

               주-수요일이후,

   trunc(sysdate, 'YYYY') t1,

   trunc(sysdate, 'MM') t2,

   trunc(sysdate, 'DD') t3,

   trunc(sysdate, 'day') t4

 from dual;

cmd창

sqlplus hr/hr@127.0.0.1:1521/xe

세팅

set linesize 300

set pagesize 40