버튼 생성

매크로를 매번 실행시 Viaul Basic 에디터에서 실행하기 귀찮기 때문에 실행버튼이 필요한 경우 있다

최초 버튼 생성시

만들고 버튼에 매크로를 변경시(마우스를 이름에 한번클릭시 이름변경가능)

매크로 지정화면 : 매크로 지정후 확인하면 앞으로 해당 매크로 실행됨

ㅁ 계정관련

▶ root계정로그인
   $mysql -u root -p
   Enter password:[패스워드]
  ※ Mysql(MariaDB동일)은 command나 Sql툴로 접근 command같은 내용이 처리됨 
  
▶ 계정관련
   select host, user, password from user;		               :조회
   create user [계정아이디]@'[접속지제한]' identified by '비밀번호';  :생성
     ※ 접속지제한  % : 전체, localhost : 해당서버만, ip인경우 특정 ip 에서만 접근제어
     ※ 접속지제한에 싱글따옴표 필수, MariaDB는 패스워드 정책에 따라 10글자 필수
   다른 방법으로는

   INSERT INTO user (Host, User, Password) VALUES ('localhost', '계정아이디', password('비밀번호'));
   FLUSH privileges;
   
   delete from user where user='[계정아이디]';
   다른 방법으로는
   drop user '사용자ID'@localhost;

ㅁ 데이터베이스 관리(생성,권한, 권한반영)

▶ DataBase변경
   use mysql
   ※ command 모드에만 적용
   
▶ DataBase(스키마) 관리
   show databases;        : 조회
   create schema DB명;    : 생성
   옵션예) create database DB명 default character set utf8;
   drop database DB명;    : 삭제  
    
▶ 설정변경에 대한 반영 command
     flush privileges

ㅁ 테이블 관리

▶ 테이블 관련 
   - database 테이블조회
     show tables;           
   - 권한 추가: localhost접속시만 처리가능
     grant all privileges on '스키마명'.'테이블명' to '계정명'@'호스트' identified by '계정비밀번호' with grant option;   
       ※스키마명.*  는 해당 스키마에 전부
   - 권한조회
     show grants for '계정명'@'호스트';                          
   - 권한 삭제
     revoke all on '스키마명'.'테이블명' from '계정명'@'호스트';  
     
▶ primary key 관리
   alter table 테이블명 drop primary key;
   alter table 테이블명 add primary key(컬럼명, 컬럼명);

ㅁ 테이블 조회

SELECT  
       TABLE_SCHEMA 스키마
      ,TABLE_NAME    테이블영문명 
      ,TABLE_COMMENT 테이블한글명
      ,TABLE_TYPE      테이블타입
      ,TABLE_COLLATION 테이블구조  
      ,TABLE_ROWS      레코드건수
       FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA like '${schema}%'
  AND (TABLE_NAME LIKE '${var}' 
   OR TABLE_COMMENT LIKE '${var}')"
   
스키마명, 테이블명(한글)   

ㅁ 컬럼조회

 SELECT ORDINAL_POSITION AS NO
     , (CASE WHEN COLUMN_KEY !='' THEN 'Y' 
               ELSE '' END) PK
     , COLUMN_NAME 영문명
     , COLUMN_COMMENT 한글명 
     ,IS_NULLABLE AS NULLYN
     , DATA_TYPE 데이터타입
     , (case when DATA_TYPE='int' 
             then NUMERIC_PRECISION 
             else CHARACTER_MAXIMUM_LENGTH end) 자릿수
     , NUMERIC_SCALE 소수점
     , ' ' 데이터
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= '{$schema}'
    AND TABLE_NAME = '${var}'
 ORDER BY ORDINAL_POSITION"

ㅁ 이름정의

    영역을 선택하고 왼쪽 상단에 정의할 이름을 정의함

ㅁ 이름정의 활용

    1) 엑셀에 콤보 만들기

             왼쪽에 콤보를 만들 셀을 선택하고 데이터 유효성 검사 선택

            해당 콤보내용이 나옴

      2) VBA userForm에서 활용

          폼에 콤보 생성

       폼에 이름정의

ㅁ 테이블스페이스 생성전 점검

SQL>SELECT * FROM DBA_DATA_FILES;                                                        : 테이블스페이스 정보 조회

SQL>SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;  : 테이블스페이스 목록

SQL>SELECT FILE_NAME, BYTES, STATUS FROM DBA_DATA_FILES;                        :테이블스페이스 파일 목록

SQL>SELECT TABLESPACE_NAME, BYTES, BLOCKS FROM DBA_FREE_SPACE;            :테이블스페이스 잔여 공간

ㅁ 테이블스페이스 생성/삭제 권한설정

SQL>CREATE TABLESPACE work DATAFILE 'work.dat' SIZE 50M AUTOEXTEND ON NEXT 10M;   : tablespace 생성
SQL>ALTER USER [계정명] DEFAULT TABLESPACE work;                                   : user tablespace 할당
SQL>DROP TABLESPACE work INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;    : tablespace 삭제

※ 테이블스페이스명 work

※ 참고 사항

테이블스페이스 online/offline

ALTER TABLESPACE info_data offline;

ALTER TABLESPACE info_data online;

생성된 테이블스페이스의 추가하기 공간 늘여주기

ALTER TABLESPACE info_data

ADD DATAFILE '/DATA/oracle/oradata/ORCL/dbf'

SIZE 100m;

생성된 테이블스페이스 크기 변경하기

ALTER DATABASE DATAFILE '/DATA/oracle/oradata/ORCL/infodata.dbf'

RESIZE 200M;

기존 테이블스페이스에 자동확장 변경하기

ALTER DATABASE DATAFILE '/DATA/oracle/oradata/ORCL/infodata.dbf'

AUTOEXTEND ON NEXT 10m

MAXSIZE 100m;

테이블스페이스 삭제

DROP TABLESPACE info_data

INCLUDING CONTENTS

CASCADE CONSTRAINTS;

테이블스페이스의 물리적 파일까지 삭제하기

DROP TABLESPACE info_data INCLUDING CONTENTS AND DATAFILES;

데이터베이스 사용자 아이디 생성 및 수정

CREATE USER scbyun IDENTIFIED BY scbyun;

생성한 유저에 권한주고 연결하기

GRANT RESOURCE, CONNECT TO scbyun;

GRANT DBA TO scbyun;

출처: https://sangchul.kr/468 [변군이글루]

ㅁ 테이블 생성 예제

-- 테이블 생성
CREATE TABLE BOARD (
      IDX        NUMBER          NOT NULL PRIMARY KEY
    , NAME       VARCHAR2(50)    NOT NULL
    , EMAIL      VARCHAR2(50)    NOT NULL
    , PASSWD     VARCHAR2(50)    NOT NULL
    , CONTENT    VARCHAR2(1000)  NOT NULL
    , POST_DATE  DATE DEFAULT SYSDATE
);

