함수 / 트리거 / 커서 / 트랜잭션의 격리 수준

Ex. 제약 조건

Persons Table

PersonID(P.K)	LastNAME	FirstNAME	Age
1	Hansen	Ola	30
2	Svendson	Tove	23
3	Pettersen	Kari	20

Orders Table

OrderID	OrderNumber(U.K)	PersonID(F.K)
1	77895	3
2	44678	3
3	22456	2
4	24562	1

V1 -> user1 : Persons.PersonID만 볼 수 있습니다.(select)
P1 -> user2 : Orders.OrderID만 볼 수 있습니다.(select)
참고) GRANT EXECUTE ON PROCEDURE ‘db_name’.’procedure_name’ TO ‘user_id’@’host’;

방법

Persons Table

use mysql;

create table Persons(PersonID int primary key, LastName varchar(45), FirstName varchar(45), Age int);
insert into Persons values(1,'Hansen','Ola',30);
insert into Persons values(2,'Svendson','Tove',23);
insert into Persons values(3,'Pettersen','Kari',20);

select * from Persons;

Orders Table

use mysql;

create table Orders(OrderID int, OrderNumber int unique, PersonID int,
constraint Persons_PersonID_Orders_PersonsID foreign key(PersonID) references Persons(PersonsID) on delete cascade);

insert into Orders values(1,77895,3);
insert into Orders values(2,44678,3);
insert into Orders values(3,22456,2);
insert into Orders valueS(4,24562,1);

select * from Orders;

조건

create view V1
as
select PersonsID from Persons;

select * from V1;

delimiter $$
create procedure P1()
begin
    select OrderID from Orders;
end $$
delimiter ;

call P1;

create user user1@'%' identified by '123456';
create user user2@'%' identified by '123456';

grant select on mysql.V1 to user1@'%';
grant execute on procedure mysql.P1 to user2@'%';

create role V1;
create role P1;

grant V1 to user1@'%';
grant P1 to user2@'%';

Untitled

Untitled (1)

함수(FUNCTION)

rand(), concat(), insert() 등 MySQL에서는 내장 함수를 지원함
이런 함수들처럼 사용자가 직접 함수를 만들 수 있도록 함
프로시저와 달리 반환값이 있어야 함
프로시저와 달리 call을 쓰지 않고 쿼리문(select, update…등) 내에서 호출됨

01. 함수 생성 / 제거

set global log_bin_trust_function_creators = 1;

drop function if exists my_rand; -- 함수 제거
delimiter $$
create function my_rand()  -- 함수 생성
    returns decimal(3, 3)  -- 리턴 타입
begin
    return rand();   -- 결과 반환
end $$
delimiter ;

select my_rand();  -- 함수 실행

참고
전체 자릿수(m)와 소수점 이하 자릿수(d)를 가진 숫자형
ex. decimal(5,2)는 전체 자릿수를 5자리로 하되, 그 중 소수점 이하를 2자리로 하겠다는 의미

Ex. 함수

SET SQL_SAFE_UPDATES=0;

drop table if exists test_table;
create table test_table(num real, num2 real default 0.0);

select my_rand();
insert into test_table values(my_rand(), 0.0);
insert into test_table values(my_rand(), 0.0);
insert into test_table values(my_rand(), 0.0);
select * from test_table;

Untitled (2)

트리거(Trigger)

테이블에 관련하여 이벤트가 발생할 때 작동시켜주는 프로시저 같은 데이터베이스 기체
테이블 관련된 이벤트라는 것이 테이블(update, delete, insert)을 수정하는 것과 관련된 이벤트를 말함

Ex. 트리거 기본 구성

트리거 동작을 테스트하기 위한 테스트 테이블 생성

drop table if exists test_table;

create table test_table(num int);
insert into test_table values(1);
insert into test_table values(2);
insert into test_table values(3);
select * from test_table;

SET SQL_SAFE_UPDATES=0;

Ex. 트리거 시작

set @before_delte = 0;
drop trigger if exists trig_delete;  -- 기본 트리거 제거
delimiter $$
create trigger trig_delete  -- 트리거 생성
before delete  -- delete 실행 전에
    on test_table  -- test_table에 대해서
    for each row  -- 각 행마다 수행
begin
    set @before_delete = @before_delete + 1;
end $$
delimiter ;

확인 1

delete from test_table where num=1;  
select @before_delete;   

delete from test_table where num=2;  
select @before_delete;   

delete from test_table where num=3;  
select @before_delete;  

Ex. 02

t1 테이블에 insert되는 경우
t2 테이블에 자동으로 데이터 insert하기

create database db01;
use db01;
create table t1(id int, name varchar(30));
create table t2(addr varchar(30));

- 트리거 시작-
drop trigger if exists trig_insert;
delimiter $$
create trigger trig_insert
before insert
    on t1
    for each row
begin
    insert into t2 values('광주');
end $$
delimiter ;

확인

insert into t1 values(1,'홍길동');
select * from t1;
select * from t2;

Ex. 03

