데이터 베이스 설계 텀 프로젝트를 위해 RWAPM 을 설치하였다.

APM 이란 말그대로 Apache + PHP + Mysql 을 한꺼번에 설치해주고 연동해주는 것이다.

따로따로 깔아보는게 물론 공부하는 데 도움이 되겠지만 역시 가시밭길을 가지말자;

설치는 정말 간단하다. 그냥 파일을 받아서 실행하고 다음다음 하면 된다.^^;

그림도 캡처하여 직접 올리려 했으나 이미 많은 사람들이 문서화 해 놓아서 퍼왔다..

혹시 apm 이 필요할 때를 대비하여 포스팅해놓자~

http://kldp.net/projects/rwapm/

이곳에서 다운받으면 된다.

매뉴얼도 한글로 되어있어서 정말 쉽다...

그러나...

문제는 여기서 끝이 아니라는거다.

php 와 oracle 을 연동하는 무시무시한 작업이 남아있는 것이다...ㅜㅜ

어떤 사람은 그냥 한번에 잘 되었다는 사람도 있었지만 나는 그렇지 못했다...

삽질에 삽질을 거듭한 끝에... 친구의 도움으로 간신히 성공하였다.

먼저 C:\RWAPM\RTM20040531\bin\php.ini 파일을 연다

사용자 삽입 이미지

php_oci8.dll 부분의 주석을 해제해야한다.

보통 여기까지 하면 잘 된다고들 했다... 그러나 계속해서 에러가 났다...

온갖 방법으로 다 해보다가 오라클 홈페이지에가서 오라클 인스턴스 클라이언트를 다운받기로 하였다..

instanceclient-basic-win32
instanceclient-odbc-win32
instanceclient-sdk-win32

위 세개의 압축파일을 받아서 풀었다.

베이직과 odbc 에 odbc_install.exe. 파일이 있는데 각각 실행시켜봤자 에러가 난다.

odbc 의 파일들을 일단 basic 폴더로 옮긴다. 그런다음 실행을 하면 실행이 된다.

그리고 sdk 의 ott.bat 파일을 실행시켰다..

그 결과... 에러 없이 오라클에 접속이 되었다...

테스트 코드는 다음과 같다..

사용자 삽입 이미지


아 정말... 끈나고 보니 간단하나... 징글징글한 삽질의 결과라는거...ㅡㅡ;

아래는 RWAPM 의 설치 화면을 첨부하였다...

자자자~ 그래도 삽질이 점점 줄어가는 것을 느끼기는 한다.  화이링~

사용자 삽입 이미지
 


Posted by 행복한 프로그래머 궁금쟁이박