-- 시퀀스
CREATE SEQUENCE BOARD_SEQ
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE 
    NOCACHE; 
 
 -- 데이터 입력
INSERT INTO BOARD (
      IDX
    , NAME
    , EMAIL
    , PASSWD
    , CONTENT
) VALUES ( 
      BOARD_SEQ.NEXTVAL
    , 'james'
    , 'james@gmail.com'
    , '1111'
    , '안녕하세요. 제임스입니다.'
);

--테이블 COMMENT설정
COMMENT ON TABLE [태이블명]  IS 'COMMENT내용';
EX) COMMENT ON TABLE EX_TABLE IS '예제테이블';


--컬럼 COMMENT설정
COMMENT ON COLUMN [테이블명].[컬럼명] IS 'COMMENT내용';
EX) COMMENT ON COLUMN EX_TABLE.DEPT IS '부서';

--컬럼 추가 1
ALTER TABLE emp ADD email VARCHAR(25);

--컬럼 추가 2 (기본 값, NOT NULL 지정)
ALTER TABLE emp ADD email VARCHAR(25) DEFAULT 'test@test.com' NOT NULL;


--컬럼 숨김
ALTER TABLE emp MODIFY job INVISIBLE;
--컬럼 보이기
ALTER TABLE emp MODIFY job VISIBLE;

▷ PRIMARY KEY DROP
ALTER TABLE [TABLE 명] DROP PRIMARY KEY
▷ PRIMARY KEY CREATE
ALTER TABLE [TABLE 명] ADD PRIMARY KEY (COL1, COL2, COL3 ...)

select * from v$version;             : 버전정보조회

select status from v$instance       : 인스턴스 상태

select * from v$database;            : 데이터베이스 조히

SELECT * FROM DBA_DATA_FILES : 테이블 스페이스 조회

시스템 정보 조회 테이블

SQL>select * from all_objects where object_name like '오브젝트명';    :오브젝트 조회

SQL>select * from all_synonyms where synonym_name='시노님명';    :시노팀 조회

SQL>select * from all_ind_columns where table_name='테이블명';      :테이블 인덱스 정보 조회  

SQL>select * from all_tab_columns where table_name='테이블명';      :테이블별 컬럼정보 조회  

SQL>select * from all_col_comments where table_name='테이블명';   :테이블 컬럼 comment 조회  

모든 정보 조회 테이블

1. ALL_CATALOG : 모든 테이블, 뷰정보 조회

2. ALL_OBJECT_TABLES : 모든 오브젝트 테이블 정보

3. ALL_TAB_COMMENTS : 모든 테이블 주석 정보

4. ALL_TYPES : 모든 오브젝트 타입에 대한 정보

5. ALL_USERS : 모든 사용자 정보

사용자 정보 조회 테이블

1. USER_TABLES : 테이블정보

2. USER_TAB_COLUMNS : 컬럼정보

3. USER_OBJECTS : 모든 오브젝트의 정보를 알려줌

4. USER_VIEWS : 뷰에 대한 정보

5. USER_SYNONYMS : 동의의 정보

6. USER_SEQUENCES : 시퀀스 정보

7. USER_CONSTRAINTS : 제약조건에 대한 정보

8. USER_CONS_COLUMNS : 제약조건에 대한 컬럼정보

9. USER_TAB_COMMENTS : 테이블/뷰에 대한 주석

10. USER_COL_COMMENTS : 컬럼에 대한 주석

11. USER_INDEXES : 인덱스에 대한 정보

12. USER_IND_COLUMNS : 인덱스 컬럼에 대한 정보

13. USER_CLUSTERS : 클러스터에 대한 정보

14. USER_DB_LINKS : 데이터베이스 링크 정보

15. USER_TRIGGERS : 트리거 정보

16. USER_SOURCE : 프로시저, 함수, 패키지 정보

17. USER_ERRORS : 코드 에러에 대한 정보

18. USER_TABLESPACES : 테이블 스페이스 정보

19. USER_USERS : 사용자에 대한 정보

20. USER_TAB_PRIVS : 테이블 권한에 대한 정보

21. USER_COL_PRIVS : 테이블열 권한에 대한 정보

22. USER_SYS_PRIVS : 시스템 권한에 대한 정보

출처: https://androphil.tistory.com/457?category=458502 [소림사의 홍반장!]

ㅁ 테이블 정보조회 SQL

▶ 테이블정보조회

SELECT '' as NO
  ,aa.TABLESPACE_NAME 스키마
  ,aa.TABLE_NAME    테이블영문명 
  ,bb.comments  테이블한글명
  ,TABLE_TYPE  테이블타입
  ,''  테이블구조  
  ,aa.NUM_ROWS        레코드건수
FROM all_tables aa
    ,ALL_TAB_COMMENTS bb
WHERE aa.TABLESPACE_NAME like UPPER('${schema}%')
  and aa.TABLE_NAME like upper('${var}')
  and bb.OWNER = AA.OWNER
  and bb.TABLE_NAME = AA.TABLE_NAME

▶ 컬럼정보조회 : 테이블 매핑관련 스키마 정보 추가
SELECT 
      C.TABLESPACE_NAME 스키마명
     ,A.TABLE_NAME       테이블명
      ,nvl((select 'Y'
    FROM ALL_CONSTRAINTS  c1
       , ALL_CONS_COLUMNS c2
   WHERE c1.TABLE_NAME      = A.TABLE_NAME
     AND c1.CONSTRAINT_TYPE = 'P' 
     AND c1.OWNER           = c2.OWNER
     AND c1.CONSTRAINT_NAME = c2.CONSTRAINT_NAME
     and c2.COLUMN_NAME      = a.COLUMN_NAME
       ),' ')  PK
      ,A.COLUMN_NAME 영문명
      , B.COMMENTS   한글명
      , A.NULLABLE AS NULLYN
      , A.DATA_TYPE 데이터타입
  FROM ALL_TAB_COLUMNS  A
      ,all_all_tables  C
      ,ALL_COL_COMMENTS B
WHERE A.TABLE_NAME      = UPPER('${var}') 
  AND A.OWNER      = B.OWNER
  AND A.TABLE_NAME = B.TABLE_NAME
  AND A.TABLE_NAME = C.TABLE_NAME
  AND A.TABLE_NAME = B.TABLE_NAME(+)
  AND A.COLUMN_NAME = B.COLUMN_NAME(+)
  ORDER BY A.COLUMN_ID

