Practice: Using SQM Scalar and Table Expressions

Overview

These practices show how to use SQL Macros as scalar and table expressions.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Use SQL Macros as a scalar expression

Ensure that PDB21 is opened. If it is not opened, open it.


$ sqlplus / AS SYSDBA

Connected.

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB21                          READ WRITE NO
SQL>

Create the HR schema and its tables in PDB21.


SQL> CONNECT sys@pdb21 AS SYSDBA
Enter password:
Connected.
SQL> @$HOME/labs/M104780GC10/hr_main.sql password users temp /home/oracle/labs /home/oracle/labs
specify password for HR as parameter 1:

specify default tablespeace for HR as parameter 2:

specify temporary tablespace for HR as parameter 3:

specify log path as parameter 4:

PL/SQL procedure successfully completed.

User created.

User altered.

User altered.

Grant succeeded.

Grant succeeded.
...

Commit complete.

PL/SQL procedure successfully completed.

$

Connect as HR in PDB21 and create the SQM as a scalar expression.


$ sqlplus hr@PDB21

Enter password: password

Connected to:

SQL> CREATE OR REPLACE FUNCTION concat_self(str varchar2, cnt pls_integer)
            RETURN VARCHAR2 SQL_MACRO(SCALAR) 
      IS BEGIN
            RETURN 'rpad(str, cnt * length(str), str)';
END;
/

Function created.

SQL>

Use the SQM to query the table and display the employees names doubled.


SQL> COL CONCAT_SELF(LAST_NAME,2) FORMAT A40
SQL> SELECT last_name, concat_self(last_name,2) FROM hr.employees;

LAST_NAME                 CONCAT_SELF(LAST_NAME,2)
------------------------- ----------------------------------------
Abel                      AbelAbel
Ande                      AndeAnde
Atkinson                  AtkinsonAtkinson
Austin                    AustinAustin
Baer                      BaerBaer
Baida                     BaidaBaida
Banda                     BandaBanda
Bates                     BatesBates
Bell                      BellBell
Bernstein                 BernsteinBernstein
Bissot                    BissotBissot
...
107 rows selected.

SQL>

Use the SQM to query the table and display the employees names tripled.


SQL> COL CONCAT_SELF(LAST_NAME,3) FORMAT A40
SQL> SELECT last_name, concat_self(last_name,3) FROM hr.employees;

LAST_NAME                 CONCAT_SELF(LAST_NAME,3)
------------------------- ----------------------------------------
Abel                      AbelAbelAbel
Ande                      AndeAndeAnde
Atkinson                  AtkinsonAtkinsonAtkinson
Austin                    AustinAustinAustin
Baer                      BaerBaerBaer
Baida                     BaidaBaidaBaida
Banda                     BandaBandaBanda
Bates                     BatesBatesBates
Bell                      BellBellBell
Bernstein                 BernsteinBernsteinBernstein
Bissot                    BissotBissotBissot
Bloom                     BloomBloomBloom
Bull                      BullBullBull
Cabrio                    CabrioCabrioCabrio
...
107 rows selected.

SQL>

Step 2 : Use SQL Macros as a table expression

The first usage of a SQL macro as a table expression shows how to use the SQM to implement a polymorphic view.

Use a simple view to display the sum of the salaries per department.


SQL> CREATE VIEW v_budget 
 AS SELECT department_id, sum(salary) v_budget 
    FROM hr.employees
    GROUP BY department_id;

View created.

SQL>

Query the result from the view.


SQL> SELECT * FROM v_budget WHERE department_id IN (10,50);

DEPARTMENT_ID   V_BUDGET
------------- ----------
           50     156400
           10       4400

SQL>

Now use an SQM as a table expression. Create the SQM.


SQL> CREATE OR REPLACE FUNCTION budget
return varchar2 SQL_MACRO
IS
BEGIN
  RETURN q'( select department_id, sum(salary) budget
             from hr.employees 
             group by department_id )';
END;
/

Function created.

SQL>

Use the SQM to display the result for departments 10 and 50.


SQL> SELECT * FROM budget() WHERE department_id IN (10,50);

DEPARTMENT_ID     BUDGET
------------- ----------
           50     156400
           10       4400

SQL>

The second usage of a SQL macro as a table expression shows how to use the SQM to display sum of the salaries per department for a particular job.

Create the SQM.


SQL> CREATE OR REPLACE FUNCTION budget_per_job(job_id varchar2)
return varchar2 SQL_MACRO
IS
BEGIN
  RETURN q'( select department_id, sum(salary) budget
             from hr.employees 
             where job_id = budget_per_job.job_id
             group by department_id )';
END;
/

Function created.

SQL>

Use the SQM to display the result for the ST_CLERK job in department 10.


SQL> SELECT * FROM budget_per_job('ST_CLERK') WHERE department_id = 10;

no rows selected

SQL>

Use the SQM to display the result for the SH_CLERK job in department 50.


SQL> SELECT * FROM budget_per_job('SH_CLERK') WHERE department_id = 50;

DEPARTMENT_ID BUDGET_PER_JOB
------------- --------------
           50          64300

SQL>

Use the DBMS_OUTPUT package to display the rewritten SQL query. Re-create the function, including the DBMS_OUTPUT package.


SQL> CREATE OR REPLACE function budget_per_job(job_id varchar2)
return varchar2 SQL_MACRO
is
  stmt varchar(2000) := q'(
   select department_id, sum(salary) budget
   from hr.employees
   where job_id = budget_per_job.job_id
   group by department_id )';
begin
  dbms_output.put_line('----------------------------------------------');
  dbms_output.put_line('SQM Text: ' );
  dbms_output.put_line('----------------------------------------------');
  dbms_output.put_line('  ' ||stmt);
  dbms_output.put_line('----------------------------------------------');
  return stmt;
end;
/

Function created.

SQL>

Re-execute the query using the SQM.


SQL> SET serveroutput on
SQL> SET LONG 20000
SQL> SELECT * FROM budget_per_job('ST_CLERK') WHERE department_id = 50;

DEPARTMENT_ID     BUDGET
------------- ----------
           50      55700

----------------------------------------------
SQM Text:
----------------------------------------------

   select department_id, sum(salary) budget
   from hr.employees
   where
   job_id = budget_per_job.job_id
   group by department_id
----------------------------------------------

SQL>

Use the USER_PROCEDURES view to display the new values of the SQL_MACRO column.


SQL> COL object_name FORMAT A30
SQL> SELECT object_name, sql_macro, object_type FROM user_procedures;

OBJECT_NAME                    SQL_MA OBJECT_TYPE
------------------------------ ------ -------------
CONCAT_SELF                    SCALAR FUNCTION
SECURE_DML                     NULL   PROCEDURE
ADD_JOB_HISTORY                NULL   PROCEDURE
BUDGET                         TABLE  FUNCTION
BUDGET_PER_JOB                 TABLE  FUNCTION
SECURE_EMPLOYEES                      TRIGGER
UPDATE_JOB_HISTORY                    TRIGGER

7 rows selected.

SQL> EXIT
$