JOIN문을 이용한 UPDATE 쿼리

UPDATE T1

SET T1.P_CODE_VALUE1 = T2.P_CODE1

,T1.P_CODE_VALUE2 = T2.P_CODE2

FROM TMAST02TT T1

INNER JOIN TCODE02TT T2 ON T1.YEAR = T2.YEAR AND T1.P_CODE = T2.T_CODE

WHERE 1=1

AND T1.GUBUN = :gubun AND T1.YEAR = :year AND T1.SEASON = :season AND T1.SCHOOL = :school 

AND T1.BOKJONG = :bokjong AND T1.PUMOK = :pumok AND T1.SAYANG = :sayang AND T1.P_CODE = :pCode

AND T2.SEQ = :seq

[MSSQL] 프로시저 생성 샘플코드

기본 프로시저

프로시저 생성

1. CREATE [or ALTER] PROCEDURE SP_SQUTY12PL
2. @year nvarchar(8)
3. , @season nvarchar(2)
4. , @returnVal nvarchar(2000) OUTPUT
5. AS
6. BEGIN
7. DECLARE
8. @sql NVARCHAR(2000)
9. set @sql = ' select * from squty02tt'
10. set @returnVal = @sql
11. END

프로시저 실행

1. DECLARE @year nvarchar(8)
2. DECLARE @season nvarchar(2)
3. DECLARE @returnVal nvarchar(2000)
4. set @year = '2012'
5. set @season = 'F'
6. exec dbo.SP_SQUTY12PL @year, @season, @returnVal OUTPUT
7. select @returnVal

동적쿼리를 이용한 프로시저

1. ALTER PROCEDURE SP_SQUTY12PL
2. @year nvarchar(8)
3. , @season nvarchar(2)
4. AS
5. BEGIN
6. DECLARE
7. @sql nvarchar(4000)
8. , @param nvarchar(400)
9. , @yy1 nvarchar(4)
10. , @yy2 nvarchar(4)
11. , @yy3 nvarchar(4)
12. set @param = '@p_year nvarchar(8)
13. , @p_season nvarchar(2)
14. '
15. set @sql = ' select top 20 * from squty02tt
16. where year = @p_year and season = @p_season
17. '
18. --set @returnVal = @sql
19. exec SP_EXECUTESQL @sql, @param, @p_year=@year, @p_season=@season
20. END

execute procedure

1. DECLARE @year nvarchar(8)
2. DECLARE @season nvarchar(2)
3. set @year = '2012'
4. set @season = 'F'
5. exec dbo.SP_SQUTY12PL @year, @season

[MSSQL] 컬럼 코멘트 조회 쿼리

 SELECT * FROM (
select  b.name ColumnName, c.name TypeName, b.length ColumnLength
, Case b.IsNullable when 1 then '' else 'NN' end Nullable
, Case When g.name is null Then '' Else 'PK' End PKwithCidx
, IsNull(d.value,'') Caption
from EVENT..sysobjects a
JOIN EVENT..syscolumns b ON (a.id = b.id and a.type='U' and a.status > 0 and a.name='TBL_MAIL_INFO')
JOIN EVENT..systypes c ON (b.xtype = c.xtype and c.name<>'sysname')
LEFT JOIN EVENT..sysproperties d ON  (b.id=d.id and b.colid = d.smallid)
LEFT JOIN (select name,e.id,colid from EVENT..sysindexes e join EVENT..sysindexkeys f on e.id=f.id
where e.id > 10000000 and e.indid=1 and f.indid=1) g ON (g.id=b.id and g.colid=b.colid)
--order by a.name, colorder
) X
WHERE ColumnName = 'MAIL_SEQ'

SQLServer 2008 버전에선 아래와 같이

SELECT 

*

FROM INFORMATION_SCHEMA.COLUMNS T1 

