暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 23C新特性之SQL功能增强

23C新特性之SQL功能增强

原创 bicewow All China Database Union 2023-09-04
286

★ SELECT 语句不带 FROM 子句

不带from直接可查询

[oracle@db23c ~]$ zs

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on 星期一 8月 21 15:23:44 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

连接到: 
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

zs@ZSPDB> select sysdate; <==== 实现不用FROM即可进行查询

SYSDATE
-------------------
2023-08-21 10:55:30

PL/SQL 支持

set serveroutput on

declare
  v_date date;
begin
  select sysdate
  into v_date;

  dbms_output.put_line(v_date);
end;
/

2023-08-21 10:57:07

隐式语句结果

在其他数据库引擎中，我们经常看到这种类型的语法用于从过程中传递结果，在Oracle中这种是不可行的。

create or replace procedure get_date as
begin
  select sysdate;
end;
/
警告：过程已创建，但有编译错误。

zs@ZSPDB> show errors
PROCEDURE GET_DATE 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PLS-00428: 在此 SELECT 语句中缺少 INTO 子句

可以使用Oracle 12.1中引入的隐式语句结果来复制预期的功能。

create or replace procedure get_date as
  l_cursor sys_refcursor;
begin
  open l_cursor for
    select sysdate;
  dbms_sql.return_result(l_cursor);
end;
/


exec get_date;

ResultSet #1

SYSDATE
-------------------
2023-08-21 11:00:06

模仿在其他数据库引擎中看到的内容。

查询转换

下面看看使用这个新语法时，背后会发生什么。

首先，刷新共享池，并确定将为新会话创建的跟踪文件。

session 1
alter system flush shared_pool;


session 2

set linesize 100
column value format a65

select value
from   v$diag_info
where  name = 'Default Trace File';

VALUE
-----------------------------------------------------------------
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_159982.trc

现在我们对该语句进行10053事件跟踪

alter session set events '10053 trace name context forever';

select sysdate; 

alter session set events '10053 trace name context off';

检查得到的跟踪文件，搜索以“Final query after transformations”开头的部分，我们看到以下语句

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT SYSDATE@! "SYSDATE" FROM "SYS"."DUAL" "DUAL"

该语句已被转换为包含FROM DUAL。

UPDATE和DELETE语句关联表直接使用

使用FROM子句将UPDATE和DELETE语句中的目标表连接到其他表。这些其他表可以限制更改的行，也可以是新值的来源。

功能验证

数据准备

drop table if exists test_upt_del1 purge;
drop table if exists test_upt_del2 purge;

create table test_upt_del1 as
select level as id,
       'num' || level as num,
       'str1 for ' || level as str
from   dual
connect by level <= 10;

alter table test_upt_del1 add constraint t1_pk primary key (id);

create table test_upt_del2 as
select level as id,
       'num' || level as num,
       'str2 for ' || level as str
from   dual
connect by level <= 10;

alter table test_upt_del2 add constraint t2_pk primary key (id);

UPDATE命令通过多表直接关联

column num for a10
column str format a30

zs@ZSPDB> select * from test_upt_del1 where id <= 5;

        ID NUM        STR
---------- ---------- ------------------------------
         1 num1       str1 for 1
         2 num2       str1 for 2
         3 num3       str1 for 3
         4 num4       str1 for 4
         5 num5       str1 for 5

5 rows selected.

SQL>

通过关联test_upt_del2中的id值来更新test_upt_del1的num和str

zs@ZSPDB> update test_upt_del1 a 
set    a.num        = b.num,
       a.str = b.str
from   test_upt_del2 b
where  a.id = b.id
and    b.id <= 5; 

# 数据已经被更新
zs@ZSPDB> select * from test_upt_del1 where id <= 5;

        ID NUM        STR
---------- ---------- ------------------------------
         1 num1       str2 for 1
         2 num2       str2 for 2
         3 num3       str2 for 3
         4 num4       str2 for 4
         5 num5       str2 for 5

5 rows selected.

rollback;

DELETE命令通过多表直接关联

column num for a10
column str format a30
zs@ZSPDB> select * from test_upt_del1 where id <= 5;

        ID NUM        STR
---------- ---------- ------------------------------
         1 num1       str1 for 1
         2 num2       str1 for 2
         3 num3       str1 for 3
         4 num4       str1 for 4
         5 num5       str1 for 5

5 rows selected.

通过关联test_upt_del2中的id值来删除test_upt_del1数据

zs@ZSPDB> delete test_upt_del1 a 
from   test_upt_del2 b
where  a.id = b.id
and    b.id <= 5; 

