圈子推荐
查看所有吧>>
活跃用户
    atlasdb 兼容oracle 特性之 SQL操作

    1.1. SQL语法

    1.1.1. 外连接运算符(+

    SELECT  * FROM mytab a,mytab b where a.id=b.id(+) and a.id(+)=1;

     

    1.1.2. CONNECT BY…… START WITH 

    CREATE TABLE sys_cbp_test2 (id INTEGER NOT NULL PRIMARY KEY,parent_id INTEGER );

    INSERT INTO sys_cbp_test2

    SELECT 1, NULL FROM dual

    UNION ALL SELECT 2, 1 FROM dual

    UNION ALL SELECT 3, 2 FROM dual

    UNION ALL SELECT 4, 3 FROM dual

    UNION ALL SELECT 5, 1 FROM dual

    UNION ALL SELECT 6, 5 FROM dual

    UNION ALL SELECT 7, 2 FROM dual

    UNION ALL SELECT 20, NULL FROM dual

    UNION ALL SELECT 21, 20 FROM dual

    UNION ALL SELECT 22, 21 FROM dual;

     

    atlasdb=# SELECT id, parent_id,sys_connect_by_path (TO_CHAR (id), '/') AS Path

    atlasdb-# FROM sys_cbp_test2

    atlasdb-# START WITH parent_id IS NULL CONNECT BY prior id = parent_id;

     id | parent_id |   path    

    ----+-----------+-----------

      1 |           | /1

     20 |           | /20

      2 |         1 | /1/2

      5 |         1 | /1/5

     21 |        20 | /20/21

      3 |         2 | /1/2/3

      6 |         5 | /1/5/6

      7 |         2 | /1/2/7

     22 |        21 | /20/21/22

      4 |         3 | /1/2/3/4

    (10 rows)

     

    1.1.3. ROWNUM伪列

    select rownum from sys_cbp_test2;

    rownum

    --------

          1

          2

          3

          4

          5

          6

          7

          8

          9

         10

    (10 rows)

     

    1.1.4. ROWID伪列

    atlasdb=# select rowid from sys_cbp_test2;

            rowid         

    ----------------------

     sEMAAA==AAAAAA==AQA=

     sEMAAA==AAAAAA==AgA=

     sEMAAA==AAAAAA==AwA=

     sEMAAA==AAAAAA==BAA=

     sEMAAA==AAAAAA==BQA=

     sEMAAA==AAAAAA==BgA=

     sEMAAA==AAAAAA==BwA=

     sEMAAA==AAAAAA==CAA=

     sEMAAA==AAAAAA==CQA=

     sEMAAA==AAAAAA==CgA=

    (10 rows)

    1.1.5. 闪回功能

    闪回查询

    1) 开启“跟踪事务提交时间戳”参数,将值设为on,修改完成后重启数据库

    2) 闪回查询

    使用asql客户端工具执行以下命令:

    CREATE TABLE t_person (id BIGINT,name VARCHAR(64));

    SELECT pg_sleep(10);

    INSERT INTO t_person VALUES (1,'bob');

    INSERT INTO t_person VALUES (2,'tim');

    SELECT * FROM t_person;

    SELECT pg_sleep(10);

    SELECT pg_sleep(10);

    UPDATE t_person SET NAME = 'cat' WHERE ID = 2;

    SELECT * FROM t_person;

    3) 闪回查询15秒以前的表数据

    SELECT * FROM flashback_query('public.t_person',CURRENT_TIMESTAMP - INTERVAL '15 second') AS t(id BIGINT,name VARCHAR(64));

    SELECT pg_sleep(10);

    SELECT pg_sleep(10);

    DELETE FROM t_person WHERE ID = 2;

    SELECT * FROM t_person;

    SELECT * FROM flashback_query('public.t_person',CURRENT_TIMESTAMP - INTERVAL '15 second') AS t(id BIGINT,name VARCHAR(64));

     

    闪回表

    1) 闪回查询

    使用asql客户端工具执行以下命令:

    TRUNCATE TABLE t_person;
    INSERT INTO t_person VALUES (1,'bob');
    INSERT INTO t_person VALUES (2,'tim');
    INSERT INTO t_person VALUES (3,'kiki');
    SELECT * FROM t_person;

    2) 对数据表的操作进行回退

    SELECT pg_sleep(10);
    SELECT pg_sleep(10);
    INSERT INTO t_person VALUES (4,'yuki');
    UPDATE t_person SET name = 'mini' WHERE id = 2;
    DELETE FROM t_person WHERE id = 3;
    SELECT * FROM t_person;
    SELECT flashback('public.t_person',CURRENT_TIMESTAMP - INTERVAL '15 second');
    SELECT * FROM t_person;

     

    1.1.6. MERGE INTO

    create table mytab (id int);

    MERGE INTO mytab t USING (SELECT 1 as key) s ON s.key = t.id WHEN MATCHED THEN DELETE;

    MERGE INTO mytab t USING (SELECT 1 as key) s ON s.key = t.id WHEN not  MATCHED THEN insert values (s.key);

    MERGE INTO mytab t USING (SELECT 1 as key) s ON s.key = t.id WHEN not  MATCHED THEN insert values (s.key) WHEN MATCHED THEN UPDATE set id = id +1;

     

    1.1.7. 内联视图无需指定别名

    atlasdb=# select * from mytab , (select 1 vv) where id=vv;

     id | vv

    ----+----

    (0 rows)

     

    1.1.8. UPDATE SET语法中允许指定表名或别名修饰

    atlasdb=# update mytab a set a.id =1;

     

    1.1.9. 空字符串和NULL等价

    v '' 跟NULL可以看成相等

    select  1 from dual where '' is null;

    ?column?

    ----------

      1

    (1 row)

     

    1.1.10. 支持序列操作语法

    支持sequence.CURRVAL与sequence.NEXTVAL

    atlasdb=# select customers_seq.nextval;

     nextval

    ---------

        1000

    (1 row)

    s

    atlasdb=# select customers_seq.currval;

     currval

    ---------

        1000

    (1 row)

     

    1.1.11. delete 省去 FROM关键字

    atlasdb=# delete mytab where id =1;

     

    1.1.12. 兼容列约束的enable/disable语法

    atlasdb=# alter table STUDENT disable constraint STUDENT_PKEY;

     

    1.1.13. 支持时间间隔操作语法

    CREATE TABLE coupons1 (

     coupon_id INTEGER CONSTRAINT coupons1_pk PRIMARY KEY,

     name VARCHAR2(30) NOT NULL,

     duration INTERVAL DAY TO SECOND

    );

    CREATE TABLE coupons (

     coupon_id INTEGER CONSTRAINT coupons_pk PRIMARY KEY,

     name VARCHAR2(30) NOT NULL,

     duration INTERVAL YEAR TO MONTH

    );

    1.2. 隐式类型转换

    定长字符串与数值类型转换

    定长字符串类型CHARACTER与NUMBER/NUMERIC,INT4,INT8,FLOAT4,FLOAT8之间相互转换。

    atlasdb=#  select '1'::CHARACTER =1::number;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=#  select '1'::CHARACTER =1::numeric;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=#  select '1'::CHARACTER =1::int4;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=#  select '1'::CHARACTER =1::int8;

     ?column?

    ----------

     t

    (1 row)

     

    atlasdb=#  select '1'::CHARACTER =1::float4;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=#  select '1'::CHARACTER =1::float8;

     ?column?

    ----------

     t

    (1 row)

     

    变长字符串类型与数值类型转换

    VARCHAR/VARCHAR2与NUMBER/NUMERIC,INT8,FLOAT4,FLOAT8之间相互转换。

    atlasdb=# select '1'::varchar2 =1::NUMERIC;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=# select '1'::varchar2 =1::INT8;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=# select '1'::varchar2 =1::FLOAT4;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=# select '1'::varchar2 =1::FLOAT8;

     ?column?

    ----------

     t

    (1 row)

     

    文本类型与数值类型转换

    NUMBER/NUMERIC,INT2,INT4,INT8,FLOAT4,FLOAT8之间相互转换。

    atlasdb=# select '1'::text =1::NUMBER;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=# select '1'::text =1::NUMERIC;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=# select '1'::text =1::INT2;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=# select '1'::text =1::INT4;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=# select '1'::text =1::INT8;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=# select '1'::text =1::FLOAT4;

     ?column?

    ----------

     t

    (1 row)

    atlasdb=# select '1'::text =1::FLOAT8;

     ?column?

    ----------

     t

    (1 row)

     

    短整型INT2转换为字符串

    CHARACTER,VARCHAR/VARCHAR2。

    atlasdb=# select (1::int2)::varchar2;

     varchar2

    ----------

     1

    (1 row)

    atlasdb=# select (1::int2)::varchar;

     varchar

    ---------

     1

    (1 row)

    atlasdb=# select (1::int2)::character;

     bpchar

    --------

     1

    (1 row)

    1.3. SQL命令1.3.1. ALTER INDEX

    v ALTER INDEX name DISABLE;

    v ALTER INDEX name ENABLE;

    create table mytable (id INT);

    CREATE INDEX IDX_XX ON MYTABLE (ID);

    ALTER INDEX IDX_XX2 DISABLE;

    ALTER INDEX IDX_XX2 ENABLE;

    1.3.2. ALTER SESSION 

    alter session set parameter=value;

    atlasdb=# alter session set work_mem='10MB';

    SET

    atlasdb=# show work_mem ;

     work_mem

    ----------

     10MB

    (1 row)

    1.3.3. ALTER TRIGGER

    v ALTER TRIGGER tg_name DISABLE;

    v ALTER TRIGGER tg_name ENABLE;

    v ALTER TRIGGER tg_name RENAME TO tg_name1;

    CREATE TABLE student (

          id   int primary key,

          name varchar(50)

        );

     CREATE TABLE score (

          studentId  int,

           math     int

        );

    INSERT INTO student VALUES(1,'April');

    INSERT INTO student VALUES(2,'Harris');

    INSERT INTO score VALUES(1, 98);

    INSERT INTO score VALUES(2,77);

    CREATE OR REPLACE FUNCTION student_delete_trigger()

    RETURNS TRIGGER AS $$

    BEGIN

       DELETE FROM score where studentId = OLD.id;

        RETURN OLD;

    END;

    $$

    LANGUAGE plpgsql;

    CREATE TRIGGER delete_trigger

    AFTER DELETE ON student

    FOR EACH ROW EXECUTE PROCEDURE student_delete_trigger();

    atlasdb=# d student

                         Table "atlasdb.student"

     Column |         Type          | Collation | Nullable | Default

    --------+-----------------------+-----------+----------+---------

     id     | integer               |           | not null |

     name   | character varying(50) |           |          |

    Indexes:

        "student_pkey" PRIMARY KEY, btree (id)

    Triggers:

        delete_trigger AFTER DELETE ON student FOR EACH ROW EXECUTE PROCEDURE student_delete_trigger()

    ALTER TRIGGER delete_trigger DISABLE;

    ALTER TRIGGER delete_trigger RENAME TO delete_trigger2;

    DELETE FROM student where id = 2;

    select * from score

     studentid | math

    -----------+------

             1 |   98

             2 |   77

    (2 rows)

    ALTER TRIGGER delete_trigger2 ENABLE;

    DELETE FROM student where id = 1;

    select * from score

     studentid | math

    -----------+------

             2 |   77

    (1 row)

    1.3.4. CREATE SYNONYM 

    创建同义词

    CREATE [OR REPLACE] [PUBLIC] SYSNONYM schema.]synonym_name FOR [schema2.]object_name;

    CREATE SYNONYM personnel FOR public.mytable

     

    删除同义词

    DROP [PUBLIC] SYNONYM [schema.]sysnonym_name;

    drop SYNONYM personnel ;

    1.3.5. Create Global Temporary Table 

    创建全局临时表,语法如下:

    Create Global Temporary Table Temp_Name

    (

    Col1 Type1,

    Col2 Type2

    ...

    )

    On Commit { Preserve | Delete }  Rows;

    示例

    atlasdb=# Create Global Temporary Table Temp_tb1 ( id int) on commit delete rows;

    CREATE TABLE

    atlasdb=# insert into Temp_tb1 values (1);

    INSERT 0 1

    atlasdb=# select * from Temp_tb1;

     id

    ----

    (0 rows)

    1.3.6. CREATE SEQUENCE

    兼容oracle nocache,nocycle语法

    CREATE SEQUENCE customers_seq

    START WITH 1000

    INCREMENT BY 1

    NOCACHE

    NOCYCLE;

     

     

    2. Oracle宏变量

    v SYSDATE

    获取当前系统时间

    select sysdate from dual;

    -[ RECORD 1 ]----------------

    sysdate | 2020-03-09 08:37:32

     

    v SYSTIMESTAMP

    获取当前系统时间戳

    atlasdb=# select SYSTIMESTAMP;

    -[ RECORD 1 ]----------------

    sysdate | 2020-03-09 08:40:07

     

    v DBTIMEZONE

    获取当前数据库时区

    atlasdb=# select DBTIMEZONE;

    -[ RECORD 1 ]---

    dbtimezone | GMT

     

    v SESSIONTIMEZONE

    获取当前会话时区。

    atlasdb=# select SESSIONTIMEZONE;

    -[ RECORD 1 ]---+----

    sessiontimezone | PRC

     

    v ROWNUM

    获取查询结果中的元组序号

    atlasdb=# select rownum from dual;

    -[ RECORD 1 ]

    rownum | 1

     

    v ROWID

    获取一条记录的一个相对唯一地址值

    atlasdb=# select rowid from mytab;

    -[ RECORD 1 ]---------------


    • 分享到:
    排序方式:回复时间 共有0条评论