Posted 2022-05-08Updated 2022-05-12 minkuen SQL / oracle8 minutes read (About 1264 words)

Oracle_practice4

조건식

sql developer

sql developer에서 새로운 sql 워크시트를 생성한다.

도구 → sql워크시트 :
오라클 SQL과 PL/SQL을 다루는 기술 92p

연산자

교재를 참고하여 코드를 익혀보자
연산자는 다음과 같이 사용한다.

-- 정리
-- 테이블 생성 : CREATE
-- 테이블 변경 (컬럼명, 데이터타입 등) : ALTER
-- 테이블 삭제 : DROP

-- 쿼리
-- SELECT (조회) FROM WHERE ORDER BY (...)
-- MERGE
-- 테이블 데이터 변경 : UPDATE SET
-- 테이블 특정 데이터 삭제 : DELETE

-- 연산자
DESC employees;

SELECT employee_id || '-' || emp_name AS employee_info
FROM employees;

Untitled

표현식

표현식이란 한 개 이상의 값과 연산자, SQL 함수 등이 결합된 식이다.
조건문은 다음과 같이 사용한다.
CASE WHEN 조건 THEN ELSE END

-- 표현식 (조건문)
-- CASE WHEN 조건 THEN ELSE END
SELECT 
    employee_id
    , salary
    , CASE  WHEN salary <= 5000 THEN 'C등급'
            WHEN salary > 5000 AND salary <= 15000 THEN 'B등급'
            ELSE 'A등급'
      END AS salary_grade
FROM employees;

Untitled

조건식

조간 혹은 조건식은 한 개 이상의 표현식과 논리 연산자가 결합된 식으로 TRUE, FALSE, UNKNOWN 세 가지 타입을 반환한다.
지금까지 SQL문을 학습하면서 WHERE절에서 사용했던 모든 조건이 바로 조건식에 포함된다.

비교 조건식

비교 조건식 : ANY, SOME, ALL 키워드로 비교하는 조건식

ANY

ANY - OR 조건식을 사용해본다.

-- p.114
-- 비교 조건식 : ANY, SOME, ALL 키워드로 비교하는 조건식
-- ANY - OR
SELECT
    employee_id
    , salary
FROM employees
WHERE salary = 2000 or salary = 3000 or salary = 4000

ANY가 ‘아무것’이나 ‘하나’라는 뜻이 있으므로 위 코드는 salary(급여)가 2000이나 3000이나 4000 중 하나라도 일치하는 모든 사원을 추출한 것이다.
하나라도 일치하면 추출하는 것이므로 ANY는 OR로 표현이 가능하다.

Untitled

ALL

ALL 조건문을 사용해본다.
ALL이므로 모든 조건을 만족하는 것만 추출한다.
ALL 조건식은 AND 조건으로 변환할 수 있다.

-- ALL
-- 모든 조건을 동시에 만족해야 함
SELECT
    employee_id
    , salary
FROM employees
WHERE salary = ALL(2000, 3000, 4000)
ORDER BY employee_id;

Untitled

SOME

SOME 조건문을 사용한다.
SOME은 ANY와 동일하게 사용되며 동작한다.

-- SOME 
SELECT
    employee_id
    , salary
FROM employees
WHERE salary = SOME(2000, 3000, 4000)
ORDER BY employee_id;

Untitled

논리 조건식

논리 조건식 = 조건절에서 AND, OR, NOT을 사용하는 조건식
AND는 모든 조건을 만족해야 하고 OR는 여러 조건 중 하나만 만족해도 TRUE를 반환된다.
NOT은 조건식 평가 결과가 **거짓(FALSE)**일 때 원하는 결과, 즉 TRUE를 반환한다.
NOT을 사용해본다.

-- 논리 조건식
SELECT 
    employee_id
    , salary
FROM employees
WHERE NOT (salary >= 2500)
ORDER BY employee_id;

Untitled

BETWEEN AND 조건식

BETWEEN은 범위에 해당되는 값을 찾을 때 사용하는데 크거나 같고 작거나 같은 값을 찾는다.
- 따라서 ‘>=’와 ‘<=’ 논리 연산자로 변환이 가능하다.

-- BETWEEN AND 조건식
-- 범위 지정
-- 급여가 2000~2500 사이에 해당하는 사원을 조회하라
SELECT
    employee_id
    , salary
FROM employees
WHERE salary BETWEEN 2000 AND 2500
ORDER BY employee_id;

Untitled

IN 조건식

OR, ANY와 같은 기능을 수행 가능하다.
IN 조건식은 조건절에 명시한 값이 포함된 건을 반환하는데 앞에서 배웠던 ANY와 비슷하다.

-- IN 조건식
-- OR - ANY 조건식과 비슷하다
SELECT
    employee_id
    , salary
FROM employees
WHERE salary IN (2000, 3000, 4000)
ORDER BY employee_id;

Untitled

NOT IN 조건식

IN 조건식과 반대의 결과를 출력한다.

-- NOT IN
SELECT
    employee_id
    , salary
FROM employees
WHERE salary NOT IN (2000, 3000, 4000)
ORDER BY employee_id;

Untitled

EXISTS 조건식

XISTS 조건식 역시 IN과 비슷하지만 후행 조건절로 값의 리스트가 아닌 서브 쿼리만 올 수 있다.
또한 서브 쿼리 내에서 조인 조건(a.department_id = b.department_id)이 있어야 한다.

-- EXISTS 조건식
-- IN과 비슷함, 단 서브쿼리 절에만 사용 가능
SELECT department_id
       , department_name
FROM departments a
WHERE EXISTS (  SELECT *
                FROM employees b
                WHERE a.department_id = b.department_id
                AND b.salary > 3000)
ORDER BY a.department_name;

서브쿼리 부분이 메인이다.

Untitled

LIKE 조건식

LIKE 조건식은 문자열의 패턴을 검색할 때 사용하는 조건식이다.
예를 들어, 사원 테이블에서 사원이름이 ‘A’로 시작되는 사원을 조회하는 쿼리를 작성한다면 다음과 같이 LIKE 조건식을 사용.

-- LIKE 조건식
-- 문자열의 패턴을 검색할 때 사용하는 조건식
-- 사원 테이블 사원 이름이 'A'로 시작되는 사원 조회
SELECT emp_name
FROM employees
WHERE emp_name LIKE 'A%'
ORDER BY emp_name;

Untitled

Reference : 오라클 SQL과 PL/SQL을 다루는 기술

Posted 2022-05-07Updated 2022-05-10 minkuen SQL / oracle9 minutes read (About 1423 words)

Oracle_practice3

sql developer

sql developer에서 새로운 sql 워크시트를 생성한다.

도구 → sql워크시트 :
오라클 SQL과 PL/SQL을 다루는 기술 92p

SELECT 사용법

교재를 참고하여 코드를 익혀보자
SELECT문은 다음과 같이 사용한다.

-- SELECT
SELECT * 혹은 컬럼
FROM 테이블 또는 VIEW
WHERE 조건
ORDER BY 컬럼;

어디서 가져올 것인가? FROM
무얼을 가져올 것인가? SELECT
어떻게 가져올 것인가? WHERE

SELECT 문

SELECT문을 실제로 사용해본다.

-- SELECT
-- 질문 : 사원 테이블에서 급여가 5000이 넘는 사원번호와 사원명을 조회한다.
DESC employees;

SELECT 
    employee_id
    , emp_name
    , salary
FROM employees
WHERE salary > 5000;

다음과 같이 출력된다.

Untitled

select문을 좀 더 다양하게 작성해본다.

-- 급여가 5000 이상이고 job_id가 IT_PROG 사원 조회
-- 94p
SELECT 
    employee_id
    , emp_name
    , salary
FROM employees
WHERE salary > 5000
    AND job_id = 'IT_PROG'
ORDER BY employee_id;

Untitled

INSERT문, SELECT문

INSERT문으로 행을 삽입하고 SELECT로 출력해보자.

-- p.94
-- 급여가 5000 이상이고 job_id가 IT_PROG 사원 조회
SELECT 
    employee_id
    , emp_name
    , salary
FROM employees
WHERE salary > 5000
    AND job_id = 'it_prog' -- 영어는 대소문자 구분
ORDER BY employee_id;

-- 급여가 5000 이상이거나 job_id가 'IT_PROG'인 사원 (OR)
-- 합집합
SELECT 
    employee_id
    , emp_name
    , salary
    , job_id
FROM employees
WHERE salary > 5000
    OR job_id = 'it_prog' -- 영어는 대소문자 구분
ORDER BY employee_id;

SELECT * FROM employees;

-- INSERT 
CREATE TABLE ex3_1 (
    col1 VARCHAR2(10), 
    col2 NUMBER, 
    col3 DATE
);

INSERT INTO ex3_1(col1, col2, col3) 
VALUES ('ABC', 10, SYSDATE);

