* com.ibatis.dao.client.template.SqlMapDaoTemplate.queryForObject("service.createSeqTable");  // Query실행 시.

* Object가 이미 존재할 경우 ORA-00955

* Dynamic Query를 이용해 좀더 활용 가능할 듯.

* 아래는 일자별 시퀀스 테이블 생성과, 시퀀스 조회 함수 생성 예

* Server App 초기화 시 사용해 볼 수 있을듯.

<statement id="createSeqTable">
 CREATE TABLE NET_DAILYSEQ ( FDTSEQ CHAR(8), FSEQ NUMBER)
</statement>

<statement id="createSeqFunction">

 CREATE OR REPLACE FUNCTION GET_DAILYSEQ RETURN NUMBER IS
 PRAGMA AUTONOMOUS_TRANSACTION;
 vFseq NUMBER;
 BEGIN

    vFseq := -1;

     SELECT  
      NVL(MAX(FSEQ),0)+1 AS FSEQ into vFseq
     FROM NET_DAILYSEQ
     WHERE
  FDTSEQ = TO_CHAR(SYSDATE,'YYYYMMDD');
   
    
       MERGE INTO NET_DAILYSEQ A
       USING
  (
      SELECT NVL(MAX(FDTSEQ),TO_CHAR(SYSDATE,'YYYYMMDD')) FDTSEQ, vFSEQ as FSEQ
      FROM NET_DAILYSEQ
      WHERE FDTSEQ = TO_CHAR(SYSDATE,'YYYYMMDD')
     
  ) B
    ON (A.FDTSEQ = B.FDTSEQ)
       WHEN MATCHED THEN
     UPDATE SET A.FSEQ = B.FSEQ
       WHEN NOT MATCHED THEN
     INSERT VALUES (B.FDTSEQ, B.FSEQ);
   
    COMMIT;       

    RETURN vFseq;
   
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
      WHEN OTHERS THEN
        RAISE;
 END GET_DAILYSEQ;

</statement>

MERGE INTO 업데이트_인서트_대상_테이블 a
        USING (조인조건등의 조건 테이블)  b
        ON (a.id = b.id)
  WHEN MATCHED THEN  -- On조건이 맞을경우 실행
        UPDATE SET a.age = b.age
  WHEN NOT MATCHED THEN -- 조건이 안맞을 경우 실행
       INSERT VALUES (b.id, 'new name')

INTO - 데이터가 업데이트/삽입될 테이블
USING - 대상 데이터와 비교될 테이블/뷰
ON : INTO 테이블과 USING테이블의 비교 조건
WHEN MATCHED : ON 조건에 맞는 ROW가 있을경우 수행
WHEN NOT MATCHED : ON 조건에 맞는 ROW가 없을 때 수행

* INSERT VALUES 절에서 들어갈 수 있는 데이터(KEY)는 USING테이블에 있는 키여야하며, 키가 아닌 데이터는 들어 상수/변수가 들어갈 수 있음.

* 없으면 INSERT 있으면 UPDATE할 경우 유용.

* 사용자 생성
SQL>CONN SYSTEM/MANAGER
SQL>CREATE USER username IDENTIFIED BY password

GRANT connect, resource TO username;

* 패스변경
ALTER USER username IDENTIFIED BY newpassword

* 사용자 삭제 (CASCADE : 사용자 소유의 객체도 같이 삭제)
DROP USER scott CASCADE;

 

* 사용자 리스트 및 생성시간, UID 조회
SELECT * FROM ALL_USERS;

 

* 주요 권한 

CREATE SESSION : 데이터베이스를연결할수있는권한

CREATE ROLE : 오라클데이터베이스역할을생성할수있는권한

CREATE VIEW : 뷰의생성권한

ALTER USER : 생성한사용자의정의를변경할수있는권한

DROP USER : 생성한사용자를삭제시키는권한

 

 * 사용자에 권한 부여 (WITH ADMIN OPTION : 권한을 받은 사용자가 다른 사용자에 권한 재부여 가능)

SQL>GRANT CREATE USER, ALTER USER, DROP USER TO username WITH ADMIN OPTION.

 

* 권한 제거
SQL>REVOKE CREATE USER, ALTER USER, DROP USER FROM username

* DML권한 부여 (WITH GRANT OPTION : 권한을 받은 사용자가 다른 사용자에게 권한부여 가능)
SQL> GRANT SELECT, INSERT ON talbename TO username WITH GRANT OPTION

 

 * 사용자에게서 권한 제거

