--USE [TempTable]
--GO
/****** Object:  UserDefinedFunction [dbo].[fn_GetFileInfoInFullPathUsingXml]    Script Date: 2017-01-17 오후 1:43:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-----------------------------------------------------------------------------------------------------------  
--  파일경로에서 파일정보 가져오기  
--  파일경로 문자열에서 파일명,경로,확장자가지오기(filepath:경로,filename:파일명,extension:확장자)  
--  예: SELECT 데이터베이스명.소유자명.fn_GetFileInfoInFullPathUsingXml('filename','test.xml','/')  
--  SELECT  dbo.fn_GetFileInfoInFullPathUsingXml('filename','c:/dkdk/test.xml','/')  
--  SELECT  dbo.fn_GetFileInfoInFullPathUsingXml('filepath','c:/dkdk/test.xml','/')  
--  SELECT  dbo.fn_GetFileInfoInFullPathUsingXml('extension','c:/dkdk/test.xml','/')  
-----------------------------------------------------------------------------------------------------------  
-----------------------------------------------------------------------------------------------------------  
-- 프로시저 생성  
-----------------------------------------------------------------------------------------------------------  
  
ALTER FUNCTION     [dbo].[fn_GetFileInfoInFullPathUsingXml]  
(  
    @iGubun         NVARCHAR(10),   -- filepath:경로,filename:파일명,extension:확장자  
    @iFullPath      NVARCHAR(MAX),  
    @iDelimiter     NVARCHAR(10)  
)   
RETURNS NVARCHAR(MAX)  
AS  
BEGIN  
      
      
    DECLARE @wTblSplit AS TABLE   
    (  
         Seq        INT     IDENTITY(1,1),  
         Fragment   NVARCHAR(MAX)  
    )  
    DECLARE @wFileInfo  NVARCHAR(MAX)  
    DECLARE @wXml       XML  
    DECLARE @wMax       INT  
      
    SELECT  @wFileInfo  = ''  
    SELECT  @wMax       = 0  
    SET     @wXml       = N'' + REPLACE(@iFullPath, @iDelimiter,'') + ''  
  
    INSERT  INTO @wTblSplit(Fragment)  
    SELECT  r.value('.','NVARCHAR(MAX)') as item  
    FROM    @wXml.nodes('//root/r') as records(r)  
  
    SELECT  @wMax   = MAX(Seq) FROM @wTblSplit  
    IF  @iGubun = 'filepath'  
    BEGIN  
        IF  @wMax   = 1  
        BEGIN  
            SELECT  @wFileInfo = '.'  
        END  
        ELSE  
        BEGIN  
            SELECT  @wFileInfo  = COALESCE(@wFileInfo + @iDelimiter + Fragment, Fragment)  
            FROM    @wTblSplit  
            WHERE   Seq         < @wMax  
            ORDER BY Seq  
              
            SELECT  @wFileInfo  = SUBSTRING(@wFileInfo,2,LEN(@wFileInfo)) + '/'  
        END  
    END  
    ELSE IF @iGubun = 'filename'  
    BEGIN  
        SELECT  @wFileInfo = Fragment FROM @wTblSplit WHERE Seq = @wMax
		SELECT  @wFileInfo = SUBSTRING(@wFileInfo,1,CHARINDEX('.',@wFileInfo))  
    END  
    ELSE IF @iGubun = 'extension'  
    BEGIN  
        SELECT  @wFileInfo = Fragment FROM @wTblSplit WHERE Seq = @wMax  
        SELECT  @wFileInfo = SUBSTRING(@wFileInfo,CHARINDEX('.',@wFileInfo)+1,LEN(@wFileInfo)-CHARINDEX('.',@wFileInfo))  
    END  
      
    RETURN LTRIM(RTRIM(@wFileInfo))  
  
END


http://rocabilly.tistory.com/88 에 있던걸 extension 부분만 수정했다. aaa.bbb.ccc.jpg 처럼 dot('.')이 여러개일경우 오류가 발생할수 있다.


Posted by 두억시니 트랙백 0 : 댓글 0
  SELECT A.TABLE_NAME, B.COMMENTS
    FROM ALL_TABLES A, ALL_TAB_COMMENTS B
   WHERE A.TABLE_NAME = B.TABLE_NAME AND A.TABLE_NAME LIKE :table_name || '%'
ORDER BY A.TABLE_NAME
종종 사용하는데 쓸때마다 잊어서 메모해놓자.
Posted by 두억시니 트랙백 0 : 댓글 0
 SELECT COLUMN_ID AS SEQ,
         (SELECT NVL (D.POSITION, '')
            FROM ALL_CONS_COLUMNS D, all_constraints E
           WHERE     D.OWNER = UPPER ( :user_name)
                 AND D.OWNER = A.OWNER
                 AND D.TABLE_NAME = A.TABLE_NAME
                 AND D.COLUMN_NAME = A.COLUMN_NAME
                 AND D.OWNER = E.OWNER
                 AND D.TABLE_NAME = E.TABLE_NAME
                 AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
                 AND E.CONSTRAINT_TYPE = 'P')
            AS PK,
         (SELECT CASE WHEN D.POSITION IS NULL THEN '' ELSE 'V' END
            FROM ALL_CONS_COLUMNS D, all_constraints E
           WHERE     D.OWNER = UPPER ( :user_name)
                 AND D.OWNER = A.OWNER
                 AND D.TABLE_NAME = A.TABLE_NAME
                 AND D.COLUMN_NAME = A.COLUMN_NAME
                 AND D.OWNER = E.OWNER
                 AND D.TABLE_NAME = E.TABLE_NAME
                 AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
                 AND E.CONSTRAINT_TYPE = 'R')
            AS FK,
         A.COLUMN_NAME AS COLUMN_ID,
         C.COMMENTS,
         A.DATA_TYPE,
         A.DATA_LENGTH,
         A.NULLABLE,
         A.DATA_DEFAULT
    FROM ALL_TAB_COLUMNS A, ALL_TAB_COMMENTS B, ALL_COL_COMMENTS C
   WHERE     A.OWNER = UPPER ( :user_name)
         AND B.OWNER = UPPER ( :user_name)
         AND C.OWNER = UPPER ( :user_name)
         AND A.OWNER = B.OWNER
         AND A.OWNER = C.OWNER
         AND A.TABLE_NAME = B.TABLE_NAME
         AND A.TABLE_NAME = C.TABLE_NAME
         AND A.COLUMN_NAME = C.COLUMN_NAME
         AND A.TABLE_NAME = UPPER ( :table_name)
ORDER BY 1
MSSQL은 어떻게 할까나...


Posted by 두억시니 트랙백 0 : 댓글 0
CREATE FUNCTION [dbo].[fx_FormatUsingMask] 
(
    -- Add the parameters for the function here
    @input nvarchar(1000),
    @mask nvarchar(1000)
)
RETURNS nvarchar(1000)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result nvarchar(1000) = ''
    DECLARE @inputPos int = 1
    DECLARE @maskPos int = 1
    DECLARE @maskSign char(1) = ''

    WHILE @maskPos <= Len(@mask)
    BEGIN
        set @maskSign = substring(@mask, @maskPos, 1)

        IF @maskSign = '#'
        BEGIN
            set @result = @result + substring(@input, @inputPos, 1)
            set @inputPos += 1
            set @maskPos += 1
        END
        ELSE
        BEGIN
            set @result = @result + @maskSign
            set @maskPos += 1
        END
    END
    -- Return the result of the function
    RETURN @result

END

MSSQL 버전마다 어떤지는 모르겠지만, 적당한 함수를 못찾아서 인터넷을 검색해보니 이런 녀석이 나오드라.

사용 예)
select dbo.fx_FormatUsingMask('201503050900', '####-##-## ##:##:00')

Posted by 두억시니 트랙백 0 : 댓글 0
보통 2개 의 테이블에서 조인을 하여 특정 테이블의 컬럼을 Update 하고 싶을 때, 대부분 SET 절에 SubQuery 형태로 문장을 작성하여, 특정값을 가져오게 하여 Update 하도록 많이 작성을 할 것입니다. 예를 들면 아래와 같은 문장이 되겠죠?
UPDATE table_a a
   SET column_1 = (SELECT column_1
                     FROM table_b b
                    WHERE b.KEY = a.KEY)
그럼 위의 문장과 같은 형식으로 table_a 에 두개 이상의 컬럼을 Update 한다고 생각하면,
UPDATE table_a a
   SET column_1 = (SELECT column_1
                     FROM table_b b
                    WHERE b.key = a.key),
       column_2 = (SELECT column_2
                     FROM table_b b
                    WHERE b.key = a.key),
       column_3 = (SELECT column_3
                     FROM table_b b
                    WHERE b.key = a.key)
와 같은 식의 문장을 생각할 수 있습니다. 하지만, 아래와 같은 문장은 어떻게 될까요?
UPDATE
       (
        SELECT a.column_1 AS a_column_1,
               b.column_1 AS b_column_1
          FROM table_a a,
               table_b b
         WHERE a.key= b.key
       )
   SET a_column_1 = b_column_1
SQL문을 많이 접해보신 분이라면, 위 문장이 대략 어떤 의미인지 아실 수 있을 것 입니다. 즉, 위 문장과 같이 Updatable Join View 를 생각할 수 있습니다. 물론 이경우 조인되는 2개의 테이블은 반드시 1:1 또는 1:N의 관계여야 하며, Update되는 컬럼의 테이블은 N쪽 집합이어야 합니다. 이것은 1에 해당하는 집합인 부모 table의 조인 컬럼이 UK 또는 PK로 설정되어 있어야 된다는 것입니다. 위의 문장에서 보이는 table_a 와 table_b가 UK 또는 PK 로 설정이 되어 있지 않으면 ORA-01779 cannot modify a column which maps to a non key-preserved table 에러를 방생하며 실행되지 않습니다. 이쯤에서 한번 고민해봐야 할 문제가, 처음에 문제로 던졌던 두개의 Table을 조인하여, 특정 table의 컬럼 하나만 업데이트 할경우엔 Set 절에 Sub-Query 형태로 문장을 작성해도 큰 문제가 되지 않을 듯 합니다. 하지만, 컬럼이 여러개일 경우와 행이 무수히 많은 Table을 Update 할 경우 SET절에서 동일 문장으로 특정 테이블을 반복적으로 실행해야하는 로드가 발생하므로 Update 해야 하는 행이 많을 경우엔 Updatable Join View를 활용 해야만 하는 필요가 있다. 그러나, Updateable Join View 를 활용하기 위해 매번 테이블의 UK 혹은 PK를 생성해야만 해야 하나 라는 필요성에 대해 생각해 보게 되는데, 일반적으로는 UK나 PK Constraint를 설정하기 어려운 것이 현실입니다. 따라서, 이러한 Constraint를 피해서 Updatable Join View를 사용할 수 있도록 하기 위해 준비된 오라클 힌트가 있는데 그것이 바로, BYPASS_UJVC 입니다. 즉, 위에서 소개한 문장에서 UK 혹은 PK가 설정되지 않아 에러가 리턴 될경우 아래와 같이 힌트를 준다면 정상적으로 실행이 될 것입니다.
UPDATE 
       (
        SELECT a.column_1 AS a_column_1,
               b.column_1 AS b_column_1
          FROM table_a a,
               table_b b
         WHERE a.key= b.key
       )
   SET a_column_1 = b_column_1
컬럼이 여러개 일 경우에도,
UPDATE 
       (
        SELECT a.column_1 AS a_column_1,
               b.column_1 AS b_column_1,
               a.column_2 AS a_column_2,
               b.column_2 AS b_column_2,
               a.column_3 AS a_column_3,
               b.column_3 AS b_column_3
          FROM table_a a,
               table_b b
         WHERE a.key= b.key
       )
   SET a_column_1 = b_column_1,
       a_column_2 = b_column_2,
       a_column_3 = b_column_3

위와 같이 표현될 수 있을 것 입니다. 이렇게 두개이상의 테이블을 조인하여 Update 할 경우 Updatable Join View 를 활용하여 Update 문을 실행하면, 실행시간도 단축(튜닝)되는 효과를 보실 수 있을 듯 합니다. 마지막으로 위에서 소개한 힌트의 의미를 풀어보자면 bypass_ujvc : Bypass Updateable Join view Constraints 이라는 의미 입니다. 풀어서 적어보니, UJVC 라는 의미가 쏙 들어오지 않나요?


출처 : http://oracleerp.co.kr/bbs/board.php?bo_table=database&wr_id=3

Posted by 두억시니 트랙백 0 : 댓글 0

여백 만들기

2014.11.26 19:30 from database/oracle
/* Formatted on 2014/11/26 오후 7:31:59 (QP5 v5.256.13226.35510) */
WITH M
     AS (SELECT 1 AS seq, 'A' AS val FROM DUAL
         UNION ALL
         SELECT 2 AS seq, 'B' AS val FROM DUAL
         UNION ALL
         SELECT 3 AS seq, 'C' AS val FROM DUAL
         UNION ALL
         SELECT 4 AS seq, 'E' AS val FROM DUAL
         UNION ALL
         SELECT 5 AS seq, 'E' AS val FROM DUAL)