댓글을 달아 주세요

  1. BlogIcon 셈틀쟁이 2008.05.30 16:34 신고  댓글주소  수정/삭제  댓글쓰기

    아오 우리 디비 계정 암호 다 나오게 하면 어떻게 하나 ㅎㅎ

  2. 손님 2008.11.18 20:47 신고  댓글주소  수정/삭제  댓글쓰기

    해당 RWAPM 설치 후에 FILE SIZE 변경은 어떻게 하나요

  3. BlogIcon 관리자 2008.11.19 14:14 신고  댓글주소  수정/삭제  댓글쓰기

    어떤 FILE SIZE 를 말씀하시는지요?

  4. BlogIcon GermmouSe 2013.11.23 01:40 신고  댓글주소  수정/삭제  댓글쓰기

    る。 女性として暮らしているが、性的適合手術を受けていない人 [url=http://animalliberation.org.au/wp-sell.php]モンクレール 2014 秋冬[/url] で珍しい動植物の豊かさを楽しみ、また、あなたの目はあなたがコ 窃盗として知られています。

    または新しい出荷は、ちょ フォーラムがあります、それはあなたがどこElse.There 喜びを感じて踊る幼児を見て。 彼らは旅するとき、彼らははるか などを送った。 少年は、私は自分自身の馬鹿を作ることでした!このジレンマとの闘いの中で最も適したイニシアチブは正確にこれ

오라클에서 사용자 추가하는 법..

크게 4단계로 나뉜다.

1. 테이블 스페이스 생성.
2. 임시 테이블 스페이스 생성.
3. 사용자 생성.
4. 권한부여.

자.. 아래는 내가 직접 오라클 서버에 접속해서 사용자 추가한것.

$ sqlplus

사용자명 입력: system    // 관리자 계정으로 로그인!
암호 입력:

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

// 테이블 스페이스 생성
SQL> create tablespace ts_san9972
  2  datafile '/ts_san9972.dbf' size 50M
  3  autoextend on
  4  next 1M
  5  maxsize 100M
  6  ;
create tablespace ts_san9972
*
1행에 오류:
ORA-01119: '/ts_san9972.dbf' 데이터베이스 파일 작성시 오류가 생겼습니다
ORA-27040: 파일 생성 오류, 파일을 생성할 수 없습니다.
SVR4 Error: 13: Permission denied   // oracle 계정으로 솔라리스 로그인 해서...


SQL> create tablespace ts_san9972
  2  datefile '/export/home/oracle/ts_san9972.dbf' size 50M  // 쓰기권한이 열려있는 곳으로 다시 지정
  3  autoextend on
  4  next 1M
  5  maxsize 100M;
datefile '/export/home/oracle/ts_san9972.dbf' size 50M  // 오타;; 캐짜증..
*
2행에 오류:
ORA-02180: CREATE TABLESPACE 옵션이 부적합합니다


SQL> create tablespace ts_san9972
  2  datafile '/export/home/oracle/ts_san9972.dbf' size 50M
  3  autoextend on
  4  next 1M
  5  maxsize 100M;

테이블스페이스가 생성되었습니다. // 성공!!

// 임시 테이블 스페이스 생성
SQL>
SQL> create temporary tablespace ts_san9972_temp
  2  tempfile '/export/home/oracle/ts_san9972_temp.dbf' size 100M
  3  extent management local uniform size 128k;

테이블스페이스가 생성되었습니다.  // 성공!

// 사용자 생성
SQL> create user san9972 identified by *******    // 사용할 아이디 및 패스워드 지정
  2  default tablespace ts_san9972                     // 아까 만든 테이블 스페이스 및 임시 스페이스 지정
  3  temporary tablespace ts_san9972_temp;

사용자가 생성되었습니다.  // 성공!!

// 권한부여
SQL>
SQL>
SQL> grant connect resource to san9972;
grant connect resource to san9972           // 콤마 안찍음...;;
      *
1행에 오류:
ORA-00990: 권한이 부적합합니다


SQL> grant connect, resource to san9972;  // connect 권한. 테이블 생성 및 읽기 권한 포함.

권한이 부여되었습니다.    // 성공!!

SQL>
SQL> conn san9972/*******
연결되었습니다.
SQL>

Posted by 행복한 프로그래머 궁금쟁이박

댓글을 달아 주세요

ORACLE 10g 드라이버 설치

odjbc14.jar 파일을 다운받아 <J2SDK>/jre/lib/ext 데릭토리와

<Tomcat>/common/lib 디렉토리에 각각 넣어둔다.

자바코드

import java.sql.*;

public class DB_Connector {

 private String url = "jdbc:oracle:thin:@oracle.uos.ac.kr:1521:ORCL";
 private String id = "db_2003920022";
 private String pass = "db_2003920022";
 private Connection conn;
 private String status;

 public DB_Connector() throws SQLException {
  try {
   // 1 단계 : 데이터베이스와 연결하여 사용할 파일들이 있는지를 확인
   Class.forName("oracle.jdbc.driver.OracleDriver");
   // 2 단계 : 드라이버 클래스를 통해 DB 서버와 연결하는 Connection 객체 생성
   this.conn = DriverManager.getConnection(url, id, pass);
   status="연결 성공";
   System.out.println(status);
  }
  catch(SQLException ee) {
   status="연결 실패";
   System.err.println("SQL Error = " + ee.toString());
   System.err.println("error = " + ee.toString());
   this.conn.close();
  }
  catch(ClassNotFoundException ee) {
   ee.printStackTrace();
  }
 }
 public String getStatus(){
  return this.status;
 }

 public Connection getConn() {
  return this.conn;
 }
 
 public Statement getSmt() {
  Statement stmt = null;
  try {
   stmt = this.conn.createStatement();
   System.out.println(new java.util.Date() + "Statement 객체 생성 성공!");
  }
  catch(SQLException ee) {
   System.err.println(new java.util.Date() + "Statement 객체 생성 실패!");
  }
  return stmt;
 }
 
 public PreparedStatement getPsmt(String query) {
  PreparedStatement pstmt = null;
  try {
   pstmt = this.conn.prepareStatement(query);
   System.out.println(new java.util.Date() + "PreparedStatement 객체 생성 성공!");
  }
  catch(SQLException ee) {
   System.err.println(new java.util.Date() + "PreparedStatement 객체 생성 실패!");
  }
  return pstmt;
 }

 public void delAll() {
  Statement stmt = null;
  String query = "delete from pms";
  try {
   stmt = this.conn.createStatement();
   stmt.executeUpdate(query);
  }
  catch(SQLException ee) {
   ee.printStackTrace();
  }
 }
 public void close() {
  if(this.conn != null) {
   try {
    if(!this.conn.isClosed()) {
     this.conn.close();
     System.out.println(new java.util.Date() + "Statement 객체 해제 성공!");
    }
   }
   catch(SQLException ee) {
    System.out.println(new java.util.Date() + "Statement 객체 해제 실패!");
   }
   this.conn = null;
  }
 }
}

오라클의 계정을 지정하고 url 을 지정한다.

private String url = "jdbc:oracle:thin:@oracle.uos.ac.kr:1521:ORCL";

위에서 끝에있는 ORCL 은 오라클의 SID 이다. 각자 컴퓨터에 설치된

오라클의 SID 를 지정해야 한다.

Class.forName("oracle.jdbc.driver.OracleDriver");

오라클 JDBC 드라이버를 로드한다.

------------------------------------------------------------------------------

팁으로 시퀀스 사용법

CREATE TABLE book (
        num NUMBER(7) NOT NULL,
        name VARCHAR(50),
        email VARCHAR(30),
        home VARCHAR(30),
        contents NVARCHAR2(2000),
        CONSTRAINT book_num_ok PRIMARY KEY(num)
)

CREATE SEQUENCE book_num
        START WITH 1
        INCREMENT BY 1
        NOMAXVALUE;

String sql = "insert into book(num, name, email, home, contents)"
    + " values(book_num.nextVal, ?, ?, ?, ?)";

오라클에서 num 값을 자동적으로 하나씩 증가시키기 위해 시퀀스를 사용

Posted by 행복한 프로그래머 궁금쟁이박
TAG 오라클

댓글을 달아 주세요

프로그래밍 :: 오라클 강좌

★ SQL 의 종류


1.DDL (Data Definition Language) : 데이터와 그 구조를 정의 합니다.

SQL문

내 용

CREATE

데이터베이스 객체를 생성 합니다.

DROP

데이터베이스 객체를 삭제 합니다.

ALTER

기존에 존재하는 데이터베이스 객체를 다시 정의하는역할을 합니다.


2. DML (Data Manipulation Language) : 데이터의 검색과 수정등의 처리

SQL문

내 용

INSERT

데이터베이스 객체에 데이터를 입력

DELETE

데이터베이스 객체에 데이터를 삭제

UPDATE

기존에 존재하는 데이터베이스 객체안의 데이터 수정

SELECT

데이터베이스 객체로부터 데이터를 검색


3.DCL (Data Control Language) : 데이터베이스 사용자의 권한을 제어

SQL문

내 용

GRANT

데이터베이스 객체에 권한을 부여 합니다.

REVOKE

이미부여된 데이터베이스객체의 권한을 취소합니다.





★ 사용자의 생성


새로운 USER를 생성하기 위해서는 CREATE USER문을 이용하면 됩니다.
 USER를 생성하기 위해서는 USER생성 권한이 있는 사용자로 접속해야 합니다.

사용자 생성 문법(Syntax)




 - user_name : 사용자 이름

 - BY password : 사용자가 데이터베이스에 의해 인증되도록 지정하며, 데이터베이스 유저 로그온시 사용하는 비밀번호 입니다.  

 - EXTERNALLY : 사용자가 운영 체제에 의해서 인증되도록 지정합니다.

 - DEFAULT TABLESPACE사용자 스키마를 위한 기본 테이블 스페이스를 지정 합니다.

 - TEMPORARY TABLESPACE사용자의 임시 테이블 스페이스를 지정합니다.

 - QUOTA절을 사용하여 사용자가 사용할 테이블 스페이스의 영역을 할당합니다.

 - PASSWORD EXPIRE : 사용자가 SQL*PLUS를 사용하여 데이터베이스에 로그인할  때 암호를 재설정하도록 합니다.(사용자가  데이터베이스에 의해  인증될 경우에만 적합한 옵션입니다.)

 - ACCOUNT LOCK/UNLOCK :  사용자 계정을 명시적으로 잠그거나 풀 때 사용할 수  있습니다.(UNLOCK이 기본값입니다.)

 - PROFILE: 자원 사용을 제어하고 사용자에게 사용되는 암호 제어 처리 방식을 지정하는데 사용됩니다.

※ 여기선 간단한 유저생성에 대해서만 알아보고 자세한 유저관리와 PROFILE 관리는 어드민에서 설명 하겠습니다.
 

※ 참고 1

 - 임시 테이블스페이스를 지정해 주지 않으면 시스템 테이블스페이스가 기본으로 지정 되지만 시스템 테이블스페이스에 단편화가 발생할 수 있으므로 사용자를 생성할때 임시테이블스페이스를 따로 지정해 주는 것이 좋습니다.
 
- 또한 DEFAULT TABLESPACE도 사용자를 생성할때 지정해 주지 않으면 기본적으로 시스템 테이블스페이스가 지정이 됩니다. 하지만 사용자를 생성할때 DEFAULT TABLESPACE를 지정을 해서 사용자가 소유한 데이터와 객체들의 저장 공간을 별도로 관리를 해야 합니다.

  시스템 테이블스페이스는 본래의 목적(모든 데이터 사전 정보와, 저장 프로시저, 패키지, 데이터베이스 트리거등을 저장)을 위해서만 사용되어져야 하지 일반사용자의 데이터 저장용으로 사용 되어서는 안됩니다.


※ 참고 2

테이블 스페이스란 ?

 - 오라클 서버가 테이터를 저장하는 논리적인 구조입니다.
 - 테이블스페이스는 하나 또는 여러개의 데이터파일로 구성되는 논리적인 데이터 저장 구조입니다.
 
 테이블 스페이스에 대한 자세한 내용는 오라클 어드민의 테이블스페이스 강좌에서 학습하겠습니다.  



사용자 생성 예제

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를 다시 생성합니다.
 사용자가 생성되었습니다.
 

 새로 생성한 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
연결되었습니다.
 
 ※ 권한에 대한 자세한 설명은 권한 설정에서 학습 하겠습니다.




★ User의 변경 및 삭제


USER 변경하기 위해서는 ALTER USER문을 사용합니다..

● ALTER USER문으로 변경 가능한 옵션

  -  비밀번호
  -  운영체제 인증
  -  디폴트 테이블 스페이스
  -  임시 테이블 스페이스
  -  테이블 스페이스 분배 할당
  -  프로파일 및 디폴트 역할


사용자 수정 문법(Syntax)


사용자 수정 예제


SQL>CONN SYSTEM/MANAGER       -- SYSTEM USER로 접속합니다.

SQL>ALTER USER scott IDENTIFIED BY lion;    -- scott USER의 비밀번호를 수정합니다.
 사용자가 변경되었습니다.

SQL>conn scott/lion    -- scott USER의 비밀번호가 바낀걸 확인할 수 있습니다.
접속되었습니다.


SQL>conn system/manager
접속되었습니다.

SQL>ALTER USER scott IDENTIFIED BY tiger;    -- scott USER의 비밀번호를 처음처럼 수정합니다.
 사용자가 변경되었습니다.
 


사용자 삭제


문법(Syntax)



 
 ※ CASCADE를 사용하게 되면 사용자 이름과 관련된 모든 데이터베이스 스키마가 데이터 사전으로부터
     삭제되며 모든 스키마 객체들 또한 물리적으로 삭제 됩니다.


사용자 정보의 확인


데이터베이스에 등록된 사용자를 조회하기 위해서는 DBA_USERS라는 데이터사전을 조회하면 됩니다.
 
SQL*Plus를 실행시켜  system/manager로 접속을 합니다.
 
SQL>SELECT username, default_tablespace, temporary_tablespace
        FROM DBA_USERS;
   
USERNAME      DEFAULT_TABLESPACE      TEMPORARY_TABLES
---------------- -------------------        ----------------
SYS                    SYSTEM                       TEMP
SYSTEM             TOOLS                          TEMP
OUTLN                SYSTEM                        SYSTEM
DBSNMP              SYSTEM                       SYSTEM
ORDSYS              SYSTEM                       SYSTEM
ORDPLUGINS       SYSTEM                        SYSTEM
MDSYS               SYSTEM                        SYSTEM
CTXSYS              DRSYS                          DRSYS
SCOTT                SYSTEM                       SYSTEM
TEST                  TEST                            SYSTEM
STORM               STORM                         SYSTEM
KJS                    SYSTEM                        SYSTEM
OEM                   OEM_REPOSITORY         TEMP
 
 위와 같이 유저와 테이블 스페이스에 대한 정보가 화면에 나옵니다.




시스템 권한(System Privileges)

오라클에서 권한(Privilege)은 특정 타입의 SQL문을 실행하거나 데이터베이스나 데이터 베이스
 객체에 접근할 수 있는 권리입니다.


SYSTEM PRIVILEGES

 -
시스템 권한은 사용자가 데이터베이스에서 특정 작업을 수행 할 수 있도록 합니다
 
- 약 126개의 시스템 권한이 있으며 그 수는 계속 증가하고 있습니다.
 - 권한의 ANY 키워드는 사용자가 모든 스키마에서 권한을 가짐을 의미 합니다.
 - GRANT 명령은 사용자 또는 Role에 대해서 권한을 부여 합니다.
 - REVOKE 명령은 권한을 삭제 합니다.


  시스템 권한의 종류 몇가지   

  -  CREATE SESSION :
데이터 베이스를 연결할 수 있는 권한
  -  CREATE ROLE :
오라클 데이터베이스 역할을 생성할 수 있는 권한
  -  CREATE VIEW :
뷰의 생성 권한
  -  ALTER USER :
생성한 사용자의 정의를 변경할 수 있는 권한
  -  DROP USER :
생성한 사용자를 삭제시키는 권한


시스템 권한 부여 문법(Syntax)




 - 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 사용자도 다른 사용자에게 그 권한을 부여 할 수 있습니다.
 


시스템 권한의 박탈

문법(Syntax)


시스템 권한 철회 예제


SQL>REVOKE CREATE USER, ALTER USER, DROP USER
        FROM scott
       

 권한이 회수되었습니다.

*설명 : scott 사용자에게 부여한 생성, 수정, 삭제 권한을 회수합니다,
           


WITH ADMIN OPTION을 사용하여 시스템 권한 취소


 WITH ADMIN OPTION을 사용하여 시스템 권한을 부여했어도 시스템 권한을 취소 할 때는 연쇄적으로 취소 되지 않습니다.

시나리오

 1. DBA가 STORM에게 WITH ADMIN OPTION을 사용하여 CREATE TABLE 시스템 권한을 부여 합니다.

2. STORM이 테이블을 생성 합니다.

3. STORM이 CREATE TABLE 시스템 권한을 SCOTT에게 부여 합니다.

4. SCOTT가 테이블을 생성 합니다.

5. DBA가 STORM에게 부여한 CREATE TABLE 시스템 권한을 취소 합니다.

결과

 - STORM의 테이블은 여전히 존재하지만 새 테이블을 생성할 수 있는 권한은 없습니다.

 - SCOTT는 여전히 테이블과 새로운 테이블을 생성 할 수 있는 CREATE TABLE권한을 가지고 있습니다.

 



객체 권한(Object Privileges)


Object Privileges(객체 권한)


   객체 권한은 유저가 소유하고 있는 특정한 객체를 다른 사용자들이 엑세스 하거나 조작 할 수 있게 하기 위해서 생성을 합니다.


  - 테이블이나 뷰, 시퀀스, 프로시저, 함수, 또는 패키지 중 지정된 한 오브젝트에 특별한 작업을
    수행 할 수 있게 합니다.
 
  - Object 소유자는 다른 사용자에게 특정 Object Privileges를 부여 할 수 있습니다.

  - PUBLIC으로 권한을 부여하면 회수할 때도 PUBLIC으로 해야 합니다.

  - Object Privileges는 Object Type에 따라서 다릅니다.

  - 기본적으로 소유한 오브젝트에 대한 모든 권한이 자동적으로 획득됩니다

  - WITH GRANT OPTION 옵션은 롤에 권한을 부여할 때는 사용할 수 없습니다


 * Object에 따른 Privileges

객체권한

테이블

Sequence

Procedure

  ALTER



  DELETE



  EXECUTE




  INDEX




  INSERT



  REFERENCES




  SELECT


  UPDATE




  위의 표에서
맨 왼쪽에 있는 ALTER, DELETE, EXECUTE.. 등등은 object_privilege란에 오면 되고,
맨 윗줄에 있는 테이블, 뷰, 시퀀스, 프로시져 등등은 ON 다음에 있는 object에 입력하면 됩니다.


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 사용자도 다른 사용자에게 그 권한을 부여 할 수 있습니다.
 



Object 권한의 박탈



 - 객체 권한의 철회는 권한을 부여한 부여자만이 수행할수 있습니다.

 - CASCADE CONSTRAINTS : 이 명령어의 사용으로 REFERENCES객체 권한에서 사용된
               참조 무결성 제한을 같이 삭제 할 수 있습니다.

 - WITH GRANT OPTION으로 객체 권한을 부여한 사용자의 객체 권한을 철회하면, 권한을
   부여받은 사용자가 부여한 객체 권한 또한 같이 철회되는 종속철회
발생합니다.



오브젝 권한 철회 예제


SQL>REVOKE SELECT, INSERT
         ON emp
         FROM scott


*설명 : scott 사용자에게부여한 emp테이블에 대한 SELECT, INSERT권한이 회수 됩니다..
         만약 scott사용자가 다른 사용자에게 SELECT, INSERT권한을 부여했으면..
         그 권한들도 같이 철회가 됩니다.
 


WITH GRANT OPTION을 사용하여 객체 권한 취소


 WITH GRANT OPTION을 사용하여 부여한 객체 권한을 취소하면 취소 작업이 연쇄적으로 수행 됩니다.

시나리오

 1. SCOTT가 STORM에게 WITH GRANT OPTION을 사용하여 emp테이블의 SELECT 권한을 부여 합니다.

2. STORM이 emp테이블의 SELECT권한을 TEST에게 부여 합니다.

3. SCOTT가 STORM에게 부여한 emp테이블의 SELECT 권한을 취소 합니다.

결과

 - SCOTTt가 STORM에게 부여한 emp테이블에 대한 SELECT 권한을 취소하면 STORM이 부여한 TEST유저가 emp테이블을 SELECT할 수 있는 권한도 자동으로 취소가 됩니다.

 



객체 권한을 위한 일반 사용자용 데이터 사전 뷰
 

데이터 사전 뷰

설명

 USER_TAB_PRIVS

 객체 권한의 소유자, 객체 권한 부여자, 객체 권한 피부여자를 볼수있음

 USER_TAB_PRIVS_MADE

 사용자가 부여 모든 객체 권한의 뷰

 USER_TAB_PRIVS_RECD

 사용자가 부여받은 모든 객체 권한의 뷰

 USER_COL_PRIVS

 객체 권한의 소유자, 객체 권한 부여자, 객체 권한 피부여자의 컬럼의 객체
 권한 뷰

 USER_COL_PRIVS_MADE

 사용자가 부여 객체 컬럼에 대한 모든 객체 권한 뷰

 USER_COL_PRIVS_RECD

 사용자가 부여받은 객체 컬럼에 대한 모든 객체 권한 뷰


롤(Role)


 

 ROLE 이란 사용자에게 허가할 수 있는 권한들의 집합 이라고 할 수 있습니다.


  - ROLE을 이용하면 권한 부여와 회수를 쉽게 할 수 있습니다.

  - ROLE은 Create Role권한을 가진 User에 의해서 생성 됩니다. 

  - 한 사용자가 여러개의 ROLL을 ACCESS할 수 있고,  여러 사용자에게 같은 ROLE을 부여할 수
    있습니다.

  - 시스템 권한을 부여하고, 취소할 때와 동일한 명령을 사용하여 사용자에게 부여하고, 취소 합니다.

  - 사용자는 ROLE에 ROLE을 부여할 수 있습니다.

  - 오라클 데이터베이스를 설치하면 기본적으로 CONNECT, RESOURCE, DBA ROLE이 제공  
    됩니다.


   아래의 그림처럼 DBA가 유저들에게 권한을 부여할 때 일일이 권한 하나하나씩을 지정을 한다면 몹시 불편할 것 입니다. DBA가 USER의 역할에 맞도록 Role을 생성하여서 Role만 유저에게 지정을 한다면 보다 효율적으 로 유저들의 권한을 관리 할 수 있습니다.


ROLE의 생성

문법
 


ROLE의  부여 순서
 ① ROLE의 생성  :  CREATE ROLE manager
 ② ROLE에 권한 부여 : GRANT create session, create table TO manager
 ③ ROLE을 사용자 또는 ROLE에게 부여 : GRANT manager TO scott, test;
 

-- role을 생성 합니다.
SQL>
CREATE ROLE manager

-- role에 권한을 부여 합니다.
SQL>GRANT
create session, create table TO manager

-- 권한이 부여된 role을 user나 role에 부여 합니다.
SQL>GRANT
manager TO scott, test;

Dictionary 뷰

내      용

  ROLE_SYS_PRIVS

 Role에 부여된 시스템 권한

  ROLE_TAB_PRIVS

 Role에 부여된 테이블 권한

  USER_ROLE_PRIVS

 현재 사용자가 ACCESS할 수 있는 ROLE

  USER_TAB_PRIVS_MADE

 현재 사용자의 객체에 부여 객체 권한

  USER_TAB_PRIVS_RECD

 현재 사용자의 객체에 부여 객체 권한

  USER_COL_PRIVS_MADE

 현재 사용자 객체의 특정 컬럼에 부여 객체 권한

  USER_COL_PRIVS_RECD

 현재 사용자 객체의 특정 컬럼에 부여 객체 권한



★ 테이블의 생성


이번 강좌는 오라클 테이블해 대해서 설명을 하겠습니다.

 테이블은 실제로 데이터들이 저장되는 곳 이라고 생각하면 쉽게 이해 할 수 있습니다.
 CREATE TABLE명령어를 이용해서 테이블을 생성 할 수 있습니다.

 아래의 내용은 테이블에 대한 보충 설명입니다.


테이블 이란?

1. 테이블은 오라클 데이타베이스의 기본적인 데이타 저장 단위 입니다.

2. 데이타베이스 테이블은 사용자가 접근 가능한 모든 데이타를 보유하며 레코드와 컬럼으로 구성 
   
됩니다.
 
 관계형 데이타베이스가 아닌 예전의 데이타 베이스 용어에서는 파일과 테이블이, 필드와 컬럼이,
   그리고 레코드와 행이 동일시 되었습니다.

3. 테이블은 시스템내에서 독립적으로 사용되길 원하는 엔티티를 표현할수 있습니다.  
   예를 들면, 회사에서의 고용자나 제품에 대한 주문은 테이블로 표현 가능합니다.

4. 테이블은 두 엔티티간의 관계를 표현할 수 있습니다.
   즉 테이블은 고용자와 그들의 작업 숙련도 혹은 제품과 주문과의 관계를 표현하는데  사용될 수
   있습니다.

   
테이블내에 있는 외래 키 (ForeIgn Key)는  두 엔티티 사이의 관계를 표현하는데 사용됩니다.

5. 비록 "테이블" 이라는 말이 더 많이 사용되지만 테이블의 형식어는 "릴레이션" 입니다.


컬  럼
  -
테이블의 각 컬럼은 엔티티의 한 속성을 표현한다

행(ROW, 레코드)
  -
테이블의 데이타는 행에 저장됩니다



※ 테이블 생성시 제한사항과 고려할점

  - 테이블 이름과 컬럼은 항상 알파벳 문자로 시작해야 하며 A~Z까지의 문자, 0~9까지의 숫자,    
     
그리고 $,#,_(Under Bar)를 사용할 수 있습니다. (★공백 사용 불가능)

  - 테이블의 컬럼 이름은 30자를 초과할수 없고, 예약어를 사용할수 없읍니다.

  -  오라클 테이블 한 계정안에서 테이블 이름은 다른 테이블 이름과 달리 유사해야 합니다.

  - 한 테이블 안에서 컬럼이름은 같을수 없으며 다른 테이블에서의 컬럼이름과는 같을수  있습니다.


테이블의 생성 문법


[Syntax]




- 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에 대한 자세한 강좌는 오라클 어드민 강좌의 6. Storage Structure => PCTFREE와 PCTUSED를 참고해 주세요


테이블의 생성 예제

emp2와 dept2테이블을 생성하는 예제입니다.

SQL>CREATE TABLE EMP2(
        EMPNO      NUMBER    CONSTRAINT   emp_pk_empno   PRIMARY KEY,     
     -  (컬럼)         (데이터타입)                            (제약조건)
        ENAME        VARCHAR2(20),
        JOB             VARCHAR2(40),
        MGR             NUMBER,
        HIREDATE     DATE,
        SAL              NUMBER,
        COMM          NUMBER,
        DEPTNO       NUMBER)
        PCTFREE 20
        PCTUSED 50 ;

테이블이 생성되었습니다.


SQL>CREATE TABLE DEPT2(
        DEPTNO   NUMBER CONSTRAINT dept_pk_deptno PRIMARY KEY,
        DNAME      VARCHAR2(40),
        LOC          VARCHAR2(50)) ;

테이블이 생성되었습니다.



테이블 생성시 주의사항

 - 테이블 이름을 지정하고 각 컬럼들은 괄호 "()" 로 묶어 지정합니다.

 - 컬럼뒤에 데이터 타입은 꼭 지정되어야 합니다.

 - 각 컬럼들은 콤마","로 구분되고, 항상 끝은 세미콜론";" 으로 끝납니다.

 - 한 테이블 안에서 컬럼이름은 같을수 없으며 다른 테이블에서의 컬럼이름과는 같을 수 있습니다.

유저가 소유한 모든 테이블 보기


USER_TABLES 데이터사전을 조회 하면 유저가 소유한 테이블을 확인 할 수 있습니다.

-- SQL*Plus에서 실행해 보세요..
SQL>SELECT table_name FROM  USER_TABLES;

TABLE_NAME
------------
BONUS
CRETABLE
DEPT
DUMMY
EMP
EMP2
SALGRADE

위와 같이 테이블 목록이 조회 됩니다.
 



★ 테이블의 제약조건


제약조건 (Constraint)

  제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러 가지 규칙을 적용해 놓는거라 생각하면 됩니다. 간단하게 테이블안에서 테이터의 성격을 정의하는 것이 바로 제약조건 입니다.

 ★ 데이터의 무결성 유지를 위하여 사용자가 지정할 수 있는 성질 입니다.

 ★ 모든 CONSTRAINT는 데이터 사전(DICTIONARY)에 저장 됩니다.

 ★ 의미있는 이름을 부여했다면 CONSTRAINT를 쉽게 참조할 수 있습니다.

 ★ 표준 객체 명명법을 따르는 것이 좋습니다.

 ★ 제약조건은 테이블을 생성할 당시에 지정할 수도 있고, 테이블 생성 후 구조변경(ALTER)명령어를
     통해서도 추가가 가능합니다.

 ★ NOT NULL제약조건은 반드시 컬럼 레벨에서만 정의가 가능합니다.


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 조건
:
데이터의 유일성을 보장(중복되는 데이터가 존재할수 없습니다.)
자동으로 index가 생성됩니다.    


SQL> ALTER TABLE emp
        ADD CONSTRAINT emp_uk_deptno UNIQUE (deptno) ;

테이블이 변경되었습니다.

     이런식으로 하면 deptno 컬럼에 중복된 데이터가 들어갈 수 없습니다.

-- 제약 조건의 삭제

SQL>ALTER TABLE emp
       DROP CONSTRAINT emp_uk_deptno ; 

테이블이 변경되었습니다.
 



CHECK 조건 :
컬럼의 값을 어떤 특정 범위로 제한할 수 있습니다.


SQL>ALTER TABLE emp
       ADD CONSTRAINT emp_ck_comm
       CHECK (comm >= 10 AND comm <= 100000) ;

테이블이 변경되었습니다.

     comm컬럼은 체크조건에서 제한을 하고 있으므로 1에서 100까지의 값만을 가질수  있습니다.
     
체크 조건에서는 IN 연산자를 사용할수 있습니다.


-- 제약 조건의 삭제

SQL>ALTER TABLE emp
        DROP CONSTRAINT emp_ck_comm ;

테이블이 변경되었습니다.

SQL> ALTER TABLE emp
        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를 하지 않아도 오늘 날짜가 들어갑니다.



PRIMARY KEY 지정
: 기본키는 UNIQUE 와 NOT NULL의 결합과 같습니다.

  ※  기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서
       외래키들이  참조할 수 있는 키로서의 자격을 가지고 있습니다. 이를 참조 무결성이라 합니다
.  

       UNIQUE 조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성하며
       그 이름은 기본 키  제약 조건의 이름과 같습니다.


INDEX KEY
: 검색 키로서 검색 속도를 향상 시킴니다.
                   (UNIQUE,PRIMARY KEY 생성시 자동적으로 생김니다.)

SQL>CREATE TABLE emp(
        empno NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY ) ;

     이런식으로 하면 empno 컬럼에 UNIQUE 제약조건과 NOT NULL제약조건을 가지게 됩니다.



 FOREIGN KEY(외래 키)지정
: 기본키를 참조하는 컬럼 또는 컬럼들의 집합입니다.

※ 외래키를 가지는 컬럼의 데이터 형은 외뢰키가 참조하는 기본키의 컬럼과 데이터형이
    일치해야 합니다.
    이를 어기면 참조무결성 제약에의해 테이블을 생성할수 없습니다.

외래키에 의해 참조되고 있는 기본 키는 삭제할수 없다.

ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는  그 기본키가 삭제 될 때 같이
    삭제됩니다.


SQL>ALTER TABLE emp ADD CONSTRAINT emp_fk_deptno
        FOREIGN  KEY (deptno) REFERENCES dept(deptno)       

테이블이 변경되었습니다.

 이런식으로 하면 emp 테이블의 deptno 컬럼은 dept 테이블에 deptno 컬럼을 참조하는
 외래키를 가지게 됩
니다.

직접 변경해 보세요..

 


제약 조건의 확인

USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건을 볼 수 있습니다.
USER_CONSTRAINTS : 유저가 소유한 모든 제약 조건을 불 수 있습니다.  
이 두개의 데이터사전을 참조 하면 됩니다.
 
 SQL> SELECT  SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,  
               DECODE(B.CONSTRAINT_TYPE,
                                         'P','PRIMARY KEY',
                        'U','UNIQUE KEY',
                      'C','CHECK OR NOT NULL',
                                        'R','FOREIGN KEY') CONSTRAINT_TYPE,  
              A.CONSTRAINT_NAME   CONSTRAINT_NAME  
        FROM  USER_CONS_COLUMNS  A,  USER_CONSTRAINTS  B  
        WHERE  A.TABLE_NAME = UPPER('&table_name')  
            AND  A.TABLE_NAME = B.TABLE_NAME  
            AND  A.CONSTRAINT_NAME = B.CONSTRAINT_NAME  
        ORDER BY 1;  

-- 테이블 명을 입력 하면 됩니다.
table_name의 값을 입력하십시오: emp2

 
COLUMN_NAME         CONSTRAINT_TYPE   CONSTRAINT_NAME
------------------------------ ----------------- --------------
DEPTNO                   CHECK OR NOT NULL      SYS_C001362  
                               FOREIGN KEY                EMP2_FK_DEPTNO
EMPNO                     PRIMARY KEY               EMP2_PK_EMPNO
ENAME                     CHECK OR NOT NULL     EMP2_NN_ENAME
MGR                        UNIQUE KEY                  EMP2_UP_MGR




★ 테이블의 관리


테이블의 관리는 테이블의 컬럼 관리와 테이블 정보 관리로 나누어서 설명 하겠습니다.


① 테이블 컬럼의 관리

 테이블의 컬럼은 ADD, MODIFY, DROP연산자를 통해서 관리 할 수 있습니다.


ADD 연산자  : 테이블에 새로운 컬럼을 추가 할 때 사용 합니다.

SQL>ALTER TABLE emp ADD (addr VARCHAR2(50));

VARCHAR2의 데이터 형을 가지는 addr 컬럼이 emp 테이블에 추가 됩니다.



MODIFY 연산자 :
테이블의 컬럼을 수정 하거나 NOT NULL컬럼으로 변경 할 수 있습니다.  


SQL>ALTER TABLE emp MODIFY (ename VARCHAR2(50));
SQL>ALTER TABLE emp MODIFY (ename VARCHAR2(50) NOT NULL) ;

ename컬럼이 VARCHAR2 50자리로 수정 됩니다.

컬럼이 이미 테이터를 가지고 있을 경우 다른 데이터형으로 변경이 불가능합니다.



DROP
연산자  : 테이블 컬럼을 삭제 하거나, 테이블의 제약 조건을 삭제 할 때 사용 합니다.  


컬럼의 삭제 예제

 -- 컬럼의 삭제는 오라클 8i버전 부터 지원을 합니다.
  SQL>
ALTER TABLE table_name DROP COLUMN column_name


제약 조건의 삭제 예제

  SQL>ALTER TABLE emp DROP PRIMARY KEY ;


 -- CASCADE 연산자와 함께 사용하면 외래키에 의해 참조되는 기본키도 삭제될수 있습니다.
 SQL>ALTER TABLE emp DROP CONSTRAINT emp_pk_empno CASCADE;
 




② 테이블 정보의 관리

기존 테이블의 복사


 - 기존 테이블을 부분, 또는 완전히 복사할 때에  서브쿼리를 가진 CREATE TABLE 명령어
   사용해서 쉽게 테이블을 복사 할 수 있습니다.

 - 하지만 제약 조건, 트리거, 그리고 테이블 권한은  새로운 테이블로 복사되지 않습니다.  

 - 제약조건은 NOT NULL제약조건만 복사 됩니다.

[Syntax]



한번 실습해 보세요..

SQL>CREATE TABLE emp2
       AS
       SELECT * FROM emp;
 테이블이 생성되었습니다.



테이블 정보의 변경


 - 보통 테이블의 정보를 변경하는 이유는 스토리지 파라미터와 블록 활용파라미터를 변경하기 위해서
   사용 합니다.
 
 -  테이블 정보의 변경시 INITIAL의 값은 변경 할 수 없습니다.

[Syntax]



 STORAGE-CLAUSE에 들어올 수 있는 스토리지 파라미터를 정리하면 아래와 같습니다.
 
  - NEXT
    다음 번 생성될 익스텐트의 크기를 Byte단위로 지정합니다.
    이후의 익스텐트 크기는 PCTINCREASE만큼씩 증가 됩니다.

  - PCTINCREASE
    마지막 생성된 익스텐트의 바로 다음에 생성될 익스텐트의 증가율을 퍼센트지로 지정 합니다.

  - MINEXTENTS
    최초 생성되는 익스텐트의 수를 지정 합니다.

  - MAXEXTENTS
    생성될 수 있는 최대 익스텐트의 수를 지정 합니다.


기타 블록관련 파라미터는 테이블의 생성 강좌를 참고하세요..



테이블의 테이블스페이스 변경


Oracle8i이전 버전에서는 export를 해서 다시 import를 해야지만 테이블스페이스를 변경 할 수 있었지만
오라클 8i부터는 ALTER TABLE ~ MOVE TABLESPACE 명령어로 쉽게
테이블의 테이블스페이스를 변경 할 수 있습니다.

[Syntax]


-- 한번 실습해 보세요.
SQLPLUS scott/tiger
 
SQL>ALTER TABLE emp MOVE TABLESPACE test;
테이블이 변경되었습니다



테이블의 TRUNCATE


 - 테이블을 Truncate하면 테이블의 모든 행이 삭제되고 사용된 공간이 해제 됩니다.

 - TRUNCATE TABLE은 DDL명령이므로 롤백 데이터가 생성되지 않습니다.
   DELETE명령으로 데이터를 지우면 롤백명렁어로 복구 할 수 있지만
   TRUNCATE로 데이터를 삭제하면 롤백을 할 수가 없습니다.
 
 - 행당 인덱스도 같이 잘려 나갑니다.
 
 - 외래키가 참조중인 테이블은 TRUNCATE할 수 없습니다.
 
 - TRUNCATE명령을 사용하면 삭제 트리거가 실행되지 않습니다.

[Syntax]

 



DROP TABLE (테이블을 삭제할 때 사용)


[Syntax]


SQL>DROP TABLE emp ;

SQL>DROP TABLE emp CASCADE CONSTRAINT;

CASCADE CONSTRAINT : 외래키에 의해 참조되는 기본키를 포함한 테이블일 경우 기본키를 참조하던
                                   외래 키 조건도 같이 삭제 됩니다.




★ 데이터의 삽입, 수정, 삭제


▣ INSERT

INSERT명령어는 테이블 안에 데이터를 삽입하는 역할을 합니다. .





 -  
실제 데이터는 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



SQL>INSERT INTO
DEPT
       SELECT * FROM SCOTT.DEPT ;

직접 데이터를 입력해 봅시다.

오라클을 설치하면 SCOTT/TIGER USER에 기본적으로 EMP, DEPT테이블이 있습니다.
그 데이터를 TEST/TEST USER에 생성한 EMP, DEPT테이블에 INSERT하겠습니다.


SQL> INSERT INTO DEPT
        SELECT * FROM SCOTT.DEPT ;

4 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.

SQL> INSERT INTO EMP
         SELECT * FROM SCOTT.EMP;

15 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.



▣ UPDATE


테이블 안의 데이터를 수정 합니다.


 

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


사용하지 않는 데이터를 삭제 합니다.


SQL>DELETE FROM EMP
       WHERE EMPNO = 7902 ;
        사원번호가 7902번인 사람의 데이터가 삭제 되었습니다.

SQL>DELETE FROM EMP
        WHERE SAL < (SELECT AVG(SAL) FROM EMP) ;
        평균급여보다 적게 받는 사원 삭제

SQL>DELETE FROM EMP ;
        테이블의 모든 행이 삭제 됩니다.




★ Select문 및 연산자


SELECT문은 데이터베이스로부터 저장되어 있는 데이터를 검색하는데 사용합니다.

[Syntax]


 · 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

      사번      성명
---------- ---------------
      7782      CLARK
      7839      KING
      7934      MILLER



empno와 ename 은 각각 사번과 성명이라는 컬럼 별칭(alias)으로 만들어 출력했습니다.
alias를 사용할 때 as라는 키워드를 사용해도 되고, 생략할수도 있습니다.


▒ WHERE절에 사용될 수 있는 SQL 연산자

 연산자

 설      명

 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)

 list의 값과 일치하지 않는 데이터를 출력 합니다.

 NOT LIKE

 문자 형태와 일치하지 않는 데이터를 출력 합니다.

 IS NOT NULL

 NULL값을 갖지 않는 데이터를 출력 합니다.




▣ IN, NOT IN 연산자


IN 연산자
SQL> SELECT empno, ename
         FROM  emp
         WHERE  empno IN (7900, 7934) ;
--> 사번이 7900, 7934번인 사원의 사번과 성명 출력

    EMPNO    ENAME
 --------- -------------
     7934      MILLER
     7900      JAMES

2 개의 행이 선택되었습니다.

NOT IN 연산자
SQL> SELECT empno, ename
         FROM  emp
         WHERE  empno NOT IN (7900, 7934);
--> 사번이 7900, 7934번이 아닌 사원의 사번과 성명 출력

     EMPNO ENAME
-------- --------------
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
    ............................
13 개의 행이 선택되었습니다.



BETWEEN
연산자(AND를 이용해 두 조건을 결합한 검색과 같은 결과값을 보여줍니다.)

BETWEEN 연산자
SQL>  SELECT empno, ename
          FROM  emp
          WHERE  sal BETWEEN  3000 AND 5000 ;
--> 급여가 3000에서 5000사이인 사원만 보여줍니다.

     EMPNO ENAME
   ---------- ------
      7788 SCOTT
      7839 KING
      7902 FORD  
3 개의 행이 선택되었습니다.



LIKE 연산자


 - 검색 STRING 값에 대한 와일드 카드 검색을 위해서 LIKE연산자를 사용 합니다.
 - % :  여러개의 문자열을 나타내는 와일드 카드
 - _ : 단 하나의 문자를 나타내는 와일드 카드
 - ESCAPE : 와일드 카드 문자를 일반문자 처럼 사용하고 싶은 경우에 사용합니다.
   ☞ WHERE name LIKE '%a\_y%' ESCAPE '\' ;

구 분

설 명

LIKE 'A%'

컬럼이 'A'로 시작하는 데이터들만 검색됩니다.

LIKE '%A'

컬럼이 'A'로 끝나는 테이터들만 검색됩니다.

LIKE '%KIM%'

컬럼에 'KIM' 문자가 있는 데이터 들만 검색됩니다.

LIKE '%K%I%'

컬럼에 'K' 문자와 'I'문자가 있는 데이터 들만 검색됩니다.

LIKE '_A%'

컬럼에 'A'문자가 두 번째 위치한 데이터 들만 검색됩니다.


- LIKE 연산자는 대소문자를 구분합니다.
- Upper()함수를 이용해 대소문자 구분없이 출력할수 있습니다.


SQL>SELECT empno, ename
        FROM  emp
        WHERE  UPPER(ename) like '%K%';

EMPNO ENAME
------- -----------
   7698 BLAKE
   7782 CLARK
   7839 KING

'K' 문자가 들어있는 사원 정보를 보여줍니다.
upper()라는 함수는 k가 들어가 있는 것도 대문자 'K'로 인식하기 때문에 데이터들을 보여줍니다.



※ '_'를 이용한 LIKE검색

SQL>SELECT empno, ename
        FROM  emp
        WHERE  UPPER(ename) like '_I%'

  EMPNO ENAME
------- ----------
   7839 KING
   7934 MILLER

※ '_'는 한 문자를 나타냅니다.
   'I' 문자가 두 번째 문자에 위치한 사원들의 정보를 보여줍니다.

 



ORDER BY
(ASC[오름차순], DESC[내림차순])
  ORDER BY 절은 데이터의 정렬을 위해 사용합니다.  

SQL> SELECT empno, ename
         FROM  emp
         WHERE  deptno = 30
         ORDER BY ename ASC;

   EMPNO ENAME
-------- ---------
    7499 ALLEN
    7698 BLAKE
    7900 JAMES
    7654 MARTIN
    7844 TURNER
    7521 WARD

SQL> SELECT empno, ename
         FROM  emp
         WHERE  deptno = 30
         ORDER BY 2

위 두 개의 쿼리는 동일한 결과를 가져 옵니다.
 




★ 예명(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라는 예명을 주었습니다.

위 예제와 같이 예명은 컬럼에만 주는 것이 아니라. 쿼리 문 및 테이블에도 사용할 수 있습니다.
 




Equi Join, Non_Equi Join, Self 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 문안에 포함시켜서 다른 테이블 안에 있는 각 테이블의 컬럼이
   적어도 한번은 참조되도록 해야 합니다.



☞ Equi Join

 -
조건절 Equality Condition(=)에 의하여 조인이 이루 집니다.

 - Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋습니다.

SQL>SELECT  e.ename, d.dname
         FROM   emp e , dept d
          WHERE
e.deptno = d.deptno;

-
WHERE 절에 조인 조건을 작성하고 column명 앞에 테이블명을 적습니다.



☞ 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;
ENAME                DNAME
----------------- -----------
SCOTT               ACCOUNTING
FORD                 ACCOUNTING
SCOTT               RESEARCH
FORD                 RESEARCH



☞ Self Join

 - Equi Join과 같으나 하나의 테이블에서 조인이 일어나는 것이 다름니다.

 - 같은 테이블에 대해 두 개의 alias를 작성함으로 FROM절에 두 개의 테이블을 사용
    하는 것과 같이 합니다.

SQL> SELECT concat(a.ename,'  ') ||' : \'|| b.sal  급여
         FROM emp a, emp b
         WHERE a.empno = b.empno

급여
-------------------
SMITH   : \800
ALLEN   : \1600
WARD   : \1250
JONES   : \2975
MARTIN   : \1250
BLAKE   : \2850
CLARK   : \2450
SCOTT   : \3000

이름과 급여를 연결시켜서 보여줍니다.




Outer Join (LEFT, RIGHT, FULL OUTER JOIN)


Out(외부) Join

 - equijoin 문장들의 한가지 제약점은 그것들이 조인을 생성하려 하는 두 개의 테이블의 두 개
    컬럼에서 공통된 값이 없다면 테이블로부터 테이터를 Return하지 않는 다는 것입니다.

 - 정상적으로 조인 조건을 만족하지 못하는 행들을 보기위해 outer join을 사용합니다.
    Outer join 연산자 "( + )"입니다.

 - 조인시킬 값이 없는 조인측에 "( + )"를 위치 시킵니다.

 - Outer join 연산자는 표현식의 한 편에만 올 수 있습니다.


예제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

※ 다음의 쿼리를 한번 잘 보시기 바랍니다.

SQL>  SELECT DISTINCT(a.deptno), b.deptno
          FROM emp a, dept b
          WHERE  a.deptno(+) = b.deptno
               AND a.ename LIKE '%';

    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30

쿼리 결과를 잘 보면 out조인이 되지 않은 것을 알 수 있습니다.
위 쿼리를 out조인이 되기 위해서는 아래와 같이 고쳐야 합니다
.

SQL> SELECT DISTINCT(a.deptno), b.deptno
         FROM emp a, dept b
         WHERE  a.deptno(+) = b.deptno
              AND a.ename(+) LIKE '%'

    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30
                    40

OUT조인 조건이 걸려있는 테이블에는 다른 조건절이 들어와도
똑같이 OUT조인 연산자인 (+)를 해주어야 합니다.  



Oracle9i 부터는 ANSI/ISO SQL표준인 LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL OUTER JOIN를 지원 합니다.


LEFT OUTER JOIN
 왼쪽 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다.
 
SQL>SELECT DISTINCT(e.deptno), d.deptno
    FROM dept d LEFT OUTER JOIN emp e
    ON d.deptno = e.deptno;
 
 
RIGHT OUTER JOIN
 - 오른쪽에 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다.
 
SQL>SELECT DISTINCT(a.deptno), b.deptno
    FROM emp a RIGHT OUTER JOIN dept b
    ON a.deptno = b.deptno;
 
 
FULL OUTER JOIN
양쪽 테이블에 다 outer join을 거는것을 TWO-WAY OUTER JOIN 또는 FULL OUTER JOIN이라 합니다.
 
SQL>SELECT DISTINCT(a.deptno), b.deptno
    FROM emp a FULL OUTER JOIN dept b
    ON a.deptno = b.deptno;
 
-- 위 세 문장의 결과는 아래와 같습니다.
    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30
                   40
 
LEFT OUTER JOIN과 RIGHT OUTER JOIN의 테이블 순서를 바꾸어 가면서 테스트를 하시면 쉽게 이해를 하실 수 있습니다.




Commit과 Rollback 예제


Commit 과 Rollback

 
이전의 커밋(COMMIT)이 일어난 뒤부터 다음의 커밋(COMMIT) 전까지의 작업이 하나의 트랜
잭션 이며
, 커밋과 롤백(ROLLBACK)은 이러한 트랜잭션 단위로 데이터 베이스에서 발생한
작업을 저장, 삭제하는 일입니다.

 
- Automatic commit : DDL(Create, Alter, Drop), DCL(Grant, Revoke)

 
- Automatic Rollback: 비정상적인 종료, system failure  
 
커밋과 롤백 예제입니다.


SQL>DELETE FROM  emp WHERE empno = 7521 ;
       한 개의 행이 삭제 되었습니다.

SQL>COMMIT;
      커밋이 완료 되었습니다
 
  한 개의 행을 삭제하고, COMMIT 문으로 데이터를 영구히 저장했습니다. 이 것은 하나의 트랜잭션이 여기서 종료되고 새로운 트랜잭션이 발생하는 것을 말합니다.

SQL>SELECT empno FROM EMP WHERE empno = 7521;
       선택된 레코드가 없습니다.



SQL>INSERT INTO emp(empno, ename, hiredate) VALUES(9000, 'test', sysdate );
     한 개의 행이 작성되었습니다.

SQL>COMMIT;
     커밋이 완료 되었습니다.

SQL>DELETE FROM emp WHERE empno = 9000;
     한 개의 행이 삭제 되었습니다.

SQL>SELECT empno FROM emp WHERE empno = 9000 ;
       선택된 레코드가 없습니다.  


의 예제를 보면은 empno가 9000번인 데이터를 Insert한 후 commit으로 데이터를 저장한 다음에
데이터를 다시 삭제한 후 Select를 해보면 데이터가 검색되지 않는 것을 알 수 있습니다.

하지만 다른 유저에서는 커밋이나 롤백을 하기 전까지 이전에 Insert한 empno가 9000번인 데이터를
조회하면 데이터가 검색 됩니다.

데이터베이스에서의 이런 기능을 읽기 일관성이라고 합니다.


SQL>ROLLBACK ;
     롤백이 완료되었습니다.

(이전에 트랜잭션(커밋)이 발생하고나서 지금 발생한 ROLLBACK 문 전까지의 작업의 취소를 말합니다. )


검색을 해보면 커밋이 완료된 시점의 레코드 하나가 검색 됩니다.
SQL>SELECT  empno FROM  emp WHERE empno = 9000 ;

   EMPNO
----------
      9000
     한 개의 행이 선택되었습니다.



SAVEPOINT 와 ROLLBACK TO

  SAVEPOINT
는사용자가 트랜잭션의 작업을 여러개의 세그먼트로 분할할 수 있도록 하는 특별한
 작업입니다.
 SAVEPOINT는 부분적인 롤백을 가능하게 하기 위해 트랜잭션에 대한 중간점을 정의 합니다.
 

SQL>INSERT INTO emp(empno, ename, hiredate) VALUES(10000, 'test2', sysdate );
       한 개의 행이 작성되었습니다.

SQL>SAVEPOINT A;
       저장점이 생성되었습니다. (여기서 SAVEPOINT를 생성했습니다.)

SQL>INSERT INTO emp(empno, ename, hiredate) VALUES(10001, 'test3', sysdate );
     
  한 개의 행이 작성되었습니다.

SQL>INSERT INTO emp(empno, ename, hiredate) VALUES(10002, 'test4', sysdate );
   
    한 개의 행이 작성되었습니다.

SQL>DELETE FROM emp WHERE empno IN(10000, 10001, 10002);
       세 개의 행이 삭제 되었습니다.

SQL>SELECT empno, ename FROM emp WHERE empno IN(10000, 10001, 10002);
   
   선택된 행이 없습니다.

SQL>ROLLBACK TO A;
        롤백이 완료되었습니다. (SAVEPOINT까지만 롤백이 시행됩니다.)

SQL>SELECT empno , ename FROM emp WHERE empno IN(10000, 10001, 10002);
        한 개의 행이 선택되었습니다.

     EMPNO ENAME
--------- ---------------
    10000 test2

  SAVEPOINT까지만 롤백이 실행되었습니다. 그 결과 첫 번재 데이터는 그대로 남고,
 SAVEPOINT 후에 실행된 데이터 입력은 삭제되었습니다.




숫자함수(Number Functions)



숫자함수(Number Functions)



ABS(n)


ABS함수는 절대값을 계산하는 함수입니다.

SQL>SELECT ABS(-10) Absolute FROM dual ;

Absolute
--------
       10



CEIL(n)


CEIL함수는 주어진 값보다는 크지만 가장 근접하는 최소값을 구하는 함수입니다.

SQL>SELECT CEIL(10.1) TEST FROM dual ;

  TEST
-------
      11

SQL>SELECT CEIL(-10.1) TEST FROM dual ;

   TEST
-------
     -10



EXP(n)


EXP함수는 주어진 값의 e의 승수를 나타냅니다.
e는 2.171828183..입니다.



FLOOR(n)

FLOOR함수는 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수입니다.
CEIL 함수와 비교해 보세요.

SQL>SELECT FLOOR(10.1) TEST FROM dual ;

    TEST
 -------
       10

SQL>SELECT FLOOR(-10.1) TEST FROM dual ;
   
    TEST
-------
      -11



LN(n)


LN함수는 주어진 값의 자연로그 값을 반환합니다.



MOD(m, n)


MOD함수는 m을 n으로 나누어 남은 값을 반환한다. n이 0일 경우 m을 반환합니다.

SQL>SELECT MOD(9, 4) TEST FROM dual ;

    TEST
  -------
         1



POWER(m, n)


POWER함수는 m의 n승 값을 계산합니다.

SQL>SELECT POWER(4, 2) TEST FROM dual ;
       
      TEST
   -------
         16



ROUND(n, [m])


ROUND함수는 n값의 반올림을 하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.

SQL>SELECT ROUND(192.123, 1) TEST FROM dual ;

      TEST
   -------
      192.1

SQL>SELECT ROUND(192.123, -1) TEST FROM dual ;

     TEST
   -------
       190



SIGN(n)


SIGN함수는 n<0일 경우 -1DFM N=0일 경우 0을 N>0일 경우 1을 반환합니다.



SQRT(n)


SQRT함수는 n값의 루트값을 계산한다. n은 양수여야 합니다.



TRUNC(n, m)


 TRUNC함수는 n값을 m 소숫점 자리로 반내림한 값을 반환합니다.
 ROUND 함수와 비교해 보세요..

SQL>SELECT TRUNC(7.5597, 2) TEST FROM dual ;

    TEST
 -------
     7.55

SQL>SELECT TRUNC (5254.26, -2 ) TEST FROM dual ;

    TEST
 -------
     5200



Oracle9i Number Functions
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
POWER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET



문자열 처리 함수(Character Functions)


문자열 처리 함수(Character Functions)


CONCAT(char1, char2)
CONCAT 함수는 Concatenation의 약자로 두 문자를 결합하는 역할을 합니다.
"
||" 연산자와 같은 역할을 합니다.

SQL>SELECT CONCAT('Oracle', ' Korea') NAME FROM dual ;

                NAME
   -------------
   Oracle Korea



INITCAP(char)


주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 줍니다.

SQL>SELECT INITCAP('kim jung sick') NAME FROM dual ;

             NAME
 -------------
  Kim jung sick



LOWER(char)


문자열을 소문자로 변환 시켜 줍니다.


UPPER(char)

문자열을 대문자로 변환 시켜 줍니다.

SQL>SELECT LOWER('KIM JUNG SICK') NAME FROM dual ;

            NAME
-------------
  kim jung sick

SQL>SELECT UPPER('kim jung sick') NAME FROM dual ;

            NAME
--------------
KIM JUNG SICK



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*



SUBSTR(char, m ,[n])


  SUBSTR함수를 이용하여 m 번째 자리부터 길이가 n개인 문자열을 반환한 합니다. m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환합니다.

SQL>SELECT SUBSTR('JUNG-SICK', 3, 3) NAME FROM dual ;

          NAME
-----------
            NG-

-- 뒤에서부터 자를
SQL>SELECT SUBSTR('JUNG-SICK', -3, 3) NAME FROM dual ;

        NAME  
-----------
          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','JA','BL') "Changes" FROM DUAL
 
Changes
------------
BLCK and JUE
 
 
-- 대소문자를 구분한다는 것을 알수 있습니다.
SQL>SELECT REPLACE('JACK and JUE','j','BL') "Changes" FROM DUAL
 
Changes
------------
JACK and JUE



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
 

-- 왼쪽에서 3번째부터 시작을 해서 비교를 합니다. 2번째 OR의 위치가 반환 됩니다.
SQL>SELECT INSTR('CORPORATE FLOOR','OR', 3)  "Instring" FROM DUAL
 
  Instring
----------
         5


-- 왼쪽에서 3번째부터 시작을 해서 비교를 하는데  OR이 두 번째 검색되는 지점의 위치를 반환 합니다.
SQL> SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)  "Instring" FROM DUAL;
 
  Instring
----------
       14
 



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
------------
공백
 
 
SQL>SELECT NVL('  ','공백')  "TRIM Example" FROM DUAL
 
TRIM Example
------------
 





LTRIM

SQL>SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"  FROM DUAL;
 
LTRIM example
------------
XxyLAST WORD
 
 
RTRIM

SQL>SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM example"     FROM DUAL;

RTRIM examp
-----------
BROWNINGyxX

Character Functions
CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER
ASCII
INSTR
LENGTH

날짜 처리 함수(Date Functions)


LAST_DAY(d)

LAST_DAY함수는 달의 마지막 날의 날짜를 반환합니다

SQL>SELECT SYSDATE TODAY, LAST_DAY(SYSDATE) LASTDAY FROM dual ;

         TODAY             LASTDAY
-----------------------------
05-JUN-2000       30-JUN-2000        

 오늘이 6월 5일이니깐요 6월달의 마지막 날30일을 반환합니다.



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       오늘이 6월5일 이니깐요. 3개월이 더해진 9월 5일이 반환됩니다.



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
  ----------------
         -3.880635           달사이의 간격을 숫자형으로 반환합니다.



ROUND(d[,F])


ROUND 함수는 F에 지정된 단위로 반올림 합니다, F가 연도라면 연도 단위로 반올림 합니다.

SQL>SELECT ROUND(TO_DATE('1998/09/11'), 'YEAR')  FROM dual ;

    ROUND(TO_
--------------
       99-01-01


SQL>SELECT ROUND(TO_DATE('1998/04/11'), 'MONTH') FROM dual ;

    ROUND(TO_
---------------
       98-04-01

SQL>SELECT ROUND(TO_DATE('1998/04/11'), 'DAY')  FROM dual ;

    ROUND(TO_
 ---------------
       98-04-11    


 ☞ 날짜에 대한 산술 연산

연  산

결과치

사  용  목  적

날짜 + 숫자

날짜

  특정한 날로부터 며칠 후의 날짜 계산

날짜 - 숫자

날짜

  특정한 날로부터 며칠 전의 날짜 계산

날짜 - 날짜

숫자

  두 날짜 사이의 차이를 숫자로 계산

Datetime Functions

ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_DSINTERVAL
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET



 

변환 함수(Conversion Functions)


TO_CHAR

TO_CHAR함수는 DATE형, NUMBER형을 VARCHAR2형으로 바꺼 줍니다.

SQL>SELECT TO_CHAR(SYSDATE, 'MONTH') CHARTEST FROM dual ;

       CHARTEST
 --------------
             JUNE

오늘이 6월 10일 이니깐요.. 오늘의 달인 6월이 찍힘니다.

SQL>SELECT TO_CHAR(SYSDATE) CHARTEST FROM dual ;

       CHARTEST
 --------------
          00/06/10

오늘이 6월 10일 이니깐요.. 오늘의 날짜가 문자형으로 찍힙니다.



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'문자열이 숫자형으로 변합니다

Conversion Functions
ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR




General Functions


NVL

- NVL 함수는 NULL값을 다른 값으로 바꿀 때 쓰입니다.
-
모든 데이터 타입에 적용 가능합니다.
- 전환되는 값의 데이터 타입을 일치시켜야 합니다.

SQL>SELECT empno, NVL(comm, 0)  
        FROM  emp  
        WHERE deptno = 30;

         EMPNO  NVL(COMM,0)
       ----------     -----------
          7499              300
          7521              500
          7654             1400
          7698                0
          7844                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
        30        SALES
        20        RESEARCH
        30        SALES
        30        SALES
        10        ACCOUNTING
        20        RESEARCH

부서가 10번이면 'ACCOUNTING'를 20번이면 'RESEARCH'를
30번이면 'SALES'를 40번이면 'OPERATIONS'를 출력하는 예제 입니다.

기타 함수들


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수고요..  
Typ은 1만 나오던데..무엇을 의미하는지 잘 모르겠네용

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


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            BLAKE


Miscellaneous Single-Row Functions
BFILENAME
COALESCE
DECODE
DEPTH
DUMP
EMPTY_BLOB, EMPTY_CLOB
EXISTSNODE
EXTRACT (XML)
EXTRACTVALUE
GREATEST
LEAST
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
NVL2
PATH
SYS_CONNECT_BY_PATH
SYS_CONTEXT
SYS_DBURIGEN
SYS_EXTRACT_UTC
SYS_GUID
SYS_TYPEID
SYS_XMLAGG
SYS_XMLGEN
UID
UPDATEXML
USER
USERENV
VSIZE
XMLAGG
XMLCOLATTVAL
XMLCONCAT
XMLFOREST
XMLSEQUENCE
XMLTRANSFORM


Group Function의 종류


☞ 그룹함수란 ?

 - 그룹 함수란 여러 행 또는 테이블 전체의 행에 대해 함수가 적용되어 하나의 결과값을 가져오는
   함수를 말합니다..
 
 - 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컬럼중에서 제일 큰값을 반환합니다. 즉 가장 큰 급여를 반환합니다.


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
           ----------
                1566.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
          ----------
              668.331          30부서 사원의 급여 표준편차를 반환합니다.




Group By절과 Having절


GROUP BY

 -  특정한 컬럼의 테이터 들을 다른 데이터들과 비교해 유일한 값에 따라 무리를 짓습니다.

 - GROUP BY절을 사용하여 한 테이블의 행들을 원하는 그룹으로 나눕니다.

 - Column명을 GROUP함수와 SELECT절에 사용하고자 하는 경우 GROUP BY뒤에 Column명을
   추가 합니다.
 


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

부서별로 그룹을 지은 검색 결과 값이며
부서별로 사원수를 보여줍니다. .


☞ Group By 예제

scott/tiger유저로 접속해서 실행하세요..

예제1) 부서별로 그룹하여  부서번호, 인원수, 급여의 평균, 급여의 합을 구하여 출력 하여라.
 
SQL>SELECT deptno,COUNT(*),ROUND(AVG(sal)) "급여평균", ROUND(SUM(sal)) "급여합계"
        FROM emp
        GROUP BY deptno;
 
 
   DEPTNO   COUNT(*)   급여평균   급여합계
--------- ---------- ---------- ----------
       10             3             2998         8995
       20             5             2175        10875
       30             6             1567         9400



예제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      1037.5        1300          800       4150
MANAGER           3        2840        2975         2695       8520
PRESIDENT         1        5000        5000         5000       5000
SALESMAN          4        1400        1600         1250       5600

 



GROUP BY의 HAVING 절

 -  WHERE절에 GROUP Function을 사용할 수 없습니다.

 -  HAVING절은 GRUOP 함수를 가지고 조건비교를 할 때 사용 합니다.

 -  WHERE → GRUOP BY → HAVING → ORDER BY순으로  쿼리문이 와야 됩니다.
 

HAVING절 예제


예제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

 




인덱스(Index)



※ 인덱스란?


  인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의
원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조
입니다.  

자동 인덱스 : 프라이머리 키 또는 uinque 제한 규칙에 의해 자동적으로 생성되는 인덱스 입니다.

수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스들 입니다.  

※  Index를 생성하는 것이 좋은 Column

WHERE절이나 join조건 안에서 자주 사용되는 컬럼
null 값이 많이 포함되어 있는 컬럼
WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들


※  다음과 같은 경우에는 index 생성이 불필요 합니다.
table이 작을 때
테이블이 자주 갱신될 때

※  오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있습니다.

  B-tree인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 냅니다.

이 알고리즘 원리는

 ① 주어진 값을 리스트의 중간점에 있는 값과 비교합니다.    
     만약 그 값이 더 크면 리스트의 아래쪽 반을 버립니다.
     만약 그 값이 더 작다면 위쪽 반을 버립니다.

 ② 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도
     반복합니다.



 ※  인덱스는 B-tree 구조를 가지며 크게 다음 네 가지로 분류될수 있습니다.


Bitmap 인덱스

  비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 가장 잘 작동합니다.
  그러므로 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킵니다.
  테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있습니다.

SQL>CREATE BITMAP INDEX emp_deptno_indx
        ON emp(deptno);


Unique 인덱스

  Unique 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있습니다.
  프라이머리키 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스입니다.

SQL>CREATE UNIQUE INDEX emp_ename_indx
        ON  emp(ename);


Non-Unique 인덱스

   Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있습니다.

SQL>CREATE INDEX  dept_dname_indx
        ON  dept(dname);


결합 (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



★ VIEW 테이블



뷰란?

 ◈ 뷰는하나의 가상 테이블라 생각 하시면 됩니다.

 ◈ 뷰는 실제 데이터가 저장 되는 것은 아니지만 뷰를 통해 데이터를 관리 할수 있습니다.

 ◈ 뷰는 복잡한query를 통해 얻을 수 있는 결과를 간단한 query를 써서 구할 수 있게 합니다.

 ◈ 한개의 뷰로 여러 테이블에 대한 데이터를 검색할 수 있습니다.

 ◈ 특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있도록 합니다.



뷰의 제한 조건.


 ◈ 테이블에 NOT NULL로만든 컬럼들이. 뷰에 다 포함이 되 있어야 됩니다.

 ◈ 그리고 ROWID, ROWNUM, NEXTVAL, CURRVAL등과 같은 가상컬럼에 대한 참조를
     포함하고 있는 뷰에는 어떤 데이터도 Insert할 수 없습니다.

 ◈ WITH READ ONLY 옵션을 설정한 뷰도 데이터를 갱신할수 없습니다.

 ◈ WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는 데이터만 삽입, 삭제,
   
수정을 할수 있습니다.



 

 
 
  - FORCE : 기본 테이블 유무에 관계없이 VIEW를 생성

  - WITH CHECK OPTION : VIEW에 의해 엑세스될 수 있는 행만이 입력되거나 변경될 수 있음을
                                       지정 합니다.

  - WITH READ ONLY : SELECT만 가능한 VIEW의 생성

  - VIEW를 정의하는 질의어에는 ORDER BY절을 사용 할 수 없습니다.

  - 함수를 사용한 컬럼은 반드시 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


view created.

이렇게 뷰를 생성해 놓고 뷰를 통해 검색을 하면 됩니다.

SQL>SELECT * FROM Name_Query;

ENAME                DNAME
-------------------- ----------
SMITH                RESEARCH
JONES                RESEARCH
SCOTT               RESEARCH
ADAMS               RESEARCH
FORD                 RESEARCH



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

view created.

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.

단순히 읽기 만 할수 있고 데이터는 입력하지 못합니다.



VIEW에 대한 DML문 사용 규칙


  VIEW가 다음 사항을 포함하는 경우 행의 삭제 불가
   - JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령

 
 VIEW가 다음 사항을 포함하는 경우 데이터 수정 불가
   - JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령, 식으로 정의된 컬럼(EX SAL * 0.1)

 
 VIEW가 다음 사항을 포함하는 경우 데이터 추가 불가
   - JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령, 식으로 정의된 컬럼,
     VIEW에 선택되지 않는 NOT NULL컬럼
 


뷰의 정보 조회

USER_VIEWS 데이터 사전을 통해서 뷰에 대한 정보를 조회 할 수 있습니다.

SQL>SELECT view_name , text
       FROM  USER_VIEWS;


뷰의 삭제

SQL>DROP VIEW Read_Only;

      view dropped.



SEQUENCE(시퀀스)



 시퀀스란?


유일(UNIQUE)한 값을 생성해주는 오라클 객체입니다.

◈ 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할수 있습니다.

◈ 보통 primary key 값을 생성하기 위해 사용합니다.

◈ 메모리에 Cache되었을 때 Sequence 값의 액세스 효율이 증가 합니다.

◈ Sequence는 테이블과는 독립적으로 저장되고 생성됩니다. 따라서 하나의 sequence를
    여러 테이블에서 쓸 수 있습니다.
 



시퀀스 생성





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를 지정할 경우 최소값은 무한대가 됩니다

 


 SQL>CREATE SEQUENCE emp_seq
        START WITH 1
        INCREMENT BY 1
        MAXVALUE 100000 ;

      sequence created.

     시작 값이 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 ;

        CURRVAL
        ---------
                  1

 SQL>SELECT emp_seq.NEXTVAL FROM DUAL ;

       NEXTVAL
     ---------
                 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값


시퀀스의 수정 및 삭제





START WITH는 수정할수 없습니다.
START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE와 같습니다.
 


 SQL>ALTER SEQUENCE emp_seq
         INCREMENT BY 2
         CYCLE;

      sequence altered.

      2씩 증가하고, 최대값을 넘으면 다시 처음부터 순환하도록 수정하였습니다.


      DROP 문을로 필요하지 않은 시퀀스는 삭제 할수 있습니다.

 SQL>DROP SEQUENCE PRD_SEQ;
        sequence dropped.




SYNONYM(동의어)


시노님은 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(Alias)를 말합니다.
 
Synonym은 실질적으로 그 자체가 Object가 아니라 Object에 대한 직접적인 참조 입니다.
 
시노님을 사용하는 이유는..
 
데이터베이스의 투명성을 제공하기 위해서 사용 한다고 생각하면 됩니다.
    시노님은 다른 유저의 객체를 참조할 때 많이 사용을 합니다.
 
② 만약에 실무에서 다른 유저의 객체를 참조할 경우가 있을 때 시노님을 생성해서 사용을 하면은
    추후에 참조하고 있는 오프젝트가 이름을 바꾸거나 이동할 경우 객체를 사용하는 SQL문을 모두
    다시 고치는 것이 아니라 시노님만 다시 정의하면 되기 때문에 매우 편리 합니다.
 
객체의 긴 이름을 사용하기 편한 짧은 이름으로 해서  SQL코딩을 단순화 시킬 수 있습니다.
 
또한 객체를 참조하는 사용자의 오브젝트를 감추 수 있기 때문에 이에 대한 보안을 유지할 수
    있습니다
.  
   시노님을 사용하는 유저는 참조하고 있는 객체를에 대한 사용자의 object의 소유자, 이름, 서버이름을  모르고 시노님 이름만 알아도 사용 할 수 있습니다.



Synonyms을 사용하는 경우

 - 오브젝트의 실제 이름과 소유자 그리고 위치를 감춤으로써 database 보안을 개선하는데 사용 됩니다
 - Object에의 Public Access를 제공 합니다.
 - Remote Database의 Table, View, Program Unit를 위해 투명성을 제공 합니다.
 - Database 사용자를 위해 SQL 문을 단순화 할 수 있습니다.


시노님에는 두가지 종류가 있습니다.  

Private Synonym  
   - 전용 시노님은 특정 사용자만  이용할수 있습니다.
 
Public Synonym
  - 공용 시노님은 공용 사용자 그룹이 소유하며 그 Database에 있는 모든 사용자가 공유 합니다.


 시노님 생성 문법(Syntax)




 - PUBLIC : 모든 사용자가 접근 가능한 시노님을 생성 합니다.
                PUBLIC  시노님의 생성 및 삭제는 DBA만이 할 수 있습니다.

출처 블로그 > 서울전문학교
원본 http://blog.naver.com/7441472/150020439202


 ※ scott USER의 emp테이블을 test USER가 사용 하는 예제.


 1. 먼저 scott/tiger USER로 접속해서 test USER에게 emp테이블을 조작할 권한을 부여합니다.
 
 SQL>GRANT ALL ON  emp TO  test;
         권한이 부여되었습니다.

         test user에 대하여 scott의 emp테이블을 조작할 수 있는 권한을 부여합니다.
         권한이 있어야 select하거나 update, insert할수 있습니다.



 2. test USER로 접속해 동의어를 생성합니다.

 SQL> connect test/test

 SQL> CREATE SYNONYM  scott_emp FOR  scott.emp ;
         시노님이 생성되었습니다.

          scott USER가 소유하고 있는 emp 테이블에 대해 scott_emp라는 일반시노님을 생성했습니다.
          scott 사용자의 emp테이블을 test 사용자가 scott_emp라는 동의어로 사용 합니다. .

  -- 시노님을 이용한 쿼리
 SQL> SELECT empno,  ename FROM  scott_emp;

 -- 일반 테이블을 쿼리
 SQL> SELECT empno,  ename FROM  scott.emp;
          이 두 쿼리의 결과는 같습니다.

   EMPNO ENAME
-------- ---------
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
          15 개의 행이 선택되었습니다.


 동의어 삭제

 SQL> DROP SYNONYM   scott_emp;
         시노님이 삭제되었습니다.

 SQL> SELECT empno,  ename FROM  scott_emp;
         라인 1 에 오류:
         ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

Posted by 행복한 프로그래머 궁금쟁이박
TAG 오라클

댓글을 달아 주세요

    1. 기존 데이터베이스 백업 받기

우선 export 툴인 exp 이용하여 전체 database 백업 받습니다.
$ exp system/manager file=fullbackup.dmp full=y
 
그리고 기존의 데이터중 그대로 사용할 데이터 또는 만약 재설치에 실패했을 때를 대비해서
기존의 데이터를 모두 백업을 받고, 현재의 설정도 백업받아야 겠지요.
 
SQL>spool bakdata.txt
SQL>select * from v$dba_users; - 유저들의 디폴트 테이블스페이스, temp테이블스페이스
SQL>select * from v$datafile; -- 각각의 데이터파일의 위치와 크기
SQL>select * from dba_tablespaces -- 테이블 스페이스 정보
SQL>select * from dba_data_files; -- 테이블 스페이스와 파일의 구성정보
SQL>select * from dba_rollback_segs; -- 롤백 세그먼트 정보
SQL>select * from v$log; -- redo log 정보
SQL>select * from v$logfile; -- redo log file 정보
SQL>spool off
 
그리고 파라미터 파일인 init<SID>.ora, (ifile 이라는 파라미터로 설정되어있는)config*.ora 백업.
 
한가지 있습니다.
바로 control file 입니다. 이것은 복구 중요한 파일입니다.
control 파일은 2 파일이므로, 생성해주는 sql 스크립트 파일을 가지고 있으면 여러 가지로 편리합니다.
 
SQL>alter database backup controlfile to trace ;
 
이렇게 하고 init .ora 파일에(또는 config .ora) 설정된 user_dump_dest 디렉토리로 가서
가장 최근의 *.trc 파일을 열어보면 control 파일을 만드는 스크립트가 포함되어 있습니다.
이것을 편집해 놓으면 바로 controlfile 만들수 있습니다.
 
 
 
ORACLE_SID (= DB name) 을 변경했을 경우
 
- UNIX의 경우
ORACLE_SID, DB_NAME 파라미터가 들어가는 모든 파일을 찾아서 수정해야 한다.
 
1. 홈디렉토리에서 환경변수 설정파일인 .profile(또는 .cshrc)의
ORACLE_SID=<new_SID> 부분을 편집하고
$. .profile (.cshrc 의 경우 $ source .cshrc로 실행)
로 실행시켜 준다. 아니면,
$export ORACLE_SID=ORATEST
수행시켜 준다.
2. /opt/var/oracle/oratab 파일을 열어 SID:ORACLE_HOME path:Y/N를 수정
3. init*.ora 또는 config*.ora 파일의 db_name을 수정
4. 아래에 나올 crdb*.sql 파일을 수정
 
- NT 경우
regedit 실행시켜 local_machine > software > oracle > home0 (oracle 8)
이동하여 ORACLE_SID 수정합니다. 그리고 init*.ora 또는 config*.ora 파일을 수정.
 
 

2. database 생성 스크립트 crdb*.sql , crdb2*.sql 편집

 
oracle 7.3 이상의 경우
$ORACLE_HOME=/home1/oracle/app/oracle/product/7.3.3
이라면 crdb*.sql 파일들
/home1/oracle/app/oracle/admin/ORA7/create
위치합니다.
위치로 이동하여 새로운 이름으로 copy하고서, 필요한 내용을 수정합니다.
이때 init *.ora 파라미터 파일과 연동되는 부분과 각종 데이터 파일의
위치와 크기를 수정할 때는 주의를 기울여야 합니다.
 
NT 인경우 스크립트가 없습니다. sample 스크립트 파일을 참조하세요.
 

3. Create Database

 
$svrmgr <-------------------------- 서버메니져를 실행 (NT의 경우 svrmgrl.exe)
SVRMGR>connect internal <----- 로그인
SVRMGR>@crdboracle.sql
반드시 에러를 확인하고 다음으로 넘어 가야 합니다.
SVRMGR>@crdb2oracle.sql
확인 해야죠. 시간이 대략 1시간 정도.걸립니다.
 
이 과정이 끝나면 데이터 파일들은 제대로 생성되었는지 꼼꼼히 확인하고 다음으로 넘어가야 합니다.
보통 아래과정에서 에러가 생기기 마련입니다.
 

4. system table/view 생성

 
우선 ORACLE_HOME/rdbms/admin 디렉토리로 이동하여
catalog.sql, catproc.sql, catexp.sql 3가지 스크립트 파일이 존재하는지 확인하십시오.
 
SVRMGR>connect internal
SVRMGR>@$ORACLE_HOME/rdbms/admin /catalog
-- 20 소요
SVRMGR>@$ORACLE_HOME/rdbms/admin /catproc
-- 1시간 소요
SVRMGR>@$ORACLE_HOME/rdbms/admin /catexp
-- 30 소요
만일 위의 스크립트를 돌리다 에러다 싶으면 데이터베이스 생성할 뭔가 잘못된 것입니다.
확인하고 다시 실행 해야겠죠..

 

5. 도움말과 sample table / view 생성

 
sqlplus를 실행하고 system/manager login 합니다.
 
$ cd $ORACLE_HOME/sqlplus/admin/help
$ SYSTEM_PASS=system/manager;export SYSTEM_PASS
$ helpins
이렇게 해서 sqlplus 의 도움말을 설치하고 (NT 에서는 이런 과정이 필요 없습니다.)
다음에 scott/tiger 샘플 테이블을 설치합니다.
 
$ cd $ORACLE_HOME/sqlplus/admin
$ sqlplus system/manager
SQL> create user scott identified by tiger
default tablespace users temporary tablespace temp
quota unlimited on users
quota unlimited on temp;
SQL> grant connect,resource to scott;
SQL>@ $ORACLE_HOME/sqlplus/admin/pupbld.sql
SQL> connect scott/tiger;
SQL> @$ORACLE_HOME/sqlplus/demo /demobld.sql
 
이로서 DB의 재설치 끝~

 

6. 백업 데이터 복원

exp 백업받은 데이터를 복원할 때는 imp 사용하여 선별적으로 또는 full 복원니다.
그러나 임포트 하기 전에 임포트할 userid, tablespace 존재하고 있어야 합니다.
이들은 백업받은 메타데이터 정보를 이용해서 다시 살려 놓습니다.
 
SQL> create tablespace [name] datafile ‘/data2/oracle/oradata ….’ [Size 10M] [reuse]
 Default storage( initial 1024 next 1024 …. );
 
일단 테이블 스페이스를 만들고 유저를 생성.
 
SQL> create user [id] indentified by [passwd]
 default tablespace [tablespace name] temporary tablespace [temp] …;
 grant connect, resource to [id];
 
Export받은 File에서 Index 제외한 나머지만 Import 하려면 indexes=n 옵션을 주고,
 
$ imp system/manager file=fullbackup.dmp fromuser=scott touser=scott indexes=n commit=y
 
Indexfile Option 이용하여 Index Script 만든다.
 
$ imp system/manager fromuser=scott touser=scott file=scott.dmp indexfile=index.sql
 
위와 같이 명령을 실행하면 index.sql이라는 File 만들어지고,
파일을 열어서 확인해 보면 create table 문장과 create index문장이 있고 Create Table 문장은 REM으로 막혀 있어서 결과적으로 Create Index문만 실행할 있도록 되어 있습니다.
 
파일에서 Create Index문의 Tablespace 바꾸어서 다음과 같이 SQL*Plus에서 실행.
$ sqlplus scott/tiger
SQL> @index
 
이런 식으로 차례로 복구 하면 깨끗한 마음으로 복구 ~
Posted by 행복한 프로그래머 궁금쟁이박
TAG 오라클

댓글을 달아 주세요

오라클 원격 접속하기


1. 오라클 서버에 리스너(Listener) 를 가동한다.

다른 컴퓨터에서 오라클에 접속하려면 리스너가 동작해야 한다. 오라클 9i라면, 설치시에 기본적으로 리스너를 설치하고, 서비스를 구성한다. Enterprise Edition이나 Standard Edition은 기본으로 구성하는 단계가 있는데, 혹시 구성 중에 취소했다면 안 만들어 졌을 수도 있다.


리스너는 네트워크를 이용하여 클라이언트에서 오라클 서버로 연결하기 위한 오라클 네트워크 관리자 이다. 오라클 서버에서 리스너를 시작시켜 주어야 클라이언트들이 접속 할 수 있다.


리스너 관련 명령어

c:\>lsnrctl start  리스너 시작하기

c:\>lsnrctl stop  리스너 정지하기

c:\>lsnrctl reload  리스너 재시작하기

c:\>lsnrctl status  리스너 상태보기

c:\>lsnrctl help  도움말


리스너 구성 파일은 $ORACLE_HOME/network/admin 아래에 있는 listener.ora 파일이며, 오라클 서버에서 클라이언트의 요청을 듣고 클라이언트와의 통신 환경을 설정한다.


listener.ora 파일이 없다면, cmd 창을 열어서

C:\> lsnrctl start

라고 치면 자동으로 만들어진다.

만들어진 리스너는 서비스로 등록되는데, 서비스 관리자에서 Oracle....TNSListener라는 이름이 바로 리스너 서비스다.

이 서비스의 시작 유형을 "자동" 으로 해 놓는 것이 편리하다.


서비스 관리자에서 이 서비스를 start 시키거나, cmd 창에서

C:\> lsnrctl start

하면 외부에서 접속할 수 있다.

2. 클라이언트에 오라클 클라이언트를 설치 한다.


리눅스용 오라클 클라이언트는 http://www.oracle.com/technology/global/kr/software/products/database/oracle10g/index.html 에서 다운 받을 수 있다. 되도록이면 설치되어 있는 오라클 버전하고 같은 것을 다운 받는다.
주) 리눅스용 오라클 클라이언트는 X 윈도 에서 설치가 가능하다.

3. 리눅스 시스템의 경우 .bash_profile 파일을 수정한다.

SQL PLUS 같은 터미널 접속 이라면 먼저 해당 계정의 .bash_profile 파일을 수정해 주어야 한다.
예를 들면 아래와 같다.

export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/oracle/app/oracle/product/8.1.7
export ORACLE_OWNER=oracle
export ORACLE_SID=ORCL
export TMPDIR=$ORACLE_BASE/tmp
export PATH=$PATH:$ORACLE_HOME/bin:/usr/local/java/bin
export JAVA_HOME=/usr/local/java
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=American_America.KO16KSC5601
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

이 계정으로 접속을 했을 때 아무 곳에서나 오라클의 접속이 가능 하도록 패스 지정을 한 것이다.
 

접속 방법

사용자 이름   : <사용자계정>

암호          : <계정비밀번호>

호스트 스트링 : tnsnames.ora 에 설정된 호스트의 별칭

주) 웹을 이용한 접속 이라면 PHP 의 경우 오라클 클라이언트 설치 경로를 지정해 주어 PHP 를 다시 컴파일 해주어야 하며 JSP 같은 경우 라면 오라클 경로를 다시 잡아 주어야 한다.

4. 클라이언트에서 tnsnames.ora 파일을 수정한다.


tnsnames.ora 파일은 오라클 서버로 접속할 때 필요한 프로토콜 및 포트번호, 서버주소, 인스턴스 등을 설정해 주는 파일로써, C:\oracle\ora92\network\admin 디렉토리에 위치한다.

 

MY_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 126.138.95.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oradb)
    )
  )


오라클 클라이언트는 원격 DB 접속을 위해 위의 파일 내용을 참조하게 된다.

MY_DB 는 원격 데이터베이스의 사용자 정의 명칭이다. 자신의 PC 에서만 유효한 이름이므로 아무렇게나 주어도 된다. HOST = 126.138.95.21 이 부분이 중요한데...실제 원격 DB 가 위치하고 있는 머신의 IP 주소를 입력하면 된다. 포트는 기본 값이 1521 이다.

SERVICE_NAME 이라는 것은 실제 데이터베이스 이름이라고 보면 된다. MY_DB 가 내 PC 에서만 유효한 것이라면 SERVICE_NAME 은 NETWORK 내에서 유일해야 하므로 실제 원격 머신 상에서 오라클 Database 를 Create할 때 결정되어진다. SERVICE_NAME 및 호스트 머신의 IP 주소에 대해서는 원격 Database 를 관리하는 DATABASE 관리자에게 문의하면 된다.


각 오라클의 버전별로 약간의 tnsname.ora 의 내용이 약간씩 다를 수 있으나 위에 설명한 내용은 9i 에서도 그대로 적용된다.


실제 연결을 위해서 tnsname.ora 를 직접 편집하는 것보다 Net Configuration Assistant 라는 프로그램을 이용하길 바란다. 이 프로그램을 이용하여 원격 DB 연결을 설정하면 실제 연결 테스트를 해 볼 수도 있고, 직접 작성으로 인한 tnsname.ora 파일의 버전간 차이 등의 문제를 피할 수 있다. 위의 프로그램은 오라클 클라이언트를 설치하면 프로그램 메뉴에서 찾아볼 수 있다. 해당 프로그램 사용법은 지면 관계상 생략하나 네이버에서 검색하면 쉽게 찾아볼 수가 있을 것이다.


그 후에 다시 로그아웃 하고 로그인을 한후 sqlplus 라고 입력하면 됩니다.

오라클의 설치도 쉽지 않지만 클라이언트의 설치도 간단치 않으므로 차근차근 실수 없이 진행 해야 합니다.

TNSPING으로 접속 TEST하는 방법

Windows 3.1용 SQL*Net을 설치한 경우에 Nettest.exe로써 Server에 접속 여부를 test했었으나, Windows 95용 SQL*Net을 설치하면 tnsping.exe라는 프로그램이 생긴다. 이 프로그램은 일반적인 TCP/IP 프로그램에서 제공하는 Ping Utility와 유사한 방식으로 DB Server에 Login 여부를 확인할 수 있다. SQL*Net 2.2이상에서만 제공이 되며, SQL*Net V1에서는 사용할 수 없다.


C:\oracle\ora92\bin\tnsping.exe

[ 사용 방법 ]

tnsnames.ora file에 tns services name 및 접속에 관련된 내용을 기술한다.  
예: MY_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 126.138.95.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oradb)
    )
  )


tnsping utility 사용 예
 

c:\>tnsping oradb 5

TNS Ping Utility for 32-bit WINDOWS: Version 9.2.0.1.0 - Production on 18-NOV-2006 09:52:34  

Copyright (c) Oracle 1997 Corporation. All rights reserved.

사용된 매개변수 파일

C:\oracle\ora92\network\admin\sqlnet.ora

별칭 분석을 위해 TNSNAMES 어댑터 사용
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 126.138.95.21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oradb)))
확인 (50 밀리초) 
확인 (30 밀리초) 
확인 (30 밀리초) 
확인 (50 밀리초) 
확인 (30 밀리초)


주) tnsping 은 되는데 오라클 연결이 안될 때는 방화벽을 검사한다. 방화벽은 예외설정을 해도 되지 않고 사용안함 설정을 해야 한다. 또한 tnsnames.ora 파일에 HOST = 126.138.95.21 부분을 ip 주소가 아닌 host 명으로 했을 때 안되는 경우가 있다.

Posted by 행복한 프로그래머 궁금쟁이박
TAG 오라클

댓글을 달아 주세요

1. 오라클 시작

#su – oracle /* 오라클 유저로 전환
$ lsnrctl start /* 오라클리스너 시작 */
$ svrmgrl /* 오라클관리툴인 서버 매니저 실행 */
SVRMGR> connect internal /* 오라클 조정할 수 있는 권한으로 접속 */
SVRMGR> startup /* 오라클 시작 */
SVRMGR> exit


2. 오라클 종료
#su – oracle /* 오라클 유저로 전환
$ lsnrctl stop /* 오라클리스너 종료 */
$ svrmgrl /* 오라클관리툴인 서버 매니저 실행 */
SVRMGR> connect internal /* 오라클 조정할 수 있는 권한으로 접속 */
SVRMGR> shutdown immediate /* 오라클 종료 */
SVRMGR> exit
Posted by 행복한 프로그래머 궁금쟁이박
TAG 오라클

댓글을 달아 주세요

프로그래밍 :: 오라클 강좌

★ SQL 의 종류


1.DDL (Data Definition Language) : 데이터와 그 구조를 정의 합니다.

SQL문

내 용

CREATE

데이터베이스 객체를 생성 합니다.

DROP

데이터베이스 객체를 삭제 합니다.

ALTER

기존에 존재하는 데이터베이스 객체를 다시 정의하는역할을 합니다.


2. DML (Data Manipulation Language) : 데이터의 검색과 수정등의 처리

SQL문

내 용

INSERT

데이터베이스 객체에 데이터를 입력

DELETE

데이터베이스 객체에 데이터를 삭제

UPDATE

기존에 존재하는 데이터베이스 객체안의 데이터 수정

SELECT

데이터베이스 객체로부터 데이터를 검색


3.DCL (Data Control Language) : 데이터베이스 사용자의 권한을 제어

SQL문

내 용

GRANT

데이터베이스 객체에 권한을 부여 합니다.

REVOKE

이미부여된 데이터베이스객체의 권한을 취소합니다.





★ 사용자의 생성


새로운 USER를 생성하기 위해서는 CREATE USER문을 이용하면 됩니다.
 USER를 생성하기 위해서는 USER생성 권한이 있는 사용자로 접속해야 합니다.

사용자 생성 문법(Syntax)




 - user_name : 사용자 이름

 - BY password : 사용자가 데이터베이스에 의해 인증되도록 지정하며, 데이터베이스 유저 로그온시 사용하는 비밀번호 입니다.  

 - EXTERNALLY : 사용자가 운영 체제에 의해서 인증되도록 지정합니다.

 - DEFAULT TABLESPACE사용자 스키마를 위한 기본 테이블 스페이스를 지정 합니다.

 - TEMPORARY TABLESPACE사용자의 임시 테이블 스페이스를 지정합니다.

 - QUOTA절을 사용하여 사용자가 사용할 테이블 스페이스의 영역을 할당합니다.

 - PASSWORD EXPIRE : 사용자가 SQL*PLUS를 사용하여 데이터베이스에 로그인할  때 암호를 재설정하도록 합니다.(사용자가  데이터베이스에 의해  인증될 경우에만 적합한 옵션입니다.)

 - ACCOUNT LOCK/UNLOCK :  사용자 계정을 명시적으로 잠그거나 풀 때 사용할 수  있습니다.(UNLOCK이 기본값입니다.)

 - PROFILE: 자원 사용을 제어하고 사용자에게 사용되는 암호 제어 처리 방식을 지정하는데 사용됩니다.

※ 여기선 간단한 유저생성에 대해서만 알아보고 자세한 유저관리와 PROFILE 관리는 어드민에서 설명 하겠습니다.
 

※ 참고 1

 - 임시 테이블스페이스를 지정해 주지 않으면 시스템 테이블스페이스가 기본으로 지정 되지만 시스템 테이블스페이스에 단편화가 발생할 수 있으므로 사용자를 생성할때 임시테이블스페이스를 따로 지정해 주는 것이 좋습니다.
 
- 또한 DEFAULT TABLESPACE도 사용자를 생성할때 지정해 주지 않으면 기본적으로 시스템 테이블스페이스가 지정이 됩니다. 하지만 사용자를 생성할때 DEFAULT TABLESPACE를 지정을 해서 사용자가 소유한 데이터와 객체들의 저장 공간을 별도로 관리를 해야 합니다.

  시스템 테이블스페이스는 본래의 목적(모든 데이터 사전 정보와, 저장 프로시저, 패키지, 데이터베이스 트리거등을 저장)을 위해서만 사용되어져야 하지 일반사용자의 데이터 저장용으로 사용 되어서는 안됩니다.


※ 참고 2

테이블 스페이스란 ?

 - 오라클 서버가 테이터를 저장하는 논리적인 구조입니다.
 - 테이블스페이스는 하나 또는 여러개의 데이터파일로 구성되는 논리적인 데이터 저장 구조입니다.
 
 테이블 스페이스에 대한 자세한 내용는 오라클 어드민의 테이블스페이스 강좌에서 학습하겠습니다.  



사용자 생성 예제

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를 다시 생성합니다.
 사용자가 생성되었습니다.
 

 새로 생성한 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
연결되었습니다.
 
 ※ 권한에 대한 자세한 설명은 권한 설정에서 학습 하겠습니다.




★ User의 변경 및 삭제


USER 변경하기 위해서는 ALTER USER문을 사용합니다..

● ALTER USER문으로 변경 가능한 옵션

  -  비밀번호
  -  운영체제 인증
  -  디폴트 테이블 스페이스
  -  임시 테이블 스페이스
  -  테이블 스페이스 분배 할당
  -  프로파일 및 디폴트 역할


사용자 수정 문법(Syntax)


사용자 수정 예제


SQL>CONN SYSTEM/MANAGER       -- SYSTEM USER로 접속합니다.

SQL>ALTER USER scott IDENTIFIED BY lion;    -- scott USER의 비밀번호를 수정합니다.
 사용자가 변경되었습니다.

SQL>conn scott/lion    -- scott USER의 비밀번호가 바낀걸 확인할 수 있습니다.
접속되었습니다.


SQL>conn system/manager
접속되었습니다.

SQL>ALTER USER scott IDENTIFIED BY tiger;    -- scott USER의 비밀번호를 처음처럼 수정합니다.
 사용자가 변경되었습니다.
 


사용자 삭제


문법(Syntax)



 
 ※ CASCADE를 사용하게 되면 사용자 이름과 관련된 모든 데이터베이스 스키마가 데이터 사전으로부터
     삭제되며 모든 스키마 객체들 또한 물리적으로 삭제 됩니다.


사용자 정보의 확인


데이터베이스에 등록된 사용자를 조회하기 위해서는 DBA_USERS라는 데이터사전을 조회하면 됩니다.
 
SQL*Plus를 실행시켜  system/manager로 접속을 합니다.
 
SQL>SELECT username, default_tablespace, temporary_tablespace
        FROM DBA_USERS;
   
USERNAME      DEFAULT_TABLESPACE      TEMPORARY_TABLES
---------------- -------------------        ----------------
SYS                    SYSTEM                       TEMP
SYSTEM             TOOLS                          TEMP
OUTLN                SYSTEM                        SYSTEM
DBSNMP              SYSTEM                       SYSTEM
ORDSYS              SYSTEM                       SYSTEM
ORDPLUGINS       SYSTEM                        SYSTEM
MDSYS               SYSTEM                        SYSTEM
CTXSYS              DRSYS                          DRSYS
SCOTT                SYSTEM                       SYSTEM
TEST                  TEST                            SYSTEM
STORM               STORM                         SYSTEM
KJS                    SYSTEM                        SYSTEM
OEM                   OEM_REPOSITORY         TEMP
 
 위와 같이 유저와 테이블 스페이스에 대한 정보가 화면에 나옵니다.




시스템 권한(System Privileges)

오라클에서 권한(Privilege)은 특정 타입의 SQL문을 실행하거나 데이터베이스나 데이터 베이스
 객체에 접근할 수 있는 권리입니다.


SYSTEM PRIVILEGES

 -
시스템 권한은 사용자가 데이터베이스에서 특정 작업을 수행 할 수 있도록 합니다
 
- 약 126개의 시스템 권한이 있으며 그 수는 계속 증가하고 있습니다.
 - 권한의 ANY 키워드는 사용자가 모든 스키마에서 권한을 가짐을 의미 합니다.
 - GRANT 명령은 사용자 또는 Role에 대해서 권한을 부여 합니다.
 - REVOKE 명령은 권한을 삭제 합니다.


  시스템 권한의 종류 몇가지   

  -  CREATE SESSION :
데이터 베이스를 연결할 수 있는 권한
  -  CREATE ROLE :
오라클 데이터베이스 역할을 생성할 수 있는 권한
  -  CREATE VIEW :
뷰의 생성 권한
  -  ALTER USER :
생성한 사용자의 정의를 변경할 수 있는 권한
  -  DROP USER :
생성한 사용자를 삭제시키는 권한


시스템 권한 부여 문법(Syntax)




 - 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 사용자도 다른 사용자에게 그 권한을 부여 할 수 있습니다.
 


시스템 권한의 박탈

문법(Syntax)


시스템 권한 철회 예제


SQL>REVOKE CREATE USER, ALTER USER, DROP USER
        FROM scott
       

 권한이 회수되었습니다.

*설명 : scott 사용자에게 부여한 생성, 수정, 삭제 권한을 회수합니다,
           


WITH ADMIN OPTION을 사용하여 시스템 권한 취소


 WITH ADMIN OPTION을 사용하여 시스템 권한을 부여했어도 시스템 권한을 취소 할 때는 연쇄적으로 취소 되지 않습니다.

시나리오

 1. DBA가 STORM에게 WITH ADMIN OPTION을 사용하여 CREATE TABLE 시스템 권한을 부여 합니다.

2. STORM이 테이블을 생성 합니다.

3. STORM이 CREATE TABLE 시스템 권한을 SCOTT에게 부여 합니다.

4. SCOTT가 테이블을 생성 합니다.

5. DBA가 STORM에게 부여한 CREATE TABLE 시스템 권한을 취소 합니다.

결과

 - STORM의 테이블은 여전히 존재하지만 새 테이블을 생성할 수 있는 권한은 없습니다.

 - SCOTT는 여전히 테이블과 새로운 테이블을 생성 할 수 있는 CREATE TABLE권한을 가지고 있습니다.

 



객체 권한(Object Privileges)


Object Privileges(객체 권한)


   객체 권한은 유저가 소유하고 있는 특정한 객체를 다른 사용자들이 엑세스 하거나 조작 할 수 있게 하기 위해서 생성을 합니다.


  - 테이블이나 뷰, 시퀀스, 프로시저, 함수, 또는 패키지 중 지정된 한 오브젝트에 특별한 작업을
    수행 할 수 있게 합니다.
 
  - Object 소유자는 다른 사용자에게 특정 Object Privileges를 부여 할 수 있습니다.

  - PUBLIC으로 권한을 부여하면 회수할 때도 PUBLIC으로 해야 합니다.

  - Object Privileges는 Object Type에 따라서 다릅니다.

  - 기본적으로 소유한 오브젝트에 대한 모든 권한이 자동적으로 획득됩니다

  - WITH GRANT OPTION 옵션은 롤에 권한을 부여할 때는 사용할 수 없습니다


 * Object에 따른 Privileges

객체권한

테이블

Sequence

Procedure

  ALTER



  DELETE



  EXECUTE




  INDEX




  INSERT



  REFERENCES




  SELECT


  UPDATE




  위의 표에서
맨 왼쪽에 있는 ALTER, DELETE, EXECUTE.. 등등은 object_privilege란에 오면 되고,
맨 윗줄에 있는 테이블, 뷰, 시퀀스, 프로시져 등등은 ON 다음에 있는 object에 입력하면 됩니다.


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 사용자도 다른 사용자에게 그 권한을 부여 할 수 있습니다.
 



Object 권한의 박탈



 - 객체 권한의 철회는 권한을 부여한 부여자만이 수행할수 있습니다.

 - CASCADE CONSTRAINTS : 이 명령어의 사용으로 REFERENCES객체 권한에서 사용된
               참조 무결성 제한을 같이 삭제 할 수 있습니다.

 - WITH GRANT OPTION으로 객체 권한을 부여한 사용자의 객체 권한을 철회하면, 권한을
   부여받은 사용자가 부여한 객체 권한 또한 같이 철회되는 종속철회
발생합니다.



오브젝 권한 철회 예제


SQL>REVOKE SELECT, INSERT
         ON emp
         FROM scott


*설명 : scott 사용자에게부여한 emp테이블에 대한 SELECT, INSERT권한이 회수 됩니다..
         만약 scott사용자가 다른 사용자에게 SELECT, INSERT권한을 부여했으면..
         그 권한들도 같이 철회가 됩니다.
 


WITH GRANT OPTION을 사용하여 객체 권한 취소


 WITH GRANT OPTION을 사용하여 부여한 객체 권한을 취소하면 취소 작업이 연쇄적으로 수행 됩니다.

시나리오

 1. SCOTT가 STORM에게 WITH GRANT OPTION을 사용하여 emp테이블의 SELECT 권한을 부여 합니다.

2. STORM이 emp테이블의 SELECT권한을 TEST에게 부여 합니다.

3. SCOTT가 STORM에게 부여한 emp테이블의 SELECT 권한을 취소 합니다.

결과

 - SCOTTt가 STORM에게 부여한 emp테이블에 대한 SELECT 권한을 취소하면 STORM이 부여한 TEST유저가 emp테이블을 SELECT할 수 있는 권한도 자동으로 취소가 됩니다.

 



객체 권한을 위한 일반 사용자용 데이터 사전 뷰
 

데이터 사전 뷰

설명

 USER_TAB_PRIVS

 객체 권한의 소유자, 객체 권한 부여자, 객체 권한 피부여자를 볼수있음

 USER_TAB_PRIVS_MADE

 사용자가 부여 모든 객체 권한의 뷰

 USER_TAB_PRIVS_RECD

 사용자가 부여받은 모든 객체 권한의 뷰

 USER_COL_PRIVS

 객체 권한의 소유자, 객체 권한 부여자, 객체 권한 피부여자의 컬럼의 객체
 권한 뷰

 USER_COL_PRIVS_MADE

 사용자가 부여 객체 컬럼에 대한 모든 객체 권한 뷰

 USER_COL_PRIVS_RECD

 사용자가 부여받은 객체 컬럼에 대한 모든 객체 권한 뷰


롤(Role)


 

 ROLE 이란 사용자에게 허가할 수 있는 권한들의 집합 이라고 할 수 있습니다.


  - ROLE을 이용하면 권한 부여와 회수를 쉽게 할 수 있습니다.

  - ROLE은 Create Role권한을 가진 User에 의해서 생성 됩니다. 

  - 한 사용자가 여러개의 ROLL을 ACCESS할 수 있고,  여러 사용자에게 같은 ROLE을 부여할 수
    있습니다.

  - 시스템 권한을 부여하고, 취소할 때와 동일한 명령을 사용하여 사용자에게 부여하고, 취소 합니다.

  - 사용자는 ROLE에 ROLE을 부여할 수 있습니다.

  - 오라클 데이터베이스를 설치하면 기본적으로 CONNECT, RESOURCE, DBA ROLE이 제공  
    됩니다.


   아래의 그림처럼 DBA가 유저들에게 권한을 부여할 때 일일이 권한 하나하나씩을 지정을 한다면 몹시 불편할 것 입니다. DBA가 USER의 역할에 맞도록 Role을 생성하여서 Role만 유저에게 지정을 한다면 보다 효율적으 로 유저들의 권한을 관리 할 수 있습니다.


ROLE의 생성

문법
 


ROLE의  부여 순서
 ① ROLE의 생성  :  CREATE ROLE manager
 ② ROLE에 권한 부여 : GRANT create session, create table TO manager
 ③ ROLE을 사용자 또는 ROLE에게 부여 : GRANT manager TO scott, test;
 

-- role을 생성 합니다.
SQL>
CREATE ROLE manager

-- role에 권한을 부여 합니다.
SQL>GRANT
create session, create table TO manager

-- 권한이 부여된 role을 user나 role에 부여 합니다.
SQL>GRANT
manager TO scott, test;

Dictionary 뷰

내      용

  ROLE_SYS_PRIVS

 Role에 부여된 시스템 권한

  ROLE_TAB_PRIVS

 Role에 부여된 테이블 권한

  USER_ROLE_PRIVS

 현재 사용자가 ACCESS할 수 있는 ROLE

  USER_TAB_PRIVS_MADE

 현재 사용자의 객체에 부여 객체 권한

  USER_TAB_PRIVS_RECD

 현재 사용자의 객체에 부여 객체 권한

  USER_COL_PRIVS_MADE

 현재 사용자 객체의 특정 컬럼에 부여 객체 권한

  USER_COL_PRIVS_RECD

 현재 사용자 객체의 특정 컬럼에 부여 객체 권한



★ 테이블의 생성


이번 강좌는 오라클 테이블해 대해서 설명을 하겠습니다.

 테이블은 실제로 데이터들이 저장되는 곳 이라고 생각하면 쉽게 이해 할 수 있습니다.
 CREATE TABLE명령어를 이용해서 테이블을 생성 할 수 있습니다.

 아래의 내용은 테이블에 대한 보충 설명입니다.


테이블 이란?

1. 테이블은 오라클 데이타베이스의 기본적인 데이타 저장 단위 입니다.

2. 데이타베이스 테이블은 사용자가 접근 가능한 모든 데이타를 보유하며 레코드와 컬럼으로 구성 
   
됩니다.
 
 관계형 데이타베이스가 아닌 예전의 데이타 베이스 용어에서는 파일과 테이블이, 필드와 컬럼이,
   그리고 레코드와 행이 동일시 되었습니다.

3. 테이블은 시스템내에서 독립적으로 사용되길 원하는 엔티티를 표현할수 있습니다.  
   예를 들면, 회사에서의 고용자나 제품에 대한 주문은 테이블로 표현 가능합니다.

4. 테이블은 두 엔티티간의 관계를 표현할 수 있습니다.
   즉 테이블은 고용자와 그들의 작업 숙련도 혹은 제품과 주문과의 관계를 표현하는데  사용될 수
   있습니다.

   
테이블내에 있는 외래 키 (ForeIgn Key)는  두 엔티티 사이의 관계를 표현하는데 사용됩니다.

5. 비록 "테이블" 이라는 말이 더 많이 사용되지만 테이블의 형식어는 "릴레이션" 입니다.


컬  럼
  -
테이블의 각 컬럼은 엔티티의 한 속성을 표현한다

행(ROW, 레코드)
  -
테이블의 데이타는 행에 저장됩니다



※ 테이블 생성시 제한사항과 고려할점

  - 테이블 이름과 컬럼은 항상 알파벳 문자로 시작해야 하며 A~Z까지의 문자, 0~9까지의 숫자,    
     
그리고 $,#,_(Under Bar)를 사용할 수 있습니다. (★공백 사용 불가능)

  - 테이블의 컬럼 이름은 30자를 초과할수 없고, 예약어를 사용할수 없읍니다.

  -  오라클 테이블 한 계정안에서 테이블 이름은 다른 테이블 이름과 달리 유사해야 합니다.

  - 한 테이블 안에서 컬럼이름은 같을수 없으며 다른 테이블에서의 컬럼이름과는 같을수  있습니다.


테이블의 생성 문법


[Syntax]




- 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에 대한 자세한 강좌는 오라클 어드민 강좌의 6. Storage Structure => PCTFREE와 PCTUSED를 참고해 주세요


테이블의 생성 예제

emp2와 dept2테이블을 생성하는 예제입니다.

SQL>CREATE TABLE EMP2(
        EMPNO      NUMBER    CONSTRAINT   emp_pk_empno   PRIMARY KEY,     
     -  (컬럼)         (데이터타입)                            (제약조건)
        ENAME        VARCHAR2(20),
        JOB             VARCHAR2(40),
        MGR             NUMBER,
        HIREDATE     DATE,
        SAL              NUMBER,
        COMM          NUMBER,
        DEPTNO       NUMBER)
        PCTFREE 20
        PCTUSED 50 ;

테이블이 생성되었습니다.


SQL>CREATE TABLE DEPT2(
        DEPTNO   NUMBER CONSTRAINT dept_pk_deptno PRIMARY KEY,
        DNAME      VARCHAR2(40),
        LOC          VARCHAR2(50)) ;

테이블이 생성되었습니다.



테이블 생성시 주의사항

 - 테이블 이름을 지정하고 각 컬럼들은 괄호 "()" 로 묶어 지정합니다.

 - 컬럼뒤에 데이터 타입은 꼭 지정되어야 합니다.

 - 각 컬럼들은 콤마","로 구분되고, 항상 끝은 세미콜론";" 으로 끝납니다.

 - 한 테이블 안에서 컬럼이름은 같을수 없으며 다른 테이블에서의 컬럼이름과는 같을 수 있습니다.

유저가 소유한 모든 테이블 보기


USER_TABLES 데이터사전을 조회 하면 유저가 소유한 테이블을 확인 할 수 있습니다.

-- SQL*Plus에서 실행해 보세요..
SQL>SELECT table_name FROM  USER_TABLES;

TABLE_NAME
------------
BONUS
CRETABLE
DEPT
DUMMY
EMP
EMP2
SALGRADE

위와 같이 테이블 목록이 조회 됩니다.
 



★ 테이블의 제약조건


제약조건 (Constraint)

  제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러 가지 규칙을 적용해 놓는거라 생각하면 됩니다. 간단하게 테이블안에서 테이터의 성격을 정의하는 것이 바로 제약조건 입니다.

 ★ 데이터의 무결성 유지를 위하여 사용자가 지정할 수 있는 성질 입니다.

 ★ 모든 CONSTRAINT는 데이터 사전(DICTIONARY)에 저장 됩니다.

 ★ 의미있는 이름을 부여했다면 CONSTRAINT를 쉽게 참조할 수 있습니다.

 ★ 표준 객체 명명법을 따르는 것이 좋습니다.

 ★ 제약조건은 테이블을 생성할 당시에 지정할 수도 있고, 테이블 생성 후 구조변경(ALTER)명령어를
     통해서도 추가가 가능합니다.

 ★ NOT NULL제약조건은 반드시 컬럼 레벨에서만 정의가 가능합니다.


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 조건
:
데이터의 유일성을 보장(중복되는 데이터가 존재할수 없습니다.)
자동으로 index가 생성됩니다.    


SQL> ALTER TABLE emp
        ADD CONSTRAINT emp_uk_deptno UNIQUE (deptno) ;

테이블이 변경되었습니다.

     이런식으로 하면 deptno 컬럼에 중복된 데이터가 들어갈 수 없습니다.

-- 제약 조건의 삭제

SQL>ALTER TABLE emp
       DROP CONSTRAINT emp_uk_deptno ; 

테이블이 변경되었습니다.
 



CHECK 조건 :
컬럼의 값을 어떤 특정 범위로 제한할 수 있습니다.


SQL>ALTER TABLE emp
       ADD CONSTRAINT emp_ck_comm
       CHECK (comm >= 10 AND comm <= 100000) ;

테이블이 변경되었습니다.

     comm컬럼은 체크조건에서 제한을 하고 있으므로 1에서 100까지의 값만을 가질수  있습니다.
     
체크 조건에서는 IN 연산자를 사용할수 있습니다.


-- 제약 조건의 삭제

SQL>ALTER TABLE emp
        DROP CONSTRAINT emp_ck_comm ;

테이블이 변경되었습니다.

SQL> ALTER TABLE emp
        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를 하지 않아도 오늘 날짜가 들어갑니다.



PRIMARY KEY 지정
: 기본키는 UNIQUE 와 NOT NULL의 결합과 같습니다.

  ※  기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서
       외래키들이  참조할 수 있는 키로서의 자격을 가지고 있습니다. 이를 참조 무결성이라 합니다
.  

       UNIQUE 조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성하며
       그 이름은 기본 키  제약 조건의 이름과 같습니다.


INDEX KEY
: 검색 키로서 검색 속도를 향상 시킴니다.
                   (UNIQUE,PRIMARY KEY 생성시 자동적으로 생김니다.)

SQL>CREATE TABLE emp(
        empno NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY ) ;

     이런식으로 하면 empno 컬럼에 UNIQUE 제약조건과 NOT NULL제약조건을 가지게 됩니다.



 FOREIGN KEY(외래 키)지정
: 기본키를 참조하는 컬럼 또는 컬럼들의 집합입니다.

※ 외래키를 가지는 컬럼의 데이터 형은 외뢰키가 참조하는 기본키의 컬럼과 데이터형이
    일치해야 합니다.
    이를 어기면 참조무결성 제약에의해 테이블을 생성할수 없습니다.

외래키에 의해 참조되고 있는 기본 키는 삭제할수 없다.

ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는  그 기본키가 삭제 될 때 같이
    삭제됩니다.


SQL>ALTER TABLE emp ADD CONSTRAINT emp_fk_deptno
        FOREIGN  KEY (deptno) REFERENCES dept(deptno)       

테이블이 변경되었습니다.

 이런식으로 하면 emp 테이블의 deptno 컬럼은 dept 테이블에 deptno 컬럼을 참조하는
 외래키를 가지게 됩
니다.

직접 변경해 보세요..

 


제약 조건의 확인

USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건을 볼 수 있습니다.
USER_CONSTRAINTS : 유저가 소유한 모든 제약 조건을 불 수 있습니다.  
이 두개의 데이터사전을 참조 하면 됩니다.
 
 SQL> SELECT  SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,  
               DECODE(B.CONSTRAINT_TYPE,
                                         'P','PRIMARY KEY',
                        'U','UNIQUE KEY',
                      'C','CHECK OR NOT NULL',
                                        'R','FOREIGN KEY') CONSTRAINT_TYPE,  
              A.CONSTRAINT_NAME   CONSTRAINT_NAME  
        FROM  USER_CONS_COLUMNS  A,  USER_CONSTRAINTS  B  
        WHERE  A.TABLE_NAME = UPPER('&table_name')  
            AND  A.TABLE_NAME = B.TABLE_NAME  
            AND  A.CONSTRAINT_NAME = B.CONSTRAINT_NAME  
        ORDER BY 1;  

-- 테이블 명을 입력 하면 됩니다.
table_name의 값을 입력하십시오: emp2

 
COLUMN_NAME         CONSTRAINT_TYPE   CONSTRAINT_NAME
------------------------------ ----------------- --------------
DEPTNO                   CHECK OR NOT NULL      SYS_C001362  
                               FOREIGN KEY                EMP2_FK_DEPTNO
EMPNO                     PRIMARY KEY               EMP2_PK_EMPNO
ENAME                     CHECK OR NOT NULL     EMP2_NN_ENAME
MGR                        UNIQUE KEY                  EMP2_UP_MGR




★ 테이블의 관리


테이블의 관리는 테이블의 컬럼 관리와 테이블 정보 관리로 나누어서 설명 하겠습니다.


① 테이블 컬럼의 관리

 테이블의 컬럼은 ADD, MODIFY, DROP연산자를 통해서 관리 할 수 있습니다.


ADD 연산자  : 테이블에 새로운 컬럼을 추가 할 때 사용 합니다.

SQL>ALTER TABLE emp ADD (addr VARCHAR2(50));

VARCHAR2의 데이터 형을 가지는 addr 컬럼이 emp 테이블에 추가 됩니다.



MODIFY 연산자 :
테이블의 컬럼을 수정 하거나 NOT NULL컬럼으로 변경 할 수 있습니다.  


SQL>ALTER TABLE emp MODIFY (ename VARCHAR2(50));
SQL>ALTER TABLE emp MODIFY (ename VARCHAR2(50) NOT NULL) ;

ename컬럼이 VARCHAR2 50자리로 수정 됩니다.

컬럼이 이미 테이터를 가지고 있을 경우 다른 데이터형으로 변경이 불가능합니다.



DROP
연산자  : 테이블 컬럼을 삭제 하거나, 테이블의 제약 조건을 삭제 할 때 사용 합니다.  


컬럼의 삭제 예제

 -- 컬럼의 삭제는 오라클 8i버전 부터 지원을 합니다.
  SQL>
ALTER TABLE table_name DROP COLUMN column_name


제약 조건의 삭제 예제

  SQL>ALTER TABLE emp DROP PRIMARY KEY ;


 -- CASCADE 연산자와 함께 사용하면 외래키에 의해 참조되는 기본키도 삭제될수 있습니다.
 SQL>ALTER TABLE emp DROP CONSTRAINT emp_pk_empno CASCADE;
 




② 테이블 정보의 관리

기존 테이블의 복사


 - 기존 테이블을 부분, 또는 완전히 복사할 때에  서브쿼리를 가진 CREATE TABLE 명령어
   사용해서 쉽게 테이블을 복사 할 수 있습니다.

 - 하지만 제약 조건, 트리거, 그리고 테이블 권한은  새로운 테이블로 복사되지 않습니다.  

 - 제약조건은 NOT NULL제약조건만 복사 됩니다.

[Syntax]



한번 실습해 보세요..

SQL>CREATE TABLE emp2
       AS
       SELECT * FROM emp;
 테이블이 생성되었습니다.



테이블 정보의 변경


 - 보통 테이블의 정보를 변경하는 이유는 스토리지 파라미터와 블록 활용파라미터를 변경하기 위해서
   사용 합니다.
 
 -  테이블 정보의 변경시 INITIAL의 값은 변경 할 수 없습니다.

[Syntax]



 STORAGE-CLAUSE에 들어올 수 있는 스토리지 파라미터를 정리하면 아래와 같습니다.
 
  - NEXT
    다음 번 생성될 익스텐트의 크기를 Byte단위로 지정합니다.
    이후의 익스텐트 크기는 PCTINCREASE만큼씩 증가 됩니다.

  - PCTINCREASE
    마지막 생성된 익스텐트의 바로 다음에 생성될 익스텐트의 증가율을 퍼센트지로 지정 합니다.

  - MINEXTENTS
    최초 생성되는 익스텐트의 수를 지정 합니다.

  - MAXEXTENTS
    생성될 수 있는 최대 익스텐트의 수를 지정 합니다.


기타 블록관련 파라미터는 테이블의 생성 강좌를 참고하세요..



테이블의 테이블스페이스 변경


Oracle8i이전 버전에서는 export를 해서 다시 import를 해야지만 테이블스페이스를 변경 할 수 있었지만
오라클 8i부터는 ALTER TABLE ~ MOVE TABLESPACE 명령어로 쉽게
테이블의 테이블스페이스를 변경 할 수 있습니다.

[Syntax]


-- 한번 실습해 보세요.
SQLPLUS scott/tiger
 
SQL>ALTER TABLE emp MOVE TABLESPACE test;
테이블이 변경되었습니다



테이블의 TRUNCATE


 - 테이블을 Truncate하면 테이블의 모든 행이 삭제되고 사용된 공간이 해제 됩니다.

 - TRUNCATE TABLE은 DDL명령이므로 롤백 데이터가 생성되지 않습니다.
   DELETE명령으로 데이터를 지우면 롤백명렁어로 복구 할 수 있지만
   TRUNCATE로 데이터를 삭제하면 롤백을 할 수가 없습니다.
 
 - 행당 인덱스도 같이 잘려 나갑니다.
 
 - 외래키가 참조중인 테이블은 TRUNCATE할 수 없습니다.
 
 - TRUNCATE명령을 사용하면 삭제 트리거가 실행되지 않습니다.

[Syntax]

 



DROP TABLE (테이블을 삭제할 때 사용)


[Syntax]


SQL>DROP TABLE emp ;

SQL>DROP TABLE emp CASCADE CONSTRAINT;

CASCADE CONSTRAINT : 외래키에 의해 참조되는 기본키를 포함한 테이블일 경우 기본키를 참조하던
                                   외래 키 조건도 같이 삭제 됩니다.




★ 데이터의 삽입, 수정, 삭제


▣ INSERT

INSERT명령어는 테이블 안에 데이터를 삽입하는 역할을 합니다. .





 -  
실제 데이터는 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



SQL>INSERT INTO
DEPT
       SELECT * FROM SCOTT.DEPT ;

직접 데이터를 입력해 봅시다.

오라클을 설치하면 SCOTT/TIGER USER에 기본적으로 EMP, DEPT테이블이 있습니다.
그 데이터를 TEST/TEST USER에 생성한 EMP, DEPT테이블에 INSERT하겠습니다.


SQL> INSERT INTO DEPT
        SELECT * FROM SCOTT.DEPT ;

4 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.

SQL> INSERT INTO EMP
         SELECT * FROM SCOTT.EMP;

15 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.



▣ UPDATE


테이블 안의 데이터를 수정 합니다.


 

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


사용하지 않는 데이터를 삭제 합니다.


SQL>DELETE FROM EMP
       WHERE EMPNO = 7902 ;
        사원번호가 7902번인 사람의 데이터가 삭제 되었습니다.

SQL>DELETE FROM EMP
        WHERE SAL < (SELECT AVG(SAL) FROM EMP) ;
        평균급여보다 적게 받는 사원 삭제

SQL>DELETE FROM EMP ;
        테이블의 모든 행이 삭제 됩니다.




★ Select문 및 연산자


SELECT문은 데이터베이스로부터 저장되어 있는 데이터를 검색하는데 사용합니다.

[Syntax]


 · 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

      사번      성명
---------- ---------------
      7782      CLARK
      7839      KING
      7934      MILLER



empno와 ename 은 각각 사번과 성명이라는 컬럼 별칭(alias)으로 만들어 출력했습니다.
alias를 사용할 때 as라는 키워드를 사용해도 되고, 생략할수도 있습니다.


▒ WHERE절에 사용될 수 있는 SQL 연산자

 연산자

 설      명

 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)

 list의 값과 일치하지 않는 데이터를 출력 합니다.

 NOT LIKE

 문자 형태와 일치하지 않는 데이터를 출력 합니다.

 IS NOT NULL

 NULL값을 갖지 않는 데이터를 출력 합니다.




