虚谷文档 v1.0.1

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


示例 ​

该章节展示虚谷 C# 驱动简易使用方法，详细 Demo 项目，部署在gitee仓库。

多IP创建数据库连接 ​

多IP连接使用IPS键值传递IP信息。

cs
XGConnection t_Conn = new XGConnection();
t_Conn.ConnectionString = "IPS=127.0.0.1,127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON; CHAR_SET=GBK";
t_Conn.Open();
XGCommand cmd2 = t_Conn.CreateCommand();
cmd2.CommandText = "select table_name as db_id,db_id as table_name from all_tables;";

创表等初始化SQL环境语句 ​

cs
XGConnection conn = new XGConnection();
conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;";
Try
{
    conn.Open();
    XGCommand cmd = new XGCommand();
    cmd.Connection = conn;
    cmd.CommandText = "select count(*) from user_tables where table_name='TA'";
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
    {
         cmd.CommandText = "drop table TA cascade";
         cmd.ExecuteNonQuery();
    }
    cmd.CommandText = "select count(*) from user_tables where table_name='TP'";
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
    {
         cmd.CommandText = "drop table TP cascade";
         cmd.ExecuteNonQuery();
    }
    cmd.CommandText = "select count(*) from user_sequences where seq_name='SEQ_TP'";
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
    {
        cmd.CommandText = "drop sequence SEQ_TP cascade";
        cmd.ExecuteNonQuery();
    }
    cmd.CommandText = "select count(*) from user_sequences where seq_name='SEQ_TA'";
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
    {
        cmd.CommandText = "drop sequence SEQ_TA cascade";
        cmd.ExecuteNonQuery();
    }
    // 覆盖 CommandText 重复执行 SQL
    string sql_str1 = "create table ta(id number,pid integer,p_float float,p_double double,pkey bigint,p_sint smallint,p_tint tinyint,name char(100),descri varchar(100),modify_time datetime default sysdate,p_numr numeric(4,2),p_clob clob,p_bool boolean,p_date date default sysdate,p_time time default sysdate,p_blob blob)";
    string sql_str2 = "create table tp(id number,pid integer,pname char(100),descri varchar(100),modify_time datetime default sysdate)";
    string sql_str3 = "create sequence seq_ta minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20";
    string sql_str4 = "create sequence seq_tp minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20";
    string sql_str5 = "create or replace view v_ap as  select ta.id as id1,tp.id id2,ta.name,tp.pname,ta.modify_time from tp left join ta on tp.pid=ta.pid";
    string sql_str6 = "create or replace trigger trig_identity_ta before insert on ta for each row begin if inserting and :new.id is null then  :new.id := seq_ta.nextval; end if;   end trig_identity_ta;";
    string sql_str7 = "create or replace trigger trig_identity_tp before insert on tp for each row begin if inserting and :new.id is null then  :new.id := seq_tp.nextval; end if;  end trig_identity_tp;";

    cmd.CommandText = sql_str1;
    cmd.ExecuteNonQuery();
    cmd.CommandText = sql_str2;
    cmd.ExecuteNonQuery();
    cmd.CommandText = sql_str3;
    cmd.ExecuteNonQuery();
    cmd.CommandText = sql_str4;
    cmd.ExecuteNonQuery();
    cmd.CommandText = sql_str5;
    cmd.ExecuteNonQuery();
    cmd.CommandText = sql_str6;
    cmd.ExecuteNonQuery();
    cmd.CommandText = sql_str7;
    cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
    Console.WriteLine(ex.ToString());
    conn.Close();
    Console.WriteLine("测试关闭连接后连接当前状态" + conn.State.ToString());
}

不带参数的数据录入 ​

cs
XGConnection conn = new XGConnection();
conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;";
Try
{
    string insert_sql_ta = "insert into ta(pid,name) values(1,'张三')";
    string insert_sql_tp = "insert into tp(pid,pname,modify_time) values(1,'财务部',to_date('2016-04-21 12:12:00','yyyy-mm-dd hh:mi:ss'))";

    conn.Open();
    XGCommand cmd = new XGCommand();
    cmd.Connection = conn;
    cmd.CommandText = insert_sql_ta;
    cmd.ExecuteNonQuery();

    cmd.CommandText = insert_sql_tp;
    cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
    Console.WriteLine(ex.ToString());
    conn.Close();
    Console.WriteLine("测试关闭连接后连接当前状态" + conn.State.ToString());
}

带参数SQL的数据录入 ​

