메세지
ORA-12983: cannot drop all columns in a table 12983. 00000 - "cannot drop all columns in a table"
*Cause: An attempt was made to drop all columns in a table. *Action: Ensure that at least one column remains in the table after the drop column operation.
원인
테이블에 컬럼을 전부 지우려다 발생
테이블에 컬럼이 하나라도 남아있어야한다
ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_TITLE;메세지
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
원인
컬럼명의 순서를 바꿔서 들어갈 수 없는 데이터 형식이 넣어졌기에 에러발생
상황
테이블의 모든 컬럼에 값을 넣을 때는 컬럼명 명시를 생략 가능하여 해보던 중
해결 컬럼명을 명시하는 경우에는 명시한 순서를 따라 데이터를 넣으면 되고, 생략 시에는 테이블에 있는 컬럼명 순서을 따라야 한다
-- 테이블의 모든 컬럼에 값을 넣을 때는 컬럼명 명시를 생략 가능
-- INSERT 시, 컬럼명을 생략하려면 테이블에 있는 컬럼 순서를 정확히 지켜서 넣어야 함
INSERT INTO EMPLOYEE -- 컬럼명 명시부분 삭제
VALUES(900,'강건강','990311-1451201','kang_kk@kh.or.kr','01011112222',
'D1','J7','S3',4300000,0.2,200,SYSDATE,'N',NULL);
-- ORA-01841: (full) year must be between -4713 and +9999, and not be 0
-- 원인 : ENT_YN,ENT_DATE의 순서를 바꿔서 에러발생
-- 컬럼명을 명시하는 경우에는 명시한 순서를 따라 데이터를 넣으면 되고,
-- 생략 시에는 테이블에 있는 컬럼명 순서을 따라야 한다메세지
ORA-02290: check constraint (KH.SYS_C007063) violated
원인
'남'과 '여'만 들어가게끔 제약 걸어놨는데 '남자'를 넣었기에 에러 발생
상황
INSERT 시, 컬럼명을 생략하려면 테이블에 있는 컬럼 순서를 정확히 지켜서 넣어야 하는 걸 알기위해 에러발생시킴
참조코드
CREATE TABLE USER_CHECK2(
TEST_NUMBER NUMBER,
CONSTRAINT UC2_TN_CK CHECK(TEST_NUMBER >0 )
);
INSERT INTO USER_CHECK2 VALUES(1, 'user01', 'pass01', '강건강', '남', '010-1111-2222', 'kang@k.k');
INSERT INTO USER_CHECK2 VALUES(2, 'user02', 'pass02', '남나눔', '남자', '010-2222-3333', 'nam@n.n');
-- ERROR : ORA-02290: check constraint (KH.SYS_C007063) violated
-- 원인 : '남'과 '여'만 들어가게끔 제약 걸어놨는데 '남자'를 넣었기에 에러 발생
메세지
ORA-02264: name already used by an existing constraint 02264. 00000 - "name already used by an existing constraint"
*Cause: The specified constraint name has to be unique.
*Action: Specify a unique constraint name for the constraint.
원인
테이블명이 달라도 제약조건이 겹치면 에러 발생
상황
USER_GRADE2테이블을 USER_FOREIGNKEY2테이블에서 FOREIN KEY 제약조건을 걸려던 중 발생
해결
겹치는 제약 조건을 삭제
제약조건 삭제 ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
참고 코드
CREATE TABLE USER_GRADE2(
GRADE_CODE NUMBER PRIMARY KEY,
GRADE_NAME VARCHAR2(30) NOT NULL
);
INSERT INTO USER_GRADE2 VALUES(10, '일반회원');
INSERT INTO USER_GRADE2 VALUES(20, '우수회원');
INSERT INTO USER_GRADE2 VALUES(30, '특별회원');
CREATE TABLE USER_FOREIGNKEY2(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER,
CONSTRAINT UF_GC_FK FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE2(GRADE_CODE)
);/* ERROR 발생
ORA-02264: name already used by an existing constraint
02264. 00000 - "name already used by an existing constraint"
*Cause: The specified constraint name has to be unique.
*Action: Specify a unique constraint name for the constraint.
*/메세지
ORA-02292: integrity constraint (KH.UF_GC_FK) violated - child record found
원인
무결점 제약조건(integrity constraint)위반 : 자식테이블이 존재해서 지울 수 없다
GRADE_CODE = 10가 부모테이블이고 얘를 참조하고 있는 자식테이블 때문에 삭제 불가
상황
FOREIGN KEY로 자식 테이블이 부모테이블을 참조했고 커밋 후 DELETE로 테이블의 데이터를 하나 삭제할려고 하다 발생한 에러
참고 코드
CREATE TABLE USER_GRADE (
GRADE_CODE NUMBER PRIMARY KEY,
GRADE_NAME VARCHAR2(30) NOT NULL
);
INSERT INTO USER_GRADE VALUES(10, '일반회원');
INSERT INTO USER_GRADE VALUES(20, '우수회원');
INSERT INTO USER_GRADE VALUES(30, '특별회원');
CREATE TABLE USER_FOREIGNKEY (
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER,
CONSTRAINT UF_GC_FK FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE(GRADE_CODE)
-- USER_GRADE(GRADE_CODE)를 참조하고 있는 (GRADE_CODE). FOREIGN KEY를 써서 이부분을 컴에게 인지시킴
);
INSERT INTO USER_FOREIGNKEY VALUES(1, 'user01', 'pass01', '강건강','남','010-1111-2222','kang@k.k',10);
INSERT INTO USER_FOREIGNKEY VALUES(2, 'user02', 'pass02', '남나눔','남','010-1111-2222','nam@k.k',10);
INSERT INTO USER_FOREIGNKEY VALUES(3, 'user03', 'pass03', '도대담','남','010-1111-2222','do@k.k',30);
INSERT INTO USER_FOREIGNKEY VALUES(4, 'user04', 'pass04', '류라라','여','010-1111-2222','ryu@k.k',NULL);
-- 참조하는 테이블(=부모테이블)의 참조 컬럼 값 외에 null 값도 가능
INSERT INTO USER_FOREIGNKEY VALUES(5, 'user05', 'pass05', '문미미','여','010-1111-2222','moon@k.k',50);
-- ERROR : ORA-02291: integrity constraint (KH.UF_GC_FK) violated - parent key not found
-- 무결점 제약조건(integrity constraint)위반 : 부모테이블에 없는 값을 참조해서 에러발생
COMMIT; -- 데이터 확정
DELETE FROM USER_GRADE -- 데이터삭제
WHERE GRADE_CODE = 10;
-- ERROR : ORA-02292: integrity constraint (KH.UF_GC_FK) violated - child record found
-- 무결점 제약조건(integrity constraint)위반 : 자식테이블이 존재해서 지울 수 없다
IF ~ THEN ~ END IF
IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF
CASE~ WHEN~ THEN~ END(SWITCH문)
END LOOP
END LOOP
END LOOP
EXCEPTION
WHEN THEN
------------------------------------------------------------------------------
--------------------------------PL/SQL(절차형 SQL)-----------------------------
------------------------------------------------------------------------------
/*
PL/SQL ( Procedural Language extension to SQL) = 절차형 SQL
오라클 자체에 내장되어있는 절차적 언어
절차적 언어(Procedural Language)란?
반복문,if문 같은 것들이 절차적으로 사용되는 코드들임
절차적 논리, 계산 같은 것들이 sql에서 사용되는 것
변수의 정의, 조건처리, 반복처리(반복문) 등을 지원하여 PL/SQL을 통해서 SQL의 단점 보완
PL/SQL의 구조
1.선언부(DECLARE SECTION) : DECLARE
2.실행부(EXECUTABLE SECTION) : BEGIN
3.예외처리부(EXCEPTION SECTION) : EXCEPTION
1.선언부(DECLARE SECTION) : 변수, 상수 선언하는 부분
-> DECLARE로 시작
2.실행부(EXECUTABLE SECTION) : 제어문, 함수 정의 등 로직을 기술 하는 부분
-> BEGIN으로 시작
3.예외처리부(EXCEPTION SECTION) : 예외 발생 시, 해결할 수 있는 문장 기술 부분
-> EXCEPTION으로 시작
선언부,예외처리부 생략가능
- 필요에 따라 안 쓸 수도 있다는 의미
자바로 보면
System.out.println("안녕");
String hello = "안녕";
System.out.println(hello);
*/
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLOW WORLD');
END;
/
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLOW WORLD');
END;
/
-- / 포함해서 위 코드 옆에 주석 달면 에러 발생
-- SET SERVEROUTPUT ON; : 출력 결과에 뜨게끔 하는 코드
-- SP2-0265: serveroutput은 ON 또는 OFF로 설정되어야 합니다.
-- -> 주석이 위아래 옆 없어야한다. 시작부터 끝까지 안에 주석도 없어야해서 밖으로 빼줄 것
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.
-- 엔드로 끝나는게 맞는데 여기 문장이 끝이다라는 의미로 /까지 적어줘야함
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 선언부 작성
/*
-- 자바코드로 예시
-- 이 자바코드를 선언부로 작성해 볼 것임
-선언부
int empId;
String empName;
final int PI = 3.14; -- 선언 초기화 같이하면 선언부 쪽
-실행부
empId = 888; -- 값 초기화 부분도 실행부로 본다.
empName = "도대담";
System.out.println("empId : " + empId;
System.out.println("empName : " + empName);
System.out.println("PI : " + PI);
*/
-- 선언부
DECLARE -- 변수설정 // 변수 3개
EMP_ID NUMBER; -- 얘는 따로따로 직접 지정해서 쓴 것
EMP_NAME VARCHAR(30); -- 따로 쓰는게 아닌 참조해서 쓰는 방법도 있음
PI CONSTANT NUMBER := 3.14; -- 상수 넘버. 오라클에서의 상수는 CONSTANT
-- 오라클의 대입연산자 : :=
-- 실행부
BEGIN
EMP_ID := 888;
EMP_NAME := '도대담';
DBMS_OUTPUT.PUT_LINE('EMP_ID ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/
---RE
DECLARE
EMP_ID NUMBER;
EMP_NAME VARCHAR2(30);
BEGIN
EMP_ID = 999;
EMP_NAME = 'A';
DBMS_OUTPUT.PUT_LINE(EMP_ID);
END;
/
-- EMP_ID의 정보를 참조해 올 것임
-- 선언부
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
-- 실행부
BEGIN
SELECT EMP_ID, EMP_NAME
INTO EMP_ID, EMP_NAME
FROM EMPLOYEE
-- WHERE EMP_ID = 200;
WHERE EMP_ID = '&사원';
DBMS_OUTPUT.PUT_LINE('사번 : '||EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||EMP_NAME);
END;
/
-- EMP_ID의 정보를 참조해 올 것임
DECLARE
EI EMPLOYEE.EMP_ID%TYPE;
EN EMPLOYEE.EMP_NAME%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME
INTO EI, EN
FROM EMPLOYEE
WHERE EMP_ID = 200;
DBMS_OUTPUT.PUT_LINE(EI);
END;
/
/* 문제
레퍼런스 변수로 EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY를 선언하고
EMPLOYEE 테이블에서 사번, 이름, 직급코드, 부서코드, 급여를 조회하고
선언한 레퍼런스 변수에 담아 출력하시오
단, 입력받은 이름과 일치하는 조건의 직원을 조회하세요.
*/
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
DEPT_CODE EMPLOYEE.DEPT_CODE%TYPE;
JOB_CODE EMPLOYEE.JOB_CODE%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
INTO EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '&이름';
DBMS_OUTPUT.PUT_LINE('사번 : '||EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||EMP_NAME);
DBMS_OUTPUT.PUT_LINE('부서CODE : '||DEPT_CODE);
DBMS_OUTPUT.PUT_LINE('잡코드 : '||JOB_CODE);
DBMS_OUTPUT.PUT_LINE('연봉 : '||SALARY);
END;
/
/* 문제
레퍼런스 변수로 EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY를 선언하고
EMPLOYEE 테이블에서 사번, 이름, 직급코드, 부서코드, 급여를 조회하고
선언한 레퍼런스 변수에 담아 출력하시오
단, 입력받은 이름과 일치하는 조건의 직원을 조회하세요.
*/
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EN EMPLOYEE.EMP_NAME%TYPE;
DC EMPLOYEE.DEPT_CODE%TYPE;
JC EMPLOYEE.JOB_CODE%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
INTO EMP_ID, EN, DC, JC, SAL
FROM EMPLOYEE
WHERE EMP_NAME = '&사원명';
DBMS_OUTPUT.PUT_LINE('EMP_ID : '||EMP_ID);
DBMS_OUTPUT.PUT_LINE('EN : '||EN);
DBMS_OUTPUT.PUT_LINE('DC : '||DC);
DBMS_OUTPUT.PUT_LINE('JC : '||JC);
DBMS_OUTPUT.PUT_LINE('SAL : '||SAL);
END;
/
/* -- 출력 결과
EMP_ID 209
EMP_NAME 심봉선
DEPT_CODE D5
JOB_CODE J3
SALARY 3500000
*/
--변수를 많이 쓴다면?
-- 한 행 전체를 가지고 오는 레퍼런스
-- %ROWTYPE
DECLARE
E EMPLOYEE%ROWTYPE;
BEGIN
SELECT *
INTO E
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : '||E.EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : '||E.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('SALARY : '||E.SALARY);
END;
/
-- 한 행 전체 참조 : %ROWTYEP + INTO
DECLARE
E EMPLOYEE%ROWTYPE;
BEGIN
SELECT *
INTO E
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : ' ||E.EMP_ID);
END;
/
-- 선언부 생략 가능하니 각각 세미콜론(;)으로 마침을 해줘야한다
---RE
-- %ROWTYPE
DECLARE
EMP EMPLOYEE%ROWTYPE;
BEGIN
SELECT *
INTO EMP
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : ' ||EMP.EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : ' ||EMP.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('SALARY : ' ||EMP.SALARY);
END;
/
------------------------------------------------------------------------------
-----------------------------------조건문--------------------------------------
------------------------------------------------------------------------------
-- 조건문
-- IF ~ THEN ~ END IF;
-- IF ~ THEN ~ END IF= 자바 단일 IF문
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 급여, 보너스율 출력
-- 보너스를 받지 않는 사원은 보너스율 출력 전 '보너스를 지급받지 않는 사원입니다.' 출력
-- 일단 IF문 없이
--선언부
DECLARE -- 변수설정 // 변수
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BONUS EMPLOYEE.BONUS%TYPE;
--실행부
BEGIN
SELECT EMP_ID,EMP_NAME,SALARY, BONUS
INTO EMP_ID,EMP_NAME,SALARY, BONUS -- 별칭인지 여부 확인하자
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번:' || EMP_ID);
END;
/
-- 조건문
-- IF ~ THEN ~ END IF;
-- IF ~ THEN ~ END IF= 자바 단일 IF문
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 급여, 보너스율 출력
-- 보너스를 받지 않는 사원은 보너스율 출력 전 '보너스를 지급받지 않는 사원입니다.' 출력
-- NVL 적용 + BNS = 0
DECLARE
EI EMPLOYEE.EMP_ID%TYPE;
EN EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BNS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS,0)
INTO EI, EN, SAL, BNS
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
IF BNS = 0
THEN DBMS_OUTPUT.PUT_LINE('보너스X 사원');
END IF;
DBMS_OUTPUT.PUT_LINE('사번:' || EI);
DBMS_OUTPUT.PUT_LINE('이름:' || EN);
DBMS_OUTPUT.PUT_LINE('월급:' || SAL);
DBMS_OUTPUT.PUT_LINE('보너스:' || BNS*100||'%');
END;
/
-- BNS IS NULL
DECLARE
EI EMPLOYEE.EMP_ID%TYPE;
EN EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BNS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, SALARY, BONUS
INTO EI, EN, SAL, BNS
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
IF BNS IS NULL
THEN DBMS_OUTPUT.PUT_LINE('보너스 없는 사원');
END IF;
DBMS_OUTPUT.PUT_LINE('사번:' || EI);
DBMS_OUTPUT.PUT_LINE('이름:' || EN);
DBMS_OUTPUT.PUT_LINE('월급:' || SAL);
DBMS_OUTPUT.PUT_LINE('보너스:' || BNS*100||'%');
END;
/
------------------------------------------------------------------------------
-- IF ~ THEN ~ ELSE ~ END IF
-- IF ~ THEN ~ ELSE ~ END IF == IF ~ ELSE문
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 부서명, 소속 출력
-- TEAM 변수를 만들어 소속이 KO인 사원은 국내팀, 아닌 사원은 해외팀으로 저장
DECLARE
EL EMPLOYEE.EMP_ID%TYPE;
EN EMPLOYEE.EMP_NAME%TYPE;
DT DEPARTMENT.DEPT_TITLE%TYPE;
NC LOCATION.NATIONAL_CODE%TYPE;
TEAM VARCHAR2(10);
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EL, EN, DT, NC
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE EMP_ID = '&사번';
IF NC = 'KO'
THEN TEAM := '국내팀';
ELSE TEAM := '해외팀';
END IF;
DBMS_OUTPUT.PUT_LINE('사번 ' || EL);
DBMS_OUTPUT.PUT_LINE('성함 ' || EN);
DBMS_OUTPUT.PUT_LINE('부서명 ' || DT);
DBMS_OUTPUT.PUT_LINE('소속 ' || TEAM);
END;
/
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
DEPT_TITLE DEPARTMENT.DEPT_TITLE%TYPE;
NATIONAL_CODE LOCATION.NATIONAL_CODE%TYPE;
TEAM VARCHAR(10);
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE)
WHERE EMP_ID = '&사번';
IF national_code = 'KO'
THEN TEAM := '국내팀';
ELSE TEAM := '해외팀';
END IF;
DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('부서 : ' || DEPT_TITLE);
DBMS_OUTPUT.PUT_LINE('소속 : ' || TEAM);
END;
/
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 부서명, 소속 출력
-- TEAM 변수를 만들어 소속이 KO인 사원은 국내팀, 아닌 사원은 해외팀으로 저장
-- 변수명 바꿔서 다시한번
DECLARE
EI EMPLOYEE.EMP_ID%TYPE;
EN EMPLOYEE.EMP_NAME%TYPE;
DT DEPARTMENT.DEPT_TITLE%TYPE;
NC LOCATION.NATIONAL_CODE%TYPE;
TEAM VARCHAR2(10);
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EI, EN, DT, NC
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE EMP_ID = '&사번';
IF NC = 'KO'
THEN TEAM := '국내팀';
ELSE TEAM := '해외팀';
END IF;
DBMS_OUTPUT.PUT_LINE('사번 : ' || EI);
DBMS_OUTPUT.PUT_LINE('이름 : ' || EN);
DBMS_OUTPUT.PUT_LINE('부서 : ' || DT);
DBMS_OUTPUT.PUT_LINE('소속 : ' || NC);
END;
/
-- 사용자에게 사번을 받아와 그 사원의 전체 정보를 VEMP에 저장
-- VEMP를 이용하여 연봉 계산(보너스가 있는 사원은 보너스도 포함하여 계산)
-- 연봉 계산 결과 값은 YSALARY에 저장
-- 급여 이름 연봉(\1,000,000 형식)으로 출력
-- (IF없이 NVL문으로도 가능)
-- 사원 1명의 전체정보니까 한 행 전체 -> %ROWTYPE
DECLARE
VEMP EMPLOYEE%ROWTYPE;
YSALARY NUMBER;
BEGIN
SELECT *
INTO VEMP
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
IF VEMP.BONUS IS NOT NULL
THEN YSALARY := (VEMP.SALARY + VEMP.SALARY * VEMP.BONUS)*12;
ELSE YSALARY := VEMP.SALARY * 12;
END IF;
DBMS_OUTPUT.PUT_LINE(VEMP.SALARY||' '|| VEMP.EMP_NAME||' '||
TO_CHAR(YSALARY, 'FML999,999,999'));
END;
/
DECLARE
VEMP EMPLOYEE%ROWTYPE;
YSALARY NUMBER;
BEGIN
SELECT *
INTO VEMP
FROM EMPLOYEE
WHERE EMP_ID = '&사원번호';
IF VEMP.BONUS IS NOT NULL
THEN YSALARY := VEMP.SALARY * (1+VEMP.BONUS)*12;
ELSE YSALARY := VEMP.SALARY*12;
END IF;
DBMS_OUTPUT.PUT_LINE(VEMP.EMP_ID);
-- SELECT에서 *전체 불러오기 때문에 변수명.컬럼명으로 명시해서 불러와야한다
DBMS_OUTPUT.PUT_LINE(VEMP.SALARY||' '||VEMP.EMP_NAME||' '||YSALARY||
TO_CHAR(YSALARY,'FML999,999,999'));
END;
/
-- 사용자에게 사번을 받아와 그 사원의 전체 정보를 VEMP에 저장
-- VEMP를 이용하여 연봉 계산(보너스가 있는 사원은 보너스도 포함하여 계산)
-- 연봉 계산 결과 값은 YSALARY에 저장
-- 급여 이름 연봉(\1,000,000 형식)으로 출력
-- (IF없이 NVL문으로도 가능)
DECLARE
VEMP EMPLOYEE%ROWTYPE;
YSALARY NUMBER;
BEGIN
SELECT *
INTO VEMP
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
IF VEMP.BONUS IS NOT NULL
THEN YSALARY := VEMP.SALARY*(1+VEMP.BONUS)*12;
ELSE YSALARY := VEMP.SALARY*12;
END IF;
DBMS_OUTPUT.PUT_LINE(VEMP.SALARY||' '||vemp.EMP_NAME||' '||
TO_CHAR(YSALARY,'FML999,999,999'));
END;
/
------------------------------------------------------------------------------
-- IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF
-- IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF == IF~ ELSE IF~ ELSE문
-- 점수를 입력받아 SCORE 변수에 저장
-- 90점 이상이면 A, 80이상이면 B, 70이상은 C, 60점 이상은 D, 그 미만은 F 처리하여 GRADE 변수에 저장
-- 출력형식 : 당신의 점수는 N점이고, 학점은 M학점입니다.
DECLARE
SCORE NUMBER;
GRADE VARCHAR2(1);
BEGIN
SCORE := '&점수입력';
IF SCORE >= 90
THEN GRADE := 'A';
ELSIF SCORE >= 80
THEN GRADE := 'B';
ELSIF SCORE >= 70
THEN GRADE := 'C';
ELSE GRADE := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('당신의 점수는 '||SCORE||'점이고, 학점은 '||GRADE||'학점입니다.');
END;
/
DECLARE
SCORE NUMBER;
GRADE VARCHAR2(1);
BEGIN
SCORE := '&점수';
IF SCORE >= 90
THEN GRADE := 'A';
ELSIF SCORE >= 80
THEN GRADE := 'B';
ELSIF SCORE >= 70
THEN GRADE := 'C';
ELSIF SCORE >= 60
THEN GRADE := 'D';
ELSE GRADE := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('당신의 점수는 '||SCORE||'점이고, 학점은 '||GRADE||'점 입니다');
END;
/
-- 점수를 입력받아 SCORE 변수에 저장
-- 90점 이상이면 A, 80이상이면 B, 70이상은 C, 60점 이상은 D, 그 미만은 F 처리하여 GRADE 변수에 저장
-- 출력형식 : 당신의 점수는 N점이고, 학점은 M학점입니다.
DECLARE
SCORE NUMBER;
GRADE VARCHAR2(1);
BEGIN
SCORE := '&점수';
IF SCORE >= 90
THEN GRADE := 'A';
ELSIF SCORE >= 80
THEN GRADE := 'B';
ELSIF SCORE >= 70
THEN GRADE := 'C';
ELSIF SCORE >= 60
THEN GRADE := 'D';
ELSE GRADE := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('당신의 점수는 '||SCORE||'점이고, 학점은 '||GRADE||'학점 입니다.');
END;
/
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- CASE~ WHEN~ THEN~ END(SWITCH문)
-- 프로그램 재실행 후 프린트만 찍히고 다른 내용들이 안나온다면,
-- SET SERVEROUTPUT ON; 코드 찍어주고 하면 다 잘나올 것
-- IF문은 THEN마다 세미콜론( ; )이 들어가지만 CASE WHEN THEN END문은 안들어가고 마지막 END에만
-- 사원 번호를 입력하여 해당 사원의 사번,이름,부서명 출력
-- 선언부
DECLARE -- 변수설정 // 변수 2개
EMP EMPLOYEE%ROWTYPE; -- EMPLOYEE테이블의 EMP_ID의 데이터를 참조하겠다
DNAME DEPARTMENT.DEPT_TITLE%TYPE;
-- 실행부
BEGIN
SELECT * INTO EMP
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
-- 방법1
-- DNAME := CASE
-- WHEN EMP.DEPT_CODE = 'D1' THEN '인사관리부'
-- WHEN EMP.DEPT_CODE = 'D2' THEN '회계관리부'
-- WHEN EMP.DEPT_CODE = 'D3' THEN '마케팅부'
-- WHEN EMP.DEPT_CODE = 'D4' THEN '국내영업부'
-- WHEN EMP.DEPT_CODE = 'D5' THEN '해외영업1부'
-- WHEN EMP.DEPT_CODE = 'D6' THEN '해외영업2부'
-- WHEN EMP.DEPT_CODE = 'D7' THEN '해외영업3부'
-- WHEN EMP.DEPT_CODE = 'D8' THEN '기술지원부'
-- WHEN EMP.DEPT_CODE = 'D9' THEN '총무부'
-- ELSE '배정X'
-- END;
-- 방법2
DNAME := CASE EMP.DEPT_CODE
WHEN 'D1' THEN '인사관리부'
WHEN 'D2' THEN '회계관리부'
WHEN 'D3' THEN '마케팅부'
WHEN 'D4' THEN '국내영업부'
WHEN 'D5' THEN '해외영업1부'
WHEN 'D6' THEN '해외영업2부'
WHEN 'D7' THEN '해외영업3부'
WHEN 'D8' THEN '기술지원부'
WHEN 'D9' THEN '총무부'
ELSE '배정X'
END;
DBMS_OUTPUT.PUT_LINE(EMP.EMP_ID||' '||EMP.EMP_NAME||' ' ||DNAME);
END;
/
------------------------------------------------------------------------------
--------------------------------------반복문-----------------------------------
------------------------------------------------------------------------------
-- 반복문
-- BASIC LOOP
-- LOOP ~ END LOOP;
-- 반복할 내용을 작성하고 마지막에 반복을 벗어날 조건 명시
-- FOR문과 다르게 단순 반복만
-- 1~5까지 순차적 출력
DECLARE
N NUMBER := 1; -- 값 초기화
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N + 1; -- i+1처럼 값 증가
-- 방법1
-- IF N > 5 THEN EXIT; -- 반복문 종료 조건
-- END IF;
-- 방법2
EXIT WHEN N > 5; -- 반복문 종료 조건
END LOOP;
END;
/
-- 1~5까지 세로로 정상출력됨
-- 반복 종료 방법 2가지
/*
-- 방법1
IF N > 5 THEN EXIT; -- 반복문 종료 조건
END IF;
-- 방법2
EXIT WHEN N > 5;
*/
------------------------------------------------------------------------------
-- FOR LOOP
-- 특별한 목적이 있지 않는 이상 DECLARE 선언부가 필요X
-- 가데이터를 넣어서 미리 돌려보기 위해 FOR LOOP을 써서 가데이터를 많이 넣는데 사용한다
-- 1~5까지 순차적 출력
BEGIN
FOR N IN 1..5 -- 의미 1부터 5까지 // .. 2개 점 3개쓰며 에러발생
LOOP -- N은 자동적으로 NUMBER타입의 변수 설정됨
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;
/
-- 5~1까지 출력
BEGIN
FOR N2 IN REVERSE 5..1 -- 기본적으로 앞 숫자가 작아야 FOR문이 돌게된다.
LOOP -- REVERSE 붙일 것
DBMS_OUTPUT.PUT_LINE(N2);
END LOOP;
END;
/
-- REVERSE 추가 전 FOR N2 IN 5..1로만 출력하면,
-- PL/SQL 프로시저가 성공적으로 완료되었습니다. 뜨지만 아무것도 안뜬다
-- 현업 사용 예시
-- 가데이터 넣기
BEGIN
FOR N IN 1..30
LOOP
INSERT INTO TB1 VALUES(I); -- -- 가데이터 넣기
END LOOP;
END;
/
------------------------------------------------------------------------------
-- WHILE LOOP
-- 변수 값 초기화 시, 덮어쓰기 가능
-- ex)위에 코드에서 N 이미 사용했지만 아래서 다시 값 초기화해서 사용됨
-- 1~5까지 순차적 출력
DECLARE
N NUMBER := 1; -- 값 초기화
BEGIN
WHILE N <= 5
LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N + 1; -- N + 1의 LOOP 안 위치 중요. 기억!
END LOOP;
END;
/
-- 5~1까지 출력
DECLARE
N NUMBER := 5; -- 값 초기화
BEGIN
WHILE N >= 1
LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N - 1; -- N - 1의 LOOP 안 위치 중요. 기억!
END LOOP;
END;
/
-- 구구단 출력
-- 짝수 단 출력
-- 1)WHILE문
-- 2)FOR문 - WHILE문
-- 3)WHILE문 - FOR문
-- 1-9단 FOR문 출력
BEGIN
FOR N IN 1..9
LOOP
DBMS_OUTPUT.PUT_LINE('---'||N||'단---');
FOR M IN 1..9
LOOP
DBMS_OUTPUT.PUT_LINE(N||' x '||M||' = '||N*M );
END LOOP;
END LOOP;
END;
/
-- 짝수 단 출력
-- WHILE문
-- 에러남... 미완성
DECLARE
N NUMBER := 1; -- 값 초기화
BEGIN
WHILE N <= 9
LOOP
DBMS_OUTPUT.PUT_LINE('---'||N||'단---');
DECLARE
M NUMBER := 1; -- 값 초기화
BEGIN
WHILE M <= 9
LOOP
DBMS_OUTPUT.PUT_LINE(N||' x '||M||' = '||N*M );
M := M + 1;
END LOOP;
N := N + 1; -- N + 1의 LOOP 안 위치 중요. 기억!
END LOOP;
END;
/
COMMIT;
------------------------------------------------------------------------------
----------------------------예외 처리(EXCEPTION)--------------------------------
------------------------------------------------------------------------------
-- 예외 처리(EXCEPTION)
-- NO_DATA_FOUND : SELECT문이 데이터 행을 반환하지 못할 때
-- DUP_VAL_ON_INDEX : UNIQUE 제약조건이 들어간 컬럼에 중복 값이 들어갔을 때
-- DUPLICATE VALUE ON INDEX
-- ZERO_DIVIDE : 0으로 나눌 때
-- DUP_VAL_ON_INDEX
BEGIN
UPDATE EMPLOYEE
SET EMP_ID = '&사번' -- 201 인풋
WHERE EMP_ID = 200;
-- ERROR : ORA-00001: unique constraint (KH.EMPLOYEE_PK) violated
-- 예외처리 해보자
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('이미 존재하는 사번입니다.');
END;
/
-- NO_DATA_FOUND
DECLARE
NAME VARCHAR2(30);
BEGIN
SELECT EMP_NAME INTO NAME
FROM EMPLOYEE
WHERE EMP_ID = 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('조회 결과가 없습니다');
END;
/
------------------------------------------------------------------------------
| [1일1코딩][Java-SQL][JDBC] JDBC 연결 (0) | 2022.03.30 |
|---|---|
| [1일1코딩][Java-SQL][JDBC] JDBC 연결 메뉴1번 구현 (0) | 2022.03.29 |
| [1일1코딩] [Java] @Override toString() + setter/getter (0) | 2022.03.25 |
| [1일1코딩] [Java] FileWriter/FileReader 클래스 - write,append,close (0) | 2022.03.24 |
| [1일1코딩] [SQL][ORACLE] PL/SQL - %TYPE, %ROWTYPE, '&사용자입력받기', 변수명 설정, 조건문 (0) | 2022.03.23 |
참조 : %TYPE
참조 : %ROWTYPE,
'&사용자입력받기'
변수명 설정
IF ~ THEN ~ END IF = 자바 단일 IF문
IF ~ ELSE문
IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF
== IF~ ELSE IF~ ELSE문
------------------------------------------------------------------------------
--------------------------------PL/SQL(절차형 SQL)-----------------------------
------------------------------------------------------------------------------
/*
PL/SQL ( Procedural Language extension to SQL) = 절차형 SQL
오라클 자체에 내장되어있는 절차적 언어
절차적 언어(Procedural Language)란?
반복문,if문 같은 것들이 절차적으로 사용되는 코드들임
절차적 논리, 계산 같은 것들이 sql에서 사용되는 것
변수의 정의, 조건처리, 반복처리(반복문) 등을 지원하여 PL/SQL을 통해서 SQL의 단점 보완
PL/SQL의 구조
1.선언부(DECLARE SECTION) : DECLARE
2.실행부(EXECUTABLE SECTION) : BEGIN
3.예외처리부(EXCEPTION SECTION) : EXCEPTION
1.선언부(DECLARE SECTION) : 변수, 상수 선언하는 부분
-> DECLARE로 시작
2.실행부(EXECUTABLE SECTION) : 제어문, 함수 정의 등 로직을 기술 하는 부분
-> BEGIN으로 시작
3.예외처리부(EXCEPTION SECTION) : 예외 발생 시, 해결할 수 있는 문장 기술 부분
-> EXCEPTION으로 시작
선언부,예외처리부 생략가능
- 필요에 따라 안 쓸 수도 있다는 의미
자바로 보면
System.out.println("안녕");
String hello = "안녕";
System.out.println(hello);
*/
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLOW WORLD');
END;
/
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLOW WORLD');
END;
/
-- / 포함해서 위 코드 옆에 주석 달면 에러 발생
-- SET SERVEROUTPUT ON; : 출력 결과에 뜨게끔 하는 코드
-- SP2-0265: serveroutput은 ON 또는 OFF로 설정되어야 합니다.
-- -> 주석이 위아래 옆 없어야한다. 시작부터 끝까지 안에 주석도 없어야해서 밖으로 빼줄 것
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.
-- 엔드로 끝나는게 맞는데 여기 문장이 끝이다라는 의미로 /까지 적어줘야함
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- 선언부 작성
/*
-- 자바코드로 예시
-- 이 자바코드를 선언부로 작성해 볼 것임
-선언부
int empId;
String empName;
final int PI = 3.14; -- 선언 초기화 같이하면 선언부 쪽
-실행부
empId = 888; -- 값 초기화 부분도 실행부로 본다.
empName = "도대담";
System.out.println("empId : " + empId;
System.out.println("empName : " + empName);
System.out.println("PI : " + PI);
*/
-- 선언부
DECLARE -- 변수설정 // 변수 3개
EMP_ID NUMBER; -- 얘는 따로따로 직접 지정해서 쓴 것
EMP_NAME VARCHAR(30); -- 따로 쓰는게 아닌 참조해서 쓰는 방법도 있음
PI CONSTANT NUMBER := 3.14; -- 상수 넘버. 오라클에서의 상수는 CONSTANT
-- 오라클의 대입연산자 : :=
-- 실행부
BEGIN
EMP_ID := 888;
EMP_NAME := '도대담';
DBMS_OUTPUT.PUT_LINE('EMP_ID ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/
-- / 옆에 주석달면 안된다
-- 강의시간 14:00-14:20
-- EMP_ID의 정보를 참조해 올 것임
-- 선언부
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
-- 실행부
BEGIN
SELECT EMP_ID, EMP_NAME
INTO EMP_ID, EMP_NAME
FROM EMPLOYEE
-- WHERE EMP_ID = 200;
WHERE EMP_ID = '&사원';
DBMS_OUTPUT.PUT_LINE('사번 : '||EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||EMP_NAME);
END;
/
/* 문제
레퍼런스 변수로 EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY를 선언하고
EMPLOYEE 테이블에서 사번, 이름, 직급코드, 부서코드, 급여를 조회하고
선언한 레퍼런스 변수에 담아 출력하시오
단, 입력받은 이름과 일치하는 조건의 직원을 조회하세요.
*/
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
DEPT_CODE EMPLOYEE.DEPT_CODE%TYPE;
JOB_CODE EMPLOYEE.JOB_CODE%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
INTO EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '&이름';
DBMS_OUTPUT.PUT_LINE('사번 : '||EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : '||EMP_NAME);
DBMS_OUTPUT.PUT_LINE('부서CODE : '||DEPT_CODE);
DBMS_OUTPUT.PUT_LINE('잡코드 : '||JOB_CODE);
DBMS_OUTPUT.PUT_LINE('연봉 : '||SALARY);
END;
/
/* -- 출력 결과
EMP_ID 209
EMP_NAME 심봉선
DEPT_CODE D5
JOB_CODE J3
SALARY 3500000
*/
--변수를 많이 쓴다면?
-- 한 행 전체를 가지고 오는 레퍼런스
-- %ROWTYPE
DECLARE
E EMPLOYEE%ROWTYPE;
BEGIN
SELECT *
INTO E
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : '||E.EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : '||E.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('SALARY : '||E.SALARY);
END;
/
-- 한 행 전체 참조 : %ROWTYEP + INTO
DECLARE
E EMPLOYEE%ROWTYPE;
BEGIN
SELECT *
INTO E
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번 : ' ||E.EMP_ID);
END;
/
-- 선언부 생략 가능하니 각각 세미콜론(;)으로 마침을 해줘야한다
---RE
------------------------------------------------------------------------------
-----------------------------------조건문--------------------------------------
------------------------------------------------------------------------------
-- 조건문
-- IF ~ THEN ~ END IF;
-- IF ~ THEN ~ END IF= 자바 단일 IF문
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 급여, 보너스율 출력
-- 보너스를 받지 않는 사원은 보너스율 출력 전 '보너스를 지급받지 않는 사원입니다.' 출력
-- 일단 IF문 없이
--선언부
DECLARE -- 변수설정 // 변수
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BONUS EMPLOYEE.BONUS%TYPE;
--실행부
BEGIN
SELECT EMP_ID,EMP_NAME,SALARY, BONUS
INTO EMP_ID,EMP_NAME,SALARY, BONUS -- 별칭인지 여부 확인하자
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번:' || EMP_ID);
END;
/
-- 변수명 설정
DECLARE -- 변수설정 // 변수 4개
EI EMPLOYEE.EMP_ID%TYPE; -- EI
EN EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BNS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID,EMP_NAME,SALARY, BONUS
INTO EI,EN,SAL, BNS -- INTO에 들어갈 것들은 변수명임!!
FROM EMPLOYEE -- INTO에 EMP_ID를 받을 변수명 설정 : EI
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('사번:' || EI); -- EI
END;
/
-- IF문 추가
-- 변수명 설정
DECLARE -- 변수설정 // 변수 4개
EI EMPLOYEE.EMP_ID%TYPE; -- EI
EN EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BNS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID,EMP_NAME,SALARY, BONUS
INTO EI,EN,SAL, BNS -- INTO에 들어갈 것들은 변수명임!!
FROM EMPLOYEE -- INTO에 EMP_ID를 받을 변수명 설정 : EI
WHERE EMP_ID = '&사번';
IF BNS IS NULL
THEN DBMS_OUTPUT.PUT_LINE('보너스 지급X 사원' || BNS); -- EI
END IF;
DBMS_OUTPUT.PUT_LINE('사번:' || EI); -- EI
END;
/
-- NVL 추가 응용
DECLARE -- 변수설정 // 변수 4개
EI EMPLOYEE.EMP_ID%TYPE; -- EI
EN EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
BNS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID,EMP_NAME,SALARY, NVL(BONUS, 0) -- NVL
INTO EI,EN,SAL, BNS -- INTO에 들어갈 것들은 변수명임!!
FROM EMPLOYEE -- INTO에 EMP_ID를 받을 변수명 설정 : EI
WHERE EMP_ID = '&사번';
IF BNS = 0
THEN DBMS_OUTPUT.PUT_LINE('보너스 지급X 사원' || BNS); -- EI
END IF;
DBMS_OUTPUT.PUT_LINE('사번:' || EI); -- EI
DBMS_OUTPUT.PUT_LINE('EMP_NAME ' || EN);
DBMS_OUTPUT.PUT_LINE('SALARY ' || SAL);
DBMS_OUTPUT.PUT_LINE('BONUS ' || BNS);
END;
/
------------------------------------------------------------------------------
-- IF ~ THEN ~ ELSE ~ END IF
-- IF ~ THEN ~ ELSE ~ END IF == IF ~ ELSE문
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 부서명, 소속 출력
-- TEAM 변수를 만들어 소속이 KO인 사원은 국내팀, 아닌 사원은 해외팀으로 저장
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
DEPT_TITLE DEPARTMENT.DEPT_TITLE%TYPE;
NATIONAL_CODE LOCATION.NATIONAL_CODE%TYPE;
TEAM VARCHAR(10);
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE)
WHERE EMP_ID = '&사번';
IF national_code = 'KO'
THEN TEAM := '국내팀';
ELSE TEAM := '해외팀';
END IF;
DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('부서 : ' || DEPT_TITLE);
DBMS_OUTPUT.PUT_LINE('소속 : ' || TEAM);
END;
/
-- EMP_ID를 입력받아 해당 사원의 사번, 이름, 부서명, 소속 출력
-- TEAM 변수를 만들어 소속이 KO인 사원은 국내팀, 아닌 사원은 해외팀으로 저장
-- 변수명 바꿔서 다시한번
DECLARE
EI EMPLOYEE.EMP_ID%TYPE;
EN EMPLOYEE.EMP_NAME%TYPE;
DT DEPARTMENT.DEPT_TITLE%TYPE;
NC LOCATION.NATIONAL_CODE%TYPE;
TEAM VARCHAR2(10);
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EI, EN, DT, NC
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE EMP_ID = '&사번';
IF NC = 'KO'
THEN TEAM := '국내팀';
ELSE TEAM := '해외팀';
END IF;
DBMS_OUTPUT.PUT_LINE('사번 : ' || EI);
DBMS_OUTPUT.PUT_LINE('이름 : ' || EN);
DBMS_OUTPUT.PUT_LINE('부서 : ' || DT);
DBMS_OUTPUT.PUT_LINE('소속 : ' || NC);
END;
/
-- 사용자에게 사번을 받아와 그 사원의 전체 정보를 VEMP에 저장
-- VEMP를 이용하여 연봉 계산(보너스가 있는 사원은 보너스도 포함하여 계산)
-- 연봉 계산 결과 값은 YSALARY에 저장
-- 급여 이름 연봉(\1,000,000 형식)으로 출력
-- (IF없이 NVL문으로도 가능)
-- 사원 1명의 전체정보니까 한 행 전체 -> %ROWTYPE
DECLARE
VEMP EMPLOYEE%ROWTYPE;
YSALARY NUMBER;
BEGIN
SELECT *
INTO VEMP
FROM EMPLOYEE
WHERE EMP_ID = '&사원번호';
IF VEMP.BONUS IS NOT NULL
THEN YSALARY := VEMP.SALARY * (1+VEMP.BONUS)*12;
ELSE YSALARY := VEMP.SALARY*12;
END IF;
DBMS_OUTPUT.PUT_LINE(VEMP.EMP_ID);
-- SELECT에서 *전체 불러오기 때문에 변수명.컬럼명으로 명시해서 불러와야한다
DBMS_OUTPUT.PUT_LINE(VEMP.SALARY||' '||VEMP.EMP_NAME||' '||YSALARY||
TO_CHAR(YSALARY,'FML999,999,999'));
END;
/
-- 사용자에게 사번을 받아와 그 사원의 전체 정보를 VEMP에 저장
-- VEMP를 이용하여 연봉 계산(보너스가 있는 사원은 보너스도 포함하여 계산)
-- 연봉 계산 결과 값은 YSALARY에 저장
-- 급여 이름 연봉(\1,000,000 형식)으로 출력
-- (IF없이 NVL문으로도 가능)
DECLARE
VEMP EMPLOYEE%ROWTYPE;
YSALARY NUMBER;
BEGIN
SELECT *
INTO VEMP
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
IF VEMP.BONUS IS NOT NULL
THEN YSALARY := VEMP.SALARY*(1+VEMP.BONUS)*12;
ELSE YSALARY := VEMP.SALARY*12;
END IF;
DBMS_OUTPUT.PUT_LINE(VEMP.SALARY||' '||vemp.EMP_NAME||' '||
TO_CHAR(YSALARY,'FML999,999,999'));
END;
/
------------------------------------------------------------------------------
-- IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF
-- IF~ THEN~ ELSIF~ THEN~ ELSE~ END IF == IF~ ELSE IF~ ELSE문
-- 점수를 입력받아 SCORE 변수에 저장
-- 90점 이상이면 A, 80이상이면 B, 70이상은 C, 60점 이상은 D, 그 미만은 F 처리하여 GRADE 변수에 저장
-- 출력형식 : 당신의 점수는 N점이고, 학점은 M학점입니다.
DECLARE
SCORE NUMBER;
GRADE VARCHAR2(1);
BEGIN
SCORE := '&점수';
IF SCORE >= 90
THEN GRADE := 'A';
ELSIF SCORE >= 80
THEN GRADE := 'B';
ELSIF SCORE >= 70
THEN GRADE := 'C';
ELSIF SCORE >= 60
THEN GRADE := 'D';
ELSE GRADE := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('당신의 점수는 '||SCORE||'점이고, 학점은 '||GRADE||'점 입니다');
END;
/
-- 점수를 입력받아 SCORE 변수에 저장
-- 90점 이상이면 A, 80이상이면 B, 70이상은 C, 60점 이상은 D, 그 미만은 F 처리하여 GRADE 변수에 저장
-- 출력형식 : 당신의 점수는 N점이고, 학점은 M학점입니다.
DECLARE
SCORE NUMBER;
GRADE VARCHAR2(1);
BEGIN
SCORE := '&점수';
IF SCORE >= 90
THEN GRADE := 'A';
ELSIF SCORE >= 80
THEN GRADE := 'B';
ELSIF SCORE >= 70
THEN GRADE := 'C';
ELSIF SCORE >= 60
THEN GRADE := 'D';
ELSE GRADE := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('당신의 점수는 '||SCORE||'점이고, 학점은 '||GRADE||'학점 입니다.');
END;
/
------------------------------------------------------------------------------
------------------------------------------------------------------------------
| [1일1코딩] [Java] @Override toString() + setter/getter (0) | 2022.03.25 |
|---|---|
| [1일1코딩] [Java] FileWriter/FileReader 클래스 - write,append,close (0) | 2022.03.24 |
| [1일1코딩] [SQL][ORACLE] SYNONYM 동의어 (0) | 2022.03.22 |
| [1일1코딩] [SQL][ORACLE] CREATE , 데이터 타입, 제약조건 (0) | 2022.03.21 |
| [1일1코딩] [SQL][ORACLE] ALTER , DROP - 컬럼 추가,삭제,수정, 제약조건 추가 ,삭제 (0) | 2022.03.20 |
SYNONYM
동의어
객체
별칭
------------------------------------------------------------------------------
------------------------------------SYNONYM----------------------------------
------------------------------------------------------------------------------
/*
-- SYNONYM
SYNONYM = 동의어 = 데이터베이스 객체에 대한 '별칭'
별칭을 의미함
동의어는 데이터베이스 객체에 대한 별칭
다른 객체를 부르는 말
동의어는 객체만 설정 가능
가상테이블에 접근할 수 있는 코드로 DUAL을 사용하는 것
CREATE SYNONYM 별칭명 FOR 테이블명;
*/
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 비공개 동의어
CREATE SYNONYM EMP FOR EMPLOYEE;
-- EMPLOYEE 테이블의 별칭을 EMP로 지어놓겠다는 의미
-- ERROR : ORA-01031 : insufficient privileges
-- 유저나 뷰의 권한 생성 때처럼 시노님도 권한을 부여해야한다
--GRANT CREATE TO 계정명; -- VIEW 때 권한 부여 코드.
--SYSTEM계정으로 옮겨가서 아래 코드 작성.
GRANT CREATE SYNONYM TO KH;
-- Grant을(를) 성공했습니다.
CREATE SYNONYM EMP FOR EMPLOYEE;
CREATE SYNONYM EMP FOR EMPLOYEE;
------------------------------------------------------------------------------
-- 공개 동의어
CREATE PUBLIC SYNONYM DEPT FOR KH.DEPARTMENT; -- PUBLIC 추가
-- SYSTEM계정으로 바꾸고나서 실행
-- KH.테이블명 하면 타계정에도 접촉 가능
SELECT * FROM DEPT;
DROP SYNONYM EMP; -- KH계정에서 실행
DROP PUBLIC SYNONYM DEPT; -- 공개 동의어 삭제는 시스템 계정에서
------------------------------------------------------------------------------| [1일1코딩] [Java] FileWriter/FileReader 클래스 - write,append,close (0) | 2022.03.24 |
|---|---|
| [1일1코딩] [SQL][ORACLE] PL/SQL - %TYPE, %ROWTYPE, '&사용자입력받기', 변수명 설정, 조건문 (0) | 2022.03.23 |
| [1일1코딩] [SQL][ORACLE] CREATE , 데이터 타입, 제약조건 (0) | 2022.03.21 |
| [1일1코딩] [SQL][ORACLE] ALTER , DROP - 컬럼 추가,삭제,수정, 제약조건 추가 ,삭제 (0) | 2022.03.20 |
| [1일1코딩] [SQL][ORACLE] JOIN - INNER JOIN, OUTER JOIN & 오라클 전용구문, ANSI 표준구문 (0) | 2022.03.19 |
SYSDATE
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
EXTRACT
1)TO_CHAR()
2)TO_DATE()
3)TO_NUMBER()
NVL
NVL2
NULLIF
DECODE :
CASE WHEN THEN
SUM,AVG,MIN/MAX,COUNT
-- 3)날짜 관련 함수
-- SYSDATE
-- 시스템에 저장되어있는 시간을 사용
SELECT SYSDATE FROM DUAL; -- 22/03/14
-- MONTHS_BETWEEN
-- ex) MONTHS_BETWEEN(SYSDATE, HIRE_DATE)
-- 개월수의 차를 숫자로 리턴해주는 함수
SELECT MONTHS_BETWEEN(SYSDATE, HIRE_DATE) FROM EMPLOYEE; -- 385일 차이
SELECT SYSDATE,HIRE_DATE, MONTHS_BETWEEN(SYSDATE, HIRE_DATE) FROM EMPLOYEE;
SELECT SYSDATE,HIRE_DATE, ABS(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) FROM EMPLOYEE;
-- EMPLOYEE테이블에서 사원의 이름, 입사일, 근무 개월 수 조회
SELECT EMP_NAME, HIRE_DATE, MONTHS_BETWEEN(SYSDATE,HIRE_DATE) "근무 개월 수"
FROM EMPLOYEE;
SELECT EMP_NAME, HIRE_DATE, CEIL(ABS(MONTHS_BETWEEN(HIRE_DATE, SYSDATE))) || '개월차'
FROM EMPLOYEE; -- 368개월차
-- 앞뒤로 뭐가 올지 모른다면 ABS를 넣어서 절대값으로 받아오면 된다
SELECT EMP_NAME, HIRE_DATE, CEIL(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) || '일수'"근무일수" FROM EMPLOYEE;
-- ADD_MONTHS
-- 기준 날짜에다 지정한 숫자만큼의 개월수로 더한 날짜 리턴
SELECT ADD_MONTHS(SYSDATE,4) FROM EMPLOYEE; -- 22/07/19
-- 지금부터 4개월 뒤 반환
SELECT ADD_MONTHS(SYSDATE,12) FROM EMPLOYEE; -- 23/03/19
-- 개월수가 더해져서 연도가 넘어가면 연도도 올라감
--EMPLOYEE테이블에서 사원의 이름, 입사일, 입사 후 6개월이 된 날짜 조회
SELECT EMP_NAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE,6) FROM EMPLOYEE;
SELECT EMP_NAME, ADD_MONTHS('00/01/01',12) FROM EMPLOYEE; -- 01/01/01
-- 마이너스 -12개월
SELECT EMP_NAME, ADD_MONTHS('00/01/01',-12) FROM EMPLOYEE; -- 99/01/01
-- NEXT_DAY
-- 기준 날짜에서 구하려는 요일에 가장 가까운 날짜 리턴
-- 1=일, 2=월, 3=화,4=수, 5=목, 6=금, 7=토
-- 텍스트의 맨 앞글자만 따와서 요일 반환
SELECT SYSDATE, NEXT_DAY(SYSDATE, '일') FROM DUAL; -- 22/03/19 22/03/20
SELECT SYSDATE, NEXT_DAY(SYSDATE, '토') FROM DUAL; -- 22/03/19 22/03/26
-- 지금 기준으로부터 가장 가까운 목요일 구하기
SELECT NEXT_DAY(SYSDATE, '목') FROM DUAL; -- 22/03/24
SELECT NEXT_DAY(SYSDATE, '목요일') FROM DUAL; -- 22/03/24
SELECT NEXT_DAY(SYSDATE, 5) FROM DUAL; -- 22/03/24
-- 1=일, 2=월, 3=화,4=수, 5=목, 6=금, 7=토
SELECT NEXT_DAY(SYSDATE, 'THUR') FROM DUAL; -- ERROR : not a valid day of the week
SELECT SYSDATE, NEXT_DAY(SYSDATE, '목성주기') FROM DUAL; -- 22/03/19 22/03/24
-- 요일과 관련 없는 단어지만 맨 앞글자 '목'만 따서 요일 반환하는 걸 알 수 있다
SELECT SYSDATE, NEXT_DAY(SYSDATE, '금성') FROM DUAL; -- 22/03/19 22/03/25
-- 금성의 맨 앞글자 '금' = 금요일
-- 한글만 인지하게끔 설정이 되어있어 인식오류 뜨는 것
-- 영어로 세팅
ALTER SESSION SET NLS_LANGUAGE = AMERICAN; -- Session이(가) 변경되었습니다.
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURSDAY') FROM DUAL; -- 22/03/15 22/03/17
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THUR') FROM DUAL; -- 22/03/15 22/03/17
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURSTY') FROM DUAL; -- 22/03/15 22/03/17
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THUR--') FROM DUAL; -- 22/03/15 22/03/17
-- 한글과 동일하게 앞글자만 맞으면 요일 반환. 아마도 첫발음의 한글자를 치는 듯
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURDAY') FROM DUAL; -- 22/03/19 22/03/24
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THUR') FROM DUAL; -- 22/03/19 22/03/24
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THUR---') FROM DUAL;
-- 한글로 세팅
ALTER SESSION SET NLS_LANGUAGE = KOREAN; -- Session이(가) 변경되었습니다.
SELECT NEXT_DAY(SYSDATE, '금') FROM DUAL; -- 22/03/25
SELECT NEXT_DAY(SYSDATE, '금성') FROM DUAL;
-- LAST_DAY
-- 해당 날짜의 월의 마지막 일 반환
SELECT LAST_DAY(SYSDATE) FROM DUAL; -- 22/03/31
SELECT LAST_DAY(HIRE_DATE) FROM EMPLOYEE; -- 90/02/28 01/09/30
SELECT LAST_DAY(JOB_CODE) FROM EMPLOYEE; -- ERROR
-- ORA-01841: (full) year must be between -4713 and +9999, and not be 0
-- EXTRACT
-- 년, 월, 일 정보 추출 반환
-- 시간은 추출 불가
SELECT EXTRACT(SYSDATE-hire_date) FROM EMPLOYEE;
SELECT EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE),
EXTRACT(DAY FROM SYSDATE) FROM EMPLOYEE; -- 2022 3 19
SELECT EXTRACT(DAY FROM SYSDATE)
-- EXTRACT(HOUR FROM SYSDATE), -- 시간은 추출 불가
-- EXTRACT(MINUTE FROM SYSDATE)
FROM EMPLOYEE;
-- EMPLOYEE테이블에서 사원의 이름, 입사연도, 입사월, 입사일 조회
SELECT EMP_NAME, EXTRACT(YEAR FROM HIRE_DATE) 입사연도,
EXTRACT(MONTH FROM HIRE_DATE) 입사월,
EXTRACT(MONTH FROM HIRE_DATE) 입사일
FROM EMPLOYEE; -- 선동일 1990 2 2
-- EMPLOYEE테이블에서 사원의 이름, 입사일, 근무년수 조회
-- 단, 근무년수는 현재연도 - 입사연도로 조회
SELECT EMP_NAME,HIRE_DATE, SYSDATE,
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) 근무년수
FROM EMPLOYEE; -- 선동일 90/02/06 22/03/19 32
----------실습문제------------
--1.EMPLOYEE테이블에서 사원명, 입사일-오늘, 오늘-입사일 조회
-- 단, 별칭은 근무일수1, 근무일수2로 하고 모두 정수처리(내림)와 양수로 처리
--2.EMPLOYEE테이블에서 사번이 홀수인 직원들의 정보 모두 조회
--3.EMPLOYEE테이블에서 근무년수가 20년 이상인 직원 전체 정보 조회
--4.EMPLOYEE테이블에서 사원명, 입사일, 입사한 달의 근무일수 조회
--1.EMPLOYEE테이블에서 사원명, 입사일-오늘, 오늘-입사일 조회
-- 단, 별칭은 근무일수1, 근무일수2로 하고 모두 정수처리(내림)와 양수로 처리
-- 절삭이 아니라 내림이라 FLOOR. TRUNC는 절삭이고 FLOOR는 수학적 내림
SELECT EMP_NAME, FLOOR(ABS(HIRE_DATE - SYSDATE)) 근무일수1,
FLOOR(ABS(SYSDATE - HIRE_DATE)) 근무일수2
FROM EMPLOYEE;
SELECT EMP_NAME, ABS(FLOOR(HIRE_DATE - SYSDATE)) 근무일수1, FLOOR(SYSDATE - HIRE_DATE) 근무일수2
FROM EMPLOYEE;
SELECT EMP_NAME, FLOOR(ABS(HIRE_DATE - SYSDATE)) 근무일수1, FLOOR(ABS(SYSDATE - HIRE_DATE)) 근무일수2
FROM EMPLOYEE;
--2.EMPLOYEE테이블에서 사번이 홀수인 직원들의 정보 모두 조회
-- 오라클에서는 % 나머지 연산지 지원X
SELECT *
FROM EMPLOYEE
WHERE MOD(EMP_ID,2) = 1;
--WHERE MOD(EMP_ID,2) != 0;
--3.EMPLOYEE테이블에서 근무년수가 20년 이상인 직원 전체 정보 조회
-- 방법1
SELECT *
FROM EMPLOYEE
WHERE CEIL(ABS(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)))/12 >=20;
-- MONTHS_BETWEEN 반환값 확인방법
SELECT EMP_NAME, CEIL(ABS(MONTHS_BETWEEN(HIRE_DATE, SYSDATE)))
--SELECT EMP_NAME, ABS(MONTHS_BETWEEN(HIRE_DATE, SYSDATE))
FROM EMPLOYEE
WHERE CEIL(ABS(MONTHS_BETWEEN(SYSDATE ,HIRE_DATE))) >=20;
-- 방법2
SELECT *
FROM EMPLOYEE
WHERE ABS(MONTHS_BETWEEN(SYSDATE, HIRE_DATE))/12 >=20;
-- 방법3
SELECT * FROM EMPLOYEE
WHERE MONTHS_BETWEEN(SYSDATE, HIRE_DATE)>=240;
-- 방법4
SELECT * FROM EMPLOYEE
WHERE (SYSDATE - HIRE_DATE) >= 365*20;
-- 방법5
SELECT *
FROM EMPLOYEE
WHERE ADD_MONTHS(HIRE_DATE,240) <= SYSDATE;
--4.EMPLOYEE테이블에서 사원명, 입사일, 입사한 달의 근무일수 조회
SELECT EMP_NAME 사원명, HIRE_DATE 입사일,
EXTRACT(DAY FROM LAST_DAY(HIRE_DATE))- EXTRACT(DAY FROM HIRE_DATE) "입사한 달의 근무일수"
FROM EMPLOYEE;
SELECT EMP_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE) - HIRE_DATE
FROM EMPLOYEE;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 4)형변환 함수
-- A) TO_CHAR
-- B) TO_DATE
-- C) TO_NUMBER
-- A) TO_CHAR : 날짜/숫자형 데이터를 문자형 데이터로 변경
-- B) TO_DATE : 문자/숫자형 데이터를 날짜형 데이터로 변환
-- C) TO_NUMBER : 문자형 데이터를 숫자형 데이터로 변환
-- A) TO_CHAR
-- 날짜/숫자형 데이터를 문자형 데이터로 변경
-- 자릿수에 맞춰 문자로 형변환
-- TO_CHAR(형변환할 날짜or숫자OR컬럼명, 전체자릿수 0 OR 9)
-- 여분공간을 0은 0으로 9는 공백으로
-- 남은 공백은 왼쪽에 표시
SELECT 1234 LITERAL_NUMBER FROM DUAL; -- 1234
SELECT 12345 LITERAL_NUMBER FROM DUAL;
SELECT TO_CHAR(1234) FROM DUAL; -- 1234
SELECT TO_CHAR('1234') FROM DUAL; -- 문자to문자 형변환 가능
SELECT 1234 LITERAL_NUMBER , TO_CHAR(1234) FROM DUAL; -- 1234 1234
SELECT 1234 " 숫자 ", TO_CHAR(1234) " 문자 " FROM DUAL;
-- 숫자로 인지되었으면 오른쪽 정렬, 문자로 인지되어있으면 왼쪽 정렬
-- 글자가 어디에 들어가 있느냐에 따라서 문자인지 숫자인지 구분 가능
SELECT 369 + 963, TO_CHAR(369)+TO_CHAR(963) FROM DUAL; -- 1332
SELECT 1234+4321, TO_CHAR(1234)+TO_CHAR(4321) FROM DUAL; -- 5555 5555
-- 문자인데 연산이 가능한 이유 : 숫자로 형변환에 문제 없는 문자는 오라클에서 자동으로 연산해줌
-- 문자 연산 가능 // 자바에선 불가능
SELECT TO_CHAR(2222, '999999') FROM DUAL; -- 2222//
SELECT TO_CHAR(2222, '000000') FROM DUAL; -- 002222//
SELECT TO_CHAR(2222, '111111') FROM DUAL; -- ERROR:ORA-01481: invalid number format model
SELECT TO_CHAR(1234, '99999') FROM DUAL; -- (공백)1234
SELECT TO_CHAR(1234, '99999') A FROM DUAL; -- (공백)1234
-- '99999'의 의미는 5칸을 만들겠다. 여기에 1234를 넣고 빈 공간은 공백으로 하겠다는 의미
-- ' ' 안에는 9와 0만 가능. 다른 숫자는 에러
SELECT TO_CHAR(1234, '99') A FROM DUAL; -- ###
SELECT TO_CHAR(1234, '88') A FROM DUAL; -- ERROR
SELECT TO_CHAR(1234,'00000') A FROM DUAL; -- 01234
-- 비어있는 곳을 0으로 채워라
SELECT TO_CHAR(1234,'$99999') FROM DUAL; -- $1234
SELECT TO_CHAR(1234,'L99999') FROM DUAL; -- ￦1234
-- L을 붙인건 현재 설정된 나라의 원화표시를 붙인 것
SELECT TO_CHAR(1234,'FML99999') FROM DUAL; -- ￦1234
-- FM을 추가로 넣으면 공백 없애는 것
SELECT TO_CHAR(1234,'$99999') FROM DUAL; -- $1234
-- 달러를 찍고 싶으면 L대신 $기호
SELECT TO_CHAR(1234,'FM$99999') FROM DUAL; --$1234
SELECT TO_CHAR(1234,'99,999') FROM DUAL; -- 1,234
SELECT TO_CHAR(1234,'FM99,999') FROM DUAL; -- 1,234
SELECT TO_CHAR(1234,'00,000') FROM DUAL; -- 01,234
SELECT TO_CHAR(1234,'FM00,000') FROM DUAL; -- 01,234
SELECT TO_CHAR(1234,'999') FROM DUAL; -- ####
-- 자릿수가 부족해서 ####으로 대체 출력
-- 1234보다 더 적은 자리수를 지정할 경우는?
-- ##을 이용하면 됨
-- EMPLOYEE테이블에서 사원명, 급여(\(원화표시)9,000,000 형식) 조회
-- 모범 답안
SELECT EMP_NAME, TO_CHAR(SALARY, 'L999,999,999')
--SELECT EMP_NAME, TO_CHAR(SALARY, 'FML999,999,999')
FROM EMPLOYEE;
SELECT EMP_NAME,TO_CHAR(SALARY,'L9,999,999')
FROM EMPLOYEE; -- ￦8,000,000
SELECT EMP_NAME,TO_CHAR(SALARY,'L999,999,999')
FROM EMPLOYEE; -- ￦8,000,000
SELECT EMP_NAME,TO_CHAR(SALARY,'FML999,999,999')
FROM EMPLOYEE; -- ￦8,000,000
-- 시간
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; -- 03:40:27
SELECT TO_CHAR(SYSDATE, 'AM:HH:MI:SS') FROM DUAL; -- 오후:03:41:01
SELECT TO_CHAR(SYSDATE, 'PM:HH:MI:SS') FROM DUAL; -- 오후:03:41:43
SELECT TO_CHAR(SYSDATE, 'AM:HH24:MI:SS') FROM DUAL; -- 오후:15:42:38
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; -- 15:43:02
-- 날짜
SELECT TO_CHAR(SYSDATE, 'YY-MM-DD') FROM DUAL; --22-03-20
SELECT TO_CHAR(SYSDATE, 'YY-MM-DD DAY') FROM DUAL; -- 22-03-20 일요일
SELECT TO_CHAR(SYSDATE, 'YY-MM-DD DAY HH:MI:SS') FROM DUAL; -- 22-03-20 일요일 03:46:52
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY HH:MI:SS') FROM DUAL; -- 2022-03-20 일요일 03:48:07
SELECT TO_CHAR(SYSDATE, 'YYYY-FMMM-DD DAY HH:MI:SS') FROM DUAL; --2022-3-20 일요일 3:48:47 공백제거
SELECT TO_CHAR(SYSDATE, 'YYYY-FMMM-DD DAY AM HH:MI:SS') FROM DUAL; -- 2022-3-20 일요일 오후 3:50:0
SELECT TO_CHAR(SYSDATE, 'YYYY"년"-MM"월"-DD"일" DAY') FROM DUAL; -- 2022년-03월-20일 일요일 // 리터럴 추가
SELECT TO_CHAR(SYSDATE, 'YYYY"년"-MM"월"-DD"일" DAY AM HH:MI:SS') FROM DUAL; -- 2022년-03월-20일 일요일 오후 03:55:31
SELECT TO_CHAR(SYSDATE) FROM DUAL; -- 22/03/15
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; -- 08:07:45
SELECT TO_CHAR(SYSDATE, 'AM:HH:MI:SS') FROM DUAL; -- 오후:08:08:07
SELECT TO_CHAR(SYSDATE, 'PM:HH:MI:SS') FROM DUAL; -- 오후:08:08:07 // AM PM 아무거나 쓰면됨
SELECT TO_CHAR(SYSDATE, 'AM:HH24:MI:SS') FROM DUAL; -- 오후:20:10:01
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY AM HH:MI:SS') FROM DUAL; --2022-03-15 화요일 오후 08:10:50
SELECT TO_CHAR(SYSDATE, 'YYYY-FMMM-DD DAY AM HH:MI:SS') FROM DUAL;--2022-3-15 화요일 오후 8:10:59
-- FM을 적용시키면 뒤에까지 적용되서 공백제거 되서 나온다
-- 01초가 나와야하는데 1초가 나옴
SELECT TO_CHAR(SYSDATE, 'YYYY"년"-MM"월"-DD"일" DAY AM HH:MI:SS') FROM DUAL; -- 2022년-03월-15일 화요일 오전 11:40:48
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" DAY AM HH:MI:SS') FROM DUAL; -- 2022년 03월 15일 화요일 오후 08:15:13
-- 글자 출력
-- 글자처럼 비어있는 자리를 지워주는 역할
-- 보이는게 다른 3가지
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL; -- 2022
SELECT TO_CHAR(SYSDATE,'YY') FROM DUAL; -- 22
SELECT TO_CHAR(SYSDATE,'YEAR')FROM DUAL; -- TWENTY TWENTY-TWO
SELECT TO_CHAR(SYSDATE,'MM')FROM DUAL; -- 03
SELECT TO_CHAR(SYSDATE,'MONTH')FROM DUAL; -- 3월
SELECT TO_CHAR(SYSDATE,'MON')FROM DUAL; -- 3월
SELECT TO_CHAR(SYSDATE,'RM')FROM DUAL; -- III
SELECT TO_CHAR(SYSDATE,'MM'), TO_CHAR(SYSDATE,'MONTH'),
TO_CHAR(SYSDATE,'MON'), TO_CHAR(SYSDATE,'RM')
FROM DUAL; -- 03 3월 3월 III
SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL; -- 2022
SELECT TO_CHAR(SYSDATE, 'YY') FROM DUAL; -- 22
SELECT TO_CHAR(SYSDATE, 'MM') FROM DUAL; -- 03
SELECT TO_CHAR(SYSDATE, 'MONTH') FROM DUAL; -- 3월
SELECT TO_CHAR(SYSDATE, 'MON') FROM DUAL; -- 3월
SELECT TO_CHAR(SYSDATE, 'RM') FROM DUAL; -- III
SELECT TO_CHAR(SYSDATE, 'YY-MONTH-RM') FROM DUAL; -- 22-3월 -III
SELECT TO_CHAR(SYSDATE, 'YY MONTH RM') FROM DUAL; -- 22 3월 III
SELECT TO_CHAR(SYSDATE, 'DDD'), -- 한 해를 기준으로 몇일이 지나있는가
TO_CHAR(SYSDATE, 'DD'), -- 한 달을 기준으로 몇일이 지나있는가
TO_CHAR(SYSDATE, 'D') -- 한 주를 기준으로 몇일이 지나있는가(일요일 기준)
FROM DUAL; -- 079 20 1
SELECT TO_CHAR(SYSDATE, 'Q'), -- Q : 분기를 나타냄. 1-4분기
TO_CHAR(SYSDATE, 'DAY'), -- DAY : 요일
TO_CHAR(SYSDATE, 'DY') -- DY : 요일
FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'Q DAY DDD') FROM DUAL; -- 1 일요일 079
SELECT TO_CHAR(SYSDATE,'DDD'), -- 한달을 기준으로 몇일이 지나있는가
TO_CHAR(SYSDATE,'DD'), -- 주를 기준으로 몇일이 지나있는가
TO_CHAR(SYSDATE,'D') -- 한 해를 기준으로 몇일이 지나있는가
FROM DUAL; -- 074 15 3
SELECT TO_CHAR(SYSDATE,'Q'), TO_CHAR(SYSDATE,'DAY'), TO_CHAR(SYSDATE,'DY')
FROM DUAL; -- 1 일요일 일
-- Q : 분기를 나타냄. 1-4분기
-- DAY : 요일
-- DY : 요일
--EMPLOYEE테이블에서 이름, 입사일(2022년 03월 15일 (화) 형식) 조회
SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일" (DY)')
FROM EMPLOYEE; -- 선동일 1990년 02월 06일 (화)
FROM EMPLOYEE;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- B) TO_DATE
-- 문자/숫자형 데이터를 날짜형 데이터로 변환
--
-- Y : 두자리 연도에 무조건 현재 세기(21세기 , 20XX) 적용
-- R : 두자리 연도가 50이상일 때, 이전 세기(20세기,19XX) 적용
-- 두자리 연도가 50미만일 때, 현재 세기(21세기, 20XX) 적용
SELECT TO_DATE('20220315','YYYYMMDD') FROM DUAL; -- 22/03/15
SELECT TO_DATE(20220315,'YYYYMMDD') FROM DUAL; -- 22/03/15
SELECT TO_DATE('20220315','YYYYMMDD') FROM DUAL; -- 22/03/15
-- 입력 받은 문자열 : 20220713을 2022년 07월 13일 형식으로 출력하고 싶다면?
-- 20220713 -> 2022년 07월 13일
-- 문자열을 받는거니 TO_CHAR써서 하면 편하지 않을까? 해서 써보면 에러
-- TO_CHAR는 날짜/숫자형 데이터를 문자형 데이터로 변경하는 함수
-- 아래에서 TO_CHAR()의 첫 매개변수가 숫자가 ' '로 감싸진 문자라서 에러나는 것
-- 문자+문자 더하기 가능한건? EX)TO_CHAR(1234) + TO_CHAR(4321)
-- 문자가 숫자로 바뀌는 문자라면 문제없이 연산처리 가능하도록 오라클은 되어있기에 가능했던 것
-- 자바는 '1234'+'4321'이면 '12344321'이겠지만 오라클은 숫자형식인 문자면 자동형변환해서 연산해준다
SELECT TO_CHAR('20220315','YYYY"년" MM"월" DD"일"') -- ERROR : invalid number format model
FROM DUAL;
-- TO_CHAR()를 쓸려면 '20220315'의 형식 바꿔야함
-- TO_DATE()로 날짜 데이터를 만듬 : SELECT T0_DATE('20220315','YYYYMMDD')
SELECT TO_CHAR('20220315','YYYY"년" MM"월" DD"일"')
FROM DUAL; -- ERROR : invalid number format model
SELECT TO_CHAR(TO_DATE('20220315','YYYYMMDD'), 'YYYY"년" MM"월" DD"일"')
FROM DUAL; -- 2022년 03월 15일
SELECT TO_CHAR(TO_DATE('220713 175019','YYMMDD HH24MISS'), 'YY-MM-DD AM HH:MI:SS DY')
FROM DUAL; -- 22-07-13 오후 05:50:19 수
-- 데이트는 시간까지 보여주지 않는다. 타임스탬프 써야함
-- TO_DATE()에서의 연도 표시 : YY, RR
-- 연도표시를 YY말고도 RR도 가능. 차이는?
SELECT TO_DATE('980630', 'YYMMDD'), -- 98/06/30
TO_DATE('980630', 'RRMMDD'), -- 98/06/30
TO_DATE('140918', 'YYMMDD'), -- 14/09/18
TO_DATE('140918', 'RRMMDD') -- 14/09/18
FROM DUAL; -- 98/06/30 98/06/30 14/09/18 14/09/18
-- TO_DATE() 출력에서는 차이가 없어보인다. TO_CHAR()+TO_DATE()에서는?
SELECT TO_CHAR(TO_DATE('980630', 'YYMMDD'),'YYYYMMDD'), -- 20980630
TO_CHAR(TO_DATE('980630', 'RRMMDD'),'YYYYMMDD'), -- 19980630
TO_CHAR(TO_DATE('140918', 'YYMMDD'),'YYYYMMDD'), -- 20140918
TO_CHAR(TO_DATE('140918', 'RRMMDD'),'YYYYMMDD') -- 20140918
FROM DUAL; -- 20980630 19980630 20140918 20140918
-- 앞에있는 천의자리랑 백의자리가 생략되어있어서 그동안 몰랐던 것.
-- TO_CHAR()로 천의자리까지 만들어준 것
-- Y : 두자리 연도에 무조건 현재 세기(21세기 , 20XX) 적용
-- R : 두자리 연도가 50이상일 때, 이전 세기(20세기,19XX) 적용
-- 두자리 연도가 50미만일 때, 현재 세기(21세가, 20XX) 적용
-- 문자 -> 날짜
SELECT TO_DATE('20220320','YYYYMMDD') FROM DUAL; -- 22/03/20
-- 숫자 -> 날짜
SELECT TO_DATE(20220320, 'YYYYMMDD') FROM DUAL; -- 22/03/20
-- 데이트는 시간까지 보여주지 않는다. 타임스탬프 써야함
-- TO_DATE + TO_CHAR
-- 년,월,일,시간 같은 것들을 붙일려면 TO_CHAR로 감싼다
SELECT TO_CHAR(TO_DATE(20220320,'YYYYMMDD'), 'YYYY"년" MM"월" DD"일"')
FROM DUAL; -- 2022년 03월 20일
SELECT TO_DATE('220713 215005','YYMMDD HH24MISS')
FROM DUAL; -- 22/07/13 // 시간 출력 X
SELECT TO_CHAR(TO_DATE('220713 215005','YYMMDD HH24MISS'), 'YY/MM/DD AM HH:MI:SS DY')
FROM DUAL; -- 22/07/13 오후 09:50:05 수
SELECT TO_CHAR(TO_DATE('220713 175019','YYMMDD HH24MISS'), 'YY-MM-DD AM FMHH:MI:SS DY')
FROM DUAL; -- 22-07-13 오후 5:50:19 수
-- Y와 R의 차이
-- TO_DATE()로만으로는 차이가 없어보인다. TO_DATE()+TO_CHAR()는?
SELECT TO_DATE('000320','YYMMDD'), -- 00/03/20
TO_DATE('000320','RRMMDD'), -- 00/03/20
TO_DATE('450625','YYMMDD'), -- 45/06/25
TO_DATE('450625','RRMMDD') -- 45/06/25
FROM DUAL;
SELECT TO_CHAR(TO_DATE('900320','YYMMDD'),'YYYY/MM/DD'), -- 2090/03/20
TO_CHAR(TO_DATE('900320','RRMMDD'),'YYYY/MM/DD'), -- 1990/03/20
TO_CHAR(TO_DATE('450625','YYMMDD'),'YYYY/MM/DD'), -- 2045/06/25
TO_CHAR(TO_DATE('450625','RRMMDD'),'YYYY/MM/DD') -- 2045/06/25
FROM DUAL;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- C) TO_NUMBER
-- 문자형 데이터를 숫자형 데이터로 변환
-- TO_NUMBER(변환할 컬럼or문자,'들어온 데이터형식 인식시키는기준')
-- TO_NUMBER('10,000','999,999')
SELECT '1234' CHAR_NUMBER, TO_NUMBER('1234') FROM DUAL;
-- 문자는 왼쪽정렬, 숫자는 오른쪽 정렬
-- CHAR + CHAR 숫자 연산 가능한데 왜 굳이 NUMBER연산해야할까?
--
SELECT '1234'+'4321' FROM DUAL; -- 5555
SELECT '10,000' + '5,000' FROM DUAL; -- ERROR : invalid number
-- 쉼표 때문에 에러. 사람이 인식하기에 ,는 숫자 자릿수 구분이지만
-- 컴퓨터한테는 쉼표가 붙어 숫자로 인식할 수 없게 된 것
SELECT TO_NUMBER('10,000') FROM DUAL; -- ERROR : invalid number
SELECT TO_NUMBER('10,000','999,999') FROM DUAL; -- 10000
-- 두번째 인자 : 지금 들어온 문자 타입이 이런 형식이라는 것을 인식시키는 기준
SELECT TO_NUMBER('10,000','999,999'), TO_NUMBER('5,000','999,999') FROM DUAL;--10000 5000
SELECT TO_NUMBER('10,000','999,999') + TO_NUMBER('5,000','999,999') FROM DUAL;--15000
SELECT TO_NUMBER('10,000','000,000') -- ERROR: ORA-01722: invalid number
FROM DUAL; -- 공백을 0으로 채우면 이상해지니 에러나는듯
SELECT TO_NUMBER('10,000','999,999') -- 10000
FROM DUAL;
-- TO_NUMBER+TO_NUMBER의 산술연산
SELECT TO_NUMBER('5000','999,999') + TO_NUMBER('5000','999,999')
FROM DUAL; -- ORA-01722: invalid number
-- 에러원인 : 셋째자리에 ,받기로 했는데 ,이 없어서 에러
SELECT TO_NUMBER('5,000','999,999') + TO_NUMBER('5,000','999,999')
FROM DUAL; -- 10000
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 5)NULL처리 함수
-- NVL
-- null값을 지정한 값으로 대체하는 함수
--
-- 실제값을 바꾸는건 X
-- 대체할 값은 대체할려는 데이터의 타입을 따라가야한다
-- ex)bonus의 데이터타입이 number이므로 숫자만가능
-- NULL을 다른 값으로 인지시키게 못하나?로부터 나오게 됨
SELECT EMP_NAME, BONUS, NVL(BONUS, 0)
FROM EMPLOYEE;
SELECT NVL(BONUS,0) FROM EMPLOYEE;
SELECT NVL(BONUS,100) FROM EMPLOYEE;
SELECT NLV(BONUS, '보너스없음') FROM EMPLOYEE;
SELECT NVL(BONUS, '보너스X') -- ERROR : : invalid number
FROM EMPLOYEE; -- ex)bonus의 데이터타입이 number이므로 숫자만가능
-- NVL2
-- NVL2(컬럼명, NULL이면 이걸로 변경, NULL이 아니면 이걸로 변경)
-- NULL값이 존재한다면 두번째 인자값으로 변경, NULL값이 존재하지 않으면 세번째 인자값으로 변경
-- 해당데이터가 NULL이면 0.7로 아니면 0.5로 대체
SELECT NVL2(BONUS, 0, 100) FROM EMPLOYEE;
SELECT NVL2(BONUS, 50, 100,) FROM EMPLOYEE;
SELECT NVL2(BONUS,0.7,0.5) FROM EMPLOYEE;
-- NULLIF
-- 비교하는 값이 같으면 NULL, 다르면 앞에 있는 값 반환
-- (NULL) 123
SELECT NULLIF(123,123) FROM DUAL; -- 두 비교값이 같아 NULL반환
-- 데이터(컬럼명) 적용
SELECT EMP_NAME, NULLIF(EMPLOYEE.JOB_CODE, JOB.JOB_CODE) FROM EMPLOYEE, JOB;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 6.선택함수
-- DECODE
-- DECODE(계산식|컬럼명, 조건값1, 선택값1, 조건값2, 선택값2, ...)
-- 계산식에 따라 조건값1에 맞으면 선택값1 반환, 조건값2에 맞으면 선택값2를 반환하는 함수
-- 범위를 다루는 >,< 같은 것들은 DECODE로 쓸 수 없다
-- JAVA SWITCH 스위치도 딱딱 수가 떨어지는 것만 가능
-- 주민번호 옆에 성별 같이 찍히도록 해볼 것
SELECT EMP_ID, EMP_NAME, EMP_NO,
DECODE(SUBSTR(EMP_NO,8,1),1,'남',2,'여') 성별
FROM EMPLOYEE;
-- 직원의 급여를 인상하고자 한다
-- 직급코드가 J7인 직원은 급여의 10%를 인상하고
-- 직급코드가 J6인 직원은 급여의 15%를 인상하고
-- 직급코드가 J5인 직원은 급여의 20%를 인상하며
-- 그 외 직급의 직원은 급여의 5%만 인상한다
-- 직원 테이블에서 직원명, 직급코드, 급여, 인상급여(위 조건)을 조회
SELECT EMP_NAME, JOB_CODE, SALARY,
DECODE(JOB_CODE,'J7',SALARY*1.1,'J6',SALARY*1.15,'J5',SALARY*1.2,SALARY*1.05) 인상급여
FROM EMPLOYEE;
------------------------------------------------------------------------------
-- CASE ~ WHEN ~ THEN
-- CASE WHEN 조건식THEN 결과값
-- WHEN 조건식THEN 결과값
-- ELSE 결과값
-- END
-- 주민번호 + 성별 출력
SELECT EMP_NAME, EMP_NO,
DECODE(SUBSTR(EMP_NO,8,1),1,'남',2,'여')
FROM EMPLOYEE;
-- 직원의 급여를 인상하고자 한다
-- 직급코드가 J7인 직원은 급여의 10%를 인상하고
-- 직급코드가 J6인 직원은 급여의 15%를 인상하고
-- 직급코드가 J5인 직원은 급여의 20%를 인상하며
-- 그 외 직급의 직원은 급여의 5%만 인상한다
-- 직원 테이블에서 직원명, 직급코드, 급여, 인상급여(위 조건)을 조회
SELECT EMP_NAME, JOB_CODE, SALARY,
CASE WHEN JOB_CODE = 'J7' THEN SALARY*1.1
WHEN JOB_CODE = 'J6' THEN SALARY*1.15
WHEN JOB_CODE = 'J5' THEN SALARY*1.2
ELSE SALARY*1.05
END "인상급여"
FROM EMPLOYEE;
-- 위와 같은 코드
-- CASE 공통된컬럼명 WHEN 조건식 THEN 결과
SELECT EMP_NAME, JOB_CODE, SALARY,
CASE JOB_CODE WHEN 'J7' THEN SALARY*1.1
WHEN 'J6' THEN SALARY*1.15
WHEN 'J5' THEN SALARY*1.2
ELSE SALARY*1.05
END "인상급여"
FROM EMPLOYEE;
-- 사번, 사원명, 급여, 등급조회
-- 등급 조건 : 급여가 500만보다 크면 1등급, 350만보다 크면 2등급, 200만보다 크면 3등급, 나머지는 4등급
-- DECODE로도 가능?
-- 범위를 다루는 >,< 같은 것들은 DECODE로 쓸 수 없다
-- JAVA SWITCH 스위치도 딱딱 수가 떨어지는 것만 가능
SELECT EMP_ID, EMP_NAME, SALARY,
CASE WHEN SALARY> 5000000 THEN '1등급'
WHEN SALARY> 3500000 THEN '2등급'
WHEN SALARY> 2000000 THEN '3등급'
ELSE '4등급'
END "등급조회"
FROM EMPLOYEE; -- 200 선동일 8000000 1등급
-- ERROR : 축약에러
SELECT EMP_ID, EMP_NAME, SALARY,
CASE SALARY WHEN > 5000000 THEN '1등급' -- ERROR : 축약에러
WHEN > 3500000 THEN '2등급' -- ORA-00936: missing expression
WHEN > 2000000 THEN '3등급'
ELSE '4등급'
END "등급조회"
FROM EMPLOYEE;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
---------------------------------- 그룹 함수 ----------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 그룹 함수
-- SUM
-- 총합계
-- EMPLOYEE테이블에서 전 사원의 급여 총합
SELECT SUM(SALARY) FROM EMPLOYEE;
-- EMPLOYEE테이블에서 남자사원의 급여 총합
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) = 1;
-- AVG
-- 평균
-- EMPLOYEE테이블에서 전 사원의 급여 평균
-- 3047662.60869565217391304347826086956522
SELECT AVG(SALARY) FROM EMPLOYEE;
-- EMPLOYEE테이블에서 여자사원의 급여 총합
-- 2542030
SELECT AVG(SALARY)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) = 2;
-- EMPLOYEE테이블에서 전 사원의 보너스 평균
-- BONUS가 NULL인 사원은 0으로 처리
-- 0.0847826086956521739130434782608695652174
-- SUM(BONUS)/23
SELECT AVG(NVL(BONUS,0))
FROM EMPLOYEE;
-- 0.2166666666666666666666666666666666666667
-- NULL을 가진 값은 평균 계산에서 제외되어 계산
-- SUM(BONUS)/9
SELECT AVG(BONUS)
FROM EMPLOYEE; -- 0.2166666666666666666666666666666666666667
SELECT SUM(BONUS)/9
FROM EMPLOYEE; -- 0.2166666666666666666666666666666666666667
SELECT SUM(BONUS)/23
FROM EMPLOYEE; -- 0.0847826086956521739130434782608695652174
-- MIN / MAX
-- 최소 / 최대
-- 숫자, 날짜, 문자도 가능
-- EMPLOYEE테이블에서 최소 급여와 최대 급여
-- 8000000 1380000
SELECT MAX(SALARY), MIN(SALARY) FROM EMPLOYEE; -- 8000000 1380000
-- 문자 넣기
SELECT MAX(EMP_NAME), MIN(EMP_NAME) FROM EMPLOYEE;
-- 날짜 넣기
SELECT MAX(HIRE_DATE), MIN(HIRE_DATE) FROM EMPLOYEE;
-- COUNT
-- NULL값은 제외하고 카운트
-- 23
-- 직원 23명 카운트함
SELECT COUNT(*) FROM EMPLOYEE; -- 전체 자료수를 카운트하는 듯
-- 21
-- NULL 2명을 제외한 21 반환
SELECT COUNT(DEPT_CODE) FROM EMPLOYEE; -- 21
연습문제 풀이
-- EMPLOYEE 테이블의 사번,이름,급여 조회
SELECT EMP_ID, EMP_NAME, SALARY FROM EMPLOYEEL
--EMPLOYEE 테이블의 모든 정보 조회
SELECT * FROM EMPLOYEE;
/*
-- 실습 문제 --
1.JOB 테이블의 모든 정보 조회
2.JOB 테이블의 직급 이름 조회
3.DEPARTMENT 테이블의 모든 정보 조회
4.EMPLOYEE 테이블의 직원명, 이메일, 전화번호, 고용일 조회
5.EMPLYEE 테이블의 고용일, 사원이름, 월급 조회
*/
--1.JOB 테이블의 모든 정보 조회
-- 방법1
SELECT * FROM JOB;
-- 방법2
SELECT JOB_CODE,JOB_NAME FROM JOB;
--2.JOB 테이블의 직급 이름 조회
SELECT JOB_NAME FROM JOB;
--3.DEPARTMENT 테이블의 모든 정보 조회
-- 방법1
SELECT * FROM DEPARTMENT;
-- 방법2
SELECT DEPT_ID, DEPT_TITLE, LOCATION_ID FROM DEPARTMENT;
--4.EMPLOYEE 테이블의 직원명, 이메일, 전화번호, 고용일 조회
SELECT EMP_NAME,EMAIL,PHONE,HIRE_DATE FROM EMPLOYEE;
--5.EMPLYEE 테이블의 고용일, 사원이름, 월급 조회
SELECT HIRE_DATE, EMP_NAME,SALARY FROM EMPLOYEE;
--컬럼 값 산술 연산
--EMPLOYEE테이블에서 직원명, 연봉 조회(연봉 = 급여*12)
SELECT EMP_NAME, SALARY*12 연봉 FROM EMPLOYEE;
--EMPLOYEE테이블에서 직원명, 연봉, 보너스를 추가한 연봉 조회
-- 방법1
SELECT EMP_NAME, SALARY*12 연봉, SALARY*(1+BONUS) FROM EMPLOYEE;
-- 수식에 NULL이 있으면 결과값에 NULL이 나옴
-- 방법2
SELECT EMP_NAME 이름, SALARY * 12 연봉, SALARY*(1+BONUS)*12 AS 보너스
FROM EMPLOYEE;
/*
------------실습문제------------
1.EMPLOYEE테이블에서 이름, 연봉, 총수령액(보너스포함), 실수령액(총수령액-(연봉*세금3%)) 조회
2.EMPLOYEE테이블에서 이름, 고용일, 근무일수(오늘 날짜 - 고용일) 조회
- 오늘날짜 SYSDATE
*/
--(SALARY+SALARY*BONUS)*12
SELECT EMP_NAME 이름, SALARY * 12 연봉,SALARY *(1+BONUS)*12 "총수령액(보너스포함)",
SALARY *(1+BONUS)*12 - SALARY*12*0.03 AS 실수령액
FROM EMPLOYEE;
-- 컬럼 별칭
-- 컬럼명 AS 별칭 / 컬럼명 AS “별칭” / 컬럼명 별칭 / 컬럼명 “별칭”
/*
리터럴(literal)
값 자체 ‘ ‘
오라클에서는 문자,문자열,데이트 상관없이 다 싱글쿼테이션(’ ‘)으로 감싸준다
데이터 타입에 상관없이 별칭(””) 외에는 다 싱글쿼테이션으로 감싼다
*/
-- EMPLOYEE 테이블에서 직원의 직원 번호, 사원명, 급여, 단위 조회
-- 별칭 : ""
-- 리터럴을 싱글쿼테이션으로 처리한다 : ' '
-- '원'이라는 컬럼을 보여주고 싶지않으면 더블쿼테이션""으로 단위써주자
-- EMPLOYEE테이블에서 직원의 직급 코드 조회
-- bang으로 시작하는 email 다 조회
-- 결과 : 방명수 bang ns@kh.kr
SELECT EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE 'bang%';
-- 결과 : 없음
SELECT EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE 'Bang%';
-- 리터럴은 대소문자 철저히 구별하기에 대문자 BANG으로 시작하는 EMAIL이 없어서 안나온 것
-- EMPLOYEE 테이블에서 직원의 부서 코드를 중복 없이 조회
SELECT DISTINCT DEPT_CODE
FROM EMPLOYEE;
-- DISTINCT는 한번만 쓸 수 있다
--SELECT DISTINCT DEPT_CODE, DISTINCT DEPT_CODE -- error
--FROM EMPLOYEE;
/*
SELECT 컬럼명 -- 조회하고자 하는 컬럼명 기술
FROM 테이블명 -- 조회하고자 하는 컬림이 포함된 테이블명 기술
WHERE 조건식; -- 행을 선택하는 조건 기술, 조건을 만족하는 행만 반환
-- 조건식 복수로 붙여서 사용가능. 복수라도 WHERE절 한개만 기술
--비교연산자
>, <, >=, <=, = ,!=
크다, 작다, 크거나 같다
같다 : =
같지않다 : != , ^= , <>
*/
--EMPLOYEE테이블에서 부서코드가 'D9'인 직원의 이름, 부서코드 조회
SELECT EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';
-- '' 없이 D9만 쓰면 컬럼으로 인지하게 됨
-- 'd9'라고 써도 안됨. 리터럴은 대소문자 구분
--급여가 4000000이상인 직원의 이름, 급여 조회
-- ''로 감싸도 숫자로 인지잘한다. '' 없어도 됨
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY >= 4000000;
-- EMPLOYEE테이블에서 부서코드가 D9이 아닌 사원의 사번, 이름, 부서코드조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE != 'D9';
--WHERE DEPT_CODE ^= 'D9';
--WHERE DEPT_CODE <> 'D9';
-- EMPLOYEE 테이블에서 퇴사 여부가 N인 직원을 조회하고 근무 여부를 재직중으로 표시
-- 사번,이름, 고용일, 근무여부 조회
SELECT EMP_ID, EMP_NAME, HIRE_DATE, '근무 여부' 재직중
FROM EMPLOYEE
WHERE ENT_YN = 'N';
-------------- 실습문제 --------------
--1.EMPLOYEE테이블에서 월급이 3000000이상인 사원의 이름, 월급, 고용일 조회
--2.EMPLOYEE테이블에서 SAL_LEVEL이 S1인 사원의 이름, 월급, 고용일, 연락처 조회
--3.EMPLOYEE테이블에서 실수령액(총수령액-(연봉*세금3%))이 5천만원 이상인
-- 사원의 이름, 급여, 실수령액, 고용일 조회
SELECT EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE SALARY >= 3000000;
SELECT EMP_NAME, SALARY, HIRE_DATE, PHONE
FROM EMPLOYEE
WHERE SAL_LEVEL = 'S1';
SELECT EMP_NAME, SALARY, SALARY+SALARY*(SALARY*BONUS)-(SALARY*12*0.03)실수령액, HIRE_DATE
FROM EMPLOYEE
WHERE SALARY+SALARY*(SALARY*BONUS)-(SALARY*12*0.03) >= 5000000;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 논리 연산자 AND OR
-- EMPLOYEE 테이블에서 부서코드가 D6이고 급여를 3000000보다 많이 받는 직원의 이름, 부서코드, 급여 조회
SELECT EMP_NAME, DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6' AND SALARY > 3000000;
-- EMPLOYEE 테이블에서 부서코드가 D6이거나 급여를 3000000보다 많이 받는 직원의 이름,부서코드, 급여조회
SELECT EMP_NAME, DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6' OR SALARY > 3000000;
-- 1.EMPLOYEE 테이블에서 월급이 4000000이상이고 JOB_CODE가 J2인 사원의 전체 내용 조회
-- 2.EMPLOYEE 테이블에 DEPT_CODE가 D9이거나 D5인 사원 중에 고용일이 02년 1월 1일보다 빠른 사원의
-- 이름, 부서코드, 고용일 조회
SELECT *
FROM EMPLOYEE
WHERE SALARY >= 4000000 AND JOB_CODE ='J2';
SELECT EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9' OR HIRE_DATE < '02/01/01';
--- 다시풀이
SELECT *
FROM EMPLOYEE
WHERE SALARY >= 4000000 AND JOB_CODE = 'J2';
SELECT EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE = 'D9' OR DEPT_CODE = 'D5')
AND HIRE_DATE < '02/01/01' ;
-- 이 코드는 틀린 코드
-- 기준일보다 작은게 빠른 것. 오늘보다 내일이 더 큰 숫자
-- 날짜를 기준으로 조건식을 쓴다면 ''로 감싸줘야한다
-- 자바나 SQL이나 OR AND는 AND가 우선순위로 먼저 연산
-- OR를 먼저 연산하고 싶다면 소괄호를 쳐줄 것
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- BETWEEN AND
-- ~이상 ~이하
-- EMPLOYEE 테이블에서 급여를 3500000이상 6000000이하를 받는 사원의 사번, 이름, 급여, 부서코드, 직급코드 조회
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE SALARY >= 3500000 AND SALARY <= 6000000;
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE SALARY BETWEEN 3500000 AND 6000000;
---다시 풀이
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE SALARY BETWEEN 3500000 AND 6000000;
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE SALARY>=3500000 AND SALARY <=6000000;
-- 반대로 급여를 3500000미만 6000000초과를 받는 사원의 사번, 이름, 급여, 부서코드, 직급코드 조회
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
--WHERE SALARY < 3500000 OR SALARY > 6000000;
--WHERE SALARY NOT BETWEEN 3500000 AND 6000000;
WHERE NOT SALARY BETWEEN 3500000 AND 6000000;
-------실습문제-------
-- 1.EMPLOYEE 테이블에 고용일이 90/01/01 ~ 01/01/01인 사원의 전체 내용을 조회
-- 시간의축을 가로선상에 놓고 보면 시간과 연산자 사용계산이 쉬워짐
SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE >= '90/01/01' AND HIRE_DATE <= '01/01/01';
SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN '90/01/01' AND '01/01/01';
-- WHERE HIRE_DATE >= '90/01/01' AND HIRE_DATE <= '01/01/01';
---다시풀이
SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN '90/01/01' AND '01/01/01';
SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE >= '90/01/01' AND HIRE_DATE <='01/01/01';
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
/*
-- LIKE
비교하려는 값이 특정 패턴을 만족시키는지 조회
ex) 이멜에 i가 들어가는 것을 조회, 이름이 김씨가 들어간 사람들 조회
와일드카드 2종류
_ : 1글자
% : 0글자 이상
ex)
'_' (한글자)
'__' (두글자)
'___' (세글자)
'글자%' : 글자로 시작하는 값
글자 뒤로 뭐가와도 ok
ex) 글자최고, 글자(공백), 글자왕, 글자
'%글자' : 글자로 끝나는 값
글자 앞에 뭐가와도 ok
ex) 먹글자, 한글은 글자, (공백)글자
'글%자' : 글로 시작해서 자로 끝나는 값
글과 자 사이 뭐가와도 ok
ex)글씨를 잘쓰자, 글자, 글과자, 글(공백)자
'%글자%' : 글자가 포함되어있는 값
ex) 한글자값, 먹글자, 글자, 한두글자
*/
--EMPLOYEE 테이블에서 성이 전씨인 사원의 사번, 이름, 고용일 조회
SELECT EMP_ID, EMP_NAME, HIRE_DATE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '전%';
-- WHERE EMP_NAME LIKE '전__';
-- 도 가능하나 이름이 세글자인 사람들만 가능해서 이름이 두글자나 네글자이상이면 정확도가 떨어짐
--EMPLOYEE 테이블에서 이름이 '하'가 포함된 직원의 이름, 주민번호, 부서코드 조회
SELECT EMP_NAME, EMP_NO, DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%하%';
--EMPLOYEE 테이블에 전화번호 4번째 자리가 9로 시작하는 사원의 사번, 이름, 전화번호 조회
-- 전번 양식 : 01012345678
-- 01X 세자리 + 9로 시작하는 네번지째 자릿수
SELECT EMP_ID, EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE LIKE '___9%';
---RE
SELECT EMP_ID, EMP_NAME,PHONE
FROM EMPLOYEE
WHERE PHONE LIKE '___9%';
--EMPLOYEE 테이블에서 이메일 중 _의 앞 글자가 3자리인 이메일 주소를 가진 사원의 사번, 이름, 이메일 주소 조회
SELECT EMP_ID, EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '___ _%' ESCAPE ' ';
--RE
SELECT EMP_ID, EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '___!_%' ESCAPE '!';
/*
LIKE를 쓸 때 패턴!
와일드카드 : _ %
각각 한글자,와 0글자 이상을 나타내는 와일드 카드
세글자 자릿수 언더바 3개 + 검색하고자 하는 데이터로의 언더바 1개 = 총4개의 언더바
검색하고자 하는 패턴의 문자와 와일드 카드가 일치할 경우에는 패턴과 와일드 카드를 구분하지 못하기 때문에
ESCAPE OPTION을 통해 구분해준다
- 패턴(데이터)으로 쓸 것 앞에 구분기호 사용
*/
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- ESCAPE 식별자
-- EMPLOYEE테이블에서 김씨 성이 아닌 직원의 사번, 이름, 고용일 조회
SELECT EMP_ID, EMP_NAME, HIRE_DATE
FROM EMPLOYEE
--WHERE EMP_NAME NOT LIKE '김%';
WHERE NOT EMP_NAME LIKE '김%';
---RE
SELECT EMP_ID, EMP_NAME, HIRE_DATE
FROM EMPLOYEE
WHERE EMP_NAME NOT LIKE '김%';
--WHERE NOT EMP_NAME LIKE '김%';
-- NOT 순서 : 해당 컬럼명 앞뒤 둘 다 가능
/*
WHERE EMP_NAME != '김%';
= 혹은 != '김%'으로 검색하면 '김%' 데이터자체를 검색
LIKE '김%' 뒤에 어떤게 오든 상관없다
LIKE와 사용하는 % _ 는 와일드카드 적용받는다
LIKE는 문자에 대한 패턴 검색하는 것
!= '김%'에서는 이름이 김%인 사람을 찾는 것(리터럴 김% 데이터를 찾는 것)
여기서 %는 와일드카드로 적용이 되지 않고 그냥 순수 기호 %인 것
*/
-----------실습문제-----------
--1.EMPLOYEE 테이블에서 이름 끝이 '연'으로 끝나는 사원 이름 조회
--2.EMPLOYEE 테이블에서 전화번호 처음 세자리가 010이 아닌 사원의 이름, 전화번호 조회
--3.EMPLOYEE 테이블에서 메일주소 _의 앞이 4자이면서 DEPT_CODE가 D9 또는 D6이고
-- 고용일이 90/01/01 ~ 00/12/01이고, 급여가 2700000만 이상인 사원의 전체 정보 조회
SELECT EMP_NAME
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%연';
SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE NOT LIKE '010%';
SELECT *
FROM EMPLOYEE
WHERE EMAIL LIKE '____!_%' ESCAPE '!'
AND (DEPT_CODE = 'D9' OR DEPT_CODE = 'D6')
-- AND (HIRE_DATE >= '90/01/01' AND HIRE_DATE <= '00/12/01')
AND HIRE_DATE BETWEEN '90/01/01' AND '00/12/01'
AND SALARY >= 2700000;
---RE
SELECT EMP_NAME
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%연';
SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE NOT LIKE '010%';
SELECT *
FROM EMPLOYEE
WHERE EMAIL LIKE '____!_%' ESCAPE '!'
AND (DEPT_CODE = 'D9' OR DEPT_CODE = 'D6')
AND HIRE_DATE BETWEEN '90/01/01' AND '00/12/01'
AND SALARY >= 2700000;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- IS NULL
-- IS NOT NULL
-- 컬럼값이 NULL이냐
-- 컬럼값이 NULL이 아니냐
--EMPLOYEE 테이블에서 보너스를 받지 않는 사원의 사번, 이름, 급여, 보너스 조회
SELECT EMP_ID, EMP_NAME,SALARY,BONUS
FROM EMPLOYEE
WHERE BONUS IS NULL;
---RE
SELECT EMP_ID, EMP_NAME,SALARY,BONUS
FROM EMPLOYEE
WHERE BONUS IS NULL;
--WHERE BONUS = NULL; 값 안나옴
--WHERE BONUS = (null); 값 안나옴
--EMPLOYEE 테이블에서 보너스를 받는 사원의 사번, 이름, 급여, 보너스 조회
SELECT EMP_ID, EMP_NAME,SALARY,BONUS
FROM EMPLOYEE
WHERE BONUS IS NOT NULL;
WHERE NOT BONUS IS NULL;
---RE
SELECT EMP_ID, EMP_NAME,SALARY,BONUS
FROM EMPLOYEE
WHERE BONUS IS NOT NULL;
-- WHERE NOT BONUS IS NULL;
-- NOT은 컬렴명 앞에 붙어도 가능
--WHERE BONUS NOT LIKE '%(null)%';
-- EMPLOYEE 테이블에서 관리자도 없고 부서 배치도 받지 않은 직원의 이름, 관리자, 부서코드 조회
-- EMPLOYEE 테이블에서 부서 배치를 받지 않았지만 보너스를 지급받는 직원의 이름, 보너스, 부서코드 조회
SELECT EMP_NAME, MANAGER_ID, DEPT_CODE
FROM EMPLOYEE
WHERE MANAGER_ID IS NULL AND DEPT_CODE IS NULL;
SELECT EMP_NAME, BONUS, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE IS NULL AND BONUS IS NOT NULL;
---RE
SELECT EMP_NAME, MANAGER_ID, DEPT_CODE
FROM EMPLOYEE
WHERE MANAGER_ID IS NULL AND DEPT_CODE IS NULL;
SELECT EMP_NAME, BONUS, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE IS NULL AND BONUS IS NOT NULL;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
--IN
-- 목록에 일치하는 값이 있으면 TRUE가 되어 값 반환
-- 목록에 일치하는 값이 있으면 TRUE를 반환
-- D6부서와 D9부서원들의 이름, 부서코드, 급여 조회
-- 직급코드가 J1, J2, J3, J4인 사람들의 이름, 직급코드, 급여 조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D6' OR DEPT_CODE = 'D9';
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
--WHERE JOB_CODE = 'J1' OR JOB_CODE = 'J2' OR JOB_CODE = 'J3' OR JOB_CODE = 'J4';
WHERE JOB_CODE IN ('J1','J2','J3','J4');
---RE
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE JOB_CODE IN ('J1','J2','J3','J4');
-- 소괄호로 처음 들어가서 D6를 맞딱들이고 각 조건 체크해서 반환. 다음 D9체크
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 연결 연산자 ||
-- 자바에서의 or논리연산자의 기호가 SQL에서는 연결연산자
-- EMPLOYEE테이블에서 사번, 이름, 급여를 연결해서 조회 (EX. 200선동일8000000)
SELECT EMP_ID || EMP_NAME || SALARY 급여
FROM EMPLOYEE;
-- EMPLOYEE테이블에서 ' "사원명"의 월급은 "급여"원입니다' 형식으로 조회
SELECT EMP_NAME ||'명의 월급은' || SALARY ||'원 입니다'
FROM EMPLOYEE;
-- 문장으로 쓰고 싶을 경우 필요
---RE
SELECT EMP_NAME, EMAIL || ' 이메일 주소 '||'힘내'
FROM EMPLOYEE;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
--BETWEEN
-- 반대로 급여를 3500000미만 6000000초과를 받는 사원의 사번, 이름, 급여, 부서코드, 직급코드 조회
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_CODE, JOB_CODE
FROM EMPLOYEE
--WHERE SALARY BETWEEN 3500000 AND 6000000;
WHERE SALARY NOT BETWEEN 3500000 AND 6000000;
-- 1.EMPLOYEE 테이블에 고용일이 90/01/01 ~ 01/01/01인 사원의 전체 내용을 조회 (BETWEEN 사용)
SELECT *
FROM EMPLOYEE
--WHERE HIRE_DATE >= '90/01/01' AND HIRE_DATE <= '01/01/01';
WHERE HIRE_DATE BETWEEN '90/01/01' AND '01/01/01';
-- LIKE
--EMPLOYEE 테이블에서 이메일 중 _의 앞 글자가 3자리인 이메일 주소를 가진 사원의 사번의 사번, 이름, 이메일 주소 조회
SELECT EMP_ID, EMP_NAME, EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '___^_%' ESCAPE '^';
-- NULL
-- EMPLOYEE 테이블에서 부서 배치를 받지 않았지만 보너스를 지급받는 직원의 이름, 보너스, 부서코드 조회
SELECT EMP_NAME, BONUS, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE IS NULL AND BONUS IS NOT NULL;
-- IN
-- 직급코드가 J1, J2, J3, J4인 사람들의 이름, 직급코드, 급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
--WHERE JOB_CODE = 'J1' OR JOB_CODE = 'J2' OR JOB_CODE = 'J3' OR JOB_CODE = 'J4';
WHERE JOB_CODE IN ('J1','J2','J3','J4');
-----------실습문제-----------
--1.EMPLOYEE 테이블에서 이름 끝이 '연'으로 끝나는 사원 이름 조회
--2.EMPLOYEE 테이블에서 전화번호 처음 세자리가 010이 아닌 사원의 이름, 전화번호 조회
--3.EMPLOYEE 테이블에서 메일주소 _의 앞이 4자이면서 DEPT_CODE가 D9 또는 D6이고
-- 고용일이 90/01/01 ~ 00/12/01이고, 급여가 2700000만 이상인 사원의 전체 정보 조회
SELECT EMP_NAME
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%연';
SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE NOT LIKE '010%';
SELECT *
FROM EMPLOYEE
WHERE EMAIL LIKE '____!_%' ESCAPE '!'
AND (DEPT_CODE 'D9' OR DEPT_CODE 'D6')
AND HIRE_DATE '90/01/01' AND '00/12/01'
AND SALARY >= 2700000;
| [1일1코딩] [SQL][Oracle] 단일 행 함수 - 문자관련, 숫자관련 : INSTR, SUBSTR, REPLACE, LPAD,LTRIM (0) | 2022.03.15 |
|---|---|
| [1일1코딩] [Java] String 클래스 + 메소드 (0) | 2022.03.15 |
| [1일1코딩] [Java] 바이트 기반+보조 : FileOutputStream, BufferedOutputStream 클래스 (0) | 2022.03.14 |
| [1일1코딩] [Java] 배열 복사-깊은복사-System.arraycopy(), Arrays.copyOf() 복습2 (0) | 2022.03.13 |
| [1일1코딩] [SQL][Oracle] 오답 및 햇갈리는 문제들 재차 복습 (0) | 2022.03.13 |