先在schema下建立自定義類型(packages下的類型無效)
1.先建立一個object type
CREATE OR REPLACE TYPE PLANTINFO_TYPE AS OBJECT
(
PLANT VARCHAR2(6),
SUBPLANT VARCHAR2(6)
)
2.再建立一個collections type
CREATE OR REPLACE TYPE PLANTINFO_TAB_TYPE AS TABLE OF PLANTINFO_TYPE;
3.建立一個procedure,其參數引用以上的type
CREATE OR REPLACE PACKAGE MYPACK_TEST AS
PROCEDURE TEST2(PARAM1 IN MF_SAVEINFO_TAB_TYPE, PARAM2 OUT SYS_REFCURSOR);
END MYPACK_TEST;
CREATE OR REPLACE PACKAGE BODY MYPACK_TEST AS
PROCEDURE TEST2(PARAM1 IN MF_SAVEINFO_TAB_TYPE, PARAM2 OUT SYS_REFCURSOR) IS
BEGIN
OPEN PARAM2 FOR
SELECT PLANT, SUBPLANT FROM TABLE(PARAM1);
END TEST2;
END MYPACK_TEST;
如果想要在ASP.NET中調用以上procedure,直接用microsoft的oracleclient是沒有辦法實現的,必須引用Oracle公司出品的
ODP.NET,它是ODAC套件中的一個組件,請去Oracle官方網站下載.
假設你的環境已經建立好,那麼我們看如何用odp.net來調用這個存儲過程
1.先寫好一個class for oracle customer types
[OracleCustomTypeMappingAttribute("PLANTINFO_TYPE")]
public class PlantInfoFactory : IOracleCustomTypeFactory
{
#region IOracleCustomTypeFactory Members
public IOracleCustomType CreateObject()
{
return new PlantInfo();
}
#endregion
}
[OracleCustomTypeMappingAttribute("PLANTINFO_TAB_TYPE")]
public class PlantInfo_TabFactory : IOracleArrayTypeFactory
{
#region IOracleArrayTypeFactory Members
public Array CreateArray(int numElems)
{
return new PlantInfo[numElems];
}
public Array CreateStatusArray(int numElems)
{
return null;
}
#endregion
}
public class PlantInfo : IOracleCustomType
{
private bool p_mIsNull;
[OracleObjectMappingAttribute("PLANT")]
public String PLANT { get; set; }
[OracleObjectMappingAttribute("SUBPLANT")]
public String SUBPLANT { get; set; }
public static PlantInfo Null
{
get
{
PlantInfo info = new PlantInfo();
info.p_mIsNull = true; ;
return info;
}
}
#region INullable Members
public bool IsNull
{
get { return p_mIsNull; }
}
#endregion
#region IOracleCustomType Members
public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
if (PLANT != null)
OracleUdt.SetValue(con, pUdt, "PLANT", PLANT);
else
throw new NullReferenceException("SaveInfo.PLANT is null");
if (SUBPLANT != null)
OracleUdt.SetValue(con, pUdt, "SUBPLANT", SUBPLANT);
else
throw new NullReferenceException("SaveInfo.SUBPLANT is null");
}
public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
PLANT = (String)OracleUdt.GetValue(con, pUdt, "PLANT");
SUBPLANT = (String)OracleUdt.GetValue(con, pUdt, "SUBPLANT");
}
#endregion
}
2.寫方法直接調用procedure.如下:
string ConStr = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleServer)));User Id=root;Password=root;";
PlantInfo[] records = new PlantInfo[1];
PlantInfo record = new PlantInfo();
record.PLANT = "W000";
record.SUBPLANT = "MP";
records[0] = record;
//DataOP.Instance.ExecuteCommand("MYPACK_TEST.TEST2", new object[] { records, null });
using (OracleConnection oc = new OracleConnection(ConStr))
{
OracleCommand cmd = oc.CreateCommand();
cmd.CommandText = "MYPACK_TEST.TEST2";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter();
p1.OracleDbType = OracleDbType.Array;
p1.Direction = ParameterDirection.Input;
p1.UdtTypeName = "PLANTINFO_TAB_TYPE";
p1.Value = records;
cmd.Parameters.Add(p1);
OracleParameter p2 = new OracleParameter();
p2.OracleDbType = OracleDbType.RefCursor;
p2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p2);
oc.Open();
try
{
OracleDataReader reader = cmd.ExecuteReader();
//cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
dt.Load(reader);
}
catch (Exception ex)
{
throw ex;
}
finally
{
oc.Close();
}
}
3.上面的DataTable dt 就是調用procedure返回的結果.
至此,ASP.NET調用這种特殊的存儲過程的寫法就完成了.這种傳值的方法的好處是將值打包成一個對象傳遞給存儲過程,然後在存儲過程中再做存值等其它操作.