flush privileges;
'Database > Mysql' 카테고리의 다른 글
MySQL Start, Stop (0) | 2010.10.29 |
---|---|
[MySQL] java 에서 사용시 한글 깨짐현상 해결하기 (0) | 2010.06.21 |
between (0) | 2009.02.11 |
UNIQUE KEY (0) | 2009.02.11 |
트리거 간단하게 구현한것. (0) | 2009.02.11 |
MySQL Start, Stop (0) | 2010.10.29 |
---|---|
[MySQL] java 에서 사용시 한글 깨짐현상 해결하기 (0) | 2010.06.21 |
between (0) | 2009.02.11 |
UNIQUE KEY (0) | 2009.02.11 |
트리거 간단하게 구현한것. (0) | 2009.02.11 |
SELECT GREATEST(4, CEIL(NUM_ROWS/
((ROUND(((1958-(INI_TRANS*23))*
((100-PCT_FREE)/100))/AVG_ROW_LEN)))) * 2048)
TABLESIZE_KBYTES
FROM USER_TABLES
WHERE TABLE_NAME = UPPER('&1');
테이블이 사용중인 블록 크기를 계산해주는 SQL
/*--------------------------------------------------------------------------*/
/* TABLE이 사용하는 블럭 크기를 구하는 스크립트...
/* 사용법 : 1) DBA 권한으로 로그인한다.
/* 2) SQL> @TAB_BLOCK [TABLE명]
/* NOTICE : SUM(BLOCKS)는 사용하는 블럭의 갯수이며 사이즈는
/* DB_BLOCK_SIZE를 곱하여 얻을 수 있다.
/*--------------------------------------------------------------------------*/
SELECT OWNER, TABLESPACE_NAME, SEGMENT_NAME, SUM(BLOCKS)
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = UPPER('&1')
GROUP BY OWNER, TABLESPACE_NAME, SEGMENT_NAME
/
/* 테이블스페이스별 디스크 사용량 보기 */
SELECT SUBSTRB(A.TABLESPACE_NAME, 1,16) as TABLESPACE,
TO_CHAR(A.TOTAL, '999,999,999,990') as "총량(바이트)",
TO_CHAR(B.FREE, '999,999,999,990') as "남은량(바이트)",
TO_CHAR(A.BLOCKS, '9,999,990') as "총블럭",
TO_CHAR(C.BLOCKS, '9,999,990') as "사용블럭",
TO_CHAR(100*NVL(C.BLOCKS,0)/A.BLOCKS, '999.99') as "사용율%"
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) as TOTAL,
SUM(BLOCKS) as BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) A,
(SELECT TABLESPACE_NAME,
SUM(BYTES) as FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) B,
(SELECT TABLESPACE_NAME,
SUM(BLOCKS) as BLOCKS
FROM DBA_EXTENTS
GROUP BY TABLESPACE_NAME
) C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
ORDER BY A.TABLESPACE_NAME ;
SELECT SUBSTRB(TABLESPACE_NAME,1,20) AS "테이블스페이스",
SUBSTRB(FILE_NAME, 1, 26) AS "파일명",
TO_CHAR(BLOCKS,'999,999,990') as " 블럭수",
TO_CHAR(BYTES/1024/1024,'99,999,999') as " 크기(MB)"/*,
decode(STATUS, 'AVAILABLE', '사용', '중지') as "상태"*/
FROM SYS.DBA_DATA_FILES
WHERE TABLESPACE_NAME like 'REC_%'
ORDER BY TABLESPACE_NAME, FILE_NAME ;
오라클 클라이언트 설치후 리스너 등록 (0) | 2009.04.09 |
---|---|
사용자 추가 (2) | 2009.04.09 |
merge into = mysql 의 replace 와 비슷한 기능 (0) | 2009.02.11 |
sum over 와 rollup 을 이용한 통계, 누적 쿼리 (0) | 2009.02.11 |
누적 쿼리 (0) | 2009.02.11 |
사용자 추가 (2) | 2009.04.09 |
---|---|
테이블 사이즈 구하기 (0) | 2009.02.11 |
sum over 와 rollup 을 이용한 통계, 누적 쿼리 (0) | 2009.02.11 |
누적 쿼리 (0) | 2009.02.11 |
Date 관련 query (1) | 2009.02.11 |
cp_id | datetime |
61 | 2008-12-03 |
62 | 2008-11-03 |
62 | 2008-12-03 |
62 | 2008-12-03 |
63 | 2008-12-03 |
63 | 2008-12-03 |
cp_id | datetime | cnt |
61 | 200812 | 1 |
61 | Sum | 1 |
62 | 200811 | 1 |
62 | 200812 | 2 |
62 | Sum | 3 |
63 | 200812 | 2 |
63 | Sum | 2 |
Total | Sum | 6 |
cp_id | datetime | cnt |
61 | 200812 | 1 |
62 | 200811 | 1 |
62 | 200812 | 3 |
63 | 200812 | 2 |
테이블 사이즈 구하기 (0) | 2009.02.11 |
---|---|
merge into = mysql 의 replace 와 비슷한 기능 (0) | 2009.02.11 |
누적 쿼리 (0) | 2009.02.11 |
Date 관련 query (1) | 2009.02.11 |
날짜 계산 - 날짜 차이 (0) | 2009.02.11 |
merge into = mysql 의 replace 와 비슷한 기능 (0) | 2009.02.11 |
---|---|
sum over 와 rollup 을 이용한 통계, 누적 쿼리 (0) | 2009.02.11 |
Date 관련 query (1) | 2009.02.11 |
날짜 계산 - 날짜 차이 (0) | 2009.02.11 |
Date 포멧.. (0) | 2009.02.11 |
2.1 날짜를 다양하기 표시하기
- 날짜를 yyyy/mm/dd 형태로 표시하기
SELECT TO_CHAR(to_date('92-FEB-16','YY-MON-DD'), 'yyyy/mm/dd') FROM dual;
2.2 날짜에 대한 반올림/반내림 값 구하기
- 날짜 중에 일(dd)에 대한 반올림
SELECT ROUND(to_date('92-FEB-16','YY-MON-DD'), 'DAY') FROM dual;
- 날짜 중에 월(mm)에 대한 반올림
SELECT ROUND (to_date('92-FEB-16','YY-MON-DD'), 'MONTH') FROM dual;
- 날짜 중에 년(yy)에 대한 반올림
SELECT ROUND (to_date('92-FEB-16','YY-MON-DD'), 'YEAR') FROM dual;
- 날짜 중에 일(dd)에 대한 반내림
SELECT TRUNC(to_date('92-FEB-16','YY-MON-DD'), 'DAY') FROM dual;
- 날짜 중에 월(mm)에 대한 반내림
SELECT TRUNC (to_date('92-FEB-16','YY-MON-DD'), 'MONTH') FROM dual;
- 날짜 중에 년(yy)에 대한 반내림
SELECT TRUNC (to_date('92-FEB-16','YY-MON-DD'), 'YEAR') FROM dual;
2.3 날짜에 대한 덧셈 연산하기
- 날짜 중에 일(dd)를 더하기 위해서
SELECT to_date(’92-JUN-27’, ‘YY-MON-DD’) + 1 FROM dual
- 날짜 중에 월(mm)을 더하기 위해서
SELECT ADD_MONTHS(to_date(’92-JUN-27’, ‘YY-MON-DD’),1) FROM dual;
- 날짜 중에 년(yy)을 더하기 위해서
SELECT ADD_MONTHS(to_date(’92-JUN-27’, ‘YY-MON-DD’),12) FROM dual;
- 주어진 날짜로부터 다음 번에 나오는 월요일을 구하기 위해서
SELECT next_day (to_date(’92-JUN-27’, ‘YY-MON-DD’) , ‘Monday’) FROM dual;
2.4 날짜에 대한 뺄셈 연산하기
- 날짜 중에 일(dd)를 빼기 위해서
SELECT to_date(’92-JUN-27’, ‘YY-MON-DD’) - 1 FROM dual
- 날짜 중에 월(mm)을 빼기 위해서
SELECT ADD_MONTHS(to_date(’92-JUN-27’, ‘YY-MON-DD’),-1) FROM dual;
- 날짜 중에 년(yy)을 빼기 위해서
SELECT ADD_MONTHS(to_date(’92-JUN-27’, ‘YY-MON-DD’),-12) FROM dual;
- 두 날짜 사이의 일 수 계산 하기
SELECT to_date(’92-JUN-29’, ‘YY-MON-DD’) - to_date(’92-JUN-27’, ‘YY-MON-DD’) FROM dual;
- 두 날짜 사이의 월 수 계산 하기
SELECT MONTHS_BETWEEN(to_date(’92-JUN-29’, ‘YY-MON-DD’), to_date(’92-JUN-27’, ‘YY-MON-DD’)) FROM dual;
- 두 날짜 사이의 년 수 계산 하기
SELECT days(date('92-JUN-26'), date(’92-OCT-29’)) / 365.254
SELECT (to_date(’92-JUN-29’, ‘YY-MON-DD’) – to_date(’92-JUN-27’, ‘YY-MON-DD’) ) /365.254 FROM dual;
2.5 날짜에 대한 요일/월 이름 구하기
- 주어진 날짜에서 요일 구하기
SELECT TO_CHAR(to_date('92-06-29','YY-MM-DD'), 'DAY') FROM dual;
SELECT TO_CHAR (to_date('92-06-29','YY-MM-DD'), 'DY') FROM dual;
- 주어진 날짜에서 월 이름 구하기
SELECT TO_CHAR(to_date('92-06-29','YY-MM-DD'), 'MONTH') FROM dual;
SELECT TO_CHAR(to_date('92-06-29','YY-MM-DD'), 'MON') FROM dual;
2.6 날짜에 대한 일/월 구하기
- 주어진 날짜에서 일 구하기
SELECT TO_NUMBER(to_char(to_date('92-06-29','YY-MM-DD'), 'DD')) FROM dual;
- 주어진 날짜에서 요일을 수로 구하기
SELECT TO_NUMBER(to_char(to_date('92-06-29','YY-MM-DD'), 'D')) FROM dual;
- 주어진 날짜에서 월 구하기
SELECT TO_NUMBER(to_char(to_date('92-06-29','YY-MM-DD'), 'MM')) FROM dual;
2.7 현재 일자 구하기
SELECT SYSDATE FROM dual;
SELECT CURRENT_DATE FROM dual;
2.8 해당 월의 마지막 날짜 구하기
SELECT LAST_DAY(sysdate) FROM dual;
2.9 NEW_TIME
NEW_TIME ( date-expression, timezone, new timezone )
sum over 와 rollup 을 이용한 통계, 누적 쿼리 (0) | 2009.02.11 |
---|---|
누적 쿼리 (0) | 2009.02.11 |
날짜 계산 - 날짜 차이 (0) | 2009.02.11 |
Date 포멧.. (0) | 2009.02.11 |
트리거 예제 (0) | 2009.02.11 |
누적 쿼리 (0) | 2009.02.11 |
---|---|
Date 관련 query (1) | 2009.02.11 |
Date 포멧.. (0) | 2009.02.11 |
트리거 예제 (0) | 2009.02.11 |
계층적 쿼리 connect by (0) | 2009.02.11 |
Date 관련 query (1) | 2009.02.11 |
---|---|
날짜 계산 - 날짜 차이 (0) | 2009.02.11 |
트리거 예제 (0) | 2009.02.11 |
계층적 쿼리 connect by (0) | 2009.02.11 |
group by (0) | 2009.02.11 |
========
오라클
========
!! 트리거,트리거백업,트리거업뎃,after,before
create table tr1(name varchar2(10), age number(3));
create table test1(name varchar2(10), age number(3));
create table test(name varchar2(10), age number(3), tel varchar2(16));
==============================================
트리거 연습
create trigger t1_trigger
after insert on test
begin
insert into tr1 values('park',25);
end;
/
insert into test values('hong',25,'1234-44');
select * from tr1;
insert into test values('nam',25,'1334-44');
select * from tr1;
drop trigger t1_trigger;
===================================================
지우면 아래 값넣기
create trigger tr2_trigger
after delete on test1
for each row
begin
insert into tr1 values('행트리거',10);
end;
/
insert into test1 values('11',22);
delete test1;
하면
insert into tr1 values('행트리거',10);가 실행된다
====================================
지우기전에 빽업
create trigger tr3_trigger
after delete on test1
for each row
begin
insert into tr1 values(:old.name,:old.age);
end;
/
insert into test1 values('qqqq',31);
delete test1;
select * from tr1;
=====================================
사원 테이블에서 2000보다 많은 봉급을 받는 여사원들의 봉급이
update 되면 update_tab3 table 에('sawon',1,'female') data 를 insert (행 trigger)
create table update_tab3(name varchar2(10), su number(3), tel varchar(10));
create trigger ex_tri5
after update of sapay on sawon for each row
when(old.sapay>2000 and old.sasex='여자')
begin
insert into update_tab3 values('sawon',1,'femail');
end;
/
select * from sawon;
update sawon set sapay=sapay*0.9 where sasex='여자';
select * from update_tab3;
select * from sawon;
===========================================================
사원 테이블의 data 가 update 될때 이전에 있던 데이터를 savesawon 테이블에 insert
create table savesawon(name, job,pay,bouns)
as select saname, sajob, sapay,sapay*0.7 from sawon;
create trigger up_tri
before update on sawon for each row
begin
insert into savesawon values
(:old.saname,:old.sajob,:old.sapay,:old.sapay*0.7);
end;
select * from sawon;
update sawon set sapay=sapay*1.1;
select * from savesawon;
select * from sawon;
===========================================
sawon table data 가 update 될때 새로운 data 를 newsawon table 에 insert
create table newsawon(name,job,pay,bouns)
as select saname,sajob,sapay,sapay*0.7
from sawon;
create or replace trigger up_tri2
after update on sawon for each row
begin
insert into newsawon values
(:new.saname,:new.sajob,:new.sapay,:new.sapay*0.7);
end;
/
select * from sawon;
update sawon set sapay=sapay*0.9;
select * from newsawon;
===================================================================
if book_loan(책대출 테이블)에 data insert 시 같은 책의 id를 가진책을
book_loan tale(책정보)에서 loan column을 Y 대출중 표시 로 update
create table book_info
(id number primary key, bookname varchar2(10),loan char(2));
create table book_loan
(id number, anem varchar2(10),
constraints book_fk foreign key(id)
references book_info(id));
insert into book_info values(1,'computer','n');
insert into book_info values(2,'economy','n');
insert into book_info values(3,'science','n');
create trigger book_tri
after insert on book_loan for each row
begin
update book_info set loan='y'
where book_info.id = :new.id;
end;
/
insert into book_loan values(1,'kim');
select * from book_info;
날짜 계산 - 날짜 차이 (0) | 2009.02.11 |
---|---|
Date 포멧.. (0) | 2009.02.11 |
계층적 쿼리 connect by (0) | 2009.02.11 |
group by (0) | 2009.02.11 |
오라클 페이징 (서브쿼리) (0) | 2009.02.11 |
Connect by 계층적 쿼리는 오라클만이 가진 기능 중 하나로, 데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.
예를 들면, 아래와 같이 직원 테이블이 있다고 생각 하자.
직원 직속상사 직급
--------------------
철수 순희 대리
순희 영희 과장
길동 순희 대리
영희 개똥 부장
개똥 사장
기본적인 SQl을 사용하여 계층 관계를 표현하는것은 불가능하다. 하지만 재귀 PL/SQL 루틴과 connect by 를 사용한다면 표현이 가능하다.
재귀 PL/SQL은개발과 처리 과정에서 다소 많은 시간이 필요로 한다는 단점이 있으며, 변경사항이 있을 때 다른 저장 프로시저를 만들거나 보다 복잡하게 변경해야한다는 점도 무시 할수 없다.
이에 오라클에서는 connect by라는 확장된 select 구문을 지원한다.
기본형식
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원
직원 직급
-------------
개똥 사장
영희 부장
순희 과장
철수 대리
길동 대리
start with
select 구문의 start with 절은 계층 구조가 어떤 행에서 시작하는지 지정하는 기능을 한다.
정의 : start with <조건>
where 절의 내용으로 쓸 수 있는 조건이라면 start with로도 사용이 가능하며, 하나 이상의 조건을 결함하는 것도 가능하다.
ex) start with 직원 ='개똥'and 직원 ='순희'
start with 적의 조건에 맞는 행은 결과셋의 루트 노드가 된다. 주의할점은 조건에 맞는 행이 한 번 이상 등장할 경우이다.
예를 들면 start with 직원 ='개똥'and 직원 ='순희' 사용하면 개똥 이 순희 하위에 있기 때문에 순희 트리가 두 번 만들어지게 된다.
(한번은 개똥의 하위에서, 그리고 한 번은 루트로서)
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 = '개똥' or 직원 ='순희'
connect by 직속상사 = prior 직원 직원 직급
-------------
순희 과장
철수 대리
길동 대리
개똥 사장
영희 부장
순희 과장
철수 대리
길동 대리
처음 쿼리의 예제에서 직원 ='개똥'이라는 조건을 사용했으며, 이는 회사의 가장 높은 사람을 의미하는 것으로 전체 직원에 대한 목록이 만들어 진다. 하지만 이러한 방법은 그다지 좋지 않다. 왜냐하면, 개똥이 테이블에서 빠져나간다면 새로운 쿼리를 작성하여 직속상사가 의 값이 NULL 인 직원으로 부터 루트 노드가 다시 시작되도록 해야할 것이다.
그러므로, 가능하면 보다 구체적인, 즉 결과셋의 양이 적은 조건을 사용하는 것이 바람직하다. 직원 테이블을 보면 개똥의 직속상사의 값이 NULL로 저장되어 있는데, 이는 개똥이라는 직원이 보고할 사람이 없음을, 즉 가장 최상의 간부임을 의미한다.
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직속상사 is null
connect by 직속상사 = prior 직원
직원 직급
-------------
개똥 사장
영희 부장
순희 과장
철수 대리
길동 대리
Connect by Prior
connect by 절은 각 행이 어떻게 연결되는지를 오라클에게 알려주는 역할을 한다. 즉 계층 구조 내에서 각 행의 관계를 설정하는 것이다.
현재 행과 다른 행은 Prior라는 키워드를 통해 구별된다. Prior는 상위 행을 참조하는 것으로, 우리의 예제에서는 다음과 같이 사용되었다.
connect by 직속상사 = prior 직원
이는 "방금 전 행의 직원 값이 현재 행의 직속상사 값인 모든 행을 찾아라"라는 의미이다.
쉽게 말하면, 방금전에 살펴본 직원이 현재 직원의 상사가 되는 방식으로 리턴하라는 것이다.
다음 예제 코드를 보면, prior 부분이 = 기호를 사이에 두고 반대편으로 건너갔는데, 결과는 다음과 같이 트리를 거슬러 내려가는 것이 아니라, 거슬러 올라가는 방식으로 리턴되었다.
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원
직원 직급
-------------
철수 대리
순희 과장
영희 부장
개똥 사장
이 쿼리에서는 철수가 루트 노드이며, 그의 상사가 오히려 아래에 표현되어 있다. 그 이유는 " 방금 전 행의 직속상사 값이 현재 행의 직원 값인 모든 행을 찾아라"라고 선언했기 때문이다. 이와 같이 prior 키워드를 등호의 반대편으로 넣어도 오류가 발생하지 않고, 전혀 다른 결과가 얻어짐을 알 수 있다.
prior 키워드는 또한 이전 행의 열을 참조하기 위해 다음과 같이 select 절 내에서 사용 될 수도 있다.
select lpad(' ',(level-1)*2,' ')||직원 직원, prior 직원 상사,직급
from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원
직원 상사 직급
-------------------
철수 대리
순희 철수 과장
영희 순희 부장
개똥 영희 사장
여기서는 직원과 직속상사의 이름을 동시에 선택하였는데, 사실 두 값은 같은 행에 존재하는 것이 아니기 때문에 평범한 방법으로는 이와 같은 결과를 얻을 수 없다. 그래서 예제에서는 두 행을 동시 접근하여 각각 값을 얻어낸 것이다.
Level
level은 오라클에서 실행되는 모든 쿼리 내에서 사용 가능한 가상-열로서, 트리 내에서 어떤 단계(level)에 있는지를 나타내는 정수값이다.
계층적인 쿼리가 아니라면 다음과 같이 모든 값이 0, 즉 같은 단계를 가질 것이다.
select 직원,level
from 직원
직원 level
-----------
철수 0
순희 0
길동 0
영희 0
개똥 0
한편, 계층적 쿼리에서는 level의 값을 통해 트리에서의 위치를 확인할 수 있다. 루트 노드의 level 값이 1이다.
select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level
from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사
직원 직급 level
-------------------
개똥 사장 1
영희 부장 2
순희 과장 3
철수 대리 4
길동 대리 4
트리를 한 단계씩 거슬러 내려갈 때마다 값이 1씩 증가함을 알 수 있다.
level은 여러 가지 면에서 아주 유용하다. 먼저, 다음과 같이 각 항목을 출력할 때 앞에 붙는 공백의 양을 조절하여 계층적인 형식을 한눈에 알아볼 수 있도록 하는 것이 가능하다.
select lpad(' ',(level-1)*2,' ')||직원 직원
또한, level 값이 3까지인 내용만을 출력하라. 등의 명령도 가능하다.
select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level
from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사 and level <=3
직원 직급 level
-------------------
개똥 사장 1
영희 부장 2
순희 과장 3
철수와 길동의 경우는 level 값이 4이기 때문에 출력되지 않았다.
level <=3 이라는 조건을 where 절이 아닌 connect by 절에 넣은 것에 주의해야한다. 어떤 곳에 넣어도 결과는 같지만, where 절에 넣으면 전체 트리를 구성한 후에 다시 선택하는 반면, connect by 절에 넣으면 이 조건을 사용해서 트리를 구성하기 때문에 보다 효과적이라고 할 수 있다
정렬은..ORDER SIBLINGS BY사용가능
Date 포멧.. (0) | 2009.02.11 |
---|---|
트리거 예제 (0) | 2009.02.11 |
group by (0) | 2009.02.11 |
오라클 페이징 (서브쿼리) (0) | 2009.02.11 |
오라클 원격 접속하기 (0) | 2009.02.11 |