Oracle SQL(6)

✅세션

• 사용자가 오라클 데이터베이스에 접속을 하게 되면 세션이 생성됨
• 사용자가 데이터베이스를 접속 종료할 때 까지 계속 유지됨
• 각 세션에는 고유한 SID(Session identifier)와 SERIAL#(serial number)를 가지고 있음
• 시리얼 번호는 세션이 종료되어도 다른 세션이 동일한 SID를 갖고 시작했을 때 정확한 세션 객체에 적용될 수 있도록 보장해줌

 

✅오라클 DB 사용자 계정

오라클 DB 사용자 계정생성 방법

1. 계정생성시 세션 변경해야함

alter session set "_ORACLE_SCRIPT" = true;

2. 테이블스페이스 생성

create tablespace [테이블스페이스이름] // 대부분 회사 이름으로 사용
datafile ['데이터파일경로'] size [초기사이즈]
autoextend on
next [자동증가사이즈]
maxsize [최대사이즈];

3. 사용자 계정 생성

create user [아이디] identified by [비밀번호]
default tablespace [테이블스페이스이름];

4. 사용자 계정 목록 확인

select username from all_users;

5. 권한부여(모든 권한 부여)

• connect : 접속권한
• resource : 객체 및 데이터 조작 권한
• dba : 데이터베이스 시스템의 설치, 구성, 유지 관리 및 보안 등

grant connect, resource, dba to [아이디];

 

오라클 DB 사용자 계정삭제 방법

drop user [아이디] cascade; -- 사용자 계정 삭제

drop tablespace [테이블스페이스명] -- 테이블 스페이스 삭제
including contents and datafiles
cascade constraints;

 

✅뷰(View)

• 하나 이상의 테이블이나 다른 뷰의 데이터를 기반으로 생성된 가상 테이블
• 뷰를 기본 테이블처럼 쿼리하여 데이터를 검색할 수 있음
• alias (별칭) 사용 가능

 

뷰(View) 생성 방법

create or replace view [뷰 이름]
as [뷰에 담을 가상 테이블의 쿼리문]

 
[사용 예]

create or replace view test1_view
as
   select uname as 이름, kor as 국어, eng as 영어, mat as 수학, addr as 주소
   from sungjuk
   where addr in ('Seoul', 'Jeju');

 

테이블, 뷰(View) 목록 확인

select * from tab; --모든 객체 종류 확인
select * from tab where tabtype = 'TABLE'; --테이블 목록
select * from tab where tabtype = 'VIEW'; --뷰 목록

 

뷰(View) 삭제

drop view [뷰 이름];

 