▣ IN, NOT IN 연산자


IN 연산자
SQL> SELECT empno, ename
         FROM  emp
         WHERE  empno IN (7900, 7934) ;
--> 사번이 7900, 7934번인 사원의 사번과 성명 출력

    EMPNO    ENAME
 --------- -------------
     7934      MILLER
     7900      JAMES

2 개의 행이 선택되었습니다.

NOT IN 연산자
SQL> SELECT empno, ename
         FROM  emp
         WHERE  empno NOT IN (7900, 7934);
--> 사번이 7900, 7934번이 아닌 사원의 사번과 성명 출력

     EMPNO ENAME
-------- --------------
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
    ............................
13 개의 행이 선택되었습니다.



BETWEEN
연산자(AND를 이용해 두 조건을 결합한 검색과 같은 결과값을 보여줍니다.)

BETWEEN 연산자
SQL>  SELECT empno, ename
          FROM  emp
          WHERE  sal BETWEEN  3000 AND 5000 ;
--> 급여가 3000에서 5000사이인 사원만 보여줍니다.

     EMPNO ENAME
   ---------- ------
      7788 SCOTT
      7839 KING
      7902 FORD  
3 개의 행이 선택되었습니다.



LIKE 연산자


 - 검색 STRING 값에 대한 와일드 카드 검색을 위해서 LIKE연산자를 사용 합니다.
 - % :  여러개의 문자열을 나타내는 와일드 카드
 - _ : 단 하나의 문자를 나타내는 와일드 카드
 - ESCAPE : 와일드 카드 문자를 일반문자 처럼 사용하고 싶은 경우에 사용합니다.
   ☞ WHERE name LIKE '%a\_y%' ESCAPE '\' ;

