What is dual in mybatis?-CodePudding

I am just trying to understand some old code without help which uses this mybatis statement. I did not find any proper answer. So in the below statement of myBatis what is dual?

SELECT mySeq.currVal FROM dual

CodePudding user response：

Wrong question, I'd say. DUAL is related to Oracle, not MyBatis.

It is a single-row, single-column table, owned by SYS. It looks like this:

SQL> desc dual
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 DUMMY                                  VARCHAR2(1)

SQL> select * from dual;

DUMMY
-----
X

SQL>

To users, it is available as a public synonym:

SQL> select object_name, owner, object_type
  2  from all_objects
  3  where object_name = 'DUAL';

OBJECT_NAME          OWNER                OBJECT_TYPE
-------------------- -------------------- -------------------
DUAL                 SYS                  TABLE
DUAL                 PUBLIC               SYNONYM

SQL>

How was it created and why does it have that strange name, "dual"?

Charles Weiss:

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.

Where do we use it? Everywhere!!!

In Oracle's SQL, you have to select from something, and that's frequently dual. For example, which date is it today, or who am I?

SQL> select sysdate from dual;

SYSDATE
-------------------
04.08.2022 11:43:08

SQL> select 'My name is Littlefoot' who_am_i from dual;

WHO_AM_I
---------------------
My name is Littlefoot

SQL>

Or, in your case, you're selecting sequence's current value. Here's a demo:

Create the sequence:

SQL> create sequence myseq;

Sequence created.

Your query (won't work, though - see error description):

SQL> select myseq.currval from dual;
select myseq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session

OK, so let's first fetch nextval:

SQL> select myseq.nextval from dual;

   NEXTVAL
----------
         1

Now we also have the currval:

SQL> select myseq.currval from dual;

   CURRVAL
----------
         1

SQL>

CodePudding user response：

From the Oracle documentation:

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once.

So that statement is querying the current value of the sequence mySeq, within your session (so there must have been a call to nextval preceding it for it to work).

In Oracle SQL you always have to select from something; dual is useful when there is no actual table you want to get data from - either for a constant or literal or function (select sysadte from dual) etc.