정규표현식.zip
제약조건연결LAB.pdfcomplex view
conn scott/tiger
create table emp_test
as (select * from emp);
create table dept_test
as (select * from dept);
=====================================================================
create or replace view v_emp_dept
as (select e.empno, e.ename, e.job, d.loc
from emp_test e, dept_test d
where e.deptno=d.deptno); >>> DML 허용 안함.
=====================================================================
select *
from user_updatable_columns
where table_name='V_EMP_DEPT';
OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
---------------------- --------------- --------------- --- --- ---
SCOTT V_EMP_DEPT EMPNO NO NO NO
SCOTT V_EMP_DEPT ENAME NO NO NO
SCOTT V_EMP_DEPT JOB NO NO NO
SCOTT V_EMP_DEPT LOC NO NO NO (키보존컬럼)
================================================================================
SQL> alter table dept_test add constraint v_de_pk primary key (deptno);
SQL> alter table emp_test add constraint v_ed_fk
2 foreign key (deptno) references dept_test (deptno);
SQL> alter view v_emp_dept compile; -- 뷰의 재생성
SQL> select *
2 from user_updatable_columns
3 where table_name='V_EMP_DEPT';
OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
---------------------- --------------- ------------------- ---- --- ----
SCOTT V_EMP_DEPT EMPNO YES YES YES
SCOTT V_EMP_DEPT ENAME YES YES YES
SCOTT V_EMP_DEPT JOB YES YES YES
SCOTT V_EMP_DEPT LOC NO NO NO
exception constraints
EXCEPTION CONSTRAINTS (예외처리)
<제약조건(constraint)를 위반하는 행(row) 알아내기>
아래의 예에서는 제약조건인 primary key를 disable하고 데이터들을
삽입한 후, 다시 primary key제약조건을 enable했을 경우에 대해
알아본다. enable이 실패했을 경우 어떠한 이유로 실패했는지
파악하고 이를 해결해 보자.
--test라는 테이블을 생성 ( name varchar2(10), age number(2) )
SQL> create table test (
2 name varchar2(10),age number(2)
3 );
테이블이 생성되었습니다.
--test테이블의 name컬럼에 primary key제약조건을 추가
SQL> alter table test
2 add constraint test_name_pk primary key(name);
테이블이 변경되었습니다.
--데이터 2개를 추가한다.
SQL> insert into test values ('lee', 50);
1 개의 행이 만들어졌습니다.
SQL> insert into test values ('kim', 30);
1 개의 행이 만들어졌습니다.
--test테이블에서 primary key제약조건을 disable한다.
SQL> alter table test
2 disable constraint test_name_pk;
테이블이 변경되었습니다.
EXCEPTIONS테이블을 만들어 준다. 이 테이블은 예외가 발생한
데이터를 저장한다.
이는 오라클의 admin디렉토리에 유틸리티SQL로 제공된다.
SQL> @$ORACLE_HOME/rdbms/admin/utlexcpt.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlexcpt.sql
테이블이 생성되었습니다.
--나의 소유의 테이블들 조회
SQL> select * from tab;
(exceptions 테이블 생성 확인 : select * from exceptions)
--utlexcpt의 실행으로 생성된 EXCEPTIONS테이블의 구조
SQL> desc exceptions
이름 Null? 유형
------------------------------- -------- ----
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)
--name컬럼의 primary key제약조건을 disable했으므로 본인의 이름으로 또
다시 데이터의 삽입이 가능하다
SQL> insert into test values ('kim', 27);
1 개의 행이 만들어졌습니다.
--다시 primary key제약조건을 enable하는데 조건에 만족하지
않는 데이터가 존재하면
enable을 취소하고 EXCEPTIONS테이블에 해당 데이터를 삽입한다.
즉, EXCEPTIONS테이블에
primary key제약조건을 만족하지 않는 row의 rowid가 삽입된다.
SQL> alter table test
2 enable constraint test_name_pk
3 exceptions into exceptions;
alter table test
*
1행에 오류:
ORA-02437: (KANG.TEST_NAME_PK)를 사용가능하게 할 수 없습니다
- 잘못된 기본 키입니다
--EXCEPTIONS테이블에 삽입된 데이터이다. rowid를 가지고 조건에
맞지 않는 데이터를 알아내야 한다.
SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME CONSTRAINT
-------------------- --------- ------------ ------------
AAAAfGAAFAAAACeAAA SCOTT TEST TEST_NAME_PK
AAAAfGAAFAAAACeAAC SCOTT TEST TEST_NAME_PK
rowid를 가지고 조건에 맞지 않는 데이터를 알아내는 질의문이다.
(test테이블의 rowid컬럼을 참조했음을 유의)
--'본인이름'의 row를 가지는 데이터 때문에 primary key의 enable이
실패했음을 알 수 있다.
SQL> select e.constraint, x.name
2 from exceptions e, test x
3 where e.row_id=x.rowid;
--name컬럼에 primary key제약조건이 enable되기 위해선 중첩된
row중 하나는 삭제해야 한다.
SQL> delete from test where age=27;
1 행이 삭제되었습니다.
--이제 primary key제약조건이 enable될 수 있다.
SQL> alter table test
2 enable constraint test_name_pk;
테이블이 변경되었습니다.
External Table
1. emp.dat 편집(/home/oracle/labs/emp.dat)
$ vi emp.dat
SMITH,101,2001/03/15
JOHN,102,2002/04/15
2. Directory 생성
$ sqlplus system/oracle
SQL> grant create any directory to expert;
SQL> conn expert/expert
SQL> create directory emp_dir as '/home/oracle/labs';
SQL> conn system/oracle
SQL> grant read,write on directory emp_dir to expert;
3. External table 생성
SQL> conn expert/expert
SQL> create table ext_emp
(emp_name varchar2(10),
emp_id number(3),
hiredate date)
organization external
(type oracle_loader
default directory emp_dir
access parameters
(records delimited by newline
fields terminated by ','
(emp_name char,
emp_id char,
hiredate date "yyyy/mm/dd") )
Location ('emp.dat') );
4. Select & DML 테스트
SQL> select * from ext_emp;
EMP_ID EMP_NAME HIREDATE
--------- ---------- ------------
101 SMITH 15-MAR-01
102 JOHN 15-APR-02
# read only 이므로 입력 안됨.
SQL> insert into ext_emp values (103, 'KIM', sysdate);
ERROR at line 1:
ORA-30657: operation not supported on external organized table
# read only 이므로 index 생성 안됨.
SQL> create index ext_emp_idx on ext_emp(emp_id);
ERROR at line 1:
ORA-30657: operation not supported on external organized table
5. 일반 테이블과의 조인
SQL> select a.last_name, b.hiredate
from employees a, ext_emp b
where a.employee_id = b.emp_id;
LAST_NAME HIREDATE
------------- ------------
Kochhar 15-MAR-01
De Haan 15-APR-02
sample data
7369,SMITH,CLERK,20 7499,ALLEN,SALESMAN,30 7521,WARD,SALESMAN,30 7566,JONES,MANAGER,20 7654,MARTIN,SALESMAN,30 7698,BLAKE,MANAGER,30 7782,CLARK,MANAGER,10 7788,SCOTT,ANALYST,20 7839,KING,PRESIDENT,10 7844,TURNER,SALESMAN,30 7876,ADAMS,CLERK,20 7900,JAMES,CLERK,30 7902,FORD,ANALYST,20 7934,MILLER,CLERK,10
Using the FOR-LOOP
LOOP -- sequence of statementsEND LOOP;
------------------------------------------------------------
CREATE TABLE sqr_root_sum (num NUMBER, sq_root NUMBER(6,2), sqr NUMBER, sum_sqrs NUMBER);
DECLARE
s PLS_INTEGER;
BEGIN
FOR i in 1..100 LOOP
s := (i * (i + 1) * (2*i +1)) / 6; -- sum of squares
INSERT INTO sqr_root_sum VALUES (i, SQRT(i), i*i, s );
END LOOP;
END;
Create User Syntax
CREATE USER user
IDENTIFIED { BY password
| EXTERNALLY [ AS 'certificate_DN' ]
| GLOBALLY [ AS '[ directory_DN ]' ]
}
[ DEFAULT TABLESPACE tablespace
| TEMPORARY TABLESPACE
{ tablespace | tablespace_group_name }
| QUOTA size_clause
| UNLIMITED
}
ON tablespace
[ QUOTA size_clause
| UNLIMITED
}
ON tablespace
]...
| PROFILE profile
| PASSWORD EXPIRE
| ACCOUNT { LOCK | UNLOCK }
[ DEFAULT TABLESPACE tablespace
| TEMPORARY TABLESPACE
{ tablespace | tablespace_group_name }
| QUOTA size_clause
| UNLIMITED
}
ON tablespace
[ QUOTA size_clause
| UNLIMITED
}
ON tablespace
]...
| PROFILE profile
| PASSWORD EXPIRE
| ACCOUNT { LOCK | UNLOCK }
]...
] ;
create syntax
alter table (table name) {add | modify | drop}
(column name, data type)
Creating Aggregate Functions
CREATE FUNCTION SecondMax (input NUMBER)
RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING
SecondMaxImpl;
SELECT SecondMax(salary), department_id
FROM employees
GROUP BY department_id
HAVING SecondMax(salary) > 9000;
SECONDMAX(SALARY) DEPARTMENT_ID
----------------- -------------
13500 80
17000 90
CREATE [ OR REPLACE ] FUNCTION [ schema. ]function
[ (argument [ IN | OUT | IN OUT ]
[ NOCOPY ] datatype
[, argument [ IN | OUT | IN OUT ]
[ NOCOPY ] datatype
]...
)
]
RETURN datatype
[ { invoker_rights_clause
| DETERMINISTIC
| parallel_enable_clause
}
[ invoker_rights_clause
| DETERMINISTIC
| parallel_enable_clause
]...
]
{ { AGGREGATE | PIPELINED }
USING [ schema. ]implementation_type
| [ PIPELINED ]
{ IS | AS }
{ pl/sql_function_body | call_spec }
} ;
Flashback versions query
SQL> conn scott/tiger
SQL> create table x_emp
2 (x_no, x_name, x_job, x_sal)
3 as select empno, ename, job, sal from emp
4 where deptno=20;
SQL> select * from x_emp;
X_NO X_NAME X_JOB X_SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
SQL> update x_emp
2 set x_name='ORACLE'
3 where x_name='ADAMS';
SQL> delete from x_emp
2 where x_no=7876;
SQL> select to_char(sysdate, 'HH24:MI:SS') from dual;
TO_CHAR(
--------
02:44:09
SQL> commit;
SQL> select versions_startscn, versions_endscn, versions_xid, versions_operation,x_name
2 from x_emp versions between scn minvalue and maxvalue
3* where x_no=7876
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V X_NAME
----------------- --------------- ---------------- - --------
649762 090008002F010000 D ADAMS
649762 ADAMS
SCN ==========================================================
SQL> update x_emp
2 set x_name='LEE'
3 where x_no=7788;
SQL> commit;
SQL> delete from x_emp
2 where x_no=7788;
SQL> commit;
SQL> select versions_startscn, versions_endscn, versions_xid, versions_operation,x_name
2 from x_emp versions between scn minvalue and maxvalue
3* where x_no=7888
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V X_NAME
----------------- --------------- ---------------- - -------
649914 03001F000F010000 D LEE
649908 649914 0800220005010000 U LEE
649908 SCOTT
merge와 함수기반 인덱스
Function-base index
장점: 지속적 index 사용 보장 -> 빠른 access 보장
단점: DML 사용 시 함수를 사용하여 index 값을 저장 -> 서버에 overhead발생
다른 조건(함수를 사용하지 않는..)에 대해서는
즉, 인덱스생성시 사용한 함수가 아닌 함수 사용시 index 컬럼에 변형발생.
-> index 사용 못함. -> FTS수행
****LAB*******************************************************************
SQL> conn /as sysdba
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> grant plustrace to expert;
SQL> conn expert/expert
SQL> set autot on exp --작성된 실행계획만 보여주시오.
SQL> create index emp_upper_name_idx on employees (upper(last_name));
SQL> select * from employees
2 where upper(last_name)='KING';
==> index range scan를 사용한 실행계획 작성
SQL> select * from employees
2 where lower(last_name)='king';
==> Table access Full scan을 사용한 계획 작성
****LAB*******************************************************************
## 테이블 생성
create table movies(
movie_name varchar2(30),
showtime varchar2(30),
constraint movies_name_pk primary key (movie_name));
==========================================================
## 값 추가
insert into movies
values ('knowing','6:00 PM');
select * from movies;
==========================================================
## merge
merge into
movies M1 using movies M2
on (M2.movie_name = M1.movie_name and M1.movie_name='knowing')
when matched then
update set M1.showtime = '8:00 PM'
when not matched then
insert (M1.movie_name, M1.showtime)
values ('knowing', '8:00 PM') ;
select * from movies;