-- 컬럼 순서를 바꿔도 큰 문제가 안됨
INSERT INTO ex3_1(col3, col1, col2)
VALUES (SYSDATE, 'DEF', 20);

SELECT * FROM ex3_1;

-- 타입 맞추지 않음 오류 발생
INSERT INTO ex3_1(col1, col2, col3)
VALUES ('ABC', 10, 30);

-- p.97
-- 컬럼명 생략
INSERT INTO ex3_1
VALUES ('GHI', 10, SYSDATE);

INSERT INTO ex3_1 (col1, col2)
VALUES('GHI', 20);

SELECT * FROM ex3_1;

Untitled

자주 사용되는 SELECT문 활용법이다.

-- p.98
CREATE TABLE ex3_2(
    emp_id      NUMBER
    , emp_name  VARCHAR2(100)
);

-- 실무에서 많이 쓰임
INSERT INTO ex3_2(emp_id, emp_name) 
SELECT
    employee_id
    , emp_name
FROM employees
WHERE salary > 5000;

SELECT * FROM ex3_2;

Untitled

UPDATE 문 사용법

UPDATE : 기존 데이터를 수정
ARTER : 기존 테이블의 컬럼명 수정, 제약조건

1
2
3

UPDATE 테이블명
SET 컬럼1 = 변경값1, 컬럼2 = 변경값2
WHERE 조건;

UPDATE 문

UPDATE 문을 실제로 사용해보자.

SELECT * FROM ex3_1;

-- col2 모두 50으로 변경한다.
UPDATE ex3_1
SET col2 = 50;

SELECT * FROM ex3_1;

col2 값이 모두 50으로 수정되었다.

Untitled

SELECT문으로 사람 이름을 수정해보자.

UPDATE ex3_2
SET EMP_NAME = 10 -- Michael Hartstein
WHERE EMP_ID = 201;

SELECT * FROM ex3_2;

Michael이란 이름이 10으로 변경되었다.

Untitled

MERGE

조건 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 INSERT 있으면 UPDATE하는 방식이다.

MERGE INTO [스키마.]테이블명
        USING (update나 insert될 데이터 원천)
             ON (update될 조건)
    WHEN MATCHED THEN
           SET 컬럼1 = 값1, 컬럼2 = 값2, ...
    WHERE update 조건
           DELETE WHERE update_delete 조건
    WHEN NOT MATCHED THEN
           INSERT (컬럼1, 컬럼2, ...) VALUES (값1, 값2,...)
           WHERE insert 조건;

-- MERGE
-- 조건 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 INSERT
-- 있으면 UPDATE

CREATE TABLE ex3_3(
    employee_id     NUMBER
    , bonum_amt     NUMBER DEFAULT 0
);

SELECT * FROM SALES;
DESC SALES;

-- ex3_3 신규테이블 생성
INSERT INTO ex3_3(employee_id)
SELECT 
    e.employee_id
FROM employees e, sales s 
WHERE e.employee_id = s.employee_id
    AND s.SALES_MONTH BETWEEN '200010' AND '200012'
GROUP BY e.employee_id;
-- group by를 통해 중복 제거

SELECT * FROM ex3_3 ORDER BY employee_id;

Untitled

서브쿼리

쿼리 안에 또 다른 쿼리가 있는 형태이다.

