Practice: Using SQM Scalar and Table Expressions

Overview

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

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

Step 1 : Use SQL Macro as a scalar expression

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

    
    $ 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: 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 an 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 Macro as a table expression

  • The first usage of an 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 the 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 an 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. Recreate 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
      $