LEFT OUTER JOIN ( 

 SELECT 

 T.NAME TABLE_NAME, TD.VALUE TABLE_DESC 

 , C.NAME COLUMN_NAME, CD.VALUE COLUMN_DESC 

 FROM SYSOBJECTS T 

 INNER JOIN SYSUSERS U ON U.UID = T.UID 

 LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES TD ON TD.MAJOR_ID = T.ID AND TD.MINOR_ID = 0 AND TD.NAME = 'MS_Description' 

 INNER JOIN SYSCOLUMNS C ON C.ID = T.ID  

 LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES CD ON CD.MAJOR_ID = C.ID AND CD.MINOR_ID = C.COLID AND CD.NAME = 'MS_Description' 

 WHERE T.TYPE = 'u' 

) T2 ON T2.TABLE_NAME=T1.TABLE_NAME AND T2.COLUMN_NAME=T1.COLUMN_NAME 

WHERE T1.TABLE_NAME = ?

ORDER BY ORDINAL_POSITION 

[MSSQL] 테이블 컬럼조회 쿼리

-- 테이블 리스트 

SELECT * FROM INFORMATION_SCHEMA.TABLES

;

-- 컬럼 상세 정보 

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'SQUTY02TT'

ORDER BY 5

;

[MSSQL] 서브쿼리를 문자열로 합치기 (using xml path)

SELECT
  ',' + CODE_NAME
  FROM TCODE01TT T1
  WHERE SEX = 'M' AND BOKJONG = 'J' AND S_CODE = '1'
  FOR XML PATH('')

[MSSQL] 동적쿼리를 이용한 프로시저

프로시저 소스

