[Oracle] SQL문 정리 4

기초 복습

TABLE

테이블 생성

CREATE TABLE 테이블 이름(
	column1 datatype,
	column2 datatype
	...
	);

--ex)
CREATE TABLE char_table(
    name01 varchar2(100),
    name02 char(4),
    name03 char(10)
    );

 

테이블 변경

--Column 변경
ALTER TABLE table_naeme
MODIFY column_name datatype;

--Column 추가
ALTER TABLE table_name
ADD column_name datatype;

--Column 삭제
ALTER TABLE table_name
DROP COLUMN column_name;

 

 

제약 조건 생성

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type(column_name);

ex) ALTER TABLE emp
    ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY(employee_id);

    ALTER TABLE emp
    ADD CONSTRAINT new_emp_salary_ck CHECK(salary>0);
    
    ALTER TABLE emp
    ADD CONSTRAINT new_emp_dept_fk FOREIGN KEY(department_id) 
    				   REFERENCES departments(department_id)

 

 

하위 질의를 이용한 테이블 복사

--table_name2 의 column_name1과 column_name2 열을 추출해 
--새로운 table_name1 생성
CREATE TABLE table_name1
AS SELECT column_name1, column_name2...
FROM table_name2

ex) CREATE TABLE emp_demo (dmp_id, name, email)
	AS SELECT employee_id, last_name, email FROM emp;


--테이블의 이름 변경
RENAME ex_table_name TO new_table_name;

ex) RENAME emp TO emp_demo;

 

 

테이블 삭제

DROP TABLE table_name[CASCADE CONSTRAINT];

--CASCADE CONSTRAINT : 참조되어지는 테이블의 데이터도 함께 삭제

ex) DROP TABLE emp CASCADE CONSTRAINTS;

 

 

Sequence

create sequence board_seq --start with 11

insert into board values (board_seq.nextval, 'all', 'a', 'a', sysdate, 0);

delete from board

drop sequence board_seq

create sequence board_seq

insert into board values (board_seq.nextval, 'all', 'a', 'a', sysdate, 0);

 

 

Index 와 B Tree

INDEX

 - ROWID : 레코드(테이블에 입력된 실질적 데이터 ex) 1행 1열의 데이터)에 대한 디스크의 물리적인 저장 위치

 

 - INDEX는 rowid(주소) 와 column 값의 집합 (LEAF BLOCKS) 으로 이루어짐

   + 해당 pointer를 갖는 branch block

   (이를 통해 Oracle이 원하는 데이터에 접근 가능하며 모든 데이터에 대한 일정한 접근 속도)

 

 - 자동으로 B Tree의 형태로 균일화함

 

 

Index를 생성해야하는 경우

 - where절, join 조건으로 자주 사용하는 Column

 

 - 모든 값이 컬럼내에서 unique

 

 - 넓은 범위의 값을 가진 Column

 

 - 아주 드물게 존재하는 Column

 

 

오라클 실행 과정

 1. SQL 파싱 : SQL 구문에 오류가 있는지, SQL 실행 대상 객체 (테이블, 시퀀스, 제약조건) 가 존재하는지 검사

 

 2. SQL 최적화 (실행계획) : SQL이 실행되는데 필요한 비용(cost)을 계산

 

 3. SQL 실행 : 세워진 실행 계획을 통해 물리적 실행

 

 

Oracle에서 Index 생성

 - PRIMARY KEY, UNIQUE 제약 조건을 갖는 Column은 기본적으로 Index가 자동으로 생성

   (CREATE INDEX 인덱스명)

 

INSERT INTO board(seq, title, writer, contents, regdate, hitcount)
	(select board_seq.nextval, title, writer, contents, regdate, hitcount from board);

SELECT * FROM board 
WHERE seq = 50000;

ALTER TABLE board
ADD CONSTRAINT board_seq_pk PRIMARY KEY(seq);

CREATE INDEX board_seq_idx
ON board (seq);

 

 

Q. 'title' 에 대한 글 번호(seq) 10000번에 대해서 title 값을 'a10000' 수정하고

    'a10000' 검색 후 실행 계획을 확인(full scan) + 인덱스를 생성하고 다시 검색 후 확인

UPDATE board SET title = 'a10000'
WHERE seq = 10000;