t1에서 삭제되는 것이 t2로 자동 백업 되도록.
t1

id(int)	name(varchar30)	address(varchar30)
1	홍길동	부산
2	서길동	광주
3	남길동	서울
4	북길동	인천

id(int)	name(varchar30)	address(varchar30)	deleteDate date

방법

DELIMITER //
CREATE TRIGGER removed_name
    AFTER DELETE
    ON t1
    FOR EACH ROW
BEGIN
INSERT INTO t2
VALUES (OLD.id, OLD.name, OLD.address, CURDATE());
END
// DELIMITER ;


- 결과 확인 -
delete from t1 where id=1;
select * from t2;

delete from t1 where id=2;
select * from t2;

delete from t1 where id=3;
select * from t2;

OLD테이블
delete, update 작업이 수행되면서 삭제 또는 변경되기 전의 예전 값이 잠깐 저장된다.
예전 데이터를 참조하기 위해서는 OLD 테이블을 참조하면 된다.

Ex.

id

name

addr

t1에 1을 입력하면 t2 : 홍길동, t3 : 부산이 자동으로 생기도록.
방법

delimiter $$
create trigger name
before insert
    on t1
    for each row
begin
    insert into t2 values('홍길동');
end $$
delimiter ;

delimiter $$
create trigger addr
before insert
    on t2
    for each row
begin
    insert into t3 valueS('부산');
end $$
delimiter ;


- 결과 - 
insert into t1 values(1);

select * from t1;
select * from t2;
select * from t3;

커서(Cursor)

쿼리 결과를 한 행식 처리하기 위한 방식
SELECT 문으로 테이블 읽으면 전부 다 읽어버리는 데 cursor를 사용해서 처음부터 하나하나 읽을 수 있게 됨

1. 커서 문법

declare [커서 명] cursor for [select 문]  -- 커서 생성
open [커서 명]  -- 커서 열기
fetch [커서 명] into [변수 명]  -- 커서 열기
close [커서 명]  -- [커서 닫기]

drop procedure if exists proc_single_column_cursor;
delimiter $$
create procedure proc_single_column_cursor()
begin
    declare _name varchar(30);
    declare _name_list varchar(1000) default '';
    declare _cursor_usertbl cursor for select name from usertbl;  -- 커서 생성

    open _cursor_usertbl;  -- 커서를 연다.

    fetch _cursor_usertbl into _name;  -- 1번째 줄 읽음
    set _name_list = concat(_name_list, _name, ' ');

    fetch _cursor_usertbl into _name;  -- 2번째 줄 읽음
    set _name_list = concat(_name_list, _name, ' ');

    fetch _cursor_usertbl into _name;  -- 3번째 줄 읽음
    set _name_list = concat(_name_list, _name, ' ');

    fetch _cursor_usertbl into _name;  -- 4번째 줄 읽음
    set _name_list = concat(_name_list, _name, ' ');

    close _cursor_usertbl;  -- 커서 닫음
    select _name_list;  -- 결과 출력

end $$
delimiter ;

call proc_single_column_cursor()  == 바비킴 은지원 조관우 조용필

CONCAT 함수
둘 이상의 문자열을 입력한 순서대로 합쳐서 반환해주는 함수
CONCAT(문자열1, 문자열2 [, 문자열3 …])

트랜잭션의 격리 수준

트랜잭션의 격리 수준은 동시에 여러 트랜잭션이 처리 될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할 지 말지를 결정하는 것.
데이터 베이스의 격리 수준을 이야기 하면 항상 함께 언급되는 3가지 부정합 문제점이 있음.
이 3가지 부정합의 문제는 격리 수준의 레벨에 따라 발생할 수도 있고 발생하지 않을 수도 있음.

격리 수준	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	O	O	O
READ COMMITTED	X	O	O
REPEATABLE READ	X	X	O(InnoDB는 발생 X)
SERIALIZABLE	X	X	X

격리 수준에 따라 발생할 수 있는 문제점(부정합)

DIRTY READ
어떠한 트랜잭션에서 처리한 작업이 완료되지 않았음에도 불구하고 다른 트랜잭션에서 볼 수 있게 되는 현상
NON-REPEATABLE READ
동일한 SELECT 쿼리를 실행했을 때 항상 같은 결과를 보장해야 한다는 REPEATABLE READ 정합성에 어긋나는 현상
PHANTOM READ
한 트랜잭션내에서 동일한 쿼리를 두 번 수행했는데,
첫 번째 쿼리에서 존재하지 않던 유령(Pahntom) 레코드가 두 번째 쿼리에서 나타나는 현상

트랜잭션 격리 수준 변경 및 확인

SET autocommit = 0;  -- 자동 commit 비활성화

트랜잭션 격리 수준 확인하기

방법 01. SELECT @@GLOBAL.transaction_isolation;  -- GLOBAL 정보 확인
방법 02. SELECT @@SESSION.transaction_isolation;  -- SESSION 정보 확인

'REPEATABLE-READ'  -- 기본 값