구 분

설 명

LIKE 'A%'

컬럼이 'A'로 시작하는 데이터들만 검색됩니다.

LIKE '%A'

컬럼이 'A'로 끝나는 테이터들만 검색됩니다.

LIKE '%KIM%'

컬럼에 'KIM' 문자가 있는 데이터 들만 검색됩니다.

LIKE '%K%I%'

컬럼에 'K' 문자와 'I'문자가 있는 데이터 들만 검색됩니다.

LIKE '_A%'

컬럼에 'A'문자가 두 번째 위치한 데이터 들만 검색됩니다.


- LIKE 연산자는 대소문자를 구분합니다.
- Upper()함수를 이용해 대소문자 구분없이 출력할수 있습니다.


SQL>SELECT empno, ename
        FROM  emp
        WHERE  UPPER(ename) like '%K%';

EMPNO ENAME
------- -----------
   7698 BLAKE
   7782 CLARK
   7839 KING

'K' 문자가 들어있는 사원 정보를 보여줍니다.
upper()라는 함수는 k가 들어가 있는 것도 대문자 'K'로 인식하기 때문에 데이터들을 보여줍니다.



※ '_'를 이용한 LIKE검색

SQL>SELECT empno, ename
        FROM  emp
        WHERE  UPPER(ename) like '_I%'

  EMPNO ENAME