▶ 테이블 컬럼정보조회 : 컬럼상세까지 조회
SELECT A.COLUMN_ID AS NO
      ,nvl((select 'Y'
    FROM ALL_CONSTRAINTS  c1
       , ALL_CONS_COLUMNS c2
   WHERE c1.TABLE_NAME      = A.TABLE_NAME
     AND c1.CONSTRAINT_TYPE = 'P' 
     AND c1.OWNER           = c2.OWNER
     AND c1.CONSTRAINT_NAME = c2.CONSTRAINT_NAME
     and c2.COLUMN_NAME      = a.COLUMN_NAME
       ),' ')  PK
      ,A.COLUMN_NAME 영문명
      , B.COMMENTS   한글명
      , A.NULLABLE AS NULLYN
      , A.DATA_TYPE 데이터타입
      , A.DATA_LENGTH 자릿수
      , A.DATA_SCALE 소수점
      , ' ' 데이터

  FROM ALL_TAB_COLUMNS  A
      ,ALL_COL_COMMENTS B
      
WHERE A.TABLE_NAME      = UPPER('${var}') 
  AND A.OWNER = B.OWNER
  AND A.TABLE_NAME = B.TABLE_NAME(+)
  AND A.COLUMN_NAME = B.COLUMN_NAME(+)
  ORDER BY A.COLUMN_ID

ㅁ 오라클 계정설정

SQL> CREATE USER [계정명] identified by [패스워드];        : 사용자 등록 
SQL> alter user [계정명] identified by [패스워드];         : 사용자 패스워드변경
SQL> DROP USER [계정명] CASCADE                           : 사용자 삭제 
SQL> GRANT [권한 종류] to [계정명];            : 사용자 권한등록
SQL> REVOKE [권한 종료] FROM [계정명];  : 권한삭제
※ 권한 종류 : CONNECT, RESOURCE, CREATE JOB, CREATE VIEW, CREATE ANY CONTEXT, DBA
※ sysdba계정으로 sqlplus 접속, 패스워드 특수문자는 쌍따옴표로 처리


SQL> show user                                            :현재접속사용자
SQL>SELECT * FROM ALL_USERS;                             : 사용자 조회

ㅁ 오라클 접속 정보 설정(listener.ora, sqlnet.ora, tnsnames.ora) 

ㅁ 오라클 환경구성

ㅁ 시스템 환경정보

