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

배치파일 생성 후 윈도우 At명령어느 스케줄러마법사를 이용해 실행 빈도를 설정한다.

http://kmckmc.tistory.com/entry/window-At-%EB%AA%85%EB%A0%B9%EC%96%B4

배치파일

::인터넷연결

@echo off

echo Network Connect

net use \\아이피주소

::연도별 YYYY-MM 디렉토리 생성

echo Create Directory

md d:\XXXX\%date:~0,7%

md \\아이피주소\XXXX\%date:~0,7%

::mysqldump를 이용한 백업 

echo Running dump...

"c:\Program Files\MySQL\MySQL Server 5.5\bin\"mysqldump -u 계정명 -p비밀번호 --result-file="d:\XXXX\%date:~0,7%\backup_%date%.sql" 스키마이름

::파일시스템에 백업 z:\는 아이피주소에 할당된 드라이브 이름

echo Copy to File System

copy d:\XXXX\%date:~0,7%\backup_%date%.sql z:\XXXX\%date:~0,7%\backup_%date%.sql

echo Done!

오라클의 Rownum = 1개념

limit 1 Order By A

SELECT

R.RESULT_SEQ

 , R.EMP_NO

 , R.DEPT_NO

 , R.PJT_CD

 , R.PJT_TYPE

 , P.PJT_NM

 , R.PJT_PART

     , (SELECT DATE_FORMAT(START_DT, '%Y-%m-%d')  

FROM

RH_RESULT

WHERE

EMP_NO = 2015103

AND PJT_CD = R.PJT_CD

AND USE_YN = 'Y'

AND PJT_TYPE <> '휴일/휴가' 

     ORDER BY START_DT

                limit 1) AS START_DT

              , (SELECT DATE_FORMAT(END_DT, '%Y-%m-%d')  

FROM

RH_RESULT

WHERE

EMP_NO = 2015103

AND PJT_CD = R.PJT_CD

AND USE_YN = 'Y'

AND PJT_TYPE <> '휴일/휴가' 

     ORDER BY START_DT DESC

                limit 1

              ) AS END_DT  

 , R.RATE_CD

 , R.CREATE_USER

 , R.CREATE_DATE

 , R.CREATE_IP

 , R.UPDATE_USER

 , R.UPDATE_DATE

 , R.UPDATE_IP  

 , R.MEMO_INFO

 , R.USE_YN

FROM

RH_RESULT R,

RH_PROJECT P

WHERE

R.EMP_NO = 2015103

AND R.PJT_CD = P.PJT_CD

AND R.USE_YN = 'Y'

AND P.USE_YN = 'Y'

AND R.PJT_TYPE <> '휴일/휴가' 

Group BY R.PJT_CD

ORDER BY R.START_DT

<1> 오라클 계정 생성

1. SQL Plus에 접속
   

   C:>sqlplus system/비밀번호 (또는 C:>sqlplus "/as sysdba")
   일반계정으로 연결되어 있다면 SQL>conn /as sysdba
   
2. 계정 생성 : create user 유저네임 identified by 패스워드;

3. 접속권한부여 : grant resource, connect to 유저네임; 
                        resource, connect 는 일반적인것을 다 사용 할 수 있음. DDL, DML 사용 가능 
   
   dba 권한 : grant DBA to 유저네임
   

4. 작업 tablesapce 지정 : alter user 유저네임 default tablespace users;
5. 임시 tablespace 지정 : alter user 유저네임 temporary tablespace temp;

<2> 오라클 계정 삭제

1. SQL Plus에 접속
   C:>sqlplus system/비밀번호
   

2. SQL>drop user 유저네임 cascade;   사용자 삭제

   cascade 를 명시하면 유저네임과 관련된 모든 데이터베이스 스키마가 데이터 사전으로부터 삭제되고 모든 스키마 객체도 물리적으로 삭제 됨.

참고 : http://kjo1023.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%EA%B3%84%EC%A0%95-%EC%83%9D%EC%84%B1-%EB%B0%8F-%EC%82%AD%EC%A0%9C

C:\ProgramFiles\MySQL\MySQLServer5.5\bin>mysqladmin-uroot-p create basicjsp

•로컬호스트(localhost)에접근할수있는권한

grant select,insert,update,delete,create,drop,alter

on basicjsp.*to'jspid'@'localhost'

identified by 'jsppass';

•모든서버(%)에접근할수있는권한

grant select,insert,update,delete,create,drop,alter

on basicjsp.*to'jspid'@'%'

identified by 'jsppass';

MySQL 접속

cd\

cd C:\Program Files\MySQL\MySQL Server 5.5\bin

mysql -u root –p 입력

패스워드: 1234

종료는quit

create or replace procedure sp_employee

is

 cursor cur_emp is

      select first_name, salary

      from employees;

   fname varchar2(20);

   salary number(9,2);

begin

 open cur_emp(id);

 loop

    fetch cur_emp into fname, salary;

    dbms_output.put_line(fname|| '==>' || salary);

    exit when cur_emp%notfound;

 end loop;

 close cur_emp;

end;

/

create or replace procedure sp_employee2

(id in number)

is

 cursor cur_emp(id2 number) is 

      select first_name, salary

      from employees

      where department_id = id2;

   fname varchar2(20);

   salary number(9,2);

