함수(Function)
- 시스템에 컴파일되어 제공되는 실행가능한 모듈
- 문자열, 숫자, 날짜, 형변환, NULL처리, 집계함수(SUM,AVG,COUNT,MAX,MIN)가 제공
-- 사용자가 만들어쓸 수 있는 함수도 있음
-- 프로시저는 반환값이 없고 독립적으로 사용 불가능
-- 매개변수(argument/parameter): 함수를 위해 필요한 데이터/ 데이터가 이동하는 통로
1. 문자열 함수
1)CONCAT(c1,c2) --결합 시켜주는 함수(자바의 +와 같은 의미)/c1,c2는 매개변수
- 주어진 문자열 c1, c2를 결합하여 새로운 문자열 반환
- '||'연산자와 동일 기능
사용예) 회원테이블에서 회원명과 주민번호를 출력하시오
단 주민번호는 'XXXXXX-XXXXXXX'형식으로 출력하며 CONCAT함수를 사용
SELECT MEM_NAME AS 회원명,
CONCAT(CANCAT(MEM_REGNO1,'-'),MEM_REGNO2) AS 주민번호
--앞의 주민번호를 먼저 하고 하나의 함수 안에 다른 함수가 겹쳐질수 있음(집계함수 제외)
FROM MEMBER;** 테이블 변경: ALTTER
-테이블의 컬럼을 추가/삭제/변경
-테이블의 제약조건의 추가/변경/삭제
-테이블 이름 변경
1) 테이블 이름 변경
-테이블의 이름을 변경
-이름이 변경되어도 기본키와 외래키에는 영향이 없음
(사용형식)
ALTER TABLE old_table_name RENAME TO new_table_name;
사용예) HR계정의 EMPLOYEES 테이블을 EMP로, DEPARTMENTS를 DEPT로 변경하시오
ALTER TABLE EMPLOYEES RENAME TO EMP; --HR계정에서 변경해야 가능함
ALTER TABLE DEPARTMENTS RENAME TO DEPT;
2)테이블의 컬럼을 추가/삭제/변경
(1)컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN old_column_name TO new_column_name;
(2)컬럼속성 변경
ALTER TABLE 테이블명 MODIFY(컬럼명 데이터타입[(크기 [BYTE|CHAR])] [DEFAULT 값])
(3)컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
(4)컬럼 추가
ALTER TABLE 테이블명 ADD(컬럼명 데이터타입[(크기 [BYTE|CHAR])] [DEFAULT 값]);
사용예) 사원테이블(EMP)에서 EMP_NAME이라는 컬럼(VARCHAR2(80))을 추가하고 이 컬럼에 FIRST_NAME과 LAST_NAME컬럼의 값을 합쳐서
EMP_NAME컬럼에 값을 배정하시오. 단, 두 이름을 합칠때 중간에 공백을 삽입하시오.
또, 퇴직자 처리를 위해 RETIRE_DATE컬럼(DATE)을 추가하고 기본값은 NULL.
--컬럼추가
ALTER TABLE EMP ADD(EMP_NAME VARCHAR2(80));
ALTER TABLE EMP ADD(RETIRE_DATE DATE);
--EMP_NAME에 값을 배정
UPDATE EMP
SET EMP_NAME=CONCAT(FIRST_NAME,CONCAT(' ',LAST_NAME));
COMMIT;
2)LOWER(C), UPPER(C), INITCAP(C)--문자열이 되어지면 대소문자 구별 확실
- LOWER(C): 주어진 자료 C의 모든 문자를 소문자로 변환하여 반환
- UPPER(C): 주어진 자료 C의 모든 문자를 대문자로 변환하여 반환
- INITCAP(C): C에 포함된 단어의 첫 글자만 대문자로 변환하며 단어의 구분은 공백
--낙타식 표기방법(단어의 시작글자는 대문자)
사용예) 회원테이블에서 'D001'회원의 정보를 조회하시오
Alias는 회원명, 전화번호(핸드폰), 직업이다.
SELECT MEM_NAME AS 회원명,
MEM_HP AS "전화번호(핸드폰)",
MEM_JOB AS 직업
FROM MEMBER
WHERE UPPER(MEM_ID) = 'D001';
SELECT EMP_NAME,PHONE_NUMBER,LOWER(EMAIL)||'@' --이메일 뒤에 @붙혀주기
FROM EMP;
SELECT EMP_NAME,
LOWER(EMP_NAME),
INITCAP(LOWER(EMP_NAME)) -- 문자 시작만 대문자로 바꿔줌
FROM EMP;
3)LPAD(c1,n[,c2]), RPAD(c1,n[,c2])
- 주어진 문자열 c1을 n만큼 확보된 기억장소에 저장한 후 남는 기억장소에(왼쪽(오른쪽))에 c2를 채움
- c2가 생략되면 공백이 채워짐
- c1의 크기보다 작은 공간이 지정되면 오류
사용예) 상품테이블에서 분류코드 'P102'에 속한 상품정보를 조회하시오
Alias는 상품코드, 상품명, 매입단가, 판매단가이며 매입단가,
판매단가는 10자리에 출력하고 남는 왼쪽공간에는 '*'를 출력하시오
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
LPAD(PROD_COST,10,'*') AS 매입단가, --왼쪽에 *채워짐
RPAD(PROD_PRICE,10,'*') AS 판매단가 --오른쪽에 *채워짐
FROM PROD
WHERE UPPER(PROD_LGU)='P102'
사용예) 대전에 거주하는 회원을 조회하시오
Alias는 회원번호, 회원명, 주민번호, 주소이며, 주민등록번호는 '123456-2******'형태로 출력
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_REGNO1||'-'||RPAD(LPAD(MEM_REGNO2,1),7,'*') AS 주민번호,
MEM_ADD1||' '|| MEM_ADD2 AS 주소
FROM MEMBER
WHERE MEM_ADD1 LIKE '대전%'
4) LTRIM(c1[,c2]), RTRIM(c1[,c2])
--c1은 수정하고자 하는 문자열이 들어있음 C2와 C1이 같으면 삭제됨
( LTRIM는 왼쪽에 있는것이 일치해야 삭제 R은 오른쪽)
- 주어진 문자열 c1에서 우측(좌측)에서 c2를 찾아 제거함, c2가 생략되면 공백을 제거
- 단어 내부의 공백은 제거하지 못함(REPLACE 함수로 제거)
** 사원테이블의 이름(EMP_NAME)의 데이터 타입을 CHAR(80)으로 변경
ALTER TABLE EMP MODIFY(EMP_NAME CHAR(80));
** 사원테이블의 이름(EMP_NAME)의 데이터 타입을 VARCHAR2(80)으로 변경
ALTER TABLE EMP MODIFY(EMP_NAME VARCHAR2(80));
--CHAR를 VARCHAR2로 바꾸었을때는 남는 기억장소가 자동으로 시스템에 반납하는게 아님 그래서 UPDATE를 써야함(알맹이만 저장하는 기능)
UPDATE EMP
SET EMP_NAME=RTRIM(EMP_NAME);
COMMIT;
사용예)
SELECT LTRIM('나보기가 역겨워','보기'), -- 시작 글자가 다르면 안지워짐
LTRIM('나보기가 역겨워','나보기'), -- 시작 글자가 같으면 지워짐
LTRIM (' 나보기가 역겨워 ') --오른쪽만 띄워줌
FROM DUAL; --가상으로 제공해주는 테이블 SYS.DUAL이 원래 뜻
5)TRIM (c1)
- 문자열 c1에 존재하는 (무효의 공백:단어 시작 전,후) 오른쪽 및 왼쪽을 제거 --유효의 공백: 단어 내부에 들어있는 공백
6)SUBSTR(c,m[,n]) -- 가장 많이 쓰는 문자열 함수(문자열에서 문자열 추출)
- 주어진 문자열 c에 저장된 데이터에서 왼쪽부터 m번째 문자를 시작으로 n개의 문자를 추출하여 반환 --m은 시작 n은 갯수
- n이 생략되면 m번째부터 나머지 모두를 반환
- m이 0이면 1로 간주
- m이 음수이면 오른쪽부터 처리
사용예)
SELECT SUBSTR('DB Modling and Oracle SQL', 4,8), --4번째 글자부터 8글자 때기
SUBSTR('DB Modling and Oracle SQL', 4), --갯수 생략될 시 8번째 글자부터 나머지 다
SUBSTR('DB Modling and Oracle SQL', -14,8) --오른쪽부터 14번째부터 8글자
from dual;
사용예) 회원테이블에서 주민번호를 이용하여 나이를 구하고 나이에 따른 연령대(10대,30대,40대...등)를 출력하는 sql작성
Alias는 회원번호, 회원명, 주민번호, 나이, 연령대
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_REGNO1||'-'||MEM_REGNO2 AS 주민번호,
CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','2') THEN
2021 -TO_NUMBER ('19'||SUBSTR(MEM_REGNO1,1,2))
ELSE
2021-TO_NUMBER('20'||SUBSTR(MEM_REGNO1,1,2))
END AS 나이, --앞에서 2자리를 땐것에 19를 더해줌
CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','2') THEN
TRUNC(2021-TO_NUMBER('19'||SUBSTR(MEM_REGNO1,1,2)),-1)||'대'
ELSE
TRUNC(2021-TO_NUMBER('20'||SUBSTR(MEM_REGNO1,1,2)),-1)||'대'
END AS 연령대
FROM MEMBER
7)REPLACE(c1,c2[,c3])
- 문자나 문자열을 치환하기 위한 함수
- 주어진 문자열 cl에서 c2를 찾아 c3로 치환
- c3이 생략되면 c2를 제거
- 주로 문자열 내부의 공백을 제거할때 사용
**사용예) **상품테이블의 상품열 중 '대우'를 찾아 '대덕'으로 치환하시오
Alias는 상품번호, 상품명, 거래처코드, 적정재고량이다.
SELECT PROD_ID AS 상품번호,
PROD_NAME AS 상품명,
REPLACE(PROD_NAME,'대우','대덕') AS 상품명, --대우를 찾아서 대덕으로 치환
PROD_BUYER AS 거래처코드,
PROD_PROPERSTOCK AS 적정재고량
FROM PROD
WHERE PROD_NAME LIKE '%대우%'; --대우가 들어간 모든 글자(중간이나 끝도 가능)/ 두개 들어가도 반복으로 치환
-- SELECT 출력하는 열에 관하며 눈으로 확인 가능한것 FROM는 테이블을 메모리에 가지고 올라올 수 있게 함
-- 컬럼은 SELECT 절에서 별칭으로 사용되어짐
SELECT REPLACE('APPLE PERSIMON BANNA','N','y'), --APPLE PERSIMON BANNA에서 N을 찾아서 y로 바꿔라
REPLACE('APPLE PERSIMON BANNA','N'), --N을 찾아서 공백으로 바뀌는게 아님(N이 그냥 사라짐)
REPLACE('APPLE PERSIMON BANNA',' ') -- 공백을 찾아서 다 삭제
FROM DUAL;
8) INSTR(c1,c2[,m[,n]]) -- 특정한 위치 값을 알고 싶을때 사용
-주어진 문자열 c1에서 c2문자열이 처음 나온 위치값(index)을 반환
-m은 검색 시 시작위치를 1이외의 다른 값으로 지정할 때 사용
-n은 출현횟수를 지정하여 검색할때 사용 --m을 사용해야 함
사용예) SELECT INSTR('무궁화 꽃이 피었습니다. 무궁화 꽃은... ','화') AS COL1,
INSTR('무궁화 꽃이 피었습니다. 무궁화 꽃은... ','화',4) AS COL2,--시작위치 4번째 부터 화 찾기
INSTR('무궁화 꽃이 피었습니다. 무궁화 꽃은... ','화',1,2) AS COL3 -- 시작위치에서 두번째 화를 찾아라
FROM DUAL;
신입 개발자 입니다!!!