- データベース管理者のための主要20c新機能の学習
- データベース管理者のための主要20c新機能の学習
- ツールおよび言語
- SQL
- SQLマクロ
- 演習: SQMスカラー式および表式の使用
演習: SQMスカラー式および表式の使用
これらの演習では、SQLマクロをスカラー式および表式として使用する方法を示します。
- 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
- 最初の演習は、SQLマクロの実行中に定義された回数だけ従業員名をその名前に連結する方法を示す簡単なものです。
HRスキーマおよび表を作成します。$ sqlplus sys@pdb20 AS SYSDBA SQL*Plus: Release 20.0.0.0.0 - Production on Wed Apr 1 12:32:01 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: password Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 SQL> @$ORACLE_HOME/demo/schema/human_resources/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. SQL> EXIT $- SQMをスカラー式として作成します。
$ sqlplus hr@PDB20 SQL*Plus: Release 20.0.0.0.0 - Production on Mon Mar 16 10:37:50 2020 Version 20.2.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Enter password: password Last Successful login time: Mon Mar 16 2020 09:27:07 +00:00 Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production Version 20.2.0.0.0 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> - SQMを使用して表を問い合せ、従業員名を二重にして表示します。
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> - SQMを使用して表を問い合せ、従業員名を三重にして表示します。
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>
- 2番目の演習では、SQMを表式として使用して多相ビューを実装する方法を示します。
- 単純なビューを使用して、部門ごとの給与の合計を表示します。
SQL> CREATE VIEW v_budget AS SELECT department_id, sum(salary) v_budget FROM hr.employees GROUP BY department_id; View created. SQL> - ビューから結果を問い合せます。
SQL> SELECT * FROM v_budget WHERE department_id IN (10,50); DEPARTMENT_ID V_BUDGET ------------- ---------- 50 156400 10 4400 SQL> - ここで、SQMを表式として使用します。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> - SQMを使用して、部門10および50の結果を表示します。
SQL> SELECT * FROM budget() WHERE department_id IN (10,50); DEPARTMENT_ID BUDGET ------------- ---------- 50 156400 10 4400 SQL>
- 単純なビューを使用して、部門ごとの給与の合計を表示します。
- 3番目の演習では、SQMを表式として使用して、特定のジョブについて部門ごとの給与の合計を表示する方法を示します。
- 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> - SQMを使用して、部門10の
ST_CLERKジョブの結果を表示します。SQL> SELECT * FROM budget_per_job('ST_CLERK') WHERE department_id = 10; no rows selected SQL> - SQMを使用して、部門50の
SH_CLERKジョブの結果を表示します。SQL> SELECT * FROM budget_per_job('SH_CLERK') WHERE department_id = 50; DEPARTMENT_ID BUDGET_PER_JOB ------------- -------------- 50 64300 SQL> DBMS_OUTPUTパッケージを使用して、書き換えられたSQL問合せを表示します。DBMS_OUTPUTパッケージを含めて関数を再作成します。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>- 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>
- SQMを作成します。
USER_PROCEDURESビューを使用して、SQL_MACRO列の新しい値を表示します。SQL> COL object_name FORMAT A30 SQL> SELECT object_name, sql_macro, object_type FROM user_procedures; OBJECT_NAME SQL_MA OBJECT_TYPE ------------------------------ ------ ------------- SECURE_DML NULL PROCEDURE BUDGET TABLE FUNCTION ADD_JOB_HISTORY NULL PROCEDURE BUDGET_PER_JOB TABLE FUNCTION CONCAT_SELF SCALAR FUNCTION SECURE_EMPLOYEES TRIGGER UPDATE_JOB_HISTORY TRIGGER 7 rows selected. SQL> EXIT $
親トピック: SQLマクロ