✅PL/SQL (Oracle's Procedural Language extension to SQL)

• 오라클 데이터베이스에서 사용되는 프로그래밍 언어
• 프로시저, 함수, 트리거, 패키지 등을 개발할 수 있음
• SQL문을 프로그래밍 언어의 구문과 함께 사용하여 데이터베이스 객체를 조작할 수 있음
• 객체 조작의 작업을 자동화함
• 작업의 반복성을 줄일 수 있음

 

PL/SQL 실행순서

1. PL/SQL Block 실행
2. PL/SQL 엔진에서 PL/SQL 부분과 일반 SQL 부분이 분리
3. SQL 부분을 Database Server 가 처리
4. Database Server 에서 처리된 SQL 결과를 PL/SQL Engine 로 전달
5. PL/SQL Engine 에서 처리된 SQL 결과를 바탕으로 나머지 작업 수행

 

PL/SQL문 콘솔창에 출력

PL/SQL문의 결과는 눈에 보이지 않기 때문에 콘솔창 출력을 통해 확인이 가능함
콘솔창 출력을 위한 준비작업을 해야함

set serveroutput on;

 

✅프로시저 (Stored Procedure)

• PL/SQL을 사용하여 작성할 수 있음
• 데이터베이스 내에서 사용자가 정의한 프로그램 유형 중 하나
• 프로시저는 데이터베이스 서버에서 실행되므로, 클라이언트 애플리케이션에서 직접 SQL 쿼리를 실행하는 것보다 성능이 우수함
• 프로시저를 사용하면 클라이언트와 데이터베이스 간의 네트워크 트래픽이 감소하고, 보안도 강화할 수 있음

 

프로시저 작성시 고려할 요소

1. 입력 매개변수: 프로시저에 전달할 입력 값
2. 출력 매개변수: 프로시저가 반환할 출력 값
3. 예외 처리: 프로시저 실행 중에 발생할 수 있는 오류 처리
4. 트랜잭션 처리: 프로시저 내에서 수행되는 작업이 트랜잭션을 구성하도록 설계
5. 보안: 프로시저가 실행되는 권한 제어

 

프로시저의 구조

CREATE [OR REPLACE] PROCEDURE procedure_name
(input_parameter1 [IN | OUT | IN OUT] data_type1,
input_parameter2 [IN | OUT | IN OUT] data_type2,
...
input_parameterN [IN | OUT | IN OUT] data_typeN)
IS
-- 선언 부분
BEGIN
-- 실행 부분
END;

CREATE [OR REPLACE] PROCEDURE : 새로운 프로시저를 생성하거나, 이미 존재하는 프로시저를 대체할 수 있음
procedure_name : 프로시저의 이름
input_parameter : 프로시저에 전달할 입력 매개변수를 나타냄
[IN | OUT | IN OUT] : 매개변수의 방향을 나타냄
data_type : 매개변수의 데이터 타입을 나타냄
선언 부분 : 변수, 상수 및 예외 처리와 관련된 선언문을 포함함
실행 부분 : 프로시저가 수행할 작업을 포함함
 
[사용 예]

CREATE OR REPLACE PROCEDURE GET_EMPLOYEE_INFO
(
  p_employee_id IN NUMBER,
  p_employee_name OUT VARCHAR2,
  p_employee_salary OUT NUMBER
)
IS
BEGIN
  SELECT employee_name, salary
  INTO p_employee_name, p_employee_salary
  FROM employees
  WHERE employee_id = p_employee_id;
END;

 

프로시저 호출 형태

1. 일반적인 프로시저 호출 형태

EXEC PROCEDURE_NAME(parameters);

2. ⇒기호 사용하여 변수명과 해당 변수의 값을 연결

EXEC PROCEDURE_NAME(parameter1 => value1,
                    parameter2 => value2, ...);

 

✅인덱스(index)

• 데이터가 위치한 장소의 정보를 가진 일종의 주소록같은 역할을 함
• 데이터의 주소(ROWID)를 가지고 있음
• PK(Primary key)인 컬럼은 자동으로 index가 적용됨

 

인덱스 사용 목적 및 장점

• 일부 SQL 쿼리(query) 성능 향상가능
• 포인터를 사용하여 행 검색 속도를 높일 수 있는 스키마 객체
  • 테이블 행에 직접 빠르게 접속 가능
  • 인덱스 경로를 사용한 신속한 자료 검색
  • 데이터베이스의 모든 블록을 다 읽지 않고, 목표 데이터가 있는 블록 주소를 찾아 당해 블록만을 메모리에 복사하여 작업을 진행함

 

인덱스 생성

-- 단일인덱스 생성
create index [인덱스명] on [테이블명(인덱스를 적용할 칼럼명)];

-- 복합인덱스 생성 (최대 32개의 컬럼까지 지정하여 생성 가능)
create index [인덱스명] on [테이블명(인덱스를 적용할 칼럼명1, 인덱스를 적용할 칼럼명2)];

 

cost

• 해당 쿼리가 동작되었을 때 소요하는 비용을 말함
• 비용이 클수록 오라클이 많은 일을 하고 있다고 생각하면됨

 
[사용 예]

select count(*) from school_location; --11873행의 데이터가 저장되어있는 테이블

 
[index없이 조회]

select * from school_location where sch_name like ('박달%'); --cost : 58

 
[index생성 후 조회]

create index school_location_sch_name_idx on school_location(sch_name);
select * from school_location where sch_name like ('박달%'); --cost : 4

→ 인덱스를 이용한 조회가 cost가 훨씬 덜 듦
 

index 삭제

drop index [인덱스명];

 

index 데이터 사전

• 인덱스 집합 테이블
• 인덱스의 정보 확인 가능

select * from user_indexes;
select * from user_indexes where index_name like 'school%';

 

✅트리거(TRIGGER)

• 데이터베이스 입력, 갱신, 삭제 등의 이벤트에 반응하여 실행되는 사용자 정의 프로시저
• 트리거는 table과는 별도로 database에 저장됨
• 트리거는 table에 관해서만 정의될 수 있음 (view는 트리거 정의 대상이 아님)
• 직접 호출하지 않아도 데이터의 변화에 자동으로 호출하여 처리함(수동으로는 실행할 수 없음)
• 어떤 동작에서 실행되는지 지정해야함
• 일반적인 사용 예에는 "입고"테이블에 새로운 제품이 들어왔을 때 그 수량을 "재고"테이블에 자동으로 반영되게 하는 경우가 있음

 

트리거 생성

create or replace trigger [트리거 명]
after
	insert or update or delete on [테이블 명]
declare
	-- 변수 및 상수 선언
begin
	-- 실행 부분
end;

 
[사용 예]
마찬가지로 프로시저이기때문에 눈으로 확인하려면 사전작업을 먼저 해준 후 실행한다.

set serveroutput on;

create or replace trigger kor_trigger
after
    insert or update or delete on sungjuk
declare
    avg_kor number;
begin
    select avg(kor) into avg_kor from sungjuk;
    dbms_output.put_line('국어평균: ' || avg_kor);
end;

→ 이후 행이 생성, 수정, 삭제 될 시 kor_trigger가 작동하게 됨
 

✅함수(function)

• 일반적인 프로그래밍에서의 함수와 동일
• 매개변수를 받아 특정 계산(작업)을 수행하고 결과를 반환하는 구조로 되어 있음

 

함수 생성

create or replace function [함수명][(매개변수)]
	-- 변수 및 상수 선언
	return [변수명]
is
	[변수 선언];
begin
	-- 실행 부분
    return [변수명];
end;

 
[사용 예]

create or replace function fn_update(v_sno number)
    return number
is
    v_kor number;
begin
    update sungjuk set kor=kor+5 where sno=v_sno;
    select kor into v_kor from sungjuk where sno=v_sno;
    return v_kor; --인상된 국어점수
end;

 
[함수 호출과 사용]

var korea number;
execute :korea := fn_update(29);
print korea;