MySQL DB 공부

1. 프로시저(Procedure) 

일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한  쿼리의 집합

=================================================================================

[생성]

 

CREATE OR REPLACE PROCEDURE 프로시저이름( 파라미터1,파라미터2...);

 

IS

변수

 

BEGIN

쿼리문

 

END 프로시저 이름;

=================================================================================

[예제]

 

CREATE OR REPLACE PROCEDURE GET_TIER(in_name IN VARCHAR2, out_tier OUT VARCHAR2)

 

IS

 

BEGIN

      SELECT TIER INTO out_tier  FROM SUMMONER_TB WHERE NAME = in_name;

 

EXCEPTION

       -- 소환사를 찾을 수 없을 때

       out_tier := 'NO_SUMMONER_FOUND';

 

END GET_TIER;

=================================================================================

[조회]

 

DECLARE

출력될 변수 선언

실행할 프로시저

출력문(Optional)

END;

=================================================================================

[예제]

 

DECLARE

out_tier VARCHAR2(10); //out_tier 변수 선언

BEGIN get_tier('faker', out_tier); // faker의 out_tier를 출력

DBMS_OUTPUT.PUT_LINE(out_tier); // out_tier라는 변수값을 보여줌 

END;

=================================================================================

[수정 : 예제] //수정은  CREATE OR REPLACE 구문을 사용하면 해당 프로시저명이 있다면 수정, 없다면 생성된다.

 

CREATE OR REPLACE PROCEDURE GET_TIER(in_name IN VARCHAR2, out_tier OUT VARCHAR2)

 

IS

 

BEGIN

      SELECT TIER INTO out_tier  FROM SUMMONER_TB WHERE NAME = in_name;

 

EXCEPTION

       WHEN NO_DATA_FOUND THEN

       out_tier := 'NO_DATA_FOUND';

 

END GET_TIER;

=================================================================================

[삭제]

 DROP PROCEDURE 프로시저명;

=================================================================================

[예제]

DROP PROCEDURE GET_TIER;

=================================================================================

 

2. 함수(Function)

일반적인 프로그래밍에서의 함수와 동일, 

매개변수를 받아 특정 계산을 수행하고 결과를 반환하는 구조.

즉, 특정 동작을 수행하는 코드 부분을 의미한다.

 

[생성]

=================================================================================

CREATE OR REPLACE FUNCTION 함수이름(파라미터1이름 타입, 파라미터2이름 타입...)

 

RETURN 반환할 타입

 

IS

변수 선언

 

BEGIN 

실행문 작성

       RETURN 반환할 값

 

END;

=================================================================================

[예제] 두 개의 숫자를 받아 합의 두배를 반환하는 함수

 

CREATE OR REPLACE FUNCTION SUM_TWICE(n1 NUMBER, n2 NUMBER)

 

RETURN NUMBER

 

IS

                             twice_sum NUMBER :=0; (NUMBER 0으로 초기화)

BEGIN

                             twice_sum := (n1+n2) * 2;  (n1, n2 값을 설정)

                             RETURN twice_sum; ( 최종 공식 결과값 도출)

END;

=================================================================================

[실행]

 

SELECT SUM_TWICE(12,13) //(n1+n1)*2

FROM DUAL; //DUAL 테이블이라는 계산 결과값을 확인 할 떄 사용하는 테이블

================================================================================= 

[조회]

 

SELECT * FROM USER_OBJECTS WHERE OBJECT = 'FUNCTION';

=================================================================================

[수정:예제] // 프로시저와 같은 맥락으로 CREATE OR REPLACE 구문을 사용한다.

 

CREATE OR REPLACE FUNCTION SUM_TWICE(n1 NUMBER, n2 NUMBER)

 

RETURN NUMBER

 

IS

                             twice_sum NUMBER :=0; 

BEGIN

                             twice_sum := (n1+n2) * 4;  

                             RETURN twice_sum;

END;

=================================================================================

[삭제]

 

DROP FUNTION 함수명;

=================================================================================

[예제]

 

DROP FUNCTION SUM_TWICE;

=================================================================================

3.이벤트(Event)

 

DB의 특정 테이블에 대해 , 특정 동작을 원하는 주기마다 실행시키고 싶은 경우 Event를 등록하여 쉽게 처리할 수 있다.

=================================================================================

[예제] 

 

//특정 시간부터 시작

CREATE EVENT write_event_name

        ON SCHEDULE every 1 day

        STARTS '2020-03-01 00:05:00'

        COMMENT '매일 1회 0시 5분에 실행하는 프로시저'

        DO

                CALL write_procedure_name();

 

//특정 시간 1회 명령

CREATE EVENT IF NOT EXISTS [이벤트 이름]

        ON SCHEDULE

                  AT '2018-11-02 13:40:13'

        ON COMPLETION NOT PRESERVE

        ENABLE

        COMMENT [코멘트]

        DO 

             [수행할 명령]

END

=================================================================================

[조회] 

SELECT * FROM information_schema.events;

=================================================================================

[삭제]

 

DROP EVENT write_event_name;

=================================================================================

 

4. 테이블(Table)

 

테이블은 기본적으로 행(row)과 열(column)로 구성되어 있다.

 

행은 레코드와 튜플과 같이 특정 인스턴스에 관한 값들의 모임이다.

 

열은 속성과 필드와 같이  어떠한 개체를 표현하고 저장되는 데 사용된다.  

=================================================================================

[생성: 예제] 

 

속성/    학번      이름      학년     신장     학과

-----------------------------------------------------------

튜플/   89001     홍길동     2       170      CD

           89002     이순신     1       169      CD

.

.

.

-----------------------------------------------------------

 

CREATE TABLE '학생' (

'학번'  INT(10) NOT NULL ,

'이름'  VARCHAR(10) NOT NULL,

'학년'  VARCHAR(10) NOT NULL,

'신장'  VARCHAR(5) NOT NULL,

'학과' VARCHAR(5) NOT NULL,

PRIMARY KEY (학번));

=================================================================================

[데이터 생성]

INSERT INTO 학생 ( 학번, 이름 , 학년 , 신장 ,학과)

VALUES ( 021, 홍길동 , 2 , 182 , DB)

=================================================================================

[조회]

SELECT * FROM 학생;

SELECT 학번 , 이름 FROM 학생;

SELECT * FROM 학생 WHERE 이름 LIKE '김%';

SELECT * FROM 학생 WHERE 이름 LIKE '김%' AND 신장>= 170;

=================================================================================

[수정]

UPDATE 학생 SET 학년 = '3'  WHERE 학과 ='DB';

=================================================================================

[삭제]

DROP TABLE 학생;

=================================================================================

 

5. 뷰(View)

 

하나 또는 그 이상의 테이블을 조인하여 만든 새로운 가상 테이블. (생성된 뷰로 새로운 뷰를 만들 수 있음)

=================================================================================

[생성:예제]

 

CREATE VIEW 학생2 

SELECT 학번, 이름, 학년

FROM 학생

WHERE 학년 = '2';

=================================================================================

[조회]

SELECT * FROM 학생2;

=================================================================================

[수정] //생성과 구조를 동일 시 한다.

CREATE VIEW 학생2 

SELECT 학번, 이름, 학년, 신장

FROM 학생

WHERE 학년 = '3';

=================================================================================

[삭제]

DROP VIEW 학생2;

=================================================================================