Structure Query Language (SQL): Chapter 9 – Stored Procedures.

A stored procedure is a set of instructions developed in T-SQL language (Transact-Sql) that, whenever stored or saved, are inside the server in a pre-compiled way. This procedure is stored in the database (Server) and can be called at any time, either by the database management system (DBMS) or by an application using Elipse E3. Using stored procedures helps improve the applications’ performance.

The goal of this article is to show the basic use of stored procedures in conjunction with Elipse E3. The database used in this example is Microsoft SQL Server 2012.

Creating a stored procedure with an input parameter

The first step is to create a table in the database to be used later by the stored procedure:

CREATE TABLE tbl_teste
       (
user_ssn int not null primary key,
user_nome varchar(128) not null,
user_email varchar(128) not null
       )
GO

In the example below, we’ll create a stored procedure that will pass parameters, where the Social Security number (SSN) will be informed as an input parameter. The stored procedure will return this result:

— CREATE STORED PROCEDURE
CREATE PROCEDURE SSNregistry_SELECT
— INPUT PARAMETER, IN THIS CASE THE SOCIAL SECURITY NUMBER
@SSN int
AS
— T-SQL COMMAND TO RETRIEVE RESULT
SELECT * FROM tbl_test
WHERE user_ssn = @SSN
GO

The figure below shows the command to execute the stored procedure. The number informed next to the instruction (998958889) is the input parameter:


Figure 1: Execute command

After the stored procedure has been created and set up in the database, is must run it via E3. First, add a Query, and then enble SQL’s direct edition by adding the following line:


Figure 2: Execution via E3

This SQL command executes the stored procedure by sending a variable as input parameter (<%input%>).

To send an input value and return the value of the query performed by the stored procedure, add the following script to E3:


Figure 3: Script inserted in E3


Creating a stored procedure with an input parameter and an output return

The first step is to create a table in the database to be used later by the stored procedure:

CREATE TABLE STAFFERS
(
CODE varchar(5),
NAME varchar(80),
DEPARTMENT int,
WAGES money
)
GO

In the example below, we’ll create a stored procedure that will pass input and output parameters; in this case, you will need to inform an OUTPUT variable. This stored procedure sends an input parameter as selection criteria for retrieving the sum of wages in the STAFFERS table via an output parameter.

CREATE PROCEDURE Wages_Dept
           (@DEPT int,
@SUM money OUTPUT) AS
BEGIN
SELECT @SUM=sum(WAGES) FROM STAFFERS
WHERE DEPARTMENT = @DEPT
END

The stored procedure will be executed as follows:

— Output variable
DECLARE @TOTAL_WAGES Money
— Variable for passing input parameter
DECLARE @DEPARTMENT int
SET @DEPARTMENT = 5
EXEC Wage_Dept@DEPARTMENT, @TOTAL_WAGE OUTPUT
PRINT @TOTAL_WAGES

Stored procedure sending and receiving parameters via E3:


Figure 4: Executing the SP via E3 Query

Executing the query, you’ll get the following result:


Figure 5: E3 Query result

To execute an Oracle stored procedure from E3, use call command in the Query’s SQL code, and then the name of the stored procedure as well as its parameters. To execute the command, use Query’s Execute method.

Related Articles


Este artigo foi útil? Was this post helpful?
Yes0
No0

Leave a Reply

Your email address will not be published.Required fields are marked *