“Freedom from effort in the present merely means that there has been effort stored up in the past.”
- Theodore Roosevelt
Stored Procedures
•Contains SQL
•Contains comprehensive SPL
•Parameter values can be passed to it
•Must use a cursor to retrieve > than 1 row
•Stored in DATABASE or USER PERM
•May return 1 or more values to client as parameter
Macros
•Contains SQL
•May contain BTEQ Dot commands
•Parameter values can be passed
•May retrieve 1 or more rows
•Stored in DBC PERM Space
•Returns rows to the client
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.
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.
You SELECT from a View, EXECUTE a Macro, and you CALL a Stored Procedure.
CALL Second_Procedure ( ) ;
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.
CALL Declare_Procedure ( ) ;
When you DECLARE a variable and then reference that variable later, a colon is always in front of the Variable.
CALL SetVar_Procedure ( ) ;
Once a variable and the data type is defined the value must be assigned. SET is the more flexible a method compared to DEFAULT.
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. Warning: You cannot add, subtract, or change an IN variable. You set it when you call the procedure and that value remains constant.
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;
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. Notice that we named the OUT parameter Msg and then we needed to put the name Msg in our Call statement.
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 var1 < var2 THEN SET Msg = 'Variable 1 less' ;
END IF ;
IF var1 > 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”? Because both Var1 and Var2 were the same value.
These queries do the SAME thing. However, the first one is more efficient because it only does TWO calculations instead of three.
LOOPs require Labeling. Much like when you have more than one BEGIN/END.
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.
Your mission is to create the table above and then create a stored procedure that will insert 1,000 rows. The tricky part is that col1 should have 1,000 unique values, but col2 should have only 250 different values.
CREATE Table SQL01.Table_TLC
( Col1 INTEGER
,Col2 INTEGER)
Primary Index (Col1) ;
CREATE PROCEDURE SQL01.Final_TLC ()
BEGIN
DECLARE cntr INTEGER Default 0;
MyLoop:LOOP
SET cntr = cntr + 1;
IF cntr > 1000 THEN LEAVE MyLoop;
END IF;
INSERT INTO SQL01.Table_TLC
(:cntr, :cntr MOD 250);
END LOOP MyLoop;
END;
CALL SQL01.Final_TLC () ;
Your mission was to create a table and then create a stored procedure that will insert 1,000 rows. The tricky part was that col1 had 1,000 unique values, but col2 had only 250 different values. This is the way we did it.
CREATE PROCEDURE SQL01.Final_TLC2 ()
BEGIN
DECLARE cntr INTEGER Default 0;
DECLARE cntr2 INTEGER Default 0;
MyLoop:LOOP
SET cntr = cntr + 1;
IF cntr > 1000 THEN LEAVE MyLoop;
END IF;
Set cntr2 = cntr2 + 1;
IF cntr2 > 250 THEN Set cntr2 = 1;
END IF;
INSERT INTO SQL01.Table_TLC
(:cntr, :cntr2);
END LOOP MyLoop;
END;
CALL SQL01.Final_TLC2 () ;
Your mission was to create a table and then create a stored procedure that will insert 1,000 rows. The tricky part was that col1 had 1,000 unique values, but col2 had only 250 different values. This is another way we did it.