存储函数包的执行

XuguConnection conn = new XuguConnection();
conn.ConnectionString = conn_xugu;

try
{
    conn.Open();
    XuguCommand cmd = new XuguCommand();
    cmd.Connection = conn;
    cmd.CommandText = "select count(*) from user_tables where table_name='T_PACK_FUNC1'";//T_pack_func1
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
    {
        cmd.CommandText = "drop table T_PACK_FUNC1 cascade";
        cmd.ExecuteScalar();
    }

    cmd.CommandText = "create table T_pack_func1(c1 int ,c2 double,c3 datetime,c4 numeric(32,8),c5 varchar)";
    cmd.ExecuteNonQuery();
    cmd.CommandText = " insert into  T_pack_func1 values(1,null,'2017-07-10 15:01:35',33493.23423,'this is the func1 values 1')";
    cmd.ExecuteNonQuery();
    cmd.CommandText = " insert into  T_pack_func1 values( 2,23423.23,null,98763.2333,'here is the func1 the No2 value and so we need ')";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "insert into  T_pack_func1 values( 3,972.332,'2017-07-09 19:45:22',null,'so we get the third 3 value')";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "insert into  T_pack_func1 values( 4, 243.2342,'2017-07-04 20:35:18',3454.32,null)";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "insert into  T_pack_func1 values( 5,843.23,'2008-09-01 12:25:38',205.23,'')";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "select count(*) from user_tables where table_name='T_PACK_PAN1'";//T_pack_pan1
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
    {
        cmd.CommandText = "drop table T_PACK_PAN1 cascade";
        cmd.ExecuteScalar();
      }
      cmd.CommandText = "create table T_pack_pan1(c1 bigint,c2 char(50),c3 date,c4 int, c5 float)";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "insert into T_pack_pan1 values(123,'sdishosho hereess ','2017-07-01',null,324.56)";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "  insert into T_pack_pan1 values(456,'some get out the sdishosho hereess ',null,234,45.324) ";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "  insert into T_pack_pan1 values(789,null,'2017-07-03',34,23445.23) ";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "insert into T_pack_pan1 values(678,'the 4fourth in ','2017-07-04',44,null)";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "select count(*) from user_PACKAGES where PACK_name='PACK_NAME1'";//PACK_NAME1
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
    {
        cmd.CommandText = "alter package pack_name1 recompile";
        cmd.ExecuteScalar();
    }
    Else
    {
        string sql_pack_head = "create or replace  package pack_name1 is ";
        sql_pack_head += " function pa_func1(aa in int,";
        sql_pack_head += " bb in out double,";
        sql_pack_head += " cc out datetime,";
        sql_pack_head += " dd out numeric,";
        sql_pack_head += " ee out varchar)";
        sql_pack_head += " return datetime;";
        sql_pack_head += " procedure proc_pan1";
        sql_pack_head += "(aa in bigint,bb in out char(50), cc out date, dd out int, ee out float);";
        sql_pack_head += " end; ";
        cmd.CommandText = sql_pack_head;
        cmd.ExecuteNonQuery();
        string sql_pack_body = "create or replace package body pack_name1 is";
        sql_pack_body += " function pa_func1(";
        sql_pack_body += " aa in int,  bb in out double,  cc out datetime,  dd out numeric,  ee out varchar  )";
        sql_pack_body += " return datetime   as   TMP_DT DATETIME; begin";
        sql_pack_body += " select c2 ,c3,c4,c5 into bb,cc,dd,ee from T_pack_func1 where c1=aa;";
        sql_pack_body += " TMP_DT:=cc;"; //sql_pack_body += "";
            sql_pack_body += " return TMP_DT;end;";
        sql_pack_body += " procedure proc_pan1(";
            sql_pack_body += " aa in bigint,     bb in out char(50),     cc out date,     dd out int,     ee out float)";
        sql_pack_body += " as begin ";
        sql_pack_body += " select c2 ,c3,c4,c5 into bb,cc,dd,ee from T_pack_pan1  where c1=aa;";
        sql_pack_body += " end;  ";
        sql_pack_body += " end; ";
        cmd.CommandText = sql_pack_body;
            cmd.ExecuteNonQuery();
    }
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "PACK_NAME1.PA_FUNC1";
    XuguParameters aa = new XuguParameters("AA", XuguDbType.Int);
    aa.Direction = ParameterDirection.Input;
    aa.Value =2;//2 3 4 5
    cmd.Parameters.Add(aa);

    XuguParameters bb = new XuguParameters("BB", XuguDbType.Double);
    bb.Direction = ParameterDirection.InputOutput;
    bb.Value = 1.0;
    cmd.Parameters.Add(bb);

    XuguParameters cc = new XuguParameters("CC", XuguDbType.DateTime);
    cc.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(cc);
    XuguParameters dd = new XuguParameters("DD", XuguDbType.Numeric);
    dd.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(dd);

    XuguParameters ee = new XuguParameters("ee", XuguDbType.VarChar, 200);
    ee.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(ee);

    XuguParameters ff = new XuguParameters("ff", XuguDbType.DateTime);
    ff.Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.Add(ff);
    cmd.ExecuteNonQuery();

    Console.WriteLine("aa=" + aa.Value.ToString());
    Console.WriteLine("bb=" + bb.Value.ToString());
    Console.WriteLine("cc=" + cc.Value.ToString());
    Console.WriteLine("dd=" + dd.Value.ToString());
    Console.WriteLine("ee=" + ee.Value.ToString());
    Console.WriteLine("ff=" + ff.Value.ToString());
    return 0;           
}
catch (System.Exception ex)
{
    Console.WriteLine(ex.ToString());
    conn.Close();
    return 0;
}