기린콩

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

테이블 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;

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_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

[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

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

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