Teradata 简明教程

Teradata - Macros

宏是一组 SQL 语句，它们通过调用宏名称来存储和执行。宏的定义存储在数据字典中。用户只需要 EXEC 权限来执行宏。用户不需要对宏中使用的数据库对象有单独的权限。宏语句作为单个事务执行。如果宏中的一个 SQL 语句失败，那么所有语句都会回滚。宏可以接受参数。宏可以包含 DDL 语句，但该语句应为宏中的最后一条语句。

Macro is a set of SQL statements which are stored and executed by calling the Macro name. The definition of Macros is stored in Data Dictionary. Users only need EXEC privilege to execute the Macro. Users don’t need separate privileges on the database objects used inside the Macro. Macro statements are executed as a single transaction. If one of the SQL statements in Macro fails, then all the statements are rolled back. Macros can accept parameters. Macros can contain DDL statements, but that should be the last statement in Macro.

Create Macros

使用 CREATE MACRO 语句创建宏。

Macros are created using CREATE MACRO statement.

Syntax

以下为 CREATE MACRO 命令的通用语法。

Following is the generic syntax of CREATE MACRO command.

CREATE MACRO <macroname> [(parameter1, parameter2,...)] (
   <sql statements>
);

Example

考虑以下 Employee 表。

Consider the following Employee table.

EmployeeNo

FirstName

LastName

BirthDate

101

Mike

James

1/5/1980

104

Alex

Stuart

11/6/1984

102

Robert

Williams

3/5/1983

105

Robert

James

12/1/1984

103

Peter

Paul

4/1/1983

以下示例创建了一个名为 Get_Emp 的宏。它包含一个 select 语句以从员工表中检索记录。

The following example creates a Macro called Get_Emp. It contains a select statement to retrieve records from employee table.

CREATE MACRO Get_Emp AS (
   SELECT
   EmployeeNo,
   FirstName,
   LastName
   FROM
   employee
   ORDER BY EmployeeNo;
);

Executing Macros

使用 EXEC 命令执行宏。

Macros are executed using EXEC command.

Syntax

以下为 EXECUTE MACRO 命令的语法。

Following is the syntax of EXECUTE MACRO command.

EXEC <macroname>;

Example

以下示例执行名为 Get_Emp 的宏；当执行以下命令时，它会从员工表中检索所有记录。

The following example executes the Macro names Get_Emp; When the following command is executed, it retrieves all records from employee table.

EXEC Get_Emp;
*** Query completed. 5 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
EmployeeNo             FirstName                      LastName
-----------  ------------------------------  ---------------------------
   101                  Mike                          James
   102                  Robert                        Williams
   103                  Peter                         Paul
   104                  Alex                          Stuart
   105                  Robert                        James

Parameterized Macros

Teradata 宏可以接受参数。在宏中，这些参数用 ;(分号)引用。

Teradata Macros can accept parameters. Within a Macro, these parameters are referenced with ; (semicolon).

以下是接受参数的宏示例。

Following is an example of a Macro that accepts parameters.

CREATE MACRO Get_Emp_Salary(EmployeeNo INTEGER) AS (
   SELECT
   EmployeeNo,
   NetPay
   FROM
   Salary
   WHERE EmployeeNo = :EmployeeNo;
);

Executing Parameterized Macros

使用 EXEC 命令执行宏。您需要 EXEC 权限才能执行宏。

Macros are executed using EXEC command. You need EXEC privilege to execute the Macros.

Syntax

以下为 EXECUTE MACRO 语句的语法。

Following is the syntax of EXECUTE MACRO statement.

EXEC <macroname>(value);

Example

以下示例执行名为 Get_Emp 的宏；它接受员工编号作为参数，并为该员工从员工表中提取记录。

The following example executes the Macro names Get_Emp; It accepts employee no as parameter and extracts records from employee table for that employee.

EXEC Get_Emp_Salary(101);
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

EmployeeNo      NetPay
-----------  ------------
   101           36000