例: 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; );