Oracle 9강 - ALTER, UPDATE문, 문자열함수(ALTER,LOWER,LPAD,TRIM,SUBSTR)

5개의 함수를 제외하고는 함수중첩이 가능함
(예외 - 5가지의 집계함수 : 다른 함수에 포함될 수 없음, 중첩될 수 없음)

ROLLBACK이 불가능 - 다시 바꾸고 싶으면 다시 RENAME TO 를 써야함.
오라클 객체에서 내부 특성을 변경할 때

UPDATE 테이블명 SET 변경할 컬럼명

** 테이블 변경: 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_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에 포함된 단어의 첫 글자만 대문자로 변환하며 단어의 구분은 공백 --낙타식표기법(자바의 CLASS NAME과 같음)
  

사용예)회원테이블에서 '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를 찾아 제거 함, c2가 생략되면 공백을 제거
• 단어 내부의 공백은 제거하지 못함(REPLACE함수로 제거)
  

** 사원테이블의 이름(EMP_NAME)의 데이터 타입을 CHAR(80)으로 변경
ALTER TABLE EMP MODIFY(EMP_NAME CHAR(80));

** 사원테이블의 이름(EMP_NAME)의 데이터 타입을 CHAR(80)으로 변경 --CHAR -> VARCHAR2로 변경할 경우 데이터크기가 자동 조정되지 않음

ALTER TABLE EMP MODIFY(EMP_NAME VARCHAR2(80));

UPDATE EMP
   SET EMP_NAME=RTRIM(EMP_NAME);  --CHAR로 저장했을 때 남아있던 공백을 제거하고 업데이트

COMMIT;   

사용예)

SELECT LTRIM('나보기가 역겨워','보기'), 
             LTRIM('나보기가 역겨워','나보기'), 
             LTRIM('        나보기가 역겨워     ')
        FROM DUAL; --처리하는 데 필요한 TABLE이 없을 때 DUAL이 가상으로 TABLE 역할을 해줌.
    

5)TRIM(c1)

• 문자열 c1에 존재하는 무효의 공백(오른쪽 및 왼쪽)을 제거 -- 유효의 공백: 단어 내부의 공백

6)SUBSTR(c,m[,n])

• 주어진 문자열 c에 저장된 데이터에서 m번째(왼쪽부터 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) --뒤에서부터 14번째자리부터 시작
    FROM DUAL;   
    

사용예)회원테이블에서 주민번호를 이용하여 나이를 구하고 나이에 따른 연령대(10대,30대,40대...등)를 출력하는 SQL작성
Alias는 회원번호,회원명,주민번호,나이,연령대

SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_REGNO1||'-'||MEM_REGNO2 AS 주민번호,
         EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) AS 나이,
         SUBSTR(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR),1,1)||'0대' AS 연령대
    FROM MEMBER;   
    
 SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_REGNO1||'-'||MEM_REGNO2 AS 주민번호,
         CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','2') THEN 
         --CASE WHEN ~ THEN ... END: IF와 비슷함
                   2021-TO_NUMBER('19'||SUBSTR(MEM_REGNO1,1,2)) 
                   --TO_NUMBER : 문자열을 숫자로 바꿔주는 함수
              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)||'대' 
                  --버림TRUNC(숫자, 버림할 자릿수) , -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를 제거
• 주로 문자열 내부의 공백을 제거할 때 사용