[20240927]学习SQL MASCROS.txt

[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)，网上应该找到许多例子，能生成更加复杂的情况。