1、运用序列 StringBuilder strSql = new StringBuilder();
strSql.Append("begin");
strSql.Append(" insert into TEST.PERSON(PID,PNAME,PAGE)");
strSql.Append(" values(TEST.SEQ_PERSON.NEXTVAL,:pname,:page);");
strSql.Append(" select TEST.SEQ_PERSON.CURRVAL into :c_id from dual;");
strSql.Append(" end;");
OracleParameter[] paras ={new OracleParameter("pname",OracleType.NVarChar,50),
new OracleParameter("page",OracleType.Int32),
new OracleParameter("c_id",OracleType.Int32)};
paras[0].Value = "zhang";
paras[1].Value = 20;
paras[2].Direction = ParameterDirection.Output;
string connStr = "Data Source=orcl;User Id=TEST;Password=test;";
object o = OracleHelper.ExecuteNonQuery(connStr, CommandType.Text, strSql.ToString (), paras);
object c_id = paras[2].Value;
Response.Write(c_id);
2、取ID的最大值
StringBuilder strSql = new StringBuilder();
strSql.Append("begin");
strSql.Append(" insert into TEST.PERSON(PID,PNAME,PAGE)");
strSql.Append(" values(TEST.SEQ_PERSON.NEXTVAL,:pname,:page);");
strSql.Append(" select MAX(PID) into :MaxId from TEST.PERSON;");
strSql.Append(" end;");
OracleParameter[] paras ={new OracleParameter("pname",OracleType.NVarChar,50),
new OracleParameter("page",OracleType.Int32),
new OracleParameter("MaxId",OracleType.Int32)};
paras[0].Value = "zhang";
paras[1].Value = 20;
paras[2].Direction = ParameterDirection.Output;
string connStr = "Data Source=orcl;User Id=TEST;Password=test;";
object o = OracleHelper.ExecuteNonQuery(connStr, CommandType.Text, strSql.ToString (), paras);
Response.Write(paras[2].Value);
注意:执行多条sql语句不能有物理换行