zs@ZSPDB> select * from test_upt_del1 where id <= 5;

no rows selected

rollback;

UPDATE在NULL值精细控制

官方文档地址： ON NULL

新语法

# DEFAULT ON NULL 此语法默认为 DEFAULT ON NULL FOR INSERT ONLY
DEFAULT ON NULL [FOR INSERT ONLY]  

DEFAULT ON NULL FOR INSERT AND UPDATE

功能演示

  • DEFAULT ON NULL 测试
[oracle@db23c ~]$ zs

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Aug 25 16:03:14 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 23 2023 11:13:40 +08:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

zs@ZSPDB>

# 测试表

drop table if exists test_null purge;

create table test_null (
  id            number,
  str1  varchar2(15) default on null 'red',
  str2  varchar2(15) default on null for insert only 'blue'
);

# 插入数据

insert into test_null (id, str1, str2) values (1, null, null);
insert into test_null (id) values (2);

# 已插入默认值
zs@ZSPDB>select * from test_null;

        ID STR1            STR2
---------- --------------- ---------------
         1 red             blue
         2 red             blue

2 rows selected.

# 手动更新str1 str2为NULL, 设置了只有insert为NULL时给默认值，updat提示错误

zs@ZSPDB>update test_null set str1=null,str2=null;
update test_null set str1=null,str2=null
                     *
ERROR at line 1:
ORA-01407: cannot update ("ZS"."TEST_NULL"."STR1") to NULL
  • DEFAULT ON NULL FOR INSERT AND UPDATE
# 创建测试表，指定insert update为NULL时，使用默认值
drop table if exists test_null purge;

create table test_null (
  id           number,
  str1  varchar2(15) default on null for insert and update 'red'
);

# 插入数据

insert into test_null (id, str1) values (1, null);
insert into test_null (id) values (2);
insert into test_null (id,str1) values (3,'blue');


        ID STR1
---------- ---------------
         1 red
         2 red
         3 blue

3 rows selected.

# 使用update id 3为NULL,查询，ID 3被指定默认值red

update test_null set str1=null where id=3;

zs@ZSPDB>select * from test_null;

        ID STR1
---------- ---------------
         1 red
         2 red
         3 red

3 rows selected.
  • 触发器测试

创建基于test_null的insert和update的触发器，并使用:new.str1给新值赋为NULL，这种情况下，:new.str1的赋值操作将不会被给予默认值，并提示报错。

create or replace trigger tnull_trg 
before insert or update on test_null
for each row
begin
  :new.str1 := NULL;
end;
/

insert into test_null (id, str1) values (4, null);


update test_null set str1='blue' where id=3;

