一:使用rownum,伪列
由于单纯地使用ROWNUM无法实现对于查询的排序, 下面是一个简单的利用例子:
select username,rownum from dba_users
where rownum < 4 order by username;
USERNAME ROWNUM
-------------------------- ------
OUTLN 3
SYS 1
SYSTEM 2
这样就可以通过取得记录然后排序解决问题:
SELECT username,rownum FROM
(SELECT username FROM dba_users ORDER BY username)
WHERE ROWNUM < 4;
USERNAME ROWNUM
-------------------------- ------
AURORA$ORB$UNAUTHENTICATED 1
CTXSYS 2
DBSNMP 3
一个(>)符号和 rownum 一起使用时遇到负数则不会有结果:
1 SELECT username,rownum FROM
2 (SELECT username FROM dba_users ORDER BY username)
3* WHERE ROWNUM > 4
SQL> /
no rows selected
要显示最末的三条记录就不能使用(>):
1 SELECT username,rownum FROM
2 (SELECT username FROM dba_users ORDER BY username desc)
3* WHERE ROWNUM < 4
SQL> /
USERNAME ROWNUM
-------------------------- ------
TESTUSER 1
SYSTEM 2
SYS 3
------------
二:查询的时候,手动指定索
select /*+ index(tablename index_name)*/ from tablename
三:在oracle里使用大对象
private void writeclob()
{
OracleConnection myOracleConnection = new OracleConnection();
myOracleConnection.ConnectionString = ConfigurationSettings.AppSettings["ConnectionOraStr"];
myOracleConnection.Open();
OracleCommand myOracleCommand = myOracleConnection.CreateCommand();
// step 1: create an OracleTransaction object
OracleTransaction myOracleTransaction = myOracleConnection.BeginTransaction();
if (this.selectListValue.Text !="")
{
// step 2: read the row
myOracleCommand.CommandText ="SELECT Autoid, P_itemContent " +"FROM Pcontent " +"WHERE Autoid = '"+this.selectListValue.Text+"'";
OracleDataReader myOracleDataReader = myOracleCommand.ExecuteReader();
myOracleDataReader.Read();
// step 3: get the LOB locator
OracleClob myOracleClob =myOracleDataReader.GetOracleClobForUpdate(1);
// step 4: write to the LOB
myOracleClob.Erase();
string text = this.content.Text;
char [] charArray = text.ToCharArray();
myOracleClob.Write(charArray, 0, charArray.Length);
// step 5: commit the transaction
myOracleTransaction.Commit();
// close the OracleDataReader and the OracleConnection object
myOracleDataReader.Close();
myOracleConnection.Close();
}