SIGN 함수는 인자가 음수, 0, 양수인지를 판별하여 다음과 같이 값을 리턴합니다.

1) 음수인 경우 return -1 

2) 0인 경우 return 0

3) 양수인 경우 return 1

 

[Examples]

SELECT SIGN(143-1948) FROM DUAL; -- RETURN -1
-- DECODE문을 활용하여 SIGN 함수를 사용하는 경우도 있습니다. (Max값 노출 기준 등에 활용)
-- SIGN(100-200) 이 음수인 경우 100을 RETURN
-- SIGN(100-200) 이 0인 경우 0을 RETURN
-- SIGN(100-200) 이 양수인 경우 200을 RETURN
SELECT DECODE(SIGN(100-200), -1, 100, 0, 0, 1, 200) AS TEST FROM DUAL; -- RETURN 100
728x90

테이블 CRUD 입력 쿼리 조회 구문입니다.

상황에 맞추어 수정하여 사용하면 됩니다.

 

[INSERT]

   SELECT 'INSERT INTO' || TABLE_NAME || '( /*' || COMMENTS || ' */'
     FROM ALL_TAB_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL 
   SELECT CASE WHEN ROWNUM = 1 THEN '' ELSE ', ' END || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL
   SELECT ') VALUES ('
     FROM DUAL
UNION ALL
   SELECT CASE WHEN ROWNUM = 1 THEN '' ELSE ', ' END || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL
   SELECT ')'
     FROM DUAL;

 

[SELECT]

   SELECT CASE WHEN ROWNUM = 1 THEN 'SELECT ' ELSE ', ' END || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL
   SELECT 'FROM ' || TABLE_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_TAB_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME;

 

[UPDATE]

   SELECT 'UPDATE ' || TABLE_NAME || ' /* ' || COMMENTS || ' */' 
     FROM ALL_TAB_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME 
UNION ALL     
   SELECT CASE WHEN ROWNUM = 1 THEN 'SET ' ELSE ', ' END || COLUMN_NAME || ' = ' || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME
UNION ALL
   SELECT CASE WHEN ROWNUM = 1 THEN 'WHERE ' ELSE 'AND ' END || COLUMN_NAME || ' = ' || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME;

 

[DELETE]

   SELECT 'DELETE FROM ' || TABLE_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_TAB_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME;
UNION ALL
   SELECT CASE WHEN ROWNUM = 1 THEN 'WHERE ' ELSE 'AND ' || COLUMN_NAME || ' = ' || COLUMN_NAME || ' /* ' || COMMENTS || ' */'
     FROM ALL_COL_COMMENTS
    WHERE OWNER      = [스키마명]
      AND TABLE_NAME = :TABLE_NAME;
728x90

DATA_TYPE이 NUMBER인 경우 DATA_PRECISION 와 DATA_SCALE 을 확인해야 합니다.

DATA_SCALE 이 0인 경우 INTEGER로 간주할 수 있습니다.

 

· DATA_PRECISION : 전체 자릿수 (최대 38 자릿수)

· DATA_SCALE : 소수점 자리수

SELECT  A.TABLE_NAME
     ,  A.COLUMN_ID
     ,  A.COLUMN_NAME
     ,  B.COMMENTS
     ,  DATA_TYPE
     , (CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN DATA_LENGTH||''
             WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION > 0 AND DATA_SCALE > 0 THEN DATA_PRECISION||','||DATA_SCALE
             WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION > 0 THEN DATA_PRECISION||''
         END) AS DATA_LENGTH
     ,  DECODE(A.NULLABLE, 'N', '', 'O') AS NULLABLE
  FROM  ALL_TAB_COLUMNS A
 INNER
  JOIN  ALL_COL_COMMENTS B
    ON  A.OWNER       = B.OWNER
   AND  A.TABLE_NAME  = B.TABLE_NAME
   AND  A.COLUMN_NAME = B.COLUMN_NAME
 WHERE  A.OWNER       = [스키마명]
   AND  A.TABLE_NAME  = UPPER([테이블명]);

 

[References]

http://www.gurubee.net/article/68709

 

ALL_TAB_COLUMNS 테이블 DATA_TYPE 질문이요

TBL_A 라는 테이블이 있고 COL_A라는 컬럼이 있습니다.   토드에서 테이블스키마를 보면 컬럼 타입이 INTEGER로 나오는데요 ALL_TAB_COLUMNS ..

www.gurubee.net

 

728x90

ALL_TABLES

권한을 가진 모든 테이블이 조회됩니다.

 

SELECT *
  FROM ALL_TABLES
 WHERE OWNER = [스키마명]
