-
ORACLE์์ DATE ํ์ ์ ์ปฌ๋ผ์ ํน์ ๋ ์ง๊ฐ์ ๋ฃ์ด์ค์ผ ํ๋ ๊ฒฝ์ฐ์๋ TO_DATE๋ฅผ ์ฌ์ฉํด์ผ ํ๋ค.
-
TO_DATE('๋ ์ง๊ฐ', 'FORMAT');
INSERT INTO TEST(DATE_COLUMN)
VALUES(TO_DATE('2019/10/15 21:31:10', 'YYYY/MM/DD HH24:MI:SS'));
---
SELECT TO_DATE(DATE_COLUMN,'HH24:MI:SS')
FROM TB1;
- ORACLE์์ TO_CHAR๋ ์ซ์๋ ๋ ์ง๋ฅผ ์ํ๋ ํํ์ ๋ฐ์ดํฐ๋ก ๋ง๋ค ์ ์๋ค.
YYYY | 4์๋ฆฌ ๋ ๋๋ฅผ ํ์ |
---|---|
MM | ์์ 2์๋ฆฌ ์ซ์๋ก ํ์, ์: 01, 12 |
DD | ์ผ์ 2์๋ฆฌ ์ซ์๋ก ํ์ |
HH24 | 24์๊ฐ ํ์์ ์๊ฐ์ ํ์ |
MI | ๋ถ์ ํ์ |
SS | ์ด๋ฅผ ํ์ |
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'); -- 2023-10-03 15:45:30
--- ์ซ์๋ฅผ ๋ฌธ์๋ก ๋ณ๊ฒฝ
TO_CHAR(๊ฐ);
--- ๊ฐ์ด ์ค์ /์คํ์ธ์ง ํ์
TO_CHAR(SYSDATE, 'HH:MI AM');
TO_CHAR(SYSDATE, 'HH:MI PM');
--- ๊ฐ์ด ๋ฌด์จ ์์ผ์ธ์ง
TO_CHAR(SYSDATE, 'DAY'); -- WEDNESDAY
--- ๊ฐ์ ๋
์์ผ, ์์ผ๊น์ง ํ์
TO_CHAR(SYSDATE, 'DL');
--- 999๋ฅผ ์
๋ ฅํ ์๋ฆฌ์๊น์ง ํ์
TO_CHAR(๊ฐ, '999')
--- ๋ฐ์ดํ(,) ์์์ ํ์
TO_CHAR(๊ฐ, '999,999');
TO_CHAR(๊ฐ, '999.99');
--- S๋์ ์์๋ฉด +, ์์๋ฉด -๊ฐ ๋์ด
TO_CHAR(๊ฐ, 'S999');
SELECT TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date
FROM orders;
---
SELECT TO_CHAR(price, '999,999.99') AS formatted_price
FROM products;
TO_DATE vs TO_CHAR |
---|
TO_DATE๋ ๋ฌธ์์ด์ ๋ ์ง ๊ฐ์ผ๋ก ๋ณํ. |
TO_CHAR๋ ๋ ์ง๋ ์ซ์๋ฅผ ๋ฌธ์์ด๋ก ๋ณํ. |
- if - else๋ ๋น์ท
SELECT DECODE(column_name, '์กฐ๊ฑด 1', '์กฐ๊ฑด 1 ์ผ๋ ๊ฐ') AS decode_result
FROM TB1;
SELECT DECODE(
column_name,
'์กฐ๊ฑด 1', '์กฐ๊ฑด 1 ์ผ๋ ๊ฐ',
'์กฐ๊ฑด 2', '์กฐ๊ฑด 2 ์ผ๋ ๊ฐ',
'์กฐ๊ฑด์ ๋ง๋ ๊ฐ์ด ์์ ๋'
) AS decode_result
FROM TB1;
SELECT DECODE(
job_id,
'MANAGER', '๊ด๋ฆฌ์',
'EMPLOYEE', '์ง์',
'์ ์ ์์'
) AS job_description
FROM employees;
-
์กฐ๊ฑด์ ๋ง์ ๊ฒฝ์ฐ์๋ THEN์ผ๋ก ๊ทธ๋ ์ง ์์ ๊ฒฝ์ฐ์๋ ELSE๋ก ์ถ๋ ฅ
-
ELSE๋ฅผ ์์ฑํ์ง ์์ ๊ฒฝ์ฐ์๋ ์กฐ๊ฑด์ ๋ง์ง ์๋ ๊ฐ๋ค์ด ๋ชจ๋ NULL๋ก ์ถ๋ ฅ
SELECT
CASE
WHEN TEXT = 'A' THEN 'A์
๋๋ค'
WHEN TEXT = 'B' THEN 'B์
๋๋ค'
WHEN TEXT = 'C' THEN 'C์
๋๋ค'
ELSE 'A,B,C๊ฐ ์๋๋๋ค'
END AS CASE_WHEN_RESULT
FROM CASEWHENTB;
-
NVL ํจ์: ์ฒซ ๋ฒ์งธ ์ธ์๊ฐ NULL์ธ ๊ฒฝ์ฐ ๋ ๋ฒ์งธ ์ธ์๋ฅผ ๋ฐํ.
-
NVL2 ํจ์: ์ฒซ ๋ฒ์งธ ์ธ์๊ฐ NULL์ธ ๊ฒฝ์ฐ ์ธ ๋ฒ์งธ ์ธ์๋ฅผ ๋ฐํ.
SELECT NVL(employee_name, 'No Name') AS employee_name
FROM employees;
---
SELECT NVL2(email, 'Has Email', 'No Email') AS email_status
FROM employees;
-
์ค๋ผํด์์ BETWEEN A AND B๋ ํน์ ๋ฒ์์ ๋ ์ง๋ฅผ ์กฐ๊ฑด์ผ๋ก ์กฐํํ ๋ ์ฃผ๋ก ์ฌ์ฉ.
-
WHERE ์ ์์ ์ปฌ๋ผ๋ช BETWEEN A AND B ์ด๋ ๊ฒ ์ฌ์ฉ.
SELECT DATECOL
FROM (SELECT TO_DATE('20190713') AS DATECOL FROM DUAL
UNION ALL
SELECT TO_DATE('20190113') AS DATECOL FROM DUAL
UNION ALL
SELECT TO_DATE('20201210') AS DATECOL FROM DUAL)
WHERE DATE1 BETWEEN '20190101' AND '20191231'
-
EXISTS๋ ์กฐํ ๊ฒฐ๊ณผ๊ฐ ์์๋๋ง ์กฐํ.
-
NOT EXISTS๋ ์กฐํ ๊ฒฐ๊ณผ๊ฐ ์์๋๋ง ์กฐํ.
SELECT *
FROM EMP
WHERE EXISTS (
SELECT EMPNO
FROM EMP
WHERE EMPNO = '7369'
)
---
SELECT *
FROM EMP
WHERE NOT EXISTS (
SELECT EMPNO
FROM EMP
WHERE EMPNO = '73691'
)
-
์ค๋ผํด์์
CONCAT, ||
๋ฅผ ์ฌ์ฉํด ๋ฌธ์์ด์ ํฉ์น ์ ์๋ค. -
CONCAT(๊ฐ, ๊ฐ)
-
2๊ฐ ์ด์์ ํฉ์น ๊ฒฝ์ฐ์๋ ||๋ฅผ ์ฌ์ฉ โ ๊ฐ || ๊ฐ || ๊ฐ
SELECT
CONCAT('A', 'B') CONCAT_ATTACH1,
CONCAT(1, 2) CONCAT_ATTACH2,
'A' || 'B' || 'C' ATTACH3,
1 || 2 || 3 || 4 ATTACH4
FROM DUAL
---
์ถ๋ ฅ ๊ฐ:
AB
12
ABC
1234
-
์ค๋ผํด์์ WITH๋ ๊ฐ์์ ํ ์ด๋ธ์ ๋ง๋ค ๋ ์ฌ์ฉ
- WITH ํ ์ด๋ธ๋ช AS ( ํ ์ด๋ธ๋ช ์ ๋ค์ด๊ฐ ๊ฐ SQL )
-
WITH ์์์๋ UNION, UNION ALL์ ์ฌ์ฉํด ํ WITH ํ ์ด๋ธ๊ณผ๋ ํฉ์น๊ธฐ ๊ฐ๋ฅ.
WITH TBL1 AS
(
SELECT 'A' AS COL1 FROM DUAL
UNION ALL SELECT 'B' FROM DUAL
UNION ALL SELECT 'C' FROM DUAL
UNION ALL SELECT 'D' FROM DUAL
),
TBL2 AS
(
SELECT 'E' AS COL1 FROM DUAL
UNION ALL
SELECT COL1 FROM TBL1 -- ํ WITH์ ํฉ์น๊ธฐ ๊ฐ๋ฅ
)
SELECT * FROM TBL2;
---
์ถ๋ ฅ ๊ฐ:
|COL1|
----
|E|
|A|
|B|
|C|
|D|
- ์ง๊ณ ๋ฐ์ดํฐ๋ฅผ ์์ฑํ๊ณ ๊ตฌ๋ณํ๊ธฐ ์ํด ์ฌ์ฉ
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_id);
---
DEPARTMENT_ID | JOB_ID | TOTAL_SALARY
------------------------------
10 | SALES | 12000
10 | HR | 6000
10 | NULL | 18000 -- 10๋ฒ ๋ถ์์ ์ด๊ธ์ฌ
20 | HR | 7000
20 | NULL | 7000 -- 20๋ฒ ๋ถ์์ ์ด๊ธ์ฌ
NULL | NULL | 25000 -- ๋ชจ๋ ๋ถ์์ ์ด๊ธ์ฌ
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department_id, job_id);
---
DEPARTMENT_ID | JOB_ID | TOTAL_SALARY
------------------------------
10 | SALES | 12000
10 | HR | 6000
20 | HR | 7000
10 | NULL | 18000 -- 10๋ฒ ๋ถ์์ ์ด๊ธ์ฌ
20 | NULL | 7000 -- 20๋ฒ ๋ถ์์ ์ด๊ธ์ฌ
NULL | HR | 13000 -- HR ์ง๋ฌด์ ์ด๊ธ์ฌ
NULL | SALES | 12000 -- SALES ์ง๋ฌด์ ์ด๊ธ์ฌ
NULL | NULL | 25000 -- ๋ชจ๋ ๋ถ์์ ์ง๋ฌด์ ์ด๊ธ์ฌ
GROUPING ํจ์๋ CUBE์ ROLLUP ์ฐ์ฐ์์ ์ฌ์ฉ๋ ๋,
ํด๋น ์ปฌ๋ผ์ด ์ง๊ณ์ ํฌํจ๋์ง ์์์ ๋ 1์ ๋ฐํํ๊ณ , ์๋ ๊ฐ์ด ์ฌ์ฉ๋์์ ๋๋ 0์ ๋ฐํ
---
SELECT department_id, job_id, SUM(salary) AS total_salary,
GROUPING(department_id) AS grp_department,
GROUPING(job_id) AS grp_job
FROM employees
GROUP BY CUBE(department_id, job_id);
---
DEPARTMENT_ID | JOB_ID | TOTAL_SALARY | GRP_DEPARTMENT | GRP_JOB
--------------------------------------------------------------
10 | SALES | 12000 | 0 | 0
10 | HR | 6000 | 0 | 0
20 | HR | 7000 | 0 | 0
10 | NULL | 18000 | 0 | 1
20 | NULL | 7000 | 0 | 1
NULL | HR | 13000 | 1 | 0
NULL | SALES | 12000 | 1 | 0
NULL | NULL | 25000 | 1 | 1