아래 SQL 을 가공하여 사용하면 더 쉬운 문서 한벌이 만들어 질 수 있으니 고효율이 아닐까요
SELECT T1.TABLE_NAME
, T3.COMMENTS AS TABLE_COMMENTS
, T1.COLUMN_NAME
, T2.COMMENTS AS COLUMN_COMMENTS
, CASE WHEN T1.DATA_TYPE IN ('LONG', 'CLOB', 'DATE') THEN DATA_TYPE
WHEN T1.DATA_TYPE = 'VARCHAR2' THEN DATA_TYPE || '(' || DATA_LENGTH || ' BYTE)'
WHEN T1.DATA_TYPE = 'NUMBER' THEN DATA_TYPE || '(' || DATA_LENGTH || ' BYTE)'
ELSE T1.DATA_TYPE
END AS DATA_TYPE
FROM ALL_TAB_COLUMNS T1
INNER JOIN USER_COL_COMMENTS T2
ON T1.TABLE_NAME = T2.TABLE_NAME
AND T1.COLUMN_NAME = T2.COLUMN_NAME
INNER JOIN USER_TAB_COMMENTS T3
ON T1.TABLE_NAME = T3.TABLE_NAME
WHERE OWNER = 'SCOTT'
AND T1.TABLE_NAME = 'EMP'
ORDER BY T1.TABLE_NAME, T1.COLUMN_ID
위의 SQL 만 놓고 보면 특정 한 개의 테이블을 조회를 하는것으로 보이지만
조회조건에서 TABLE_NAME 을 제거한다면
특정 스키마에 존재하는 테이블의 COLUMN 과 COMMENT, 그리고 DATA_TYPE 까지 한번에 조회할 수 있습니다.
테이블 정의서에 맞게 해당 SQL 을 가공하여 사용하면 시간을 조금이나마 아낄 수 있게 됩니다.
오라클에서 제공하는 시스템 테이블이 있는데 이 테이블을 활용하면 내가 원하는 내용을 쉽게 찾을 수 있습니다.
SELECT T1.OBJECT_ID
, T1.OBJECT_NAME
, T1.OBJECT_TYPE
, T2.TEXT
FROM USER_OBJECTS T1
INNER JOIN USER_SOURCE T2
ON T1.OBJECT_NAME = T2.NAME
WHERE T1.OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION')
AND T2.TEXT LIKE '%TEST_TABLE%' /* 찾으려는 프로시저 또는 함수 내 문구 입력 */
;
오라클 프로시저를 사용할 때 파라미터를 테이블에 사용하는 방법에 대해 알아보도록 하겠습니다.
그럼 일단 아래 예시 프로시저를 확인해볼까요!
CREATE OR REPLACE PROCEDURE PROC_TABLE_PARAM(p_TB_ID IN VARCHAR2, p_RET OUT VARCHAR2)
IS
/*----------------------------------------------------------------------------
프로그램명: PROC_TABLE_PARAM
작성일 : 2020/08/25
내 용 : 동적 테이블 파라메터를 이용하여 사용
------------------------------------------------------------------------------*/
v_FILE_NM VARCHAR2(100);
v_ERR_MESG VARCHAR2(4000);
v_RET VARCHAR2(1000);
v_SQL VARCHAR2(1000);
BEGIN
BEGIN
BEGIN
v_SQL := 'SELECT SUBSTR(FILE_ID, 1, INSTR(FILE_ID , '''||'.'||''',1)-1) AS FILE_ID FROM '|| p_TB_ID ||' WHERE ROWNUM = 1' ;
EXECUTE IMMEDIATE v_SQL INTO v_FILE_NM ;
END;
BEGIN
SELECT REGEXP_REPLACE ( TO_CHAR(WM_CONCAT(ERROR_MSG)), ',', ' | ') AS MSG
INTO v_ERR_MESG
FROM TEST_A T1
;
END;
BEGIN
UPDATE TEST_B
SET ERR_CODE = p_ERR_TYPE
, ERR_MESG = v_ERR_MESG
WHERE FILE_ID = v_FILE_NM
;
END;
COMMIT;
END;
p_RET := 'OK';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_RET := 'ERROR';
END;
여기서 중요하게 봐야 할 부분만 딱 집어보겠습니다.
#1. 테이블명 파라메터 받기
CREATE OR REPLACE PROCEDURE PROC_TABLE_PARAM(p_TB_ID IN VARCHAR2, p_RET OUT VARCHAR2)
p_TB_ID IN VARCHAR2 는 파라메터를 외부에서 받을 수 있습니다.
테이블명을 외부 프로그램에서 받아오면 프로시져에서 사용할 수 있겠네요
#2. 테이블명 FROM 절에서 사용하기
v_SQL := 'SELECT SUBSTR(FILE_ID, 1, INSTR(FILE_ID , '''||'.'||''',1)-1) AS FILE_ID FROM '|| p_TB_ID ||' WHERE ROWNUM = 1' ;
EXECUTE IMMEDIATE v_SQL INTO v_FILE_NM ;
SQL 문장을 변수에 담아서 문장을 만듭니다.
문장을 만들 땐 FROM 절에서 파라메터로 받아온 테이블을 파이프(||) 로 연결하여 사용하였습니다.
SELECT T1.JOB
, T2.DNAME
, T2.LOC
, SUM(T1.SAL) AS SALARY
FROM SCOTT.EMP T1
INNER JOIN SCOTT.DEPT T2
ON T1.DEPTNO = T2.DEPTNO
GROUP BY T1.JOB
, T2.DNAME
, T2.LOC
일반적인 GROUP BY 후 SUM 을 한 결과입니다.
JOB 과 DNAME 을 그룹핑 후 SAL 값의 합계를 조회한 것을 알 수 있습니다.
#ROLLUP
SELECT T1.JOB
, T2.DNAME
, T2.LOC
, SUM(T1.SAL) AS SALARY
FROM SCOTT.EMP T1
INNER JOIN SCOTT.DEPT T2
ON T1.DEPTNO = T2.DEPTNO
GROUP BY ROLLUP(T1.JOB, T2.DNAME, T2.LOC)
GROUP BY 절 안에 ROLLUP 함수를 사용하므로써
JOB, DNAME, LOC 별로 SALARY 의 소계값이 조회되고
가장 마지막에 JOB과 DNAME과 LOC 의 최종합계값, 즉 ROW DATA 의 전체합계가 조회된 것을 볼 수 있습니다.
#CUBE
SELECT T1.JOB
, T2.DNAME
, T2.LOC
, SUM(T1.SAL) AS SALARY
FROM SCOTT.EMP T1
INNER JOIN SCOTT.DEPT T2
ON T1.DEPTNO = T2.DEPTNO
GROUP BY CUBE(T1.JOB, T2.DNAME, T2.LOC)
CUBE 함수를 사용하면 ROLLUP 에서 사용했던
JOB, DNAME, LOC 별 소계값이 동일하게 조회되며
추가적으로 파란색 네모칸으로 조회되는 DNAME(부서)별 SALARY 의 합계값 까지 조회되는 것을 볼 수 있습니다.
CUBE 함수를 사용하면 GROUP BY 한 모든 항목에 대해서 집계값을 조회할 수 있는 것을 알 수 있네요
조회할 수 있는 모든 조합의 집계 정보를 보고 싶다면 CUBE 함수를 사용할 수 있습니다.
너무 많은 정보가 조회되어 데이터를 추출하는데 어려움이 있다면
조회조건을 추가하면 좀 더 간략한 정보를 볼 수 있으니 참고하시면 될 것 같습니다.
SELECT T1.JOB
, T2.DNAME
, T2.LOC
, SUM(T1.SAL) AS SALARY
FROM SCOTT.EMP T1
INNER JOIN SCOTT.DEPT T2
ON T1.DEPTNO = T2.DEPTNO
WHERE JOB = 'MANAGER'
GROUP BY CUBE(T1.JOB, T2.DNAME, T2.LOC)
ROLLUP 과 CUBE 의 결정적인 차이점이라고 한다면
ROLLUP 은 ROLLUP 함수 안에 들어온 컬럼수 + 1 개의 결과가 조회되며
CUBE 는 CUBE 함수 안에 들어온 컬럼수의 2의 n 승개의 결과가 조회되는것이 가장 크다고 볼 수 있네요
제가 사용한 예제는 GROUP BY 는 3 + 1 = 3 , CUBE 는 2*2*2 = 8 개의 결과가 나오는 것을 확인할 수 있습니다.
ROLLUP 이나 CUBE 함수는 실제로 많이 사용하지는 않지만 이런 기능이 있다 정도로만 알고 있으면 될 것 같습니다.
create tablespace TS_SAMPLE_REPORT
datafile '/data/oradata/COMPANY/TS_SAMPLE_REPORT.dbf'
size 10M
autoextend on next 10M
maxsize 100M
uniform size 10M
상세 오류의 내용은 아래와 같습니다.
명령의 29 행에서 시작하는 중 오류 발생 -
create tablespace TS_SAMPLE_REPORT
datafile '/data/oradata/COMPANY/TS_SAMPLE_REPORT.dbf'
size 10M
autoextend on next 10M
maxsize 100M
uniform size 10M
오류 보고 -
ORA-03214: 지정된 파일 크기가 기본 최소치보다 작습니다
03214. 00000 - "File Size specified is smaller than minimum required"
*Cause: File Size specified for add/resize datafile/tempfile does not
allow for the minimum required of one allocation unit.
*Action: Increase the specification for file size
SELECT TO_DATE('20200324', 'YYYYMMDDHH24MISS')
FROM DUAL;
결과
가장 기본적으로 사용할 수 있는 TO_DATE 문법입니다.
1번째 Parameter : char ( vhachar2 ) 값을 입력합니다.
2번째 Parameter : 날짜 형식을 지정해줍니다.
날짜 형식
YYYY : 4자리 년도
MM : 2자리 월
DD : 2자리 일
HH24 : 24시간 단위 시간
MI : 분
SS : 초
TO_DATE 형식변경
TO_CHAR 함수와 함께 사용한다면 더욱 보기 좋게 만들어줄 수 있습니다.
SELECT TO_CHAR(TO_DATE('20200324', 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') AS TO_DATE_형식1
, TO_CHAR(TO_DATE('20200324', 'YYYYMMDDHH24MISS'), 'YYYY/MM/DD HH24:MI:SS') AS TO_DATE_형식2
, TO_CHAR(TO_DATE('20200324', 'YYYYMMDDHH24MISS'), 'DD/MM/YYYY HH24:MI:SS') AS TO_DATE_형식3
FROM DUAL;
결과
날짜함수 활용
TO_DATE 함수를 이용하면 전일, 전월 등 날짜 계산도 할 수 있습니다.
SELECT TO_DATE('20200324', 'YYYYMMDDHH24MISS')-1 AS "전일"
, LAST_DAY(TO_DATE('20200324', 'YYYYMMDDHH24MISS')) AS "당월 마지막 일"
, ADD_MONTHS(TO_DATE('20200324', 'YYYYMMDDHH24MISS'),-1) AS "전월"
, TO_CHAR(ADD_MONTHS(TO_DATE('20200324', 'YYYYMMDD'),-1), 'YYYYMM')||'01' AS "전월1일"
, ADD_MONTHS(TO_DATE('20200324', 'YYYYMMDDHH24MISS'),-12) AS "전년"
FROM DUAL;
CREATE USER : 데이터 베이스 유저 생성 권한
SELECT ANY TABLE : 모든 유저의 테이블 조회 권한
CREATE SESSION : 데이터베이스 접속 권한
CREATE TABLE : 테이블 생성 권한
CREATE VIEW : view 생성 권한
CREATE PROCEDURE USER : procedure, function, package 생성 권한
CREATE SEQUENCE : sequence 생성 권한
SYSDBA : 데이터베이스 관리 최고 권한
SYSOPER : 데이터베이스 관리 권한
DML 권한 부여 및 권한 취소
GRANT DELETE, INSERT, SELECT, UPDATE ON 테이블명 TO 유저ID;
REVOKE DELETE, INSERT, SELECT, UPDATE ON 테이블명 TO 유저ID;
