ORACLE - 19.11.01

1. TABLE에 BLOB DATA 넣기

2. 임시테이블

3. 가상칼럼

 

 

 

 

 

1-1. SYS 계정 로그인

SQL*Plus: Release 11.2.0.2.0 Production on 금 11월 1 10:41:08 2019
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: sys /oracle as sysdba

onnected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

 

 

 

1-2. 실제 폴더 생성 후 IMAGE DATA 넣기

 

 

 

 

 

1-3. DIRECTORY 경로지정, 권한 부여

SQL> CREATE OR REPLACE DIRECTORY
  2  IMAGE_DIR as
  3  'D:\upload';
  
SQL> grant read,write on directory IMAGE_DIR to scott;

Grant succeeded.

 

 

 

 

 

1-4. SCOTT 계정으로 테이블 생성

CREATE TABLE IMAGE (
NAME VARCHAR2(10),
IMAGE BLOB);

 

 

 

 

 

1-5. 프로시져 실행

DECLARE
    L_DIR VARCHAR2(10) := 'IMAGE_DIR';
    L_FILE VARCHAR2(20) := 'test.PNG';
    L_BFILE BFILE;
    L_BLOB BLOB;
BEGIN
    INSERT INTO IMAGE 
    VALUES ('TEST', EMPTY_BLOB()) 
    RETURN IMAGE INTO L_BLOB;
    
    L_BFILE := BFILENAME(L_DIR,L_FILE);
    DBMS_LOB.OPEN(L_BFILE, DBMS_LOB.file_readonly);
    dbms_lob.loadfromfile(L_BLOB, L_BFILE, dbms_lob.getlength(L_BFILE));
    dbms_lob.close(L_BFILE);
    
    COMMIT;
END;
/

 

 

 

 

2-1. 임시테이블 만들기 CREATE GLOBAL TEMPORARY TABLE

CREATE  GLOBAL TEMPORARY TABLE today_test  
(  
    ID       VARCHAR2(5),  
    NAME     VARCHAR2(20),  
    TEL      NUMBER(10)  
) ON COMMIT PRESERVE ROWS; 

-- ON COMMIT PRESERVE ROWS : SESSION 단위, 다른 SESSION에서 볼 수 없음, SESSION 종료 되면 사라짐.
-- ON COMMIT DELETE ROWS : TRANSACTION 단위, COMMIT; 되는 시점에 자동으로 Data Delete

 

 

 

 

2-2. 임시테이블에 데이터 넣기

INSERT INTO     today_test(ID,NAME,TEL)  
SELECT '0001' ID, 'A' NAME, 89.34 TEL FROM DUAL  
UNION ALL  
SELECT '0002' ID, 'B' NAME, 50.34 TEL FROM DUAL  
UNION ALL  
SELECT '0003' ID, 'C' NAME, 70 TEL FROM DUAL  
UNION ALL  
SELECT '0004' ID, 'D' NAME, 30.45 TEL FROM DUAL  
;  

 

 

 

 

2-3. 조회

 

 

 

 

3-1. 가상칼럼 테이블 생성

CREATE TABLE today_test1  
(  
    ID       VARCHAR2(5),  
    NAME     VARCHAR2(20),  
    NUM1     NUMBER(10),
    NUM2     NUMBER(10), 
    TOTAL_VALUE AS (NUM1 * NUM2) VIRTUAL
); 

 

 

 

 

3-2. 데이터 생성 

INSERT INTO today_test1(ID,NAME,NUM1,NUM2) VALUES('0001','A',2,3);

 

 

 

 

3-3. 결과확인