------- ----------
   7839 KING
   7934 MILLER

※ '_'는 한 문자를 나타냅니다.
   'I' 문자가 두 번째 문자에 위치한 사원들의 정보를 보여줍니다.

 



ORDER BY
(ASC[오름차순], DESC[내림차순])
  ORDER BY 절은 데이터의 정렬을 위해 사용합니다.  

SQL> SELECT empno, ename
         FROM  emp
         WHERE  deptno = 30
         ORDER BY ename ASC;

   EMPNO ENAME
-------- ---------
    7499 ALLEN
    7698 BLAKE
    7900 JAMES
    7654 MARTIN
    7844 TURNER
    7521 WARD

SQL> SELECT empno, ename
         FROM  emp
         WHERE  deptno = 30
         ORDER BY 2

위 두 개의 쿼리는 동일한 결과를 가져 옵니다.
 




★ 예명(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라는 예명을 주었습니다.

위 예제와 같이 예명은 컬럼에만 주는 것이 아니라. 쿼리 문 및 테이블에도 사용할 수 있습니다.
 




Equi Join, Non_Equi Join, Self 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 문안에 포함시켜서 다른 테이블 안에 있는 각 테이블의 컬럼이
   적어도 한번은 참조되도록 해야 합니다.



☞ Equi Join

 -
조건절 Equality Condition(=)에 의하여 조인이 이루 집니다.

 - Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋습니다.

SQL>SELECT  e.ename, d.dname
         FROM   emp e , dept d
          WHERE
e.deptno = d.deptno;

-
WHERE 절에 조인 조건을 작성하고 column명 앞에 테이블명을 적습니다.



☞ 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;
ENAME                DNAME
----------------- -----------
SCOTT               ACCOUNTING
FORD                 ACCOUNTING
SCOTT               RESEARCH
FORD                 RESEARCH



☞ Self Join

 - Equi Join과 같으나 하나의 테이블에서 조인이 일어나는 것이 다름니다.

 - 같은 테이블에 대해 두 개의 alias를 작성함으로 FROM절에 두 개의 테이블을 사용
    하는 것과 같이 합니다.

SQL> SELECT concat(a.ename,'  ') ||' : \'|| b.sal  급여
         FROM emp a, emp b
         WHERE a.empno = b.empno

급여
-------------------
SMITH   : \800
ALLEN   : \1600
WARD   : \1250
JONES   : \2975
MARTIN   : \1250
BLAKE   : \2850
CLARK   : \2450
SCOTT   : \3000

이름과 급여를 연결시켜서 보여줍니다.




Outer Join (LEFT, RIGHT, FULL OUTER JOIN)


Out(외부) Join

 - equijoin 문장들의 한가지 제약점은 그것들이 조인을 생성하려 하는 두 개의 테이블의 두 개
    컬럼에서 공통된 값이 없다면 테이블로부터 테이터를 Return하지 않는 다는 것입니다.

 - 정상적으로 조인 조건을 만족하지 못하는 행들을 보기위해 outer join을 사용합니다.
    Outer join 연산자 "( + )"입니다.

 - 조인시킬 값이 없는 조인측에 "( + )"를 위치 시킵니다.

 - Outer join 연산자는 표현식의 한 편에만 올 수 있습니다.


예제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

※ 다음의 쿼리를 한번 잘 보시기 바랍니다.

SQL>  SELECT DISTINCT(a.deptno), b.deptno
          FROM emp a, dept b
          WHERE  a.deptno(+) = b.deptno
               AND a.ename LIKE '%';

    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30

쿼리 결과를 잘 보면 out조인이 되지 않은 것을 알 수 있습니다.
위 쿼리를 out조인이 되기 위해서는 아래와 같이 고쳐야 합니다
.

SQL> SELECT DISTINCT(a.deptno), b.deptno
         FROM emp a, dept b
         WHERE  a.deptno(+) = b.deptno
              AND a.ename(+) LIKE '%'

    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30
                    40

OUT조인 조건이 걸려있는 테이블에는 다른 조건절이 들어와도
똑같이 OUT조인 연산자인 (+)를 해주어야 합니다.  



Oracle9i 부터는 ANSI/ISO SQL표준인 LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL OUTER JOIN를 지원 합니다.


LEFT OUTER JOIN
 왼쪽 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다.
 
SQL>SELECT DISTINCT(e.deptno), d.deptno
    FROM dept d LEFT OUTER JOIN emp e
    ON d.deptno = e.deptno;
 
 
RIGHT OUTER JOIN
 - 오른쪽에 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다.
 
SQL>SELECT DISTINCT(a.deptno), b.deptno
    FROM emp a RIGHT OUTER JOIN dept b
    ON a.deptno = b.deptno;
 
 
FULL OUTER JOIN
양쪽 테이블에 다 outer join을 거는것을 TWO-WAY OUTER JOIN 또는 FULL OUTER JOIN이라 합니다.
 
SQL>SELECT DISTINCT(a.deptno), b.deptno
    FROM emp a FULL OUTER JOIN dept b
    ON a.deptno = b.deptno;
 
-- 위 세 문장의 결과는 아래와 같습니다.
    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30
                   40
 
LEFT OUTER JOIN과 RIGHT OUTER JOIN의 테이블 순서를 바꾸어 가면서 테스트를 하시면 쉽게 이해를 하실 수 있습니다.




Commit과 Rollback 예제


Commit 과 Rollback

 
이전의 커밋(COMMIT)이 일어난 뒤부터 다음의 커밋(COMMIT) 전까지의 작업이 하나의 트랜
잭션 이며
, 커밋과 롤백(ROLLBACK)은 이러한 트랜잭션 단위로 데이터 베이스에서 발생한
작업을 저장, 삭제하는 일입니다.

 
- Automatic commit : DDL(Create, Alter, Drop), DCL(Grant, Revoke)

 
- Automatic Rollback: 비정상적인 종료, system failure  
 
커밋과 롤백 예제입니다.


SQL>DELETE FROM  emp WHERE empno = 7521 ;
       한 개의 행이 삭제 되었습니다.

SQL>COMMIT;
      커밋이 완료 되었습니다
 
  한 개의 행을 삭제하고, COMMIT 문으로 데이터를 영구히 저장했습니다. 이 것은 하나의 트랜잭션이 여기서 종료되고 새로운 트랜잭션이 발생하는 것을 말합니다.

SQL>SELECT empno FROM EMP WHERE empno = 7521;
       선택된 레코드가 없습니다.



SQL>INSERT INTO emp(empno, ename, hiredate) VALUES(9000, 'test', sysdate );
     한 개의 행이 작성되었습니다.

SQL>COMMIT;
     커밋이 완료 되었습니다.

SQL>DELETE FROM emp WHERE empno = 9000;
     한 개의 행이 삭제 되었습니다.

SQL>SELECT empno FROM emp WHERE empno = 9000 ;
       선택된 레코드가 없습니다.  


의 예제를 보면은 empno가 9000번인 데이터를 Insert한 후 commit으로 데이터를 저장한 다음에
데이터를 다시 삭제한 후 Select를 해보면 데이터가 검색되지 않는 것을 알 수 있습니다.

하지만 다른 유저에서는 커밋이나 롤백을 하기 전까지 이전에 Insert한 empno가 9000번인 데이터를
조회하면 데이터가 검색 됩니다.

데이터베이스에서의 이런 기능을 읽기 일관성이라고 합니다.


SQL>ROLLBACK ;
     롤백이 완료되었습니다.

(이전에 트랜잭션(커밋)이 발생하고나서 지금 발생한 ROLLBACK 문 전까지의 작업의 취소를 말합니다. )


검색을 해보면 커밋이 완료된 시점의 레코드 하나가 검색 됩니다.
SQL>SELECT  empno FROM  emp WHERE empno = 9000 ;

   EMPNO
----------
      9000
     한 개의 행이 선택되었습니다.



SAVEPOINT 와 ROLLBACK TO

  SAVEPOINT
는사용자가 트랜잭션의 작업을 여러개의 세그먼트로 분할할 수 있도록 하는 특별한
 작업입니다.
 SAVEPOINT는 부분적인 롤백을 가능하게 하기 위해 트랜잭션에 대한 중간점을 정의 합니다.
 

SQL>INSERT INTO emp(empno, ename, hiredate) VALUES(10000, 'test2', sysdate );
       한 개의 행이 작성되었습니다.

SQL>SAVEPOINT A;
       저장점이 생성되었습니다. (여기서 SAVEPOINT를 생성했습니다.)

SQL>INSERT INTO emp(empno, ename, hiredate) VALUES(10001, 'test3', sysdate );
     
  한 개의 행이 작성되었습니다.

SQL>INSERT INTO emp(empno, ename, hiredate) VALUES(10002, 'test4', sysdate );
   
    한 개의 행이 작성되었습니다.

SQL>DELETE FROM emp WHERE empno IN(10000, 10001, 10002);
       세 개의 행이 삭제 되었습니다.

SQL>SELECT empno, ename FROM emp WHERE empno IN(10000, 10001, 10002);
   
   선택된 행이 없습니다.

SQL>ROLLBACK TO A;
        롤백이 완료되었습니다. (SAVEPOINT까지만 롤백이 시행됩니다.)

SQL>SELECT empno , ename FROM emp WHERE empno IN(10000, 10001, 10002);
        한 개의 행이 선택되었습니다.

     EMPNO ENAME
--------- ---------------
    10000 test2

  SAVEPOINT까지만 롤백이 실행되었습니다. 그 결과 첫 번재 데이터는 그대로 남고,
 SAVEPOINT 후에 실행된 데이터 입력은 삭제되었습니다.




숫자함수(Number Functions)



숫자함수(Number Functions)



ABS(n)


ABS함수는 절대값을 계산하는 함수입니다.

SQL>SELECT ABS(-10) Absolute FROM dual ;

Absolute
--------
       10



CEIL(n)


CEIL함수는 주어진 값보다는 크지만 가장 근접하는 최소값을 구하는 함수입니다.

SQL>SELECT CEIL(10.1) TEST FROM dual ;

  TEST
-------
      11

SQL>SELECT CEIL(-10.1) TEST FROM dual ;

   TEST
-------
     -10



EXP(n)


EXP함수는 주어진 값의 e의 승수를 나타냅니다.
e는 2.171828183..입니다.



FLOOR(n)

FLOOR함수는 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수입니다.
CEIL 함수와 비교해 보세요.

SQL>SELECT FLOOR(10.1) TEST FROM dual ;

    TEST
 -------
       10

SQL>SELECT FLOOR(-10.1) TEST FROM dual ;
   
    TEST
-------
      -11



LN(n)


LN함수는 주어진 값의 자연로그 값을 반환합니다.



MOD(m, n)


MOD함수는 m을 n으로 나누어 남은 값을 반환한다. n이 0일 경우 m을 반환합니다.

SQL>SELECT MOD(9, 4) TEST FROM dual ;

    TEST
  -------
         1



POWER(m, n)


POWER함수는 m의 n승 값을 계산합니다.

SQL>SELECT POWER(4, 2) TEST FROM dual ;
       
      TEST
   -------
         16



ROUND(n, [m])


ROUND함수는 n값의 반올림을 하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.

SQL>SELECT ROUND(192.123, 1) TEST FROM dual ;

      TEST
   -------
      192.1

SQL>SELECT ROUND(192.123, -1) TEST FROM dual ;

     TEST
   -------
       190



SIGN(n)


SIGN함수는 n<0일 경우 -1DFM N=0일 경우 0을 N>0일 경우 1을 반환합니다.



SQRT(n)


SQRT함수는 n값의 루트값을 계산한다. n은 양수여야 합니다.



TRUNC(n, m)


 TRUNC함수는 n값을 m 소숫점 자리로 반내림한 값을 반환합니다.
 ROUND 함수와 비교해 보세요..

SQL>SELECT TRUNC(7.5597, 2) TEST FROM dual ;

    TEST
 -------
     7.55

SQL>SELECT TRUNC (5254.26, -2 ) TEST FROM dual ;

    TEST
 -------
     5200



Oracle9i Number Functions
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
POWER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET



문자열 처리 함수(Character Functions)


문자열 처리 함수(Character Functions)


CONCAT(char1, char2)
CONCAT 함수는 Concatenation의 약자로 두 문자를 결합하는 역할을 합니다.
"
||" 연산자와 같은 역할을 합니다.

SQL>SELECT CONCAT('Oracle', ' Korea') NAME FROM dual ;

                NAME
   -------------
   Oracle Korea



INITCAP(char)


주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 줍니다.

SQL>SELECT INITCAP('kim jung sick') NAME FROM dual ;

             NAME
 -------------
  Kim jung sick



LOWER(char)


문자열을 소문자로 변환 시켜 줍니다.


UPPER(char)

문자열을 대문자로 변환 시켜 줍니다.

SQL>SELECT LOWER('KIM JUNG SICK') NAME FROM dual ;

            NAME
-------------
  kim jung sick

SQL>SELECT UPPER('kim jung sick') NAME FROM dual ;

            NAME
--------------
KIM JUNG SICK



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*



SUBSTR(char, m ,[n])


  SUBSTR함수를 이용하여 m 번째 자리부터 길이가 n개인 문자열을 반환한 합니다. m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환합니다.

SQL>SELECT SUBSTR('JUNG-SICK', 3, 3) NAME FROM dual ;

          NAME
-----------
            NG-

-- 뒤에서부터 자를
SQL>SELECT SUBSTR('JUNG-SICK', -3, 3) NAME FROM dual ;

        NAME  
-----------
          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','JA','BL') "Changes" FROM DUAL
 
Changes
------------
BLCK and JUE
 
 
-- 대소문자를 구분한다는 것을 알수 있습니다.
SQL>SELECT REPLACE('JACK and JUE','j','BL') "Changes" FROM DUAL
 
Changes
------------
JACK and JUE



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
 

-- 왼쪽에서 3번째부터 시작을 해서 비교를 합니다. 2번째 OR의 위치가 반환 됩니다.
SQL>SELECT INSTR('CORPORATE FLOOR','OR', 3)  "Instring" FROM DUAL
 
  Instring
----------
         5


-- 왼쪽에서 3번째부터 시작을 해서 비교를 하는데  OR이 두 번째 검색되는 지점의 위치를 반환 합니다.
SQL> SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)  "Instring" FROM DUAL;
 
  Instring
