Oracle执行参数化SQL语句和存储过程

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data.OracleClient;
  5. using System.Data;
  6. namespace OracleOpDemo
  7. {
  8.     class Program
  9.     {
  10.         private static string connstr = "Data Source=oratest;User Id=m;password=m123;";
  11.         private static string connstrSQL = "Data Source=oratest;User Id=ctd;password=cc;";
  12.         static void Main(string[] args)
  13.         {
  14.             ExecuteProc();
  15.             ExecutePareSQL();
  16.             Console.WriteLine("execute completed");
  17.             Console.Read();
  18.         }
  19.         private static void ExecutePareSQL()
  20.         {
  21.             using (OracleConnection conn = new OracleConnection(connstrSQL))
  22.             {
  23.                 try
  24.                 {
  25.                     conn.Open();
  26.                    
  27.                    string sql = "insert into ivan_test(id,name) values(:p_id,:p_name)";
  28.                    
  29.                     OracleCommand cmd = new OracleCommand(sql,conn);
  30.                     OracleParameter para = new OracleParameter("p_id",OracleType.Number);
  31.                     para.Value = 510;
  32.                     cmd.Parameters.Add(para);
  33.                      para = new OracleParameter("p_name",OracleType.VarChar);
  34.                     para.Value = "IVANtest";
  35.                     cmd.Parameters.Add(para);
  36.                     cmd.ExecuteNonQuery();
  37.                 }
  38.                 catch (OracleException ex)
  39.                 {
  40.                     throw new Exception(ex.Message);
  41.                 }
  42.                 finally
  43.                 {
  44.                     conn.Close();
  45.                 }
  46.             }
  47.         }
  48.         private static void ExecuteProc()
  49.           {
  50.       
  51.             using (OracleConnection conn = new OracleConnection(connstr))
  52.             {
  53.                 try
  54.                 {
  55.                     conn.Open();
  56.                     OracleCommand cmd = conn.CreateCommand();
  57.                     cmd.Connection = conn;
  58.                     cmd.CommandType = CommandType.StoredProcedure;
  59.                     cmd.CommandText = "P_Test";
  60.                     OracleParameter para = new OracleParameter("p_checkid""09531C6083FC4E579EB376DD9861B299");
  61.                     cmd.Parameters.Add(para);
  62.                     para = new OracleParameter("p_item",10);
  63.                     cmd.Parameters.Add(para);
  64.                     para = new OracleParameter("p_value",11);
  65.                     cmd.Parameters.Add(para);
  66.                     para = new OracleParameter("p_delayday",1);
  67.                     cmd.Parameters.Add(para);
  68.                     para = new OracleParameter("p_description","Desc");
  69.                     cmd.Parameters.Add(para);
  70.                     cmd.ExecuteNonQuery();
  71.                 }
  72.                 catch (OracleException ex)
  73.                 {
  74.                     throw new Exception(ex.Message);
  75.                 }
  76.                 finally
  77.                 {
  78.                     conn.Close();
  79.                 }
  80.             }
  81.         }
  82.     }
  83. }

Oracle Procedure:

  1. CREATE OR REPLACE procedure PTest(
  2.        p_checkid in varchar2,p_item in number,
  3.        p_value in number,p_delayday in number,p_description in varchar2
  4.         
  5. is
  6. v_state iqc_check.verify_state%type;
  7. begin
  8.      declare cursor mycursor1 is
  9.      select a.verify_state from check a where a.id = p_checkid;
  10.      begin
  11.           open mycursor1;
  12.           fetch mycursor1 into v_state;
  13.           if v_state=0 then
  14.              insert into check_pack(checkid,item,value,delayday,description)
  15.              values(p_checkid,p_item,p_value,p_delayday,p_description);
  16.           else
  17.               update check_pack 
  18.                      set value=p_value,
  19.                      delayday=p_delayday,
  20.                      description=p_description 
  21.               where checkid=p_checkid and item =p_item;
  22.           end if;
  23.           close mycursor1;
  24.      end ;
  25.  --commit;
  26. end PTest;

 

阅读更多
换一批

没有更多推荐了,返回首页