[Oracle] 오라클 기본정리(3)

 

* OBJECT의 종류

- SEQUENCE

* SEQUENCE란 고유한 숫자값을 생성해 주는 객체

SEQUENCE GENERATOR가 자동으로 그 값을 생성해 주며

주로 PK값 생성을 위해 사용됩니다.

SEQUENCE는 공유 가능한 객체로서 한 사용자가 생성하면

다른 사용자도 사용할 수 있을 뿐 아니라, SEQUENCE는 테이블과 독립적이므로

여러 테이블에서 이 값을 참조하여 사용할 수 있습니다.

> CREATE SEQUENCE 이름 [ INCREMENT BY n ] [ START WITH n ]

 

[ { MAXVALUE n | NOMAXVALUE } ]

[ { MINVALUE n | NOMINVALUE } ] [ { CYCLE | NOCYCLE } ]

[ { CACHE n | NOCACHE } ]

SQL> CREATE SEQUENCE c_test_seq INCREMENT BY 1

START WITH 26 MAXVALUE 9999999 NOCACHE NOCYCLE ;

 

* SEQUENCE가 가지는 PSEUDO COLUMN은 NEXTVAL, CURRVAL 두 가지가 있습니다.

주로 데이터 입력시에 NEXTVAL을 이용해서 데이터를 입력하게 되면,

CURRVAL은 SEQUENCE의 현재 값으로 NEXTVAL가 할당되어야만 그 값이 CURRVAL로 참조 가능해집니다.

SEQUENCE 자체는 일련적으로 값을 생성하지만 SEQUENCE는 테이블에는 독립적인 Object이므로

같은 테이블에서 SEQUENCE가 사용되거나 ROLLBACK이나 SYSTEM CRASH 에 의해서 GAP이 발생할 수 있습니다.

따라서 SEQUENCE가 특정 테이블에 사용된 경우는 일련번호로 입력되는 것을 보장받기는 어려우며,

단지 UNIQUE NUMBER만 생성할 뿐입니다.

SQL> SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences ;

 

- SEQUENCE의 변경 및 삭제

o SEQUENCE의 변경

> ALTER SEQUENCE SEQUENCE이름 [ INCREMENT BY n ] [ { MAXVALUE n | NOMAXVALUE } ]

[ { MINVALUE n | NOMINVALUE } ] [ { CYCLE | NOCYCLE } ]

[ { CACHE n | NOCACHE } ]

* 자신의 SEQUENCE이거나 그 SEQUENCE에 대한 ALTER 권한을 가져야만 SEQUENCE를 수정할 수

있습니다. 유효성 검사를 하게 됩니다.

 

유효성 검사의 예를 들면 새로운 MAXVALUE는 현재의 SEQUENCE 값보다 작지 않아야 합니다.

START WITH 옵션은 ALTER SEQUENCE를 써서 변경할 수 없습니다.

다른 번호에서 시작하려면 이전 SEQUENCE를 삭제하고 다시 생성해야 합니다.

 

o SEQUENCE의 삭제

> DROP SEQUENCE SEQUENCE이름 ;

SEQUENCE를 제거할 DROP ANY SEQUENCE권한을 가지고 있거나

 

그 SEQUENCE의 소유자이어야 합니다.

 

- INDEX

ORACLE에서 제공하는 INDEX는 Pointer를 이용하여 좀더 빠르게 데이터를 검색하도록 하는

Object입니다.

만약 특정 컬럼에 INDEX가 생성되어 있지 않으면

 

일반적으로 검색시에 Full Scan을 하게 됩니다.

 

 

 

o INDEX의 생성방법에 따른 종류

Unique INDEX NON-Unique INDEX

Primary key , Unique 제약조건에 의해서 생성 또는 자동으로 생성 또는 사용자에 의해 생성

각 INDEX(Table)의 값이 유일함을 보장 각 INDEX(Table)의 값이 유일하지 않음

 

 

 

o INDEX의 생성

> CREATE INDEX 인덱스명 ON 테이블명 (칼럼 [, 칼럼] ... ) ;

- 인덱스를 만드는 때

* 조건절(WHERE)이나 조인(Join) 조건에서 칼럼을 자주 이용할 때

* 칼럼이 넓은 범위값을 가질 때

* 많은 NULL 값을 갖는 칼럼일 때

* 테이블의 데이터가 많고 그 테이블에서 조회되는 행의 수가 전체의 10-15%정도일 때

* 다수의 %를 갖는 행을 조회할 경우 Full Scan이 유리한 경우도 있음

 