----------
       14
 



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
------------
공백
 
 
SQL>SELECT NVL('  ','공백')  "TRIM Example" FROM DUAL
 
TRIM Example
------------
 





LTRIM

SQL>SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"  FROM DUAL;
 
LTRIM example
------------
XxyLAST WORD
 
 
RTRIM

SQL>SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM example"     FROM DUAL;

RTRIM examp
-----------
BROWNINGyxX

Character Functions
CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER
ASCII
INSTR
LENGTH

날짜 처리 함수(Date Functions)


LAST_DAY(d)

LAST_DAY함수는 달의 마지막 날의 날짜를 반환합니다

SQL>SELECT SYSDATE TODAY, LAST_DAY(SYSDATE) LASTDAY FROM dual ;

         TODAY             LASTDAY
-----------------------------
05-JUN-2000       30-JUN-2000        

 오늘이 6월 5일이니깐요 6월달의 마지막 날30일을 반환합니다.



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       오늘이 6월5일 이니깐요. 3개월이 더해진 9월 5일이 반환됩니다.



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
  ----------------
         -3.880635           달사이의 간격을 숫자형으로 반환합니다.



ROUND(d[,F])


ROUND 함수는 F에 지정된 단위로 반올림 합니다, F가 연도라면 연도 단위로 반올림 합니다.