grep . /etc/*-release
Ubuntu 14.04

서버그룹 dba
user는 oracle 

docker를 이용하여 설치함 sqlplus 위치
/u01/app/oracle-product/11.2.0/xe/bin/sqlplus
/u01/app/oracle-product/11.2.0/xe/sqlplus

ㅁ 프로파일 설정

▶ 내 profile 위치 및 설정정보
   /etc/bash.bashrc
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=XE

alias ss='sqlplus / as sysdba'

   
<다른데 참고용>
export ORACLE_BASE=/u01/app
export ORACLE_HOME=$ORACLE_BASE/oracle-product/11.2.0/xe/
export ORACLE_SID=orcl
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export PATH=$PATH:$ORACLE_HOME/bin

# alias : sysdba계정으로 로그인 방법
alias ss='sqlplus / as sysdba'

ㅁ Oracle 환경설정

▶ 패스워드 대소문자 구분
   SQL>show parameter sec_case;                         : 대소문자 구분 확인 
   SQL>alter system set sec_case_sensitive_logon=false; : 대소문자 구분하지 않기
   SQL>alter system set sec_case_sensitive_logon=true;  : 대소문자 구분하지 하기
 

※ View 테이블은 command로 조회하는 내용을 SQL로 조회 가능토록 함

ㅁ 권한/특권 관련 뷰

sysibmadm.privileges
sysibmadm.authorizationids
sysibmadm.objecttowners
syscat.dbauth
syscat.tabauth
syscat.colauth
syscat.indexauth
syscat.schemaauth
syscat.sequenceauth 

ㅁ 일반 정보

SYSCAT.CHECKS 점검제한조건
SYSCAT.COLUMNS 컬럼
SYSCAT.COLCHECKS 점검 제한 조건이 참조하는 컬럼
SYSCAT.KEYCOLUSE 키에서 사용되는 컬럼
SYSCAT.DATATYPES 데이터 유형
SYSCAT.FUNCPARMS 함수 매개변수 또는 함수의 결과
SYSCAT.REFERENCES 참조 제한 조건
SYSCAT.SCHEMATA 스키마
SYSCAT.TABCONST 테이블 제한 조건
SYSCAT.TABLES 테이블
SYSCAT.TRIGGERS 트리거
SYSCAT.FUNCTIONS 사용자 정의 함수
SYSCAT.VIEWS 뷰

▶ 테이블 인덱스 조회 예
SELECT TABNAME,INDNAME,COLNAMES,UNIQUERULE,INDEXTYPE
  FROM SYSCAT.INDEXES
  WHERE TABSCHEMA='CCS'   
    인덱스 조회 DESCRIBE INDEXES FOR TABLE table-name [show detail]
 
 
▶ 테이블 컬럼조회
  select TABNAME,COLNAME,TYPESCHEMA,LENGTH,NULLS,DEFAULT,REMARKS
  from SYSCAT.COLUMNS
  where TABSCHEMA ='CCS' --AND TABNAME = 'TBH001'
  ORDER BY TABNAME,COLNO
  
▶ 기타  예제
   SELECT *  FROM SYSIBM.SYSVERSIONS  : 버전확인
   SELECT *  FROMSYSIBM.SYSINDEXES    : INDEX 테이블 

※ COMMAND로 조회시 주의

※ ssh접속에서는 따옴표로 sql구분
   ex) $db2 "select * from SYSCAT.VIEWS" 

ㅁ 나만에 테이블 및 컬럼조회

▶ 테이블 목록조회
SELECT 
  BB.TABSCHEMA     AS 스키마
  ,aa.NAME         AS 테이블영문명 
  ,AA.REMARKS      AS 테이블한글명
  ,TYPE            AS 테이블타입
  ,''              AS 테이블구조  
  ,0               AS 레코드건수
FROM SYSIBM.SYSTABLES AA
 ,(  
   SELECT TYPESCHEMA
         ,TABSCHEMA
         ,TABNAME
     from SYSCAT.COLUMNS
     GROUP BY TYPESCHEMA , TABSCHEMA, TABNAME
   )   BB
WHERE (NAME    LIKE UPPER('${var}')
    OR REMARKS LIKE UPPER('${var}'))
 AND AA.TYPE = 'T'   
 AND AA.NAME = BB.TABNAME
 AND BB.TABSCHEMA LIKE  '${schema}%'
WITH UR; 

▶ 테이블 컬럼조회
SELECT 
      B.COLNO  AS  NO
     ,(CASE WHEN B.KEYSEQ='1' THEN 'Y' 
           ELSE ' ' END) AS PK
     ,B.NAME             AS 영문명
     ,B.REMARKS          AS 한글명
     ,NULLS              AS NULLYN
     ,B.COLTYPE          AS 데이터타입
     ,B.LENGTH           AS 자릿수
     ,B.SCALE            AS 소수점
    ,''                  AS 데이터
  FROM SYSIBM.SYSTABLES A
      ,SYSIBM.SYSCOLUMNS B
WHERE  (A.NAME    = UPPER('${var}')
    OR A.REMARKS =UPPER('${var}'))
 AND A.CREATOR = B.TBCREATOR
 AND A.NAME = B.TBNAME
 ORDER BY B.COLNO
 WITH UR

ㅁ 시스템 명령어

▶ DB 기동 및 종료
   db2start/db2stop
   
▶ 데이터베이스 명령어(work: 데이터베이스)
   $db2 connect to work  : 접속
       ==>    $db2 connect to  database명  user userid using password  :특정사용자접속  
   $db2 terminate                 : 종료
   $db2 get connection state      : 현재 접속되어 있는 DB 확인
   
   db2  force  application  all
   
▶ db 서버 설정 정보 확인
  $db2 get dbm cfg
  $db2 get db cfg for dbname(db connection 후 사용)
  $db2 get db cfg for work | grep code : 데이터베이스의 코드 페이지 확인
  $db2 get db cfg for work | grep LOG : 데이터베이스의 로그 파일
     ==> $db2 get db cfg for work | grep -i log
   $db2 list history backup all for db work : 현재 로그파일 넘버
     ※ 해당 로그파일 경로에 Number of primary log files과 Number of secondary log files 외 번호는 이전 파일임
     


▶ db2 tcp/ip설정
- db2 get dbm cfg | grep SVCENAME (port가 있어야 함)
- db2set -all (환경변수 보기)
   [i] DB2ENVLIST=EXTSHM
   [i] DB2COMM=TCPIP
   [i] DB2ENVLIST=EXTSHM

▶ db application   : 프로세스(=session) 조회 및  종료
   $db2 list applications               : 프로세스(=session) 조회 
   $db2 force application(handle_id)    : 프로세스(=session) 종료
   $db2 "force applications all"        : session all kill

▶ db2 lock확인
- db2 list applications for database dbname show detail
   대부분 UOW-wait상태 lock인경우 LOCK-wait상태

▶ bufferpool만들기
- db2 "create bufferpool bp8k size 1000 pagesize 8k"   

[미확인 Command]
▶ db2 버전 확인 ?? 
   $db2level   

▶ 시스템의 로그를 보고자 할 경우 ??
   $tail -f SystemOut.log

ㅁ Instance > TableSpace > DataBase > INDEX  생성(크기순)

▶ instance 생성
   $db2icrt -u FenceID userID (FenceID와 userID는 대부분 동일)
 
▶ tablespace 생성
   $db2 "create tablespace ingrian pagesize 8k managed by database using (file '/home/ingrian/DMS/ingrian.dat' 2G) bufferpool bp8k"

▶ database 생성/삭제
   $db2 create database [데이터베이스]
   $db2 drop db [데이터베이스]
   
▶ database 권한 추가   
   $db2 grant select on test_db.test_table to user testuser
   select/update/delete/all 등
※ Instance별로 실행해야 함

▶ INDEX추가
   CREATE INDEX INDEX명 ON 테이블명(COLUMN1명,COLUMN1명..)
   DROP INDEX INDEX명

ㅁ Instance, TableSpace, DataBase  명령어 및 주요테이블

▶ instance list조회 
   $db2ilist                            :인스턴스 전체 목록
   $db2 get instance                    :현재 사용중인 Instance 확인
   
▶ instance 단위로 테이블스페이스, 테이블 확인
   $db2 list db directory              :인스턴스 db2inst1에 있는 시스템 데이터베이스의 목록
   $db2 list tablespaces               :테이블 스페이스 확인 과 테이블 확인
   $db2 list active databases          : 현재 활성화되어 있는 데이터베이스의 목록   
   
▶ table space 조회(db connection후)
   $db2 list tablespaces : 
   $db2  list  tablespaces |  more
    or 
   $db2 list tablespace show detail | more

▶ table 조회 : 별도 sql로 조회가능(db connection후)
   $db2 list tables
   $db2 list tables for all
       ==> $db2  list  tables  for  all  |  more 여기서 more는 space눌러 page 표시
   $db2 list tables for system
   $db2 list tables for user
   $db2 list tables for schema kkk

   $db2 drop table "tablename" : 테이블 삭제   

▶ 컬럼조회(db connection후)
   $db2 “select  tabschema,  tabname,  colname,  typename,length, scale,  default,  nulls from  syscat.columns “   

미정리

1. dascrt 설정  : 현재는 안함
   cd /db2/ibm/db2/V10.5/instance
   ./dascrt -u dasusr1
2. 인스턴스 생성
   ./db2icrt -u db2fenc1 db2inst1

ㅁ 테이블 관련

▶ 테이블 생성
   create table bbsdoc(
       IDX bigint not null primary key generated by default as identity(start with 1, increment by 1)
       
      ,TITLE varchar(30) not null
      ,WRITER varchar(20) not null
      ,WRITEDATE varchar(20) not null
      ,CONTENTS varchar(50) not null
      , ATT varchar(20) not null
   )
▶ 테이블 주석    
   COMMENT ON TABLE 테이블명 IS '주석'
   
▶ 컬럼 주석       
   COMMENT ON 테이블명(
    컬럼명 IS '주석'
    , 컬럼명 IS '주석'
    , 컬럼명 IS '주석'
    )

▶ 테이블 삭제
   db2 drop table "tablename"

▶ alter : 테이블 컬럼 컬럼 사이 추가/수정/삭제
 - 컬럼추가시
   ex) ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 char(1) default '0'
 - 컬럼수정시
   ex) ALTER TABLE 테이블명 ALTER COULMN SET DATA TYPE (varchar/number...etc)(length);
 - 컬럼삭제
   DB2에서는 지원이 안됨
   
▶ PK 삭제하기 
/* CONSTRAINT_NAME을 테이블이름_PK라고 정했습니다. */
ALTER TABLE 테이블이름 DROP CONSTRAINT 테이블이름_PK ;

/* PK 추가하기 */
ALTER TABLE 테이블이름
ADD CONSTRAINT 테이블이름_PK
PRIMARY KEY(컬럼이름1, 컬럼이름2, 컬럼이름3);

REORG TABLE 테이블이름 ;
※ REORG TABLE를 하는 이유는 테이블 컬럼 속성이 변하였을 때, 재구성을 하기 위해서 입니다.
필수입니다.CLEAN UP과 비슷한거죠.

ㅁ 템프테이블의 활용

    추가중

ㅁ 기타 정리사항

    추가중 예를 들어 UNION ALL과 UNION 의 차이 등 정리

▶ DB2계정 패스워드 변경(OS계정 변경이 해당계정 패스워드 변경임)

▶ DB2 사용자계정관리

▶ 사용자 계정 추가 : db2iadm1 그룹으로 설정
   useradd 사용자명
   $useradd -u [UID] -g db2iadm1 -m -d /home/[USERNAME] [USERNAME]
   
