CREATE TABLE foo(a int);
ALTER TABLE foo ADD COLUMN a1 int;
ALTER TABLE foo ADD COLUMN a2 int default 3;
ALTER TABLE foo ADD COLUMN b1 int, ADD COLUMN b2 int;
ALTER TABLE foo ADD (c1 int);
ALTER TABLE foo ADD (c2 int default 3);
ALTER TABLE foo ADD (e1 int), ADD e2 int, ADD COLUMN e3 int, ADD (e4 int, e5 int);
begin
execute immediate 'alter table foo add(d int)';
end;
/
1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE 命令支持 TRUNCATE PARTITION partition_name 时指定 update indexes。 示例
create table part_update_indexes_range(id int primary key)
PARTITION by range(id)(
partition p1 VALUES LESS THAN (202000) nocompress,
partition p2 VALUES LESS THAN (202001) compress,
partition p3 VALUES LESS THAN (202002)
);
ALTER TABLE part_update_indexes_range TRUNCATE PARTITION p1 update indexes;
1
2
3
4
5
6
7
8
DROP TABLE 命令支持通过 cascade constraints 级联删除该表所有的约束。 示例
CREATE TABLE student(id int, name varchar2(100));
ALTER TABLE student ADD CONSTRAINT uk_student unique(id);
\d student
Table "public.student"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
id | integer | | |
name | varchar2(100) | | |
Indexes:
"uk_student" UNIQUE CONSTRAINT, btree (id)
DROP TABLE student cascade constraints;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
新增 CREATE TYPE name { AS | IS } OBJECT 语法，支持创建对象类型。 示例
select dbms_output.serveroutput(true);
CREATE TYPE stock AS OBJECT (
stock_no int,
price numeric(16,2),
member procedure dis(prompt varchar)
);
CREATE TYPE BODY stock AS
member procedure dis(prompt varchar) is
begin
dbms_output.put_line(prompt || ':' || self.stock_no || '-' || self.price);
end;
END;
/
DECLARE
s stock := stock(600570, 180.00);
BEGIN
s.dis('current');
END;
/
current:600570-180.00
DO
CREATE TABLE testorafce_to_clob (
col_char CHAR(10),
col_varchar2 VARCHAR2(20),
col_varchar VARCHAR(20),
col_nchar NCHAR(10),
col_nvarchar2 NVARCHAR2(20),
col_smallint smallint,
col_integer integer,
col_bigint bigint,
col_decimal decimal,
col_numeric numeric,
col_real real,
col_double double precision,
col_clob CLOB,
col_raw raw(10)
);
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC1', 'Hello', 'world', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', 'AB');
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC2', 'Hello', 'world', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', '1');
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC3', 'Hello', 'world', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, to_clob('This is a CLOB'), '1AB456789');
SELECT to_clob(col_char) AS clob_char,
to_clob(col_varchar2) AS clob_varchar2,
to_clob(col_varchar) AS col_varchar,
to_clob(col_nchar) AS clob_nchar,
to_clob(col_nvarchar2) AS clob_nvarchar2,
to_clob(col_clob) AS clob_clob,
to_clob(col_smallint) AS col_smallint,
to_clob(col_integer) AS col_integer,
to_clob(col_bigint) AS col_bigint,
to_clob(col_decimal) AS col_decimal,
to_clob(col_numeric) AS col_numeric,
to_clob(col_real) AS col_real,
to_clob(col_double) AS col_double,
to_clob(col_raw) AS clob_nclob
FROM testorafce_to_clob order by col_char asc;
clob_char | clob_varchar2 | col_varchar | clob_nchar | clob_nvarchar2 | clob_clob | col_smallint | col_integer | col_bigint | col_decimal | col_numeric | col_real | col_double | clob_nclob
------------+---------------+-------------+------------+----------------+----------------+--------------+-------------+------------+-------------+-------------+----------+------------+------------
ABC1 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | AB
ABC2 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 01
ABC3 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 01AB456789
(3 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
新增 XMLType 对象类型，支持 XMLType.getClobVal 方法。 示例
CREATE TABLE xml_table (id NUMBER, xml_column XMLType );
INSERT INTO xml_table (id, xml_column) VALUES (1, XMLType('John'));
DECLARE
v_clob CLOB;
BEGIN
SELECT XMLType.getClobVal(xml_column) INTO v_clob FROM xml_table WHERE id = 1;
DBMS_OUTPUT.PUT_LINE('CLOB value: ' || v_clob);
END;
/
CLOB value: John
DO
DECLARE
xml_data XMLType;
clob_data CLOB;
BEGIN
xml_data := XMLType('John Doe');
clob_data := xml_data.getClobVal();
DBMS_OUTPUT.PUT_LINE(clob_data);
END;
/
John Doe
DO
SELECT XMLType.getClobVal(xml_column) from xml_table;
getclobval
--------------------------------
John
(1 row)
create table std(no int, name varchar2(32));
insert into std values(1, 'zhangsan');
select * from std;
no | name
----+----------
1 | zhangsan
(1 row)
set lightdb_oracle_sql_mode to 'true';
select * from std;
NO | NAME
----+----------
1 | zhangsan
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ltsql 命令新增支持Oracle SQL*Plus的 RENAME old_name TO new_name 命令，可以对表名进行重命名。
