함수(Function)
- 시스템에 컴파일되어 제공되는 실행가능한 모듈
- 문자열, 숫자, 날짜, 형변환, NULL처리, 집계함수가 제공
1. 문자열 함수
1) CONCAT(C1,C2)
매개변수가 문자열 타입
- 주어진 문자열 C1, C3를 결합하여 새로운 문자열 반환
- '||' 연산자와 동일 기능
사용예) 회원 테이블에서 회원명과 주민번호를 출력하시오
단 주민번호는 'XXXXXX-XXXXXXX' 형식으로 출력하며 CONCAT 함수를 사용
SELECT MEM_NAME AS 회원명,
CONCAT(CONCAT(MEM_REGNO1,'-'), MEM_REGNO2)
AS 주민번호
FROM MEMBER ;
--|| 쓰고말지 복잡해서 잘안씀
테이블 변경: ALTER
- 테이블의 컬럼을 추가/삭제/변경
- 테이블의 제약조건의 추가/변경/삭제
- 테이블 이름 변경
1) 테이블 이름 변경
- 테이블의 이름을 변경
- 이름이 변경되어도 기본키와 외래키에는 영향이 없음
(사용형식)
ALTER TABLE old_table_name RENAME TO new_table_name;사용예) HR 계정의 EMPLOYEES 테이블을 EMP로, DEPARTMENTS를 DEPT로 변경하시오
ALTER TABLE EMPLOYEES RENAME TO EMP;
ALTER TABLE DEPARTMENTS RENAME TO DEPT; 2) 테이블의 컬럼을 추가/삭제/변경
(1)컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN old_column_name
To new_colunm_name;
(2)컬럼속성 변경
ALTER TABLE 테이블명 MODIFY(컬럼명 데이터타입[(크기[BYTE|CHAR]) [DEFAULT 값])
(3)컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
(4)컬럼 추가
ALTER TABLE 테이블명 ADD(컬럼명 데이터타입[(크기[BYTE|CHAR]) [DEFAULT 값]);
사용예)사원테이블(EMP)에서 EMP_NAME 이라는 컬럼(VARCAHR2(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, 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를 찾아 제거함, c2가 생략되면 공백을 제거
- 단어 내부의 공백은 제거하지 못함(REPLACE함수로 제거)
** 사원테이블의 이름(EMP_NAME)의 데이터 타입을 CHAR(80)으로 변경
ALTER TABLE EMP MODIFY(EMP_NAME CHAR(80));
ALTER TABLE EMP MODIFY(EMP_NAME VARCHAR2(80));
--CHAR을 VARCHR2로 변경했을때 공백이 축소되지않음 ㄱ-
UPDATE EMP
SET EMP_NAME=RTRIM(EMP_NAME); --공백제거
COMMIT;사용예)
SELECT LTRIM('나보기가 역겨워','보기'),
-- C1, C2 시작글자다르면 못지움
LTRIM('나보기가 역겨워','나보기'),
-- 같으면 지움
LTRIM(' 나보기가 역겨워 ')
FROM DUAL; -- 가상으로 테이블 역할을 해주는것 DUAL
-> 시스템관리자에서 제공해주는 테이블. 정확히는 SYS.DUAL임5) TRIM(c1)
문자열 c1에 존재하는 무효의 공백(오른쪽 및 왼쪽)을 제거
단어내부에 들어있는 공백: 유효 공백
6) SUBSTR(c,m[,n])
가장많이 사용되는 문자열 함수
- 주어진 문자열 c에 저장된 데이터에서 왼쪽부터 m번째 문자를 시작으로 n개의 문자를 추출하여 반환
- n이 생략되면 m번째부터 나머지 모두를 반환
- m이 0이면 1로 간주
- m이 음수이면 오른쪽부터 처리
사용예)
SELECT SUBSTR('DB Modeling and Oracle SQL', 4, 8),
SUBSTR('DB Modeling and Oracle SQL', 4),
SUBSTR('DB Modeling and Oracle SQL',-14, 8)
FROM DUAL;사용예) 회원테이블에서 주민번호를 이용하여 나이를 구하고 나이에 따른 연령대(10대, 30대, 40대...등)를 출력하는 sql작성 Alias는 회원번호, 회원명, 주민번호, 나이, 연령대
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_REGNO1||'-'||MEM_REGNO2 AS 주민번호,
-- MEM_REGNO2 첫자리가 1,2면1900년 3,4면 2000년
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 나이, --만나이 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])
- 문자나 문자열을 치환하기 위한 함수
- 주어진 문자열 c1에서 c2를 찿아 c3로 치환
- c3이 생략되면 c2를 제거
- (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 REPLACE('APPLE PERSIMON BANNA', 'N', 'y'),
-- 모든 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은 출현횟수를 지정하여 검색할때 사용
사용예)
SELECT INSTR('무궁화 꽃이 피었습니다. 무궁화 꽃은...','화') AS COL1,
INSTR('무궁화 꽃이 피었습니다.
무궁화 꽃은...','화',4) AS COL2,
INSTR('무궁화 꽃이 피었습니다.
무궁화 꽃은...','화',1,2) AS COL3
--첫번째값에서 찾기시작해서 2번째로 출현한 화의 위치
FROM DUAL;