17.10 - CREATE MACROおよびREPLACE MACROの例 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1144-171K-JPN
Language
日本語 (日本)

例: INSERT操作とその後のSELECT検査操作

次のリクエストでは、最初にemployee(従業員)テーブルに新しい従業員の行を挿入し、次にその情報が正しく入力されたことを確認するためにSELECTリクエストを実行するマクロを作成することを示します。

    CREATE MACRO new_emp1oyee (
      number   INTEGER, 
      name     VARCHAR(12), 
      dept     INTEGER    DEFAULT 900, 
      position VARCHAR(12) 
      sex      CHARACTER, 
      dob      DATE       FORMAT 'MMMbDDbYYYY', 
      edlev    BYTEINT )  AS
      (INSERT INTO employee (empno,name,deptno,jobtitle,sex,dob,edlev)
       VALUES (:number, :name, :dept, :position, :sex, :dob, :edlev);
       --   The following select verifies the insert       
       SELECT * 
       FROM employee 
       WHERE empno = :number; );

-- 注釈構文を使用して、マクロ内に注釈を含めます。--の後から行末までのテキストは、実行されません。

このマクロをANSIセッション モードで実行した場合、INSERTはまだコミットされていません。これは、Teradataセッション モードでも同じです。Teradataセッション モードでは、このマクロは明示的なトランザクションの一部として実行されています。

例: INSERT操作とその後のUPDATE操作