SELECT * FROM board
WHERE title = 'a10000';

CREATE INDEX board_title_idx
ON board (title)

DROP INDEX board_title_idx;

 

 

VIEW

 - 과도한 조인의 대안

 

 - 보안적인 측면의 대안

 

Q. hr) 1. 기존에 과도한 조인이 필요한 sql을 뷰를 생성해서 간단히 사용해 보자.
         2. employees 테이블의 salary를 제외한 내용을 검색할 수 있도록 view를 만들어 보자.

CREATE VIEW empInfo_vw AS
SELECT employee_id, last_name, department_name, city, state_province, country_name, region_name
   FROM  departments d 
   JOIN employees e ON d.department_id = e.department_id
   JOIN locations l ON d.location_id = l.location_id
   JOIN countries c ON l.country_id = c.country_id
   JOIN  regions r ON c.region_id = r.region_id;

SELECT last_name, department_name, country_name,region_name
FROM empInfo_vw
WHERE employee_id = 100;

DESC empInfo_vw;

 

 

 

PL / SQL (Procedural Language Extension to Structured Query Language)

 - SQL에 프로그래밍 언어 기능(변수, 제어문, 함수)을 절차적으로 확장한 언어

 

 

처리과정

 1. PL/SQL로 작성된 실행 Block을 Oracle Server로 전송하면 Oracle Server 내의 PL/SQL Engine이 이를 처리

 

 2. PL/SQL 엔진은 블록 내의 SQL문을 분리하여 하나씩 SQL 엔진에 전달

 

 3. PL/SQL 코드는 Oracle Server에 필요에 따라 저장 가능하며, 이름 앞에 Stored를 붙여 Stored Procedure라 명명

 

 

PL/SQL의 장점

 1. Compile이 필요 없어 Script 생성 및 변경 후 바로 실행 가능

 

 2. 프로그램 개발의 모듈화

  - 블록 내에서 논리적으로 관련된 문장들의 그룹화 가능

  - 복잡한 문제에 대한 프로그래밍이 적절히 나뉜 모듈들의 집합으로 구성

 

 3. 식별자 선언

  - 변수, 상수 등을 선언하고 SQL과 절차적인 프로그램에서 사용

  - 데이터베이스의 테이블과 record를 기반으로 하는 dynamic 한 변수 선언이 가능

  - 단일형 데이터 타입과 복합형 데이터 타입을 선언할 수 있음

 

 4. 절차적 언어 구조로 된 프로그램 작성 가능

  - IF문은 조건에 따라 일련의 문장을 실행

  - LOOP문을 사용하여 일련의 문장을 반복적으로 실행

  - Explicit Cursor를 이용하여 Multi-row 질의를 처리

 

 5. ERROR 처리

  - Exception 처리 루틴을 이용하여 Oracle Server 에러를 처리함

  - 사용자 정의 에러를 선언하고 Exception 처리 루틴으로 처리 가능함

 

 6. 성능 향상

  - PL/SQL은 응용 프로그램의 성능을 향상할 수 있음

  - PL/SQL은 여러 SQL 문장을 BLOCK으로 묶고 한 번에 BLOCk전부를 서버로 전송하기 때문에 통신량을 줄임

 

 

set serveroutput on;

 - PL/SQL 에서 DBMS_OUTPUT.PUT_LINE(" ")을 통한 출력을 보기 위해 선행적으로 serveroutput을 on으로 바꿔야 함

 

 

식별자 명명 규칙

- 'name'에 식별자 이름을 치환해서 명명

 

 

PL/SQL 기본 구조 (DECLARE ... BEGIN ... END)

선언부 (DECLARE)

 - 실행부에서 사용할 변수나 상수, 커서 등 선언;

 

실행부 (BEGIN ... END)

 - SQL, PL/SQL 프로그램 코드;

 

예외처리부 (Exception)

 - 런타임 오류나 예외 발생 시 처리할 코드;

 

ex)

//선언부
DECLARE
	v_counter NUMBER(5);
   
//실행부
BEGIN
	v_counter := 10;
    v_counter := v_counter / 2;
    dbms_output.put_line(v_counter);

//예외처리부
EXCEPTION WHEN OTHERS THEN
	dbms_output.put_line('ERRORs');