zs@ZSPDB>insert into test_null (id, str1) values (4, null);
insert into test_null (id, str1) values (4, null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ZS"."TEST_NULL"."STR1")

zs@ZSPDB>update test_null set str1='blue' where id=3;
update test_null set str1='blue' where id=3
                     *
ERROR at line 1:
ORA-01407: cannot update ("ZS"."TEST_NULL"."STR1") to NULL

Boolean 数据类型

boolean列

使用boolean或bool关键字定义布尔数据类型

[oracle@db23c trace]$ zs

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on 星期一 8月 21 13:54:39 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

连接到: 
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0 

drop table if exists zs_test_boolean purge;

create table zs_test_boolean (
  id        number generated always as identity,
  active    boolean,
  archived  bool
);

可以通过多种方式为布尔类型赋值。以下全部将活动标志设置为true，将存档标志设置为false

STATE TRUE FALSE
- ‘true’ ‘false’
- ‘yes’ ‘no’
- ‘on’ ‘off’
- ‘1’ ‘0’
- ‘t’ ‘f’
- ‘y’ ‘n’

注意，数字被转换为布尔值，如下所示

  • 0转换为FALSE

  • 非0值（如42或-3.14）转换为TRUE

insert into zs_test_boolean (active, archived) values (true, false);
insert into zs_test_boolean (active, archived) values (TRUE, FALSE);

insert into zs_test_boolean (active, archived) values ('true', 'false');
insert into zs_test_boolean (active, archived) values ('TRUE', 'FALSE');

insert into zs_test_boolean (active, archived) values ('yes', 'no');
insert into zs_test_boolean (active, archived) values ('YES', 'NO');

insert into zs_test_boolean (active, archived) values ('on', 'off');
insert into zs_test_boolean (active, archived) values ('ON', 'OFF');

insert into zs_test_boolean (active, archived) values (1, 0);
insert into zs_test_boolean (active, archived) values ('1', '0');

insert into zs_test_boolean (active, archived) values ('t', 'f');
insert into zs_test_boolean (active, archived) values ('T', 'F');

insert into zs_test_boolean (active, archived) values ('y', 'n');
insert into zs_test_boolean (active, archived) values ('Y', 'N');

commit;

查询结果

zs@ZSPDB> select * from zs_test_boolean;

        ID ACTIVE      ARCHIVED
---------- ----------- -----------
         1 TRUE        FALSE
         2 TRUE        FALSE
         3 TRUE        FALSE
         4 TRUE        FALSE
         5 TRUE        FALSE
         6 TRUE        FALSE
...

已选择 14 行。

Boolean列约束支持

布尔列支持以下约束：

  • NOT NULL

  • UNIQUE

  • PRIMARY KEY

  • FOREIGN KEY

  • CHECK

Boolean列比较与赋值

布尔列支持以下比较运算符：

=, !=, < >, <, <=, >, >=, GREATEST, LEAST, [NOT] IN

zs@ZSPDB> select * from zs_test_boolean where ACTIVE = TRUE;

        ID ACTIVE      ARCHIVED
---------- ----------- -----------
         1 TRUE        FALSE
         2 TRUE        FALSE
         3 TRUE        FALSE
         4 TRUE        FALSE
         5 TRUE        FALSE
         6 TRUE        FALSE
...

已选择 14 行。

zs@ZSPDB> select * from zs_test_boolean where ACTIVE = false;

未选定行

Boolean列布尔运算

可以对SQL条件、布尔列和布尔常量使用NOT、AND和OR运算符

zs@ZSPDB> select * from zs_test_boolean where ACTIVE and true;

        ID ACTIVE      ARCHIVED
---------- ----------- -----------
         1 TRUE        FALSE
         2 TRUE        FALSE
         3 TRUE        FALSE
         4 TRUE        FALSE
         5 TRUE        FALSE
         6 TRUE        FALSE
 ...

已选择 14 行。

zs@ZSPDB> select * from zs_test_boolean where ACTIVE and false;

未选定行


zs@ZSPDB> select * from zs_test_boolean where ACTIVE or false;

        ID ACTIVE      ARCHIVED
---------- ----------- -----------
         1 TRUE        FALSE
         2 TRUE        FALSE
         3 TRUE        FALSE
         4 TRUE        FALSE
         5 TRUE        FALSE
         6 TRUE        FALSE
...

已选择 14 行。

Boolean 运算 NOT

NOT（TRUE）为FALSE。NOT（FALSE）为TRUE。NOT（NULL）为NULL

Boolean 运算 AND

AND TRUE FALSE NULL
TRUE TRUE FALSE NULL
FALSE FALSE FALSE FALSE
NULL NULL FALSE NULL

示例：


zs@ZSPDB> select TRUE AND FALSE;

TRUEANDFALS
-----------
FALSE

zs@ZSPDB> select TRUE and NULL;

TRUEANDNULL
-----------


zs@ZSPDB> select NULL and NULL;

NULLANDNULL
-----------


zs@ZSPDB> select false and NULL;

FALSEANDNUL
-----------
FALSE

Boolean 运算 OR

OR TRUE FALSE NULL
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE NULL
NULL TRUE NULL NULL

在这儿发现2个有意思函数


zs@ZSPDB>select BOOLEAN_AND_AGG(ACTIVE) from zs_test_boolean;

BOOLEAN_AND
-----------
TRUE

已选择 1 行。

zs@ZSPDB>select BOOLEAN_OR_AGG( false or true);

BOOLEAN_OR_
-----------
TRUE

已选择 1 行。

zs@ZSPDB>select BOOLEAN_OR_AGG( false or true or false);

BOOLEAN_OR_
-----------
TRUE

已选择 1 行。

zs@ZSPDB>select BOOLEAN_OR_AGG( false or false);

BOOLEAN_OR_
-----------
FALSE

已选择 1 行。

zs@ZSPDB>select BOOLEAN_AND_AGG( false and true);

BOOLEAN_AND
-----------
FALSE

已选择 1 行。

zs@ZSPDB>select BOOLEAN_AND_AGG( true and true);   

BOOLEAN_AND
-----------
TRUE

已选择 1 行。

Boolean 运算 IS

IS TRUE FALSE NULL
TRUE TRUE FALSE FALSE
FALSE FALSE TRUE FALSE
NULL FALSE FALSE TRUE

Boolean 运算 IS NOT

IS NOT TRUE FALSE NULL
TRUE FALSE TRUE TRUE
FALSE TRUE FALSE TRUE
NULL TRUE TRUE FALSE
墨力计划新特性oracle23c
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论