Teradata 简明教程

Teradata - Stored Procedure

存储过程包含一组 SQL 语句和过程语句。它们可能仅包含过程语句。存储过程的定义存储在数据库中，并且参数存储在数据字典表中。

A stored procedure contains a set of SQL statements and procedural statements. They may contain only procedural statements. The definition of stored procedure is stored in database and the parameters are stored in data dictionary tables.

Advantages

Stored procedures reduce the network load between the client and the server.
Provides better security since the data is accessed through stored procedures instead of accessing them directly.
Gives better maintenance since the business logic is tested and stored in the server.

Creating Procedure

存储过程使用 CREATE PROCEDURE 语句创建。

Stored Procedures are created using CREATE PROCEDURE statement.

Syntax

以下是 CREATE PROCEDURE 语句的通用语法。

Following is the generic syntax of the CREATE PROCEDURE statement.

CREATE PROCEDURE <procedurename> ( [parameter 1 data type, parameter 2 data type..] )
BEGIN
   <SQL or SPL statements>;
END;

Example

请考虑以下 Salary 表。

Consider the following Salary Table.

EmployeeNo	Gross	Deduction	NetPay
101	40,000	4,000	36,000
102	80,000	6,000	74,000
103	90,000	7,000	83,000
104	75,000	5,000	70,000

以下示例创建了一个名为 InsertSalary 的存储过程以接受值并将其插入 Salary 表中。

The following example creates a stored procedure named InsertSalary to accept the values and insert into Salary Table.

CREATE PROCEDURE InsertSalary (
   IN in_EmployeeNo INTEGER, IN in_Gross INTEGER,
   IN in_Deduction INTEGER, IN in_NetPay INTEGER
)
BEGIN
   INSERT INTO Salary (
      EmployeeNo,
      Gross,
      Deduction,
      NetPay
   )
   VALUES (
      :in_EmployeeNo,
      :in_Gross,
      :in_Deduction,
      :in_NetPay
   );
END;

Executing Procedures

存储过程使用 CALL 语句执行。

Stored Procedures are executed using CALL statement.

Syntax

以下是 CALL 语句的通用语法。

Following is the generic syntax of the CALL statement.

CALL <procedure name> [(parameter values)];

Example

以下示例调用存储过程 InsertSalary 并将记录插入 Salary 表中。

The following example calls the stored procedure InsertSalary and inserts records to Salary Table.

CALL InsertSalary(105,20000,2000,18000);

一旦执行以上查询，它将生成以下输出，您可以在 Salary 表中看到已插入的行。

Once the above query is executed, it produces the following output and you can see the inserted row in Salary table.

EmployeeNo	Gross	Deduction	NetPay
101	40,000	4,000	36,000
102	80,000	6,000	74,000
103	90,000	7,000	83,000
104	75,000	5,000	70,000
105	20,000	2,000	18,000