Oracle SQL Developer 쿼리툴에서 PROCEDURE의 CURSOR를 출력하기 위해서는 REFCURSOR 변수를 선언하여 프로시저의 인자에 할당하면 된다. VAR rc REFCURSOR EXEC PC_TEST('10', :rc) PRINT rc 스크립트 실행(F5)으로 프로시저를 실행하면 커서의 결과가 출력된다
오라클에서 PIVOT을 사용하다보면 IN절에 동적으로 값을 할당하고 싶을때가 있다. 그러나 PIVOT의 IN절은 SQL의 IN절과 다르다. PIVOT의 IN절은 할당된 값의 개수와 순서를 맞춰서 결과가 출력된다. 일반적인 쿼리문의 PIVOT은 IN절에 동적으로 값을 할당 할 수 없지만, PIVOT XML 또는 Dynamic SQL을 활용하여 동적 PIVOT을 구현 할 수 있다. 일반적인 PIVOT 쿼리 SELECT * FROM (SELECT job , deptno , sal FROM emp ) PIVOT ( SUM(sal) FOR deptno IN ('10', '20', '30', '40') ) ORDER BY job PIVOT 쿼리는 IN절 ('10', '20', '30', '40')의 값과 순서에 따..
오라클에서 날짜별로 건수를 집계하면 존재하는 날짜만 조회되고 존재하지 않는 날짜는 표시되지 않는다. 상황에 따라서 건수가 존재하는 않는 날짜도 "0"으로 조회 되어야 할 상황이 있다. 가상의 날짜뷰를 만들어서 아우터 조인을 하면 해당 날짜가 데이터에 존재하지 않는 날짜도 표시가 가능하다. 필요에 따라서 이런 케이스가 많다면 물리적인 테이블을 생성해 놓고 사용하는 방법도 있다. 일반적인 일별 통계 (존재하는 일자만 표시) SELECT TO_CHAR(hiredate, 'YYYY-MM-DD') AS hiredate , COUNT(*) FROM emp WHERE hiredate BETWEEN TO_DATE('1981-02-01', 'YYYY-MM-DD') AND TO_DATE('1981-02-28', 'YYYY..
오라클에서 GREATEST 함수는 최대값, LEAST 함수는 최소값을 반환하는 함수이다. MAX, MIN 함수와 차이점은 서로다른 컬럼의 값을 비교한다는 것이다. 같은 행(Row)에서 서로다른 컬럼의 값을 비교하여 최댓값 또는 최솟값을 반환한다. 함수의 인자 개수는 무한대 수준으로 입력이 가능하다. GREATEST("값1", "값2", "값3", "값4", "값5", "값6", ...) 인자값 중 최댓값을 반환 LEAST("값1", "값2", "값3", "값4", "값5", "값6", ...) 인자값 중 최솟값을 반환 최댓값을 반환 (GREATEST) SELECT GREATEST(100, 200, 300, 400, 500) FROM dual --결과: 500 최솟값을 반환 (LEAST) SELECT LE..
오라클에서 쿼리문을 작성할 때 TO_CHAR() 함수는 날짜, 숫자 등의 값을 문자열로 변환하는 함수이다. 자주 사용하는 기본 함수이므로 아래의 다양한 변환 방법을 알고 있으면 많은 도움이 된다. 날짜 포맷 변경 (YYYY-MM-DD) SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') --20200723 , TO_CHAR(SYSDATE, 'YYYY/MM/DD') --2020/07/23 , TO_CHAR(SYSDATE, 'YYYY-MM-DD') --2020-07-23 , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') --2020-07-23 11:10:52 FROM dual YYYY: 년, MM: 월, DD: 일, HH24: 24시간, HH: 12시간, MI: 분,..
오라클 프로시저를 만들어서 사용하다 보면 아래와 같은 오류가 발생할 수 있다. 오류가 발생하는 이유는 CURSOR 변수에 값이 할당되지 않은 상태에서 프로시저의 실행이 끝나면 발생한다. ORA-01023: 커서 문맥이 없습니다(부적합한 커서번호) 아래의 프로시저를 보면 out_cursor에 값이 할당되기 전에 프로시저의 실행이 중단될 수 있기 때문에 주의해야 한다. CURSOR에 값이 할당되기 전에 프로시저의 실행이 중단된다면 오류가 발생한다. 커서(CURSOR)에 값이 할당되기 전에 RETURN이 되는 부분이 있는지 확인해 봐야 한다.
오라클에서 여러 행의 컬럼 값을 하나로 합치기 위해서는 XMLAGG(오라클 9i 이하), WM_CONCAT(오라클 10g~11g R1) 함수를 사용하였다. 오라클 11g R2 버전부터 WM_CONCAT 함수를 사용을 할 수 없으며 LISTAGG 함수를 사용해야 한다. (WM_CONCAT, XMLAGG 사용법) LISTAGG( ) 함수는 그룹 함수이기 때문에 GROUP BY 또는 PARTITION BY 절과 함께 사용해야 한다. WM_CONCAT 함수는 DISTINCT를 사용할 수 있으나, LISTAGG 함수는 사용할 수 없다. 그러나 정규식 함수를 사용하여 중복을 제거할 수 있으며 ORDER BY 절을 사용할 수 있어서 값의 정렬이 가능하다. LISTAGG([합칠 컬럼명], [구분자]) WITHIN GR..
오라클에서 컬럼을 변경 할 경우 ALTER TABLE를 사용하며, 컬럼의 데이터 타입, 길이를 수정할 때는 MODIFY, 컬럼명을 수정할 때는 RENAME COLUMN을 사용하면 된다. 컬럼 데이터 타입 변경 --데이터 타입 변경 : NUMBER(4) -> VARCHAR2(4) ALTER TABLE emp MODIFY editid VARCHAR2(4); ALTER TABLE [테이블명] MODIFY [컬럼명] [새로운 데이터타입(길이)] 데이터 타입을 변경해야 하는 경우 해당 컬럼의 값을 모두 지워야 변경이 가능하다. 컬럼 길이(크기) 변경 --데이터 길이(크기) 변경 : VARCHAR2(4) -> VARCHAR2(5) ALTER TABLE emp MODIFY editid VARCHAR2(5); ALTE..
오라클 테이블에 컬럼을 추가하기 위해서는 ALTER TABLE을 사용한다. 컬럼을 추가하면 테이블 컬럼의 마지막 순서에 추가가 되며, 컬럼 순서의 중간에 컬럼을 추가할 수는 없다. 오라클 12c 부터는 조금 쉽게 컬럼의 순서를 변경할 수 있도록 기능이 추가 되었으며, 이전 버전에서 컬럼 순서를 변경하기 위해서는 테이블을 재생성 해야 한다. 컬럼 추가, 삭제 --컬럼 추가 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 DROP COLUMN email; 컬럼 ..
오라클에서 테이블을 만들기 위해서는 CREATE TABLE을 사용하여 컬럼명, 데이터 타입을 정의하여 테이블을 생성하면 된다. 아래는 EMP(사원) 테이블을 만드는 예제이다. ① 테이블 명, ② 컬럼 명 - 테이블, 컬럼 명의 길이는 30byte 문자 (Oracle 12c R2 부터는 128byte) - 문자(영문, 한글), 숫자, 특수문자(_, $, #)만 가능 - 반드시 문자로 시작 ③ 데이터 타입 - NUMBER(4) : 4자리의 가변 길이 정수 - NUMBER(7,2) : 7자리의 가변 길이 정수와 2자리의 가변길이 소수 - VARCHAR2(10) : 10byte의 가변 길이 문자 ④ 기본 값 - 테이블에 값이 입력(INSERT) 될 때 값이 없으면 기본으로 생성되는 값 - DEFAULT [값(문..