PL/SQL - 내장함수

오라클에서 제공하는 함수는 무조건 값을 반환한다

 

문자열관련 내장함수

 

• NVL(컬럼명,NULL인 경우 대체할 값) 
• LOWER('문자열'): 대문자 ---> 소문자
  UPPER('문자열'): 소문자 ---> 대문자
  ex) SELECT UPPER('oracle') FROM DUAL; //ORACLE
• INITCAP('문자열')
  - 첫 영문자를 대문자로 변환. 전부 대문자라면 첫문자를 제외하고 소문자로 자동변환된다.
  ex) SELECT INITCAP('oracle') FROM DUAL;   //Oracle
• CONCAT('문자열','문자열')
  - 문자열 연결.  || 를 사용해도 된다 
  ex) SELECT CONCAT('ORACLE','JAVA') FROM DUAL +>ORACLEJAVA
• LENGTH()
  - 문자열 길이
  ex) SELECT LENGTH('오라클') FROM DUAL;   //3
• LENGTHB()
  - 문자열 길이를 바이트로
  ex) SELECT LENGTHB('오라클') FROM DUAL;   //6
• LPAD('문자열', 문자열을 출력할 전체 자리수, '채울 문자열')
  - 좌측을 지정한 값으로 채운다
  ex) SELECT LPAD('HELLO',10,'X') FROM DUAL;  // XXXXXHELLO
       HELLO의 왼쪽을 채우는데 총10자리지정이니까 남는 5자리만큼 X를 입력하게 된다
  ex) SELECT LPAD('9',2,'0') FROM DUAL;  //09
• RPAD('문자열', 전체 자리수, '채울 문자열')
  - 우측을 지정한 값으로 채운다
  ex) SELECT LPAD('HELLO',10,'X') FROM DUAL;    //HELLOXXXXX
  ex) SELECT LPAD('9',2,'0') FROM DUAL;   //90
  ex) SELECT RPAD('930224-',14,'*') FROM DUAL;     //930224-******* 주민번호처럼
• INSTR('문자열','찾을 문자열')
  - 찾은 문자열의 인덱스 반환.  인덱스는 1부터 시작 / 찾은 문자열이 없다면 0 반환
  ex) SELECT INSTR('ABCDEFG','DE') FROM DUAL;   //4
  또한, LIKE연산자보다 INSTR(~~~,'#') !=0 이 더 성능이 좋다
     성능이 더 좋음 // SELECT * FROM DEPT WHERE instr(LOWER(DNAME),'e') !=0
     성능이 좀 떨어짐 // SELECT * FROM DEPT WHERE LOWER(DNAME) like '%e%' 
• SUBSTR('문자열', 시작인덱스, 길이)
  - 문자열에서 시작인덱스부터 길이 만큼 가져옴. 인덱스는 1부터 시작
  ex) SELECT SUBSTR('123456789',3,3) FROM DUAL;   //345
• REPLACE('문자열','바꿀 문자열','바뀔 문자열')
  - 특정 문자열을 다른 문자열로 대체
  ex) SELECT REPLACE('HELLO WORLD','HELLO','JAVA') FROM DUAL +>JAVA WORLD
• TO_CHAR(숫자 혹은 날짜);
  - 숫자형식 포맷문자열 또는 날짜형식 포맷 문자열로 변환  아래에 자세히!
• TO_NUMBER()
  - 문자형을 숫자형으로 변환
  ex) SELECT TO_NUMBER('123') + TO_NUMBER('456') FROM DUAL;   //579
  ex) SELECT '123' + '456' FROM DUAL;  //579
  사실 그냥 +를 써도 오라클이 알아서 숫자로 인식해준다. 
• TRIM()
  - 양쪽 공백 제거 (중간은 제거되지않는다)
  ex) SELECT TRIM('    JA  VA    ') FROM DUAL;    //'JA  VA'

 

***TO_CHAR(숫자,'숫자포맷문자열')   :: 숫자를 문자로 다룰떼

9는 값이 있으면 표시, 없으면 공백

0은 값이 있으면 표시, 없으면 0으로 표시

단 소수점은 9든 0이든 의미없이 값이 없으면 모두 0으로 표시됨

단 소수점은 실제값의 자리수가 많으면 짤리고(반올림),

정수인데 실제값의 자리수가 많으면 #으로 표시 

ex)SELECT TO_CHAR(123,'0999') FROM DUAL;  // 0123 값이없는경우 0으로 채우는효과. 총4자리

ex)SELECT TO_CHAR(123,'9999') FROM DUAL; //   123 값이없는 경우 공백. 총4자리

ex)SELECT TO_CHAR(1234,'$999,999.99') FROM DUAL;  //$1,234.00

ex)SELECT TO_CHAR(1234,'L999,999.00') FROM DUAL;  //\1,234.00

ex)SELECT TO_CHAR(19.12345,'09.99') FROM DUAL;   //19.12

 

***TO_CHAR, 날짜 -> 문자 ::날짜형을 문자로 다룰때

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

ex) SELECT TO_CHAR(HIREDATE,'YYYY/MM/DD DAY') HIREDATE FROM EMP;

ex) SELECT TO_CHAR(HIREDATE,'YYYY"년"MM"월"DD"일" DY') HIREDATE FROM EMP;

**한글로 'yyyy"년"mm"월"' 등 한글이 들어갈땐 ""로 감싸자. 총 ''로 감싸져있으니까

