My Dev Blog

<MySQL>

SHOW CREATE FUNCTION 함수명;

SHOW CREATE PROCEDURE 프로시저명;

SHOW CREATE TRIGGER 트리거명;

SHOW CREATE SEQUENCE 시퀀스명;

SHOW CREATE VIEW 뷰명;

<Oracle>

SELECT DBMS_METADATA.GET_DDL('FUNCTION', '함수명') AS FUNCTION_SRC
FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', '프로시저명') AS FUNCTION_SRC
FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('TRIGGER', '트리거명') AS FUNCTION_SRC
FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', '시퀀스명') AS FUNCTION_SRC
FROM DUAL;

SELECT DBMS_METADATA.GET_DDL('VIEW', '뷰명') AS FUNCTION_SRC
FROM DUAL;

컬럼1, 컬럼2가 PK일 때

<MySQL>

INSERT INTO 테이블명
(
    컬럼1
  , 컬럼2
  , 컬럼3
  , 컬럼4
  , 컬럼5
)
VALUES
(
    값1
  , 값2
  , 값3
  , 값4
  , 값5
)
ON DUPLICATE KEY UPDATE
(
    컬럼3 = 값3
  , 컬럼4 = 값4
  , 컬럼5 = 값5
);

<Oracle>

MERGE INTO 테이블명
USING DUAL ON (컬럼1 = 값1 AND 컬럼2 = 값2)
WHEN MATCHED THEN
UPDATE SET
(
    컬럼3 = 값3
  , 컬럼4 = 값4
  , 컬럼5 = 값5
)
WHEN NOT MATCHED THEN
INSERT
(
    컬럼1
  , 컬럼2
  , 컬럼3
  , 컬럼4
  , 컬럼5
)
VALUES
(
    값1
  , 값2
  , 값3
  , 값4
  , 값5
);

Function : 매개변수를 받아 특정 계산(작업)을 수행하고 결과를 반환하는 기능

Procedure : 특정 작업을 위한 쿼리들의 블록(함수와 거의 비슷)
장점 : 1. 하나의 요청으로 여러 SQL문을 실행(네트워크 부하를 줄일 수 있음)
           2. 네트워크 소요 시간을 줄여 성능 개선
           3. 여러 어플리케이션과 공유 가능(API처럼 제공 가능)
           4. 기능 변경이 편함
단점 : 1. 문자나 숫자열 연산에 사용하면 오히려 C, Java보다 느린 성능을 보일 수 있음
           2. 유지보수가 어려움(프로시져가 앱의 어디에 사용되는지 확인 어려움)

Trigger : 사전적 뜻은 총의 방아쇠로 총의 방아쇠를 당기는 것과 같이 어떤 이벤트의 자동 실행

View : 하나 이상의 테이블에서 원하는 모든 데이터를 선택하여 간단하게 나타낸 것
장점 : 1. 각 사용자에 따라 알맞게 데이터를 보여줄 수 있음
           2. 자주 쓰이는 쿼리문을 간단하게 사용하기 위해 사용
           3. 뷰에 나타나지 않는 데이터를 제어하여 데이터 보안이 가능
단점 : 1. 뷰의 정의를 변경할 수 없음
           2. INSERT문은 원본 테이블에서 실행해야 하며, PRIMARY KEY, NOT NULL 등의 제약사항이 위배되는 경우

              삽입이 불가한 등 많은 제약이 따름
           3. 독자적인 인덱스를 가질 수 없음

Package : 패키지는 프로시저 또는 함수 등 서브 프로그램을 하나로 묶어놓은 객체
장점 : 1. 모듈화(함수와 프로시저 등 서브 프로그램을 한곳에 모아서 사용할 수 있으므로 관리 및 사용하기가 수월)
           2. 캡슐화(선언부만 외부에 공개되고, 본문은 감춰져 있기 때문에 접근을 제어하여 오용을 방지)
           3. 성능(패키지의 서브 프로그램 호출 시 패키지 전체가 메모리에 올라가 다른 서브 프로그램 호출 시 훨씬 나은

               성능을 보임)

Index : 테이블의 검색 속도를 향상시키는 자료구조

Synonyms : 사전적 뜻은 동의어이다. 데이터베이스 객체의 NickName

Cursor : 특정 SQL 문장을 처리한 결과를 담고 있는 영역을 가리키는 일종의 포인터

<Oracle>

프로시저(PROCEDURE) 조회 쿼리

SELECT *
  FROM USER_SOURCE
 WHERE TYPE = 'PROCEDURE'
   AND NAME = '프로시저명'

함수(FUNCTION) 조회 쿼리

SELECT *
  FROM USER_SOURCE
 WHERE TYPE = 'FUNCTION'
   AND NAME = '함수명'

프로시저와 함수의 차이

프로시저(PROCEDURE) : 넓은 의미로는 어떤 업무를 수행하기 위한 절차를 뜻한다.

함수(FUNCTION) : 위의 프로시저의 각 프로세스를 수행하기 위해 필요한 기능들을 함수라 한다.

<MySQL>

SQL_MODE 확인

SHOW VARIABLES LIKE 'SQL_MODE'

SQL_MODE 초기화

SET SQL_MODE = '';

SET @변수명 = 초기값 (:= 가능)

SELECT @변수명

또는

SELECT @변수명 := @변수명 + 더해줄 값

등으로 사용 가능

SELECT NAME
     , OCCUPATION
  FROM OCCUPATIONS

SELECT NAME
     , OCCUPATION
     , ROW_NUMBER() OVER (ORDER BY NAME)
  FROM OCCUPATIONS

SELECT NAME
     , OCCUPATION
     , ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME)
  FROM OCCUPATIONS

ANIMAL_INS 테이블의 데이터 중 NAME 컬럼 값이 대소문자 구분 없이

'el'을 포함하면서 ANIMAL_TYPE이 'Dog'인 데이터의 ANIMAL_ID와 NAME을 오름차순으로 정렬

<MySQL>

UPPER(NAME) : NAME 컬럼 값의 대문자

LOWER(NAME) : NAME 컬럼 값의 소문자

LIKE : 같은지 비교

CONCAT('A', 'B', 'C') : 'A'와 'B'와 'C'를 연결

'%' : 임의의 문자열('_' : 하나의 문자)

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_INS
 WHERE UPPER(NAME) LIKE UPPER('%el%')
   AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_INS
 WHERE UPPER(NAME) LIKE CONCAT('%', UPPER('el'), '%')
   AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

<Oracle>

UPPER(NAME) : NAME 컬럼 값의 대문자

LOWER(NAME) : NAME 컬럼 값의 소문자

LIKE : 같은지 비교

'A' || 'B' || 'C' : 'A'와 'B'와 'C'를 연결

'%' : 임의의 문자열('_' : 하나의 문자)

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_INS
 WHERE UPPER(NAME) LIKE UPPER('%el%')
   AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_INS
 WHERE UPPER(NAME) LIKE '%' || UPPER('el') || '%'
   AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME