IT당~얼쑤가 아니네

CREATE TABLE 테이블명 (컬럼명1,컬럼명2) AS

SELECT 컬럼명1,컬럼명2

FROM 테이블명

WHERE 조건

조회한 후 인서트시 컬럼명을 변경할때 사용됨

오라클 SH 스키마 필요

SELECT
    CUST_ID, YYYYMM, SUM("01") AS "1일", SUM("02") AS "2일", SUM("03") AS "3일", SUM("04") AS "4일", SUM("05") AS "5일", SUM("06") AS "6일",
    SUM("07") AS "7일", SUM("08") AS "8일", SUM("09") AS "9일", SUM("10") AS "10일", SUM("11") AS "11일", SUM("12") AS "12일",
    SUM("13") AS "13일", SUM("14") AS "14일", SUM("15") AS "15일", SUM("16") AS "16일", SUM("17") AS "17일", SUM("18") AS "18일",
    SUM("19") AS "19일", SUM("20") AS "20일", SUM("21") AS "21일", SUM("22") AS "22일", SUM("23") AS "23일", SUM("24") AS "24일",
    SUM("25") AS "25일", SUM("26") AS "26일", SUM("27") AS "27일", SUM("28") AS "28일", SUM("29") AS "29일", SUM("30") AS "30일", SUM("31") AS "31일",
    SUM(AMOUNT_SOLD) AS "총계"
FROM(
        SELECT CUST_ID, TO_CHAR(TIME_ID,'YYYY-MM') AS YYYYMM, TO_CHAR(TIME_ID,'DD') AS DD, AMOUNT_SOLD
        , DECODE(TO_CHAR(TIME_ID,'DD'), '01', AMOUNT_SOLD, 0) AS "01"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '02', AMOUNT_SOLD, 0) AS "02"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '03', AMOUNT_SOLD, 0) AS "03"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '04', AMOUNT_SOLD, 0) AS "04"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '05', AMOUNT_SOLD, 0) AS "05"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '06', AMOUNT_SOLD, 0) AS "06"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '07', AMOUNT_SOLD, 0) AS "07"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '08', AMOUNT_SOLD, 0) AS "08"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '09', AMOUNT_SOLD, 0) AS "09"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '10', AMOUNT_SOLD, 0) AS "10"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '11', AMOUNT_SOLD, 0) AS "11"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '12', AMOUNT_SOLD, 0) AS "12"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '13', AMOUNT_SOLD, 0) AS "13"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '14', AMOUNT_SOLD, 0) AS "14"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '15', AMOUNT_SOLD, 0) AS "15"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '16', AMOUNT_SOLD, 0) AS "16"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '17', AMOUNT_SOLD, 0) AS "17"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '18', AMOUNT_SOLD, 0) AS "18"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '19', AMOUNT_SOLD, 0) AS "19"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '20', AMOUNT_SOLD, 0) AS "20"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '21', AMOUNT_SOLD, 0) AS "21"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '22', AMOUNT_SOLD, 0) AS "22"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '23', AMOUNT_SOLD, 0) AS "23"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '24', AMOUNT_SOLD, 0) AS "24"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '25', AMOUNT_SOLD, 0) AS "25"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '26', AMOUNT_SOLD, 0) AS "26"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '27', AMOUNT_SOLD, 0) AS "27"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '28', AMOUNT_SOLD, 0) AS "28"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '29', AMOUNT_SOLD, 0) AS "29"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '30', AMOUNT_SOLD, 0) AS "30"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '31', AMOUNT_SOLD, 0) AS "31"
        FROM SH.SALES
        --WHERE TO_CHAR(TIME_ID,'YYYYMM') = '199801'
)GROUP BY CUST_ID, YYYYMM
ORDER BY CUST_ID ASC, YYYYMM ASC

결과

SH 스키마 필요 // 년도별 월별 통계 //2014.10.29 수정

SELECT
    CUST_ID, YYYY, SUM("01") AS "1월", SUM("02") AS "2월", SUM("03") AS "3월", SUM("04") AS "4월", SUM("05") AS "5월", SUM("06") AS "6월",
    SUM("07") AS "7월", SUM("08") AS "8월", SUM("09") AS "9월", SUM("10") AS "10월", SUM("11") AS "11월", SUM("12") AS "12월",
    SUM(AMOUNT_SOLD) AS "총계"
FROM(
        SELECT CUST_ID, TO_CHAR(TIME_ID,'YYYY') AS YYYY, TO_CHAR(TIME_ID,'MM') AS MM, TO_CHAR(TIME_ID,'DD') AS DD, AMOUNT_SOLD
        , DECODE(TO_CHAR(TIME_ID,'MM'), '01', AMOUNT_SOLD, 0) AS "01"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '02', AMOUNT_SOLD, 0) AS "02"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '03', AMOUNT_SOLD, 0) AS "03"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '04', AMOUNT_SOLD, 0) AS "04"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '05', AMOUNT_SOLD, 0) AS "05"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '06', AMOUNT_SOLD, 0) AS "06"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '07', AMOUNT_SOLD, 0) AS "07"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '08', AMOUNT_SOLD, 0) AS "08"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '09', AMOUNT_SOLD, 0) AS "09"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '10', AMOUNT_SOLD, 0) AS "10"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '11', AMOUNT_SOLD, 0) AS "11"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '12', AMOUNT_SOLD, 0) AS "12"
        FROM SH.SALES
--        WHERE TO_CHAR(TIME_ID,'YYYY') BETWEEN 1999 AND 2000 --특정년도 범위

--        AND TO_CHAR(TIME_ID,'YYYYMM') = '199801'  --특정년월
--        AND TO_CHAR(TIME_ID,'MM') = 05 --특정달만
)GROUP BY CUST_ID, YYYY
ORDER BY CUST_ID ASC, YYYY ASC

결과

1. 세션별 CPU Time, Memory 사용량
 
select s.sid, s.serial#, p.spid as "os pid", s.username, s.module, s.sql_id, event, seconds_in_wait,
st.value/100 as "cpu sec",
round(pga_used_mem/1024/1024) "pga_tot(mb)",
round(pga_used_mem/1024/1024) "pga_per_sess(mb)"
from v$sesstat st, v$statname sn, v$session s, v$process p
where sn.name = 'CPU used by this session' -- cpu
and st.statistic# = sn.statistic#
and st.sid = s.sid
and s.paddr = p.addr
and s.last_call_et < 1800 -- active within last 1/2 hour
and s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
order by st.value

 
 
 
 
2. Client별 Memory 사용량
 
select machine,status,count(*) cnt, 
       round(sum(pga_used_mem)/1024/1024) "pga_tot(mb)",
       round(sum(pga_used_mem)/count(*)/1024/1024) "pga_per_sess(mb)"
from v$session s, v$process p
where 1=1
--and s.status='active'
and s.paddr=p.addr
and type <> 'BACKGROUND'
group by machine,status
order by 1

CREATE OR REPLACE PROCEDURE SP_USER_CONTROL(   
    SEL_MODE IN VARCHAR2,
    P_X_ID IN X_TEMP_TABLE.X_ID%TYPE,
    P_X_PW IN X_TEMP_TABLE.X_PW%TYPE,
    ERROR_CODE OUT VARCHAR2, --에러코드 리턴
    ERROR_MSG OUT VARCHAR2 --에러메시지
)IS
    V_ERROR_CODE VARCHAR2(30); --예외코드 변수
    V_ERROR_MSG VARCHAR2(1000); --예외메시지 변수
    V_CNT NUMBER;
    CALLEXT EXCEPTION; --사용자 예외 선언
BEGIN   
    IF SEL_MODE='1' THEN --INSERT
        DBMS_OUTPUT.PUT_LINE('INSERT');
        BEGIN
            INSERT INTO X_TEMP_TABLE VALUES(P_X_ID,P_X_PW);
            EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
                            V_ERROR_CODE:='E001';
                            V_ERROR_MSG:='중복된 데이터로 인해 입력에 실패하엿습니다.';
                            RAISE CALLEXT; --사용자 예외 던지기
                      WHEN OTHERS THEN
                            RAISE; --예외 던지기
        END;
    ELSIF SEL_MODE='2' THEN --UPDATE
        DBMS_OUTPUT.PUT_LINE('UPDATE');
        BEGIN           
            SELECT COUNT(*) INTO V_CNT FROM X_TEMP_TABLE WHERE X_ID = P_X_ID;
            IF V_CNT > 0 THEN
                UPDATE X_TEMP_TABLE SET X_ID = P_X_ID, X_PW = P_X_PW WHERE X_ID = P_X_ID;
            ELSE
               V_ERROR_CODE:='E002';
               V_ERROR_MSG:='대상 데이터가 검색되지 않앗습니다.';
               RAISE CALLEXT; --사용자 예외 던지기
            END IF;
           
            EXCEPTION WHEN OTHERS THEN
                      RAISE; --예외 던지기
        END;       
    ELSIF SEL_MODE='3' THEN --DELETE
        DBMS_OUTPUT.PUT_LINE('DELETE');
        BEGIN           
            SELECT COUNT(*) INTO V_CNT FROM X_TEMP_TABLE WHERE X_ID = P_X_ID;
            IF V_CNT > 0 THEN
                DELETE FROM X_TEMP_TABLE WHERE X_ID = P_X_ID;
            ELSE
               V_ERROR_CODE:='E002';
               V_ERROR_MSG:='대상 데이터가 검색되지 않앗습니다.';
               RAISE CALLEXT; --사용자 예외 던지기
            END IF;
           
            EXCEPTION WHEN OTHERS THEN
                      RAISE; --예외 던지기
        END;
    ELSIF SEL_MODE='4' THEN --TRUNCATE
        DBMS_OUTPUT.PUT_LINE('TRUNCATE');
        BEGIN           
            EXECUTE IMMEDIATE 'TRUNCATE TABLE X_TEMP_TABLE';
            EXCEPTION WHEN OTHERS THEN
                      RAISE; --예외 던지기
        END;
    ELSE
        RAISE CALLEXT;
    END IF;
   
    ERROR_CODE:='E000';
    ERROR_MSG:='정상적으로 처리되엇습니다.';
    COMMIT;
   
    EXCEPTION
        WHEN CALLEXT THEN --사용자 예외
             ERROR_CODE:=V_ERROR_CODE;
             ERROR_MSG:=V_ERROR_MSG;
             ROLLBACK;
        WHEN NO_DATA_FOUND THEN
             ERROR_CODE:='E002';
             ERROR_MSG:='데이터를 찾을수가 없습니다.';
             ROLLBACK;
        WHEN OTHERS THEN
             ERROR_CODE:='E004';
             ERROR_MSG:=SQLERRM || SQLCODE;   
             ROLLBACK;
END SP_USER_CONTROL;

java.sql.SQLException: 논리적 핸들이 더 이상 유효하지 않습니다

위와 같은 오류 발생시 제일먼저 확인해야할것은 JDBC CONNECTION 관리가 제대로 되고있는지부터 확인해야한다.

참조  : http://www.okjsp.pe.kr/seq/50687

 
CREATE OR REPLACE FUNCTION FN_GET_SPLIT(
    IN_STR         IN  VARCHAR2,    
    IN_LEVEL       IN  INT,         
    IN_DELIMETER   IN  VARCHAR2,
    IN_DEFAULT_VAL IN  VARCHAR2     
)
RETURN VARCHAR2

IS
    V_RETURN              VARCHAR2(200);    
    STRVALUE              VARCHAR2(4000) := IN_STR; 
    DEFAULT_RETURN_VAL    VARCHAR2(4000) := IN_DEFAULT_VAL;
    IDX INT;    
    ILEVEL INT := 0;

