10년여 IT계통에 있으면서 사용한 DB가 많지 않아 Tool 또한 Toad, SQL Developer, SQL Gate 등 몇가지 종류밖에 사용하지 않았는데 이번에 그룹내 IT팀에 들어가게 되면서 다양한 DB 사용으로 다양한 DB를 지원하는 Tool이 필요하게 되었다. 그래서 추천 받은 Tool은 DBeaver!
최근 오라클만 계속 사용하다 보니, 정말 기본정보이지만 MySql을 제대로 해 본 적이 없어 자주 사용하는 명령어의 기본적인 차이점과 문법에 대해 공부겸 기록한다.
1. 현재 시간 ! oracle은 sysdate 1) DATE 타입의 컬럼에 현재 날짜를 입력하고 싶을 때NOW()함수를 사용하는 방법 ex) select now() as time from dual; : 2020-08-06 14:57:06.02) 날짜가 들어갈 컬럼(BOARD_YMD)의 데이터 타입을TIMESTAMP DEFAULT NOW()를 주고 INSERT 시 DEFAULT값을 입력하는 방법 ex) CREATE TABLE USER( USER_ID VARCHAR(20) NOT NULL, USER_TYPE VARCHAR(10) NOT NULL, CRE_DATE TIMESTAMP DEFAULT NOW(), PRIMARY KEY(USER_ID) ); insert into user values("rosebay","1",DEFAULT); 1-1. 현재 날짜 +- 처리(1년전/현재/1년후) 1-1-1) Oracle : select sysdate -365 as A, sysdate as B, sysdate+365 as C from dual; 19/09/08 | 20/09/07 | 21/09/07 1-1-2) Mysql select date_add(now(),interval -365 day) as A, now() as B, date_add(now(),interval 365 day) as C from dual; select date_add(now(),interval -12 Month) as A, now() as B, date_add(now(),interval 12 Month) C from dual; select date_add(now(),interval -1 Year) as A, now() as B, date_add(now(),interval 1 Year) C from dual; 2019-09-08 15:06:01.0 | 2020-09-07 15:06:01.0 | 2021-09-07 15:06:01.0
2. NULL 치환 1) oracle ex) select nvl(user_id,'1234') as id from dual; 2) mysql ex) select infull(user_id,'1234') as id from dual;
3. 날짜 포맷 변환 1) oracle ex) select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') as time from dual; => 2020-08-06 04:00:50 2) mysql ! %Y는 4자리년도(2020)이고, %y는 2자리년도(20)이다. 즉, 대소문자에 따라 다르게 표기됨. ex) select date_format(now(),'%Y-%m-%d %H:%i:%s') as time from dual; => 2020-08-06 15:59:56
4. 문자 합치기 1) oracle select '**'||'test'||'**' as text from dual; =>**test** 2) mysql select concat('**','test','**') as text from dual; => **test**
--컬럼명 사용하는 테이블 찾기 SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME = '컬럼명' -- DB에서 사용하는 Table, Procedure, Function 등에서 원하는 단어 위치 찾기 SELECT * FROM SYS.ALL_SOURCE WHERE TYPE='PACKAGE' AND TEXT LIKE '%소스에서 검색할 단어%'; -- DB 레퍼런스 확인 SELECT * FROM USER_DEPENDENCIES WHERE REFERENCED_NAME = 'P_IF_PCOFF'; SELECT * FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME = 'TB111'; -- 세션 lock, kill select a.sid, a.serial# from v$session a, v$lock b, dba_objects c where a.sid = b.sid and b.id1=c.object_id and b.type='TM' and c.object_name='오브젝트네임';
alter system kill session '337, 59059';
--1. 오라클 데이타베이스명을 확인하는 방법 SELECT NAME, DB_UNIQUE_NAME FROM v$database; --2. 오라클 SID를 확인하는 방법 SELECT instance FROM v$thread;
중복 체크 ---------------------------------------------------------------------- SELECT T1.컬럼명 , COUNT(*) FROM 테이블명 T1 GROUP BY T1.KIKB_DONG_NM HAVING COUNT(*) > 1 ;
DECLARE P_SQLCODE VARCHAR2(1000); -- 오류 로그 기록을 위함 P_SQLERRM VARCHAR2(1000); -- 오류 로그 기록을 위함 P_변수1 VARCHAR2(20); P_변수2 VARCHAR2(20); BEGIN /*시작*/ FOR PER_CHG in( select * from 테이블명 WHERE 조건부 ) /* FOR문 안에는 ; 세미콜론이 들어가지 않는다. */ Loop BEGIN /* FOR문의 조회 데이터 파라미터를 이용한 프로시저 실행이나 * insert/update/delete와 같은 DML 명령어 실행시 사용에 용의하다. */ P_PAPN_TPAP350_UPDATE(P_SQLCODE,P_SQLERRM,변수1,변수2,PER_CHG.변수a,PER_CHG.변수b); EXCEPTION WHEN OTHERS THEN /*화면 ERROR 로그 확인*/ DBMS_OUTPUT.PUT_LINE( 'SABUN='||PER_CH.SABUN ); P_SQLERRM := 'SABUN='||PER_CH.SABUN||'의 P_PAPN_TPAP350_UPDATE => ' || SQLERRM; P_COM_SET_LOG_NOCOMMIT('변수1,변수2','구분자','위치','SABUN='||PER_CH.SABUN ||SQLERRM,생성자); RETURN; END; END Loop; END;
1. 뷰 생성하기(테이블 및 컬럼은 예시 기재) CREATE OR REPLACE FORCE VIEW"계정명"."V_뷰이름" ("성명", "사번", "직위", "년차", "생년월일", "양력구분", "재직상태", "최종진급일", "전직급승진일") AS SELECT NAME AS 성명 , SABUN AS 사번 , JIKWEE_CD AS 직위 , F_COM_GET_JIKWEE_YEUNCHA(SABUN, TO_CHAR(SYSDATE, 'YYYYMMDD')) AS 년차 , BIR_YMD AS 생년월일 , DECODE(LUN_TYPE,1,'양',2,'음') AS 양력구분 , STATUS_CD AS 재직상태 , CURR_JIKGUB_YMD AS 최종진급일 , NVL(ORD_YMD,CURR_JIKGUB_YMD) AS 전직급승진일 FROM ( SELECT A.NAME AS NAME -- 성명 , A.RES_NO AS RES_NO -- 주민번호 , A.BIR_YMD AS BIR_YMD -- 생년월일 , A.EMP_YMD AS EMP_YMD -- 입사일 , A.GEMP_YMD AS GEMP_YMD -- 그룹입사일 , A.LUN_TYPE AS LUN_TYPE -- 양음구분(1;양, 2:음) , C.* , F_COM_GET_CURR_JIKWEE_YMD(A.ENTER_CD,A.SABUN,TO_CHAR(SYSDATE,'YYYYMMDD')) AS CURR_JIKGUB_YMD FROM 인사마스터 A , 인사기본 C WHERE A.ENTER_CD = C.ENTER_CD AND A.SABUN = C.SABUN AND A.RET_YMD IS NULL AND C.STATUS_CD NOT IN('RA') AND TO_CHAR(SYSDATE, 'yyyymmdd') BETWEEN C.SDATE AND NVL(C.EDATE, '99991231') ) WHERE SABUN NOT IN ('testinsa') -- 테스트계정 order by STATUS_CD, SABUN, NAME;
2. 권한주기 : 타 계정 grant select on ROSEBAY.V_INSA_INFO to INSA_INF; 계정명.뷰이름 to 권한 부여할 계정명 ! 유저 계정 생성하기 create user testUser identified ROSEBAY ※ USER를 생성하기 위해서는 USER생성 권한이 있는 사용자로 접속해야 한다.
3. 타 계정(INSA_INF)에서 조회하기 select * from ROSEBAY.V_INSA_INFO;
4. 동의어(synonym) 부여하기 create synonym V_INSA_INF for ROSEBAY.V_INSA_INFO; 동의어로 사용할 이름 for 원래 이름 select * fromV_INSA_INF; ( = select * fromROSEBAY.V_INSA_INFO; )