1. ALTER PROCEDURE SP_SQUTY12PL_WITH_OLD_000
2. @year nvarchar(8)
3. , @season nvarchar(2)
4. , @area nvarchar(2)
5. , @agent nvarchar(10)
6. , @bokjong nvarchar(8)
7. AS
8. BEGIN
9. DECLARE
10. @sql nvarchar(4000)
11. , @param nvarchar(400)
12. , @yyyyMinus1 nvarchar(8)
13. , @yyMinus1 nvarchar(4)
14. , @yyMinus2 nvarchar(4)
15. , @yyMinus3 nvarchar(4)
16. , @yySeason nvarchar(6)
17. set @param = '@p_year nvarchar(8)
18. , @p_season nvarchar(2)
19. , @p_area nvarchar(2)
20. , @p_agent nvarchar(2)
21. , @p_yyyyMinus1 nvarchar(8)
22. , @p_yyMinus1 nvarchar(4)
23. , @p_yyMinus2 nvarchar(4)
24. , @p_yyMinus3 nvarchar(4)
25. , @p_yySeason nvarchar(6)
26. , @p_bokjong nvarchar(8)
27. '
28. set @yySeason = substring(@year, 3, 2) + @season
29. set @yyyyMinus1 = convert(nvarchar(4), @year - 1)
30. set @yyMinus1 = convert(nvarchar(4), substring(@year, 3, 2) - 1)
31. set @yyMinus2 = convert(nvarchar(4), substring(@year, 3, 2) - 2)
32. set @yyMinus3 = convert(nvarchar(4), substring(@year, 3, 2) - 3)
33. set @sql = ' SELECT
34. T1.AREA
35. , ISNULL(SUJU_QTY, 0) SUJU_QTY, ISNULL(SUJU_AMT, 0) SUJU_AMT
36. , ISNULL(CHGO_QTY, 0) CHGO_QTY, ISNULL(CHGO_AMT, 0) CHGO_AMT
37. , ISNULL(INVT_QTY, 0) INVT_QTY, ISNULL(INVT_AMT, 0) INVT_AMT
38. , ISNULL(CHGO_QTY, 0) - ISNULL(INVT_QTY, 0) SALE_QTY
39. , ISNULL(CHGO_AMT, 0) - ISNULL(INVT_AMT, 0) SALE_AMT
40. FROM (
41. SELECT * FROM (
42. SELECT CODE AS AREA, SERIAL FROM MCODE02TT WHERE GRCODE = ''101''
43. ) T1
44. ) T1 LEFT OUTER JOIN (
45. SELECT
46. AREA, SUM(T1.SUJU_QTY) SUJU_QTY, SUM(T1.SUJU_AMT) SUJU_AMT
47. FROM (
48. SELECT 
49. T1.AREA, T1.ORDERNO, T1.SUJU_QTY
50. , T1.SUJU_QTY * ISNULL((SELECT CHULGOGA CHULGOGA FROM SCHGO01TT X WHERE X.SEASON=T1.SEASON AND X.PUMBUN = T1.PUMBUN),0) SUJU_AMT
51. FROM SMAST01TT T1 INNER JOIN SQUTY01TT T2
52. ON T1.ORDERNO = T2.ORDERNO
53. WHERE T2.QTY_GUBUN = ''1''
54. AND SEASON = @p_yySeason '
55. if @bokjong is not null or @bokjong <> ''
56. begin
57. set @sql = @sql + ' AND T1.BOKJONG = @p_bokjong '
58. end
59. set @sql = @sql + ') T1
60. GROUP BY AREA
61. ) T2 ON T1.AREA = T2.AREA
62. LEFT OUTER JOIN (
63. SELECT 
64. AREA, SUM(CHGO_QTY) AS CHGO_QTY, SUM(CHGO_QTY * CHGO_AMT) CHGO_AMT
65. FROM (
66. SELECT AREA, SIZE1+SIZE2+SIZE3+SIZE4+SIZE5+SIZE6+SIZE7+SIZE8+SIZE9+SIZE10+SIZE11+SIZE12+SIZE13+B_QTY1+B_QTY2+B_QTY3 AS CHGO_QTY
67. , ISNULL((SELECT TOP 1 CHULGOGA FROM SMAST01TT X INNER JOIN SCHGO01TT Y ON X.PUMBUN = Y.PUMBUN AND X.SCHOOL = T1.SCHOOL AND X.BOKJONG = T1.BOKJONG  WHERE X.SEASON = @p_yySeason),0) AS CHGO_AMT 
68. FROM SINVT01TT T1
69. WHERE YEAR = @p_yyyyMinus1 AND SEASON = @p_season AND S_YEAR IN (@p_yyMinus1, @p_yyMinus2, @p_yyMinus3) '
70. /* AND T1.BOKJONG = ''C'' */
71. if @bokjong is not null or @bokjong <> ''
72. begin
73. set @sql = @sql + ' AND T1.BOKJONG = @p_bokjong '
74. end
75. set @sql = @sql + ') T1
76. GROUP BY AREA
77. ) T3
78. ON T1.AREA = T3.AREA
79. LEFT OUTER JOIN (
80. SELECT 
81. AREA, SUM(INVT_QTY) AS INVT_QTY, SUM(INVT_QTY * INVT_AMT) INVT_AMT
82. FROM (
83. SELECT AREA, SIZE1+SIZE2+SIZE3+SIZE4+SIZE5+SIZE6+SIZE7+SIZE8+SIZE9+SIZE10+SIZE11+SIZE12+SIZE13+B_QTY1+B_QTY2+B_QTY3 AS INVT_QTY
84. , ISNULL((SELECT TOP 1 CHULGOGA FROM SMAST01TT X INNER JOIN SCHGO01TT Y ON X.PUMBUN = Y.PUMBUN AND X.SCHOOL = T1.SCHOOL AND X.BOKJONG = T1.BOKJONG  WHERE X.SEASON = @p_yySeason),0) AS INVT_AMT 
85. FROM SINVT01TT T1
86. WHERE YEAR = @p_year AND SEASON = @p_season AND S_YEAR IN (substring(@p_year, 3, 2), @p_yyMinus1, @p_yyMinus2) '
87. /* AND T1.BOKJONG = ''C'' */
88. if @bokjong is not null or @bokjong <> ''
89. begin
90. set @sql = @sql + ' AND T1.BOKJONG = @p_bokjong '
91. end
92. set @sql = @sql + ') T1
93. GROUP BY AREA
94. ) T4
95. ON T1.AREA = T4.AREA
96. ORDER BY SERIAL
97. '
98. exec SP_EXECUTESQL @sql, @param, @p_year=@year, @p_season=@season, @p_area=@area, @p_agent=@agent, @p_yyyyMinus1=@yyyyMinus1, @p_yyMinus1=@yyMinus1, @p_yyMinus2=@yyMinus2, @p_yyMinus3=@yyMinus3, @p_yySeason=@yySeason, @p_bokjong=@bokjong    
99. END
100. 
     

테스트

1. USE [skoolooks_erp]
   GO
   
   
   DECLARE@return_value int
   
   
   EXEC@return_value = [dbo].[SP_SQUTY12PL_WITH_OLD_000]
   @year = N'2012',
   @season = N'F',
   @area = null,
   @agent = null,
   @bokjong = null
   
   
   SELECT'Return Value' = @return_value
   
   
   GO
   
   

[MSSQL] 오라클의 sequence 같은 기능 구현하기

mssql에서 sequence 구현하기

1. 테이블 생성

CREATE TABLE SEQUENCES (

ID VARCHAR(100) PRIMARY KEY

, SEQ INT

)

GO

2. 프로시져 생성

CREATE PROCEDURE nextval

@id VARCHAR(100)

, @seq INT OUTPUT

as

DECLARE @cnt INT

SELECT @cnt = COUNT(*) FROM SEQUENCES 

WHERE ID = @id

PRINT @cnt

IF @cnt = 0

BEGIN

INSERT INTO SEQUENCES (ID, SEQ) VALUES (@id, 0)

END

UPDATE SEQUENCES SET @seq = seq = seq + 1

WHERE ID = @id

RETURN @seq

GO

3. java

public static int getSequence(Connection connection, String sequenceName) throws Exception {

String procname = null;

procname = "{call nextval(?, ?)}";

CallableStatement cs = null;

int result = 0;

cs = connection.prepareCall(procname);

cs.setString(1, sequenceName);

cs.registerOutParameter(2, java.sql.Types.INTEGER);

cs.execute();

result = Integer.parseInt(cs.getString(2));

return result;

}

[MSSQL] lock 확인 및 강제종료

sp_lock

lock 확인을 위한 프로시저 

mode = x 이면 lock

dbcc inputbuffer(spid)

클라이언트가 MicrosoftSQL Server 인스턴스로 마지막으로 전송한 문을 표시

spid에는 위에서 lock로 표시된 값 입력

kill spid

lock 걸린 프로세스 강제 종료

ms sqlserver management tools 에서 transaction 적용하기

MS-SQL SERVER 2005 부터는
도구>옵션>쿼리 실행>SQL SERVER>ANSI
SET IMPLICIT_TRANSACTIONS를 체크

[MSSQL] merge

sqlserver 도 2008 버전부터 merge가 지원된다

MERGE TMAST03TT AS TARGET
USING (
  SELECT 'A' GUBUN, '2011' YEAR, 'F' SEASON, 'SA001' SCHOOL, 'B' BOKJONG, 'N' PUMOK, 'N' SAYANG, '' P_CODE, '1' P_CODE_VALUE1, '' ISSUE_DATE, '' ISSUE_ID
) AS SOURCE
ON (TARGET.GUBUN = SOURCE.GUBUN AND TARGET.YEAR = SOURCE.YEAR AND TARGET.SEASON = SOURCE.SEASON AND TARGET.SCHOOL = SOURCE.SCHOOL AND TARGET.BOKJONG = SOURCE.BOKJONG)
WHEN MATCHED THEN
  UPDATE SET TARGET.P_CODE_VALUE1 = SOURCE.P_CODE_VALUE1
WHEN NOT MATCHED BY TARGET THEN
  INSERT (GUBUN, YEAR, SEASON, SCHOOL, BOKJONG
    , PUMOK, SAYANG, P_CODE, P_CODE_VALUE1, ISSUE_DATE
    , ISSUE_ID
  )
  VALUES (SOURCE.GUBUN, SOURCE.YEAR, SOURCE.SEASON, SOURCE.SCHOOL, SOURCE.BOKJONG
    , SOURCE.PUMOK, SOURCE.SAYANG, SOURCE.P_CODE, SOURCE.P_CODE_VALUE1, SOURCE.ISSUE_DATE
    , SOURCE.ISSUE_ID)
OUTPUT $action, Inserted.*, Deleted.*
;

[MSSQL] IDENTITY 가져오기

 /** IDENTITY_가져오기 */
/** 1. 입력한 테이블의 현재 IDENTITY 값 가져오기 */
INSERT INTO PUBS..TEST
(TITLE)
VALUES
('XXX')
GO

SELECT [SCOPE_IDENTITY] = SCOPE_IDENTITY()
GO

DELETE FROM PUBS..TEST
WHERE TITLE = 'XXX'
GO

/** 2. 특정 테이블의 IDENTITY 값 알아내기 */
SELECT [ident_current] = IDENT_CURRENT('PUBS..TEST')