1 Chapter. 6 Oracle SQL
1. SQL 문의종류 2 DDL (Data Definition Language) : 데이터와그구조를정의 SQL문 CREATE DROP ALTER 내용데이터베이스객체를생성데이터베이스객체를삭제기존에존재하는데이터베이스객체를다시정의하는역할 DML (Data Manipulation Language) : 데이터의검색과수정등의처리 SQL문 INSERT DELETE UPDATE SELECT 내용데이터베이스객체에데이터를입력데이터베이스객체에데이터를삭제기존에존재하는데이터베이스객체안의데이터수정데이터베이스객체로부터데이터를검색 DCL (Data Control Language) : 데이터베이스사용자의권한을제어 GRANT REVOKE SQL 문 내용 데이터베이스객체에권한을부여 이미부여된데이터베이스객체의권한을취소
2. 테이블의생성과수정그리고삭제 - 테이블의생성 3 테이블이란? 1. 테이블은오라클데이타베이스의기본적인데이타저장단위 2. 데이타베이스테이블은사용자가접근가능한모든데이타를보유하며레코드와컬럼으로구성 3. 테이블은시스템내에서독립적으로사용되길원하는엔티티를표현예를들면, 회사에서의고용자나제품에대한주문은테이블로표현가능 4. 테이블은두엔티티간의관계를표현즉테이블은고용자와그들의작업숙련도혹은제품과주문과의관계를표현하는데사용테이블내에있는외래키 (ForeIgn Key) 는두엔티티사이의관계를표현하는데사용 5. 비록 " 테이블 " 이라는말이더많이사용되지만테이블의형식어는 " 릴레이션 " 컬럼 : 테이블의각컬럼은엔티티의한속성을표현행 (ROW, 레코드 ) : 테이블의데이타는행에저장 테이블생성시제한사항과고려할점 - 테이블이름과컬럼은항상알파벳문자로시작해야하며 A~Z 까지의문자, 0~9 까지의숫자, 그리고 $,#,_(Under Bar) 를사용가능 ( 공백사용불가능 ) - 테이블의컬럼이름은 30 자를초과할수없고, 예약어를사용할수없음. - 오라클테이블한계정안에서테이블이름은다른테이블이름과달리유사해야함. - 한테이블안에서컬럼이름은같을수없으며다른테이블에서의컬럼이름과는같을수있음.
2. 테이블의생성과수정그리고삭제 - 테이블의생성 4 테이블생성문법 CREATE TABLE [schema.]table_name ( column datatype [, column datatype...] ) [TABLESPACE tablespace ] [ PCTFREE integer ] [ PCTUSED integer ] [ INITRANS integer ] [ MAXTRANS integer ] [ STORAGE storage-clause ] [ LOGGING NOLOGGING ] [ CACHE NOCACHE ] ; - schema : 테이블의소유자 - table_name: 테이블이름 - column: 컬럼의이름 - datatype: 컬럼의데이터유형 - TABLESPACE: 테이블이데이터를저장할테이블스페이스를지정 - PCTFREE : 블럭내에이미존재하고있는 Row 에 Update 가가능하도록예약시켜놓는블럭의퍼센트값을지정 - PCTUSED : 테이블데이터가저장될블록의행데이터부분의크기를퍼센트지로지정 PCTFREE 에의해지정된크기만큼 Block 이차면 PCTUSED 값보다작아져야새로운행삽입이가능 - INITRANS : 하나의데이터블록에지정될초기트랜잭션의값을지정 ( 기본값은 1) - MAXTRANS: 하나의데이터블록에지정될수있는트랜잭션최대수를지정 ( 기본값은 255) - STORAGE: 익스텐트스토리지에대한값을지정합니다. - LOGGING: 테이블에대해이후의모든작업이리두로그파일내에기록되도록지정 (default) - NOLOGGING: 리두로그파일에테이블의생성과특정유형의데이터로드를기록하지않도록지정 - CACHE : 전체테이블스캔 (full table scan) 이수행될때읽어들인블록이버퍼캐쉬내의 LRU 리스트의가장최근에사용된것의자리에위치하도록지정 - NOCACHE : 전체테이블스캔 (full table scan) 이수행될때읽어들인블록이버퍼캐쉬내의 LRU 리스트의가장최근에사용되지않은것의자리에위치하도록지정 * PCTFREE, PCTUSED 에대한자세한강좌는오라클어드민강좌를참고
2. 테이블의생성과수정그리고삭제 - 테이블의생성 5 테이블생성예제 DEPT2 테이블생성예제 SQL>CREATE TABLE DEPT2( DEPTNO NUMBER CONSTRAINT dept_pk_deptno PRIMARY KEY, - ( 컬럼 ) ( 데이터타입 ) ( 제약조건 ) DNAME VARCHAR2(40), LOC VARCHAR2(50)) PCTFREE 20 PCTUSED 50 ; 테이블이생성 테이블생성시주의사항 - 테이블이름을지정하고각컬럼들은괄호 "()" 로묶어지정 - 컬럼뒤에데이터타입은꼭지정되어야함. - 각컬럼들은콤마 "," 로구분되고, 항상끝은세미콜론 ";" 으로끝남. - 한테이블안에서컬럼이름은같을수없으며다른테이블에서의컬럼이름과는같을수있음. 유저가소유한모든테이블조회 USER_TABLES 데이터사전을조회하면유저가소유한테이블을확인할수있음. SQL>SELECT table_name FROM USER_TABLES; TABLE_NAME ------------ BONUS CRETABLE
2. 테이블의생성과수정그리고삭제 - 테이블의제약조건 6 제약조건이란? 제약조건이란테이블에부적절한자료가입력되는것을방지하기위해서여러가지규칙을적용해놓는것. 간단하게테이블안에서데이터의성격을정의하는것 - 데이터의무결성유지를위하여사용자가지정할수있는성질 - 모든 CONSTRAINT 는데이터사전 (DICTIONARY) 에저장 - 의미있는이름을부여했다면 CONSTRAINT 를쉽게참조할수있음. - 표준객체명명법을따르는것이좋다. - 제약조건은테이블을생성할당시에지정할수도있고, 테이블생성후구조변경 (ALTER) 명령어를통해서도추가가가능 - NOT NULL 제약조건은반드시컬럼레벨에서만정의가가능 제약조건 PRIMARY KEY(PK) FOREIGN KEY(FK) UNIQUE key(uk) NOT NULL(NN) CHECK(CK) 설 유일하게테이블의각행을식별 (NOT NULL 과 UNIQUE 조건을만족 ) 열과참조된열사이의외래키관계를적용하고설정합니다. 테이블의모든행을유일하게하는값을가진열 (NULL 을허용 ) 명 열은 NULL 값을포함할수없습니다. 참이어야하는조건을지정함 ( 대부분업무규칙을설정 )
2. 테이블의생성과수정그리고삭제 - 테이블의제약조건 7 NOT NULL 조건 : 컬럼을필수필드화시킬때사용 SQL>CREATE TABLE emp( ename VARCHAR2(20) CONSTRAINT emp_nn_ename NOT NULL ); 위와같이테이블을생성하면 ename 컬럼에는꼭데이터를입력해야만함. - 여기서 emp_nn_ename 은 ( 테이블이름 _ 제약조건이름 _ 컬럼이름 ) 형식으로 CONSTRAINT NAME 을정의 - CONSTRAINT NAME 은 USER_CONSTRAINTS 뷰 (VIEW) 를통해서확인 SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME ='EMP' ; CONSTRAINT_NAME ----------------------- emp_nn_ename à 이런식으로제약조건의이름을확인 UNIQUE 조건 UNIQUE 조건 : 데이터의유일성을보장 ( 중복되는데이터가존재할수없음.) 유니크조건을생성하면자동으로 index 가생성 SQL> ALTER TABLE emp ADD CONSTRAINT emp_uk_deptno UNIQUE (deptno) ; 테이블이변경 - 위와같이제약조건을생성하면 deptno 컬럼에중복된데이터가들어갈수없음. -- 제약조건의삭제 SQL>ALTER TABLE emp DROP CONSTRAINT emp_uk_deptno ; 테이블이변경
2. 테이블의생성과수정그리고삭제 - 테이블의제약조건 8 CHECK 조건 : 컬럼의값을어떤특정범위로제한 SQL>ALTER TABLE emp ADD CONSTRAINT emp_ck_comm CHECK (comm >= 10 AND comm <= 100000) ; 테이블이변경 - comm 컬럼은체크조건에서제한을하고있으므로 10 에서 100000 까지의값만을가질수있음. 또체크조건에서는 IN 연산자를사용 SQL>ALTER TABLE emp DROP CONSTRAINT emp_ck_comm ; 테이블이변경 -- 제약조건의삭제 SQL> ALTER TABLE emp -- IN 연산자를이용해서 CHECK 제약조건을생성 ADD CONSTRAINT emp_ck_comm CHECK (comm IN (10000,20000,30000,40000,50000)) ; 테이블이변경 - comm 컬럼은 10000,20000,30000,40000,50000 의값만을가질수있음. DEFAULT( 컬럼기본값 ) 지정 데이터입력시에입력을하지않아도지정된값이디폴트로입력 SQL>CREATE TABLE emp( hiredate DATE DEFAULT SYSDATE ) ; 위와같이디폴트값을설정하면 hiredate 컬럼에 INSERT 를하지않아도오늘날짜가들어감.
2. 테이블의생성과수정그리고삭제 - 테이블의제약조건 9 PRIMARY KEY( 기본키 ) 지정 PRIMARY KEY : Primary Key( 기본키 ) 는 UNIQUE 와 NOT NULL 의결합과같다. - 기본키는그데이터행을대표하는컬럼으로서의역할을수행하여다른테이블에서외래키들이참조할수있는키로서의자격을가지고있으며이를참조무결성이라함. - UNIQUE 조건과마찬가지로기본키를정의하면자동으로인덱스를생성하며그이름은기본키제약조건의이름과같다. SQL>CREATE TABLE emp( empno NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY ) ; 위와같이제약조건을설정하면 empno 컬럼에 UNIQUE 제약조건과 NOT NULL 제약조건을가지게된다. FOREIGN KEY( 외래키 ) 지정 FOREIGN KEY( 외래키 ) 지정 : 기본키를참조하는컬럼또는컬럼들의집합 - 외래키를가지는컬럼의데이터형은외뢰키가참조하는기본키의컬럼과데이터형이일치해야함. 이를어기면참조무결성제약에의해테이블을생성할수없음. - 외래키에의해참조되고있는기본키는삭제할수없음. - ON DELETE CASCADE 연산자와함께정의된외래키의데이터는그기본키가삭제될때같이삭제 SQL>ALTER TABLE emp ADD CONSTRAINT emp_fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) 테이블이변경 위와같이제약조건을생성하면 emp 테이블의 deptno 컬럼은 dept 테이블에 deptno 컬럼을참조하는외래키를가짐.
2. 테이블의생성과수정그리고삭제 - 오라클데이터타입 10 오라클테이블을생성할때각컬럼에지정할수있는데이터타입 DATA TYPE 설 명 VARCHAR2(n) 가변길이문자데이터 (1~4000byte) CHAR(n) 고정길이문자데이터 (1~2000byte) NUMBER(p,s) 전체 p자리중소수점이하 s자리 (p:1~38, s:-84~127) DATE 7Byte(BC 4712년 1월 1일부터 AD 9999년 12월 31일 ) LONG 가변길이문자데이터 (1~2Gbyte) CLOB 단일바이트가변길이문자데이터 (1~4Gbyte) RAW(n) n Byte의원시이진데이터 (1~2000) LONG RAW 가변길이원시이진데이터 (1~2Gbyte) BLOB 가변길이이진데이터 (1~4Gbyte) BFILE 가변길이외부파일에저장된이진데이터 (1~4Gbyte) 숫자데이터 (Numeric Data) - 오라클데이터베이스에서숫자는항상가변길이데이터로저장되며유효자릿수 38 자리까지저장.
2. 테이블의생성과수정그리고삭제 - 오라클데이터타입 11 문자데이터 (Character Data) - 문자데이터는데이터베이스에고정길이, 또는가변길이문자열로저장 - CHAR 와 NCHAR 같은고정길이문자데이터유형은고정길이까지공백으로채워서저장 - NCHAR 는고정폭 (fixed-width), 또는가변폭 (variable-width) character set 의저장을가능하게하는 NLS 데이터유형최대크기는한문자를저장하는데필요한바이트수에따라결정되며한행당최대 2000 바이트기본값은 charater set 에따라 1 문자, 또는 1 바이트 - 가변길이문자데이터유형은실제컬럼값을저장하는데필요한바이트만큼만을사용하며각행에따라크기가다양. VARCHAR2 와 NVARCHAR2 가있습니다. 날짜 (DATE) 데이터유형 - 오라클서버는날짜를 7 바이트, 고정길이필드 (field) 로저장 - 오라클 DATE 는항상시간을포함 RAW 데이터유형 - 크기가적은이진데이터의저장에사용
2. 테이블의생성과수정그리고삭제 - 오라클데이터타입 12 크기가큰오브젝트를저장하기위한데이터유형 1 크기가큰오브젝트를저장하기위한데이터유형에는 LONG 과 LONG RAW, LOB 데이터유형이있다. 2 LONG 데이터유형은 2GB 의문자열데이터를저장 3 오라클은 LOB 을저장하기위한여섯가지데이터유형을제공 - 큰고정폭 (fixed-width) 문자데이터를위한 CLOB 과 LONG - 큰고정폭국가 character set 데이터를위한 NCLOB - 구조화되지않은데이터를저장하기위한 BLOB 과 LONG RAW - 구조화되지않은데이터를운영체제파일에저장하기위한 BFILE LONG 과 LOB 데이터유형비교 LONG, LONG RAW 테이블에컬럼하나만생성할수있음 2GB SELECT결과로데이터를리턴데이터를직접저장오브젝트유형을지원하지않음 LOB 테이블에여러개의컬럼생성이가능 4GB SELECT결과로위치를리턴데이터를직접또는간접저장오브젝트유형지원
2. 테이블의생성과수정그리고삭제 - 테이블의관리 13 테이블컬럼의관리 테이블의컬럼은 ADD, MODIFY, DROP 연산자를통해서관리할수있음. ADD 연산자 ADD 연산자 : 테이블에새로운컬럼을추가할때사용 SQL>ALTER TABLE emp ADD (addr VARCHAR2(50)); VARCHAR2 의데이터형을가지는 addr 컬럼이 emp 테이블에추가 MODIFY 연산자 MODIFY 연산자 : 테이블의컬럼을수정하거나 NOT NULL 컬럼으로변경할수있음. SQL>ALTER TABLE emp MODIFY (ename VARCHAR2(50) NOT NULL) ; -> ename 컬럼이 VARCHAR2 50 자리로수정 컬럼이이미테이터를가지고있을경우다른데이터형으로변경불가능 DROP 연산자 컬럼의삭제예제 SQL>ALTER TABLE table_name DROP COLUMN column_name -- 컬럼의삭제는오라클 8i 버전부터지원 제약조건의삭제예제 -- CASCADE 연산자와함께사용하면외래키에의해참조되는기본키도삭제될수있음 SQL>ALTER TABLE emp DROP CONSTRAINT emp_pk_empno CASCADE;
2. 테이블의생성과수정그리고삭제 - 테이블의관리 14 테이블정보의관리 기존테이블의복사 - 기존테이블을부분, 또는완전히복사할때에서브쿼리를가진 CREATE TABLE 명령어를사용해서쉽게테이블을복사가능 - 하지만제약조건, 트리거, 그리고테이블권한은새로운테이블로복사되지않는다. - 제약조건은 NOT NULL 제약조건만복사 [ 문법 ] [ 예제 ] CREATE TABLE [schema.]table_name SQL>CREATE TABLE emp2 [ LOGGING NOLOGGING ] AS [... ] SELECT * FROM emp; AS 테이블이생성 subquery 테이블의테이블스페이스변경 Oracle8i 이전버전에서는 export 를해서다시 import 를해야지만테이블스페이스를변경할수있었지만오라클 8i 부터는 ALTER TABLE ~ MOVE TABLESPACE 명령어로쉽게테이블의테이블스페이스를변경할수있음. ALTER TABLE table_name MOVE TABLESPACE tablespace_name; SQL>ALTER TABLE emp MOVE TABLESPACE test; 테이블이변경 àemp 테이블의테이블스페이스를 test 로변경
2. 테이블의생성과수정그리고삭제 - 테이블의관리 15 테이블정보의변경 - 보통테이블의정보를변경하는이유는스토리지파라미터와블록활용파라미터를변경하기위해서사용 - 테이블정보의변경시 INITIAL 의값은변경할수없음. ALTER TABLE [schema.]table_name [ STORAGE storage-clause ] [ PCTFREE integer ] [ PCTUSED integer ] [ INITRANS integer ] [ MAXTRANS integer ] STORAGE-CLAUSE 에들어올수있는스토리지파라미터 - NEXT 다음번생성될익스텐트의크기를 Byte 단위로지정합니다. 이후, 익스텐트크기는 PCTINCREASE 만큼씩증가 - PCTINCREASE 마지막생성된익스텐트의바로다음에생성될익스텐트의증가율을퍼센트지로지정 - MINEXTENTS 최초생성되는익스텐트의수를지정 - MAXEXTENTS 생성될수있는최대익스텐트의수를지정 기타블록관련파라미터는테이블의생성강좌를참고
2. 테이블의생성과수정그리고삭제 - 테이블의관리 16 테이블의 TRUNCATE - 테이블을 Truncate 하면테이블의모든행이삭제되고사용된공간이해제 - TRUNCATE TABLE 은 DDL 명령이므로롤백데이터가생성되지않음. DELETE 명령으로데이터를지우면롤백명렁어로복구할수있지만 TRUNCATE 로데이터를삭제하면롤백을할수가없음. - 행의인덱스도같이잘려나감. - 외래키가참조중인테이블은 TRUNCATE 할수없음. - TRUNCATE 명령을사용하면삭제트리거가실행되지않음. TRUNCATE TABLE [schema.]table_name ; DROP TABLE ( 테이블을삭제할때사용 ) DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS] ; SQL>DROP TABLE emp ; SQL>DROP TABLE emp CASCADE CONSTRAINT; CASCADE CONSTRAINT è 외래키에의해참조되는기본키를포함한테이블일경우기본키를참조하던외래키조건도같이삭제
3. 데이터조작어 (DML) - 데이터의삽입, 수정, 삭제 17 INSERT INSERT 명령어는테이블안에데이터를삽입하는역할 INSERT INTO table_name(column1, column2, ) VALUES ( 데이터, 데이터, ) ; - 실제데이터는 VALUES 괄호 () 안에입력하고문자열은단일따옴표 (' ') 로둘러싼다. - 각각의테이터는 "," 로구분 - 테이블이름옆에 () 생략시에는모든컬럼을 VALUES() 안에입력 모든데이터를입력할경우 SQL>INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902, TO_DATE('80/12/17'), 800, NULL, 20); 원하는데이터만입력할경우 SQL>INSERT INTO DEPT (DEPTNO, DNAME) VALUES(10, 'ACCOUNTING' ); SELECT 문장을이용한 INSERT INSERT INTO table_name(column1, column2, ) SELECT column1, column2, FROM table_name WHERE 조건 ; SQL>INSERT INTO DEPT SELECT * FROM SCOTT.DEPT ;
3. 데이터조작어 (DML) - 데이터의삽입, 수정, 삭제 18 UPDATE UPDATE 명령어는테이블안의데이터를수정 UPDATE table_name SET column1 = 값 ( 고칠내용 ), column2 = 값, WHERE 조건 SQL>UPDATE EMP SET DEPTNO = 30 WHERE EMPNO = 7902 ; è 사원번호가 7902 번인사람의부서번호가 30 번으로수정됨 SQL>UPDATE EMP SET SAL = SAL * 1.1 WHERE DEPTNO = 20 ; è 20 부서의사원들의급여가 10% 인상됨 SQL>UPDATE EMP SET HIREDATE = SYSDATE è 모든사원의입사일이오늘로수정됨 DELETE DELETE 명령어는테이블안의데이터를삭제 DELETE FROM table_name WHERE 조건 ; SQL>DELETE FROM EMP WHERE EMPNO = 7902 ; è 사원번호가 7902 번인사람의데이터가삭제 SQL>DELETE FROM EMP WHERE SAL < (SELECT AVG(SAL) FROM EMP) ; è 평균급여보다적게받는사원삭제 SQL>DELETE FROM EMP ; è 테이블의모든행이삭제
3. 데이터조작어 (DML) - SELECT 문및연산자 19 SELECT 문 SELECT 문은데이터베이스로부터저장되어있는데이터를검색하는데사용 SELECT [DISTINCT] {*, column [alias],...} FROM table_name [WHERE condition] [ORDER BY {column, expression} [ASC DESC]]; - DISTINCT : 중복되는행을제거하는옵션 - * : 테이블의모든 column 을출력 - alias : 해당 column 에대해서다른이름을부여할때사용 - table_name : 질의대상테이블명 - WHERE : 조건을만족하는행들만검색 - condition : column, 표현식, 상수및비교연산자 - ORDER BY : 질의결과정렬을위한옵션 (ASC: 오름차순 (Default),DESC 내림차순 ) SQL 문의작성방법 - SQL 문장은대소문자를구별하지않음. - SQL 문장은한줄또는여러줄에입력 - 일반적으로키워드는대문자로입력. 다른모든단어, 즉테이블이름, 열이름은소문자로입력 ( 권장 ) - 가장최근의명령어가 1 개가 SQL buffer 에저장 - SQL 문마지막절의끝에 ";" 를기술하여명령의끝을표시 [ 예제 ] SQL>SELECT empno 사번, ename 성명 FROM emp WHERE deptno = 10
3. 데이터조작어 (DML) - SELECT 문및연산자 20 WHERE 절에올수있는연산자들. 연산자설명 BETWEEN a AND b a 와 b 사이의데이터를출력 (a, b 값포함 ) IN (list) list 의값중어느하나와일치하는데이터를출력 LIKE 문자형태로일치하는데이터를출력 (%, _ 사용 ) IS NULL NULL 값을가진데이터를출력 NOT BETWEEN a AND b a 와 b 사이에있지않은데이터를출력 (a, b 값포함하지않음 ) NOT IN (list) NOT LIKE NOT IS NULL IN 과 NOT IN 연산자 IN 연산자 list 의값과일치하지않는데이터를출력 문자형태와일치하지않는데이터를출력 NULL 값을갖지않는데이터를출력 SQL> SELECT empno, ename FROM emp WHERE empno IN (7900, 7934) ; --> 사번이 7900, 7934 번인사원의사번과성명출력 NOT IN 연산자 SQL> SELECT empno, ename FROM emp WHERE empno NOT IN (7900, 7934); --> 사번이 7900, 7934 번이아닌사원의사번과성명출력
3. 데이터조작어 (DML) - SELECT 문및연산자 21 BETWEEN 연산자 SQL> SELECT empno, ename FROM emp WHERE sal BETWEEN 3000 AND 5000 ; --> 급여가 3000 에서 5000 사이인사원만보여줌. BETWEEN 연산자 (AND 를이용해두조건을결합한검색과같은결과값을보여줌.) LIKE 연산자 - 검색 STRING 값에대한와일드카드검색을위해서 LIKE 연산자를사용 - % : 여러개의문자열을나타내는와일드카드 - _ : 단하나의문자를나타내는와일드카드 - ESCAPE : 와일드카드문자를일반문자처럼사용하고싶은경우에사용 WHERE name LIKE '%a\_y%' ESCAPE '\' ; - LIKE 연산자는대소문자를구분 - Upper() 함수를이용해대소문자구분없이출력 [ 예제 ] SQL>SELECT empno, ename FROM emp WHERE UPPER(ename) like '%K%'; 'K' 문자가들어있는사원정보를보여줌. upper() 라는함수는 k 가들어가있는것도대문자 'K' 로인식하기때문에데이터들을보여줌. '_' 를이용한 LIKE 검색 SQL>SELECT empno, ename FROM emp WHERE UPPER(ename) like '_I%' '_' 는한문자를나타냄. 'I' 문자가두번째문자에위치한사원들의정보를보여줌.
3. 데이터조작어 (DML) - 예명 (Alias) 22 테이블예명 (Alias) 테이블예명 (Alias) - 테이블 alias 로 column 을단순, 명확히할수있음. - 현재의 SELECT 문장에대해서만유효 - 테이블 alias 는길이가 30 자까지가능하나짧을수록더욱좋다. - 테이블 alias 는의미가있어야함. - FROM 절에테이블 alias 설정시해당테이블 alias 는 SELECT 문장에서테이블이름대신에사용 SQL> SELECT a.dname, b.cnt FROM dept a, (SELECT deptno, COUNT(empno) cnt FROM emp GROUP BY deptno) b WHERE a.deptno = b.deptno AND b.cnt > 3 DNAME CNT ----------------------------------- ---------- RESEARCH 6 SALES 6 사원수가 3 명이넘는부서의부서명과사원수를보여줌. 위쿼리에선총 3 개의 Alias 가사용첫번째로 DEPT 테이블을 a 라는예명을, 두번째로부서의사원수인 COUNT(empno) 를 cnt 라는예명을세번째로부서별사원수를가져오는쿼리를 b 라는예명을줌. 위예제와같이예명은컬럼에만주는것이아니라. 쿼리문및테이블에도사용할수있음.
3. 데이터조작어 (DML) - 조인 (Join) 23 조인 (Join)? 조인 (Join) 이란 - 둘이상의테이블을연결하여데이터를검색하는방법 - 보통둘이상의행들의공통된값 Primary Key 및 Foreign Key 값을사용하여조인 - 그러므로두개의테이블을 SELECT 문장안에서조인하려면적어도하나의컬럼이그두테이블사이에서공유되어야함. 조인방법 - Equijoin( 동등조인, 내부조인 ) - Non-equijoin - Outer join - Self join Cartesian Product( 카티션곱 ) 검색하고자했던데이터뿐아니라조인에사용된테이블들의모든데이터가 Retrun 되는현상 Cartesian product 는다음과같은경우에발생 - 조인조건을정의하지않았을경우 - 조인조건이잘못된경우 - 첫번째테이블의모든행들이두번째테이블의모든행과조인이되는경우 - 테이블의개수가 N 이라면 Cartesian product 를피하기위해서는적어도 N-1 개의등가조건을 SELECT 문안에포함시켜서다른테이블안에있는각테이블의컬럼이적어도한번은참조되도록해야함.
3. 데이터조작어 (DML) - 조인 (Join) 24 Equi Join Equi Join - 조건절 Equality Condition(=) 에의하여조인이이루어짐. - Equi join 의성능을높이기위해서는 Index 기능을사용하는것이좋다. SQL>SELECT e.ename, d.dname è WHERE 절에조인조건을작성하고 column 명앞에테이블명을적음. FROM emp e, dept d WHERE e.deptno = d.deptno; Non-Equijoin Non-Equijoin - Non-equijoin 은테이블의어떤 column 도 join 할테이블의 column 에일치하지않을때사용하고조인조건은동등 ( = ) 이외의연산자를갖는다. (BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN) SQL>SELECT e.ename, d.dname FROM emp e, dept d WHERE e.sal BETWEEN 3000 AND 4000; Self Join Self Join - Equi Join 과같으나하나의테이블에서조인이일어나는것이다름. - 같은테이블에대해두개의 alias 를작성함으로 FROM 절에두개의테이블을사용 SQL> SELECT concat(a.ename,' ') ' : \' b.sal 급여 FROM emp a, emp b WHERE a.empno = b.empno
3. 데이터조작어 (DML) - 조인 (Join) 25 Out( 외부 ) Join Out( 외부 ) Join - equijoin 문장들의한가지제약점은그것들이조인을생성하려하는두개의테이블의두개컬럼에서공통된값이없다면테이블로부터테이터를 Return 하지않는다. - 정상적으로조인조건을만족하지못하는행들을보기위해 outer join 을사용 Outer join 연산자 "( + )" - 조인시킬값이없는조인측에 "(+)" 를위치시킨다. - Outer join 연산자는표현식의한편에만올수있다. - Outer join 은 IN 연산자를사용할수없고 OR 연산자에의해다른하나의조건에연결될수없다. 예제 1) 일반조인의경우 SQL> SELECT DISTINCT(a.deptno), b.deptno FROM emp a, dept b WHERE a.deptno = b.deptno DEPTNO DEPTNO ---------- ---------- 10 10 20 20 30 30 예제 2) out join 을했을경우 SQL> SELECT DISTINCT(a.deptno), b.deptno FROM emp a, dept b WHERE a.deptno(+) = b.deptno DEPTNO DEPTNO ---------- ---------- 10 10 20 20 30 30 40
3. 데이터조작어 (DML) - 트랜잭션 (commit 과 rollback) 26 데이터베이스 TRANSACTION 데이터베이스 TRANSACTION 트랜잭션은데이터처리의한단위 오라클서버에서발생하는 SQL 문들을하나의논리적인작업단위로써성공하거나실패하는일련의 SQL 문 ORACLE SERVER 는 TRANSACTION 을근거로데이터의일관성을보증 TRANSACTION 은데이터를일관되게변경하는 DML 문장으로구성 (COMMIT, ROLLBACK, SAVEPOINT) 1 TRANSACTION 의시작 - 실행가능한 SQL 문장이제일처음실행될때 2 TRANSACTION 의종료 - COMMIT 이나 ROLLBACK - DDL 이나 DCL 문장의실행 ( 자동 COMMIT) - 기계장애또는시스템충돌 (crash) - deadlock 발생 - 사용자가정상종료 3 자동 COMMIT 은다음의경우발생 - DDL,DCL 문장이완료될때 - 명시적인 COMMIT 이나 ROLLBACK 없이 SQL*Plus 를정상종료했을경우 4 자동 ROLLBACK 은다음의경우발생 - SQL*Plus 를비정상종료했을경우 - 비정상적인종료, system failure
3. 데이터조작어 (DML) - 트랜잭션 (commit 과 rollback) 27 COMMIT 과 ROLLBACK COMMIT 과 ROLLBACK COMMIT : 변경사항저장 ROLLBACK : 변경사항취소 1 COMMIT 과 ROLLBACK 의장점 - 데이터의일관성을제공 - 데이터를영구적으로변경하기전에데이터변경을확인 - 관련된작업을논리적으로그룹화 - COMMIT, SAVEPOINT,ROLLBACK 문장으로 TRANSACTION 의논리를제어 2 COMMIT 이나 ROLLBACK 이전의데이터상태 - 데이터이전의상태로북구가가능 - 현재사용자는 SELECT 문장으로 DML 작업의결과를확인 - 다른사용자는 SELECT 문장으로현재사용자사용한 DML 문장의결과를확인할수없음. - 변경된행은 LOCK 이설정되어서다른사용자가변경할수없음. 3 COMMIT 이후의데이터상태 - 데이터베이스에데이터를영구적으로변경 - 데이터의이전상태는완전히상실 - 모든사용자가결과를볼수있다. - 변경된행의 LOCK 이해제되고다른사용자가변경할수있다. - 모든 SAVEPOINT 는제거
4. 내장함수 (Sing-Row Functions) - 숫자함수 (Number Functions) 28 ABS(n) : ABS함수는절대값을계산하는함수 CEIL(n) : CEIL함수는주어진값보다는크지만가장근접하는최소값을구하는함수 EXP(n) : EXP함수는주어진값의 e의승수. e = 2.171828182 FLOOR(n) : FLOOR함수는주어진값보다작거나같은최대정수값을구하는함수. (CEIL 함수와비교 ) LN(n) : LN함수는주어진값의자연로그값을반환 MOD(m, n) : MOD함수는 m을 n으로나누어남은값을반환. n이 0일경우 m을반환 POWER(m, n) : POWER함수는 m의 n승값을계산 ROUND(n, [m]) : ROUND함수는 n값의반올림을하는함수로 m은소숫점아래자릿수를나타냄. SQL>SELECT ROUND(192.123, 1) TEST FROM dual ; SQL>SELECT ROUND(192.123, -1) TEST FROM dual ; TEST TEST ------- ------- 192.1 190 SIGN(n) : SIGN함수는 n<0일경우 1을 N=0일경우 0을 N>0일경우 1을반환 SQRT(n) : SQRT함수는 n값의루트값을계산. n은양수 TRUNC(n, m) : TRUNC함수는 n값을 m 소숫점자리로반내림한값을반환. (ROUND 함수와비교 ) SQL>SELECT TRUNC(6.5597, 2) TEST FROM dual ; SQL>SELECT TRUNC (5253.26, -2 ) TEST FROM dual ; TEST TEST ------- ------- 6.55 5200
4. 내장함수 (Sing-Row Functions) - 문자열처리함수 (Character Functions) 29 CONCAT(char1, char2) CONCAT 함수는 Concatenation 의약자로두문자를결합하는역할. " " 연산자와같은역할을합니다. SQL>SELECT CONCAT('Oracle', ' Korea') NAME FROM dual ; NAME ------------- Oracle Korea INITCAP(char) : 주어진문자열의첫번째문자를대문자로변환 LOWER(char) : 문자열을소문자로변환 UPPER(char) : 문자열을대문자로변환 LPAD(char1, n [,char2]) : 왼쪽에문자열을끼어놓는역할 n 은반환되는문자열의전체길이를나타내며, char1 의문자열이 n 보다클경우 char1 을 n 개문자열만큼반환 SQL>SELECT LPAD('JUNG-SICK', 10, '*') NAME FROM dual ; NAME ------------ *JUNG-SICK RPAD(char1, n [,char2]) : LPAD 와반대로오른쪽에문자열을끼어놓는역할 SQL>SELECT RPAD('JUNG-SICK', 10, '*') NAME FROM dual ; NAME ------------ JUNG-SICK*
4. 내장함수 (Sing-Row Functions) - 문자열처리함수 (Character Functions) 30 SUBSTR(char, m,[n]) : SUBSTR 함수를이용하여 m 번째자리부터길이가 n 개인문자열을반환 m 이음수일경우에는뒤에서 M 번째문자부터반대방향으로 n 개의문자를반환 -- 뒤에서부터자를 SQL>SELECT SUBSTR('JUNG-SICK', 3, 3) NAME FROM dual ; SQL>SELECT SUBSTR('JUNG-SICK', -3, 3) NAME FROM dual ; NAME NAME ----------- ----------- NG- ICK LENGTH(char1) : 문자열의길이를리턴 SQL>SELECT LENGTH('JUNG-SICK') TEST FROM dual ; TEST ---------- 9 REPLACE(char1, str1, str2) : REPLACE 는문자열의특정문자를다른문자로변환 SQL> SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL; Changes ------------------------ BLACK and BLUE -- 대소문자를구분한다는것을알수있다. SQL>SELECT REPLACE('JACK and JUE','j','BL') "Changes" FROM DUAL Changes ---------------------- JACK and JUE
4. 내장함수 (Sing-Row Functions) - 문자열처리함수 (Character Functions) 31 INSTR : 문자열이포함되어있는지를조사하여문자열의위치를반환지정한문자열이발견되지않으면 0 이반환 -- 지정한문자 OK 가발견되지않아서 0 이반환 SQL>SELECT INSTR('CORPORATE FLOOR','OK') "Instring" FROM DUAL Instring ---------- 0 -- OR 이있는위치 2 를반환. 왼쪽부터비교를한다는것을알수있다. SQL>SELECT INSTR('CORPORATE FLOOR','OR') "Instring" FROM DUAL Instring ---------- 2 TRIM : 특정한문자를제거제거할문자를입력하지않으면기본적으로공백이제거. 리턴값의데이터타입은 VARCHAR2 -- 0 을제거 SQL>SELECT TRIM(0 FROM 0009872348900) "TRIM Example" FROM DUAL; TRIM Example ------------------- 98723489 -- 어떤문자도입력하지않으면기본적으로공백이제거 -- TRIM 을사용한위에예제와사용하지않은아래예제의결과값이다르게나오는것을알수있다. SQL>SELECT NVL(TRIM (' '),' 공백 ') "TRIM Example" FROM DUAL TRIM Example ------------ 공백
4. 내장함수 (Sing-Row Functions) - 날짜처리함수 (Date Functions) 32 LAST_DAY(d) : LAST_DAY 함수는달의마지막날의날짜를반환 SQL>SELECT SYSDATE TODAY, LAST_DAY(SYSDATE) LASTDAY FROM dual ; TODAY LASTDAY ---------------- ---------------- 05-JUN-2000 30-JUN-2000 ADD_MONTHS(a, b) : ADD_MONTHS 함수는 a 의날짜에 b 의달을더한값을반환 SQL>SELECT TO_CAHR(ADD_MONTHS(SYSDATE,3),'RRRR/MM/DD' LASTDAY) "date" FROM dual ; date -------------- 2000/09/05 MONTH_BETWEEN(a1, a2) : MONTH_BETWEEN 은 a1 과 a2 사이의달의수를 NUMBER 형타입으로반환 SQL>SELECT MONTHS_BETWEEN(TO_DATE('2000/06/05'), TO_DATE('2000/09/23')) "Date" FROM dual ; Date ---------------- -2.880635 ROUND(d[,F]) : ROUND 함수는 F 에지정된단위로반올림합니다, F 가연도라면연도단위로반올림 SQL>SELECT ROUND(TO_DATE('1998/09/11'), 'YEAR') FROM dual ; ROUND(TO_ -------------- 99-01-01
4. 내장함수 (Sing-Row Functions) - 변환함수 (Conversion Functions) 33 TO_CHAR : TO_CHAR 함수는 DATE 형, NUMBER 형을 VARCHAR2 형으로변환 SQL>SELECT TO_CHAR(SYSDATE, 'MONTH') CHARTEST FROM dual ; CHARTEST ---------------- JUNE 오늘이 6 월 10 일이라면,.. 오늘의달인 6 월이찍힘 TO_DATE : TO_DATE 함수는 CHAR, VARCHAR2 형을 DATE 타입으로변환 SQL>SELECT TO_DATE('2000/06/16','RRRR/MM/DD') FROM dual ; TO_DATE( --------------- 2000/06/16 '2000/06/16' 문자열이날짜형으로변환 TO_NUMBER : TO_NUMBER 함수는 CHAR, VARCHAR2 의데이터타입을숫자형식으로변환 SQL>SELECT TO_NUMBER('1210616') FROM dual ; TO_NUMBER( -------------------- 1210616 '1210616' 문자열이숫자형으로변환 * SQL Function 에대하여아래사이트참조. http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a90125/functions2.htm
4. 내장함수 (Sing-Row Functions) - 기타함수 (Miscellaneous Single-Row Functions) 34 NVL - NVL 함수는 NULL 값을다른값으로바꿀때쓰임. - 모든데이터타입에적용가능 - 전환되는값의데이터타입을일치시켜야함. SQL>SELECT empno, NVL(comm, 0) FROM emp WHERE deptno = 30; EMPNO NVL(COMM,0) ----------- ------------------- 7499 300 7654 1400 7698 0 7900 0 Commsion이없는사원에대해 0으로바꾸어서출력 DECODE - DECODE 함수는데이터들을다른값으로바꾸어줌 - 형식 DECODE(VALUE, IF1, THEN1, IF2, THEN2...) - VALUE 값이 IF1 일경우에 THEN1 값으로바꾸어주고 VALUE 값이 IF2 일경우에는 THEN2 값으로바꾸어줌 SQL> SELECT deptno, DECODE(deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 40, 'OPERATIONS') FROM emp ; DEPTNO DECODE(DEP ------------ ------------------- 20 RESEARCH 30 SALES 10 ACCOUNTING 부서가 10 번이면 'ACCOUNTING' 를 20 번이면 'RESEARCH' 를 30 번이면 'SALES' 를 40 번이면 'OPERATIONS' 를출력하는예제
4. 내장함수 (Sing-Row Functions) - 기타함수 (Miscellaneous Single-Row Functions) 35 DUMP : DUMP 는바이트크기와해당데이터타입코드를반환 SQL>SELECT ename, DUMP(ename, 16) "16 진수 " FROM emp WHERE ename = 'ALLEN' ename 16진수 --------- ------------------------------ ALLEN Typ=1 Len=5: 41,4c,4c,45,4e 만약에 16 대신 8 을넣으면 8 진수로, 10 를넣으면 10 진수로변환 16, 10, 8, 17 이올수있는데, 17 은단일문자열을반환함. Len 은 ename 의해당 byte 수 GREATEST : GREATEST 함수는검색값중에서가장큰값을반환 SQL>SELECT GREATEST(10, 100, 5, -7) FROM DUAL; GREATEST(10,100,5,-7) --------------------------------- 100 -- 가장큰수 : 100 LEAST : LEAST 함수는 GREATEST 함수와반대로가장작은값을반환 SQL>SELECT LEAST(10, 100, 5, -7) FROM DUAL; LEAST(10,100,5,-7) ------------------------- -7
4. 내장함수 (Sing-Row Functions) - 기타함수 (Miscellaneous Single-Row Functions) 36 UID : 현재사용자의유일한 ID 번호를반환 USER : 현재오라클을사용하는사용자를 VARCHAR2 형식으로반환 SQL> SELECT USER, UID FROM DUAL; USER UID ------------- ------ SCOTT 32 USERENV : USERENV 함수는현재세션의환경정보를반환 - ENTRYID : 사용가능한 Auditing entry Identifier 를반환 - LABEL : 현재세션의 Label 을반환 - LANGUAGE : 현재세션에서사용중인언어와테리토리값을반환 - SESSIONID : Auditing( 감사 ) Session ID 를반환 - TERMINAL : 현재세션터미널의 OS ID 를반환 SQL> SELECT USERENV('LANGUAGE') FROM DUAL; USERENV('LANGUAGE') --------------------------------------------- KOREAN_KOREA.KO16KSC5601 VSIZE : 해당문자의 BYTE 수를반환합니다. 해당문자가 NULL 이면 NULL 값이반환 SQL> SELECT VSIZE(ename), ename FROM emp WHERE deptno = 30; VSIZE(ENAME) ENAME --------------------- ---------- 5 ALLEN 4 WARD 6 MARTIN
5. 그룹함수 (Group Functions) - Group Function 의종류 37 그룹함수란? - 그룹함수란여러행또는테이블전체의행에대해함수가적용되어하나의결과값을가져오는함수 - GROUP BY 절을이용하여그룹당하나의결과가주어지도록그룹화할수있다. - HAVING 절을사용하여그룹함수를가지고조건비교를할수있다. - COUNT(*) 를제외한모든그룹함수는 NULL 값을고려하지않는다. - MIN, MAX 그룹함수는모든자료형에대해서사용할수있다. 그룹함수의종류 COUNT : COUNT 함수는검색된행의수를반환 SQL>SELECT COUNT(deptno) FROM DEPT ; COUNT(DEPTNO) ------------------------ 4 ==> 검색된행의총수 4 개를반환. 즉 4 개의부서가존재 MAX : MAX 함수는컬럼중의최대값을반환 SQL>SELECT MAX(sal) salary FROM emp ; SALARY ------------- 5000 ==> sal 컬럼중에서제일큰값을반환. 즉가장큰급여를반환
5. 그룹함수 (Group Functions) - Group Function 의종류 38 MIN : MIN 함수는컬럼중의최소값을반환 SQL>SELECT MIN(sal) salary FROM emp ; SALARY ------------ 800 ==> sal 컬럼중에서가장작은값반환. 즉가장적은급여를반환 AVG : AVG 함수는평균값을반환 SQL>SELECT ROUND(AVG(sal),1) salary FROM emp WHERE deptno = 30 SALARY ------------- 1565.7 ==> 30 부서사원의평균급여를소수점 1 자리이하에서반올림 SUM : SUM 함수는검색된컬럼의합을반환 SQL>SELECT SUM(sal) salary FROM emp WHERE deptno = 30; SALARY ----------- 9400 ==> 30 부서사원의급여합계를보여줌 STDDEV : STDDEV 함수는표준편차를반환 SQL> SELECT ROUND(STDDEV(sal),3) salary FROM emp WHERE deptno = 30 ; SALARY ----------- 667.331 ==> 30 부서사원의급여표준편차를반환
5. 그룹함수 (Group Functions) - Group By 절과 Having 절 39 GROUP BY? - 특정한컬럼의테이터들을다른데이터들과비교해유일한값에따라무리를지음 - GROUP BY 절을사용하여한테이블의행들을원하는그룹으로나눔 - Column 명을 GROUP 함수와 SELECT 절에사용하고자하는경우 GROUP BY 뒤에 Column 명을추가 GROUP BY 예제 예제1) 부서별로그룹을지은검색결과값이며부서별로사원수를보여줌 SQL>SELECT b.deptno, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY b.deptno DEPTNO COUNT(*) ------------ ------------- 10 3 20 5 30 6 예제 2) 업무별로그룹하여업무, 인원수, 평균급여액, 최고급여액, 최저급여액및합계를출력하라. SQL>SELECT job, COUNT(empno) " 인원수 ", AVG(sal) " 평균급여액 ", MAX(sal) " 최고급여액 ", MIN(sal) " 최저급여액 ", SUM(sal) " 급여합계 " FROM emp GROUP BY job JOB 인원수평균급여액최고급여액최저급여액급여합계 -------- ---------- ---------------- ----------------- ---------------- ------------- ANALYST 2 3000 3000 3000 6000 CLERK 4 1036.5 1300 800 4150 MANAGER 3 2840 2975 2695 8520 PRESIDENT 1 5000 5000 5000 5000 SALESMAN 4 1400 1600 1250 5600
5. 그룹함수 (Group Functions) - Group By 절과 Having 절 40 GROUP BY 의 HAVING 절 - WHERE 절에 GROUP Function 을사용할수없음 - HAVING 절은 GRUOP 함수를가지고조건비교를할때사용 - WHERE GRUOP BY HAVING ORDER BY 순으로쿼리문이와야됨. 예제 1) 사원수가 5 명이넘는부서의부서명과사원수를출력해라 SQL>SELECT b.dname, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY dname HAVING COUNT(a.empno) > 5 DNAME COUNT(A.EMPNO) ------------------------- ------------------------- RESEARCH 6 SALES 6 예제2) 전체월급이 5000을초과하는각업무에대해서업무와월급여합계를출력하여라. 단판매원은제외하고월급여합계로내림차순정렬하여라. SQL>SELECT job, SUM(sal) " 급여합계 " FROM emp WHERE job NOT IN ('SALES') -- 판매원은제외 GROUP BY job -- 업무별로 Group By HAVING SUM(sal) > 5000 -- 전체월급이 5000을초과하는 ORDER BY SUM(sal) DESC; -- 월급여합계로내림차순정렬 JOB 급여합계 --------------- ---------- MANAGER 8520 ANALYST 6000 SALESMAN 5600
6. 서브쿼리 (Subquery) - 서브쿼리 (Subquery) 란? 41 Subquery 란? SUBQUERY는다른하나의 SQL 문장의절에 NESTEDED된 SELECT 문장 SELECT, UPDATE, DELETE, INSERT와같은 DML문과 CREATE TABLE 또는 VIEW에서이용될수있음 알려지지않은조건에근거한값들을검색하는 SELECT 문장을작성하는데유용 SUBQUERY는 MAIN QUERY가실행되기이전에한번실행 Guidelines SUBQUERY는괄호로묶어야함. SUBQUERY는연산자의오른쪽에나타냄 두종류의비교연산자들이 SUBQUERY에사용 - 단일행연산자 (=,>, >=, <, <=, <>,!=) - 복수행연산자 (IN, NOT IN, ANY, ALL, EXISTS) Guidelines 단일행 (Sing-Row) 서브쿼리 : SELECT문장으로부터오직하나의행만을검색하는질의 다중행 (Multiple-Row) 서브쿼리 : SELECT문장으로부터하나이상의행을검색하는질의 다중열 (Multiple-Column) 서브쿼리 : SELECT문장으로부터하나이상의컬럼을검색하는질의 FROM절상의서브쿼리 (INLINE VIEW) : FROM절상에오는서브쿼리로 VIEW처럼작용 상관관계서브쿼리 : 바깥쪽쿼리의컬럼중의하나가안쪽서브쿼리의조건에이용되는처리방식
6. 서브쿼리 (Subquery) - 단일행 (Sing-Row) 서브쿼리 42 단일행 (Sing-Row) 서브쿼리란? 오직한개의행 ( 값 ) 을반환 (Return only one row) 단일행연산자 (=,>, >=, <, <=, <>,!=) 만사용 SQL> SELECT ename,job FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7369); 위의서브쿼리를실행시키면. 오른쪽의그림처럼. empno 가 7369 인 job 을먼저검색하고.. job 이 'CLERK' 인사원의이름과직업을반환
6. 서브쿼리 (Subquery) - 다중행 (Multiple-Row) 서브쿼리 43 다중행 (Multiple-Row) 서브쿼리란? 하나이상의행을 RETURN 하는 SUBQUERY 를다중행 SUBQUERY 복수행연산자 (IN, NOT IN, ANY, ALL, EXISTS) 를사용 IN 연산자의사용예제 예제 ) 부서별로가장급여를많이받는사원의정보를출력하는예제 SQL>SELECT empno,ename,sal,deptno FROM emp WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno); EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7698 BLAKE 2850 30 7788 SCOTT 3000 20 7902 FORD 3000 20 7839 KING 5000 10 ANY 연산자의사용예제 - ANY 연산자는서브쿼리의결과값중어느하나의값이라도만족이되면결과값을반환 SQL>SELECT ename, sal FROM emp WHERE deptno!= 20 AND sal > ANY(SELECT sal FROM emp WHERE job='salesman'); ENAME SAL ---------- ---------- ALLEN 1600 BLAKE 2850 CLARK 2450 KING 5000 TURNER 1500 MILLER 1300 6 개의행이선택
6. 서브쿼리 (Subquery) - 다중행 (Multiple-Row) 서브쿼리 44 ALL 연산자의사용예제 - ALL 연산자는서브쿼리의결과값중모든결과값이만족되야만결과값을반환 SQL>SELECT ename, sal FROM emp WHERE deptno!= 20 AND sal > ALL(SELECT sal FROM emp WHERE job='salesman'); ENAME SAL ---------- ---------- BLAKE 2850 CLARK 2450 KING 5000 3 개의행이선택 EXISTS 연산자의사용예제 - EXISTS 연산자를사용하면서브쿼리의데이터가존재하는가의여부를먼저따져존재하는값들만을결과로반환 - SUBQUERY 에서적어도 1 개의행을 RETURN 하면논리식은참이고그렇지않으면거짓 예제 ) 사원을관리할수있는사원의정보를보여줌 SELECT empno, ename, sal FROM emp e WHERE EXISTS (SELECT empno FROM emp WHERE e.empno = mgr) EMPNO ENAME SAL ---------- ---------- ---------- 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7902 FORD 3000 6 개의행이선택
6. 서브쿼리 (Subquery) - 다중열 (Multiple-Column) 서브쿼리 45 다중열 (Multiple-Column) 서브쿼리란? 다중열서브쿼리란서브쿼리의결과값이두개이상의컬럼을반환하는서브쿼리 Pairwise( 쌍비교 ) Subquery 서브쿼리가한번실행되면서모든조건을검색해서주쿼리로넘겨줌 ex) SELECT empno, sal, deptno FROM emp WHERE (sal, deptno) IN ( SELECT sal, deptno FROM emp WHERE deptno = 30 AND comm is NOT NULL ); EMPNO SAL DEPTNO ---------- ---------- ---------- 7521 1250 30 7654 1250 30 7844 1500 30 7499 1600 30 Null Values in a Subquery 서브쿼리에서 null 값이반환되면주쿼리에서는어떠한행도반환되지않음. Nonpairwise( 비쌍비교 ) Subquery 서브쿼리가여러조건별로사용되어서결과값을주쿼리로넘겨줌 ex) SELECT empno, sal, deptno FROM emp WHERE sal IN ( SELECT sal FROM emp WHERE deptno = 30 AND comm is NOT NULL ) AND deptno IN ( SELECT deptno FROM emp WHERE deptno = 30 AND comm is NOT NULL ); EMPNO SAL DEPTNO ---------- ---------- ---------- 7521 1250 30 7654 1250 30 7844 1500 30 7499 1600 30
6. 서브쿼리 (Subquery) - FROM 절상의서브쿼리 (INLINE VIEW) 와상관관계서브쿼리 46 FROM 절상의서브쿼리 (INLINE VIEW) 란? SUBQUERY 는 FROM 절에서도사용이가능 INLINE VIEW 란 FROM 절상에오는서브쿼리로 VIEW 처럼작용 예제 ) 급여가 20 부서의평균급여보다크고사원을관리하는사원으로서 20 부서에속하지않은사원의정보를보여주는 SQL 문 SELECT b.empno,b.ename,b.job,b.sal, b.deptno FROM (SELECT empno FROM emp WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno = 20)) a, emp b WHERE a.empno = b.empno AND b.mgr is NOT NULL AND b.deptno!= 20 EMPNO ENAME JOB SAL DEPTNO ---------- ---------- --------------- ---------- ---------- 7698 BLAKE MANAGER 2850 30 7782 CLARK MANAGER 2450 10 상관관계서브쿼리 상관관계서브쿼리란바깥쪽쿼리의컬럼중하나가안쪽서브쿼리의조건에이용되는처리방식 이는주쿼리에서서브쿼리를참조하고이값을다시주쿼리로반환 예제 ) 사원을관리할수있는사원의평균급여보다급여를많이받는사원의정보를출력 SELECT empno, ename, sal FROM emp e WHERE sal > (SELECT AVG(sal) sal FROM emp WHERE e.empno = mgr) EMPNO ENAME SAL ---------- ---------- ---------- 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7902 FORD 3000
6. 서브쿼리 (Subquery) - 집합쿼리 (UNION, INTERSECT, MINUS) 47 집합쿼리 (UNION, INTERSECT, MINUS) 집합연산자를사용시집합을구성할컬러의데이터타입이동일해야함 UNION : 합집합 UNION ALL: 공통원소두번씩다포함한합집합 INTERSECT: 교집합 MINUS: 차집합 UNION UNION 은두테이블의결합을나타내며, 결합시키는두테이블의중복되지않은값들을반환 SQL>SELECT deptno FROM emp UNION SELECT deptno FROM dept; DEPTNO ------------ 10 20 30 40 UNION ALL UNION 과같으나두테이블의중복되는값까지반환 SQL>SELECT deptno FROM emp UNION ALL SELECT deptno FROM dept; DEPTNO ------------ 20 30 30 20 10 20 10 30...
6. 서브쿼리 (Subquery) - 집합쿼리 (UNION, INTERSECT, MINUS) 48 INTERSECT INTERSECT 는두행의집합중공통된행을반환 SQL>SELECT deptno FROM emp INTERSECT SELECT deptno FROM dept; DEPTNO ------------ 10 20 30 MINUS MINUS 는첫번째 SELECT 문에의해반환되는행중에서두번째 SELECT 문에의해반환되는행에존재하지않는행들을보여줌 SQL>SELECT deptno FROM dept MINUS SELECT deptno FROM emp; DEPTNO ------------ 40
SQL 연습문제 -1 49 lives(person-name, street, city) works(person-name, company-name, salary) located-in(company-name, city) manages(person-name, manager-name) 문제 1. First bank 에근무하는모든사람에이름을찾아라 답 : select person-name from work where company-name ="First Bank Corporation"
SQL 연습문제 -2 50 lives(person-name, street, city) works(person-name, company-name, salary) located-in(company-name, city) manages(person-name, manager-name) 문제 2. First bank 에근무하는모든사람의이름과도시를찾아라 답 : select l.person-name, l.city from lives l, works w where w.company-name = "First Bank Corporation" and w.person-name = l.person-name
SQL 연습문제 -3 51 lives(person-name, street, city) works(person-name, company-name, salary) located-in(company-name, city) manages(person-name, manager-name) 문제 3. First bank 에근무하고 10,000$ 이상의연봉을받는근로자의이름, 거리및도시를찾아라 답 : select * from lives where person-name in (select person-name from works where company-name = "First Bank" and salary > 10,000
SQL 연습문제 -4 52 lives(person-name, street, city) works(person-name, company-name, salary) located-in(company-name, city) manages(person-name, manager-name) 문제 4. 자기가근무하는회사와같은도시에사는모든근로자를찾아라 답 : select l.person-name from lives l, works w, located-in i where l.person-name = w.person-name and l.city = i.city and w.company-name = i.company-name
SQL 연습문제 -5 53 lives(person-name, street, city) works(person-name, company-name, salary) located-in(company-name, city) manages(person-name, manager-name) 문제 5. 자기의관리자와같은도시, 같은거리에사는모든사람을찾아라 답 : select P.person-name from lives P, lives R, manages M where P.person-name = M.person-name and M.manager-name = R.person-name and P.street = R.street and P.city = R.city
SQL 연습문제 -6 54 lives(person-name, street, city) works(person-name, company-name, salary) located-in(company-name, city) manages(person-name, manager-name) 문제 6. First bank 에서일하지않는모든사람을찾아라 답 : select person-name from works where company-name!= "First Bank"
SQL 연습문제 -7 55 lives(person-name, street, city) works(person-name, company-name, salary) located-in(company-name, city) manages(person-name, manager-name) 문제 7. Small bank 의모든근로자보다연봉이많은사람을찾아라 답 : select person-name from works where salary > all (select salary from works where company-name = "Small Bank Corporation")
7. 데이터사전 (Data Dictionary)- 데이터사전 (Data Dictionary) 56 데이터사전 (Data Dictionary) 이란? 데이터사전 (Data Dictionary) 이란읽기전용테이블및뷰들의집합으로데이터베이스전반에대한정보를제공 데이터사전에저장되는내용 - 오라클의사용자이름 - 오라클권한과롤 - 데이터베이스스키마객체 (Table, View, index, cluster, Synonym, Sequence..) 이름과정의들 - 무결성제약조건에관한정보 - 데이터베이스의구조정보 - 오라클데이터베이스의함수와프로지저및트리거에대한정보 - 기타일반적인 DataBase 정보 Oracle 사용자 SYS 는데이타디셔널리의모든기본 Table 과 View 를소유 Oracle 은 DDL 명령이실행될때마다 Data Dictionary 를 Access 모든 Oracle 사용자는 DB 정보에대한읽기전용참조로 Data Dictionary 사용할수있음. DB 작업동안 Oracle 은 Data Dictionary 를읽어개체의존재여부와사용자에게적합한 Access 권한이있는지를확인. 또한 Oracle 은 Data Dictionary 를계속갱신하여 DataBase 구조, 감사, 사용자권한, 데이터등의변경사항을반영 데이터사전테이블은아래의스크립트를실행시켜서도생성 - @$ORACLE_HOME/rdbms/admin/sql.bsq ==> 기본데이터사전테이블생성스크립트 - @$ORACLE_HOME/rdbms/admin/catalog.sql ==> 자주사용되는데이터사전뷰생성스크립트
7. 데이터사전 (Data Dictionary)- 데이터사전 (Data Dictionary) 57 User_XXXX View 한특정사용자에게종속되어있고, 그사용자가조회가능한데이터사전뷰 ALL_XXXX View 의모든정보의부분집합 Public Synonym 을가질수있다. 아래의예는 Scott 사용자의 Table 조회결과 SQL> SELECT table_name, tablespace_name FROM USER_TABLES; TABLE_NAME TABLESPACE_NAME ------------- ------------------ BONUS TOOLS DEPT TOOLS DUMMY TOOLS ALL_XXXX View 한특정사용자가조회가능한모든데이터사전뷰를의미 자신이조회하려는객체의주인이아니더라도그객체에접근할수있는권한을가지고있다면 ALL_XXX 뷰를통하여조회 SQL> SELECT table_name, tablespace_name FROM ALL_TABLES; DUAL SYSTEM SYSTEM_PRIVILEGE_MAP TABLE_PRIVILEGE_MAP DEPT TEST DUMMY TEST... SYSTEM SYSTEM
7. 데이터사전 (Data Dictionary)- 데이터사전 (Data Dictionary) 58 DBA_XXXX View DBA 권한을가진사용자만이조회할수있는데이터사전으로서모든오라클데이터베이스객체에대한정보를보여줌. SELECT ANY TABLE 권한이있는사용자또한질의가가능 이러한 View 에대한동의어는생성되지않으며, 다른사용자가질의하려면앞에 sys. 이라는접두어를붙여야함. ex) SELECT OWNER, OBJECT_NAME FROM SYS.DBA_OBJECTS;
8. 오라클객체 - 인덱스 (Index) 59 인덱스란? 인덱스는테이블이나클러스트에서쓰여지는선택적인객체로서, 오라클데이터베이스테이블내의원하는레코드를빠르게찾아갈수있도록만들어진데이터구조 자동인덱스 : 프라이머리키또는 uinque 제한규칙에의해자동적으로생성되는인덱스 수동인덱스 : CREATE INDEX 명령을실행해서만드는인덱스 Index 를생성하는것이좋은 Column 1 WHERE 절이나 join 조건안에서자주사용되는컬럼 2 null 값이많이포함되어있는컬럼 3 WHERE 절이나 join 조건에서자주사용되는두개이상의컬럼들 index 생성이불필요한경우 1 table 이작을때 3 테이블이자주갱신될때 오라클인덱스는 B-tree(binary search tree) 에대한원리를기반 B-tree 인덱스는컬럼안에독특한데이터가많을때가장좋은효과 이알고리즘원리는 1 주어진값을리스트의중간점에있는값과비교만약그값이더크면리스트의아래쪽반을버리고, 그값이더작다면위쪽반을버린다. 2 하나의값이발견될때까지또는리스트가끝날때까지그와같은작업을다른반쪽에도반복
8. 오라클객체 - 인덱스 (Index) 60 1 Bitmap 인덱스 비트맵인덱스는각컬럼에대해적은개수의독특한값이있을경우에가장잘작동그러므로비트맵인덱스는 B-tree 인덱스가사용되지않을경우에서성능을향상 테이블이매우크거나수정 / 변경이잘일어나지않는경우에사용예를들어여권기록을포함하고있는테이블의성별열이나결혼여부열의경우에는 B- 트리인덱스보다는비트맵인덱스가더유리함. 질의문이 OR 연산자를포함하는여러개의 WHERE 조건을자주사용할때유리함. SQL>CREATE BITMAP INDEX emp_deptno_indx ON emp(deptno); 2 Unique 인덱스 Unique 인덱스는인덱스를사용한컬럼의중복값들을포함하지않고사용할수있는장점이있다. 프라이머리키와 Unique 제약조건시생성되는인덱스는 Unique 인덱스 SQL>CREATE UNIQUE INDEX emp_ename_indx ON emp(ename); 3 Non-Unique 인덱스 Non-Unique 인덱스는인덱스를사용한컬럼에중복데이터값을가질수있다. SQL>CREATE INDEX dept_dname_indx ON dept(dname);
8. 오라클객체 - 인덱스 (Index) 61 4 결합 (Concatenated(=Composite)) 인덱스 복수개의컬럼에생성할수있으며복수키인덱스가가질수있는최대컬럼값은 16 개 SQL>CREATE UNIQUE INDEX emp_empno_ename_indx ON emp(empno, ename); 인덱스의삭제 인덱스의구조는테이블과독립적이므로인덱스의삭제는테이블의데이터에는아무런영향도미치지않는다. 인덱스를삭제하려면 INDEX 의소유자이거나 DROP ANY INDEX 권한을가지고있어야함 INDEX 는 ALTER 를할수없다. SQL>DROP INDEX emp_empno_ename_indx ; 인덱스에대한정보는 USER_INDEXES 뷰또는 USER_IND_COLUMNS 뷰를통해검색할수있다. SQL> SELECT index_name, index_type FROM USER_INDEXES WHERE table_name='emp'; INDEX_NAME INDEX_TYPE --------------------------- ----------- EMP_DEPTNO_INDX BITMAP EMP_PK_EMPNO NORMAL
8. 오라클객체 - VIEW 테이블 62 뷰란? 뷰 : 하나의가상테이블 뷰는실제데이터가저장되는것은아니지만뷰를통해데이터를관리할수있다. 뷰는복잡한query를통해얻을수있는결과를간단한 query를써서구할수있게한다. 한개의뷰로여러테이블에대한데이터를검색 특정평가기준에따른사용자별로다른데이터를액세스할수있도록함 뷰의제한조건 테이블에 NOT NULL 로만든컬럼들이. 뷰에다포함이되있어야한다. 그리고 ROWID, ROWNUM, NEXTVAL, CURRVAL 등과같은가상컬럼에대한참조를포함하고있는뷰에는어떤데이터도 Insert 할수없다. WITH READ ONLY 옵션을설정한뷰도데이터를갱신할수없다. WITH CHECK OPTION 을설정한뷰는뷰의조건에해당되는데이터만삽입, 삭제, 수정이가능
8. 오라클객체 - VIEW 테이블 63 뷰생성문법 CTEATE [OR REPLACE] [FORCE NOFORCE] VIEW view_name AS subquery [WITH CHECK OPTION [CONSTRAINT 제약조건 ]] [WITH READ ONLY] - FORCE : 기본테이블유무에관계없이 VIEW 를생성 - WITH CHECK OPTION : VIEW 에의해엑세스될수있는행만이입력되거나변경될수있음을지정 - WITH READ ONLY : SELECT 만가능한 VIEW 의생성 - 함수를사용한컬럼은반드시 ALIAS 를지정 SQL> CREATE OR REPLACE VIEW Name_Query AS SELECT a.ename, b.dname FROM emp a, dept b WHERE a.deptno = b.deptno AND b.deptno = 20 이렇게뷰를생성해놓고뷰를통해검색 SQL>SELECT * FROM Name_Query; ENAME DNAME -------------------- ---------------- SMITH RESEARCH JONES RESEARCH
8. 오라클객체 - VIEW 테이블 64 WITH CHECK OPTION view 의조건식을만족하는데이터만 INSERT 또는 UPDATE 가가능하도록하는옵션 SQL> CREATE OR REPLACE VIEW Check_Option AS SELECT empno, ename, deptno FROM emp WHERE deptno = 10 WITH CHECK OPTION SQL> INSERT INTO Check_Option(empno, ename, deptno) VALUES (10005, 'jain', 30); INSERT INTO Check_Option(empno, ename, deptno) * 1 행에오류 : ORA-01402: 뷰의 WITH CHECK OPTION 의조건에위배부서번호가 10 인사원만 INSERT, UPDATE WITH READ ONLY SELECT 만가능한 VIEW 를생성 SQL> CREATE OR REPLACE VIEW Read_Only AS SELECT empno, ename, deptno FROM emp WHERE deptno = 10 WITH READ ONLY view created. 단순히읽기만할수있고데이터는입력은할수없음 뷰의정보조회 : USER_VIEWS 데이터사전을통해서뷰에대한정보를조회할수있다. SQL>SELECT view_name, text FROM USER_VIEWS; 뷰의삭제 SQL>DROP VIEW Read_Only; view dropped.
8. 오라클객체 - SEQUENCE( 시퀀스 ) 65 시퀀스란? 유일 (UNIQUE) 한값을생성해주는오라클객체 시퀀스를생성하면기본키와같이순차적으로증가하는컬럼을자동적으로생성 보통 primary key 값을생성하기위해사용 메모리에 Cache 되었을때 Sequence 값의액세스효율이증가 Sequence 는테이블과독립적으로저장되고생성. 따라서하나의 sequence 를여러테이블에서쓸수있다. 시퀀스생성문법 CREATE SEQUENCE sequence_name [START WITH n] [INCREMENT BY n] [MAXVALUE n NOMAXVALUE] [MINVALUE n NOMINVALUE] [CYCLE NOCYCLE] START WITH : 시퀀스의시작값을지정합니다. n 을 1 로지정하면 1 부터순차적으로시퀀스번호가증가 INCREMENT BY : 시퀀스의증가값을말합니다. n 을 2 로하면 2 씩증가 START WITH 를 1 로하고 INCREMENT BY 를 2 으로하면 1, 3,5,7,.. 이렇게시퀀스번호가증가 MAXVALUE n NOMAXVALUE : MAXVALUE 는시퀀스가증가할수있는최대값 NOMAXVALUE 는시퀀스의값을무한대로지정 MINVALUE n NOMINVALUE : MINVALUE 는시퀀스의최소값을지정기본값은 1 이며, NOMINVALUE 를지정할경우최소값은무한대
8. 오라클객체 - SEQUENCE( 시퀀스 ) 66 SQL>CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 MAXVALUE 100000 ; è 시작값이 1 일고 1 씩증가하고, 최대값이 100000 만이되는시퀀스를생성 SQL>INSERT INTO emp(empno, ename, hiredate ) VALUES(emp_seq.NEXTVAL, 'julia', sysdate); è empno 는컬럼값을입력할때일일이다음값을기억하지않아도 NEXTVAL 을사용하여자동으로입력할수있다. - CURRVAL : 현재값을반환 - NEXTVAL : 현재시퀀스값의다음값을반환 SQL>SELECT emp_seq.currval FROM DUAL ; SQL>SELECT emp_seq.nextval FROM DUAL ; CURRVAL NEXTVAL -------------- ------------ 1 2 시퀀스사용규칙 NEXTVAL, CURRVAL을사용할수있는경우 - subquery가아닌 select문 - insert문의 select절 - insert문의 value절 - update문의 set절 NEXTVAL, CURRVAL 을사용할수없는경우 - view 의 select 절 - distinct 키워드가있는 select 문 - group by, having, order by 절이있는 select 문 - select, delete, update 의 subquery - create table, alter table 명령의 default 값
8. 오라클객체 - SEQUENCE( 시퀀스 ) 67 시퀀스의수정및삭제 ALTER SEQUENCE sequence_name [INCREMENT BY n] [MAXVALUE n NOMAXVALUE] [MINVALUE n NOMINVALUE] [CYCLE NOCYCLE] START WITH 는수정할수없습니다. START WITH 절이없다는점을빼고는 CREATE SEQUENCE 와같다. SQL>ALTER SEQUENCE emp_seq INCREMENT BY 2 CYCLE; è2 씩증가하고, 최대값을넘으면다시처음부터순환하도록수정 DROP 문으로필요하지않은시퀀스는삭제할수있음 SQL>DROP SEQUENCE PRD_SEQ; sequence dropped.
8. 오라클객체 - SYNONYM( 동의어 ) 68 시노님이란? 시노님 : 오라클객체 ( 테이블, 뷰, 시퀀스, 프로시저 ) 에대한대체이름 (Alias) Synonym 은실질적으로그자체가 Object 가아니라 Object 에대한직접적인참조 시노님을사용하는이유 1 데이터베이스의투명성을제공시노님은다른유저의객체를참조할때많이사용함. 2 만약에다른유저의객체를참조할경우가있을때시노님을생성해서사용을하면은추후에참조하고있는오프젝트가이름을바꾸거나이동할경우객체를사용하는 SQL 문을모두다시고치는것이아니라시노님만다시정의하면되기때문에매우편리하다. 3 객체의긴이름을사용하기편한짧은이름으로해서 SQL 코딩을단순화 4 또한객체를참조하는사용자의오브젝트를감추수있기때문에이에대한보안을유지시노님을사용하는유저는참조하고있는객체를에대한사용자의 object 의소유자, 이름, 서버이름을모르고시노님이름만알아도사용할수있다. 시노님을사용하는이유 오브젝트의실제이름과소유자그리고위치를감춤으로써 database 보안을개선하는데사용 Object 에의 Public Access 를제공 Remote Database 의 Table, View, Program Unit 를위해투명성을제공 Database 사용자를위해 SQL 문을단순화
8. 오라클객체 - SYNONYM( 동의어 ) 69 시노님의종류 1 Private Synonym : 전용시노님은특정사용자만이용 2 Public Synonym : 공용시노님은공용사용자그룹이소유하며그 Database 에있는모든사용자가공유 시노님생성문법 CREATE [PUBLIC] SYSNONYM sysnonym_name FOR object_name scott USER 의 emp 테이블을 test USER 가사용하는예제. 1. 먼저 scott/tiger USER 로접속해서 test USER 에게 emp 테이블을조작할권한부여 SQL>GRANT ALL ON emp TO test; è test user 에대하여 scott 의 emp 테이블을조작할수있는권한부여 2. test USER 로접속해동의어생성 SQL> connect test/test SQL> CREATE SYNONYM scott_emp FOR scott.emp ; è scott USER 가소유하고있는 emp 테이블에대해 scott_emp 라는일반시노님생성 scott 사용자의 emp 테이블을 test 사용자가 scitt_emp 라는동의어로사용 -- 시노님을이용한쿼리 -- 일반테이블을쿼리 SQL> SELECT empno, ename FROM scott_emp; SQL> SELECT empno, ename FROM scott.emp; è 이두쿼리의결과같다. 동의어삭제 è SQL> DROP SYNONYM scott_emp
9. ORACLE 사용자의생성과권한의설정 - 오라클을설치하면기본적으로생성되는유저 70 오라클을설치하면아래의유저를포함한여러유저들이기본적으로생성 유저명 SYS SYSTEM SCOTT 비밀번호 CHANGE_ON_INSTALL MANAGER TIGER SYS - 데이터베이스의모든기본테이블과뷰는 SYS 스키마에저장 - 기본테이블과뷰는 oracle 을운영하는데꼭필요 - SYS 스키마의테이블은 data dictionary 의무결성유지관리를위해 oracle 에의해처리 - 대부분의 database 사용자는 SYS 계정으로접속하지말아야함 SYSTEM - 관리정보를화면으로보여주는추가테이블과뷰, 오라클도구가사용하는내부테이블과뷰를만들수있다. - SYSTEM 유저는모든시스템권한을가지고있다. 참고 - SYS 와 SYSTEM 은사용자를만들거나데이터베이스를관리할수있는권한 (DBA) 을가진다. ( 막강한어드민유저 ) - SYS 나 SYSTEM 유저에테이블을생성하거나, 일반데이터들을 Insert 하는방법은아주좋지않은방법 SYS 나 SYSTEM 유저는데이터베이스를관리하기위해서만사용되어야함. SCOTT - SCOTT 유저는일반사용자로오라클의기본적인 SQL 문을테스트를하기위한테이블과데이터들이있다.
9. ORACLE 사용자의생성과권한의설정 - 사용자의생성 71 사용자생성문법 CREATE USER user_name IDENTIFIED [BY password EXTERNALLY ] [ DEFAULT TABLESPACE tablespace ] [ TEMPORARY TABLESPACE tablespace ] [ QUOTA { integer [ K M ] UNLIMITIED } ON tablespace] [ PASSWORD EXPIRE ] [ ACCOUNT { LOCK UNLOCK } ] [ PROFILE { profile DEFAULT } ] - user_name : 생성될사용자이름 - BY password : 사용자가데이터베이스에의해인증되도록지정하며, 데이터베이스유저로그온시사용하는비밀번호 - EXTERNALLY : 사용자가운영체제에의해서인증되도록지정 - DEFAULT TABLESPACE 는사용자스키마를위한기본테이블스페이스지정 - TEMPORARY TABLESPACE 는사용자의임시테이블스페이스지정 - QUOTA 절을사용하여사용자가사용할테이블스페이스의영역할당 - PASSWORD EXPIRE : 사용자가 SQL*PLUS 를사용하여데이터베이스에로그인할때암호를재설정 ( 사용자가데이터베이스에의해인증될경우에만적합한옵션 ) - ACCOUNT LOCK/UNLOCK : 사용자계정을명시적으로잠그거나풀때사용할수있다.(UNLOCK 이기본값 ) - PROFILE: 자원사용을제어하고사용자에게사용되는암호제어처리방식을지정하는데사용
9. ORACLE 사용자의생성과권한의설정 - 사용자의생성 72 새로운 USER 를생성하기위해서는 CREATE USER 문을이용 USER 를생성하기위해서는 USER 생성권한이있는사용자로접속 아래는유저를생성하는아주간단한예제 SQL PLUS 를실행시키고 SCOTT/TIGER 로접속 SQL>CREATE USER TEST IDENTIFIED BY TEST; 1 행에오류 : ORA-01031: 권한이불충분 SCOTT USER 는사용자생성권한이없어서사용자를생성할수없다. 위의유저생성예제는테이블스페이스를지정하지않음. 아래 [ 참고 ] 의내용은유저를생성할때테이블스페이스를지정해주지않을경우의문제 SQL>CONN SYSTEM/MANAGER -- DBA Role 이있는유저로접속 SQL>CREATE USER TEST IDENTIFIED BY TEST; 사용자가생성 -- USER 를다시생성 참고 - 임시테이블스페이스를지정해주지않으면시스템테이블스페이스가기본으로지정되지만시스템테이블스페이스에단편화가발생할수있으므로사용자를생성할때임시테이블스페이스를따로지정해주는것이좋음. - 또한 DEFAULT TABLESPACE 도사용자를생성할때지정해주지않으면기본적으로시스템테이블스페이스가지정이된다. 하지만사용자를생성할때 DEFAULT TABLESPACE 를지정을해서사용자가소유한데이터와객체들의저장공간을별도로관리를해야함. 시스템테이블스페이스는본래의목적 ( 모든데이터사전정보와, 저장프로시저, 패키지, 데이터베이스트리거등을저장 ) 을위해서만사용되어져야하지일반사용자의데이터저장용으로사용되어서는안됨.
9. ORACLE 사용자의생성과권한의설정 - 사용자의생성 73 새로생성한 USER 로접속 SQL> CONN TEST/TEST ERROR: ORA-01045: 사용자 TEST 는 CREATE SESSION 권한을가지고있지않음 ; 로그온이안됨 - 새로생성한 TEST USER 는권한이없어서접근할수없음. - 모든 USER 는권한이있고권한에해당하는역할 - TEST 라는 USER 를사용하기위해서도권한을부여해주어야함 SQL> CONN SYSTEM/MANAGER 연결 SQL> GRANT connect, resource TO TEST ; 권한이부여 SQL> CONN TEST/TEST 연결 권한에대한자세한내용은권한설정강좌참고. 참고테이블스페이스란? - 오라클서버가테이터를저장하는논리적인구조 - 테이블스페이스는하나또는여러개의데이터파일로구성되는논리적인데이터저장구조
9. ORACLE 사용자의생성과권한의설정 - 사용자의변경및삭제 74 USER 변경하기위해서는 ALTER USER 문사용 ALTER USER문으로변경가능한옵션 - 비밀번호 - 운영체제인증 - 디폴트테이블스페이스 - 임시테이블스페이스 - 테이블스페이스분배할당 - 프로파일및디폴트역할 사용자수정문법 ALTER USER user_name [ IDENTIFIED {BY password EXTERNALLY } ] [ DEFAULT TABLESPACE tablespace ] [ TEMPORARY TABLESPACE tablespace ] [ PASSWORD EXPIRE ] [ ACCOUNT { LOCK UNLOCK } ] User 의 Password 수정예제 SQL>CONN SYSTEM/MANAGER SQL>ALTER USER scott IDENTIFIED BY lion; -- SYSTEM USER 로접속 -- scott USER 의비밀번호를 lion 으로수정 SQL>conn scott/lion -- scott USER 의비밀번호가수정된것을확인 SQL>conn system/manager SQL>ALTER USER scott IDENTIFIED BY tiger; -- scott USER 의비밀번호를처음처럼수정
9. ORACLE 사용자의생성과권한의설정 - 사용자의변경및삭제 75 사용자삭제문법 DROP USER user_name [CASCADE] SQL>DROP USER scott; -- scott 유저를삭제하는예제 SQL>DROP USER scott CASCADE; -- scott 유저가객체를소유하고있을경우에는 CASCADE옵션을추가해서삭제 CASCADE 를사용하게되면사용자이름과관련된모든데이터베이스스키마가데이터사전으로부터삭제되며모든스키마객체들또한물리적으로삭제 데이터베이스에등록된사용자정보의확인 데이터베이스에등록된사용자를조회하기위해서는 DBA_USERS 라는데이터사전을조회 SQL*Plus 를실행시켜 system/manager 로접속 SQL>SELECT username, default_tablespace, temporary_tablespace FROM ALL_USERS; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLES ---------------- ----------------------------- --------------------------- SYS SYSTEM TEMP SYSTEM TOOLS TEMP CTXSYS DRSYS DRSYS SCOTT SYSTEM SYSTEM STORM STORM STORMTEMP... 위와같이유저와테이블스페이스에대한정보가화면에나옴
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) 76 권한이란? 오라클에서권한 (Privilege) 은특정타입의 SQL 문을실행하거나데이터베이스나데이터베이스객체에접근할수있는권리 시스템권한 (System Privileges) 시스템권한 - 시스템권한은사용자가데이터베이스에서특정작업을수행할수있도록함 - 약 126 개의시스템권한이있으며그수는계속증가 - 권한의 ANY 키워드는사용자가모든스키마에서권한을가짐을의미 - GRANT 명령은사용자또는 Role 에대해서권한부여 - REVOKE 명령은권한삭제 시스템권한의종류몇가지 - CREATE SESSION : 데이터베이스를연결할수있는권한 - CREATE ROLE : 오라클데이터베이스역할을생성할수있는권한 - CREATE VIEW : 뷰의생성권한 - ALTER USER : 생성한사용자의정의를변경할수있는권한 - DROP USER : 생성한사용자를삭제시키는권한 시스템권한부여문법 GRANT [system_privilege role] TO [user role PUBLIC] [WITH ADMIN OPTION]
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) [ 문법설명 ] - system_privilege : 부여할시스템권한의이름 - role : 부여할데이터베이스역할의이름 - user, role : 부여할사용자이름과다른데이터베이스역할이름 - PUBLIC : 시스템권한, 또는데이터베이스역할을모든사용자에게부여할수있다. - WITH ADMIN OPTION : 권한을부여받은사용자도부여받은권한을다른사용자또는역할로부여할수있게되며, 만약사용자가 WITH ADMIN OPTION 과같이역할을부여받는다면부여된역할은그사용자에의해변경또는삭제될수있다. 시스템권한부여예제 SQL>GRANT CREATE USER, ALTER USER, DROP USER TO scott WITH ADMIN OPTION. 권한부여 * 설명 : scott 사용자에게사용자를생성, 수정, 삭제할수있는권한을부여 scott 사용자도다른사용자에게그권한을부여 77 시스템권한박탈문법 REVOKE [system_privilege role] FROM [user role PUBLIC] SQL>REVOKE CREATE USER, ALTER USER, DROP USER FROM scott 권한회수 * 설명 : scott 사용자에게부여한생성, 수정, 삭제권한회수
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) WITH ADMIN OPTION 을사용하여시스템권한취소 78 WITH ADMIN OPTION 을사용하여시스템권한을부여했어도시스템권한을취소할때는연쇄적으로취소되지않는다. 시나리오 1. DBA 가 STORM 에게 WITH ADMIN OPTION 을사용하여 CREATE TABLE 시스템권한부여 2. STORM 이테이블을생성 2. STORM 이 CREATE TABLE 시스템권한을 SCOTT 에게부여 3. SCOTT 가테이블을생성 4. DBA 가 STORM 에게부여한 CREATE TABLE 시스템권한취소 결과 - STORM 의테이블은여전히존재하지만새테이블을생성할수있는권한은없다. - SCOTT 는여전히테이블과새로운테이블을생성할수있는 CREATE TABLE 권한을가진다.
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) 객체권한 (Object Privileges) Object Privileges( 객체권한 ) 객체권한은유저가소유하고있는특정한객체를다른사용자들이액세스하거나조작할수있게하기위하여생성 - 테이블이나뷰, 시퀀스, 프로시저, 함수, 또는패키지중지정된한오브젝트에특별한작업을수행할수있게함. - Object 소유자는다른사용자에게특정 Object Privileges 를부여 - PUBLIC 으로권한을부여하면회수할때도 PUBLIC 으로해야함. - 기본적으로유저가소유한오브젝트에대한모든권한은자동적으로획득 - WITH GRANT OPTION 옵션은롤에권한을부여할때는사용할수없다 Object 별부여할수있는권한 객체권한테이블 (Table) 뷰 (View) 시퀀스 (Sequence) 프로시져 (Procedure) ALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE 79
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) 80 객체권한부여문법 GRANT object_privilege [column] ON object TO {user[,user] role PUBLIC] [WITH GRANT OPTION] 이전페이지표에서맨왼쪽에있는 ALTER, DELETE, EXECUTE.. 등등은 object_privilege 란에오면되고, 맨윗줄에있는테이블, 뷰, 시퀀스, 프로시져등등은 ON 다음에있는 object 에입력하면된다. 문법설명 - object_privilege : 부여할객체권한의이름 - object : 객체명 - user, role : 부여할사용자이름과다른데이터베이스역할이름 - PUBLIC : 오브젝권한, 또는데이터베이스역할을모든사용자에게부여 - WITH GRANT OPTION : 권한을부여받은사용자도부여받은권한을다른사용자또는역할로부여할수있다. 객체권한부여예제 SQL>GRANT SELECT, INSERT ON emp TO scott WITH GRANT OPTION 권한부여 * 설명 : scott 사용자에게 emp 테이블을 SELECT, INSERT 할수있는권한부여 scott 사용자도다른사용자에게그권한을부여
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) 81 객체권한회수문법 REVOKE {privilege[,privilege,..] ALL} ON object FROM {user[,user] role PUBLIC] [CASCADE CONSTRAINTS] 문법설명 - 객체권한의철회는그권한을부여한부여자만이수행 - CASCADE CONSTRAINTS : 이명령어의사용으로 REFERENCES 객체권한에서사용된참조무결성제한을같이삭제 - WITH GRANT OPTION 으로객체권한을부여한사용자의객체권한을철회하면, 권한을부여받은사용자가부여한객체권한또한같이철회되는종속철회가발생 객체권한회수예제 SQL>REVOKE SELECT, INSERT ON emp FROM scott * 설명 : scott 사용자에게부여한 emp 테이블에대한 SELECT, INSERT 권한이회수만약 scott 사용자가다른사용자에게 SELECT, INSERT 권한을부여했으면.. 그권한들도같이철회된다.
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) WITH GRANT OPTION 을사용하여객체권한취소 WITH GRANT OPTION 을사용하여부여한객체권한을취소하면취소작업이연쇄적으로수행 WITH ADMIN OPTION 과비교 시나리오 82 WITH GRANT OPTON SCOTT STORM TEST 권한부여 SCOTT STORM TEST 1. SCOTT 가 STORM 에게 WITH GRANT OPTION 을사용하여 emp 테이블의 SELECT 권한을부여 2. STORM 이 emp 테이블의 SELECT 권한을 TEST 에게부여 3. SCOTT 가 STORM 에게부여한 emp 테이블의 SELECT 권한을취소 결과 SCOTT STORM TEST 권한취소 X 결과 - SCOTT 가 STORM 에게부여한 emp 테이블에대한 SELECT 권한을취소하면 STORM 이부여한 TEST 유저가 emp 테이블을 SELECT 할수있는권한도자동으로취소
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) 롤 (Role) ROLE 이란사용자에게허가할수있는권한들의집합이라고할수있다. - ROLE 을이용하면권한부여와회수를쉽게할수있다. - ROLE 은 Create Role 권한을가진 User 에의해서생성 - 한사용자가여러개의 ROLL 을 ACCESS 할수있고, 여러사용자에게같은 ROLE 을부여할수있다. - 시스템권한을부여하고, 취소할때와동일한명령을사용하여사용자에게부여하고, 취소 - 사용자는 ROLE 에 ROLE 을부여 - 오라클데이터베이스를설치하면기본적으로 CONNECT, RESOURCE, DBA ROLE 이제공 아래의그림처럼 DBA 가유저들에게권한을부여할때일일이권한하나하나씩을지정을한다면몹시불편할것이다. DBA 가 USER 의역할에맞도록 Role 을생성하여서 Role 만유저에게지정을한다면보다효율적으로유저들의권한을관리할수있다. 83 권한 1 권한 2 권한 3 DBA 권한1 권한1 권한2 권한2 권한3 권한3 User1 User2 User3 DBA 권한1 권한2 권한3 Role Role Role Role User1 User2 User3 [ 일반적으로권한을부여하는방법 ] [ 롤을생성하여권한을부여하는방법 ]
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) 84 롤의생성문법 CREATE ROLE role_name ROLE 의부여순서 1 ROLE 의생성 : CREATE ROLE manager 2 ROLE 에권한부여 : GRANT create session, create table TO manager 3 ROLE 을사용자또는 ROLE 에게부여 : GRANT manager TO scott, test; SQL> CREATE ROLE manager -- role 생성 SQL>GRANT create session, create table TO manager -- role 에권한부여 SQL>GRANT manager TO scott, test; -- 권한이부여된 role 을 user 나 role 에부여 롤관련데이터사전 Dictionary 뷰 ROLE_SYS_PRIVS ROLE_TAB_PRIVS USER_ROLE_PRIVS USER_TAB_PRIVS_MADE USER_TAB_PRIVS_RECD USER_COL_PRIVS_MADE USER_COL_PRIVS_RECD 테이블 (Table) Role에부여된시스템권한 Role에부여된테이블권한현재사용자가 ACCESS할수있는 ROLE 현재사용자의객체에부여한객체권한현재사용자의객체에부여된객체권한현재사용자객체의특정컬럼에부여한객체권한현재사용자객체의특정컬럼에부여된객체권한
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) 85 오라클데이터베이스를생성하면기본적으로생성되는롤 (Role) 오라클데이터베이스를생성하면기본적으로몇가지의 Role 이생성 DBA_ROLES 데이터사전을통하여미리정의된 Role 을조회할수있다. SQL>SELECT * FROM DBA_ROLES; ROLE PASSWORD ---------------------- ----------------- CONNECT NO RESOURCE NO DBA NO EXP_FULL_DATABASE NO... 이외에도많이 ROLE 이존재. 그중에서가장많이사용하는 3 가지만설명 DBA Role - 모든시스템권한이부여된 Role - DBA Role 은데이터베이스관리자에게만부여
9. ORACLE 사용자의생성과권한의설정 - 권한 (Privileges) 과롤 (Role) RESOURCE - Store Procedure 나 Trigger 와같은 PL/SQL 을사용할수있는권한들로이루어짐 - PL/SQL 을사용하려면 RESOURCE Role 을부여 - 유저를생성하면일반적으로 CONNECT, RESOURCE 롤을부여 SQL>SELECT grantee, privilege FROM DBA_SYS_PRIVS WHERE grantee = 'RESOURCE'; CONNECT - 오라클에접속할수있는세션생성및테이블을생성하거나조회할수있는가장일반적인권한들로이루어짐 - CONNECT Role 이없으면유저를생성하고서도 Oracle 에접속할수없음 - 아래의명령어로 CONNECT Role 이어떤권한으로이루어져있는지확인할수있음 SQL>SELECT grantee, privilege FROM DBA_SYS_PRIVS WHERE grantee = 'CONNECT'; GRANTEE PRIVILEGE --------------- ------------------------------------- CONNECT ALTER SESSION CONNECT CREATE CLUSTER CONNECT CREATE DATABASE LINK CONNECT CREATE SEQUENCE CONNECT CREATE SESSION. 86