mysql -u root 로 접속해본다. 실행 잘 되는지 확인 해 보고 Ctrl + C 로 중지
윈도우즈 서비스 등록
mysqld --install
서비스 제거
mysqld --remove
sql 백업 파일 import
mysql -h hostname -u username -p databasename < db_backup.sql
foreign key 오류.( ERROR 1215 (HY000) at line 3041: Cannot add foreign key constraint )
cat <(echo "SET FOREIGN_KEY_CHECKS=0;") imports.sql | mysql
date null 관련 오류.
select @@sql_mode; (또는 select @@global.sql_mode;) 실행하여 나오는 설정을 my.ini ( my.cnf ) 설정의 [mysqld] 아래의 sql_mode='...,...,..' 에 복사하여 집어 넣는다. 이때 NO_ZERO_DATE 관련한 설정을 제거한다. sql_mode 는 대소문을 구분한다.(대문자일때 서비스가 올라가지 않았다)
패스워드 변경 정책
쉬운 단어로 변경이 안될 때
정책확인 SHOW VARIABLES LIKE 'validate_password%';
정책을 낮게 변경 SET GLOBAL validate_password_policy=LOW;
테이블명 대소문자 구분(윈도우는 괜챃으나, 리눅스에서 간혹..)
#Error Code: 1146. Table 'dbname.table_name' doesn't exist -확인 show variables like 'lower_case_table_names'; (0: 대소문자 구분함) -수정 /etc/my.cnf 의 [mysqld] 영역에 lower_case_table_names=1 (1: 대소문자 구분안함) 을 추가한다. (재시작필요 systemctl restart mysqld)
TABLE 상세확인
show table status;
DB FILE 저장위치 확인
SHOW VARIABLES WHERE Variable_Name LIKE '%dir'; 또는 select @@datadir;
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND CREATED >= to_date('20120313', 'yyyyMMdd')
2. 순서, 컬럼명, 타입, 길이, NULL여부, 초기값 을 가져오기
SELECT COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_NAME' ORDER BY COLUMN_ID
3. 코멘트 가져오기
SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'TABLE_NAME'
4. 2번의 결과에 3번 코멘트 삽입
SELECT COLUMN_ID , COLUMN_NAME ,(SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME ) T_COMMENT , DATA_TYPE , DATA_LENGTH , NULLABLE , DATA_DEFAULT FROM ALL_TAB_COLUMNS ATC WHERE TABLE_NAME = 'TABLE_NAME' ORDER BY COLUMN_ID
5. 테이블의 PK 알아보기
SELECT A.OWNER || '.' || A.TABLE_NAME TABLE_NAME, A.CONSTRAINT_NAME, DECODE(A.STATUS, 'ENABLED', 'Y', NULL) ENABLED, A.CONSTRAINT_TYPE, B.COLUMN_NAME, B.POSITION FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B WHERE A.CONSTRAINT_TYPE IN ('P', 'R') AND A.TABLE_NAME = B.TABLE_NAME AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME='TABLE_NAME'
6. 4번의 결과에 5번의 PK 여부 추가하기
SELECT COLUMN_ID , COLUMN_NAME ,(SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME ) T_COMMENT , DATA_TYPE , DATA_LENGTH , NULLABLE , (SELECT DECODE(POSITION, 1, 'PK', 2, 'FK', '') IS_PK FROM USER_CONS_COLUMNS WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME AND POSITION IS NOT NULL ) IS_PK , DATA_DEFAULT FROM ALL_TAB_COLUMNS ATC WHERE TABLE_NAME = 'TABLE_NAME' ORDER BY COLUMN_ID
7. 1번의 테이블 목록을 추가 하여 최종 결과 보기
SELECT
TABLE_NAME
, COLUMN_ID , COLUMN_NAME ,(SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME ) T_COMMENT , DATA_TYPE , DATA_LENGTH , NULLABLE , (SELECT DECODE(POSITION, 1, 'PK', 2, 'FK', '') IS_PK FROM USER_CONS_COLUMNS WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME AND POSITION IS NOT NULL ) IS_PK , DATA_DEFAULT FROM ALL_TAB_COLUMNS ATC WHERE TABLE_NAME IN (SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND CREATED >= to_date('20120313', 'yyyyMMdd')) ORDER BY TABLE_NAME, COLUMN_ID
프로시저 작성을 완료 했으니 이제 스케쥴러에 등록을 해 보겠습니다.(자세하게 파악은 못했지만 스케줄과 잡은 구분되는 단어인것 같네요. 스케줄에 잡이 포함되는것 같습니다. 개별적인 job들을 따로 등록할수 있고 같은 시간에 동작할 job들은 스케줄을 만들어서 공통으로 동작할 시간으로 사용하는 거지요. 하지만 제 토드엔 스케줄이 안보이므로 일단 패스. 아마 sysadmin 이 아니거나 오라클10이 아니라서 그런듯.. 합니다.)
1. 작업이 시작될 날짜와 시간을 결정합니다.(first execution, At this time)
2. 매번 작동할 시간간격을 선택합니다. (subsequent executions)
3. What to excute 에 이전에 작성해둔 프로시저명을 입력합니다.
4. ShowSQL 버튼을 누르면 자동 작성될 SQL 구문을 미리 확인 할 수 있습니다.
5. OK 버튼을 누르고 완성된 잡을 선택하여 스크립트를 살펴보면 다음과 같습니다.
TEST_DATA 테이블의 값을 조금 수정해 두고 Job 을 1분뒤 첫 작동을 하도록 설정하고 등록하였습니다. 1분후 TEST_STATS 테이블에는 기대하던 대로 원하던 결과값이 정확히 변경 적용된것을 확인할 수 있었습니다. 또한 30분마다 작동하도록 설정하였으므로 소스테이블의 데이터를 다시한번 수정해 주고 30분 후에 다시한번 확인해 본 결과 원하던 값이 정확히 적용되었음을 확인할 수 있었습니다. 프로시저가 어려웠지 Job 등록은 어렵지 않네요. ^^
interval 은 다음작업할 시간까지의 간격을 나타내는 수식이 들어가는 부분이다.
'sysdate + 1/24' 1시간에 1번 'sysdate + 1' 1일에 1번 'sysdate + 7' 일주일에 한번
단순작업 주기가 아닌 특정 일시를 지정해 주고 싶다면 다음과 같이 작성다.
'trunc(sysdate) + 6 + 1/24' 매일 06시에 작업 실행 'trunc(sysdate, ''D'') + 7' 매주 일요일 00시에 작업 실행
위 잡들의 시간은 user_jobs 테이블에서 확인할수 있다. next_date 컬럼에서 다음에 작동할 시간을 알 수 있으며, interval 컬럼 부분을 복사해서 다음 쿼리를 사용해도 같은 결과를 얻을 수 있다.
SELECT trunc(sysdate) + 6 + 1/24 FROM DUAL;
하지만 'sysdate + 1' 부분을 집어넣고서 어랏 값이 달라~! 어떡~하지~ 같은 소리는 하지 말자. '지금 시간'으로 부터 계산되는거니까 당연히 다르다.
갑자기 통계쪽 데이터가 맞지 않다는 요청이 있어서 살펴 보았습니다. 일단은 검색을 통해 해당 테이블을 사용하는 곳이 있는지를 살펴 보았습니다. 통계를 보여주는 메인 페이지에서 SELECT 하는것 외에는 사용하는 곳이 없네요. 헐.. 대체 UPDATE 혹은 INSERT 를 해주는 곳이 없다면 이 테이블의 데이터는 대체 어디서 만들어 주는 것일까요.. 그래서 오라클에 혹시 예약작업이나 자동화 작업이 되는게 있는지 검색을 해보았습니다. 역시나.. jobs 라는 것이 있네요. 첨알았어요..;
바로 toad 의 schema browser 탭들을 뒤적여 보니 Jobs 가 있었고, 해당하는 테이블의 이름을 딴 스케쥴이 정의되어 있습니다. script를 살펴 보니 procedure 를 호출하도록 되어 있습니다. 이번엔 Proc 탭을 가서 찾아보니 빙고. 이곳에 작업이 정의 되어 있네요. 유레카!
하지만 전 DBA가 아니니 전문적으로 알필요까지는 없겠지만.. 그래도 간단하게 알고 넘어가봐야 겠습니다. 혹시라도 수동으로 동작시킨다던가, 스케쥴 시간을 조정하는 정도는 해야 할지도 모르니까요. 예제를 한두개 만들어 보는정도로 만족하려 합니다.
준비를 위해 테스트 테이블을 만들겠습니다. 년, 월 구분해서 갯수 뽑아내는 정도의 간단한 작업을 하려 합니다.
PL/SQL 은 Oracle’s Procedural Language extension to SQL 의 약자 이다.
SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,오라클 자체에 내장되어 있는 Procedure Language 이다.
DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항 이다.
PL/SQL 문은 블록 구조로 되어 있고 PL/SQL자신이 컴파일 엔진을 가지고 있다.
PL/SQL의 장점
PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB로 보내서 처리하므로 수행속도를 향상 시킬수 있다.
PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.
보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있다.
VARIABLE, CONSTANT, CURSOR, EXCEPTION을 정의하고, SQL문장과 Procedural 문장에서 사용 한다.
단순, 복잡한 데이터 형태의 변수를 선언 한다.
테이블의 데이터 구조와 컬럼명에 준하여 동적으로 변수를 선언 할 수 있다.
EXCEPTION 처리 루틴을 이용하여 Oracle Server Error를 처리 한다.
사용자 정의 에러를 선언하고 EXCEPTION 처리 루틴으로 처리 가능 하다.
끝이 없네요.. 전문가가 될것도 아니고 여기까지만 보겠습니다.
우선 procedure 를 작성해 보겠습니다.
사용하는 Toad 버전은 8.5.3.2, 오라클은9 입니다.
프로시저 에디터가 열리고 기본 문장들이 뭔가 많이 들어가 있는데요. 필요한 부분만 남기고 지우고 사용하면 되겠습니다.
다음은 최초 작성한 코드입니다.
CREATE OR REPLACE PROCEDURE pc_test_stats IS tmpVar NUMBER;
BEGIN tmpVar := 0; SELECT COUNT(*) INTO tmpVar FROM TEST_DATA WHERE t_year = '2012' AND t_month = '1'; END pc_test_stats; /
파란색의 부분들은 항상지켜져야 하는 문법입니다. 생성구문으로 시작하고 IS 와 BEGIN 사이에서 변수를 선언합니다. 프로시저를 끝낼때는 항상 '/' 를 넣어줘야합니다. Toad 의 프로시저에디터에서 작성을 마치면 F9 혹은 왼쪽 상단의화살표 버튼을 클릭하시면 컴파일이 실행됩니다. 문법의 오류가 있다면 여기서 에러를 출력하여 보여줍니다.
문제 없이 컴파일이 되었으면 다음으로 넘어가 보겠습니다. 이번엔 테스트용 통계 테이블에 값을 넣는 부분을 추가해 보겠습니다.
CREATE OR REPLACE PROCEDURE pc_test_stats IS
/* 변수가 선언되는 부분입니다. 타입을 지정해 주어야 합니다. IS 와 BEGIN 사이에 위치해야 합니다. */
tmpVar NUMBER;
BEGIN tmpVar := 0; SELECT COUNT(*) INTO tmpVar/* select 를 통해 얻은 결과값이 tmpVar 변수에 들어갑니다. */ FROM TEST_DATA WHERE t_year = '2012' AND t_month = '1'; INSERT INTO test_stats VALUES ('2012', '1', tmpVar); COMMIT; END pc_test_stats; /
이번에도 컴파일을 실행해 봅니다. 에러가 없이 통과 했다면 이제 실행을 시켜 봅시다. 토드의 Procs 탭으로 가보면 새로 작성한 프로시저가 목록에 추가되어 있는것을 볼수 있습니다. 선택하고 오른쪽 마우스를 클릭하면 메뉴가 나오는데 execute procedure 를 선택하여 실행합니다.
결과를 확인해 볼까요.
SELECT * FROM TEST_STATS;
정상적으로 작동하는걸 확인했습니다. 그럼 계속해서 이번엔 연별, 월별로 구분하는 작업을 추가해 보기로 하겠습니다.
우선은 루프문이 필요 할것 같습니다. 아마도 소스테이블의 연도와 달을 그룹으로 만든 값을 사용하면 될겠네요. 그럼 루프문은 어떻게 사용하는지 한번 적용해 볼까요.
그전에 DBMS Output 이라는 창을 열어두도록 하겠습니다. 콘솔같은거라고 생각합니다.(전문가가 아니라 저두 모릅니다--;)
View 메뉴에서 DBMS Output 을 선택해 줍시다.
아래와 같은 창이 열립니다.
반복문 (FOR, WHILE, LOOP)
다음은 루프를 적용하여 수정한 프로시져입니다.
CREATE OR REPLACE PROCEDURE pc_test_stats IS tmpVar NUMBER; v_year CHAR(4 byte); v_month CHAR(2 byte); BEGIN
FOR year_group IN (SELECT t_year, t_month FROM TEST_DATA GROUP BY t_year, t_month) LOOP tmpVar := 0; v_year := year_group.t_year; v_month := year_group.t_month; SELECT COUNT(*) INTO tmpVar FROM TEST_DATA WHERE t_year = v_year AND t_month = v_month; DELETE TEST_STATS WHERE t_year = v_year AND t_month = v_month; INSERT INTO test_stats VALUES (v_year, v_month, tmpVar); DBMS_OUTPUT.PUT_LINE(v_year || ':' || v_month || tmpVar); END LOOP; COMMIT; END pc_test_stats; /
연도와 월을 변수로 받기 위해 두개의 변수를 추가 했습니다. 컬럼명이 t_ 인데 변수를 v_ 로 했더니 눈에 잘 띄지도 않고 구분도 잘 안되서 오타때문에 컴파일 하다 오류가 몇번이나 났네요.. 구분이 잘 되게 사용하도록 합시다. ㅠㅠ
DBMS_OUPPUT.PUT_LINE 으로 표시한 부분이 DBMS Output 창에 출력됩니다. 출력이 되지 않는다면 BEGIN 다음 구문에 DBMS_OUTPUT.ENABLE; 를 입력해 보세요.
소스만 보게되면 그룹을 구성한 데이터를 선택하고 그걸 기반으로 루프를 돕니다. 한개의 row 씩 돌면서 각 변수에 값을 담고 해당하는 작업을 합니다. 기존의 데이터가 있을지 몰라서 DELETE 를 사용해서 먼저 삭제를 수행하도록 했습니다. 역시 컴파일후 프로시저를 실행시킨 후 TEST_STATS 의 데이터를 확인하니 원하는 결과값이 정상적으로 들어가 있음을 확인 할 수 있었습니다.
위의 FOR문외에도 WHILE 문처럼 사용 가능합니다. 무한루프에 빠질 위험이 큰 문장은 별로 사용하질 않는 주의라...
LOOP <- 세미콜론이 들어가지 않습니다.
EXIT; /* 바로 빠져나갑니다. */
EXIT WHEN index > 30; /* 조건문에 만족하는 경우 순환문을 빠져나갑니다. */
END LOOP;
혹은 WHILE index < 30 LOOP 의 형태로도 사용 가능합니다. while 문의 조건이 true 인동안 동작합니다. 이하 구문은 바로위의 예제와 같습니다.
조건문(IF)
간단하게 알아보려 했는데 글이 계속 길어 집니다. 다음은 IF 문입니다. 사용법만 적고 예제는 만들지 않습니다.
IF 조건문 THEN
ELSEIF 조건문 THEN
ELSE
END IF; <- 여기에만 세미콜론.
암시적커서(Implicit Cursor)
PL/SQL 내에서 SQL문이 실행되면 자동으로 커서가 생성되며, 그 속성을 사용할 수 있습니다.
BEGIN
/* SQL 이 시작되기전 커서가 자동으로 OPEN 됩니다. */
SELECT * FROM TEST_DATA;
/* SQL 이 종료 되면 커서가 자동으로 CLOSE 됩니다. */
/* 새로운 SQL 이 시작되기전 위 SQL에 대한 암시적 커서의 속성을 사용할 수 있습니다.
SQL%FOUND : 위 SQL 의 결과가 한개 이상 있는경우 true
SQL%NOTFOUND : 결과가 없는 경우 ture
SQL%ROWCOUNT : 결과 행의 수
END;
암시적 커서와 반대로 명시적커서(Explicit Cursor) 가 있는데요. 너무 깊이 가는거 같아서..-_-; 보지 않았습니다.
mysql> \s -------------- mysql Ver 14.14 Distrib 5.5.28, for osx10.6 (i386) using readline 5.1
Connection id: 459 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.28 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: euckr Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 1 hour 20 min 17 sec
Threads: 1 Questions: 643 Slow queries: 0 Opens: 34 Flush tables: 1 Open tables: 27 Queries per second avg: 0.133
