CodeSmith获取oracle存储过程或者包的参数,oracle有个一系统表,记录了没个存储过程包表的详细信息,包括参数类型,参数方向,我们只要从这个表获取想要存储过程或者包的参数那就可以达到目的了…当然获取参数是为了可以自动生成数据库层的代码…上网基本都是讲MSSQL获取参数..获取参数我写了2个package,利用这2个package就可以获取到想要的参数了.
测试平台:ORACLE9I+VS2003+CodeSmith4.12

Code
1
CREATE OR REPLACE PACKAGE BODY GETPARAMETERS AS
2
3
4
PROCEDURE GETPARAMETERSLIST(
5
6
I_PROCNAME IN VARCHAR2, --GET PACKAGE NAME
7
8
I_OBJECT_TYPE IN VARCHAR2, -- PACKAGE OR PROCEDURR
9
10
I_OBJECT_NAME IN VARCHAR2,
11
12
O_CURSOR OUT T_CURSOR) --RETURN CURSOR
13
14
IS
15
16
BEGIN
17
18
OPEN O_CURSOR FOR
19
20
SELECT * FROM ALL_ARGUMENTS WHERE OBJECT_ID =
21
22
(SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_TYPE=I_OBJECT_TYPE
23
24
AND OBJECT_NAME =I_PROCNAME) and OBJECT_NAME =I_OBJECT_NAME;
25
26
END GETPARAMETERSLIST;
27
28
END GETPARAMETERS;
29
30
/
31
32
33
34
35
36
37
38
39
40
CREATE OR REPLACE PACKAGE GETBODY AS
41
42
TYPE T_CURSOR IS REF CURSOR;
43
44
PROCEDURE GETBODY(I_PROCNAME IN VARCHAR2,I_OBJECT_TYPE IN VARCHAR2,
45
46
O_CURSOR OUT T_CURSOR);
47
48
END GETBODY;
49
50
/
51
52
53
54
55
56
57
58
CREATE OR REPLACE PACKAGE BODY GETBODY AS
59
60
PROCEDURE GETBODY(
61
62
I_PROCNAME IN VARCHAR2, --GET PACKAGE NAME
63
64
I_OBJECT_TYPE IN VARCHAR2, -- PACKAGE OR PROCEDURR
65
66
O_CURSOR OUT T_CURSOR) --RETURN CURSOR
67
68
IS
69
70
BEGIN
71
72
OPEN O_CURSOR FOR
73
74
SELECT object_name FROM ALL_ARGUMENTS WHERE OBJECT_ID =
75
76
(SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_TYPE=I_OBJECT_TYPE
77
78
AND OBJECT_NAME =I_PROCNAME ) group by object_name;
79
80
END GETBODY;
81
82
END GETBODY;
83
84
/
85

Code
1
<%@ CodeTemplate Language="C#" TargetLanguage="C#" ResponseEncoding="UTF-8" Description="Generates a update stored procedure." %>
2
3
<%@ Property Name="PROC_NAME" Type="System.String" Category="Context" Description="存储过程或者包名" %>
4
5
<%@ Property Name="PROC_TYPE" Type="OBJECTTYPE" Category="Context" Description="选择是存储过程还是包" %>
6
7
<%@ Assembly Name="System.Data.OracleClient" %>
8
9
<%@ Import Namespace="System.Data.OracleClient" %>
10
11
using System;
12
13
using System.Data;
14
15
using System.Data.OracleClient;
16
17
namespace <%= PROC_NAME%>
18
19

{
20
21
public class <%= PROC_NAME%>
22
23
{
24
25
<%
26
27
DataSet ds = new DataSet();
28
29
DataSet dsList = new DataSet();
30
31
ds = GETBODY();
32
33
string strBody;
34
35
System.Text.StringBuilder builder = new System.Text.StringBuilder();
36
37
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
38
39
{
40
41
builder.Append("\n\t\t包內过程");
42
43
builder.Append(ds.Tables[0].Rows[i][0].ToString());
44
45
builder.Append("\n\t\t过程参数:\n");
46
47
strBody = ds.Tables[0].Rows[i][0].ToString();
48
49
dsList = GETPARAMETERSLIST(strBody);
50
51
for(int j = 0 ; j < dsList.Tables[0].Rows.Count; j++)
52
53
{
54
55
if(!(dsList.Tables[0].Rows[j][5].ToString() == ""))
56
57
{
58
59
builder.Append("\t\t"+dsList.Tables[0].Rows[j][5].ToString());
60
61
builder.Append("\t"+dsList.Tables[0].Rows[j][9].ToString());
62
63
builder.Append("\t"+dsList.Tables[0].Rows[j][12].ToString());
64
65
builder.Append("\n");
66
67
}
68
69
}
70
71
}
72
73
Response.Write(builder.ToString());
74
75
%>
76
77
}
78
79
}
80
81
<script runat="template">
82

