Chapter 32
Stored Procedure Functions
“Freedom from effort in the present merely means that there has been effort stored up in the past.”
Table of Contents Chapter 32 – Stored Procedure Functions
– Stored Procedures Vs. Macros
– How you CALL a Stored Procedure
– Label all BEGIN and END statements except the first ones
– How to Declare a Variable and then SET the Variable
– An IN Variable is passed to the Procedure during the CALL
– The IN, OUT and INOUT Parameters
– Using IF inside a Stored Procedure
– Example of two Stored Procedures with different techniques
– Using Loops in Stored Procedures
– You can Name the First Begin and End if you choose
– Using Keywords LEAVE vs. UNTIL for LEAVE vs. REPEAT
Stored Procedures Vs. Macros
| Macros | Stored Procedures |
| • Contains SQL | • Contains SQL |
| • May contain BTEQ Dot commands | • Contains comprehensive SPL |
| • Parameter values can be passed | • Parameter values can be passed to it |
| • May retrieve 1 or more rows | • Must use a cursor to retrieve > than 1 row |
| • Stored in DBC PERM Space | • Stored in DATABASE or USER PERM |
| • Returns rows to the client | • May return 1 or more values to client as parameter |
Stored Procedures are a lot like Macros. However, they manipulate data a row at a time. Stored Procedures take up PERM Space, unlike Views and Macros that do NOT. Stored Procedures are actually compiled, and will use a Cursor to retrieve or manipulate more than one row. Although, Stored Procedures utilize SQL they also utilize SPL, which stands for Stored Procedure Language, which provides loops, while, etc.
Creating a Stored Procedure
THE BEGIN and END statements are required in all Stored Procedures. Don't miss a semi-colon. They are everywhere. I have bolded them for your convenience.
How you CALL a Stored Procedure
CREATE PROCEDURE First_Procedure ( )
BEGIN
INSERT INTO Customer_Table DEFAULT VALUES;
END;
You SELECT from a View, EXECUTE a Macro, and you CALL a Stored Procedure.
Label all BEGIN and END statements except the first ones
When you have multiple BEGIN and END statements, you have to label them all (except for the first BEGIN and END statements). We have labeled our next set of BEGIN and END SecondSection.
How to Declare a Variable
When you DECLARE a variable and then reference that variable later, a colon is always in front of the Variable.
How to Declare a Variable and then SET the Variable
Once a variable and the data type is defined the value must be assigned. SET is the more flexible a method compared to DEFAULT.
An IN Variable is passed to the Procedure during the CALL
The Variable Var1 was not assigned with the DEFAULT or the SET, but instead passed as a parameter. There are three types of parameters (IN, OUT, INOUT). In this example, an IN is being used.
The IN, OUT and INOUT Parameters
CREATE PROCEDURE Test_Proc
(IN var1 BYTEINT, IN var2 BYTEINT, OUT Msg CHAR(20) )
BEGIN
CASE WHEN var1 = var2 THEN Set Msg = ‘They are equal' ;
WHEN var1 < var2 THEN Set Msg = ‘Variable 1 less’ ;
ELSE Set Msg = ‘Variable 1 greater’ ;
END CASE;
END;
CALL Test_Proc (1,2, Msg) ;
| Msg |
| Variable 1 Less |
There are three types of parameters (IN, OUT, INOUT). This is an example of an IN and an OUT parameter. What that means is this Stored Procedure will take a parameter in and then spit something out.
Using IF inside a Stored Procedure
CREATE PROCEDURE TestIF_Proc
(IN var1 BYTEINT, IN var2 BYTEINT, OUT Msg CHAR(20) )
BEGIN
IF var1 = var2 THEN SET Msg = ‘They are equal';
END IF;
IF vari < var2 THEN SET Msg = ‘Variable 1 less';
END IF;
IF vari > var2 THEN SET Msg = ‘Variable 1 greater';
END IF;
END;
CALL TestIF_Proc (2,2, Msg ) ;
| Msg |
| They are equal |
Why did the Msg say “They are equal”?
Example of two Stored Procedures with different techniques
These queries do the SAME thing. However, the first one is more efficient because it only does TWO calculations instead of three.
Using Loops in Stored Procedures
CALL Inserter_Five () ;
SELECT * FROM My_Log_Tbl ORDER BY 1 ;
| Cntr | The Time |
| 1 | 09:48:43 |
| 2 | 09:48:43 |
| 3 | 09:48:43 |
| 4 | 09:48:43 |
| 5 | 09:48:43 |
LOOPs require Labeling. Much like when you have more than one BEGIN/END.
You can Name the First Begin and End if you choose
CREATE Table My_Log_Tbl
(
Cntr Integer
,TheTime Time
) Primary Index (Cntr);
First we have to CREATE the Table.
CREATE PROCEDURE Inserter_Five ( )
LOOPER:BEGIN
DECLARE Cntr INTEGER DEFAULT 0
Loopit:LOOP
SET Cntr = Cntr + 1;
IF Cntr > 5 THEN LEAVE Loopit;
END IF;
INSERT INTO My_Log_Tbl
VALUES (:Cntr, TIME);
END LOOP Loopit ;
END LOOPER;
Then we build the Stored Procedure
CALL Inserter_Five () ;
Now we call the Stored Procedure
This loops 5 times! We didn't have to label Looper because it's the first Begin and End. The LEAVE statement is how the LOOP is told it is done looping.
Using Keywords LEAVE vs. UNTIL for LEAVE vs. REPEAT
--Procedure One
CREATE PROCEDURE Ins5( )
LOOPER:BEGIN
DECLARE Cntr INTEGER
DEFAULT 0;
Loopit:LOOP
SET Cntr = Cntr + 1;
IF Cntr > 5 THEN LEAVE Loopit;
END IF;
INSERT INTO My_Log_Tbl
VALUES (:Cntr, TIME);
END LOOP Loopit ;
END LOOPER;
Both Procedures above do the same thing. The UNTIL keyword in Procedure Two jumps it out of the REPEAT Loop. There are some differences in the above. The first example (Procedure One) tests Cntr before the INSERT, but Procedure two does not so Procedure two will always do at least one INSERT no matter what Cntr is set at.