SQL>REVOKE SELECT, INSERT ON tablename FROM username

* 롤 생성,  롤에 권한부여 , 사용자에 롤 할당

SQL> CREATE ROLE rolename
SQL>GRANT create session, create table TO rolename
SQL>GRANT rolename TO [username | rolename]

ROLE_SYS_PRIVS : ROLE에 부여된 시스템권한 확인

ROLE_TAB_PRIVS : ROLE에 부여된 테이블권한 확인

USER_ROLE_PRIVS : 현재 사용자가 엑세스 할 수 있는 ROLE 확인

USER_TAB_PRIVS_MADE : 현재 사용자의 객체에 부여한 객체권한 확인

USER_TAB_PRIVS_RECD : 현재 사용자의 객체에 부여된 객체권한 확인

USER_COL_PRIVS_MADE : 현재 사용자 객체의 특정 컬럼에 부여한 객체권한 확인

USER_COL_PRIVS_RECD : 현재 사용자 객체의 특정 컬럼에 부여된 객체권한 확인

 

 

SELECT tablename FROM USER_TABLES --유저가 소유한 모든 테이블 조회.

 
SELECT * FROM USER_CONSTRAINTS --현재 사용자의 테이블 제약조건 조회..

 

 * 제약 추가/삭제

ALTER TABLE tablename ADD CONSTRAINT tbl_uk_id UNIQUE(deptno)

 

ALTER TABLE tablename DROP CONSTRAINT tbl_uk_id;

 

ALTER TABLE emp ADD CONSTRAINT tbl_ck_column CHECK (column>= 10 AND column<= 100)

 

CREATE TABLE tmp(fiddate DATE DEFAULT SYSDATE ) ;

 

SQL>ALTER TABLE emp ADD CONSTRAINT tbl_fk_fiddate FOREIGN KEY(fiddate) REFERENCES tablename(fiddate)

* 컬럼 명 변경 

ALTER TABLE tablename RENAME COLUMN fiddate TO fdtdate

* 컬럼 추가
ALTER TABLE tablename ADD( age number);

* 컬럼 속성 변경 

ALTER TABLE tablename MODIFY (colname VARCHAR2(10) NOT NULL)
* 컬럼 삭제

ALTER TABLE table_name DROP COLUMN colname

*제약 삭제 (CASCADE : 해당 키를 참조하는 외래키도 같이 삭제)
ALTER TABLE tablename DROP CONSTRAINT tbl_pk_id CASCADE

 
* 테이블 복사 생성 

CREATE TABLE tablename AS SELECT * FROM srctablename

* 테이블스페이스 변경 

ALTER TABLE tablename MOVE TABLESPACE anotherTableSpace

* 모든 제약과 함께 테이블 삭제.
DROP TABLE tablename CASCADE CONSTRAINT;

* 인덱스 생성 조건 (WHERE, JOIN절에서 자주사용되거나 NULL값을 많이 포함한 컬럼으로 테이블갱신횟수가 적은 경우)
-- 여러개의 WHERE조건이 사용되거나 테이블이 크고 자주 변경되지 않는경우
CREATE BITMAP INDEX tbl_colname_idx ON tbl(colname)
--중복값들 포함하지 않는 컬럼
CREATE UNIQUE INDEX tbl_colname_idx ON tbl(colname)
-- 중복된 값이 존재하는 컬럼
CREATE INDEX tbl_colname_idx ON tbl(colname)

DROP INDEX tbl_colname_idx
--인덱스 정보보기
SELECT * FROM USER_INDEXES
WHERE table_name= 'tablename'

* 뷰 생성
-- WITH CHECK OPTION 조건을 만족하는 데이터만 갱신 가능
CREATE OR REPLACE VIEW viewname
AS
SELECT id, name, age
FROM member
WHERE aget=10
WITH CHECK OPTION
-- WITH READ ONLY 읽기전용
CREATE OR REPLACE VIEW viewname
AS
SELECT id, name, age
FROM member
WHERE aget=10
WITH READ ONLY

--뷰 정보 조회
SELECT *  FROM USER_VIEWS

* 시퀀스 생성 (NOMAXVALUE 무한대)
CREATE SEQUENCE tbl_seq
START WITH1
INCREMENT BY1
[MAXVALUE 1234 | NOMAXVALUE]

select [tbl_seq.currval  | tbl_seq.nextval ] from dual

*시너넘 생성
CREATE SYNONYM newname FOR ownername.objectname

select trim(to_char(to_number('2568','XXXXX')+1,'XXXXX')) as hex from dual