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

select initcap('student'), initcap(last_name), upper(last_name), lower(email) from employees

select 10+20 from dual

select sysdate from dual

select first_name || last_name||'님' "연결1", 

concat(concat(first_name,last_name),'님') "연결2", 

substr(first_name, 1,3) "앞글자3개", instr(first_name, 'a') "a위치", 

lpad(first_name, 10, '*') "왼쪽*채우기",  

rpad(first_name, 10, '*') "오른쪽*채우기",

ltrim(first_name, 's') "왼쪽s지우기", 

rtrim(first_name, 'n') "오른쪽n지우기",

length('민철') "이름길이", 

length('minchul') "이름길이",

lengthb('민철') "이름 byte길이 한글3", 

lengthb('minchul') "이름길이"

from employees

select round(123.547, 1) 반올림, trunc(123.17, 1) 절삭, mod(14, 9) "나머지", ceil(123.11122) "올림", floor(123.11111) "버림" from dual

포멧바꾸기

select first_name, hire_date, to_char(hire_date, 'yyyy/mm/dd') 일자, salary, to_char(salary, '99,999,999') sal,  department_id

 from employees

 where department_id = to_number('30')

 and hire_date = to_date('12/24/2005')

nvl(column, '')

nvl2(column, '')

 nullif(비교1, 비교2)

select first_name, hire_date, 

 nvl(to_char(department_id),'부서없음') nvl,

 nvl2(to_char(department_id),to_char(department_id),'부서없음') nvl2, 

 nullif('a','a') nulliftest,

 nullif('a','b') nulliftest,

 decode(department_id, 30, '30번이네요', 

                       90, salary*1.2, '아니네요') decodetest,

 (case when department_id=30 then salary*1.1

      when department_id=90 then salary*1.2  

      else salary

  end) casetest

from employees

select first_name, department_id, 

 decode(department_id/10, 9,  'A',

                          8,  'B',

                          7,  'C',

                          6,  'D', 'F') decodetest

 from employees

select first_name, department_id, 

 (case when department_id>=90 then 'A'

       when department_id>=80 then 'B'  

       when department_id>=70 then 'C'  

       when department_id>=60 then 'D'  

       else 'F'

  end) casetest

 from employees

①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;

package day6.thread;

class Parent{

}

class MyThread2 extends Parent implements Runnable{

//상속중일때 Thread 구현하는 Runnable 인터페이스

public void run() {

for (int i = 0; i < 10; i++) {

try {

Thread.sleep((int)(Math.random()*1000));

} catch (InterruptedException e) {

e.printStackTrace();

}

System.out.println("C를 출력합니다.");

}

}

}

class MyThread extends Thread{

String name;

MyThread(String name){

this.name = name;

}

public void run() {

for (int i = 0; i < 10; i++) {

try {

sleep((int)(Math.random()*1000));

} catch (InterruptedException e) {

e.printStackTrace();

}

System.out.println(name + "A를 출력합니다.");

}

}

}

public class ThreadTest {

public static void main(String[] args) {

MyThread a = new MyThread("민철");

MyThread b = new MyThread("철민");

a.start();

try {

a.join();

} catch (InterruptedException e1) {

e1.printStackTrace();

}

b.start();

//Runable 인터페이스 사용할 때 선언

MyThread2 r = new MyThread2();

Thread c = new Thread(r);

c.start();

for (int i = 0; i < 10; i++) {

try {

Thread.sleep((int)(Math.random()*1000));

} catch (InterruptedException e) {

e.printStackTrace();

}

System.out.println("B를 출력합니다.");

}

}

}

package day6.network;

import java.io.IOException;

import java.net.ServerSocket;

import java.net.Socket;

public class ServerChating {

public static void main(String[] args) {

ServerSocket server = null;

try {

server = new ServerSocket(9000);

Socket socket = server.accept();

Sender a = new Sender(socket);

Receiver b = new Receiver(socket);

a.start();

b.start();

} catch (IOException e) {

e.printStackTrace();

}

}

}

package day6.lab3;

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.FileReader;

import java.io.FileWriter;

import java.util.ArrayList;

import day6.lab2.FileController;

public class FileCalculator {

public static void main(String[] args) throws Exception {

new FileCalculator().addCalculateResult("src/day6/lab3/source.txt");

}

void addCalculateResult(String inputFileName) throws Exception{

FileReader fr = new FileReader(inputFileName); //파일읽기

BufferedReader br = new BufferedReader(fr); // 버퍼에 읽기

//다 읽고 반납할 수 있도록 구현해야됨 -> 저장공간 필요

FileWriter fw = new FileWriter(inputFileName, true);  //파일 쓰기, true=기존의 파일에 append함

BufferedWriter bw = new BufferedWriter(fw);      //버퍼로 쓰기

ArrayList<String> list = new ArrayList<String>(); // arraylist 객체 선언

String s = null; 

while((s = br.readLine()) != null){  //마지막이 아닐때까지 계속 돌아라

list.add(s); //list에 s의 내용을 넣는다

}

br.close();

fr.close();  //br, sr 닫음

int sum = 0;

int mul = 1;

for(String item : list){

sum += Integer.parseInt(item);

mul *= Integer.parseInt(item);

}

bw.newLine();

bw.write(sum+""); //문자열로 저장하기 위해 ""을 넣어 String 값으로 찍음

bw.newLine();

bw.write(mul+"");

bw.close();  //자원반납

fw.close();

}

}

/*void reverseLine(String inputFileName, String outputFileName) throws Exception

{

Collections.reverse(list); //내용 뒤집는 함수

for(String ss :list){

System.out.println(ss);

} // 뒤집어서 읽기

//뒤에서부터 읽기

for(int i = list.size()-1; i>=0; i--){

System.out.println(list.get(i));

bw.write(list.get(i));   //버퍼의 내용으로 파일에 쓰기

bw.newLine();            //줄바꾸기

}

}

public static void main(String[] args) throws Exception {

new FileController().reverseLine("src/day6/lab2/input.txt", "src/day6/lab2/output.txt"); //객체 선언과 메서드사용

}*/