์ ์ฉํ ๋ฐ์ดํฐ์ ์งํฉ
๋ฐ์ดํฐ ๋ฒ ์ด์ค์ ์ ์ฅ๋ ์ ๋ณด๋ ๊ฒ์, ์์ , ์ญ์ ์ ์ฉ์ด
ํตํฉ์ ์ธ ์ ๋ณด๊ด๋ฆฌ๋ฅผ ์ํ ๊ฐ์ฒด ๊ด๊ณํ ๋ฐ์ดํฐ๋ฒ ์ด์ค ๊ด๋ฆฌ ์์คํ
ORACLE Intance(๋ฉ๋ชจ๋ฆฌ+ํ๋ก์ธ์ค)์ ๋ฐ์ดํฐ๋ฒ ์ด์ค(๋ฌผ๋ฆฌ์ ๊ณต๊ฐ๋ฐ์ดํฐ)๋ก ๊ตฌ์ฑ
๋ฐ์ดํฐ๋ฒ ์ด์ค ๋ด์ ์กด์ฌํ๋ ๋
ผ๋ฆฌ์ ์ธ ์ ์ฅ ๊ตฌ์กฐ
์ค๋ผํด๊ณผ ๊ฐ์ DBMS๊ฐ ๋ฐ์ดํฐ๋ฅผ ๊ด๋ฆฌํ๋ ค๋ฉด ๋ฐ์ดํฐ๋ฅผ ์ด๋๊ฐ์ ์ ์ฅํด ๊ด๋ฆฌํด์ผ ํ๋๋ฐ,
์ด๋ฐ ๋ชฉ์ ์ ๋ฌ์ฑํ๊ธฐ ์ํด ํ์ํ ๋ชจ๋ ๋
ผ๋ฆฌ์ ์ธ ์ ์ฅ ๊ตฌ์กฐ๊ฐ ๋ฐ์ดํฐ๋ฒ ์ด์ค ๊ฐ์ฒด์ด๋ค.
๐ก Schema Object๋?
Schema๋ Schema Objects์ ๋ชจ์์ด๋ฉฐ ์ค๋ผํด์์ ์ฌ์ฉ์๋ฅผ ์คํค๋ง๋ผ๊ณ ๋ถ๋ฅธ๋ค.
์คํค๋ง ์ค๋ธ์ ํธ์๋ TABLES, INDEXES, VIEWS, SEQUENCES, SYNONYM, CLUSTERS, DATABASE LINK, PROCEDURES, FUNCTIONS, PACKAGE๋ฑ์ด ํด๋น๋๋ค.
CREATE TABLE ํ
์ด๋ธ๋ช
(
์ปฌ๋ผ๋ช
1 ํ
์ดํฐ_ํ์
[ ์นผ๋ผ์ ๊ฐ์ด ์๋ค์ด ์ค๋ ๊ฒฝ์ฐ ์ฌ์ฉ๋๋ ์นผ๋ผ์ ๊ธฐ๋ณธ๊ฐ ][์นผ๋ผ์ ์ ์ฝ์กฐ๊ฑด(NOT NULL๋ฑ)],
์ปฌ๋ผ๋ช
2 ํ
์ดํฐ_ํ์
[ ์นผ๋ผ์ ๊ฐ์ด ์๋ค์ด ์ค๋ ๊ฒฝ์ฐ ์ฌ์ฉ๋๋ ์นผ๋ผ์ ๊ธฐ๋ณธ๊ฐ ][constrinats],
...
);
ex )
create table addrbook1 (
name varchar2(10) not null,
address varchar2(50),
age number default 0
);
sql ALTER TABLE table_name ADD (column_name data_type [DEFAULT expr]โฆ
sql ALTER TABLE table_name MODIFY (column_name data_type [DEFAULT expr]โฆ)
sql ALTER TABLE table_name DROP COLUMN column_name
RENAME old_table_name TO new_table_name
DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS]
๋ทฐ (View) ์ฌ์ฉ ๋ชฉ์
์ฌ๋ฌํ
์ด๋ธ์ JOIN๊ณผ GROUP BY ๊ฐ์ ๊ฐ์ ๋ณต์กํ ์ฟผ๋ฆฌ๋ฅผ view๋ก ์ ์ฅ์์ผ๋์ผ๋ฉด ๋ค์๋ถํฐ๋ ์ ์ฅํ view์ ์ ๋ณด๋ง ๊ฐ์ ธ์ค๋ฉด ๋๋ฏ๋ก ์ฝ๊ฒ ์ข ๋ ํธ๋ฆฌํ๊ฒ ์ฌ์ฉํ ์ ์์ต๋๋ค.
๋ทฐ๋ ๋ฐ์ดํฐ ๋ณด์์์๋ ์ ๋ฆฌํ๋ค.
๋ทฐ๋ฅผ ๋ณด๋ฉด ์ปฌ๋ผ๊ณผ ๋ฐ์ดํฐ๋ง ๊ณต๊ฐ๋๋ฏ๋ก ์์ฒ ํ
์ด๋ธ์ ์ ์ ์์ต๋๋ค.
๋ทฐ (View) ์์ฑ ๋ฐ ์์
--๋ฌธ๋ฒ
CREATE OR REPLACE VIEW [์คํค๋ง.][๋ทฐ NAME] AS
SELECT๋ฌธ;
ex )
--์์
CREATE OR REPLACE VIEW v1
AS
SELECT empno
, ename
, job
, hiredate
FROM emp
;
๋ทฐ ์์ฑ ๋ฐ ์์ ์ CREATE OR REPLACE VIEW
CREATE๋ ์์ฑ, REPLACE๋ ์์ ์ธ๋ฐ CREATE OR REPLACE๋ผ๊ณ ๋ช
์ํด์คฌ์๊ฒฝ์ฐ,
ํ๊ฒ ๋ทฐ์ด๋ฆ์ด ์์๊ฒฝ์ฐ CREATE๋ฅผ ํ๊ณ ์์ ๊ฒฝ์ฐ REPLACE๋ฅผ ํจ
--๋ฌธ๋ฒ
DROP VIEW [์คํค๋ง.][๋ทฐ NAME]
ex )
--์์
DROP VIEW V1;
๋ทฐ๋ ๋ค๋ฅธ ํ
์ด๋ธ์ ์ฐธ์กฐ๋ง ํ๊ณ ์์ ๋ฟ์ด๋ฏ๋ก
๋ทฐ๋ฅผ ์ญ์ ํ๋๋ผ๋ ์ค์ ๋ทฐ๊ฐ ์ฐธ์กฐํ๊ณ ์๋ ํ
์ด๋ธ์ ์๋ ๋ฐ์ดํฐ๋ ์ญ์ ๋์ง ์์.
--๋ฌธ๋ฒ
desc [์คํค๋ง.][๋ทฐ NAME];
ex )
--์์
desc V1;
ํ
์ด๋ธ์ ์๋ ๋ฐ์ดํฐ๋ฅผ ๋น ๋ฅด๊ฒ ์ฐพ๊ธฐ ์ํ ๊ฐ์ฒด
๋ฐ์ดํฐ๊ฐ ์์นํ ์ฅ์์ ์ ๋ณด๋ฅผ ๊ฐ์ง ์ผ์ข
์ ์ฃผ์๋ก
๋ฐ์ดํฐ์ ์ฃผ์(ROWID)๋ฅผ ๊ฐ์ง๊ณ ์๋ค.
์ธ๋ฑ์ค๋ ํ ๊ฐ ์ด์์ ์ปฌ๋ผ์ผ๋ก ๋ง๋ค ์ ์์ผ๋ฉฐ, ํ๋์ ํ
์ด๋ธ์ ์ฌ๋ฌ ๊ฐ์ ์ธ๋ฑ์ค๋ฅผ ์์ฑํ ์ ์๋ค.
์ธ๋ฑ์ค๊ฐ ๋ง์ด ์์ฑ๋์ด ์์ผ๋ฉด, SELECT๋ ๋น ๋ฅผ ์ ์์ง๋ง ๋ฐ์ดํฐ ๋ณ๊ฒฝ(INSERT, UPDATE, DELETE)์ ๋๋ ค์ง ์ ์์ผ๋ฏ๋ก ์ฃผ์
CREATE [UNIQUE] INDEX [์คํค๋ง๋ช
.]์ธ๋ฑ์ค๋ช
ON [์คํค๋ง๋ช
.]ํ
์ด๋ธ๋ช
(์ปฌ๋ผ1 [, ์ปฌ๋ผ2, ์ปฌ๋ผ3, ...])
-- ๋๊ดํธ ์๋ต ๊ฐ๋ฅ
--ex )
-- ๋จ์ผ ์ธ๋ฑ์ค
CREATE INDEX emp_ix01 ON emp(hiredate)
-- ๋ณตํฉ ์ธ๋ฑ์ค (2๊ฐ ์ด์์ ์ปฌ๋ผ์ผ๋ก ๊ตฌ์ฑ, ์ต๋ 32๊ฐ ์ปฌ๋ผ๊น์ง ์ง์ ํ์ฌ ์์ฑ ๊ฐ๋ฅ)
CREATE INDEX emp_ix02 ON emp(job, deptno)
-- ์ ๋ํฌ ์ธ๋ฑ์ค ์์ฑ
CREATE UNIQUE INDEX emp_uk ON emp(empno, mgr)
--UNIQUE ์ธ๋ฑ์ค๋ฅผ ์์ฑํ๋ฉด ์ธ๋ฑ์ค๋ก ์ง์ ๋ ์ปฌ๋ผ์ ํด๋น ํ
์ด๋ธ์์ ์ ์ผํ ๊ฐ
DROP INDEX ์ธ๋ฑ์ค๋ช
๋ฐ์ดํฐ๋ฒ ์ด์ค ๊ฐ์ฒด์ ๋ํ ๋ณ์นญ์ ๋ถ์ฌํ ๊ฐ์ฒด
์คํค๋ง๋ช
(์์ ์๋ช
)์ ๋ถ์ด์ง ์๊ณ ํ
์ด๋ธ์ ์ฌ์ฉํ๊ณ ์๋ค๋ฉด ํด๋น ํ
์ด๋ธ์ ์๋
ธ๋์ ์ฌ์ฉํ๊ณ ์๋ ๊ฒ
CREATE [OR REPLACE]
SYNONYM '[์คํค๋ง๋ช
].์๋
ธ๋๋ช
'
FOR '์คํค๋ง๋ช
.๋์์ค๋ธ์ ํธ๋ช
'
--โ OR REPLACE : ์๋
ธ๋์ ์์ ํ ๋ DROP ํ์ง ์๊ณ ๊ธฐ์กด ์์ฑ ์ฟผ๋ฆฌ๋ฅผ ์ฌ์ฉํ์ฌ ์์ ํ ์ ์๋ค.
--โก PUBLIC : PUBLIC๋ฅผ ์๋ตํ๋ฉด PRIVATE๋ก ์๋
ธ๋์ด ์์ฑ๋๋ค.
--โข [์คํค๋ง๋ช
].์๋
ธ๋๋ช
: ์์ ์ ๊ณ์ ์์ ์๋
ธ๋์ ์์ฑํ ๊ฒฝ์ฐ [์คํค๋ง๋ช
]์ ์๋ต์ด ๊ฐ๋ฅ
-- ํ ๊ณ์ ์์ ์๋
ธ๋์ ์์ฑํด ์ค ๊ฒฝ์ฐ ๋ช
์ํด์ผ ํ๋ค.
--ex)
CREATE SYNONYM employees FOR hr.employees
๐ก PUBLIC๊ณผ PRIVATE์ฐจ์ด
- PUBLIC : PUBLIC ์๋ ธ๋์ผ๋ก ์์ฑํ ๊ฒฝ์ฐ ๋์ ์ค๋ธ์ ํธ์ ๊ถํ์ ๊ฐ์ง๊ณ ์๋ ๋ชจ๋ ์คํค๋ง์ ํด๋น ์๋ ธ๋์ ์ฌ์ฉํ ์ ์๋ค. ์คํค๋ง๋ง๋ค ์๋ ธ๋์ ์์ฑํด ์ค ํ์ ์์.
- PRIVATE : ์๋ ธ๋์ ์์ฑํ ์คํค๋ง์์๋ง ํด๋น ์๋ ธ๋์ ์ฌ์ฉํ ์ ์๋ค.
DROP [PUBLIC] SYNONYM ์๋
ธ๋๋ช
;
์ผ๋ จ๋ฒํธ๋ฅผ ์์ฑํด์ฃผ๋ ๊ฐ์ฒด
์ค๋ผํด์์๋ ์ปฌ๋ผ์ ๊ฐ์ ์ฆ๊ฐ์ํค๊ธฐ ์ํด์๋ MAX(์ปฌ๋ผ) + 1 ๋๋ ์ํ์ค๋ฅผ ์ฌ์ฉํ์ฌ ์ผ๋ จ๋ฒํธ๋ฅผ ๋ถ์ฌํ๋ค.
CACHE๋ฅผ ์ฌ์ฉํ ๊ฒฝ์ฐ CACHE ๋๋ CACHE "๊ฐ"์ผ๋ก ์ง์ ํ ์๊ฐ ์๋ค.
๊ฐ์ ์ง์ ํ์ง ์์ผ๋ฉด ๊ธฐ๋ณธ๊ฐ์ 20์ด๋ค. ์ง์ ํ ๊ฐ๋งํผ ์ํ์ค๋ฅผ ๋ฏธ๋ฆฌ ์์ฑํด ๋๋๋ค.
SELECT emp_seq.NEXTVAL
FROM dual
์ํ์ค๋ช
.NEXTVAL์ ์ฌ์ฉํ์ฌ ์ผ๋ จ๋ฒํธ๋ฅผ ์์ฑ
์ํ์ค๋ฅผ ์คํํ ๋๋ง๋ค ๊ฐ์ด ์ฆ๊ฐํ๋ ์ฃผ์ํด์ผ ํ๋ค. (์ฆ๊ฐ๋ ๊ฐ์ ๋ค์ ๋ด๋ฆด ์ ์๋ค)
SELECT emp_seq.NEXTVAL
FROM dual
์ํ์ค๋ช
.CURRVAL์ ์ฌ์ฉํ์ฌ ํ์ฌ ์ํ์ค ์๋ฒ์ ๊ฐ์ ธ์ฌ ์ ์๋ค.
CURRVAL์ ์ฌ๋ฌ๋ฒ ์คํํด๋ ์๋ฒ์ ์ฆ๊ฐํ์ง ์๊ณ , ํ์ฌ ์๋ฒ๋ง ๊ฐ์ ธ์จ๋ค
ALTER SEQUENCE ์ํ์ค๋ช
INCREMENT BY 2
--์ํ์ค ์ฆ๊ฐ๊ฐ์ 1 โ 2๋ก ๋ณ๊ฒฝ
ALTER SEQUENCE ์ํ์ค๋ช
MAXVALUE 99999
์ํ์ค ์ต๋๊ฐ์ 9999 โ 99999 ๋ณ๊ฒฝ
DROP SEQUENCE ์ํ์ค๋ช
ํน์ ์ฐ์ฐ์ ํ๊ณ ๊ฐ์ ๋ฐํํ๋ ๊ฐ์ฒด
CREATE OR REPLACE FUNCTION ํจ์์ด๋ฆ(ํ๋ผ๋ฏธํฐ1์ด๋ฆ ํ์
, ํ๋ผ๋ฏธํฐ2์ด๋ฆ ํ์
...)
RETURN ๋ฐํํ ํ์
IS
๋ณ์ ์ ์ธ
BEGIN
์คํ๋ฌธ ์์ฑ
RETURN ๋ฐํํ ๊ฐ
END;
-- ex )
--๋ ๊ฐ์ ์ซ์๋ฅผ ๋ฐ์ ํฉ์ ๋๋ฐฐ๋ฅผ ๋ฐํํ๋ ํจ์
CREATE OR REPLACE FUNCTION SUM_TWICE(n1 NUMBER, n2 NUMBER)
RETURN NUMBER
IS
twice_sum NUMBER := 0;
BEGIN
twice_sum := (n1 + n2)*2;
RETURN twice_sum;
END;
SELECT SUM_TWICE(12,13)
FROM DUAL;
-- ๋ชจ๋ ํจ์ ์กฐํ
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';
DROP FUNCTION ํจ์๋ช
;
ํจ์์ ๋น์ทํ์ง๋ง ๊ฐ์ ๋ฐํํ์ง ์๋ ๊ฐ์ฒด
์์ฃผ ์ฌ์ฉํ๋ SQL์ ํ๋ก์์ ๋ก ๋ง๋ ๋ค ํ์ ํ ๋๋ง๋ค ํธ์ถ,์ฌ์ฉํ์ฌ ์์
ํจ์จ์ ๋๋ฆด ์ ์๋ค.
CREATE OR REPLACE PROCEDURE ํ๋ก์์ ธ์ด๋ฆ (ํ๋ผ๋ฏธํฐ1,ํ๋ผ๋ฏธํฐ2...);
IS
๋ณ์
BEGIN
์ฟผ๋ฆฌ๋ฌธ
END ํ๋ก์์ ธ ์ด๋ฆ;
-- ex )
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
--์ํ์ฌ๋ฅผ ์ฐพ์ ์ ์์ ๋
WEHN NO_DATA_FOUND THEN
out_tier:='NO_SUMMONER_FOUND';
END GET_TIER;
ํ๋ผ๋ฏธํฐ ๊ฐ์ in,out,inout์ผ๋ก ์ด ์ธ๊ฐ์ง ์ข
๋ฅ๋ก ์์ฑํ ์ ์๋ค.
in : ์ ๋ฌ๋ ๋ฐ์ดํฐ | out : ๊ฒฐ๊ณผ๋ก ๋๊ฐ ๋ฐ์ดํฐ | inout : in๊ณผ out ๋ชจ๋ ๊ฐ๋ฅํ ๋ฐ์ดํฐ
DECLARE
์ถ๋ ฅ๋ ๋ณ์ ์ ์ธ
์คํํ ํ๋ก์์
์ถ๋ ฅ๋ฌธ(Optional)
END
-- ex ) faker์ ํฐ์ด๋ฅผ ์ถ๋ ฅํ๋ ํ๋ก์์
DECLARE
out_tier VARCHAR2(10);
BEGIN get_tier('faker',out_tier);
DBMS_OUTPUT.PUT_LINE(out_tier);
END;
DROP PROCEDURE ํ๋ก์์ ๋ช
;
์ฉ๋์ ๋ง๊ฒ ํจ์๋ ํ๋ก์์ ๋ฅผ ํ๋๋ก ๋ฌถ์ด๋์ ๊ฐ์ฒด
์ํ์ ๊ดํ ํจ์๋ค์ ๋ชจ์ ๋์ MATH๋ผ๋ ํจํค์ง๊ฐ ์๊ณ ๊ทธ ์์ ์ฐ์ฐ์ ํ๋ ํจ์ ๋๋ ํ๋ก์์ ๋ฑ์ด ์๋ ๊ฒ์ด๋ค.
ํจํค์ง๋ ์ปดํ์ผ์ ๊ฑฐ์ณ์ ์คํ๋๋ฉฐ, ๋ค๋ฅธ ํจํค์ง๋ฅผ ์ฐธ์กฐ, ์คํํ ์ ์์ต๋๋ค.
CREATE OR replace PACKAGE ํจํค์ง์ด๋ฆ
AS
FUNCTION ํจ์์ด๋ฆ(
ํ๋ผ๋ฏธํฐ1์ด๋ฆ ํ์
,
ํ๋ผ๋ฏธํฐ2์ด๋ฆ ํ์
...)
RETURN ํ์
;
PROCEDURE ํ๋ก์์ ์ด๋ฆ(
ํ๋ผ๋ฏธํฐ๋ช
1 ์
์ถ๋ ฅ ํ์
,
ํ๋ผ๋ฏธํฐ๋ช
1 ์
์ถ๋ ฅ ํ์
...)
END ํจํค์ง์ด๋ฆ;
-- ex ) TWONUMBER_MATH๋ฅผ ์กฐํ
CREATE OR replace PACKAGE twonumber_math
AS
FUNCTION Twonumber_sum(
n1 NUMBER,
n2 NUMBER)
RETURN NUMBER;
FUNCTION Twonumber_minus(
n1 NUMBER,
n2 NUMBER)
RETURN NUMBER;
END twonumber_math;
CREATE OR replace PACKAGE BODY
ํจํค์ง์ด๋ฆ
AS
FUNCTION ํจ์์ด๋ฆ( ํ๋ผ๋ฏธํฐ๋ช
1 ํ์
,
ํ๋ผ๋ฏธํฐ๋ช
2 ํ์
...)
RETURN ํ์
IS
๋ณ์ ์ ์ธ
BEGIN
์คํ๋ฌธ
RETURN ๋ฐํ๊ฐ;
END;
PROCEDURE ํ๋ก์์ ์ด๋ฆ( ํ๋ผ๋ฏธํฐ๋ช
1 ์
์ถ๋ ฅํ์
ํ์
,
ํ๋ผ๋ฏธํฐ๋ช
2 ์
์ถ๋ ฅํ์
ํ์
...)
IS
๋ณ์ ์ ์ธ
BEGIN
์คํ๋ฌธ
RETURN ๋ฐํ๊ฐ;
END;
END ํจํค์ง์ด๋ฆ;
-- ex )
CREATE OR replace PACKAGE BODY twonumber_math
AS
-- ๋ ๊ฐ ์ซ์๋ฅผ ํฉ์ณ์ฃผ๋ ํจ์
FUNCTION Twonumber_sum(n1 NUMBER,
n2 NUMBER)
RETURN NUMBER
IS
twonum_sum NUMBER := 0;
BEGIN
twonum_sum := n1 + n2;
RETURN twonum_sum;
END;
-- ๋ ๊ฐ ์ซ์๋ฅผ ๋นผ๋ ํจ์
FUNCTION Twonumber_minus(n1 NUMBER,
n2 NUMBER)
RETURN NUMBER
IS
twonum_minus NUMBER := 0;
BEGIN
twonum_minus := n1 - n2;
RETURN twonum_minus;
END;
END;
ํ๋ผ๋ฏธํฐ ๊ฐ์ in,out,inout์ผ๋ก ์ด ์ธ๊ฐ์ง ์ข
๋ฅ๋ก ์์ฑํ ์ ์๋ค.
in : ์ ๋ฌ๋ ๋ฐ์ดํฐ | out : ๊ฒฐ๊ณผ๋ก ๋๊ฐ ๋ฐ์ดํฐ | inout : in๊ณผ out ๋ชจ๋ ๊ฐ๋ฅํ ๋ฐ์ดํฐ
SELECT ํจํค์ง์ด๋ฆ.ํจ์(ํ๋ผ๋ฏธํฐ๊ฐ)
FROM ํ
์ด๋ธ์ด๋ฆ;
--OR
BEGIN
ํจํค์ง์ด๋ฆ.ํ๋ก์์ (ํ๋ผ๋ฏธํฐ);
END;
-- ex ) ๋ ์ซ์๋ฅผ ์ฐ์ฐํ๋ ํจํค์ง์ธ TWONUMBER_MATH์ ํจ์๋ค์ ์คํ
SELECT TWONUMBER_MATH.TWONUMBER_SUM(3,5) ๋์ซ์๋ํ๊ธฐ
FROM DUAL;
SELECT TWONUMBER_MATH.TWONUMBER_MINUS(3,5) ๋์ซ์๋นผ๊ธฐ
FROM DUAL;
SELECT *
FROM ALL_PROCEDURES
WHERE OBJECT_NAME = 'ํจํค์ง์ด๋ฆ';
-- ex ) TWONUMBER_MATH๋ฅผ ์กฐํ
SELECT *
FROM ALL_PROCEDURES
WHERE OBJECT_NAME = 'TWONUMBER_MATH';
DROP PACKAGE ํจํค์ง์ด๋ฆ;