END;

 

 

DECLARE

 - 기본형

   v_search VARCHAR2(30) := 'Lisa';

 

 - 레퍼런스 형

   v_name employees.last_name%TYPE;

   v_salary employees.salary%TYPE;

 

DECLARE
  --변수 선언
  v_no NUMBER := 10;
  v_hireDate VARCHAR2(30) := TO_CHAR(SYSDATE, 'YYYY/MM/DD');

 --상수 선언
 c_message CONSTANT VARCHAR2(50) := '안녕 PL/SQL!!!';

BEGIN
  DBMS_OUTPUT.PUT_LINE('5일 수업내용');
  DBMS_OUTPUT.PUT_LINE(c_message);
  DBMS_OUTPUT.PUT_LINE(v_hireDate);

END;

 

 

Q. hr) 특정 테이블의 row를 검색하여 변수에 할당 후 출력

DECLARE
  v_name VARCHAR2(20);
  v_salary NUMBER;
  v_hiredate VARCHAR2(30);

BEGIN
  SELECT first_name, salary, TO_CHAR(hire_date, 'yyyy-MM-dd')
	INTO v_name, v_salary, v_hiredate
	FROM employees
	WHERE first_name = 'Ellen';
  
  DBMS_OUTPUT.PUT_LINE('검색된 사원 정보');
  DBMS_OUTPUT.PUT_LINE(v_name || '  '|| v_salary ||'   '|| v_hiredate);

END;

 

 

Q. hr) 사원번호 100번에 해당하는 사원의 이름과 부서명을 출력

DECLARE
v_emp_name VARCHAR2(50);
v_dept_name VARCHAR2(50);

BEGIN

SELECT last_name, department_name INTO v_emp_name, v_dept_name 
FROM employees e, departments d
where e.department_id = d.department_id
AND employee_id = 100;

dbms_output.put_line(v_emp_name|| '  ' || v_dept_name);
END;

 

 

Q. hr) 사원 테이블에서 201번 사원의 이름과 이메일을 출력 (레퍼런스형)   

DECLARE
    v_employees_name employees.last_name%TYPE;
    v_employees_email employees.email%TYPE;
    
BEGIN
SELECT last_name, email
    into v_employees_name, v_employees_email
    from employees
    where employee_id = 201;
    
dbms_output.put_line(v_employees_name ||'   '||v_employees_email);

end;

 

 

Q. hr) 사원테이블에서 사원번호가 가장 큰 사원을 찾은 후

        그 사원의 사원번호보다 1 큰 사원을 아래의 데이터를 통해 추가

create table employees2 as select * from employees;

DECLARE
    v_employees_id employees.employee_id%TYPE;
    v_employees_name employees.last_name%TYPE := 'Hong Gil Dong';
    v_employees_email employees.email%TYPE := 'aa@aa.com';
    v_employees_hiredate employees.hire_date%TYPE := sysdate;
    v_employees_job_id employees.job_id%TYPE := 'AD_VP';
    
BEGIN

select max(employee_id)
into v_employees_id
from employees;

insert into employees2(employee_id, last_name, email, hire_date, job_id)
values (v_employees_id + 1, v_employees_name, v_employees_email, 
		v_employees_hiredate, v_employees_job_id);

end;

 

 

 

---

PL/SQL의 구성요소 - 변수와 상수

 - 일반적 변수 및 상수 선언과 유사

 

 - 상수 선언 시, 상수명 뒤에 constant 키워드를 붙여야 함

 

 - %TYPE : 기존 테이블에 있는 Column의 데이터 타입을 그대로 이용

 

 - %ROWTYPE : 특정 테이블의 모든 Column의 데이터 타입을 읽어올 수 있음

 

ex)

emp_num1 NUMBER(9);

nYear CONSTANT INTEGER := 30;

nSalaries EMPLOYEES.SALARY%TYPE;

 

 

예외 (Exception)

컴파일 시 문법적 오류, 실행 시 발생하는 오류

 - 사전 정의된 오라클 서버 예외 : 선언할 필요 없이, 발생 시 자동으로 예외처리

 

 - 사용자 생성 예외 : 선언부에서 정의한 예외, 실행부에서 RAISE문을 통해 인위적으로 예외 발생

 

