PL/SQL 필기 내용 정리 및 연습문제들..
PL_SQL정리.txt
PL_SQL정리.txt 내용
*-----------------------------------------------------------------*
| 기본
*-----------------------------------------------------------------*
8.X.X
DB엔진.ServicePack.BugPatch
Query : select
DML : Insert, update, delete
TCL : commit, rollbak;
DDL : create, alter, drop, rename, truncate, comment
DCL : grant, revoke
SQL의 특징 : 자연어(고급언어), ;, ANSI, 축약(x), 변수(x), 제어문(x)
*-----------------------------------------------------------------*
| SQL*PLUS
*-----------------------------------------------------------------*
자주쓰는 명령어 : spool, save, ed, run, /, set null "널이네"
SQL> select * from dept;
SQL> save v1 -- v1.sql 파일로 저장
SQL> @vi -- vi.sql 파일내용 실행
SQL> host -- console로 잠시 나감
C:\> notepad v1.sql
C:\> exit -- sqlplus로 돌아감
치환변수(SQL*PLUS에서만 사용)
SQL> select * from &tab1; --변수사용(tab1)
tab1의 값을 입력하십시오: dept --변수값 입력(dept)
구 1: select * from &tab1
신 1: select * from dept
SQL> select &col1 from &tab1;
col1의 값을 입력하십시오: deptno
tab1의 값을 입력하십시오: dept
구 1: select &col1 from &tab1
신 1: select deptno from dept
SQL*PLUS 환경변수 설정
+일시적 설정 : [set 환경변수 value]
SQL> set linesize 100 --가로 100자
SQL> set pagesize 50 --하나의 페이지의 줄수(페이지=헤더에서 헤더 사이)
SQL> show all --환경변수 모두 보기
SQL> show pagesize --환경변수 하나만 보기
SQL> show user --현재 DB에 접속한 사용자 ID
SQL> set time on --현재 시간 표기 "09:31:22 SQL>"
SQL> set timing on --쿼리 실행 시간 보여주기
SQL> select * from emp;
*주의.LogOut시 설정정보가 사라짐
+영구적 설정
glogin.sql 파일 편집
>> C:\oracle\product\10.2.0\db_1\sqlplus\admin\glogin.sql
set linesize 100
set pagesize 50
...
+prompt 설정
SQL> set sqlprompt "_user:SQL>"
SCOTT:SQL>
Script 파일 생성
SQL> ed [파일명]
SQL> @[파일명]
example]
SQL> ed abc --각종 SQL*PLUS 명령 및 SQL 작성
SQL> @abc --실행
SPOOL
> save file --저장
> @ file --실행
> spool file --화면 capture start
> spool off --화면 capture end
┏━━━━━━━┳━━━━━━━━━━━━━━┒
┃ <SQL> ┃ <SQL*PLUS> ┃
┠━━━━━━━╋━━━━━━━━━━━━━━┫
┃ANSI ┃tool(ORACLE) ┃
┠━━━━━━━╋━━━━━━━━━━━━━━┫
┃; ┃□ ┃
┠━━━━━━━╋━━━━━━━━━━━━━━┫
┃SELECT ┃SAVE,@ SPOOL,R,/,RUN,DESC, ┃
┃ ┃ED(edit) ┃
┠━━━━━━━╋━━━━━━━━━━━━━━┫
┃축약(X) ┃축약(O) ┃
┠━━━━━━━╋━━━━━━━━━━━━━━┫
┃buffer(O) ┃buffer(X) ┃
┗━━━━━━━┷━━━━━━━━━━━━━━┛
SQL> ed --버퍼 내용 확인
--afiedt.buf 편집후 종료
SQL> / --버퍼내용 실행(r,/)
*-----------------------------------------------------------------*
| BETWEEN ... IN
*-----------------------------------------------------------------*
┏━━━━┒
━━┷━━━━┷━━
1 SQL> select ename, sal from emp where sal >= 1500 and sal <= 3000;
2 SQL> select ename, sal from emp where sal BETWEEN 1500 AND 3000; --가독성
1 SQL> select ename, sal, comm from emp where comm = 300 or comm = 500 or comm = 1400;
2 SQL> select ename, sal, comm from emp where comm IN (300, 500, 1400); --가독성
*-----------------------------------------------------------------*
| 문자열 LIKE
*-----------------------------------------------------------------*
SQL> select ename from emp where ename LIKE '%A%'; -- % : 공집합, 1, n
SQL> select ename from emp where ename LIKE '_A%'; -- _ : 반드시 1문자
*-----------------------------------------------------------------*
| NULL
*-----------------------------------------------------------------*
SQL> select empno, ename, mgr from emp where mgr = null; (X)
SQL> select empno, ename, mgr from emp where mgr IS NULL; (O)
*-----------------------------------------------------------------*
| SQL & select
*-----------------------------------------------------------------*
* SQL란? 구조적 질의어
-------------------
ㆍ변수(X)
ㆍ제어문(X)
ㆍ초급 쉽다
ㆍ(영어)언어 구조
* SQL 종류
Query : SELECT
DML : Insert, Update, Delete
TCL : Commit, Rollback
* SELECT
SELECT col1, col2, alias, *, '~~', ||, sal*12, func, (),distinct
FROM table1, table2, ...
WHERE condition -----------------------> 연산자 1. >,<,>=,<=,=,!=,<>
and/or condition 2. between A and B
GROUP BY col1, col2, ... 3.IN
HAVING condition 4.like
ORDER BY col1 [ASC|DESC], col2, ... 5.is null
position
* ORACLE SQL 파싱 순서
4. SELECT
1. FROM
2. WHERE
3. GROUPO BY
5. HAVING
6. ORDER BY
* 이름명명규칙
1. A-Z, a-z, 0-9, _, $, #
2. 첫글자 영문
3. max 30글자
4. 예약어 금지 (select, from... 등 금지)
5. 의미있게
*-----------------------------------------------------------------*
| 연산자
*-----------------------------------------------------------------*
* 연산자 [부정형은 NOT]
┌[NOT] >,<,>=,<=,!=,<>
├[NOT] BETWEEN a AND b
├[NOT] IN
├[NOT] like
└is [NOT] Null
*-----------------------------------------------------------------*
| FUNCTION
*-----------------------------------------------------------------*
FUNCTION - sql은 변수를 사용하지 않고 ROW를 사용
┌built-in function (기본제공) ┳ Single row function : 하나의 row return
│ ┗ Multiple row function : 여러개 row return
└USER define function : PL/SQL
─SINGLE ROW FUNCTION
SQL> select UPPER(ename), LOWER(ename), INITCAP(ename) from emp;
-- upper 대문자, lower 소문자, initcap 첫 알파벳만 대문자
UPPER(ENAM LOWER(ENAM INITCAP(EN
---------- ---------- ----------
SMITH smith Smith
ALLEN allen Allen
WARD ward Ward
...
SQL> select substr(ename, 1, 3), substr(ename, 4), instr(ename, 'A'), length(ename), concat(ename, job), lpad(sal, 10,'*'), rpad(sal, 10,'*')
from emp;
SUBSTR SUBSTR(ENAME,4 INSTR(ENAME,'A') LENGTH(ENAME) CONCAT(ENAME,JOB) LPAD(SAL,10,'*') RPAD(SAL,10,'*')
------ -------------- ---------------- ------------- ------------------- -------------------- --------------------
SMI TH 0 5 SMITHCLERK *******800 800*******
ALL EN 1 5 ALLENSALESMAN ******1600 1600******
WAR D 2 4 WARDSALESMAN ******1250 1250******
...
SQL> select round(45.129, 2), trunc(45.129, 2) from dual; --반올림, 내림
ROUND(45.129,2) TRUNC(45.129,2)
--------------- ---------------
45.13 45.12
SQL> select mod(101, 2) from dual; --나머지
MOD(101,2)
----------
1
SQL> select sysdate from dual;
SYSDATE
--------
10/05/18
SQL> select months_between(sysdate, hiredate), --월수 계산 (months_between과 sysdate 함수의 중첩)
add_months(hiredate, 6), --월수 추가
next_day(hiredate, '금'), --다음 '금'요일 날짜 (or 'FRIDAY')
last_day(hiredate) --해당월의 마지막 날짜
from emp;
MONTHS_BETWEEN(SYSDATE,HIREDATE) ADD_MONT NEXT_DAY LAST_DAY
-------------------------------- -------- -------- --------
353.047841 81/06/17 80/12/19 80/12/31
350.951066 81/08/20 81/02/27 81/02/28
SQL> select ename, hiredate, next_day( add_months(hiredate,6), '금') as "6개월후 금" from emp;
ENAME HIREDATE 6개월후 금
---------- -------- --------
SMITH 80/12/17 81/06/19
ALLEN 81/02/20 81/08/21
WARD 81/02/22 81/08/28
─MULTIPLE ROW FUNCTION
select
COUNT(*), COUNT(sal), MIN(sal), MAX(sal), SUM(sal), AVG(sal)
from emp;
주의.AVG() 같은 경우 Null 값을 제외하고 계산함.
- 형변환 FUNCTION : to_형변환(before, 형식)
┏━━━┒---to_char()---> ┏━━━━━┒---to_date()---> ┏━━┒
┃Number┃ ┃Character ┃ ┃Date┃
┗━━━┛<--to_number()-- ┗━━━━━┛<---to_char()--- ┗━━┛
SQL> select sysdate - to_date('2000/01/01', 'YYYY/MM/DD') from dual;
SYSDATE-TO_DATE('2000/01/01','YYYY/MM/DD')
------------------------------------------
3790.53318
SQL> select to_char(sysdate, 'YYYY/MM/DD:HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2010/05/18:12:48:54
SQL> select ename, to_char(sal, '$999,999.99') from emp;
ENAME TO_CHAR(SAL,
---------- ------------
SMITH $800.00
ALLEN $1,600.00
SQL> select ename, to_char(sal, 'L999,999.99') from emp;
ENAME TO_CHAR(SAL,'L999,999
---------- ---------------------
SMITH ￦800.00
ALLEN ￦1,600.00
- 일반 FUNCTION
* Null 처리 예제
SQL> select ename, sal*12 + nvl(comm, 0), nvl2(comm,1,0), comm from emp;
ENAME SAL*12+NVL(COMM,0) NVL2(COMM,1,0) COMM
---------- ------------------ -------------- ----------
SMITH 9600 0
ALLEN 19500 1 300
WARD 15500 1 500
* '오라클의 IF문 decode'와 'ANSI표준 case문'
SQL> select ename, job, sal,
decode(job, 'MANAGER', SAL*1.1,
'CLERK', SAL*1.05,
SAL
) "upsal"
,
case job
when 'MANAGER'
then SAL*1.1
when 'CLERK'
then SAL*1.05
else
sal
end "upsal2"
from emp;
* 자주쓰는 FUNCTION : to_char, to_number, to_date, nvl, nvl2, decode, case문
*-----------------------------------------------------------------*
| GROUP BY
*-----------------------------------------------------------------*
GROUP BY : 동일한 값끼리 grouping
SQL> select avg(sal), deptno from emp group by deptno;
-- group by 절에 온 column 만 select에 올 수 있다.
HAVING : group by에 대한 조건절
SQL> select avg(sal), deptno from emp
group by deptno
having avg(sal) >= 2500;
차이점 ┌WHERE condition : ROW 제한
└HAVING condition : group 제한
*-----------------------------------------------------------------*
| ORDER BY
*-----------------------------------------------------------------*
정렬(order by) : 항상 select문 가장 마지막에 위치하는 절
SQL> select ename, sal SQL> select ename, sal
from emp from emp
order by sal desc, ename ASC; order by sal desc, ename DESC;
ENAME SAL ENAME SAL
---------- ---------- ---------- ----------
KING 5000 KING 5000
FORD 3000 <---------> SCOTT 3000
SCOTT 3000 <---------> FORD 3000
JONES 2975 JONES 2975
**.참고.Table을 명시해야 다른 table에서 column을 찾는 과정이
없어지므로 성능상 좋음.
*-----------------------------------------------------------------*
| JOIN
*-----------------------------------------------------------------*
1) SQL92 방식에서의 Join
*Cartesian product: 모든 조합 생성
SQL> select ename, loc
from emp, dept;
*Equi-Join (Inner Join) : equal의 사용
SQL> select e.ename, d.dname, e.deptno
from emp e, dept d
where e.deptno = d.deptno;
*Non-Equi Join : equal의 미사용
SQL> select e.ename, s.grade
from emp e, salgrade s
where e.sal BETWEEN s.losal AND s.hisal;
*Self Join : 자기자신을 join
SQL> select a.empno, a.ename, a.mgr, b.ename
from emp a, emp b
where a.mgr = b.empno;
*Outer Join : Null 컬럼쪽에 (+) 추가. Null에 대한 출력.
SQL> select a.empno, a.ename, a.mgr, b.ename
from emp a, emp b
where a.mgr = b.empno(+);
참고.Join Table이 N개 ---> Join 조건은 최소 N-1개
2) SQL99 방식
*Cross Join (cartesian product):
SQL> select e.ename, d.dname
from emp e CROSS JOIN dept d;
--SQL92의 "select e.ename, d.dname fro emp e, dept d;" 와 동일.
*Natural Join : Column명이 동일한 Column을 join condition으로 사용
+동일 column이 한개인 경우 자동 column 지정
SQL> select e.ename, d.dname
from emp e NATURAL JOIN dept d;
--SQL92의 Equi Join와 동일
--select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
+동일 column이 여러개인 경우 특정 column 지정
SQL> select e.ename, d.dname
from emp e JOIN dept d
USING(deptno);
+다른 column 간의 join 지정
SQL> select e.ename, d.dname
from emp e JOIN dept d
ON( e.deptno = d.deptno ); --Inner Join
*Outer Join
+Left outer join
SQL> select e.ename, c.ename
from emp e LEFT [OUTER] JOIN emp c
ON( e.mgr = c.empno );
--SQL92에서의 "where e.mgr = c.empno(+)"
+Right outer join
+Full outer join
*-----------------------------------------------------------------*
| subquery와 select 정리
*-----------------------------------------------------------------*
┏━━━━━━━━━┒
┃ Main query ┃
┃ ┏━━━━━━┫
┃ ┃ Sub query ┃
┗━━┷━━━━━━┛
SubQuery ::
+ Single Row Subquery : 한개 Row 반환
+ Multiple Row Subquery : 여러개 Row 반환
+ Multiple Column Subquery : 여러개 Column 반환
SubQuery Condition
1. 괄호안에 포함된다. (Subquery)
2. Single Row Subquery 앞엔 Single Row Operator(비교연산자:>,<,=,!=,<>)가 와야 한다.
3. Multiple Row Subquery 앞엔 Multiple Row Operator(IN) 가 와야 한다.
4. Subquery 내에서는 order by 절 무의미 => 구현(X)
(단, 8i R2 부터 from 절에 사용되는 Subquery의 경우 가능)
Single Row Subquery
EX] JONES 사원보다 더 많은 급여를 받는 사원의 사번, 이름, 급여는?
SQL> select empno, ename, sal
from emp
where sal >=
(
select sal from emp where ename = 'JONES' --sub query
);
EX] BLAKE 사원보다 더 늦게 입사한 사원의 이름, 입사일?
SQL> select ename, hiredate
from emp
where hiredate >
(
select hiredate from emp where ename = 'BLAKE' --sub query
)
order by hiredate asc;
Multiple Row Operation
+ IN : 하나라도 있다면 True
+ >ALL, <ALL : 모두 만족 한다면 True
+ >ANY, <ANY : 하나 이상 있다면 True
EX] select ename, sal form emp where sal [M.R.O.] ([Subquery]);
┏━━━━┳━━━━━━━━━━━━━━━┳━━┒
┃ ┃ >ALL <ALL >Any <Any┃ ┃
┃ SAL ┃ 大 小 小 大┃ ┃
┠━━━━╋━━━━━━━━━━━━━━━╋━━┫
┃ 800 ┃ F T F T ┃ 850┃
┃ 900 ┃ F F T T ┃ 950┃
┃ 1000 ┃ F F T T ┃1050┃
┃ 1100 ┃ T F T F ┃ ┃
┗━━━━┷━━━━━━━━━━━━━━━┷━━┛
*-----------------------------------------------------------------*
| DDL
*-----------------------------------------------------------------*
- DDL(Data Definition Languege) : CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT
*DDL 문은 AutoCommit(exit, ddl, dcl) 일어남.
+ 테이블 조작
SQL> CREATE table copy_emp as select * from emp; --구조 및 데이터 복사
SQL> CREATE table copy_emp1 as select * from emp where 0=1; --구조만 복사(subquery)
SQL> insert into copy_emp1 select * from emp; --테이터 복사(subquery)
SQL> update copy_emp1 --데이터 수정(subquery)
set sal = (select max(sal) from emp)
where deptno = (select deptno from dept where loc = 'DALLAS');
SQL> DROP table copy_emp1; --테이블 삭제
**휴지통 기능(10g)
SQL> show recyclebin --휴지통 내용보기
SQL> flashback table copy_emp1 to before drop; --복원(flashBACK)
SQL> drop table copy_emp1 purge --휴지통에 넣지 않고 삭제(PURGE)
SQL> purge recyclebin --휴지통 비우기
SQL> RENAME copy_emp1 to cemp; --테이블명 변경
SQL> ALTER TABLE emp1 RENAME TO cemp; --테이블명 변경
SQL> TRUNCATE table cemp; --테이블 데이터 전체삭제
(즉시 commit수행되어 rollback 안됨)
** TRUNCATE vs DELETE
┏ TRAUNCATE : before image 사용하지 않으므로 빠름(autocommit->rollback 불가). where절 불가
┗ DELETE : before image 사용하여 rollback 사용가능. where절 가능
SQL> comment on table emp is '사원 table'; --테이블 주석
comment on column emp.empno is '사원번호'; --컬럼 주석
SQL> select * from user_tab_comments; --테이블 주석 보기
select * from user_col_comments; --컬럼 주석 보기
+ 컬럼
SQL> ALTER table copy_emp1 ADD hp varchar2(11); --컬럼 추가
SQL> ALTER table copy_emp1 RENAME COLUMN hp TO mp; --컬럼명 변경
SQL> ALTER table copy_emp1 MODIFY mp varchar2(15); --컬럼 타입 변경
SQL> ALTER table copy_emp1 DROP COLUMN mp; --컬럼 삭제
*-----------------------------------------------------------------*
| DML
*-----------------------------------------------------------------*
* DML (Data Manuplate Language) INSERT, UPDATE, DELETE
* DML 수행시 rollback segment에 before image를 복사해두어 rollback 수행시 before image로 복원시킴.
* DML 수행 순서
1. before Image copy
2. lock 설정
3. 변경
SQL> update cemp set sal = 100 where ename = 'SCOTT';
SQL> INSERT into dept values (50, 'A', 'B');
SQL> INSERT into dept values (60, 'B', null); -- Null의 명시적 삽입법
SQL> INSERT into dept (deptno, dname) values (70, 'C'); -- Null의 암시적 삽입법
SQL> UPDATE dept set dname ='경리' where deptno = 50;
SQL> DELETE from dept where deptno IN (50, 60, 70);
*-----------------------------------------------------------------*
| TCL
*-----------------------------------------------------------------*
* TCL (Transaction Control Language) : Comit(모든 성공), RollBack(모두 취소)
Transaction : 일련의 작업처리를 위한 연관된 DML의 모음
*-----------------------------------------------------------------*
| LOCK 과 TCL
*-----------------------------------------------------------------*
TCL : commit, rollback
Read Consistency : select 문이 실행되는 순간까지 commit된 data만 읽는다.
Commit ┃ Rollback
━━━━━━━━━╋━━━━━━━━━━━
Transaction 반영 ┃ Transaction 취소
━━━━━━━━━┷━━━━━━━━━━━
Transaction 종료
┳ LogIn
┃ ┃
┃ 1T->┃
┃ ┃
┃ ┠ TCL : T종료
┃ ┃
┃ ┃
┃ ┃
┷ Logout
SAVEPOINT <이름>; :rollback 저장점 생성
ROLLBACK to <이름>;
ex] savepoint p1;
insert into ...;
rollback to p1;
DB의 성질:
무결성
동시성
LOCK : Transaction간 상호파괴적인 행위를 막기 위한 Oracle의 관리메카니즘
+ 무한대기
+ 최저 level로 관리
+ Auto
+ DML => Row level lock 지원
Lock ::
A (서울) ┃ B (부산)
━━━━━━━━━━━╋━━━━━━━━━━━
1.update emp ┃ 2.select anem,sal
set sal=100 ┃ from emp
where ename='SCOTT';┃ where ename='SCOTT';
┃
┃ 3.update emp
┃ set sal=200
┃ where ename='SCOTT';
4. rollback; ┃ --대기 (Row level lock)
--B의대기 풀림 ┃
━━━━━━━━━━━┷━━━━━━━━━━━
Row level lock ::
A (서울) ┃ B (부산)
━━━━━━━━━━━╋━━━━━━━━━━━
1.update emp ┃ 2.update emp
set sal=100 ┃ set sal=200
where ename='SCOTT';┃ where ename='SMITH';
┃ --수행
┃ 3.update emp
┃ set comm=300
┃ where ename='SCOTT'; --A에서 작업중인 row
4. rollback; ┃ --대기(Row level lock)
--B의대기 풀림 ┃
┃
━━━━━━━━━━━┷━━━━━━━━━━━
DeadLock ::
A (서울) ┃ B (부산)
━━━━━━━━━━━╋━━━━━━━━━━━
1.update emp ┃ 2.update emp
set sal=100 ┃ set sal=200
where ename='SCOTT';┃ where ename='SMITH';
--수행 ┃ --수행
┃
3.update emp ┃ 4.update emp
set sal=300 ┃ set sal=400
where ename='SMITH';┃ where ename='SCOTT';
--대기 ┃ --대기 (DEAD LOCK)
*DEAD LOCK ERROR표시 ┃
━━━━━━━━━━━┷━━━━━━━━━━━
Oracle을 DeadLock 자동 검출
: 오래기다린 A에게 에러 표시
: ORA-00060: 자원 대기중 교착 상태가 검출되었습니다.
DeadLock 회피 ::
1. 동일시간대에 비슷한 작업을 동시에 처리하기때문에 발생
- 시간대를 달리하여 작업
- 업무이관 or 한명이 작업
*-----------------------------------------------------------------*
| TABLE
*-----------------------------------------------------------------*
┏ 관리용 Table => Data Dictionary <<┒
┃ ┠ metadata
┗ USER Table => define table >>┛
- 관리용 Table : Data Dictionary
SQL> select * from dict; --659개 (scott에서 실행)
SQL> select * from dict; --1870개 (sys에서 실행)
*관리테이블 명칭 형식 : 접두사 + 관심 KEYWORD + 복수형
SQL> conn scott/tiger -- scott 계정으로 변경
┏ USER_[TABLE] : 내가 생성한 것과 관련된 정보
┃ SQL> desc user_tables --테이블 정보
┃ SQL> select table_name rom user_tables;
┃
┠ ALL_[TABLE] : 접근 권한이 있는 것과 관련된 정보
┃ SQL> desc all_tables;
┃ SQL> select table_name, owner from all_tables; --테이블명과 소유자 확인
┃
┠ DBA_[TABLE] : 관리자만 접근해야하는 관련된 정보
┃ SQL> conn[ect] sys/oracle as sysdba; --관리자 계정으로 변경
┃ SQL> desc dba_users;
┃ SQL> select username, password from dba_users; --사용자 정보 보기
┃
┗ V$[TABLE] : 동적, 성능 관련된 정보(ex:동시접속자 수)(ex:동시접속자 수)
- USER table
EX] Create table emps
(
empno number(4), --숫자4자리(ex:9999)
ename varchar2(20), --글자20자리
sal number(7,2), --숫자7자리중 소숫점 2자리(ex:12345.67)
hiredate date --날짜형
);
* 문자형 ┏ char : 고정문자
┠ varchar2 : 가변형 문자(varchar 개선된 버전)
┗ varchar : 가변형 문자(사용하지 않음)
] CHAR vs VARCHAR2
┏ char(8) 에 ABC를 저장할 경우 : 길이가 남을 경우 공백포함
┃ ┏━┳━┳━┳━┳━┳━┳━┳━┓
┃ ┃A ┃B ┃C ┃ ┃ ┃ ┃ ┃ ┃
┃ ┗━┻━┻━┻━┻━┻━┻━┻━┛
┗ varchar2(8) 에 ABC를 저장할 경우 : 남는 공간은 반납
┏━┳━┳━┓
┃A ┃B ┃C ┃
┗━┻━┻━┛
* Number ┏ number(32) : 32자리까지 가능
┗ number(7,2) : 실수형, 12345.67
*-----------------------------------------------------------------*
| Data Type
*-----------------------------------------------------------------*
* Data Type
┏ Number
┣ Varchar2(4000 byte) -> Long(2GB) : Text ─> CLOB(4GB) :Text
┣ Char(2000 byte)
┣ Date
┗ Raw Long Raw(2GB) : Binary ┬> BLOB (4GB)
└> BFILE (4GB) : OS의 File 위치만 DB에 저장하며 실제 데이터는 별도 파일로 저장
-. Long Type
- 1개의 타입에 Long 타입 1개만 가능
SCOTT> CREATE TABLE LONGEX
2 (
3 Col1 LONG,
4 Col2 LONG RAW
5 );
Col2 long raw
*
4행에 오류:
ORA-01754: LONG 유형의 열은 테이블에 1 개만 포함될 수 있습니다
-. LOB Type (BLOB, CLOB, Bfile)
SCOTT> CREATE TABLE LOBEX
2 (
3 COL1 CLOB,
4 COL2 BLOB,
5 COL3 BFILE
6 );
SCOTT> DESC LOBEX;
이름 널? 유형
------ -------- -------------------
COL1 CLOB
COL2 BLOB
COL3 BINARY FILE LOB
SCOTT> SELECT * FROM LOBEX;
SP2-0678: SQL*Plus에 의해 열 또는 속성 유형을 나타낼 수 없습니다
경 과: 00:00:00.01
*-----------------------------------------------------------------*
| 제약조건
*-----------------------------------------------------------------*
제약조건 : 무결성, 동시성
-. Constraint : DML에 대한 제약조건을 주어 무결성 유지
1) Primary Key : 중복X, Null X
2) Not Null : Null X, Check 제약의 일종
3) Check : 특정조건 체크
4) Unique : 고유값
5) Foreign Key : 외부참조
-. 제약조건 설정
1)Table 생성시 설정
(1) Column Level 정의 방식
- Column명 Datatype [CONSTRAINT 제약명] 제약종류
: 제약명 생략시 -> SYS_C숫자 형식의 제약명을 임의 부여
ex)
SCOTT> CREATE TABLE DEPT1
2 (
3 DEPTNO NUMBER(2) PRIMARY KEY,
4 DNAME VARCHAR2(20) UNIQUE,
5 LOC VARCHAR2(20)
6 );
(2) Table Level 정의 방식
- CREATE TABLE Table명(
-- Column 정의 시작
Column명 DataType,
Column명 DataType,
...
-- 제약 정의 시작
CONSTRAINT 제약명 제약종류(Column명)
...
);
- 제약들을 Table 정의 시, 마지막에 위치시킴
SCOTT> CREATE TABLE EMP1
2 (
3 EMPNO NUMBER(4) CONSTRAINT EMP1_EMPNO_PK PRIMARY KEY,
4 ENAME VARCHAR2(20) NOT NULL,
5 SAL NUMBER(7,2),
6 DEPTNO NUMBER(2),
7 CONSTRAINT EMP1_SAL_CK CHECK( SAL BETWEEN 500 AND 5000),
8 CONSTRAINT EMP1_DEPTNO_FK FOREIGN KEY(DEPTNO)
9 REFERENCES DEPT1(DEPTNO)
10 );
SCOTT> DESC USER_CONSTRAINTS;
SCOTT> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('EMP1', 'DEPT1');
※ 컬럼 Heading Format 설정
COL 컬럼HEADING명 FORMAT A숫자
ex)
SCOTT> COL SEARCH_CONDITION FORMAT A25
2) Table 생성이후 설정
(1) Constraint 추가
- ALTER TABLE 테이블명 ADD CONSTRAINT 제약명 제약종류(컬럼명)
SCOTT> ALTER TABLE DEPT1
ADD CONSTRAINT DEPT1_LOC_UK UNIQUE(LOC);
(2) Constraint 삭제
- ALTER TABLE 테이블명 DROP CONSTRAINT 제약명
SCOTT> ALTER TABLE DEPT1 DROP CONSTRAINT DEPT1_LOC_UK;
- Not Null 제약의 경우 유일하게 Table Level 정의 방식으로 정의불가
SCOTT> ALTER TABLE DEPT1 ADD CONSTRAINT DEPT1_LOC_NN NOT NULL(LOC); --에러발생
- 따라서 다음과 같은 방식으로 추가할 수 있음.
SCOTT> ALTER TABLE DEPT1 MODIFY( LOC VARCHAR2(20) NOT NULL);
*-----------------------------------------------------------------*
| Set Operator(집합연산)
*-----------------------------------------------------------------*
* Set Operator(집합연산)
┏ Set Operator의 대상이 되는 테이블들의 데이터의 갯수, 타입이 서로 동일해야함.
┣ Set Operator의 INTERSECT, MINUS, UNION의 경우 내부적으로 Sort를 유발.
┗ Set Operator의 UNION ALL의 경우 Sort를 하지 않음
-. MINUS : 차집합 A - B (내부에서 Sort 함)
SCOTT> CREATE TABLE EMP_HIS
AS
SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO = 10;
SCOTT> SELECT *
FROM EMP
WHERE EMPNO NOT IN (SELECT EMPNO FROM EMP_HIS);
SCOTT> SELECT EMPNO, ENAME, JOB FROM EMP
MINUS
SELECT EMPNO, ENAME, JOB FROM EMP_HIS;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7788 SCOTT ANALYST
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
-. INTERSECT : 교집합 A ∩B (내부에서 Sort 함)
SCOTT> SELECT EMPNO, ENAME, JOB FROM EMP
INTERSECT
SELECT EMPNO, ENAME, JOB FROM EMP_HIS;
EMPNO ENAME JOB
---------- ---------- ---------
7782 CLARK MANAGER
7839 KING PRESIDENT
7934 MILLER CLERK
-. UNION : 합집합 A + B - (A ∩B) (내부에서 Sort 함)
SCOTT> SELECT EMPNO, ENAME, JOB, SAL FROM EMP
2 UNION
3 SELECT EMPNO, ENAME, JOB, 0 FROM EMP_HIS;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 0
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 0
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 0
7934 MILLER CLERK 1300
※ 위와 같은 경우 매칭 컬럼의 갯수를 맞추기 위하여 EMP 테이블의
SAL Column과 같은 타입의 데이터(0)를 임의로 지정 하거나 NULL을
사용하여 연산을 수행할 수 있음
-. UNION ALL : 합집합 A + B
- 단순연결, Sort하지않음
- A ∩B가 공집합(∮)일 경우 UNION ALL이 Sort를 하지않아 성능이 좋음
SCOTT> SELECT EMPNO, ENAME, JOB FROM EMP
UNION ALL
SELECT EMPNO, ENAME, JOB FROM EMP_HIS;
※ Set Operator 과 Join 비교
1) SET Operator
┌─┐ ┌─┐
│ │ │ │
└─┘ │ │
+ = │ │
┌─┐ │ │
│ │ │ │
└─┘ └─┘
2) Join
┌─┐ ┌─┐ ┌───┐
│ │ + │ │ = │ │
└─┘ └─┘ └───┘
*-----------------------------------------------------------------*
| View
*-----------------------------------------------------------------*
4. View
+ 물리적인 Table에 근거한 논리적 가상 Table.
+ View는 Table과 달리 저장장소를 갖지 않음.
+ View는 Data Dictionary(USER_VIEWS)에 SELECT문이 저장되어 있음.
+ Query(SELECT) 수행시 DB는 FROM절의 참조대상의 타입을
Dicitionary에 Query하여 수행하여 테이블 or 뷰여부 판단.
View인 경우 Dictionary에서 Text를 읽어와 SubQuery 형식으로 수행
SELECT * FROM ( Dictionary의 TEXT );
-. View의 장점
1) 보안
- Table에서는 선택적인 컬럼 참조가 불가 All or None.
2) 액세스의 단순화
-. View 생성
- 10g에서 View 생성을 하기 위하여서는 권한필요(9i까지는 자동권한)
SYSTEM> GRANT CREATE VIEW TO SCOTT;
SCOTT> CREATE VIEW EMPVW30
2 AS
3 SELECT EMPNO, ENAME, SAL, DEPTNO
4 FROM EMP
5 WHERE DEPTNO = 30;
SCOTT> DESC USER_VIEWS;
SCOTT> SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
SCOTT> INSERT INTO EMPVW30 VALUES(2222, '홍길동', 3000, 30);
-. View 종류
1) Simple View
2) Complex View : group by, having, group function, join, distinct 키워드를 포함한 View
│ DML │
──────┼───┼─
Simple View │ ○ │
Complex View│ △ │
▶ Complex View의 경우 일부 DML 수행시 충분한 데이터가 존재하지 않기 때문에 수행 불가한 경우가 발생
ex) SUM_DEPT View에 Insert 시, 실데이터는 EMP Table에 Insert해야하나,
입력 데이터의 경우 Group화 된 데이터이기에 EMP Table에 Insert할 수 있는
데이터가 충분하지 않음.
-. View 수정
- ALTER VIEW는 존재하지 않음 : Data Dictionary에 저장된 Text를 수정해야 하기 때문.
- CREATE OR REPLACE [FORCE|NOFORCE] VIEW 뷰명으로 수정
- FORCE OPTION : 강제적으로 View 생성 <-> NOFORCE(default)
SCOTT> CREATE OR REPLACE [FORCE|NOFORCE] VIEW FORCEVIEW
AS
SELECT DEPTNO, AVG(SAL) AVGSAL
FROM DEPT
GROUP BY DEPTNO;
경고: 컴파일 오류와 함께 뷰가 생성되었습니다.
-. View 삭제 : DROP VIEW View명
SCOTT> DROP VIEW EMPVW30;
*-----------------------------------------------------------------*
| Top-N 방식, Limit Query
*-----------------------------------------------------------------*
사원의 사번, 이름, 급여 출력 급여가 많은순으로(3명만)
SCOTT> select empno, ename, sal, rownum
from(
select empno, ename, sal
from emp ORDER BY SAL DESC
)
where ROWNUM <=3;
*-----------------------------------------------------------------*
| ROWID, ROWNUM (Pseudo Column:의사열)
*-----------------------------------------------------------------*
SCOTT> SELECT EMPNO, ROWID, ROWNUM FROM EMP;
EMPNO ROWID ROWNUM
---------- ------------------ ----------
2222 AAAMfPAAEAAAAAdAAA 1
3333 AAAMfPAAEAAAAAdAAB 2
4444 AAAMfPAAEAAAAAdAAC 3
7369 AAAMfPAAEAAAAAgAAA 4
*-----------------------------------------------------------------*
| Index
*-----------------------------------------------------------------*
* Index
- Full Table Scan : Table 전체를 Scan
- Oracle에서 가장 빠른 접근방법은 RowId로 접근하는 것.
SCOTT> select empno, ROWID from emp;
EMPNO ROWID
---------- ------------------
7369 AAAMfPAAEAAAAAgAAA
7499 AAAMfPAAEAAAAAgAAB
※
│ Book │ DB
────┼─────┼─────────────
│ 본문 │TABLE
│ INDEX │INDEX
────┼─────┼─────────────
구성 │Keyword │자주검색할 Column의 Value
│PageNo │ROWID
────┼─────┼─────────────
필수여부│Index(X) │Index(X)
────┼─────┼─────────────
정렬순서│ ASC │ ASC
-. INDEX 장단점
1) Index는 별도의 저장장소에 저장되는 Object임.
2) Index 생성으로 Select 속도 ↑, DML속도 ↓
- Table에 Data를 Insert 한 경우, Index테이블에도 저장하여야 하기때문
3) Index 생성시에는 자주 검색되는 Column으로 구성하는 것이 좋음.
-. INDEX 생성
1) CREATE INDEX Index명 ON Table명(Column1명, .... ColumnN명)
SCOTT> select empno, ename,sal from emp where ename = 'SCOTT'; -- Table Full Scan방식으로 Search
SCOTT> create index idx_emp_ename on emp(ename); -- Index 생성
SCOTT> select empno, ename, sal from emp where ename = 'SCOTT'; -- Index Scan
2) Index 생성 방법
(1) Auto
- PK, UK 생성시, Constraint Name과 동일한 Index를 자동으로 생성
PK, UK의 경우, 중복이 불가능함으로 PK, UK 데이터 Insert시 DB는 중복여부 체크 필요
따라서 Index 생성
- 사용자 임의로 Index 삭제 불가.(필요시 제약사항을 제거하여 삭제)
SCOTT> drop index emp1_empno_pk;
drop index emp1_empno_pk
*
1행에 오류:
ORA-02429: 고유/기본 키 적용을 위한 인덱스를 삭제할 수 없습니다.
(2) Manual
- 사용자가 임의로 생성
-. Index 조회
SCOTT> desc user_indexes
SCOTT> select index_name, index_type from user_indexes;
※ NORMAL : B*Tree
LOB : LOB 데이터를 조회하기 위한 자동 생성된 Index
-. Index 삭제
SCOTT> drop index idx_emp_ename;
*-----------------------------------------------------------------*
| Sequence
*-----------------------------------------------------------------*
* Sequence
- 자동 번호 생성기
-. Sequence 생성
1) CREATE SEQUENCE Sequence명
START WITH 시작값
INCREMENT BY 증가값
MINVALUE 최소값
MAXVALUE 최대값
NOCACHE --Cache 여부
NOCYCLE --Cycle 여부
;
SCOTT> create sequence dept_deptno
start with 1
increment by 1
minvalue 1
maxvalue 100
nocache
nocycle;
SCOTT> insert into dept values(DEPT_DEPTNO.NEXTVAL, 'A', 'B');
2) Option
┌ Cycle : 최대값 도달 후, 최소값으로 돌아감
└ NoCycle : 최대값 도달 후, 에러
┌ Cache n : 한번 접근시 n개 만큼 미리 생성하여 메모리에 저장
└ NoCache : 임시저장소를 사용하지 않음.
-. Sequence 사용
1) Application에서 PK Value 입력시 Sequence 사용
2) Sequence.NEXTVAL : 다음값
3) Sequence.CURRVAL : 현재값
※ NEXTVAL, CURRVAL : Pseudo Column임.
-. Sequence 조회
SCOTT> desc user_sequences
SCOTT> select * from user_sequences;
-. Sequence 삭제
SCOTT> drop sequence dept_deptno;
*-----------------------------------------------------------------*
| Synonym
*-----------------------------------------------------------------*
* Synonym
-. Synonym 생성
1) Synonym 생성 권한
SCOTT> conn system/oracle
SYSTEM> grant create synonym to scott;
SCOTT> create synonym e3 for emp;
2) CREATE SYNONYM Synonym명 FOR Table명;
SCOTT> create synonym e3 for emp;
-. Synonym 조회
SCOTT> desc user_synonyms;
SCOTT> select * from user_synonyms;
-. Synonym 삭제
SCOTT> drop synonym e3;
*-----------------------------------------------------------------*
| 사용자 계정
*-----------------------------------------------------------------*
* 사용자 계정
-. 계정
1) SCOTT/TIGER :예제계정
2) SYS/change_on_install => Oracle의 소유자
3) SYSTEM/MANAGER => DBA계정(SYS계정으로부터 권한 위임된 계정)
1) SCOTT/TIGER :예정제계
5) INTERNAL/ORACLE
=> ORACLE V7에서의 최고관리자 계정 V8에서 호환을 위하여 남겨둠.
ORACLE 9i 부터 INTERNAL 접속 불가.
◈ ORACLE 8i(nternet) : Internet 친화적, Java지원 시작.
-. 계정 Lock/Unlock : ACCOUNT [LOCK|UNLOCK];
SQL> ALTER USER user_id IDENTIFIED BY user_pwd
-. 사용자 계정 생성 : CREATE USER user_id IDENTIFIED BY user_pwd;
SYSTEM> create user mouse identified by cat;
SYSTEM> grant connect, resource to mouse;
SYSTEM> conn mouse/cat
-. 계정변경 :
ALTER USER user_id
[IDENTIFIED BY user_pwd] -- Password 변경
[ACCOUNT lock|unlock] -- 계정 Lock/Unlock
SYSTEM> alter user mouse identified by click account lock;
SYSTEM> alter user mouse account unlock;
**.자기 자신의 Pwd 변경가능
MOUSE> alter user mouse identified by test;
-. 계정삭제 : DROP USER user명;
SYSTEM> drop user mouse;
*-----------------------------------------------------------------*
| Privilege
*-----------------------------------------------------------------*
*. Privilege
**. DDL, DCL 사용시 AutoCommit 된다.
-. 권한 종류
1) SYSTEM PRIVILEGE : 전체 시스템에 대한 Priviege -> SYS가 권한 가짐
2) OBJECT PRIVILEGE : 해당 Object에 대한 Priviege -> Creater가 권한을 가짐 -> 특정 Object 단위로 권한부여
-. 권한 부여/취소
1) 부여
- System Privilege : GRANT 권한명 TO 사용자명;
SYSTEM> grant connect, resource to mouse;
- Object Privilege : GRANT 권한명 ON Object명 TO 사용자명;
SCOTT> grant select on emp to mouse;
2) 권한 취소
- System Privilege : REVOKE 권한명 FROM 사용자명;
SYSTEM> revoke connect, resource from mouse;
- Object Privilege : REVOKE 권한명 ON Object명 FROM 사용자명;
SCOTT> revoke select on emp from mouse;
*-----------------------------------------------------------------*
| PL/SQL
*-----------------------------------------------------------------*
PL/SQL 종류 ┳ anonymouse block
┣ function (stored/applicate)
┣ procedure (stored/applicate)
┣ package
┗ trigger
PL/SQL 특징 ┳ 변수 사용 가능
┣ 제어문(FOR LOOP 반복문 등...)
┣ 라인 종결자(;)
┣ 대입연산자(:=)
┗
+ anonymouse block
┏━━━━━━━━━━━━┓
┃Declare (선택) ┃
┃ 변수 언언부 ┃
┣━━━━━━━━━━━━┫
┃begin (필수) ┃
┃ 실행부 ┃
┣━━━━━━━━━━━━┫
┃exception (선택) ┃
┃ 예외처리부 ┃
┣━━━━━━━━━━━━┫
┃end; (필수) ┃
┃ 종료 ┃
┗━━━━━━━━━━━━┛
/*
SQL> set serveroutput on
dbms_output.put_line()의 출력을 보여주도록 설정한다.
*/
SQL> begin
DBMS_output.put_line('HELLO PL/SQL');
end;
/
+ function : DB에 저장하여 실행
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃create or replace function 함수명 (변수명 자료형, ...) ┃
┃ return 자료형 ┃
┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃is ┃
┃ 변수선언 ┃
┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃begin ┃
┃ return 변수; ┃
┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃exception ┃
┃ ┃
┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃end; ┃
┃ ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
SQL> show errors --function 컴파일 에러 확인
SQL> drop function ANNSAL; --function 제거
SQL> select distinct name, type from user_source; --소스 확인
SQL> select text from user_source where name = 'ANNSAL'; --소스 확인
SQL> desc [함수명] --function 확인
SQL>
CREATE OR REPLACE FUNCTION annsal
(
vempno emp.empno%type
)
RETURN number
IS
vsal emp.sal%type :=0;
BEGIN
select sal*12 + NVL(comm,0) into vsal from emp where empno = vempno;
RETURN vsal;
END;
/
+ procedure
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃create or replace procedure 프로시저명 ┃
┃ (변수명 자료형, ...) ┃
┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃is ┃
┃ 변수선언 ┃
┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃begin ┃
┃ ┃
┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃exception ┃
┃ ┃
┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
┃end; ┃
┃ ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
매개변수 : IN(default), OUT, INOUT
실행 : SQL> 프로시저명(인자);
+ package
사용법 1 : SQL> select empno, mypack.tax(empno), mypack.annsal(empno) from emp;
사용법 2 : SQL> execute mypack.upsal(7788);
1. package spec 생성
create or replace package mypack
is
FUNCTION TAX (VEMPNO EMP.EMPNO%TYPE) RETURN NUMBER;
FUNCTION ANNSAL (VEMPNO EMP.EMPNO%TYPE) RETURN NUMBER;
PROCEDURE UPSAL (VEMPNO EMP.EMPNO%TYPE);
PROCEDURE DOWNSAL (VEMPNO EMP.EMPNO%TYPE);
end;
/
2. package body 생성
create or replace package body mypack
is
FUNCTION TAX (VEMPNO EMP.EMPNO%TYPE) RETURN NUMBER
IS
blah, blah ...
BEGIN
blah, blah ...
END TAX;
FUNCTION ANNSAL (VEMPNO EMP.EMPNO%TYPE) RETURN NUMBER
IS
blah, blah ...
BEGIN
blah, blah ...
END ANNSAL;
PROCEDURE UPSAL (VEMPNO EMP.EMPNO%TYPE)
IS
blah, blah ...
BEGIN
blah, blah ...
END UPSAL;
PROCEDURE DOWNSAL (VEMPNO EMP.EMPNO%TYPE)
(VEMPNO EMP.EMPNO%TYPE)
IS
blah, blah ...
BEGIN
blah, blah ...
END DOWNSAL;
end;
+ trigger : USER_TRIGGERS :
trigger의 목적
1. 보안용 (ex. 업무시간에만 DML 허용)
2. 감사용 (ex. 데이터의 변경 이력을 감시)
SQL: 보안용 예제>
/*
근무시간(09~18)이 아니거나 토,일요일인 경우
DML 작업을 못하도록 한다.
*/
CREATE OR REPLACE TRIGGER dml_tri
before insert or update or delete on dept
declare
vHH24 varchar2(10);
vDY varchar2(10);
begin
select to_char(sysdate, 'HH24'), to_char(sysdate, 'DY')
into vHH24, vDY from dual;
if ( vHH24 NOT BETWEEN '09' AND '18') or vDY in ('토', '일') then
-- -20000~ -20999 : 개발자용 error 번호
raise_application_error(-20001, '업무시간 아니삼');
else
dbms_output.put_line(user || '님 왜그러세요');
end if;
end;
/
SQL: 감사용 예제>
--system 계정 접속
conn system/oracle;
--audit용 테이블 생성
SYSTEM:SQL>
create table audit_data
(
username varchar2(20),
o_deptno number(2),
o_dname varchar2(20),
o_loc varchar2(20),
n_deptno number(2),
n_dname varchar2(20),
n_loc varchar2(20),
daytime date
);
--trigger 생성
SYSTEM:SQL>
create or replace trigger aud_tri
after insert or update or delete on scott.dept
for each row
begin
insert into audit_data
values (user, :old.deptno, :old.dname, :old.loc,
:new.deptno, :new.dname, :new.loc, sysdate);
end;
/
--scott 계정으로 dept테이블 insert, update, delete 수행
SCOTT:SQL> insert into dept values (60, 'A', 'B');
SCOTT:SQL> update dept set dname = 'Z' where deptno = 60;
SCOTT:SQL> delete from dept where deptno = 60;
--system 계정의 audit_data 확인
SYSTEM:SQL> select * from audit_data;
=======================================================================================
USERNAME O_DEPTNO O_DNAME O_LOC N_DEPTNO N_DNAME N_LOC DAYTIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
SCOTT 60 A B 10/06/07
SCOTT 60 A B 60 Z B 10/06/07
SCOTT 60 Z B 10/06/07
=======================================================================================
--audit_data의 데이터로 rollback 수행하는 procedure 생성
/*
scott 계정에서 system에게 권한 부여 ::
grant select, insert, update, delete on dept to system;
*/
create or replace procedure audit_rb
is
cursor curso is
select * from audit_data
order by daytime desc;
begin
for rowData in curso loop
if rowData.o_deptno is null --insert인 경우 delete 수행
and
rowData.n_deptno is not null then
delete from scott.dept where deptno = rowData.n_deptno;
elsif rowData.o_deptno is not null --delete인 경우 insert 수행
and
rowData.n_deptno is null then
insert into scott.dept values (rowData.o_deptno, rowData.o_dname, rowData.o_loc);
elsif rowData.o_deptno is not null --update인 경우 update 수행
and
rowData.n_deptno is not null then
update scott.dept set dname = rowData.o_dname, loc = rowData.o_loc where deptno = rowData.n_deptno;
else
dbms_output.put_line('데이터 불일치 발생');
end if;
end loop;
delete audit_data;
commit;
end;
/
SQL> alter trigger dml_tri disable; --trigger disable
SQL> alter trigger dml_tri enable; --trigger enable
/*
after insert on cdept --cdept 테이블에 insert 발생 후에..
for each row --여러 row 발생시마다 begin 실행
:NEW.deptno -- 새로운 값(update, insert)
:OLD.deptno -- 기존 값(update, delete)
*/
SQL: insert (:NEW)>
create or replace trigger ins_tri
after insert on cdept
for each row
declare
begin
insert into dept values (:NEW.deptno, :NEW.dname, :NEW.loc);
end;
/
SQL: update (:NEW, :OLD)>
create or replace trigger up_tri
after update on cdept
for each row
declare
begin
update dept
set dname = :NEW.dname, loc = :NEW.loc
where deptno = :OLD.deptno;
end;
/
SQL: delete (:OLD)>
create or replace trigger del_tri
after delete on cdept
for each row
declare
begin
delete from dept where deptno = :old.deptno;
end;
/
SQL> drop trigger ins_tri;
SQL> drop trigger up_tri;
SQL> drop trigger del_tri;
SQL: insert, update, delete (:NEW, :OLD)>
/*
INSERTING, UPDATEING, DELETING keyword 사용
*/
create or replace trigger dml_tri
after insert or update or delete on cdept
for each row
begin
if INSERTING then
insert into dept values (:NEW.deptno, :NEW.dname, :NEW.loc);
elsif UPDATING then
update dept
set dname = :NEW.dname, loc = :NEW.loc
where deptno = :OLD.deptno;
elsif DELETING then
delete from dept where deptno = :old.deptno;
else
dbms_output.put_line('else');
end if;
end;
/
[기능]=======================================================[기능]
/*
바인드 변수 사용
*/
SCOTT> var[iable] pno varchar2(20) --변수선언
SCOTT> begin --변수값 할당
2 :pno := '01234567';
3 end;
4 /
SCOTT> print pno --변수값 출력
PNO
--------------------------------
01234567
SCOTT> execute format_phone( :pno ); --프로시저 호출 & 바인드 변수 사용
/*
난수 생성
*/
SCOTT> select trunc(dbms_random.value(0,45), 0) from dual;
TRUNC(DBMS_RANDOM.VALUE(0,45),0)
--------------------------------
37
/*
소스 암호화 : anonymous block 외에 가능(function, procedure, trigger, packaget 등에 가능)
*/
C:>wrap iname=a5.sql --SQL파일 암호화 -> xx.pbl파일 생성
SQL>@a5.plb --.plb 파일 실행하여 function 생성
SQL>select text from user_source where name = 'FIND_DNAME'; --소스 조회
function : SQL문, 1개 value return
procedure : 작업 수행, (IN, OUT, INOUT)
[PL/SQL의 문법]=======================================================[PL/SQL의 문법]
*SQL, 치환변수(&), 입력(accept), 변수 할당(into)
SQL>
/*
부서번호를 입력받아 1명만 출력
*/
--입력값 확인 메세지 off
set verify off
--변수값 입력 --입력(accept)
ACCEPT vno PROMPT '부서번호 입력 : '
declare
vename emp.ename%type;
vsal emp.sal%type;
vdeptno number(2) := &vno; --치환변수(&)
begin
select ename, sal
INTO vename, vsal --변수 할당(into)
from emp where rownum = 1;
dbms_output.put_line(vdeptno ||'번 부서, 이름 '|| vename || ', 급여 ' || vsal);
end;
/
* 조건 분기문
SQL>
declare
begin
/* 조건문
IF 조건 THEN
...
ELIF 조건 THEN
...
ELSE
...
END IF;
*/
vmod := mod(vempno,2);
if vmod = 0 then
dbms_output.put_line('짝수');
elsif vmod = 1 then
dbms_output.put_line('홀수');
else
dbms_output.put_line('넌 NaN이냐? INFINITE냐? 뭐냐?');
end if;
end;
/
*반복문
SQL>
declare
I number;
begin
/* 반복문 탈출
exit when i > 10;
exit;
*/
/* FOR LOOP 반복문
FOR 변수명 IN [REVERSE] 하한값..상한값 LOOP -- REVERSE는 step -1
for (i=1; i<=9; i++) 와 동일
i는 변수선언이 필요 없으며 1씩만 증가함.
END LOOP;
*/
dbms_output.put_line('------------------------------------');
dbms_output.put_line('For Loop : FOR 변수명 IN [REVERSE] 하한값..상한값 LOOP');
for i in 1..9 loop
dbms_output.put_line('| 3 * '|| I ||' = '|| 3*I);
end loop;
/* BASIC LOOP 문
LOOP
EXIT WHEN 조건; --반복 탈퇴
END LOOP;
*/
dbms_output.put_line('------------------------------------');
dbms_output.put_line('Basic Loop : LOOP');
I := 1;
loop
exit when I > 9;
dbms_output.put_line('| 3 * '|| I ||' = '|| 3*I);
I := I + 1;
end loop;
/* While Loop 문
WHILE 조건 LOOP
END LOOP;
*/
dbms_output.put_line('------------------------------------');
dbms_output.put_line('While Loop : WHILE 조건 LOOP');
I := 1;
while I < 10 loop
dbms_output.put_line('| 3 * '|| I ||' = '|| 3*I);
I := I + 1;
end loop;
end;
/
*변수의 사용
변수종류 ┳ 스칼라 변수 : number, varchar2(20) ...
┣ 동적 datatype : %TYPE, %ROWTYPE
┣ PL/SQL TABLE : ex.배열
┣ PL/SQL RECORD : ex.구조체
┗ CURSOR :
1. 스칼라 변수 : number, varchar2(20) ...
i number := 1 ; --변수의 정의 및 초기화("number default 1" 와 동일)
2. 동적 datatype : %TYPE, %ROWTYPE
i emp.empno%type ; --테이블의 컬럽 타입으로 지정
e emp%rowtype ; --emp 테이블의 전 컬럼
3. PL/SQL TABLE : 배열과 같은 형태
/*
TYPE 타입명 IS TABLE OF 데이터타입
index by binary_integer;
*/
declare
type pl_tab is table of emp.ename%type
index by binary_integer;
pl1 pl_tab;
begin
for i in 1..20 loop
pl1(i) := 'SCOTT' || i;
dbms_output.put_line(pl1(i));
end loop;
end;
/
4. PL/SQL RECORD : 잘 사용안하며 cursor 사용함.
/*
TYPE 타입명 IS RECORD
(
변수명 타입,
vempno emp.empno%type
);
*/
declare
type pl_rec is record
(
vempno emp.empno%type,
vename emp.ename%type
);
pl2 pl_rec;
begin
select empno, ename into plt
from emp where empno = 7788;
dbms_output.put_line(pl2.vempno ||' '|| pl2.vename);
end;
/
5. CURSOR :
암시적(implicit) cursor : 일반 쿼리
명시적(explicit) cursor : CURSOR선언하여 사용
SQL>
/*
[CURSOR]%ISOPEN : T/F
%FOUND : T/F
%NOTFOUND : T/F
%ROWCOUNT : ROW수
*/
accept vdeptno prompt '부서번호 ? : '
declare
-- CURSOR 선언
CURSOR c1 is
select empno, ename, sal
from emp
where deptno = &vdeptno;
data c1%rowtype;
begin
-- CASE1 : CURSOR 선언 -> OPEN -> FETCH -> CLOSE
dbms_output.put_line('-----------------------------------');
OPEN c1;
if c1%ISOPEN then
dbms_output.put_line('CURSOR opened');
end if;
loop
FETCH c1 INTO data;
if c1%FOUND then
dbms_output.put_line('%FOUND ('|| c1%ROWCOUNT ||') :: '|| data.empno ||' '|| data.ename ||' '|| data.sal);
elsif c1%NOTFOUND then
dbms_output.put_line('%NOTFOUND ('|| c1%ROWCOUNT ||')');
exit;
end if;
end loop;
CLOSE c1;
if c1%ISOPEN = FALSE then
dbms_output.put_line('CURSOR closed');
end if;
/*
CASE2 : CURSOR 선언 -> FOR .. IN .. LOOP
FOR [변수] IN [CURSOR | (쿼리문)] LOOP
END LOOP;
*. 변수는 declare에 정의하지 않아도 된다.
*. CURSOR 대신 full query를 넣어도 된다.
*/
dbms_output.put_line('-----------------------------------');
FOR dataRow IN c1 LOOP --dataRow는 선언하지 않아도 c1%type으로 사용할 수 있다.
dbms_output.put_line(dataRow.empno ||' '|| dataRow.ename ||' '|| dataRow.sal);
END LOOP;
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('hello pl/sql');
end;
/
/*
--한글/영문 구분해보기
--원문:http://forums.oracle.com/forums/thread.jspa?threadID=463593&tstart=1187
declare
cbuf varchar2(100);
loc integer:=0;
ret integer:=0;
bytepos number :=2;
begin
--return : 0 if ascii, 1 if 1st byte of DBCS, 2 if 2nd byte of DBCS
cbuf := '한글';
if cbuf is NULL then
ret := 0;
end if;
while (loc <= bytepos) loop
if (ret = 1 ) then
ret := 2;
elsif (length(substrb(cbuf, loc+1, 2)) = 1) then
ret := 1;
else
ret := 0;
end if;
loc := loc + 1;
end loop;
dbms_output.put_line(ret);
end;
/
*/
*Exception ┳ predefine exception : 미리 정의된 예외
┣ nonpredefine exception : 오라클 에러 재정의
┗ user define exception : 에러 사용자 정의
1. predefine exception
* Summary of Predefined PL/SQL Exceptions
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#sthref1966
┏ NO_DATA_FOUND : 데이터가 없는 경우
┣ NOT_LOGGED_ON : DB로긴하지 않은 경우
┣ TOO_MANY_ROWS : 여러 개의 행을 리턴한 경우
┣ VALUES_ERROR : 변수 길이 초과한 경우
┣ ZERO_DIVIDE : 0으로 나누려는 경우
┣ INVALID_CURSOR : 잘못된 커서 사용의 경우
┣ INVALID_NUMBER : 수의 문자열 변환 오류의 경우
┣ DUP_VAL_ON_INDEX : 인덱스 내의 중복값 발생된 경우 (PK,UK)
┗ ...
SQL>
/*
1. exception 처리
*/
accept vno prompt '사번 입력 : '
declare
vempno emp.empno%type := &vno;
vename emp.ename%type ;
vsal emp.sal%type;
begin
select empno, ename, sal into vempno, vename, vsal from emp where empno = vempno;
dbms_output.put_line(vempno || ' '|| vename ||' '|| vsal);
EXCEPTION
-- 1. exception 처리
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('그런 데이터는 없삼');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('동일한 사번을 가진 사람 존재');
WHEN INVALID_CURSOR THEN
dbms_output.put_line('커서의 잘못된 사용법');
WHEN DUP_VAL_ON_INDEX THEN
dbms_output.put_line('PK나 UK 값 중복 발생');
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('0으로 나눌수 없습니다.');
end;
/
2. nonpredefine exception
SQL>
/*
1. exception명 정의
2. ora-XXXXX와 exception 변수 매핑
3. ora-XXXXX 발생
4. exception 처리
*/
declare
vsal number;
vexc EXCEPTION; --1. exception 변수
PRAGMA EXCEPTION_INIT(vexc, -06502); --2. exception 매핑
begin
vsal := 'SCOTT'; --3. ora-06502 발생
EXCEPTION
when vexc then --exception 처리 --4. exception 처리
dbms_output.put_line('-06502 발생: 문자는 숫자에 할당 급지');
end;
/
3. user define exception
SQL>
/*
1. exception명 정의
2. exception 발생
3. exception처리
*/
declare
vexc exception; --1. exception 변수
begin
if 0=0 then
RAISE vexc; --2. exception 발생
end if;
EXCEPTION
when vexc then --3. exception 처리
dbms_output.put_line('사용자 정의 예외 발생');
end;
/
**. when OTHERS then
SQL>
/*
1. 모든 exception catch
*/
...
EXCEPTION
when OTHERS then --1. 모든 exception catch
dbms_output.put_line('예기치 않은 예외 발생');
...
*-----------------------------------------------------------------*
| 종합문제2.
*-----------------------------------------------------------------*
SCOTT> SELECT
SUM(1) AS "TOTAL",
SUM(DECODE ( SUBSTR(TO_CHAR(HIREDATE, 'YYYY'),1,4) , '1980', 1, 0)) AS "1980",
SUM(DECODE ( SUBSTR(TO_CHAR(HIREDATE, 'YYYY'),1,4) , '1981', 1, 0)) AS "1981",
SUM(DECODE ( SUBSTR(TO_CHAR(HIREDATE, 'YYYY'),1,4) , '1982', 1, 0)) AS "1982",
SUM(DECODE ( SUBSTR(TO_CHAR(HIREDATE, 'YYYY'),1,4) , '1983', 1, 0)) AS "1983"
FROM EMP;
TOTAL 1980 1981 1982 1983
---------- ---------- ---------- ---------- ----------
14 1 10 1 0
*-----------------------------------------------------------------*
| 종합문제3. sal이 sumsal에 누적되어 출력되도록 작성.
*-----------------------------------------------------------------*
다음과 같이 sumsal이 누적되면서 나오도록 출력하라.
ename sal sumsal
scott 3000 3000
smith 1650 4650
king 5000 9650
...
SQL>
select ename, sal,
sum(sal) over(order by rowid) "sumsal"
from emp;
*-----------------------------------------------------------------*
| 각 부서별 평균급여보다 더 많은 급여를 받는 사원의 이름 급여?
*-----------------------------------------------------------------*
SQL>
select C.deptno, C.ename, C.sal
from emp C
where sal >=
(
select avg(E.sal) avgsal from emp E
where E.deptno = C.deptno
)
order by C.deptno ASC
/
*-----------------------------------------------------------------*
| 부하직원이 존재하는 사원의 사번 이름 mgr?
*-----------------------------------------------------------------*
SQL: IN>
select empno, ename, mgr
from emp
where empno in (select mgr from emp);
SQL: EXISTS>
/*
IN은 모두 비교하지만
EXISTS는 하나만 만족됨을 찾으면 수행을 중단한다.
*/
select e.empno, e.ename, e.mgr
from emp e
where exists(select 1 from emp where mgr=e.empno);
*-----------------------------------------------------------------*
| start with ... connect by prior ...
*-----------------------------------------------------------------*
SQL>
col empno format a20
select lpad(empno, 3*(LEVEL+1), ' ') empno,
ename, sal
from emp
start with ename = 'KING'
connect by prior empno = mgr
*-----------------------------------------------------------------*
| TIME ZONE
*-----------------------------------------------------------------*
SQL>
select dbtimezone, sessiontimezone from dual;
create table timezone_test
(
D1 date, -- 10/06/01
D2 timestamp, -- 10/06/01 12:32:23.000000
D3 timestamp(9), -- 10/06/01 12:32:23.000000000
D4 timestamp(4), -- 10/06/01 12:32:23.0000
D5 timestamp with time zone, -- 10/06/01 12:32:23.000000 +09:00
D6 timestamp with local time zone -- 10/06/01 12:32:23.000000
);
insert into timezone_test values (sysdate, sysdate, sysdate, sysdate, sysdate, sysdate);
alter session set time_zone ='-05:00';
select sessiontimezone from dual;
select * from timezone_test;
10/06/01
10/06/01 12:32:23.000000
10/06/01 12:32:23.000000000
10/06/01 12:32:23.0000
10/06/01 12:32:23.000000 +09:00
10/05/31 22:32:23.000000
'DB' 카테고리의 다른 글
| MSSQL (0) | 2011.01.29 |
|---|---|
| RAID (0) | 2011.01.29 |
| mysql (0) | 2010.12.13 |
| Oracle architecture (0) | 2010.07.31 |
| AUDIT - 감사 (0) | 2010.07.30 |
| SQL99 - Insert All, MERGE, IS NAN, INTERVAL (0) | 2010.07.30 |
| Index-Organized Table(IOT), Partitioned Table & Index (0) | 2010.07.30 |
| lock 관련 (0) | 2009.02.27 |