[20240927]学习SQL MASCROS.txt

--//抽一点时间学习SQL MASCROS，我的理解类似C语句的宏，起替换的作用，这样看上去sql语句代码显得更加简洁一些。
--//参考链接：https://connor-mcdonald.com/2021/11/16/sql-macros-complex-sql-made-easy-elapsed-time-calculation/

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
SCOTT@book01p> select ename,hiredate,(sysdate-hiredate)*86400 from emp where empno=7369;
ENAME      HIREDATE            (SYSDATE-HIREDATE)*86400
---------- ------------------- ------------------------
SMITH      1980-12-17 00:00:00               1381593182

--//如果sysdate换成systimestamp。
SCOTT@book01p> select ename,hiredate,(systimestamp-hiredate)*86400 from emp where empno=7369;
select ename,hiredate,(systimestamp-hiredate)*86400 from emp where empno=7369
                                             *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small

--//问题出在返回的数据类型，前者返回整形，而后者执行如下。
SCOTT@book01p> select ename,hiredate,(systimestamp-hiredate) from emp where empno=7369;
ENAME      HIREDATE            (SYSTIMESTAMP-HIREDATE)
---------- ------------------- --------------------------
SMITH      1980-12-17 00:00:00 +000015990 15:54:31.577475

--//改写如下：
select ename,hiredate, extract(day from (systimestamp-hiredate))*86400+
extract(hour from (systimestamp-hiredate))*3600+
extract(minute from (systimestamp-hiredate))*60+
extract(second from (systimestamp-hiredate)) ela
from emp where empno=7369;

ENAME      HIREDATE                   ELA
---------- ------------------- ----------
SMITH      1980-12-17 00:00:00 1381593449

--//如果大量的语句使用类似的功能代码就显得很冗余，sql macros就可以在这里发挥作用。

create or replace function elapsed(
  ts1 in timestamp,
  ts2 in timestamp
) return varchar2 sql_macro(scalar) is
begin
  return '
extract(day from (ts2-ts1))*86400+
extract(hour from (ts2-ts1))*3600+
extract(minute from (ts2-ts1))*60+
extract(second from (ts2-ts1))';
end;
/
Function created.
--//注意micros的写法，实际上返回的类型是字符串。定义sql micros是scalar。

SCOTT@book01p> select ename,hiredate,elapsed(hiredate,systimestamp) from emp where empno=7369;
ENAME      HIREDATE            ELAPSED(HIREDATE,SYSTIMESTAMP)
---------- ------------------- ------------------------------
SMITH      1980-12-17 00:00:00                     1381593831

SCOTT@book01p> select ename,hiredate,elapsed(hiredate,sysdate) from emp where empno=7369;
ENAME      HIREDATE            ELAPSED(HIREDATE,SYSDATE)
---------- ------------------- -------------------------
SMITH      1980-12-17 00:00:00                1381593938

--//使用systimestamp，sysdate两者都可以。而前面如果写成如下：

select ename,hiredate, extract(day from (sysdate-hiredate))*86400+
extract(hour from (sysdate-hiredate))*3600+
extract(minute from (sysdate-hiredate))*60+
extract(second from (sysdate-hiredate)) ela
from emp where empno=7369;
select ename,hiredate, extract(day from (sysdate-hiredate))*86400+
                                                *
ERROR at line 1:
ORA-30076: invalid extract field for extract source

SCOTT@book01p> select  extract(day from (sysdate-hiredate))*86400 from emp where empno=6369;
select  extract(day from (sysdate-hiredate))*86400 from emp where empno=6369
                                 *
ERROR at line 1:
ORA-30076: invalid extract field for extract source

--//这样代码通用性要好许多。
--//如果使用函数这样写是错误的，这样返回的是字符串。
create or replace function elapsed1(
  ts1 in timestamp,
  ts2 in timestamp
) return varchar2  is
begin
  return '
extract(day from (ts2-ts1))*86400+
extract(hour from (ts2-ts1))*3600+
extract(minute from (ts2-ts1))*60+
extract(second from (ts2-ts1))';
end;
/
Function created.

SCOTT@book01p> select ename,hiredate,elapsed1(hiredate,sysdate) c60 from emp where empno=7369;
ENAME      HIREDATE            C60
---------- ------------------- ------------------------------------------------------------
SMITH      1980-12-17 00:00:00
                               extract(day from (ts2-ts1))*86400+
                               extract(hour from (ts2-ts1))*3600+
                               extract(minute from (ts2-ts1))*60+
                               extract(second from (ts2-ts1))

--//如果使用函数,改写如下：
create or replace function elapsed2(
  ts1 in timestamp,
  ts2 in timestamp
) return number  is
begin
  return
extract(day from (ts2-ts1))*86400+
extract(hour from (ts2-ts1))*3600+
extract(minute from (ts2-ts1))*60+
extract(second from (ts2-ts1));
end;
/

SCOTT@book01p> select ename,hiredate,elapsed2(hiredate,sysdate) from emp where empno=7369;
ENAME      HIREDATE            ELAPSED2(HIREDATE,SYSDATE)
---------- ------------------- --------------------------
SMITH      1980-12-17 00:00:00                 1381594378

--//至于两者性能上是否有差别我不是很清楚。
--//在链接下还有一些例子：https://connor-mcdonald.com/2024/02/26/scalar-sql-macros-cherry-pick-the-best-features/
--//还是定义sql_macro(table)，网上应该找到许多例子，能生成更加复杂的情况。