cs
XGConnection conn = new XGConnection();
conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;" ;
Try
{
    conn.Open();
    XGCommand cmd = new XGCommand();
    cmd.Connection = conn;
    string sql = "insert into tp(pid,pname) values(?,?)";
    cmd.CommandText = sql;
    XGTransaction trans = conn.BeginTransaction();//为连接创建显示事务，此时该连接事务处于非自动提交状态
    cmd.Transaction = trans;//指定申明命令事务环境

    //参数赋值
    cmd.Parameters.Add("pid", XGDbType.Int).Value = 2;
    cmd.Parameters.Add("pname", XGDbType.VarChar).Value = "开发部";
    cmd.ExecuteNonQuery();
    //重复执行参数赋值前，需清除前次参数
    cmd.Parameters.Clear();
    cmd.Parameters.Add("pid", XGDbType.Int ).Value = 3;
    cmd.Parameters.Add("pname", XGDbType.VarChar).Value = "测试部";
    cmd.ExecuteNonQuery();

    trans.Commit();//提交命令事务
    return 1;
}
catch (System.Exception ex)
{
    return 0;
}

结果集获取 ​

cs
XGConnection conn = new XGConnection();
conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;";

try
{
    conn.Open();
    XGCommand cmd = new XGCommand();
    cmd.Connection = conn;
    string sql_sql = "select * from ta";
    XGDataAdapter da = new XGDataAdapter(sql_sql, conn);
    DataSet ds = new DataSet();
    da.Fill(ds, "ta");
    for (int i = 0; i < ds.Tables[0].Rows.Count;i++ )
    {
        string val = "人员表数据: ";
        for (int j = 0; j < ds.Tables[0].Columns.Count;j++ )
        {
            val += ds.Tables[0].Rows[i][j].ToString()+"|";
        }
    }
    string sql_str2 = "select * from tp";
    XGCommand cmd2 = conn.CreateCommand();// 或者 new DBCommand(sql_str, conn);
    cmd.CommandText = sql_str2;
    XGDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        string val = "人员数据: ";
        string id = reader.GetInt32(0).ToString();
        string pid = reader.GetString(1);
        string pname = reader.GetString(2);
        string desc = reader.IsDBNull(3) ? null : reader.GetString(3);
        string date = reader.GetDateTime(4).ToString();
        val += id + "|" + pid + "|" + pname + "|" + desc + "|" + date + "|";
        Console.WriteLine(val);
    }
}
catch (System.Exception ex)
{
    Console.WriteLine(ex.ToString());
    conn.Close();
    return 0;
}

DataSet 更新数据库 ​

cs
XGConnection conn = new XGConnection();
conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;";

try
{
    conn.Open();
    XGCommand cmd = new XGCommand();
    cmd.Connection = conn;
    string sql_sql = "select * from tp";
    XGDataAdapter adapter = new XGDataAdapter();
    adapter.SelectCommand = new XGCommand(queryString, conn);
    string tableName = "tp";
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet, tableName);
    foreach (DataRow dr in dataSet.Tables["tp"].Rows)
    {
        if (dr["PNAME"].ToString().Trim().Equals("测试部"))
          {
              dr.Delete(); //删除DataSet 中的行
              break;
          }
     }
    dataSet.Tables[tableName].Rows[0][1] = 38;//更新DataSet中第一行第2列的值
    dataSet.Tables[tableName].Rows[1][3] = "开发项目";
    string[] dd = new String[5] { "124", "24", "dsf", "dsgrt", "2016-12-12 12:11:11" };
    dataSet.Tables[tableName].Rows.Add(dd);//增加一行 考虑参数的形式
    //增加一行
    adapter.Update(dataSet, tableName);

    return 0;
}
catch (System.Exception ex)
{
    Console.WriteLine(ex.ToString());
    conn.Close();
    return 0;
}

大对象应用 ​

cs
// 提前准备好文件
public static string filepath = "D:\\csharp_data\\data254.mp4";
//blob 
public static int test_blob()
{
    XGConnection conn = new XGConnection();
    conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;";
    Try
    {
        conn.Open();
        XGCommand cmd = new XGCommand();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME='TEST_BLOB'";
        if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
        {
            cmd.CommandText = "CREATE TABLE TEST_BLOB(ID INT,SS BLOB)";
            cmd.ExecuteNonQuery();
        }
        else
        {
            cmd.CommandText = "DROP TABLE TEST_BLOB";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE TEST_BLOB(ID INT,SS BLOB)";
            cmd.ExecuteNonQuery();
        }
        cmd.CommandText = "INSERT INTO TEST_BLOB VALUES(1,NULL)";
        cmd.ExecuteNonQuery();
        cmd.CommandText = "UPDATE TEST_BLOB SET SS=? WHERE ID=1";
        XGBlob xb = new XGBlob();
        FileAccess acces = FileAccess.Read;
        FileMode filemode = FileMode.Open;
        long big_count = 0;
        using (FileStream fs = new FileStream(filepath, filemode, acces))
        {
            byte[] b = new byte[64768];
            Int64 amountRead = 0;
            amountRead = fs.Read(b, 0, b.Length);
            xb.BeginChunkWrite();
            Int64 have_r = amountRead;
            while (amountRead > 0)
            {
                big_count += amountRead;// count
                have_r = have_r + b.Length;
                xb.write(b, 0, (Int32)amountRead);
                amountRead = fs.Read(b, 0, b.Length);
            }
            xb.EndChunkWrite();
        }
        cmd.Parameters.Add("?", XGDbType.LongVarBinary, xb, ParameterDirection.Input);
        cmd.ExecuteNonQuery();
        xb.Close();
    }
    catch (Exception ei)
    {
        Console.WriteLine(ei.ToString());
    }
    finally
    {
        conn.Close();
        Console.WriteLine("测试 关闭连接后 连接当前状态" + conn.State.ToString());
    }
    return 0;
}