▶ 사용자 계정 패스워드 변경
   $passwd [USERNAME]
   
※ 사용자 계정추가후 DB2 를 사용할수 있게 profile 수정이 필요
   사용자계정접속 : vi .bash_profile
   if [ -f /database/config/db2inst1/sqllib/db2profile ]; then
     . /database/config/db2inst1/sqllib/db2profile
   fi
   
   /database/config/db2inst1/sqllib/db2profile 는 환경 맞게 설정
   
▶ 사용자 계정 데이터베이스 관련 추가 권한 설정
    $db2 grant [권한] on database to user [USERNAME]
    $db2 grant connect on database to user [USERNAME] : 데이터베이스 접속권한
    $db2 grant dbadm on database to user [USERNAME]   : dbadm 권한 
    $db2 grant ACCESSCTRL, BINDADD, CONNECT, CREATETAB, CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, CREATE_SECURE_OBJECT, DATAACCESS, DBADM, EXPLAIN, IMPLICIT_SCHEMA, LOAD, QUIESCE_CONNECT, SECADM, SQLADM, WLMADM on database to user [USERNAME] 
    ==>Full 권한설정

※ 권한종류

▶ 디렉토리 정리

/database/config/db2inst1/sqllib : alias 설정 sqllib
/opt/ibm/db2/V11.5/instance      : alias 설정 insta    
/database/config/db2inst1/sqllib/db2dump/DIAG0000/db2diag.log : 로그파일 alias sqllog

▶ 환경설정

db2set DB2COMM=TCPIP       : db2 통신방법
db2set DB2CODEPAGE=1208    : 기본이 970 -> 1208 변경

[서버 문자셋 UTF-8로 변경]
1) localedef -c -i ko_KR -f UTF-8 ko_KR.UTF-8
2) localectl set-locale LANG=ko_KR.UTF-8   

※  DB2 CODEPAGE

▶ 언어조회 방법

    db2 get database configuration for <databaseName> : database codepage

ㅁ DB2 사용자 계정 작성 및 그룹에 사용자 추가
1. (IBM® AIX® 또는 Linux® 관리자) IBM® AIX®나 Linux OS에서 또는 AIX나 Linux 관리자 도구에서 이러한 세 개의 사용자 계정을 만든 후 6단계에서 만든 해당 그룹에 사용자를 추가합니다.

• DB2 인스턴스 소유자 - 이 계정을 작성하면 홈 디렉토리에 자동으로 작성됩니다.
• Fenced - DB2 Fenced 사용자는 사용자 정의 함수(DB2 Access)를 실행하고 DB2 데이터베이스에서 사용되는 주소 공간 외부에 프로시저를 저장합니다.
• DAS 계정 - DAS 계정은 시스템에서 DB2 서버를 실행합니다.

2. 다음과 같은 사용자 및 그룹이 설정되었습니다.

3. (AIX 관리자) AIX OS 또는 AIX 관리자 도구에서 다음과 같이 만든 사용자에게 2차 그룹을 할당합니다.

▶ DB2 계정특성

1. DB2에서는 데이터베이스 외부에 있는 사용자 정보를 이용합니다.
   오라클과는 달리 데이타베이스내에 별도의 사용자를 생성할 필요가 없습니다.
   시스템 계정임, password도 시스템 패스워드를 사용함


2. 인스턴스 및 인스턴스 내에 존재하는 데이터베이스를 제어하는 sysadm, sysctrl, sysmaint, sysmon권한이 존재합니다.


3. 인스턴스 생성시 인스턴스 소유자인 사용자 및 동일 그룹에 속하는 사용자에게 sysadm이 부여되며
   database manager configuration 파일을 수정하여 sysadm,sysctrl,sysmaint 권한을 부여할 수 있습니다.


4. sysadm, sysctrl,sysmaint는 모두 인스턴스 시작과 중지를 수행할 수 있습니다.


5. Sysadm > sysctrk > sysmain 순으로 권한이 높습니다.


6. dbadm이라는 특권을 통해 데이터베이스에 속하는 여러가지 특권을 부여받을 수 있습니다.

▶ Instance, Database, TableSpace 구성

    db2에서의 instance :  DB2 엔진을 사용할수 있는 논리적 공간

    ※ Database종류마다 다름

※ 사용자별 테이블 생성시

사용자 A가 DB_A 데이터베이스를 생성하고, DB_A에 연결하여 테이블 TB_A를 생성하고, 사용자 B가 DB_B 데이터베이스를 생성하고 DB_B에 연결하여 테이블 TB_B를 생성했다고 가정하자.

• 사용자 A : DB_A 생성 -> TB_A 생성
• 사용자 B : DB_B 생성 -> TB_B 생성

이 때, 사용자 A가 DB_A에 연결하여 조회한다면, DB_B에 생성되어 있는 TB_B는 조회를 할 수 없다.

같은 맥락으로 사용자 B가 DB_B에 연결했다면 DB_A에 있는 테이블은 조회할 수 없다.

또한 사용자 A가 DB_B에 연결하여 TB_C를 생성했다 하더라도, 사용자 A가 DB_A에 연결되어 있다면 본인이 만든 테이블임에도 DB_B에 있는 TB_C는 조회할 수 없다.

 스키마란? 

1. 스키마는 데이터베이스의 구조와 제약 조건에 관한 전반적인 명세를 기술한 메타데이터의 집합이다.

2. 스키마는 데이터베이스를 구성하는 데이터 개체(Entity), 속성(Attribute), 관계(Relationship) 및 데이터 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의한다.

3. 스키마는 사용자의 관점에 따라 외부 스키마, 개념 스키마, 내부 스키마로 나눠진다.

스키마의 특징

1. 스키마는 데이터 사전(Data Dictionary)에 저장되며, 다른 이름으로 메타데이터라고도 한다.

2. 스키마는 현실 세계의 특정한 한 부분의 표현으로서 특정 데이터 모델을 이용해서 만들어진다.

3. 스키마는 시간에 따라 불변인 특성을 갖는다.

4. 스키마는 데이터의 구조적 특성을 의미하며, 인스턴스에 의해 규정된다.

 스키마의 3계층 

데이터베이스 관리 시스템은 외부적 스키마에 따라 명시된 사용자의 요구를 개념적 스키마에 적합한 형태로 변경하고 이를 다시 내부적 스키마에 적합한 형태로 변환한다.

외부 스키마(External Schema) = 사용자 뷰(View)

1. 외부스키마는 사용자나 응용프로그래머가 각 개인의 입장에서 필요로 하는 데이터베이스의 논리적 구조를 정의한 것이다.

2. 외부스키마는 전체 데이터베이스의 한 논리적인 부분으로 볼 수 있으므로 서브 스키마(Sub Schema)라고도 한다.

