[MS-SQL 2008 PROCEDURE] 임시 테이블에 값을 저장하여 UPDATE후 임시 테이블 내용을 조회하여 RETURN

1. 구분자(;)로 연결되어진 문자를 분리하여 여러 ROW DATA로 만드는 FUNCTION

CREATE FUNCTION [UFN_SEPARATION]

(
    @PARA_STR VARCHAR(8000),     -- PARAMETER의 전체 문자열 (형식 : AAAA:BBB:CCCCC:)
    @PARA_SEPARATION VARCHAR(50) -- 분리 문자
)
RETURNS @TB_RETURN_SEPA TABLE( SEQ INT IDENTITY(0,1), CODE VARCHAR(50) )
AS
BEGIN

        -- DECLARE VARIABLE
        DECLARE @TEMP_INDEX INT     -- 분리문자 현재 INDEX 위치
        SET @TEMP_INDEX = CHARINDEX(@PARA_SEPARATION,@PARA_STR)

        -- 문자열 분리
        WHILE @TEMP_INDEX > 0
        BEGIN
         INSERT INTO @TB_RETURN_SEPA( CODE ) VALUES( SUBSTRING(@PARA_STR,0,@TEMP_INDEX) )
         SET @PARA_STR = SUBSTRING(@PARA_STR,@TEMP_INDEX+1,LEN(@PARA_STR)-@TEMP_INDEX)
         SET @TEMP_INDEX = CHARINDEX(@PARA_SEPARATION,@PARA_STR)
        END

        RETURN
END

 

1. 임시 테이블에 값을 저장하여 UPDATE후 임시 테이블 내용을 조회하여 RETURN

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*-----------------------------------------------------------------------------

 * Copyright 2010 LG Innoteck All rights reserved -----------------------------*/

CREATE PROCEDURE [USP_SEL_DATA]
 @SEQ1 BIGINT,
 @SEQ2 BIGINT,
 @USERID  VARCHAR(50),
 @MIN       VARCHAR(50),
 @MAX       VARCHAR(50),
 @STEP      VARCHAR(50),
 @SELECT_PART INT
AS
SET NOCOUNT ON

DECLARE @PL        VARCHAR(50)

SET @PL        = 'BB:CC:'

SET @USERID  = 'TEST'

CREATE TABLE #TEMP_DATA ( SEQ INT, PL VARCHAR(50), PL_MIN FLOAT, PL_MAX FLOAT, PL_STEP INT )
INSERT INTO #TEMP_PL ( SEQ, PL )
SELECT SEQ, CODE FROM UFN_SEPARATION( @PL, ':' )

-- PL DATA
UPDATE  #TEMP_PL SET
        PL_MIN  = T2.CODE,
        PL_MAX  = T3.CODE,
        PL_STEP = T4.CODE
FROM    #TEMP_PL AS T1
        INNER JOIN  (   -- PL MIN
                    SELECT  SEQ AS SEQ,
                            (CASE WHEN CODE = '' THEN 0 ELSE CONVERT(FLOAT,CODE) END) AS CODE 
                    FROM    UFN_LED_CS_SEPARATION_SEQ( @MIN, ':' )
                    ) AS T2 ON T1.SEQ = T2.SEQ
        INNER JOIN  (   -- PL MAX
                    SELECT  SEQ AS SEQ,
                            (CASE WHEN CODE = '' THEN 1000 ELSE CONVERT(FLOAT,CODE) END) AS CODE 
                    FROM    UFN_LED_CS_SEPARATION_SEQ( @MAX, ':' )
                    ) AS T3 ON T1.SEQ = T3.SEQ
        INNER JOIN  (   -- PL STEP
                    SELECT  SEQ AS SEQ,
                            (CASE WHEN CODE = '' THEN '20' ELSE CODE END) AS CODE 
                    FROM    UFN_LED_CS_SEPARATION_SEQ( @STEP, ':' )
                    ) AS T4 ON T1.SEQ = T4.SEQ
      

