sqldeveloper-3

함수(函數, 영어: function) 또는 사상(寫像, 영어: map, mapping)은 어떤 집합의 각 원소를 다른 어떤 집합의 유일한 원소에 대응시키는 이항 관계이다.

 

프로그램 함수(function) : 처리 코드 저장하는 용도

변수(Variable) : 데이터 값을 담는 용도

 

SQL (Structed Query Language) : 구조적인 질의어 <==> 프로그램x

 

1. 파라미터 (parameter) : 함수 정의할 때 함수가 전달받아 처리하는 어떠한 입력값

CREATE FUNCTION SYSDATE (birthday date) AS...

 

2. 인수(argument) : curr

SELECT NOW_DATE(curr)

FROM dual;

 

 

함수 객체를 만들면 저장되어 있음

 

오라클 사용 설명서 사이트

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/index.html

Database Reference

 

 

 

SYS <-- Db 운영

System  <--권한 관리

 

-- 함수(Function)

-- 함수 : 프로그래밍 언어에서의 일반적인 함수란, 실행 코드를 저장하는 단위 vs 변수 : 데이터를 저장하는 단위
-- 자주 사용하는 기능을 함수라는 객체 형태로 저장해두었다가 필요할 때 꺼내어 쓰는 (=호출) 용도
-- ex. System.out.println(숫자/문자열 데이터) --> 숫자/ 문자열 출력  + 줄바꿈(=개행)

-- 오라클 기본 데이터 타입 : 숫자, 문자(열), 날짜 + LOB(Large Object)-CLOB(character), BLOB(Binary)
-- 그밖에 LOB(Large Object)-CLOB(character), BLOB(Binary) 타입 등이 있다.

 

-- 1. 기본 함수 : 기본 데이터타입을 사용하는 함수 (=일반함수)
-- dual : 관리자가 생성한 테이블, 누구나 사용할 수 있게 등록 ==> 단순한 함수 실행을 위해서 이따끔씩 사용

SELECT 1 + 1
FROM dual;

-- 사용자 계정 소유의 테이블 조회 명령

SELECT *
FROM user_tables
WHERE table_name = 'DUAL';

 

-- (1) ABS() : 절대값을 반환하는 함수

SELECT ABS(-32) AS ABS1, ABS(32) AS ABS2
FROM dual;

-- (2) SIGN() : 음수인지 양수인지 판별하는 함수

SELECT SIGN(-32) AS SIGN1,
    SIGN(0) AS SIGN2,
    SIGN(32) AS SIGN3
FROM dual;

-- (3) ROUND(n, i) : 반올림 함수*
-- n : 값
-- i : (소숫점 이하) 자릿수 / 생략할 경우 기본값 0을 갖는다.
-- i가 음수일 경우, 정수부에서 반올림
-- i가 양수일 경우, 소숫점 이하 i+1 번째 자리에서 반올림

SELECT ROUND(3.141592) AS ROUND1,
    ROUND(3.141592, -1) AS ROUND2,
    ROUND(3.141592, 1) AS ROUND3
FROM dual;

-- (4) TRUNC() : 버림함수*
-- n : 값
-- i : (소숫점 이하) 자릿수 / 생략할 경우 기본값 0을 갖는다.
-- i가 음수일 경우, 정수부에서 반올림
-- i가 양수일 경우, 소숫점 이하 i+1 번째 자리에서 반올림

SELECT TRUNC(3.141592) AS TRUNC1,
    TRUNC(3.141592, -1) AS TRUNC2,
    TRUNC(3.141592, 1) AS TRUNC3
FROM dual;

-- Q. 사원테이블에서 수수료(급여x커미션율)을 구하시오

SELECT employee_id, first_name, commission_pct, salary AS 급여, ROUND(salary * commission_pct) AS 수수료
FROM employees
WHERE commission_pct IS NOT NULL;

-- (5) CEIL(n) : 같거나 큰 가장 작은 정수 

SELECT CEIL(325.2) AS CEIL1,
               FLOOR(325.2) AS FLOOR1
FROM dual;

-- (6) FLOOR(n) : 같거나 가장 작은 큰 정수

 

-- % : 나머지 연산자 vs MOD(m, n) : m을 n으로 나눈 나머지를 반환하는 함수

SELECT MOD(5,2) AS MOD1,
               MOD(4,2) AS MOD2
FROM dual;

 

-- Q. 사원중 100번 - 110번 부서에 근무하는 사원들의 급여를 15% 인상하여 지급하고자 할때,
-- 사원의 정보와 인상된 급여를 함께 조회하고자 한다.
-- 사번 | 이름 | 급여 | 15% 인상된 급여 | 부서번호 순으로 조회하시오 (단, 소숫점 이하 둘째자리까지 조회)

SELECT employee_id, first_name, salary, ROUND(salary + (salary * 0.15)) AS "15%인상된 급여1",
TRUNC(salary + (salary * 0.15)) AS "15%인상된 급여2", department_id
FROM employees
WHERE department_id BETWEEN 100 AND 110
ORDER BY department_id;

 

-- 2. 문자함수
-- 1) CONCAT(str1, str2) : 연결 연산자 ||와 같은 ..문자열 연결 함수
-- 오라클은 문자, 날짜 데이터를 ' (홑따옴표)로 감싸서 표기
SELECT CONCAT('Hello', ' Oracle') AS CONCAT1,
'Hello' ||' '|| 'Oracle' AS CONCAT2
FROM dual;

-- 2) INITCAP(char) : 문자열(영문) 첫 글자를 대문자로 변환하는 함수
SELECT INITCAP('hello world'), INITCAP('hello java hello oracle') AS INIT2
FROM dual;

 

-- 3) LOWER(char) / UPPER(char) : 문자열(영문)을 소문자로 변환하는 함수 / 대문자로 변환하는 함수
SELECT INITCAP('hello world'), INITCAP('hello java hello oracle') AS INIT2,
LOWER('HELLO WORLD') AS LOwer1, UPPER('hello world') AS UPPER1
FROM dual;

-- Q. 사원테이블의 사원정보 중 사번, 이름, 이메일, 업무 코드를 조회하시오 (단, 이메일과 업무코드는 소문자로 조회)
SELECT
    employee_id,
    first_name,
    initcap(lower(email))  AS email1,
    initcap(lower(job_id)) AS job_id
FROM
    employees;
-- 4)LPAD(char1, n, (,char2)) : 문자열 왼쪽에 길이 n에서 char1 만큼을 뺀 나머지를 char2로 채우는 함수
-- 4)RPAD(char1, n, (,char2)) : 문자열 오른쪽에 길이 n에서 char1 만큼을 뺀 나머지를 char2로 채우는 함수
-- char2 생략시 : 공백

SELECT 'abc' AS cha1,
LPAD('abc', 5) AS LPAD,
LPAD('abc', 5, '#') AS LPAD2
FROM dual;

SELECT 'abc' AS cha1,
RPAD('abc', 5) AS RPAD,
RPAD('abc', 5, '#') AS RPAD2
FROM dual;

SELECT *
FROM employees;

-- (5) LTRIM(char1 (,char2)) / RTRIM(char1 (,char2))
-- char2가 생략되면 기본값으로 '공백'을 제거함
-- 문자열의 왼쪽/ 오른쪽에서 문자열 제거

-- (6) TRIM : (LTRIM/ RTIM을 선택적으로 ) 함수
-- TRIM([LEADING | TRAILING | BOTH]), (,char2:제거할 문자) FROM char1) vs LPAD() / RPAD()
-- LEADING : 왼쪽(left)
-- TRAILING : 오른쪽(right)
-- BOTH  : 양쪽(left + right)
-- 이 때 제거할 문자(char2)는 1개만 가능
-- ORA-3001: 트림 설정은 하나 문자만 가지고 있어야 합니다

SELECT employee_id,
TO_CHAR(TRIM(LEADING 0 FROM hire_date)) AS TRIM_DATE
FROM employees
WHERE department_id = 60
ORDER BY employee_id;

 

