-
Notifications
You must be signed in to change notification settings - Fork 77
Expand file tree
/
Copy pathtestVariableInProc.java
More file actions
132 lines (125 loc) · 6.06 KB
/
Copy pathtestVariableInProc.java
File metadata and controls
132 lines (125 loc) · 6.06 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
package oracle;
import gudusoft.gsqlparser.*;
import gudusoft.gsqlparser.nodes.*;
import gudusoft.gsqlparser.sqlenv.TDDLSQLEnv;
import gudusoft.gsqlparser.stmt.TInsertSqlStatement;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure;
import junit.framework.TestCase;
public class testVariableInProc extends TestCase {
public static void test1(){
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "CREATE TABLE MARKETDATA_TRADE\n" +
"(\n" +
" tradeID NUMBER,\n" +
" chappyDataID VARCHAR2,\n" +
" strumentID NUMBER\n" +
");\n" +
"\n" +
"CREATE TABLE MARKETDATA_TRADE_2\n" +
"(\n" +
" tradeID NUMBER,\n" +
" chappyDataID VARCHAR2,\n" +
" strumentID NUMBER\n" +
");\n" +
"\n" +
"CREATE PROCEDURE doSomething\n" +
"AS\n" +
" v_row MARKETDATA_TRADE%ROWTYPE;\n" +
"BEGIN\n" +
"\n" +
" SELECT * INTO v_row FROM MARKETDATA_TRADE WHERE tradeID = 1;\n" +
"\n" +
" persistTrade(v_row.tradeID, v_row.chappyDataID, v_row.strumentID);\n" +
"\n" +
"END doSomething;\n" +
"\n" +
"CREATE PROCEDURE persistTrade(tradeID OUT NUMBER, chappyDataID IN VARCHAR2, instrumentID OUT NUMBER)\n" +
"AS\n" +
" instrumentCount NUMBER;\n" +
"BEGIN\n" +
" SELECT MARKETDATA_TRADE_SEQ.NEXTVAL INTO tradeID FROM dual;\n" +
"\n" +
" SELECT COUNT(*) INTO instrumentCount\n" +
" FROM instrument_mastersecurity m\n" +
" WHERE m.cusip = theCusip;\n" +
"\n" +
" IF instrumentCount > 0 THEN\n" +
" SELECT m.id INTO instrumentID\n" +
" FROM instrument_mastersecurity m\n" +
" WHERE m.cusip = theCusip;\n" +
" END IF;\n" +
"\n" +
" INSERT INTO MARKETDATA_TRADE_2 VALUES(tradeID, instrumentID, chappyDataID);\n" +
"\n" +
"END persistTrade;";
assertTrue(sqlparser.parse() == 0);
TPlsqlCreateProcedure procedure = (TPlsqlCreateProcedure) sqlparser.sqlstatements.get(3);
TCustomSqlStatement sqlStatement = procedure.getBodyStatements().get(3);
assertTrue(sqlStatement.sqlstatementtype == ESqlStatementType.sstinsert);
TInsertSqlStatement insert = (TInsertSqlStatement) sqlStatement;
TResultColumn value0 = insert.getValues().getMultiTarget(0).getColumnList().getResultColumn(0);
TObjectName variable0 = value0.getExpr().getObjectOperand();
assertTrue (variable0.getDbObjectType() == EDbObjectType.variable);
assertTrue(variable0.getSourceTable() == null);
}
public void test2(){
String sql = "CREATE TABLE MARKETDATA_TRADE\n" +
"(\n" +
" tradeID NUMBER,\n" +
" chappyDataID VARCHAR2,\n" +
" strumentID NUMBER\n" +
");\n" +
"\n" +
"CREATE TABLE MARKETDATA_TRADE_2\n" +
"(\n" +
" tradeID NUMBER,\n" +
" chappyDataID VARCHAR2,\n" +
" strumentID NUMBER\n" +
");\n" +
"\n" +
"CREATE PROCEDURE doSomething\n" +
"AS\n" +
" v_row MARKETDATA_TRADE%ROWTYPE;\n" +
"BEGIN\n" +
"\n" +
" SELECT * INTO v_row FROM MARKETDATA_TRADE WHERE tradeID = 1;\n" +
"\n" +
" persistTrade(v_row.tradeID, v_row.chappyDataID, v_row.strumentID);\n" +
"\n" +
"END doSomething;\n" +
"\n" +
"CREATE PROCEDURE persistTrade(tradeID OUT NUMBER, chappyDataID IN VARCHAR2, instrumentID OUT NUMBER)\n" +
"AS\n" +
" instrumentCount NUMBER;\n" +
"BEGIN\n" +
" SELECT MARKETDATA_TRADE_SEQ.NEXTVAL INTO tradeID FROM dual;\n" +
"\n" +
" SELECT COUNT(*) INTO instrumentCount\n" +
" FROM instrument_mastersecurity m\n" +
" WHERE m.cusip = theCusip;\n" +
"\n" +
" IF instrumentCount > 0 THEN\n" +
" SELECT m.id INTO instrumentID\n" +
" FROM instrument_mastersecurity m\n" +
" WHERE m.cusip = theCusip;\n" +
" END IF;\n" +
"\n" +
" INSERT INTO MARKETDATA_TRADE_2 VALUES(tradeID, instrumentID, chappyDataID);\n" +
"\n" +
"END persistTrade;";
TDDLSQLEnv ddlSQLEnv = new TDDLSQLEnv(null, null, null, null, EDbVendor.dbvoracle, sql);
ddlSQLEnv.initSQLEnv();
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = sql;
sqlparser.setSqlEnv(ddlSQLEnv);
assertTrue(sqlparser.parse() == 0);
TPlsqlCreateProcedure procedure = (TPlsqlCreateProcedure) sqlparser.sqlstatements.get(3);
TCustomSqlStatement sqlStatement = procedure.getBodyStatements().get(3);
assertTrue(sqlStatement.sqlstatementtype == ESqlStatementType.sstinsert);
TInsertSqlStatement insert = (TInsertSqlStatement) sqlStatement;
TResultColumn value0 = insert.getValues().getMultiTarget(0).getColumnList().getResultColumn(0);
TObjectName variable0 = value0.getExpr().getObjectOperand();
assertTrue (variable0.getDbObjectType() == EDbObjectType.variable);
assertTrue(variable0.getSourceTable() == null);
}
}