Aginity Netezza Workbench allows managing stored procedures easily and transparently.
The stored procedures in a database are available under the Stored Procedures node of the database in the object explorer tree.
To create a stored procedure:
1. Do one of the following:
a. Right-click the Stored Procedures node of the database in the object explorer tree that you want to create the stored procedure for and select Create Procedure in the context menu.
b. In the object explorer tree, select the node of the database that you want to create the stored procedure for and then click Object > Procedure > Create Procedure in the top menu.
2. In the General section of the Stored Procedure Properties form, enter the name of the procedure and its language and specify whether you like to execute the procedure as the owner.
Let's call the new procedure MY_NEW_PROCEDURE:
3. In the Arguments section, enter the variables for the procedure and set their data types, precision, and scale.
Let's specify one argument named "Result" here and select DATE as the data type for it:
Note that you can declare more variables later on if necessary, by editing the SQL query text.
4. In the Returns section of the form, select the data type, precision, and scale for the return data. You can also specify the reference table name here if necessary.
In our example, we will select DATE as the data type:
Click OK when done.
5. As a result, a new tab is created in the Query Editor window. The tab is named MY_NEW_PROCEDURE and contains the basic SQL query text that looks like the below:
Enter the query text at the cursor position — between BEGIN and END.
Let's enter the following here:
Result := MIN(DATE) FROM TABLE; RETURN Result;
Now the code of the stored procedure looks like this:
When called, this procedure will return the minimum value from the DATE column of the TABLE1 table in the current database.
6. Press CTL+F5 (execute as a single batch) to execute the SQL statement and finish creating the stored procedure. The name of the new stored procedure will appear in the Stored Procedures node of the database in the object explorer tree.