- using System;
- using System.Collections.Generic;
- using System.Text;
- using System.Data.OracleClient;
- using System.Data;
- namespace OracleOpDemo
- {
- class Program
- {
- private static string connstr = "Data Source=oratest;User Id=m;password=m123;";
- private static string connstrSQL = "Data Source=oratest;User Id=ctd;password=cc;";
- static void Main(string[] args)
- {
- ExecuteProc();
- ExecutePareSQL();
- Console.WriteLine("execute completed");
- Console.Read();
- }
- private static void ExecutePareSQL()
- {
- using (OracleConnection conn = new OracleConnection(connstrSQL))
- {
- try
- {
- conn.Open();
- string sql = "insert into ivan_test(id,name) values(:p_id,:p_name)";
- OracleCommand cmd = new OracleCommand(sql,conn);
- OracleParameter para = new OracleParameter("p_id",OracleType.Number);
- para.Value = 510;
- cmd.Parameters.Add(para);
- para = new OracleParameter("p_name",OracleType.VarChar);
- para.Value = "IVANtest";
- cmd.Parameters.Add(para);
- cmd.ExecuteNonQuery();
- }
- catch (OracleException ex)
- {
- throw new Exception(ex.Message);
- }
- finally
- {
- conn.Close();
- }
- }
- }
- private static void ExecuteProc()
- {
- using (OracleConnection conn = new OracleConnection(connstr))
- {
- try
- {
- conn.Open();
- OracleCommand cmd = conn.CreateCommand();
- cmd.Connection = conn;
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.CommandText = "P_Test";
- OracleParameter para = new OracleParameter("p_checkid", "09531C6083FC4E579EB376DD9861B299");
- cmd.Parameters.Add(para);
- para = new OracleParameter("p_item",10);
- cmd.Parameters.Add(para);
- para = new OracleParameter("p_value",11);
- cmd.Parameters.Add(para);
- para = new OracleParameter("p_delayday",1);
- cmd.Parameters.Add(para);
- para = new OracleParameter("p_description","Desc");
- cmd.Parameters.Add(para);
- cmd.ExecuteNonQuery();
- }
- catch (OracleException ex)
- {
- throw new Exception(ex.Message);
- }
- finally
- {
- conn.Close();
- }
- }
- }
- }
- }
Oracle Procedure:
- CREATE OR REPLACE procedure PTest(
- p_checkid in varchar2,p_item in number,
- p_value in number,p_delayday in number,p_description in varchar2
- ) is
- v_state iqc_check.verify_state%type;
- begin
- declare cursor mycursor1 is
- select a.verify_state from check a where a.id = p_checkid;
- begin
- open mycursor1;
- fetch mycursor1 into v_state;
- if v_state=0 then
- insert into check_pack(checkid,item,value,delayday,description)
- values(p_checkid,p_item,p_value,p_delayday,p_description);
- else
- update check_pack
- set value=p_value,
- delayday=p_delayday,
- description=p_description
- where checkid=p_checkid and item =p_item;
- end if;
- close mycursor1;
- end ;
- --commit;
- end PTest;