오라클 사용자 정의 함수, 그룹 순위 결정 함수, DECODE함수,TO_CHAR()함수

 

 

오라클 PL/SQL 에서 제공하는 사용자 정의 함수 구문 

 

CREATE OR REPLACE FUNCTION 함수 이름(매개변수, 매개변수2)

RETRUN 데이터 타입;

IS
   변수, 상수 선언부분
   
BEGIN 
	실행부
    RETURN  반환 값;
[EXCEPTION 예외 처리부 ]

END[함수이름];

예: 매개변수 2개를 받아서 더하는 사용자 정의 함수 

 

CREATE OR REPLACE FUNCTION MY_ADD(NUM1 NUMBER, NUM2 NUMBER)
		RETRUN NUMBER --반환 데이터 타입은 NUMBER
      
      IS
      VN_SUM :=0;  --두 변수를 더해서 저장할 곳 
      
      BEGIN 
      VN_SUM = NUM1+NUM2; -- 연산
      
      END;

 

함수호출을 할 때에는 

 

select MY_ADD(1.12) from dual;

 

 

 

 

오라클 그룹 내에서 순위결정하는 함수 

 

 

select ROW_NUMBER() over(partition by [그룹핑할 컬럼 ] order by [정렬할 컬럼]) FROM 테이블명;
select RANK() over(partition by [그룹핑할 컬럼 ] order by [정렬할 컬럼])FROM 테이블명;
select DENSE_RANK() over(partition by [그룹핑할 컬럼 ] order by [정렬할 컬럼])FROM 테이블명;

 

 

1. ROW_NUMBER() : 1등이 2명이어도, 1등, 2등으로 나뉜다.

 

2. RANK() : 1등이 2명이면, 그 다음 순위는 3등이다. 

 

3. DENSE_RANK(): 1등이 2명이어도, 다음 순위는 2등이다. 

 

 

DECODE 함수 

 

DECODE함수는 IF ELSE와 비슷한 기능을 수행한다. 

 

DECODE (컬럼, 조건1, 결과1, 조건2, 결과2, 조건3, 결과 3)

DECODE (GENDER, 'm', '남자', 'f','여자','기타')

 

select gender, DECODE(gender, 'M', '남자', 'F','여자','기타') gender2 from temp

 

 

 

TO_CHAR(숫자 혹은 날짜) : 숫자나 날짜를 문자로 변환해주는 함수 

 

TO_CHAR(): 숫자, 날짜를 문자로 변환해주는 함수 

 

 

SELECT TO_CHAR(123456789, '999,999,999') FROM DUAL;

 

이렇게 하면 123,456,789 이 형식으로 출력된다. 

 

물론 날짜도 가능하다. 

 

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

 

이렇게 하면 2022-04-02로 출력된다. 

 

 

오라클 LISTAGG 함수 

 

LISTAGG 함수는 오라클에서 여러 행의 컬럼 값을 하나로 합치기 위한 함수이다. 

 

LISTAGG 함수는 그룹함수이기에, GROUP BY 또는 PARTITION BY 절과 함께 사용해야 한다. 

 

LISTAGG([합칠 컬럼명],[구분자]) WITHIN GROUP (ORDER BY[정렬할 컬럼명])

 

 

 

오라클 NVL 함수, NVL2함수  

 

NVL 함수는 값이 NULL 인 경우, 지정값을 출력하고 NULL이 아니면, 원래 값을 그대로 출력한다. 

 

NVL(COMM, 0) AS COMM2  -- COMM값이 NULL이면 0으로 치환된다.

 

NVL 2함수는 NULL이 아닌경우, 지정값1을 출력하고, NULL인 경우 지정값2를 출력한다. 

 

NVL2(COMM, 'Y','N') AS COMM2  -- NULL이 아니면 지정값1을, NULL이면 지정값2를 출력한다.

 

 

 

오라클 UNION  

 

UNION함수는  여러개의 SQL문을 합쳐 하나의 SQL문을 만드는 합집합을 만드는 함수이다. 

 

UNION: 두 쿼리 결과에서 중복되는 값은 삭제한다. 

 

UNION ALL: 두 쿼리 결과에서 중복되는 값을 모두 보여줍니다. 

 

SELECT * FROM TB1 UNION SELECT * FROM TB2

이렇게 UNION을 하면 합집합을 하여서, 두 테이블이 모두 조회가 된다.