C# Call Oracle Procedure Demo(paras Query)

最新推荐文章于 2021-03-17 10:48:14 发布

JustLovePro

最新推荐文章于 2021-03-17 10:48:14 发布

阅读量1.1k

点赞数

分类专栏： c#2005 文章标签： oracle query c# sql cmd

本文链接：https://blog.csdn.net/JustLovePro/article/details/3420814

版权

c#2005 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

Procedure

 
 CREATE OR REPLACE PROCEDURE ivan_sptest1 (
   material_no   IN       sd_form_box.material_no%TYPE,
   pallet_id     IN       sd_form_box.pallet_id%TYPE,
   box_id        IN       sd_form_box.box_id%TYPE,
   v_ds1         OUT      sys_refcursor,
   v_ds2         OUT      sys_refcursor
)
IS
   sql1   VARCHAR2 (1000) := NULL;
   sql2   VARCHAR2 (1000) := NULL;
BEGIN
   sql1 := 'SELECT * FROM SD_FORM_BOX WHERE 1=1';
   IF (material_no IS NOT NULL)
   THEN
      sql1 := sql1 || ' AND MATERIAL_NO=' || '''' || material_no || '''';
   END IF;
   IF (pallet_id IS NOT NULL)
   THEN
      sql1 := sql1 || ' AND PALLET_ID=' || '''' || pallet_id || '''';
   END IF;
   IF (box_id IS NOT NULL)
   THEN
      sql1 := sql1 || ' AND BOX_ID=' || '''' || box_id || '''';
   END IF;
   OPEN v_ds1 FOR sql1;
   sql2 := 'SELECT * FROM SD_FORM_PALLET';
   OPEN v_ds2 FOR sql2;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END ivan_sptest1;
 

 
   string connstr = "Data Source=test;uid=cc;password=ccc";
            DataSet ds = null;
            using (OracleConnection conn = new OracleConnection(connstr))
            {
                try
                {
                    conn.Open();
                    OracleCommand cmd = new OracleCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = "IVAN_SPTEST1";
                    
                    OracleParameter para = new OracleParameter("V_DS1", OracleType.Cursor);
                    para.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(para);
                    para = new OracleParameter("V_DS2",OracleType.Cursor);
                    para.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(para);
                    para = new OracleParameter("material_no",OracleType.VarChar);
                    para.Value = txtmaterial.Text;
                    para.Direction = ParameterDirection.Input;
                    cmd.Parameters.Add(para);
                    para = new OracleParameter("pallet_id", OracleType.VarChar);
                    para.Value = txtpallet.Text;
                    para.Direction = ParameterDirection.Input;
                    cmd.Parameters.Add(para);
                    para = new OracleParameter("box_id", OracleType.VarChar);
                    para.Value = txtbox.Text;
                    para.Direction = ParameterDirection.Input;
                    cmd.Parameters.Add(para);
                    OracleDataAdapter ada = new OracleDataAdapter(cmd);
                    ds = new DataSet();
                    ada.Fill(ds);
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    conn.Close();
                }
            }
            this.dataGridView1.AutoGenerateColumns = true;
            this.dataGridView1.DataSource = ds.Tables[0];
            this.dataGridView2.AutoGenerateColumns = true;
            this.dataGridView2.DataSource = ds.Tables[1];
 
 