3. 하나의 데이터베이스 시스템에는 여러개의 외부 스키마가 존재할 수 있으며 하나의 외부 스키마를 여러개의 응용 프로그램이나 사용자가 공용할 수도 있다.

4. 같은 데이터베이스에 대해서도 서로 다른 관점을 정의할 수 있도록 허용한다.

5. 일반 사용자는 질의어(SQL)을 이용하여 DB를 쉽게 사용할 수 있다.

6. 응용 프로그래머는 C, JAVA등의 언어를 사용하여 DB에 접근한다.

개념 스키마(Conceptual Schema) = 전체적인 뷰(View)

1. 개념 스키마는 데이터베이스의 전체적인 논리적 구조로서, 모든 응용 프로그램이나 사용자들이 필요로 하는 데이터를 종합한 조직 전체의 데이터베이스로 하나만 존재한다.

2. 개념스키마는 개체간의 관계와 제약 조건을 나타내고 데이터베이스의 접근 권한, 보안 및 무결성 규칙에 관한 명세를 정의한다.

3. 데이터베이스 파일에 저장되는 데이터의 형태를 나타내는 것으로, 단순히 스키마(Schema)라고 하면 개념 스키마를 의미한다.

4. 기관이나 조직체의 관점에서 데이터베이스를 정의한것이다.

5. 데이터베이스 관리자(DBA)에 의해서 구성된다.

내부 스키마(Internal Schema) = 저장 스키마(Storage Schema)

1. 내부 스키마는 물리적 저장장치의 입장에서 본 데이터베이스 구조로, 물리적인 저장장치와 밀접한 계층이다.

2. 내부스키마는 실제로 데이터베이스에 저장될 레코드의 물리적인 구조를 정의하고, 저장 데이터 항목의 표현방법, 내부 레코드의 물리적 순서 등을 나타낸다.

3. 시스템 프로그래머나 시스템 설계자가 보는 관점의 스키마이다.

또다른 정의

[그림 1. 스키마의 예]

스키마는 3계층 스키마로 나누어져 구성되어있고, 이 각각의 스키마는 결국 데이터베이스의 구조와 제약조건에 대한 전반적인 명세를 기술한 것을 의미한다. 3계층으로 스키마를 나눈 이유는 관점에 따라서 분류한 것이다.

 - 외부스키마 : 개인의 입장, '서브스키마'라고도 한다, 사용자 뷰를 가리킨다. 

                    하나의 외부스키마는 여럿이 공유 가능하며, 

                    하나의 DB시스템에 여러 개의 외부스키마가 존재 가능

 - 내부스키마 : 시스템 프로그래머나 설계자의 관점에서 바라보는 스키마,

                    데이터베이스의 물리적 구조를 가리킴(= 실제 저장방법을 기술하는 물리적인 저장장치와 관련됨) 

 - 개념스키마 : 조직 전체의 입장, 전체적인 뷰를 가리킨다.

                    개체간의 관계와 제약조건을 나타내고, 

                    데이터베이스의 접근권한/보안/무결성 규칙에 대한 명세를 정의함, 

   일반적으로 '스키마'라는 내부스키마를 가리킴, 

   내부스키마는 DBA가 만듦, 데이터베이스의 전체적인 구조로써 하나만 존재해야 함

출처: https://ykcb.tistory.com/entry/데이터베이스-스키마의-개념-특징 [YKCB Team]

각 DataBase별 환경설정 부분은 dicws.tistory.com/62 앞에서 확인하시고

이번에는 엑셀에서 해당 테이블을 가져오기 위해 추가설정부분에 대한 얘기를 하고자 합니다.

엑셀만 그렇지는 않고 보통 개발하다 보면 라이브러리가 필요로 할때가 많습니다. 그리고 많은 라이브러리들을 추가해주고 설정해야됩니다. 이런과정이 VBA에도 있습니다.

데이터 연결방법 두가지

    윈도우즈 각 드라이버를 설치하고,

       첫번째는 윈도우드ODBC에서 연결을 하고 연결된 ODB를 통하여 데이터 연결하는 방법

       두번째는 윈도우에 각 드라이버 직접접속 하여 데이터 연결하는 방법 

           두번째는 CreationObject를 통하여 각 DataBase 종류별로 Connection 스트링을 맞춰 직접 연결

           첫번째는 OLBC에서 미리 접속연결하고 동일한 Connection 스트링으로 연결하면 됩니다.

일단 라이브러리 추가 -> 데이터 베이스 연결을 해보겠습니다.

▶ 라이브러리 추가

   VBA에서 DataBase를 연결하기위해 또는 일반 파일을 엑세스 하기위해 라이브러리를 추가해야됩니다.

   먼저 DataBase를 연결하기 위한 라이브러리를 추가합니다.

    메뉴에서 도구->참조

Microsoft ActiveX Data Object Library를 선택 버전은 여러개 있는데 2.0이후부터인데 보통 2.7, 2.8 6.1 선택하시면 됩니다.

▶ DataBase 연결정보 확인

     연결전 드라이버를 확인

      검색->ODBC 입력후-> 64bit선택

▶ DataBase 직접연결 방법

Option Explicit                        '명시적 선언
Public dbconn As ADODB.Connection      '추가한 라이브러리 선언

Public Sub DBTest()

    'DataBase 연결 사용하기 위한 변수 설정
    Dim strCon  As String            '연결String ※요게 DataBase마다 다름 
    Dim rs      As ADODB.Recordset   'Variant 형으로 선언해도 됨 
    Dim sql
    
    
    '1-1) Mysql기준으로 Connection String 작성
    strCon = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=127.0.0.1;PORT=3306;UID=root;PWD=해당패스워드;DATABASE=mysql;"
    
    '1-2) 커넥션 객체 생성 (객체 연결방법 두가지) 
    'Set dbconn = CreateObject("ADODB.Connection")   '라이브러리를 추가하지 않고 직접생성방법
    Set dbconn = New ADODB.Connection                '라이브러리를 참조로 추가했기 때문에 사용가능
   
    dbconn.Open strCon      '1-3)연결
    
    '2-1) DataBase 접속한후 작업
    'Set rs = CreateObject("ADODB.RecordSet")        'RecordSet이라고 조회후 내용을 담아둘 객체 수기생성 
    Set rs = New ADODB.Recordset                     '참조된 라이브러리로 생성
    
    '2-2) 쿼리 작성
    sql = "select * from db "
    
    '2-3) 쿼리 수행(여기서는 조회쿼리로 RecordSet에 담아서 결과출력)
    rs.Open sql, dbconn                    'Connecton 객체로 sql 수행
   
    While rs.EOF = False
   
        Debug.Print rs.Fields(1)           '조회결과를 index로 출력
        'Debug.Print rs("컬럼명")          '컬럼명을 가지고 출력 
       
        rs.MoveNext                        '다음Record로 전환 
    Wend
    
    rs.close      'RecordSet 객체 닫기 : 자원반납
    dbconn.close  'Connection 객체 닫기: 자원반납  