过程参数列表
1
public DataSet GETPARAMETERSLIST(string packbody)
2
3

{
4
5
OracleConnection con = new OracleConnection("Data Source=ecstest;user id=apps;password=fnd");
6
7
8
9
OracleCommand cmd = new OracleCommand();
10
11
12
13
cmd.CommandType = CommandType.StoredProcedure;
14
15
16
17
cmd.CommandText = "GETPARAMETERS.GETPARAMETERSLIST";
18
19
20
21
cmd.Connection = con;
22
23
24
25
OracleParameter I_PROCNAME = new OracleParameter("I_PROCNAME",OracleType.VarChar);
26
27
28
29
I_PROCNAME.Direction = ParameterDirection.Input;
30
31
32
33
OracleParameter I_OBJECT_TYPE = new OracleParameter("I_OBJECT_TYPE", OracleType.VarChar);
34
35
36
37
I_OBJECT_TYPE.Direction = ParameterDirection.Input;
38
39
40
41
OracleParameter I_OBJECT_NAME = new OracleParameter("I_OBJECT_NAME", OracleType.VarChar);
42
43
44
45
I_OBJECT_NAME.Direction = ParameterDirection.Input;
46
47
48
49
OracleParameter O_CURSOR = new OracleParameter("O_CURSOR", OracleType.Cursor);
50
51
52
53
O_CURSOR.Direction = ParameterDirection.Output;
54
55
56
57
cmd.Parameters.Add(I_PROCNAME);
58
59
60
61
cmd.Parameters.Add(I_OBJECT_TYPE);
62
63
64
65
cmd.Parameters.Add(I_OBJECT_NAME);
66
67
68
69
cmd.Parameters.Add(O_CURSOR);
70
71
72
73
cmd.Parameters["I_PROCNAME"].Value = PROC_NAME;
74
75
76
77
cmd.Parameters["I_OBJECT_TYPE"].Value = PROC_TYPE;
78
79
80
81
cmd.Parameters["I_OBJECT_NAME"].Value = packbody;
82
83
84
85
OracleDataAdapter sda = new OracleDataAdapter();
86
87
88
89
sda.SelectCommand = cmd;
90
91
92
93
DataSet ds = new DataSet();
94
95
96
97
sda.Fill(ds);
98
99
100
101
return ds;
102
103
104
105
}
106
107
108
109

包內过程数
1
2
3
public DataSet GETBODY()
4
5

{
6
7
OracleConnection con = new OracleConnection("Data Source=ecstest;user id=apps;password=fnd");
8
9
10
11
OracleCommand cmd = new OracleCommand();
12
13
14
15
cmd.CommandType = CommandType.StoredProcedure;
16
17
18
19
cmd.CommandText = "GETBODY.GETBODY";
20
21
22
23
cmd.Connection = con;
24
25
26
27
OracleParameter I_PROCNAME = new OracleParameter("I_PROCNAME",OracleType.VarChar);
28
29
30
31
I_PROCNAME.Direction = ParameterDirection.Input;
32
33
34
35
OracleParameter I_OBJECT_TYPE = new OracleParameter("I_OBJECT_TYPE", OracleType.VarChar);
36
37
38
39
I_OBJECT_TYPE.Direction = ParameterDirection.Input;
40
41
42
43
OracleParameter O_CURSOR = new OracleParameter("O_CURSOR", OracleType.Cursor);
44
45
46
47
O_CURSOR.Direction = ParameterDirection.Output;
48
49
50
51
cmd.Parameters.Add(I_PROCNAME);
52
53
54
55
cmd.Parameters.Add(I_OBJECT_TYPE);
56
57
58
59
cmd.Parameters.Add(O_CURSOR);
60
61
62
63
cmd.Parameters["I_PROCNAME"].Value = PROC_NAME;
64
65
66
67
cmd.Parameters["I_OBJECT_TYPE"].Value = PROC_TYPE;
68
69
70
71
OracleDataAdapter sda = new OracleDataAdapter();
72
73
74
75
sda.SelectCommand = cmd;
76
77
78
79
DataSet ds = new DataSet();
80
81
82
83
sda.Fill(ds);
84
85
86
87
return ds;
88
89
90
91
}
92
93
完整代码下载
https://files.cnblogs.com/mextb1860/Desktop.zip