oracle开发的小技巧(原创)

一:使用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();  
   }