Oracle's interval query statement: 1. "select * from table name where rownum
The operating environment of this tutorial: Windows 10 system, Oracle version 12c, Dell G3 computer.
What is Oracle’s interval query statement?
Oracle uses the rownum keyword to implement this query:
First we Suppose there is a regional information table area, and its table structure is as shown in the figure below:
The data in the table is as shown in the figure below (the result of the select * from area statement) :
1) Query the first 8 records in the table
select * from area where rownum <= 8
The query results are as follows:
2) Query the 2nd to 8th records
For this form of query, Oracle is not as convenient as mysql. It must be implemented using subqueries or set operations. We can use the following three methods to achieve this:
A: select id,province,city,district from (select id,province,city,district,rownum as num from area) where num between 2 and 8;
First get a temporary table based on select id, province, city, district, rownum as num from area. There is a rownum in this temporary table. Column (a pseudo column, similar to
rowid, but different from rowid, because rowid is a physically existing column, that is to say, any table in Oracle has a rowid column, and rownum does not exist physically),
Then query in the temporary table.
B: select * from area where rownum <= 8 minus select * from area where rownum < 2;
Use the set minus operator minus, which returns records that appear in the first select but not in the second select.
C: select id,province,city,district from (select id,province,city,district,rownum as num from area) where num >=2 intersect
select * from area where rownum
Using the set intersection operator intersect, there is a twist here (but this twist realizes that rownum is greater than a certain Number query), it first uses A's method to query to obtain all records with
rownum greater than 2, and then performs an intersection operation with the record set with rownum less than or equal to 8. The results obtained by the three operations are the same, as shown in the figure below:
Recommended tutorial: "Oracle Video Tutorial"
The above is the detailed content of What is Oracle's interval query statement?. For more information, please follow other related articles on the PHP Chinese website!

Oracle has a wide and diverse customer base, covering multiple fields such as financial services, healthcare, retail and e-commerce, and manufacturing. 1) In financial services, Oracle FLEXCUBE improves operational efficiency and data security; 2) In health care, OracleHealth Sciences Clinical Development accelerates new drug research and development; 3) In retail and e-commerce, OracleRetail and OracleCDP achieve personalized customer experience; 4) In manufacturing, OracleERPCloud optimizes supply chain management.

Triggers in Oracle are stored procedures used to automatically perform operations after a specific event (insert, update, or delete). They are used in a variety of scenarios, including data verification, auditing, and data maintenance. When creating a trigger, you need to specify the trigger name, association table, trigger event, and trigger time. There are two types of triggers: the BEFORE trigger is fired before the operation, and the AFTER trigger is fired after the operation. For example, the BEFORE INSERT trigger ensures that the age column of the inserted row is not negative.

Two ways to rename Oracle table names: use SQL statements: ALTER TABLE <Old table name> RENAME TO <New table name>;Use PL/SQL statements: EXECUTE IMMEDIATE 'ALTER TABLE ' || :old_table_name || ' RENAME TO ' || :new_table_name;

Oracle provides the following ways to fall back on committed database changes: Use the ROLLBACK statement to immediately revoke all uncommitted changes. Operation through the database management tool interface. Use Oracle Flashback technology to return to a specific point in time and restore data, flashback logging is required.

Methods to check whether the index has been rebuilt in Oracle: DBA_INDEXES view: view REBUILT value (YES/NO); ALL_INDEXES view: view STATUS value (VALID/UNUSABLE); V$INDEX_STATISTICS view: view NUM_REBUILDS value, indicating the number of index reconstructions.

After the Oracle upgrade fails, follow the following steps to restore the system: Terminate recovery and switch to recovery mode. Use the recovery command to roll back the data file. Open the database and confirm that the data file is installed and restore the redo log. If the control file is corrupted, recreate it. Reopen the database in full recovery mode. Verify the restore and confirm that the data and objects are intact. If a rollback segment was created during restore, roll it back.

To modify an expired Oracle database password, follow these steps: 1. Exit all sessions; 2. Connect with the database with SYSDBA permissions; 3. Execute the ALTER USER command to modify the password; 4. Reconnect with the new password; 5. Execute the query to confirm that the password has been modified.

Escape characters in Oracle are used to indicate special characters or control sequences, including line connections, string delimiters, line breaks, carriage return, tabs, and backspace characters. Escape character processing usually involves escaping special characters in a string, using | concatenating multiline strings, and using a backslash to escape the escape character itself.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Notepad++7.3.1
Easy-to-use and free code editor

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.