End Sub

기본적인 연결을 정의해보았고 DataBase별 다른부분을 설명하겠습니다.

다른부분은 DataBase별 StrCon이라고 Connection String이 달라집니다. 

▶DataBase 종류Connection String

   1) Excel

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=파일경로;Extended Properties=Excel 12.0;"

※ 엑셀도 일반 DataBase처럼 SQL로 조회 수정이 가능합니다.

  2) Oracle

strCon = "DRIVER={드라이버명};Uid=사용자ID;Pwd=사용자암호;DBQ=오라클TNS명;DBA=W;"

※ 드라이버명은 위에서 Oracle in instantclient_19_8 입니다.

  3) Mysql

strCon = "Driver={드라이버명};SERVER=서버IP;PORT=서버포트;UID=사용자ID;PWD=사용자암호;DATABASE=데이터베이스명;"

※ 드라이버명은 위에서 MySQL ODBC 8.0 Unicode Driver 입니다

4) IBM DB2

strCon = "Driver={드라이버명};Database=데이터베이스명;Hostname=서버IP;Port=서버포트;Protocol=TCPIP;Uid=사용자ID;Pwd=패스워드;CurrentSchema=스키마명;"

※ 드라이버명은 위에서 IBM DB2 ODBC DRIVER 입니다

현재 개발을 하면서 DataBase없이는 개발을 할수 없는 상황입니다. 더군다나 BigData 시대라 불리우는데 DataBase가 없어서는 안될 필요 조건이라 생각됩니다.

엑셀에서는 2000년도 사용당시 DataBase는 사용한다는 생각을 안해봤지만 그이후 개발하면서 많은 DataBase에 정보를 수집하고 편집하고 하는 일들이 많았다 생각되어집니다.

그러므로 엑셀을 사용함에 있어 DataBase연결이 필수라 생각되어 지고 또한 연결을 통해 많은 정보들을 가져오기 위해 몇가지 설정들이 있습니다. 이제 이부분에 대한 설명을 하겠습니다.

윈도우에는 기본적으로 ODBC가 있습니다(데이터베이스를 연결하기 위한설정)

여기에 해당 드라이버를 설치하고 나면 사용할수 있는 드라이버를 등록되었을경우

엑셀에서는 ODBC를 통하여 DataBase에 연결하여 가져올수 있습니다.

윈도우 8이상은 검색에서 odbc를 입력하면 해당 odbc가 나옵니다. -> 현재 윈도우10 (버전수정)

os에 따라 64 bit or 32bit 두가지로 나오면 os 처리방식하고 동일한 office bit를 설치하길 권하여 드립니다. 그래야 서로 동일하게 처리할수 있습니다.

해당 odbc를 실행하면 먼저 드라이버 텝에 연결하고자 하는 드라이버가 설치됬는지 확인이 필요합니다.

※ 참고로 OLEDB 는 첫번째 드라이버 추가 필수 이고 OLEDB에 시스템 DSN, 사용자 DSN에 Connect을 맺고

   기본적인 OLEDB로 모든 데이터 베이스를 연결할수 있도록 고안된 설정 프로그램임

▶ 엑셀에서 ODBC를 사용하기 위한 Driver 설치 

    ORACLE, MYSQL(마리아도 동일) 드라이버가 설치되어야 됩니다.

    1) Mysql or 마리아DB(설치과정 생략) : https://dev.mysql.com/downloads/connector/odbc/

             다운 파일 mysql-connector-odbc-8.0.13-winx64.msi

            설치후 odbc화면 : odbc가 설치됨을 확인할수 있습니다.

     2) Oracle  다운로드 및 설치 (odbc 패키지에 odbc_install.exe를 실행)

         ▶ 설치

* 아래의 홈페이지에서 아래의 파일을 다운로드한다.
  다운로드 사이트 : http://www.oracle.com/technetwork/topics/winx64soft-089540.html
  다운로드 파일명 
    instantclient-basic-windows.x64-11.2.0.3.0.zip
    instantclient-odbc-windows.x64-11.2.0.3.0.zip
    
* 다운로드 받은 파일의 압축을 해제한다.
  - 압축 해제하면 instantclient_11_2 폴더에 모든 파일이 존재한다.

* instantclient_11_2 를 C:\Oracle 폴더로 복사한다.
  - C:\Oracle 이 없으면 생성한다.

* 관리자 권한으로 DOS 창을 실행한다.
  - C:\Oracle\instantclient_11_2 폴더로 이동한 후, 아래와 같이 명령을 실행한다.
  - ODBC 설치가 정상적으로 되었으면 아래와 같이 성공 메시지가 보여진다.

*설치 실행
C:\Oracle\instantclient_11_2>odbc_install.exe
Oracle ODBC Driver is installed successfully. 

      ▶ 드라이버 확인

    ▶ 환경변수 등록: 검색에서 ->고급 시스템 설정 -> 환경변수 등록

ORACLE_HOME=C:\Oracle\instantclient_19_8
TNS_ADMIN=c:\Oracle\instantclient_19_8 ( tnsnames.ora 파일이 위치할 폴더 )
NLS_LANG=KOREAN_KOREA.KO16MSWIN949 ( 한글관련 설정 )
         KOREAN_KOREA.AL32UTF8  (이것도 있는듯)
PATH=c:\Oracle\instantclient_19_8 ( 기존 패스에 추가 )

  ▶ tnsnames.ora파일 생성 : ORACLE HOME 지정위치에 C:\Oracle\instantclient_19_8

# Generated by Oracle configuration tools.
# ORCL : 서비스 이름 또는 tns name
# DESCRIPTION : 접속하고자 하는 대상 데이터베이스 정보
# ADDRESS : 접속하고자 하는 데이터베이스 서버의 리스너를 호출하기 위한 주소 정보(프로토콜, 호스트, 리스너 포트)
# CONNECT_DATA : SERVICE_NAME 옵션을 이용하여 접속할 리스너 프로세스가 사용하는 서비스 이름을 지정하거나 
#                또는 SID옵션을 이용하여 데이터베이스의 SID명을 지정
# SERVER = DEDICATED : 오라클 서버와 연결시 생성된 프로세스를 전용연결 
                     Dedicated Server(전용 서버) 방식 혹은 Shared Server(공유 서버) 방식으로 운영함
# SERVICE_NAME = MYNAME : 오라클 서버의 리스너(listener.ora)에 기술된 서비스명 
                         / 서버측에 사용자 (DB관리자)가 임의로 작성하여 넣은 이름

ORCL=
(DESCRIPTION=
 (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=TCP)(HOST=아이피)(PORT=포트))
 )
 (CONNECT_DATA=
 (SERVER=DEDICATED)
 (SERVICE_NAME=xe)
 )
)

     3) DB2 Client 설치 

         다운로드 : http://www-01.ibm.com/support/docview.wss?uid=swg27007053
