1.直接调用存储过程 2 是用包的方式调用存储过程 3
调用存储过程且该存储过程返回记录集 这种必须使用包的方式
1.直接调用存储过程
测试存储过程为：
createorreplaceproceduretest(FKSSQINVARCHAR2,aaaoutvarchar2)is
begin
aaa:=substrb(fkssq,1,1);
endtest;
代码为：
mand
mandText="TEST"'存储过程的名字
mandType=mandType.StoredProcedure‘类型是存储过程
test.Connection=conn_whg’连接
Diminpara,outparaAsOleDb.OleDbParameter
inpara=NewSystem.Data.OleDb.OleDbParameter("fkssq",System.Data.OleDb.OleDbType.VarWChar,10)’"fkssq"存储过程的第一个参数名字
inpara.Direction=ParameterDirection.Input‘设置第一个参数的类型和是输入参数
inpara.Value="678678"’给输入参数赋值
outpara=NewSystem.Data.OleDb.OleDbParameter("aaa",System.Data.OleDb.OleDbType.VarChar,10)’"aaa"存储过程的输出参数名字
outpara.Direction=ParameterDirection.Output‘输出参数指点类型
test.Parameters.Add(inpara)‘加入参数
test.Parameters.Add(outpara)
DimbbAsInteger
conn_whg.Open()'打开链接
bb=test.ExecuteNonQuery()
Ifbb=1Then
MsgBox("OK!")
MessageBox.Show(test.Parameters("aaa").Value)’取得输出参数的值
Else
MsgBox("Fail!")
EndIf
conn_whg.Close()‘关闭连接
2
是用包的方式调用存储过程 (传入两个参数。输出两个参数)
创建包
CREATE OR REPLACE PACKAGE PKG_APUP1110 AS
procedure APUP1110(FAC_CD_in IN CHAR,
UPD_MNTH_in IN CHAR,
RETURNMARK OUT NUMBER,
MSG OUT VARCHAR2) ;
END PKG_APUP1110;
创建包体
CREATE OR REPLACE PACKAGE BODY PKG_APUP1110
IS
procedure APUP1110(FAC_CD_in IN CHAR,
UPD_MNTH_in IN CHAR,
RETURNMARK OUT NUMBER,
MSG OUT VARCHAR
)
ASFAC_CD CHAR(12);'定义类型
UPD_MNTH
CHAR(6);begin
FAC_CD := FAC_CD_in;‘赋值
UPD_MNTH :=UPD_MNTH_in;
commit;RETURNMARK :=0; ’输出参数赋值
EXCEPTION
WHEN OTHERS THEN
RETURNMARK :=1;MSG :=SUBSTR(SQLERRM,1,100);
ROLLBACK;
end APUP1110;
END PKG_APUP1110;
代码
Dim command As OleDb.OleDbCommand
Dim conn As New OleDb.OleDbConnection
Dim str_date As String
str_date = Trim(Txt_IPT1.Text) + Trim(Txt_IPT2.Text)
Try
'conn.ConnectionString = "Password=HAT_V30;User ID=HAT_V30;Data
Source=PRONES_94"
conn.ConnectionString = TApp.ConnectionString连接方式
command = New OleDb.OleDbCommand("PKG_APUP1110.APUP1110",
conn)包体名字.存储过程名字
mandType = CommandType.StoredProcedure'表示是存储过程
command.Parameters.Add("FAC_CD_in", OleDb.OleDbType.Char,
2000).Direction = ParameterDirection.Input
command.Parameters("FAC_CD_in").Value = Trim(AppSet.FAC_CD)
command.Parameters.Add("UPD_MNTH_in", OleDb.OleDbType.Char,
2000).Direction = ParameterDirection.Input
command.Parameters("UPD_MNTH_in").Value = Trim(str_date)
command.Parameters.Add("RETURNMARK", OleDb.OleDbType.Numeric,
2000).Direction = ParameterDirection.Output
command.Parameters.Add("MSG", OleDb.OleDbType.VarChar,
2000).Direction = ParameterDirection.Output
conn.Open()
command.ExecuteNonQuery()
If CInt(command.Parameters("RETURNMARK").Value) = 1 Then’得到一个输出参数
进行判断存储过程是否执行正确
'MsgBox(command.Parameters("MSG").Value.ToString,
MsgBoxStyle.Information)
MsgBox(GetMSG("41102"), MsgBoxStyle.Information)
Else
MsgBox(GetMSG("14101"), MsgBoxStyle.Information)
End If
Catch ex As Exception
MsgBox(ex.ToString, MsgBoxStyle.Information)
Finally
conn.Close()
End Try
3 调用存储过程且该存储过程返回记录集
这种必须使用包
注意创建包体的内容 有个输出游标 myrctype
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE
sp_RLZQX(RETCODE OUT varchar2,ERRBUF OUT varchar2,p_rc OUT
myrctype);
END pkg_test;
创建包体
CREATE OR REPLACE PACKAGE BODY pkg_test
IS
PROCEDURE sp_RLZQX (RETCODE OUT varchar2,ERRBUF
OUT varchar2, p_rc OUT myrctype)‘ p_rc 为输出的记录集
AS
sqlStrvarchar2(32367);
sqlStrsqlvarchar2(32367);
--构造字符串
cursor getcursor IS SELECT
DISTINCTI_GROUP from AT_NYURYOKUSYA_KENGEN_MS;
定义游标
BEGIN
sqlStr:= 'selectI_GYOMU_NO,ID ';
FOR CUR IN GETCURSOR LOOP’循环游标
sqlStr:= sqlStr || ',sum(case trim(I_GROUP) when''' ||trim( CUR.I_GROUP) || '''thenI_KENGEN end ) as ' || CUR.I_GROUP;
END LOOP;
sqlStr:=sqlStr ||' from
AT_NYURYOKUSYA_KENGEN_MS GROUP BY I_GYOMU_NO,ID ';
-- open p_rc
forsqlStr;
execute
immediate sqlStr;执行字符串
sqlStrsql := 'SELECT B.I_DL_CD 业务编号, B.I_DL_DESC 业务名 ,
A.*FROM('||sqlStr
||')ARIGHT
JOINT_TRAde_ms B ON A.I_GYOMU_NO = B.I_DL_CD
ORDER BY B.I_DL_CD, A.ID';
open p_rc FORSELECT 1 FROM DUAL;返回的结果集
EXCEPTION
WHEN OTHERS THEN
RETCODE := SQLCODE;
ERRBUF:= SUBSTR(SQLERRM, 1, 100);
END sp_RLZQX;
END pkg_test;
代码
Dim command As OracleClient.OracleCommand
Dim conn As New OracleClient.OracleConnection
Dim dR As System.Data.datatable
' Dim oracleparameter As New OracleClient.OracleParameter("p_rc",
OracleType.Cursor)
Try
conn.ConnectionString = "Password=HAT_V30;User ID=HAT_V30;Data
Source=PRONES_94"
command = New OracleClient.OracleCommand("pkg_test.sp_RLZQX",
conn)
mandType = CommandType.StoredProcedure
command.Parameters.Add("RETCODE", OracleType.Char, 2000).Direction
= ParameterDirection.Output
command.Parameters.Add("ERRBUF", OracleType.Char, 2000).Direction =
ParameterDirection.Output
command.Parameters.Add("p_rc", OracleType.Cursor).Direction =
ParameterDirection.Output
conn.Open()
Command.ExecuteNonQuery()
dR = command.Parameters("p_rc").Value直接给DAtatable
就是得到的记录集
--------------------------------------------------------------------------也可以是下面这张来转换为table
Dim dR As
System.Data.OracleClient.OracleDataReader
dR =
command.Parameters("p_rc").Value
If dR.HasRows Then
Dim DT As New DataTable
DT.Columns.Add("I_DL_CD")
DT.Columns.Add("I_DL_DESC")
DT.Columns.Add("I_GYOMU_NO")
DT.Columns.Add("ID")
DT.Columns.Add("TEST2")
DT.Columns.Add("TEST1")
While dR.Read
Dim aRow As DataRow = DT.NewRow
aRow.Item("I_DL_CD") = dR.Item("I_DL_CD")
aRow.Item("I_DL_DESC") = dR.Item("I_DL_DESC")
aRow.Item("I_GYOMU_NO") = dR.Item("I_GYOMU_NO")
aRow.Item("ID") = dR.Item("ID")
aRow.Item("TEST2") = dR.Item("TEST2")
aRow.Item("TEST1") = dR.Item("TEST1")
DT.Rows.Add(aRow)DT为得到的记录集
End While
End If
oracle调用存储过程output 调用oracle的存储过程的方法（带输入参数和输出参数或者返回记录集）...