begin

 open cur_emp(id);

 loop

    fetch cur_emp into fname, salary;

    dbms_output.put_line(fname|| '==>' || salary);

    exit when cur_emp%notfound;

 end loop;

 close cur_emp;

end;

/

create or replace package pkg_emp

as

 procedure sp_employee;

 procedure sp_employee2(id in number);

end pkg_emp;

/

create or replace package body pkg_emp

as

 procedure sp_employee

 is

    cursor cur_emp is

    select first_name, salary from employees;

    fname varchar2(20);

    salary number(9,2);

 begin

    open cur_emp;

    loop

      fetch cur_emp into fname, salary;

      dbms_output.put_line(fname|| '==>' || salary);

      exit when cur_emp%notfound;

    end loop;

    close cur_emp;

 end sp_employee;

 procedure sp_employee2(id in number)

 is

    cursor cur_emp(id2 number) is 

    select first_name, salary from employees

    where department_id = id2;

    fname varchar2(20);

    salary number(9,2);

 begin

   open cur_emp(id);

   loop

    fetch cur_emp into fname, salary;

    dbms_output.put_line(fname|| '==>' || salary);

    exit when cur_emp%notfound;

    end loop;

 close cur_emp;

 end sp_employee2;

end pkg_emp;

/

create table constraint_emp

(eid char(3) constraint pkeid primary key,

 ename varchar2(20) constraint nename not null,

 eno char(14) constraint neno not null constraint ueno unique,

 email varchar2(25) constraint uemail unique,

 phone varchar2(12),

 hire_date date default sysdate,

 jid varchar2(10) constraint fkjid references jobs(job_id) on delete set null,

 salary number,

 bonus_pct number,

 marriage char(1) default 'n' constraint chk check (marriage in ('y','n')),

 mid number constraint fkmid references emp(empno) on delete set null,

 did number,

constraint fkdid foreign key(did) references dept(deptno) on delete cascade);

select * from user_constraints;

select * from user_cons_columns;

select a.table_name, a.constraint_name, a.constraint_type, 

       b.column_name 

from user_constraints a, user_cons_columns b

where a.owner = 'HR'

and a.table_name = 'EMPLOYEES'

and a.owner = b.owner

and a.constraint_name = b.constraint_name;

insert into constraint_emp (eid, ename, eno,email, hire_date, MARRIAGE)

values ('126', '김민철', '123456786','mckmckmc@kmckmckmc', '09/24/2014', 'y')

select * from constraint_emp

//phone의 길이가 5 이상 되게 제약조건

alter table constraint_emp

add constraint phone_chk

          CHECK ( length(phone) >= 5 )

//salary의 5000 이상 되게 제약조건

alter table constraint_emp

modify constraint salary_chk

          CHECK ( salary >= 5000 )

select * from user_constraints where table_name = 'constraint_emp'

alter table constraint_emp

drop constraint salary_chk

desc constraint_emp

select * from user_constraints;

select * from user_cons_columns;

========= 기본키, 외래키에 포함된 칼럼 나태내기 ============

select a.table_name, a.constraint_name, a.constraint_type, 

       b.column_name 

from user_constraints a, user_cons_columns b

where a.owner = 'HR'

and a.table_name = 'EMPLOYEES'

and a.owner = b.owner

and a.constraint_name = b.constraint_name;

select a.index_name, a.table_name,  b.column_name, b.column_position 

from user_indexes a, user_ind_columns b

where a.index_name = b.index_name;

select * from employees

where email like 'SK%'

create index idx_fullname

   on employees (first_name, last_name)

select  first_name, last_name

from employees

where first_name like '%'

and last_name like '%'

select  first_name, last_name

from employees

where first_name like 'Ellen'

and last_name like 'Abel'

//시퀀스

create table board (

 no number primary key,

 title varchar2(20),

 contents varchar2(50) )

drop sequence board_seq

create sequence board_seq

 start with 1

 increment by 1

insert into board

values (board_seq.nextval, '알림', '월요일 반드시 출근')

select * from board

select board_seq.nextval from dual

select board_seq.currval from dual

declare

  j number := 1;

  k number := 1;

begin

  dbms_output.put_line('------------for test-------------');

 for i in 1..10 loop

  dbms_output.put_line(i);

 end loop;

  dbms_output.put_line('------------loop test-------------');

 loop 

  dbms_output.put_line(j);

  exit when j>=10;

  j := j+1;

 end loop;

  dbms_output.put_line('------------while test-------------');

 while k <= 10 loop

  dbms_output.put_line(k);

  k := k+1;

 end loop;

end;

/

declare

 dno number;

begin

 select deptno

 into dno

 from emp

 where empno =  7844;

 if dno = 10 then

  dbms_output.put_line('내가 가고싶은 부서');

 elsif dno = 20 then

  dbms_output.put_line('가고싶지않아요');

 elsif dno = 30 then

  dbms_output.put_line('놀기');

 else

  dbms_output.put_line('부서배치해주세요');

 end if;

 exception

  when no_data_found then

  dbms_output.put_line('사원이 없어요');

      WHEN   TOO_MANY_ROWS   THEN  

        DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS에러 발생');

      WHEN   NO_DATA_FOUND   THEN  

        DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND에러 발생');

      WHEN OTHERS THEN 

        DBMS_OUTPUT.PUT_LINE('기타 에러 발생');

end;

/

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