Oracle 11g Study Notes (3)

Some ASP. NET + Oracle 11G systems write and learn essays, including Oracle's "rownum" and "sequence serial number 」.

(6) Oracle rownum, equivalent to the select top of other databases

Oracle does not support the select top syntax. to retrieve the maximum or minimum records, you must use the rownum keyword and subquery.

For example, you can use the following statement to obtain the minimum 10:
Select ID, name, rownum from (select ID, name from Table order by ID) Where rownum <= 10;

If you want to get the maximum 10 strokes, add Desc:
Select ID, name, rownum from (select ID, name from Table order by id desc) Where rownum <= 10;

In addition, you can make some application changes:
Select a. ID, A. Name, rownum from (select ID, name from Table order by ID) A where rownum <= 10 order by rownum DESC;

Note that the where condition containing rownum must contain 1, for example:
Where rownum> 0
Where rownum> = 1
Where rownum <10
If this parameter is not set to 1, no data is found in the query.

Official Oracle rownum and top-N query teaching (English ):
Http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
Http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html

In addition, Oracle also has two row_number () over and rank () over syntaxes. You can also query all the data and then assign continuous sequential numbers. Although they cannot give the where conditional like rownum
Used to write Stored Procedure for the "pagination" function of the ASP. NET gridview control. For usage instructions, refer:
Http:// I .cn.yahoo.com/gaoxiaoqing2003/blog/p_8/
Http://keke-wanwei.javaeye.com/blog/138632

 

(7) Oracle sequence, equivalent to identity of other databases

Oracle does not support automatic identity addition fields supported by SQL Server and Sybase, nor does it support the "select @ identity;" syntax for obtaining the latest identity number immediately after the insert into statement,
To achieve the above functions, you must use sequence (sequential number ).

Sequence is not included in a table, and a sequence does not correspond to a table one-to-one. To use sequence, you must manually create the sequence first. The syntax is as follows:
Create sequence seq_name;
Or
Create sequence seq_name
Increase by 1
Start with 1
Max value 9999
Nocache
Nocycle;

If no parameter exists, the default value starts from 1. Each time the number 1 is added, the maximum value is the 27 power of 10. If the storage value reaches maxvalue, It is not automatically re-numbered (if it corresponds to the table's primary key, this value should be nocycle by default );
The default value of the cache option. 20 data records are generated in memory.

Run the following statement to view all sequence settings and storage content. Last_name is the value to be generated next to it.
Select * From user_sequences;

To view the current value and next value of a sequence, use the following statement:
Select table1_seq.CurrvalFrom dual;
Select tableeclipse1.NextvalFrom dual;

Note that as long as the second statement nextval is executed, the internal number of the sequence will automatically add a number, not just select capture.

To add a record to table with sequence, the following syntax is available:
Insert into Table1 (ID, name) values (table1_seq.Nextval, 'Name1 ');

In Oracle versions earlier than 10 Gb, or you are using the oledb online mode (oracleclient can also be used), when you want to insert an into record, the primary key wants to write the sequence value, can be written as follows (also
The latest Sequence Value of the record can be returned immediately after the addition is complete ):

Using system. Data. oledb;
Oledbconnection odconn = NULL;
Oledbcommand odcmd = NULL;
Int64 intdata_id_afterinserted = 0;
String strsql ="BeginSelect table1_seq.Nextval: ID from dual;Insert into Table1 (ID, name) values (: ID,: Name );End;";

... In the middle...

Oledbparameter P;
P = odcmd. Parameters. Add (": ID", oledbtype. Double, 7 );
P. Direction = parameterdirection.Output;
Odcmd. Parameters. Add (": Name", oledbtype. varwchar, 30). value = textbox1.text;

Odcmd. executenonquery ();
Intdata_id_afterinserted = convert. toint64 (P. Value); // returns the latest Sequence Value of the record immediately.

If you are using Oracle 10g and later versions, and use oracleclient data provider, you can use the following "returning into" statement in a more concise way. However, it must be noted that if oledb is used online
It does not cause an error or exception, but the write value is abnormal.

String strsql = "insert into Table1 (ID, name) values (table1_seq.nextval,: name)ReturningIDInto: ID ";

---------------------------------------------
ASP. NET 2.0 + Oracle 11g sample download point at (7) of this post (the latest sequence value is obtained immediately after batch addition + addition ):
Http://files.cnblogs.com/WizardWu/081128.zip
---------------------------------------------

If you do not install the Oracle 11g server-side software (the database is installed on another host) in the operating system of this example, when you use Visual Studio to execute this example, the following error message may appear:
"Oraoledb. oracle.1" is not registered on the Local Computer

Solution: Go to the Oracle official website to download data provider and client-sideProgramAnd install it. The download URL is as follows:
Http://www.oracle.com/technology/software/tech/windows/odpnet/index.html (newer)
Http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html (older)

After installation, you can use Visual Studio to execute this example. However, if you change to IIS for execution, the above error message will still appear, because you need to set up some users who want IIS/ASP. NET to have the permission to write data to the folder where the oracle on the servo end is located. For more information about the settings, see the next article "Oracle 11g Study Notes (4)" on this site )」.