Oracle 操作

 包内执行SQL

EXECUTE IMMEDIATE 'Delete From table';

 

--一个字段存储多个ids
Select wm_concat(b.prod_line_code), a.record_id,a.p_prod_line_group_id,a.p_prod_line_ids
  from ps_mo_publish_history a, ps_production_line b
 where instr(',' || a.p_prod_line_ids || ',', ',' || b.prod_line_id || ',') > 0
 and a.p_prod_line_group_id = 8004
 Group by a.record_id,a.p_prod_line_group_id,a.p_prod_line_ids
 order by a.record_id

 

//数据复制

insert into code_key(evcodetype,issyscode,evcodename,evid)(select evcodetype,issyscode,evcodename,(select Max(evid)+1 from code_key)

 from code_key t where t.evid=100001)

//复制多条数据

insert into code_number(evorderid,evcodeid,evcode,evtext,evid)(select evorderid,evcodeid,evcode,evtext,evid+1+
((select max(evid) from code_number)-(select min(evid) from code_number where evcodeid=200001))
 from code_number t where t.evcodeid=200001)

//自增ID

insert into SYM_LOG(evid,Evlogtime, evusername,evopertype,Evoperation,Evresult)values((select Max(EVID)+1 from SYM_LOG),'','g1111',101358,'登陆',1)

 

        public bool InsertAdmin(string userName, string password, string remark, string mail, int departId, string power)
        {
            string sql = "insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(?,?,?,?,(select max(DepartId)+1 from S_Admin),?)";
            OracleConnection connection = new OracleConnection();
            connection.ConnectionString = "Host=wj-3931;Port=1521;Service Name=orcl;User ID=gedigis20130528;Password=gedigis20130528;";//此处设置链接字符串
            OracleCommand command = new OracleCommand(sql, connection);
            command.Parameters.Add(":UserName",OracleDbType.NVarChar).Value = userName;
            command.Parameters.Add(":Password", OracleDbType.NVarChar).Value = password;
            command.Parameters.Add(":Remark", OracleDbType.NVarChar).Value = remark;
            command.Parameters.Add(":Mail", OracleDbType.NVarChar).Value = mail;
            //command.Parameters.Add(":DepartId", OracleDbType.NVarChar).Value = "(select max(DepartId)+1 from S_Admin)";
            command.Parameters.Add(":Power", OracleDbType.NVarChar).Value = power;
            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            connection.Close();
            command.Dispose();
            return rowsAffected > 0;
        }