위 URL 접속하면 아래 화면이 보이는데 원하는 버전을 선택합니다.

          ※ 압축을 풀고 나면 실행파일 setup.exe 실행하여 제품 설치

 4) SqlLite ODBC 설치

    다운로드 사이트 : www.ch-werner.de/sqliteodbc

    ※ 64bit는 w64.exe를 설치하시면 됩니다.

▶ 설치후 odbc 조회

 5) Mongo DB ODBC 설치

   다운로드 사이트 : docs.mongodb.com/bi-connector/master/reference/odbc-driver

주의는 Visual C++ Redistributable For Visual Studio 2005가 설치되어 있어야됨

툴에 기능에 많은것을 알면 좋지만 이정도만 알았으면 하는 기능을 중간정리 해봅시다

엑셀을 도구 만들기 좋은 하나는 천천히 한줄씩 처리하면서 처리과정 및 값들을 볼수 있고

또한 다시 위치를 위로 변경하고 값을 변경해서 디버깅하는 기능이 잘되어 있다고 생각되어 집니다.

이러한 기능을 사용하기 하단 3가지만 잘 사용하여도 됩니다.

▶ VBA 툴 사용 팁

    1) 편집: 중간에 툴이 나옴

    2) 직접실행창: 하단에 창이 나옴 특히 프로그램에서

        ex1) Debug.Print에서 값을 출력하면 나옴.

        ex2) 값을 변경할때는 직접실행에서 i=10 하고 Enter를 쓰면 값이 10으로 변경

     3) 조사식 : 마우스 오른쪽을 누르고 해당 변수를 등록해주면 값이나 속성들이 변경됨에 따라 많은 정보가 나옴

   4) 코딩시 일괄로 주석 처리 및 주석해제 단축키

여기서는 기본적은 VB의 문법을 소개 하고자 합니다.

엑셀 매크로는 VB의 문법을 사용하고 있고 추가로 엑셀의 함수를 지원하여 VBA라고 불리워 집니다.

그러므로 VB문법에 대해 소개 하고자 합니다.

▶ 변수선언 방법 두가지 : 명시적/묵시적

    Java 나 C를 해보신분은 변수를 선언하지 않으면 컴파일이 안됩니다 이것을 명시적이라 합니다..  

    VB에서는 기본적으로 변수 선언없이(묵시적) 으로 사용가능하지만, 코딩이 길어질때 오히려 명시적으로 하는것이 좋습니다. 

그래서 변수 형이 지정되어 있지 않은 Variant 형이 VB에서만 존재 합니다.

Variant는 변수가 문자가 처음 저장되면 String형, 숫자가 저장되면 Integer로 알아서 내부적으로 적용됩니다.

▶ 변수선언  및 타입 확인

 dim 변수명 as 변수타입    : 변수 선언방법
 
 ※ Integer, String,Variant   : 변수타입 종류  

   ※ as 부터 뒤에가 없으면 Variant(형이 없는)으로 선언되면 변수값이 저장될때 형이 지정됩니다.

▶ 조건문

if  조건문 then

elseif 조건문  ==> 생략가능

else              ==>생략가능

end if

ex)
If a > 10 Then
   MsgBox("10이상")
ElseIf b > 5 Then
   MsgBox("5이상")
Else
   MsgBox("기타")
End If 

 자바와 기본적으로 다른 문법을 사용합니다.

▶ 반복문 (For,  Loop)

▶ 활용예시

    자 그럼 활용예로 엑셀시트에 1행부터 10행까지 x 10을 해서 쓰는거 , 쓴것을 읽는것을 한번 해봅시다

   1) 쓰기 예제

Public Sub 사용자처리()
 
    For i = 1 To 10
   
      Sheets("sheet1").Cells(i, 1) = i * 10
     
    Next
    
End Sub

   2) 읽기 예제

 For i = 1 To 10
   
     'vba 편집에서 보기->직접실행을 선택하면 하단에 debug.print한 값이 나옴
     '->자바에서는 붙여쓰기를 + 로 하는데 vb는& 로 함 +인경우는 연산자로 인식
     Debug.Print "시트내용:" & Sheets("sheet1").Cells(i, 1)
      
 Next

▶ 엑셀시트에 위치 및 셀에 값을 변경하기

Public Sub 사용자처리()
    Sheet("sheet1").Cells(3, 3) = "하하하하"
End Sub

※ 참고
Sheet("시트명") : 특정 시트
Workbooks("파일명").Sheet("시트명") : 해당파일명에 해당 시트명

ActiveWorkbook : 현재 활성화 되어 있는 파일 [("파일명")은 없음]
ActiveSheet    : 현재 활성화 되어 있는 시트 [("시트명")은 없음]

▶ 엑셀 시트의 위치를 선택하는 두가지 방법

 Range("C2") 라 직접 쓰는 방식이 있지만 우리가 반복작업을 하기위해 A->B->C를 FOR문이나  lOOP에서 돌리기 어렵기 때문에 Cells(세로줄 라인위치, 가로줄 라인 위치)를 사용합니다.

하단은 ' 로 주석을 하였고 동일한것으로 Cells명령어를 사용하여 작성해보았습니다.

End Sub
    '주석 엑셀 시트에 가져오는 방법
    MsgBox Cells(3, 3)         'Cells(행[y축] 위치, 열[x축] 위치) 표시
    MsgBox Range("C3")         'Range는 열은 영문으로 행은 위치로 표시  
   
End Sub

※ 주석은 ' 작은따옴표 하나이며 작은따옴표 뒤에는 전부 주석됨   

※ 위 두가지 방법은 동일한 결과를 갖게 됨

▶ 엑셀 시트쓰기, 엑셀시트에서 값을 사용하기

   등호(=)을 사용하고 오른쪽에 시트에 저장할값을 쓰면됩니다.

 Public Sub 사용자처리()
   
    Cells(3, 3) = "하하하하"                     '=>엑셀시트에 하하하하를 저장
    Range ("C3")= "하하하하"
    
    MsgBox  Range ("C3")                        ' 엑셀시트값을 사용하기
   
End Sub

  처리결과)

Sheets는 일반 Visual Basic(일명 VB)에는 없는 함수로 엑셀에 내장되어 있는 VB로 사람들은 Visual Basic Application(일명 VBA) 라고 합니다. 

※ 요약정보

파일함수.시트함수.셀함수

파일함수 : ActiveWorkbook,Workbooks("파일명")
시트함수 : ActiveSheet. WorkSheet("시트명")
셀함수   : ActiveCell, Cell(행 위치, 열 위치), Range( 열 영문, 행위치)
           Range예) Range("D1") 하고 Cells(1,4) 는 동일함
           
※ Cells를 많이 쓰는이유는 For, Loop문으로 코딩하기 위해 많이 사용함