-- (7) SUbSTR(char, position [,length])* : 문자열 char에서 위치 position부터 갯수[,length] 만큼 추출하는 함수
-- lenth를 생략하면 문자열 끝까지
-- position이 음수면, char의 오른쪽에서 시작해서 문자열 시작
SELECT SUBSTR('ABCDEFG', 3, 4) "Substring1",
SUBSTR('ABCDEFG', -5, 4) "Substring2",
SUBSTR('ABCDEFG', 3) "Substirng3"
FROM dual;

-- 주민등록번호 7번째 자리가 : 1,3 - 남성, 2,4 - 여성

SELECT '950802-234566' AS 주민등록번호1,
SUBSTR('950802-234566',8,1) 성별1
FROM dual;

-- Q. 사원 테이블의 사원정보중 이메일을 포함하여 50번 부서에 근무하는 사원정보를 조회하시오
-- 단, 이메일 주소는 id@domain.com 형태로 조회

SELECT employee_id,first_name, email || '@hanuledu.co.kr', department_id
FROM employees
WHERE department_id = 50;

SELECT 'admin@hanuledu.co.kr' AS email,
    SUBSTR('admin@hanuledu.co.kr', 1, 5) AS email_id,
    SUBSTR('admin@hanuledu.co.kr', 7 ) AS email_address
FROM dual;

-- (8) INSTR(char, search_char [,position] [,_th]) : 문자열에서 특정문자열의 위치를 찾아서 반환하는 함수
-- position, _th : 옵션
-- 만약 position,_th를 생략하면 기본값 1이 적용된다.
SELECT 'asdf@hanuledu.co.kr' AS email,
    INSTR('asdf@hanuledu.co.kr', '@') AS INSTR1
FROM dual;

 

-- (9) REPLACE(char, search_char, replace_char) : 문자열 char에서 찾을 문자(search_char)를 찾아
-- replace_char (변경할 문자)로 바꾸어 변환하는 수
-- 바꿀 문자(replace_char)를 생략하면 빈문자(null) ==> 제외한다
SELECT REPLACE('Jack and Jue', 'J', 'Bl') "change",
       REPLACE('Jack and Jue', 'J') "change2"
FROM dual;

 

도구 > 환경설정 > 데이터베이스 > LNS : National Language Support (국가별 언어 설정)

시스템 뷰를 통해 설정을 확인

 

--3. 날짜함수
--ROUND(date), TRUNC(date)
-- (1) ADD_MONTHS(date, n) : date에 n개월을 더한 날짜를 반환하는 함수
-- 만약 필요에 따라 설정을 변경하더라도 일회성 변경이므로, 다음 세션(로그인)에서는 다시 기본값으로 초기화 (영구설정x)
-- ALTER SESSION SET "_ORACLE_SCRIPT" = true; 처럼 날씨 꽌련 포멧, 설정을 변경한다면?
ALTER SESSION SET nls_date_format = 'RR-MM-DD';
-- 글자 크기를 최대로 설정한 경우 nls_date_format 항목이 공백처리된 것처럼 보여질 수 있음 (nls date format..)

-- 0) SYSDATE : 오늘 날짜를 알려주는 함수 ==> 다른 함수와 달리 ()이 없다. => 자주 사용한다!
-- (2) MONTHS_BETWEEN(date1, date2) : 두 날짜 사이의 개월 차이를 계산해서 반환하는 함수
-- 차이 계산은 date1 - date2 형태, 나중 날짜 - 이전 날짜

-- Q. 50번 부서에 근무하는 사원 중 사원의 입사일을 포함해서 사원정보를 조회하시오
SELECT
    employee_id,
    first_name,
    hire_date,
    round(months_between(sysdate, hire_date)),
    round(months_between(sysdate, hire_date)/12) as 근무년수,
    job_id,
    department_id
FROM
    employees
WHERE
    department_id = 50;

SELECT ADD_MONTHS(sysdate, -1) 
FROM dual;

-- 3) LAST_DAY(date) : date의 마지막 일자를 반환하는 함수
-- 월말 보고서 ==> 매월 말 (30/31..)에 맞춰서 보고서를 생성하는 기능
SELECT LAST_DAY(SYSDATE) AS LASTDAY1,
    LAST_DAY(ADD_MONTHS(SYSDATE,-1)) lastday2,
    LAST_DAY(ADD_MONTHS(SYSDATE,1)) lastday3
FROM dual;