Teradata - Stored Procedure
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.
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.
Stored Procedures are created using CREATE PROCEDURE statement.
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;
Consider the following Salary Table.
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;
Stored Procedures are executed using CALL statement.
Following is the generic syntax of the CALL statement.
CALL <procedure name> [(parameter values)];
The following example calls the stored procedure InsertSalary and inserts records to Salary Table.
Once the above query is executed, it produces the following output and you can see the inserted row in Salary table.