系统特性
更新时间 2025-02-14 10:25:16
最近更新时间: 2025-02-14 10:25:16
本页介绍天翼云TeleDB数据库Oracle语法的系统特性。
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 | 2update支持别名
需要打开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
