[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] 동적쿼리를 이용한 프로시저

프로시저 소스

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
   
   

오라클에서 프로시저 호출

package org.javaya.test;

import java.sql.*;

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;

public class RefCursor {

public static void main(String[] args) {
RefCursor vTest = new RefCursor();
vTest.prepareCall();
}

void prepareCall() {

Connection conn = null;
CallableStatement cstmt = null;
OracleCallableStatement ocstmt = null;

try {
String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORA";
String user = "scott";
String password = "tiger";

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(url, user, password);

// Stored Procedure 를 호출하기 위해 JDBC Callable Statement를 사용 합니다
String query = null;

query = "{call PROCUDURE_NAME(?, ?,?)}";

cstmt = conn.prepareCall(query);

// 프로시져의 In Parameter로 SELECT문장을 넘깁니다.
int x = 1;
cstmt.setString(x++, "001");
cstmt.setString(x++, "2008");

// CallableStatement를 위한 REF CURSOR OUTPUT PARAMETER를
// OracleTypes.CURSOR로 등록합니다.
int outIndex = x++;
cstmt.registerOutParameter(outIndex, OracleTypes.CURSOR);

// CallableStatement를 실행합니다.
cstmt.execute();

// getCursor() method를 사용하기 위해 CallableStatement를
// OracleCallableStatement object로 바꿉니다.
ocstmt = (OracleCallableStatement) cstmt;

// OracleCallableStatement 의 getCursor() method를 사용해서 REF CURSOR를
// JDBC ResultSet variable 에 저장합니다.
ResultSet cursor = ocstmt.getCursor(outIndex);
//ResultSet cursor = ocstmt.executeQuery();

// 쿼리결과 empno, ename 출력
ResultSetMetaData rsmd = cursor.getMetaData();
int columnCount = rsmd.getColumnCount();

for (int i=0; i<columnCount; i++) {
System.out.print(rsmd.getColumnName(i+1) + "\t");
}
System.out.println("");

int count = 0;
while (cursor.next()) {
System.out.print(count++ + "==>\t");
for (int i=0; i<columnCount; i++) {
System.out.print(cursor.getString(i+1) + "\t");
}
System.out.println("");
}

System.out.println("done!");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ocstmt.close();
cstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}