/********   PL 좌표별 SHEET 좌표 확인    *******/
-- 기준 정보 확인
DECLARE @X_LOCATION INT -- X축 자리이동 수
DECLARE @Y_LOCATION INT -- Y추 자리이동 수
DECLARE @X_COUNT INT    -- X축 COUNT
DECLARE @Y_COUNT INT    -- Y축 COUNT

 SELECT  TTT1.*
 INTO    #TEMP_TB1
 FROM   
 (
  SELECT
    TT1.SEQ
   ,TT1.TIME
   ,CONVERT(INT,TT1.NUMBER) NUMBER
   ,CONVERT(INT,TT1.X) X
   ,CONVERT(INT,TT1.Y) Y
  FROM
  (
   SELECT
     T1.SEQ
    ,T1.TIME
    ,T1.NUMBER1 AS NUMBER
    ,T1.X1 AS X
    ,T1.Y1 AS Y
   FROM
   (
    SELECT
       [SEQ]
      ,[TIME]
      ,[VAL001] AS NUMBER1
      ,[VAL002] AS X1
      ,[VAL003] AS Y1
    FROM [PLEDEQDB01].[dbo].[TB_1]
    WHERE SEQ = @SEQ1
   )T1,
   (
    SELECT
       [VAL001] AS NUMBER2
      ,[VAL002] AS X2
      ,[VAL003] AS Y2
    FROM [PLEDEQDB01].[dbo].[TB_1]
    WHERE SEQ = @SEQ2
   ) T2
   WHERE T1.NUMBER1 = T2.NUMBER2
    AND T1.X1 = T2.X2
    AND T1.Y1 = T2.Y2
  ) TT1
 ) TTT1

 

    SELECT  T2.SEQ AS SEQ,
            T2.PL AS PL,
            CONVERT(DECIMAL(18,2),MIN(T1.FD1))    AS PL_MIN,
            CONVERT(DECIMAL(18,2),MAX(T1.FD1))    AS PL_MAX,
            MAX(T2.PL_STEP) AS PL_STEP,
            CONVERT(DECIMAL(18,2),AVG(T1.FD1))    AS PL_AVG,
            CONVERT(DECIMAL(18,2),STDEV(T1.FD1))  AS PL_STD,
            (CONVERT(DECIMAL(18,2),MAX(T1.FD1)) - CONVERT(DECIMAL(18,2),MIN(T1.FD1))) / MAX(T2.PL_STEP) AS PL_STEP_ONE
    INTO    #RETURN_TEMP_PL
    FROM    #TEMP_TB1 AS T1  
            INNER JOIN #TEMP_PL AS T2 ON T2.PL = 'WD' AND T1.FD1 >= T2.PL_MIN  AND T1.FD1 <= T2.PL_MAX
    GROUP BY T2.SEQ, T2.PL

 

 

    /********   PL 좌표별 SHEET 좌표 확인    *******/
    -- 기준 정보 확인
    SELECT  @X_LOCATION = MIN(T1.X)*(-1),
            @Y_LOCATION = MIN(T1.Y)*(-1),
            @X_COUNT    = MAX(T1.X) - MIN(T1.X) + 1,
            @Y_COUNT    = MAX(T1.Y) - MIN(T1.Y) + 1
    FROM    #TEMP_TB1 AS T1   /* PL DATA */

    -- PL DATA LIST
    SELECT  --T1.LOTID AS LOTID,
            T1.NUMBER AS NUMBER,
            T1.X + @X_LOCATION AS X_INDEX,
            @Y_LOCATION - T1.Y AS Y_INDEX,
            T1.X AS X_PL,
            T1.Y AS Y_PL,
    INTO    #RETURN_TEMP_TB1
    FROM    #TEMP_TB1 AS T1   /* PL DATA */
    ORDER BY CONVERT(INT,T1.NUMBER), T1.Y DESC

 

    CREATE TABLE #TEMP_MIN_MAX ( SEQ_PL INT, SEQ_STEP INT, PL VARCHAR(50), PL_MIN DECIMAL(18,2), PL_MAX DECIMAL(18,2), PL_STEP_POINT FLOAT, PL_COLOR_R INT, PL_COLOR_G INT, PL_COLOR_B INT, STEP_PER DECIMAL(18,2) )
    DECLARE @I INT
    SET @I = 0

    WHILE @I <= 5
    BEGIN
            DECLARE @VAR_PL VARCHAR(50)
            DECLARE @VAR_MIN DECIMAL(18,2)
            DECLARE @VAR_MAX DECIMAL(18,2)
            DECLARE @VAR_STEP INT
            DECLARE @VAR_STEP_ONE FLOAT
            DECLARE @VAR_DATA FLOAT
       
            SELECT  @VAR_PL = T1.PL, @VAR_MIN = T1.PL_MIN, @VAR_MAX = T1.PL_MAX, @VAR_STEP = T1.PL_STEP, @VAR_STEP_ONE = T1.PL_STEP_ONE
            FROM    #RETURN_TEMP_PL AS T1 
            WHERE   T1.SEQ = @I    

            DECLARE @J INT
            SET @J = 0
            SET @VAR_DATA = @VAR_MIN

            WHILE @J < @VAR_STEP
            BEGIN
                    INSERT INTO #TEMP_MIN_MAX( SEQ_PL, SEQ_STEP, PL, PL_MIN, PL_MAX, PL_STEP_POINT )
                    SELECT  @I, @J, @VAR_PL, @VAR_DATA, @VAR_DATA + @VAR_STEP_ONE, (((@VAR_DATA + @VAR_DATA + @VAR_STEP_ONE)/2) - @VAR_MIN)/(@VAR_MAX-@VAR_MIN)*100

                    SET @J = @J + 1   
                    SET @VAR_DATA = @VAR_DATA + @VAR_STEP_ONE
            END

            SET @I = @I + 1   
    END

    DECLARE @POINT_PER FLOAT
    SET @POINT_PER = 255.0/25
   
    UPDATE  #TEMP_MIN_MAX SET
            PL_COLOR_R  = CASE WHEN 0  <= PL_STEP_POINT AND PL_STEP_POINT <= 50  THEN 0
                               WHEN 75 <= PL_STEP_POINT AND PL_STEP_POINT <= 100 THEN 255
                               WHEN 50 <  PL_STEP_POINT AND PL_STEP_POINT <  75  THEN (PL_STEP_POINT - 50) * @POINT_PER
                          END,
            PL_COLOR_G  = CASE WHEN 25 <= PL_STEP_POINT AND PL_STEP_POINT <= 75  THEN 255
                               WHEN 0  <= PL_STEP_POINT AND PL_STEP_POINT <  25  THEN PL_STEP_POINT * @POINT_PER
                               WHEN 75 <  PL_STEP_POINT AND PL_STEP_POINT <= 100 THEN (100 - PL_STEP_POINT) * @POINT_PER
                          END,
            PL_COLOR_B  = CASE WHEN 0  <= PL_STEP_POINT AND PL_STEP_POINT <= 25  THEN 255
                               WHEN 50 <= PL_STEP_POINT AND PL_STEP_POINT <= 100 THEN 0
                               WHEN 25 <  PL_STEP_POINT AND PL_STEP_POINT <  50  THEN (50 - PL_STEP_POINT) * @POINT_PER
                          END

 

    DECLARE @COUNT_TOTAL FLOAT
 DECLARE @COUNT FLOAT

 SELECT T2.PL, COUNT(*) * 100 / @COUNT_TOTAL AS SU
 INTO #TEMP_SU
    FROM     #TEMP_TB1 AS T1  
   INNER JOIN #RETURN_TEMP_PL AS T2 ON T2.PL = 'WD' AND T1.dWLENGTH >= T2.PL_MIN AND T1.dWLENGTH <= T2.PL_MAX
 GROUP BY T2.PL

 UNION ALL

 SELECT T2.PL, COUNT(*) * 100 / @COUNT_TOTAL
    FROM     #TEMP_TB1 AS T1  
   INNER JOIN #RETURN_TEMP_PL AS T2 ON T2.PL = 'WP' AND T1.PWLENGTH >= T2.PL_MIN AND T1.PWLENGTH <= T2.PL_MAX
 GROUP BY T2.PL

 UNION ALL

 SELECT T2.PL, COUNT(*) * 100 / @COUNT_TOTAL
    FROM     #TEMP_TB1 AS T1  
   INNER JOIN #RETURN_TEMP_PL AS T2 ON T2.PL = 'FWHM' AND T1.FWHM >= T2.PL_MIN AND T1.FWHM <= T2.PL_MAX
 GROUP BY T2.PL

 

 IF (@SELECT_PART = 1)
  BEGIN
   -- 1. RETURN DATA : PL 특성성 평균, 표준편차
   SELECT  T1.PL, T1.PL_MIN, T1.PL_MAX, T1.PL_STEP, T1.PL_AVG, T1.PL_STD , T2.SU
   FROM    #RETURN_TEMP_PL AS T1
     LEFT OUTER JOIN #TEMP_SU T2 ON T1.PL = T2.PL
   ORDER BY T1.SEQ
 
  END
 ELSE IF (@SELECT_PART = 2)
  BEGIN
   -- 2. RETURN DATA : WAFER POINT별 특성값
   SELECT  T1.NUMBER, T1.X_INDEX, T1.Y_INDEX, T1.X_PL, T1.Y_PL,
     T1.WD, T1.WP, T1.FWHM, T1.INTEINT, T1.PEAKINT, T1.THICKNESS,
     T1.WD_COLOR_R, T1.WP_COLOR_R, T1.FWHM_COLOR_R, T1.INTEINT_COLOR_R, T1.PEAKINT_COLOR_R, T1.THICKNESS_COLOR_R,
     T1.WD_COLOR_G, T1.WP_COLOR_G, T1.FWHM_COLOR_G, T1.INTEINT_COLOR_G, T1.PEAKINT_COLOR_G, T1.THICKNESS_COLOR_G,
     T1.WD_COLOR_B, T1.WP_COLOR_B, T1.FWHM_COLOR_B, T1.INTEINT_COLOR_B, T1.PEAKINT_COLOR_B, T1.THICKNESS_COLOR_B
   FROM    #RETURN_TEMP_TB1 AS T1 
  END

    -- DROP TEMP TABLE
 DROP TABLE #TEMP_SU
 DROP TABLE #TEMP_TB1
 DROP TABLE #RETURN_TEMP_PL
 DROP TABLE #RETURN_TEMP_TB1
 DROP TABLE #TEMP_MIN_MAX

-- DROP TEMP TALBE
DROP TABLE #TEMP_PL

SET NOCOUNT OFF