存储函数包的执行 ​

cs
XGConnection conn = new XGConnection();
conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;";

try
{
    conn.Open();
    XGCommand cmd = new XGCommand();
    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";
    XGParameters aa = new XGParameters("AA", XGDbType.Int);
    aa.Direction = ParameterDirection.Input;
    aa.Value =2;//2 3 4 5
    cmd.Parameters.Add(aa);

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

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

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

    XGParameters ff = new XGParameters("ff", XGDbType.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;
}

存储函数带引用型游标输出结果集 ​

使用 xgconsole 或其他管理工具连接虚谷数据库实例，在 SYSTEM 库中执行此章节所依赖的前置 SQL 。 SQL 如下：

SQL
create table ta(id number,pid integer, name char(100),descri varchar(100),modify_time datetime default sysdate,   p_date date default sysdate,p_time time default sysdate ) ;
create table tp(id number,pid integer,pname char(100),descri varchar(100),modify_time datetime default sysdate);
insert into tp values(1, 38 , '财务部', '测试数据变更', '2017-12-12 12:11:11' );
insert into tp values(2, 1 , '财务部', '开发项目', '2017-12-12 12:11:11');
insert into tp values( 3, 2 , '开发部', '测试数据变更', '2017-12-12 12:11:11');
insert into tp values(124,24 , 'dsf', 'dsgrt', '2016-12-12 12:11:11');

insert into ta values( 1, 1 , '张三', NULL, '2017-08-01 09:34:49.458' , '2017-08-01',  '09:34:49.458') ;
insert into ta values( 2, 2 , '李四', NULL, '2017-08-01 09:36:51.113' , '2017-08-01',  '09:36:51.113') ;
insert into ta values( 3, 2 , '王五', NULL, '2017-08-01 09:36:53.427' , '2017-08-01',  '09:36:53.427') ;

create or replace function myfunc_outrefur(refcur_out  out sys_refcursor)
  return sys_refcursor is refcur_ret sys_refcursor;
  begin
  open refcur_ret for  select * from ta;
  open refcur_out for   select * from tp;
  return refcur_ret;
  end ;

C# 示例代码如下：

CS
XGConnection conn = new XGConnection();
conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;";
try
{
    conn.Open();
    XGCommand cmd = new XGCommand();
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "myfunc_outrefur";
    cmd.Parameters.Clear();

    XGParameters pa1= new XGParameters("refcur1",XGDbType.RefCursor);
    pa1.Direction= ParameterDirection.Output;
    cmd.Parameters.Add(pa1);

    XGParameters pa2 = new XGParameters("refcur2", XGDbType.RefCursor);
    pa2.Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.Add(pa2);
    ret = cmd.ExecuteNonQuery();
    //output to reader
    XGDataReader reader_out1 = ((XGRefCursor)pa1.Value).GetDataReader();
    Console.WriteLine("reader_out1.getname(2) = " + reader_out1.GetName(2));
    XGDataReader reader_out2 = ((XGRefCursor)pa2.Value).GetDataReader();
    Console.WriteLine("reader_out2.getname(2) = " + reader_out2.GetName(2));
    reader_out1.Close();
    reader_out1.Dispose();
    reader_out2.Close();
    reader_out2.Dispose();
    //
    cmd.Parameters.Clear();
    cmd.Dispose();
    conn.Close();
    conn.Dispose();
}
catch (System.Exception ex)
{
    Console.WriteLine(ex.ToString());
    conn.Close();
    return 0;
}

存储过程输入参数的执行 ​

cs
XGConnection conn = new XGConnection();
conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;";
try
{
    conn.Open();
    XGCommand cmd = new XGCommand();
    cmd.Connection = conn;
    cmd.CommandText = "select count(*) from user_tables where table_name='T_PROC10'";//T_pack_func1
    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
    {
        cmd.CommandText = "drop table T_PROC10 cascade";
        cmd.ExecuteNonQuery();
    }
    cmd.CommandText = "create table T_PROC10(pid int ,pname varchar)";
    cmd.ExecuteNonQuery();
    string sql = "create or replace PROCEDURE P1(pid  INT,pname VARCHAR )";
    sql += " AS  BEGIN ";
    sql += " insert into T_PROC10 values(pid,pname);";
    sql += " end; ";
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
    cmd.CommandText = "P1";
    cmd.CommandType = CommandType.StoredProcedure;
    //参数赋值
    cmd.Parameters.Add("pid", XGDbType.Int).Value = 2;
    cmd.Parameters.Add("pname", XGDbType.VarChar).Value = "开发部";
    cmd.ExecuteNonQuery();
    //重复执行参数赋值前，需清除前次参数
    cmd.Parameters.Clear();
    cmd.Parameters.Add("pid", XGDbType.Int).Value = 3;
    cmd.Parameters.Add("pname", XGDbType.VarChar).Value = "测试部";
    cmd.ExecuteNonQuery();
    return 1;
}
catch (System.Exception ex)
{
    Console.WriteLine(ex.ToString());
    conn.Close();
    return 0;
}

存储过程输出参数的执行 ​

cs
XGConnection conn = new XGConnection();
conn.ConnectionString = "IP=127.0.0.1; DB=SYSTEM; USER=SYSDBA; PWD=SYSDBA; PORT=5138; AUTO_COMMIT=ON;";

try
{
    conn.Open();
    XGCommand cmd = new XGCommand();
    cmd.Connection = conn;
    cmd.CommandText = "select count(*) from user_tables where table_name='TEST_CSH_PROC'";
     if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
    {
        cmd.CommandText = "drop table TEST_CSH_PROC cascade";
        cmd.ExecuteScalar();
    }
    cmd.CommandText = "CREATE TABLE TEST_CSH_PROC(ID INT,SS VARCHAR(20),KK DATE,SP NUMERIC(12,5),PP FLOAT,TT FLOAT)";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "insert into TEST_CSH_PROC values(1,'testss',TO_DATE('2016-1-1 23:45:09','YYYY-MM-DD HH24:MI:SS'),321.34,123.123,321.123)";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "insert into TEST_CSH_PROC values(1,'testss',TO_DATE('2016-1-1 23:45:09','YYYY-MM-DD HH24:MI:SS'),111.23,231.12,322)";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "insert into TEST_CSH_PROC values(1,'testss',TO_DATE('2016-1-1 23:45:09','YYYY-MM-DD HH24:MI:SS'),234.123,986.234,9322)";
    cmd.ExecuteNonQuery();
    string sql_str = "CREATE OR REPLACE PROCEDURE P1(IN_ID IN OUT INT,OSS OUT VARCHAR,OKK OUT DATE,OSP  OUT NUMERIC,OPP OUT FLOAT,OTT OUT FLOAT)";
    sql_str += "AS TEMP_ID INT; BEGIN";
    sql_str += " select count(*),ss,kk,sum(sp),sum(pp),sum(tt) into temp_id,oss,okk,osp,opp,ott from TEST_CSH_PROC where id=IN_ID group by ss,kk;";
    sql_str += " IN_ID:=TEMP_ID; end;";
    cmd.CommandText = sql_str;
    cmd.ExecuteNonQuery();

    // 设置命令属性
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "P1";
    XGParameters par_id = new XGParameters("IN_ID", XGDbType.Int);
    // 设置参数输入输出属性
    par_id.Direction = ParameterDirection.InputOutput;
    par_id.Value = 1;
    cmd.Parameters.Add(par_id);
    XGParameters par_oss = new XGParameters("OSS", XGDbType.VarChar, 500);
    par_oss.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(par_oss);
    XGParameters par_okk = new XGParameters("OKK", XGDbType.DateTime);
    par_okk.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(par_okk);
    XGParameters par_osp = new XGParameters("OSP", XGDbType.Numeric);
    par_osp.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(par_osp);
    XGParameters par_opp = new XGParameters("OPP", XGDbType.Double);
    par_opp.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(par_opp);
    XGParameters par_ott = new XGParameters("OTT", XGDbType.Double);
    par_ott.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(par_ott);
    cmd.ExecuteNonQuery();

    Console.WriteLine("in_id=" + par_id.Value.ToString());
    Console.WriteLine("oss=" + par_oss.Value.ToString());
    Console.WriteLine("okk=" + par_okk.Value.ToString());
    Console.WriteLine("osp=" + par_osp.Value.ToString());
    Console.WriteLine("opp=" + par_opp.Value.ToString());
    Console.WriteLine("ott=" + par_ott.Value.ToString());
    cmd.Dispose();
    conn.Dispose();

    return 0;
}
catch (System.Exception ex)
{
    Console.WriteLine(ex.ToString());
    conn.Close();
    return 0;
}