[교육] SQL초보에서실전전문가까지 ORACLE - 6

--// MERGE SAMPLES

CREATE TABLE TEST

AS 

SELECT * FROM EMP WHERE DEPTNO=10; 

SELECT * FROM TEST;

UPDATE TEST 

SET SAL = SAL+1000;

SELECT * FROM TEST;

SELECT * FROM EMP;

MERGE INTO TEST T

USING EMP E

ON (T.EMPNO = E.EMPNO)

WHEN MATCHED THEN

UPDATE SET T.SAL = E.SAL

WHEN NOT MATCHED THEN

INSERT VALUES(E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO);

DROP TABLE TEST;

--// SELECT INTO 구분의 차이

--// SQL SERVER

SELECT * INTO TEST FROM EMP WHERE DEPTNO=10;

--// ORACLE 

CREATE TABLE TEST

AS 

SELECT * FROM EMP WHERE DEPTNO=10;

UPDATE EMP

SET ENAME='MILLER'

WHERE EMPNO=7979;

--// 유일하지 않은 키로 조인했을 경우

MERGE INTO TEST T

USING EMP E

ON (T.ENAME = E.ENAME)

WHEN MATCHED THEN

UPDATE SET T.SAL = E.SAL

WHEN NOT MATCHED THEN

INSERT VALUES(E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO);

--// ERROR ORA-30926: unable to get a stable set of rows in the source tables

--// 결론은 유일한 키가 될 수 있는 값으로만 JOIN 조건이 될 수 있다.

SELECT * FROM TEST;

--// 임시테이블 생성

CREATE GLOBAL TEMPORARY TABLE TEMP (

NO NUMBER(10)

);

CREATE TABLE ADDRESS (

ID NUMBER(3),

NAME VARCHAR2(50),

ADDR VARCHAR2(100),

PHONE VARCHAR2(30),

EMAIL VARCHAR2(100));

SELECT * FROM TAB;

SELECT * FROM ADDRESS;

INSERT INTO ADDRESS VALUES(1, 'HGDONG', 'SEOUL', '123-12345', 'ZETUS@NAVER.COM');

--// 서브쿼리를 이용한 테이블 생성

--// SCHEMA만 복사해서 테이블 생성

CREATE TABLE ADDRESS_SEC

AS

SELECT * FROM ADDRESS WHERE 1=2;

DESC ADDRESS_SEC;

--// 컬럼 추가

ALTER TABLE ADDRESS_SEC ADD BIRTH DATE;

DESC ADDRESS_SEC;

--// DEFAULT 옵션 사용 

ALTER TABLE ADDRESS_SEC ADD COMMENTS VARCHAR2(200) DEFAULT 'NO COMMENT';

DESC ADDRESS_SEC;

INSERT INTO ADDRESS_SEC (ID) VALUES (1);

SELECT * FROM ADDRESS_SEC;

--// 칼럼 삭제

ALTER TABLE ADDRESS_SEC DROP COLUMN COMMENTS;

--// 테이블명 변경

RENAME ADDRESS_SEC TO ADDRESS_TRD;

DESC ADDRESS_TRD;

DROP TABLE ADDRESS_TRD;

SELECT * FROM ADDRESS_TRD;

SHOW RECYCLEBIN; -- 현버전에서는 먹히지 않음

• 주석 추가 방법

COMMENT ON [TABLE|COLUMN] [TABLE_NAME|TABLE_NAME.COLUMN_NAME]

IS 'CONTENT OF COMMENT';

COMMENT ON TABLE ADDRESS

IS 'ART OF MISSION';

• 데이터 사전의 종류 user_

SELECT * FROM user_tab_comments --// 대소문자 구분 

WHERE TABLE_NAME='ADDRESS'; 

user_

all_

dba

user_object; 테이블 전체 세부 속성(이것을 주로 사용함)

user_catalog; 테이블 간단한 속성 

• 삭제테이블 복구

select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

DEPT                           TABLE             

EMP                            TABLE             

BONUS                          TABLE             

SALGRADE                       TABLE             

TOAD_PLAN_TABLE                TABLE             

SAL                            TABLE             

DEPTNO                         TABLE             

SALES                          TABLE             

SALES_DATA                     TABLE             

TEMP                           TABLE             

BIN$x7ExPPoqDxfgQAB/AQAkPw==$0 TABLE 삭제된 테이블  

TEST                           TABLE             

ADDRESS                        TABLE             

BIN$x7CI2MV1fVDgQAB/AQAnSA==$0 TABLE  삭제된 테이블

flashback table ADDRESS_TRD  to before drop;

이 명령어로 삭제된 테이블을 복구 가능하다.

--// 휴지통 보기

show recyclebin;

--// 휴지통 비우기

purge recyclebin;

drop table table_name purge;

UNIQUE 고유키 제약조건 컬럼에는 NULL 이 허용된다.

CHECK 제약조건 값에 NULL 이 허용된다.

• 제약조건을 추가하여 테이블 생성

CREATE TABLE SUBJECT 

