두부의 코딩일기

오라클 쿼리를 짤 때도 조건문이 필요할 때가 생길텐데요. PL/SQL에서 IF ELSE 문을 이용하시면 됩니다. IF 조건 THEN -- 처리문 ELSIF 조건2 THEN -- 처리문 ELSE -- 처리문 END IF; 다른 언어와 구조는 똑같고 THEN 을 붙이는 부분과 ELSIF 명령어를 사용합니다. 마지막으로 END IF; 를 붙여줘야합니다. DECLARE p_NUM NUMBER := 90; BEGIN IF p_NUM

오라클에서 쿼리나 프로시저, 커서 등의 실행 시간을 알고 싶다면 SET TIMING ON; 을 사용하면 된다. SET TIMING ON; [실행할 쿼리문] SELECT * FROM DUAL; // Script Output Elapsed: 00 : 00 : 01 : 69

오늘 데이터베이스를 확인하던 중에 COUNT(1) 을 쓴 쿼리를 확인하고, COUNT(*) 과 어떤 차이가 있는지 궁금해서 검색해봤다. COUNT() 함수는 집계 함수로 SELECT 쿼리에 대한 행 수를 계산하는 함수이다. COUNT(*) vs COUNT(1) 결론부터 말하자면 두 개의 명령문에는 차이가 없다. 두 개의 명령문은 모두 동일한 방식으로 작동하며 성능 상에 차이가 없다고 공식 문서에도 명시되어있다. COUNT(*), COUNT(1) vs COUNT(컬럼명) COUNT(컬럼) : 해당 컬럼에 NULL 값이 들어간 행은 제외하고 계산함. COUNT(*) : NULL 값에 상관없이 모두 계산함. COUNT(컬럼) 명령문은 해당 컬럼에 NULL 값이 있으면 세지 않기 때문에 모든 행을 세는 COUN..

PL/SQL에서 사용자 정의 함수를 만들 때 오류 코드 또한 사용자가 임의로 설정할 수 있다. 아래와 같은 방법으로 에러 메시지를 정의할 수 있는데, 오류 코드의 범위는 -20000 ~ -20999다. RAISE_APPLICATION_ERROR(오류코드, 에러메시지); 오류코드: -20000 ~ -20999 예시 CREATE OR REPLACE PROCEDURE RAISE_TEST IS USER_ERROR EXCEPTION; CNT NUMBER; BEGIN SELECT COUNT(*) INTO CNT FROM TMP; IF CNT

오라클에서는 여러 행으로 조회된 컬럼 값을 하나로 합쳐주는 함수가 있다. 오라클 9i 이하에서는 XMLAGG, 오라클 10g - 11g R1 에서는 WM_CONCAT, 오라클 11g R2 부터는 WM_CONCAT 함수를 사용할 수 없고, LISTAGG 함수를 사용하면 된다. 오라클 버전 함수명 ~ 9i XMLAGG 10g ~ 11g R1 WM_CONCAT 11g R2 ~ LISTAGG LISTAGG 함수는 그룹 함수이므로 GROUP BY 또는 PARTITION BY 절과 함께 사용해야한다. 또한, WM_CONCAT 함수처럼 DISTINCT를 사용할 수 없지만 정규식 함수를 사용하여 중복 제거를 할 수 있고, ORDER BY절로 정렬도 가능하다. 기본 문법 SELECT LISTAGG(합칠 컬럼명, 구분자)..

오라클 프로시저, 함수를 호출할 때 주로 발생하는 에러로 프로시저 혹은 함수 내에서 Array (배열) 변수를 사용할 때, 주어진 크기를 넘어섰을 경우에 발생한다. DECLARE TYPE ARR_A IS TABLE OF VARCHAR2(5); ARR_B ARR_A; BEGIN ARR_B := ARR_A('A', 'B', 'C', 'D', 'E'); // 맨 마지막 요소 2개 삭제 ARR_B.TRIM(2); DBMS_OUTPUT.PUT_LINE(ARR_B(1)); // A DBMS_OUTPUT.PUT_LINE(ARR_B(2)); // B DBMS_OUTPUT.PUT_LINE(ARR_B(3)); // C DBMS_OUTPUT.PUT_LINE(ARR_B(4)); // 삭제된 데이터 (D) EXCEPTION W..