BEGIN

    V_RETURN := '';

    -- 문자열이 없으면 기본 리턴값 반환 후 종료
    IF NVL(STRVALUE,'NO_STRING') = 'NO_STRING' THEN
        RETURN DEFAULT_RETURN_VAL;
    END IF;
        
    LOOP
        --구분자 인덱스 확인
        IDX := INSTR(STRVALUE, IN_DELIMETER);
        
        IF IDX > 0 THEN --구분자로 문자를 찾은경우
                                    
            ILEVEL := ILEVEL + 1;
            
            -- 현재 레벨이 원하는 레벨이면 현재 문자열 반환 AND 레벨이 -1인경우는 마지막까지 LOOP
            IF ILEVEL = IN_LEVEL AND IN_LEVEL != -1 THEN
                V_RETURN := SUBSTR(STRVALUE, 1, IDX-1);
                EXIT;
            END IF;
                        
            STRVALUE := SUBSTR(STRVALUE, IDX + LENGTH(IN_DELIMETER));
        ELSE  -- 구분자가 없을 경우, 문자열을 그대로 반환
        
            IF ILEVEL = 0 THEN
                --구분자가 포함이 안되었지만 레벨이 1인경우 문자 그대로 반환
                IF IN_LEVEL = 1 THEN
                    V_RETURN := STRVALUE;
                ELSE
                    V_RETURN := '';
                END IF;
            ELSE
                -- 마지막 문자열일 경우
                ILEVEL := ILEVEL + 1;
                
                -- 마지막을 원하는 경우 마지막 문자열 반환 / -1은 레벨을 모를경우 구분자의 마지막 문자열 반환
                IF ILEVEL = IN_LEVEL OR IN_LEVEL = -1 THEN
                        V_RETURN := STRVALUE;
                ELSE
                    -- 원하는 레벨의 값이 없을 경우, 공백 반환
                    V_RETURN := '';        
                END IF;
            END IF;
            
            EXIT; --반복 탈출문
            
        END IF;
    END LOOP;    
    
    --최종결과 리턴
    RETURN NVL(V_RETURN, DEFAULT_RETURN_VAL);
        
    EXCEPTION
         WHEN OTHERS THEN
              RETURN SQLERRM;
END FN_GET_SPLIT;

connecty by 사용 전

WITH A AS(
    SELECT 'A' AS CODE, '' AS P_CODE , '인사팀' AS TEAM_NAME FROM DUAL
    UNION ALL
    SELECT 'B' AS CODE, '' AS P_CODE , '재무팀' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'C' AS CODE, '' AS P_CODE , '삽질팀' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'D' AS CODE, 'A' AS P_CODE , '김우진' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'E' AS CODE, 'B' AS P_CODE , '이동수' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'F' AS CODE, 'C' AS P_CODE , '이진수' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'G' AS CODE, 'C' AS P_CODE , '조둔서' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'H' AS CODE, 'B' AS P_CODE , '개뿔딱' AS TEAM_NAME  FROM DUAL
)
SELECT A.*
FROM   A;

connecty by 사용 후
WITH A AS(
    SELECT 'A' AS CODE, '' AS P_CODE , '인사팀' AS TEAM_NAME FROM DUAL
    UNION ALL
    SELECT 'B' AS CODE, '' AS P_CODE , '재무팀' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'C' AS CODE, '' AS P_CODE , '삽질팀' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'D' AS CODE, 'A' AS P_CODE , '김우진' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'E' AS CODE, 'B' AS P_CODE , '이동수' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'F' AS CODE, 'C' AS P_CODE , '이진수' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'G' AS CODE, 'C' AS P_CODE , '조둔서' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'H' AS CODE, 'B' AS P_CODE , '개뿔딱' AS TEAM_NAME  FROM DUAL
)
SELECT CODE,P_CODE,LPAD(' ',  8 * (LEVEL-1))||TEAM_NAME
FROM   A
START WITH P_CODE IS NULL
CONNECT BY  PRIOR CODE = P_CODE;

에러유형

VARCHAR2

VARCHAR2(-1)

VARCHAR2(50)

조치 : varchar2의 크기를 1..32767 범위안에 잇어야함.

인덱스 생성

CREATE [ UNIQUE ] INDEX MAPPING_INFO_IDX_01 ON MAPPING_INFO(REG_NO ASC, DOC_TYPE ASC ,ORGN_ID ASC);

인덱스 테이블 스페이스 변경

ALTER INDEX MAPPING_INFO_IDX_01 REBUILD TABLESPACE DATA;

인덱스 정보 확인

SELECT * FROM USER_INDEXES WHERE INDEX_NAME = 'MAPPING_INFO_IDX_01 ';

인덱스 제거

DROP INDEX MAPPING_INFO_IDX_01;

원본 : http://radiocom.kunsan.ac.kr/lecture/oracle/variable/serveroutput.html

프로시저 결과를 화면에 출력하고 싶을때 사용

SQL> show serveroutput
serveroutput OFF
SQL> set serveroutput on

----테스트
SQL> begin
  2   dbms_output.put_line('확인');
  3  end;
  4  /
확인   >  출력되면 성공

PL/SQL procedure successfully completed.