(

    SUBNO NUMBER(5)

        CONSTRAINT SUBJECT_NO_PK PRIMARY KEY

        DEFERRABLE INITIALLY DEFERRED -- INSERT 할 때 건건이 제약조건 확인할 지 아니면 COMMIT 할 때 체크할 건지 옵션 (DEFERRED 이면 COMMIT 할 때 체크한다는 의미임, 기본값은 IMMEDIATE 라고 함)

        USING INDEX TABLESPACE USERS,

    SUBNAME VARCHAR(20)

        CONSTRAINT SUBJECT_NAME_NN NOT NULL,

    TERM VARCHAR2(1)

        CONSTRAINT SUBJECT_TERM_CK CHECK (TERM IN ('1','2')),

    TYPE    VARCHAR2(1)

);

• 제약조회 활성/비활성

SELECT CONSTRAINT_NAME, STATUS

FROM user_constraints

WHERE TABLE_NAME = 'SUBJECT'

CONSTRAINT_NAME                STATUS  

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

SUBJECT_NAME_NN                ENABLED 

SUBJECT_TERM_CK                ENABLED 

SUBJECT_NO_PK                  ENABLED 

--// 비활성화

ALTER TABLE SUBJECT DISABLE CONSTRAINT SUBJECT_NAME_NN;

SELECT CONSTRAINT_NAME, STATUS

FROM user_constraints

WHERE TABLE_NAME = 'SUBJECT'

CONSTRAINT_NAME                STATUS  

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

SUBJECT_NAME_NN                DISABLED

SUBJECT_TERM_CK                ENABLED 

SUBJECT_NO_PK                  ENABLED 

INSERT INTO SUBJECT VALUES ('2', 'JAVA2', '1', 'M');

1 row created.

INSERT INTO SUBJECT VALUES ('1', '', '1', 'M');

1 row created.

SELECT * FROM SUBJECT;

     SUBNO SUBNAME              TERM TYPE

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

         4 JAVA2                1    M   

         2 JAVA2                1    M   

         1                      1    M 

         

         

--// 활성화 (기본)

ALTER TABLE SUBJECT ENABLE CONSTRAINT SUBJECT_NAME_NN;

ORA-02293: cannot validate (SCOTT.SUBJECT_NAME_NN) - check constraint violated

--// 활성화 (VALIDATION 체크 무시)

ALTER TABLE SUBJECT ENABLE NOVALIDATE CONSTRAINT SUBJECT_NAME_NN;

Table altered.

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

--// VIEW

CREATE VIEW EMP_VIEW AS

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, COMM, DEPTNO

FROM EMP;

ORA-01031: insufficient privileges

CONN /AS DBA

SQL> conn /as sysdba

Connected.

SQL> grant create view to scott;

Grant succeeded.

CREATE VIEW EMP_VIEW AS

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, COMM, DEPTNO

FROM EMP;

SELECT * FROM EMP_VIEW;

--// VIEW도 업데이트 가능하다.

UPDATE EMP_VIEW 

SET COMM = 1000;

SELECT * FROM EMP_VIEW;

SELECT * FROM EMP;

--// INSERT도 가능하다.

INSERT INTO EMP_VIEW 

VALUES(1022, 'KKKKK', 'LAWYER', 7566, TO_DATE('2011-11-11', 'YYYY-MM-DD'), 90,20);

ALTER TABLE EMP ADD CONSTRAINT EMP_SAL_NN NOT NULL FOR SAL;

CREATE VIEW V_EMP

AS 

SELECT DEPTNO, SUM(SAL) AS SUM_SAL, AVG(SAL) AS AVG_SAL

FROM EMP

GROUP BY DEPTNO;

SELECT * FROM V_EMP;

INSERT INTO EMP VALUES ( 1009, 'MMMM', 'LAWYER', 1022, TO_DATE('2011-11-11', 'YYYY-MM-DD'), 8000, 90,20);

SELECT * FROM V_EMP;

    DEPTNO    SUM_SAL    AVG_SAL

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

        30      38890 4321.11111

        20      18875 3145.83333

        40       7000       3500

        10      17750     4437.5

INSERT INTO EMP VALUES ( 1001, 'SSSS', 'LAWYER', 1009, TO_DATE('2011-11-11', 'YYYY-MM-DD'), 6000, 90,20);

SELECT * FROM V_EMP;

    DEPTNO    SUM_SAL    AVG_SAL

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

        30      38890 4321.11111

        20      24875 3553.57143

        40       7000       3500

        10      17750     4437.5

        

--// 생성한 VIEW 조회하기

select * from user_views;

CREATE OR REPLACE VIEW V_ADDR

AS 

SELECT *

FROM ADDRESS_TRD

View created.

DROP VIEW V_ADDR

View dropped.

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

SQL> show user;

USER is "SYS"

SQL> create role basic;

Role created.

SQL> create role basic_pass

  2  identified by pass;

Role created.

SQL> grant resource, connect to basic;

Grant succeeded.

SQL> grant all to basic_pass;

grant all to basic_pass

      *

ERROR at line 1:

ORA-01919: role 'ALL' does not exist

SQL> create user vvv identified by vvv;

User created.

SQL> grant basic to vvv;

Grant succeeded.

SQL> conn vvv/vvv

Connected.

SQL> select * from tab;

no rows selected

SQL>

SQL>

SQL>

SQL> role_tab_privs , user_role_privs