행복한 스마일맨 ^^*

select user_id from tbl_customer group by user_id;

오라클에서 group by 를 사용하면 select 할 수 있는 필드는 group by 에서 사용한 것 뿐이다. 




select * from tbl_customer group by user_id;

위와 같이 사용하면 에러가 난다.

select * from (select rownum as rnum, tbl.* from tbl_user_pb_05 tbl) where rnum between 1 and 2;

오라클 원격 접속하기

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 명으로 했을 때 안되는 경우가 있다.

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

NVL(표현식1,표현식2)

- 표현식 1이 NULL이면 표현식2, 아니면 표현식 1

 

NVL2(표현식1,표현식2,표현식3)

- 표현식 1이 널이 아니면 표현식 2

- 표현식 1이 널이면 표현식 3

WHERE num between '1' and '2';

UNIQUE KEY usrid(usrid)

UNIQUE KEY type(usrid, name) 도 가능

하나의 데이터베이스에 여러 테이블이 있는데 alarm table에 새로운 레코드가 insert 되면
트리거를 통해 같은 데이터베이스 taglist_t 테이블에도 insert가 되도록 하고 싶습니다.

delimiter //
CREATE TRIGGER t_alarm AFTER INSERT ON alarm FOR EACH ROW BEGIN
INSERT INTO taglist_t SET
TagNmae=new.TAG_NAME
,TagDate=new.DATE
,TagDesc=new.TAG_DESC
;
END;
//
delimiter ;

// 첫번째 Table
mysql> select * from first;
+------+------+------+
| num1 | a    | b    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    1 |    2 |
|    3 |    2 |    3 |
|    4 |    3 |    5 |
+------+------+------+


// 두번째 Table
mysql> select * from second;
+------+------+------+
| num2 | b    | c    |
+------+------+------+
|    1 |    1 | x    |
|    2 |    4 | y    |
|    3 |    3 | z    |
|    4 |    5 | q    |
|    5 |    7 | a    |
+------+------+------+



// Join

mysql> select first.*, second.* from first, second where first.b = second.b;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+



mysql> select first.*, second.* from first inner join second on first.b = second.b;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+



mysql> select first.*, second.* from first natural join second;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+


====================================================================================== 같은 결과




mysql> select first.*, second.* from first left join second on first.b = second.b;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    2 |    1 |    2 | NULL | NULL | NULL |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+


mysql> select first.*, second.* from first left outer join second using(b);
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    2 |    1 |    2 | NULL | NULL | NULL |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+


mysql> select first.*, second.* from first left join second using (b);
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
|    2 |    1 |    2 | NULL | NULL | NULL |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
+------+------+------+------+------+------+



====================================================================================== 같은 결과



mysql> select first.*, second.* from first right outer join second using(b);
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
| NULL | NULL | NULL |    2 |    4 | y    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
| NULL | NULL | NULL |    5 |    7 | a    |
+------+------+------+------+------+------+



mysql> select first.*, second.* from first natural right join second;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
| NULL | NULL | NULL |    2 |    4 | y    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
| NULL | NULL | NULL |    5 |    7 | a    |
+------+------+------+------+------+------+




mysql> select first.*, second.* from first right join second on first.b=second.b;
+------+------+------+------+------+------+
| num1 | a    | b    | num2 | b    | c    |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 | x    |
| NULL | NULL | NULL |    2 |    4 | y    |
|    3 |    2 |    3 |    3 |    3 | z    |
|    4 |    3 |    5 |    4 |    5 | q    |
| NULL | NULL | NULL |    5 |    7 | a    |
+------+------+------+------+------+------+


====================================================================================== 같은 결과

13.2.4.1. INSERT ... SELECT

 

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    SELECT ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

 

INSERT ... SELECT 문에서 하나 혹은 그 이상의 테이블로 부터 많은 레코드들을 빠르게 삽입 할 수 있다.

 

INSERT INTO tbl_temp2 (fld_id)

  SELECT tbl_temp1.fld_order_id

  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

 

다음 조건들은 INSERT ... SELECT 문들에서 속한 것들이다.

 

중복 키 위반을 일으키는 레코드들을 무시 하기 위해 IGNORE를 사용한다.

 

DELAYED는 INSERT ... SELECT 로 무시 된다..

 

INSERT 문의 목표 테이블은 질의의 SELECT 문의 FROM 절에 나타날 수 있다. (예전 MySQL에서는 발생 하지 않았다)

 

AUTO_INCREMENT컬럼들이 변함 없이 작동한다.

 

바이너리 로그가 원본 테이블의 재 생성을 위해 사용되도록하려면  MySQL은 INSERT ... SELECT 문을 위한 현재 삽입들을 가능하지 않게 한다.

 

현재는 , 하부 질의 에서 같은 테이블로 부터 선택이나 삽입을 할수 없다.

 

ON DUPLICATE KEY UPDATE의 값 부분에서 SELECT 부분에서 GROUD BY문을사용 하지 않는 이상  여러분은 다른 테이블에서 컬럼들을 참조 할수 있다. 하나의 side effect는 여러분이 값 부분에서 유일 하지 않은 컬럼 이름들을 손질 해야 한다는 것이다.