-- AND TABLE_NAME LIKE 'M%'
   AND TABLE_NAME NOT IN ([테이블명], 'PLAN_TABLE')
;

 

[References]

https://gent.tistory.com/460

 

[Oracle] 오라클 테이블 조회 방법 (ALL_TABLES)

오라클에서 테이블 목록을 조회할 때는 오라클에서 제공하는 3가지 시스템 뷰를 많이 사용한다. 3가지 시스템 뷰는 all_tables, all_tab_comments, all_objects이며 접근 권한에 따라서 접두어(all)를 바꿔서

gent.tistory.com

 

728x90

[TEST PROCEDURE]

-- 패키지 선언
CREATE OR REPLACE PACKAGE PKG_TEST
IS     
    PROCEDURE PROC_TEST (
        RESULT_DATA OUT CURSOR_REF
    ,   P_USE_YN    IN  VARCHAR2    
    );
END;

-- 패키지 바디
CREATE OR REPLACE PACKAGE BODY PKG_TEST
IS     
    PROCEDURE PROC_TEST (
        RESULT_DATA OUT CURSOR_REF
    ,   P_USE_YN    IN  VARCHAR2    
    )
    BEGIN
        OPEN RESULT_DATA FOR
            SELECT USR_ID
                 , USR_NM
              FROM TB_EMP
             WHERE USE_YN = P_USE_YN
    END;
END;

 

[SQL Developer] 

VAR RC REFCURSOR; -- REFCURSOR 변수 선언
EXEC PKG_TEST.PROC_TEST(:RC, 'Y'); -- 프로시저 인자에 REFCURSOR 변수 할당 및 프로시저 호출
PRINT RC; -- 결과 출력

※ SQL Developer 에서 실행결과를 보려면 상단의 [보기 > DBMS 출력] 메뉴를 선택 후 [+] 아이콘을 선택하면 결과 내용을 볼 수 있습니다.

 

[DBeaver]

DECLARE 
    RESULT_DATA SYS_REFCURSOR;
    P_USE_YN VARCHAR(1) := 'Y';
    
    V_01 VARCHAR2(500);
    V_02 VARCHAR2(500);

BEGIN 
    PKG_TEST.PROC_TEST(RESULT_DATA, P_USE_YN);

    LOOP
        FETCH RESULT_DATA INTO V_01, V_02;
        EXIT WHEN RESULT_DATA%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(V_01 || ', ' || V_02);
    END LOOP;
    CLOSE RESULT_DATA;
END;

※ 리턴 결과값의 컬럼 개수와 호출 개수는 동일해야합니다.

 

[References]

https://goddaehee.tistory.com/117

 

[Oracle] PL/SQL 기초3 - 커서

 [Oracle] PL/SQL 기초3 - 커서 (CURSOR) 안녕하세요. 갓대희 입니다. 이번 포스팅은 [ PL / SQL 커서 ] 입니다. : ) 개인적으론 PL / SQL 의 꽃은 "커서"가 아닐까 생각합니다. 커서 (CURSOR) [정의]  - SQL..

goddaehee.tistory.com

 

728x90

오라클에서 제공하는 임시테이블은 크게 두가지 유형이 있습니다.

 

1) 트랜잭션 임시테이블

- 트랜잭션이 살아있는 동안에만 데이터 유지

- ON COMMIT DELETE ROWS : 임시테이블에 데이터 생성 및 수정하는 경우 데이터를 유지하지만, COMMIT을 실행 시 데이터 삭제처리 (다른 세션에서 사용중인 데이터는 사라지지 않음)

 

[생성]

CREATE GLOBAL TEMPORARY TABLE [테이블명]
(
    [컬럼명] [데이터타입], 
    ...
)
ON COMMIT DELETE ROWS;

 

2) 세션 임시테이블 

- 세션이 종료되면 데이터 삭제처리

- ON COMMIT PRESERVE ROWS : COMMIT을 실행 후 데이터 유지 (다른 세션에서 조회 불가)

 

[생성]

CREATE GLOBAL TEMPORARY TABLE [테이블명]
(
    [컬럼명] [데이터타입],
    ....
)
ON COMMIT PRESERVE ROWS;

 

 

[References]

https://rocabilly.tistory.com/261

 

[ORACLE] Temp Table ( 임시테이블 ) - CREATE GLOBAL TEMPORARY TABLE

[ORACLE] Temp Table ( 임시테이블 ) - CREATE GLOBAL TEMPORARY TABLE 1. 특징 1) 테이블 정의는 모든 세션에서 볼 수 있음  data는 세션별로 독립적, 즉 특정 세션 임시 테이블 data는 다른 session에서 접근..

rocabilly.tistory.com

 

728x90

+ Recent posts