DECLARE
	employee_record employees%ROWTYPE;
    
BEGIN
	SELECT employee_id, last_name, department_id
    INTO employee_record.employee_id,
    	employee_record.last_name,
        employee_record.department_id
    FROM employees
    WHERE department_id = 50;
    
EXCEPTION
	--UNIQUE 제약을 갖는 COLUMN에 중복된 데이터를 추가하려 할 때
	WHEN DUP_VAL_ON_INDEX THEN
    	DBMS_OUTPUT.PUT_LINE('이미 존재하는 사원입니다.');
    
    --SELECT문 검색 결과가 2개 이상의 ROW를 반환할 때
    WHEN TOO_MANY_ROWS THEN
    	DBMS_OUTPUT.PUT_LINE('검색된 ROW가 너무 많습니다.');

	--SELECT문 결과 데이터가 없을 때
    WHEN NO_DATA_FOUND THEN
    	DBMS_OUTPUT.PUT_LINE('검색된 사원이 없습닌다.');
        
    WHEN OTHERS THEN
    	DBMS_OUTPUT.PUT_LINE('기타 예외');
        
END;

 

 

인위적 예외 발생

DECLARE
	employee_record employees%ROWTYPE;
    cnt number := 0;
    e_user_exception EXCEPTION;
    
BEGIN
    SELECT count(*)
    INTO cnt
    FROM employees
    WHERE department_id = 50;
    
IF cnt < 5 THEN
	--인위적 예외 발생
	RAISE e_user_exception;
END IF;

EXCEPTION
	WHEN e_user_exception THEN
    	dbms_output.put_line('부서원이 너무 적습니다.');
end;

 

 

Q. hr) 신입사원을 입력 시 잘못된 부서 번호에 대한 사용자 예외처리

         => employee2 테이블 활용

         => 부서번호 50000 => 사용자 정의 예외 발생

 

DECLARE
    v_department_id number := 50000;
    v_cnt NUMBER := 0;
    v_employee_id employees2.employee_id%TYPE;
    user_exception EXCEPTION;
    
BEGIN
    select count(*) into v_cnt
    from employees2
    where v_department_id = department_id;    
    
    if v_cnt = 0 then
        raise user_exception;
    end if;

    select max(employee_id) into v_employee_id from employees2;

    INSERT INTO employees2(employee_id, last_name, email, hire_date, job_id, department_id)
        VALUES(v_employee_id, 'aa', 'aa@aa.com', SYSDATE, 'AD_VP', v_department_id);
    
commit;
      
EXCEPTION
    WHEN user_exception THEN
        dbms_output.put_line('해당 부서가 존재하지 않습니다!');
    WHEN OTHERS THEN
        dbms_output.put_line('기타 등등');
END;

 

 

ROWTYPE

1개 행의 데이터를 저장 가능

DECLARE
    employee_record employees%ROWTYPE;

BEGIN
SELECT * INTO employee_record
FROM employees
WHERE first_name = 'Lisa';

dbms_output.put_line(employee_record.employee_id ||' '
||employee_record.first_name||' '||employee_record.salary);

END;

 

Q. scott) 40번 부서의 정보를 rowtype을 이용해서 출력

DECLARE
    dept_record dept%rowtype;    

BEGIN
select * into dept_record
from dept
where deptno = 40;

dbms_output.put_line(dept_record.deptno||' '||dept_record.dname||' '||dept_record.loc);

END;

 

 

PL/SQL의 구성요소 - 레코드

 - 테이블 형태의 데이터 타입 (구조체와 비슷)

 

TYPE 레코드이름 IS RECORD (필드1, 데이터타입1, 필드2, 데이터타입2 ...);

레코드이름 테이블명%ROWTYPE;
레코드이름 커서명%ROWTYPE;

 

 

---

제어문

IF

IF 조건1 THEN
	처리문1;
    
ELSIF 조건2 THEN
	처리문2;
.
.
.
ELSE
	처리문3;

END IF;

 

DECLARE
    v_no NUMBER := 7;

BEGIN
    if v_no = 7 then
    dbms_output.put_line('7!');
    
    elsif v_no = 5 then
    dbms_output.put_line('5!');
    
    else
    dbms_output.put_line('else!');
    
    end if;

