내국인/외국인 주민등록번호 유효성 검사 개인정보법이 개정되어 주민등록번호 저장 시 암호화하여 저장해야 한다. 암호화되지 않은 주민등록번호가 있을시 불이익을 당할수 있으므로 해당 데이터를 삭제하거나 DB 컬럼을 암호화 해야한다. 주민등록번호와 유사한 계좌번호 등 13자리의 숫자가 존재하기도 한다. 진짜 주민등록번호인지 쉽게 판단할 수 있는 함수가 필요한데 오라클에서 기본으로 제공하지 않으므로 직접 생성해서 사용해야 한다. 아래의 함수는 내국인과 외국인 주민등록번호의 유효성을 판단하여 0 (주민번호 아님), 1 (주민번호) 을 리턴한다. 12345678910111213141516171819202122232425262728293031323334353637CREATE OR REPLACE FUNCTION FT_I..
오라클 원 단위 금액 절사 방법 TRUNC() 함수를 사용하여 원단위 절사를 할 수 있다아래는 \1,255원의 금액 일부를 절사하는 예제이다 --1원 단위 이하 절사 SELECT TRUNC(1255, -1) FROM DUAL ; 결과 : 1250 --10원 단위 이하 절사 SELECT TRUNC(1255, -2) FROM DUAL ; 결과 : 1200 --100원 단위 이하 절사 SELECT TRUNC(1255, -3) FROM DUAL ; 결과 : 1000
RAISE_APPLICATION_ERROR (사용자 에러코드 함수) PL/SQL에서 사용자정의 함수를 만들때 오류코드 또한 사용자가 임으로 설정을 할 수 있다. 다음 사용자 오류처리 함수를 사용하여 -20000 ~ -20999 사이의 코드를 사용하여 에러 메시지를 정의 할 수 있다. RAISE_APPLICATION_ERROR( [에러코드], [에러메시지] ) 에러코드 : -20000 ~ -20999 사이의 코드 예시) RAISE_APPLICATION_ERROR(-20000, '지정한 월이 부적합합니다.') 사용자 함수 예외처리(EXCEPTION) 부분에 에러코드 함수를 사용 에러 발생 시 함수에서 정의한 에러메시지가 출력된다 사용불가능한 에러코드를 사용할 경우 (-20000 ~ -20999 범위가 아닌경..
패키지(Pckage) 내부 프로시저(Prodedure) 조회 방법패지키명으로 조회를 하면 패키지에 포함된 프로시저명을 조회할 수 있다그 외에도 함수, 프로시저, 트리거 등도 조회가 가능하다 /* 패키지 내부 프로시저 조회 */ SELECT * FROM ALL_PROCEDURES WHERE OBJECT_NAME = 'PKG_SUP_CS_D_01' ; /* 프로시저 조회 */ SELECT * FROM ALL_PROCEDURES WHERE OBJECT_TYPE IN ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TRIGGER') ▲ 패지키에 포함된 프로시저가 조회 되는것을 확인 할 수 있다
오라클 REGEXP_REPLACE 다중(여러개) Replace(치환) 하는 방법 오라클 10g 부터 정규식 함수가 추가 되었다. 정규식을 사용하여 기존 함수보다 더 많은 기능을 수행한다.기존 Replace 함수를 사용하여 여러개의 문자를 치환하기 위해서는 해당 함수를 중첩으로 사용하였다. REGEXP_REPLACE 함수를 한번 사용하여 여러개의 문자를 치환하는 방법을 알아보자. --치환할 문자를 |로 구분하여 입력 SELECT REGEXP_REPLACE('Stay Hungry, Stay Foolish. - Steve Jobs', 'Stay|Foolish|Steve', '') FROM DUAL --결과: Hungry, . - Jobs ; --문자를 제거만 할꺼면 3번재 파라미터는 생략가능 SELECT RE..
오라클에서 날짜의 요일(Weekday)을 구하기 위해서는 TO_CHAR 함수를 사용하면 된다. TO_CHAR 함수는 날짜의 요일을 정수 또는 문자(영문 or 한글)로 반환받을 수 있다. 아래의 예제를 보면 쉽게 이해할 수 있을 것이다. 목차 요일 구하기 (정수) 요일 구하기 (한글) 사용 예제 요일 구하기 (정수) SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') AS "날짜" , TO_CHAR(SYSDATE, 'd') AS "요일" FROM dual TO_CHAR("날짜", "포맷") TO_CHAR 함수의 포맷 인자에 "d"를 입력하면 해당 날짜의 요일이 정수로 반환된다. (1:일, 2:월, 3:화, 4:수, 5:목, 6:금, 7:토) 요일 구하기 (한글) SELECT TO_CHAR(..
[ 오라클 날짜의 년을 더하고 빼는 방법 ] 날짜에서 년을 더하고 빼기 위해서는 ADD_MONTHS() 함수를 황용하여 년을 개월수로 환산하여 연산을 하거나 날짜에 일수를 빼는 방법을 주로 사용한다. 위의 방법도 똑같은 결과의 값을 얻을수 있지만 INTERVAL을 활용하면 조금더 쉽고 직관적으로 년을 더하거나 뺄수 있다. --년을 더하고 빼기 SELECT SYSDATE - (INTERVAL '2' YEAR) MINUS_YEAR , SYSDATE + (INTERVAL '2' YEAR) ADD_YEAR FROM DUAL ※ INTERVAL 여러가지 활용 방법 SYSDATE + (INTERVAL '1' YEAR) --1년 더하기 SYSDATE + (INTERVAL '1' MONTH) --1개월 더하기 SYSD..
ORA-01785: ORDER BY 항목은 SELECT 목록 식의 수라야 합니다 ORDER BY 절에서 해당 오류가 발생 하였을 경우 ORDER BY 절에 함수(DECODE, NVL, TO_NUMBER, 사용자함수 등)를 사용하였는지 확인해 보아야 한다. 일반적으로 ORDER BY 절에 함수를 사용하는건 아무런 이상이 없지만 해당 쿼리문에 집합연산자 ( UNION, UNION ALL , MINUS, INTERSECT)가 사용이 되었다면 오류가 발생 한다. 집한연산자가 사용된 쿼리문의 ORDER BY 절에는 함수 사용을 하지 말아야하며 꼭 사용해야 한다면 해당 쿼리문을 인라인 뷰(INLINE VIEW)로 감싸서 ORDER BY를 하면 된다. UNION ALL을 사용한 쿼리문에서 ORDER BY 절에 DEC..
오라클 쿼리를 작성하다 보면 칼럼의 수치값만큼 행의 개수를 늘려서 조회를 해야 하는 경우가 있다. CONNECT BY를 사용하여 순차적인 값이 있는 가상 테이블을 만들어서 조인하는 방법과 물리적인 테이블을 만들어서 조인하는 방법이 있다. 아래는 가상 테이블을 활용하여 칼럼의 수치값만큼 SELECT 행을 추가하는 방법이다. CNT 칼럼의 수치값만큼 해당 NM의 행을 늘리는 방법이다. WITH test_table AS ( SELECT 'AAA' nm, 3 cnt FROM dual UNION ALL SELECT 'BBB' nm, 5 cnt FROM dual UNION ALL SELECT 'CCC' nm, 2 cnt FROM dual ) SELECT a.nm , a.cnt , b.num FROM test_tab..
오라클 GROUP BY 쿼리에서 ROLLUP 함수를 사용하여 손쉽게 합계와 소계를 구할 수 있다. 1. GROUP BY 컬럼이 하나인 경우 - GROUP BY 절에 ROLLUP 함수를 추가한 후 그룹핑 컴럼을 인자로 넣는다 SELECT job , SUM(sal) FROM emp GROUP BY ROLLUP(job) 2. GROUP BY 컬럼이 두 개 이상인 경우 - 합계 및 소계 까지 계산되어 표시된다 SELECT job , deptno , SUM(sal) FROM emp GROUP BY ROLLUP(job, deptno) 3. GROUP BY 컬럼이 두 개 이상인 경우 합계만 표시하고 싶을 때 - HAVING 절에 GROUPING_ID 함수를 이용하여 소계 값을 제외할 수 있다 SELECT job , ..