-- (1) 관리자 사번(manager_id)이 146번인 사원을 찾는다.
-- (2) ex3_3 테이블에 있는 사원의 사번과 일치하면
--     보너스 금액에 자신의 급여의 1%를 보너스로 갱신
-- (3) ex3_3 테이블에 있는 사원의 사번과 일치하지 않으면
--      (1)의 결과의 사원을 신규로 입력 (이 때 보너스 금액은 급여의 0.1%
-- (4) 이 때 급여가 8000미만인 사원만 처리해보자.

-- 서브쿼리
SELECT
    employee_id
    , manager_id
    , salary
    , salary * 0.01 -- 10%
FROM employees
WHERE employee_id IN (SELECT employee_id FROM ex3_3);

Untitled

서브쿼리의 또 다른 예시이다.

-- 관리자 사번이 146번인 사원은 161번 사원 한 명이므로
-- ex3_3 테이블에서 161인 건의 보너스 금액은 7,000 * 0.01, 즉 70으로 갱신

SELECT
    employee_id
    , manager_id
    , salary
    , salary * 0.01 -- 10%
FROM employees
WHERE employee_id NOT IN (SELECT employee_id FROM ex3_3)
    AND manager_id = 146;

Untitled

MERGE 문

MERGE INTO ex3_3 d 
    USING (SELECT
                employee_id
                , salary 
                , manager_id
           FROM employees
           WHERE manager_id = 146) b 
        ON (d.employee_id = b.employee_id)
WHEN MATCHED THEN 
    UPDATE SET d.bonus_amt = d.bonus_amt + b.salary * 0.01 
WHEN NOT MATCHED THEN 
    INSERT (d.employee_id, d.bonus_amt) VALUES (b.employee_id, b.salary * 0.001)
    WHERE (b.salary < 8000);
    
SELECT * FROM ex3_3 ORDER BY employee_id;

오타 처리하다 3번 실행한 결과이다.

Untitled

DELETE 문

UPDATE에 비해 간단하다.
삭제하기만 하면 되기 때문.

-- DELETE 문
-- 106p
DELETE ex3_3;
SELECT * FROM ex3_3;

COMMIT, ROLLBACK

commit과 rollback을 사용해보자.

-- Commit Rollback Truncate
-- Commit : 변경한 데이터를 DB에 마지막으로 반영
-- ROLLBACK : 반대로 변경한 데이터를 변경하기 이전 상태로 되돌림
CREATE TABLE ex3_4 (
    employee_id NUMBER 
);
    
INSERT INTO ex3_4 VALUES (100);

SELECT * FROM ex3_4;

commit;
rollback;

Untitled

commit 후에는, sqlplus에서도 테이블을 출력 할 수 있게 된다.

Untitled

TRUNCATE문

한번 실행 시, 데이터 바로 삭제한다.
ROLLBACK 적용 안됨

-- TRUNCATE 문
-- 한번 실행시, 데이터 바로 삭제, ROLLBACK 적용 안됨
-- DELETE문은 데이터 삭제 후, COMMIT 필요 / ROLLBACK 데이터가 삭제되기 전의 복구 불가

SELECT * FROM ex3_4;
TRUNCATE TABLE ex3_4;

Untitled

Reference : 오라클 SQL과 PL/SQL을 다루는 기술

Posted 2022-05-06Updated 2022-05-09 minkuen SQL / oracle10 minutes read (About 1529 words)

Oracle_practice2

sql developer

sql developer에서 새로운 sql 워크시트를 생성한다.
- 도구 → sql 워크시트 : ch02_0426
오라클 SQL과 PL/SQL을 다루는 기술 60p

실습

교재를 참고하여 코드를 익혀보자
제약조건을 작성해본다.

-- 60P
-- not null
-- 제약 조건
CREATE TABLE ex2_6(
    COL_NULL        VARCHAR2(10)
    , COL_NOT_NULL  VARCHAR2(10) NOT NULL
);

INSERT INTO ex2_6 VALUES ('AA', '');
INSERT INTO ex2_6 VALUES ('AA', 'BB');

SELECT * FROM ex2_6;

-- USER CONSTRAINTS 제약 조건 확인
SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_6';

SELECT 문을 사용하여 제약 조건을 출력.

Untitled

이어서 실습한다.


-- UNIQUE
-- 중복값 허용 안 함
CREATE TABLE ex2_7 (
    COL_UNIQUE_NULL VARCHAR2(10) UNIQUE
    , COL_UNIQUE_NULL VARCHAR2(10) UNIQUE NOT NULL
    , COL_UNIQUE VARCHAR(10)
    , CONSTRAINTS unique_nm1 UNIQUE (COL_UNIQUE)
);

SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_7';
 
 SELECT * FROM ex2_7;
 
 INSERT INTO ex2_7 VALUEs('AA', 'AA', 'AA');
 SELECT * FROM ex2_7;
 
 INSERT INTO ex2_7 VALUES('AA', 'AA', 'AA');
 SELECT * FROM ex2_7;
 
 INSERT INTO ex2_7 VALUES('', 'BB', 'BB');
 INSERT INTO ex2_7 VALUES('', 'CC', 'CC');
 SELECT * FROM ex2_7;

-- 기본키 ( 63P)
CREATE TABLE ex2_8(
    COL VARCHAR2(10) PRIMARY KEY
    , COL2 VARCHAR2(10)
);

SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_8';
 
INSERT INTO ex2_8 VALUES('','AA');
-- 오류 보고 ORA-01400: NULL을 ("ORA_USER"."EX2_8"."COL") 안에 삽입할 수 없습니다
-- NULL 값을 삽입하여 생기는 오류이다. NULL값 대신 다른 값을 입력하자.

여기까지 입력하고 실행하면 오류가 발생한다.

— 오류 보고 ORA-01400: NULL을 (“ORA_USER”.”EX2_8”.”COL”) 안에 삽입할 수 없습니다

Untitled

NULL 값을 삽입하여 생기는 오류이다. NULL값 대신 다른 값을 입력하자.

-- 오류 보고 ORA-01400: NULL을 ("ORA_USER"."EX2_8"."COL") 안에 삽입할 수 없습니다
-- NULL 값을 삽입하여 생기는 오류이다. NULL값 대신 다른 값을 입력하자.
INSERT INTO ex2_8 VALUES('AA','AA');
SELECT * FROM ex2_8;

이번에는 오류가 출력되지 않았다.

Untitled

같은 값을 다시 입력해보자.

INSERT INTO ex2_8 VALUES('AA','AA');
-- ORA-00001: 무결성 제약 조건(ORA_USER.SYS_C007484)에 위배됩니다
-- 값이 같은 데이터를 입력하여 생기는 오류이다. 다른 데이터를 입력해야 한다.

오류가 발생했다.

– ORA-00001: 무결성 제약 조건(ORA_USER.SYS_C007484)에 위배됩니다

값이 같은 데이터를 입력하여 생기는 오류이다. 다른 데이터를 입력하면 해결된다.

Untitled

테이블 생성과 제약조건

90p의 테이블을 생성해보자.

-- 90p 테이블 1, 2, 3번 생성
CREATE TABLE ORDERS (
    ORDER_ID              NUMBER(12, 0)
    , ORDER_DATE          DATE
    , ORDER_MODE          VARCHAR2(8 BYTE)
    , CUSTOMER_ID         NUMBER(6, 0)
    , ORDER_STATUS        NUMBER(2, 0)
    , ORDER_TOTAL         NUMBER(8, 2) DEFAULT 0
    , SALES_REP_ID        NUMBER(6, 0)
    , PROMOTION_ID        NUMBER(6, 0)
    , CONSTRAINT PK_ORDER PRIMARY KEY (ORDER_ID)
    , CONSTRAINT CK_ORDER_MODE CHECK (ORDER_MODE in ('direct', 'online'))
);

CREATE TABLE ORDER_ITEMS (
           ORDER_ID	    NUMBER(12,0),
           LINE_ITEM_ID NUMBER(3,0) ,
           ORDER_MODE	  VARCHAR2(8 BYTE),
           PRODUCT_ID   NUMBER(3,0), 
           UNIT_PRICE   NUMBER(8,2) DEFAULT 0, 
           QUANTITY     NUMBER(8,0) DEFAULT 0,
           CONSTRAINT PK_ORDER_ITEMS PRIMARY KEY (ORDER_ID, LINE_ITEM_ID)
); 

CREATE TABLE PROMOTIONS (
           PROMO_ID	    NUMBER(12,0),
           PROMO_NAME	  VARCHAR2(8 BYTE),
           CONSTRAINT PK_PROMOTIONS PRIMARY KEY (PROMO_ID)
); 

-- CHECK
CREATE TABLE ex2_9 (
    num1     NUMBER 
        CONSTRAINTS check1 CHECK ( num1 BETWEEN 1 AND 9),
    gender   VARCHAR2(10) 
        CONSTRAINTS check2 CHECK ( gender IN ('MALE', 'FEMALE'))        
); 

SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_9';
 
INSERT INTO ex2_9 VALUES (10, 'MAN');
INSERT INTO ex2_9 VALUES (5, 'FEMALE');

테이블 삭제

-- DEFAULT 
-- PL/SQL 사용하면 편하게 해결 가능
DROP TABLE ex2_10;

CREATE TABLE ex2_10 (
   Col1        VARCHAR2(10) NOT NULL, 
   Col2        VARCHAR2(10) NULL, 
   Create_date DATE DEFAULT SYSDATE
);
   
-- Col1 Col2 사용자가 입력
-- Create_Date DB에서 자동으로 입력
INSERT INTO ex2_10 (col1, col2) VALUES ('AA', 'BB');
SELECT * FROM ex2_10;

여기까지 작성하고 실행하면 다음과 같이 출력된다.

Untitled

테이블 변경

이번엔 테이블을 여러 명령어를 통해 다뤄보자


-- 테이블 변경
-- 69p (1) 컬럼명 변경

ALTER TABLE ex2_10 RENAME COLUMN Col1 TO Col11;
SELECT * FROM ex2_10;

DESC ex2_10;

-- (2) 컬럼 타입 변경
-- 컬럼 타입 변경 (VARCHAR2(10) ~ VARCHAR2(30))으로 변경
ALTER TABLE ex2_10 MODIFY Col2 VARCHAR2(30);
DESC ex2_10;

테이블이 변경된다.

Untitled

이번엔 생성하거나 삭제해본다.

-- (3) col3 NUMBER 타입으로 신규 생성
ALTER TABLE ex2_10 ADD Col3 NUMBER;
DESC ex2_10;

-- (4) 컬럼삭제
ALTER TABLE ex2_10 DROP COLUMN Col3;
DESC ex2_10;

-- 제약조건 추가
ALTER TABLE ex2_10 ADD CONSTRAINTS pk_ex2_10 PRIMARY KEY (col11);

-- USER CONSTRAINTS 제약 조건 확인
SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_10';

제약조건이 추가되었다.

Untitled

제약조건을 삭제해보자.

-- 제약조건 삭제
 ALTER TABLE ex2_10 DROP CONSTRAINTS pk_ex2_10;
 
 -- USER CONSTRAINTS 제약 조건 확인
SELECT constraint_name, constraint_type, table_name, search_condition
  FROM user_constraints
 WHERE table_name = 'EX2_10';

이전에 추가한 제약조건이 삭제되었다.

Untitled

뷰(view)

emp_dept_v1 뷰를 생성한다.

SELECT 
    a.employee_id
    , a.emp_name
    , a.department_id -- 부서명 컬럼
    , b.department_name
FROM 
    employees a
    , departments b
WHERE a.department_id = b.department_id;

CREATE OR REPLACE VIEW emp_dept_v1 AS 
SELECT 
    a.employee_id
    , a.emp_name
    , a.department_id
    , b.department_name
FROM 
    employees a
    , departments b
WHERE a.department_id = b.department_id;

새로운 view가 생성된다.

Untitled

인덱스(index)

인덱스를 생성한다.

-- 인덱스 생성
-- 75p
-- 추후 공부해야 할 내용 : 인텍스 내부 구조에 따른 분류
---- (초중급 레벨) B-Tree 인덱스, 비트맵 인덱스, 함수 기반 인덱스
---- DB 성능
-- 인덱스 생성
-- col11 값에 중복 값을 허용하지 않는다.
-- 인덱스 생성 시, user_indexes 시스템 뷰에서 내역 확인
CREATE UNIQUE INDEX ex2_10_ix011 
ON ex2_10(col11);

SELECT index_name, index_type, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EX2_10';

실제로 인덱스가 생성되었다는 안내문이 출력된다.

Untitled

Reference : 오라클 SQL과 PL/SQL을 다루는 기술

Posted 2022-05-05Updated 2022-05-08 minkuen SQL / oracle3 minutes read (About 473 words)

Oracle_practice1

사전준비

이전 글을 참고하여 Oracle 설치한다.
https://www.notion.so/Oracle-setting-e380ad08eac048ebba97c7b90c69a132

실습

SQL Developer 에서 실행
테이블을 생성한다.
- 다음 코드를 작성해보자

CREATE TABLE ex2_1 (
COLUMN1     CHAR(10)
, COLUMN2     VARCHAR2(10)
, COLUMN3     NVARCHAR2(10)
, COLUMN4     NUMBER
);

저장(ctrl + s) 후 실행(ctrl + enter)
- 다음과 같이 테이블과 컬럼이 생성된다.

Untitled

오라클 SQL과 PL/SQL을 다루는 기술 52p
교재를 따라 다음과 같이 작성하고 실행해 본다.
- 스크립트 실행 = 전체 실행 = F5
- 명령문 실행 = 일부 실행 = ctrl + enter


CREATE TABLE ex2_1 (
COLUMN1 CHAR(10)
, COLUMN2 VARCHAR2(10)
, COLUMN3 NVARCHAR2(10)
, COLUMN4 NUMBER
);

-- 데이터 추가
INSERT INTO ex2_1 (COLUMN1, COLUMN2) VALUES('abc', 'abc')

-- 데이터 확인
SELECT
COLUMN1
, LENGTH(COLUMN1) as len1
, COLUMN2
, LENGTH(COLUMN2) as len2
FROM ex2_1;

-- 새로운 테이블 생성
CREATE TABLE ex2_2(
COLUMN1 VARCHAR2(3)
, COLUMN2 VARCHAR2(3 byte)
, COLUMN3 VARCHAR2(3 char)
);

-- 데이터 추가
INSERT INTO ex2_2 VALUES('abc', 'abc', 'abc');

-- 조회
SELECT
COLUMN1
, LENGTH(COLUMN1) As len1
, COLUMN2
, LENGTH(COLUMN2) As len2
, COLUMN3
, LENGTH(COLUMN3) As len3
FROM ex2_2;

-- 한글 데이터 삽입
-- 에러 발생
INSERT INTO ex2_2 VALUES('홍길동', '홍길동', '홍길동');

- 데이터 조회
SELECT
COLUMN3
, LENGTH(COLUMN3) As len3
, LENGTHB(COLUMN3) As bytelen
FROM ex2_2;

- 숫자 데이터 삽입
CREATE TABLE ex2_3(
COL_INT INTEGER
, COL_DEC DECIMAL
, COL_NUM NUMBER
);

SELECT
column_id
, column_name
, data_type
, data_length
FROM user_tab_cols
WHERE table_name = 'EX2_3'
ORDER BY column_id;

-- p57
CREATE TABLE ex2_4 (
COL_FLOT1 FLOAT(32),
COL_FLOT2 FLOAT
);

INSERT INTO ex2_4 (col_flot1, col_flot2) VALUES (1234567891234, 1234567891234);

-- 조회
SELECT * FROM ex2_4;

-- p.58
CREATE TABLE ex2_5(
COL_DATE DATE
, COL_TIMESTAMP TIMESTAMP
);
INSERT INTO ex2_5 VALUES (SYSDATE, SYSTIMESTAMP);
SELECT * FROM ex2_5;

-- LOB 데이터 타입
-- Large object의 약자로 대용량 데이터 저장할 수 있는 데이터 타입
-- 비정형 데이터는 그 크기가 매우 큰데, 이런 데이터를 저장한다.

Reference
- 오라클 19c 기본 세팅 - Data Science | DSChloe
- 오라클 SQL과 PL/SQL을 다루는 기술

Posted 2022-05-04Updated 2022-05-07 minkuen setting / oracle10 minutes read (About 1436 words)

Oracle_setting

사전준비

오라클을 설치한다.

구글링 : oracle database 19c download

Oracle Database 19c Download for Microsoft Windows x64 (64-bit) | Oracle 대한민국

Untitled

SQL Developer 설치한다.

구글링 : sql developer

Oracle SQL Developer Downloads

Untitled

c 드라이브 경로에 폴더 생성 : sql_lecture

앞서 다운로드한 설치 파일을 sql_lecture 폴더에 정리한다.

압축 해제한다.

우클릭 후에 “extract WINDOWS.X64\”

관리자 권한으로 실행 : setup
다음 경로로 이동하여 실행하면 된다.

c 드라이브 → sql_lecture 폴더 → WINDOWS.X64_193000_db_home 폴더 → setup

Untitled

체크 : 단일 인스턴스 데이터베이스 생성 및 구성

Untitled

체크 : 데스크톱 클래스

Untitled

체크 : 가상 계정 사용

Untitled

오라클 SQL과 PL/SQL을 다루는 기술 22p 참고하여 설정한다.
전역 데이터베이스 이름 : myoracle
비밀번호 : 1234
체크 해제 : 컨테이너 베이스로 생성

Untitled

설치까지 진행한다.
설치 완료

Untitled

1단계 sqlplus 실행하기

관리자 권한으로 실행 : SQL Plus

Untitled

정보를 입력한다.

→사용자명 : system

→비밀번호 : 1234

Untitled

2단계 : 테이블 스페이스 생성하기

오라클 SQL과 PL/SQL을 다루는 기술 27p 참고
다음 코드를 사용하여 테이블 스페이스를 생성한다.
- 테이블 스페이스는 myts라 명명하고 100MB 크기로 생성
- 만약 데이터가 증가하면 5MB씩 자동 증가 옵션 추가

→CREATE TABLESPACE myts DATAFILE 'C:\sql_lecture\oradata\MYORACLE\myts.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M;

Untitled

3단계 : 사용자 생성

해당 사용자에게 롤(Role, 권한)을 부여해야 한다. 현 시점에서는 ‘ora_user’ 사용자에게 DBA라는 롤을 부여한다.
- 이 권한을 부여받으면 오라클에서 제공하는 웬만한 기능을 모두 사용한다.
사용자를 생성하는 코드를 작성한다.

(패스워드를 evan으로 할 경우, 다음과 같이 작성)

→CREATE USER ora_user IDENTIFIED BY evan DEFAULT TABLESPACE MYTS TEMPORARY TABLESPACE TEMP;

Untitled

4단계 : 사용자 계정으로 DB에 접속하기

ora_user로 접속한다.

→GRANT DBA TO ora_user;

→connect ora_user/evan;

접속 후, show user; 입력하면 현재 로그인한 사용자 이름이 출력된다.

→ show user;

Untitled

SQL Developer 실행

새 접속 화면이 나타나면 접속 이름, 사용자 이름을 ora_user로 입력, 비밀번호는 입력, SID 항목에는 처음 설치 시 이름인 myoracle을 입력하고 테스트를 실행한다.
압축 해제한다.

우클릭 후에 “extract sqldeveloper214.3…”

관리자 권한으로 실행 : sqldeveloper
다음 경로로 이동하여 실행하면 된다.

c 드라이브 → sql_lecture 폴더 → sqldeveloper-21.4.3.063.0100-x64 폴더

→ sqldeveloper 폴더 → sqldeveloper

만약 다음 확인 창이 출력되면 ‘아니오’ 선택한다.

Untitled

Oracle 접속을 새로 만든다.

우클릭 : Oracle 접속 → 새 접속

Untitled

다음과 같이 설정하고 ‘테스트’
- 사용자 이름 : ora_user
- 비밀번호 : evan
상태 : 성공
성공했다면 ‘접속’

Untitled

다음과 같이 출력된다.

Untitled

환경설정

다음 환결설정에서 인코딩을 UTF-8로 변경한다.
- 메뉴 바 → 도구 → 환경 설정 → 환경 → 인코딩 : UTF-8

Untitled

SQL Developer 날짜 기록

다음 경로로 [NLS] 항목을 선택한다.
- 메뉴 바 → 도구 → 환경 설정 → 데이터 베이스 → NLS
NLS에서 ‘시간 기록 형식’을 수정.
- 다음과 같이 수정한다.
- 시간 기록 형식 : YYYY/MM/DD HH24:MI:SS

Untitled

샘플 스키마 설치

expall.dmp와 expcust.dmp 파일을 내려 받는다.
- URL : https://github.com/gilbutITbook/006696/tree/master/01장 환경설정
c 드라이브에 폴더 생성
- 폴더 생성 : backup
backup 폴더에 expall.dmp와 expcust.dmp 파일을 옮긴다.

Untitled

관리자 권한으로 실행 : 명령 프롬프트
C:\backup 경로로 이동한다.
다음 코드 실행 : expall.dmp 을 올린다.

→ imp ora_user/evan file=expall.dmp log=empall.log ignore=y grants=y rows=y indexes=y full=y

다음 코드 실행 : expcust.dmp 을 올린다.

→ imp ora_user/evan file=expcust.dmp log=expcust.log ignore=y grants=y rows=y indexes=y full=y

Untitled

임포트가 정상 종료되었다면 Oracle SQL로 이동하여 작업
다음 코드를 작성

→ SELECT table_name FROM user_tables;

→ 실행 : ctrl + enter

→ 다음과 같이 출력되면 성공.

Untitled

Git 연동

SQL Developer with Git - Data Science | DSChloe

Reference
- 오라클 19c 기본 세팅 - Data Science | DSChloe
- 오라클 SQL과 PL/SQL을 다루는 기술

Oracle on Jupyter Lab

Oracle_practice4

조건식

sql developer

연산자

표현식

조건식

비교 조건식

ANY

ALL

SOME

논리 조건식

BETWEEN AND 조건식

IN 조건식

NOT IN 조건식

EXISTS 조건식

LIKE 조건식

Oracle_practice3

sql developer

SELECT 사용법

SELECT 문

INSERT문, SELECT문

UPDATE 문 사용법

UPDATE 문

MERGE

서브쿼리

MERGE 문

DELETE 문

COMMIT, ROLLBACK

TRUNCATE문

Oracle_practice2

sql developer

실습

테이블 생성과 제약조건

테이블 삭제

테이블 변경

뷰(view)

인덱스(index)

Oracle_practice1

사전준비

실습

Oracle_setting

사전준비

1단계 sqlplus 실행하기

2단계 : 테이블 스페이스 생성하기

3단계 : 사용자 생성

4단계 : 사용자 계정으로 DB에 접속하기

SQL Developer 실행

환경설정

SQL Developer 날짜 기록

샘플 스키마 설치

Links

Categories

Recents

Archives

Tags

Subscribe for updates

follow.it