END;

 

 

CASE

CASE...WHEN...THEN...ELSE...END CASE;

 

 

Q. hr) 난수 구하기 => ROUND(DBMS_RANDOM.VALUE(10, 120) -1)

        10~120중 임의의 부서 번호를 받아서 해당 부서의 평균 급여에 따라서 등급을 출력

 

1. IF

DECLARE
    v_deptno_random employees.department_id%type := round(dbms_random.value(10, 120), -1);
    v_salary number := 0;
    
BEGIN
    select avg(salary) into v_salary from employees
    where department_id = v_deptno_random;
    
    if v_salary < 3000 then
        dbms_output.put_line('낮음');
    
    --elsif v_salary between 3000 and 6000 then
    elsif v_salary >= 3000 and v_salary < 6000 then
        dbms_output.put_line('보통');
        
    else
        dbms_output.put_line('높음');
    end if;
    
END;

 

2. CASE

BEGIN
    select avg(salary) into v_salary from employees
    where department_id = v_deptno_random;
    
    CASE WHEN v_salary BETWEEN 1 AND 3000 THEN
    DBMS_OUTPUT.PUT_LINE('낮음');
    
    WHEN v_salary BETWEEN 3000 AND 6000 THEN
    DBMS_OUTPUT.PUT_LINE('보통');
    
    ELSE
    DBMS_OUTPUT.PUT_LINE('높음');
    END CASE;
      
END;

 

 

LOOP

LOOP
...;
END LOOP;

 

 

WHILE

WHILE 조건 LOOP
...;
END LOOP;

 

 

FOR

FOR 카운터 IN [REVERSE] 최소값..최대값 LOOP
...;
END LOOP;

 

 

Q. 구구단을 1단부터 9단까지 출력

DECLARE
    
BEGIN
    for i in 1..9 loop
        for j in 1..9 loop
            dbms_output.put_line(i * j);
        end loop;
    end loop;
      
END;

 

 

PL/SQL 커서

CURSOR 커서명 IS SELECT 문장;
OPEN 커서명;
FETCH 커서명 IS 변수 ...;
CLOSE 커서명;

 

묵시적 커서

 - 오라클 내부에서 각 쿼리 결과에 접근하여 사용하기 위한 내부 커서

 

 

PL/SQL 서브 프로그램 - 함수 (사용자 정의 함수)

특정 기능을 수행한 뒤, 값을 반환하는 서브프로그램

CREATE OR REPLACE FUNCTION 함수명
	(
    파라미터1 데이터타입,
 	파라미터2 데이터타입, 
    ...
 	)
RETURN 데이터타입 IS[AS]

BEGIN
	처리내용 ...
RETURN 반환값;

END;

  

 

PL/SQL 서브프로그램 - 프러시저

특정 기능을 수행하지만 값을 반환하지는 않는 서브 프로그램

CREATE OR REPLACE PROCEDURE 함수명
	(
    파라미터1 데이터타입,
 	파라미터2 데이터타입, 
    ...
 	)
IS[AS]
	변수선언부...
BEGIN
	처리내용 ...
EXCEPTION
	예외처리...
END;

 

 

PL/SQL 서브 프로그램 - 프러시저

업무적 혹은 기능적으로 유사한 함수와 프러시저를 모아놓은 서브 프로그램

CREATE OR REPLACE PACKAGE 페키지명 AS
	선언부…
END 패키지명;

CREATE OR PRELACE PACKAGE BODY 패키지명 AS
	변수선언…
    처리내용…

BEGIN

END;

 

 

패키지의 장점

 - 애플리케이션을 더 효율적으로 개발 가능

 

 - 관련 스키마 오브젝트들을 다시 컴파일할 필요 없이 수정 가능

 

 - 한 번에 여러 개의 패키지 오브젝트들을 메모리로 로드 가능

 

 - 프로시저나 함수들의 오버로딩 가능

 

 - 패키지 내의 모든 타입, 항목, 서브 프로그램들을 PUBLIC이나 PRIVATE로 선언해서 사용 가능

 

 

* Oracle SQL Developer 의 포트(8080)가 닫혔을 경우

서비스의 해당 탭들을 재실행함으로써 포트를 다시 열고 사용할 수 있다.