안녕하세요. 오늘은 명령 프롬포트 (cmd) 창에서 사용자 계정을 생성하고 권한을 주는 방법에 대해서 알아보겠습니다. 사용자 계정 생성하는 방법 1. 명령 프롬포트 (cmd)에서 sqlplus에 관리자로 접속한다. sqlplus "/as sysdba” 위 명령어로 접속이 되지 않는다면 sqlplus를 입력 후에 user-name에 sys as sysdba 를 입력하고, password는 입력하지 않고 엔터를 입력하면 된다. 2. 계정 생성 CREATE USER [ID] IDENTIFIED BY [PW]; ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다. 그대로 작성했는데 에러가 발생한다. 정상이다. 에러가 발생하지 않는 분들도 오라클 버전의 문제로 오라클 12c 기준으로 아래 버전은 에러가 ..

오라클에서는 함수 내의 인자값 중에서 최대값, 최솟값을 찾을 수 있는 함수를 제공한다. 최대값과 최솟값을 구하는 함수로 MAX 와 MIN 함수가 생각날텐데 사용 방법이 조금 다르다. MAX, MIN 은 검색 조건에 맞는 값 중에서 최대값, 최솟값을 찾는다. GREATEST, LEAST 는 여러 개의 열(Column)에서 최대값, 최소값을 찾고, 숫자가 아닌 문자열도 비교가 가능하다. GREATEST, LEAST SELECT GREATEST(100, 200, 300) AS GREATEST , LEAST(100, 200, 300) AS LEAST FROM DUAL; // 300, 100 여러 개의 인자값에서 최대값, 최소값을 뽑아올 수 있다. 위 쿼리의 GREATEST에선 300, LEAST에선 100이 출..

해당 오라클 오류는 데이터 타입을 맞춰주지 않아서 생기는 오류입니다. TO_CHAR 함수로 날짜 형식을 변경하려고 할 때 주로 발생하는 오류인데요. 아래 예시로 자세히 살펴보겠습니다. TO_CHAR('20220911', 'YYYYMMDD' // 오류 발생 TO_CHAR(TO_DATE('20220911'), 'YYYYMMDD'), 'YYYYMMDD') // 오류 해결 SELECT TO_CHAR(TO_DATE('20220911'), 'YYYYMMDD'), 'YYYYMMDD') AS TODAY_ FROM DUAL; 테이블 내의 date 컬럼을 넘겨주면 오류가 발생하지 않지만, 다른 형식의 컬럼을 넘겨주면 위와 같은 오류가 발생합니다. 이러한 오류는 TO_CHAR 함수 내에 TO_DATE 함수를 넣어서 쉽게 해..

오라클에서 사용자 계정의 잠금을 해제하기 위해선 ALTER USER 문을 사용하면 된다. 사용자 계정의 ACCOUNT 속성이 UNLOCK이면 잠금 해제, LOCK이면 잠금이다. 사용자 계정의 잠금 상태를 확인하고 직접 변경을 해보겠다. 사용자 계정 잠금 여부 조회 SELECT * FROM dba_users WHERE USERNAME = 'SCOTT' 사용자 계정 잠금 상태를 확인하고 싶으면 dba_users (시스템 뷰)를 조회하면 된다. 사용자 계정이 잠금일 경우에는 ACCOUNT_STATUS 컬럼이 LOCKED이고, 잠금이 아닐 경우에는 OPEN으로 되어있다. 현재 SCOTT 계정을 조회해보면 잠금이 해제된 상태인 것을 알 수 있다. 사용자 계정 잠금 / 잠금 해제하는 방법 ALTER USER SCO..