Teradata - Stored Procedure


Advertisements


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

Stored Procedures are created using CREATE PROCEDURE statement.

Syntax

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

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

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

Stored Procedures are executed using CALL statement.

Syntax

Following is the generic syntax of the CALL statement.

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

Example

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

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

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


Advertisements
E-Books Store