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 userSYS
but is accessible by the name DUAL to all users. It has one column,DUMMY
, defined to beVARCHAR2(1)
, and contains one row with a valueX
. Selecting from theDUAL
table is useful for computing a constant expression with theSELECT
statement. BecauseDUAL
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.