Oracle 21c 实训演练：SQL Macro 宏的特性和试验

首先，请开启一个 Oracle 21c 实训环境。

1. 按照实训手册，创建 初始化HR Schema Demo
2. 连接数据库：sqlplus hr/hr2021@modb:1521/xepdb1

接下来，查询如下输出：

SELECT e.first_name || ' ' || e.last_name "Name", j.JOB_ID,
TRUNC(MONTHS_BETWEEN(j.end_date, j.start_date)) "Months Worked",
TRUNC(j.end_date - j.start_date) "Days Worked",
TRUNC(j.end_date - j.start_date)*24 "Hours Worked"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY 1;
复制

以上查询显示了员工在公司不同岗位，工作的月数、天数、小时 数，还好这是 Date 类型，如果是 timestamp 类型，查询还会复杂得多。

查询结果如下：

Name                                           JOB_ID     Months Worked Days Worked Hours Worked
---------------------------------------------- ---------- ------------- ----------- ------------
Den Raphaely                                   ST_CLERK              21         647        15528
Jennifer Whalen                                AD_ASST               69        2100        50400
Jennifer Whalen                                AC_ACCOUNT            53        1644        39456
Jonathon Taylor                                SA_MAN                11         364         8736
Jonathon Taylor                                SA_REP                 9         282         6768
Lex De Haan                                    IT_PROG               66        2018        48432
Michael Hartstein                              MK_REP                46        1401        33624
Neena Kochhar                                  AC_ACCOUNT            49        1497        35928
Neena Kochhar                                  AC_MGR                40        1234        29616
Payam Kaufling                                 ST_CLERK              11         364         8736
复制

现在在 21c 中，有了 SQL Marco ，则可以通过创建宏，简化SQL，反复调用，并且因为宏在SQL解析式处理，没有函数的上下文切换，会获得更好的性能提升，同时增加程序的可读性，还便于代码重用。

以下我们可以创建几个宏函数：

create or replace function months(
 dt1 in date,
 dt2 in date
) return varchar2 sql_macro(scalar) is
 begin
  return 'trunc(months_between(dt1,dt2))';
end;
/

create or replace function days(
 dt1 in date,
 dt2 in date
) return varchar2 sql_macro(scalar) is
 begin
  return '(dt1-dt2)';
end;
/

create or replace function hours(
 dt1 in date,
 dt2 in date
) return varchar2 sql_macro(scalar) is
 begin
  return 'trunc(dt1-dt2)*24';
end;
/
复制

然后，前面的SQL可以改写如下：

SELECT e.first_name || ' ' || e.last_name "Name", j.JOB_ID,
months(j.end_date,j.start_date) "Months Worked",
days(j.end_date,j.start_date) "Days Worked",
hours(j.end_date,j.start_date) "Hours Worked"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY 1;
复制

输出结果如下：

Name                                           JOB_ID     Months Worked Days Worked Hours Worked
---------------------------------------------- ---------- ------------- ----------- ------------
Den Raphaely                                   ST_CLERK              21         647        15528
Jennifer Whalen                                AD_ASST               69        2100        50400
Jennifer Whalen                                AC_ACCOUNT            53        1644        39456
Jonathon Taylor                                SA_MAN                11         364         8736
Jonathon Taylor                                SA_REP                 9         282         6768
Lex De Haan                                    IT_PROG               66        2018        48432
Michael Hartstein                              MK_REP                46        1401        33624
Neena Kochhar                                  AC_ACCOUNT            49        1497        35928
Neena Kochhar                                  AC_MGR                40        1234        29616
Payam Kaufling                                 ST_CLERK              11         364         8736
复制

作业

将表中的 Date 数据类型，更改为 Timestamp，重新制造 Demo 数据，然后实现类似的逻辑和三个宏函数。