この例では、employeeテーブルに新しい従業員のための行も挿入するマクロを作成し、SELECTリクエストではなくUPDATEリクエストを実行します。UPDATEリクエストは、:deptパラメータの値と一致する部門番号を含む行の従業員数を1つ増やして数えて、departmentテーブルを変更します。

     CREATE MACRO new_employee_2 (
      (number   INTEGER, 
       name     VARCHAR(12), 
       dept     INTEGER     DEFAULT 900, 
       position VARCHAR(12), 
       sex      CHARACTER, 
       dob      DATE        FORMAT 'MMMbDDbYYYY', 
       edlev    BYTEINT)   AS
     (INSERT INTO employee (empno,name,deptno,jobtitle,sex,dob,edlev)
      VALUES (:number, :name, :dept, :position, :sex, :dob, :edlev) ;
      UPDATE department 
      SET empcount=empcount+1 
      WHERE deptno = :dept; );

このマクロをANSIセッション モードで実行した場合、INSERT文およびUPDATEリクエストはまだコミットされていません。これは、Teradataセッション モードでも同じです。Teradataセッション モードでは、このマクロは明示的なトランザクションの一部として実行されています。

例: REPLACE MACROの使用

以下の文はマクロを置換します。

    REPLACE MACRO new_employee(name VARCHAR(12) NOT NULL, 
     street CHARACTER(30), 
     city   CHARACTER(20), 
     number INTEGER NOT NULL, 
     dept   SMALLINT DEFAULT 999) AS
    (INSERT INTO employee (name, street, city, empno, deptno) 
      VALUES (:name, :street, :city, :number, :dept); 
    UPDATE department 
    SET empcount = empcount + 1 
    WHERE deptno = :dept ;);

例: UDTパラメータのマクロ サポート

この例では、マクロのp2パラメータはVARCHARで、このパラメータはマクロに渡されます。変換は、NEWコンストラクタ式を使用してマクロ自体の内部で実行されます。<Teradata Vantage™ - SQL演算子およびユーザー定義関数、B035-1210>を参照してください。

CREATE MACRO m2 (p1 integer, p2 VARCHAR(100)) 
AS (INSERT t1(:p1, NEW structured_type(:p2)););
USING (a INTEGER, b VARCHAR(100))
EXEC m2(:a, :b);

例: トリガーによるUDTパラメータのマクロ サポート

mailing_addressesというテーブルに新規顧客の名、姓、および自宅の住所を挿入するins_mail_listというマクロがあるとします。顧客の自宅の住所の値は、addressというSTRUCTURED型UDTを使って定義されます。

そのマクロは、次のように定義されます。

CREATE MACRO insert_mail_list (first VARCHAR(15), last VARCHAR(15), 
                               addr address)
AS (INSERT INTO mailing_addresses VALUES (:first, :last, :addr););

次のトリガーは、新規顧客行が顧客表に挿入されるたびにcustomerの新規顧客をメーリング リストに追加します。mailing_addresses表への挿入は、トリガーされるSQLアクション文、マクロinsert_mail_listを使って行なわれますが、これはUDTパラメータaddressを受け取ります。

CREATE TRIGGER ca_mailing_list 
AFTER INSERT ON customer
REFERENCING NEW AS newrow
FOR EACH ROW
WHEN (newrow.address.state() = 'CA')
EXEC insert_mail_list(newrow.name.last(), newrow.name.first(),
     newrow.address);

例: TOPnの値をパラメータとしてマクロに渡す

この例では、TOP n演算子のnの整数値を、pという名前のパラメータとしてマクロに渡します。

CREATE MACRO m (p INTEGER)
AS (SELECT TOP :p x1 
    FROM t1;);

例: パラメータを使用したトランザクション用クエリー バンドの設定

次のマクロは、パラメータqbinを使用してトランザクション用クエリー バンドを設定します。

CREATE MACRO qbmac (p1 INTEGER, p2 INTEGER, qbin VARCHAR(200)
AS (SET QUERY_BAND = :qbin FOR TRANSACTION
    SELECT GetQueryBand(););
 *** Macro has been created.
 *** Total elapsed time was 1 second.
EXEC qbmac (5,10,'music=classical;musician=david_tudor;');
 *** Set QUERY_BAND accepted.
*** Total elapsed time was 1 second.
 *** Query completed. One row found. One column returned.
GetQueryBand()
--------------------------------------------------------
=T> music=classical;musician=david_tudor;

例: マクロ定義からのSQL UDFの呼び出し

次の例では、マクロcommon_value_expressionの定義内でカプセル化されたSELECTリクエスト内からSQL UDFm1を呼び出します。

CREATE MACRO m1 (a1 INTEGER, b1 INTEGER, c1 INTEGER)
AS (SELECT test.common_value_expression(:a1 = :b1) 
    FROM t1 
    WHERE t1.a1 = :c1; );

例: マクロ呼び出し前にNEWコントラクタ呼び出しを使用するUDTパラメータのマクロ サポート

次の例は、UDTパラメータのマクロ サポートを説明しています。

マクロのp2パラメータはSTRUCTURED型UDTです。

VARCHARからSTRUCTURED型UDTへの変換はNEWコンストラクタ呼び出し式を使って行なわれますが、これはマクロが実際に呼び出される前に実行されます。

    CREATE MACRO m1 (p1 INTEGER, p2 structured_type) 
    AS (INSERT t1(:p1, :p2););
    USING (a INTEGER, b VARCHAR(100))
    EXEC m1(:a, NEW structured_type(:b));

例: ABORTまたはROLLBACK条件の指定

定義内にABORTリクエストまたはROLLBACKリクエストを組み入れることにより、マクロの実行を中止させる条件を指定します。実行中に指定された条件が検出されると、そのマクロはアボートされます。進行中のトランザクションは終了し、テーブルに対するロックは解除され、データに対して行なわれた変更は元に戻され、スプール出力は削除されます。

例として、部門番号が300の重役室に従業員を加えないようnew_employeeマクロを使用不能にするためには、次のようにABORTリクエストをマクロの指定に組み込みます。

    CREATE MACRO personnel.new_employee
     (number    (SMALLINT     FORMAT '9(5)'), 
      name      (VARCHAR(12)), 
      dept      (SMALLINT     FORMAT '999'), 
      position  (VARCHAR(12)), 
      birthdate (DATE         FORMAT 'MMMbDDbYYYY'), 
      sex       (CHARACTER(1)) 
      education (BYTEINT))   AS
    (
    (ABORT 'Department 300 not valid' 
     WHERE :dept = 300;
     INSERT INTO employee (empno,name,deptno,jobtitle,dob,sex,edlev)
     VALUES (:number,:name,:dept,:position,:birthdate,:sex,:education);     );

ABORTキーワードに続けて、任意のエラー メッセージをAPOSTROPHE文字で囲んで指定します。このメッセージは、指定された条件でこのマクロがアボートされた場合に、端末の画面に表示されます。この例では、ABORTリクエストのWHERE句の中でアボート条件:dept = 300が指定されています。

例: ABORT条件を使用した削除

この例では、DELETEリクエストを示し、次にdepartmentテーブルのその従業員数が減らされるところを示しています。削除しようとする従業員の行がemployeeテーブルにない場合、ABORTリクエストでこのマクロの実行を終了させます。

次の例に、ANSIモードで使用するために設計されたマクロを示します。この例では、ユーザーは、削除および更新操作が正常に終了した場合に、コミットすることを希望しています。マクロ内のリクエストは、1つの複文リクエストとして処理されます。そのため、ROLLBACK文のWHERE条件を満たした場合、リクエスト全体がアボートされ、empcountの値は保護されます。

    CREATE MACRO delete_employee 
     (num   SMALLINT    FORMAT '9(5)', 
      dname VARCHAR(12), 
      dept  SMALLINT    FORMAT '999') AS
    (ABORT 'Name does not exist' 
     WHERE :num NOT IN (SELECT empno
                        FROM employee 
                        WHERE name = :dname);
          
     DELETE FROM employee 
     WHERE name = :dname;
          
     UPDATE department 
     SET empcount = empcount - 1
     WHERE deptno = :dept; 
     COMMIT WORK; );