• YY: 년도 2자리만
  YYYY: 년도 4자리
  MM : 01~12형태의 월 
  D : 요일 반환(일요일은 1,월요일 2)
  DD :1~31형태의 일 표시
  DDD : 해당 년도의 1월 1일부터 현재까지의 일수
  
  HH :1~12시 형태로 표시
    =HH12 :1~12시 형태로 표시
  HH24 :0~23시
  MI :0~59분
  SS :0~59초
• AM/PM : 아무거나 써주면 오전/오후로 알아서 출력된다
• DAY : 요일출력됨  ex)목요일
• DY :  딱 요일만 출력됨  ex)목

***TO_DATE(' ') 날짜형식의문자를 -> DATE타입으로 

'2020-9-24'로 바로 집어넣으면 문자열이기때문에 인식을 하지못해서, DATE타입으로 해줘야한다

따라서 TO_CHAR(TO_DATE('원하는 날짜'), 원하는형식 'YYYY-DD'등) 이렇게 사용한다

단, 20/09/24, 20.09.24, 20-09-24, 20*09*24 다 날짜형식이 인정이 되는데

20년09월24일등 한글이 들어가면 날짜형식이 인정이 안된다.

 

 

 

 

 

날짜 관련 내장함수

• SYSDATE : 현재 날짜 가져오기
• +, -  : 날짜 더하기 빼기가 그대로 가능하다
• TO_DATE : 위에 적혀져있음
• MONTHS_BETWEEN (DATE타입,DATE타입) :개월 수 구하기
  ex)SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2012/01/01')) FROM DUAL;  //2.48.....
• ADD_MONTHS(DATE타입,숫자): 개월 수 더하기
  ex)SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;  //12-24

--예시1 만난날짜로부터 오늘이 몇일째인가?(정수형으로 떨구기위해 변환함)
SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'))-TO_DATE('2019-10-03') FROM DUAL;

 

 

 

 

숫자 관련 내장함수

• ROUND() : 반올림 
  ex) SELECT ROUND(3.145) FROM DUAL;    //3
• FLOOR(): 소수자리 버림
• CEIL(): 올림
  ex) SELECT CEIL(3.1) FROM DUAL;   //4
• MOD: 나머지
  ex) SELECT MOD(5,2) FROM DUAL;   //1
• POWER(2,3):지수곱 
  ex) 2의 3승 = 8
• SQRT(9):제곱근  //3
• DECODE() 함수 : 첫번째 매개변수의 값에 따라 결과를 표시하는 함수

 

***DECODE() 함수 ?

DECODE(표현식, 값1,결과값1,값2,결과값2, ... 값N,결과값N,기본값)

• 표현식이 값1일때 결과값1이 반환, 값2이면 결과값2 .... 아무것도 없으면 Dedault값이 반환
• -witch문과 같다고 생각하면 된다 (값이 딱 떨어질때만 쓰는것)
• 범위라면, CASE WHEN 을 사용한다

ACCEPT NUM PROMPT '숫자를 입력하세요';
DECLARE
    --NUM_ NUMBER := #
    STR NVARCHAR2(50);
    NOT_NUMBER EXCEPTION;
    PRAGMA EXCEPTION_INIT(NOT_NUMBER,-01722);
BEGIN
    SELECT DECODE(MOD('&NUM',3),0,'나머지가 0',1,'나머지가1','나머지가2') INTO STR FROM DUAL;
    DBMS_OUTPUT.PUT_LINE(STR);
    EXCEPTION
    WHEN NOT_NUMBER THEN
        DBMS_OUTPUT.PUT_LINE('숫자만 입력하세요');
END;
/

--또다른 예시
SELECT ENAME,JOB,DECODE(JOB,'CLERK','점원','SALESMAN','영업사원','기타')AS 직책 FROM EMP;

 

 

***CASE WHEN절

-범위로 사용할수도 있고, 물론 DECODE()처럼도 사용할수있다

-- CASE (만약 여기 무슨 식이 왔으면 DECODE식) WHEN 조건1 .....

CASE WHEN 조건1 THEN 결과값1
    WHEN 조건2 THEN 결과값2
    ELSE 그외값
 END AS 별칭  --기니까 별칭을 주자

 

예시 _CASE WHEN절 / 범위별 연봉등급구분하기

SELECT ENAME,SAL,
    CASE WHEN SAL>=3000 THEN '고액연봉자'
        WHEN SAL>=2000 THEN '중간연봉자'
        ELSE '소액연봉자'
    END 연봉레벨, JOB
FROM EMP
ORDER BY SAL;

 

 

연습문 ) 내장되어있는 테이블을 조건에 따라 정렬하기

SELECT LAST_NAME,RPAD(SUBSTR(EMAIL,1,1),LENGTH(EMAIL),'*')EMAIL,
    CASE WHEN SALARY >= 10000 THEN '고액연봉자'
        WHEN SALARY >= 5000 THEN '중간연봉자'
        ELSE '보통'
    END 등급,TO_CHAR(SALARY,'$999,999') SALARY,
        TO_CHAR(HIRE_DATE,'YYYY"년"MM"월"DD"일"')HIRE_DATE
FROM EMPLOYEES 
WHERE INSTR(LAST_NAME,'t') = LENGTH(LAST_NAME)
ORDER BY SALARY DESC;

 

---