국비교육 30일차 Oracle : DQL, TCL, DDL, DCL, Sequence(시퀀스), Synonym(동의어), PL/SQL, Function 생성
DML, DDL, DCL, TCL, DQL 이어서...
1. 데이터 질의어(DQL == Data Query Language)
- DQL은 select를 말한다.
2. 트렌잭션 질의어(TCL == Trasaction Control Language)
- TCL은 commit, rollback을 말한다.
1) Transaction(트랜잭션) 처리
-
Transaction(트랜잭션)이라 함은 관련된 일련의 DML(INSERT, UPDATE, DELETE, MERGE)로 이루어진 한꾸러미(한세트)를 말한다.
-
Transaction(트랜잭션)이라 함은 데이터베이스의 상태를 변환시키기 위하여 논리적 기능을 수행하는 하나의 작업단위를 말한다.
-
예시 : 온라인 카페에서 활동을 한다고 할 때, 글쓰기(board 테이블)(insert)를 1번하면 내포인트(member 테이블) 점수가 10점이 올라가고(update), 댓글쓰기(insert)를 1번하면 내포인트 점수가 5점이 올라간다고 한다(update).
-
insert 와 update 가 한꾸러미(한세트)로 이루어져 있는 것이다.
-
이와 같이 서로 다른 DML문이 1개의 작업을 이룰때 Transaction(트랜잭션) 처리라고 부른다.
-
Transaction(트랜잭션) 처리에서 가장 중요한 것은
모든 DML문이 성공해야만 최종적으로 모두 commit 을 해주고,
DML문중에 1개라도 실패하면 모두 rollback 을 해주어야 한다는 것이다. -
예를 들면 네이버카페(다음카페)에서 글쓰기(insert)가 성공 했다라면
그 이후에 내포인트 점수가 10점이 올라가는(update) 작업을 해주고, update 작업이 성공했다라면 commit 을 해준다. 만약에 글쓰기(insert) 또는 10점이 올라가는(update) 작업이 실패했다라면 rolllback 을 해준다. -
예를 들면 온라인 뱅킹에서 돈을 이체하는 하나의 행위
-
2) ROLLBACK TO SAVEPOINT
- 특정 시점까지 rollback을 할 수 있다.
savepoint point_1;
-- Savepoint이(가) 생성되었습니다.
delete from employees
where department_id is null;
-- 1 행 이(가) 삭제되었습니다.
rollback to savepoint point_1;
-- 롤백 완료.
-- savepoint point_1; 이 선언되어진 이후로 실행된 DML문을 rollback 시킨다. 3. DDL(Data Definition Language)
- 데이터 정의어 ==> create, drop, alter, truncate
- 여기서 중요한 것은 DDL문을 실행을 하면 자동적으로 commit; 이 되어진다. 즉, DDL문은 auto commit 이다.
1) DDL문의 auto commit
- update 후 create를 실행해서 깨진 DB, 원격지에 있는 데이터 불러와서 되돌리기
update employees set salary = 54321, email = 'dfsfsfd'
where employee_id = 100;
-- 1 행 이(가) 업데이트되었습니다.
create table tbl_temp
(no number
,name varchar2(20)
);
-- Table TBL_TEMP이(가) 생성되었습니다.
-- DDL문을 실행했으므로 자동적으로 auto commit;이 되어진다. -
update 이후에 DDL문을 사용하였기 때문에 rollback이 불가능하다.
-
원격으로 원본 데이터 받아오기
-- LE는 로컬에 있는 employee라는 지정
update employees LE set first_name = (select first_name from employees@TEACHERSERVER where employee_id = LE.employee_id)
, last_name = (select last_name from employees@TEACHERSERVER where employee_id = LE.employee_id)
, email = (select email from employees@TEACHERSERVER where employee_id = LE.employee_id)
, salary = (select salary from employees@TEACHERSERVER where employee_id = LE.employee_id);
-- 107개 행 이(가) 업데이트되었습니다.
commit;
-- 커밋 완료.2) TRUNCATE table 테이블명;
-
TRUNCATE table 테이블명;을 실행하면 테이블명 에 존재하던 모든 행(row)들을 삭제해주고, 테이블명에 해당하는 테이블은 완전초기화가 되어진다. 중요한 사실은TRUNCATE table 테이블명;은 DDL 문이기에 auto commit; 되어지므로 rollback 이 불가하다. -
delete from 테이블명;을 실행하면 이것도 테이블명 에 존재하던 모든 행(row)들을 삭제해준다. 이것은 DML문 이므로 rollback 이 가능하다. delete는 워터마크가 남아있어서 이미 지워진 행까지의 데이터를 읽어오기 때문에 비효율적이라는 단점이 있지만,
실수할 경우 데이터 복구를 위해 DML(delete) 이후에 commit을 해주고, 함부로 truncate는 사용하지 말아야 한다.
4. DCL(Data Control Language)
- SYS에서 진행
- ORAUSER1이라는 오라클 사용자 계정을 생성한 상태
1) 데이터 제어어 ==> grant, revoke
- DCL(Data Control Language)문은 자동 commit( Auto commit) 이므로 rollback 이 불가하다.
- grant는 권한을 부여하는 명령어이고, revoke는 부여한 권한을 뺐는 명령어이다.
grant create session to orauser1;
- orauser1이라는 오라클 사용자에게 오라클서버에 접속이 가능하도록 하는 권한을 부여해주는 것이다.
revoke create session from orauser1;
- orauser1이라는 오라클 사용자에게 오라클서버에 접속할 수 있는 권한을 취소하는 것이다.
2) 권한 부여
- orauser1 이라는 오라클 일반 사용자 계정을 생성
create user orauser1 identified by al default tablespace users;- 권한부여
grant connect, resource, create view, unlimited tablespace to orauser1;
-- connect, resource 등에는 여러가지 권한(create등)이 다 포함되어있는데
-- view는 없기 때문에 따로 주어야 한다.
-- unlimited tablespace 오라클 서버에 있는 용량을 무제한으로 사용할 수 있다.
-- 데이터가 무제한으로 들어와도 괜찮다.
-- Grant을(를) 성공했습니다.- 사용자계정 삭제
drop user orauser1 CASCADE; - User ORAUSER1이(가) 삭제되었습니다.
- 데이터베이스에서 객체라함은 ==> create되어진 테이블, 뷰, 인덱스, 시퀀스, 프로시저, 함수 등을 말한다.
- orauser1이라는 사용자 계정을 삭제하기 위한 전제조건은 orauser1로 생성되어진 데이터베이스 객체가 존재하지 않을 때이다. 모든 객체를 하나하나 제거하기에는 너무 불편하다. 그래서 cascade라는 옵션을 주면 orauser1 로 생성되어진 모든 객체를 먼저 제거해주고 나서 orauser1이라는 사용자 계정을 지워준다.
- 여기까지 sys에서 권한 부여, 회수 작업
5. 시퀀스(sequence)
- 쉽게 생각하면 은헹애서 발급해주는 대기번호표와 거의 유사하다. 숫자로 이루어져 있으며, 매번 정해진 증가치만큼 숫자가 증가되어지는 것이다.
1) 시퀸스(sequence) 공식
create sequence seq_ex -- seq_ex는 시퀀스(sequence) 이름이다.
start with 2 -- 첫번째 출발은 1 부터 한다.
increment by 1 -- 증가치 값 2 3 4 5 ......
maxvalue 5 -- 최대값이 5 이다.
-- nomaxvalue -- 최대값이 없는 무제한. 계속 증가시키겠다는 말이다.
minvalue 1 -- 최소값이 2 이다. cycle 이 있을때만 minvalue 에 주어진 값이 사용된다.
-- nocycle 일 경우에는 minvalue 에 주어진 값이 사용되지 않는다.
-- minvalue 숫자 에 해당하는 숫자 값은 start with 숫자 에 해당하는 숫자 값과 같든지
-- 아니면 start with 숫자 에 해당하는 숫자보다 작아야 한다.
-- nominvalue -- 최소값이 없다.
cycle -- 반복을 한다.
-- nocycle -- 반복이 없는 직진.
nocache;
-- 2(start with) 3 4 5 1(minvalue) 2 3 4 5(maxvalue) 1 2 3 4 5
-- start with는 시작할 때만 해당 숫자로 시작하는 것이고,
-- 실질적인 반복은 minvalue와 maxvalue 값을 반복하게 된다. 2) nextval
- 데이터 삽입
insert into tbl_board_test_1(boardno, subject)
values(seq_ex.nextval, '첫번째 글입니다.');3) 생성된 시퀀스 조회
select *
from user_sequences;- 마지막으로 사용된 시퀀스 값 조회
select 시퀀스명.currval
from dual; 4) 다음에 들어올 번호 조회
select last_number
from user_sequences
where sequence_name = 'SEQ_EX'; 5) 시퀀스 삭제하기
drop sequence SEQ_EX;6. Synonym(동의어)
- 테이블명이 아주 길 때 테이블명을 짧게 만들어서 사용하고자 할 때, 사용하는 것이 Synonym(동의어) 이다.
select *
from tbl_reservation_merge@teacherServer; -- 로컬 PC에서 실행
create or replace synonym tbl_bon_merge for tbl_reservation_merge@teacherServer;
-- Synonym TBL_BON_MERGE이(가) 생성되었습니다.
-- tbl_bon_merge 이 Synonym이고,
-- for 다음에 나오는 tbl_reservation_merge@teacherServer이 원래 이름이다. 1) Synonym 조회
select *
from user_synonyms;2) Synonym 삭제
drop synonym tbl_bon_merge;7. PL/SQL(Procedure Language / Structured Query Language)
- select문을 쓰지 않고도 이렇게 나오도록 만드는 것이 Procedure이다. 데이터에 같은 값을 반복적으로 부여하기 위해서 사용하며, 자바와 같은 프로그램 언어와 합칠 때 속도가 빠르다.
1) 프로시져 구문
create or replace procedure pcd_empInfo(p_employee_id IN number)
-- 변수, 파라미터, 헷갈리기 때문에 p 붙여서 적어준다.
-- IN 모드는 입력 모드, 출력은 OUT 모드
-- 오라클은 IN만 받을 수 있다. out은 자바랑 같이 사용.
-- number(5)와 같이 자리수를 넣어주면 오류이다.
is
-- 변수의 선언부
v_employee_id number(5); -- 파라미터에는 자리수를 적으면 안되지만 변수에는 자릿수가 들어와도 괜찮다.
v_ename varchar2(50); -- 자리수 적어줘야 한다.
begin
-- 실행부
select employee_id, first_name || ' ' || last_name
INTO
v_employee_id, v_ename
-- 여기서 select 되어진 정보는 반드시 변수에 담아야 한다.
-- 그 변수를 is 다음에 적어준다.
-- employee_id를 v_employee_id에 넣어준다.
-- 실행부와 변수의 선언부가 1대1로 매칭된다.
from employees
where employee_id = p_employee_id;
dbms_output.put_line( lpad('-',20,'-') );
-- java의 sysout과 유사하다.
-- '-'를 채우고 20자리에서 남은 자리를 '-'로 채워라
dbms_output.put_line( '사원번호 사원명' );
dbms_output.put_line( lpad('-',20,'-') );
dbms_output.put_line(v_employee_id || ' ' || v_ename);
end pcd_empInfo;
exec pcd_empInfo(101); -- DBMS 출력으로 조회2) 생성된 프로시저 소스 조회
select text
from user_source
where type = 'PROCEDURE' and name = 'PCD_EMPINFO';8. function 생성
1) function 생성 구문
create or replace function func_gender (p_jubun IN varchar2)
-- 파라미터는 무조건 자리수를 넣을 수 없다.
-- varchar2(13)과 같이 자리수를 쓰면 오류이다.
return varchar2 -- varchar2(10)와 같이 return 다음에도 자리수를 넣을 수 없다.
is
v_result varchar2(10); -- 여기는 자리수를 써야한다.
begin
select case when substr(p_jubun,7,1) in ('1','3') then '남' else '여' end
into
v_result
from dual;
return v_result;
end func_gender;
-- function의 사용
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, jubun AS 주민번호
, func_gender(jubun) AS 성별
from employees;2) 생성된 함수 조회
select *
from user_source
where type = 'FUNCTION' and name = 'FUNC_GENDER';와.. 오늘로 수업 들은지 1달을 꽉 채웠다... 오늘 너무 어려워서 속으로 울면서 수업들었지만..😂 30일동안 꼬박꼬박 정리한 거 나름 뿌듯하기도 하고...😁
