dual

teledb=# select 1 as f1 from dual; 
 f1 
----
  1
(1 row)
teledb=#

rowid

需要设置配置项default_with_rowid为on

teledb=# create table t_rowid(f1 int,f2 int);
CREATE TABLE
teledb=# insert into t_rowid values(1,1);
INSERT 0 1
teledb=# select rowid,f1,f2 from  t_rowid;
        rowid         | f1 | f2 
----------------------+----+----
 XPK3fw==AQAAAAAAAAA= |  1 |  1
(1 row)

rownum

teledb=# create table t_rownum(f1 int,f2 int);
CREATE TABLE
teledb=# insert into t_rownum values(1,1);
INSERT 0 1
teledb=# insert into t_rownum values(2,2);
INSERT 0 1
teledb=# insert into t_rownum values(3,3);
INSERT 0 1
teledb=# insert into t_rownum values(4,4);
INSERT 0 1
teledb=# insert into t_rownum values(5,5);
INSERT 0 1
teledb=# select rownum,* from t_rownum;
 rownum | f1 | f2 
--------+----+----
      1 |  1 |  1
      2 |  2 |  2
      3 |  3 |  3
      4 |  4 |  4
      5 |  5 |  5
(5 rows)
teledb=# select rownum,* from t_rownum where rownum<3;
 rownum | f1 | f2 
--------+----+----
      1 |  1 |  1
      2 |  2 |  2
(2 rows)

teledb=# 

sysdate

teledb=# select sysdate from dual;
        orcl_sysdate    
----------------------------
 2023-08-24 14:30:07.260456
(1 row)

teledb=# select systimestamp from dual;   
       orcl_systimestamp   
-------------------------------
 2023-08-24 14:30:16.496373+08
(1 row)

teledb=#

merge into

teledb=# create table test1(id int primary key,name varchar2(10));
CREATE TABLE
teledb=# insert into test1 values(1,'test1');
INSERT 0 1
teledb=# insert into test1 values(2,'test1');
INSERT 0 1
teledb=# insert into test1 values(3,'test1');
INSERT 0 1
teledb=# create table test2(id int primary key,name varchar2(10));
CREATE TABLE
teledb=# insert into test2 values(2,'test2');
INSERT 0 1
teledb=# insert into test2 values(3,'test2');
INSERT 0 1
teledb=# insert into test2 values(4,'test2');
INSERT 0 1
teledb=# insert into test2 values(5,'test2');
INSERT 0 1
teledb=# MERGE INTO test1 t
teledb-# USING (
teledb(#   select * from test2
teledb(# ) t2 ON (t.id = t2.id)
teledb-# WHEN MATCHED THEN UPDATE SET t.name = t2.name WHERE t.id = t2.id 
teledb-# WHEN NOT MATCHED THEN INSERT (id,name) VALUES (t2.id, t2.name) ;
MERGE 4
teledb=# select * from test1;
 id | name  
----+-------
  1 | test1
  2 | test2
  3 | test2
  4 | test2
  5 | test2
(5 rows)

connect by

使用level实现1到5的序列。

teledb=# select level from dual connect by level<=5;
 level 
-------
     1
     2
     3
     4
     5
(5 rows)

pivot

create table scores(student varchar(10) not null, course varchar(10) not null,score int not null);
insert into scores values('张三','语文',78);
insert into scores values('张三','语文',98);
insert into scores values('张三','数学',79);
insert into scores values('张三','英语',80);
insert into scores values('张三','物理',81);
insert into scores values('李四','语文',65);
insert into scores values('李四','数学',75);
insert into scores values('李四','英语',85);
insert into scores values('李四','物理',95);
select * from scores pivot (
sum(score)
for student in ('张三','李四')
);
 course | '张三' | '李四' 
--------+--------+--------
 英语   |     80 |     85
 数学   |     79 |     75
 物理   |     81 |     95
 语文   |    176 |     65
(4 rows)

limit x offset 1

如果参数enable_oracle_compatible配置为on，则offset 1表示从第一条提取记录。

teledb=# select * from test1;
 id | name  
----+-------
  1 | test1
  2 | test2
  3 | test2
  4 | test2
  5 | test2
(5 rows)
teledb=# select * from test1 limit 5 offset 1;
 id | name  
----+-------
  1 | test1
  2 | test2
  3 | test2
  4 | test2
  5 | test2

teledb=# 

dml 操作加强

select支持别名不用as修饰

teledb=# create table student(f1 int,f2 int);
CREATE TABLE
teledb=# insert into student  values(1,1);
INSERT 0 1
teledb=# select * from student as st where st.f1=1;
 f1 | f2 
----+----
  1 |  2
(1 row)
teledb=# select * from student st where st.f1=1;   
 f1 | f2 
----+----
  1 |  2

update支持别名

需要打开enable_oracle_compatible

teledb=# set enable_oracle_compatible to on;
SET
teledb=# create table student(f1 int,f2 int);
CREATE TABLE
teledb=# insert into student  values(1,1);
INSERT 0 1
teledb=# update student st set st.f2=2 where f1=1;
UPDATE 1
teledb=# select * from student ;
 f1 | f2 
----+----
  1 |  2
#实际
teledb=# UPDATE student st
SET st.f2 = 2
WHERE st.f1 = 1;
ERROR:  column "st" of relation "student" does not exist
LINE 2: SET st.f2 = 2