SELECT seq, val FROM M
UNION ALL
    SELECT ROWNUM + (SELECT COUNT (*) FROM M) AS seq,
           DECODE (ROWNUM, 1, '-이하여백-', NULL)
      FROM (SELECT '' val
              FROM DUAL
             WHERE 0 <
                      MOD (
                         (CASE
                             WHEN (SELECT COUNT (*) FROM M) = 0 THEN 29
                             ELSE (SELECT COUNT (*) FROM M)
                          END),
                         30)                           -- 30으로 딱 떨어질때를 위한 제동장치
                            ) S
CONNECT BY LEVEL <=
                30
              - MOD (
                   (CASE
                       WHEN (SELECT COUNT (*) FROM M) = 0 THEN 29
                       ELSE (SELECT COUNT (*) FROM M)
                    END),
                   30)
Posted by 두억시니 트랙백 0 : 댓글 0
SELECT WMSYS.WM_CONCAT (MESSAGE) MESSAGE
  FROM (SELECT LO.MESSAGE MESSAGE
          FROM SMEIS.S_LECTUREOPINION LO
         WHERE     LO.YEAR = '2014'
               AND LO.SEASONCODE = '11'
               AND LO.SUBJECTCODE = '380031'
               AND LO.DIVISION = '12'
               AND MESSAGE IS NOT NULL)
Posted by 두억시니 트랙백 0 : 댓글 0
INSERT INTO S_UPDATELOG
   SELECT *
     FROM (SELECT *
             FROM S_UPDATELOG AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE) A
    WHERE     A.STNO = '2009432007'
          AND A.CHANGECODE = '21'
          AND A.CHANGEYEAR = '2012';
Posted by 두억시니 트랙백 0 : 댓글 0


-- 테이블 이름 변경
-- CustomerName 이라는 테이블을 Customer 라는 이름으로 변경
sp_rename 'CustomerName', 'Customer'

-- 컬럼 이름 변경
-- Test 테이블의 CustomerName 이라는 컬럼을 Customer라는 이름으로 변경
sp_rename 'reserve.[CustomerName]', 'Customer', 'COLUMN'

-- 컬럼 타입 변경
-- Test 테이블의 Customer 컬럼의 타입을 varchar(10)으로 변경
ALTER TABLE Test
ALTER COLUMN Customer varchar(10)



종종 사용하는데 왜 기억을 못하는 걸까? ㅠ_ㅜ

Posted by 두억시니 트랙백 0 : 댓글 0



SELECT * FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET%';


Posted by 두억시니 트랙백 0 : 댓글 0