SQL>SELECT ROUND(TO_DATE('1998/09/11'), 'YEAR')  FROM dual ;

    ROUND(TO_
--------------
       99-01-01


SQL>SELECT ROUND(TO_DATE('1998/04/11'), 'MONTH') FROM dual ;

    ROUND(TO_
---------------
       98-04-01

SQL>SELECT ROUND(TO_DATE('1998/04/11'), 'DAY')  FROM dual ;

    ROUND(TO_
 ---------------
       98-04-11    


 ☞ 날짜에 대한 산술 연산

연  산

결과치

사  용  목  적

날짜 + 숫자

날짜

  특정한 날로부터 며칠 후의 날짜 계산

날짜 - 숫자

날짜

  특정한 날로부터 며칠 전의 날짜 계산

날짜 - 날짜

숫자

  두 날짜 사이의 차이를 숫자로 계산

Datetime Functions

ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_DSINTERVAL
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET



 

변환 함수(Conversion Functions)


TO_CHAR

TO_CHAR함수는 DATE형, NUMBER형을 VARCHAR2형으로 바꺼 줍니다.

SQL>SELECT TO_CHAR(SYSDATE, 'MONTH') CHARTEST FROM dual ;

       CHARTEST
 --------------
             JUNE

오늘이 6월 10일 이니깐요.. 오늘의 달인 6월이 찍힘니다.

SQL>SELECT TO_CHAR(SYSDATE) CHARTEST FROM dual ;

       CHARTEST
 --------------
          00/06/10

오늘이 6월 10일 이니깐요.. 오늘의 날짜가 문자형으로 찍힙니다.



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'문자열이 숫자형으로 변합니다

Conversion Functions
ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR




General Functions


NVL

- NVL 함수는 NULL값을 다른 값으로 바꿀 때 쓰입니다.
-
모든 데이터 타입에 적용 가능합니다.
- 전환되는 값의 데이터 타입을 일치시켜야 합니다.

SQL>SELECT empno, NVL(comm, 0)  
        FROM  emp  
        WHERE deptno = 30;

         EMPNO  NVL(COMM,0)
       ----------     -----------
          7499              300
          7521              500
          7654             1400
          7698                0
          7844                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
        30        SALES
        20        RESEARCH
        30        SALES
        30        SALES
        10        ACCOUNTING
        20        RESEARCH

부서가 10번이면 'ACCOUNTING'를 20번이면 'RESEARCH'를
30번이면 'SALES'를 40번이면 'OPERATIONS'를 출력하는 예제 입니다.

기타 함수들


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수고요..  
Typ은 1만 나오던데..무엇을 의미하는지 잘 모르겠네용

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


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            BLAKE


Miscellaneous Single-Row Functions
BFILENAME
COALESCE
DECODE
DEPTH
DUMP
EMPTY_BLOB, EMPTY_CLOB
EXISTSNODE
EXTRACT (XML)
EXTRACTVALUE
GREATEST
LEAST
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
NVL2
PATH
SYS_CONNECT_BY_PATH
SYS_CONTEXT
SYS_DBURIGEN
SYS_EXTRACT_UTC
SYS_GUID
SYS_TYPEID
SYS_XMLAGG
SYS_XMLGEN
UID
UPDATEXML
USER
USERENV
VSIZE
XMLAGG
XMLCOLATTVAL
XMLCONCAT
XMLFOREST
XMLSEQUENCE
XMLTRANSFORM


Group Function의 종류


☞ 그룹함수란 ?

 - 그룹 함수란 여러 행 또는 테이블 전체의 행에 대해 함수가 적용되어 하나의 결과값을 가져오는
   함수를 말합니다..
 
 - 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컬럼중에서 제일 큰값을 반환합니다. 즉 가장 큰 급여를 반환합니다.


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
           ----------
                1566.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
          ----------
              668.331          30부서 사원의 급여 표준편차를 반환합니다.




Group By절과 Having절


GROUP BY

 -  특정한 컬럼의 테이터 들을 다른 데이터들과 비교해 유일한 값에 따라 무리를 짓습니다.

 - GROUP BY절을 사용하여 한 테이블의 행들을 원하는 그룹으로 나눕니다.

 - Column명을 GROUP함수와 SELECT절에 사용하고자 하는 경우 GROUP BY뒤에 Column명을
   추가 합니다.
 


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

부서별로 그룹을 지은 검색 결과 값이며
부서별로 사원수를 보여줍니다. .


☞ Group By 예제

scott/tiger유저로 접속해서 실행하세요..

예제1) 부서별로 그룹하여  부서번호, 인원수, 급여의 평균, 급여의 합을 구하여 출력 하여라.
 
SQL>SELECT deptno,COUNT(*),ROUND(AVG(sal)) "급여평균", ROUND(SUM(sal)) "급여합계"
        FROM emp
        GROUP BY deptno;
 
 
   DEPTNO   COUNT(*)   급여평균   급여합계
--------- ---------- ---------- ----------
       10             3             2998         8995
       20             5             2175        10875
       30             6             1567         9400



예제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      1037.5        1300          800       4150
MANAGER           3        2840        2975         2695       8520
PRESIDENT         1        5000        5000         5000       5000
SALESMAN          4        1400        1600         1250       5600

 



GROUP BY의 HAVING 절

 -  WHERE절에 GROUP Function을 사용할 수 없습니다.

 -  HAVING절은 GRUOP 함수를 가지고 조건비교를 할 때 사용 합니다.

 -  WHERE → GRUOP BY → HAVING → ORDER BY순으로  쿼리문이 와야 됩니다.
 

HAVING절 예제


예제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

 




인덱스(Index)



※ 인덱스란?


  인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의
원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조
입니다.  

자동 인덱스 : 프라이머리 키 또는 uinque 제한 규칙에 의해 자동적으로 생성되는 인덱스 입니다.

수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스들 입니다.  

※  Index를 생성하는 것이 좋은 Column

WHERE절이나 join조건 안에서 자주 사용되는 컬럼
null 값이 많이 포함되어 있는 컬럼
WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들


※  다음과 같은 경우에는 index 생성이 불필요 합니다.
table이 작을 때
테이블이 자주 갱신될 때

※  오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있습니다.

  B-tree인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 냅니다.

이 알고리즘 원리는

 ① 주어진 값을 리스트의 중간점에 있는 값과 비교합니다.    
     만약 그 값이 더 크면 리스트의 아래쪽 반을 버립니다.
     만약 그 값이 더 작다면 위쪽 반을 버립니다.

 ② 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도
     반복합니다.



 ※  인덱스는 B-tree 구조를 가지며 크게 다음 네 가지로 분류될수 있습니다.


Bitmap 인덱스

  비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 가장 잘 작동합니다.
  그러므로 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킵니다.
  테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있습니다.

SQL>CREATE BITMAP INDEX emp_deptno_indx
        ON emp(deptno);


Unique 인덱스

  Unique 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있습니다.
  프라이머리키 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스입니다.

SQL>CREATE UNIQUE INDEX emp_ename_indx
        ON  emp(ename);


Non-Unique 인덱스

   Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있습니다.

SQL>CREATE INDEX  dept_dname_indx
        ON  dept(dname);


결합 (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



★ VIEW 테이블



뷰란?

 ◈ 뷰는하나의 가상 테이블라 생각 하시면 됩니다.

 ◈ 뷰는 실제 데이터가 저장 되는 것은 아니지만 뷰를 통해 데이터를 관리 할수 있습니다.

 ◈ 뷰는 복잡한query를 통해 얻을 수 있는 결과를 간단한 query를 써서 구할 수 있게 합니다.

 ◈ 한개의 뷰로 여러 테이블에 대한 데이터를 검색할 수 있습니다.

 ◈ 특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있도록 합니다.



뷰의 제한 조건.


 ◈ 테이블에 NOT NULL로만든 컬럼들이. 뷰에 다 포함이 되 있어야 됩니다.

 ◈ 그리고 ROWID, ROWNUM, NEXTVAL, CURRVAL등과 같은 가상컬럼에 대한 참조를
     포함하고 있는 뷰에는 어떤 데이터도 Insert할 수 없습니다.

 ◈ WITH READ ONLY 옵션을 설정한 뷰도 데이터를 갱신할수 없습니다.

 ◈ WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는 데이터만 삽입, 삭제,
   
수정을 할수 있습니다.



 

 
 
  - FORCE : 기본 테이블 유무에 관계없이 VIEW를 생성

  - WITH CHECK OPTION : VIEW에 의해 엑세스될 수 있는 행만이 입력되거나 변경될 수 있음을
                                       지정 합니다.

  - WITH READ ONLY : SELECT만 가능한 VIEW의 생성

  - VIEW를 정의하는 질의어에는 ORDER BY절을 사용 할 수 없습니다.

  - 함수를 사용한 컬럼은 반드시 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


view created.

이렇게 뷰를 생성해 놓고 뷰를 통해 검색을 하면 됩니다.

SQL>SELECT * FROM Name_Query;

ENAME                DNAME
-------------------- ----------
SMITH                RESEARCH
JONES                RESEARCH
SCOTT               RESEARCH
ADAMS               RESEARCH
FORD                 RESEARCH



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

view created.

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.

단순히 읽기 만 할수 있고 데이터는 입력하지 못합니다.



VIEW에 대한 DML문 사용 규칙


  VIEW가 다음 사항을 포함하는 경우 행의 삭제 불가
   - JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령

 
 VIEW가 다음 사항을 포함하는 경우 데이터 수정 불가
   - JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령, 식으로 정의된 컬럼(EX SAL * 0.1)

 
 VIEW가 다음 사항을 포함하는 경우 데이터 추가 불가
   - JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령, 식으로 정의된 컬럼,
     VIEW에 선택되지 않는 NOT NULL컬럼
 


뷰의 정보 조회

USER_VIEWS 데이터 사전을 통해서 뷰에 대한 정보를 조회 할 수 있습니다.

SQL>SELECT view_name , text
       FROM  USER_VIEWS;


뷰의 삭제

SQL>DROP VIEW Read_Only;

      view dropped.



SEQUENCE(시퀀스)



 시퀀스란?


유일(UNIQUE)한 값을 생성해주는 오라클 객체입니다.

◈ 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할수 있습니다.

◈ 보통 primary key 값을 생성하기 위해 사용합니다.

◈ 메모리에 Cache되었을 때 Sequence 값의 액세스 효율이 증가 합니다.

◈ Sequence는 테이블과는 독립적으로 저장되고 생성됩니다. 따라서 하나의 sequence를
    여러 테이블에서 쓸 수 있습니다.
 



시퀀스 생성





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를 지정할 경우 최소값은 무한대가 됩니다

 


 SQL>CREATE SEQUENCE emp_seq
        START WITH 1
        INCREMENT BY 1
        MAXVALUE 100000 ;

      sequence created.

     시작 값이 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 ;

        CURRVAL
        ---------
                  1

 SQL>SELECT emp_seq.NEXTVAL FROM DUAL ;

       NEXTVAL
     ---------
                 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값


시퀀스의 수정 및 삭제





START WITH는 수정할수 없습니다.
START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE와 같습니다.
 


 SQL>ALTER SEQUENCE emp_seq
         INCREMENT BY 2
         CYCLE;

      sequence altered.

      2씩 증가하고, 최대값을 넘으면 다시 처음부터 순환하도록 수정하였습니다.


      DROP 문을로 필요하지 않은 시퀀스는 삭제 할수 있습니다.

 SQL>DROP SEQUENCE PRD_SEQ;
        sequence dropped.




SYNONYM(동의어)


시노님은 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(Alias)를 말합니다.
 
Synonym은 실질적으로 그 자체가 Object가 아니라 Object에 대한 직접적인 참조 입니다.
 
시노님을 사용하는 이유는..
 
데이터베이스의 투명성을 제공하기 위해서 사용 한다고 생각하면 됩니다.
    시노님은 다른 유저의 객체를 참조할 때 많이 사용을 합니다.
 
② 만약에 실무에서 다른 유저의 객체를 참조할 경우가 있을 때 시노님을 생성해서 사용을 하면은
    추후에 참조하고 있는 오프젝트가 이름을 바꾸거나 이동할 경우 객체를 사용하는 SQL문을 모두
    다시 고치는 것이 아니라 시노님만 다시 정의하면 되기 때문에 매우 편리 합니다.
 
객체의 긴 이름을 사용하기 편한 짧은 이름으로 해서  SQL코딩을 단순화 시킬 수 있습니다.
 
또한 객체를 참조하는 사용자의 오브젝트를 감추 수 있기 때문에 이에 대한 보안을 유지할 수
    있습니다
.  
   시노님을 사용하는 유저는 참조하고 있는 객체를에 대한 사용자의 object의 소유자, 이름, 서버이름을  모르고 시노님 이름만 알아도 사용 할 수 있습니다.



Synonyms을 사용하는 경우

 - 오브젝트의 실제 이름과 소유자 그리고 위치를 감춤으로써 database 보안을 개선하는데 사용 됩니다
 - Object에의 Public Access를 제공 합니다.
 - Remote Database의 Table, View, Program Unit를 위해 투명성을 제공 합니다.
 - Database 사용자를 위해 SQL 문을 단순화 할 수 있습니다.


시노님에는 두가지 종류가 있습니다.  

Private Synonym  
   - 전용 시노님은 특정 사용자만  이용할수 있습니다.
 
Public Synonym
  - 공용 시노님은 공용 사용자 그룹이 소유하며 그 Database에 있는 모든 사용자가 공유 합니다.


 시노님 생성 문법(Syntax)




 - PUBLIC : 모든 사용자가 접근 가능한 시노님을 생성 합니다.
                PUBLIC  시노님의 생성 및 삭제는 DBA만이 할 수 있습니다.

출처 블로그 > 서울전문학교
원본 http://blog.naver.com/7441472/150020439202


 ※ scott USER의 emp테이블을 test USER가 사용 하는 예제.


 1. 먼저 scott/tiger USER로 접속해서 test USER에게 emp테이블을 조작할 권한을 부여합니다.
 
 SQL>GRANT ALL ON  emp TO  test;
         권한이 부여되었습니다.

         test user에 대하여 scott의 emp테이블을 조작할 수 있는 권한을 부여합니다.
         권한이 있어야 select하거나 update, insert할수 있습니다.



 2. test USER로 접속해 동의어를 생성합니다.

 SQL> connect test/test

 SQL> CREATE SYNONYM  scott_emp FOR  scott.emp ;
         시노님이 생성되었습니다.

          scott USER가 소유하고 있는 emp 테이블에 대해 scott_emp라는 일반시노님을 생성했습니다.
          scott 사용자의 emp테이블을 test 사용자가 scott_emp라는 동의어로 사용 합니다. .

  -- 시노님을 이용한 쿼리
 SQL> SELECT empno,  ename FROM  scott_emp;

 -- 일반 테이블을 쿼리
 SQL> SELECT empno,  ename FROM  scott.emp;
          이 두 쿼리의 결과는 같습니다.

   EMPNO ENAME
-------- ---------
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
          15 개의 행이 선택되었습니다.


 동의어 삭제

 SQL> DROP SYNONYM   scott_emp;
         시노님이 삭제되었습니다.

 SQL> SELECT empno,  ename FROM  scott_emp;
         라인 1 에 오류:
         ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

Posted by 행복한 프로그래머 궁금쟁이박
TAG 오라클

댓글을 달아 주세요

alter 명령::::::::::
sqlplus internal 하시던지 svrmgrl로 들어가셔서
SVRMGR> SHUTDOWN IMMEDIATE;
Backup your database
SVRMGR> STARTUP MOUNT;
SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; (That will allow us to have at least one session and it will be resticted for users to connect)
SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; (That will serve same purpose - for connectivity)
SVRMGR> ALTER DATABASE OPEN; (that will open database but only for you)
SVRMGR> ALTER DATABASE CHARACTER SET KO16KSC5601;
SVRMGR> ALTER DATABASE NATIONAL CHARACTER SET KO16KSC5601;

sys.props$ 수정:::::::::
connect internal 한다음

select name,value$ from props$; 엔터하면 그에 따른 nls가 나타납니다.
여기에서 nls_characterset,nls_nchar_characterset의 값을
K016KSC5601로 바꾸어주면 됩니다. 주의(KO16 --> 케오일육, 5601-->오육공하나) 바꾸는 방법
update props$ set value$='KO16KSC5601' where name='NLS_CHARACTERSET';
commit;

update props$ set value$='KO16KSC5601' where name='NLS_NCHAR_CHARACTERSET';
commit;

shutdown;

다시 svrmgrl, connect internal, startup

이렇게 하면, nls가 업데이트 되었을 겁니다.
select name , value$ from props$;
확인 합니다.
<<<<<<자기 shell file과 일치 시키는 것은 기본이겠죠 >>>>>>>>

만약 두번째 방법으로 하셨는데 export할때 에러가 난다던지 아니면
proc.등이 complile이 가끔 안될수가 있습니다.

그럴때는 javavm/install/rmjvm.sql을 돌려주시면 되겠읍니다.

참고로 sql에서 character set을 단순히 바꿔 보실려면.

select convert(columnname,'KO16KSC5601') from emp;

하시면 됩니다.
Posted by 행복한 프로그래머 궁금쟁이박
TAG 오라클

댓글을 달아 주세요

  1. BlogIcon replica watches 2013.01.22 17:00 신고  댓글주소  수정/삭제  댓글쓰기

    당신이 여기에 온 것을 좋아 당신이 말을 당신이 말한과 방법 사랑 해요.

    1. 기존 데이터베이스 백업 받기

우선 export 툴인 exp 이용하여 전체 database 백업 받습니다.
$ exp system/manager file=fullbackup.dmp full=y
 
그리고 기존의 데이터중 그대로 사용할 데이터 또는 만약 재설치에 실패했을 때를 대비해서
기존의 데이터를 모두 백업을 받고, 현재의 설정도 백업받아야 겠지요.
 
SQL>spool bakdata.txt
SQL>select * from v$dba_users; - 유저들의 디폴트 테이블스페이스, temp테이블스페이스
SQL>select * from v$datafile; -- 각각의 데이터파일의 위치와 크기
SQL>select * from dba_tablespaces -- 테이블 스페이스 정보
SQL>select * from dba_data_files; -- 테이블 스페이스와 파일의 구성정보
SQL>select * from dba_rollback_segs; -- 롤백 세그먼트 정보
SQL>select * from v$log; -- redo log 정보
SQL>select * from v$logfile; -- redo log file 정보
SQL>spool off
 
그리고 파라미터 파일인 init<SID>.ora, (ifile 이라는 파라미터로 설정되어있는)config*.ora 백업.
 
한가지 있습니다.
바로 control file 입니다. 이것은 복구 중요한 파일입니다.
control 파일은 2 파일이므로, 생성해주는 sql 스크립트 파일을 가지고 있으면 여러 가지로 편리합니다.
 
SQL>alter database backup controlfile to trace ;
 
이렇게 하고 init .ora 파일에(또는 config .ora) 설정된 user_dump_dest 디렉토리로 가서
가장 최근의 *.trc 파일을 열어보면 control 파일을 만드는 스크립트가 포함되어 있습니다.
이것을 편집해 놓으면 바로 controlfile 만들수 있습니다.
 
 
 
ORACLE_SID (= DB name) 을 변경했을 경우
 
- UNIX의 경우
ORACLE_SID, DB_NAME 파라미터가 들어가는 모든 파일을 찾아서 수정해야 한다.
 
1. 홈디렉토리에서 환경변수 설정파일인 .profile(또는 .cshrc)의
ORACLE_SID=<new_SID> 부분을 편집하고
$. .profile (.cshrc 의 경우 $ source .cshrc로 실행)
로 실행시켜 준다. 아니면,
$export ORACLE_SID=ORATEST
수행시켜 준다.
2. /opt/var/oracle/oratab 파일을 열어 SID:ORACLE_HOME path:Y/N를 수정
3. init*.ora 또는 config*.ora 파일의 db_name을 수정
4. 아래에 나올 crdb*.sql 파일을 수정
 
- NT 경우
regedit 실행시켜 local_machine > software > oracle > home0 (oracle 8)
이동하여 ORACLE_SID 수정합니다. 그리고 init*.ora 또는 config*.ora 파일을 수정.
 
 

2. database 생성 스크립트 crdb*.sql , crdb2*.sql 편집

 
oracle 7.3 이상의 경우
$ORACLE_HOME=/home1/oracle/app/oracle/product/7.3.3
이라면 crdb*.sql 파일들
/home1/oracle/app/oracle/admin/ORA7/create
위치합니다.
위치로 이동하여 새로운 이름으로 copy하고서, 필요한 내용을 수정합니다.
이때 init *.ora 파라미터 파일과 연동되는 부분과 각종 데이터 파일의
위치와 크기를 수정할 때는 주의를 기울여야 합니다.
 
NT 인경우 스크립트가 없습니다. sample 스크립트 파일을 참조하세요.
 

3. Create Database

 
$svrmgr <-------------------------- 서버메니져를 실행 (NT의 경우 svrmgrl.exe)
SVRMGR>connect internal <----- 로그인
SVRMGR>@crdboracle.sql
반드시 에러를 확인하고 다음으로 넘어 가야 합니다.
SVRMGR>@crdb2oracle.sql
확인 해야죠. 시간이 대략 1시간 정도.걸립니다.
 
이 과정이 끝나면 데이터 파일들은 제대로 생성되었는지 꼼꼼히 확인하고 다음으로 넘어가야 합니다.
보통 아래과정에서 에러가 생기기 마련입니다.
 

4. system table/view 생성

 
우선 ORACLE_HOME/rdbms/admin 디렉토리로 이동하여
catalog.sql, catproc.sql, catexp.sql 3가지 스크립트 파일이 존재하는지 확인하십시오.
 
SVRMGR>connect internal
SVRMGR>@$ORACLE_HOME/rdbms/admin /catalog
-- 20 소요
SVRMGR>@$ORACLE_HOME/rdbms/admin /catproc
-- 1시간 소요
SVRMGR>@$ORACLE_HOME/rdbms/admin /catexp
-- 30 소요
만일 위의 스크립트를 돌리다 에러다 싶으면 데이터베이스 생성할 뭔가 잘못된 것입니다.
확인하고 다시 실행 해야겠죠..

 

5. 도움말과 sample table / view 생성

 
sqlplus를 실행하고 system/manager login 합니다.
 
$ cd $ORACLE_HOME/sqlplus/admin/help
$ SYSTEM_PASS=system/manager;export SYSTEM_PASS
$ helpins
이렇게 해서 sqlplus 의 도움말을 설치하고 (NT 에서는 이런 과정이 필요 없습니다.)
다음에 scott/tiger 샘플 테이블을 설치합니다.
 
$ cd $ORACLE_HOME/sqlplus/admin
$ sqlplus system/manager
SQL> create user scott identified by tiger
default tablespace users temporary tablespace temp
quota unlimited on users
quota unlimited on temp;
SQL> grant connect,resource to scott;
SQL>@ $ORACLE_HOME/sqlplus/admin/pupbld.sql
SQL> connect scott/tiger;
SQL> @$ORACLE_HOME/sqlplus/demo /demobld.sql
 
이로서 DB의 재설치 끝~

 

6. 백업 데이터 복원

exp 백업받은 데이터를 복원할 때는 imp 사용하여 선별적으로 또는 full 복원니다.
그러나 임포트 하기 전에 임포트할 userid, tablespace 존재하고 있어야 합니다.
이들은 백업받은 메타데이터 정보를 이용해서 다시 살려 놓습니다.
 
SQL> create tablespace [name] datafile ‘/data2/oracle/oradata ….’ [Size 10M] [reuse]
 Default storage( initial 1024 next 1024 …. );
 
일단 테이블 스페이스를 만들고 유저를 생성.
 
SQL> create user [id] indentified by [passwd]
 default tablespace [tablespace name] temporary tablespace [temp] …;
 grant connect, resource to [id];
 
Export받은 File에서 Index 제외한 나머지만 Import 하려면 indexes=n 옵션을 주고,
 
$ imp system/manager file=fullbackup.dmp fromuser=scott touser=scott indexes=n commit=y
 
Indexfile Option 이용하여 Index Script 만든다.
 
$ imp system/manager fromuser=scott touser=scott file=scott.dmp indexfile=index.sql
 
위와 같이 명령을 실행하면 index.sql이라는 File 만들어지고,
파일을 열어서 확인해 보면 create table 문장과 create index문장이 있고 Create Table 문장은 REM으로 막혀 있어서 결과적으로 Create Index문만 실행할 있도록 되어 있습니다.
 
파일에서 Create Index문의 Tablespace 바꾸어서 다음과 같이 SQL*Plus에서 실행.
$ sqlplus scott/tiger
SQL> @index
 
이런 식으로 차례로 복구 하면 깨끗한 마음으로 복구 ~
Posted by 행복한 프로그래머 궁금쟁이박
TAG 오라클

댓글을 달아 주세요