개발은 무서워요

어떤 테이블에서 아래와 같이 컬럼으로 부모 자식 간의 관계가 정의되어 있지 않고, 한 컬럼에 부모 데이터, 자식 데이터가 다모두 존재하면서 이 컬럼에서 부모 데이터와 자식 데이터를 구분할 수 있는 패턴이 존재하는 경우, 계층형 쿼리를 이용해서 해당 데이터의 계층 구조를 컬럼으로 분산할 수 있다. 예를 들어 아래와 같은 데이터가 있다고 가정할 때, 코드 코드명 A 자동차 A0 세단 A01 K8 A1 SUV A11 쏘렌토 자동차라는 ROOT 데이터 아래 LEAF노드로 A0, A01, A1, A11로 나뉘는 걸 파악할 수 있다. 부모/자식 코드로 혼재되어 있는 데이터를 컬럼별로 나누는 작업을 진행한다. SELECT 코드 AS 코드 , 코드명 AS 코드명 , SUBSTR(코드,1,LENGTH(코드)-1) AS ..

딕셔너리 뷰를 이용해서 특정 스키마에 있는 테이블들의 명세를 추출할 수 있다. ※ 테이블 정보 추출은 ALL_TABLES, ALL_TAB_COLS, ALL_TAB_COMMENTS, ALL_COL_COMMENTS 제약조건 추출은 ALL_CONSTRAINTS, ALL_CONS_COLUMNS을 이용한다. (제약조건은 PRIMARY KEY만 추출) /* SCOTT 스키마의 테이블 정보 조회 */ SELECT A.OWNER , A.TABLE_NAME , C.COMMENTS , B.COLUMN_NAME , D.COMMENTS , B.DATA_TYPE , B.DATA_LENGTH , B.DATA_PRECISION , B.DATA_SCALE , B.NULLABLE FROM ALL_TABLES A , ALL_TAB_CO..

기본제공되는 SCOTT 스키마로 SELECT 구문을 만들기 위해서 (SELECT ~ FROM ~) ALL_TAB_COLS, ALL_TAB_COMMENTS, ALL_COL_COMMENTS 를 이용한다. ※ DBA_* 로 시작하는 딕셔너리 뷰는 데이터베이스 내에 생성된 모든 스키마의 오브젝트를 조회할 수 있고, ALL_*로 시작되는 뷰는 현재 사용자의 조회 권한 범위 내의 스키마를 조회할 수 있으며, USER_*로 시작되는 뷰는 현재 접속한 사용자의 스키마 정보를 확인할 수 있다. /* SCOTT 스키마의 테이블 정보 조회 */ SELECT * FROM ALL_TAB_COLS WHERE 1=1 AND OWNER = 'SCOTT'; /* SCOTT 스키마의 테이블 정보 조회 */ SELECT * FROM AL..

CONNECT BY 구문을 이용해서 날짜 테이블을 만들 수 있다. 계층형 질의에서 쓰이는 구문이지만 조금 응용을 하면 원하는 범위의 날짜를 구할 수 있다. 아래의 예시를 보자. SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5; LEVEL ---------- 1 2 3 4 CONNECT BY를 조건절 처럼 걸었더니 4개의 행만이 나왔다. LEVEL 값이 차례대로 4까지 나온 것을 응용하여 금일 기준 +3 일까지 구할 수 있다. SELECT LEVEL , SYSDATE + (LEVEL -1) FROM DUAL CONNECT BY LEVEL < 5; LEVEL SYSDATE+(LEV ---------- ------------ 1 05-MAY-23 2 06-MAY-23 3 07-M..

■ 유저 생성 오라클에서 유저를 생성할 때는 아래와 같은 구문으로 생성한다. (DEV01 유저를 생성하고 해당 유저의 디폴트 TABLESPACE는 TS_DEV01로 설정한다.) --sqlplus 접속 sqlplus "/as sysdba" --19c이상 버전일 경우 pdb로 세션 변경 alter session set container=orclpdb; --user 생성 create user dev01 identified by dev01 default tablespace ts_dev01 temporary tablespace temp; --user생성 확인 select * from dba_users where 1=1 and username = 'DEV01'; 이렇게 생성하고 나서 접속을 시도하면 접속이 되지 않..

오라클을 실습하다 보면 간혹 SCOTT이 생성되어 있지 않은 경우가 있다. 이럴 경우 아래 경로에 파일에 SCOTT.SQL 파일을 찾을 수 있다. %ORACLE_HOME%\rdbms\admin 해당파일을 찾았다면 폴더 주소 입력창에서 'CMD' 입력한다. 입력 후 SQLPLUS를 실행시켜 접속 후, @SCOTT.SQL 명령어로 SCOTT.SQL 파일을 실행한다. SCOTT.SQL 파일 내용은 ① SCOTT 유저 생성 -> ② CONN SCOTT/TIGER 접속 -> ③ 데이터 스크립트 실행 으로 이루어져있다. 이 때, 19C 이상에서 PDB에 SCOTT을 만들 경우, - ALTER PLUGGABLE DATABAES PDB OPEN; 를 실행시킨 후 ② SCOTT.SQL에 아래라인을 추가한다. - ALTE..

데이터 흐름상 뒤에 위치하는 정보계나 테스트계을 맡아 프로젝트를 수행할 때, 개발 중인 운영계의 오브젝트 변경사항이 발생할 수 있다. 담당자가 있음에도 불구하고 여러가지 사정으로 변경분이 적용된 오브젝트가 생성되지 않는 경우가 생기거나, 권한 문제로 패키지를 사용 못할 경우를 대비해 아래와 같이 간단하게 쿼리로 DDL문을 구현할 수 있다. 테이블 스페이스명이나 제약조건 중 PK외에 조건이 있을 경우 인덱스 종류를 추가해야 할 경우는 아래 쿼리를 상황에 맞게 적용하면 될거 같다. /************************************************/ /* NAME : DDL CREATE /* DATE : 2022.06.13 /* PURPOSE : 오라클 DDL문 생성 /*********..

사이트를 다니다보면 가끔씩 컬럼에 SQL 문장 자체를 가지고 있는 경우가 있다. Cursor로 행별 데이터를 받아 실행하는 로직을 익명 블록으로 구현함 /* 선언부 */ DECLARE V_SQL VARCHAR2(4000); --커서를 생성한다 --SELECT 결과값을 담는다. CURSOR STR_SQL IS SELECT REPLACE(STR_SQL,';','') FROM --INSERT SQL이 있는 테이블 INSERT_SQL; /* 실행부 */ BEGIN OPEN STR_SQL; --커서오픈 LOOP --루프 시작 FETCH STR_SQL INTO V_SQL; --루프를 돌면서 순차적으로 결과행을 V_SQL에 담는다. EXIT WHEN STR_SQL%NOTFOUND; --커서에서 더이상 데이터가 없을 ..

오라클 12버전부터 멀티태넌트의 개념이 생기면서 유저 생성에 있어 명 앞에 c##을 붙여줘야 하게 되었다. 12버전 이전의 유저 생성을 원하면 alter session set "_oracle_script" = true; 으로 처리하면 된다.