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>

SELECT A.AUTHOR_ID
     , B.AUTHOR_NAME
     , A.CATEGORY
     , SUM(A.PRICE * C.SALES) AS TOTAL_SALES
  FROM BOOK A
     , AUTHOR B
     , BOOK_SALES C
 WHERE A.AUTHOR_ID = B.AUTHOR_ID
   AND A.BOOK_ID = C.BOOK_ID
   AND DATE_FORMAT(C.SALES_DATE, '%Y-%m') = '2022-01'
 GROUP BY A.AUTHOR_ID, A.CATEGORY, B.AUTHOR_NAME
 ORDER BY A.AUTHOR_ID, A.CATEGORY DESC

<Oracle>

SELECT A.AUTHOR_ID
     , B.AUTHOR_NAME
     , A.CATEGORY
     , SUM(A.PRICE * C.SALES) AS TOTAL_SALES
  FROM BOOK A
     , AUTHOR B
     , BOOK_SALES C
 WHERE A.AUTHOR_ID = B.AUTHOR_ID
   AND A.BOOK_ID = C.BOOK_ID
   AND TO_CHAR(C.SALES_DATE, 'YYYY-MM') = '2022-01'
 GROUP BY A.AUTHOR_ID, A.CATEGORY, B.AUTHOR_NAME
 ORDER BY A.AUTHOR_ID, A.CATEGORY DESC

프로그래머스 저자 별 카테고리 별 매출액 집계하기 SQL

<MySQL>

SELECT MST.YEAR
     , MST.MONTH
     , MST.GENDER
     , COUNT(MST.GENDER) AS USERS
  FROM (
         SELECT YEAR(A.SALES_DATE) AS YEAR
              , MONTH(A.SALES_DATE) AS MONTH
              , B.GENDER
           FROM ONLINE_SALE A
              , USER_INFO B
          WHERE A.USER_ID = B.USER_ID
            AND B.GENDER IN (0, 1)
       GROUP BY YEAR(A.SALES_DATE), MONTH(A.SALES_DATE), B.USER_ID
       ) MST
GROUP BY MST.YEAR, MST.MONTH, MST.GENDER
ORDER BY MST.YEAR, MST.MONTH, MST.GENDER

<Oracle>

SELECT MST.YEAR
     , MST.MONTH
     , MST.GENDER
     , COUNT(MST.GENDER) AS USERS
  FROM (
         SELECT EXTRACT(YEAR FROM A.SALES_DATE) AS YEAR
              , EXTRACT(MONTH FROM A.SALES_DATE) AS MONTH
              , B.GENDER
           FROM ONLINE_SALE A
              , USER_INFO B
          WHERE A.USER_ID = B.USER_ID
            AND B.GENDER IN (0, 1)
       GROUP BY EXTRACT(YEAR FROM A.SALES_DATE), EXTRACT(MONTH FROM A.SALES_DATE), B.USER_ID, B.GENDER
       ) MST
GROUP BY MST.YEAR, MST.MONTH, MST.GENDER
ORDER BY MST.YEAR, MST.MONTH, MST.GENDER

MySQL에서 MONTH(날짜), DATE_FORMAT(날짜, '%c') 차이

Oracle에서 EXTRACT(MONTH FROM 날짜), TO_CHAR(날짜, 'FMMM') 차이

정렬 기준의 차이를 확인할 수 있다.

프로그래머스 년, 월, 성별 별 상품 구매 회원 수 구하기 SQL

<MySQL>

SELECT A.PRODUCT_ID
     , B.PRODUCT_NAME
     , SUM(A.AMOUNT) * B.PRICE AS TOTAL_SALES
  FROM FOOD_ORDER A
     , FOOD_PRODUCT B
 WHERE A.PRODUCT_ID = B.PRODUCT_ID
   AND DATE_FORMAT(A.PRODUCE_DATE, '%Y-%m') = '2022-05'
 GROUP BY A.PRODUCT_ID
 ORDER BY SUM(A.AMOUNT) * B.PRICE DESC, A.PRODUCT_ID

<Oracle>

SELECT A.PRODUCT_ID
     , A.PRODUCT_NAME
     , A.PRICE * B.AMOUNT_SUM AS TOTAL_SALES
  FROM FOOD_PRODUCT A
     , (
         SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT_SUM
           FROM FOOD_ORDER
          WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
       GROUP BY PRODUCT_ID
       ) B
 WHERE A.PRODUCT_ID = B.PRODUCT_ID
 ORDER BY A.PRICE * B.AMOUNT_SUM DESC, A.PRODUCT_ID

프로그래머스 5월 식품들의 총매출 조회하기 SQL

SELECT G.NODE
  FROM (
         SELECT CONCAT(N, ' Root') AS NODE
              , N
           FROM BST
          WHERE P IS NULL
      UNION ALL
         SELECT CONCAT(N, ' Inner') AS NODE
              , N
           FROM BST
          WHERE N IN (
                       SELECT P
                         FROM BST
                     )
            AND P IS NOT NULL
      UNION ALL       
         SELECT CONCAT(N, ' Leaf') AS NODE
              , N
           FROM BST
          WHERE N NOT IN (
                           SELECT DISTINCT P
                             FROM BST
                            WHERE P IS NOT NULL
                         )
       ) G
ORDER BY G.N

해커랭크 Binary Tree Nodes SQL