- 인덱스를 만들지 않아야 할 때

* 테이블이 작을 때

* 칼럼이 조회의 조건으로 사용되는 경우가 별로 없을 때

* 대부분의 조회가 행의 10-15% 이상을 검색한다고 예상될 때

* 테이블이 자주 변경될 때

SQL> CREATE INDEX c_test1_idx ON test1 (col1) ;

 

o INDEX의 정보 확인

* USER_INDEXES 는 인덱스의 이름과 고유성 정보를 갖고 있습니다.

* USER_IND_COLUMNS 는 인덱스명, 테이블명, 칼럼명을 갖고 있습니다.

SQL> SELECT ic.index_name, ic.column_name, ic.column_position, ix.uniqueness

FROM user_indexes ix, user_ind_columns ic

WHERE ic.index_name = ix.index_name

AND ic.table_name = 'TEST1' ;

 

 

 

o INDEX의 삭제

> DROP INDEX 인덱스명 ;

인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는

테이블의 데이터에는 아무 영향이 없습니다.

SQL> DROP INDEX C_test1_idx ;

 

인덱스를 삭제하려면 그 인덱스의 소유자이거나 DROP ANY INDEX권한을 가지고 있어야 합니다.

특정 칼럼에 생성된 인덱스를 다른 칼럼으로 변경하는 ALTER는 불가능하므로 인덱스된 칼럼을

변경하려면 삭제한 후 다시 만들어야 합니다.

많은 DATA가 있는 Table의 Index를 생성할 시에는 각 행의 Index에 대해서 정의하므로 많은 시간이 소요됩니다.

 

 

- VIEW

View란 테이블이나 다른 뷰를 기초로 한 논리적이고 가상(Virtual)의 테이블입니다.

자체의 데이터는 없지만 테이블의 데이터를 보거나 변경할 수 있는 창과 같고, 실제적으로는 질의문장을

가집니다.

 

o VIEW의 종류 및 특성

* Simple View Complex View

* 테이블 수 ONE MANY

* Function의 포함여부 NO YES

* Grouping 여부 NO YES

* View를 통한 DML여부 YES NO

 

o VIEW의 생성

> CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW 이름 [ (alias[, alias] ...)] AS subquery

[ WITH CHECK OPTION [ CONSTRAINT 제약 조건 ] ] [ WITH READ ONLY ] ;

 

 

* 뷰를 정의하는 질의어는 조인, 그룹, Subquery를 포함하는 복잡한 SELECT문장으로 구성될 수 있음

* 뷰를 정의하는 질의어에는 ORDER BY 절을 쓸 수 없음

* 제약조건의 이름을 명시하지 않으면 시스템이 SYS_Cn형태의 이름을 지정함

* 뷰를 삭제하거나 재생성하지 않고 뷰의 정의를 변경하려면 OR REPLACE 옵션을 쓸 수 있음

 

 

 

- 뷰에 대한 DML문 사용 규칙

간단한 뷰에서는 DML연산 수행가능.

* 뷰가 다음 사항을 포함하는 경우 행을 삭제할 수 없음

조인(Join) 조건, 그룹 함수, GROUP BY 절, DISTINCT 명령

* 뷰가 다음 사항을 포함하는 경우 데이터를 수정할 수 없음

조인(Join) 조건, 그룹 함수, GROUP BY 절, DISTINCT 명령, 연산식으로 정의된 칼럼

* 뷰가 다음 사항을 포함하는 경우 데이터를 추가할 수 없음

조인(Join) 조건, 그룹 함수, GROUP BY 절, DISTINCT 명령, 연산식으로 정의된 칼럼,

선택되지 않은 NOT NULL 칼럼

 

 

 

o VIEW 생성시 사용되는 OPTION

 

o VIEW의 정보 확인

SQL> DESC user_views

SQL> SELECT view_name , text FROM user_views ;

 

 

 

o VIEW의 삭제

> DESC VIEW view이름 ;

* View에 대한 ALTER 명령어는 제공되지 않습니다.

* 기존의 View에 대한 정보를 변경하고 싶은 경우에는

OR REPLACE OPTION을 써서 VIEW를 생성합니다.

 

 

 

- SYNONYM

특정 Object에 부여하는 또다른 이름으로, 사용자의 편의나 참조를 빠르게 하기 위해서 사용합니다.

> CREATE [ PUBLIC ] SYNONYM Synonym명FOR Object명 ;

 

* PUBLIC은 모든 사용자가 접근가능한 동의어를 생성합니다.

* PUBLIC 동의어의 생성 및 삭제는 DBA 만이 할 수 있습니다.

 

NSC_A.SQL> CREATE SYNONYM Test1 FOR NSC_B.Test1

[출처] [기초강좌] Oracle 개발자 입장에서 사용하기...(8) (파워빌더 커뮤니티(PBExpertClub)) |작성자 또치

 

DYNAMIC SQL

 

PL/SQL은 Binding이 Compile시에 일어나므로 Database Object의 Name이 Compile시에 고정되어야 하는 등의 제한이 있다. PL/SQL 2.1(RDBMS 7.1)이후 Version에서는 DBMS_SQL Package로 Dynamic SQL Statement의 사용을 가능하게 한다. 이는 Database Object의 Name을 Runtime에 줄 수 있을 뿐더러 DDL문장을 기술할 수도 있는 장점이 있다.　　　

 

　* Function Open_Cursor

SQL문의 실행에 필요한 새로운 Cursor를 열고 Cursor ID Number를Return 한다.

 

　* Function Is_Open

주어진 Cursor가 현재 Open되어 있으면 TRUE를, 아니면 FALSE를 Return한다.　　　

 

　* Procedure Parse

Statement를 Check하고 Cursor와 결합시킨다.　　　

 

　* Procedure Bind_Variable

Program내에서 Data를 저장한 Placeholder의 값을 제공하는 역할을 한다.

 

　* Procedure Define_Column

Cursor로부터 Select된 Column의 값을 받는 변수를 지정한다.　　　

 

　* Function Execute

SQL문을 실행하고 처리된 Row의 수를 Return한다. (Insert, Update, Delete인 경우에만 해당)　　　

 

　* Function Fetch_Rows

Cursor로부터 Row를 Fetch하고 실제로 Fetch된 Row의 수를 Return한다. 이 Row들은 Buffer에

Column_Value를 호출하여 읽어들여야 한다.　　　　

 

　* Function Execute_And_Fetch

xecute와 Fetch Row를 동시에 수행하고 실제로 Fetch된 Row의 수를 Return한다.　　　

 

　* Procedure Variable_Value

주어진 변수의 값을 Return한다.　　　

 

　* Procedure Column_Value

Fetch_Rows에 의해 Fetch된 Data의 값을 Return한다.　　　

 

　* Procedure Close_Cursor

Cursor를 닫는다.　　　

 

< 사용 예 >　　　

* Table을 Create하는 Procedure로 Table Name, Column Name과 그Type을Parameter로 받는다.　　　

 

CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS　　　

　 cursor1 INTEGER;　　　

BEGIN　　　

　 cursor1 := dbms_sql.open_cursor;　　　

　 dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename ||　　　

　　　 ' ( ' || cols || ' )', dbms_sql.v7);　　　

　 dbms_sql.close_cursor(cursor1);　　　

end;　　　

/　　　

 

 

 

SQL> execute ddlproc ('MYTABLE','COL1 NUMBER, COL2 VARCHAR2(10)');　　　

PL/SQL procedure successfully completed.　　　

SQL> desc mytable;　　　

　COL1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 NUMBER　　　

　COL2　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 VARCHAR2(10)　　　

 

DDL Statement는 Parse Command에 의해 수행된다.

그러므로 DDL Statement에서는 Bind Variable을 사용할 수가 없다.　　　

 

* DDL Statemenet를 수행하는 Procedure로 DDL Statement자체를　 Parameter로 받는다.

 

create procedure anyddl (s1 varchar2) as　　　

　 cursor1 integer;　　　

begin　　　

　 cursor1 := dbms_sql.open_cursor;　　　

　 dbms_sql.parse(cursor1, s1, dbms_sql.v7);　　　

　 dbms_sql.close_cursor(cursor1);　　　

end;　　　

/　　　

 

SQL> execute anyddl('CREATE TABLE MYTABLE (COL1 NUMBER)');　　　

PL/SQL procedure successfully completed.　　　 　　　　

SQL> desc mytable;　　　

　COL1　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 NUMBER　　　

 

SQL> execute anyddl('drop table mytable');　　　 　　　　

PL/SQL procedure successfully completed.　　　

l Using the DBMS_SQL Package to Execute Dynamic SQL Statements:　　　

 

DBMS_SQL package는 dynamic SQL statement를 수행하는데 이용되어 질 수 있는데 이는 runtime전에 statement의 일부분 혹은 전체를 알 수 없는 경우에 쓰여진다.

[출처] [기초강좌] Oracle 개발자 입장에서 사용하기...(9) (파워빌더 커뮤니티(PBExpertClub)) |작성자 또치